MySQL 8——学习笔记03（插入、更新、删除 数据 [DML语句]）

一、插入数据1.1 插入所有字段、插入部分字段1.2 同时插入多条记录（批量插入）1.3 将查询结果插入到表中二、更新数据三、删除数据四、为表增加计算列五、查询数据5.1 基本查询语句5.2 表单查询5.2.1 查询指定字段5.2.2 带 in 或 not in 关键字的查询5.2.3 带 between and 的范围查询5.2.4 带 like 的字符匹配查询5.2.5 查询空值（is null）5.2.6 带 and 的多条件查询5.2.7 带 or 的多条件查询5.2.8 查询结果不重复（distinct）5.2.9 对查询结果排序（order by）5.2.10 分组查询（group by分组、having过滤）5.2.11 使用 limit 限制查询结果的数量5.3 使用聚合函数查询5.3.1 count() 函数5.3.2 sum() 函数5.3.3 avg() 函数5.3.4 max() 函数5.3.5 min() 函数5.4 连接查询5.4.1 内连接（inner join）5.4.2 自连接5.4.3 外连接（left join、right join）5.4.4 复合条件连接查询5.5 子查询5.5.1 带 any、some 关键字的子查询5.5.2 带all关键字的子查询5.5.3 带exists关键字的子查询5.5.4 带in关键字的子查询5.5.5 带比较运算符的子查询5.6 合并查询结果5.7 为表和字段取别名5.7.1 为表取别名5.7.2 为字段取别名5.8 使用正则表达式查询5.9 通用表表达式5.10 其它查询5.10.1 查询下一个自增主键id的值5.11 复杂查询5.11.1 FIELD（排序）

声明：

本博客摘录自MySQL 8 从入门到精通（视频教学版） 王英英©著。部分内容总结自互联网。

一、插入数据

1.1 插入所有字段、插入部分字段

// 语法insert into table_name (column_list) values (value_list);// 案例insert into person (id,name,age,info) value (1,'zhangsan',21,'大学生');// 插入全部字段的话可以省略所有字段名insert into person value (1,'zhangsan',21,'大学生');// 插入部分字段的话不能省略字段名insert into person(id,name) values (1,'zhangsan');

特点：

insert语句用来给表的字段插入值，字段的类型和值的类型要一致或者兼容，字段个数和值个数要必须一致。如果插入全部字段的值，可以省略所有字段名。如果插入部分字段的值，则必须写字段名，没有插入值的字段会使用NULL值或默认值填充。即没有提供值的字段要么允许为空(null)，要么有默认值，否则会出错。

1.2 同时插入多条记录（批量插入）

// 语法insert into table_name (column_list) values (value_list),(value_list), ... ;// 案例insert into person (id,name,info) values (1,'zhangsan','大学生'),(2,'lisi','研究生'),(3,'wangwu','博士生');

1.3 将查询结果插入到表中

如果想要从另一个表中合并个人信息到 person 表，只需要使用一条 insert 语句和一条 select 语句组成的组合语句即可快速地从一个或多个表中想一个表中插入多行。

// 语法insert into table_name (column_list1) select (column_list2) from table_name2 where (condition);// 案例insert into new_table (t_id,t_name) select s_id,s_name from old_table;

二、更新数据

// 语法update table_nameset column_name1 = value1,column_name2 = value2, .. ,column_namen = valuenwhere (condition)// 案例1update person set age = 15, name = 'lisi' where id = 1;// 案例2update person set info = 'student' where id between 18 to 22;

如果省略where子句，表示修改表的所有记录。

三、删除数据

// 语法delete from table_name (where <condition>);// 删除表的全部记录truncate table table_name;// 案例 1delete from person where id = 3;// 案例 2delete from person where age between 18 to 22;// 案例 3delete from person;

如果省略where子句，表示删除表的所有记录。

面试题：delete 和 truncate 删除的区别？

truncate不能加where条件，而delete可以加where条件。truncate删除不能回滚(永久删除)，delete删除可以回滚。truncate删除表的全部记录，效率比delete语句高。

四、为表增加计算列

什么叫计算列呢，简单来说就是某一列的值是通过别的列计算得来的。

// 增加计算列的语法字段名 字段类型 [generated always] as (表达式)[virtual | stored] [unique [key]] [comment comment][not null | null] [[primary] key]// 案例create table tb1{id int(9) not null auto_increment,a int(9) default null,b int(9) default null,c int(9) generated always as ((a + b)) virtual,primary key ('id')};

五、查询数据

5.1 基本查询语句

// 基本查询语句语法select [字段1,,字段2,...,字段n] from [表或视图] where [查询条件];// 完整结构语法SELECT { * | <字段列表>}FROM 表名 [WHERE 子句] [GROUP BY 子句] [HAVING 子句] [ORDER BY 子句] [LIMIT 起始记录的索引，每页记录数];

{ * | <字段列表>}：包含型号通配符和字段列表，表示查询的字段。其中字段列表至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不加逗号。from <表1>,<表2>…：表1和表2表示查询数据的来源，可以是单个或者多个。where：子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。group by <字段>：该子句告诉 mysql 如何显示查询出来的数据，并按照指定的字段分组。[order by <字段>]：该子句告诉 mysql按照什么样的顺序显示查询出来的数据，可以进行的排序有 升序（ASC），降序（DESC）。[limit [< offset >,] < row count >]：mysql每次显示查询出来的数据条数。

5.2 表单查询

5.2.1 查询指定字段

