MySQL必知必会

第1章了解SQL

数据库（database） 保存有组织的数据的容器（通常是一个文件或一组文件）。
表（table） 某种特定类型数据的结构化清单。
模式（schema） 关于数据库和表的布局及特性的信息。
列（column） 表中的一个字段。所有表都是由一个或多个列组成的。
数据类型（datatype） 所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。
行（row） 表中的一个记录。
主键（primary key） 一列（或一组列），其值能够唯一区分表中每个行。

表中的任何列都可以作为主键，只要它满足以下条件：
1. 任意两行都不具有相同的主键值；
2. 每个行都必须具有一个主键值（主键列不允许NULL值）。
主键的最好习惯：
1. 不更新主键列中的值；
2. 不重用主键列的值；
3. 不在主键列中使用可能会更改的值。

第2章 MySQL简介

DBMS（数据库管理系统）分两类：
1. 基于共享文件系统的DBMS，如Microsoft Access和FileMaker；
2. 基于客户机—服务器的DBMS，如MySQL、Oracle以及Microsoft SQL Server。

第3章使用MySQL

SHOW DATABASES; /* 显示所有数据库 */
USE mysql; /* 选择数据库mysql */

SHOW TABLES; /* 显示所有表 */

SHOW COLUMNS FROM user; /* 显示user表中的列信息 */
DESCRIBE user; /* 同上 */

SHOW STATUS; /* 显示服务器状态信息 */

SHOW CREATE DATABASE mysql; /* 显示创建数据库mysql的SQL语句 */
SHOW CREATE TABLE user; /* 显示创建表user的SQL语句 */

show grants; /* 显示用户的授权信息 */

SHOW ERRORS; /* 显示服务器中的错误信息 */
SHOW WARNINGS; /* 显示服务器中的警告信息 */

可以使用help show 来查看帮助信息。

第4章检索数据

SELECT User FROM user;
SELECT user.User FROM user; /* 同上 */
SELECT User FROM mysql.user; /* 同上 */

SELECT Host,User FROM user;
SELECT * FROM user;

SELECT DISTINCT User FROM user; /* 去除重复 */

SELECT User FROM user LIMIT 3; /* 只显示查询结果的前3条记录 */
SELECT User FROM user LIMIT 2,3; /* 只显示查询结果的第2条开始（下标从0开始）后面的3条记录 */
SELECT User FROM user LIMIT 3 OFFSET 2; /* 同上 */

SQL不区分大小写，也忽略空格。

DISTINCT关键字应用于所有列而不仅是前置它的列。

第5章排序检索数据

SELECT User FROM user ORDER BY User; /* 升序排序 */
SELECT User FROM user ORDER BY User ASC; /* 同上 */

SELECT User FROM user ORDER BY User DESC; /* 降序排序 */

SELECT User FROM user ORDER BY User, Host; /* 先按User升序排序，再按Host升序排序 */
SELECT User FROM user ORDER BY User DESC, Host; /* 先按User降序排序，再按Host升序排序 */
SELECT User FROM user ORDER BY User DESC, Host ASC; /* 同上 */

DESC关键字只应用到直接位于其前面的列名。

第6章过滤数据

SELECT Host, User FROM user WHERE User='root';

SELECT Host, User, max_connections FROM user WHERE max_connections = 0;
SELECT Host, User, max_connections FROM user WHERE max_connections != 0;
SELECT Host, User, max_connections FROM user WHERE max_connections <> 0; /* 同上 */
SELECT Host, User, max_connections FROM user WHERE max_connections < 0;
SELECT Host, User, max_connections FROM user WHERE max_connections <= 0;
SELECT Host, User, max_connections FROM user WHERE max_connections > 0;
SELECT Host, User, max_connections FROM user WHERE max_connections >= 0;
SELECT Host, User, max_connections FROM user WHERE max_connections BETWEEN 0 AND 5;
SELECT Host, User, max_connections FROM user WHERE ssl_cipher IS NULL; /* ssl_cipher字段为NULL */

