mysql DQL

--1取得每个部门中的最高薪水的人员名称。第一步取得每个部门的最高薪水。第二步把上面获得的结果作为临时表t然后与emp表进行内连接中的等值连接连接条件为emp表的deptno,sal和t中的deptno,maxsal相等。SELECT d.deptno,d.dname,max(e.sal)from dept d INNER JOIN emp e on d.deptnoe.deptno GROUP BY d.deptnoSELECT s.dname,e1.enameFROM emp e1 INNER JOIN(SELECT d.deptno,d.dname,max(e.sal) maxsalfrom dept d INNER JOIN emp e on d.deptnoe.deptno GROUP BY d.deptno ) s on e1.deptnos.deptno and e1.sals.maxsal--2哪些人的薪水在部门的平均薪水之上。select d.deptno ,d.dname,avg(e.sal) avgsalfrom dept d INNER JOIN emp e on d.deptnoe.deptno GROUP BY d.deptnoselect e1.enamefrom emp e1 INNER JOIN (select d.deptno ,d.dname,avg(e.sal) avgsalfrom dept d INNER JOIN emp e on d.deptnoe.deptno GROUP BY d.deptno) s on e1.deptnos.deptnoand e1.sals.avgsal--3--3.1取得部门所有人的平均薪水的等级。select avg(e.sal) avgsalfrom dept d INNER JOIN emp e on d.deptnoe.deptnoselect sal.gradefrom salgrade sal INNER JOIN (select avg(e.sal) avgsalfrom dept d INNER JOIN emp e on d.deptnoe.deptno ) s on s.avgsal BETWEENsal.losal and sal.hisal--3.2取得每个部门中所有人的薪水等级的平均等级。select d.dname,avg( s.grade) avgfrom dept d INNER JOIN emp e on d.deptnoe.deptno INNER JOIN salgrade s on e.sal BETWEENs.losal and s.hisal GROUP BY d.deptnoselect avg(s.grade)from salgrade s INNER JOIN (select s.gradefrom dept d INNER JOIN emp e on d.deptnoe.deptno INNER JOIN salgrade s on e.sal BETWEENs.losal and s.hisal ) s1 on s.grades1.gradeselect s.dname, sal.gradefrom salgrade sal INNER JOIN (select avg(e.sal) avgsal ,d.dname dnamefrom dept d INNER JOIN emp e on d.deptnoe.deptno GROUP BY d.deptno ) s on s.avgsal BETWEENsal.losal and sal.hisal--4不准用分组函数Max取得最高薪水。select e.sal 最高薪水FROM emp e where e.salall(select sal from emp where sal!e.sal )SELECT salFROM emp ORDER BY sal desc LIMIT 1--5、取得平均薪水最高的部门的部门编号select d.dname,avg(e.sal) avgfrom emp e INNER JOIN dept d on e.deptnod.deptno GROUP BY d.deptnoselect deptnofrom (select deptno, avg(sal) as avg_sal from emp GROUP by deptno) sorder by avg_sal desclimit 1;--6、取得平均薪水最高的部门的部门名称。select s.dnamefrom (select e.deptno deptno, d.dname dname,avg(e.sal) avgfrom emp e left JOIN dept d on e.deptnod.deptno GROUP BY e.deptno) s ORDER BY avg desc LIMIT 1--7、求平均薪水的等级最低的部门的部门名称。select d.dname,avg( s.grade) avgfrom dept d INNER JOIN emp e on d.deptnoe.deptno INNER JOIN salgrade s on e.sal BETWEENs.losal and s.hisal GROUP BY d.deptnoselect s.dnamefrom ( select d.dname,avg( s.grade) avgfrom dept d INNER JOIN emp e on d.deptnoe.deptno INNER JOIN salgrade s on e.sal BETWEENs.losal and s.hisal GROUP BY d.deptno) s ORDER BY avg LIMIT 1附表CREATE TABLE student (id INT(10) PRIMARY KEY auto_increment , --学生ID 自增name VARCHAR(20) NOT NULL , -- 学生姓名sex VARCHAR(4) , -- 学生性别birth YEAR, -- 学生出生年份department VARCHAR(20) , --所在院系address VARCHAR(50) -- 家庭住址);CREATE TABLE score (id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT , --自增IDstu_id INT(10) NOT NULL , --学生ID 外键创建可有可无c_name VARCHAR(20) , -- 学科名称grade INT(10) -- 分数);INSERT INTO student VALUES( 901,张老大, 男,1985,计算机系, 北京市海淀区);INSERT INTO student VALUES( 902,张老二, 男,1986,中文系, 北京市昌平区);INSERT INTO student VALUES( 903,张三, 女,1990,中文系, 湖南省永州市);INSERT INTO student VALUES( 904,李四, 男,1990,英语系, 辽宁省阜新市);INSERT INTO student VALUES( 905,王五, 女,1991,英语系, 福建省厦门市);INSERT INTO student VALUES( 906,王六, 男,1988,计算机系, 湖南省衡阳市);INSERT INTO score VALUES(NULL,901, 计算机,98);INSERT INTO score VALUES(NULL,901, 英语, 80);INSERT INTO score VALUES(NULL,902, 计算机,65);INSERT INTO score VALUES(NULL,902, 中文,88);INSERT INTO score VALUES(NULL,903, 中文,95);INSERT INTO score VALUES(NULL,904, 计算机,70);INSERT INTO score VALUES(NULL,904, 英语,92);INSERT INTO score VALUES(NULL,905, 英语,94);INSERT INTO score VALUES(NULL,906, 计算机,90);INSERT INTO score VALUES(NULL,906, 英语,85);CREATE TABLE emp (empno int(11) NOT NULL AUTO_INCREMENT,ename varchar(50) DEFAULT NULL,job varchar(50) DEFAULT NULL,mgr int(11) DEFAULT NULL,hiredate date DEFAULT NULL,sal decimal(7,2) DEFAULT NULL,comm decimal(7,2) DEFAULT NULL,deptno int(11) DEFAULT NULL,PRIMARY KEY (empno))INSERT INTO emp VALUES (1001, 张贝贝, 文员, 1013, 2000-12-17, 8000.00, null, 20);INSERT INTO emp VALUES (1002, 黛依思, 销售员, 1006, 2001-02-20, 16000.00, 3000.00, 30);INSERT INTO emp VALUES (1003, 李莫愁, 销售员, 1006, 2001-02-22, 12500.00, 5000.00, 30);INSERT INTO emp VALUES (1004, 刘备, 经理, 1009, 2001-01-02, 29750.00, null, 20);INSERT INTO emp VALUES (1005, 谢逊, 销售员, 1006, 2001-09-28, 12500.00, 14000.00, 30);INSERT INTO emp VALUES (1006, 关羽, 经理, 1009, 2001-05-01, 27000.00, null, 30);INSERT INTO emp VALUES (1007, 张飞, 经理, 1009, 2003-04-01, 25000.00, null, 10);INSERT INTO emp VALUES (1008, 诸葛亮, 分析师, 1004, 2007-04-09, 30000.00, null, 20);INSERT INTO emp VALUES (1009, 黄盖, 文员, 1007, 2005-01-01, 9500.00, 1000.00, 10);INSERT INTO emp VALUES (1010, 张三, 董事长, null, 1998-01-01, 50000.00, null, 10);INSERT INTO emp VALUES (1011, 李四, 保洁, 1001, 2023-10-01, 80000.00, 10000.00, 50);CREATE TABLE dept(deptno INT PRIMARY KEY,dname varchar(14),loc varchar(13));INSERT INTO dept VALUES (10, 教研部, 北京);INSERT INTO dept VALUES (20, 学工部, 郑州);INSERT INTO dept VALUES (30, 销售部, 广州);INSERT INTO dept VALUES (40, 财务部, 深圳);CREATE TABLE salgrade (grade int(11) NOT NULL DEFAULT 0,losal decimal(7,2) DEFAULT NULL,hisal decimal(7,2) DEFAULT NULL)INSERT INTO salgrade VALUES (1, 7000.00, 12000.00);INSERT INTO salgrade VALUES (2, 12010.00, 14000.00);INSERT INTO salgrade VALUES (3, 14010.00, 20000.00);INSERT INTO salgrade VALUES (4, 20010.00, 30000.00);INSERT INTO salgrade VALUES (5, 30010.00, 99999.00);