MySQL6 嵌套查询、子查询、自关联查询、SQL函数、 流程控制

嵌套查询(子查询)

基本概念：一个查询语句（外部查询）中包含了另一个查询语句（子查询）

select 列名1,列名2....
from 表1,表2
where 条件 条件表达式(子查询语句)

子查询被当作一个单元来执行，它先于外部查询执行，其结果可以被外部查询使用。
子查询的结果通常被看作是一个临时表，外部查询可以像操作任何其他表一样操作这个临时结果集。

1.单行单列

1.查询年龄最大的员工信息,显示信息包含员工号、员工名字、员工年龄

-- step1.找出年龄最大的年龄
USE db4;
SELECT MAX(age) FROM emp;
-- step2.和emp表进行再次匹配,从这张表找年龄为
SELECT * FROM emp WHERE age=85;
-- 子查询合并
SELECT * FROM emp WHERE age=(SELECT MAX(age) FROM emp);

2.多行单列

2.查询研发部和销售部的员工信息,包含员工号、员工名字

-- 关联查询
SELECT * FROM emp,dept
WHERE dept.`name` IN ('研发部','销售部')
AND emp.dept_id = dept.deptno;

-- 多行单列查询
SELECT * FROM emp
WHERE emp.dept_id IN(
	SELECT deptno FROM dept
	WHERE NAME IN('研发部','销售部')
);

3.多行多列

-- 3.查询研发部85岁以下的员工信息,包括员工号、员工名字、部门名字
SELECT * FROM emp,dept
WHERE emp.age AND dept.`name`='研发部' AND
emp.dept_id=dept.deptno;

-- step1 找研发部的信息
-- step2 找85岁以下的员工
SELECT * FROM 
(SELECT * FROM dept WHERE dept.name='研发部')t1,
(SELECT * FROM emp WHERE age <85)t2
WHERE t1.deptno=t2.dept_id;

子查询关键字

ALL

所有 相当于and

-- 子查询关键字
-- ALL 所有 相当于and
-- 查询年龄大于'1003'部门的所有年龄的员工信息

-- 写法1
-- step1:查找1003部门年龄最大的
SELECT MAX(age)
FROM emp
WHERE dept_id = '1003';
-- step2:嵌套查询
SELECT *
FROM emp
WHERE age>(
	SELECT MAX(age)
	FROM emp
	WHERE dept_id = '1003'
);

-- 写法2:使用ALL关键字
SELECT *
FROM emp
WHERE age> ALL(
	SELECT age
	FROM emp
	WHERE dept_id = '1003'
);

ANY

任意 相当于or

-- 查询年龄大于'1003'任意一个员工年龄的员工信息
-- 写法1
-- step1:查找1003部门年龄最小的
SELECT MIN(age)
FROM emp
WHERE dept_id = '1003';
-- step2:嵌套查询
SELECT *
FROM emp
WHERE age>(
	SELECT MIN(age)
	FROM emp
	WHERE dept_id = '1003'
);

-- 写法2:使用ANY关键字
SELECT *
FROM emp
WHERE age> ANY(
	SELECT age
	FROM emp
	WHERE dept_id = '1003'
);

IN

在SQL语言中，IN是一个运算符，用于测试一个值是否在一个列表（一组值）中。它通常用在WHERE子句中，以过滤出满足特定条件的记录。

当你看到这样的查询语句：

SELECT * FROM table WHERE column IN (value1, value2, value3, ...);

这个查询会返回所有column列的值存在于括号内列表（value1, value2, value3等）的记录。

举例来说，如果你有一个orders表，其中有一个customer_id列，你可以使用IN来找出特定顾客ID列表的所有订单：

SELECT * FROM orders WHERE customer_id IN (101, 102, 103);

这个查询将返回customer_id为101，102或103的所有订单。

在你提供的查询中：

SELECT *
FROM emp
WHERE dept_id IN (
    SELECT deptno FROM dept
) AND dept_id > 1001;

这里IN用于检查emp表中的dept_id是否存在于由子查询返回的部门编号列表中。如果存在，并且dept_id大于1001，那么相应的员工记录就会被选中并返回。

-- 查询所有部门信息真实存在的员工
SELECT *
FROM emp
WHERE dept_id IN(
	SELECT deptno FROM dept
) AND dept_id > 1001;

EXISTS

把外层sql的结果，拿到内层sql去测试，如果内层的sql成立，则该行取出。内层查询是exists后的查询。

至少存在员工的部门

-- 至少存在员工的部门
SELECT *
FROM dept
WHERE EXISTS(
	SELECT * FROM emp
	WHERE dept.deptno = emp.dept_id
);

SELECT *
FROM dept
WHERE deptno IN(
	SELECT DISTINCT dept_id  FROM  emp
);

自关联查询

自己和自己做内连接或者外连接,用于显示自关联的层级关系

-- 6. 显示员工KING和FORD管理的下属员工的员工姓名。
-- step1 查询KING和FORD
-- step2 根据mgr查询姓名
SELECT empno FROM emp WHERE ename IN ('KING','FORD');

SELECT empno FROM emp WHERE mgr IN(7902,7839);

SELECT ename FROM emp WHERE mgr IN(
	SELECT empno FROM emp WHERE ename IN ('KING','FORD')
);

-- t1员工   
SELECT *
FROM emp t1 INNER JOIN emp t2
ON t1.mgr=t2.empno AND (t2.ename IN ('KING','FORD'));

数据准备

-- db4的t_sanguo表
-- 创建表,并建立自关联约束
-- 添加自关联约束
CREATE TABLE t_sanguo(
   eid INT PRIMARY KEY,
   ename VARCHAR(20),
   manager_id INT,
   FOREIGN KEY (manager_id) REFERENCES t_sanguo(eid)
);

insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);

SELECT * FROM t_sanguo;
-- 查询每个三国人物的上级
SELECT *
FROM t_sanguo t1 LEFT JOIN t_sanguo t2
ON t1.manager_id = t2.eid;

-- 查询每个三国人物的上级的上级
SELECT *
FROM t_sanguo t1 LEFT JOIN t_sanguo t2
ON t1.manager_id = t2.eid
LEFT JOIN t_sanguo t3 
ON t2.manager_id = t3.eid;

DQL总练习题

-- DQL总练习题
-- 2024-08-10 14:30
CREATE DATABASE mydb3;
USE mydb3;
--学生表 Student(sno-学号,sname-姓名,ssex-性别,sage-年龄,sdept-所在系)
--课程表Course(con-课程号,cname-课程名称,cpno-先行课,ccredit-学分)
--选课表sc(sno-学号,cno-课程号，grade-成绩)


