MySQL 基础入门

概念

• 数据库（database）：保存有组织的数据的容器（通常是一个文件或一组文件）。
• DBMS：数据库软件，数据库管理系统。
• 表（table）：某种特定类型数据的结构化清单。
• 模式（schema）：关于数据库和表的布局及特性的信息。
• 列（column）：表中的一个字段。所有表都是由一个或多个列组成的。
• 数据类型（datatype）：所容许的数据的类型，每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。
• 行（row）：表中的一个记录。
• 主键（primary key）：一列（或一组列），其值能够唯一区分表中每一行。
  主键应该满足：
  • 任意两行都不具有相同的主键值。
  • 每个行都必须具有一个主键值（主键值不允许 NULL 值）。
• 外键（foreign key）：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

使用 MySQL

• SHOW DATABASES：返回可用数据库的一个列表。
• USE xxx：选择数据库。
• SHOW TABLES：获得一个数据库内的表的列表。
• SHOW COLUMNS from xxx：显示表列。
• DESCRIBE xxx：可以理解为 SHOW COLUMNS from xxx 的快捷方式。
• SHOW STATUS：显示服务器状态信息。
• SHOW CREATE DATABASE <name>：显示创建特定数据库的 MySQL 语句。
• SHOW CREATE TABLE <name>：显示创建特定表的语句。
• SHOW GRANTS：显示授予用户（所有用户或特定用户）的安全权限。
• SHOW ERRORS 和 SHOW WARNINGS：用来显示服务器错误或警告信息。
• help show; ：显示允许的 show 语句。

检索数据

• SELECT xxx(, xxx, ..., xxx) FROM xxx
• SELECT * FROM xxx
• SELECT DISTINCT xxx FROM xxx：指示 MySQL 只返回不同的值，关键字应用于所有列而不仅是前置它的列。
• SELECT xxx FROM xxx LIMIT x：为了返回第一行或前几行，可使用 LIMIT 子句。
• LIMIT 5,5：可以指定要检索的开始行和行数，例子中从行 5 开始的 5 行，第一个数为开始位置，第二个数为要检索的行数。
• LIMIT 4 OFFSET 3：意为从行 3 开始取 4 行，等价于 LIMIT 3, 4 。
• LIMIT 1, 1：检索出来的第一行为行 0 而不是行 1，例子中将检索出第二行而不是第一行。
• 多条 SQL 语句必须以分号（;）风格；如果使用的是 MySQL 命令行，必须加上分号来结束 SQL 语句。

使用别名

别名是一个字段或值的替换名。别名用 AS 关键字赋予。

排序检索数据

• ORDER BY 子句取一个或多个列的名字，据此对输出进行排序。

  SELECT prod_name(, xxx, ..., xxx)
  FROM products
  ORDER BY prod_name;

• 为了进行降序排序，必须指定 DESC 关键字，DESC 关键字只应用到直接位于其前面的列名，如果想在多个列上进行降序排序，必须对每个列指定 DESC 关键字。升序的关键字是 ASC，升序是默认的。

  SELECT prod_id, prod_price, prod_name
  FROM products
  ORDER BY prod_price DESC, prod_name;

• 给出 ORDER BY 子句时，应该保证它位于 FROM 子句之后，如果使用 LIMIT ，它必须位于 ORDER BY 之后。

过滤数据

WHERE

在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤，WHERE 子句在 FROM 子句之后给出，在执行匹配时默认不区分大小写。

SELECT xxx, xxx FROM xxx WHERE xxx = xxx

在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后。

范围值检查

为了检查某个范围的值，可使用 BETWEEN 和 AND 操作符。

空值检查

SELECT xxx FROM xxx WHERE xxx IS NULL：检查具有 NULL 值的列。

操作符

操作符：用来联结或改变 WHERE 子句中的子句的关键字，也称为逻辑操作符。

• AND：用在 WHERE 子句中的关键字，用来指示检索满足所有给定条件的行。

• OR：指示 MySQL 检索匹配任一条件的行。

• SQL 在处理 OR 操作符前，优先处理 AND 操作符，所以尽量使用圆括号明确地分组相应的操作符。