// 查询所有字段select * from 表名;// 查询指定字段select 列名 from 表名;// 查询多个字段select 字段名1, 字段名2, 字段名3, ... ,字段名n from 表名;// 查询指定记录select 字段名1, 字段名2, 字段名3, ... ,字段名nfrom 表名where 查询条件

5.2.2 带 in 或 not in 关键字的查询

IN 操作符用来查询满足指定范围内的条件记录，使用 IN 操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

// 语法select 字段名1, 字段名2, 字段名3, ... ,字段名nfrom 表名where 字段名 {in | not in} (值1,值2,...)

【in 关键字查询案例】

mysql> select s_id,f_name,f_price-> from fruits-> where s_id in (101,102)-> order by f_price;+------+------------+---------+| s_id | f_name| f_price |+------+------------+---------+| 101 | cherry| 3.20 || 101 | apple| 5.20 || 102 | grape| 5.30 || 101 | blackberry | 10.20 || 102 | banana| 10.30 || 102 | orange| 11.20 |+------+------------+---------+6 rows in set (0.07 sec)

【not in 关键字查询案例】

mysql> select s_id,f_name,f_price-> from fruits-> where s_id not in (101,102)-> order by f_price;+------+---------+---------+| s_id | f_name | f_price |+------+---------+---------+| 103 | apricot | 2.20 || 105 | xbabay | 2.60 || 107 | xxxx | 3.60 || 107 | xbababa | 3.60 || 104 | lemon | 6.40 || 104 | berry | 7.60 || 105 | melon | 8.20 || 103 | coconut | 9.20 || 105 | xxtt | 11.60 || 106 | mango | 15.60 |+------+---------+---------+10 rows in set (0.10 sec)

5.2.3 带 between and 的范围查询

【案例】

mysql> select f_name,f_price-> from fruits-> where f_price between 2.00 and 6.22-> order by f_price;+------------+---------+| f_name| f_price |+------------+---------+| apricot | 2.20 || xbabay| 2.60 || cherry| 3.20 || xxxx | 3.60 || xbababa | 3.60 || apple| 5.20 || grape| 5.30 |+------------+---------+12 rows in set (0.10 sec)

5.2.4 带 like 的字符匹配查询

（1）百分号通配符 ‘%’ ，匹配任意长度的字符，甚至包括零字符

// 案例1mysql> select f_id,f_name-> from fruits-> where f_name like 'b%';+------+------------+| f_id | f_name|+------+------------+| b1 | blackberry || b2 | berry|| t1 | banana|+------+------------+3 rows in set (0.08 sec)// 案例2mysql> select f_id,f_name from fruits where f_name like '%g%';+------+--------+| f_id | f_name |+------+--------+| bs1 | orange || m1 | mango || t2 | grape |+------+--------+3 rows in set (0.08 sec)// 案例3mysql> select f_id,f_name from fruits where f_name like 'b%y';+------+------------+| f_id | f_name|+------+------------+| b1 | blackberry || b2 | berry|+------+------------+2 rows in set (0.12 sec)

（2）下划线通配符 ‘_’ ，一次只能匹配任意一个字符

// y 前面输入了四个下划线 _mysql> select f_id,f_name from fruits where f_name like '____y';+------+--------+| f_id | f_name |+------+--------+| b2 | berry |+------+--------+1 row in set (0.08 sec)

（3）字符串拼接CONCAT关键字

select f_id,f_name from fruits where f_name LIKE CONCAT( '%', 'berry', '%' );

5.2.5 查询空值（is null）

空值（NULL），不同于 0 ，也不同于弄字符串。

空值一般表示数据未知、不适用或将在以后添加数据。使用is null和is not null关键字来判断。

【查询空值案例】

// 查询空值案例mysql> SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL;+-------+----------+---------+| c_id | c_name | c_email |+-------+----------+---------+| 10003 | Netbhood | NULL |+-------+----------+---------+1 row in set (0.09 sec)// 查询非空值案例mysql> SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL;+-------+---------+-------------------+| c_id | c_name | c_email |+-------+---------+-------------------+| 10001 | RedHook | LMing@|| 10002 | Stars | Jerry@ || 10004 | JOTO | sam@ |+-------+---------+-------------------+3 rows in set (0.09 sec)

5.2.6 带 and 的多条件查询

在 select 查询时，可以添加查询的限制条件，这样可以使查询的结果更加精确。

// 案例mysql> SELECT f_id, f_price, f_name FROM fruits WHERE s_id = '101' AND f_price >=5;+------+---------+------------+| f_id | f_price | f_name|+------+---------+------------+| a1 | 5.20 | apple|| b1 | 10.20 | blackberry |+------+---------+------------+2 rows in set (0.09 sec)// 案例：在fruits表中查询s_id = 101或者102，且f_price大于5，并且f_name=‘apple’的水果价格和名称，SQL语句如下：mysql> select f_id,f_price,f_name from fruits-> where s_id in ('101','102') and f_price >= 5 and f_name = 'apple';+------+---------+--------+| f_id | f_price | f_name |+------+---------+--------+| a1 | 5.20 | apple |+------+---------+--------+1 row in set (0.08 sec)

5.2.7 带 or 的多条件查询

与 and 相反，在where声明中使用 or 操作符，表示只需要满足其中一个条件的记录即可返回。

or 也可以连接两个甚至多个查询条件，多个条件表达式之间用 or 分开。