CREATE TABLE Student          
(Sno   CHAR(9)  PRIMARY KEY,   /* 列级完整性约束条件,Sno是主码*/                  
   Sname CHAR(20) UNIQUE,       /* Sname取唯一值*/
   Ssex  CHAR(2),
   Sage  SMALLINT,
   Sdept CHAR(20)
); 


CREATE TABLE  Course(
   Cno  VARCHAR(4)  PRIMARY KEY,    /*列级完整性约束条件，Cno为主码*/
   Cname   CHAR(40) NOT NULL,       /*列级完整性约束条件，Cname不能取空值*/
   Cpno    CHAR(4),                 /*Cpno的含义为先修课*/                        
   Ccredit SMALLINT
);


CREATE TABLE SC
   (Sno CHAR(9), 
    Cno CHAR(4),  
    Grade SMALLINT,
    PRIMARY KEY (Sno,Cno),
                 /* 主码由两个属性构成，必须作为表级完整性进行定义*/
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
                 /* 表级完整性约束条件，Sno是外码，被参照表是Student */
    FOREIGN KEY (Cno)REFERENCES Course(Cno)
                 /* 表级完整性约束条件， Cno是外码，被参照表是Course*/
); 

INSERT INTO  Student VALUES('202415121','李勇','男',20,'CS'),
('202415122','刘晨','女',19,'CS'),
('202415123','王敏','女',18,'MA'),
('202415125','张立','男',19,'IS')

INSERT INTO  Student VALUES('202415124','猫猫','男',20,'LS');
INSERT INTO  Student VALUES('202415126','欧阳疯子','女',20,'LS');
INSERT INTO  Student VALUES('202415127','欧阳封','女',20,'LS');



INSERT INTO  Course VALUES('1','数据库','5',4),
('2','数学',NULL,2),
('3','信息系统','1',4),
('4','操作系统','6',3),
('5','数据结构','7',4),
('6','数据处理',NULL,2),
('7','java语言','6',4)

INSERT INTO  SC VALUES('202415121','1',92),
('202415121','2',85),
('202415121','3',88),
('202415122','2',90),
('202415122','3',80)

SELECT * FROM course;
SELECT * FROM sc;
SELECT * FROM student;
-- 练习1~30 2024-08-10 14:32
-- 1. 查询全体学生的学号与姓名。
SELECT sno,sname FROM student;
-- 2.查询全体学生的姓名、学号、所在系
SELECT sname,sno,sdept FROM student;
-- 3.查询全体学生的详细记录。
SELECT * FROM student;

SELECT * FROM student 
LEFT JOIN sc on student.Sno =sc.Sno
LEFT JOIN course ON sc.Cno=course.Cno;
-- 4.查询全体学生的姓名及其出生年份。
SELECT sname,'2024-08-10'-sage AS 出生年份 FROM student;
-- 5.查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。
SELECT sname,'2024'-sage AS 出生年份,LOWER(sdept) FROM student;
-- 6.查询选修了课程的学生学号。
SELECT sno FROM sc
GROUP BY sno;
-- 7.查询计算机科学系全体学生的名单(cs计算机系)。
SELECT * FROM student WHERE sdept='CS';

SELECT * FROM student 
LEFT JOIN sc on student.Sno =sc.Sno
LEFT JOIN course ON sc.Cno=course.Cno
WHERE student.Sdept='CS';
-- 8.查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT sname,sage
FROM student
WHERE sage < 20;
-- 9.查询考试成绩有不及格的学生的学号。
SELECT sno FROM sc WHERE grade<90;
-- 10. 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄
SELECT sname,sdept,sage
FROM student
WHERE sage BETWEEN 20 AND 23;


-- 11.查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT sname,sdept,sage
FROM student
WHERE sage NOT  BETWEEN 20 AND 23;
-- 12. 查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。
SELECT sname,ssex
FROM student
WHERE sdept IN('CS','MA','IS');
-- 13查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。
SELECT sname,ssex
FROM student
WHERE sdept NOT IN('CS','MA','IS');
-- 14 查询所有姓刘学生的姓名、学号和性别。
SELECT sname,sno,ssex
FROM student
WHERE sname LIKE '刘%';
-- 15查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECT sname
FROM student
WHERE sname LIKE '欧阳_';
-- 16 查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECT sname,sno
FROM student
WHERE sname LIKE '_阳%';
-- 17.查询所有不姓刘的学生姓名、学号和性别。
SELECT sname,sno,ssex
FROM student
WHERE sname  NOT LIKE '刘%';
-- 18某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。
-- 查询缺少成绩的学生的学号和相应的课程号。
SELECT sno,cno
FROM sc
WHERE grade IS  NULL;
-- 19.查询所有有成绩的学生学号和课程号。
SELECT sno,cno
FROM sc
WHERE grade IS NOT NULL;
-- 20 查询计算机系年龄在20岁以下的学生姓名。
SELECT sname
FROM student
WHERE sage<20 AND sdept = 'CS';


-- 21 查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。
SELECT sname,ssex
FROM student
WHERE sdept IN ('CS','MA','IS');
-- 22 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
SELECT sno,grade FROM sc 
WHERE cno = 3
ORDER BY grade DESC;
-- 23查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
SELECT * FROM student
ORDER BY sdept ASC,sage DESC;
-- 24 查询学生总人数。
SELECT COUNT(sno) FROM student;
-- 25 查询选修了课程的学生人数。
SELECT COUNT(DISTINCT sno) FROM sc;
-- 26 计算1号课程的学生平均成绩。
SELECT AVG(grade) FROM sc WHERE cno =1;
-- 27  查询选修1号课程的学生最高分数。
SELECT MAX(grade) FROM sc WHERE cno =1;
-- 28 查询学生202415121选修课程的总学分数。
SELECT SUM(ccredit)
FROM course
WHERE cno IN (
	SELECT cno FROM sc WHERE sno='202415121'
);

SELECT SUM(ccredit)
FROM sc s LEFT JOIN course c ON
c.Cno=s.Cno AND sno='202415121';
-- 29 求各个课程号及相应的选课人数。
SELECT c.Cno, COUNT(DISTINCT s.sno) AS '选课人数'
FROM course c LEFT JOIN sc s ON 
c.Cno = s.Cno
GROUP BY c.Cno;
-- 30 查询选修了3门以上课程的学生学号。
SELECT sno,COUNT(cno) AS num
FROM sc
GROUP BY sno
HAVING num >= 3;