• IN：指定条件范围，范围中的每个条件都可以进行匹配，IN 取合法值的由逗号分隔的清单，全都括在圆括号中，与 OR 完成相同的功能。

  SELECT prod_name, prod_price
  FROM products
  WHERE vend_id IN (1002, 1003)
  ORDER BY prod_name;

  等价于

  SELECT prod_name, prod_price
  FROM products
  WHERE vend_id = 1002 OR vend_id = 1003
  ORDER BY prod_name;

• NOT：否定它之后所有的任何条件。

  SELECT prod_name, prod_price
  FROM products
  WHERE vend_id NOT IN (1002, 1003)
  ORDER BY prod_name;

通配符进行过滤

为在搜索子句中使用通配符，必须使用 LIKE 操作符

• 最常使用的通配符是百分号（%），% 表示任何字符出现任意次数，百分号不能匹配 NULL

  SELECT prod_id, prod_name
  FROM products
  WHERE prod_name LIKE 'jet%';

• _：下划线匹配单个字符而不是多个字符

使用正则表达式

• REGEXP 后所跟的东西作为正则表达式处理

  SELECT prod_name
  FROM products
  WHERE prod_name REGEXP '1000'
  ORDER BY prod_name

• . 是正则表达式语言中一个特殊的字符，它表示匹配任意一个字符。

• | 为正则表达式的 OR 操作符，它表示匹配其中之一。

  SELECT prod_name
  FROM products
  WHERE prod_name REGEXP '1000|2000'
  ORDER BY prod_name

• [] 用来匹配特定的字符，[123] 定义匹配 1 或 2 或 3。

• - 用来定义一个范围，如 [0-9]

• 为了匹配特殊字符，必须使用 \\ 为前导。\\- 表示查找 -，\\. 表示查找 . 。

LIKE 与 REGEXP

LIKE 匹配整个列，如果被匹配的文本在列值中出现，LIKE 将不会找到它，相应的行业不被返回（除非使用通配符）。而 REGEXP 在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP 将会找到它，相应的行将被返回。

重复元字符

元字符	说明
*	0 个或多个匹配
+	1 个或多个匹配
？	0 个或多个匹配（等于 {1, }）
{n}	指定数目的匹配
{n, }	不少于指定数目的匹配
{n, m}	匹配数目的范围（m 不超过 255）

SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name

正则表达式 \\( 匹配 ( ，[0-9] 匹配任意数字，sticks? 匹配 stick 和 sticks（s 后的 ? 使 s 可选，因为 ? 匹配它前面的任何字符的 0 次或 1 次出现），\\) 匹配 )。

定位符

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name

^ 匹配串的开始，因此，^[0-9\\.] 只在 . 或任意数字为串中第一个字符时才开始匹配它们。

计算字段

计算字段是运行时在 SELECT 语句内创建的。

SELECT Concat(vend_name, ' (', vend_country, ')')
FROM vendors
ORDER BY vend_name;

+--------------------------------------------+
| Concat(vend_name, ' (', vend_country, ')') |
+--------------------------------------------+
| ACME (USA)                                 |
| Anvils R Us (USA)                          |
| Furball Inc. (USA)                         |
| Jet Set (England)                          |
| Jouets Et Ours (France)                    |
| LT Supplies (USA)                          |
+--------------------------------------------+
6 rows in set (0.00 sec)

Concat() 拼接串，即把多个串连接起来形成一个较长的串。Concat() 需要一个或多个指定的串，各个串之间用逗号分隔。

函数

文本处理函数

• RTrim()：去掉串右边的空格；LTrim()：去掉左边的空格；Trim()：去掉串左右两边的空格
• Upper()：将文本转换为大写

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个字串
Lower()	将串转换为小写
Right()	返回串右边的字符
Soundex()	返回串的 SOUNDEX 值
SubString()	返回字串的字符

SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX 能对串进行发音比较而不是字母比较。

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');

+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+
1 row in set (0.00 sec)

日期处理函数

函数	说明
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
Day()	返回日期时间的天数部分
Time()	返回日期时间的时间部分
DateDiff()	计算两个日期之差
DayOfWeek()	对于一个日期，返回对应的星期几

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'

等价于

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

• AVG() 用来确定特定列或行的平均值，为了获得多个列的平均值，必须使用多个 AVG() 函数，AVG() 函数忽略列值为 NULL 的行。