// 在fruits表中查询s_id = 101或者102，且f_price大于5，并且f_name=‘apple’的水果价格和名称mysql> select s_id,f_name,f_price from fruits where s_id = 101 or s_id = 102;+------+------------+---------+| s_id | f_name| f_price |+------+------------+---------+| 101 | apple| 5.20 || 101 | blackberry | 10.20 || 102 | orange| 11.20 || 101 | cherry| 3.20 || 102 | banana| 10.30 || 102 | grape| 5.30 |+------+------------+---------+6 rows in set (0.05 sec)// 上面的or查询可以替换成 inmysql> SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN(101,102);+------+------------+---------+| s_id | f_name| f_price |+------+------------+---------+| 101 | apple| 5.20 || 101 | blackberry | 10.20 || 102 | orange| 11.20 || 101 | cherry| 3.20 || 102 | banana| 10.30 || 102 | grape| 5.30 |+------+------------+---------+6 rows in set (0.06 sec)

5.2.8 查询结果不重复（distinct）

在 select 语句中，可以使用distinct 关键字指示 mysql 小褚重复的记录值。

// 语法select distinct 字段名 from 表名;// 案例mysql> select distinct s_id from fruits;+------+| s_id |+------+| 101 || 103 || 104 || 107 || 102 || 105 || 106 |+------+7 rows in set (0.07 sec)

5.2.9 对查询结果排序（order by）

mysql 可以通过 select 语句中使用order by子句对查询的结果进行排序。

默认升序（ASC）排列；可指定DESC（降序）排列。

// 单例排序//【案例】查询fruits表的f_name字段值，并对其进行排序select f_name from fruits order by f_name;// 多列排序//【案例】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序select f_name from fruits order by f_name,f_price;// 指定排序方向//【案例】查询fruits表，先按f_price降序排序，再按f_name字段升序排序mysql> SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name;+---------+------------+| f_price | f_name|+---------+------------+| 15.60 | mango|| 11.60 | xxtt || 11.20 | orange|| 10.30 | banana|| 10.20 | blackberry || 9.20 | coconut || 8.20 | melon|| 7.60 | berry|| 6.40 | lemon|| 5.30 | grape|| 5.20 | apple|| 3.60 | xbababa | //f_price相同时，姓名升序排列| 3.60 | xxxx | //f_price相同时，姓名升序排列| 3.20 | cherry|| 2.60 | xbabay|| 2.20 | apricot |+---------+------------+16 rows in set (0.08 sec)

注意：

在多列排序中，如果第一列数据中所有值都是唯一的，将不会再对第二列进行排序。当使用 rollup 时，不能同时使用 order by 子句进行结果排序，互斥！！

5.2.10 分组查询（group by分组、having过滤）

（1）group by 分组

分组查询是对数据按照某个或多个字段进行分组。group by关键字对数据进行分组。

group by 关键字通常和聚合函数一起使用，比如 max(),min(),count(),sum(),avg()。

【语法】

[group by 字段] [having <条件表达式>]//【注释】字段：为进行分组时所依据的列名称；having <条件表达式> ：指定满足表达式限定条件的结果将被显示。

【案例】根据s_id对fruits表中的数据进行分组

mysql> SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;+------+-------+| s_id | Total |+------+-------+| 101 |3 || 102 |3 || 103 |2 || 104 |2 || 105 |3 || 106 |1 || 107 |2 |+------+-------+7 rows in set (0.08 sec)

【案例】根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来。（使用group_count() 函数）

mysql> SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id;+------+-------------------------+| s_id | Names |+------+-------------------------+| 101 | apple,blackberry,cherry || 102 | orange,banana,grape|| 103 | apricot,coconut || 104 | berry,lemon || 105 | melon,xbabay,xxtt || 106 | mango || 107 | xxxx,xbababa |+------+-------------------------+7 rows in set (0.07 sec)

（2）having 过滤分组

group by 可以使用 having 一起限定显示记录所需满足的条件，只有满足条件的分组才回被显示。

【案例】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息

mysql> SELECT s_id, GROUP_CONCAT(f_name) AS Names -> from fruits-> group by s_id having count(f_name) >1;+------+-------------------------+| s_id | Names |+------+-------------------------+| 101 | apple,blackberry,cherry || 102 | orange,banana,grape|| 103 | apricot,coconut || 104 | berry,lemon || 105 | melon,xbabay,xxtt || 107 | xxxx,xbababa |+------+-------------------------+6 rows in set (0.07 sec)

（3）在 group by 子句中使用 with rollup

使用with rollup 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

【案例】根据s_id对fruits表中的数据进行分组，并显示记录数量

mysql> SELECT s_id, COUNT(*) AS Total -> from fruits-> group by s_id with rollup;+------+-------+| s_id | Total |+------+-------+| 101 |3 || 102 |3 || 103 |2 || 104 |2 || 105 |3 || 106 |1 || 107 |2 || NULL | 16 |+------+-------+8 rows in set (0.07 sec)

当使用 rollup 时，不能同时使用 order by 子句进行结果排序，互斥！！

（4）多字段分组

【案例】根据s_id和f_name字段对fruits表中的数据进行分组

SELECT * FROM fruits group by s_id,f_name;

（5）group by 和 order by 一起使用

【案例】查询订单价格大于100的订单号和总订单价格

mysql> select o_num,sum(quantity * item_price) as orderTotal-> from orderitems-> group by o_num-> having orderTotal >= 100;+-------+------------+| o_num | orderTotal |+-------+------------+| 30001 | 268.80|| 30003 | 1000.00 || 30004 | 125.00|| 30005 | 236.85|+-------+------------+4 rows in set (0.06 sec)