-- 32.查询每个学生及其选修课程的情况
SELECT *
FROM student LEFT JOIN sc ON
student.Sno=sc.Sno
LEFT JOIN course ON
course.Cno=sc.Cno;
-- 33.查询每个学生及其选修课程的情况，使用自然连接。[有问题,没做出来]
SELECT *
FROM student NATURAL JOIN sc ON
student.Sno = sc.Sno
NATURAL JOIN course ON 
sc.Cno = course.Cno;

-- 34.查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。
SELECT sc.Sno,sname
FROM sc INNER JOIN student ON
sc.Sno = student.Sno
WHERE cno = 2 AND grade >=90;
-- 35.查询每一门课的间接先修课（即先修课的先修课）。
SELECT *
FROM course c1 LEFT JOIN course c2 ON c1.Cpno=c2.Cno
LEFT JOIN course c3 ON c2.Cpno=c3.Cno;
-- 36.查询每个学生及其选修课程的情况，改写（有问题，需注意）。
SELECT student.Sno, student.Sname, course.Cno, course.Cname
FROM student LEFT JOIN sc ON student.Sno = sc.Sno
LEFT JOIN course ON sc.Cno = course.Cno;

-- 37 查询每个学生的学号、姓名、选修的课程名及成绩。
SELECT student.Sno, student.Sname, course.Cname,sc.Grade
FROM student LEFT JOIN sc ON student.Sno = sc.Sno
LEFT JOIN course ON sc.Cno = course.Cno;
-- 38.查询与“刘晨”在同一个系学习的学生。
SELECT *
FROM student WHERE sdept = (
	SELECT sdept FROM student WHERE sname='刘晨'
) AND sname !='刘晨';
-- 39.查询选修了课程名为“信息系统”的学生学号和姓名。
SELECT student.Sno,student.Sname
FROM student LEFT JOIN sc ON 
student.Sno = sc.Sno
LEFT JOIN course ON 
sc.Cno = course.Cno
WHERE cname = '信息系统';
-- 40.找出每个学生不小于他选修课程平均成绩的课程号。
SELECT *
FROM student LEFT JOIN sc ON student.Sno = sc.Sno
LEFT JOIN course ON sc.Cno = course.Cno
WHERE sc.grade >= (
   SELECT AVG(sc2.grade)
   FROM sc sc2
   WHERE sc2.Sno = student.Sno
);

SELECT *
FROM sc
WHERE sc.Grade >= (
   SELECT AVG(sc2.Grade)
   FROM sc sc2
   WHERE sc2.Sno = sc.Sno
);



-- 41.查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。
SELECT s1.Sname, s1.Sage
FROM student s1
WHERE s1.Sdept != 'CS' AND s1.Sage < (
   SELECT MIN(sage)
   FROM student s2
   WHERE s2.Sdept = 'CS'
);

-- 42. 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
SELECT s1.Sname, s1.Sage
FROM student s1
WHERE s1.Sdept != 'CS' AND s1.Sage < ALL (
   SELECT s2.Sage
   FROM student s2
   WHERE s2.Sdept = 'CS'
);
-- 43.查询所有选修了1号课程的学生姓名。
SELECT DISTINCT student.Sname
FROM student
JOIN sc ON student.Sno = sc.Sno
WHERE sc.Cno = '1';
-- 44. 查询没有选修1号课程的学生姓名。
SELECT sname
FROM student
WHERE sno NOT IN (
    SELECT sno
    FROM sc
    WHERE cno = '1'
);

-- 45.查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换。
SELECT s1.Sname
FROM student s1
WHERE s1.Sdept = (
    SELECT s2.Sdept
    FROM student s2
    WHERE s2.Sname = '刘晨'
) AND s1.Sname != '刘晨';

DQL难题

CREATE DATABASE mydb4;
USE mydb4;
-- 学生表：Student（编号sid，姓名sname，生日birthday，性别ssex，班级 classid) 
-- 课程表：Course（课程编号cid，课程名称cname，教师编号tid） 
-- 成绩表：Sc（学生编号sid，课程编号cid，成绩score） 
-- 教师表：Teacher（教师编号tid，姓名tname）
-- 班级表：Class （班级编号 classid，班级名称 classname）

-- 学生表 Student
create table student(Sid int primary key, Sname varchar(10), birthday datetime, Ssex varchar(10), classid int);
insert into Student values('1' , '赵雷' , '1990-01-01' , '男', '1');
insert into Student values('2' , '钱电' , '1990-12-21' , '男', '2');
insert into Student values('3' , '孙风' , '1990-05-20' , '男', '1');
insert into Student values('4' , '李云' , '1990-08-06' , '男', '2');
insert into Student values('5' , '周梅' , '1991-12-01' , '女', '1');
insert into Student values('6' , '吴兰' , '1992-03-01' , '女', '2');
insert into Student values('7' , '郑竹' , '1989-07-01' , '女', '1');
insert into Student values('8' , '王菊' , '1990-01-20' , '女', '2');


-- 成绩表 SC
create table SC(Sid int, Cid int, score decimal(18,1));
insert into SC values('1' , '1' , 80);
insert into SC values('1' , '2' , 90);
insert into SC values('1' , '3' , 99);
insert into SC values('2' , '1' , 70);
insert into SC values('2' , '2' , 60);
insert into SC values('2' , '3' , 80);
insert into SC values('3' , '1' , 80);
insert into SC values('3' , '2' , 80);
insert into SC values('3' , '3' , 80);
insert into SC values('4' , '1' , 50);
insert into SC values('4' , '2' , 30);
insert into SC values('4' , '3' , 20);
insert into SC values('5' , '1' , 76);
insert into SC values('5' , '2' , 87);
insert into SC values('6' , '1' , 31);
insert into SC values('6' , '3' , 34);
insert into SC values('7' , '2' , 89);
insert into SC values('7' , '3' , 98);


-- 课程表 Course
create table Course(Cid int primary key,Cname varchar(10),Tid varchar(10));
insert into Course values('1' , '语文' , '2');
insert into Course values('2' , '数学' , '1');
insert into Course values('3' , '英语' , '3');


-- 教师表 Teacher
create table teacher1(
	Tid int primary key auto_increment,
	Tname varchar(10),
	Tsex TINYINT default 1,
	Tbirthday date,
	Taddress varchar(255),
	Temail varchar(255),
	Tmoney DECIMAL(20,2)
);
insert into Teacher values('1' , '张三',1,'1988-1-15','陕西咸阳','zhangsan@qq.com',3000.00);
insert into Teacher values('2' , '李四',0,'1992-5-9','陕西宝鸡','lisi@qq.com',4000.00);
insert into Teacher values('3' , '王五',1,'1977-7-1','山西太原','wangwu@qq.com',5000.00);