第7章数据过滤

SELECT Host, User FROM user WHERE Host='localhost' AND User='root';
SELECT Host, User FROM user WHERE Host='localhost' OR User='root';
SELECT Host, User FROM user WHERE (Host='localhost' OR User='root') AND max_connections = 0;

SELECT Host, User FROM user WHERE Host IN ('%', 'localhost');
SELECT Host, User FROM user WHERE Host NOT IN ('%', 'localhost');

AND的优先级比OR高。

IN操作符完成与OR相同的功能，但是更快。

第8章用通配符进行过滤

1 2	SELECT Host, User FROM user WHERE Host LIKE 'local%'; SELECT Host, User FROM user WHERE User LIKE 'r__t';

百分号（%）表示任何字符出现任意次数。%不能匹配NULL值。

下划线（_）只匹配单个字符。

第9章用正则表达式进行搜索

SELECT Host, User FROM user WHERE User REGEXP 'r..t';
SELECT Host, User FROM user WHERE Host REGEXP '%|localhost';
SELECT Host, User FROM user WHERE User REGEXP 'r[a-z]{2}t';
SELECT Host, User FROM user WHERE User REGEXP 'mysql\\.sys';

可以使用类似SELECT 'hello' REGEXP '[0-9]'这样的SQL来测试正常表达式，匹配成功返回1，否则返回0。

第10章创建计算字段

1
2
3

SELECT CONCAT(User, '(', Host, ')') FROM user;

SELECT CONCAT(User, '(', Host, ')') AS UserHost FROM user;

第11章使用数据处理函数

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格返
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。并且总是应该使用4位数字的年份。

第12章汇总数据

聚集函数（aggregate function） 运行在行组上，计算和返回单个值的函数。
AVG()函数 计算单列的平均值。

AVG()函数忽略列值为NULL的行。
COUNT()函数 进行计数。

使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。

使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
MAX()函数 返回指定列中的最大值。

MAX()函数忽略列值为NULL的行。
MAX()函数 返回指定列中的最小值。

MIN()函数忽略列值为NULL的行。
SUM()函数 用来返回指定列值的和（总计）。

SUM()函数忽略列值为NULL的行。
以上5个聚集函数都可以如下使用：
1. 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；
2. 只包含不同的值，指定DISTINCT参数。
  
  如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
  
  将DISTINCT用于MIN()和MAX() 。虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否包含不同值都是相同的。

第13章分组数据

在 MySQL 中，GROUP BY 语句用于将查询结果按照一个或多个列进行分组，通常与聚合函数（如 COUNT()、SUM()、AVG() 等）一起使用，生成每个分组的汇总数据。

基本语法：

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;

示例：

假设我们有一个 orders 表，包含订单的相关信息（order_id, customer_id, order_date, amount），我们希望按客户分组，统计每个客户的订单总数以及总金额。

1
2
3

SELECT customer_id, COUNT(order_id) AS total_orders, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

解释：

customer_id：我们将按照 customer_id 进行分组。
COUNT(order_id)：计算每个客户的订单数量。
SUM(amount)：计算每个客户的订单总金额。

注意事项：

聚合函数的使用：
- GROUP BY 通常和聚合函数一起使用，例如 COUNT()、SUM()、AVG() 等。如果查询中的列未包含在 GROUP BY 子句中，那么这些列必须是聚合函数的一部分，否则会导致 SQL 错误。
错误示例：
1
SELECT customer_id, order_date, SUM(amount) FROM orders GROUP BY customer_id;
上面查询会抛出错误，因为 order_date 既不是 GROUP BY 子句中的列，也不是聚合函数的一部分。修复后：
1
SELECT customer_id, MAX(order_date), SUM(amount) FROM orders GROUP BY customer_id;
HAVING 子句：
- HAVING 子句允许对分组后的结果进行过滤，类似于 WHERE 过滤行，但 WHERE 在 GROUP BY 之前执行，而 HAVING 在 GROUP BY 之后执行。
示例：过滤出订单金额大于 500 的客户。
1
2
3
4
SELECT customer_id, COUNT(order_id) AS total_orders, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING total_amount > 500;
GROUP BY 多列：
- 可以根据多列进行分组，这样可以按多个维度汇总数据。
示例：按客户 ID 和订单日期分组，统计每天每个客户的订单总金额。
1
2
3
SELECT customer_id, order_date, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id, order_date;

