北屿

北屿小智障

0%

MySQL笔记

Posted on Edited on In

: colon
; semicolon

MySQL

SQL(Structured Query Language)

Features

  • Open source(开源)
  • Scalable(可伸缩)
  • Portable(可移植)
  • Works with client/server and embedded architecture(使用客户端/服务端嵌入式架构)
  • High performance(高性能)
  • Low TCO(低成本)
  • Reliable(可靠)
  • Easy to use(易于使用)
  • Secure(安全)
  • High availability(高可用性)

最小屏幕分辨率$1280\times 1024$, 推荐$1920\times 1200$及以上

Database Query Language

DDL

It is used to define and modify the structure the database objects, such as tables, views, and indexs.
数据定义语言(Data Definition Language, DDL)：数据的模式定义和数据的物理存取构建。

  • CREATE
  • ALTER
  • RENAME
  • DROP
  • TRUNCATE

DML

Is used to manipulate the data in database objects.
数据操纵语言(Data Manipulation Language, DML)：负责数据的操纵，包括查询及增、删、改等操作。

  • INSERT
  • UPDATE
  • DELETE
  • SELECT

DCL

Is used to control the access to the objects in the database.
数据控制语言(Data Control Language, DCL)：负责数据的完整性、安全性的定义与检查以及并发控制、故障恢复等功能。

  • GRANT(to assign access permissions to the users on the database objects.)
  • REVOKE(to deny permissions)

Layered architecture

Application layer

Administrative interface and utilities
Client interfaces and utilities
Query interface and utilities

Logical layer

Query processor subsystem
Transaction management subsystem
Recovery management subsystem
Storge management subsystem

储存引擎(Storage engines)

  • InnoDB(default)
  • MyISAM(supports full-text search indexs.)
  • Memory(Nontransaction-safe storage engine, supports temporary tables types and stores tables in memory rather than in secondary storage devices.)

Physical layer

MySql登录

1
2
3
4
5
6
mysql -P 端口号 -h mysql主机名\ip -u root(用户名) -p password dbname
mysql -P 3306 -h localhost -u root -p sakila(DatabaseName)

or 

mysql --port=port_num --host=hostname --user username --password=password dbname

最简单

1
mysql -u root -p

MySQL commands

1
2
3
4
5
6
7
8
9
10
11
12
Help or \h or ?
Eg. Help show
Clear or \c
Eg. Clear
Connect or \r
Eg. Connect sakila 
Quit or \q
Eg. Quit
Status or \s
Eg. Status
Delimiter or \d
Eg. Delimiter &

数据库

Database Objects

Table

A table contains data int rows and columns. It provides a structured organization of data. It offers a provision for maintaining data integrity by allowing users to:
Store only the data that satisfies certain pre-defined conditions.
Perform only those operations on the data that do not violate the conditions.

Index

An index is an internal table structure that MySQL uses to provide quick access to the rows of a table, based on the values of one or more columns. An index is similar to the index of a book.

View

A view is a virtual table that provides access to a subset of columns from one or more related tables.

Stored procedure

A stored procedure is a named block of SQL and procedural statements that are stored in the server and executed as a single unit.

Function

A function is a named block of SQL and procedural statements that are stored under one name and return a value.

Trigger

A trigger is a named object associated with a table, and comprises a block of SQL statements. A trigger is invoked automatically whenever an event on the assoociated table occurs.

Types of Databases

User-defined Databases
System Databases

  • information_schema
  • mysql
  • performance_schema

建立数据库

1
2
3
CREATE DATABASE [IF NOT EXISTS] <database_name>
[[DEFAULT] CHARACTER SET <character_set_name>]
[[DEFAULT] COLLATE <collation_name>]

The default character in MySQL is latin1.
big5 supports the Chinese language.

使用数据库

1
2
USE <database_name>
SHOW DATABASES;

修改数据库

1
2
3
ALTER DATABASE <database_name>
[[DEFAULT] CHARACTER SET <character_set_name>]
[[DEFAULT] COLLATE <collation_name>]

查询使用的character set

1
SHOW CHARACTER SET;

删除数据库

1
DROP DATABASE database_name;

MySQL does not allow deleting system databases.

数据类型

Numeric
String
Date

Integer Data Types