-- 班级表 Class
create table Class(classid int primary key, classname varchar(20));
insert into Class values('1', '一班');
insert into Class values('2', '二班');


SELECT * FROM class;
SELECT * FROM course;
SELECT * FROM sc;
SELECT * FROM student;
SELECT * FROM teacher;
-- 1.	查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
SELECT * FROM student 
INNER JOIN 
(SELECT * FROM sc WHERE cid = 1)t1 ON student.sid = t1.sid
INNER JOIN 
(SELECT * FROM sc WHERE cid = 2)t2 ON student.sid = t2.sid
WHERE t1.score > t2.score;


SELECT s.*,t1.score
FROM student s,
	(SELECT sid,cid,score FROM sc WHERE cid = 1)t1,
	(SELECT sid,cid,score FROM sc WHERE cid = 2)t2
WHERE s.sid=t1.sid AND t1.sid=t2.sid AND t1.score>t2.score;
-- 答案1:
SELECT s.*,t1.score,t2.score
FROM student s,
	(SELECT sid,cid,score FROM sc WHERE cid = 1)t1,
	(SELECT sid,cid,score FROM sc WHERE cid = 2)t2
WHERE s.sid=t1.sid AND t1.sid=t2.sid AND t1.score>t2.score;
-- 答案2:
SELECT s.*,t1.score,t2.score
FROM student s,sc t1,sc t2
WHERE s.Sid=t1.Sid AND t1.Sid=t2.Sid AND (t1.Cid=1 AND t2.Cid=2)
		AND t1.score>t2.score;
-- 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT sc.sid,sname,AVG(score) AS num
FROM sc JOIN student ON sc.sid=student.sid
GROUP BY sc.sid 
HAVING num>=60;

-- 答案1
SELECT *
FROM student s,(
	SELECT sid,AVG(score) avg_score
	FROM sc 
	GROUP BY sid) t2
WHERE s.sid=t2.sid AND t2.avg_score>=60; 

-- 答案2
SELECT student.`*`,AVG(score) avg_score
FROM student LEFT JOIN sc ON student.Sid=sc.Sid
GROUP BY student.Sid
HAVING avg_score>=60;

-- 3. 查询在 SC 表存在成绩的学生信息
SELECT student.*
FROM sc LEFT JOIN student
ON sc.Sid=student.Sid
GROUP BY sc.Sid;

-- 答案
SELECT * 
FROM student 
WHERE sid IN (
	SELECT DISTINCT  sid 
	from sc 
	WHERE  score IS NOT NULL);
-- 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT student.sid,sname,COUNT(cid),SUM(score)
FROM student LEFT JOIN sc
ON student.sid = sc.sid
GROUP BY sid;

-- 答案
SELECT * 
FROM student 
LEFT JOIN  (SELECT sid,COUNT(*),SUM(score) FROM sc GROUP BY sid) t1
ON  student.sid = t1.sid;
-- 5. 查询「李」姓老师的数量
SELECT COUNT(*)
FROM teacher
WHERE tname LIKE '李%';
-- 6. 查询学过「张三」老师授课的同学的信息
-- step1:查询张三老师教的课
SELECT tid FROM teacher WHERE tname='张三';

-- step2:查询课程表,老师带的哪个班
SELECT cid FROM course WHERE tid=1;

-- step3:查询cid为2的sid
SELECT sid 
FROM sc 
WHERE cid=2;

-- step4:查询sid对应的学生信息
SELECT * 
FROM student,sc 
WHERE student.Sid=sc.Sid AND cid=2

-- 总的
SELECT * 
FROM student,sc 
WHERE student.Sid=sc.Sid AND 
cid=(SELECT cid FROM course WHERE 
	tid=(SELECT tid FROM teacher WHERE tname='张三')
);

-- 答案
SELECT  * 
FROM student
WHERE EXISTS (
    SELECT * 
    FROM sc
    WHERE cid IN (
	    SELECT cid
	    FROM teacher t1,course t2
	    WHERE tname = '张三' AND t1.tid = t2.tid
	 ) AND student.sid = sc.sid
)
-- 7. 查询没有学全所有课程的同学的信息
SELECT student.*,COUNT(*) course_num
FROM student
LEFT JOIN sc ON student.sid = sc.Sid
GROUP BY student.sid
HAVING course_num<(
	select COUNT(*) FROM course
);

-- 答案
SELECT student.* 
FROM  student 
LEFT  JOIN  sc ON  student.sid = sc.sid
GROUP  BY  student.sid 
HAVING  count(cid) < (SELECT  count(*) FROM   course);

-- 8. 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息
-- 赵雷学了科目内容1 2 3
-- 至少1,2,3 1,2 1,2,4 1,4
SELECT student.* 
FROM student INNER JOIN sc ON student.Sid=sc.Sid
WHERE student.Sid NOT IN(
	SELECT sid FROM sc WHERE cid NOT IN(
		SELECT cid FROM sc WHERE sid=1
	)
)
GROUP BY student.Sid;

-- 答案
SELECT  DISTINCT student.*
FROM student,(SELECT * 
FROM sc
WHERE  cid IN(
	SELECT cid
	FROM sc 
	WHERE sid = 7))t1
WHERE student.sid = t1.sid
-- 9. 查询和” 01 “号的同学学习的课程完全相同的其他同学的信息
-- step1:查询01号同学学习的课程
SELECT cid FROM sc WHERE sid =1
-- step2:查询学习1,2,3课程的人的名字
SELECT s.*,t1.*,t2.*
FROM student s,
(SELECT sid,cid,score FROM sc WHERE cid=1)t1,
(SELECT sid,cid,score FROM sc WHERE cid=2)t2,
(SELECT sid,cid,score FROM sc WHERE cid=3)t3
WHERE s.Sid=t1.sid AND t1.sid=t2.sid AND t2.sid = t3.sid

-- 总的【错误的,查询不是动态的】
SELECT s.*
FROM student s,
     (SELECT sid, cid, score FROM sc WHERE cid = 1) t1,
     (SELECT sid, cid, score FROM sc WHERE cid = 2) t2,
     (SELECT sid, cid, score FROM sc WHERE cid = 3) t3
WHERE s.sid = t1.sid
  AND t1.sid = t2.sid
  AND t2.sid = t3.sid;

