MySQL 基础学习笔记

我的MySQL学习笔记，记录一些MySQL的基础知识。目录在下面：

SQL 语句与种类

• DDL（Data Definition Language）数据库定义语言：如 create drop alter
• DML（Data Manipulation Language）数据操纵语言：如 select insert update delete
• DCL（Data Control Language）数据控制语言：如 commit rollback grant revoke

我们使用的 SQL 语句中，有 90% 属于 DML

MySQL 数据类型

整数类型：

浮点类型：

定点数类型：

日期和时间类型：

字符串类型：

枚举类型ENUM：它表示在给定的字符串列表里选择一个

ENUM('str1', 'str2', 'str3' ⋯)

SET类型，表示在给定的字符串列表里选择多个：

SET('str1', 'str2', 'str3' ⋯)

二进制类型：

数据库的基本操作

• 展示数据库：SHOW DATABASES;
• 创建数据库：CREATE DATABASE 数据库名;，可以在创建前先判断是否存在 CREATE DATABASE IF NOT EXISTS 数据库名;
• 切换数据库：USE 数据库名称;
• 删除数据库：DROP DATABASE 数据库名;，可以先判断是否存在：DROP DATABASE IF EXISTS 数据库名;

表的基本操作

• 展示数据库中有哪些表：SHOW TABLES;

• 创建表：

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  CREATE TABLE 表名 ( 列名1 数据类型 [列的属性], 列名2 数据类型 [列的属性], ... 列名n 数据类型 [列的属性] ); CREATE TABLE 表名 ( 各个列的信息 ... ) COMMENT '表的注释信息'; CREATE TABLE IF NOT EXISTS 表名( 各个列的信息 ... );

• 删除表：DROP TABLE 表1, 表2, ..., 表n;

• 查看表结构：

  1 2 3 4 5

  DESCRIBE 表名; DESC 表名; EXPLAIN 表名; SHOW COLUMNS FROM 表名; SHOW FIELDS FROM 表名;

查看表创建语句:

1
2

SHOW CREATE TABLE 表名; 
SHOW CREATE TABLE 表名\G; # 显示效果好点

使用utf8字符集建库

utf8字符集是残缺的只有三个字节，utf8mb4 占4个字节，里面可以存储所有的utf8字符。

1
2

create database xiaohaizi character set utf8mb4;
create database IF NOT EXISTS xiaohaizi character set utf8mb4; # 不存在才建立

修改表名

1
2

ALTER TABLE 旧表名 RENAME TO 新表名;
RENAME TABLE 旧表名1 TO 新表名1, 旧表名2 TO 新表名2, ... 旧表名n TO 新表名n;

还可以把一个数据库中的表转移到另一个数据库中：

1

RENAME TABLE dahaizi.first_table1 TO xiaohaizi.first_table;

增加表中的列

1
2
3
4

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [列的属性];

例子：
ALTER TABLE first_table ADD COLUMN third_column CHAR(4) ;

默认mysql会添加在最后一列，如果想要添加到第一列，可以这样：

1

ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [列的属性] FIRST;

添加在指定列名的后面：

1

ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [列的属性] AFTER 指定列名;

删除列

1

ALTER TABLE 表名 DROP COLUMN 列名;

修改列的信息：

方式1：

1

ALTER TABLE 表名 MODIFY 列名 新数据类型 [新属性];

注意：修改后的数据类型和属性一定要兼容表中现有的数据

方式2：

这种修改方式需要我们填两个列名，也就是说在修改数据类型和属性的同时也可以修改列名

1

ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型 [新属性];

修改列排列位置

将列设为表的第一列：

1

ALTER TABLE 表名 MODIFY 列名 列的类型 列的属性 FIRST;

将列放到指定列的后边：

1

ALTER TABLE 表名 MODIFY 列名 列的类型 列的属性 AFTER 指定列名;

一条语句中包含多个修改操作

如果对同一个表有多个修改操作的话，我们可以把它们放到一条语句中执行，就像这样：

1

ALTER TABLE 表名 操作1, 操作2, ..., 操作n;

列的属性

简单查询语句：

1

SELECT * FROM 表名;

简单插入语句：

1
2
3

INSERT INTO 表名(列1, 列2, ...) VALUES(列1的值，列2的值, ...);
# 批量插入
INSERT INTO 表名(列1,列2, ...) VAULES(列1的值，列2的值, ...), (列1的值，列2的值, ...), (列1的值，列2的值, ...), ...;

默认值

在书写INSERT语句插入记录的时候可以只指定部分的列，那些没有被显式指定的列的值将被设置为NULL，换一种说法就是列的默认值为NULL，NULL的含义是这个列的值还没有被设置。如果我们不想让默认值为NULL，而是设置成某个有意义的值，可以在定义列的时候给该列增加一个DEFAULT属性，就像这样：

1

列名 列的类型 DEFAULT 默认值

NOT NULL属性

有时候我们需要要求表中的某些列中必须有值，不能存放NULL，那么可以用这样的语法来定义这个列：

列名 列的类型 NOT NULL

主键

有时候在我们的表里可以通过某个列或者某些列确定唯一的一条记录，我们就可以把这个列或者这些列称为候选键。比如在学生信息表student_info中，只要我们知道某个学生的学号，就可以确定一个唯一的学生信息，也就是一条记录。当然，我们也可以通过身份证号来确定唯一的一条学生信息记录，所以学号和身份证号都可以作为学生信息表的候选键。在学生成绩表student_score中，我们可以通过学号和科目这两个列的组合来确定唯一的一条成绩记录，所以学号、科目这两个列的组合可以作为学生成绩表的候选键。

一个表可能有多个候选键，我们可以选择一个候选键作为表的主键。一个表最多只能有一个主键，主键的值不能重复，通过主键可以找到唯一的一条记录

主键可以通过两种方式进行声明：

1. 如果主键只是单个列的话，可以直接在该列后声明PRIMARY KEY

2. 也可以把主键的声明单独提取出来，用这样的形式声明：

   1	PRIMARY KEY (列名1, 列名2, ...)

   对于多个列的组合作为主键的情况，必须使用这种单独声明的形式

主键默认具有 NOT NULL 属性。

UNIQUE属性

对于不是主键的其他候选键，如果也想让MySQL在我们向表中插入新记录的时候帮助我们校验一下某个列或者列组合的值是否重复，那么我们可以把这个列或列组合添加一个UNIQUE属性，表明该列或者列组合的值是不允许重复的。

声明方式也是两种：

1. 如果我们想为单个列声明UNIQUE属性，可以直接在该列后填写UNIQUE或者UNIQUE KEY

2. 也可以把UNIQUE属性的声明单独提取出来

   UNIQUE [约束名称] (列名1, 列名2, ...)
   或
   UNIQUE KEY [约束名称] (列名1, 列名2, ...)
   