Data type Range for signed integers Range for unsigned integers Storage(Bytes)
TINYINT -128 to 127 0 to 255 1
SMALLINT -32,768 to 32,767 0 to 65535 2
MEDIUMINT -83,88,608 to 83,88,607 0 to 16777215 3
INT / INTEGER -2,147,483,648 to 2,147,483,647 0 to 4294967295 4
BIGINT -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 0 to 18446744073709551615 8

Fractional Data Types

Data type Range Used to store Storage(Bytes)
FLOAT -3.402823466E+38 to -1.175494351E-38, 0 and 1.175494351E-38 to 3.402823466E+38 Fractional value with floating single-precision 4
DOUBLE(p,s) PRECISION(p,s) REAL(p,s) -1.7976931348623157E+308 to -2.2250738585072014E-308, 0 and 2.2250738585072014E-308 to 1.7976931348623157E+308 Fractional value with floating double- precision and scale 8
DECIMAL(p,s) DEC(p,s) NUMERIC(p,s) FIXED(p,s) Depends on values specified in the column definition Fractional value with fixed precision and scale NA

Bit Data Type

BIT(M)

表示方法b’1001’

String

Data type Range Used to store
CHAR(n) n characters, 1 <= n <= 255 Fixed length
VARCHAR(n) n characters, 1 <=n <= 255 Variable length
TINYTEXT 1 to 255 characters Variable length
TEXT 1 to 65,535 characters Variable length
MEDIUMTEXT 1 to 16,777,215 characters Variable length
LONGTEXT 1 to 4,294,967,295 characters Variable length
BINARY(n) n bytes, 1<= n <= 255 Fixed length
VARBINARY(N) n bytes, 1<=n <= 255 Variable length
TINYBLOB 255 characters(355 bytes) Variable length
BLOB 1 to 65,535 characters(64KB) Variable length
MEDIUMBLOB 1 to 16,777,215 characters(16MB) Variable length
LONGBLOB 1 to 4,294,967,295 characters(4GB) Variable length

BLOB 类型可以储存图片

Date

Data type Range Used to store
DATE 1000-01-01 through 9999-12-31 Date data
DATETIME 1000-01-01 00:00:00 through 9999-12-31 23:59:59 Date and time data
TIMESTAMP 1970-01-01 00:00:00 2037-12-31 Date and time value that is updated with the current date and time value of system, whenever a record is updated or inserted into the table
YEAR 1901 to 2155 Year data
TIME -838:59:59 to 838:59:59 Time data

ENUM

用一个数字index表示数据

eg.

1
2
3
4
5
6
create table if not exists tb(
id int primary key not null,
status enum('Married', 'Unmarried', 'Divorced', 'Widow', 'Widower')
);

insert into tb values(1, 1), (2, 2), (3,4);

Set

跟ENUM差不多，但是是个集合，取值为子集而不是用index

Eg.

1
2
3
4
5
6
7
8
create table if not exists tb (
id int not null,
S set('A', 'B', 'C', 'D')
);

insert into tb value(1, ('A,B,C'));

select * from tb;

表格

创建表格

1
2
3
4
5
6
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <table_name>(<column_definition> [{, <column_definition>}...])
[ENGINE = { MEMORY | INNODB | MERGE | MRG_MYISAM | MYISAM}]

<column_definition>=
<column_name> <data_type> [NOT NULL | NULL] [DEFAULT <value>]
[AUTO_INCREMENT]

查看表格属性

1
2
DESCRIBE table_name [column_name | `value`]
DESC Products 'P%'

查看是数据库中表格

1
SHOW TABLES

添加约束

在创建表格时

1
2
3
[CONSTRAINT constraint_name] PRIMARY KEY(<column_name> [{, <column_name>}...])
[CONSTRAINT constraint_name] UNIQUE (<column_name> [{, <column_name>}...])
[CONSTRAINT constraint_name] FOREIGN KEY (<column_name> [{, <column_name>}...]) REFERENCES master_table_name(<column_name> [{,<column_name>}...])

修改表格

1
2
3
4
5
6
7
8
ALTER TABLE   <table_name>
{ADD [COLUMN] <column_definition> [FIRST|AFTER <column_name>]} |
{ADD [CONSTRAINT] <constraint_name> <constraint_clause> } |
{ALTER [COLUMN] <column_name> {SET DEFAULT <values> | DROP DEFAULT}} |
{MODIFY [COLUMN] <column_definition> [FIRST|AFTER <column_name>]} |
{DROP [COLUMN] <column_name> } |
{DROP PRIMARY KEY | FOREIGN KEY constraint_name | UNIQUE constraint_name} |
{RENAME [TO] <new_table_name>}

