MySQL进阶：SQL查询技巧与高级操作-优快云博客

本文链接：https://blog.youkuaiyun.com/Stephencurr/article/details/121904084

本文深入探讨了MySQL的高级查询技巧，包括按关键字排序、分组、LIMIT使用、别名设置、通配符和子查询。详细介绍了ORDER BY、GROUP BY、LIMIT、AS以及LIKE和REGEXP在查询中的应用。此外，还涉及视图的定义、作用和使用示例，NULL值的处理以及正则表达式在数据过滤中的应用。最后，讨论了各种运算符和连接查询，如内连接、左连接和右连接。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、常用查询

对 MySQL 数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。例如只取 10 条数据、对查询结果进行排序或分组等等。

1. 按关键字排序

使用 SELECT 语句可以将需要的数据从 MySQL 数据库中查询出来，如果对查询的结果进行排序，可以使用 ORDER BY 语句来对语句实现排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，也可以针对多个字段。

(1) 语法

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... 
1

ASC | DESC：

ASC 是按照升序进行排序的，是默认的排序方式，即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式，则默认按 ASC 方式进行排序。
DESC 是按降序方式进行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤。
12

(2) 创建模板表

数据库有一张 info 表，记录了学生的 id，姓名，分数，地址和爱好
create table info (id int(10) primary key not null auto_increment,name varchar(20),score decimal(5,2),address varchar(40),hobby varchar(20));

insert into info values(1,'liuyi',80,'beijing',2);
insert into info values(2,'wangwu',90,'shengzheng',2);
insert into info values(3,'lisi',60,'shanghai',4);
insert into info values(4,'tianqi',99,'hangzhou',5);
insert into info values(5,'jiaoshou',98,'laowo',3);
insert into info values(6,'hanmeimei',10,'nanjing',3);
insert into info values(7,'lilei',11,'nanjing',5);
insert into info values(8,'caicai',16,'nanjing',5);
12345678910

(3) 单字段排序

按分数排序，默认不指定是升序排列 ASC

mysql> select id,name,score from info order by score;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  6 | hanmeimei | 10.00 |
|  7 | lilei     | 11.00 |
|  8 | caicai    | 16.00 |
|  3 | lisi      | 60.00 |
|  1 | liuyi     | 80.00 |
|  2 | wangwu    | 90.00 |
|  5 | jiaoshou  | 98.00 |
|  4 | tianqi    | 99.00 |
+----+-----------+-------+
8 rows in set (0.01 sec)
1234567891011121314

分数按降序排列，使用 DESC

mysql> select id,name,score from info order by score desc;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  4 | tianqi    | 99.00 |
|  5 | jiaoshou  | 98.00 |
|  2 | wangwu    | 90.00 |
|  1 | liuyi     | 80.00 |
|  3 | lisi      | 60.00 |
|  8 | caicai    | 16.00 |
|  7 | lilei     | 11.00 |
|  6 | hanmeimei | 10.00 |
+----+-----------+-------+
8 rows in set (0.01 sec)
1234567891011121314

(4) 条件查询

order by 还可以结合 where 进行条件过滤，筛选地址是杭州的学生按分数降序排列

mysql> select name,score from info where address='hangzhou' order by score desc;
+--------+-------+
| name   | score |
+--------+-------+
| tianqi | 99.00 |
+--------+-------+
1 row in set (0.00 sec)
1234567

(5) 多字段排序

order by 之后的参数，使用 “,” 分割，优先级是按先后顺序而定

mysql> select id,name,hobby from info order by hobby desc,id asc;
+----+-----------+-------+
| id | name      | hobby |
+----+-----------+-------+
|  4 | tianqi    | 5     |
|  7 | lilei     | 5     |
|  8 | caicai    | 5     |
|  3 | lisi      | 4     |
|  5 | jiaoshou  | 3     |
|  6 | hanmeimei | 3     |
|  1 | liuyi     | 2     |
|  2 | wangwu    | 2     |
+----+-----------+-------+
8 rows in set (0.00 sec)
1234567891011121314

order by 之后的第一个参数只有在出现相同的数值，第二个字段才有意义

mysql> select id,hobby from info order by id asc,hobby desc;
+----+-------+
| id | hobby |
+----+-------+
|  1 | 2     |
|  2 | 2     |
|  3 | 4     |
|  4 | 5     |
|  5 | 3     |
|  6 | 3     |
|  7 | 5     |
|  8 | 5     |
+----+-------+
8 rows in set (0.00 sec)
1234567891011121314

(6) 区间判断及查询不重复记录

AND / OR —— 且 / 或

mysql> select * from info where score > 10 and score <= 90;
+----+--------+-------+------------+-------+
| id | name   | score | address    | hobby |
+----+--------+-------+------------+-------+
|  1 | liuyi  | 80.00 | beijing    | 2     |
|  2 | wangwu | 90.00 | shengzheng | 2     |
|  3 | lisi   | 60.00 | shanghai   | 4     |
|  7 | lilei  | 11.00 | nanjing    | 5     |
|  8 | caicai | 16.00 | nanjing    | 5     |
+----+--------+-------+------------+-------+
5 rows in set (0.00 sec)