-- 答案:
-- step1:内连接
SELECT *
FROM student
INNER JOIN sc ON student.sid = sc.sid
-- step2:查询课程在都01的学生选课的信息中的学生
SELECT student.sid,student.Sname,COUNT(*)
FROM student
INNER JOIN sc ON student.sid = sc.sid
WHERE 
student.sid NOT IN(
	SELECT sid
	FROM sc
	WHERE cid NOT IN(
		SELECT cid FROM sc WHERE sid='01'
	)
)
GROUP BY student.sid,student.sname
HAVING COUNT(cid) = (SELECT COUNT(*) FROM sc WHERE sid=1);


-- 10. 查询没学过”张三”老师讲授的任一门课程的学生姓名
SELECT Sname 
FROM student 
WHERE Sid NOT IN(
  SELECT DISTINCT Sid FROM sc WHERE Cid=(
    SELECT Cid FROM course WHERE Tid=(
			SELECT Tid FROM teacher WHERE Tname='张三'
		)
	)
);

-- 答案
SELECT  * 
FROM student
WHERE NOT  EXISTS (
    SELECT * 
    FROM sc
    WHERE cid IN (
	    SELECT cid
	    FROM teacher t1,course t2
	    WHERE tname = '张三' AND t1.tid = t2.tid
	 ) AND student.sid = sc.sid
);
-- 11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT sc.Sid,sname,AVG(score)
FROM sc,student
WHERE score<60 AND sc.Sid=student.Sid
GROUP BY sc.Sid
HAVING COUNT(*)>=2;

-- 12. 检索” 01 “课程分数小于 60，按分数降序排列的学生信息
SELECT student.*
FROM student
INNER JOIN sc ON student.Sid=sc.Sid
WHERE score < 60 AND cid = 1
ORDER BY score DESC;

-- 答案
SELECT * 
FROM student,sc 
WHERE student.sid = sc.sid AND cid = '01' AND sc.score<60
ORDER BY score DESC;
-- 13. 查询各科成绩最高分、最低分和平均分，以如下形式显示：
-- 课程 ID，课程 name，最高分，最低分，平均分，
-- 及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。
-- 方式1
SELECT sc.Cid AS '课程ID',cname '课程',
MAX(score) AS '最高分',
MIN(score) AS '最低分',
AVG(score) AS '平均分',
(
	(SELECT COUNT(sid) FROM sc WHERE score>=60 AND sc.Cid=course.Cid)/
	(SELECT COUNT(sid) FROM sc WHERE sc.Cid=course.Cid)
)AS '及格率',
(
	(SELECT COUNT(sid) FROM sc WHERE score>=70 AND score<80 AND sc.Cid=course.Cid)/
	(SELECT COUNT(sid) FROM sc WHERE sc.Cid=course.Cid)
)AS '中等率',
(
	(SELECT COUNT(sid) FROM sc WHERE score>=80 AND score<90 AND sc.Cid=course.Cid)/
	(SELECT COUNT(sid) FROM sc WHERE sc.Cid=course.Cid)
)AS '优良率',
(
	(SELECT COUNT(sid) FROM sc WHERE score>=90 AND sc.Cid=course.Cid)/
	(SELECT COUNT(sid) FROM sc WHERE sc.Cid=course.Cid)
)AS '优秀率'
FROM sc
INNER JOIN course ON sc.cid = course.cid
GROUP BY course.Cid;

-- 方式2
SELECT 
	sc.Cid AS '课程ID',
	course.cname AS '课程',
	MAX(sc.score) AS '最高分',
	MIN(sc.score) AS '最低分',
	AVG(sc.score) AS '平均分',
	SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / COUNT(sc.sid) AS '及格率',
	SUM(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END) / COUNT(sc.sid) AS '中等率',
	SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END) / COUNT(sc.sid) AS '优良率',
	SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / COUNT(sc.sid) AS '优秀率'
FROM 
	sc
INNER JOIN 
	course ON sc.cid = course.cid
GROUP BY 
	sc.Cid, course.cname;
	
-- 方式3
SELECT 
	sc.Cid AS '课程ID',
	course.cname AS '课程',
	MAX(sc.score) AS '最高分',
	MIN(sc.score) AS '最低分',
	AVG(sc.score) AS '平均分',
	COUNT(case when sc.score>=60 then score END)/COUNT(*) AS '及格率',
	COUNT(case when sc.score>=70 AND sc.score<80 then score END)/COUNT(*) AS '中等率',
	COUNT(case when sc.score>=80 AND sc.score<90 then score END)/COUNT(*) AS '优良率',
	COUNT(case when sc.score>=90 then score END )/COUNT(*) AS '优秀率'
FROM 
   sc
INNER JOIN 
   course ON sc.cid = course.cid
GROUP BY 
   sc.Cid, course.cname;

SQL函数

数据准备

-- MySQL的函数
CREATE DATABASE db5;
USE db5;

create table emp(
    emp_id int primary key auto_increment comment '编号',
    emp_name char(20) not null default '' comment '姓名',
    salary decimal(10,2) not null default 0 comment '工资',
    department char(20) not null default '' comment '部门'
);
 
insert into emp(emp_name,salary,department) 
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),
('刘云鹏',7800,'销售部');

group_concat函数

-- group_concat函数
-- 将所有员工的名字合成一行
SELECT GROUP_CONCAT(emp_name)
FROM emp;

-- 指定分隔符和分组合并
SELECT department,GROUP_CONCAT(emp_name SEPARATOR ';')
FROM emp
GROUP BY department;

-- 指定排序方式和分隔符
SELECT department,GROUP_CONCAT(emp_name ORDER BY salary DESC SEPARATOR ';')
FROM emp
GROUP BY department;

-- 指定排序方式和分隔符并去重
SELECT department,GROUP_CONCAT(DISTINCT emp_name ORDER BY salary DESC SEPARATOR ';')
FROM emp
GROUP BY department;

数学相关函数

abs()取绝对值

-- abs() 取绝对值
SELECT ABS(5),ABS(-2.2),ABS(2.2),ABS(0);

sqrt()开平方

-- sqrt()开平方--负数开不了
SELECT SQRT(4),SQRT(5),SQRT(0),SQRT(-5);

mod(x,y)求余数

-- mod(x,y) 求余数-- 对0取余为NULL
SELECT MOD(10,3),MOD(10,-3),MOD(12.5,6),MOD(10,0);

ceil(x)和ceiling(x)向上取整

-- ceil(x)和ceiling(x)向上取整
SELECT CEIL(-2.1),CEILING(-2.9),CEIL(2.1),CEIL(2.9);

floor(x)向下取整

-- floor(x)向下取整
SELECT FLOOR(-2.1),FLOOR(-2.9),FLOOR(2.1),FLOOR(2.9);

rand()生成随机数

-- rand()生成随机数
SELECT RAND;