Manipulating Table Data

Inserting Data

INSERT Statement

1
2
3
4
5
6
INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] <table_name> 
{<values_option> | <set_option>};

<values_option>=
SET <column_name>={<value_list> | DEFAULT}
[{, <column_name>={value_list> | DEFAULT}}...]

REPLACE Statement

1
REPLACE [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] {<values_option> | <set_option>};

Updating Data

1
2
UPDATE [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] <table_name> 
SET <column_name>=<expression> [{, <column_name>=<expression>} ...] [WHERE <condition>];

Copying Data

SELECT statement

1
SELECT *|<column_name> [{,<column_name>}...] FROM <table_name>;

Copying data to an existing table

1
2
3
4
5
INSERT INTO <table_name>
SELECT *|<column_name> [{,<column_name>}...] FROM <table_name>;
or
REPLACE INTO <table_name>
SELECT *|<column_name> [{,<column_name>}...] FROM <table_name>;

Copying data to a new table

1
2
3
CREATE TABLE <table_name> (
<column_definition>[{, <column_definition>}...]
) SELECT *|<column_name> [{,<column_name>}...] FROM <table_name>;

Deleting Data

DELETE statement

1
DELETE [LOW_PRIORITY] [IGNORE] FROM <table_name> [WHERE <condition>];

TRUNCATE statement
删除所有记录，相当于把表删除重建

1
TRUNCATE [TALBE] <table_name>;

Removing a Table

1
DROP [TEMPORARY] TALBE [IF EXISTS] [database_name].<table_name>[{,<table_name>}...]

插入图片

long blob类型

1
insert into commoditytable values('qiezibao', load_file('F:\\Code\\mysql\\a.jpg'));

Retrieving Data 检索数据

Retrieving Specific Attributes

SELECT statement

1
2
SELECT [ALL|DISTINCT] select_expr[as new_name] [, select_expr...]
    [FROM table_reference] [WHERE where_condition]

逻辑运算符

1
2
3
OR(||)
AND(&&)
NOT(!)

比较运算符

1
2
3
4
5
6
7
8
9
>
<
=
>=
<=
<>, !=
BETWEEN
LIKE 类似于正则匹配(% is used to match any number of characters, _ is used to match a single characters) Eg. LIKE '_F%'
IN 在一个集合中 Eg. IN ('001', '002')

算术运算符

1
2
3
4
5
6
DIV (整除)
/ (除法)
-
%, MOD
+
*

用LIMIT约束

1
2
LIMIT [offset, ] row_count
offset 跳过计数的行数

用函数Customize查询结果

String Functions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
ASCII
SELECT ASCII('ABC'); return 65

LEFT
SELECT LEFT('DAVID', 4); return 'DAVI'

REVERSE
SELECT REVERSE('ABC'); return 'CBA'

RIGHT
SELECT RIGHT('LAWSON', 4); return 'WSON'

SUBSTRING
SELECT SUBSTRING('Whether', 2, 2); return 'he'

UPPER
SELECT UPPER('lawson'); return 'LAWSON'

LOWER
SELECT LOWRT('RICHARD'); return 'richard'

CONCAT
SELECT CONCAT('Mary', ' ', 'Smith'); return 'Mary Smith'

REGEXP
SELECT 'John Peter' REGEXP 'peter'
1
2
3
4
5
6
7
8
9
10
11
. matches a single character
p* matches zero or any number of occurrences of the p character
p+ matches one or any number of occurrences of the p character
p? matches zero or one occurrence of the p character
^p matches the string(s) that begins with a p character
p$ matches the string(s) that ends with a p character
pqr|abc matches the string(s) having either the sequence of pqr or abc
[pqrs] matches a single character from the characters contained in '[' and ']'
[^pqrs] matches a single character that is not contained in '[' and ']'
[p-s] matches any character from p to s
[^p-s] matches any character except the characters from p to s

Date Functions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ADDDATE
SELECT ADDDATE('2008-8-24 15:25:52', INTERVAL '8:20' HOUR_MINUTE); return '2008-08-24 23:45:52'
DATE
SELECT DATE('2008-8-24 15:25:52'); return '2008-08-24'
MONTH
SELECT MONTH('2008-8-24 15:25:52'); return 8
MONTHNAME
SELECT MONTHNAME('2008-8-24 15:25:52'); return 'August'
YEAR
SELECT YEAR('2008-8-24 15:25:52'); return 2008
DATEDIFF
SELECT DATEDIFF('2008-8-24 15:25:52', '2008-9-4 18:20:00'); return -11