【案例】上面的返回的结果中orderTotal列的总订单价格并没有按照一定顺序显示，接下来，使用ORDER BY关键字按总订单价格排序显示结果

mysql> SELECT o_num, SUM(quantity * item_price) AS orderTotal-> FROM orderitems-> GROUP BY o_num-> HAVING SUM(quantity*item_price) >= 100-> order by orderTotal;+-------+------------+| o_num | orderTotal |+-------+------------+| 30004 | 125.00|| 30005 | 236.85|| 30001 | 268.80|| 30003 | 1000.00 |+-------+------------+4 rows in set (0.07 sec)

Having 和 where 的区别：

其它区别：

如果用了别名，则后面的 group by 和 having 子句就可以使用列的别名。where子句不支持列的别名。

（6）group by 不再隐式排序

从 mysql 8.0 版本开始，MySQL 对 group by 字段不在隐式排序。如果确实需要培训，必须加上 order by 子句。

5.2.11 使用 limit 限制查询结果的数量

【语法】

limit [位移偏移量,] 行数// 解释位移偏移量：指从哪一行开始显示；可选参数，如果不指定默认从第一条记录开始（第一条记录位置偏移量为 0，递增1）。行数：指要返回多少条记录。

【案例】

//省略偏移量mysql> SELECT * From fruits LIMIT 4;+------+------+------------+---------+| f_id | s_id | f_name| f_price |+------+------+------------+---------+| a1 | 101 | apple| 5.20 || a2 | 103 | apricot | 2.20 || b1 | 101 | blackberry | 10.20 || b2 | 104 | berry| 7.60 |+------+------+------------+---------+4 rows in set (0.08 sec)// 加上偏移量mysql> SELECT * From fruits LIMIT 1,4;+------+------+------------+---------+| f_id | s_id | f_name| f_price |+------+------+------------+---------+| a2 | 103 | apricot | 2.20 || b1 | 101 | blackberry | 10.20 || b2 | 104 | berry| 7.60 || b5 | 107 | xxxx | 3.60 |+------+------+------------+---------+4 rows in set (0.07 sec)

limit 使用场景（重点）

limit 用于分页查询，比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来，而是一页显示固定的条数。

假设每页显示size条记录，查询第page页的数据，通用SQL如下：

// 分页查询通用 SQLselect 字段 from 表 limit （page-1）*size, size;

5.3 使用聚合函数查询

MySQL 常用聚合函数

5.3.1 count() 函数

count() 函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。

count(*)计算表中总的行数，不管某列是否有数值或者为空值。count(字段名)计算指定列下的总的行数，计算时忽略空值的行。

【案例】在orderitems表中，使用COUNT()函数统计不同订单号中订购的水果种类

mysql> SELECT o_num, COUNT(f_id) FROM orderitems GROUP BY o_num;+-------+-------------+| o_num | COUNT(f_id) |+-------+-------------+| 30001 | 4 || 30002 | 1 || 30003 | 1 || 30004 | 1 || 30005 | 4 |+-------+-------------+5 rows in set (0.15 sec)

5.3.2 sum() 函数

sum() 是一个求总和的函数，返回指定列值的总和。

sum() 函数在计算时，忽略值为 null 的行。

【案例】在orderitems表中查询30005号订单一共购买的水果总量

// 案例实现SQL语句mysql> select sum(quantity) as items_total-> from orderitems-> where o_num = 30005;+-------------+| items_total |+-------------+| 30|+-------------+1 row in set (0.07 sec)// 查询语句mysql> select * from orderitems where o_num = 30005;+-------+--------+------+----------+------------+| o_num | o_item | f_id | quantity | item_price |+-------+--------+------+----------+------------+| 30005 |1 | c0 | 5 | 10.00|| 30005 |2 | b1 | 10 | 8.99 || 30005 |3 | a2 | 10 | 2.20 || 30005 |4 | m1 | 5 | 14.99|+-------+--------+------+----------+------------+4 rows in set (0.07 sec)

sum() 可以和 group by 一起使用，来计算每个分组的总和；

【案例】在orderitems表中，使用SUM()函数统计不同订单号中订购的水果总量；

mysql> select o_num,sum(quantity) as items_total-> from orderitems-> group by o_num;+-------+-------------+| o_num | items_total |+-------+-------------+| 30001 | 33|| 30002 | 2 || 30003 | 100 || 30004 | 50|| 30005 | 30|+-------+-------------+5 rows in set (0.07 sec)

5.3.3 avg() 函数

avg() 函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

【案例】在fruits表中，查询s_id=103的供应商的水果价格的平均值

mysql> SELECT AVG(f_price) AS avg_price-> from fruits-> where s_id = 103;+-----------+| avg_price |+-----------+| 5.700000 |+-----------+1 row in set (0.07 sec)

avg() 可以与 group by 一起使用，来计算每个分组的平均值。group by 关键字根据 字段 对记录进行分组，然后计算出每个分组的平均值。

【案例】在fruits表中，查询每一个供应商的水果价格的平均值

mysql> select s_id,avg(f_price) as avg_price from fruits group by s_id;+------+-----------+| s_id | avg_price |+------+-----------+| 101 | 6.200000 || 102 | 8.933333 || 103 | 5.700000 || 104 | 7.000000 || 105 | 7.466667 || 106 | 15.600000 || 107 | 3.600000 |+------+-----------+7 rows in set (0.07 sec)

