筛选条件之group by(分组),聚合函数min max avg count,过滤条件之having,筛选条件之distinct(去重),过滤条件之排序 order by,筛选条件之 limit(限制展示条数),过滤条件之正则表达式,多表查询和子查询

Ⅰ 筛选条件之group by(分组)

数据引入


mysql> create database study001;
Query OK, 1 row affected (0.17 sec)

mysql> use study001;
Database changed

mysql> create table emp(  id int not null unique auto_increment,    name varchar(20) not null,    sex enum("male","female") not null default "male",    age int(3) unsigned not null default 28,    hire_date date not null,    post varchar(50),    post_comment varchar(100),    salary double(15,2),    office int,    depart_id int);
Query OK, 0 rows affected (0.93 sec)

mysql> desc emp;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.20 sec)

mysql> insert into emp(name, sex, age, hire_date, post, salary, office, depart_id) values
    -> ("dream", "male", 78, '20220306', "陌夜痴梦久生情", 730.33, 401, 1), # 以下是教学部
    -> ("mengmeng", "female", 25, '20220102', "teacher", 12000.50, 401, 1),
    -> ("xiaomeng", "male", 35, '20190607', "teacher", 15000.99, 401, 1),
    -> ("xiaona", "female", 29, '20180906', "teacher", 11000.80, 401, 1),
    -> ("xiaoqi", "female", 27, '20220806', "teacher", 13000.70, 401, 1),
    -> ("suimeng", "male", 33, '20230306', "teacher", 14000.62, 401, 1), # 以下是销售部
    -> ("娜娜", "female", 69, '20100307', "sale", 300.13, 402, 2),
    -> ("芳芳", "male", 45, '20140518', "sale", 400.45, 402, 2),
    -> ("小明", "male", 34, '20160103', "sale", 350.80, 402, 2),
    -> ("亚洲", "female", 42, '20170227', "sale", 320.99, 402, 2),
    -> ("华华", "female", 55, '20180319', "sale", 380.75, 402, 2),
    -> ("田七", "male", 44, '20230808', "sale", 420.33, 402, 2), # 以下是运行部
    -> ("大古", "female", 66, '20180509', "operation", 630.33, 403, 3),
    -> ("张三", "male", 51, '20191001', "operation", 410.25, 403, 3),
    -> ("李四", "male", 47, '20200512', "operation", 330.62, 403, 3),
    -> ("王五", "female", 39, '20210203', "operation", 370.98, 403, 3),
    -> ("赵六", "female", 36, '20220724', "operation", 390.15, 403, 3);
Query OK, 17 rows affected (0.13 sec)
Records: 17  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
| id | name     | sex    | age | hire_date  | post                  | post_comment | salary   | office | depart_id |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |   730.33 |    401 |         1 |
|  2 | mengmeng | female |  25 | 2022-01-02 | teacher               | NULL         | 12000.50 |    401 |         1 |
|  3 | xiaomeng | male   |  35 | 2019-06-07 | teacher               | NULL         | 15000.99 |    401 |         1 |
|  4 | xiaona   | female |  29 | 2018-09-06 | teacher               | NULL         | 11000.80 |    401 |         1 |
|  5 | xiaoqi   | female |  27 | 2022-08-06 | teacher               | NULL         | 13000.70 |    401 |         1 |
|  6 | suimeng  | male   |  33 | 2023-03-06 | teacher               | NULL         | 14000.62 |    401 |         1 |
|  7 | 娜娜     | female |  69 | 2010-03-07 | sale                  | NULL         |   300.13 |    402 |         2 |
|  8 | 芳芳     | male   |  45 | 2014-05-18 | sale                  | NULL         |   400.45 |    402 |         2 |
|  9 | 小明     | male   |  34 | 2016-01-03 | sale                  | NULL         |   350.80 |    402 |         2 |
| 10 | 亚洲     | female |  42 | 2017-02-27 | sale                  | NULL         |   320.99 |    402 |         2 |
| 11 | 华华     | female |  55 | 2018-03-19 | sale                  | NULL         |   380.75 |    402 |         2 |
| 12 | 田七     | male   |  44 | 2023-08-08 | sale                  | NULL         |   420.33 |    402 |         2 |
| 13 | 大古     | female |  66 | 2018-05-09 | operation             | NULL         |   630.33 |    403 |         3 |
| 14 | 张三     | male   |  51 | 2019-10-01 | operation             | NULL         |   410.25 |    403 |         3 |
| 15 | 李四     | male   |  47 | 2020-05-12 | operation             | NULL         |   330.62 |    403 |         3 |
| 16 | 王五     | female |  39 | 2021-02-03 | operation             | NULL         |   370.98 |    403 |         3 |
| 17 | 赵六     | female |  36 | 2022-07-24 | operation             | NULL         |   390.15 |    403 |         3 |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
17 rows in set (0.00 sec)

【一】什么时候需要分组？

参考关键字：每个、平均、最高、最低

【二】按照部门分组

【1】引入

按照指定条件对所有数据进行分组
比如我们在对图书分类的时候可能会按照出版社 / 出版日期
对员工进行分组按照年龄 / 部门

【2】语法

select * from * where * group by *;

【3】按照部门对所有员工进行分组

（1）查询数据

-- 按照部门对所有员工进行分组
select * from emp group by post;
-- ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'emp_data.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

（2）严格模式

关闭这个严格模式
模糊查询所有严格模式

show variables like "%mode";

mysql> show variables like "%mode";
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name            | Value                                                                                                                                     |
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| block_encryption_mode    | aes-128-ecb                                                                                                                               |
| gtid_mode                | OFF                                                                                                                                       |
| innodb_autoinc_lock_mode | 1                                                                                                                                         |
| innodb_strict_mode       | ON                                                                                                                                        |
| offline_mode             | OFF                                                                                                                                       |
| pseudo_slave_mode        | OFF                                                                                                                                       |
| rbr_exec_mode            | STRICT                                                                                                                                    |
| slave_exec_mode          | STRICT                                                                                                                                    |
| sql_mode                 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set, 1 warning (0.20 sec)

（3）替换严格模式

解决办法 ---> 修改严格模式
删除了 ONLY_FULL_GROUP_BY

-- 解决办法 ---> 修改严格模式
set global sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
-- 不要忘了重新进客户端，否则不生效

mysql> show variables like "%mode";
+--------------------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name            | Value                                                                                                                  |
+--------------------------+------------------------------------------------------------------------------------------------------------------------+
| block_encryption_mode    | aes-128-ecb                                                                                                            |
| gtid_mode                | OFF                                                                                                                    |
| innodb_autoinc_lock_mode | 1                                                                                                                      |
| innodb_strict_mode       | ON                                                                                                                     |
| offline_mode             | OFF                                                                                                                    |
| pseudo_slave_mode        | OFF                                                                                                                    |
| rbr_exec_mode            | STRICT                                                                                                                 |
| slave_exec_mode          | STRICT                                                                                                                 |
| sql_mode                 | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------+------------------------------------------------------------------------------------------------------------------------+
9 rows in set, 1 warning (0.00 sec)

（4）再查询数据

select * from emp group by post;

按照部门进行分组后显示出来的信息是每一个部门的第一个人的信息
最小的操作单位应该是组，而不是组内的单个数据
这条命令在没有设置严格模式的时候是可以执行的，返回的数据是每组的第一条数据
但是分组不应该以单条数据为参考，而是要以组为操作单位
如果设置了严格模式，上述命令会直接报错
也就是上面的那个错误

（5）其他办法

设置严格模式

set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