TIMEDIFF
DAY
EXTRACT
DATE_FORMAT

expr

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
HOUR 小时
MINUTE 分
SECOND 秒
MICROSECOND 毫秒
YEAR 年
MONTH 月
DAY 日
WEEK 周
QUARTER 季
YEAR_MONTH 年和月
DAY_HOUR 日和小时
DAY_MINUTE 日和分钟
DAY_ SECOND 日和秒
HOUR_MINUTE 小时和分
HOUR_SECOND 小时和秒
MINUTE_SECOND 分钟和秒

1
2
3
4
5
6
7
8
9
%a name of weekday in an abbreviate
%b name of month in an abbreviate
%c month in numeric form
%H hour in numeric form
%i minutes in numeric form
%j day of year in numeric form
%M name of month
%p time in am or pm
%S second

Mathematical Functions

1
2
3
4
5
6
FLOOR
COT return cotangent of the specified angle in radians
PI
POW
ROUND (numeric_expression, length) SELECT ROUND(12.789, 2); return 12.79
SQRT

Information Functions

1
2
3
4
5
current_user()
connection_id()
database()
version()
charset() return character set of the string argument

Cast Functions

1
2
3
BINARY() # 大小写敏感
CAST() # CAST(expr AS type)
CONVERT() # CONVERT(expr, type)

Aggregate Functions

1
2
3
4
5
avg
sum
min
max
count

Sorting and Grouping Data

Sorting Data

1
2
3
4
5
6
7
SELECT
    [ALL | DISTINCT]
    select_expr [, select_expr ...]
    [FROM table_reference]
    [WHERE where_condition]
    [ORDER BY column_name]
    [ASC | DESC]

Grouping Data

1
2
3
4
5
6
7
SELECT
    [ALL | DISTINCT]
    select_expr [, select_expr ...]
    [FROM table_reference]
    [WHERE where_condition]
    [GROUP BY {column_name | expr}]
    [HAVING where_condition]

Join和Subqueries

Join

一张图片搞定

Inner Join

1
2
3
4
5
SELECT column_name, column_name, [, column_name]
FROM table1_name JOIN table2_name
ON table1_name.ref_column_name join_operator 
table2_name.ref_column_name | USING (ref_column_name)
[WHERE search_condition]

ON 和 USING 二选一，USING可以合并相同的列

Outer Join

LEFT OUTER JOIN
RIGHT OUTER JOIN

1
2
3
4
5
SELECT column_name, column_name, [, column_name]
FROM table1_name [LEFT | RIGHT] OUTER JOIN table2_name
ON table1_name.ref_column_name join_operator 
table2_name.ref_column_name | USING (ref_column_name)
[WHERE search_condition]

Natural Join

根据公共列连接两个表
A natural join clubs columns from one table with the columns of another table on the basis of a common column

1
2
SELECT column_name, column_name, [, column_name]
FROM <table_reference> NATURAL [{LEFT| RIGHT} [OUTER]] JOIN <table_reference>

Cross Join

A cross join is used to join each row of one table with each row of another table.
就是乘法

1
2
SELECT column_name, column_name, [, column_name]
FROM <table_reference> CROSS JOIN <table_reference>

Self Join

Compare the value of one column with that of another in the same table
In a self join a table is joined to itself. As a result, one row in a table correlates with other rows in the same table.

1
2
3
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Subqueries

IN

1
2
3
4
SELECT column, column [, column]
FROM table_name
WHERE column IN | NOT IN
    ( SELECT column FROM table_name [ WHERE where_condition] )

EXISTS

1
2
3
4
SELECT column, column [, column]
FROM table_name
WHERE column EXISTS | NOT EXISTS
    ( SELECT * FROM table_name [ WHERE where_condition] )

1、exists是对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；
2、in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。
3、如果用not in ，则是内外表都全表扫描，无索引，效率低，可考虑使用not exists，也可使用A left join B on A.id=B.id where B.id is null 进行优化。

Using Modified Comparison Operators

1
2
3
4
SELECT column, column [, column]
FROM table_name
WHERE column > | < | = ALL | ANY
    ( SELECT column FROM table_name [ WHERE where_condition] )