5.3.4 max() 函数

max() 返回指定列中的最大值。

【案例】在fruits表中查找市场上价格最高的水果

mysql> select max(f_price) as max_price from fruits;+-----------+| max_price |+-----------+| 15.60|+-----------+1 row in set (0.07 sec)

max() 也可以和group by 关键字一起使用，求每个分组中的最大值。

【案例】在fruits表中查找不同供应商提供的价格最高的水果

mysql> select s_id,max(f_price) as max_price-> from fruits-> group by s_id;+------+-----------+| s_id | max_price |+------+-----------+| 101 | 10.20|| 102 | 11.20|| 103 | 9.20|| 104 | 7.60|| 105 | 11.60|| 106 | 15.60|| 107 | 3.60|+------+-----------+7 rows in set (0.07 sec)

5.3.5 min() 函数

min() 函数返回查询列中的最小值。

【案例】在fruits表中查找市场上价格最低的水果

mysql> select min(f_price) as min_price from fruits;+-----------+| min_price |+-----------+| 2.20|+-----------+1 row in set (0.07 sec)

min() 也可以和 group by 关键字一起使用，求出每个分组中的最小值。

【案例】在fruits表中查找不同供应商提供的价格最低的水果

mysql> select s_id,min(f_price) as min_price-> from fruits group by s_id;+------+-----------+| s_id | min_price |+------+-----------+| 101 | 3.20|| 102 | 5.30|| 103 | 2.20|| 104 | 6.40|| 105 | 2.60|| 106 | 15.60|| 107 | 3.60|+------+-----------+7 rows in set (0.07 sec)

5.4 连接查询

用到的表结构：

mysql> desc fruits;+---------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| f_id | char(10)| NO | PRI | NULL | || s_id | int(11)| NO || NULL | || f_name | char(255) | NO || NULL | || f_price | decimal(8,2) | NO || NULL | |+---------+--------------+------+-----+---------+-------+4 rows in set (0.05 sec)mysql> desc suppliers;+--------+----------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra|+--------+----------+------+-----+---------+----------------+| s_id | int(11) | NO | PRI | NULL | auto_increment || s_name | char(50) | NO || NULL ||| s_city | char(50) | YES || NULL ||| s_zip | char(10) | YES || NULL ||| s_call | char(50) | NO || NULL ||+--------+----------+------+-----+---------+----------------+5 rows in set (0.06 sec)

5.4.1 内连接（inner join）

内连接（inner join）：使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。也就是说，在内连接查询中，只有满足条件的记录才会出现在结果关系中。

【隐式内连接】【案例】从fruits表中查询f_name、f_price字段，从suppliers表中查询s_id、s_name

mysql> select suppliers.s_id,s_name,f_name,f_price-> from fruits,suppliers-> where fruits.s_id = suppliers.s_id;+------+----------------+------------+---------+| s_id | s_name | f_name| f_price |+------+----------------+------------+---------+| 101 | FastFruit Inc. | apple| 5.20 || 103 | ACME | apricot | 2.20 || 101 | FastFruit Inc. | blackberry | 10.20 || 104 | FNK Inc. | berry| 7.60 || 107 | DK Inc. | xxxx | 3.60 || 102 | LT Supplies | orange| 11.20 || 105 | Good Set | melon| 8.20 || 101 | FastFruit Inc. | cherry| 3.20 || 104 | FNK Inc. | lemon| 6.40 || 106 | Just Eat Ours | mango| 15.60 || 105 | Good Set | xbabay| 2.60 || 105 | Good Set | xxtt | 11.60 || 103 | ACME | coconut | 9.20 || 102 | LT Supplies | banana| 10.30 || 102 | LT Supplies | grape| 5.30 || 107 | DK Inc. | xbababa | 3.60 |+------+----------------+------------+---------+16 rows in set (0.09 sec)

where 子句在这里作为过滤条件，指明只有两个表中的 s_id 字段值相等的时候才符合连接查询的条件。

【显式内连接】【推荐】【案例】在fruits表和suppliers表之间，使用 INNER JOIN 语法进行内连接查询（效果等同与上面的连接查询）

// 多加了一个f_sid是为了更直观的了解连接情况mysql> select suppliers.s_id,s_name,fruits.s_id as f_sid,f_name,f_price-> from fruits inner join suppliers-> on fruits.s_id = suppliers.s_id;+------+----------------+-------+------------+---------+| s_id | s_name | f_sid | f_name| f_price |+------+----------------+-------+------------+---------+| 101 | FastFruit Inc. | 101 | apple| 5.20 || 103 | ACME | 103 | apricot | 2.20 || 101 | FastFruit Inc. | 101 | blackberry | 10.20 || 104 | FNK Inc. | 104 | berry| 7.60 || 107 | DK Inc. | 107 | xxxx | 3.60 || 102 | LT Supplies | 102 | orange| 11.20 || 105 | Good Set | 105 | melon| 8.20 || 101 | FastFruit Inc. | 101 | cherry| 3.20 || 104 | FNK Inc. | 104 | lemon| 6.40 || 106 | Just Eat Ours | 106 | mango| 15.60 || 105 | Good Set | 105 | xbabay| 2.60 || 105 | Good Set | 105 | xxtt | 11.60 || 103 | ACME | 103 | coconut | 9.20 || 102 | LT Supplies | 102 | banana| 10.30 || 102 | LT Supplies | 102 | grape| 5.30 || 107 | DK Inc. | 107 | xbababa | 3.60 |+------+----------------+-------+------------+---------+16 rows in set (0.10 sec)

