缪雪峰MySQL教程

本文最后更新于：2020年2月19日 晚上

一： MySQL执行外部sql脚本文件的命令

sql脚本是包含一到多个sql命令的sql语句，我们可以将这些sql脚本放在一个文本文件中（我们称之为“sql脚本文件”），然后通过相关的命令执行这个sql脚本文件。基本步骤如下：

1.1 创建包含sql命令的sql脚本文件

文件中包含一些列的sql语句，每条语句最后以;结尾，文件内容示例如下：
–创建表，使用“–”进行注释

1
2
3
4
5
6
create table 表名称
(
  Guid Varchar(38) not null primary key,  
  Title Varchar(255),

) TYPE=InnoDB;
  • 在表A中增加字段Status
    alter table A add Status TinyInt default '0';
  • 在表A上创建索引
    create index XX_TaskId_1 on A(Id_);
  • 在A表中添加一条记录
    Insert into A (Id,ParentId, Name) values(1,0,'名称');
  • 添加、修改、删除数据后，有可能需要提交事务
    Commit;

1.2 执行sql脚本文件

1.2.1 方法一 ：使用cmd命令执行(windows下，unix或linux在的其控制台下)

【Mysql的bin目录】\mysql –u用户名 –p密码 –D数据库<【sql脚本文件路径全名】，示例：
D:\mysql\bin\mysql –uroot –p123456 -Dtest<d:\test\ss.sql
注意：
A、如果在sql脚本文件中使用了use 数据库，则-D数据库选项可以忽略
B、如果【Mysql的bin目录】中包含空格，则需要使用“”包含，如：“C:\Program Files\mysql\bin\mysql” –u用户名 –p密码 –D数据库<【sql脚本文件路径全名】

1.2.2 方法二 ：进入mysql的控制台后，使用source命令执行

Mysql>source 【sql脚本文件的路径全名】 或 Mysql>. 【sql脚本文件的路径全名】，示例：source d:\test\ss.sql 或者 \. d:\test\ss.sql

二： 关系数据库概述

数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：

  • 层次模型
  • 网状模型
  • 关系模型

随着时间的推移和市场竞争，最终，基于关系模型的关系数据库获得了绝对市场份额。

2.1 数据类型

对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等：

名称 类型 说明
INT 整型 4字节整数类型，范围约+/-21亿
BIGINT 长整型 8字节整数类型，范围约+/-922亿亿
REAL 浮点型 4字节浮点数，范围约+/-1038
DOUBLE 浮点型 8字节浮点数，范围约+/-10308
DECIMAL(M,N) 高精度小数 由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N) 定长字符串 存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N) 变长字符串 存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN 布尔类型 存储True或者False
DATE 日期类型 存储日期，例如，2018-06-22
TIME 时间类型 存储时间，例如，12:20:59
DATETIME 日期和时间类型 存储日期+时间，例如，2018-06-22 12:20:59

2.2 SQL DML 和 DDL

总的来说，可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。
SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。
查询和更新指令构成了 SQL 的 DML 部分：

  1. SELECT - 从数据库表中获取数据
  2. UPDATE - 更新数据库表中的数据
  3. DELETE - 从数据库表中删除数据
  4. INSERT INTO - 向数据库表中插入数据

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。
SQL 中最重要的 DDL 语句:

  1. CREATE DATABASE - 创建新数据库
  2. ALTER DATABASE - 修改数据库
  3. CREATE TABLE - 创建新表
  4. ALTER TABLE - 变更（改变）数据库表
  5. DROP TABLE - 删除表
  6. CREATE INDEX - 创建索引（搜索键）
  7. DROP INDEX - 删除索引

2.3 安装MySQL