mysql> select * from info where score > 70 or score <= 90;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 90.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  4 | tianqi    | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou  | 98.00 | laowo      | 3     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
8 rows in set (0.00 sec)
1234567891011121314151617181920212223242526

嵌套 / 多条件

mysql> select * from info where score > 70 or (score > 50 and score <= 80);
+----+----------+-------+------------+-------+
| id | name     | score | address    | hobby |
+----+----------+-------+------------+-------+
|  1 | liuyi    | 80.00 | beijing    | 2     |
|  2 | wangwu   | 90.00 | shengzheng | 2     |
|  3 | lisi     | 60.00 | shanghai   | 4     |
|  4 | tianqi   | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou | 98.00 | laowo      | 3     |
+----+----------+-------+------------+-------+
5 rows in set (0.00 sec)
1234567891011

(7) distinct 查询不重复记录

语法：

select distinct 字段 from 表名﹔
1

示例：

mysql> select distinct hobby from info;
+-------+
| hobby |
+-------+
| 2     |
| 4     |
| 5     |
| 3     |
+-------+
4 rows in set (0.00 sec)
12345678910

PS：以下语句是否可以筛重

select name,hobby from info where hobby in (select distinct hobby from info);
1

示例：

mysql> select name,hobby from info where hobby in (select distinct hobby from info); 
+-----------+-------+
| name      | hobby |
+-----------+-------+
| liuyi     | 2     |
| wangwu    | 2     |
| lisi      | 4     |
| tianqi    | 5     |
| jiaoshou  | 3     |
| hanmeimei | 3     |
| lilei     | 5     |
| caicai    | 5     |
+-----------+-------+
8 rows in set (0.00 sec)
1234567891011121314

distinct 必须放在最开头。
distinct 只能使用需要去重的字段进行操作 ---- 也就是说我 distinct 了 name,hobby 两个字段，我后面想根据 id 进行排序，是不可以的，因为只能 name,hobby 两个字段进行操作。
distinct 去重多个字段时，几个字段同时重复时才会被过滤。

2. 对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现，GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：

计数（COUNT）
求和（SUM）
求平均数（AVG）
最大值（MAX）
最小值（MIN）
12345

GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。

语法

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator valueGROUP BY column_name;
selet字段，聚合函数from表名，(where字段名（匹配）数值)group by字段名;
12
对 info 进行分组，筛选范围/条件是 score 大于等于 45 的 'name',score 相同的会默认分在一个组
mysql> select count(name),score from info where score >= 45 group by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           1 | 60.00 |
|           1 | 80.00 |
|           1 | 90.00 |
|           1 | 98.00 |
|           1 | 99.00 |
+-------------+-------+
5 rows in set (0.00 sec)
1234567891011

分组排序

对 info 表中 hobby 相同的 id 进行数量统讦，并按照相同 hobby 进行分组
mysql> select count(id),hobby from info group by hobby;
+-----------+-------+
| count(id) | hobby |
+-----------+-------+
|         2 | 2     |
|         2 | 3     |
|         1 | 4     |
|         3 | 5     |
+-----------+-------+
4 rows in set (0.00 sec)
12345678910
基于上一条操作，结合 order by 把统计的 id 数量进行降序排列
mysql> select count(id),hobby from info group by hobby order by count(id) desc;
+-----------+-------+
| count(id) | hobby |
+-----------+-------+
|         3 | 5     |
|         2 | 2     |
|         2 | 3     |
|         1 | 4     |
+-----------+-------+
4 rows in set (0.00 sec)
12345678910

分组条件

结合 where 语句，筛选分数大于等于 80 的分组，计算学生个数按降序排列
mysql> select count(name),score from info where score >= 80 group by hobby order by count(name) desc;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           2 | 80.00 |
|           1 | 99.00 |
|           1 | 98.00 |
+-------------+-------+
3 rows in set (0.00 sec)
123456789

3. 限制结果条目（limit）

limit 限制输出的结果记录，在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录（行）。有时候仅需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句。

语法

SELECT column1, column2, ... FROM table_name LIMIT [offset,] number
1
LIMIT 的第一个参数是位置偏移量（可选参数），是设置 MySQL 从哪一行开始显示。 如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，第一条记录的位置偏移量是 0，第二条是 1，以此类推。第二个参数是设置返回记录行的最大数目。

查询所有信息显示前 4 行记录

mysql> select * from info limit 3;
+----+--------+-------+------------+-------+
| id | name   | score | address    | hobby |
+----+--------+-------+------------+-------+
|  1 | liuyi  | 80.00 | beijing    | 2     |
|  2 | wangwu | 90.00 | shengzheng | 2     |
|  3 | lisi   | 60.00 | shanghai   | 4     |
+----+--------+-------+------------+-------+
3 rows in set (0.00 sec)
123456789

从第 4 行开始，往后显示 3 行内容

mysql> select * from info limit 3,3;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  4 | tianqi    | 99.00 | hangzhou | 5     |
|  5 | jiaoshou  | 98.00 | laowo    | 3     |
|  6 | hanmeimei | 10.00 | nanjing  | 3     |
+----+-----------+-------+----------+-------+
3 rows in set (0.00 sec)
123456789

结合 order by 语句，按 id 的大小升序排列显示前三行