• 如果指定列名，则指定列的值为空的行被 count() 函数忽略，但如果 count() 函数中用的是星号(*) ，则不忽略。

• 聚集不同值：使用 AVG() 函数返回特定供应商提供的产品的平均价格，但使用了 DISTINCT 参数，因此平均值只考虑各个不同的价格。

  SELECT AVG(prod_price) AS avg_price
  FROM products
  WHERE vend_id = 1003;
  
  +-----------+
  | avg_price |
  +-----------+
  | 13.212857 |
  +-----------+
  1 row in set (0.00 sec)

  SELECT AVG(DISTINCT prod_price) AS avg_price
  FROM products
  WHERE vend_id = 1003;
  
  +-----------+
  | avg_price |
  +-----------+
  | 15.998000 |
  +-----------+
  1 row in set (0.00 sec)

数据分组

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set (0.00 sec)

• GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套， 为数据分组提供更细致的控制。

• 如果在 GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算 （所以不能从个别的列取回数据）。

• GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式 （但不能是聚集函数）。如果在 SELECT 中使用表达式， 则必须在GROUP BY 子句中指定相同的表达式。不能使用别名。

• 除聚集计算语句外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。

• 如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行NULL 值，它们将分为一组。

• GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

• 使用 WITH ROLLUP 关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。

  SELECT vend_id, COUNT(*) AS num_prods
  FROM products
  GROUP BY vend_id WITH ROLLUP;
  
  +---------+-----------+
  | vend_id | num_prods |
  +---------+-----------+
  |    1001 |         3 |
  |    1002 |         2 |
  |    1003 |         7 |
  |    1005 |         2 |
  |    NULL |        14 |
  +---------+-----------+
  5 rows in set (0.00 sec)

过滤分组

• WHERE 过滤指定的是行而不是分组，HAVING 过滤分组。
• WHERE 是一个约束声明，使用 WHERE 约束来自数据库的数据，WHERE 是在结果返回之前起作用，WHERE 中不能使用聚合函数。
• HAVING 是一个过滤声明，是在查询返回结果集之后对查询结果进行的过滤操作，在 HAVING 中可以使用聚合函数。

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
2 rows in set (0.00 sec)

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

分组和排序

ORDER BY	GROUP BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

SELECT 子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

子查询

子查询，即嵌套在其他查询中的查询。

SELECT cust_id
FROM orders
WHERE order_num IN
(SELECT order_num
 FROM orderitems
 WHERE prod_id = 'TNT2');

相关子查询，即涉及外部查询的子查询。任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。

联结

联结是一种机制，用来在一条 SELECT 语句中关联表

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors, vend_id = products.vend_id
ORDER BY vend_name, prod_name;

内部联结

目前为止所用的联结为 等值联结 ，它基于两个表之间的相等测试，这种联结也称为 内部联结 。

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

高级联结

使用表别名

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';

表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机。

自联结

使用表别名的主要原因之一是能在单条 SELECT 语句中不止一次引用相同的表。

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';

+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.01 sec)

等价于

SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                FROM products
                WHERE prod_id = 'DTNTR');

+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.00 sec)

自然联结

自然连接（Natural join）是一种特殊的等值连接，要求两个关系表中进行比较的属性组必须是名称相同的属性组，并且在结果中把重复的属性列去掉（即：留下名称相同的属性组中的其中一组）。自然连接的结果会有数据丢失，这些丢失的数据就是那些没有匹配的数据。

select * from A natural join B

Aname	Bid	salary
Getz	1	3000
Davis	2	1500
King	2	2200
Davis	3	5000
Jane	5	4000

Bid	Bname
1	Sales
2	Marketing
3	Accounts
4	Administration

联结结果为：

Aname	Bid	salary	Bname
Getz	1	3000	Sales
Davis	2	1500	Marketing
King	2	2200	Marketing
Davis	3	5000	Accounts

外部联结

• 外部联结包含了那些在相关表中没有关联行的行。
• 在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）。
• 左联结：返回包括左表中的所有记录和右表中联结字段相等的记录。
• 右联结：返回包括右表中的所有记录和左表中联结字段相等的记录。

先给出一个内部联结，检索所有客户及其订单：

SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.00 sec)