使用这种语法的时候，连接的条件使用 on 子句，而不是 where，on 和 where 后面指定的条件相同。

5.4.2 自连接

自连接是把一张表看成两张来做连接查询，一定要给表取别名。它是特殊的内连接或外连接。

【案例】查询供应f_id= ‘a1’的水果供应商提供的其他水果种类

mysql> select f1.f_id,f1.f_name-> from fruits as f1,fruits as f2-> where f1.s_id = f2.s_id and f2.f_id = 'a1';+------+------------+| f_id | f_name|+------+------------+| a1 | apple|| b1 | blackberry || c0 | cherry|+------+------------+3 rows in set (0.08 sec)

5.4.3 外连接（left join、right join）

外连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中仅是符合查询条件和连接条件的行。

外连接分为左连接或左外连接和右连接或右外连接：

left join（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。right join（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

（1）left join左连接：

左连接的结果包括 left outer 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配的行，则在相关联的结果行中，右表的所有选择列表列均为空值。

【案例】在customers表和orders表中，查询所有客户，包括没有订单的客户

// 这里取别名和多字段是为了观察连接情况mysql> SELECT customers.c_id as c_cid, orders.c_id as o_cid,orders.o_num-> from customers left outer join orders-> on customers.c_id = orders.c_id;+-------+-------+-------+| c_cid | o_cid | o_num |+-------+-------+-------+| 10001 | 10001 | 30001 || 10003 | 10003 | 30002 || 10004 | 10004 | 30003 || 10001 | 10001 | 30005 || 10002 | NULL | NULL |+-------+-------+-------+5 rows in set (0.09 sec)

（2）right join右连接：

右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。

【案例】在customers表和orders表中，查询所有订单，包括没有客户的订单

// 这里取别名和多字段是为了观察连接情况mysql> SELECT customers.c_id as c_cid, orders.c_id as o_cid,orders.o_num-> from customers right outer join orders-> on customers.c_id = orders.c_id;+-------+-------+-------+| c_cid | o_cid | o_num |+-------+-------+-------+| 10001 | 10001 | 30001 || 10003 | 10003 | 30002 || 10004 | 10004 | 30003 || NULL | 10005 | 30004 || 10001 | 10001 | 30005 |+-------+-------+-------+5 rows in set (0.10 sec)

5.4.4 复合条件连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件限制查询的结果，使查询的结果更加准确。

【案例】在customers表和orders表中，使用INNER JOIN语法查询customers表中ID为10001的客户的订单信息

// 这里取别名和多字段是为了观察连接情况mysql> SELECT customers.c_id as c_cid, orders.c_id as o_cid,orders.o_num-> from customers inner join orders-> on customers.c_id = orders.c_id and customers.c_id = 10001;+-------+-------+-------+| c_cid | o_cid | o_num |+-------+-------+-------+| 10001 | 10001 | 30001 || 10001 | 10001 | 30005 |+-------+-------+-------+2 rows in set (0.09 sec)

5.5 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。在select子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或多个表。

5.5.1 带 any、some 关键字的子查询

any和some关键字是同义词，表示满足其中的任意条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询的任何一个比较条件，就返回一个结果作为外层查询的条件。

any关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为true，则返回true

【案例】返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何1个值，即为符合查询条件的结果。

mysql> SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);+------+| num1 |+------+| 13 || 27 |+------+2 rows in set (0.09 sec)

5.5.2 带all关键字的子查询

all关键字与any和some不同，使用all时需要同时满足所有内层查询的条件。

all关键字接在一个比较操作符后面，表示与子查询返回的所有值比较为true，则返回true。

【案例】返回tbl1表中比tbl2表num2 列所有值都大的值

mysql> SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);+------+| num1 |+------+| 27 |+------+1 row in set (0.07 sec)

5.5.3 带exists关键字的子查询

exists关键字后面的参数是一个任意的子查询，，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么exists的结果为true，此时外层查询语句将进行查询；，如果子查询没有返回任何行，那么exists返回的结果是false，此时外层语句将不进行查询。

【案例】查询suppliers表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的记录

mysql> select * from fruits-> where exists-> (select s_name from suppliers where s_id = 107);+------+------+------------+---------+| f_id | s_id | f_name| f_price |+------+------+------------+---------+| a1 | 101 | apple| 5.20 || a2 | 103 | apricot | 2.20 || b1 | 101 | blackberry | 10.20 || b2 | 104 | berry| 7.60 || b5 | 107 | xxxx | 3.60 || bs1 | 102 | orange| 11.20 || bs2 | 105 | melon| 8.20 || c0 | 101 | cherry| 3.20 || l2 | 104 | lemon| 6.40 || m1 | 106 | mango| 15.60 || m2 | 105 | xbabay| 2.60 || m3 | 105 | xxtt | 11.60 || o2 | 103 | coconut | 9.20 || t1 | 102 | banana| 10.30 || t2 | 102 | grape| 5.30 || t4 | 107 | xbababa | 3.60 |+------+------+------------+---------+16 rows in set (0.09 sec)// 子查询中返回行，结果为true，然后进行外层查询mysql> select s_name from suppliers where s_id = 107;+---------+| s_name |+---------+| DK Inc. |+---------+1 row in set (0.07 sec)

exists关键字可以和条件表达式一起使用。

【案例】查询suppliers表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的f_price大于10.20的记录