设置严格模式后，按照什么分组就只能拿到什么
其他字段不能直接获取，获取其他数据需要借助其他方法
- 查询数据

select post from emp group by post;

mysql> select post from emp group by post;
+-----------------------+
| post                  |
+-----------------------+
| operation             |
| sale                  |
| teacher               |
| 陌夜痴梦久生情        |
+-----------------------+
4 rows in set (0.00 sec)

【三】聚合函数min / max /avg / count

【1】获取每个部门的最高薪资(max)

（1）聚合函数 - max

聚合函数：max - 取最大值
可以利用as关键字给字段起别名，或者默认不写

语法

-- 查询当前每个分组下的最高信息max
select * from emp group by post;
-- 为什么显示的name是本分组下的第一个人的名字？
-- 因为代码比较傻
select post,max(salary) from emp group by post;

（2）数据代码展示

示例

mysql> select post,max(salary) from emp group by post;
+-----------------------+-------------+
| post                  | max(salary) |
+-----------------------+-------------+
| operation             |      630.33 |
| sale                  |      420.33 |
| teacher               |    15000.99 |
| 陌夜痴梦久生情        |      730.33 |
+-----------------------+-------------+
4 rows in set (0.17 sec)

【2】查询数据指定别名,定制化字段显示的内容

语法

-- 有的时候我们想自定制显示的信息 
-- 定制化字段显示的内容
select post,max(salary) as "最高薪资" from emp group by post;

示例

###### max(salary) as "最高薪资"  #######
mysql> select post,max(salary) as "最高薪资" from emp group by post;
+-----------------------+--------------+
| post                  | 最高薪资     |
+-----------------------+--------------+
| operation             |       630.33 |
| sale                  |       420.33 |
| teacher               |     15000.99 |
| 陌夜痴梦久生情        |       730.33 |
+-----------------------+--------------+
4 rows in set (0.10 sec)

【3】获取每个部门的最低薪资(min)

（1）聚合函数 - min

聚合函数：min- 取最小值

语法

-- 查询每个部门下的最低薪资
select post,min(salary) as "最低薪资" from emp group by post;

（2）数据代码展示

示例

mysql> select post,min(salary) as "最低薪资" from emp group by post;
+-----------------------+--------------+
| post                  | 最低薪资     |
+-----------------------+--------------+
| operation             |       330.62 |
| sale                  |       300.13 |
| teacher               |     11000.80 |
| 陌夜痴梦久生情        |       730.33 |
+-----------------------+--------------+
4 rows in set (0.00 sec)

【4】获取每个部门的平均薪资(av

（1）聚合函数 - avg

聚合函数：avg- 取平均值

语法

-- 查询每个部门下的平均薪资
select post,avg(salary) as "平均薪资" from emp group by post;

（2）数据代码展示

示例

mysql> select post,avg(salary) as "平均薪资" from emp group by post;
+-----------------------+--------------+
| post                  | 平均薪资     |
+-----------------------+--------------+
| operation             |   426.466000 |
| sale                  |   362.241667 |
| teacher               | 13000.722000 |
| 陌夜痴梦久生情        |   730.330000 |
+-----------------------+--------------+
4 rows in set (0.11 sec)

【5】获取每个部门的薪资总和(sum)

（1）聚合函数 - sum

聚合函数：sum- 取总和

语法

-- 获取每个部门的薪资总和
select post,min(salary) as "最低薪资" from emp group by post;

（2）数据代码展示

示例

mysql>  select post as "部门",sum(salary) as "总薪资"  from emp group by post;
+-----------------------+-----------+
| 部门                  | 总薪资    |
+-----------------------+-----------+
| operation             |   2132.33 |
| sale                  |   2173.45 |
| teacher               |  65003.61 |
| 陌夜痴梦久生情        |    730.33 |
+-----------------------+-----------+
4 rows in set (0.10 sec)

【6】获取每个部门的人数(count)

（1）聚合函数 - count

聚合函数：count- 计数

语法

-- 获取每个部门的人数
select post,min(salary) as "最低薪资" from emp group by post;

select post as "部门" ,count(id) as "部门的人数" from emp group by post;

（2）数据代码展示

示例

mysql>  select post as "部门" ,count(salary) as "部门总人数" from emp group by post;
+-----------------------+-----------------+
| 部门                  | 部门总人数      |
+-----------------------+-----------------+
| operation             |               5 |
| sale                  |               6 |
| teacher               |               5 |
| 陌夜痴梦久生情        |               1 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)

mysql> select post as "部门" ,count(id) as "部门的人数" from emp group by post;
+-----------------------+-----------------+
| 部门                  | 部门的人数      |
+-----------------------+-----------------+
| operation             |               5 |
| sale                  |               6 |
| teacher               |               5 |
| 陌夜痴梦久生情        |               1 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)

【7】count 函数不能对 null 进行计数

语法

-- count 函数不能对 null 进行计数 ，计数为 0
-- post_comment里全是null
select post,count(post_comment) as "总人数" from emp group by post;

示例

mysql> select post,count(post_comment) as "总人数" from emp group by post;
+-----------------------+-----------+
| post                  | 总人数    |
+-----------------------+-----------+
| operation             |         0 |
| sale                  |         0 |
| teacher               |         0 |
| 陌夜痴梦久生情        |         0 |
+-----------------------+-----------+
4 rows in set (0.00 sec)

【8】查询分组之后的部门名称和每个部门下所有的员工姓名(group_concat)

（1）聚合函数 - group_concat

聚合函数：group_concat- 获得分组之后的具体的值
不单单支持获取分组之后的其他字段值，还支持拼接操作

语法

-- 查询分组之后的部门名称以及每个部门下的所有成员的名字
-- group_concat : 获取到分组之后具体的字段的值
select post,group_concat(name) from emp group by post;

select post,group_concat(name) as "人员名单" from emp group by post;

-- group_concat ：不仅仅只是能获取当前分组下的所有数据还可以支持字符串拼接操作
select post,group_concat(name,'_saler') as "人员名单" from emp group by post;

-- 支持查询个人的很多数据
select post,group_concat(name,'_saler','|',salary) as "人员名单" from emp group by post;

（2）查询数据

示例

（3）查询数据(不分组之前用concat)

语法

-- group_concat : 必须分组之后才能使用
select group_concat("NAME:",name)as "新名字",age  from emp ;


-- concat ：不分组的情况下对每一个字段进行定制
select concat("NAME:",name)as "新名字",age  from emp ;


select concat("NAME:",name)as "新名字",*  from emp ;
-- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*  from emp' at line 1
-- 只能按照单个字段进行取值，不允许使用 * 代替后面的所有字段


select concat("NAME:",name)as "新名字",concat("SALARY",salary)  from emp ;

示例

（4）as语法

as 语法不单单可以给字段起别名，还可以给表取别名
- 只能临时起别名
- 查数据

语法

select emp.id,emp.name from emp;

-- 查数据起别名
select emp.id,emp.name from emp as ti;

mysql> select emp.id,emp.name from emp as ti;
ERROR 1054 (42S22): Unknown column 'emp.id' in 'field list'


select ti.id,ti.name from emp as ti;

示例

mysql> select emp.id,emp.name from emp;
+----+----------+
| id | name     |
+----+----------+
|  1 | dream    |
|  2 | mengmeng |
|  3 | xiaomeng |
|  4 | xiaona   |
|  5 | xiaoqi   |
|  6 | suimeng  |
|  7 | 娜娜     |
|  8 | 芳芳     |
|  9 | 小明     |
| 10 | 亚洲     |
| 11 | 华华     |
| 12 | 田七     |
| 13 | 大古     |
| 14 | 张三     |
| 15 | 李四     |
| 16 | 王五     |
| 17 | 赵六     |
+----+----------+
17 rows in set (0.00 sec)