如果表中为某个列或者列组合定义了UNIQUE属性的话，MySQL会对我们插入的记录做校验，如果新插入记录在该列或者列组合的值已经在表中存在了，那就会报错！

主键和UNIQUE约束的区别

1. 一张表中只能定义一个主键，却可以定义多个UNIQUE约束！
2. 规定：主键列不允许存放NULL，而声明了UNIQUE属性的列可以存放NULL，而且NULL可以重复地出现在多条记录中！

外键

定义外键的语法：

1

CONSTRAINT [外键名称] FOREIGN KEY(列1, 列2, ...) REFERENCES 父表名(父列1, 父列2, ...);

其中的外键名称也是可选的。如果A表中的某个列或者某些列依赖与B表中的某个列或者某些列，那么就称A表为子表，B表为父表。子表和父表可以使用外键来关联起来。

父表中被子表依赖的列或者列组合必须建立索引，如果该列或者列组合已经是主键或者有UNIQUE属性，那么它们也就被默认建立了索引。示例中student_score表依赖于stuent_info表的number列，而number列又是stuent_info的主键（注意上一章定义的student_info结构中没有把number列定义为主键，本章才将其定义为主键，如果你的机器上还没有将其定义为主键的话，赶紧修改表结构呗～），所以在student_score表中创建外键是没问题的。

AUTO_INCREMENT属性

AUTO_INCREMENT翻译成中文可以理解为自动增长，简称自增。如果一个表中的某个列的数据类型是整数类型或者浮点数类型，那么这个列可以设置AUTO_INCREMENT属性。当我们把某个列设置了AUTO_INCREMENT属性之后，如果我们在插入新记录的时候不指定该列的值，或者将该列的值显式地指定为NULL或者0，那么新插入的记录在该列上的值就是当前该列的最大值加1后的值，定义语法为：

列名 列的类型 AUTO_INCREMENT

列id是从1开始递增的。在为列定义AUTO_INCREMENT属性的时候需要注意这几点：

1. 一个表中最多有一个具有AUTO_INCREMENT属性的列。
2. 具有AUTO_INCREMENT属性的列必须建立索引。主键和具有UNIQUE属性的列会自动建立索引。不过至于什么是索引，在学习MySQL进阶的时候才会介绍。
3. 拥有AUTO_INCREMENT属性的列就不能再通过指定DEFAULT属性来指定默认值。
4. 一般拥有AUTO_INCREMENT属性的列都是作为主键的属性，来自动生成唯一标识一条记录的主键值。

列的注释

上一章中我们说了在建表语句的末尾可以添加COMMENT语句来给表添加注释，其实我们也可以在每一个列末尾添加COMMENT语句来为列来添加注释，比方说：

CREATE TABLE first_table (
    id int UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    first_column INT COMMENT '第一列',
    second_column VARCHAR(100) DEFAULT 'abc' COMMENT '第二列'
) COMMENT '第一个表';

影响展示外观的ZEROFILL属性

下边是正整数3的三种写法：

• 写法一：3
• 写法二：003
• 写法三：000003

对于无符号整数类型的列，我们可以在查询数据的时候让数字左边补0，如果想实现这个效果需要给该列加一个ZEROFILL属性（也可以理解为这是一个属于数据类型的属性），就像这样：

mysql> CREATE TABLE zerofill_table (
    ->     i1 INT UNSIGNED ZEROFILL,
    ->     i2 INT UNSIGNED
    -> );
Query OK, 0 rows affected (0.03 sec)

显示宽度。显示宽度是在查询语句显示的结果中，如果声明了 ZEROFILL 属性的整数列的实际值的位数小于显示宽度时，会在实际值的左侧补0，使补0的位数和实际值的位数相加正好等于显示宽度。我们也可以自己指定显示宽度

• 在展示查询结果时，某列数据自动补0的条件有这几个：

  • 该列必须是整数类型的
  • 该列必须有UNSIGNED ZEROFILL的属性
  • 该列的实际值的位数必须小于显示宽度

• 在创建表的时候，如果声明了ZEROFILL属性的列没有声明UNSIGNED属性，那MySQL会为该列自动生成UNSIGNED属性。

  也就是说如果我们创建表语句是这样的：

  CREATE TABLE zerofill_table (
      i1 INT ZEROFILL,
      i2 INT UNSIGNED
  );
  

  MySQL会自动帮我们为i1列加上UNSIGNED属性，也就是这样：

  CREATE TABLE zerofill_table (
      i1 INT UNSIGNED ZEROFILL,
      i2 INT UNSIGNED
  );
  

  也就是说MySQL现在只支持对无符号整数进行自动补0的操作。

• 每个整数类型都会有默认的显示宽度。

  比如TINYINT的默认显示宽度是4，INT的默认显示宽度是(11)… 如果加了UNSIGNED属性，则该类型的显示宽度减1，比如TINYINT UNSIGNED的显示宽度是3，INT UNSIGNED的显示宽度是10。

• 显示宽度并不会影响实际类型的实际存储空间。

  显示宽度仅仅是在展示查询结果时，如果整数的位数不够显示宽度的情况下起作用的，并不影响该数据类型要求的存储空间以及该类型能存储的数据范围，也就是说INT(1)和INT(10)仅仅在展示时可能有区别，在别的方面没有任何区别。比方说zerofill_table表中i1列的显示宽度是5，而数字12345678的位数是8，它照样可以被填入i1列中：

  mysql> INSERT INTO zerofill_table(i1, i2) VALUES(12345678, 12345678);
  Query OK, 1 row affected (0.01 sec)
  
  mysql>
  

• 只有列的实际值的位数小于显示宽度时才会补0，实际值的位数大于显示宽度时照原样输出。

  比方说我们刚刚把12345678存到了i1列里，在展示这个值时，并不会截短显示的数据，而是照原样输出：

  mysql> SELECT * FROM zero_table;
  +----------+----------+
  | i1       | i2       |
  +----------+----------+
  |    00001 |        1 |
  | 12345678 | 12345678 |
  +----------+----------+
  2 rows in set (0.00 sec)
  
  mysql>
  

• 对于没有声明ZEROFILL属性的列，显示宽度没有一毛钱卵用。

  只有在查询声明了ZEROFILL属性的列时，显示宽度才会起作用，否则忽略显示宽度这个东西的存在。

一个列同时具有多个属性

每个列可以同时具有多个属性，属性声明的顺序无所谓，各个属性之间用空白隔开就好了～

查看表结构时的列属性

mysql> DESC student_info;
+-----------------+-------------------+------+-----+---------+-------+
| Field           | Type              | Null | Key | Default | Extra |
+-----------------+-------------------+------+-----+---------+-------+
| number          | int(11)           | NO   | PRI | NULL    |       |
| name            | varchar(5)        | YES  |     | NULL    |       |
| sex             | enum('男','女')   | YES  |     | NULL    |       |
| id_number       | char(18)          | YES  | UNI | NULL    |       |
| department      | varchar(30)       | YES  |     | NULL    |       |
| major           | varchar(30)       | YES  |     | NULL    |       |
| enrollment_time | date              | YES  |     | NULL    |       |
+-----------------+-------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql>