mysql> select * from fruits-> where f_price > 10.02 and exists-> (SELECT s_name FROM suppliers WHERE s_id = 107);+------+------+------------+---------+| f_id | s_id | f_name| f_price |+------+------+------------+---------+| b1 | 101 | blackberry | 10.20 || bs1 | 102 | orange| 11.20 || m1 | 106 | mango| 15.60 || m3 | 105 | xxtt | 11.60 || t1 | 102 | banana| 10.30 |+------+------+------------+---------+5 rows in set (0.07 sec)

no exists与exists使用方法相同，返回的结果相反。子查询如果至少返回一行，那么no exists的结果为false，此时外层查询语句将不进行查询；如果子查询语句没有返回任何行，那么no exists返回的结果为true，此时外层查询语句将进行查询。

【案例】查询suppliers表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录

mysql> select * from fruits-> where not exists-> (SELECT s_name FROM suppliers WHERE s_id = 107);Empty set

提示

exists 和 not exists 的结果只取决于是否会返回行，而不是取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。

5.5.4 带in关键字的子查询

in 关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

【案例】在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id

mysql> select c_id from orders where o_num in-> (select o_num from orderitems where f_id = 'c0');+-------+| c_id |+-------+| 10004 || 10001 |+-------+2 rows in set (0.09 sec)

select 语句中可以使用 not in关键字，其作用与 in 正好相反。

【案例】

mysql> select c_id from orders where o_num not in-> (select o_num from orderitems where f_id = 'c0');+-------+| c_id |+-------+| 10001 || 10003 || 10005 |+-------+3 rows in set (0.07 sec)mysql> select o_num from orderitems where f_id = 'c0';+-------+| o_num |+-------+| 30003 || 30005 |+-------+2 rows in set (0.09 sec)mysql> select c_id,o_num from orders where o_num not in-> (select o_num from orderitems where f_id = 'c0');+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10005 | 30004 |+-------+-------+3 rows in set (0.07 sec)

5.5.5 带比较运算符的子查询

子查询可以使用比较运算符，如"<“、”<=“、”=“、”>=“、”>" 和 "!="等。

【案例】在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类

mysql> select s_id,f_name from fruits-> where s_id = -> (select s1.s_id from suppliers as s1 where s1.s_city = 'Tianjin');+------+------------+| s_id | f_name|+------+------------+| 101 | apple|| 101 | blackberry || 101 | cherry|+------+------------+3 rows in set (0.07 sec)mysql> select s1.s_id from suppliers as s1 where s1.s_city = 'Tianjin';+------+| s_id |+------+| 101 |+------+1 row in set (0.07 sec)

【案例】在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有非该供应商提供的水果的种类

mysql> select s_id,f_name from fruits-> where s_id <>-> (select s1.s_id from suppliers as s1 where s1.s_city = 'Tianjin');+------+---------+| s_id | f_name |+------+---------+| 103 | apricot || 104 | berry || 107 | xxxx || 102 | orange || 105 | melon || 104 | lemon || 106 | mango || 105 | xbabay || 105 | xxtt || 103 | coconut || 102 | banana || 102 | grape || 107 | xbababa |+------+---------+13 rows in set (0.08 sec)

5.6 合并查询结果

利用 union 关键字，可以给出多条 select 语句，并将他们的结果组合成单个结果集。

合并时，两个表对应的列数和数据类型必须相同。各个 select 语句之间使用 union 或 union all 关键字分隔。union 不使用关键字 all，执行的时候删除重复的记录，所有返回的行都是唯一的。union 使用关键字 all 的作用是不删除重复行也不对结果进行排序。

【语法】

select column,... from table1union [all]select column,... from table2

【案例】查询所有价格小于9的水果的信息，查询s_id等于101和103所有的水果的信息，使用UNION连接查询结果

mysql> select s_id,f_name,f_price from fruits-> where f_price < 9.0-> union-> select s_id,f_name,f_price from fruits-> where s_id in(101,103);+------+------------+---------+| s_id | f_name| f_price |+------+------------+---------+| 101 | apple| 5.20 || 103 | apricot | 2.20 || 104 | berry| 7.60 || 107 | xxxx | 3.60 || 105 | melon| 8.20 || 101 | cherry| 3.20 || 104 | lemon| 6.40 || 105 | xbabay| 2.60 || 102 | grape| 5.30 || 107 | xbababa | 3.60 || 101 | blackberry | 10.20 || 103 | coconut | 9.20 |+------+------------+---------+12 rows in set (0.08 sec)// 可以单独查看两个查询语句

【案例】查询所有价格小于9的水果的信息，查询s_id等于101和103的所有水果的信息，使用UNION ALL连接查询结果

mysql> select s_id,f_name,f_price from fruits-> where f_price < 9.0-> union all-> select s_id,f_name,f_price from fruits-> where s_id in(101,103);+------+------------+---------+| s_id | f_name| f_price |+------+------------+---------+| 101 | apple| 5.20 || 103 | apricot | 2.20 || 104 | berry| 7.60 || 107 | xxxx | 3.60 || 105 | melon| 8.20 || 101 | cherry| 3.20 || 104 | lemon| 6.40 || 105 | xbabay| 2.60 || 102 | grape| 5.30 || 107 | xbababa | 3.60 || 101 | apple| 5.20 || 103 | apricot | 2.20 || 101 | blackberry | 10.20 || 101 | cherry| 3.20 || 103 | coconut | 9.20 |+------+------------+---------+15 rows in set (0.09 sec)

【对比】