要在Windows或Mac上安装MySQL，首先从MySQL官方网站下载最新的MySQL Community Server版本：
MySQL官方网站
选择对应的操作系统版本，下载安装即可。在安装过程中，MySQL会自动创建一个root用户，并提示输入root口令。
要在Linux上安装MySQL，可以使用发行版的包管理器。例如，Debian和Ubuntu用户可以简单地通过命令apt-get install mysql-server安装最新的MySQL版本。

2.4 运行MySQL

MySQL安装后会自动在后台运行。为了验证MySQL安装是否正确，我们需要通过mysql这个命令行程序来连接MySQL服务器。
在命令提示符下输入mysql -u root -p，然后输入口令，如果一切正确，就会连接到MySQL服务器，同时提示符变为mysql>。
输入exit退出MySQL命令行。注意，MySQL服务器仍在后台运行。

三： 关系模型

我们已经知道，关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。
表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。
字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串’’。

通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。

在关系数据库中，关系是通过主键和外键来维护的。

3.1 主键

在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。
每一条记录都包含若干定义好的字段。同一个表的所有记录都有相同的字段定义。
对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。
对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。
由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。
所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。
因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。
作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：

  • 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
  • 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

对于大部分应用来说，通常自增类型的主键就能满足需求。

PS:如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。
对于联合主键，允许一列有重复，只要不是所有主键列都重复即可。
没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

小结
主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。可以使用多个列作为联合主键，但联合主键并不常用。

3.2 外键

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个classes的记录可以对应多个students表的记录。
为了表达这种一对多的关系，我们需要在students表中加入一列class_id，让它的值与classes表的某条记录相对应。
这样，我们就可以根据class_id这个列直接定位出一个students表的记录应该对应到classes的哪条记录。
在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。
外键并不是通过列名实现的，而是通过定义外键约束实现的：

1
2
3
4
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。
通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。
由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。
要删除一个外键约束，也是通过ALTER TABLE实现的：

1
2
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

通过一个表的外键关联到另一个表，我们可以定义出一对多关系。有些时候，还需要定义“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。
多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系。
一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。 目的是把经常读取和不经常读取的字段分开，提高查询速度，以获得更高的性能。

小结
关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

3.3 索引

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。
索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。
如果要经常根据score列进行查询，就可以对score列创建索引：

1
2
ALTER TABLE students
ADD INDEX idx_score (score);

使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

1
2
ALTER TABLE students
ADD INDEX idx_name_score (name, score);

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。
可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。
对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

小结
通过对数据库表创建索引，可以提高查询速度。
通过创建唯一索引，可以保证某一列的值具有唯一性。
数据库索引对于用户和应用程序来说都是透明的。

四： 查询数据

在关系数据库中，最常用的操作就是查询。

4.1 基本查询

要查询数据库表的数据，我们使用如下的SQL语句：
SELECT * FROM <表名>
假设表名是students，要查询students表的所有行，我们用如下SQL语句：
SELECT * FROM students;。
使用SELECT * FROM students时，SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，本例中是students表。
该SQL将查询出students表的所有数据。注意：查询结果也是一个二维表，它包含列名和每一行的数据。

在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。
关键词 DISTINCT 用于返回唯一不同的值。语法：

1
SELECT DISTINCT 列名称 FROM 表名称

小结
使用SELECT(选择、挑选、选拔)查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据。SELECT查询的结果是一个二维表。

4.2 条件查询

SELECT语句可以通过WHERE（到哪里）条件来设定查询条件，查询结果是满足查询条件的记录。例如，要指定条件“分数在80分或以上的学生”，写成WHERE条件就是:
SELECT * FROM students WHERE score >= 80;。
其中，WHERE关键字后面的score >= 80就是条件。score是列名，该列存储了学生的成绩，因此，score >= 80就筛选出了指定条件的记录：
SELECT * FROM students WHERE score >= 80;。
因此，条件查询的语法就是：
SELECT * FROM <表名> WHERE <条件表达式>