mysql> select id,name from info order by id limit 3;
+----+--------+
| id | name   |
+----+--------+
|  1 | liuyi  |
|  2 | wangwu |
|  3 | lisi   |
+----+--------+
3 rows in set (0.00 sec)
123456789

显示后三行并倒序排列

mysql> select id,name from info order by id desc limit 3;
+----+-----------+
| id | name      |
+----+-----------+
|  8 | caicai    |
|  7 | lilei     |
|  6 | hanmeimei |
+----+-----------+
3 rows in set (0.00 sec)
123456789

limit 是作为位置偏移量的定义，它的起始是从 0 开始，而 0 表示的是字段

4. 设置别名（alias —> as）

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性。

使用场景：
1、对复杂的表进行查询的时候，别名可以缩短查询语句的长度
2、多表相连查询的时候（通俗易懂、减短 sql 语句）

语法

对于列的别名：SELECT column_name AS alias_name FROM table_name;
对于表的别名：SELECT column_name(s) FROM table_name AS alias_name;
12

在使用 AS 后，可以用 alias_name 代替 table_name，其中 AS 语句是可选的。AS 之后的别名，主要是为表内的列或者表提供临时的名称，在查询过程中使用，库内实际的表名或字段名是不会被改变的。

列别名设置

select name as 姓名,score as 成绩 from info;
——————————————————————————————————————————————
mysql> select name as ne,score as G from info;
+-----------+-------+
| ne        | G     |
+-----------+-------+
| liuyi     | 80.00 |
| wangwu    | 90.00 |
| lisi      | 60.00 |
| tianqi    | 99.00 |
| jiaoshou  | 98.00 |
| hanmeimei | 10.00 |
| lilei     | 11.00 |
| caicai    | 16.00 |
+-----------+-------+
8 rows in set (0.00 sec)
12345678910111213141516

表别名设置

select i.name as 姓名,i.score as 成绩 from info as i;
———————————————————————————————————————————————————————
mysql> select i.name as 姓名,i.score as 成绩 from info as i;
+-----------+--------+
| 姓名      | 成绩   |
+-----------+--------+
| liuyi     |  80.00 |
| wangwu    |  90.00 |
| lisi      |  60.00 |
| tianqi    |  99.00 |
| jiaoshou  |  98.00 |
| hanmeimei |  10.00 |
| lilei     |  11.00 |
| caicai    |  16.00 |
+-----------+--------+
8 rows in set (0.00 sec)
12345678910111213141516

查询 info 表的记录数量，以 number 显示

select count(*) as number from info;
————————————————————————————————————————
mysql> select count(*) as number from info;
+--------+
| number |
+--------+
|      8 |
+--------+
1 row in set (0.00 sec)
123456789

不用 as 也可以显示显示别名

select count(*) number from info;
——————————————————————————————————————
mysql> select count(*) number from info;
+--------+
| number |
+--------+
|      8 |
+--------+
1 row in set (0.00 sec)
123456789

AS 还可以作为连接语句的操作符

创建 t1 表，将 info 表的查询记录全部插入 t1 表

create table t1 as select * from info;
1
此处 AS 起到的作用：`
`1、创建了一个新表 t1 并定义表结构，插入表数据（与 info 表相同）`
`2、但是 "约束" 没有被完全 "复制" 过来，但是如果原表设置了主键，那么附表的 default 字段会默认设置一个 0`
`相似：克隆、复制表结构
create table t1 (select * from info);
1

也可以加入 where 语句判断

create table test1 as select * from info where score >=60;
1

在为表设置别名时，要保证别名不能与数据库中的其他表的名称冲突
列的别名是在结果中有显示的，而表的别名在结果中没有显示，只在执行查询时使用

5. 通配符

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。
通常通配符都是跟 LIKE（模糊查询）一起使用的，并协同 WHERE 子句共同来完成查询任务。常用的通配符有两个，分别是：

%：百分号表示零个、一个或多个字符
_：下划线表示单个字符
12
查询名字是 c 开头的记录
mysql> select id,name from info where name like 'c%';
+----+--------+
| id | name   |
+----+--------+
|  8 | caicai |
+----+--------+
1 row in set (0.00 sec)
1234567
查询名字里是 c 和 i 中间有一个字符的记录
mysql> select id,name from info where name like 'c_ic_i';
+----+--------+
| id | name   |
+----+--------+
|  8 | caicai |
+----+--------+
1 row in set (0.00 sec)
1234567
查询名字中间有 g 的记录
mysql> select id,name from info where name like '%g%';
+----+--------+
| id | name   |
+----+--------+
|  2 | wangwu |
+----+--------+
1 row in set (0.00 sec)
1234567
查询 hanmei 后面 3 个字符的名字记录
mysql> select id,name from info where name like 'hanmei___';
+----+-----------+
| id | name      |
+----+-----------+
|  6 | hanmeimei |
+----+-----------+
1 row in set (0.00 sec)
1234567

通配符 "%" 和 "_" 不仅可以单独使用，也可以组合使用

查询名字以 s 开头的记录
mysql> select id,name from info where name like 's%_';
Empty set (0.00 sec)
12

6. 子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。

PS: 子语句可以与主语句所查询的表相同，也可以是不同表

相同表示例