-- RAND(x) x为随机数种子,种子相同,每次生成的随机数也相同
SELECT RAND(5);-- 0.40613597483014313

round()四舍五入函数

-- round():四舍五入函数
SELECT ROUND(-2.1),ROUND(-2.9),ROUND(2.1),ROUND(2.9);
-- 从指定位数进行四舍五入
-- 21.123
-- -1 0. 1 2 3第几位
SELECT ROUND(21.153,-2); -- 0
SELECT ROUND(25.153,-1); -- 30
SELECT ROUND(21.553,0); -- 22
SELECT ROUND(21.153,1); --21.2
SELECT ROUND(21.153,2); --21.15
SELECT ROUND(21.153,3); --21.153
SELECT ROUND(21.153,300); --21.153

sign()判断值的范围

-- sign(): 整数>0 1  整数=0 0 整数<0 -1
SELECT SIGN(100),SIGN(0),SIGN(-100);

pow(x,y),x的y次方

-- pow(x,y) power(x,y):x的y次方
SELECT POW(2,2),POW(2,-2),POW(-2,2);

sin()函数

-- sin() 角度xπ÷180°
SELECT SIN(PI()/6);  -- SIN(π/6)
SELECT PI();

truncate(x,y)截断

-- truncate(x,y)截断 从x的第y位往后的截断,不进行四舍五入
-- 2.123456
-- 0.123456 第几位
SELECT TRUNCATE(2.123456,2); -- 2.12
SELECT TRUNCATE(2.123456,-2);-- 0
SELECT TRUNCATE(2.123456,800);-- 2.123456

字符串相关的函数

lower() 将大写转小写

-- lower() 将大写转小写
SELECT LOWER('LIYU'),LOWER('LiYu'),LOWER('liyu'),LOWER('李YU'),LOWER('李郁')

upper() 将小写转大写

-- upper() 将小写转大写
SELECT UPPER('LIYU'),UPPER('LiYu'),UPPER('liyu'),UPPER('李yu'),UPPER('李郁')
    
-- 查询多列
USE test;
SELECT ename,LOWER(ename),UPPER(ename) FROM emp;

concat() 字符串连接

-- concat() 字符串连接
SELECT CONCAT('一','猫','人'),CONCAT(ename,'_','的工作',job) FROM emp;

substr()/substring()截取字符串

-- substr()/substring() 截取字符串
-- substr(x,y) x要截取的字符串,y要截取的字符串的起始位置(默认从1开始数)
-- substr(x,y,z) z截取的字符个数
SELECT SUBSTR('HelloWorld',5),SUBSTRING('HelloWorld',5);-- oWorld
SELECT SUBSTR('HelloWorld',5,3),SUBSTRING('HelloWorld',5,3);-- oWo

SELECT SUBSTR('HelloWorld',-5),SUBSTRING('HelloWorld',-5);-- World
SELECT SUBSTR('HelloWorld',5,-3),SUBSTRING('HelloWorld',5,-3);-- 空
SELECT SUBSTR('HelloWorld',-5,-3),SUBSTRING('HelloWorld',-5,-3);-- 空

length() 返回字符串的长度

-- length() 返回字符串的长度
SELECT LENGTH('abcdefg'),LENGTH('一猫人');-- 7 9 (UTF8一个汉字占3个字节,GBK2个字节)

char_length() character_length() 返回一共有多少个字

-- char_length() character_length() 返回一共有多少个字
SELECT CHAR_LENGTH('abcdefg'),CHAR_LENGTH('一猫人');-- 7 3
SELECT CHARACTER_LENGTH('abcdefg'),CHARACTER_LENGTH('一猫人');-- 7 3

instr() 在字符串中找某个字符串第一次出现的位置

-- instr() 在字符串中找某个字符串第一次出现的位置
SELECT INSTR('footer','o'),INSTR('footer','oo');

LPAD()、RPAD()指定字符串输出的长度

-- 指定字符串输出的长度
-- lpad() rapd() 不够指定的位数补字符串
SELECT LPAD('毛阿敏',10,'5');-- 5555555毛阿敏
SELECT RPAD('毛阿敏',10,'5');-- 毛阿敏5555555

SELECT RPAD('毛阿敏',-10,'5');-- 空

trim去除字符串的字符/空格

-- trim去除字符串的两端空格
SELECT CONCAT('a',(TRIM('  Stringzhua ')),'a');

-- 去除左边空格或字符
SELECT TRIM(LEADING ' ' FROM '  Stringzhua  ') AS 去除左边空格,
TRIM(LEADING 'M' FROM 'MMMStringzhuaMMM')AS 去除左边字符;

-- 去除右边空格或字符
SELECT TRIM(TRAILING ' ' FROM '  Stringzhua  ') AS 去除右边空格,
TRIM(TRAILING 'M' FROM 'MMMStringzhuaMMM')AS 去除右边字符;

-- 去除两边空格或字符
SELECT TRIM(BOTH ' ' FROM '  Stringzhua  ') AS 去除两边空格,
TRIM(BOTH 'M' FROM 'MMMStringzhuaMMM')AS 去除两边字符;

replace() 替换字符串

-- replace() 替换字符串,替换字符串中指定的旧字符串内容为新字符
-- 替换的是全部匹配的旧字符进行替换
SELECT REPLACE('www.github.com','github','gitee');

-- repeat(str,count) 将str重复count次后返回
SELECT repeat('docker',3);

-- reverse() 将字符串反转后返回
SELECT REVERSE('docker');

repeat() 重复字符串

-- repeat(str,count) 将str重复count次后返回
SELECT repeat('docker',3);

reverse() 反转字符串

-- reverse() 将字符串反转后返回
SELECT REVERSE('docker');

format() 格式化字符串

-- 格式化数字, 将其转换为字符串format(x,y) 整数部分每3位一个逗号
-- y为小数位数,保留几位,保留时进行四舍五入
SELECT FORMAT(1234,3);
SELECT FORMAT(1234.56782,4);-- 1,234.5678
SELECT FORMAT(1234.56789,4);-- 1,234.5679

日期时间相关函数

获取日期相关函数

-- 1.获取日期相关函数
-- NOW()返回当前的日期和时间,CURDATE()函数返回当前的日期
-- CURTIME()返回当前的时间
SELECT NOW(),CURDATE(),CURTIME();

获取日期

-- 2.获取日期 WEEK(NOW())获取的是这一年的第多少周
SELECT DATE(NOW()),YEAR(NOW()),MONTH(NOW()),DAY(NOW()),WEEK(NOW());
SELECT DATE(CURDATE()),YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),WEEK(CURDATE());

获取时间