The ALL keyword returns a TRUE value if the comparison performed is true for all the values that are retrieved by the subquery. It return FALSE if only some of the values satisfy the comparison operator or if the subquery does not return any rows to the outer statement
The ANY keyword returns a TRUE value if any value retrieved by the subquery satisfies the comparison operator. It returns a FALSE value if no values in the subquery satisifies the comparison operator or if the subquery does not return any row that matches the outer statement.

Using Nested Subqueries

You may need to use one or more subqueries within another subquery, known as a nested subquery. In the nested subquery, the condition specified in a subquery depends on the result of another subquery, which, in turns depends on the result of some another subquery.

Using Correlated Subquery

相关子查询

1.扫描外查询的第一条记录
2.扫描子查询，并将第一条记录的对应值传给子查询，由此计算出子查询的结果
3.根据子查询的结果，返回外查询的结果。
4.重复上述动作，开始扫描外查询的第二条记录，第三条记录，直至全部扫描完毕

You may want to use an inner query that depends on the outer query for its execution.
In a normal nested subquery, the inner query executes only once. The main query is executed by using the value returned by the inner query.
On the other hand, in a correlated subquery, the inner query is driven by the outer query. In this case, the inner query executes once for each row seleted by the outer query.

Implementing Indexes and Views

Creating and Managing Indexes

Identifying the Types of Indexes

MySQL allows you to create indexes on the tables to speed up data access. Indexes should be created on the columns that are most frequently included in the conditions, such as the columns included in the WHERE or HAVING clauses of SELECT statements. Therefore, you can create appropriate indexes on the required columns to improve the response time of the MySQL server.

MySQL supports the following types of indexes:

  • Primary key
  • Foreign key
  • Unique
  • Regular
  • Full-text

Full-text index is defined on the column(s) that can accept string values. The purpose of this index is to improve the speed of the searches made on the string values included in the column data. The data type of the column(s) on which the full-text index can be defined can be CHAR, VARCHAR, or TEXT. Moreover, these column(s) can accept deplicate or NULL values.
The full-text indexes can be defined only on the tables that are accessed by using the MyISAM storage engine.

Creating Indexes

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE <table_name> (
...
{INDEX | KEY} [<index_name>] (<column_name> [{, <column_name>}...])
FULLTEXT {INDEX | KEY} [<index_name>] (<column_name> [{, <column_name>}...])
)


ALTER TALBE <table_name>
ADD {INDEX | KEY} [<index_name>] (<column_name> [{, <column_name>}...])

CREAR UNIQUE INDEX <index_name> ON <table_name> (<column_name> [{, <column_name>}...])
CREAR INDEX <index_name> ON <table_name> (<column_name> [{, <column_name>}...])
CREAR FULLTEXT INDEX <index_name> ON <table_name> (<column_name> [{, <column_name>}...])

Viewing Indexes

1
SHOW INDEXES FROM table_name [FROM database_name]

Removing Indexes

1
2
3
4
5
6
ALTER TABLE <table_name>
DROP INDEX <index_name>

or 

DROP INDEX <index_name> ON <table_name>

Creating and Managing Views

A view is a database object that stores a query raised on one or more tables or other views. The query that contains joins, calculated columns, and subqueries is generally complex.
Views help in simplifying the execution of complex queries.

Creating Views

A view is a virtual table that provides access to subset of columns or records from one or more tables or views. In addition to the columns of tables or other views, a view definition can also contain calculated columns using aggregate functions. View is a query strored as an object in the database and does not have its own data. A view can derived are known as the base tables or underlying tables. A database developer can create views to ensure the security of data by restricting access to

  • Specific records of a table.
  • Specific columns of a table.
  • Specific records and columns of a table.
  • Records fetches by using joins.
  • Subsets of other views or a subset of views and tables.
    In addition to restricting access, views can also be used to create and save queries based on multiple tables.
    1
    2
    3
    CREATE [OR REPLACE]
    VIEW view_name [(<column_name> [, <column_name>]...)]
    AS <select_statment>

Querying Views

1
SELECT <select_statment> FROM <view_name> [WHERE <where_condition>]

View 可以包含 WHERE, ORDER BY, GROUP BY

Restrictions on Views