可以看到：

• NULL列代表该列是否可以存储NULL，值为NO时，表示不允许存储NULL，值为YES是表示可以存储NULL。
• Key列存储关于所谓的键的信息，当值为PRI是PRIMARY KEY的缩写，代表主键；UNI是UNIQUE KEY的缩写，代表UNIQUE属性。
• Default列代表该列的默认值。
• Extra列展示一些额外的信息。比方说如果某个列具有AUTO_INCREMENT属性就会被展示在这个列里。

简单查询

查询单个列

1

SELECT 列名 FROM 表名;

列的别名

我们也可以为结果集中的列重新定义一个别名，命令格式如下：

1

SELECT 列名 [AS] 列的别名 FROM 表名;

查询多个列

如果想查询多个列的数据，可以在SELECT后边写多个列名，用逗号,分隔开就好：

1

SELECT 列名1, 列名2, ... 列名n FROM 表名;

我们把SELECT语句后边跟随的多个列统称为查询列表，需要注意的是，查询列表中的列名可以按任意顺序摆放，结果集将按照我们指定的列名顺序显示

查询所有列

1

SELECT * FROM 表名;

需要注意的是，除非你确实需要表中的每个列，否则一般最好别使用星号*来查询所有列，虽然星号*看起来很方便，不用明确列出所需的列，但是查询不需要的列通常会降低性能。

查询结果去重

去除单列的重复结果

1

SELECT DISTINCT 列名 FROM 表名;

去除多列的重复结果

1

SELECT DISTINCT 列名1, 列名2, ... 列名n  FROM 表名;

限制查询结果条数

有时候查询结果的条数会很多，都显示出来可能会撑爆屏幕～ 所以MySQL给我们提供了一种限制结果集中的记录条数的方式，就是在查询语句的末尾使用这样的语法：

1

LIMIT 开始行, 限制条数;

开始行指的是我们想从第几行数据开始查询，限制条数是结果集中最多包含多少条记录。

LIMIT 后面也可以直接跟数字，代表开始行为 0

对查询结果排序

按照单个列的值进行排序

我们可以用下边的语法来指定返回结果的记录按照某一列的值进行排序：

1

ORDER BY 列名 ASC|DESC

• ASC和DESC指的是排序方向。

• ASC是指按照指定列的值进行由小到大进行排序，也叫做升序

• DESC是指按照指定列的值进行由大到小进行排序，也叫做降序，中间的|表示这两种方式只能选一个

按照多个列的值进行排序

1

ORDER BY 列1 ASC|DESC, 列2 ASC|DESC ...

• 如果不指定排序方向，则默认使用的是ASC，也就是从小到大的升序规则。

带搜索条件的查询

使用 where 子句

形如：SELECT number, name, id_number, major FROM student_info WHERE name = '范剑';

MySQL还提供了很多别的比较操作符，比如：

匹配列表中的元素：

匹配NULL值

多个搜索条件

使用 AND 或者 OR 操作符吗，其中 AND 的优先级比较大

通配符

通常用来进行模糊查询

MySQL支持两种通配符：

1. %：代表任意一个字符串。
2. _：代表任意一个字符。

如果待匹配的字符包含 % 或者 _ ，则可以使用 \ 进行转义

表达式和函数

操作数

MySQL中操作数可以是下边这几种类型：

1. 常数

   常数很好理解，我们平时用到的数字、字符串、时间值什么的都可以被称为常数，它是一个确定的值，比如数字1，字符串'abc'，时间值2019-08-16 17:10:43啥的。

2. 列名

   针对某个具体的表，它的列名可以被当作表达式的一部分，比如对于student_info表来说，number、name都可以作为操作数。

3. 函数调用

   MySQL中有函数的概念，比方说获取当前时间的函数NOW，而在函数后边加个小括号就算是一个函数调用，比如NOW()。

4. 标量子查询或者行子查询

5. 其他表达式

   一个表达式也可以作为一个操作数与另一个操作数来形成一个更复杂的表达式，比方说（假设col是一个列名）：

   • (col - 5) / 3
   • (1 + 1) * 2 + col * 3

操作符

1. 算术操作符

   操作符	示例	描述
   +	a + b	加法
   -	a - b	减法
   *	a * b	乘法
   /	a / b	除法
   DIV	a DIV b	除法，取商的整数部分
   %	a % b	取余
   -	-a	负号

   在使用MySQL中的算术操作符时需要注意，DIV和/都表示除法操作符，但是DIV只会取商的整数部分，/会保留商的小数部分。比如表达式 2 DIV 3的结果是0，而2 / 3的结果是0.6667。

2. 比较操作符

   操作符	示例	描述
   =	a = b	a等于b
   <>或者!=	a <> b	a不等于b
   <	a < b	a小于b
   <=	a <= b	a小于或等于b
   >	a > b	a大于b
   >=	a >= b	a大于或等于b
   BETWEEN	a BETWEEN b AND c	满足 b <= a <= c
   NOT BETWEEN	a NOT BETWEEN b AND c	不满足 b <= a <= c
   IN	a IN (b1, b2, ...)	a是b1, b2, … 中的某一个
   NOT IN	a NOT IN (b1, b2, ...)	a不是b1, b2, … 中的任意一个
   IS NULL	a IS NULL	a的值是NULL
   IS NOT NULL	a IS NOT NULL	a的值不是NULL
   LIKE	a LIKE b	a匹配b
   NOT LIKE	a NOT LIKE b	a不匹配b

   由比较操作符连接而成的表达式也称为布尔表达式，表示真或者假，也可以称为TRUE或者FALSE。比如1 > 3就代表FALSE，3 != 2就代表TRUE。

3. 逻辑操作符

   操作符	示例	描述
   AND	a AND b	只有a和b同时为真，表达式才为真
   OR	a OR b	只要a或b有任意一个为真，表达式就为真
   XOR	a XOR b	a和b有且只有一个为真，表达式为真

表达式的使用

只要把这些操作数和操作符相互组合起来就可以组成一个表达式。表达式主要以下边这两种方式使用：

1. 放在查询列表中

   我们前边都是将列名放在查询列表中的(*号代表所有的列名～)。列名只是表达式中超级简单的一种，我们可以将任意一个表达式作为查询列表的一部分来处理，比方说我们可以在查询student_score表时把score字段的数据都加100，就像这样：

   1	SELECT number, subject, score + 100 FROM student_score;

   需要注意的是，放在查询列表的表达式也可以不涉及列名，就像这样：

   1	SELECT 1 FROM student_info;