条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。例如，符合条件“分数在80分或以上”，并且还符合条件“男生”，把这两个条件写出来：

  • 条件1：根据score列的数据判断：score >= 80；
  • 条件2：根据gender列的数据判断：gender = ‘M’，注意gender列存储的是字符串，需要用单引号括起来。

就可以写出WHERE条件：
SELECT * FROM students WHERE score >= 80 AND gender = 'M';。
第二种条件是<条件1> OR <条件2>，表示满足条件1或者满足条件2。例如：
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
第三种条件是NOT <条件>，表示“不符合该条件”的记录。例如：
SELECT * FROM students WHERE NOT class_id = 2;表示不是2班的学生。
要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。例如，编写一个复杂的条件：分数在80以下或者90以上，并且是男生：
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

常用的条件表达式：

条件 表达式举例1 表达式举例2 说明
使用=判断相等 score = 80 name = ‘abc’ 字符串需要用单引号括起来
使用>判断大于 score > 80 name > ‘abc’ 字符串比较根据ASCII码，中文字符比较根据数据库设置
使用>=判断大于或相等 score >= 80 name >= ‘abc’
使用<判断小于 score < 80 name <= ‘abc’
使用<=判断小于或相等 score <= 80 name <= ‘abc’
使用<>判断不相等 score <> 80 name <> ‘abc’
使用LIKE判断相似 name LIKE ‘ab%’ name LIKE ‘%bc%’ %表示任意字符，例如’ab%’将匹配’ab’，’abc’，’abcd’

小结
通过WHERE条件查询，可以筛选出符合指定条件的记录，而不是整个表的所有记录。

4.3 投影查询

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。
例如，从students表中返回id、score和name这三列：
SELECT id, score, name FROM students;
这样返回的结果集就只包含了我们指定的列，并且，结果集的列的顺序和原表可以不一样。

使用SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...。

例如，以下SELECT语句将列名score重命名为points，而id和name列名保持不变：SELECT id, score points, name FROM students;
投影查询同样可以接WHERE条件，实现复杂的查询：
SELECT id, score points, name FROM students WHERE gender = 'M';

小结
使用SELECT *表示查询表的所有列，使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影。SELECT语句可以对结果集的列进行重命名。

4.4 排序

可以加上ORDER BY子句。可以按照成绩从低到高进行排序：
SELECT id, name, gender, score FROM students ORDER BY score;
如果要反过来，按照成绩从高到底排序，我们可以加上DESC表示“倒序”：
SELECT id, name, gender, score FROM students ORDER BY score DESC;
如果score列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC和ORDER BY score效果一样。
如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查询一班的学生成绩，并按照倒序排序：

1
2
3
4
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

这样，结果集仅包含符合WHERE条件的记录，并按照ORDER BY的设定排序。
小结
使用ORDER BY可以对结果集进行排序；可以对多列进行升序、倒序排序。

4.5 分页查询

要实现分页功能，实际上就是从结果集中显示第1100条记录作为第1页，显示第101200条记录作为第2页，以此类推。
因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <M> OFFSET <N>子句实现。我们先把所有学生按照成绩从高到低进行排序，我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0： 

1
2
3
4
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。
如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：LIMIT 3 OFFSET 3;
LIMIT 3表示的意思是“最多3条记录”。

可见，分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

  • LIMIT总是设定为pageSize；
  • OFFSET计算公式为pageSize * (pageIndex - 1)。

这样就能正确查询出第N页的记录集。

PS:OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。
OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。
在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。
使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。

小结
使用LIMIT <M> OFFSET <N>可以对结果集进行分页，每次查询返回结果集的一部分；分页查询需要先确定每页的数量和当前页数，然后确定LIMIT和OFFSET的值。

4.6 聚合查询

4.6.1 聚合函数

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。
仍然以查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询：
SELECT COUNT(*) FROM students;
COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。
通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：
SELECT COUNT(*) num FROM students;
COUNT(*)和COUNT(id)实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
除了COUNT()函数外，SQL还提供了如下聚合函数：

