【必知必会的MySQL知识】⑤DQL语言

来源：cnblogs　　作者：xiezhr　　时间：2023/5/4 9:15:46　　对本文有异议

一、前言

上一节中我们说了DML 数据操作语言，这一篇到了DQL语言，DQL语言就是我们常说的select 语句。
它是从一个表或多个表中根据各种条件，检索出我们想要的数据集。
DQL语句算是我们工作中最长用也是最复杂的SQL语句了。

二、基础查询

2.1 语法

-- ① 查询字段
select 字段1 as 别名1，字段2 as 别名2,字段3 as 别名3 ...字段 n 别名n from 表名; -- 当然了字段也是有限的，as 别名也是非必须的
-- ② 查询表达式
select 表达式; --
-- ③ 查询函数
select 函数名(参数列表);
-- ④ 查询常量
select 常量值;  --字符型和日期型的常量值必须用单引号引起来，数值型不需要

2.2 实践操作

新建user_profile信息表，并插入5条数据

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');

数据结构如下

id	device_id	gender	age	university	province
1	2138	male	21	北京大学	BeiJing
2	3214	male	None	复旦大学	Shanghai
3	6543	female	20	北京大学	BeiJing
4	2315	female	23	浙江大学	ZheJiang
5	5432	male	25	山东大学	Shandong

① 运营童鞋想要查看用户信息表中所有的数据【查询字段】

mysql> SELECT id,device_id,gender,age,university,province FROM user_profile;
+----+-----------+--------+------+------------+----------+
| id | device_id | gender | age  | university | province |
+----+-----------+--------+------+------------+----------+
|  1 |      2138 | male   |   21 | 北京大学   | BeiJing  |
|  2 |      3214 | male   | NULL | 复旦大学   | Shanghai |
|  3 |      6543 | female |   20 | 北京大学   | BeiJing  |
|  4 |      2315 | female |   23 | 浙江大学   | ZheJiang |
|  5 |      5432 | male   |   25 | 山东大学   | Shandong |
+----+-----------+--------+------+------------+----------+
5 rows in set (0.01 sec)

② 运营童鞋想要用户的设备id对应的性别、年龄和学校的数据【查询字段别名】

mysql> SELECT device_id AS 设备id,gender AS 性别,age AS 年龄,university AS 学校 FROM user_profile;
+--------+--------+------+----------+
| 设备id | 性别   | 年龄 | 学校     |
+--------+--------+------+----------+
|   2138 | male   |   21 | 北京大学 |
|   3214 | male   | NULL | 复旦大学 |
|   6543 | female |   20 | 北京大学 |
|   2315 | female |   23 | 浙江大学 |
|   5432 | male   |   25 | 山东大学 |
+--------+--------+------+----------+
5 rows in set (0.00 sec)

③ 运营童鞋想要查询 2568*234/23+234 等于多少？【计算表达式】

mysql> SELECT 2568*234/23+234 as result;
+------------+
| result     |
+------------+
| 26360.6087 |
+------------+
1 row in set (0.00 sec)

④ 运营童鞋想要查询当前时间【查询函数】

mysql 函数有很多，这里就不一一列举了，后面写一篇常用函数的使用

mysql> SELECT NOW() AS currdate;
+---------------------+
| currdate            |
+---------------------+
| 2022-09-15 23:42:29 |
+---------------------+
1 row in set (0.00 sec)

⑤ 运营童鞋想要构建一个张三同学【查询常量】

mysql> SELECT '张三' AS user_name, '男' AS sex, 18 AS age, 150 AS wight;
+-----------+-----+-----+-------+
| user_name | sex | age | wight |
+-----------+-----+-----+-------+
| 张三      | 男  |  18 |   150 |
+-----------+-----+-----+-------+
1 row in set (0.00 sec)

三、条件查询

3.1 语法

SELECT 查询列表 FROM 表名 WHERE 筛选条件;

3.2 where 语句操作符

条件运算符

操作符	操作符说明
`=`	等于
`<>`	不等于
`!=`	不等于
`<`	小于
`<=`	小于等于
`>=`	大于等于

逻辑运算符

操作符	操作符说明
`and`	连接多个条件，表示满足所有过滤条件的行
`or`	连接多个条件，表示满足任意一个条件的行
`not`	否定之后所跟的条件

模糊运算符

操作符	操作符说明
`like`	`%` 通配符表示任何字符出现任意次数 ;`_`通配符表示匹配一个字符
`between 取值1 and 取值2`	表示在取值1范围和取值2范围之间查询,取值1为范围的起始值；取值2为范围的终止值。通常是时间范围也可以是数字范围
`not between 取值1 and 取值2`	与上面相反，不在取值1和取值2范围之间的
`in`	指定条件范围，范围内的每个条件都可以进行匹配。in 的取值全都括在括号中，每个值用逗号隔开
`is null`	表示某个字段为null
`is not null`	表示某个字段不为空

3.3 实践操作

数据准备

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);

数据结构如下

id	device_id	gender	age	university	gpa
1	2138	male	21	北京大学	3.4
2	3214	male		复旦大学	4
3	6543	female	20	北京大学	3.2
4	2315	female	23	浙江大学	3.6
5	5432	male	25	山东大学	3.8
6	2131	male	28	北京师范大学	3.3

① 运营童鞋想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。

mysql> SELECT t.`device_id`,t.`university` FROM user_profile t WHERE t.`university` = '北京大学';
+-----------+------------+
| device_id | university |
+-----------+------------+
|      2138 | 北京大学   |
|      6543 | 北京大学   |
+-----------+------------+
2 rows in set (0.00 sec)