Some of these restriction are

  • A view cannot be created on a temporary table.
  • A temporary view cannot be created.
  • A trigger cannot be created on a view.
  • An index cannot be created on a view.
  • The underlying table(s) or view(s) of view must already exist.
  • The SELECT statement in a view definition cannot contain subqueries in the FROM clause.

Updatable Views

The views are said to be non-updatable if the following constructs are contained in their defubutuib:

  • An aggregate function in the column list.
  • The DISTINCT, GROUP BY, HAVING clause in the SELECT statement.
  • A subquery in the SELECT statement.
  • A nonupdatable view in the FROM clause.

Insert operations can be performed only on the views that contain all the NOT NULL columns from the underlying table(s) and do not contain any calculated column in the column list.

Update operation cannot bt performed on a view when

  • It modifies the values of the derived columns.
  • It results in modification of multiple tables.

MySQL allows you to perform the dalate operations only on those views that contain columns from a single table.

Managing Views

1
2
3
4
5
6
ALTER [ALFORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS <select_statment>
[WITH CHECK OPTION]

DROP VIEW [IF EXISTS] view_name [, view_name]

Implementing Compound Statements and Stored Routines

实现复合语句和储存过程

Creating a Compound Statement

复合语句

1
2
3
[begin_label:] BEGIN
    [statement_list]
END [end_label]

变量

declare

1
DECLARE var_name [, var_name] type [default values]

set

1
SET variable_assignment [, variable_assignment]

声明与初始化

1
2
3
4
5
6
7
# Local Variable 局部变量声明必须在begin end之间
DECLARE var int;
SET var=15;

# Gloabal Variable 全局变量可以直接声明
declare @var int;
set @var=15, @age=19;

函数

1
2
3
4
5
6
7
8
9
delimiter //
create function f(t int) returns int
begin
    declare age int;
    set age=t;
    return t;
end
//
delimiter ;

好像函数必须要有返回值…

流程控制语句

IF-THEN-ELSE

1
2
3
4
5
6
7
8
9
10
11
12
IF condition THEN
statements;
[ELSE IF condition THEN
statements;]
[ELSE
statements;]
END IF;
    
IF a>b THEN SET s=CONCAT(a, '>', b);
ELSEIF a=b THEN SET s=CONCAT(a, '=', b);
else set s=CONCAT(a, '<', b);
END IF;

如果是elseif只需要一个end if; 如果是else if则需要两个

CASE

1
2
3
4
5
6
7
8
CASE expression
WHEN value1 THEN
statements;
[WHEN value2 THEN
statements;]
[ELSE
statements;]
END CASE;

WHILE

1
2
3
[loopname:] WHILE condition DO
statements;
END WHILE [loopname];

LOOP

1
2
3
[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LEAVE lable 可以跳出LOOP, REPEAT, WHILE

ITERATE statement

相当于continue

1
ITERATE lable

REPEAT statement

1
2
3
4
[begin_label:] REPEAT
    statement_list
UNTIL condition
END REPEAT [end_label]

Handling Exceptions

1
2
3
4
5
6
7
8
9
10
DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action:
    CONTINUE | EXIT | UNDO

condition_value:
    mysql_error_code | SQLSTATE [VALUE] sqlstate_value
    | SQLWARNING | NOT FOUND | SQLEXCEPTION

异常数值文档
https://dev.mysql.com/doc/refman/5.6/en/server-error-reference.html

返回异常

1
2
3
4
SIGNAL SQLSTATE [VALUE] sqlstate_value
[SET MESSAGE_TEXT=string_or_variable]

use '45000', which means “unhandled user-defined exception.”

Using Cursors

游标
In MySQL cursors can be used inside stored procedures, triggers, and functions. Folloewing steps:

  • Declare a cursor 声明
  • Open the cursor 打开
  • Fetch the cursor 获取
  • Close the curson 关闭
1
2
3
4
DECLARE cursor_name CURSOR FOR select_statment
OPEN cursor_name
FETCH cursor_name INTO var_name [, var_name] ...;
CLOSE cursor_name

Implementing Stored Routines

Procedures

1
2
3
4
CREATE PROCEDURE proc_name ([[IN|OUT|INOUT]pro_parameter [, ...]])
    [characteristic ...] routine_body

CALL proc_name([parameter[,...]])

IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT:该值可在存储过程内部被改变，并可返回
INOUT:调用时指定，并且可被改变和返回

Function

1
2
3
4
CREATE FUNCTION func_name ([func_parameter[,..]]) RETURNS type
    [characteristic ..] routine_body

SELECT func_name();

Implementing Triggers and Transactions

Triggers

A triggers can be considered as small program that is pre-compiled and stored in the database. It is automatically executed when a DML statement, such as UPDATE, DELETE, or INSERT is performed on the corresponding table.

1
2
3
4
5
6
7
8
9
CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER <trigger_name> <trigger_time> <trigger_event>
    ON <table_name> FOR EACH ROW <trigger_body>

trigger_time:
    AFER | BEFORE
trigger_event:
    INSERT | UPDATE | DELETE

Restrictions on Triggers

  • Any foreign key action does not activate triggers.
  • The CALL statement is not permitted inside the trigger body.
  • A TEMPORARY table or a view cannot be associated with a trigger.
  • The RETURN statement is not permitted in trigger.
  • The mysql database does not permit any trigger creation on its tables.
  • Statement related to transactions such as COMMIT, ROLLBACK, and START TRANSACTION cannot be executed inside a trigger.
  • The trigger cache does not update itself dynamically if any changes are made to the structure of a database object after the information about its structure has been loaded in the cache.

用NEW | OLD来区分， DELETE 里只有 OLD.

1
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

Transactions

A transaction stands for a series of data manipulation statments encapsulated as a single logical unit of work. This single locical unit of work is executed in a specific sequence and its completion depends on the successful execution of each individual statement within it. If an individual statement fails, the entire logical unit of work will fail.
In an RDBMS, whenever an operation is performed, it can be treaded as a reansaction if it has ACID properties. These properties of a transaction can be explained as:

  • Atomicity: This means that either all the data-rekated operations in a transaction atr performed or none of them is performed.
  • Consistency: This means that all the data is in a consistent state after a transaction is completed successfully.
  • Isolation: This delermines whether or not any changes in data by an operation atr made visible to a concurrent transaction.

  • Durability: This states that any change in data by a completed transaction remains permanently in effect in the system.

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
1
2
3
4
5
6
7
8
9
10
11
12
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
SET sql_safe_updates=0

START TRANSACTION 显式地开启一个事务；
COMMIT 提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO SAVAPOINT identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别
SHOW VARIABLES LIKE 'tx_isolation' 查看当前数据库的事务隔离级别

事务的并发问题
1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

MySQL事务隔离级别

事务隔离级别 脏读 不可重复读 幻读
读未提交(READ UNCOMMITTED) 是 是 是
不可重复读(READ COMMITED) 否 是 是
可重复读(REPEATABLE READ) 否 否 是
串行化(SERIALIZABLE) 否 否 否

Mysql 支持4种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
a).read uncommitted(读未提交数据)：允许事务读取未被其他事物提交的变更。脏读、不可重复读和幻读的问题都会出现
b).read commited(读已提交数据)：只允许事务读取已经被其他事务提交的变更。可避免脏读，但不可重复读和幻读问题仍然存在
c).repeatable read(可重复读)：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。可避免脏读和不可重复读，但幻读的问题仍然存在
d).serializable(串行化)：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行增删改操作。所有的并发问题都可以避免，但性能什么低下。

