MySQL第八章

搬砖的小刘
199 阅读6分钟

第8章 select子句

8.1 select各个子句顺序

（1）select

（2）from：从哪些表中筛选

（3）inner|left|right ... on：关联多表查询时，去除笛卡尔积

（4）where：从表中筛选的条件

（5）group by：分组依据

（6）having：在分组统计结果中再次筛选（with rollup)

（7）order by：排序

（8）limit：分页

必须按照（1）-（8）的顺序编写子句。

8.2 演示

8.2.1 select 列表

SELECT语句是用于查看计算结果、或者查看从数据表中筛选出的数据的。

SELECT语句的基本语法：

SELECT 常量;
SELECT 表达式;
SELECT 函数;
SELECT * 
SELECT 字段列表
SELECT DISTICT 字段列表

mysql可以在查询结果中使用distinct关键字去重。

8.2.2 from子句

#1、from子句
SELECT * 
FROM t_employee; #表示从某个表中筛选数据

8.2.3 on子句

#2、on子句
/*
（1）on必须配合join使用
（2）on后面只写关联条件
所谓关联条件是两个表的关联字段的关系
（3）有n张表关联，就有n-1个关联条件
两张表关联，就有1个关联条件
三张表关联，就有2个关联条件
*/
SELECT *
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did;  #1个关联条件
​
#查询员工的编号，姓名，职位编号，职位名称，部门编号，部门名称
#需要t_employee员工表，t_department部门表，t_job职位表
SELECT eid,ename,t_job.job_id,t_job.job_name, `t_department`.`did`,`t_department`.`dname`
FROM t_employee INNER JOIN t_department INNER JOIN t_job
ON t_employee.did = t_department.did AND t_employee.job_id = t_job.job_id;
​

8.2.4 where子句

#3、where子句，在查询结果中筛选
#查询女员工的信息，以及女员工的部门信息
SELECT *
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did
WHERE gender = '女';

8.2.5 group by子句

#4、group by分组
#查询所有员工的平均薪资
SELECT AVG(salary) FROM t_employee;
​
#查询每一个部门的平均薪资
SELECT did,ROUND(AVG(salary),2 )
FROM t_employee
GROUP BY did;
​
#查询每一个部门的平均薪资，显示部门编号，部门的名称，该部门的平均薪资
SELECT t_department.did,dname,ROUND(AVG(salary),2 )
FROM t_department LEFT JOIN t_employee
ON t_department.did = t_employee.did
GROUP BY t_department.did;
​
#查询每一个部门的平均薪资，显示部门编号，部门的名称，该部门的平均薪资
#要求，如果没有员工的部门，平均薪资不显示null，显示0
SELECT t_department.did,dname,IFNULL(ROUND(AVG(salary),2),0)
FROM t_department LEFT JOIN t_employee
ON t_department.did = t_employee.did
GROUP BY t_department.did;
​
#查询每一个部门的女员工的平均薪资，显示部门编号，部门的名称，该部门的平均薪资
#要求，如果没有员工的部门，平均薪资不显示null，显示0
SELECT t_department.did,dname,IFNULL(ROUND(AVG(salary),2),0)
FROM t_department LEFT JOIN t_employee
ON t_department.did = t_employee.did
WHERE gender = '女'
GROUP BY t_department.did;

问题1：合计，WITH ROLLUP，加在group by后面

#问题1：合计，WITH ROLLUP，加在group by后面
#按照部门统计人数
SELECT did, COUNT(*) FROM t_employee GROUP BY did;
#按照部门统计人数，并合计总数
SELECT did, COUNT(*) FROM t_employee GROUP BY did WITH ROLLUP;
SELECT IFNULL(did,'合计'), COUNT(*) FROM t_employee GROUP BY did WITH ROLLUP;
SELECT IFNULL(did,'合计') AS "部门编号" , COUNT(*)  AS "人数" FROM t_employee GROUP BY did WITH ROLLUP;
​

问题2：是否可以按照多个字段分组统计