转换成外部联结，检索所有客户，包括没有订单的客户：

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.00 sec)

右联结：

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON orders.cust_id = customers.cust_id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.00 sec)

组合查询

MySQL 允许执行多个查询（多个 SELECT 语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询。

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+
8 rows in set (0.00 sec)

在使用 UNION 时，重复的行被自动取消。如果想返回所有匹配行，可使用 UNION ALL 而不是 UNION 。

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+
9 rows in set (0.00 sec)

在使用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须出现在最后一条 SELECT 语句之后。

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002)
ORDER BY vend_id, prod_price;

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
|    1003 | FC      |       2.50 |
|    1003 | TNT1    |       2.50 |
|    1003 | SLING   |       4.49 |
+---------+---------+------------+
8 rows in set (0.00 sec)

规则

• UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔（因此，如果组合 4 条 SELECT 语句，将要使用 3 个 UNION 关键字）。
• UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

数据修改

插入数据

INSERT INTO <table_name>
VALUES (...);

INSERT INTO <table_name>(...)
VALUES (...);

第二种方式在表名后的括号里明确地给出了列名，VALUES 必须以其指定的次序匹配指定的列名，不一定按各个列出现在实际表中的次序。

如果数据检索是最重要的，可以通过在 INSERT 和 INTO 之间添加关键字 LOW_PRIORITY ，指示 MySQL 降低 INSERT 语句的优先级。

更新数据

UPDATE customers
SET cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

为了即使发生错误，也能继续进行更新，可使用 IGNORE 关键字。UPDATE IGNORE customers... 。

为了删除某个列的值，可设置它为 NULL（假定表定义允许 NULL 值）。

UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;

删除数据

DELETE FROM customers
WHERE cust_id = 10006;

DELETE 不需要列名或通配符。DELETE 删除整行而不是删除列。为了删除指定的列，请使用 UPDATE 语句。

DELETE 语句从表中删除行，甚至是删除表中所有行。但是，DELETE 不删除表本身。

如果想从表中删除所有行，不要使用 DELETE 。可使用 TRUNCATE TABLE 语句，它完成相同的工作，但速度更快。

更新和删除的指导原则

• 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。
• 保证每个表都有主键，尽可能像 WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。
• 在对 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。
• 使用强制实施引用完整性的数据库，这样 MySQL 将不允许删除具有与其他表相关联的数据的行。

创建和操纵表

CREATE TABLE xxx
(
  xxx int NOT NULL AUTO_INCREMENT,
  xxx char(50) NOT NULL DEFAULT xxx,
  ...
  PRIMARY KEY (xxx)
) ENGINE = InnoDB;

• 利用 CREATE TABLE 创建表。新表的名字在关键字 CREATE TABLE 之后给出，表列的名字和定义，用逗号分隔。

• 如果你仅想在一个表不存在时创建它，应该在表名后给出 IF NOT EXISTS 。

• AUTO_INCREMENT 告诉 MySQL 本列每当增加一行时自动增量。

• 每个表只允许一个 AUTO_INCREMENT 列，而且它必须被索引。

• 默认值用 CREATE TABLE 语句的列定义中的 DEFAULT 关键字指定。

  CREATE TABLE orderitems
  (
    order_num int  NOT NULL,
    order_item int NOT NULL,
    prod_id   char(10) NOT NULL,
    quantity   int NOT NULL  DEFAULT 1,
    PRIMARY KEY (order_num, order_item)
  ) ENGINE=InnoDB;

引擎类型

• InnoDB 是一个可靠的事务处理引擎，它不支持全文本搜索
• MEMORY 在功能等同于 MyISAM，但由于数据存储在内存（而不是磁盘）中，速度很快（特别适合于临时表）
• MyISAM 是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理

更新表

添加一个列

ALTER TABLE vendors
ADD vend_phone CHAR(20);

删除一个列

ALTER TABLE vendors
DROP COLUMN vend_phone;

定义外键

ALTER TABLE <表名>
ADD CONSTRAINT <FK_ID>
FOREIGN KEY (外键字段名)
REFERENCES <外表表名> (主键字段名);

删除表

DROP TABLE customers2;

重命名表

RENAME TABLE customers TO customers;

视图