READ UNCOMMITTED

The READ-UNCOMMITTED isolation level specifies that the transactions are able to see the data changes made by other transactions that atr not yet committed.

READ COMMITED

The READ-COMMITTED isolation level specifies that the data changes are visible to other transactions only when thay are committed.

REPEATABLE READ

The REPEATABLE-READ isolation level specifies that every read in the transaction will return the same set of data.

SERIALIZABLE

The SERIALIZABLE isolation level specifies that transactions place locks on all accessed records and the resource so that records cannot be added, updated, or deleted fron the table used in the transaction.

1
2
3
SET [GOLBAL | SESSION] TRANSACTION ISOLATION LEVEL {
    READ UNCOMMITTED | READ COMMITED | REPEATABLE READ | SERIALIZABLE
}

Lock

MyISAM

1
2
3
4
5
6
7
LOCK {TABLE | TABLES}
<table_name> [AS <alias>] {READ [LOCAL] | [LOW_PRIORITY] WRITE} 
[{, <table_name> [AS <alias>] {READ [LOCAL] | [LOW_PRIORITY] WRITE} }...]

UNLOCK {TABLE | TABLES}

SHOW OPEN TABLES;

导出和导入数据

导出数据

1
2
3
4
5
6
7
8
9
select * from <tablename> into OUTFILE `<filename>` [<export_option>]
<export_option>::=
{ FIELDS
[TERMINATED BY `<value>`]  # 列结束
[[OPTIONALLY] ENCLOSED BY `<value>`]   # 开始和结束
[ESCAPED BY `<value>`] }  
| { LINES
[STARTING BY `<value>`]     # 行起始
TERMINATED BY `<value>`] }  # 行结束