#问题2：是否可以按照多个字段分组统计
#按照不同的部门，不同的职位，分别统计男和女的员工人数
SELECT did, job_id, gender, COUNT(*)
FROM t_employee 
GROUP BY did, job_id, gender;

问题3：分组统计时，select后面字段列表的问题

#问题4：分组统计时，select后面字段列表的问题
SELECT eid,ename, did, COUNT(*) FROM t_employee; 
#eid,ename, did此时和count(*)，不应该出现在select后面
​
SELECT eid,ename, did, COUNT(*) FROM t_employee GROUP BY did; 
#eid,ename此时和count(*)，不应该出现在select后面
​
SELECT  did, COUNT(*) FROM t_employee GROUP BY did;
#分组统计时，select后面只写和分组统计有关的字段，其他无关字段不要出现，否则会引起歧义

8.2.6 having子句

#5、having
/*
having子句也写条件
where的条件是针对原表中的记录的筛选。where后面不能出现分组函数。
having子句是对统计结果（分组函数计算后）的筛选。having可以加分组函数。
*/
#查询每一个部门的女员工的平均薪资，显示部门编号，部门的名称，该部门的平均薪资
#要求，如果没有员工的部门，平均薪资不显示null，显示0
#最后只显示平均薪资高于12000的部门信息
SELECT t_department.did,dname,IFNULL(ROUND(AVG(salary),2),0)
FROM t_department LEFT JOIN t_employee
ON t_department.did = t_employee.did
WHERE gender = '女'
GROUP BY t_department.did
HAVING IFNULL(ROUND(AVG(salary),2),0) >12000;
​
​
#查询每一个部门的男和女员工的人数
SELECT did,gender,COUNT(*)
FROM t_employee
GROUP BY did,gender;
​
#查询每一个部门的男和女员工的人数，显示部门编号，部门的名称，性别，人数
SELECT t_department.did,dname,gender,COUNT(eid)
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
GROUP BY t_department.did,gender;
​
#查询每一个部门薪资超过10000的男和女员工的人数，显示部门编号，部门的名称，性别，人数
#只显示人数低于3人
SELECT t_department.did,dname,gender,COUNT(eid)
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE salary > 10000
GROUP BY t_department.did,gender
HAVING COUNT(eid) < 3;

8.2.7 order by子句

#6、排序 order by
/*
升序和降序，默认是升序
asc代表升序
desc 代表降序
*/
#查询员工信息，按照薪资从高到低
SELECT * FROM t_employee
ORDER BY salary DESC;
​
#查询每一个部门薪资超过10000的男和女员工的人数，显示部门编号，部门的名称，性别，人数
#只显示人数低于3人，按照人数升序排列
SELECT t_department.did,dname,gender,COUNT(eid)
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE salary > 10000
GROUP BY t_department.did,gender
HAVING COUNT(eid) < 3
ORDER BY COUNT(eid);
​
​
#查询员工的薪资，按照薪资从低到高，薪资相同按照员工编号从高到低
SELECT *
FROM t_employee
ORDER BY salary ASC , eid DESC;

8.2.8 limit子句

#演示limit子句
/*
limit子句是用于分页显示结果。
limit m,n
n：表示最多该页显示几行
m：表示从第几行开始取记录，第一个行的索引是0
m = (page-1)*n  page表示第几页
​
每页最多显示5条，n=5
第1页，page=1，m = (1-1)*5 = 0;  limit 0,5
第2页，page=2，m = (2-1)*5 = 5;  limit 5,5
第3页，page=3，m = (3-1)*5 = 10;  limit 10,5
*/
#查询员工表的数据，分页显示，每页显示5条记录
#第1页
SELECT * FROM t_employee LIMIT 0,5;
#第2页
SELECT * FROM t_employee LIMIT 5,5;
#第3页
SELECT * FROM t_employee LIMIT 10,5;
#第4页
SELECT * FROM t_employee LIMIT 15,5;
#第5页
SELECT * FROM t_employee LIMIT 20,5;
#第6页
SELECT * FROM t_employee LIMIT 25,5;
​
​
#查询所有的男员工信息，分页显示，每页显示3条，第2页
#limit m,n  n=3,page=2,m=(page-1)*n=3
SELECT *
FROM t_employee
WHERE gender ='男'
LIMIT 3,3
​
#查询每一个编号为偶数的部门，显示部门编号，名称，员工数量，
#只显示员工数量>=2的结果，按照员工数量升序排列，
#每页显示2条，显示第1页
SELECT t_department.did,dname,COUNT(eid)
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_department.did%2=0
GROUP BY t_department.did
HAVING COUNT(eid)>=2
ORDER BY COUNT(eid)
LIMIT 0,2;