select name,score from info where id in (select id from info where score >80);
——————————————————————————————————————————————————————————————————————————————
mysql> select name,score from info where id in (select id from info where score > 80);
+----------+-------+
| name     | score |
+----------+-------+
| wangwu   | 90.00 |
| tianqi   | 99.00 |
| jiaoshou | 98.00 |
+----------+-------+
3 rows in set (0.00 sec)
1234567891011

以上：

主语句：select name,score from info where id
子语句（集合）：select id from info where score >80
12
PS：子语句中的 sql 语句是为了，最后过滤出一个结果集，用于主语句的判断条件`
`in: 将主表和子表关联/连接的语法

多表查询

create table ky13 (id int(4));
insert into ky13 values(1),(2),(3);
select id,name,score from info where id in (select * from ky13);
——————————————————————————————————————————————————————————————————
mysql> select id,name,score from info where id in (select * from ky13);
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | liuyi  | 80.00 |
|  2 | wangwu | 90.00 |
|  3 | lisi   | 60.00 |
+----+--------+-------+
3 rows in set (0.00 sec)
12345678910111213
子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。
子查询的结果集可以通过 INSERT 语句插入到其他的表中

将 t1 里的记录全部删除，重新插入 info 表的记录

delete from t1;
insert into t1 select * from info where id in (select id from info);
select * from t1;
————————————————————————————————————————————————————————————————————
mysql> delete from t1;
Query OK, 8 rows affected (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 select * from info where id in (select id from info);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 90.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  4 | tianqi    | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou  | 98.00 | laowo      | 3     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
8 rows in set (0.00 sec)            
12345678910111213141516171819202122232425262728

UPDATE 语句也可以使用子查询。UPDATE 内的子查询，在 set 更新内容时，可以是单独的一列，也可以是多列

update info set score=50 where id in (select * from ky13 where id=2);
select * from info;
——————————————————————————————————————————————————————————————————————
mysql> update info set score=50 where id in (select * from ky13 where id=2);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 50.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  4 | tianqi    | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou  | 98.00 | laowo      | 3     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
8 rows in set (0.00 sec)
123456789101112131415161718192021

DELETE 也适用于子查询

删除分数大于 80 的记录
delete from info where id in (select id where score>80);
——————————————————————————————————————————————————————————
mysql> delete from info where id in (select id where score > 80);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from info;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 50.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
6 rows in set (0.00 sec)
1234567891011121314151617
在 IN 前面还可以添加 NOT，其作用与 IN 相反，表示否定（即不在子查询的结果集里面） 删除分数不是大于等于80的记录
select id,name,score from t1;
delete from t1 where id not in (select id where score>=80);
select id,name,score from t1;
————————————————————————————————————————————————————————————
mysql> select id,name,score from t1;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | liuyi     | 80.00 |
|  2 | wangwu    | 90.00 |
|  3 | lisi      | 60.00 |
|  4 | tianqi    | 99.00 |
|  5 | jiaoshou  | 98.00 |
|  6 | hanmeimei | 10.00 |
|  7 | lilei     | 11.00 |
|  8 | caicai    | 16.00 |
+----+-----------+-------+
8 rows in set (0.00 sec)

mysql> delete from t1 where id not in (select id where score >= 80);
Query OK, 4 rows affected (0.00 sec)

mysql> select id,name,score from t1;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | liuyi    | 80.00 |
|  2 | wangwu   | 90.00 |
|  4 | tianqi   | 99.00 |
|  5 | jiaoshou | 98.00 |
+----+----------+-------+
4 rows in set (0.00 sec)
1234567891011121314151617181920212223242526272829303132

子查询 - exists

EXISTS 这个关键字在子查询时，主要用于判断 exists 之后的条件是否成立，如果成立，则正常执行主语句的匹配，如不成立，则不会执行主语句查询，如子查询结果集不成立的话，输出为 null。
PS：count 为计数，sum 为求和，使用 sum 求和结合 exists，如子查询结果集不成立的话，输出为 null

查询如果存在分数等于 80 的记录则计算 info 的字段数
select count(*) from info where exists(select id from info where score=80);
1
查询如果存在分数小于 50 的记录则计算 info 的字段数，info 表没有小于 50 的，所以返回 0
 select count(*) from info where exists(select id from info where score<50);
1

子查询，别名 as

查询 info 表 id,name 字段
select id,name from info;
1
将结果集做为一张表进行查询的时候，我们也需要用到别名`
`需求：从 info 表中的 id 和 name 字段的内容做为 "内容" 输出 id 的部分
mysql> select id from (select id,name from info);
ERROR 1248 (42000): Every derived table must have its own alias
12
此时会报错，原因为：
select * from 表名 此为标准格式，而以上的查询语句，"表名" 的位置其实是一个结果集，
mysql 并不能直接识别，而此时给与结果集设置一个别名，以 "select a.id from a" 的方式查询将此结果集是为一张 "表"，就可以正常查询数据了，如下：

select a.id from (select id,name from info) a;
相当于
select info.id,name from info;
select 表.字段，字段 from 表；
1234567

二、视图

1. 视图的定义

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。
数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。
数据库中的虚拟表不包含真实数据，只是做了映射。镜花水月/倒影，动态保存结果集。

2. 视图的作用

使操作简单化，可以对经常使用的查询定义一个视图，使用户不必为同样的查询操作指定条件
增加数据的安全性，通过视图，用户只能查询和修改指定的数据
提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响

总结：使用视图的大部分情况是为了保障数据安全性，提高查询效率

3. 示例

通过视图定义展示的条件
需求：满足 80 分的学生展示在视图中
PS：这个结果会动态变化，同时可以给不同的人群（例如权限范围）展示不同的视图

create view v_score as select * from info where score>=80;
#创建名为 v_score 的视图
show table status\G
123
查看视图
select * from v_score;
————————————————————————————————————————————————————————
mysql> select * from v_score;
+----+----------+-------+------------+-------+
| id | name     | score | address    | hobby |
+----+----------+-------+------------+-------+
|  1 | liuyi    | 80.00 | beijing    | 2     |
|  2 | wangwu   | 90.00 | shengzheng | 2     |
|  4 | tianqi   | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou | 98.00 | laowo      | 3     |
+----+----------+-------+------------+-------+
4 rows in set (0.00 sec)
123456789101112
修改原表数据并查看视图的变化情况
update info set score='60' where name='wangwu';
————————————————————————————————————————————————————————
mysql> update info set score='60' where name='wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from v_score;
+----+----------+-------+----------+-------+
| id | name     | score | address  | hobby |
+----+----------+-------+----------+-------+
|  1 | liuyi    | 80.00 | beijing  | 2     |
|  4 | tianqi   | 99.00 | hangzhou | 5     |
|  5 | jiaoshou | 98.00 | laowo    | 3     |
+----+----------+-------+----------+-------+
3 rows in set (0.00 sec)
123456789101112131415

从两次视图表的查询结果可以看到，随着原表数据的变化，视图的数据也在实时变化。需要注意的是，不仅改原表视图会变，改视图原表也会变。

三、NULL 值

在 SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使用 NOT NULL 关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意的是，NULL 值与数字 0 或者空白（spaces）的字段是不同的，值为 NULL 的字段是没有值的。在 SQL 语句中，使用 IS NULL 可以判断表内的某个字段是不是 NULL 值，相反的用 IS NOT NULL 可以判断不是 NULL 值。

null 值与空值的区别

空值长度为 0，不占空间；NULL 值的长度为 null，占用空间
is null 无法判断空值
空值使用 “=” 或者 “<>” 来处理（！=）
count() 计算时，NULL 会忽略，空值会加入计算

mysql> desc info;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(10)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)  | YES  |     | NULL    |                |
| score   | decimal(5,2) | YES  |     | NULL    |                |
| address | varchar(40)  | YES  |     | NULL    |                |
| hobby   | varchar(20)  | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1234567891011