② 运营童鞋想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校。

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE age >24;
+-----------+--------+------+------------+
| device_id | gender | age  | university |
+-----------+--------+------+------------+
|      5432 | male   |   25 | 山东大学   |
+-----------+--------+------+------------+
1 row in set (0.00 sec)

③ 运营童鞋想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄。

mysql> SELECT t.`device_id`,t.`gender`,t.`age` FROM user_profile t WHERE age BETWEEN 20 AND 23;
+-----------+--------+------+
| device_id | gender | age  |
+-----------+--------+------+
|      2138 | male   |   21 |
|      6543 | female |   20 |
|      2315 | female |   23 |
+-----------+--------+------+
3 rows in set (0.00 sec)

④ 运营童鞋想要查看除复旦大学以外的所有用户明细，请你取出相应数据

-- ① 第一种写法 使用<>
mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE t.`university` <> '复旦大学';
+-----------+--------+------+------------+
| device_id | gender | age  | university |
+-----------+--------+------+------------+
|      2138 | male   |   21 | 北京大学   |
|      6543 | female |   20 | 北京大学   |
|      2315 | female |   23 | 浙江大学   |
|      5432 | male   |   25 | 山东大学   |
+-----------+--------+------+------------+
4 rows in set (0.00 sec)
-- ② 第二种写法 使用!=
mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE t.`university` != '复旦大学';
+-----------+--------+------+------------+
| device_id | gender | age  | university |
+-----------+--------+------+------------+
|      2138 | male   |   21 | 北京大学   |
|      6543 | female |   20 | 北京大学   |
|      2315 | female |   23 | 浙江大学   |
|      5432 | male   |   25 | 山东大学   |
+-----------+--------+------+------------+
4 rows in set (0.00 sec)

⑤ 运营童鞋想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE t.`age` IS NOT NULL;
+-----------+--------+------+------------+
| device_id | gender | age  | university |
+-----------+--------+------+------------+
|      2138 | male   |   21 | 北京大学   |
|      6543 | female |   20 | 北京大学   |
|      2315 | female |   23 | 浙江大学   |
|      5432 | male   |   25 | 山东大学   |
+-----------+--------+------+------------+
4 rows in set (0.00 sec)

⑥ 运营童鞋想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研，请你取出相关数据。

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university`,t.gpa FROM user_profile t WHERE t.gpa>3.5;
+-----------+--------+------+------------+------+
| device_id | gender | age  | university | gpa  |
+-----------+--------+------+------------+------+
|      3214 | male   | NULL | 复旦大学   |    4 |
|      2315 | female |   23 | 浙江大学   |  3.6 |
|      5432 | male   |   25 | 山东大学   |  3.8 |
+-----------+--------+------+------------+------+
3 rows in set (0.00 sec)

⑦ 运营童鞋想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据（使用OR实现）

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university`,t.gpa FROM user_profile t WHERE (t.`university` = '北京大 学' OR t.`gpa`>3.7);
+-----------+--------+------+------------+------+
| device_id | gender | age  | university | gpa  |
+-----------+--------+------+------------+------+
|      2138 | male   |   21 | 北京大学   |  3.4 |
|      3214 | male   | NULL | 复旦大学   |    4 |
|      6543 | female |   20 | 北京大学   |  3.2 |
|      5432 | male   |   25 | 山东大学   |  3.8 |
+-----------+--------+------+------------+------+
4 rows in set (0.00 sec)

⑧ 运营童鞋想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university`,t.gpa FROM user_profile t WHERE t.`university` IN ('北京大学','复旦大学','山东大学');
+-----------+--------+------+------------+------+
| device_id | gender | age  | university | gpa  |
+-----------+--------+------+------------+------+
|      2138 | male   |   21 | 北京大学   |  3.4 |
|      3214 | male   | NULL | 复旦大学   |    4 |
|      6543 | female |   20 | 北京大学   |  3.2 |
|      5432 | male   |   25 | 山东大学   |  3.8 |
+-----------+--------+------+------------+------+
4 rows in set (0.00 sec)

⑨ 运营童鞋想要找到gpa在3.5以上(不包括3.5)的山东大学用户或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university`,t.gpa FROM user_profile t WHERE (t.gpa>3.5 AND t.`university` = '山东大学') OR (t.gpa>3.8 AND t.`university` = '复旦大学');
+-----------+--------+------+------------+------+
| device_id | gender | age  | university | gpa  |
+-----------+--------+------+------------+------+
|      3214 | male   | NULL | 复旦大学   |    4 |
|      5432 | male   |   25 | 山东大学   |  3.8 |
+-----------+--------+------+------------+------+
2 rows in set (0.00 sec)

⑩ 运营童鞋想查看所有大学中带有北京的用户的信息，请你取出相应数据。


mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE t.`university` LIKE '%北京%';
+-----------+--------+------+--------------+
| device_id | gender | age  | university   |
+-----------+--------+------+--------------+
|      2138 | male   |   21 | 北京大学     |
|      6543 | female |   20 | 北京大学     |
|      2131 | male   |   28 | 北京师范大学 |
+-----------+--------+------+--------------+
3 rows in set (0.00 sec)

四、排序查询

4.1 语法格式

asc代表升序，desc代表降序，如果不写，默认是asc
排序列表可以是单个字段、多个字段、别名、函数、表达式
order by的位置一般放在查询语句的最后（除limit语句之外）

SELECT 
  查询列表 
FROM
  表 
【WHERE 筛选条件】
ORDER BY 排序列表 【asc | desc】 ;

4.2 实践操作

数据准备

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',23,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);

id	device_id	gender	age	university	gpa
1	2138	male	21	北京大学	3.4
2	3214	male	23	复旦大学	4
3	6543	female	20	北京大学	3.2
4	2315	female	23	浙江大学	3.6
5	5432	male	25	山东大学	3.8
6	2131	male	28	北京师范大学	3.3