8.3 行列转置

8.3.1 单表

DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp`  (
  `sid` int NOT NULL,
  `sname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `course` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `score` int NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
​
-- ----------------------------
-- Records of temp
-- ----------------------------
INSERT INTO `temp` VALUES (1, '张三', '语文', 89);
INSERT INTO `temp` VALUES (2, '张三', '数学', 96);
INSERT INTO `temp` VALUES (3, '张三', '英语', 75);
INSERT INTO `temp` VALUES (4, '李四', '语文', 88);
INSERT INTO `temp` VALUES (5, '李四', '数学', 99);

select * from temp;

image-20221106193257943.png

/*
不够完美
select sid,sname,
   case when course='语文' then score end as "语文",
   CASE WHEN course='数学' THEN score END AS "数学",
   CASE WHEN course='英语' THEN score END AS "英语"
from temp;*/
​
SELECT sid,sname,
   SUM(CASE WHEN course='语文' THEN score END) AS "语文",
   SUM(CASE WHEN course='数学' THEN score END) AS "数学",
   SUM(CASE WHEN course='英语' THEN score END) AS "英语"
FROM temp
GROUP BY sname;
​

image-20221106193214035.png

8.3.2 多表

#学生表
drop table if exists t_stu;
create table t_stu(
    sid int primary key,
    sname varchar(20)
);
​
#添加学生信息
insert into t_stu values(1,'张三');
insert into t_stu values(2,'李四');
​
#课程表
drop table if exists t_course;
create table t_course(
    cid int primary key,
    cname varchar(50)
);
​
#添加课程信息
insert into t_course values(1,'java');
insert into t_course values(2,'mysql');
    
#成绩表
drop table if exists t_score;
create table t_score(
    id int primary key auto_increment,
    sid int ,
    cid int ,
    score int
);
​
insert into t_score values(null,1,1,89);
insert into t_score values(null,2,1,100);
insert into t_score values(null,1,2,79);

#查询每个人的每门课的成绩
SELECT t_stu.sid,sname,t_course.cid,cname,score
FROM t_stu INNER JOIN t_score ON t_stu.sid = t_score.sid
   INNER JOIN t_course ON t_course.cid = t_score.cid
ORDER BY t_stu.sid;

image-20221106193903776.png

#查询每个人的每门课的成绩
SELECT sid,sname,
    SUM(CASE WHEN cname='java' THEN score END) AS "java",
    SUM(CASE WHEN cname='mysql' THEN score END) AS "mysql"
FROM    
(SELECT t_stu.sid,sname,t_course.cid,cname,score
FROM t_stu INNER JOIN t_score ON t_stu.sid = t_score.sid
   INNER JOIN t_course ON t_course.cid = t_score.cid
ORDER BY t_stu.sid) temp
GROUP BY sid
ORDER BY sid;

image-20221106193942694.png

#查询java多少人参与考试，mysql多少人参与考试
SELECT cname,COUNT(*)
FROM t_score INNER JOIN t_course 
ON t_score.cid = t_course.cid
GROUP BY cname;

image-20221106194005188.png

SELECT 
SUM(CASE WHEN cname='java' THEN c END) AS "java",
SUM(CASE WHEN cname='mysql' THEN c END) AS "mysql"
FROM 
(SELECT cname,COUNT(*) AS c
FROM t_score INNER JOIN t_course 
ON t_score.cid = t_course.cid
GROUP BY cname)AS  temp;

image-20221106194044252.png

avatar
文章
阅读
粉丝