函数 说明
SUM 计算某一列的合计值，该列必须为数值类型
AVG 计算某一列的平均值，该列必须为数值类型
MAX 计算某一列的最大值
MIN 计算某一列的最小值

注意，MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。
要统计男生的平均成绩，我们用下面的聚合查询：
SELECT AVG(score) average FROM students WHERE gender = 'M';
要特别注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL。
通过聚合查询获得总页数:
SELECT CEILING(COUNT(*) / 3) FROM students;

4.6.2 分组聚合

对于聚合查询，SQL还提供了“分组聚合”的功能。我们观察下面的聚合查询：
SELECT COUNT(*) num FROM students GROUP BY class_id;
执行这个查询，COUNT()的结果不再是一个，而是3个，这是因为，GROUP(组) BY子句指定了按class_id分组，因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算，因此，得到了3行结果。
但是这3行结果分别是哪三个班级的，不好看出来，所以我们可以把class_id列也放入结果集中：
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;这下结果集就可以一目了然地看出各个班级的学生人数。
也可以使用多个列进行分组。例如，我们想统计各班的男生和女生人数：
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
小结
使用SQL提供的聚合查询，我们可以方便地计算总数、合计值、平均值、最大值和最小值；聚合查询也可以添加WHERE条件。

4.7 多表查询

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>。
例如，同时从students表和classes表的“乘积”，即查询数据，可以这么写：SELECT * FROM students, classes;
这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。
这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

上述查询的结果集有两列id和两列name，两列id是因为其中一列是students表的id，而另一列是classes表的id，但是在结果集中，不好区分。两列name同理。要解决这个问题，我们仍然可以利用投影查询的“设置列的别名”来给两个表各自的id和name列起别名：

1
2
3
4
5
6
7
8
SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

注意，多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。但是，用表名.列名这种方式列举两个表的所有列实在是很麻烦，所以SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点：

1
2
3
4
5
6
7
8
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

注意到FROM子句给表设置别名的语法是FROM <表名1> <别名1>, <表名2> <别名2>。这样我们用别名s和c分别表示students表和classes表。
多表查询也是可以添加WHERE条件的：

1
2
3
4
5
6
7
8
9
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

小结
使用多表查询可以获取M x N行记录；多表查询的结果集可能非常巨大，要小心使用。

4.8 连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。
例如，我们想要选出students表的所有学生信息，可以用一条简单的SELECT语句完成： 

1
SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;

但是，假设我们希望结果集同时包含所在班级的名称，上面的结果集只有class_id列，缺少对应班级的name列。
现在问题来了，存放班级名称的name列存储在classes表中，只有根据students表的class_id，找到classes表对应的行，再取出name列，就可以获得班级名称。
这时，连接查询就派上了用场。我们先使用最常用的一种内连接——INNER JOIN来实现： 

1
2
3
4
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

注意INNER JOIN查询的写法是：

  1. 先确定主表，仍然使用FROM <表1>的语法；
  2. 再确定需要连接的表，使用INNER(里面的) JOIN <表2>的语法；
  3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
  4. 可选：加上WHERE子句、ORDER BY等子句。

那什么是内连接（INNER JOIN）呢？先别着急，有内连接（INNER JOIN）就有外连接（OUTER JOIN）。我们把内连接查询改成外连接查询，看看效果：

1
2
3
4
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

执行上述RIGHT OUTER JOIN可以看到，和INNER JOIN相比，RIGHT OUTER JOIN多了一行，多出来的一行是“四班”，但是，学生相关的列如name、gender、score都为NULL。
这也容易理解，因为根据ON条件s.class_id = c.id，classes表的id=4的行正是“四班”，但是，students表中并不存在class_id=4的行。
有RIGHT OUTER JOIN，就有LEFT OUTER JOIN，以及FULL OUTER JOIN。它们的区别是：

  • INNER JOIN只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。
  • RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。
  • LEFT OUTER JOIN则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL。

