IT评测·应用市场-qidao123.com

标题: 【必知必会的MySQL知识】⑤DQL语言 [打印本页]

---

作者: 雁过留声    时间: 2023-5-4 11:31
标题: 【必知必会的MySQL知识】⑤DQL语言
目录

• 一、前言
  
• 二、基础查询
  
  
  
  • 2.1 语法
    
  • 2.2 实践操作
    
  
  
• 三、条件查询
  
  
  
  • 3.1 语法
    
  • 3.2 where 语句操作符
    
  • 3.3 实践操作
    
  
  
• 四、排序查询
  
  
  
  • 4.1 语法格式
    
  • 4.2 实践操作
    
  
  
• 五、分组查询
  
  
  
  • 5.1 语法
    
  • 5.2  聚集函数
    
    
    
    • 5.2.1 聚集函数简单使用
      
    
    
  • 5.3 实践操作
    
  • 5.4 规定与小结
    
  
  
• 六、连接查询（多表查询）
  
  
  
  • 6.1 简介
    
  • 6.2 笛卡儿积
    
  • 6.3 连接分类
    
  • 6.4  语法格式
    
    
    
    • 6.4.1 内连接之等值连接sql92标准
      
    • 6.4.2  内连接之等值连接sql99标准
      
    • 6.4.3  内连接之非等值连接
      
    • 6.4.3  内连接之自连接
      
    • 6.4.4  外连接之右连接
      
    • 6.4.5  外连接之左连接
      
    
    
  • 6.5 实践操作
    
  
  
• 七、子查询
  
  
  
  • 7.1 简介
    
  • 7.2 出现的位置
    
  • 7.3 实践操作
    
  
  
• 八、组合查询
  
  
  
  • 8.1 简介
    
  • 8.2 语法
    
  • 8.3 特点
    
  • 8.4 实践操作
    
  
  
• 九、分页查询
  
  
  
  • 9.1 简介
    
  • 9.2 语法
    
    
    
    • 9.2.1 limit 语法
      
    • 9.2.2 分页查询语法
      
    
    
  • 9.3 实践操作
    
  
  

一、前言

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

2.1 语法

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

复制代码

2.2 实践操作

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

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

复制代码

数据结构如下
iddevice_idgenderageuniversityprovince12138male21北京大学BeiJing23214maleNone复旦大学Shanghai36543female20北京大学BeiJing42315female23浙江大学ZheJiang55432male25山东大学Shandong① 运营童鞋想要查看用户信息表中所有的数据【查询字段】

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

复制代码

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

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

复制代码

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

1. mysql> SELECT 2568*234/23+234 as result;
2. +------------+
3. | result     |
4. +------------+
5. | 26360.6087 |
6. +------------+
7. 1 row in set (0.00 sec)

复制代码

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

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

1. mysql> SELECT NOW() AS currdate;
2. +---------------------+
3. | currdate            |
4. +---------------------+
5. | 2022-09-15 23:42:29 |
6. +---------------------+
7. 1 row in set (0.00 sec)

复制代码

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

1. mysql> SELECT '张三' AS user_name, '男' AS sex, 18 AS age, 150 AS wight;
2. +-----------+-----+-----+-------+
3. | user_name | sex | age | wight |
4. +-----------+-----+-----+-------+
5. | 张三      | 男  |  18 |   150 |
6. +-----------+-----+-----+-------+
7. 1 row in set (0.00 sec)

复制代码

三、条件查询

3.1 语法

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表示某个字段为nullis not null表示某个字段不为空3.3 实践操作

数据准备

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

复制代码

数据结构如下
iddevice_idgenderageuniversitygpa12138male21北京大学3.423214male复旦大学436543female20北京大学3.242315female23浙江大学3.655432male25山东大学3.862131male28北京师范大学3.3① 运营童鞋想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。

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

复制代码

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

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

复制代码

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

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

复制代码

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