插入一条记录，分数字段输入 null，显示出来就是 null

alter table info add column addr varchar(50);
update info set addr='nj' where score >=70;
12
统计数量：检测 null 是否会加入统计中
select count(addr) from info;
1
将 info 表中其中一条数据修改为空值'
update info set addr='' where name='wangwu';
1
统计数量，检测空值是不会被添加到统计中
select count(addr) from info;
1
查询 null 值
select * from info where addr is null;
1

空值数据：select count(*) from YourTable where Your Column Name is null;

查询不为空的值
select * from info where addr is not null;
1

非空值数据：select count(*) from YourTable where YourColumnName is not null;

四、正则表达式

MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式，REGEXP 操作符所支持的匹配模式如表所示。

匹配	描述
^	匹配文本的开始字符
$	匹配文本的结束字符
.	匹配任何单个字符
*	匹配零个或多个在它前面的字符
+	匹配前面的字符 1 次或多次
字符串	匹配包含指定的字符串
p1\|p2	匹配 p1 或 p2
[…]	匹配字符集合中的任意一个字符
[^…]	匹配不在括号中的任何字符
{n}	匹配前面的字符串 n 次
{n,m}	匹配前面的字符串至少 n 次，至多 m 次

查询以 sh 开头的学生信息

mysql> select id,name from info where name regexp '^sh';
+----+----------+
| id | name     |
+----+----------+
|  9 | shanqian |
+----+----------+
1 row in set (0.00 sec)
1234567

查询以 n 结尾的学生信息

mysql> select id,name from info where name regexp 'n$';
+----+----------+
| id | name     |
+----+----------+
|  9 | shanqian |
+----+----------+
1 row in set (0.00 sec)
1234567

查询名字中包含 an 的学生信息

mysql> select id,name from info where name regexp 'an';
+----+-----------+
| id | name      |
+----+-----------+
|  2 | wangwu    |
|  4 | tianqi    |
|  6 | hanmeimei |
|  9 | shanqian  |
+----+-----------+
4 rows in set (0.00 sec)
12345678910

查询名字是 han 开头，i 结尾，中间不知道是什么字符的学生信息

mysql> select id,name from info where name regexp '^han.*i$';
+----+-----------+
| id | name      |
+----+-----------+
|  6 | hanmeimei |
+----+-----------+
1 row in set (0.00 sec)
1234567

查询名字中包含 an 或者 zh 的学生信息