-- 3.获取时间
SELECT TIME(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
SELECT TIME(CURTIME()),HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME());

获取当前天

-- 4.获取当前天 226 13 3(周日为第一天,1 3为周二)
SELECT DAYOFYEAR(NOW()),DAYOFMONTH(NOW()),DAYOFWEEK(NOW());
SELECT WEEKOFYEAR(NOW());-- 当前时间为该年的第多少周

日期相关的转换

格式化时间

-- 1.格式化时间DATE_FORMAT(date,'格式')
SELECT NOW(),DATE_FORMAT(NOW(),'%y-%m-%d %h:%i:%s');

SELECT DATE_FORMAT(NOW(),'%Y-%b-%d');-- 2024-Aug-13
SELECT DATE_FORMAT(NOW(),'%Y-%M-%e');-- 2024-August-13
SELECT DATE_FORMAT(NOW(),'%Y-%m-%D');-- 2024-08-13th
SELECT DATE_FORMAT(NOW(),'%Y-%c-%j');-- 2024-8-226

SELECT DATE_FORMAT(NOW(),'%a');-- Tue 
SELECT DATE_FORMAT(NOW(),'%W');-- Tuesday
SELECT DATE_FORMAT(NOW(),'%H:%i:%S');-- 12:13:19
SELECT DATE_FORMAT(NOW(),'%h:%i:%f');-- 12:13:000000

SELECT DATE_FORMAT(NOW(),'%T');-- 12:14:28

主题	格式	描述
年	%Y	4位数的年份
月	%b	月份对应的英文缩写
月	%M	月份对应的英文全称
月	%m	01-12的月
月	%c	01-12的月份数值
日	%d	01-31的某月里面的第几天
日	%e	01-31的某月里面的第几天
日	%D	用th后缀表示某月中的第几天
日	%j	001~366的一年中的第几天
周	%a	星期几对应的英文缩写
周	%W	星期几对应的英文全称
时	%H	00~23的小时
时	%h	01~12的小时
分	%i	00~59的分钟
秒	%S	秒(00~59)
秒	%f	微秒
时分秒	%T	返回当前的时分秒,24~小时(hh:mm:ss)

extract函数

-- 2.extract函数,用于返回一个具体日期时间的单独部分
SELECT 
	EXTRACT(YEAR FROM NOW()) AS col1,
	EXTRACT(MONTH FROM NOW()) AS col2,
	EXTRACT(DAY FROM NOW()) AS col3,
	EXTRACT(WEEK FROM NOW()) AS col4,
	EXTRACT(HOUR FROM NOW()) AS col5,
	EXTRACT(MINUTE FROM NOW()) AS col6,
	EXTRACT(SECOND FROM NOW()) AS col7;

unit	说明
year	年
month	月
day	日
hour	小时
minute	分钟
second	秒
week	周数，全年第几周

日期相关计算问题(向后/向前偏移时间)

-- 3.日期相关计算问题
-- 向后偏移时间date_add(date,interval num unit)
SELECT NOW(),
		DATE_ADD(NOW(),INTERVAL 1 YEAR),
		DATE_ADD(NOW(),INTERVAL 1 MONTH),
		DATE_ADD(NOW(),INTERVAL 1 WEEK),
		DATE_ADD(NOW(),INTERVAL 1 DAY),
		DATE_ADD(NOW(),INTERVAL 1 HOUR),
		DATE_ADD(NOW(),INTERVAL 1 MINUTE),
		DATE_ADD(NOW(),INTERVAL 1 SECOND);
-- 向前偏移时间date_sub(date,interval num unit)
SELECT NOW(),
		DATE_SUB(NOW(),INTERVAL 1 YEAR),
		DATE_SUB(NOW(),INTERVAL 1 MONTH),
		DATE_SUB(NOW(),INTERVAL 1 WEEK),
		DATE_SUB(NOW(),INTERVAL 1 DAY),
		DATE_SUB(NOW(),INTERVAL 1 HOUR),
		DATE_SUB(NOW(),INTERVAL 1 MINUTE),
		DATE_SUB(NOW(),INTERVAL 1 SECOND);

datediff(end_date,start_date) 和指定时间差多少天

-- 4.datediff(end_date,start_date) 和指定时间差多少天
SELECT DATEDIFF(NOW(),'2022-08-12');

练习

-- 2024-08-13 12:05
-- emp
-- 查询入职时间为1981年的人
USE test;
SELECT * FROM emp;

SELECT *
FROM emp
WHERE SUBSTRING(hiredate,1,4)=1981;

SELECT * 
from emp 
WHERE EXTRACT(YEAR FROM hiredate)=1981;

流程控制函数

if()类似三目运算

-- if
-- 1.if()类似三目运算
SELECT if(10<5,'大','小'),if(1>2,1,0),if(1<2,'√','×');

USE mydb4;
SELECT *,if(Tsex=1,'男','女') AS 性别,
if(INSTR(Taddress,'陕西')>0,'陕西','其他')AS 籍贯
FROM teacher;

ifnull()判断是否为空

-- 2.ifnull()判断是否为空
SELECT IFNULL(NULL,'猫头鹰'),IFNULL('猫头鹰','一猫人');

nullif(expr1,expr2) 比较两个字符串是否相等,相等为null,不相等返回expr1

-- 3.nullif(expr1,expr2) 比较两个字符串是否相等,相等为null,不相等返回expr1
SELECT NULLIF('苏轼','苏轼'),NULLIF('苏轼','苏东坡'),NULLIF(1,1);

-- case
CREATE TABLE t_stu(
	id INT AUTO_INCREMENT COMMENT '编号',
	NAME VARCHAR(10) COMMENT '姓名',
	sex TINYINT COMMENT '性别,0:未知,1:男,2:女',
	PRIMARY KEY(id)
) COMMENT '学生表';

INSERT INTO t_stu (NAME,sex) VALUES
('张学友',1),('刘德华',1),('郭富城',1),('蔡依林',2),('猫猫',0);

SELECT * FROM t_stu;
-- 写法1
SELECT *,(case t.sex
				when 1 then '男'
				when 2 then '女'
				ELSE '未知'
			END
)AS '性别'
FROM t_stu AS t;

-- 写法2
SELECT *,(case 
				when t.sex=1 then '男'
				when t.sex=2 then '女'
				ELSE '未知'
			END
)AS '性别'
FROM t_stu AS t;

练习

-- 2024-08-13 14:35
use mydb4; 
-- 创建订单表
create table orders(
 oid int primary key, -- 订单id
 price double, -- 订单价格
 payType int -- 支付类型(1:微信支付 2:支付宝支付 3:银行卡支付 4：其他)
);
 