貌似高版本无法导出文件，需要在MySQL安装路径里找my.ini，然后在[mysqld]后面加上一句，就能导出了

1
2
3
4
5
6
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306

secure_file_priv = ''

导出csv

1
2
3
4
select * from customertable
into outfile 'F:\\Code\\mysql\\a.csv'
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\r\n';

导出图片

1
2
select [select options] into dumpfile `<filename>`;
select img from commoditytable where id='qiezibao' into dumpfile 'F:\\Code\\mysql\\a.jpg';

导入数据

1
2
3
4
5
6
7
8
9
10
LOAD DATA INFILE `<filename>` into table <tablename> [<import_option>] [IGNORE <number> LINES] [(<column name>[{, <column name>}...])]

<import_option>::=
{ FIELDS | COLUMNS
[TERMINATED BY `<value>`]  # 列结束
[[OPTIONALLY] ENCLOSED BY `<value>`]   # 开始和结束
[ESCAPED BY `<value>`] }  
| { LINES
[STARTING BY `<value>`]     # 行起始
TERMINATED BY `<value>`] }  # 行结束

导入csv

1
2
3
load data infile 'F:\\Code\\mysql\\a.csv' into table customertable
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\r\n';

Administering MySQL Databases

用户管理

创建用户

用sql语句有两种方法
一种create new user

1
2
create user 'joe' identified by 'password_joe';
create user 'joe'@'localhost' identified by 'password_joe';

一种insert into mysql.user

1
insert into mysql.user values('%', 'beiyu', PASSWORD('beiyu'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, '', '');

用flush privileges;刷新权限

hosts: localost = %

修改用户名

1
rename user <old_user_name> to <new_user_name>;

修改用户密码

两种方式，set password 和 update

1
2
3
4
set password=password('1234'); # 修改当前账户密码
set password for <account_name> = {
    password('password_1234') | 'encrypted_password'
}
1
update mysql.user set password=password('1234') where user  'beiyu';

删除用户

两种方式，drop user 和 delete

1
2
drop user account_name;
delete from user where user='beiyu';

用户权限管理

给予权限

1
2
3
4
grant <privilege_type> [(<col_name> [, <col_name>...])] [, <privilege_type> [(<col_name> [, <col_name>...])] ...]
    on privilege_level 
    to account_name [identified by [password] 'password']
    [, account_name [identified by [password] 'password']...]

privilege_type:

  • all
  • alter
  • create
  • create routine
  • create user
  • create view
  • delete
  • drop
  • select
  • update

privilege_level:

  • *.*
  • *
  • database_name.*
  • table_name

查看权限

1
SHOW GRANT FOR account_name

撤销权限

1
2
3
4
5
REVOKE <privilege_type> [(<column_name>) [, <column_name>...]]
[,{<privilege_type> [(<column_name>) [, <column_name>...]]}...]
ON privilege_level
FROM account_name [IDENTIFED BY [PASSWORD] `<password>`]
[,account_name [IDENTIFED BY [PASSWORD] `<password>`] ...]

备份

Back up

1
2
3
4
5
6
mysqld --log-bin [=<base_name>]
mysqldump [--flush-logs] -u <user_name> -p <database_name>
mysqldump [--flush-logs] -u <user_name> -p <database_name> > <path_and_filename>
mysqldump [--flush-logs] -u <user_name> -p <database_name> [<table_name>, [<table_name>]...] > <path_and_filename>
mysqldump [--flush-logs] -u <user_name> -p --database <database_name> [<database_name>...] > <path_and_filename>
mysqldump --all-databases > <path_and_filename>

Recover

1
mysql -u <user_name> -p [<database_name>] < <path_and_filename>

Updating the Reloaded Databases Using Binary Logs

1
mysqlbinlog "<Path>\<binary_log_file>" | mysql -u <user_name> -p

Using Text File with Exported Binary Log Data

1
mysqlbinlog "<Path>\<binary_log_file>" > "<Path>\<text_file>"

参考

Related Posts
Have fun.