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

已于 2022-10-11 15:17:34 修改
阅读量1.2k 收藏 5
点赞数 2
分类专栏： 数据库 文章标签： mysql 学习 数据库
于 2022-02-27 15:03:01 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43680543/article/details/123129170
版权

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

声明：

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

一、插入数据

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

特点：

  1. insert语句用来给表的字段插入值，字段的类型和值的类型要一致或者兼容，字段个数和值个数要必须一致。
  2. 如果插入全部字段的值，可以省略所有字段名。
  3. 如果插入部分字段的值，则必须写字段名，没有插入值的字段会使用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_name
	set column_name1 = value1,column_name2 = value2, .. ,column_namen = valuen
	where (condition)

// 案例1
update person set age = 15, name = 'lisi' where id = 1;

// 案例2
update person set info = 'student' where id between 18 to 22;

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

三、删除数据

// 语法
delete from table_name (where <condition>);

// 删除表的全部记录
truncate table table_name;

// 案例 1
delete from person where id = 3;

// 案例 2
delete from person where age between 18 to 22;

// 案例 3
delete from person;

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

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

  1. truncate不能加where条件，而delete可以加where条件。
  2. truncate删除不能回滚(永久删除)，delete删除可以回滚。
  3. 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, ... ,字段名n
from 表名
where 查询条件

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

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

// 语法
select 字段名1, 字段名2, 字段名3, ... ,字段名n
from 表名
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）百分号通配符 ‘%’ ，匹配任意长度的字符，甚至包括零字符

// 案例1
mysql> 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)

// 案例2
mysql> 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)

// 案例3
mysql> 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@163.com     |
| 10002 | Stars   | Jerry@hotmail.com |
| 10004 | JOTO    | sam@hotmail.com   |
+-------+---------+-------------------+
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查询可以替换成 in
mysql> 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)

注意：

  1. 在多列排序中，如果第一列数据中所有值都是唯一的，将不会再对第二列进行排序。
  2. 当使用 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 的区别：

其它区别：

  1. 如果用了别名，则后面的 group by 和 having 子句就可以使用列的别名。
  2. 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如下：

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

5.3 使用聚合函数查询

MySQL 常用聚合函数