2. 作为搜索条件

   搜索条件也可以不带列名，比如这样：

   1	SELECT number, name, id_number, major FROM student_info WHERE 2 > 1;

函数

在使用MySQL过程中经常会有一些需求，比方说将给定文本中的小写字母转换成大写字母，把某个日期数据中的月份值提取出来等等。为了解决这些常遇到的问题，MySQL提供了很多所谓的函数：

• UPPER函数是用来把给定的文本中的小写字母转换成大写字母。
• MONTH函数是用来把某个日期数据中的月份值提取出来。
• NOW函数用来获取当前的日期和时间。

文本处理函数：

日期和时间处理函数：

在使用DATE_ADD和DATE_SUB这两个函数时需要注意，增加或减去的时间间隔单位可以自己定义，下边是MySQL支持的一些时间单位：

如果我们相让2019-08-16 17:10:43这个时间值增加2分钟，可以这么写：

1

SELECT DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 MINUTE);

在使用DATE_FORMAT函数时需要注意，我们可以通过一些所谓的格式符来自定义日期和时间的显示格式，下边是MySQL中常用的一些日期和时间的格式符以及它们对应的含义：

可以把我们想要的显示格式用对应的格式符描述出来，就像这样：

1

SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');

'%b %d %Y %h:%i %p'就是一个用格式符描述的显示格式，意味着对应的日期和时间应该以下边描述的方式展示：

• 先输出简写的月份名称（格式符%b），也就是示例中的Aug，然后输出一个空格。
• 再输出用数字格式表示的的月份中的日期（格式符%d），也就是示例中的16，然后输出一个空格。
• 再输出4位数字形式的年（格式符%Y），也就是示例中的2019，然后输出一个空格。
• 再输出十二小时制的小时（格式符%h），也就是示例中的05，然后输出一个冒号:。
• 再输出数值格式的分钟（格式符%i），也就是示例中的10，然后输出一个空格。
• 最后输出上午或者下午（格式符%p），也就是示例中的PM。

数值处理函数

聚集函数

如果将上边介绍的那些函数以函数调用的形式放在查询列表中，那么会为表中符合WHERE条件的每一条记录调用一次该函数。

有些函数是用来统计数据的，比方说统计一下表中的行数，某一列数据的最大值是什么，我们把这种函数称之为聚集函数，下边介绍MySQL中常用的几种聚集函数：

COUNT函数使用来统计行数的，它有下边两种使用方式：

1. COUNT(*)：对表中行的数目进行计数，不管列的值是不是NULL。
2. COUNT(列名)：对特定的列进行计数，会忽略掉该列为NULL的行。

两者的区别是会不会忽略统计列的值为NULL的行！

聚集函数中DISTINCT的使用

默认情况下，上边介绍的聚集函数将计算指定列的所有非NULL数据，如果我们指定的列中有重复数据的话，可以选择使用DISTINCT来过滤掉这些重复数据。比方说我们想查看一下student_info表中存储了多少个专业的学生信息，就可以这么写：

1

SELECT COUNT(DISTINCT major) FROM student_info;

组合聚集函数

这些聚集函数也可以集中在一个查询中使用，比如这样：

1

SELECT COUNT(*) AS 成绩记录总数, MAX(score) AS 最高成绩, MIN(score) AS 最低成绩, AVG(score) AS 平均成绩 FROM student_score;

隐式类型转换

只要某个值的类型与上下文要求的类型不符，MySQL就会根据上下文环境中需要的类型对该值进行类型转换，由于这些类型转换都是MySQL自动完成的，所以也可以被称为隐式类型转换。我们列举几种常见的隐式类型转换的场景：

1. 把操作数类型转换为适合操作符计算的相应类型。

   比方说对于加法操作符+来说，它要求两个操作数都必须是数字才能进行计算，所以如果某个操作数不是数字的话，会将其隐式转换为数字，比方说下边这几个例子：

   1 + 2       →   3
   '1' + 2     →   3
   '1' + '2'   →   3
   

   虽然'1'、'2'都是字符串，但是如果它们作为加法操作符+的操作数的话，都会被强制转换为数字，所以上边几个表达式其实都会被当作1 + 2去处理的