mysql> select id,name from info where name regexp 'an|zh';
+----+-----------+
| id | name      |
+----+-----------+
|  2 | wangwu    |
|  4 | tianqi    |
|  6 | hanmeimei |
|  9 | shanqian  |
+----+-----------+
4 rows in set (0.00 sec)
12345678910

查询名字中有 an，g 可有可无的学生信息

mysql> select id,name from info where name regexp 'ang*';
+----+-----------+
| id | name      |
+----+-----------+
|  2 | wangwu    |
|  4 | tianqi    |
|  6 | hanmeimei |
|  9 | shanqian  |
+----+-----------+
4 rows in set (0.00 sec)
12345678910

查询名字中含有 an，g 至少出现一次的学生信息

mysql> select id,name from info where name regexp 'ang+';
+----+--------+
| id | name   |
+----+--------+
|  2 | wangwu |
+----+--------+
1 row in set (0.00 sec)
1234567

查询名字以 s-x 开头的学生信息

mysql> select id,name from info where name regexp '^[s-x]';
+----+----------+
| id | name     |
+----+----------+
|  2 | wangwu   |
|  4 | tianqi   |
|  9 | shanqian |
+----+----------+
3 rows in set (0.00 sec)
123456789

查询名字不是 caicai 的学生信息

mysql> select id,name from info where name regexp '[^caicai]';
+----+-----------+
| id | name      |
+----+-----------+
|  1 | liuyi     |
|  2 | wangwu    |
|  3 | lisi      |
|  4 | tianqi    |
|  5 | jiaoshou  |
|  6 | hanmeimei |
|  7 | lilei     |
|  9 | shanqian  |
+----+-----------+
8 rows in set (0.00 sec)
1234567891011121314

查询学生名字不以 czx 各字母开头的学生信息

mysql> select id,name from info where name regexp '^[^czx]';
+----+-----------+
| id | name      |
+----+-----------+
|  1 | liuyi     |
|  2 | wangwu    |
|  3 | lisi      |
|  4 | tianqi    |
|  5 | jiaoshou  |
|  6 | hanmeimei |
|  7 | lilei     |
|  9 | shanqian  |
+----+-----------+
8 rows in set (0.00 sec)
1234567891011121314

五、运算符

MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符。

1. 算术运算符

以 SELECT 命令来实现最基础的加减乘除运算，MySQL 支持使用的算术运算符如表所示：

运算符	描述
+	加法
-	减法
*	乘法
/	除法
%	取余

在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为 NULL。需要注意的是，如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算符没有先后顺序。

示例：

mysql> select 1+2,2-1,3*4,4/2,5%2;
+-----+-----+-----+--------+------+
| 1+2 | 2-1 | 3*4 | 4/2    | 5%2  |
+-----+-----+-----+--------+------+
|   3 |   1 |  12 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

mysql> create table mt as select 1+2,2-1,3*4,4/2,5%2;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from mt;
+-----+-----+-----+--------+------+
| 1+2 | 2-1 | 3*4 | 4/2    | 5%2  |
+-----+-----+-----+--------+------+
|   3 |   1 |  12 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

mysql> desc mt;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| 1+2   | int(3)       | NO   |     | 0       |       |
| 2-1   | int(3)       | NO   |     | 0       |       |
| 3*4   | int(3)       | NO   |     | 0       |       |
| 4/2   | decimal(5,4) | YES  |     | NULL    |       |
| 5%2   | int(1)       | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
12345678910111213141516171819202122232425262728293031

2. 比较运算符

比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出表中有哪些记录是符合条件的，如果比较的结果（以布尔值的方式进行返回判断）为真则返回 1，如果为假则返回 0，比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的，如果要区分大小写可以通过 binary 关键字来实现。

运算符	描述
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
!= 或 <>	不等于
is null	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
BETWEEN AND	两者之间
IN	在集合中
LIKE	通配符匹配
GREATEST	两个或多个参数时返回最大值
LEAST	两个或多个参数时返回最小值
REGEXP	正则表达式

说明：