insert into orders values(1,1200,1);
insert into orders values(2,1000,2);
insert into orders values(3,200,3);
insert into orders values(4,3000,1);
insert into orders values(5,1500,2);

SELECT * FROM orders;
-- 查询支付方式
-- 写法1
SELECT *,(case t.paytype
				when 1 then '微信支付'
				when 2 then '支付宝支付'
				when 2 then '银行卡支付'
				ELSE '其他'
			END
)AS '支付方式'
FROM orders AS t;

-- 写法2
SELECT *,(case 
				when t.paytype=1 then '微信支付'
				when t.paytype=2 then '支付宝支付'
				when t.paytype=2 then '银行卡支付'
				ELSE '其他'
			END
)AS '支付方式'
FROM orders AS t;

开窗函数

数据准备

USE db5;
create table test(id int,name varchar(10),sale int);
insert into test values(1,'aaa',100);
insert into test values(1,'bbb',200);
insert into test values(1,'ccc',200);
insert into test values(1,'ddd',300);
insert into test values(2,'eee',400);
insert into test values(2,'fff',200);

排序函数

SELECT * FROM test;
-- ROW_NUMBER()计算的值表示每组内部排序后的顺序编号(组内的排序是连续且唯一的)
SELECT *,ROW_NUMBER() OVER() FROM test;
-- 根据id分组,进行排序
SELECT *,ROW_NUMBER() OVER(PARTITION BY id) FROM test;
SELECT *,ROW_NUMBER() OVER(PARTITION BY id ORDER BY sale DESC) FROM test;
-- RANK()有相同的排名时,相同排名的数据有相同的序号,排序序号不连续
SELECT *,RANK() OVER(PARTITION BY id ORDER BY sale DESC) FROM test;
-- DENSE_RANK(),有相同的排名时,相同排名的数据有相同的序号,排序序号连续
SELECT *,DENSE_RANK() OVER(PARTITION BY id ORDER BY sale DESC) FROM test;

SELECT *,
	ROW_NUMBER() OVER(ORDER BY sale DESC),
	RANK() OVER(ORDER BY sale DESC),
	DENSE_RANK() OVER(ORDER BY sale DESC)
FROM test;

-- 开窗聚合函数
SELECT *,SUM(salary) OVER() FROM employee;

-- 开窗聚合函数求和(分组求和)
SELECT *,SUM(salary) OVER (PARTITION BY dname) FROM employee;

-- 开窗聚合函数求和(分组并排序求和)
SELECT *,SUM(salary) OVER (PARTITION BY dname ORDER BY hiredate DESC) FROM employee;

滑动窗口

-- 滑动窗口
-- 算出当前行和前三行的和
SELECT *,SUM(salary) OVER (
	PARTITION BY dname
	ORDER BY hiredate DESC
	ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) sum
FROM employee;

-- 找出前两行、当前行、后一行中的最大值
SELECT *,MAX(salary) OVER (
	PARTITION BY dname
	ORDER BY hiredate DESC
	ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) MAX
FROM employee;
-- 找出前两行、当前行、后一行中的最小值
SELECT *,MIN(salary) OVER (
	PARTITION BY dname
	ORDER BY hiredate DESC
	ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) MIN
FROM employee;

-- 找出前两行、当前行、后一行中的平均值
SELECT *,AVG(salary) OVER (
	PARTITION BY dname
	ORDER BY hiredate DESC
	ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) AVG
FROM employee;

-- 找出前两行、当前行、后一行中的总和
SELECT *,SUM(salary) OVER (
	PARTITION BY dname
	ORDER BY hiredate DESC
	ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) SUM
FROM employee;
	
-- 找出前两行、当前行、后一行中的个数
SELECT *,COUNT(salary) OVER (
	PARTITION BY dname
	ORDER BY hiredate DESC
	ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) COUNT
FROM employee;

练习

数据准备

USE db5;
create table employee( 
   dname varchar(20), -- 部门名 
   eid varchar(20), 
   ename varchar(20), 
   hiredate date, -- 入职日期 
   salary double -- 薪资
); 

insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
 
insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

SELECT * FROM employee;
-- 1.- 对每个部门的员工按照薪资排序，并给出排名
SELECT *,ROW_NUMBER() OVER(PARTITION BY dname ORDER BY salary DESC) FROM employee;
-- 2.-- 对每个部门的员工按照薪资排序，并给出排名 rank
SELECT *,RANK() OVER(PARTITION BY dname ORDER BY salary DESC) FROM employee;
-- 3.-- 对每个部门的员工按照薪资排序，并给出排名 dense-rank
SELECT *,DENSE_RANK() OVER(PARTITION BY dname ORDER BY salary DESC) FROM employee;
-- 4-- 求出每个部门薪资排在前三名的员工-（提示，子查询）单行单列 单行多列 多行多列
SELECT *
FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY dname ORDER BY salary DESC) AS number
FROM employee) t1
WHERE number<=3;

前后函数

-- 为每一行数据的上一行数据进行开窗,如果该行没有上一行数据,则显示为NULL
SELECT sale_date,salary,
		LAG(salary) OVER(ORDER BY sale_date) AS lag_salary
FROM sales;

-- 设置OFFSET偏移量为2,可以查到当前行的前面第2行的数据
-- 当前行的前面第2行如果没有数据,则显示为null,否则返回并显示对应的数据内容
SELECT sale_date,salary,
		LAG(salary,2) OVER(ORDER BY sale_date) AS lag_salary
FROM sales;

-- 设置OFFSET偏移量为1,可以查到当前行的往下数1行的数据
-- 当前行的往下数1行如果没有数据,则显示为0,即把默认显示的NULL替换为自定义的显示内容
SELECT sale_date,salary,
		LEAD(salary,1,0) OVER(ORDER BY sale_date) AS lag_salary
FROM sales;

-- 存在下一行的数据显示为exist,不存在往下数2行的数据则显示0,这个NULL为默认的
SELECT sale_date,salary,
		LEAD('exist',1,0) OVER(ORDER BY sale_date) AS lag_salary
FROM sales;

首尾函数

-- 首尾函数
-- db5的employee表
USE db5;
SELECT *,
	FIRST_VALUE(salary)OVER(PARTITION BY dname ORDER BY hiredate ) AS FIRST,
	LAST_VALUE(salary)OVER(PARTITION BY dname ORDER BY hiredate ) AS LAST
FROM employee;

分箱函数

-- 分箱函数
-- test的emp表
USE test;
SELECT ename,hiredate,deptno,
NTILE(4) OVER(PARTITION BY deptno ORDER BY hiredate)
FROM emp;