2. 将函数参数转换为该函数期望的类型。

   我们拿用于拼接字符串的CONCAT函数举例，这个函数以字符串类型的值作为参数，如果我们在调用这个函数的时候，传入了别的类型的值作为参数，MySQL会自动把这些值的类型转换为字符串类型的：

   CONCAT('1', '2')    →   '12'
   CONCAT('1', 2)      →   '12'
   CONCAT(1, 2)        →   '12'
   

   虽然1、2都是数字，但是如果它们作为CONCAT函数的参数的话，都会被强制转换为字符串，所以上边几个表达式其实都会被当作CONCAT('1', '2)去处理的

3. 存储数据时，把某个值转换为某个列需要的类型。

类型转换的注意事项

1. MySQL会尽量把值转换为表达式中需要的类型，而不是产生错误。

   按理说'23sfd'这个字符串无法转换为数字，但是MySQL规定只要字符串的开头部分包含数字，那么就把这个字符串转换为开头的数字，如果开头并没有包含数字，那么将被转换成0，比方说这样：

   '23sfd'         →   23
   '2019-08-28'    →   2019
   '11:30:32'      →   11
   'sfd'           →   0
   

   不过需要注意的是，这种强制转换不能用于存储数据中

2. 在运算时会自动提升操作数的类型。比如TINYINT最大能表示的数字是127，如果两个相加就会爆，所以MySQL会自动提升操作数的类型，让结果不爆。MySQL自动将整数类型的操作数提升到了BIGINT，这样就不会产生运算结果太大超过TINYINT能表示的数值范围的尴尬情况了。类似的，有浮点数的运算过程会把操作数自动转型为DOUBLE类型。

分组查询

分组的概念，就是：针对某个列，将该列的值相同的记录分到一个组中。

创建分组

subject列中有多少不重复的课程，那就会有多少个分组。幸运的是，只要我们在GROUP BY子句中添加上分组列就好了，MySQL会帮助我们自动建立分组来方便我们统计信息，具体语句如下：

1
2
3
4
5
6
7
8

mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject;
+-----------------------------+------------+
| subject                     | AVG(score) |
+-----------------------------+------------+
| 母猪的产后护理              |    73.0000 |
| 论萨达姆的战争准备          |    73.2500 |
+-----------------------------+------------+
2 rows in set (0.01 sec)

这个查询的执行过程就是按照subject中的值将所有的记录分成两组，然后分别对每个分组中记录的score列调用AVG函数进行数据统计。

在使用分组的时候必须要意识到，分组的存在仅仅是为了方便我们分别统计各个分组中的信息，所以我们只需要把分组列和聚集函数放到查询列表处就好！当然，如果非分组列出现在查询列表中会出现什么情况呢？比如下边这个查询：

1
2

mysql> SELECT number, subject, AVG(score) FROM student_score GROUP BY subject;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xiaohaizi.student_score.number' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

可以看到出现了错误。为啥会错误呢？

本例中的查询列表处放置了既非分组列、又非聚集函数的number列。这样会产生不确定性，所以MySQL中不允许。

带有WHERE子句的分组查询

这时就需要使用WHERE子句了。比如老师觉得各个科目的平均分太低了，所以想先把分数低于60分的记录去掉之后再统计平均分，就可以这么写：

1
2
3
4
5
6
7
8

mysql> SELECT subject, AVG(score) FROM student_score WHERE score >= 60 GROUP BY subject;
+-----------------------------+------------+
| subject                     | AVG(score) |
+-----------------------------+------------+
| 母猪的产后护理              |    89.0000 |
| 论萨达姆的战争准备          |    82.3333 |
+-----------------------------+------------+
2 rows in set (0.00 sec)

整个过程是，首先过滤掉不符合where条件的记录，然后再进行分组。

作用于分组的过滤条件

HAVING子句，当分组有很多不重复的值时，我们需要的结果中并不需要这么多，我们只想把符合条件的分组加入到结果集。

1
2
3
4
5
6
7

mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING AVG(score) > 73;
+-----------------------------+------------+
| subject                     | AVG(score) |
+-----------------------------+------------+
| 论萨达姆的战争准备          |    73.2500 |
+-----------------------------+------------+
1 row in set (0.00 sec)

其实这里所谓的针对分组的条件一般是指下边这两种：

• 分组列

  也就是说我们可以把用于分组的列放到HAVING子句的条件中，比如这样：

  1	SELECT subject, AVG(score) FROM student_score GROUP BY subject having subject = '母猪的产后护理';

• 作用于分组的聚集函数

  当然，并不是HAVING子句中只能放置在查询列表出现的那些聚集函数，只要是针对这个分组进行统计的聚集函数都可以，比方说老师想查询最高分大于98分的课程的平均分，可以这么写：

  1 2 3 4 5 6 7 8 9

  mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING MAX(score) > 98; +-----------------------+------------+ | subject | AVG(score) | +-----------------------+------------+ | 母猪的产后护理 | 73.0000 | +-----------------------+------------+ 1 row in set (0.00 sec) mysql>

  其中的MAX(score)这个聚集函数并没有出现在查询列表中，但仍然可以作为HAVING子句中表达式的一部分。

分组和排序

如果我们想对各个分组查询出来的统计数据进行排序，需要为查询列表中有聚集函数的表达式添加别名，比如想按照各个学科的平均分从大到小降序排序，可以这么写：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysql> SELECT subject, AVG(score) AS avg_score FROM student_score GROUP BY subject ORDER BY avg_score DESC;
+-----------------------------+-----------+
| subject                     | avg_score |
+-----------------------------+-----------+
| 论萨达姆的战争准备          |   73.2500 |
| 母猪的产后护理              |   73.0000 |
+-----------------------------+-----------+
2 rows in set (0.01 sec)

mysql>

嵌套分组

有时候按照某个列进行分组太笼统，一个分组内可以被继续划分成更小的分组。比方说对于student_info表来说

表结构如下：

+----------+--------+-----+--------------------+------------+------------------+-----------------+
| number   | name   | sex | id_number          | department | major            | enrollment_time |
+----------+--------+-----+--------------------+------------+------------------+-----------------+
| 20180101 | 杜子腾 | 男  | 158177199901044792 | 计算机学院 | 计算机科学与工程 | 2018-09-01      |
| 20180102 | 杜琦燕 | 女  | 151008199801178529 | 计算机学院 | 计算机科学与工程 | 2018-09-01      |
| 20180103 | 范统   | 男  | 17156319980116959X | 计算机学院 | 软件工程         | 2018-09-01      |
| 20180104 | 史珍香 | 女  | 141992199701078600 | 计算机学院 | 软件工程         | 2018-09-01      |
| 20180105 | 范剑   | 男  | 181048199308156368 | 航天学院   | 飞行器设计       | 2018-09-01      |
| 20180106 | 朱逸群 | 男  | 197995199501078445 | 航天学院   | 电子信息         | 2018-09-01      |
+----------+--------+-----+--------------------+------------+------------------+-----------------+

如果先按照 department 分组，再按照 major 分组，则为：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT department, major, COUNT(*) FROM student_info GROUP BY department, major;
结果为：
+------------+------------------+----------+
| department | major            | COUNT(*) |
+------------+------------------+----------+
| 航天学院   | 电子信息         | 1        |
| 航天学院   | 飞行器设计       | 1        |
| 计算机学院 | 计算机科学与工程 | 2        |
| 计算机学院 | 软件工程         | 2        |
+------------+------------------+----------+

使用分组注意事项

使用分组来统计数据给我们带来了非常大的便利，但是要随时提防有坑的地方：

1. 如果分组列中含有NULL值，那么NULL也会作为一个独立的分组存在。

2. 如果存在多个分组列，也就是嵌套分组，聚集函数将作用在最后的那个分组列上。

3. 如果查询语句中存在WHERE子句和ORDER BY子句，那么GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

4. 非分组列不能单独出现在检索列表中(可以被放到聚集函数中)。

5. GROUP BY子句后也可以跟随表达式(但不能是聚集函数)。

   上边介绍的GROUP BY后跟随的都是表中的某个列或者某些列，其实一个表达式也可以，比如这样：

   1 2 3 4 5 6 7 8 9 10 11 12

   mysql> SELECT concat('专业：', major), COUNT(*) FROM student_info GROUP BY concat('专业：', major); +-----------------------------------+----------+ | concat('专业：', major) | COUNT(*) | +-----------------------------------+----------+ | 专业：电子信息 | 1 | | 专业：计算机科学与工程 | 2 | | 专业：软件工程 | 2 | | 专业：飞行器设计 | 1 | +-----------------------------------+----------+ 4 rows in set (0.00 sec) mysql>

   MySQL会根据这个表达式的值来对记录进行分组，使用表达式进行分组的时候需要特别注意，查询列表中的表达式和GROUP BY子句中的表达式必须完全一样。不过一般情况下我们也不会用表达式进行分组，所以目前基本没啥用～

6. WHERE子句和HAVING子句的区别。

   WHERE子句在分组前进行过滤，作用于每一条记录，WHERE子句过滤掉的记录将不包括在分组中。而HAVING子句在数据分组后进行过滤，作用于整个分组。

简单查询语句中各子句的顺序

我们上边介绍了查询语句的各个子句，但是除了SELECT之外，其他的子句全都是可以省略的。如果在一个查询语句中出现了多个子句，那么它们之间的顺序是不能乱放的，顺序如下所示：

1
2
3
4
5
6
7

SELECT [DISTINCT] 查询列表
[FROM 表名]
[WHERE 布尔表达式]
[GROUP BY 分组列表 ]
[HAVING 分组过滤条件]
[ORDER BY 排序列表]
[LIMIT 开始行, 限制条数]

其中中括号[]中的内容表示可以省略，我们在书写查询语句的时候各个子句必须严格遵守这个顺序，不然会报错的！

子查询

标量子查询

主要用于合并多个SQL语句，必须要查一个人的各科成绩，需要学号，而学号在另一个表

mysql root@(none):xiaohaizi> select * from student_score where number = (select number from student_info where name = '杜琦燕') ; 
+----------+--------------------+-------+
| number   | subject            | score |
+----------+--------------------+-------+
| 20180102 | 母猪的产后护理     | 100   |
| 20180102 | 论萨达姆的战争准备 | 98    |
+----------+--------------------+-------+
2 rows in set
Time: 0.010s

子查询必须放在小括号()中，小括号中的查询语句也被称为子查询或者内层查询，使用内层查询的结果作为搜索条件的操作数的查询称为外层查询。如果你在一个查询语句中需要用到更多的表的话，那么在一个子查询中可以继续嵌套另一个子查询，在执行查询语句时，将按照从内到外的顺序依次执行这些查询。

标量子查询单纯的代表一个值，由标量子查询作为的操作数组成的搜索条件只要符合表达语法就可以。

列子查询

如果我们想查询'计算机科学与工程'专业的学生的成绩，我们最后要得到的是成绩，所以成绩先写前面，成绩需要用到学号查，那么我们的子查询只需要得到计算机科学与工程专业的学号，最后用 in 语句连接：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

mysql root@(none):xiaohaizi> select * from student_score where number in (select number from student_info where major='计算机科学与工程' );                                                    
+----------+--------------------+-------+
| number   | subject            | score |
+----------+--------------------+-------+
| 20180101 | 母猪的产后护理     | 78    |
| 20180101 | 论萨达姆的战争准备 | 88    |
| 20180102 | 母猪的产后护理     | 100   |
| 20180102 | 论萨达姆的战争准备 | 98    |
+----------+--------------------+-------+
4 rows in set
Time: 0.015s

行子查询

有列子查询，大家肯定就好奇有没有行子查询。哈哈，当然有了，只要子查询的结果集中最多只包含一条记录，而且这条记录中有超过一个列的数据（如果该条记录只包含一个列的话，该子查询就成了标量子查询），那么这个子查询就可以被称之为行子查询，比如这样：

1
2
3
4
5
6
7
8
9

mysql> SELECT * FROM student_score WHERE (number, subject) = (SELECT number, '母猪的产后护理' FROM student_info LIMIT 1);
+----------+-----------------------+-------+
| number   | subject               | score |
+----------+-----------------------+-------+
| 20180104 | 母猪的产后护理        |    55 |
+----------+-----------------------+-------+
1 row in set (0.01 sec)

mysql>

在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用LIMIT 1子句来限制记录数量。

表子查询

如果子查询结果集中包含多行多列，那么这个子查询也可以被称之为表子查询，比如这样：

mysql> SELECT * FROM student_score WHERE (number, subject) IN (SELECT number, '母猪的产后护理' FROM student_info WHERE major = '计算机科学与工程');
+----------+-----------------------+-------+
| number   | subject               | score |
+----------+-----------------------+-------+
| 20180101 | 母猪的产后护理        |    78 |
| 20180102 | 母猪的产后护理        |   100 |
+----------+-----------------------+-------+
2 rows in set (0.00 sec)

mysql>

在这个例子中的子查询执行之后的结果集中包含多行多列，所以可以被看作是一个表子查询。

EXISTS和NOT EXISTS子查询

有时候外层查询并不关心子查询中的结果是什么，而只关心子查询的结果集是不是为空集，这时可以用到下边这两个操作符：

我们来举个例子：

1
2
3
4

mysql> SELECT * FROM student_score WHERE EXISTS (SELECT * FROM student_info WHERE number = 20180108);
Empty set (0.00 sec)

mysql>

其中子查询的意思是在student_info表中查找学号为20180108的学生信息，很显然并没有学号为20180108的学生，所以子查询的结果集是一个空集，于是EXISTS表达式的结果为FALSE，所以外层查询也就不查了，直接返回了一个Empty set，表示没有结果。你可以自己试一下NOT EXISTS的使用。

不相关子查询和相关子查询

前边介绍的子查询和外层查询都没有依赖关系，也就是说子查询可以独立运行并产生结果之后，再拿结果作为外层查询的条件去执行外层查询，这种子查询称为不相关子查询

而有时候我们需要在子查询的语句中引用到外层查询的值，这样的话子查询就不能当作一个独立的语句去执行，这种子查询被称为相关子查询。比方说我们想查看一些学生的基本信息，但是前提是这些学生在student_score表中有成绩记录，那可以这么写：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

mysql> SELECT number, name, id_number, major FROM student_info WHERE EXISTS (SELECT * FROM student_score WHERE student_score.number = student_info.number);
+----------+-----------+--------------------+--------------------------+
| number   | name      | id_number          | major                    |
+----------+-----------+--------------------+--------------------------+
| 20180101 | 杜子腾    | 158177199901044792 | 计算机科学与工程         |
| 20180102 | 杜琦燕    | 151008199801178529 | 计算机科学与工程         |
| 20180103 | 范统      | 17156319980116959X | 软件工程                 |
| 20180104 | 史珍香    | 141992199701078600 | 软件工程                 |
+----------+-----------+--------------------+--------------------------+
4 rows in set (0.00 sec)

mysql>

student_info和student_score表里都有number列，所以在子查询的WHERE语句中书写number = number会造成二义性，也就是让服务器懵逼，不知道这个number列到底是哪个表的，所以为了区分，在列名前边加上了表名，并用点.连接起来，这种显式的将列所属的表名书写出来的名称称为该列的全限定名。所以上边子查询的WHERE语句中用了列的全限定名：student_score.number = student_info.number。

这条查询语句可以分成这么两部分来理解

对同一个表的子查询

比方说我们想看看在student_score表的'母猪的产后护理'这门课的成绩中，有哪些超过了平均分的记录

聚集函数不能放到WHERE子句中

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysql>  SELECT * FROM student_score WHERE subject = '母猪的产后护理' AND score > (SELECT AVG(score) FROM student_score WHERE subject = '母猪的产后护理');
+----------+-----------------------+-------+
| number   | subject               | score |
+----------+-----------------------+-------+
| 20180101 | 母猪的产后护理        |    78 |
| 20180102 | 母猪的产后护理        |   100 |
+----------+-----------------------+-------+
2 rows in set (0.01 sec)

mysql>

我们使用子查询先统计出了'母猪的产后护理'这门课的平均分，然后再到外层查询中使用这个平均分组成的表达式来作为搜索条件去查找大于平均分的记录。

连接查询

连接本质上就是把多张表合成一张大表。设计的时候为了节省存储空间，故而分成多张表，本质上多个表组合后结果集就是多个表的笛卡尔积

最简单的表的连接方式为：

1
2
3

SELECT t1.m1, t1.n1, t2.m2, t2.n2 FROM t1, t2;
SELECT m1, n1, m2, n2 FROM t1, t2;
SELECT t1.*, t2.* FROM t1, t2;

连接过程简介

笛卡尔积一般来说是非常大的，所以我们一般不把整张表全部连接，只需要在连接过程中筛选出我们需要的。其中，过滤条件可以分成两种：

• 涉及单张表的条件
• 设计多张表的条件

比如下面的查询语句：

1

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

在这个查询中我们指明了这三个过滤条件：

• t1.m1 > 1
• t1.m1 = t2.m2
• t2.n2 < 'd'

内连接和外连接

假设我们要查询出学生的基本信息，两个表由学号相连，所以可以这么写：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

mysql root@(none):xiaohaizi> SELECT student_info.number, name, major, subject, score FROM student_info, student_score WHERE student_info.number = student_score.number;    
+----------+--------+------------------+--------------------+-------+
| number   | name   | major            | subject            | score |
+----------+--------+------------------+--------------------+-------+
| 20180101 | 杜子腾 | 计算机科学与工程 | 母猪的产后护理     | 78    |
| 20180101 | 杜子腾 | 计算机科学与工程 | 论萨达姆的战争准备 | 88    |
| 20180102 | 杜琦燕 | 计算机科学与工程 | 母猪的产后护理     | 100   |
| 20180102 | 杜琦燕 | 计算机科学与工程 | 论萨达姆的战争准备 | 98    |
| 20180103 | 范统   | 软件工程         | 母猪的产后护理     | 59    |
| 20180103 | 范统   | 软件工程         | 论萨达姆的战争准备 | 61    |
| 20180104 | 史珍香 | 软件工程         | 母猪的产后护理     | 55    |
| 20180104 | 史珍香 | 软件工程         | 论萨达姆的战争准备 | 46    |
+----------+--------+------------------+--------------------+-------+
8 rows in set
Time: 0.012s

但是比如有些同学缺考，却并没有被查询出来，所以在score表中没有记录，这个需求的本质是：驱动表中的记录即使在被驱动表中没有出现过，也需要加入结果集

为了解决这个问题，就有了内连接和外连接的概念：

• 对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接。

• 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

  在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为2种：

  • 左外连接

    选取左侧的表为驱动表。

  • 右外连接

    选取右侧的表为驱动表。

可是这样仍然存在问题，即使对于外连接来说，有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。这就犯难了，有时候匹配失败要加入结果集，有时候又不要加入结果集，这咋办，有点儿愁啊。。。噫，把过滤条件分为两种不就解决了这个问题了么，所以放在不同地方的过滤条件是有不同语义的：

• WHERE子句中的过滤条件

  WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。

• ON子句中的过滤条件

  对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

  需要注意的是，这个**ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的**，所以如果把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。

一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。

左（外）连接的语法

比如我们要把t1表和t2表进行左外连接查询可以这么写：

1

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

其中中括号里的OUTER单词是可以省略的。对于LEFT JOIN类型的连接来说，我们把放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表。所以上述例子中t1就是外表或者驱动表，t2就是内表或者被驱动表。需要注意的是，对于左（外）连接和右（外）连接来说，必须使用ON子句来指出连接条件。了解了左（外）连接的基本语法之后，再次回到我们上边那个现实问题中来，看看怎样写查询语句才能把所有的学生的成绩信息都查询出来，即使是缺考的考生也应该被放到结果集中：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

mysql> SELECT student_info.number, name, major, subject, score FROM student_info LEFT JOIN student_score ON student_info.number = student_score.number;
+----------+-----------+--------------------------+-----------------------------+-------+
| number   | name      | major                    | subject                     | score |
+----------+-----------+--------------------------+-----------------------------+-------+
| 20180101 | 杜子腾    | 计算机科学与工程         | 母猪的产后护理              |    78 |
| 20180101 | 杜子腾    | 计算机科学与工程         | 论萨达姆的战争准备          |    88 |
| 20180102 | 杜琦燕    | 计算机科学与工程         | 母猪的产后护理              |   100 |
| 20180102 | 杜琦燕    | 计算机科学与工程         | 论萨达姆的战争准备          |    98 |
| 20180103 | 范统      | 软件工程                 | 母猪的产后护理              |    59 |
| 20180103 | 范统      | 软件工程                 | 论萨达姆的战争准备          |    61 |
| 20180104 | 史珍香    | 软件工程                 | 母猪的产后护理              |    55 |
| 20180104 | 史珍香    | 软件工程                 | 论萨达姆的战争准备          |    46 |
| 20180105 | 范剑      | 飞行器设计               | NULL                        |  NULL |
| 20180106 | 朱逸群    | 电子信息                 | NULL                        |  NULL |
+----------+-----------+--------------------------+-----------------------------+-------+
10 rows in set (0.00 sec)

mysql>

从结果集中可以看出来，虽然范剑和朱逸群并没有对应的成绩记录，但是由于采用的是连接类型为左（外）连接，所以仍然把它放到了结果集中，只不过在对应的成绩记录的各列使用NULL值填充而已。

右（外）连接的语法

右（外）连接和左（外）连接的原理是一样一样的，语法也只是把LEFT换成RIGHT而已：

1

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

内连接的语法

内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集，我们最开始说的那些连接查询的类型都是内连接。不过之前仅仅提到了一种最简单的内连接语法，就是直接把需要连接的多个表都放到FROM子句后边。其实针对内连接，MySQL提供了好多不同的语法，我们以t1和t2表为例瞅瞅：

1

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];

