[MySQL] MySQL基本知识总结
0 前言
此篇文章由阿森一人完成，转载请标注原创。
此篇文章源于《SQL必知必会》（第5版）一书和笔者自己的经验总结。
此篇文章除书本以外的资料均来自网络。感谢菜鸟教程的参考图。
此篇文章为知识总结，不适合初学者阅读。
此篇文章可能存在细微错误，但笔者要求严谨，欢迎指正。
需要Markdown或pdf版本，请联系我的邮箱。
1 检索数据
1.1 检索单个列
SELECT prod_name
FROM Products;
prod_name
-----
Fish bean bag toy
Bird bean bag toy
Rabiit bean bag toy
8 inch teddy bear
12 inch teddy bear
18 inch teddy bear
King doll
Queen doll
- 如果没有明确排序查询结果，则返回的数据没有得定的顺序。
- 多条SQL语句必须以分号（;）分隔。
- SQL语句和大小写：
- SQL语句不区分大小写。
- 表名、列名和值：
- 在Windows中不区分大小写。
- 在UNIX/Linux中区分大小写。
- 在处理SQL语句时，其中所有空格都被忽略。
1.2 检索多个列
SELECT prod_id, prod_name, prod_price
FROM Products;
pord_id prod_name prod_price
------- ----------------- ----------
BNBG01 Fish bean bag toy 3.49
BNBG01 Brid bean bag toy 3.49
1.3 检索所有列
SELECT *
FROM Products;
注意：列的顺序一般是表中出现的物理顺序，但并不总是如此。
1.4 检索不同的值
SQL保留字：DISTINCT
SELECT DISTINCT vend_id
FROM Products;
- 如果使用DISTINCT保留字，它必须直接放在列名前面。
- 不能部分使用DISTINCT：DISTINCT保留字作用于所有列，不仅仅是跟在其后的那一列。
1. 5 限制结果
SQL保留字：LIMIT
SELECT prod_name
FROM Products
LIMIT 5;
SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;
SELECT prod_name
FROM Products
LIMIT 5,5;
- 第一个数字是检索的行数，第二个数字是指从哪开始。
- 第一个被检索的行是第0行，而不是第1行。因此，LIMIT 1 OFFSET 1会检索第2行。
- LIMIT子句应该处于SQL语句的最后。
1.6 使用注释
SELECT *
FROM users; -- 这是单行注释，--后面的空格是必须的
/*
这是
多行
注释
*/
SELECT *
FROM users;
#这是一条注释
SELECT *
FROM users;
2 排序检索数据
2.1 排序数据
SQL保留字：ORDER
SELECT *
FROM users
ORDER BY user_name;
应该保证ORDER BY是SELECT语句中最后一条子句。
2.2 按多个列排序
SELECT *
FROM users
ORDER BY user_name, user_create_time
SELECT user_name, user_create_time
FROM users
ORDER BY 1, 2;
- 1代表指定列的第1个；2代表指定列的第2个。
- 如果有必要，可以混合使用实际列名和相对列位置。
2.3 指定排序方向
SQL保留字：DESC、ASC
SELECT *
FROM users
ORDER BY user_name DESC;
SELECT *
FROM users
ORDER BY user_name DESC, user_create_time;
- DESC保留字只应用到直接位于其前面的列名。如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。
- ASC保留字代表的升序是默认的。
- 在MySQL中字符串“A”和“a”是等同的。
3 过滤数据
3.1 使用WHERE语句
SELECT *
FROM users
WHERE user_name = Aizen;
- 数据也可以在应用层过滤。为此SQL的SELECT语句为客户端应用检索出超过实际所需的数据，然后客户端代码对返回数据进行遍历，提取出需要的行。一般情况下，这种做法是不妥的。这无疑增加了服务器的负担。
- 在同时使用ORDER BY和WHERE子句时，ORDER BY位于WHERE之后。
3.2 WHERE子句操作符
| 操作符 | 描述 | 示例 |
|---|---|---|
| = | 等于 | col_name = value |
| <> 或 != | 不等于 | col_name <> value 或 col_name != value |
| < | 小于 | col_name < value |
| > | 大于 | col_name > value |
| <= | 小于或等于 | col_name <= value |
| >= | 大于或等于 | col_name >= value |
| BETWEEN | 在指定的范围内 | col_name BETWEEN value1 AND value2 |
| IS NULL | 判断是否为NULL值 | col_name IS NULL |
3.3 检查单个值
SELECT *
FROM users
WHERE age > 18;
SELECT *
FROM users
WHERE user_name != 'Aizen';
- 单引号用于限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值进行比较的值不用引号。
- 建议使用!=来代表不等于。
3.4 范围值检查
SQL保留字： BETWEEN AND
SELECT *
FROM users
WHERE age BETWEEN 18 AND 22;
3.5 空值检查
SQL保留字：IS NULL、IS NOT NULL
SELECT *
FROM users
WHERE email IS NULL;
SELECT *
FROM users
WHERE email IS NOT NULL;
4 高级数据过滤
| 操作符 | 描述 | 示例 |
|---|---|---|
| AND | 逻辑AND，多个条件同时成立 | condition1 AND condition2 |
| OR | 逻辑OR，多个条件之一成立 | condition1 OR condition2 |
| IN | 匹配一个值列表 | col_name IN (value1, value2, …) |
| NOT | 逻辑NOT，条件不成立 | NOT condition |
4.1 AND操作符
SELECT *
FROM users
WHERE age BETWEEN 18 AND 22
AND email IS NOT NULL;
4.2 OR操作符
SELECT *
FROM users
WHERE age BETWEEN 18 AND 22
OR email IS NOT NULL;
4.3 求值顺序
默认情况下，AND操作符比OR操作符优先，可以使用括号限制操作书顺序。
SELECT *
FROM users
WHERE (age BETWEEN 18 AND 22
OR email IS NOT NULL)
AND user_name != 'Aizen';
- 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。
4.4 IN操作符
SELECT *
FROM users
WHERE user_name IN ('Aizen', 'Asen');
- IN操作符完成了与OR相同的功能。
4.5 NOT操作符
SELECT *
FROM users
WHERE NOT user_name = 'Aizen'
- 允许使用NOT否定任何条件。
5 用通配符进行过滤
5.1 LIKE操作符
SQL保留字：LIKE
主要用于模糊查询。
5.2 百分号（%）通配符
%表示匹配任何字符出现任意次数。
SELECT user_name
FROM users
WHERE user_name LIKE 'A%en';
user_name
---------
Aizen
aizen
Asen
asen
- 搜索是区分大小写的。
- 在MySQL中，会用空格来填补字段的内容。所以在模糊查询时用函数取消空格。
- LIKE不会匹配值为NULL的内容。
5.3 下划线（_）通配符
_表示匹配任何单个字符。
SELECT user_name
FROM users
WHERE user_name LIKE '_sen';
user_name
---------
Asen
asen
5.4 更多匹配符
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置。如果设置了RegExp对象的Multiline属性，^也匹配’\n’或’\r’之后的位置。 |
| $ | 匹配输入字符串的结束位置。如果设置了RegExp对象的Multiline属性，$也匹配’\n’或’\r’之前的位置。 |
| . | 匹配除"\n"之外的任何单个字符。要匹配包括’\n’在内的任何字符，请使用像’[.\n]'的模式。 |
| […] | 字符集合。匹配所包含的任意一个字符。例如，'[abc]‘可以匹配"plain"中的’a’。 |
| [^…] | 负值字符集合。匹配未包含的任意字符。 |
| p1|p2|p3 | 匹配p1或p2或p3。例如，‘z|food’能匹配"z"或"food"。’(z|f)ood’则匹配"zood"或"food"。 |
| * | 匹配前面的子表达式零次或多次。例如，zo*能匹配"z"以及"zoo"。*等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，'zo+'能匹配"zo"以及"zoo"，但不能匹配"z"。+等价于{1,}。 |
| {n} | n是一个非负整数。匹配确定的n次。例如，'o{2}‘不能匹配"Bob"中的’o’，但是能匹配"food"中的两个o。 |
| {n,m} | m和n均为非负整数，其中n<=m。最少匹配n次且最多匹配m次。 |
5.5 使用通配符技巧
- 不要过度使用通配符。通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。
- 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。
- 仔细注意通配符的位置，避免匹配出错。
6 创建计算字段
6.1 拼接字段
SQL函数：CONCAT(string1, string2…)
SELECT CONCAT(first_name, ' ', last_name)
FROM users;
- 一般情况下，会配合TRIM()函数
6.2 取消字符串空格
SQL函数：TRIM(string)、LTRIM(string)、RTRIM(string)
SELECT TRIM(user_name)
FROM users
WHERE user_name = ' lalala ';
lalala
- LTRIM()代表去掉字符串左边的空格、RTRIM()代表去掉字符串右边的空格。
6.3 使用别名
SQL保留字：AS
SELECT TRIM(user_name) as user_name_after_trim
FROM users
WHERE user_name = ' lalala ';
user_name_after_trim
--------------------
lalala
- AS保留字是可选的，不过最好使用它，这被视为一条最佳实践。
- 别名还有其他用途。常见的用途在实际的表列名包含不合法的字符（如空格）时重新命名它，在原来的字符含混或容易误解时扩充它。
- 别名有时也称为导出列。
6.4 执行算术运算
| 运算符 | 描述 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| / | 除法 |
| % | 取模（取余数） |
SELECT column1 + column2 AS sum FROM table_name; -- 加法
SELECT column1 - column2 AS difference FROM table_name; -- 减法
SELECT column1 * column2 AS product FROM table_name; -- 乘法
SELECT column1 / column2 AS quotient FROM table_name; -- 除法
SELECT column1 % column2 AS remainder FROM table_name; -- 取模
7 使用函数处理数据
| 函数类型 | 函数 | 描述 |
|---|---|---|
| 字符串函数 | CONCAT(str1, str2) | 将两个字符串拼接在一起 |
| SUBSTRING(str, start, length) | 返回字符串的子串，从start位置开始，长度为length | |
| UPPER(str) | 将字符串转换为大写 | |
| LOWER(str) | 将字符串转换为小写 | |
| LENGTH(str) | 返回字符串的长度 | |
| TRIM(str) | 去除字符串两端的空格 | |
| 数学函数 | ROUND(num, decimals) | 对数字进行四舍五入 |
| CEIL(num) | 返回大于或等于指定数字的最小整数 | |
| FLOOR(num) | 返回小于或等于指定数字的最大整数 | |
| ABS(num) | 返回一个数的绝对值 | |
| SQRT(num) | 返回一个数的平方根 | |
| PI() | 返回圆周率π的值 | |
| COS(num) | 返回一个角度的正弦 | |
| SIN(num) | 返回一个角度的余弦 | |
| TAN(num) | 返回一个角度的正切 | |
| 日期和时间函数 | NOW() | 返回当前日期和时间 |
| CURDATE() | 返回当前日期（不包含时间部分） | |
| CURTIME() | 返回当前时间（不包含日期部分） | |
| DATE_FORMAT(date, format) | 将日期格式化为指定的格式 |
- 函数不区分大小写，因此UPPER()和upper()效果是一致的。
- 更多内容可以查看MySQL的官方文档。
8 汇总数据
| 函数 | 说明 |
|---|---|
| AVG(column) | 计算指定列的平均值 |
| COUNT(column) | 计算指定列的行数 |
| MAX(column) | 返回指定列的最大值 |
| MIN(column) | 返回指定列的最小值 |
| SUM(column) | 计算指定列的总和 |
8.1 AVG()函数
SELECT AVG(item_price) AS avg_item_price
FROM OrderItems;
SELECT AVG(quantity*prod_price) AS avg_pended_price
FROM OrderItems;
- AVG()只能用来确定数列值的平均值。
- AVG()函数无裂列值为NULL的行。
8.2 COUNT()函数
SELECT COUNT(*) AS num_user
FROM users;
SELECT COUNT(email) AS num_email
FROM users;
- 如果指定列名，则COUNT()函数会忽略指定列的值为NULL的行。
- 如果COUNT()函数中用的是星号，则不忽略。
8.3 MAX()函数
SELECT MAX(quantity) AS max_num_items
FROM OrderItems;
SELECT MAX(quantity*prod_price) AS max_expended_price
FROM OrderItems;
- MAX()一般用来找出最大的数值或日期值，也可以用于查找文本数据的最大值，返回该列排序后的最后一行。
- MAX()函数忽略列值为NULL的行。
8.4 MIN()函数
SELECT MIN(quantity) AS min_num_items
FROM OrderItems;
SELECT MIN(quantity*prod_price) AS min_expend_price
FROM OrderItems;
- MIN()一般用来找出最小的数值或日期值，也可以用于查找文本数据的最小值，返回该列排序后的第一行。
- MIN()函数忽略列值为NULL的行。
8.5 SUM()函数
SELECT SUM(quantity) AS total_num_items
FROM OrderItems;
SELECT SUM(quantity*prod_price) AS total_price
FROM OrderItems;
- SUM()函数忽略列值为NULL的行。
8.6 聚集不同的值
SQL保留字：DISTINCT、ALL
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM OrderItems;
- 默认情况下，使用ALL表示的所有行都会进入运算。
- DISTINCT不能用于COUNT(*)。
- DISTINCT用于MAX()和MIN()函数没有任何效果。
9 分组数据
9.1 数据分组
SQL保留字：GROUP BY
分组查询主要思路是根据某一个列分成不同的组，相同的值为一组，然后对所有组执行相同的操作。
SELECT age, COUNT(*) AS num_18_age
FROM users
WHERE age = 18;
SELECT age, COUNT(*) AS num_19_age
FROM users
WHERE age = 19;
...
上述的MySQL语句可以直接用下面方式实现：
SELECT age, COUNT(*) AS num_age
FROM users
GROUP BY age;
SELECT age, COUNT(*) AS num_age
FROM users
GROUP BY 1;
-
不推荐使用以数字标识列的方法。
-
GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套。
-
如果GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。
-
GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名：
SELECT city, AVG(age) AS average_age FROM users GROUP BY AVG(age); -
除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。例如下面就是不合法的：
SELECT department, name, MAX(salary) AS max_salary FROM employees GROUP BY department;因为在SELECT语句中选择了name列，但没有在GROUP BY子句中列出。
-
分组列中包含NULL值的行会被作为一个分组返回。
-
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
9.2 过滤分组
SELECT age, COUNT(*) AS num_age
FROM users
GROUP BY age
HAVING COUNT(*) <= 10;
-
在HAVING子句中，不能引用SELECT中聚集函数的别名，例如下面的MySQL语句是不合法的：
SELECT age, COUNT(*) AS num_age FROM users GROUP BY age HAVING num_age <= 10;即使使用了两次COUNT(*)，MySQL会执行两次COUNT()函数，也不会影响性能，因为MySQL的查询优化器通常会对这类重复计算进行优化，避免不必要的计算开销。
-
有关WHERE的所有技术和选项都适用于HAVING。
-
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤，例如：
SELECT age, COUNT(*) AS num_age FROM users WHERE age BETWEEN 18 AND 22 GROUP BY age HAVING COUNT(*) <= 10; -
如果不指定GROUP BY，那么HAVING是无效的。
9.3 分组和排序
GROUP BY和ORDER BY容易混淆，但实际上两者的区别比较大。
| 功能 | GROUP BY | ORDER BY |
|---|---|---|
| 作用 | 对数据进行分组，基于分组的结果进行聚合计算 | 对结果集按照指定的列或表达式进行排序 |
| 使用场景 | 需要按照某个列或表达式进行分组，通常与聚合函数一起使用 | 需要对结果集按照某个列或表达式进行排序 |
| 语法 | GROUP BY column_name 或 GROUP BY expression | ORDER BY column_name [ASC/DESC] |
| 顺序 | 通常位于SELECT语句的末尾，在WHERE子句之后 | 位于SELECT语句的末尾，GROUP BY 子句之后 |
| 聚合函数 | 通常与聚合函数（如SUM、AVG、COUNT等）一起使用 | 通常不涉及聚合函数，只是对结果集的排序 |
| 返回结果 | 返回分组的结果，每个分组对应一个计算结果 | 返回排序后的结果集，保留所有行，只是排序顺序不同 |
- 一般在使用GROUP BY子句时，应该也给出ORDER BY子句。确保数据的正确排序。
9.4 SELECT子句顺序
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| SELECT | 用于指定要检索的列或表达式 | 1 |
| FROM | 用于指定要查询的表 | 1 |
| WHERE | 用于筛选行，基于指定的条件 | 0 |
| GROUP BY | 用于将数据分组，并对每个组应用聚合函数 | 0 |
| HAVING | 用于在分组后筛选结果，基于聚合函数的结果 | 0 |
| ORDER BY | 用于对结果集按照指定的列或表达式进行排序 | 0 |
| LIMIT/OFFSET | 用于限制返回的行数和指定起始位置 | 0 |
10 使用子查询
10.1 利用子查询进行过滤
某些情况下，SQL过滤参数需要另一个SQL得到，使用子查询可以简化操作。
SELECT oder_num
FROM OrderItems
WHERE pord_id = 'RGAN01';
order_num
---------
20007
20008
SELECT cust_id
FROM Orders
WHERE order_num IN (20007, 20008);
可以转化为：
SELECT cust_id
FROM Orders
WHERE order_num IN (
SELECT oder_num
FROM OrderItems
WHERE prod_id = 'RGAN01');
- 子查询总是从内向外处理。
- 内嵌的子查询可以使用外部引用的表。
- 子查询的SELECT语句难以阅读和调试，它们在较为复杂时更是如此。
- 作为子查询的SLECT语句只能查询单个列。企图检索多个列将返回错误。
- 即使给出的代码有效，并且获得了所需的结果。但是，使用子查询并不总是执行这类数据检索的最有效方法。
10.2 作为计算字段使用子查询
SELECT cust_name, cust_state, (
SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers;
- 内嵌的子查询可以使用外部引用的表。
- 必须用完全限定列名，避免冲突列名而导致的歧义。
- 即使给出的代码有效，并且获得了所需的结果。但是，使用子查询并不总是执行这类数据检索的最有效方法。
11 联结表
11.1 创建联结
SELECT user_name, role
FROM users, user_roles
WHERE users.id = user_roles.user_id;
user_name role
--------- -----
Aizen ADMIN
aizen ADMIN
Asen GUEST
asen SSVIP
- 必须用完全限定列名，避免冲突列名而导致的歧义。
- 没有WHERE子句，第一个表会和第二个表的内容形成笛卡尔积结果。
11.2 内联结
SELECT user_name, role
FROM users
INNER JOIN user_roles ON users.id = user_roles.user_id;
user_name role
--------- -----
Aizen ADMIN
aizen ADMIN
Asen GUEST
asen SSVIP
- 和创建联结的示例效果一致，两个表首选INNER JOIN方法，多个表使用“,”方法。
11.3 联结多个表
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007
- 在运行时关联指定的每个表，以处理联结，这种处理可能非常耗费资源，因此应该注意，不要链接不必要的表。设计表时可以通过数据冗余来提高查询速度。
- 联结多个表可以解决多个子查询嵌套的问题。选择哪种方式，需要在两者中进行测试后决策。
12 创建高级联结表
12.1 使用表别名
SQL保留字：AS
SELECT user_name, role
FROM users AS u
INNER JOIN user_role AS r ON u.id = r.user_id;
- 表别名只在查询语句的执行过程中有效，不会修改数据库中的表结构。
- 避免使用过于复杂或难以理解的别名，以保持查询语句的简洁和易懂。
12.2 自联结
情景：查询年龄和Aizen一样的用户。
子查询解决方式：
SELECT user_name
FROM users
WHERE age = (
SELECT age
FROM users
WHERE user_name = 'Aizen');
自联结解决方式：
SELECT u1.user_name
FROM users AS u1, users AS u2
WHERE u1.age = u2.age
AND u2.user_name = 'Aizen';
- 自联结通常作为外部语句，用来代替从相同表中检索数据的使用子查询语句。
- 虽然最终结果是相同的，但MySQL处理连结远比处理子查询快得多。
12.3 自然联结
自然连接会自动根据名称相同的列进行联结。
users中的id为user_id，roles也为user_id
SELECT user_name, role
FROM users
NATURAL JOIN roles;
MySQL支持左/右自然联结：
SELECT user_name, role
FROM users
NATURAL LEFT JOIN roles;
SELECT user_name, role
FROM users
NATURAL RIGHT JOIN roles;
MySQL支持函数联结：
SELECT user_name, role
FROM users
INNER JOIN roles USING(user_id);
12.4 外联结
左外联结：
SELECT user_name, role
FROM users
LEFT JOIN roles ON users.user_name = roles.user_name;
此时若没有设定权限的用户也会被显示出现来：
user_name role
--------- -----
Aizen ADMIN
Asen GUEST
Azen NULL
右外联结类似：
- 左联结和右联结可以互相转化。
13 组合查询
13.1 使用UNION
SQL保留字：UNION
组合查询用于将多个SQL语句查询结果组合在一起。
SELECT user_name, user_age
FROM users
WHERE user_name IN ('Aizen', 'Asen')
ORDER BY user_name;
user_name user_age
--------- --------
Aizen 20
Asen 19
SELECT user_name, user_age
FROM users
INNER JOIN roles ON users.user_id = roles.user_id;
WHERE role IN ('ADMIN')
OR role IS NULL;
ORDER BY user_name;
user_name user_age
--------- --------
Aizen 20
Azen 50
SELECT user_name, user_age
FROM users
WHERE user_name IN ('Aizen', 'Asen') -- 这里没有分号
#这里的ORDER BY必须移到最后
UNION
SELECT user_name, user_age
FROM users
INNER JOIN roles ON users.user_id = roles.user_id;
WHERE role = 'ADMIN'
OR role IS NULL;
ORDER BY user_name;
user_name user_age
--------- --------
Aizen 20
Asen 19
Azen 50
13.2 UNION规则
- UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。
- UNION中的每一个查询必须包含相同的列、表达式、或聚集函数，但不需要列的顺序一致。
- 列数据的类型必须兼容：类型不必完全相同，但必须是MySQL可以隐含转换的类型。例如不同数值类型或不同的日期类型。
- UNION会自动进行类型匹配，但不要产生歧义。
- 允许第一个SELECT子句对列使用别名。
13.3 包含或取消重复的行
使用UNION时，重复的行会被自动取消。可以使用UNION ALL取消这一行为。
...
UNION ALL
...
user_name user_age
--------- --------
Aizen 20
Aizen 20
Asen 19
Azen 50
14 插入数据
14.1 使用INSERT插入数据
SQL保留字：INSERT INTO、VALUES
INSERT INTO users(id, user_name, age, email)
VALUES (5, 'Aisen', 33, NULL);
- 主键的值必须有唯一性，插入的主键若已经存在会导致插入失败。
- 表给出的列与VALUES的内容必须对齐，否则会出错。
14.2 插入部分行
INSERT INTO users(id, user_name, age)
VALUES (5, 'Aizen', 33);
- 如果表的定义允许，则可以在INSERT操作中省略某些列。
- 该表定义允许为NULL。
- 该表定义了默认值。
- 如果表中不允许有NULL值或者默认值，这时插入操作失败。
14.3 插入检索出的数据
INSERT INTO users (id, user_name, age, email)
SELECT 5, 'Fake Aizen', age, email
FROM users
WHERE user_name = 'Aizen';
-
必须使用INSERT INTO+SELECT语句，不能中间添加VALUES子句内嵌，例如下面语句是不合法的：
INSERT INTO users(id, user_name, age, email) VALUES (5, 'Fake Aizen', ( SELECT age, email FROM users WHERE user_name = 'Aizen')); -
即使可以用来复制表，但是。
-
INSERT通常插入一行，要插入多行，必须指定多个INSERT语句。
14.4 从一个表复制到另一个表
SQL保留字：CREATE TABLE、AS
CREATE TABLE users_new AS
SELECT id, user_name, email
FROM users
WHERE age > 18;
- 任何SELECT选项和子句都可以使用，包括WHERE和GOURP BY。
- 可以利用联结从多个表插入数据。
- 不管从多少个表中检索数据，数据都只能插入到一个表中。
15 更新和删除数据
15.1 更新数据
SQL保留字：UPDATE、SET
UPDATE users
SET email = 'lalala@lalala.com'
WHERE user_name = 'Azen';
UPDATE users
SET email = 'lalala@lalala.com'
age = 51
WHERE user_name = 'Azen';
-
UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。
UPDATE users SET email = (SELECT email FROM users WHERE user_name = 'Aizen'), age = (SELECT age FROM users WHERE user_name = 'Aizen') WHERE user_name = 'Fake Aizen'; -
UPDATE语句可以删除某一列的数据。
UPDATE users SET email = NULL;
15.2 删除数据
SQL保留字：DELETE FROM
DELETE FROM users
WHERE user_name = 'Fake Aizen';
-
DELETE不需要列名或通配符，DELECT删除整行而不是删除列，要删除指定的列，则使用UPDATE语句。
-
如果想从表中删除所有行，不要使用DELETE，使用TRUNCATE TABLE语句，它完成同样的工作，而且速度更快。
TRUNCATE TABLE users;
15.3 更新和删除的指导原则
- 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
- 保证每个表都有主键，尽可能像WHERE子句那样使用它。
- 在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
- 使用强制实施引用完整性的数据库，不允许删除其数据与其他表相关联的行。
- 最著名的程序员保留字“删库跑路”的来源就是如此，但同时删除公司数据库会承担相应的刑事责任。
- 一般情况下需要定期备份数据库，以防服务器崩溃，被攻击等情况。
16 创建和操作表
16.1 创建表基础
SQL保留字：CREATE TABLE
CREATE TABLE users
(
user_id CHAR(10) NOT NULL,
user_name VARCHAR(20) NOT NULL,
age INT NULL,
email VARCHAR(50) NULL
)
-
建议使用缩进，方便阅读和理解。
-
在创建表时，指定的表名必须不存在，否则会出错。防止意外覆盖已有的表。一般情况下需要删除原本的表，然后重新创建它。
DROP TABLE users; CREATE TABLE users(...);或者MySQL允许创建前判定表是否存在，若存在则不创建。SQL保留字：IF NOT EXISTS
CREATE TABLE IF NOT EXISTS users(...);
16.2 使用NULL值
创建表时一定要指明是否可以为NULL。若列为主键，则必须NOT NULL。
16.3 使用默认值
CREATE TABLE IF NOT EXISTS users (
...
age INT NULL DEFAULT 18,
...
)
16.4 更新表
- 理想情况下，不会再表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结果做大改动。
- 允许给现有的表增加列，不过对所增加列的数据类型以及NULL和DEFAULT的使用有所限制。
- MySQL允许删除或更改表中的列。
- MySQL允许重命名表中的列。
16.4.1 增加列
SQL保留字：ALTER TABLE、ADD
ALTER TABLE users
ADD is_online TINYINT(1);
16.4.2 删除列
SQL保留字：ALTER TABLE、DROP COLUMN
ALTER TABLE users
DROP COLUMN is_online;
复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：
- 用新的列布局创建一个新表；
- 使用INSERT SELECT语句从酒标复制数据到新表。有必要的话，可以使用转换函数和计算字段；
- 检验包含所需数据的新表；
- 重命名旧表（如果确定，可以删除它）；
- 用旧表原来的名字重命名新表；
- 根据需要，重新创建触发器、存储过程、索引和外键。
16.5 删除表
DROP TABLE users;
- 执行这条语句将永久删除该表。
16.6 重命名表
RENAME TABLE roles TO user_roles;
17 视图
17.1 创建视图
SQL保留字：CREATE VIEW、AS
CREATE VIEW adult_users AS
SELECT user_id, user_name, age, email
FROM users
WHERE age >= 18;
CREATE VIEW adult_users (id, name, email) AS
SELECT user_id, user_name, age, email
FROM users
WHERE age >= 18;
CREATE VIEW users_name AS
SELECT user_id, CONCAT(TRIM(first_name), ' ', TRIM(last_name)) AS user_full_name
FROM users;
17.2 使用视图
SELECT user_name, email
FROM adult_users
WHERE user_name LIKE '%en%'
SELECT user_id, user_full_name
FROM users_name;
- 从视图检索数据时，如果使用了一条WHERE子句，则会和视图中的WHERE子句自动组合。
- 视图的定义方式主要来源于AS后面的SELECT子句，SELECT的所有功能，都可以用于定义视图。
17.3 删除视图
DROP VIEW adult_users, users_name;
DROP VIEW IF EXISTS adult_users, users_name;
17.4 视图的相关规定
视图的使用：
- 重用SQL语句，简化复杂的SQL语句。
- 使用表的一部分，而不是整个表。
- 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
- 更改数据格式和表示。
视图的特性：
- 创建视图后可以用与表基本相同的方式使用它们。
- 视图仅仅是用来查看存储在别处数据的一种设施。视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图会返回更新后的结果。
- 每次使用视图时，都必须查询执行时需要的所有检索。如果用了多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。
视图的规则：
- 视图必须唯一命名。
- 对于可以创建的视图数量没有限制。
- 创建视图必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
- 视图可以嵌套。
- 可以在使用视图和定义视图时规定顺序，最终的展示顺序以使用视图的顺序为准。
- 视图不能添加索引，也不能有关联的触发器或默认值。
- 视图的计算字段必须拥有别名。
- 在MySQL中，视图是只读的，意思是无法对其添加和删除操作。
18 存储过程（方法）
存储过程就是方法，可以在SQL中调用方法简化操作。
在一般的项目中，总是由代码内嵌SQL语句实现的，每一个语句都定义了其作用，所以该功能不常用。
18.1 创建存储过程（方法）
SQL保留字：CREATE PROCEDURE、IN、OUT、BEGIN、END
CREATE PROCEDURE GetEmployeeInfo(IN empID INT)
BEGIN
SELECT employee_name, salary
FROM employees
WHERE employee_id = empID;
END;
CREATE PROCEDURE GetEmployeeInfo(OUT empName VARCHAR(255), OUT empSalary DECIMAL(10, 2), IN empID INT)
BEGIN
SELECT employee_name, salary
INTO empName, empSalary
FROM employees
WHERE employee_id = empID;
END
- IN代表方法的参数，OUT代表方法返回的列，定义时需要使用INTO保留字。它们都在括号内定义。
- 使用BEGIN、END来定义方法体。
18.2 调用存储过程（方法）
SQL保留字：CALL
CALL GetEmployeeInfo(1);
等同于：
SELECT employee_name, salary
FROM employees
WHERE employee_id = 1;
CALL GetEmployeeInfo(@name, @salary, 1);
SELECT @name, @salary;
- @name的形式是定义变量，第一次出现时默认创建变量，后续调用时可以引用。
18.3 删除存储过程（方法）
DROP PROCEDURE [IF EXISTS] GetEmployeeInfo;
- []的意思代表可选。
19 管理事务处理
19.1 事务
利用事务可以定义一列SQL的操作，将其作为一个整体，保证数据信息正确。
事务的注意：
- 事务处理用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT语句（也没必要），也不能回退CREATE或DROP操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。
- 事务的处理是并发进行的，这会产生多线程问题。
- 事务具有以下特性，通常缩写为ACID特性：
- 原子性（Atomicity）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
事务相关的专业术语：
- 事务（transaction）：指一组SQL语句。
- 回退（rollback）：指撤销指定SQL语句的过程。
- 提交（commit）：指将未存储的SQL语句结果写入数据库表。
- 保留点（savepoint）：指事务处理中设置的临时占位符（placeholder），对它可以发布回退。
19.2 定义事务范围
SQL保留字：START TRANSACTION、COMMIT
START TRANSACTION
... -- 一组SQL操作
COMMIT;
19.3 回退
SQL保留字：ROLLBACK
DELETE FROM users;
ROLLBACK;
START TRANSACTION
... -- 一组SQL操作
COMMIT;
ROLLBACK;
- ROLLBACK的上一个SQL的操作将会被回退（撤销），但DROP和CREATE不能撤销。
- 可以在事务内部执行条件判定，执行回退。
19.4 保留点
SQL保留字：SAVEPOINT
START TRANSACTION
...
SAVEPOINT savepoint_name;
...
COMMIT;
ROLLBACK TO [SAVEPOINT] savepoint_name;
- 可以尽量多地在事务中添加保留点。
19.5 示例
START TRANSACTION;
UPDATE accounts SET balance = balance - 500.00 WHERE id = 1;
SAVEPOINT savepoint1;
UPDATE accounts SET balance = balance + 500.00 WHERE id = 2;
-- 如果这里发生了错误，我们可以回滚到savepoint1
ROLLBACK TO SAVEPOINT savepoint1;
UPDATE accounts SET balance = balance + 300.00 WHERE id = 3;
COMMIT;
20 游标
20.1 游标的特性
- 能够标记游标为只读，是数据能读取，但不能更新和删除。
- 能控制可以指定的定向操作（向前、向后、第一、最后、绝对位置和相对位置等）。
- 能标记某些列为可编辑的，某些列为不可编辑的。
- 规定范围，使游标对创建它的特定请求（如存储过程/方法）或对所有请求可访问。
- 游标通常需要在内存中维护结果集，如果结果集非常大，可能会占用大量的内存。
- 游标的使用可能导致数据库的锁定问题，特别是在并发访问下。
20.2 使用游标
使用游标具有明确的几个步骤：
- 在使用前，需要声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句和游标选项。
- 一旦声明，就必须打开游标以供使用。这个过程用前面定义的SELECT语句吧数据实际检索出来。
- 对于填有数据的游标，根据需要取出（检索）各行。
- 在结束游标使用时，必须关闭游标，可能的话，释放游标。
声明游标后，可根据需要频繁地打开和关闭游标。在游标打开时，可根据需要频繁地执行取操作。
通常游标在存储过程（方法）中使用。
20.2.1 创建游标
SQL保留字：DECLARE、CURSOR、FOR
DECLARE user_basic_info CURSOR
FOR
SELECT user_name, user_age
FROM users;
20.2.2 打开游标
SQL保留字：OPEN
OPEN user_basic_info;
20.2.3 便利游标
SQL保留字：FETCH
FETCH user_basic_info INTO user_name, user_age;
20.2.4 关闭游标
SQL保留字：CLOSE
CLOSE user_basic_info;
21 高级SQL特性
21.1 约束
SQL保留字：ADD CONSTRAINT
ALTER TABLE ...
ADD CONSTRAINT ...;
允许在定义表时添加约束。
21.1.1 主键
SQL保留字：PRIMARY KEY
CREATE TABLE users
(
user_id CHAR(10) NOT NULL PRIMARY KEY,
...
);
ALTER TABLE users
ADD CONSTRAINT PRIMARY KEY (user_id);
主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且用不改动（不执行UPDATE修改其值）。表中任意列只要满足以下条件，都可以用于主键：
- 任意两行的主键值都不相同。
- 每行都具有一个主键值（即列中不允许NULL值）。
- 包含主键值的列从不修改或更新。
- 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。
21.1.2 外键
SQL保留字：FOREIGN KEY、REFERENCES
CREATE TABLE user_roles
(
...
user_id CHAR(10) NOT NULL REFERENCES users(id),
...
);
ALTER TABLE users
ADD CONSTRAINT
FOREIGN KEY (user_id) REFERENCES users (id);
外键是表中的一列，其值必须列在另一个表的主键中，外键是保证引用完整性的极其重要的一部分。
21.1.3 唯一约束
SQL保留字：UNQUER
CREATE TABLE users
(
...
user_name VARCHAR(20) NOT NULL UNIQUE,
...
);
CREATE TABLE user_roles
(
...
user_id CHAR(10) NOT NULL REFERENCES users(id),
role_id CHAR(10) NOT NULL REFERENCES roles(id),
UNIQUE (user_id, role_id),
...
);
ALTER TABLE user_roles
ADD CONSTRAINT user_role_unique UNIQUE (user_id, role_id);
唯一约束用来保证一列（或一组列）中的数据是唯一的。
- 表可包含多个唯一约束，但每个表只允许一个主键。
- 唯一约束列可包含NULL值。
- 唯一约束列可修改或更新。
- 唯一约束列的值可重复使用。
- 与主键不一样，唯一约束不能用来定义外键。
21.1.4 检查约束
CREATE TABLE users
(
...
age INT NOT NULL CHECK (age >= 18),
...
);
ALTER TABLE users
ADD CONSTRAINT CHECK (age >= 18);
检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。
常见用途：
- 检查最小或最大值。
- 指定范围。
- 只允许特定的值。
21.2 索引
SQL保留字：INDEX
CREATE INDEX user_role_index
ON user_role (user_id, role_id);
索引用来排序数组，以加快搜索和排序操作的速度。
- 索引改善检索操作的性能，但降低了数据插入，修改和删除的性能。在执行这些操作时，MySQL必须动态地更新索引。
- 索引数据可能要占用大量的存储空间。
- 并非所有数据都适合做索引。
- 索引用于数据过滤和数据排序。
- 可以在索引中定义多个列。例如州加上城市。
21.3 触发器
SQL保留字：TRIGGER、BEFORE、AFTER、FOR EACH ROW、BEGIN、END
CREATE TRIGGER trigger_name
{BEFORE|AFTER} {INSERT|UPDATE|DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- 触发器的逻辑操作
END;
触发器是特殊的存储过程，他在特定的数据库活动发生时自动执行，触发器可以与特定表上的INSERT、UPDATE和DELETE操作（或组合）相关联。
- 触发器只与单个表相关联。
- 触发器内的代码具有以下数据的访问权：
- INSERT操作中的所有新数据。
- UPDATE操作中的所有新数据和旧数据。
- DELETE操作中删除的数据。
触发器常见的用途：
- 保证数据一致。例如，保证所有州名转换为大写。
- 基于某个表的变动在其他表上指定活动。例如每天加一个选课信息就增加学生选课的学时。
- 进行额外的验证并根据需要回退数据，保证用户的金钱不超限定。
- 计算计算列的值或更新时间戳。
21.4 数据库安全
MySQL提供了管理机制，利用管理机制授予或限制对数据的访问。
需要保护的操作有：
- 对数据库管理功能（创建表、更改或删除已存在的表等）的访问。
- 对特定数据库或表的访问。
- 访问的类型（只读、对特定列的访问等）。
- 仅通过视图或存储过程对表进行访问。
- 创建多层次的安全措施，从而允许多种基于登陆的访问和控制。
- 限制管理用户账号的能力。
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