> 等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1(true)，如果不相等则返回 0（flase)。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同；
> 字符串（字母）比较：（'a'>'b'）其实比较的就是底层的 ASCII 码；
> 需要关注的是 ASCII 码有：a、A、0（97、65、48）；
> 与 linux 返回值表达相反，linux 中运行正常返回值是 0，运行异常返回值是非 0。
1234
mysql> select 2=4,2=2,2='2','e'='e','r'=null;
+-----+-----+-------+---------+----------+
| 2=4 | 2=2 | 2='2' | 'e'='e' | 'r'=null |
+-----+-----+-------+---------+----------+
|   0 |   1 |     1 |       1 |     NULL |
+-----+-----+-------+---------+----------+
1 row in set (0.00 sec)
——————————————————————————————————————————————
如果两者都是整数，则按照整数值进行比较。
如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。（在程序中，一般是不会把这两者进行相比较的）
如果两者都是字符串，则按照字符串进行比较。
如果两者中至少有一个值是 NULL，则比较的结果是 NULL。
123456789101112
不等于号有两种写法，分别是 <> 或者 !=，用于针对数字、字符串和表达式不相等的比较。 如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL
mysql> select 'abc'<>'cba',2<>2,3!=2,null<>null;
+--------------+------+------+------------+
| 'abc'<>'cba' | 2<>2 | 3!=2 | null<>null |
+--------------+------+------+------------+
|            1 |    0 |    1 |       NULL |
+--------------+------+------+------------+
1 row in set (0.00 sec)
1234567
大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回 0，同样不能用于判断 NULL;`
`小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回 0，同样不能用于判断 NULL;`
`大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断 NULL;`
`小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL。
mysql> select 5>4,3<4,'a'<'b',4.4<5,'u'>=null;
+-----+-----+---------+-------+-----------+
| 5>4 | 3<4 | 'a'<'b' | 4.4<5 | 'u'>=null |
+-----+-----+---------+-------+-----------+
|   1 |   1 |       1 |     1 |      NULL |
+-----+-----+---------+-------+-----------+
1 row in set (0.00 sec)
1234567
判断一个值是不是 null ( IS NULL、IS NOT NULL )：`
`IS NULL 判断一个值是否为 NULL，如果为 NULL 返回 1，否则返回 0`
`IS NOT NOLL 判断一个值是否不为 NULL，如果不为 NULL 返回 1，否则返回 0
mysql> select 2 is null,'a' is not null,null is null;
+-----------+-----------------+--------------+
| 2 is null | 'a' is not null | null is null |
+-----------+-----------------+--------------+
|         0 |               1 |            1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)
1234567
BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间，条件符合返回 1，否则返回 0
mysql> select 4 between 2 and 5,'c' between 'a' and 'b';
+-------------------+-------------------------+
| 4 between 2 and 5 | 'c' between 'a' and 'b' |
+-------------------+-------------------------+
|                 1 |                       0 |
+-------------------+-------------------------+
1 row in set (0.00 sec)
1234567

between and 覆盖的范围是 >= 和 <= 关系

least 和 greatest（取最小值、取最大值）:`
`LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL`
`GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为 NULL， 则返回结果就为 NULL
mysql> select least(1,2,3),greatest(1,2,3),least('a','b','c'),greatest('a','b','c');
+--------------+-----------------+--------------------+-----------------------+
| least(1,2,3) | greatest(1,2,3) | least('a','b','c') | greatest('a','b','c') |
+--------------+-----------------+--------------------+-----------------------+
|            1 |               3 | a                  | c                     |
+--------------+-----------------+--------------------+-----------------------+
1 row in set (0.00 sec)
1234567
IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0`
`NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0
mysql> select 1 in(1,2,3),2 not in ('a','b','c');
+-------------+------------------------+
| 1 in(1,2,3) | 2 not in ('a','b','c') |
+-------------+------------------------+
|           1 |                      1 |
+-------------+------------------------+
1 row in set, 3 warnings (0.00 sec)
1234567
LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0`
`LIKE 支持两种通配符：'%' 用于匹配任意数目的字符（*匹配的是前面一个字符），而 '_' 只能匹配一个字符`
`NOT LIKE 正好跟 LIKE 相反，如果没有匹配成功则返回 1，反之返回 0
mysql> select 'abc' like 'ab%','abc' like '_bc','abc' not like 'a%';
+------------------+------------------+---------------------+
| 'abc' like 'ab%' | 'abc' like '_bc' | 'abc' not like 'a%' |
+------------------+------------------+---------------------+
|                1 |                1 |                   0 |
+------------------+------------------+---------------------+
1 row in set (0.00 sec)
1234567

3. 逻辑运算符（布尔值）

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则返回 0，真和假也可以用 TRUE 和 FALSE 表示。MySQL 中支持使用的逻辑运算符有四种，具体如表所示：

运算符	描述
not 或 !	逻辑非
and 或 &&	逻辑与
or	逻辑或
xor	逻辑异或

(1) 逻辑非

逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或 ! 表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL。

mysql> select not 2,!3,not 0,! null;
+-------+----+-------+--------+
| not 2 | !3 | not 0 | ! null |
+-------+----+-------+--------+
|     0 |  0 |     1 |   NULL |
+-------+----+-------+--------+
1 row in set (0.00 sec)
1234567

(2) 逻辑与

当所有的操作数都为非 0 值且不为 null 时，返回值为 1，否则为 0
逻辑与使用 AND 或者 && 表示

mysql> select 2 and 3,4 && null,0 and null,null and null;
+---------+-----------+------------+---------------+
| 2 and 3 | 4 && null | 0 and null | null and null |
+---------+-----------+------------+---------------+
|       1 |      NULL |          0 |          NULL |
+---------+-----------+------------+---------------+
1 row in set (0.00 sec)
1234567

说明：

and 和 && 的作用相同
1 and -1 没有 0 或 null，所以返回值为 1
1 and 0 中由有 0，所以返回值为 0
1 and null 有 null，所以返回值为 null
null and 0 返回值为 0
12345

(3) 逻辑或

逻辑或通常使用 OR
逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回 0
当有一个操作数为 null 时，如果另一个操作数为非 0 值，则返回值为 1，否则为 null
如两个操作数均为 null，则返回值为 null

mysql> select 2 or 3,2 or 2,2 or null,0 or 0,null or null,0 or null;
+--------+--------+-----------+--------+--------------+-----------+
| 2 or 3 | 2 or 2 | 2 or null | 0 or 0 | null or null | 0 or null |
+--------+--------+-----------+--------+--------------+-----------+
|      1 |      1 |         1 |      0 |         NULL |      NULL |
+--------+--------+-----------+--------+--------------+-----------+
1 row in set (0.00 sec)