也就是说在MySQL中，下边这几种内连接的写法都是等价的：

• SELECT * FROM t1 JOIN t2;
• SELECT * FROM t1 INNER JOIN t2;
• SELECT * FROM t1 CROSS JOIN t2;

上边的这些写法和直接把需要连接的表名放到FROM语句之后，用逗号,分隔开的写法是等价的：

1

 SELECT * FROM t1, t2;

现在我们虽然介绍了很多种内连接的书写方式，不过熟悉一种就好了，这里我们推荐INNER JOIN的形式书写内连接（因为INNER JOIN语义很明确嘛，可以和LEFT JOIN和RIGHT JOIN很轻松的区分开）。这里需要注意的是，由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。

我们前边说过，连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。不论哪个表作为驱动表，两表连接产生的笛卡尔积肯定是一样的。而对于内连接来说，由于凡是不符合ON子句或WHERE子句中的条件的记录都会被过滤掉，其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去，所以对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合ON子句连接条件的记录也会被加入结果集，所以此时驱动表和被驱动表的关系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。

举个例子，现有t1表和t2表，如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

执行各种语句的结果为：

 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

mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
|    1 | a    | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t1 RIGHT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
| NULL | NULL |    4 | d    |
+------+------+------+------+
3 rows in set (0.00 sec)