排序：

GROUP BY 生成的结果可以使用 ORDER BY 进行排序。例如，按 total_amount 降序排序。

SELECT customer_id, COUNT(order_id) AS total_orders, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY total_amount DESC;

隐式 GROUP BY 行为：
- 在 MySQL 的一些旧版本（如 5.x）中，GROUP BY 没有严格要求所有未聚合的列必须在 GROUP BY 子句中。这种行为可能会导致不确定的查询结果，建议启用 sql_mode=ONLY_FULL_GROUP_BY，以确保查询更加准确和可预测。
使用GROUP BY创建分组时需要注意：
1. GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
2. 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
3. GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
4. 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
5. 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
6. GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
  
  使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。
分组过滤操作HAVING支持所有WHERE操作符。

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。
一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。
SELECT子句顺序

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

第14章使用子查询

子查询就是可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。
在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

第15章连结表

外键（foreign key） 为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
由没有联结条件的表关系返回的结果为 笛卡儿积（cartesian product） 。如下：
1
2
3
SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name, prod_name;
有时我们会听到返回称为 叉联结（cross join） 的笛卡儿积的联结类型。

等值联结（equijoin） 基于两个表之间的相等测试。这种联结也称为 内部联结 。下面的SQL都是等值联结：

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;

第16章创建高级联结

表别名只在查询执行中使用。

自联结

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';

自联结远比处理子查询快得多。

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。 自然联结 排除多次出现，使每个列只返回一次。这一般是通过对表使用通配符（SELECT*），对所有其他表的列使用明确的子集来完成的。

SELECT c.*, o.order_num, o.order_data, io.prod_id, io.quantity, io.item_price
FROM customers AS c, orders AS o, orderitems AS io
WHERE c.cust_id = o.cust_id
  AND io.order_num = o.order_num
  AND prod_id = 'FB';

外部联结在结果中包含了那些在另一个表中没有关联行的行。

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

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN。

第17章组合查询

MySQL允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为 并（union） 或 复合查询（compound query） 。
使用UNION规则时需要遵循的规则：
1. UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
2. UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
3. 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
UNION从查询结果集中自动去除了重复的行（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样）。这是UNION的默认行为，如果想返回所有匹配行，可使用UNION ALL而不是UNION。
在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。

第18章全文本搜索

MyISAM支持全文本搜索，InnoDB不支持。

为了进行全文本搜索，必须索引被搜索的列。一般在创建表时使用FULLTEXT启用全文本搜索。

CREATE TABLE productnotes
(
    note_id     INT         NOT NULL AUTO_INCREMENT,
    prod_id     CHAR(10)    NOT NULL,
    note_date   DATETIME    NOT NULL,
    note_text   TEXT        NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGINE=MyISAM;

不要在导入数据时使用FULLTEXT。应该首先导入所有数据，然后再修改表，定义FULLTEXT。

使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。
1
2
3
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit');
传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。

搜索不区分大小写，除非使用BINARY方式，否则全文本搜索不区分大小写。

也是使用LIKE子句进行搜索。

1
2
3

SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

使用LIKE子句进行搜索时，不会对结果进行排序。而使用函数Match()和Against()执行全文本搜索时，会对结果进行排序，具有较高等级的行先返回。等级可以通过下面的方式获取：
1
2
SELECT note_text, MATCH(note_text) AGAINST('rabbit') AS 'rank'
FROM productnotes;
在使用 查询扩展 时，MySQL对数据和索引进行两遍扫描来完成搜索：
1. 进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
2. MySQL检查这些匹配行并选择所有有用的词；
3. MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
  1
  2
  3
  SELECT note_text
  FROM productnotes
  WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION);