视图是虚拟的表。视图只包含使用时动态检索数据的查询，不包含表中应该有的任何列或数据，它包含的是一个 SQL 查询。

视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

• 视图用 CREATE VIEW 语句来创建。
• 使用 SHOW CREATE VIEW viewname; 来查看创建视图的语句。
• 用 DROP 删除视图，其语法为 DROP VIEW viewname; 。
• 更新视图时，可以先用 DROP 再用 CREATE ，也可以直接用 CREATE OR REPLACE VIEW 。如果要更新的视图不存在，则第 2 条更新语句会创建一个视图；如果要更新的视图存在， 则第 2 条更新语句会替换原有视图。
• 视图是可更新的，更新一个视图将更新其基表。如果你对视图增加或删除行，实际是对其基表增加或删除行。

CREATE VIEW <view_name> AS
SELECT ...
FROM ...
WHERE ...

存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条 MySQL 语句的集合。

调用

MySQL 称存储过程的执行为调用，因此 MySQL 执行存储过程的语句为 CALL 。CALL 接受存储过程的名字已经需要传递给它的任意参数。

CALL productpricing
(@pricelow, @pricehigh, @priceaverage);

创建

CREATE PROCEDURE productpricing()
BEGIN
   SELECT Avg(prod_price) AS priceaverage
   FROM products;
END;

• 如果存储过程接受参数，它们将在 () 中列举出来。此存储过程没有参数，但后跟的 () 仍然需要。
• BEGIN 和 END 用来限定存储过程体

MySQL 命令行程序中的分隔符

如果你使用的是 MySQL 命令行实用程序，应该仔细阅读此说明。

默认的 MySQL 语句分隔符为 ;（正如你已经在迄今为止所使用的 MySQL 语句中所看到的那样）。MySQL 命令行实用程序也使用 ; 作为语句分隔符。如果命令行实用程序要解释存储过程自身内的 ; 字符，则它们最终不会成为存储过程的成分，这会使存储过程中的 SQL 出现句法错误。

解决办法是临时更改命令行实用程序的语句分隔符，如下所示：

DELIMITER //

CREATE PROCEDURE productpricing()
BEGIN
   SELECT Avg(prod_price) AS priceaverage
   FROM products;
END //

DELIMITER ;

其中，DELIMITER // 告诉命令行实用程序使用 // 作为新的语句结束分隔符，可以看到标志存储过程结束的 END 定义为 END // 而不是 END; 。这样，存储过程体内的 ; 仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用 DELIMITER ; 。除 \ 符号外，任何字符都可以用作语句分隔符。

删除

DROP PROCEDURE productpricing;

DROP PROCEDURE IF EXISTS productpricing;

使用变量

一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

CREATE PROCEDURE productpricing(
   OUT pl DECIMAL(8,2),
   OUT ph DECIMAL(8,2),
   OUT pa DECIMAL(8,2)
)
BEGIN
   SELECT Min(prod_price)
   INTO pl
   FROM products;
   SELECT Max(prod_price)
   INTO ph
   FROM products;
   SELECT Avg(prod_price)
   INTO pa
   FROM products;
END;

• 关键字 OUT 指出相应的参数用来从存储过程传出一个值（返回给调用者）。

• MySQL 支持 IN （传递给存储过程）、OUT（从存储过程传出，如这里所用）和 INOUT（对存储过程传入和传出）类型的参数。

• 调用存储过程

  CALL productpricing(@pricelow,
                      @pricehigh,
                      @priceaverage);

  这条语句并不显示任何数据。它返回以后可以显示（或在其他处理中使用）的变量。

• 所有 MySQL 变量都必须以 @ 开始

  SELECT @priceaverage;

  显示检索出的产品平均价格。

智能存储过程

-- Name: ordertotal
-- Parameters: onumber = order number
--          ...

CREATE PROCEDURE ordertotal(
   ...
) COMMENT 'Obtain order total, ...'
BEGIN
   -- Declare variable for total
   DECLARE total DECIMAL(8,2);
   ...
   ...
   IF taxable THEN
      ...
   END IF;

   ...
END;

