您现在的位置： 主页 聚焦 > 正文

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

2023-05-04 09:32:33    来源：博客园
目录一、前言二、基础查询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 语法
-- ① 查询字段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");

数据结构如下


(资料图)

iddevice_idgenderageuniversityprovince
12138male21北京大学BeiJing
23214maleNone复旦大学Shanghai
36543female20北京大学BeiJing
42315female23浙江大学ZheJiang
55432male25山东大学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);

数据结构如下

iddevice_idgenderageuniversitygpa
12138male21北京大学3.4
23214male复旦大学4
36543female20北京大学3.2
42315female23浙江大学3.6
55432male25山东大学3.8
62131male28北京师范大学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);
iddevice_idgenderageuniversitygpa
12138male21北京大学3.4
23214male23复旦大学4
36543female20北京大学3.2
42315female23浙江大学3.6
55432male25山东大学3.8
62131male28北京师范大学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"  ) ;
idproductidproductnamesalepriceauthor
110001公众号XiezhrSpace【Oralce从入门到放弃】100xiezhr001
210002公众号XiezhrSpace【Linux核心命令快速上手】300xiezhr
310003公众号XiezhrSpace【你写注释她帮你写代码】80xiezhr
410004公众号XiezhrSpace【Java从入门到精通】150xiezhr001
510005公众号XiezhrSpace【gitee不能用了】55xiezhr
610006公众号XiezhrSpace【如何快速搭建个人博客】120xiezhr
710007公众号XiezhrSpace【MySQL从入门到入土】320xiezhr
810008公众号XiezhrSpace【idea从入门到上瘾】500xiezhr
-- 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);
iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt
12138male21北京大学3.47212
23214male复旦大学4.015525
36543female20北京大学3.212330
42315female23浙江大学3.6512
55432male25山东大学3.8201570
62131male28山东大学3.315713
74321female26复旦大学3.69652

第一行表示: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 t2where t1 和 t2 的连接条件
6.4.2 内连接之等值连接sql99标准

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

select 查询列表from 表1 t1inner join                  表2 t2on t1 和 t2 的等值连接条件where 筛选条件
6.4.3 内连接之非等值连接
select 查询列表from 表1 t1inner join                  表2 t2on t1 和 t2 的非等值连接条件where 筛选条件
6.4.3 内连接之自连接

一张表看作两张表

select 查询列表from 表1 t1inner join                  表1 t2on t1 和 t2 的关联条件where 筛选条件
6.4.4 外连接之右连接

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

select 查询列表from 表1 t1right outer join             -- outer 可以省去                表2 t2on t1 和 t2 的非等值连接条件where 筛选条件
6.4.5 外连接之左连接
select 查询列表from 表1 t1left outer  join           -- outer 可以省去        表2 t2on 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");
DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
-- 员工信息表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);
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK79021980/12/17800null20
7499ALLENSALESMAN76981981/2/20160030030
7521WARDSALESMAN76981981/2/22125050030
7566JONESMANAGER78391981/4/22975null20
7654MARTINSALESMAN76981981/9/281250140030
7698BLAKEMANAGER78391981/5/12850null30
7782CLARKMANAGER78391981/6/92450null10
7788SCOTTANALYST75661987/7/133000null20
7839KINGPRESIDENTnull1981/11/175000null10
7844TURNERSALESMAN76981981/9/81500030
7876ADAMSCLERK77881987/7/131100null20
7900JAMESCLERK76981981/12/3950null30
7902FORDANALYST75661981/12/33000null20
7934MILLERCLERK77821982/1/231300null10
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);
GRADELOSALHISAL
17001200
212011400
314012000
420013000
530019999

①运营童鞋想要查询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");
DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
-- 员工信息表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);
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK79021980/12/17800null20
7499ALLENSALESMAN76981981/2/20160030030
7521WARDSALESMAN76981981/2/22125050030
7566JONESMANAGER78391981/4/22975null20
7654MARTINSALESMAN76981981/9/281250140030
7698BLAKEMANAGER78391981/5/12850null30
7782CLARKMANAGER78391981/6/92450null10
7788SCOTTANALYST75661987/7/133000null20
7839KINGPRESIDENTnull1981/11/175000null10
7844TURNERSALESMAN76981981/9/81500030
7876ADAMSCLERK77881987/7/131100null20
7900JAMESCLERK76981981/12/3950null30
7902FORDANALYST75661981/12/33000null20
7934MILLERCLERK77821982/1/231300null10
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);
GRADELOSALHISAL
17001200
212011400
314012000
420013000
530019999

① 运营童鞋想要查询每个员工的部门名称 (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可以包含重复项

查询语句1union 【all】查询语句2union 【all】...
8.3 特点要查询的结果来自于多个表且多个表没有直接的连接关系，但查询的信息一致时，可以使用联合查询要求多条查询语句的查询列数是一致的要求多条查询语句的查询的每一列的类型和顺序一致8.4 实践操作

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

DROP TABLE IF EXISTS student;CREATE TABLE student(stuNoVARCHAR(5) PRIMARY KEY, -- 学生学号stuName VARCHAR(32) NOT NULL, -- 学生姓名gender VARCHAR(1) NOT NULLDEFAULT "男", -- 学生性别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,"厦门大学");
stuNostuNamegenderageschool
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 NULLDEFAULT "男", -- 教师性别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,"北京师范大学");
tNotNamegenderageschool
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);
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK79021980/12/17800null20
7499ALLENSALESMAN76981981/2/20160030030
7521WARDSALESMAN76981981/2/22125050030
7566JONESMANAGER78391981/4/22975null20
7654MARTINSALESMAN76981981/9/281250140030
7698BLAKEMANAGER78391981/5/12850null30
7782CLARKMANAGER78391981/6/92450null10
7788SCOTTANALYST75661987/7/133000null20
7839KINGPRESIDENTnull1981/11/175000null10
7844TURNERSALESMAN76981981/9/81500030
7876ADAMSCLERK77881987/7/131100null20
7900JAMESCLERK76981981/12/3950null30
7902FORDANALYST75661981/12/33000null20
7934MILLERCLERK77821982/1/231300null10

① 运营童鞋想要查看工资最高的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,5mysql> 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,5mysql> 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,5SELECT   e.ename,  e.job,  e.sal,  d.dname FROM  emp e   LEFT JOIN dept d     ON e.deptno = d.deptno LIMIT 10, 5 ;

关键词：

上一篇：环球资讯：曹亮_对于曹亮简单介绍

下一篇：最后一页

聚焦

X 关闭

热点
品牌

X 关闭

线上

微视频｜今天，一起来“追星”！

线下

  • 进博会呈现疫苗生态创新 全链路升级 数字化技术加持

  • 中国跳高名将朱建华亮相进博会 期待中国跳高再破纪录