①运营童鞋想要取出用户信息表中的用户年龄，请取出相应数据，并按照年龄升序排序。

mysql> SELECT t.`device_id`,t.`age`FROM user_profile t ORDER BY t.age ASC;
+-----------+------+
| device_id | age  |
+-----------+------+
|      6543 |   20 |
|      2138 |   21 |
|      3214 |   23 |
|      2315 |   23 |
|      5432 |   25 |
|      2131 |   28 |
+-----------+------+
6 rows in set (0.00 sec)

②运营童鞋想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据。

mysql> SELECT device_id,gpa,age FROM user_profile ORDER BY gpa ASC,age ASC;
+-----------+------+------+
| device_id | gpa  | age  |
+-----------+------+------+
|      6543 |  3.2 |   20 |
|      2131 |  3.3 |   28 |
|      2138 |  3.4 |   21 |
|      2315 |  3.6 |   23 |
|      5432 |  3.8 |   25 |
|      3214 |    4 |   23 |
+-----------+------+------+
6 rows in set (0.00 sec)

③ 运营童鞋想要取出用户信息表中对应的数据，并先按照gpa、年龄降序排序输出，请取出相应数据。

mysql> SELECT device_id,gpa,age FROM user_profile ORDER BY gpa DESC,age DESC;
+-----------+------+------+
| device_id | gpa  | age  |
+-----------+------+------+
|      3214 |    4 |   23 |
|      5432 |  3.8 |   25 |
|      2315 |  3.6 |   23 |
|      2138 |  3.4 |   21 |
|      2131 |  3.3 |   28 |
|      6543 |  3.2 |   20 |
+-----------+------+------+
6 rows in set (0.00 sec)

五、分组查询

5.1 语法

SELECT 
  查询列表 
FROM
  表 
【where 筛选条件】 
GROUP BY 分组的字段 
【having 分组后的筛选】
【order BY 排序的字段】 ;

5.2 聚集函数

运行在行组上，计算和返回单个值的函数

聚集函数	分组函数说明
`sum()`	返回某列值之和
`avg()`	返回某列平均值
`max()`	返回某列最大值
`min()`	返回某列最小值
`count()`	返回某列的函数

5.2.1 聚集函数简单使用

*数据准备，新建一个产品信息表product

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `productid` varchar(10) NOT NULL COMMENT '产品id',
  `productname` varchar(300) NOT NULL COMMENT '产品名称',
  `saleprice` decimal(10,0) DEFAULT NULL COMMENT '零售价',
  `author` varchar(200) DEFAULT NULL COMMENT '作者',
  PRIMARY KEY (`id`)
);
INSERT INTO `product` VALUES  (    1,    '10001',    '公众号XiezhrSpace【Oralce从入门到放弃】',    100,    'xiezhr001'  );
INSERT INTO `product` VALUES  (    2,    '10002',    '公众号XiezhrSpace【Linux核心命令快速上手】',    300,    'xiezhr'  );
INSERT INTO `product` VALUES  (    3,    '10003',    '公众号XiezhrSpace【你写注释她帮你写代码】',    80,    'xiezhr'  );
INSERT INTO `product` VALUES  (    4,    '10004',    '公众号XiezhrSpace【Java从入门到精通】',    150,    'xiezhr001'  );
INSERT INTO `product` VALUES  (    5,    '10005',    '公众号XiezhrSpace【gitee不能用了】',    55,    'xiezhr'  );
INSERT INTO `product` VALUES  (    6,    '10006',    '公众号XiezhrSpace【如何快速搭建个人博客】',    120,    'xiezhr'  );
INSERT INTO `product` VALUES  (    7,    '10007',    '公众号XiezhrSpace【MySQL从入门到入土】',    320,    'xiezhr'  );
INSERT INTO `product` VALUES  (    8,    '10008',    '公众号XiezhrSpace【idea从入门到上瘾】',    500,    'xiezhr'  ) ;

id	productid	productname	saleprice	author
1	10001	公众号XiezhrSpace【Oralce从入门到放弃】	100	xiezhr001
2	10002	公众号XiezhrSpace【Linux核心命令快速上手】	300	xiezhr
3	10003	公众号XiezhrSpace【你写注释她帮你写代码】	80	xiezhr
4	10004	公众号XiezhrSpace【Java从入门到精通】	150	xiezhr001
5	10005	公众号XiezhrSpace【gitee不能用了】	55	xiezhr
6	10006	公众号XiezhrSpace【如何快速搭建个人博客】	120	xiezhr
7	10007	公众号XiezhrSpace【MySQL从入门到入土】	320	xiezhr
8	10008	公众号XiezhrSpace【idea从入门到上瘾】	500	xiezhr

-- 1、计算所有产品单价之和
mysql> select sum(saleprice) from product;
+----------------+
| sum(saleprice) |
+----------------+
|           1625 |
+----------------+
1 row in set (0.00 sec)
-- 2、计算所有产品单价平均值
mysql> select avg(saleprice) from product;
+----------------+
| avg(saleprice) |
+----------------+
|       203.1250 |
+----------------+
1 row in set (0.00 sec)
-- 3、获取所有产品中最大单价
mysql> select max(saleprice) from product;
+----------------+
| max(saleprice) |
+----------------+
|            500 |
+----------------+
1 row in set (0.00 sec)
-- 4、获取所有产品中最小单价
mysql> select min(saleprice) from product;
+----------------+
| min(saleprice) |
+----------------+
|             55 |
+----------------+
1 row in set (0.00 sec)
-- 5、获取一共有多少产品
mysql> select count(*) from product;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)
-- 或者
mysql> select count(1) from product;
+----------+
| count(1) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

5.3 实践操作