多表连接

不管是多少个表的连接，本质上就是各个表的记录在符合过滤条件下的自由组合。

表的别名

我们也可以为表来定义别名，格式与定义列的别名一致，都是用空白字符或者AS隔开，这个在表名特别长的情况下可以让语句表达更清晰一些，比如这样：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

mysql> SELECT s1.number, s1.name, s1.major, s2.subject, s2.score FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number;
+----------+-----------+--------------------------+-----------------------------+-------+
| number   | name      | major                    | subject                     | score |
+----------+-----------+--------------------------+-----------------------------+-------+
| 20180101 | 杜子腾    | 计算机科学与工程         | 母猪的产后护理              |    78 |
| 20180101 | 杜子腾    | 计算机科学与工程         | 论萨达姆的战争准备          |    88 |
| 20180102 | 杜琦燕    | 计算机科学与工程         | 母猪的产后护理              |   100 |
| 20180102 | 杜琦燕    | 计算机科学与工程         | 论萨达姆的战争准备          |    98 |
| 20180103 | 范统      | 软件工程                 | 母猪的产后护理              |    59 |
| 20180103 | 范统      | 软件工程                 | 论萨达姆的战争准备          |    61 |
| 20180104 | 史珍香    | 软件工程                 | 母猪的产后护理              |    55 |
| 20180104 | 史珍香    | 软件工程                 | 论萨达姆的战争准备          |    46 |
+----------+-----------+--------------------------+-----------------------------+-------+
8 rows in set (0.00 sec)