即使没有FULLTEXT索引也可以使用 布尔文本搜索 ，但这是一种非常缓慢的操作。

排列而不排序 在布尔方式中，不按等级值降序排序返回的
全文本搜索的使用说明
1. 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
2. MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表。
3. 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEANMODE。
4. 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
5. 忽略词中的单引号。例如，don’t索引为dont。
6. 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
7. 如前所述，仅在MyISAM数据库引擎中支持全文本搜索。
  1
  show variables like 'ft%';

一些例子

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('+rabbit +bait' IN BOOLEAN MODE); /* 匹配词rabbit和bait */

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit bait' IN BOOLEAN MODE); /* 匹配词rabbit或bait */

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('"rabbit bait"' IN BOOLEAN MODE); /* 匹配短语"rabbit bait" */

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('>rabbit <bait' IN BOOLEAN MODE); /* 匹配词rabbit和bait，增加前者的等级，降低后者的等级 */

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('+rabbit +(<bait)' IN BOOLEAN MODE); /* 匹配词rabbit和bait，降低后者的等级 */

全文本布尔操作符

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不出现
>	包含，而且增加等级值
<	包含，且减少等级值
()	把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~	取消一个词的排序值
*	词尾的通配符
“”	定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

第19章插入数据

如果表的定义允许，则可以在INSERT操作中省略某些列：
1. 该列定义为允许NULL值（无值或空值）。
2. 在表定义中给出默认值。这表示如果不给出值，将使用默认值。
可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级：
1
INSERT LOW_PRIORITY INTO
同样适用于UPDATE和DELETE。

一些例子：

/* 一次插入一条记录 */
INSERT INTO customers (cust_name, cust_address)
VALUES ('A', 'B');

/* 一次插入多条记录 */
INSERT INTO customers (cust_name, cust_address)
VALUES ('A', 'B')
VALUES ('C', 'D');

/* 插入检索出的数据 */
INSERT INTO customers (cust_name, cust_address)
SELECT cust_name, cust_address
FROM customers_new;

INSERT SELECT中不要求列名匹配。MySQL使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。

第20章更新和删除数据

如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：
1
UPDATE IGNORE customers
如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。

第21章创建和操纵表

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。
函数last_insert_id() 返回最后一个AUTO_INCREMENT值。
外键不能跨引擎。

一些例子：

CREATE TABLE productnotes
(
    note_id     INT         NOT NULL AUTO_INCREMENT,
    prod_id     CHAR(10)    NOT NULL,
    note_date   DATETIME    NOT NULL,
    note_text   TEXT        NULL,
    update_cnt  INT         NOT NULL DEFAULT 0,
    PRIMARY KEY(note_id, prod_id),
) ENGINE=MyISAM;

ALTER TABLE Vendors
DROP COLUMN vend_phone;

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num)
REFERENCES orders (order_num);

DROP TABLE customers2;

RENAME TABLE customers2 TO customers;
RENAME TABLE customers TO customers_backup, vendors TO vendors_backup;

第22章使用视图

视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。
1
2
3
4
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
视图的规则和限制
1. 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
2. 对于可以创建的视图数目没有限制。
3. 为了创建视图，必须具有足够的访问权限。
4. 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
5. ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
6. 视图不能索引，也不能有关联的触发器或默认值。
7. 视图可以和表一起使用。
使用视图
1. 视图用CREATE VIEW语句来创建。
2. 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
3. 用DROP删除视图，其语法为DROP VIEW viewname。
4. 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。
视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。更新一个视图将更新其基表（可以回忆一下，视图本身没有数据）。如果你对视图增加或删除行，实际上是对其基表增加或删除行。
并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：
1. 分组（使用GROUP BY和HAVING）；
2. 联结；
3. 子查询；
4. 并；
5. 聚集函数（Min()、Count()、Sum()等）；
6. DISTINCT；
7. 导出（计算）列。