对于这么多种JOIN查询，到底什么使用应该用哪种呢？其实我们用图来表示结果集就一目了然了。假设查询语句是：
SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;
我们把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都存在的记录：
inner-join
LEFT OUTER JOIN是选出左表存在的记录：
left-outer-join
RIGHT OUTER JOIN是选出右表存在的记录：
right-outer-join
FULL OUTER JOIN则是选出左右表都存在的记录：
full-outer-join

小结
JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上； INNER JOIN是最常用的一种JOIN查询，它的语法是SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>； JOIN查询仍然可以使用WHERE条件和ORDER BY排序。

五： 修改数据

关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve、Update、Delete。其中，对于查询，我们已经详细讲述了SELECT语句的详细用法。
而对于增、删、改，对应的SQL语句分别是：

  • INSERT：插入新记录；
  • UPDATE：更新已有记录；
  • DELETE：删除已有记录。

5.1 INSERT

当我们需要向数据库表中插入一条新记录时，就必须使用INSERT语句。
INSERT语句的基本语法是：
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
例如，我们向students表插入一条新记录，先列举出需要插入的字段名称，然后在VALUES子句中依次写出对应字段的值：
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
注意到我们并没有列出id字段，也没有列出id字段对应的值，这是因为id字段是一个自增主键，它的值可以由数据库自己推算出来。此外，如果一个字段有默认值，那么在INSERT语句中也可以不出现。
要注意，字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。也就是说，可以写:
INSERT INTO students (score, gender, name, class_id) ...，但是对应的VALUES就得变成(80, 'M', '大牛', 2)。
还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(…)包含的一组值： 

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);
--
SELECT * FROM students;
```  

**小结**
**使用INSERT，我们就可以一次向一个表中插入一条或多条记录。**

## 5.2 UPDATE

如果要更新数据库表中的记录，我们就必须使用UPDATE语句。  
UPDATE语句的基本语法是：  
`UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;`  
例如，我们想更新students表id=1的记录的name和score这两个字段：  
`UPDATE students SET name='大牛', score=66 WHERE id=1;`  
注意到UPDATE语句的WHERE条件和SELECT语句的WHERE条件其实是一样的，因此完全可以一次更新多条记录：  
`UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;`  
在UPDATE语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：  
`UPDATE students SET score=score+10 WHERE score<80;`  
其中，SET score=score+10就是给当前行的score字段的值加上了10。  
如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。  
最后，要特别小心的是，UPDATE语句可以没有WHERE条件，例如：  
`UPDATE students SET score=60;`  
这时，整个表的所有记录都会被更新。所以，在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。  
在使用MySQL这类真正的关系数据库时，UPDATE语句会返回更新的行数以及WHERE条件匹配的行数。  

**小结**
**使用UPDATE，我们就可以一次更新表中的一条或多条记录。**

## 5.3 DELETE

如果要删除数据库表中的记录，我们可以使用DELETE语句。  
DELETE语句的基本语法是：  
`DELETE FROM <表名> WHERE ...;`
例如，我们想删除students表中id=1的记录，就需要这么写：  
`DELETE FROM students WHERE id=1;`  
注意到DELETE语句的WHERE条件也是用来筛选需要删除的行，因此和UPDATE类似，DELETE语句也可以一次删除多条记录：  
`DELETE FROM students WHERE id>=5 AND id<=7;`  
如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除。  
最后，要特别小心的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据：  
`DELETE FROM students;`  
这时，整个表的所有记录都会被删除。所以，在执行DELETE语句时也要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用DELETE删除。  
在使用MySQL这类真正的关系数据库时，DELETE语句也会返回删除的行数以及WHERE条件匹配的行数。  

**小结**
**使用DELETE，我们就可以一次删除表中的一条或多条记录。**

# 六： MySQL

安装完MySQL后，除了MySQL Server，即真正的MySQL服务器外，还附赠一个MySQL Client程序。MySQL Client是一个命令行客户端，可以通过MySQL Client登录MySQL，然后，输入SQL语句并执行。  
打开命令提示符，输入命令`mysql -u root -p`，提示输入口令。填入MySQL的root口令，如果正确，就连上了MySQL Server，同时提示符变为mysql>：  
输入exit断开与MySQL Server的连接并返回到命令提示符。
>MySQL Client的可执行程序是mysql，MySQL Server的可执行程序是mysqld。

在MySQL Client中输入的SQL语句通过TCP连接发送到MySQL Server。默认端口号是3306，即如果发送到本机MySQL Server，地址就是127.0.0.1:3306。  
也可以只安装MySQL Client，然后连接到远程MySQL Server。假设远程MySQL Server的IP地址是10.0.1.99，那么就使用-h指定IP或域名：  
`mysql -h 10.0.1.99 -u root -p 密码`  

**小结**
**命令行程序mysql实际上是MySQL客户端，真正的MySQL服务器程序是mysqld，在后台运行。**

## 6.1 管理MySQL

要管理MySQL，可以使用可视化图形界面[MySQL Workbench](https://dev.mysql.com/downloads/workbench/)。  
MySQL Workbench可以用可视化的方式查询、创建和修改数据库表，但是，归根到底，MySQL Workbench是一个图形客户端，它对MySQL的操作仍然是发送SQL语句并执行。因此，本质上，MySQL Workbench和MySQL Client命令行都是客户端，和MySQL交互，唯一的接口就是SQL。  
因此，MySQL提供了大量的SQL语句用于管理。虽然可以使用MySQL Workbench图形界面来直接管理MySQL，但是，很多时候，通过SSH远程连接时，只能使用SQL命令，所以，了解并掌握常用的SQL管理操作是必须的。  

### 6.1.1 数据库

在一个运行MySQL的服务器上，实际上可以创建多个数据库（Database）。要列出所有数据库，使用命令：  

```sql
SHOW DATABASES;