mysql> select ti.id,ti.name from emp as ti;
+----+----------+
| id | name     |
+----+----------+
|  1 | dream    |
|  2 | mengmeng |
|  3 | xiaomeng |
|  4 | xiaona   |
|  5 | xiaoqi   |
|  6 | suimeng  |
|  7 | 娜娜     |
|  8 | 芳芳     |
|  9 | 小明     |
| 10 | 亚洲     |
| 11 | 华华     |
| 12 | 田七     |
| 13 | 大古     |
| 14 | 张三     |
| 15 | 李四     |
| 16 | 王五     |
| 17 | 赵六     |
+----+----------+
17 rows in set (0.00 sec)

【9】查询每个人的年薪(13)

直接对查询到的数据进行运算

查询数据

语法

-- 如果想要查询每一个人一年 13薪的薪资
select name,salary from emp;

select name,salary*13 as "年薪" from emp;

示例

mysql> select name,salary from emp;
+----------+----------+
| name     | salary   |
+----------+----------+
| dream    |   730.33 |
| mengmeng | 12000.50 |
| xiaomeng | 15000.99 |
| xiaona   | 11000.80 |
| xiaoqi   | 13000.70 |
| suimeng  | 14000.62 |
| 娜娜     |   300.13 |
| 芳芳     |   400.45 |
| 小明     |   350.80 |
| 亚洲     |   320.99 |
| 华华     |   380.75 |
| 田七     |   420.33 |
| 大古     |   630.33 |
| 张三     |   410.25 |
| 李四     |   330.62 |
| 王五     |   370.98 |
| 赵六     |   390.15 |
+----------+----------+
17 rows in set (0.00 sec)

mysql> select name,salary*13 as "年薪" from emp;
+----------+-----------+
| name     | 年薪      |
+----------+-----------+
| dream    |   9494.29 |
| mengmeng | 156006.50 |
| xiaomeng | 195012.87 |
| xiaona   | 143010.40 |
| xiaoqi   | 169009.10 |
| suimeng  | 182008.06 |
| 娜娜     |   3901.69 |
| 芳芳     |   5205.85 |
| 小明     |   4560.40 |
| 亚洲     |   4172.87 |
| 华华     |   4949.75 |
| 田七     |   5464.29 |
| 大古     |   8194.29 |
| 张三     |   5333.25 |
| 李四     |   4298.06 |
| 王五     |   4822.74 |
| 赵六     |   5071.95 |
+----------+-----------+
17 rows in set (0.11 sec)

【四】筛选条件之 group by(分组) 注意事项

【1】引入

（1）关键字 where 和 group by 同时出现

关键字 where 和 group by 同时出现的时候，group by 必须在 where 后面
- where 先对整体数据进行过滤
- group by 再对数据进行分组

（2）where 筛选条件不能使用聚合函数

where 筛选条件不能使用聚合函数
- 不分组，默认整张表就是一组
聚合函数只能在分组之后使用
- 查询数据

语法

mysql> select id,name,age from emp where max(salary) > 3000;
ERROR 1111 (HY000): Invalid use of group function

select max(salary) from emp;

示例

mysql> select max(salary) from emp;
+-------------+
| max(salary) |
+-------------+
|    15000.99 |
+-------------+
1 row in set (0.03 sec)

【2】统计各部门年龄在 30 岁以上的员工的平均薪资

语法

-- 统计各部门年龄在 30 岁以上的员工的平均薪资
-- 统计各部门 分组
-- 年龄在 30 岁以下 过滤
-- 平均薪资 聚合函数 avg

-- 先过滤30岁以后的人
select * from emp where age > 30;
-- 在对 30 岁以后的人进行分组
select * from emp where age > 30 group by post;
-- 在对 30 岁以后的人进行分组 计数 
select count(age) from emp where age > 30 group by post;

-- 在对 30 岁以后的人进行分组 计算平均薪资 
select post as "部门",avg(salary) as "均薪" from emp where age > 30 group by post;

示例

Ⅱ 过滤条件之having

【一】引入

having与where的功能是一模一样的都是对数据进行筛选
- where用在分组之前的筛选
- havng用在分组之后的筛选
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的

【二】统计各部门年龄在 30 岁以上的员工的工资，并且保留平均薪资大于1w的部门

-- 统计各部门年龄在 30 岁以上的员工的工资
-- 并且保留平均薪资大于1w的部门

-- 统计各部门 分组
-- 在 30 岁以上的员工 where 过滤
-- 平均薪资大于1w avg(salary)>10000

语法

-- 先筛选出30岁以上的员工数据 然后再对数据进行分组
select post,avg(salary) from emp where age>30 group by post;

-- 在过滤出平均薪资大于10000的数据
-- having : 分组之后再进行筛选
select post,avg(salary) from emp 
	where age >30 
	group by post
	having avg(salary) > 10000;

示例

mysql> select post,avg(salary) from emp where age>30 group by post;
+-----------------------+--------------+
| post                  | avg(salary)  |
+-----------------------+--------------+
| operation             |   426.466000 |
| sale                  |   362.241667 |
| teacher               | 14500.805000 |
| 陌夜痴梦久生情        |   730.330000 |
+-----------------------+--------------+
4 rows in set (0.00 sec)

-- having : 分组之后再进行筛选
mysql> select post,avg(salary) from emp
    -> where age >30
    -> group by post
    -> having avg(salary) > 10000;
+---------+--------------+
| post    | avg(salary)  |
+---------+--------------+
| teacher | 14500.805000 |
+---------+--------------+
1 row in set (0.02 sec)

Ⅲ 筛选条件之distinct(去重)

必须是完全一样的数据才可以去重
一定要注意主键的问题
在主键存在的情况下是一定不可能去重的

等我们学到Django ORM之后数据会被封装成对象
那个时候主键很容易被我们忽略从而导致去重没有效果!!!

语法

-- 将该字段下的重复的数据进行去重  --- 集合
-- 我们的表中的主键ID 自增 唯一 不为空
select distinct id,age from emp ;

-- 去除一样的数据 ---> 去除除主键外的数据
select distinct sex from emp ;

示例

+----+-----+
| id | age |
+----+-----+
|  1 |  78 |
|  2 |  25 |
|  3 |  35 |
|  4 |  29 |
|  5 |  27 |
|  6 |  33 |
|  7 |  69 |
|  8 |  45 |
|  9 |  34 |
| 10 |  42 |
| 11 |  55 |
| 12 |  44 |
| 13 |  66 |
| 14 |  51 |
| 15 |  47 |
| 16 |  39 |
| 17 |  36 |
+----+-----+
17 rows in set (0.02 sec)

mysql> select distinct sex from emp ;
+--------+
| sex    |
+--------+
| male   |
| female |
+--------+
2 rows in set (0.02 sec)

Ⅳ 过滤条件之排序 order by

order by : 默认是升序
asc 默认可以省略不写 ---> 修改降序
desc : 降序

-- 排序 就是按照指定的规则进行排序
-- 正序排和倒序排
-- 正序 从小到大排 默认的排序规则 asc
-- 倒序 从大到小排 增加额外的参数 desc

语法

-- 将表中的薪资按照从小到大排
select * from emp order by salary;

select * from emp order by salary asc;

-- 将表中的薪资从大到小排
select * from emp order by salary desc;