1. -- ① 第一种写法 使用<>
2. mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE t.`university` <> '复旦大学';
3. +-----------+--------+------+------------+
4. | device_id | gender | age  | university |
5. +-----------+--------+------+------------+
6. |      2138 | male   |   21 | 北京大学   |
7. |      6543 | female |   20 | 北京大学   |
8. |      2315 | female |   23 | 浙江大学   |
9. |      5432 | male   |   25 | 山东大学   |
10. +-----------+--------+------+------------+
11. 4 rows in set (0.00 sec)
12. -- ② 第二种写法 使用!=
13. mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE t.`university` != '复旦大学';
14. +-----------+--------+------+------------+
15. | device_id | gender | age  | university |
16. +-----------+--------+------+------------+
17. |      2138 | male   |   21 | 北京大学   |
18. |      6543 | female |   20 | 北京大学   |
19. |      2315 | female |   23 | 浙江大学   |
20. |      5432 | male   |   25 | 山东大学   |
21. +-----------+--------+------+------------+
22. 4 rows in set (0.00 sec)

复制代码

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

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

复制代码

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

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

复制代码

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

1. 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);
2. +-----------+--------+------+------------+------+
3. | device_id | gender | age  | university | gpa  |
4. +-----------+--------+------+------------+------+
5. |      2138 | male   |   21 | 北京大学   |  3.4 |
6. |      3214 | male   | NULL | 复旦大学   |    4 |
7. |      6543 | female |   20 | 北京大学   |  3.2 |
8. |      5432 | male   |   25 | 山东大学   |  3.8 |
9. +-----------+--------+------+------------+------+
10. 4 rows in set (0.00 sec)

复制代码

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

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

复制代码

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

