首页 > 数据库 >【必知必会的MySQL知识】⑤DQL语言

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

时间：2023-05-04 09:11:47浏览次数：73  
标签：INSERT 20 必知 INTO -- VALUES MySQL DQL age

目录

一、前言

上一节中我们说了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 ;

标签：INSERT,20,必知,INTO,--,VALUES,MySQL,DQL,age
From： https://www.cnblogs.com/xiezhr/p/17343326.html

相关文章

  • 通过Python进行MySQL表信息统计
    在上一篇文章中简单的介绍了使用python-mysql-replication来解析MySQLbinlog来完成实时统计的业务，当然，在现实的业务中不可能用的那么简单的。啰哩八说今天的目的不是介绍真实的业务场景如何使用python-mysql-replication，而是推出一枚<MySQL表信息统计>小工具(笔者通过......
  • mysql忘记密码
    在忘记MySQL密码的情况下，可以通过--skip-grant-tables关闭服务器的认证，然后重置root的密码，具体操作步骤如下。步骤1)：关闭正在运行的MySQL服务。打开cmd进入MySQL的bin目录。步骤2)：输入mysqld--console--skip-grant-tables--shared-memory命令。–skip-gr......
  • 解决MySQL自动弹出命令行窗口
    自从装了MySQL之后，我的电脑就会在某些时刻弹出一个黑乎乎的命令行窗口，然后立马消失。一开始还以为是电脑出了什么故障，但一直没有出现其他什么问题，就是玩游戏时弹出来有点难受。有一次我眼睛看到了一闪而过的窗口有MySQL几个字，就确定了是MySQL的原因。于是上网找解决方法，找到了一......
  • mysql优化参数
    --隔离级别select*from`performance_schema`.variables_by_threadwherevariable_name='transaction_isolation';--全局级别和回话级参数作用于setsessionbinlog_rows_query_log_events=on;setGLOBALbinlog_rows_query_log_events=on;--缓冲区大小setgloba......
  • 举一个数据库死锁的例子，MySQL 怎么解决死锁？
    转载：https://juejin.cn/post/7072000882128191518分析&回答在数据库中有两种基本的锁类型：排它锁（ExclusiveLocks，即X锁）和共享锁（ShareLocks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基......
  • mysql -- 事件
    MySQL的EVENTS是用来处理计划任务的。MySQL使用称为事件调度线程的特殊线程来执行所有预定事件。默认情况下，事件调度线程是未启用（版本低于8.0.3）的状态，如要启用它，可以执行以下命令：>SETGLOBALevent_scheduler=ON;>showvariableslike'%event_scheduler%';+-----------......
  • django.core.exceptions.ImproperlyConfigured: mysqlclient 1.4.3 or newer is requi
     1、在项目中__init__.py中这个报错原因，python3.5以上版本不支持这种方式frompymysqlimportinstall_as_MySQLdbinstall_as_MySQLdb()解决：importpymysqlpymysql.version_info=(1,4,3,"final",0)#指定了pymysql的版本：1.4.3,按照你版本修改pymysql.install_as_MySQLdb()......
  • idea创建SpringBoot项目报错For artifact {mysql:mysql-connector-java:null:jar}: Th
    Forartifact{mysql:mysql-connector-java:null:jar}:Theversioncannotbeempty.报错如图：pom.xml文件如图：添加版本号：就好了......
  • PHP: mysql 5.7 and php 5.6 导入记事本编号查询不了和中文乱码问题
    --https://dev.mysql.com/doc/refman/8.0/en/charset-database.htmlshowvariableslike"character_set_%";CREATEDATABASE`geovindu`CHARACTERSETutf8COLLATEutf8_general_ci;--mysql官方说明文档才知道原来MySQL8.0已经已经把默认字符集升级成ut8mb4了,和5.0有区......
  • MySQL数据库设计规范
    一、背景规范及目的MySQL数据库与oracle、sqlserver等数据库相比，有其内核上的优势与劣势。我们在使用MySQL数据库的时候需要遵循一定规范，扬长避短。本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99