mysql>

这个例子中，我们在FROM子句中给student_info定义了一个别名s1，student_score定义了一个别名s2，那么在整个查询语句的其他地方就可以引用这个别名来替代该表本身的名字了。

自连接

我们这里需要的是两张一模一样的t1表进行连接，为了把两个一样的表区分一下，需要为表定义别名。比如这样：

1

mysql> SELECT * FROM t1 AS table1, t1 AS table2;

连接查询与子查询的转换

有的查询需求既可以使用连接查询解决，也可以使用子查询解决，比如

1

SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');

这个子查询就可以被替换：

1

SELECT s2.* FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number AND s1.major = '计算机科学与工程';

组合查询

多条查询语句产生的结果集查也可以被合并成一个大的结果集，这种将多条查询语句产生的结果集合并起来的查询方式称为合并查询，或者组合查询。

涉及单表的组合查询

使用 UNION 可以将两个查询语句连接到一起。

1
2
3

SELECT m1 FROM t1 WHERE m1 < 2 UNION SELECT m1 FROM t1 WHERE m1 > 2;
# 也可以连接多个
SELECT m1 FROM t1 WHERE m1 < 2 UNION SELECT m1 FROM t1 WHERE m1 > 2 UNION SELECT m1 FROM t1 WHERE m1 = 2;

多个表达式也可以，只要数量相同比如下边这个使用UNION连接起来的各个查询语句的查询列表处都有2个表达式：

1

SELECT m1, n1 FROM t1 WHERE m1 < 2 UNION SELECT m1, n1 FROM t1 WHERE m1 > 2;

使用UNION连接起来的各个查询语句的查询列表中位置相同的表达式的类型应该是相同的。当然这不是硬性要求，如果不匹配的话，MySQL将会自动的进行类型转换，比方说下边这个组合查询语句：

1

SELECT m1, n1 FROM t1 WHERE m1 < 2 UNION SELECT n1, m1 FROM t1 WHERE m1 > 2;

使用UNION连接起来的两个查询中，第一个语句的查询列表是m1, n1，第二个查询语句的查询列表是n1, m1，我们应该注意两点：

• 第一个查询的查询列表处的m1和第二个查询的查询列表的n1对应，第一个查询的查询列表处的n1和第二个查询的查询列表的m1对应，m1和n1虽然类型不同，但MySQL会帮助我们自动进行必要的类型转换。
• 这几个查询语句的结果集都可以被合并到一个大的结果集中，但是这个大的结果集总是要有展示一下列名的吧，所以就规定组合查询的结果集中显示的列名将以第一个查询中的列名为准，上边的例子就采用了第一个查询中的m1, n1作为结果集的列名。

涉及不同表的组合查询

我们可以使用UNION直接将这两个查询语句拼接到一起：

1

SELECT m1, n1 FROM t1 WHERE m1 < 2 UNION SELECT m2, n2 FROM t2 WHERE m2 > 2;

包含或去除重复的行

默认情况下，使用UNION来合并多个查询的记录会默认过滤掉重复的记录。

如果我们想要保留重复记录，可以使用UNION ALL来连接多个查询：

1

SELECT m1, n1 FROM t1 UNION ALL SELECT m2, n2 FROM t2;

组合查询中的ORDER BY和LIMIT子句

组合查询会把各个查询的结果汇总到一块，如果我们相对最终的结果集进行排序或者只保留几行的话，可以在组合查询的语句末尾加上ORDER BY和LIMIT子句，就像这样：

1

SELECT m1, n1 FROM t1 UNION SELECT m2, n2 FROM t2 ORDER BY m1 DESC LIMIT 2;

这里需要注意的一点是，由于最后的结果集展示的列名是第一个查询中给定的列名，所以ORDER BY子句中指定的排序列也必须是第一个查询中给定的列名（别名也可以）。

如果我们只想单独为各个小的查询排序，而不为最终的汇总的结果集排序行不行呢？先试试：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

mysql> (SELECT m1, n1 FROM t1 ORDER BY m1 DESC) UNION (SELECT m2, n2 FROM t2 ORDER BY m2 DESC);
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

mysql>

从结果来看，我们为各个小查询加入的ORDER BY子句好像并没有起作用，这是因为MySQL规定组合查询并不保证最后汇总起来的大结果集中的顺序是按照各个小查询的结果集中的顺序排序的，也就是说我们在各个小查询中加入ORDER BY子句的作用和没加一样～ 不过如果我们只是单纯的想从各个小的查询中获取有限条排序好的记录加入最终的汇总，那是可以滴，比如这样：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysql> (SELECT m1, n1 FROM t1 ORDER BY m1 DESC LIMIT 1) UNION (SELECT m2, n2 FROM t2 ORDER BY m2 DESC LIMIT 1);
+------+------+
| m1   | n1   |
+------+------+
|    3 | c    |
|    4 | d    |
+------+------+
2 rows in set (0.00 sec)

mysql>