1. 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` = '复旦大学');
2. +-----------+--------+------+------------+------+
3. | device_id | gender | age  | university | gpa  |
4. +-----------+--------+------+------------+------+
5. |      3214 | male   | NULL | 复旦大学   |    4 |
6. |      5432 | male   |   25 | 山东大学   |  3.8 |
7. +-----------+--------+------+------------+------+
8. 2 rows in set (0.00 sec)

复制代码

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

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

复制代码

四、排序查询

4.1 语法格式

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

1. SELECT
2.   查询列表
3. FROM
4.   表
5. 【WHERE 筛选条件】
6. ORDER BY 排序列表 【asc | desc】 ;

复制代码

4.2 实践操作

数据准备

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

复制代码

iddevice_idgenderageuniversitygpa12138male21北京大学3.423214male23复旦大学436543female20北京大学3.242315female23浙江大学3.655432male25山东大学3.862131male28北京师范大学3.3①运营童鞋想要取出用户信息表中的用户年龄，请取出相应数据，并按照年龄升序排序。

1. mysql> SELECT t.`device_id`,t.`age`FROM user_profile t ORDER BY t.age ASC;
2. +-----------+------+
3. | device_id | age  |
4. +-----------+------+
5. |      6543 |   20 |
6. |      2138 |   21 |
7. |      3214 |   23 |
8. |      2315 |   23 |
9. |      5432 |   25 |
10. |      2131 |   28 |
11. +-----------+------+
12. 6 rows in set (0.00 sec)

复制代码

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

1. mysql> SELECT device_id,gpa,age FROM user_profile ORDER BY gpa ASC,age ASC;
2. +-----------+------+------+
3. | device_id | gpa  | age  |
4. +-----------+------+------+
5. |      6543 |  3.2 |   20 |
6. |      2131 |  3.3 |   28 |
7. |      2138 |  3.4 |   21 |
8. |      2315 |  3.6 |   23 |
9. |      5432 |  3.8 |   25 |
10. |      3214 |    4 |   23 |
11. +-----------+------+------+
12. 6 rows in set (0.00 sec)

复制代码

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

1. mysql> SELECT device_id,gpa,age FROM user_profile ORDER BY gpa DESC,age DESC;
2. +-----------+------+------+
3. | device_id | gpa  | age  |
4. +-----------+------+------+
5. |      3214 |    4 |   23 |
6. |      5432 |  3.8 |   25 |
7. |      2315 |  3.6 |   23 |
8. |      2138 |  3.4 |   21 |
9. |      2131 |  3.3 |   28 |
10. |      6543 |  3.2 |   20 |
11. +-----------+------+------+
12. 6 rows in set (0.00 sec)

复制代码

五、分组查询

5.1 语法

1. SELECT
2.   查询列表
3. FROM
4.   表
5. 【where 筛选条件】
6. GROUP BY 分组的字段
7. 【having 分组后的筛选】
8. 【order BY 排序的字段】 ;

复制代码

5.2  聚集函数

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

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

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

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

复制代码

idproductidproductnamesalepriceauthor110001公众号XiezhrSpace【Oralce从入门到放弃】100xiezhr001210002公众号XiezhrSpace【Linux核心命令快速上手】300xiezhr310003公众号XiezhrSpace【你写注释她帮你写代码】80xiezhr410004公众号XiezhrSpace【Java从入门到精通】150xiezhr001510005公众号XiezhrSpace【gitee不能用了】55xiezhr610006公众号XiezhrSpace【如何快速搭建个人博客】120xiezhr710007公众号XiezhrSpace【MySQL从入门到入土】320xiezhr810008公众号XiezhrSpace【idea从入门到上瘾】500xiezhr

1. -- 1、计算所有产品单价之和
2. mysql> select sum(saleprice) from product;
3. +----------------+
4. | sum(saleprice) |
5. +----------------+
6. |           1625 |
7. +----------------+
8. 1 row in set (0.00 sec)
10. -- 2、计算所有产品单价平均值
11. mysql> select avg(saleprice) from product;
12. +----------------+
13. | avg(saleprice) |
14. +----------------+
15. |       203.1250 |
16. +----------------+
17. 1 row in set (0.00 sec)
18. -- 3、获取所有产品中最大单价
19. mysql> select max(saleprice) from product;
20. +----------------+
21. | max(saleprice) |
22. +----------------+
23. |            500 |
24. +----------------+
25. 1 row in set (0.00 sec)
27. -- 4、获取所有产品中最小单价
28. mysql> select min(saleprice) from product;
29. +----------------+
30. | min(saleprice) |
31. +----------------+
32. |             55 |
33. +----------------+
34. 1 row in set (0.00 sec)
35. -- 5、获取一共有多少产品
36. mysql> select count(*) from product;
37. +----------+
38. | count(*) |
39. +----------+
40. |        8 |
41. +----------+
42. 1 row in set (0.00 sec)
43. -- 或者
44. mysql> select count(1) from product;
45. +----------+
46. | count(1) |
47. +----------+
48. |        8 |
49. +----------+
50. 1 row in set (0.00 sec)

复制代码

5.3 实践操作

数据准备

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

复制代码

iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt12138male21北京大学3.4721223214male复旦大学4.01552536543female20北京大学3.21233042315female23浙江大学3.651255432male25山东大学3.820157062131male28山东大学3.31571374321female26复旦大学3.69652

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

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

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

复制代码

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

1. mysql> SELECT
2.     ->   t.university,
3.     ->   AVG(question_cnt) AS avg_question_cnt
4.     -> FROM
5.     ->   user_profile t
6.     -> GROUP BY
7.     ->   t.university
8.     -> ORDER BY
9.     ->   avg_question_cnt;
10. +------------+------------------+
11. | university | avg_question_cnt |
12. +------------+------------------+
13. | 浙江大学   |                1 |
14. | 北京大学   |              2.5 |
15. | 复旦大学   |              5.5 |
16. | 山东大学   |               11 |
17. +------------+------------------+
18. 4 rows in set (0.00 sec)

复制代码

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

1. mysql> SELECT
2.     ->   gender,
3.     ->   university,
4.     ->   COUNT(1) AS user_num,
5.     ->   AVG(active_days_within_30) AS avg_active_day,
6.     ->   AVG(question_cnt) avg_question_cnt
7.     -> FROM
8.     ->   user_profile
9.     -> GROUP BY gender,
10.     ->   university;
11. +--------+------------+----------+----------------+------------------+
12. | gender | university | user_num | avg_active_day | avg_question_cnt |
13. +--------+------------+----------+----------------+------------------+
14. | female | 北京大学   |        1 |        12.0000 |                3 |
15. | female | 浙江大学   |        1 |         5.0000 |                1 |
16. | male   | 北京大学   |        1 |         7.0000 |                2 |
17. | male   | 复旦大学   |        2 |        12.0000 |              5.5 |
18. | male   | 山东大学   |        2 |        17.5000 |               11 |
19. +--------+------------+----------+----------------+------------------+
20. 5 rows in set (0.00 sec)
21. --说明：
22. --第一行表示：北京大学的男性用户个数为1，平均活跃天数为7天，平均发帖量为2
23. -- ...
24. -- 最后一行表示：山东大学的男性用户个数为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子句中

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

复制代码

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

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

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

复制代码

6.4.3  内连接之非等值连接

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

复制代码

6.4.3  内连接之自连接

一张表看作两张表

1. select 查询列表
2. from 表1 t1
3. inner join                  
4. 表1 t2
5. on t1 和 t2 的关联条件
6. where 筛选条件

复制代码

6.4.4  外连接之右连接

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

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

复制代码

6.4.5  外连接之左连接

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

复制代码

6.5 实践操作

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

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

复制代码

DEPTNODNAMELOC10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON

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

复制代码

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO7369SMITHCLERK79021980/12/17800null207499ALLENSALESMAN76981981/2/201600300307521WARDSALESMAN76981981/2/221250500307566JONESMANAGER78391981/4/22975null207654MARTINSALESMAN76981981/9/2812501400307698BLAKEMANAGER78391981/5/12850null307782CLARKMANAGER78391981/6/92450null107788SCOTTANALYST75661987/7/133000null207839KINGPRESIDENTnull1981/11/175000null107844TURNERSALESMAN76981981/9/815000307876ADAMSCLERK77881987/7/131100null207900JAMESCLERK76981981/12/3950null307902FORDANALYST75661981/12/33000null207934MILLERCLERK77821982/1/231300null10

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

复制代码

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

1. --1.sql92标准语法
2. mysql> SELECT
3.     ->   e.ename,
4.     ->   e.deptno,
5.     ->   d.dname
6.     -> FROM
7.     ->   emp e,
8.     ->   dept d
9.     -> WHERE e.deptno = d.deptno
10.     -> AND e.ename='SMITH';
11. +-------+--------+----------+
12. | ename | deptno | dname    |
13. +-------+--------+----------+
14. | SMITH |     20 | RESEARCH |
15. +-------+--------+----------+
16. 1 row in set (0.00 sec)
18. --2.sql99标准语法
19. mysql> SELECT
20.     ->   e.ename,
21.     ->   e.deptno,
22.     ->   d.dname
23.     -> FROM
24.     ->   emp e
25.     ->   JOIN
26.     ->   dept d
27.     -> ON e.deptno = d.deptno
28.     -> WHERE e.ename='SMITH';
29. +-------+--------+----------+
30. | ename | deptno | dname    |
31. +-------+--------+----------+
32. | SMITH |     20 | RESEARCH |
33. +-------+--------+----------+
34. 1 row in set (0.00 sec)

复制代码

注 以上例子中e.deptno = d.deptno 为等值关联，所以上面例子时等值关联查询
② 运营童鞋想要查看每个员工的薪资等级，要求显示员工名、薪资、薪资等级

1. mysql> SELECT
2.     ->   e.ename,
3.     ->   e.sal,
4.     ->   s.grade
5.     -> FROM
6.     ->   emp e
7.     -> JOIN
8.     ->   salgrade s
9.     -> ON e.sal BETWEEN s.losal AND s.hisal;
10. +--------+------+-------+
11. | ename  | sal  | grade |
12. +--------+------+-------+
13. | SMITH  |  800 |     1 |
14. | ALLEN  | 1600 |     3 |
15. | WARD   | 1250 |     2 |
16. | JONES  | 2975 |     4 |
17. | MARTIN | 1250 |     2 |
18. | BLAKE  | 2850 |     4 |
19. | CLARK  | 2450 |     4 |
20. | SCOTT  | 3000 |     4 |
21. | KING   | 5000 |     5 |
22. | TURNER | 1500 |     3 |
23. | ADAMS  | 1100 |     1 |
24. | JAMES  |  950 |     1 |
25. | FORD   | 3000 |     4 |
26. | MILLER | 1300 |     2 |
27. +--------+------+-------+
28. 14 rows in set (0.00 sec)

复制代码

注 以上例子中e.sal BETWEEN s.losal AND s.hisal 为非等值关联，所以上面例子时非等值关联查询
③ 查询员工SMITH 和员工SCOTT  上级领导

1. mysql> SELECT
2.     ->   e1.ename AS '员工名',
3.     ->   e2.ename AS '领导名'
4.     -> FROM
5.     ->   emp e1
6.     -> JOIN
7.     ->   emp e2
8.     -> ON e1.mgr=e2.empno
9.     -> WHERE e1.ename IN('SMITH','SCOTT');
10. +--------+--------+
11. | 员工名 | 领导名 |
12. +--------+--------+
13. | SMITH  | FORD   |
14. | SCOTT  | JONES  |
15. +--------+--------+
16. 2 rows in set (0.01 sec)

复制代码

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

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

1. -- 部门编号为40的OPERATIONS 部门没有员工也要显示出来
2. mysql> SELECT d.deptno,d.dname,e.ename
3.     -> FROM dept d
4.     -> LEFT JOIN
5.     -> emp e
6.     -> ON d.deptno = e.deptno;
7. +--------+------------+--------+
8. | deptno | dname      | ename  |
9. +--------+------------+--------+
10. |     10 | ACCOUNTING | CLARK  |
11. |     10 | ACCOUNTING | KING   |
12. |     10 | ACCOUNTING | MILLER |
13. |     20 | RESEARCH   | SMITH  |
14. |     20 | RESEARCH   | JONES  |
15. |     20 | RESEARCH   | SCOTT  |
16. |     20 | RESEARCH   | ADAMS  |
17. |     20 | RESEARCH   | FORD   |
18. |     30 | SALES      | ALLEN  |
19. |     30 | SALES      | WARD   |
20. |     30 | SALES      | MARTIN |
21. |     30 | SALES      | BLAKE  |
22. |     30 | SALES      | TURNER |
23. |     30 | SALES      | JAMES  |
24. |     40 | OPERATIONS | NULL   |
25. +--------+------------+--------+
26. 15 rows in set (0.00 sec)

复制代码

2、通过右连接实现

1. mysql> SELECT d.deptno,d.dname,e.ename
2.     -> FROM emp e
3.     -> RIGHT JOIN
4.     -> dept d
5.     -> ON d.deptno = e.deptno;
6. +--------+------------+--------+
7. | deptno | dname      | ename  |
8. +--------+------------+--------+
9. |     10 | ACCOUNTING | CLARK  |
10. |     10 | ACCOUNTING | KING   |
11. |     10 | ACCOUNTING | MILLER |
12. |     20 | RESEARCH   | SMITH  |
13. |     20 | RESEARCH   | JONES  |
14. |     20 | RESEARCH   | SCOTT  |
15. |     20 | RESEARCH   | ADAMS  |
16. |     20 | RESEARCH   | FORD   |
17. |     30 | SALES      | ALLEN  |
18. |     30 | SALES      | WARD   |
19. |     30 | SALES      | MARTIN |
20. |     30 | SALES      | BLAKE  |
21. |     30 | SALES      | TURNER |
22. |     30 | SALES      | JAMES  |
23. |     40 | OPERATIONS | NULL   |
24. +--------+------------+--------+
25. 15 rows in set (0.00 sec)

复制代码

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

1. mysql> SELECT e.ename,e.sal, d.dname,s.grade
2.     -> FROM emp e
3.     -> JOIN dept d
4.     -> ON e.deptno = d.deptno
5.     -> JOIN salgrade s
6.     -> ON e.sal BETWEEN s.losal AND s.hisal
7.     -> WHERE e.ename = 'SMITH';
8. +-------+------+----------+-------+
9. | ename | sal  | dname    | grade |
10. +-------+------+----------+-------+
11. | SMITH |  800 | RESEARCH |     1 |
12. +-------+------+----------+-------+
13. 1 row in set (0.00 sec)

复制代码

七、子查询

7.1 简介

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

7.2 出现的位置

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

7.3 实践操作

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

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

复制代码

DEPTNODNAMELOC10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON

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

复制代码

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO7369SMITHCLERK79021980/12/17800null207499ALLENSALESMAN76981981/2/201600300307521WARDSALESMAN76981981/2/221250500307566JONESMANAGER78391981/4/22975null207654MARTINSALESMAN76981981/9/2812501400307698BLAKEMANAGER78391981/5/12850null307782CLARKMANAGER78391981/6/92450null107788SCOTTANALYST75661987/7/133000null207839KINGPRESIDENTnull1981/11/175000null107844TURNERSALESMAN76981981/9/815000307876ADAMSCLERK77881987/7/131100null207900JAMESCLERK76981981/12/3950null307902FORDANALYST75661981/12/33000null207934MILLERCLERK77821982/1/231300null10

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

复制代码

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

1. mysql> SELECT e.ename,(SELECT dname FROM dept d WHERE d.deptno = e.deptno) AS dpatname FROM emp e;
2. +--------+------------+
3. | ename  | dpatname   |
4. +--------+------------+
5. | SMITH  | RESEARCH   |
6. | ALLEN  | SALES      |
7. | WARD   | SALES      |
8. | JONES  | RESEARCH   |
9. | MARTIN | SALES      |
10. | BLAKE  | SALES      |
11. | CLARK  | ACCOUNTING |
12. | SCOTT  | RESEARCH   |
13. | KING   | ACCOUNTING |
14. | TURNER | SALES      |
15. | ADAMS  | RESEARCH   |
16. | JAMES  | SALES      |
17. | FORD   | RESEARCH   |
18. | MILLER | ACCOUNTING |
19. +--------+------------+
20. 14 rows in set (0.00 sec)

复制代码

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

1. mysql> SELECT
2.     ->   t.job,
3.     ->   t.avgsal,
4.     ->   s.grade
5.     -> FROM
6.     ->   (SELECT
7.     ->     e.job,
8.     ->     AVG(e.sal) AS avgsal
9.     ->   FROM
10.     ->     emp e
11.     ->   GROUP BY e.job) t
12.     ->   JOIN salgrade s
13.     ->     ON t.avgsal BETWEEN s.losal
14.     ->     AND s.hisal ;
15. +-----------+--------------------+-------+
16. | job       | avgsal             | grade |
17. +-----------+--------------------+-------+
18. | ANALYST   |               3000 |     4 |
19. | CLERK     |             1037.5 |     1 |
20. | MANAGER   | 2758.3333333333335 |     4 |
21. | PRESIDENT |               5000 |     5 |
22. | SALESMAN  |               1400 |     2 |
23. +-----------+--------------------+-------+
24. 5 rows in set (0.00 sec)

复制代码

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

1. mysql>    SELECT e.ename,e.sal FROM emp e WHERE sal >(SELECT MIN(sal) FROM emp );
2. +--------+------+
3. | ename  | sal  |
4. +--------+------+
5. | ALLEN  | 1600 |
6. | WARD   | 1250 |
7. | JONES  | 2975 |
8. | MARTIN | 1250 |
9. | BLAKE  | 2850 |
10. | CLARK  | 2450 |
11. | SCOTT  | 3000 |
12. | KING   | 5000 |
13. | TURNER | 1500 |
14. | ADAMS  | 1100 |
15. | JAMES  |  950 |
16. | FORD   | 3000 |
17. | MILLER | 1300 |
18. +--------+------+
19. 13 rows in set (0.00 sec)

复制代码

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

1. -- 1.查询有员工的部门
2. mysql>    SELECT d.deptno,d.dname FROM dept d WHERE EXISTS (SELECT 1 FROM emp e WHERE d.deptno = e.deptno);
3. +--------+------------+
4. | deptno | dname      |
5. +--------+------------+
6. |     10 | ACCOUNTING |
7. |     20 | RESEARCH   |
8. |     30 | SALES      |
9. +--------+------------+
10. 3 rows in set (0.00 sec)
11. -- 2.查询没有员工的部门
12. mysql> SELECT d.deptno,d.dname FROM dept d WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE d.deptno = e.deptno);
13. +--------+------------+
14. | deptno | dname      |
15. +--------+------------+
16. |     40 | OPERATIONS |
17. +--------+------------+
18. 1 row in set (0.00 sec)

复制代码

八、组合查询

8.1 简介

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

8.2 语法

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

1. 查询语句1
2. union 【all】
3. 查询语句2
4. union 【all】
5. ...

复制代码

8.3 特点

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

8.4 实践操作

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

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

复制代码

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

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

复制代码

tNotNamegenderageschool1李璇女35北京大学2张天宇男45厦门大学3刘晓女35天津大学4钟鸣男32山东大学5宋小白男35云南大学6梁小如女35北京师范大学① 运营童鞋想要查询北京大学的所有老师和学生信息

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

复制代码

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

1. mysql> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school = '山东大学'
2.     -> UNION ALL
3.     -> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school = '北京大学';
4. +---------+--------+-----+----------+
5. | stuname | gender | age | school   |
6. +---------+--------+-----+----------+
7. | 赵雷    | 男     |  34 | 山东大学 |
8. | 陈粒    | 女     |  18 | 山东大学 |
9. | 李志    | 男     |  22 | 北京大学 |
10. | 马頔    | 男     |  32 | 北京大学 |
11. +---------+--------+-----+----------+
12. 4 rows in set (0.00 sec)
13. -- 或者可以通过以下写法实现
14. mysql> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school IN ( '北京大学' ,'山东大学');
15. +---------+--------+-----+----------+
16. | stuname | gender | age | school   |
17. +---------+--------+-----+----------+
18. | 李志    | 男     |  22 | 北京大学 |
19. | 赵雷    | 男     |  34 | 山东大学 |
20. | 马頔    | 男     |  32 | 北京大学 |
21. | 陈粒    | 女     |  18 | 山东大学 |
22. +---------+--------+-----+----------+
23. 4 rows in set (0.00 sec)
25. -- 或者可以通过以下写法实现
26. mysql> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school = '北京大学' OR s.school = '山东大学';
27. +---------+--------+-----+----------+
28. | stuname | gender | age | school   |
29. +---------+--------+-----+----------+
30. | 李志    | 男     |  22 | 北京大学 |
31. | 赵雷    | 男     |  34 | 山东大学 |
32. | 马頔    | 男     |  32 | 北京大学 |
33. | 陈粒    | 女     |  18 | 山东大学 |
34. +---------+--------+-----+----------+
35. 4 rows in set (0.00 sec)

复制代码

九、分页查询

9.1 简介

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

9.2 语法

9.2.1 limit 语法

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

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

复制代码

9.2.2 分页查询语法

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

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

复制代码

9.3 实践操作

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

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

复制代码

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO7369SMITHCLERK79021980/12/17800null207499ALLENSALESMAN76981981/2/201600300307521WARDSALESMAN76981981/2/221250500307566JONESMANAGER78391981/4/22975null207654MARTINSALESMAN76981981/9/2812501400307698BLAKEMANAGER78391981/5/12850null307782CLARKMANAGER78391981/6/92450null107788SCOTTANALYST75661987/7/133000null207839KINGPRESIDENTnull1981/11/175000null107844TURNERSALESMAN76981981/9/815000307876ADAMSCLERK77881987/7/131100null207900JAMESCLERK76981981/12/3950null307902FORDANALYST75661981/12/33000null207934MILLERCLERK77821982/1/231300null10① 运营童鞋想要查看工资最高的5名员工

1. mysql> SELECT e.ename,e.sal FROM  emp e ORDER BY e.sal DESC LIMIT 5;
2. +-------+------+
3. | ename | sal  |
4. +-------+------+
5. | KING  | 5000 |
6. | FORD  | 3000 |
7. | SCOTT | 3000 |
8. | JONES | 2975 |
9. | BLAKE | 2850 |
10. +-------+------+
11. 5 rows in set (0.00 sec)

复制代码

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

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

复制代码

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

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

1. -- 第一页 (page-1)*size,size ==> (1-1)*5,5
2. mysql> SELECT
3.     ->   e.ename,
4.     ->   e.job,
5.     ->   e.sal,
6.     ->   d.dname
7.     -> FROM
8.     ->   emp e
9.     ->   LEFT JOIN dept d
10.     ->     ON e.deptno = d.deptno
11.     -> LIMIT 0, 5 ;
12. +--------+-----------+------+------------+
13. | ename  | job       | sal  | dname      |
14. +--------+-----------+------+------------+
15. | CLARK  | MANAGER   | 2450 | ACCOUNTING |
16. | KING   | PRESIDENT | 5000 | ACCOUNTING |
17. | MILLER | CLERK     | 1300 | ACCOUNTING |
18. | SMITH  | CLERK     |  800 | RESEARCH   |
19. | JONES  | MANAGER   | 2975 | RESEARCH   |
20. +--------+-----------+------+------------+
21. 5 rows in set (0.00 sec)
22. -- 第二页 (page-1)*size,size ==> (2-1)*5,5
23. mysql> SELECT
24.     ->   e.ename,
25.     ->   e.job,
26.     ->   e.sal,
27.     ->   d.dname
28.     -> FROM
29.     ->   emp e
30.     ->   LEFT JOIN dept d
31.     ->     ON e.deptno = d.deptno
32.     -> LIMIT 5, 5 ;
33. +-------+----------+------+----------+
34. | ename | job      | sal  | dname    |
35. +-------+----------+------+----------+
36. | SCOTT | ANALYST  | 3000 | RESEARCH |
37. | ADAMS | CLERK    | 1100 | RESEARCH |
38. | FORD  | ANALYST  | 3000 | RESEARCH |
39. | ALLEN | SALESMAN | 1600 | SALES    |
40. | WARD  | SALESMAN | 1250 | SALES    |
41. +-------+----------+------+----------+
42. 5 rows in set (0.00 sec)
43. -- 第三页 (page-1)*size,size ==> (3-1)*5,5
44. SELECT
45.   e.ename,
46.   e.job,
47.   e.sal,
48.   d.dname
49. FROM
50.   emp e
51.   LEFT JOIN dept d
52.     ON e.deptno = d.deptno
53. LIMIT 10, 5 ;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

---

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)

Powered by Discuz! X3.4