函数作用
avg()返回某列的平均值
count()返回某列的行数
max()返回某列的最大值
min()返回某列的最小值
sum()返回某列值的和

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 table1
union [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 关键字指定正则表达式的字符匹配模式。

选项说明例子匹配值示例
^匹配文本的开始字符^b 匹配以字母 b 开头的字符串book、big、banana
$匹配文本的结束字符st$ 匹配以 st 结尾的字符串test、resist、persist
.匹配任何单个字符b.t 匹配任何 b 和 t 之间有一个字符的字符串bit、b t（包含空格），bite
*匹配零个或多个在它前面的字符f*n 匹配字符 n 前面有任意个字符 f 的字符串fn、fan、faan、fabcn
+匹配前面的字符 1 次或多次ba+ 匹配以 b 开头后面紧跟至少有一个 a 的字符串ba、bay、baax
<字符串>匹配包含指定的字符串的文本fa 匹配包含 fa 的字符串fan、afa、faad
[字符集合]匹配字符集合中的任何一个字符[xz] 匹配包含 x 或者 z 的字符串dizzy、zebra、x-ray
[^]匹配不在括号中的任何字符[^abc] 匹配任何不包含 a、b、c 的字符串desk、fox、def
字符串{n,}匹配前面的字符串（紧挨着）至少 n 次b{2} 匹配 2 个或更多的 bbb、bbb、bbbb
字符串{n,m}匹配前面的字符串至少 n 次，至少 m 次。
如果 n 为 0，此参数为可选参数。		b{2,4} 匹配含最少 2 个、最多 4 个 b 的字符串bb、bbb、bbbb

【案例】

// 用符号 “.” 来替代字符串中的任意一个字符（包括空格）
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@163.com |
| 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值：

SELECT
	AUTO_INCREMENT 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = 'academic_system' 
	AND TABLE_NAME = 'teacher';

5.11 复杂查询

5.11.1 FIELD（排序）

关键字：CASE WHEN THEN、FIELD
字段复杂查询、字段排序

SELECT
    a.user_id,
    b.area_name,
    a.real_name userName,
    a.job_title,
    count( a.user_id ) patrolRecordCnt,
    CASE
        WHEN 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 chiefLevel
FROM
    sys_user a
        INNER JOIN sys_area b ON a.area_cd = b.area_cd
        LEFT JOIN patrol_record c ON a.user_id = c.user_id
WHERE
    a.delete_flag = 0
GROUP BY
    a.user_id
ORDER BY
    FIELD(userLevel,'市级','县级','镇级','村级'),a.sort_num,a.real_name
微醺CC
关注
  • 2
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql 8.0 建表添加数据
lanziDemo blog
03-15 1195
mysql 建立表 向表中插入数据
mysql8.0 插入、更新与删除数据
S_zhangmin的博客
02-06 3917
PART1. 插入数据 可以插入的方式由插入完整的记录、插入记录的一部分、插入多条记录、插入另一个查询的结果，下面分别介绍这些内容。 1. 为表的所有字段插入数据 使用基本的INSERT语句插入数据要求指定表名称和插入到新纪录中的值。 INSERT INTO table_name (column_list) VALUES (value_list); 注意：虽然使用INSERT插入数据时可以忽略插入数据的列名称，但是值如果不包含列名称，那么VALUES关键字后面的值不仅要求完整而且顺序必须和表定义
MySQL数据处理之增删改，MySQL8新特性计算列，完整详细可收藏
2401_84046876的博客
05-13 959
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
mysql8+新语法_MySQL的一些基本语法（8）-插入更新和删除数据 | 学步园
weixin_31869579的博客
02-02 349
数据库通过插入、更新和删除等方式来改变表中的记录。插入数据是向表中插入新的记录，通过INSERT 语句来实现。更新数据是该表表中已经存在的数据，使用UPDATA语句来实现。删除数据是删除表中不再使用的数据，通过DELETE语句来实现。1.插入数据：通常可以为表的所有字段插入数据，也可以为表的某个指定的字段插入数据。INSERT语句可以同时插入多条记录。1.1为表的所有字段插入数据：插入的新纪录包含...
MySQL 8 update语句更新数据表里边的数据
qq_42108074的博客
12-05 290
这里使用为用户表bookuser在userphone列后边添加一个类型为INT的新列userage。使用为用户表bookuser在userage列后边添加一个类型为INT的新列sex。查看数据库中的表结构，可以查看插入之后的数据字段。
mysql8insert_mysql8学习笔记6--SQL基础-insert update delete语句
weixin_33614878的博客
02-04 466
• Insert语句用于插入数据到表中，其基本语法有以下三种：其中insert…values和insert…set两种语句都是将指定的数据插入到现成的表中，而insert…select语句是将另外表中数据查出来并插入到现成的表中• Partition子句代表可以将数据插入到指定的表分区中• Tbl_name代表将数据插入到的目标表• Col_name代表要插入指定数据的目标表列，如果是多列则用逗...
MySql学习笔记（详细）
最新发布
05-18
### MySQL学习笔记（详细） #### 一、数据库操作 MySQL是一种关系型数据库管理系统，通过一系列命令来进行数据库的管理和数据的处理。以下是一些常见的数据库操作命令： - **连接MySQL服务器**： - `mysql -u ...
MySQL学习笔记 MySQL学习笔记
09-28
- DML（数据操纵语言）：涉及INSERT、UPDATE和DELETE语句，用于添加、修改和删除数据。 - DDL（数据定义语言）：用于创建和管理数据库对象，如库和表，以及定义数据类型和约束。 - TCL（事务控制语言）：涉及事务...
小白终是踏上了这条不归路—-小文的mysql学习笔记（9）—-联合查询
12-14
在结束了DQL（Data Query Language）的学习之后，小白即将踏入DML（Data Manipulation Language）的领域，这将涉及如何插入、更新和删除数据，进一步深入数据库操作的核心。继续加油，与小文一起探索更多MySQL的奥秘...
MySQL核心技术学习笔记
12-16
MySQL核心技术学习笔记，包括从MySQL产品的安装到DQL语言的学习、DML语言的学习、DDL语言的学习、TCL语言的学习、视图、存储过程等，以及相关案例实现语句等等。
MySQL个人学习笔记
04-10
个人学习MySQL的一套学习笔记，记录了我所学的各种MySQL语句及其知识要点，采用思维导图的方式记录，逻辑清晰，分类准确，方便查找所需的知识点，也方便随时打开查阅，可以拿来复习、学习、或者查阅相应语句，也可以...
mysql--数据的插入（8）
pengzhisen123的博客
04-12 605
1、为表的所有列插入数据        insert into table_name(column_list) values(value_list);        比如为book表插入一条语句：create table book( id int primary key, name varchar(20) );            ...
Mysql8.0学习笔记（3）——insert into，update...set，delete
qq_37863891的博客
04-17 3101
文章目录简介insert into 简介 在本章前先了解下table的属性，还是以customers为例，点击表旁边的扳手获得如下图 对于Datatype ： INT ：只支持数字形式，例如“1234” VARCHAR（50）：该类型为字符串格式，空间为变长，最长为50 CHAR（2）：字符串格式，空间固定为2，不足补空格 DATE：日期格式 例如"2020-04-16" PK:表示为主键 ...
MySQL学习笔记8（插入、更新、删除数据）
Tan_B的博客
04-03 594
MySQL学习笔记8（插入、更新、删除数据）
MySQL插入语句
热门推荐
清风的博客
05-22 1万+
MySQL的value和values的区别 网上看了各种版本，其实value和values没有区别。官网解释如下： VALUEis a synonym forVALUESin this context. Neither implies anything about the number of values lists, nor about the number of values per list. Either may be used whether there is a single valu...
mysql数据库更新数据库语句_mysql8学习笔记⑥数据库常用操作之Delete/update语句
weixin_39776787的博客
01-20 308
mysql8学习笔记⑥数据库常用操作之Delete/update语句-- 找出课程表中没有章节信息的课程select a.course_id,a.titlefrom imc_course aleft join imc_chapter b on b.course_id = a.course_idwhere b.course_id IS NULL-- 删除课程表中没有章节信息的课程delete afr...
mysql8操作语句
qq_40787608的博客
03-19 1050
mysql8
12MySQL-8. 数据的添加(insert)*
学无止境
11-12 255
12MySQL-8. 数据的添加(insert)*
插入最快mysql8.0_mySQL 8.0 插入语句报错
weixin_36044301的博客
01-19 620
INSERTINTOVALUES(#{orderId},#{payment},#{paymentType},#{postFee},#{status},#{createTime},#{updateTime},#{paymentTime},#{consig...INSERT INTOVALUES(#{orderId},#{payment},#{paymentType},#{postFee},#{sta...
"视频整理：Mysql和JDBC基础笔记，包括SQL语句和数据操作技巧
首先讲到了SQL的基本语句分类，包括DDL（数据定义语言）、DML（数据操纵语言）、DCL（数据控制语言）和DQL（数据查询语言）四种类型。其中DDL用于定义数据库的对象，比如创建数据表，包括create、drop、alter和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值