5.7 为表和字段取别名

5.7.1 为表取别名

为了方便操作或者需要多次使用相同的表时，可以为表指定别名，用这个别名代表原来的名称。

【语法】表名 [as] 表别名（as关键字可以省略）

【案例】

mysql> select c.c_id,o.o_num-> from customers as c left outer join orders as o-> on c.c_id = o.c_id;+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 || 10001 | 30005 || 10002 | NULL |+-------+-------+5 rows in set (0.07 sec)

5.7.2 为字段取别名

【语法】列名 [as] 列表名（as关键字可以省略）

【案例】查询fruits表，为f_name取别名fruit_name，f_price取别名fruit_price，为fruits表取别名f1，查询表中f_price < 8的水果的名称

mysql> SELECT f1.f_name AS fruit_name, f1.f_price fruit_price-> from fruits as f1-> where f1.f_price <8;+------------+-------------+| fruit_name | fruit_price |+------------+-------------+| apple| 5.20 || apricot | 2.20 || berry| 7.60 || xxxx | 3.60 || cherry| 3.20 || lemon| 6.40 || xbabay| 2.60 || grape| 5.30 || xbababa | 3.60 |+------------+-------------+9 rows in set (0.08 sec)

where 子句后面不能使用 字段别名；

【案例】查询suppliers表中字段s_name和s_city，使用CONCAT函数连接这两个字段值，并取列别名为suppliers_title

// 没取别名mysql> SELECT CONCAT(TRIM(s_name) , ' (', TRIM(s_city), ')')-> from suppliers-> order by s_name;+-------------------------------------------------+| CONCAT(TRIM(s_name) , ' (', TRIM(s_city), ')') |+-------------------------------------------------+| ACME (Shanghai) || DK Inc. (Zhengzhou) || FastFruit Inc. (Tianjin) || FNK Inc. (Zhongshan) || Good Set (Taiyuang) || Just Eat Ours (Beijing)|| LT Supplies (Chongqing)|+-------------------------------------------------+7 rows in set (0.08 sec)// 取别名mysql> SELECT CONCAT(TRIM(s_name) , ' (', TRIM(s_city), ')') as suppliers_title-> from suppliers-> order by s_name;+--------------------------+| suppliers_title|+--------------------------+| ACME (Shanghai)|| DK Inc. (Zhengzhou)|| FastFruit Inc. (Tianjin) || FNK Inc. (Zhongshan)|| Good Set (Taiyuang)|| Just Eat Ours (Beijing) || LT Supplies (Chongqing) |+--------------------------+7 rows in set (0.08 sec)

5.8 使用正则表达式查询

正则表达式通常被用来检索或替换哪些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。

MySQL 中使用 regexp 关键字指定正则表达式的字符匹配模式。

【案例】

// 用符号 “.” 来替代字符串中的任意一个字符（包括空格）mysql> select * from fruits where f_name regexp 'x.t';+------+------+--------+---------+| f_id | s_id | f_name | f_price |+------+------+--------+---------+| m3 | 105 | xx tt | 11.60 |+------+------+--------+---------+1 row in set (0.16 sec)// 匹配前面的字符串（紧挨着）至少 n 次mysql> select * from customers where c_name regexp 'O{2}';+-------+----------+---------------+---------+--------+-----------+---------------+| c_id | c_name | c_address| c_city | c_zip | c_contact | c_email |+-------+----------+---------------+---------+--------+-----------+---------------+| 10001 | RedHook | 200 Street | Tianjin | 300000 | LiMing | LMing@ || 10003 | Netbhood | 1 Sunny Place | Qingdao | 266000 | LuoCong | NULL|+-------+----------+---------------+---------+--------+-----------+---------------+2 rows in set (0.09 sec)// 使用 {n,} 或者 {n,m} 来指定字符串（紧挨着）连续出现的次数mysql> select * from fruits where f_name regexp 'ba{1,3}';+------+------+----------+---------+| f_id | s_id | f_name | f_price |+------+------+----------+---------+| m2 | 105 | xbabay | 2.60 || t1 | 102 | banana | 10.30 || t4 | 107 | xba baba | 3.60 |+------+------+----------+---------+3 rows in set (0.07 sec)

5.9 通用表表达式

待完善 。。。

5.10 其它查询

5.10.1 查询下一个自增主键id的值

查询主键自增表中下一个自增主键的id值：

SELECTAUTO_INCREMENT FROMinformation_schema.TABLES WHERETABLE_SCHEMA = 'academic_system' AND TABLE_NAME = 'teacher';

5.11 复杂查询

5.11.1 FIELD（排序）

关键字：CASE WHEN THEN、FIELD

字段复杂查询、字段排序

SELECTa.user_id,b.area_name,a.real_name userName,a.job_title,count( a.user_id ) patrolRecordCnt,CASEWHEN a.city_code IS NOT NULL AND a.county_code IS NULL THEN'市级'WHEN a.county_code IS NOT NULL AND a.town_code IS NULL THEN'县级'WHEN a.town_code IS NOT NULL AND a.village_code IS NULL THEN'镇级'WHEN a.village_code IS NOT NULL THEN'村级' END chiefLevelFROMsys_user aINNER JOIN sys_area b ON a.area_cd = b.area_cdLEFT JOIN patrol_record c ON a.user_id = c.user_idWHEREa.delete_flag = 0GROUP BYa.user_idORDER BYFIELD(userLevel,'市级','县级','镇级','村级'),a.sort_num,a.real_name