数据准备

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` float,
`answer_cnt` float
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male		复旦大学	4.0	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	female	26	复旦大学	3.6	9	6	52

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

①运营童鞋想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校

mysql> SELECT
    ->   t.university,
    ->   AVG(question_cnt) AS avg_question_cnt,
    ->   AVG(answer_cnt) AS avg_answer_cnt
    -> FROM
    ->   user_profile t
    -> GROUP BY t.university
    -> HAVING avg_question_cnt < 5
    ->   OR avg_answer_cnt < 20;
+------------+------------------+----------------+
| university | avg_question_cnt | avg_answer_cnt |
+------------+------------------+----------------+
| 北京大学   |              2.5 |             21 |
| 浙江大学   |                1 |              2 |
+------------+------------------+----------------+
2 rows in set (0.00 sec)
--说明: 平均发贴数低于5的学校或平均回帖数小于20的学校有2个
--属于北京大学的用户的平均发帖量为2.500，平均回答数量为21.000
--属于浙江大学的用户的平均发帖量为1.000，平均回答数量为2.000

② 运营童鞋想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据

mysql> SELECT
    ->   t.university,
    ->   AVG(question_cnt) AS avg_question_cnt
    -> FROM
    ->   user_profile t
    -> GROUP BY
    ->   t.university
    -> ORDER BY
    ->   avg_question_cnt;
+------------+------------------+
| university | avg_question_cnt |
+------------+------------------+
| 浙江大学   |                1 |
| 北京大学   |              2.5 |
| 复旦大学   |              5.5 |
| 山东大学   |               11 |
+------------+------------------+
4 rows in set (0.00 sec)

③ 运营童鞋想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量

mysql> SELECT
    ->   gender,
    ->   university,
    ->   COUNT(1) AS user_num,
    ->   AVG(active_days_within_30) AS avg_active_day,
    ->   AVG(question_cnt) avg_question_cnt
    -> FROM
    ->   user_profile
    -> GROUP BY gender,
    ->   university;
+--------+------------+----------+----------------+------------------+
| gender | university | user_num | avg_active_day | avg_question_cnt |
+--------+------------+----------+----------------+------------------+
| female | 北京大学   |        1 |        12.0000 |                3 |
| female | 浙江大学   |        1 |         5.0000 |                1 |
| male   | 北京大学   |        1 |         7.0000 |                2 |
| male   | 复旦大学   |        2 |        12.0000 |              5.5 |
| male   | 山东大学   |        2 |        17.5000 |               11 |
+--------+------------+----------+----------------+------------------+
5 rows in set (0.00 sec)
--说明：
--第一行表示：北京大学的男性用户个数为1，平均活跃天数为7天，平均发帖量为2
-- ...
-- 最后一行表示：山东大学的男性用户个数为2，平均活跃天数为17.5天，平均发帖量为11

5.4 规定与小结

group by 子句可以包含任意数目的列
group by 子句中列出的每个列都必须是检索列或者有效表达式（不能是聚集函数）；select 语句中使用了表达式，group by 子句中也必须指定相同的表达式；不能使用别名。
除聚集函数外，select 中的每一个列都必须在group by 子句中给出
分组列中具有null值，则将null作为一个分组返回。如果列中有多个null值，将被分为一组
各子句顺序， select 子句 from 表名 where 子句 group by 子句 having 子句 order by 子句 limit 子句 使用时必须按照上面顺序来
**where 和hiving的区别：where 在数据分组前过滤，hiving 在数据分组后过滤 **

六、连接查询（多表查询）

6.1 简介

连接查询又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

6.2 笛卡儿积

表A有m行，表B有n行，结果=m*n行
产生原因：没有有效的连接条件
避免方法：添加有效连接条件

6.3 连接分类

内连接 连接表之间没有主次关系,条件匹配上的就显示，匹配不上的就不显示
- 等值连接 连接表之间的连接条件为等值关系
- 非等值连接 连接表之间的连接条件为等值关系
- 自连接
外联结 连接表之间有主次关系，主表全部显示
- 左外连接（左连接） join右边的表为主表
- 右外连接（有连接） join 左边的表为主表

6.4 语法格式

随着mysql的升级，语法分为sql92标准、sql99标准

6.4.1 内连接之等值连接sql92标准

-写法简单，但是结构不清晰，表的连接条件和后期筛选条件都放到where子句中

select 查询列表
from 表1 t1,表2 t2
where t1 和 t2 的连接条件

6.4.2 内连接之等值连接sql99标准

-表连接的条件时独立的，连接之后，如果还需要进一步筛选，再往后加where 条件即可
内连接中inner 关键字可以省去

select 查询列表
from 表1 t1
inner join                  
表2 t2
on t1 和 t2 的等值连接条件
where 筛选条件

6.4.3 内连接之非等值连接

select 查询列表
from 表1 t1
inner join                  
表2 t2
on t1 和 t2 的非等值连接条件
where 筛选条件

6.4.3 内连接之自连接

一张表看作两张表

select 查询列表
from 表1 t1
inner join                  
表1 t2
on t1 和 t2 的关联条件
where 筛选条件

6.4.4 外连接之右连接

join 右边的表“表2”作为主表，根据条件将表2中数据全部查出来

select 查询列表
from 表1 t1
right outer join             -- outer 可以省去                
表2 t2
on t1 和 t2 的非等值连接条件
where 筛选条件

6.4.5 外连接之左连接

select 查询列表
from 表1 t1
left outer  join           -- outer 可以省去        
表2 t2
on t1 和 t2 的非等值连接条件
where 筛选条件

6.5 实践操作

数据准备，一共三张表。部门表dept 、员工信息表emp 、工资等级表SALGRADE

-- 部门表
CREATE TABLE DEPT(
    DEPTNO INT PRIMARY KEY,  -- 部门编号
    DNAME VARCHAR(14) ,  -- 部门名称
    LOC VARCHAR(13) -- 部门地址
    ) ; 
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

-- 员工信息表
CREATE TABLE EMP
    (
    EMPNO INT  PRIMARY KEY,  -- 员工编号
    ENAME VARCHAR(10),  -- 员工名称
    JOB VARCHAR(9), -- 工作
    MGR DOUBLE, -- 直属领导编号
    HIREDATE DATE,  -- 入职时间
    SAL DOUBLE, -- 工资
    COMM DOUBLE, -- 奖金
    DEPTNO INT, -- 部门号
    FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));
  INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980/12/17	800	null	20
7499	ALLEN	SALESMAN	7698	1981/2/20	1600	300	30
7521	WARD	SALESMAN	7698	1981/2/22	1250	500	30
7566	JONES	MANAGER	7839	1981/4/2	2975	null	20
7654	MARTIN	SALESMAN	7698	1981/9/28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981/5/1	2850	null	30
7782	CLARK	MANAGER	7839	1981/6/9	2450	null	10
7788	SCOTT	ANALYST	7566	1987/7/13	3000	null	20
7839	KING	PRESIDENT	null	1981/11/17	5000	null	10
7844	TURNER	SALESMAN	7698	1981/9/8	1500	0	30
7876	ADAMS	CLERK	7788	1987/7/13	1100	null	20
7900	JAMES	CLERK	7698	1981/12/3	950	null	30
7902	FORD	ANALYST	7566	1981/12/3	3000	null	20
7934	MILLER	CLERK	7782	1982/1/23	1300	null	10

CREATE TABLE SALGRADE
      ( GRADE INT,  -- 工资等级
    LOSAL DOUBLE, -- 最低工资
    HISAL DOUBLE ); -- 最高工资
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

GRADE	LOSAL	HISAL
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

①运营童鞋想要查询SMITH 员工所在部门

--1.sql92标准语法
mysql> SELECT
    ->   e.ename,
    ->   e.deptno,
    ->   d.dname
    -> FROM
    ->   emp e,
    ->   dept d
    -> WHERE e.deptno = d.deptno
    -> AND e.ename='SMITH';
+-------+--------+----------+
| ename | deptno | dname    |
+-------+--------+----------+
| SMITH |     20 | RESEARCH |
+-------+--------+----------+
1 row in set (0.00 sec)
--2.sql99标准语法
mysql> SELECT
    ->   e.ename,
    ->   e.deptno,
    ->   d.dname
    -> FROM
    ->   emp e
    ->   JOIN
    ->   dept d
    -> ON e.deptno = d.deptno
    -> WHERE e.ename='SMITH';
+-------+--------+----------+
| ename | deptno | dname    |
+-------+--------+----------+
| SMITH |     20 | RESEARCH |
+-------+--------+----------+
1 row in set (0.00 sec)

注以上例子中e.deptno = d.deptno 为等值关联，所以上面例子时等值关联查询

② 运营童鞋想要查看每个员工的薪资等级，要求显示员工名、薪资、薪资等级

mysql> SELECT
    ->   e.ename,
    ->   e.sal,
    ->   s.grade
    -> FROM
    ->   emp e
    -> JOIN
    ->   salgrade s
    -> ON e.sal BETWEEN s.losal AND s.hisal;
+--------+------+-------+
| ename  | sal  | grade |
+--------+------+-------+
| SMITH  |  800 |     1 |
| ALLEN  | 1600 |     3 |
| WARD   | 1250 |     2 |
| JONES  | 2975 |     4 |
| MARTIN | 1250 |     2 |
| BLAKE  | 2850 |     4 |
| CLARK  | 2450 |     4 |
| SCOTT  | 3000 |     4 |
| KING   | 5000 |     5 |
| TURNER | 1500 |     3 |
| ADAMS  | 1100 |     1 |
| JAMES  |  950 |     1 |
| FORD   | 3000 |     4 |
| MILLER | 1300 |     2 |
+--------+------+-------+
14 rows in set (0.00 sec)

注以上例子中e.sal BETWEEN s.losal AND s.hisal 为非等值关联，所以上面例子时非等值关联查询

③ 查询员工SMITH 和员工SCOTT 上级领导

mysql> SELECT
    ->   e1.ename AS '员工名',
    ->   e2.ename AS '领导名'
    -> FROM
    ->   emp e1
    -> JOIN
    ->   emp e2
    -> ON e1.mgr=e2.empno
    -> WHERE e1.ename IN('SMITH','SCOTT');
+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SMITH  | FORD   |
| SCOTT  | JONES  |
+--------+--------+
2 rows in set (0.01 sec)

④运营童鞋想要查看所有部门的员工信息，如果新设立的部门没有员工也要将其显示出来

任何左连接可以实现的右连接也可以实现
1、通过左连接实现

-- 部门编号为40的OPERATIONS 部门没有员工也要显示出来
mysql> SELECT d.deptno,d.dname,e.ename
    -> FROM dept d
    -> LEFT JOIN
    -> emp e
    -> ON d.deptno = e.deptno;
+--------+------------+--------+
| deptno | dname      | ename  |
+--------+------------+--------+
|     10 | ACCOUNTING | CLARK  |
|     10 | ACCOUNTING | KING   |
|     10 | ACCOUNTING | MILLER |
|     20 | RESEARCH   | SMITH  |
|     20 | RESEARCH   | JONES  |
|     20 | RESEARCH   | SCOTT  |
|     20 | RESEARCH   | ADAMS  |
|     20 | RESEARCH   | FORD   |
|     30 | SALES      | ALLEN  |
|     30 | SALES      | WARD   |
|     30 | SALES      | MARTIN |
|     30 | SALES      | BLAKE  |
|     30 | SALES      | TURNER |
|     30 | SALES      | JAMES  |
|     40 | OPERATIONS | NULL   |
+--------+------------+--------+
15 rows in set (0.00 sec)

2、通过右连接实现

mysql> SELECT d.deptno,d.dname,e.ename
    -> FROM emp e
    -> RIGHT JOIN
    -> dept d
    -> ON d.deptno = e.deptno;
+--------+------------+--------+
| deptno | dname      | ename  |
+--------+------------+--------+
|     10 | ACCOUNTING | CLARK  |
|     10 | ACCOUNTING | KING   |
|     10 | ACCOUNTING | MILLER |
|     20 | RESEARCH   | SMITH  |
|     20 | RESEARCH   | JONES  |
|     20 | RESEARCH   | SCOTT  |
|     20 | RESEARCH   | ADAMS  |
|     20 | RESEARCH   | FORD   |
|     30 | SALES      | ALLEN  |
|     30 | SALES      | WARD   |
|     30 | SALES      | MARTIN |
|     30 | SALES      | BLAKE  |
|     30 | SALES      | TURNER |
|     30 | SALES      | JAMES  |
|     40 | OPERATIONS | NULL   |
+--------+------------+--------+
15 rows in set (0.00 sec)

⑤ 运营童鞋想要查看员工SMITH 的部门薪资等级
想要完成运营童鞋的需求，需要关联三张表，但也不是什么难事

mysql> SELECT e.ename,e.sal, d.dname,s.grade
    -> FROM emp e
    -> JOIN dept d
    -> ON e.deptno = d.deptno
    -> JOIN salgrade s
    -> ON e.sal BETWEEN s.losal AND s.hisal
    -> WHERE e.ename = 'SMITH';
+-------+------+----------+-------+
| ename | sal  | dname    | grade |
+-------+------+----------+-------+
| SMITH |  800 | RESEARCH |     1 |
+-------+------+----------+-------+
1 row in set (0.00 sec)

七、子查询

7.1 简介

select语句中嵌套select语句，被嵌套的select语句称为子查询

7.2 出现的位置

select 后面
from后面 将子查询当作一张临时表
where 或having后面 将子查询当作一个条件
exists 后面

7.3 实践操作

数据准备，一共三张表。部门表dept 、员工信息表emp 、工资等级表SALGRADE

-- 部门表
CREATE TABLE DEPT(
    DEPTNO INT PRIMARY KEY,  -- 部门编号
    DNAME VARCHAR(14) ,  -- 部门名称
    LOC VARCHAR(13) -- 部门地址
    ) ; 
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

-- 员工信息表
CREATE TABLE EMP
    (
    EMPNO INT  PRIMARY KEY,  -- 员工编号
    ENAME VARCHAR(10),  -- 员工名称
    JOB VARCHAR(9), -- 工作
    MGR DOUBLE, -- 直属领导编号
    HIREDATE DATE,  -- 入职时间
    SAL DOUBLE, -- 工资
    COMM DOUBLE, -- 奖金
    DEPTNO INT, -- 部门号
    FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));
SELECT * FROM emp;
  INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980/12/17	800	null	20
7499	ALLEN	SALESMAN	7698	1981/2/20	1600	300	30
7521	WARD	SALESMAN	7698	1981/2/22	1250	500	30
7566	JONES	MANAGER	7839	1981/4/2	2975	null	20
7654	MARTIN	SALESMAN	7698	1981/9/28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981/5/1	2850	null	30
7782	CLARK	MANAGER	7839	1981/6/9	2450	null	10
7788	SCOTT	ANALYST	7566	1987/7/13	3000	null	20
7839	KING	PRESIDENT	null	1981/11/17	5000	null	10
7844	TURNER	SALESMAN	7698	1981/9/8	1500	0	30
7876	ADAMS	CLERK	7788	1987/7/13	1100	null	20
7900	JAMES	CLERK	7698	1981/12/3	950	null	30
7902	FORD	ANALYST	7566	1981/12/3	3000	null	20
7934	MILLER	CLERK	7782	1982/1/23	1300	null	10

CREATE TABLE SALGRADE
      ( GRADE INT,  -- 工资等级
    LOSAL DOUBLE, -- 最低工资
    HISAL DOUBLE ); -- 最高工资
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

GRADE	LOSAL	HISAL
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

① 运营童鞋想要查询每个员工的部门名称 (select 后面)

mysql> SELECT e.ename,(SELECT dname FROM dept d WHERE d.deptno = e.deptno) AS dpatname FROM emp e;
+--------+------------+
| ename  | dpatname   |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

② 运营童鞋想要查询每个工作岗位的平均工资及等级 (from 后面)

mysql> SELECT
    ->   t.job,
    ->   t.avgsal,
    ->   s.grade
    -> FROM
    ->   (SELECT
    ->     e.job,
    ->     AVG(e.sal) AS avgsal
    ->   FROM
    ->     emp e
    ->   GROUP BY e.job) t
    ->   JOIN salgrade s
    ->     ON t.avgsal BETWEEN s.losal
    ->     AND s.hisal ;
+-----------+--------------------+-------+
| job       | avgsal             | grade |
+-----------+--------------------+-------+
| ANALYST   |               3000 |     4 |
| CLERK     |             1037.5 |     1 |
| MANAGER   | 2758.3333333333335 |     4 |
| PRESIDENT |               5000 |     5 |
| SALESMAN  |               1400 |     2 |
+-----------+--------------------+-------+
5 rows in set (0.00 sec)

③运营童鞋想要查看比最低工资高的员工和姓名(where 后面)


mysql>    SELECT e.ename,e.sal FROM emp e WHERE sal >(SELECT MIN(sal) FROM emp );
+--------+------+
| ename  | sal  |
+--------+------+
| ALLEN  | 1600 |
| WARD   | 1250 |
| JONES  | 2975 |
| MARTIN | 1250 |
| BLAKE  | 2850 |
| CLARK  | 2450 |
| SCOTT  | 3000 |
| KING   | 5000 |
| TURNER | 1500 |
| ADAMS  | 1100 |
| JAMES  |  950 |
| FORD   | 3000 |
| MILLER | 1300 |
+--------+------+
13 rows in set (0.00 sec)

④运营童鞋想要查询有员工的部门名或没有员工的部门名(exists 后面)

-- 1.查询有员工的部门
mysql>    SELECT d.deptno,d.dname FROM dept d WHERE EXISTS (SELECT 1 FROM emp e WHERE d.deptno = e.deptno);
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
+--------+------------+
3 rows in set (0.00 sec)
-- 2.查询没有员工的部门
mysql> SELECT d.deptno,d.dname FROM dept d WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE d.deptno = e.deptno);
+--------+------------+
| deptno | dname      |
+--------+------------+
|     40 | OPERATIONS |
+--------+------------+
1 row in set (0.00 sec)

八、组合查询

8.1 简介

将查询结果集合并成新的结果集

8.2 语法

union关键字默认去重，如果使用union all可以包含重复项

查询语句1
union 【all】
查询语句2
union 【all】
...

8.3 特点

要查询的结果来自于多个表且多个表没有直接的连接关系，但查询的信息一致时，可以使用联合查询
要求多条查询语句的查询列数是一致的
要求多条查询语句的查询的每一列的类型和顺序一致

8.4 实践操作

如下所示，准备一张学生信息表、一张教师信息表

DROP TABLE IF EXISTS student;
CREATE TABLE student(
	stuNo	VARCHAR(5) PRIMARY KEY, -- 学生学号
	stuName VARCHAR(32) NOT NULL, -- 学生姓名
	gender VARCHAR(1) NOT NULL	DEFAULT '男', -- 学生性别
	age INT NOT NULL, -- 学生年龄
	school VARCHAR(100) --所属学校
);
INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('001','李志','男',22,'北京大学');
INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('002','宋东野','男',23,'天津大学');
INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('003','赵雷','男',34,'山东大学');
INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('004','马頔','男',32,'北京大学');
INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('005','陈粒','女',18,'山东大学');
INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('006','筠子','女',23,'厦门大学');

stuNo	stuName	gender	age	school
1	李志	男	22	北京大学
2	宋东野	男	23	天津大学
3	赵雷	男	34	山东大学
4	马頔	男	32	北京大学
5	陈粒	女	18	山东大学
6	筠子	女	23	厦门大学


DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher(
	tNo 	VARCHAR(5) PRIMARY KEY, -- 教师编号
	tName  VARCHAR(32) NOT NULL, -- 教师姓名
	gender VARCHAR(1) NOT NULL	DEFAULT '男', -- 教师性别
	age INT NOT NULL, -- 教师年龄
	school VARCHAR(100)  -- 所属学校
);
INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('001','李璇','女',35,'北京大学');
INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('002','张天宇','男',45,'厦门大学');
INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('003','刘晓','女',35,'天津大学');
INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('004','钟鸣','男',32,'山东大学');
INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('005','宋小白','男',35,'云南大学');
INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('006','梁小如','女',35,'北京师范大学');

tNo	tName	gender	age	school
1	李璇	女	35	北京大学
2	张天宇	男	45	厦门大学
3	刘晓	女	35	天津大学
4	钟鸣	男	32	山东大学
5	宋小白	男	35	云南大学
6	梁小如	女	35	北京师范大学

① 运营童鞋想要查询北京大学的所有老师和学生信息

mysql> SELECT '学生' AS ptype, s.stuname,s.gender,s.age FROM student s WHERE s.school = '北京大学'
    -> UNION ALL
    -> SELECT '教师' AS ptype, t.tname,t.gender,t.age FROM teacher t WHERE t.school = '北京大学';
+-------+---------+--------+-----+
| ptype | stuname | gender | age |
+-------+---------+--------+-----+
| 学生  | 李志    | 男     |  22 |
| 学生  | 马頔    | 男     |  32 |
| 教师  | 李璇    | 女     |  35 |
+-------+---------+--------+-----+
3 rows in set (0.01 sec)

② 运营童鞋想要查看山东大学和北京大学的学生信息

mysql> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school = '山东大学'
    -> UNION ALL
    -> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school = '北京大学';
+---------+--------+-----+----------+
| stuname | gender | age | school   |
+---------+--------+-----+----------+
| 赵雷    | 男     |  34 | 山东大学 |
| 陈粒    | 女     |  18 | 山东大学 |
| 李志    | 男     |  22 | 北京大学 |
| 马頔    | 男     |  32 | 北京大学 |
+---------+--------+-----+----------+
4 rows in set (0.00 sec)
-- 或者可以通过以下写法实现
mysql> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school IN ( '北京大学' ,'山东大学');
+---------+--------+-----+----------+
| stuname | gender | age | school   |
+---------+--------+-----+----------+
| 李志    | 男     |  22 | 北京大学 |
| 赵雷    | 男     |  34 | 山东大学 |
| 马頔    | 男     |  32 | 北京大学 |
| 陈粒    | 女     |  18 | 山东大学 |
+---------+--------+-----+----------+
4 rows in set (0.00 sec)
-- 或者可以通过以下写法实现
mysql> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school = '北京大学' OR s.school = '山东大学';
+---------+--------+-----+----------+
| stuname | gender | age | school   |
+---------+--------+-----+----------+
| 李志    | 男     |  22 | 北京大学 |
| 赵雷    | 男     |  34 | 山东大学 |
| 马頔    | 男     |  32 | 北京大学 |
| 陈粒    | 女     |  18 | 山东大学 |
+---------+--------+-----+----------+
4 rows in set (0.00 sec)

九、分页查询

9.1 简介

假设一个公司有10000名员工，界面上需要展示员工信息。这时候我们就需要使用分页查询，将员工信息按n页展示，每页显示m名员工信息

9.2 语法

9.2.1 limit 语法

limit语句放在查询语句的最后

- startindex 表示起始索引，size代表条目数
SELECT 
  查询列表 
FROM
  表1 别名1
【连接类型】 JOIN 表2 别名2 ON 连接条件 
【WHERE 分组前的筛选】
【GROUP BY 分组字段】 
【HAVING 分组后的筛选 】
【ORDER BY 排序字段 ASC|DESC】
LIMIT [startindex] size ;

9.2.2 分页查询语法

分页查询展示可以提高用户体验

-- page 表示第几页
-- size 表示每页显示多少条数据
select 查询列表 from 表 limit (page-1)*size,size;

9.3 实践操作

按以下脚本准备一张员工表信息

CREATE TABLE EMP
    (
    EMPNO INT  PRIMARY KEY,  -- 员工编号
    ENAME VARCHAR(10),  -- 员工名称
    JOB VARCHAR(9), -- 工作
    MGR DOUBLE, -- 直属领导编号
    HIREDATE DATE,  -- 入职时间
    SAL DOUBLE, -- 工资
    COMM DOUBLE, -- 奖金
    DEPTNO INT, -- 部门号
    FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));
  INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980/12/17	800	null	20
7499	ALLEN	SALESMAN	7698	1981/2/20	1600	300	30
7521	WARD	SALESMAN	7698	1981/2/22	1250	500	30
7566	JONES	MANAGER	7839	1981/4/2	2975	null	20
7654	MARTIN	SALESMAN	7698	1981/9/28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981/5/1	2850	null	30
7782	CLARK	MANAGER	7839	1981/6/9	2450	null	10
7788	SCOTT	ANALYST	7566	1987/7/13	3000	null	20
7839	KING	PRESIDENT	null	1981/11/17	5000	null	10
7844	TURNER	SALESMAN	7698	1981/9/8	1500	0	30
7876	ADAMS	CLERK	7788	1987/7/13	1100	null	20
7900	JAMES	CLERK	7698	1981/12/3	950	null	30
7902	FORD	ANALYST	7566	1981/12/3	3000	null	20
7934	MILLER	CLERK	7782	1982/1/23	1300	null	10

① 运营童鞋想要查看工资最高的5名员工

mysql> SELECT e.ename,e.sal FROM  emp e ORDER BY e.sal DESC LIMIT 5;
+-------+------+
| ename | sal  |
+-------+------+
| KING  | 5000 |
| FORD  | 3000 |
| SCOTT | 3000 |
| JONES | 2975 |
| BLAKE | 2850 |
+-------+------+
5 rows in set (0.00 sec)

② 运营童鞋想要查看工资排再[3-5]名的员工


mysql> SELECT e.ename,e.sal FROM  emp e ORDER BY e.sal DESC LIMIT 2,3;
+-------+------+
| ename | sal  |
+-------+------+
| SCOTT | 3000 |
| JONES | 2975 |
| BLAKE | 2850 |
+-------+------+
3 rows in set (0.00 sec)

③ 运营童鞋想要分页查看员工信息，一页展示5条记录

limit 后条件直接套用公式即可 limit (page-1)*size,size

-- 第一页 (page-1)*size,size ==> (1-1)*5,5
mysql> SELECT
    ->   e.ename,
    ->   e.job,
    ->   e.sal,
    ->   d.dname
    -> FROM
    ->   emp e
    ->   LEFT JOIN dept d
    ->     ON e.deptno = d.deptno
    -> LIMIT 0, 5 ;
+--------+-----------+------+------------+
| ename  | job       | sal  | dname      |
+--------+-----------+------+------------+
| CLARK  | MANAGER   | 2450 | ACCOUNTING |
| KING   | PRESIDENT | 5000 | ACCOUNTING |
| MILLER | CLERK     | 1300 | ACCOUNTING |
| SMITH  | CLERK     |  800 | RESEARCH   |
| JONES  | MANAGER   | 2975 | RESEARCH   |
+--------+-----------+------+------------+
5 rows in set (0.00 sec)
-- 第二页 (page-1)*size,size ==> (2-1)*5,5
mysql> SELECT
    ->   e.ename,
    ->   e.job,
    ->   e.sal,
    ->   d.dname
    -> FROM
    ->   emp e
    ->   LEFT JOIN dept d
    ->     ON e.deptno = d.deptno
    -> LIMIT 5, 5 ;
+-------+----------+------+----------+
| ename | job      | sal  | dname    |
+-------+----------+------+----------+
| SCOTT | ANALYST  | 3000 | RESEARCH |
| ADAMS | CLERK    | 1100 | RESEARCH |
| FORD  | ANALYST  | 3000 | RESEARCH |
| ALLEN | SALESMAN | 1600 | SALES    |
| WARD  | SALESMAN | 1250 | SALES    |
+-------+----------+------+----------+
5 rows in set (0.00 sec)
-- 第三页 (page-1)*size,size ==> (3-1)*5,5
SELECT 
  e.ename,
  e.job,
  e.sal,
  d.dname 
FROM
  emp e 
  LEFT JOIN dept d 
    ON e.deptno = d.deptno 
LIMIT 10, 5 ;