第23章使用存储过程

执行存储过程

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

创建存储过程

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal  = order total variable

CREATE PROCEDURE ordertotal(
    IN  onumber INT,
    IN  taxable BOOLEAN,
    OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
    --Declare variable for total
    DECLARE total DECIMAL(8, 2);
    --Declare tax percentage
    DECLARE taxrate INT DEFAULT 6;

    --GET the order total
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total;

    --Is this taxable
    IF taxable THEN
        SELECT total+(total/100*taxrate) INTO total;
    END IF;

    SELECT total INTO ototal;
END;

COMMENT的值会在SHOW PROCEDURE STATUS时显示出来。

删除存储过程
1
DROP PROCEDURE ordertotal;
检查存储过程
1
SHOW CREATE PROCEDURE ordertotal;
为了获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS 或SHOW PROCEDURE STATUS LIKE 'ordertotal'。
使用mysql命令行实用程序时需要注意：

默认的MySQL语句分隔符为; mysql命令行实用程序也使用;作为语句分隔符。如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。
解决办法是临时更改命令行实用程序的语句分隔符，如下所示：
1
2
3
4
5
6
7
8
9
DELIMITER //

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

DELIMITER ;
除\符号外，任何字符都可以用作语句分隔符。

第25章使用触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于 BEGIN 和 END 语句之间的一组语句）：
1. DELETE；
2. INSERT；
3. UPDATE。
在创建触发器时，需要给出4条信息：
1. 唯一的触发器名；
2. 触发器关联的表；
3. 触发器应该响应的活动（DELETE、INSERT或UPDATE）；
4. 触发器何时执行（处理之前或之后）。
  1
  2
  3
  CREATE TRIGGER newproduct
  AFTER INSERT ON products
  FOR EACH ROW SELECT 'Product added';
  只有表才支持触发器，视图不支持（临时表也不支持）。
触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE 和DELETE的之前和之后）。

如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL 将不执行AFTER触发器（如果有的话）。
删除触发器
1
DROP TRIGGER newproduct;
触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。
INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：
1. 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
2. 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
3. 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。
  1
  2
  3
  CREATE TRIGGER neworder
  AFTER INSERT ON orders
  FOR EACH ROW SELECT NEW.order_new;
DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点：
1. 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
2. OLD中的值全都是只读的，不能更新。
  1
  2
  3
  4
  5
  6
  7
  CREATE TRIGGER deleteorder
  BEFORE DELETE ON orders
  FOR EACH ROW
  BEGIN
  INSERT INTO archive_orders(order_num, order_date, cust_id)
  VALUE(OLD.order_num, OLD.order_date, OLD.cust_id);
  END;
UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几点：
1. 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
2. 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
3. OLD中的值全都是只读的，不能更新。
  1
  2
  3
  CREATE TRIGGER updatevendor
  BEFORE UPDATE ON vendors
  FOR EACH ROW SET NEW.vend_state = UPPER(NEW.vend_state);
MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。

第26章管理事务处理

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

MyISAM和InnoDB是两种最常使用的引擎。前者不支持明确的事务处理管理，而后者支持。
相关术语：
1. 事务（transaction） 指一组SQL语句；
2. 回退（rollback） 指撤销指定SQL语句的过程；
3. 提交（commit） 指将未存储的SQL语句结果写入数据库表；
4. 保留点（ savepoint ） 指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。
开始事务
1
START TRANSACTION;
回退事务
1
2
3
4
5
6
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句，也 不能回退CREATE或DROP操作 。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

提交事务

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置保留点。这样，如果需要回退，可以回退到某个保留点。
1
2
3
SAVEPOINT delete1; /* 创建保留点 */

ROLLBACK TO delete1; /* 回退到保留点 */
保留点越多，就越能按自己的意愿灵活地进行回退，所以保留点越多越好。

保留点在事务处理完成（执行一条ROLLBACK或 COMMIT）后自动释放。也可以用RELEASE SAVEPOINT明确地释放保留点。
默认的MySQL行为是自动提交所有更改。可以关闭：
1
SET AUTOCOMMIT=0;
autocommit标志决定是否自动提交更改，不管有没有COMMIT 语句。设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）。

autocommit标志是针对每个连接而不是服务器的。

第27章全球化和本地化

重要术语：
1. 字符集 为字母和符号的集合；
2. 编码为某个字符集成员的内部表示；
3. 校对为规定字符如何比较的指令。
查看所支持的字符集完整列表
1
SHOW CHARACTER SET;
查看所支持校对的完整列表
1
SHOW COLLATION;
许多校对出现两次，一次区分大小写（由_cs表一次不区分大小写（由_ci表示）。

通常系统管理在安装时定义一个默认的字符集和校对。

1 2	SHOW VARIABLES LIKE 'character%'; SHOW VARIABLES LIKE 'collation%';

可以在创建数据库时，指定默认的字符集和校对。

CREATE TABLE mytable
(
    c1 INT,
    c2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

一般，MySQL如下确定使用什么样的字符集和校对：
1. 如果指定CHARACTER SET和COLLATE两者，则使用这些值。
2. 如果只指定CHARACTER SET，则使用此字符集及其默认的校对（如SHOW CHARACTER SET的结果中所示）。
3. 如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认。

MySQL还允许对每个列设置：

CREATE TABLE mytable
(
    c1 INT,
    c2 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

校对在对用ORDER BY子句检索出来的数据排序时起重要的作用。如果你需要用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行：
1
2
3
SELECT *
FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;
除了ORDER BY子句，COLLATE还可以用于GROUP BY、HAVING、聚集函数、别名等。
如果绝对需要，串可以在字符集之间进行转换。为此，使用Cast()或Convert()函数。

第28章安全管理

创建用户账号
1
CREATE USER ben IDENTIFIED BY '123';
重命名用户账号
1
RENAME USER ben TO kom;
删除用户账号
1
DROP USER kom;
在创建用户账号后，必须接着分配访问权限。新创建的用户账号没有访问权限。它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。查看用户账号权限：
1
SHOW GRANTS FOR kom;
用户定义为user@host。 MySQL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%（授予用户访问权限而不管主机名）。
为设置权限，使用GRANT语句。GRANT要求你至少给出以下信息：
1. 要授予的权限；
2. 被授予访问权限的数据库或表；
3. 用户名。
  1
  GRANT SELECT ON crashcourse.* TO kom;

撤销特定的权限使用REVOKE：

1	REVOKE SELECT ON crashcourse.* FROM kom;

GRANT和REVOKE可在几个层次上控制访问权限：
1. 整个服务器，使用GRANT ALL和REVOKE ALL；
2. 整个数据库，使用ON database.*；
3. 特定的表，使用ON database.table；
4. 特定的列；
5. 特定的存储过程。
更改密码
1
SET PASSWORD FOR kom = PASSWORD('456');
不指定用户名时，SET PASSWORD更新当前登录用户的口令。SET PASSWORD = PASSWORD('789')
在使用GRANT和REVOKE时，用户账号必须存在，但对所涉及的对象没有这个要求。这允许管理员在创建数据库和表之前设计和实现安全措施。这样做的副作用是，当某个数据库或表被删除时（用DROP语句），相关的访问权限仍然存在。而且，如果将来重新创建该数据库或表，这些权限仍然起作用。
可通过列出各权限并用逗号分隔，将多条GRANT语句串在一起。
1
GRANT SELECT, INSERT ON crashcourse.* TO kom
权限列表

权限	说明
ALL	除GRANT OPTION外的所有权限
ALTER	使用ALTER TABLE
ALTER ROUTINE	使用ALTER PROCEDURE和DROP PROCEDURE
CREATE	使用CREATE TABLE
CREATE ROUTINE	使用CREATE PROCEDURE
CREATE TEMPORARY TABLES	使用CREATE TEMPORARY TABLE
CREATE USER	使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW	使用CREATE VIEW
DELETE	使用DELETE
DROP	使用DROP TABLE
EXECUTE	使用CALL和存储过程
FILE	使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION	使用GRANT和REVOKE
INDEX	使用CREATE INDEX和DROP INDEX
INSERT	使用INSERT
LOCK TABLES	使用LOCK TABLES
PROCESS	使用SHOW FULL PROCESSLIST
RELOAD	使用FLUSH
REPLICATION CLIENT	服务器位置的访问
REPLICATION SLAVE	由复制从属使用
SELECT	使用SELECT
SHOW DATABASES	使用SHOW DATABASES
SHOW VIEW	使用SHOW CREATE VIEW
SHUTDOWN	使用mysqladmin shutdown（用来关闭MySQL）
SUPER	使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER 和SET GLOBAL。还允许mysqladmin调试登录
UPDATE	使用UPDATE
USAGE	无访问权限

第29章数据库维护

备份数据
1. 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
2. 可用命令行实用程序 mysqlhotcopy 从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）。
3. 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原。
  
  为了保证所有数据被写到磁盘（包括索引数据）可能需要在进行备份前使用FLUSH TABLES语句。
进行数据库维护
1. 检查表键是否正确
  1
  ANALYZE TABLE orders;
2. CHECK TABLE用来针对许多问题对表进行检查。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。 CHANGED检查自最后一次检查以来改动过的表。EXTENDED执行最彻底的检查，FAST只检查未正常关闭的表，MEDIUM检查所有被删除的链接并进行键检验，QUICK只进行快速扫描。
  1
  CHECK TABLE orders, orderitems FAST QUICK;
  如果 MyISAM 表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决。
  
  如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能。

第30章改善性能

SQL 优化

避免在 WHERE 条件中使用函数或表达式：

经验：尽量避免在 WHERE 条件的左侧使用函数或表达式，否则会导致无法使用索引，导致全表扫描。

示例：

-- 不推荐使用函数
SELECT * FROM users WHERE YEAR(birthday) = 2020;
-- 推荐优化
SELECT * FROM users WHERE birthday >= '2020-01-01' AND birthday < '2021-01-01';

使用 EXPLAIN 分析查询语句：
- 经验：对于复杂查询，使用 EXPLAIN 分析 SQL 查询的执行计划，检查索引是否被使用，优化 SQL 的执行路径。
- 示例：
  1
  EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01';
1
使用 LIMIT 1 优化单条记录查询：
- 经验：当查询只需要返回一条记录时，使用 LIMIT 1 限制结果集的大小，减少扫描的行数。
- 示例：
  1
  SELECT * FROM users WHERE user_id = 123 LIMIT 1;

避免使用 SELECT *：

经验：只查询所需的字段，避免 SELECT * 导致全表扫描，增加 IO 负担。

示例：

-- 不推荐
SELECT * FROM users WHERE user_id = 123;
-- 推荐
SELECT user_id, user_name FROM users WHERE user_id = 123;

避免在 WHERE 条件中使用 NULL 判断：
- 经验：在 WHERE 子句中对字段进行 NULL 判断可能会导致索引失效，应尽量设计为 NOT NULL。
- 示例：
  1
  2
  3
  4
  -- 不推荐
  SELECT * FROM orders WHERE status IS NULL;
  -- 推荐
  ALTER TABLE orders MODIFY status VARCHAR(10) NOT NULL DEFAULT 'pending';
1
避免在 WHERE 中使用 ! 或 > 操作符：
- 经验：这些操作符可能导致索引失效，查询效率下降。可以通过等价重写 SQL 优化查询性能。
- 示例：
  1
  2
  3
  4
  -- 不推荐
  SELECT * FROM orders WHERE status != 'shipped';
  -- 推荐
  SELECT * FROM orders WHERE status IN ('pending', 'processing');
使用 BETWEEN AND 替代 IN：
- 经验：当范围较大时，BETWEEN AND 通常比 IN 更高效。
- 示例：
  1
  2
  3
  4
  -- 不推荐
  SELECT * FROM orders WHERE order_id IN (1, 2, 3, 4, 5);
  -- 推荐
  SELECT * FROM orders WHERE order_id BETWEEN 1 AND 5;
- 解释
  - BETWEEN AND 直接定义了一个连续的范围，数据库可以通过索引快速查找到这个范围内的所有记录。范围查询可以充分利用索引的顺序结构，进行快速的扫描或跳跃查找，减少不必要的计算。
  - IN 查询是一个离散值查询，当有大量离散值时，数据库需要分别对每个值进行查询。即使有索引，数据库仍然需要对每个值执行多次查找，这相当于执行多次单值查询，效率不如范围查询。
为搜索字段创建索引：
- 经验：对经常用作 WHERE 条件的字段创建索引，可以显著提升查询性能。
- 示例：
  1
  CREATE INDEX idx_order_date ON orders (order_date);
选择合适的存储引擎：
- 经验：选择适合业务需求的存储引擎（如 InnoDB 或 MyISAM），InnoDB 支持事务和行级锁，MyISAM 适合只读查询多的场景。
优化 LIKE 查询：
- 经验：LIKE 'abc%' 可以使用索引，而 LIKE '%abc%' 不会使用索引，需谨慎设计查询模式。
- 示例：
  1
  2
  -- 推荐，走索引
  SELECT * FROM users WHERE user_name LIKE 'John%';
使用 ENUM 代替 VARCHAR：
- 经验：对于固定值的字段，如性别、类别等，使用 ENUM 比 VARCHAR 更节省空间。
- 示例：
  1
  ALTER TABLE users ADD COLUMN gender ENUM('male', 'female');
拆分大批量 DELETE 或 INSERT 操作：
- 经验：大批量 DELETE 或 INSERT 会导致锁表或性能问题，应该将操作拆分为小批量执行。
选择合适的数据类型：
- 经验：选择尽量小、定长、使用整数的数据类型，以提高存储效率和查询速度。
- 示例：
  1
  2
  -- 选择合适的数据类型
  ALTER TABLE orders MODIFY COLUMN order_id INT UNSIGNED NOT NULL;
字段设计尽量使用 NOT NULL：
- 经验：尽量避免使用 NULL，减少判断的复杂性，提升查询效率。

水平切割和垂直切割的举例

水平切割（Sharding）

水平切割 是将表中的数据按行分成不同的表或数据库，用于分散数据量，提高查询性能。

原则：将大表按某个字段进行分片，如用户 ID、订单 ID 等，按范围或哈希进行分割。
示例：假设有 3000 万用户，可以按用户 ID 对 users 表进行分库分表。
表示例：users_01 存储 ID 为 1-10,000,000 的用户，`users_02

补充

char 和 varchar 的区别？

char 和 varchar 类型在存储和检索⽅⾯有所不同
char 列⻓度固定为创建表时声明的⻓度，⻓度值范围是1 到255
当 char 值被存储时，它们被⽤空格填充到特定⻓度，检索 char 值时需删除尾随空格。

第1章 了解SQL

第2章 MySQL简介

第3章 使用MySQL

第4章 检索数据

第5章 排序检索数据

第6章 过滤数据

第7章 数据过滤

第8章 用通配符进行过滤

第9章 用正则表达式进行搜索

第10章 创建计算字段

第11章 使用数据处理函数

第12章 汇总数据

第13章 分组数据

第14章 使用子查询

第15章 连结表

第16章 创建高级联结

第17章 组合查询

第18章 全文本搜索

第19章 插入数据

第20章 更新和删除数据

第21章 创建和操纵表

第22章 使用视图

第23章 使用存储过程

第25章 使用触发器

第26章 管理事务处理

第27章 全球化和本地化

第28章 安全管理

第29章 数据库维护

第30章 改善性能

补充