-- 按照薪资(升序)和年龄(降序)排序
-- 先按照薪资升序排，如果碰到salary相同，再按照age降序
select  * from emp order by salary asc,age desc;

select  * from emp order by sex asc,hire_date desc;

-- 统计各部门年龄在 30 岁以上的员工的工资
-- 并且保留平均薪资大于100的部门
-- 对平均工资进行排序
select post,avg(salary) from emp 
	where age > 30 
	group by post 
	having avg(salary) > 100
	order by avg(salary);

示例

-- 将表中的薪资按照从小到大排
select * from emp order by salary;

select * from emp order by salary asc;

-- 按照薪资(升序)和年龄(降序)排序
-- 先按照薪资升序排，如果碰到salary相同，再按照age降序
select  * from emp order by salary asc,age desc;

select  * from emp order by sex asc,hire_date desc;

-- 统计各部门年龄在 30 岁以上的员工的工资
-- 并且保留平均薪资大于100的部门
-- 对平均工资进行排序
select post,avg(salary) from emp 
	where age > 30 
	group by post 
	having avg(salary) > 100
	order by avg(salary);

mysql> select post,avg(salary) from emp
    -> where age > 30
    -> group by post
    -> having avg(salary) > 100
    -> order by avg(salary);
+-----------------------+--------------+
| post                  | avg(salary)  |
+-----------------------+--------------+
| sale                  |   362.241667 |
| operation             |   426.466000 |
| 陌夜痴梦久生情        |   730.330000 |
| teacher               | 14500.805000 |
+-----------------------+--------------+
4 rows in set (0.01 sec)

Ⅴ 筛选条件之 limit(限制展示条数)

针对数据太多的情况，我们大都是做分页处理
limit x,y : 第一个参数是起始位置，第二个是条数

【一】限制获取到的数据的总量：单数字限制

限制只展示十条数据

语法

-- 限制获取到的数据的总量
select * from emp limit 10;

示例

【二】查询数据：多限制

分页效果

-- 增加额外的参数进行限制，实现分页的效果
-- 索引位置是从0开始的
-- limit 索引位置,数据量

-- 从索引为0的位置开始获取数据，获取到了5条数据
select * from emp limit 0,5;

select * from emp limit 5,5;
-- 从索引位置 6 的位置开始获取数据，获取10条数据；
select * from emp limit 6,10;

【三】案例：查询工资最高的人的详细信息

语法

-- 查询工资最高的人的详细信息

select * from emp order by salary desc;

-- 只要最高的哪个人的信息
select * from emp order by salary desc limit 1;

示例

Ⅵ 过滤条件之正则表达式

【一】语法

-- python中的正则表达式非常丰富 可以写很多复杂的表达式
-- 但是在SQL中的正则表达式 支持的表达式非常少!

属性名 REGEXP '匹配方式'

其中，“属性名”表示需要查询的字段名称；
“匹配方式”表示以哪种方式来匹配查询。

【二】匹配方式

“匹配方式”中有很多的模式匹配字符，它们分别表示不同的意思。
下表列出了 REGEXP 操作符中常用的匹配方式。

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	‘^b’ 匹配以字母 b 开头的字符串	book、big、banana、bike
$	匹配文本的结束字符	‘st$’ 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	‘b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配前面的字符 0 次或多次	‘f*n’ 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
?	匹配前面的字符 0 次或1次	‘sa?’ 匹配0个或1个a字符	sa、s
字符串	匹配包含指定字符的文本	‘fa’ 匹配包含‘fa’的文本	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	‘[xz]’ 匹配 x 或者 z	dizzy、zebra、x-ray、extra
[^]	匹配不在括号中的任何字符	‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串	匹配前面的字符串至少 n 次	‘b{2}’ 匹配 2 个或更多的 b	bbb、bbbb、bbbbbbb
字符串	匹配前面的字符串至少 n 次，至多 m 次	‘b{2,4}’ 匹配最少 2 个，最多 4 个 b	bbb、bbbb

【三】示例引入

【1】准备数据

创建表

DROP TABLE IF EXISTS `person`;