其中，information_schema、mysql、performance_schema和sys四个是系统库，不要去改动它们。其他的是用户创建的数据库。
要创建一个新数据库，使用命令： 

1
CREATE DATABASE test;

要删除一个数据库，使用命令： 

1
DROP DATABASE test;

注意：删除一个数据库将导致该数据库的所有表全部被删除。
对一个数据库进行操作时，要首先将其切换为当前数据库： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE test;
```  

### 6.1.2 表

列出当前数据库的所有表，使用命令：  

```sql
SHOW TABLES;
```  

要查看一个表的结构，使用命令：  

```sql
DESC students;

还可以使用以下命令查看创建表的SQL语句： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SHOW CREATE TABLE students;
```  

创建表使用CREATE TABLE语句，而删除表使用DROP TABLE语句：  

```sql
DROP TABLE students;
```  

修改表就比较复杂。如果要给students表新增一列birth，使用：  

```sql
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
```  

要修改birth列，例如把列名改为birthday，类型改为VARCHAR(20)：

```sql
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

要删除列，使用：

1
ALTER TABLE students DROP COLUMN birthday;

6.1.3 退出MySQL

使用EXIT命令退出MySQL：
EXIT
注意EXIT仅仅断开了客户端和服务器的连接，MySQL服务器仍然继续运行。

6.2 实用SQL语句

在编写SQL时，灵活运用一些技巧，可以大大简化程序逻辑。

一. 插入或替换
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

二. 插入或更新
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句：
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

三. 插入或忽略
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

四. 快照
如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT： 

1
2
-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:  
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

新创建的表结构和SELECT使用的表结构完全一致。

五. 写入查询结果集
如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。
例如，创建一个统计成绩的表statistics，记录各班的平均成绩： 

1
2
3
4
5
6
CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

然后，我们就可以用一条语句写入各班的平均成绩： 

1
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果： 