• 注释：前面放置 --
• 用 DECLARE 语句定义了两个局部变量。DECLARE 要求指定变量名和数据类型，它也支持可选的默认值。
• 本例子中的存储过程在 CREATE PROCEDURE 语句中包含了一个 COMMENT 值。它不是必需的，但如果给出，将在 SHOW PROCEDURE STATUS 的结果中显示。

检查存储过程

为显示用来创建一个存储过程的 CREATE 语句，使用 SHOW CREATE PROCEDURE <name>; 语句。

为了获得详细信息的存储过程列表，使用 SHOW PROCEDURE STATUS 。

游标

游标（cursor） 是一个存储在 MySQL 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

MySQL 游标只能用于存储过程（和函数）。

创建

CREATE PROCEDURE processorders()
BEGIN
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;
END;

游标用 DECLARE 语句创建

打开和关闭游标

OPEN ordernumbers;

CLOSE ordernumbers;

如果你不明确关闭游标，MySQL 将会在到达 END 语句时自动关闭它。

使用游标数据

在一个游标被打开后，可以使用 FETCH 语句分别访问它的每一行。FETCH 指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条 FETCH 语句检索下一行（不重复读取同一行）。

CREATE PROCEDURE processorders()
BEGIN

  -- Declare local variables
  DECLARE o INT;

  -- Declare the cursor
  DECLARE ordernumbers CURSOR
  FOR
  SELECT order_num FROM orders;

  -- Open the cursor
  OPEN ordernumbers;

  -- Get order number
  FETCH ordernumbers INTO o;

  -- Close the cursor
  CLOSE ordernumbers;

END;

触发器

触发器是 MySQL 响应 DELETE 、INSERT 和 UPDATE 语句而自动执行的一条 MySQL 语句（或位于 BEGIN 和 END 语句之间的一组语句）。其他 MySQL 语句不支持触发器。

创建触发器

创建触发器需要给出四条信息：

• 唯一的触发器名
• 触发器关联的表
• 触发器应该响应的活动（DELETE 、INSERT 或 UPDATE）
• 触发器何时执行（处理之前或之后）

触发器用 CREATE TRIGGER 语句创建。

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

触发器可在一个操作发生之前或之后执行，这里给出了 AFTER INSERT ， 所以此触发器将在 INSERT 语句成功执行后执行。这个触发器还指定 FOR EACH ROW ，因此代码对每个插入行执行。在这个例子中，文本 Product added 将对每个插入的行显示一次。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持 6 个触发器（每条 INSERT 、UPDATE 和 DELETE 的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对 INSERT 和 UPDATE 操作执行的触发器，则应该定义两个触发器。

触发器失败

如果 BEFORE 触发器失败，则 MySQL 将不执行请求的操作。此外，如果 BEFORE 触发器或语句本身失败，MySQL 将不执行AFTER触发器（如果有的话）。

删除触发器

DROP TRIGGER newprduct;

使用触发器

INSERT 触发器

INSERT 触发器在 INSERT 语句执行之前或之后执行。

• 在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被插入的行
• 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新（允许更改被插入的值）
• 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含 0 ，在INSERT 执行之后包含新的自动生成值。

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

DELETE 触发器

DELETE 触发器在 DELETE 语句执行之前或之后执行。

• 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行
• OLD 中的值全都是只读的，不能更新

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
   INSERT INTO archive_orders(order_num, order_date, cust_id)
   VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

UPDATE 触发器

UPDATE 触发器在 UPDATE 语句执行之前或之后执行。

• 在 UPDATE 触发器代码中，你可以引用一个名为 OLD 的虚拟表访问以前（UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新更新的值
• 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新（允许更改将要用于UPDATE 语句中的值）
• OLD 中的值全都是只读的，不能更新

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

事务处理

事务处理用来维护数据库的完整性，它保证成批的 MySQL 操作要么完全执行，要么完全不执行。

控制事务处理

标志事务开始

START TRANSACTION

ROLLBACK

ROLLBACK 命令用来回退（撤销）MySQL 语句，只能在一个事务处理内使用

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotal;

事务处理用来管理 INSERT 、UPDATE 和 DELETE 语句。你不能回退 SELECT 语句。你不能回退 CREATE 或 DROP 操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

COMMIT

一般的 MySQL 语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit）， 即提交（写或保存）操作是自动进行的。

但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用 COMMIT 语句。