CREATE TABLE `person`  (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(40) NULL DEFAULT NULL,
  `heigh` int(40) NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

插入数据

INSERT INTO `person` VALUES ('Thomas ', 25, 168, '男');
INSERT INTO `person` VALUES ('Tom ', 20, 172, '男');
INSERT INTO `person` VALUES ('Dany', 29, 175, '男');
INSERT INTO `person` VALUES ('Jane', 27, 171, '男');
INSERT INTO `person` VALUES ('Susan', 24, 173, '女');
INSERT INTO `person` VALUES ('Green', 25, 168, '女');
INSERT INTO `person` VALUES ('Henry', 21, 160, '女');
INSERT INTO `person` VALUES ('Lily', 18, 190, '男');
INSERT INTO `person` VALUES ('LiMing', 19, 187, '男');

示例

mysql> DROP TABLE IF EXISTS `person`;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql>
mysql> CREATE TABLE `person`  (
    ->   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    ->   `age` int(40) NULL DEFAULT NULL,
    ->   `heigh` int(40) NULL DEFAULT NULL,
    ->   `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
    -> ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
Query OK, 0 rows affected (1.97 sec)

mysql>
mysql> INSERT INTO `person` VALUES ('Thomas ', 25, 168, '男');
Query OK, 1 row affected (0.13 sec)

mysql> INSERT INTO `person` VALUES ('Tom ', 20, 172, '男');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO `person` VALUES ('Dany', 29, 175, '男');
Query OK, 1 row affected (0.23 sec)

mysql> INSERT INTO `person` VALUES ('Jane', 27, 171, '男');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO `person` VALUES ('Susan', 24, 173, '女');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO `person` VALUES ('Green', 25, 168, '女');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO `person` VALUES ('Henry', 21, 160, '女');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO `person` VALUES ('Lily', 18, 190, '男');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `person` VALUES ('LiMing', 19, 187, '男');
Query OK, 1 row affected (0.05 sec)

mysql> desc `person`;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
| age   | int(40)      | YES  |     | NULL    |       |
| heigh | int(40)      | YES  |     | NULL    |       |
| sex   | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.05 sec)

mysql> select * from `person`;
+---------+------+-------+------+
| name    | age  | heigh | sex  |
+---------+------+-------+------+
| Thomas  |   25 |   168 | 男   |
| Tom     |   20 |   172 | 男   |
| Dany    |   29 |   175 | 男   |
| Jane    |   27 |   171 | 男   |
| Susan   |   24 |   173 | 女   |
| Green   |   25 |   168 | 女   |
| Henry   |   21 |   160 | 女   |
| Lily    |   18 |   190 | 男   |
| LiMing  |   19 |   187 | 男   |
+---------+------+-------+------+
9 rows in set (0.00 sec)

-- . : 一个字符
-- * : 零次或无数次
-- ? : 零次或一次
-- + : 一次货无数次
-- ^ : 以 ... 开头
-- $ : 以 ... 结尾

【2】查询 name 字段以j开头的记录

select * from person where name REGEXP '^j';

mysql> select * from person where name REGEXP '^j';
+------+------+-------+------+
| name | age  | heigh | sex  |
+------+------+-------+------+
| Jane |   27 |   171 | 男   |
+------+------+-------+------+
1 row in set (0.04 sec)

【3】查询 name 字段以“y”结尾的记录

select * from person where name REGEXP 'y$';

mysql> select * from person where name REGEXP 'y$';
+-------+------+-------+------+
| name  | age  | heigh | sex  |
+-------+------+-------+------+
| Dany  |   29 |   175 | 男   |
| Henry |   21 |   160 | 女   |
| Lily  |   18 |   190 | 男   |
+-------+------+-------+------+
3 rows in set (0.00 sec)

【4】查询 name 字段值包含“a”和“y”，且两个字母之间只有一个字母的记录

select * from person where name REGEXP 'a.y';

mysql> select * from person where name REGEXP 'a.y';
+------+------+-------+------+
| name | age  | heigh | sex  |
+------+------+-------+------+
| Dany |   29 |   175 | 男   |
+------+------+-------+------+
1 row in set (0.00 sec)

【5】查询 name 字段值包含字母“T”，且“T”后面出现字母“h”的记录

select * from person where name REGEXP 'Th*';

mysql> select * from person where name REGEXP 'Th*';
+---------+------+-------+------+
| name    | age  | heigh | sex  |
+---------+------+-------+------+
| Thomas  |   25 |   168 | 男   |
| Tom     |   20 |   172 | 男   |
+---------+------+-------+------+
2 rows in set (0.00 sec)

【6】查询 name 字段值包含字母“T”，且“T”后面至少出现“h”一次的记录

select * from person where name REGEXP 'Th+';

mysql> select * from person where name REGEXP 'Th+';
+---------+------+-------+------+
| name    | age  | heigh | sex  |
+---------+------+-------+------+
| Thomas  |   25 |   168 | 男   |
+---------+------+-------+------+
1 row in set (0.00 sec)

【7】查询 name 字段值包含字母“S”，且“S”后面出现“a”一次或零次的记录

select * from person where name REGEXP  'sa?';

mysql> select * from person where name REGEXP  'sa?';
+---------+------+-------+------+
| name    | age  | heigh | sex  |
+---------+------+-------+------+
| Thomas  |   25 |   168 | 男   |
| Susan   |   24 |   173 | 女   |
+---------+------+-------+------+
2 rows in set (0.00 sec)

【8】查询 name 字段值包含字符串“an”的记录

select * from person where name REGEXP 'an';

mysql> select * from person where name REGEXP 'an';
+-------+------+-------+------+
| name  | age  | heigh | sex  |
+-------+------+-------+------+
| Dany  |   29 |   175 | 男   |
| Jane  |   27 |   171 | 男   |
| Susan |   24 |   173 | 女   |
+-------+------+-------+------+
3 rows in set (0.00 sec)

【9】查询 name 字段值包含字符串“an”或“en”的记录

- 指定多个字符串时，需要用|隔开。只要匹配这些字符串中的任意一个即可。

select * from person where name REGEXP 'an|en';

mysql> select * from person where name REGEXP 'an|en';
+-------+------+-------+------+
| name  | age  | heigh | sex  |
+-------+------+-------+------+
| Dany  |   29 |   175 | 男   |
| Jane  |   27 |   171 | 男   |
| Susan |   24 |   173 | 女   |
| Green |   25 |   168 | 女   |
| Henry |   21 |   160 | 女   |
+-------+------+-------+------+
5 rows in set (0.00 sec)

【10】查询 name 字段值包含字母“i”或“o”的记录

select * from person where name REGEXP '[io]';

mysql> select * from person where name REGEXP '[io]';
+---------+------+-------+------+
| name    | age  | heigh | sex  |
+---------+------+-------+------+
| Thomas  |   25 |   168 | 男   |
| Tom     |   20 |   172 | 男   |
| Lily    |   18 |   190 | 男   |
| LiMing  |   19 |   187 | 男   |
+---------+------+-------+------+
4 rows in set (0.00 sec)

【11】方括号[ ]还可以指定集合的区间

例如，“[a-z]”表示从 a~z 的所有字母；“[0-9]”表示从 0~9 的所有数字；“[a-z0-9]”表示包含所有的小写字母和数字；“[a-zA-Z]”表示匹配所有字符。MySQL中的正则表达式匹配不区分大小写。为区分大小写，可使用BINARY关键字。

select * from person where name REGEXP BINARY   '^[a-z]';

mysql> select * from person where name REGEXP BINARY   '^[a-z]';
Empty set (0.00 sec)

【12】查询 name 字段值包含字母 a~t 以外的字符的记录

select * from person where name REGEXP   '[^a-t]';

mysql> select * from person where name REGEXP   '[^a-t]';
+---------+------+-------+------+
| name    | age  | heigh | sex  |
+---------+------+-------+------+
| Thomas  |   25 |   168 | 男   |
| Tom     |   20 |   172 | 男   |
| Dany    |   29 |   175 | 男   |
| Susan   |   24 |   173 | 女   |
| Henry   |   21 |   160 | 女   |
| Lily    |   18 |   190 | 男   |
+---------+------+-------+------+
6 rows in set (0.00 sec)

【13】查询 name 字段值出现字母‘e’ 至少 2 次的记录

select * from person where name REGEXP  'e{2,}';

mysql> select * from person where name REGEXP  'e{2,}';
+-------+------+-------+------+
| name  | age  | heigh | sex  |
+-------+------+-------+------+
| Green |   25 |   168 | 女   |
+-------+------+-------+------+
1 row in set (0.00 sec)

【14】查询 name 字段值出现字符串“i” 最少 1 次，最多 3 次的记录

select * from person where name REGEXP  'i{1,3}';

mysql> select * from person where name REGEXP  'i{1,3}';
+--------+------+-------+------+
| name   | age  | heigh | sex  |
+--------+------+-------+------+
| Lily   |   18 |   190 | 男   |
| LiMing |   19 |   187 | 男   |
+--------+------+-------+------+
2 rows in set (0.00 sec)

Ⅶ 多表查询和子查询

【一】子查询

就相当于是我们日常生活中解决问题的方式(一步步解决)
将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件
eg:以昨天的员工表和部门表为例查询silence所在的部门名称
子查询的步骤
- 先查silence所在的部门编号
- 根据部门编号去部门表中查找部门名称

【二】联表查询

先将多张表拼接到一起形成一张大表然后基于单表查询获取数据
eg:以昨天的员工表和部门表为例查询silence所在的部门名称
连表操作
- 先将员工表和部门表按照某个字段拼接到一起
- 基于单表查询

【三】解释

-- 多表查 又称为连表查询 多张表放到一起去查询数据
-- 员工表和部门表 ： 把员工表和部门表合并成一张表 ---> 在合并后的表中查询数据
id name dep_id dep_name desc 
1  silence   1      后勤  后勤


-- 子查询
-- 先查一条数据，将这条数据的结果作为下一条查询语句的起始
-- 先在员工表中查询数据 查询 silence 的dep_id
员工表 
# id   name   dep_id
  1  silence  1

-- 再拿着上面的dep_id 在部门表中查询到 name
# 部门表
id  name  desc 
1   后勤  后勤

【四】准备数据

【1】创建表

create table dep(
	id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(20)
);


CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    sex ENUM("male","female") NOT NULL DEFAULT "male",
    age INT,
    dep_id INT
);

【2】插入数据

insert into dep values
("200","技术部"),
("201","人力资源"),
("202","销售部"),
("203","运营部"),
("204","售后部");

insert into emp(name,sex,age,dep_id) values
("silence","male",18,200),
("mark","female",18,201),
("happy","male",38,202),
("smile","male",18,203),
("own","male",28,204),
("thdream","male",18,205);

【3】查看表和数据

mysql> create table dep(
    -> id int PRIMARY KEY AUTO_INCREMENT,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.22 sec)

mysql>
mysql>
mysql> CREATE TABLE emp (
    ->     id INT PRIMARY KEY AUTO_INCREMENT,
    ->     name VARCHAR(20),
    ->     sex ENUM("male","female") NOT NULL DEFAULT "male",
    ->     age INT,
    ->     dep_id INT
    -> );
Query OK, 0 rows affected (0.50 sec)

mysql> desc dep;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)           | YES  |     | NULL    |                |
| sex    | enum('male','female') | NO   |     | male    |                |
| age    | int(11)               | YES  |     | NULL    |                |
| dep_id | int(11)               | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> insert into dep values
    -> ("200","技术部"),
    -> ("201","人力资源"),
    -> ("202","销售部"),
    -> ("203","运营部"),
    -> ("204","售后部");
Query OK, 5 rows affected (0.11 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into emp(name,sex,age,dep_id) values
    -> ("silence","male",18,200),
    -> ("mark","female",18,201),
    -> ("happy","male",38,202),
    -> ("smile","male",18,203),
    -> ("own","male",28,204),
    -> ("thdream","male",18,205);
Query OK, 6 rows affected (0.13 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+-----+--------------+
| id  | name         |
+-----+--------------+
| 200 | 技术部       |
| 201 | 人力资源     |
| 202 | 销售部       |
| 203 | 运营部       |
| 204 | 售后部       |
+-----+--------------+
5 rows in set (0.00 sec)

mysql> select * from emp;
+----+---------+--------+------+--------+
| id | name    | sex    | age  | dep_id |
+----+---------+--------+------+--------+
|  1 | silence | male   |   18 |    200 |
|  2 | mark    | female |   18 |    201 |
|  3 | happy   | male   |   38 |    202 |
|  4 | smile   | male   |   18 |    203 |
|  5 | own     | male   |   28 |    204 |
|  6 | thdream | male   |   18 |    205 |
+----+---------+--------+------+--------+
6 rows in set (0.00 sec)

【五】子查询

-- 获取员工silence所在的部门名称

-- 先在员工表中获取到当前员工对应的部门ID
select dep_id from emp where name="silence";

-- 再根据部门ID去部门表中查询部门名称
select name from dep where id =200;

-- 结合
select name from dep where id =(select dep_id from emp where name="silence");

-- 查询到当前部门的ID
select id from dep where name ="技术部";

-- 再根据部门ID去查当前这个人的详细信息
select * from emp where dep_id in (select id from dep where name ="技术部");

子查询就相当于我们平时解决问题的思路
将一个查询语句的结果当做另外一个查询语句的条件去用

【1】案例一：获取员工silence所在的部门名称

（1）先获取silence的部门编号

-- 获取员工silence所在的部门名称

-- 先在员工表中获取到当前员工对应的部门ID
select dep_id from emp where name="silence";

mysql> select dep_id from emp where name="silence";
+--------+
| dep_id |
+--------+
|    200 |
+--------+
1 row in set (0.00 sec)

（2）将结果加括号作为查询条件

select name from dep where id =(select dep_id from emp where name="silence");

mysql> select name from dep where id =(select dep_id from emp where name="silence");
+-----------+
| name      |
+-----------+
| 技术部    |
+-----------+
1 row in set (0.00 sec)

【2】案例二：

查询部门是技术或者人力资源的员工信息
先获取部门的ID号，再去员工表里面删选出对应的员工
查询语句

-- 查询到当前部门的ID
select id from dep where name ="技术部";

mysql> select id from dep where name ="技术部";
+-----+
| id  |
+-----+
| 200 |
+-----+
1 row in set (0.00 sec)

-- 再根据部门ID去查当前这个人的详细信息
select * from emp where dep_id in (select id from dep where name ="技术部");

mysql> select * from emp where dep_id in (select id from dep where name ="技术部");
+----+---------+------+------+--------+
| id | name    | sex  | age  | dep_id |
+----+---------+------+------+--------+
|  1 | silence | male |   18 |    200 |
+----+---------+------+------+--------+
1 row in set (0.00 sec)

【六】联表查询

【1】笛卡尔积补充

（1）什么是笛卡尔积

笛卡尔积是一个数学概念，它描述的是两个集合之间所有可能的元素组合的数量。
具体来说，如果集合A有n个元素，集合B有m个元素，则它们的笛卡尔积的大小为nm。

（2）简单来说

笛卡尔积是通过组合两个集合的所有元素来创建一个新的集合的过程。
在最简单的例子中，如果有一个集合包含三个元素a、b和c，另一个集合包含两个元素x和y，那么这两个集合的笛卡尔积将包含六个元素：ax、ay、bx、by、cx和cy。

（3）MySQL中的笛卡尔积

在SQL中，当我们使用JOIN操作将两个或更多的表连接在一起时，结果集中的行数是所有连接表的行数的乘积。这就是所谓的笛卡尔积。
例如，假设我们有两个表A和B，其中A有5行，B有3行。
如果我们使用INNER JOIN将这两个表连接起来，那么结果集中将会有5 x 3 = 15行。
这是因为对于每一行A，我们可以从B中选择任意一行进行匹配。
因此，总共有5种不同的方式来组合A表中的每一行和B表中的每一行，这导致了最终结果集的大小为5 x 3 = 15。
这个过程就是笛卡尔积，它是数学中的一种运算，用于计算两个集合的所有可能的元素组合的数量。
在这个情况下，每个元素都是一个表格中的行。
所以，当我们在MySQL中使用JOIN操作时，结果集的大小实际上是所有连接表的行数的乘积，这就是为什么我们称其为笛卡尔积的原因。

（4）总结

笛卡尔积是一种非常有用的工具，它可以帮助我们理解数据之间的复杂关系，并为我们提供更深入的数据洞察。

【2】拼表

结果叫笛卡尔积

查询数据

-- 根据笛卡尔积进行品表 一共有 30 行数据
select * from dep,emp;

mysql> select * from dep,emp;
+-----+--------------+----+---------+--------+------+--------+
| id  | name         | id | name    | sex    | age  | dep_id |
+-----+--------------+----+---------+--------+------+--------+
| 200 | 技术部       |  1 | silence | male   |   18 |    200 |
| 201 | 人力资源     |  1 | silence | male   |   18 |    200 |
| 202 | 销售部       |  1 | silence | male   |   18 |    200 |
| 203 | 运营部       |  1 | silence | male   |   18 |    200 |
| 204 | 售后部       |  1 | silence | male   |   18 |    200 |
| 200 | 技术部       |  2 | mark    | female |   18 |    201 |
| 201 | 人力资源     |  2 | mark    | female |   18 |    201 |
| 202 | 销售部       |  2 | mark    | female |   18 |    201 |
| 203 | 运营部       |  2 | mark    | female |   18 |    201 |
| 204 | 售后部       |  2 | mark    | female |   18 |    201 |
| 200 | 技术部       |  3 | happy   | male   |   38 |    202 |
| 201 | 人力资源     |  3 | happy   | male   |   38 |    202 |
| 202 | 销售部       |  3 | happy   | male   |   38 |    202 |
| 203 | 运营部       |  3 | happy   | male   |   38 |    202 |
| 204 | 售后部       |  3 | happy   | male   |   38 |    202 |
| 200 | 技术部       |  4 | smile   | male   |   18 |    203 |
| 201 | 人力资源     |  4 | smile   | male   |   18 |    203 |
| 202 | 销售部       |  4 | smile   | male   |   18 |    203 |
| 203 | 运营部       |  4 | smile   | male   |   18 |    203 |
| 204 | 售后部       |  4 | smile   | male   |   18 |    203 |
| 200 | 技术部       |  5 | own     | male   |   28 |    204 |
| 201 | 人力资源     |  5 | own     | male   |   28 |    204 |
| 202 | 销售部       |  5 | own     | male   |   28 |    204 |
| 203 | 运营部       |  5 | own     | male   |   28 |    204 |
| 204 | 售后部       |  5 | own     | male   |   28 |    204 |
| 200 | 技术部       |  6 | thdream | male   |   18 |    205 |
| 201 | 人力资源     |  6 | thdream | male   |   18 |    205 |
| 202 | 销售部       |  6 | thdream | male   |   18 |    205 |
| 203 | 运营部       |  6 | thdream | male   |   18 |    205 |
| 204 | 售后部       |  6 | thdream | male   |   18 |    205 |
+-----+--------------+----+---------+--------+------+--------+
30 rows in set (0.00 sec)

【3】拼表升级

查询数据

-- 基于笛卡尔积进行品表 
-- 再对品表进行过滤
-- 员工表中的部门ID=部门表中ID 的数据
-- 拼接数据过滤的时候必须是二者ID相等才会被保留
select * from dep,emp where emp.dep_id = dep.id;

mysql> select * from emp;
+----+---------+--------+------+--------+
| id | name    | sex    | age  | dep_id |
+----+---------+--------+------+--------+
|  1 | silence | male   |   18 |    200 |
|  2 | mark    | female |   18 |    201 |
|  3 | happy   | male   |   38 |    202 |
|  4 | smile   | male   |   18 |    203 |
|  5 | own     | male   |   28 |    204 |
|  6 | thdream | male   |   18 |    205 |
+----+---------+--------+------+--------+
6 rows in set (0.00 sec)

mysql> select * from dep;
+-----+--------------+
| id  | name         |
+-----+--------------+
| 200 | 技术部       |
| 201 | 人力资源     |
| 202 | 销售部       |
| 203 | 运营部       |
| 204 | 售后部       |
+-----+--------------+
5 rows in set (0.00 sec)

mysql> select * from dep,emp where emp.dep_id = dep.id;
+-----+--------------+----+---------+--------+------+--------+
| id  | name         | id | name    | sex    | age  | dep_id |
+-----+--------------+----+---------+--------+------+--------+
| 200 | 技术部       |  1 | silence | male   |   18 |    200 |
| 201 | 人力资源     |  2 | mark    | female |   18 |    201 |
| 202 | 销售部       |  3 | happy   | male   |   38 |    202 |
| 203 | 运营部       |  4 | smile   | male   |   18 |    203 |
| 204 | 售后部       |  5 | own     | male   |   28 |    204 |
+-----+--------------+----+---------+--------+------+--------+
5 rows in set (0.00 sec)

一条SQL语句的查询结果我们也可以看成是一张虚拟表
如果一条SQL语句中设计到多张表的字段名称编写建议使用表名前缀做区分

【4】拼表关键字

inner join：内连接
- 只拼接两张表中共有的数据部分
left join：左连接
- 左表所有的数据都展示出来，没有对应的项就用null表示
right join：右连接
- 右表所有的数据都展示出来，没有对应的项就用null表示
union：全连接
- 左右两表的数据都展示出来

-- 内连接 inner join 并集
-- 只有两张表都有的数据才会被保留

-- 左连接 left join 
-- 只有左面表都有的数据才会被保留

-- 右连接 right join
-- 只有右面表都有的数据才会被保留

-- 全连接
-- 两张表不管有没有全都拼上去

（1）inner join(内连接)

只拼接两张表中共有的数据部分

select * from emp inner join dep on emp.dep_id = dep.id;

mysql> select * from emp inner join dep on emp.dep_id = dep.id;
+----+---------+--------+------+--------+-----+--------------+
| id | name    | sex    | age  | dep_id | id  | name         |
+----+---------+--------+------+--------+-----+--------------+
|  1 | silence | male   |   18 |    200 | 200 | 技术部       |
|  2 | mark    | female |   18 |    201 | 201 | 人力资源     |
|  3 | happy   | male   |   38 |    202 | 202 | 销售部       |
|  4 | smile   | male   |   18 |    203 | 203 | 运营部       |
|  5 | own     | male   |   28 |    204 | 204 | 售后部       |
+----+---------+--------+------+--------+-----+--------------+
5 rows in set (0.00 sec)

（2）left join(左连接)

左表所有的数据都展示出来，没有对应的项就用null表示

select * from dep left  join emp on emp.dep_id = dep.id;

mysql> select * from dep left  join emp on emp.dep_id = dep.id;
+-----+--------------+------+---------+--------+------+--------+
| id  | name         | id   | name    | sex    | age  | dep_id |
+-----+--------------+------+---------+--------+------+--------+
| 200 | 技术部       |    1 | silence | male   |   18 |    200 |
| 201 | 人力资源     |    2 | mark    | female |   18 |    201 |
| 202 | 销售部       |    3 | happy   | male   |   38 |    202 |
| 203 | 运营部       |    4 | smile   | male   |   18 |    203 |
| 204 | 售后部       |    5 | own     | male   |   28 |    204 |
+-----+--------------+------+---------+--------+------+--------+
5 rows in set (0.00 sec)

（3）right join(右连接)

右表所有的数据都展示出来，没有对应的项就用null表示

select * from dep right join emp on emp.dep_id = dep.id;

mysql> select * from dep right join emp on emp.dep_id = dep.id;
+------+--------------+----+---------+--------+------+--------+
| id   | name         | id | name    | sex    | age  | dep_id |
+------+--------------+----+---------+--------+------+--------+
|  200 | 技术部       |  1 | silence | male   |   18 |    200 |
|  201 | 人力资源     |  2 | mark    | female |   18 |    201 |
|  202 | 销售部       |  3 | happy   | male   |   38 |    202 |
|  203 | 运营部       |  4 | smile   | male   |   18 |    203 |
|  204 | 售后部       |  5 | own     | male   |   28 |    204 |
| NULL | NULL         |  6 | thdream | male   |   18 |    205 |
+------+--------------+----+---------+--------+------+--------+
6 rows in set (0.02 sec

（4）union(全连接)

左右两表的数据都展示出来

-- 全连接就是 左连接 union 右连接
select * from emp left join dep on emp.dep_id = dep.id 
union
select * from emp right join dep on emp.dep_id = dep.id;
;

mysql> select * from emp left join dep on emp.dep_id = dep.id
    -> union
    -> select * from emp right join dep on emp.dep_id = dep.id;
+------+---------+--------+------+--------+------+--------------+
| id   | name    | sex    | age  | dep_id | id   | name         |
+------+---------+--------+------+--------+------+--------------+
|    1 | silence | male   |   18 |    200 |  200 | 技术部       |
|    2 | mark    | female |   18 |    201 |  201 | 人力资源     |
|    3 | happy   | male   |   38 |    202 |  202 | 销售部       |
|    4 | smile   | male   |   18 |    203 |  203 | 运营部       |
|    5 | own     | male   |   28 |    204 |  204 | 售后部       |
|    6 | thdream | male   |   18 |    205 | NULL | NULL         |
+------+---------+--------+------+--------+------+--------------+
6 rows in set (0.02 sec)

【七】总结

我们学会了连表操作之后其实就可以将N多张表拼接到一起
思路
- 我们可以将两张表拼接之后的结果起别名当做一张表使用
- 然后再去跟另外一张表拼接

【八】案例：查询平均年龄在25岁以上的部门名称

-- 查询平均年龄在25岁以上的部门名称

-- 方案一：
-- 先查员工表 分组 取均值 过滤 剩下部门ID
select dep_id from emp 
	group by dep_id 
	having avg(age) > 25 ;
-- 再去部门表中根据部门ID查部门名称
select * from dep where id in (select dep_id from emp 
	group by dep_id 
	having avg(age) > 25);


-- 方案二：联表查
-- 先品表 ---> 过滤 在计算
select * from emp inner join dep on emp.dep_id = dep.id;

select dep.name from emp inner join dep 
    on emp.dep_id = dep.id
    group by dep.id
    having avg(age) > 25
    ;

涉及到多表操作的时候，一定要加上表的前缀

【1】联表

先拿到部门和员工表拼接之后的结果
对拼接后的结果进行部门分组

select * from emp inner join dep on emp.dep_id = dep.id;

select dep.name from emp inner join dep 
    on emp.dep_id = dep.id
    group by dep.id
    having avg(age) > 25
    ;

mysql> select * from emp inner join dep on emp.dep_id = dep.id;
+----+---------+--------+------+--------+-----+--------------+
| id | name    | sex    | age  | dep_id | id  | name         |
+----+---------+--------+------+--------+-----+--------------+
|  1 | silence | male   |   18 |    200 | 200 | 技术部       |
|  2 | mark    | female |   18 |    201 | 201 | 人力资源     |
|  3 | happy   | male   |   38 |    202 | 202 | 销售部       |
|  4 | smile   | male   |   18 |    203 | 203 | 运营部       |
|  5 | own     | male   |   28 |    204 | 204 | 售后部       |
+----+---------+--------+------+--------+-----+--------------+
5 rows in set (0.00 sec)

mysql> select dep.name from emp inner join dep
    ->     on emp.dep_id = dep.id
    ->     group by dep.id
    ->     having avg(age) > 25
    ->     ;
+-----------+
| name      |
+-----------+
| 销售部    |
| 售后部    |
+-----------+
2 rows in set (0.03 sec)

【2】子查询

分布操作

-- 先查员工表 分组 取均值 过滤 剩下部门ID
select dep_id from emp 
	group by dep_id 
	having avg(age) > 25 ;
-- 再去部门表中根据部门ID查部门名称
select * from dep where id in (select dep_id from emp 
	group by dep_id 
	having avg(age) > 25);

mysql> select dep_id from emp
    -> group by dep_id
    -> having avg(age) > 25 ;
+--------+
| dep_id |
+--------+
|    202 |
|    204 |
+--------+
2 rows in set (0.00 sec)

mysql> select * from dep where id in (select dep_id from emp
    -> group by dep_id
    -> having avg(age) > 25);
+-----+-----------+
| id  | name      |
+-----+-----------+
| 202 | 销售部    |
| 204 | 售后部    |
+-----+-----------+
2 rows in set (0.03 sec)

【补充】关键字exist

只返回布尔值
返回true时，外层查询语句执行
返回false时，外层查询语句不执行

select * from emp where exists (select id from dep where id > 3);

# 结果为 true

+----+---------+--------+------+--------+
| id | name    | sex    | age  | dep_id |
+----+---------+--------+------+--------+
|  1 | silence | male   |   18 |    200 |
|  2 | mark    | female |   18 |    201 |
|  3 | happy   | male   |   38 |    202 |
|  4 | smile   | male   |   18 |    203 |
|  5 | own     | male   |   28 |    204 |
|  6 | thdream | male   |   18 |    205 |
+----+---------+--------+------+--------+
6 rows in set (0.10 sec)

select * from emp where exists (select id from dep where id > 300);

# 结果为 false

Empty set (0.03 sec)

posted on 2024-05-30 21:25 silence^ 阅读(29) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

· Navicat， PDManer，PyMySQL模块，SQL注入问题，PyMySQL进阶之主动提交事务

· MySQL之过滤条件

· MySQL之筛选和过滤条件

· 【10.0】多表查询和子查询

zyb123

筛选条件之group by(分组),聚合函数min max avg count,过滤条件之having,筛选条件之distinct(去重),过滤条件之排序 order by,筛选条件之 limit(限制展示条数),过滤条件之正则表达式,多表查询和子查询

Ⅰ 筛选条件之group by(分组)

【一】什么时候需要分组？

【二】按照部门分组

【1】引入

【2】语法

【3】 按照部门对所有员工进行分组

（1）查询数据

（2）严格模式

（3）替换严格模式

（4）再查询数据

（5）其他办法

【三】聚合函数min / max /avg / count

【1】获取每个部门的最高薪资(max)

（1）聚合函数 - max

（2）数据代码展示

【2】查询数据指定别名,定制化字段显示的内容

【3】获取每个部门的最低薪资(min)

（1）聚合函数 - min

（2）数据代码展示

【4】获取每个部门的平均薪资(av

（1）聚合函数 - avg

（2）数据代码展示

【5】获取每个部门的薪资总和(sum)

（1）聚合函数 - sum

（2）数据代码展示

【6】获取每个部门的人数(count)

（1）聚合函数 - count

（2）数据代码展示

【7】count 函数不能对 null 进行计数

【8】查询分组之后的部门名称和每个部门下所有的员工姓名(group_concat)

（1）聚合函数 - group_concat

（2）查询数据

（3）查询数据(不分组之前用concat)

（4）as语法

【9】查询每个人的年薪(13)

【四】 筛选条件之 group by(分组) 注意事项

【1】引入

（1）关键字 where 和 group by 同时出现

（2）where 筛选条件不能使用聚合函数

【2】统计各部门年龄在 30 岁以上的员工的平均薪资

Ⅱ 过滤条件之having

【一】引入

【二】统计各部门年龄在 30 岁以上的员工的工资，并且保留平均薪资大于1w的部门

Ⅲ 筛选条件之distinct(去重)

Ⅳ 过滤条件之排序 order by

Ⅴ 筛选条件之 limit(限制展示条数)

【一】限制获取到的数据的总量：单数字限制

【二】查询数据：多限制

【三】案例：查询工资最高的人的详细信息

Ⅵ 过滤条件之正则表达式

【一】语法

【二】匹配方式

【1】准备数据

【2】查询 name 字段以j开头的记录

【3】查询 name 字段以“y”结尾的记录

【4】查询 name 字段值包含“a”和“y”，且两个字母之间只有一个字母的记录

【5】查询 name 字段值包含字母“T”，且“T”后面出现字母“h”的记录

【6】查询 name 字段值包含字母“T”，且“T”后面至少出现“h”一次的记录

【7】查询 name 字段值包含字母“S”，且“S”后面出现“a”一次或零次的记录

【8】查询 name 字段值包含字符串“an”的记录

【9】查询 name 字段值包含字符串“an”或“en”的记录

【10】查询 name 字段值包含字母“i”或“o”的记录

【11】方括号[ ]还可以指定集合的区间

【12】查询 name 字段值包含字母 a~t 以外的字符的记录

【13】查询 name 字段值出现字母‘e’ 至少 2 次的记录

【14】查询 name 字段值出现字符串“i” 最少 1 次，最多 3 次的记录

Ⅶ 多表查询和子查询

【一】子查询

【二】联表查询

【三】解释

【四】准备数据

【1】创建表

【2】插入数据

【3】查看表和数据

【五】子查询

【1】案例一：获取员工silence所在的部门名称

（1）先获取silence的部门编号

（2）将结果加括号作为查询条件

【3】按照部门对所有员工进行分组

【四】筛选条件之 group by(分组) 注意事项