1
SELECT * FROM statistics;

六. 强制使用指定索引
在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。例如： 

1
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

指定索引的前提是索引idx_class_id必须存在。

七： 事务

在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。例如，一个转账操作： 

1
2
3
4
5
-- 从id=1的账户给id=2的账户转账100元
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

这两条SQL语句必须全部执行，或者，由于某些原因，如果第一条语句成功，第二条语句失败，就必须全部撤销。
这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。
可见，数据库事务具有ACID这4个特性：

  1. A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
  2. C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
  3. I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
  4. D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。
要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务，例如，把上述的转账操作作为一个显式事务：

1
2
3
4
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

很显然多条SQL语句要想作为一个事务执行，就必须使用显式事务。
COMMIT是指提交事务，即试图把事务内的所有SQL所做的修改永久保存。如果COMMIT语句执行失败了，整个事务也会失败。
有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败： 

1
2
3
4
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

数据库事务是由数据库系统保证的，我们只需要根据业务逻辑使用它就可以。

隔离级别

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。
SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

Isolation Level 脏读（Dirty Read） 不可重复读（Non Repeatable Read） 幻读（Phantom Read）
Read Uncommitted Yes Yes Yes
Read Committed - Yes Yes
Repeatable Read - - Yes
Serializable - - -

我们会依次介绍4种隔离级别的数据一致性问题。

小结
数据库事务具有ACID特性，用来保证多条SQL的全部执行。

7.1 Read Uncommitted

Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。
我们来看一个例子。首先，我们准备好students表的数据，该表仅一行记录。
然后，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2 BEGIN; BEGIN;
3 UPDATE students SET name = ‘Bob’ WHERE id = 1;
4 SELECT * FROM students WHERE id = 1;
5 ROLLBACK;
6 SELECT * FROM students WHERE id = 1;
7 COMMIT;

当事务A执行完第3步时，它更新了id=1的记录，但并未提交，而事务B在第4步读取到的数据就是未提交的数据。
随后，事务A在第5步进行了回滚，事务B再次读取id=1的记录，发现和上一次读取到的数据不一致，这就是脏读。
可见，在Read Uncommitted隔离级别下，一个事务可能读取到另一个事务更新但未提交的数据，这个数据有可能是脏数据。

7.2 Read Committed

在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。
不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。
我们仍然先准备好students表的数据，然后，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2 BEGIN; BEGIN;
3 SELECT * FROM students WHERE id = 1;
4 UPDATE students SET name = ‘Bob’ WHERE id = 1;
5 COMMIT;
6 SELECT * FROM students WHERE id = 1;
7 COMMIT;

当事务B第一次执行第3步的查询时，得到的结果是Alice，随后，由于事务A在第4步更新了这条记录并提交，所以，事务B在第6步再次执行同样的查询时，得到的结果就变成了Bob，因此，在Read Committed隔离级别下，事务不可重复读同一条记录，因为很可能读到的结果不一致。

7.3 Repeatable Read

在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。
幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。
我们仍然先准备好students表的数据，然后，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2 BEGIN; BEGIN;
3 SELECT * FROM students WHERE id = 99;
4 INSERT INTO students (id, name) VALUES (99, ‘Bob’);
5 COMMIT;
6 SELECT * FROM students WHERE id = 99;
7 UPDATE students SET name = ‘Alice’ WHERE id = 99;
8 SELECT * FROM students WHERE id = 99;
9 COMMIT;

事务B在第3步第一次读取id=99的记录时，读到的记录为空，说明不存在id=99的记录。随后，事务A在第4步插入了一条id=99的记录并提交。事务B在第6步再次读取id=99的记录时，读到的记录仍然为空，但是，事务B在第7步试图更新这条不存在的记录时，竟然成功了，并且，事务B在第8步再次读取id=99的记录时，记录出现了。
可见，幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

7.4 Serializable

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。
虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

默认隔离级别
如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。