START TRANSACTION;
DELETE FROM ...
DELETE FROM ...
COMMIT;

最后的 COMMIT 语句仅在不出错时写出更改。如果第一条 DELETE 起作用，但第二条失败，则 DELETE 不会提交。

保留点

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。

这些占位符称为保留点。为了创建占位符，可使用 SAVEPOINT 语句。

SAVEPOINT delete1;

为了回退到本例给出的保留点，可如下进行：

ROLLBACK TO delete1;

安全管理

MySQL 用户账号和信息存储在名为 mysql 的 MySQL 数据库中。

USE mysql;
SELECT user FROM user;

创建用户账号

为了创建一个新的用户账号，使用 CREATE USER 语句。

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

在创建用户账号时不一定需要口令。

重新命名一个用户账号，使用 RENAME USER 语句。

RENAME USER ben TO bforta;

删除用户账号

为了删除一个用户账号（以及相关的权限），使用 DROP USER 语句。

DROP USER bforta;

设置访问权限

为了看到赋予用户账号的权限，使用 SHOW GRANTS FOR 。

SHOW GRANTS FOR bforta;

为了设置权限，使用 GRANT 语句。

GRANT SELECT ON crashcourse.* TO bforta;

GRANT 的反操作为 REVORK ，用它来撤销特定的权限。

REVOKE SELECT ON crashcourse.* FROM bforta;

更改口令

为了更改口令，可使用 SET PASSWORD 语句。

SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');

其他

数据库 GUI 客户端

1. Navicat
2. DataGrip
3. DBeaver
4. DBngin

Docker 下使用 MySQL

1. docker pull mysql
2. docker run -itd --name <name> -p 3306:3306 -e MYSQL_ROOT_PASSWORD=<pwd> mysql:<version>
3. docker exec -it <name> /bin/bash
4. mysql -u root -p

Homebrew 安装 MySQL

1. brew install mysql

2. 启动服务
   运行 brew service start mysql 可以后台启动 MySQL
   运行 mysql.server start 前台启动 MySQL（关闭控制台，服务停止，或者 mysql.server stop）

3. 初始化配置 mysql_secure_installation

   Securing the MySQL server deployment.
   
   Connecting to MySQL using a blank password.
   
   VALIDATE PASSWORD PLUGIN can be used to test passwords
   and improve security. It checks the strength of password
   and allows the users to set only those passwords which are
   secure enough. Would you like to setup VALIDATE PASSWORD plugin?
   
   // 这个选 yes 的话密码长度就必须要设置为8位以上
   Press y|Y for Yes, any other key for No: y
   
   There are three levels of password validation policy:
   
   LOW    Length >= 8
   MEDIUM Length >= 8, numeric, mixed case, and special characters
   STRONG Length >= 8, numeric, mixed case, special characters and dictionary
   // 这里提示选一个密码强度等级
   Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
   Please set the password for root here.
   // 然后按照所选的密码强度要求设定密码
   New password:
   
   Re-enter new password:
   
   Estimated strength of the password: 50
   Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
    ... Failed! Error: Your password does not satisfy the current policy requirements
   
   New password:
   
   Re-enter new password:
   
   Estimated strength of the password: 100
   Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
   By default, a MySQL installation has an anonymous user,
   allowing anyone to log into MySQL without having to have
   a user account created for them. This is intended only for
   testing, and to make the installation go a bit smoother.
   You should remove them before moving into a production
   environment.
   // 这里删除默认无密码用户
   Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
   Success.
   
   
   Normally, root should only be allowed to connect from
   'localhost'. This ensures that someone cannot guess at
   the root password from the network.
   // 禁止远程 root 登录，我选的是不禁止。因为我的 mac 上的数据库不会放到公网上，也不会存什么敏感数据
   Disallow root login remotely? (Press y|Y for Yes, any other key for No) : no
   
    ... skipping.
   By default, MySQL comes with a database named 'test' that
   anyone can access. This is also intended only for testing,
   and should be removed before moving into a production
   environment.
   
   // 这里删除默认自带的 test 数据库
   Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
    - Dropping test database...
   Success.
   
    - Removing privileges on test database...
   Success.
   
   Reloading the privilege tables will ensure that all changes
   made so far will take effect immediately.
   
   Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
   Success.
   
   All done!