mysql> select 0 or null or 2;
+----------------+
| 0 or null or 2 |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)
123456789101112131415

(4) 逻辑异或

两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0
如果一个为 0，另一个为非 0，则返回结果为 1
当任意一个值为 NULL 时，返回值为 NULL

mysql> select 2 xor 3,1 xor 0,0 xor null,null xor null;
+---------+---------+------------+---------------+
| 2 xor 3 | 1 xor 0 | 0 xor null | null xor null |
+---------+---------+------------+---------------+
|       0 |       1 |       NULL |          NULL |
+---------+---------+------------+---------------+
1 row in set (0.00 sec)
1234567

4. 位运算符

位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式（1010 1111），然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。MySQL 支持 6 种位运算符，具体如表所示。

位运算符	描述
&	按位与
\|	按位或
~	按位取反
^	按位异或
<<	按位左移
>>	按位右移

mysql> select 10 & 15,10 | 15,10 ^ 15,5 &~ 1;
+---------+---------+---------+--------+
| 10 & 15 | 10 | 15 | 10 ^ 15 | 5 &~ 1 |
+---------+---------+---------+--------+
|      10 |      15 |       5 |      4 |
+---------+---------+---------+--------+
1 row in set (0.00 sec)
1234567

说明：

10 转换为二进制数是 1010, 15 转换为二进制数是 1111
按位与运算（&），是对应的二进制位都是1的，它们的运算结果为 1，否则为 0，所以 10 & 15 的结果为 10。
按位或运算（|），是对应的二进制位有一个或两个为 1 的，运算结果为 1，否则为 0， 所以 10 | 15 的结果为 15。
按位异或运算（^），是对应的二进制位不相同时，运算结果 1，否则为 0，所以 10 ^ 15 的结果为 5。
按位取反（~），是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1。数字 1 的二进制是 0001，取反后变为 1110, 数字 5 的二进制是 0101，将 1110 和 0101 进行求与操作，其结果是二进制的 0100，转换为十进制就是 4。
12345
以上不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算，优先级如下表所示：

优先级	运算符
1	！
2	~
3	^
4	*、/、%
5	+,-
6	>>,<<
7	&
8	\|
9	=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
10	BETWEEN,CASE,WHEN,THEN,ELSE
11	NOT
12	&&,AND
13	\|\|,OR,XOR
14	:=

六、连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的记录行结合起来，基于这些表之间的共同字段，进行数据的拼接。
首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。
使用较多的连接查询包括：内连接、左连接和右连接

以左边表格为主表，叫左连查询
以右边表格为主表，叫右连查询

案例准备

mysql> create database info;
mysql> use info;
mysql> create table test1 (
    -> a_id int(11) default null,
    -> a_name varchar(32) default null,
    -> a_level int(11) default null);

mysql> create table test2 (
    -> b_id int(11) default null,
    -> b_name varchar(32) default null,
    -> b_level int(11) default null);

insert into test1 values (1,'aaaa',10);
insert into test1 values (2,'bbbb',20);
insert into test1 values (3,'cccc',30);
insert into test1 values (4,'dddd',40);
insert into test2 values (2,'bbbb',20);
insert into test2 values (3,'cccc',30);
insert into test2 values (5,'eeee',50);
insert into test2 values (6,'ffff',60);

mysql> select * from test1;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
|    1 | aaaa   |      10 |
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
|    4 | dddd   |      40 |
+------+--------+---------+
4 rows in set (0.00 sec)

mysql> select * from test2;
+------+--------+---------+
| b_id | b_name | b_level |
+------+--------+---------+
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
|    5 | eeee   |      50 |
|    6 | ffff   |      60 |
+------+--------+---------+
4 rows in set (0.00 sec)
123456789101112131415161718192021222324252627282930313233343536373839404142

1. 内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合
通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN
同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表
简而言之：
内连查询就是通过 inner join 的方式将两张表指定的相同字段的记录行输出出来

语法格式：
SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
12
mysql> select a.a_id,a.a_name,a.a_level from test1 a inner join test2 b on a.a_id=b.b_id;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
+------+--------+---------+
2 rows in set (0.00 sec)
12345678

在这里插入图片描述

2. 左连接

左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示
左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。

mysql> select * from test1 a left join test2 b on a.a_name=b.b_name;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
|    1 | aaaa   |      10 | NULL | NULL   |    NULL |
|    4 | dddd   |      40 | NULL | NULL   |    NULL |
+------+--------+---------+------+--------+---------+
#join左表的test1表作为主表全部显示出来，右表test2只会显示符合搜索条件的记录，右表记录不足的地方均为NULL
12345678910

3. 右连接

右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。
右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配

mysql> select * from test1 a right join test2 b on a.a_name=b.b_name;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
| NULL | NULL   |    NULL |    5 | eeee   |      50 |
| NULL | NULL   |    NULL |    6 | ffff   |      60 |
+------+--------+---------+------+--------+---------+
#join右边的test2表作为主表全部显示出来，左表test1只会显示符合搜索条件的记录，坐表记录不足的地方均为NULL