圣诞节的作文

 笔记：一、聚合函数：计数 最大值  最小值  平均数  求和1.计数 COUNT()  忽略NULL值方式1：COUNT(*)  *代表所有字段 一般用于查询表中共有多少条记录（实体）SELECT COUNT(*) zongshu FROM emp;方式2：计算某个字段中非NULL值的个数SELECT COUNT(ALL comm) FROM emp;-- all 可以省略方式3：去重计数  计算某列中不重复非NULL值的个数SELECT COUNT( DISTINCT job) FROM emp;2.最大值  MAX() 求某一列中的最大值，不分组的情况下不可与其他字段一起使用SELECT MAX(sal) FROM emp ;3.最小值  MIN()  不分组的情况下不可与其他字段一起使用SELECT  MIN(sal) FROM emp ;4.平均值  AVG()  不分组的情况下不可与其他字段一起使用SELECT AVG(sal) FROM emp;5.求和  SUM() 不分组的情况下不可与其他字段一起使用SELECT SUM(sal) FROM emp;练习：1.查询员工的总人数；SELECT COUNT(*) FROM emp;2.查询员工的平均工资；SELECT AVG(sal) FROM emp;3.查询文员的总人数；SELECT COUNT(*) FROM emp WHERE job='文员';4. 查询30号部门中文员的总工资；SELECT SUM(sal+IFNULL(comm,0)) FROM emp WHERE deptno=30 AND job ='文员';5.查询10号部门中员工的最低工资；SELECT MIN(sal) FROM emp WHERE deptno=10;6.查询入职时间在2001-2005的员工的最高工资SELECT MAX(sal) FROM emp WHERE hiredate BETWEEN '2001' AND '2005';7.查询2005年之前入职的员工的平均工资SELECT AVG(sal) FROM emp WHERE hiredate <'2005';8.查询2002年之后入职的员工的最高工资和最低工资和平均工资。SELECT MAX(sal) ,MIN(sal),AVG(sal) FROM emp  WHERE hiredate >'2002';9.查询30号部门的最高工资、最低工资、平均工资；SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE deptno =30;10.查询10号或20号部门的文员的最高工资、最低工资、平均工资；SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE (deptno =10 OR deptno =20 ) AND job ='文员';二、分组  GROUP BY分组计算时SELECT 语句中可以有分组字段和聚合函数，但不要放其他字段；SELECT deptno, AVG(sal),MAX(sal) ,MIN(sal) ,SUM(sal)FROM  emp WHERE sal >20000 GROUP BY deptno;HAVING 用户过滤聚合函数的值 只能用在分组后面SELECT deptno ,MIN(sal),MAX(sal) FROM emp GROUP BY deptno HAVING MIN(sal)>8000 AND MAX()<50000;SELECT deptno ,MIN(sal) minSal ,MAX(sal) b FROM emp GROUP BY deptno HAVING minSal>8000 AND b<50000;使用多个字段分组：计算每个部门中每个岗位的平均工资：SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno ,job ;分组练习：-- 1.查询各个部门的最高工资、最低工资、平均工资。SELECT deptno,MAX(sal),MIN(sal),AVG(sal) FROM emp GROUP BY deptno;-- 2.查询各个职位的平均工资 降序排列SELECT job,AVG(sal) avgSal FROM emp GROUP BY job ORDER BY avgSal DESC ;-- 3.查询平均工资大于10000的岗位SELECT job ,AVG(sal) avgSal FROM emp GROUP BY job HAVING avgSal >10000 ;-- 4.查询每个部门的人数SELECT deptno ,COUNT(ename) FROM emp GROUP BY deptno;-- 5.查询人数大于5的部门SELECT deptno ,COUNT(ename) qty FROM emp GROUP BY deptno HAVING qty>5;-- 6.查询部门人数小于3的部门的平均工资、最高工资、最低工资SELECT deptno,AVG(sal),MAX(sal),MIN(sal) FROM emp  GROUP BY deptno HAVING COUNT(*)<=3;-- 7.查询各个部门中工资大于10000的人数 降序显示SELECT deptno ,COUNT(empno) qty FROM emp WHERE sal>10000 GROUP BY deptno ORDER BY qty DESC ;-- 8.查询各个岗位中工资大于10000的人数 升序显示-- 9.查询平均工资大于10000的前两个部门。SELECT AVG(sal) ,deptno FROM emp  GROUP BY deptno HAVING AVG(sal)>10000 LIMIT 2;-- 10.查询每个部门中没有津贴的人数。SELECT deptno,COUNT(*) FROM emp WHERE comm IS NULL GROUP BY deptno三、组合查询 - 子查询1.单行单列  跟在WHERE子句后，用于判断的条件-- 比李世民工资高的所有员工的信息SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='李世民')2.单行多列  跟在WHERE子句后-- 查询和李世民工资及职位都一样的人的所有信息SELECT * FROM emp WHERE (job,sal) IN (SELECT job ,sal FROM emp WHERE ename='李世民')AND ename !='李世民';3.多行单列-- 查询大于30号部门中任意一个员工工资的人的所有信息-- any:任意的意思SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30);SELECT * FROM emp WHERE sal >  (SELECT MIN(sal) FROM emp WHERE deptno=30);-- 查询大于30号部门中所有人工资的员工信息SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);4.多行多列 用在FROM子句后面 作为虚表使用SELECT e.ename FROM (SELECT * FROM emp WHERE deptno =30) e WHERE e.comm IS NULL;组合查询-- 合并结果集  上下两条查询语句的字段个数要一样；UNION --去除重复数据的合并；SELECT ename,sal FROM emp WHERE deptno=10 UNION SELECT  ename,sal FROM emp WHERE deptno=20;UNION ALL  -- 不去除重复记录SELECT ename,sal  FROM emp WHERE deptno=10 UNION ALL SELECT  ename,sal FROM emp WHERE deptno=20;四、链接1. 内连接 去除笛卡尔积：去除匹配错误的数据；1.1 mysql方言方式：SELECT * FROM emp ,dept  WHERE emp.deptno= dept.deptno AND dept.dname='学工部' ORDER BY emp.sal DESC ;1.2 标准SQL方式：SELECT * FROM emp INNER JOIN dept  ON emp.deptno= dept.deptno;1.3自然连接：SELECT * FROM emp NATURAL JOIN dept;2.外连接2.1 左外连接 LEFT OUTER JOIN  -- out可以省略,以左边表的行数为准，若右边表没有与之匹配的数据，那么用null值填充SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno =d.deptno;SELECT * FROM dept d LEFT OUTER JOIN emp e ON e.deptno =d.deptno;2.2 右外连接  RIGHT OUTER JOIN  与左外连接意思相反SELECT * FROM emp e RIGHT  JOIN dept d ON e.deptno =d.deptno;二。1：查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。2：薪水大于12000的雇员，按照部门编号进行分组，分组后的平均薪水必须大于15000，查询各分组的平均工资，按照工资的倒序进行排列3:查询每个雇员和其所在的部门名4.查询每个雇员姓名及其工资所在的等级5：查询雇员名第2个字不是‘中‘的雇员的姓名、所在的部门名、工资所在的等级。6：查询每个雇员和其经理的姓名-- 7：查询每个雇员和其经理的姓名（包括公司老板本身（他上面没有经理））SELECT e1.ename,IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;-- 8：查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）SELECT e.ename,d.dname FROM dept d  LEFT JOIN emp e  ON  d.deptno=e.deptno;-- 9：查询每个部门中工资最高的人的姓名、薪水和部门编号SELECT * FROM emp WHERE (deptno,sal)IN (SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno);SELECT * FROM emp e1,(SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal = e2.sal;-- 10：查询每个部门平均工资所在的等级SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(SELECT deptno,AVG(sal) avgSal  FROM emp GROUP BY deptno) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;-- 11：查询每个部门内平均的薪水等级SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(SELECT deptno,AVG(sal+IFNULL(comm,0)) avgSal  FROM emp GROUP BY deptno) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;-- 12：查询雇员中有哪些人是经理人-- 13：不准用max函数，求雇员表中薪水的最高值。SELECT * FROM emp ORDER BY sal+IFNULL(comm,0)  DESC LIMIT 0,1;-- 14：平均薪水最高的部门的部门编号SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1-- 15：求平均薪水最高的部门的部门名称SELECT emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1-- 16：查询手下有员工的领导的信息SELECT DISTINCT IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;-- mysql联表查询练习：-- 1.查询销售部工资大于20000的所有人；SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.dname='销售部' AND emp.sal >20000;-- 2.查询每个部门的人数，要求显示部门名称；SELECT dept.deptno,dept.dname,dept.loc,IFNULL(d1.co,0) FROM dept LEFT JOIN (SELECT deptno,COUNT(*) co FROM emp GROUP BY deptno) d1  ON dept.deptno=d1.deptno;-- 3.查询每个部门的最高工资，平均工资，最低工资 ，要求显示部门名称；SELECT * FROM dept LEFT JOIN (SELECT deptno ,MAX(sal),AVG(sal) ,MIN(sal) FROM emp GROUP BY deptno) d2  ON dept.deptno = d2.deptno-- 4.查询教研部中入职时间最早的员工信息，要显示部门名称，姓名，入职时间；SELECT * FROM emp,dept WHERE emp.deptno =dept.deptno AND dept.dname='教研部'  ORDER BY hiredate  LIMIT 1;-- 5.查询当前没有员工的部门信息；SELECT * FROM (SELECT dept.deptno,dept.dname ,dept.loc  ,emp.empno FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno) a WHERE a.empno IS NULL;三。课后作业：-1.学生表Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--创建测试数据create table Student(Sid varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));insert into Student values('01' , '赵雷' , '1990-01-01' , '男');insert into Student values('02' , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-05-20' , '男');insert into Student values('04' , '李云' , '1990-08-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '女');insert into Student values('06' , '吴兰' , '1992-03-01' , '女');insert into Student values('07' , '郑竹' , '1989-07-01' , '女');insert into Student values('08' , '王菊' , '1990-01-20' , '女');--2.课程表Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 教师编号create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));insert into Course values('01' , '语文' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03');--3.教师表Teacher(Tid,Tname) --Tid 教师编号,Tname 教师姓名create table Teacher(Tid varchar(10),Tname varchar(10));insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');--4.成绩表SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '02' , 30);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);课后作业：-1.学生表Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--创建测试数据create table Student(Sid varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));insert into Student values('01' , '赵雷' , '1990-01-01' , '男');insert into Student values('02' , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-05-20' , '男');insert into Student values('04' , '李云' , '1990-08-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '女');insert into Student values('06' , '吴兰' , '1992-03-01' , '女');insert into Student values('07' , '郑竹' , '1989-07-01' , '女');insert into Student values('08' , '王菊' , '1990-01-20' , '女');--2.课程表Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 教师编号create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));insert into Course values('01' , '语文' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03');--3.教师表Teacher(Tid,Tname) --Tid 教师编号,Tname 教师姓名create table Teacher(Tid varchar(10),Tname varchar(10));insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');--4.成绩表SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '02' , 30);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩SELECT st.sid,st.sname,AVG(sc.score) avgScore FROM student st ,sc WHERE st.sid=sc.Sid GROUP BY sid  HAVING avgScore>60;--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩SELECT st.sid,st.sname ,COUNT(sc.cid),SUM(sc.score) FROM student st ,sc WHERE st.sid=sc.sid GROUP BY st.sid;--6、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩SELECT * FROM student st,sc,course co,teacher  te WHEREst.sid=sc.sid AND sc.cid=co.cid AND co.tid=te.tid AND te.tname='张三' ORDER BY sc.score DESC LIMIT 1;--7、查询本周过生日的学生SELECT  * FROM student WHERE  WEEKOFYEAR(DATE_ADD(sage,INTERVAL YEAR(NOW())-YEAR(sage) YEAR))=WEEKOFYEAR(NOW()) ;--8、查询下周过生日的学生SELECT  * FROM student WHERE  WEEKOFYEAR(DATE_ADD(sage,INTERVAL YEAR(NOW())-YEAR(sage) YEAR))=WEEKOFYEAR(NOW())+1 ;--9、查询本月过生日的学生SELECT * FROM student WHERE MONTH(sage) =MONTH(NOW());--10、查询下月过生日的学生SELECT * FROM student WHERE MONTH(sage) =MONTH(NOW())+1;--11、查询每门功成绩最好的前两名SELECT  sid,cid,scoreFROM sc r1WHERE  (SELECT COUNT(*) FROM sc r2 WHERE r2.cid=r1.cid AND r1.score <= r2.score) <=2 ORDER BY cid ;--12、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列SELECT  cid,COUNT(*) qty FROM sc GROUP BY cid HAVING qty>5 ORDER BY qty DESC ;--13、检索至少选修两门课程的学生学号SELECT sid ,COUNT(*) FROM sc GROUP BY sid HAVING COUNT(*) >=2;--14、查询选修了全部课程的学生信息SELECT sid ,COUNT(*) aa  FROM sc GROUP BY sid HAVING aa=(SELECT COUNT(*) FROM course);--15、查询各学生的年龄SELECT * ,YEAR(NOW())-YEAR(sage) FROM student--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数SELECT  a.sid,a.score,b.score FROM (SELECT st.sid,sc.cid,sc.score FROM student st ,sc WHERE st.sid=sc.sid AND sc.Cid=01 ) a,(SELECT st.sid,sc.cid,sc.score FROM student st ,sc WHERE st.sid=sc.sid AND sc.cid=02) b  WHERE a.sid=b.sid  AND a.score >b.score;--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩SELECT DISTINCT s1.Sid,s1.Sname,s2.avgs FROM student s1 INNER JOIN(SELECT sid,AVG(score)avgs FROM sc GROUP BY sid HAVING avgs>=60)s2 ON s1.Sid=s2.Sid ;--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩SELECT DISTINCT s1.Sid,s1.Sname,s2.avgs FROM student s1 INNER JOIN(SELECT sid,AVG(score)avgs FROM sc GROUP BY sid HAVING avgs<60)s2 ON s1.Sid=s2.Sid ;--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩SELECT DISTINCT s1.Sid,s1.Sname,s2.cc,s2.ss FROM student s1 INNER JOIN(SELECT sid,COUNT(cid)cc,SUM(score)ss FROM sc GROUP BY sid) s2 ON s1.Sid=s2.Sid;--6、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩SELECT DISTINCT * FROM student s1 INNER JOIN (SELECT * FROM sc WHERE cid=01 ORDER BY score DESC LIMIT 2) s2 ON s1.sid=s2.sid;--7、查询本周过生日的学生--8、查询下周过生日的学生--9、查询本月过生日的学生--10、查询下月过生日的学生--11、查询每门功成绩最好的前两名(SELECT *FROM sc WHERE cid =01 ORDER BY score DESC LIMIT 2)UNION(SELECT *FROM sc WHERE cid =02 ORDER BY score DESC LIMIT 2)UNION(SELECT *FROM sc WHERE cid =03 ORDER BY score DESC LIMIT 2);--12、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列SELECT cid,COUNT(sid) cs FROM sc GROUP BY cid HAVING cs>5 ORDER BY cs DESC;--13、检索至少选修两门课程的学生学号SELECT sid FROM sc GROUP BY sid HAVING COUNT(cid)>=2;--14、查询选修了全部课程的学生信息SELECT * FROM Student s1 INNER JOIN (SELECT sid,COUNT(cid) cc FROM sc GROUP BY sid HAVING cc=3) s2 ON s1.sid=s2.sid--15、查询各学生的年龄--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数四。1：查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。2：薪水大于12000的雇员，按照部门编号进行分组，分组后的平均薪水必须大于15000，查询各分组的平均工资，按照工资的倒序进行排列3:查询每个雇员和其所在的部门名4.查询每个雇员姓名及其工资所在的等级5：查询雇员名第2个字不是‘中‘的雇员的姓名、所在的部门名、工资所在的等级。6：查询每个雇员和其经理的姓名-- 7：查询每个雇员和其经理的姓名（包括公司老板本身（他上面没有经理））    SELECT e1.ename,IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;-- 8：查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）  SELECT e.ename,d.dname FROM dept d  LEFT JOIN emp e  ON  d.deptno=e.deptno;-- 9：查询每个部门中工资最高的人的姓名、薪水和部门编号    SELECT * FROM emp WHERE (deptno,sal)IN (SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno);    SELECT * FROM emp e1,(SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal = e2.sal;-- 10：查询每个部门平均工资所在的等级SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(SELECT deptno,AVG(sal) avgSal  FROM emp GROUP BY deptno) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;-- 11：查询每个部门内平均的薪水等级  SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(SELECT deptno,AVG(sal+IFNULL(comm,0)) avgSal  FROM emp GROUP BY deptno) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;-- 12：查询雇员中有哪些人是经理人-- 13：不准用max函数，求雇员表中薪水的最高值。  SELECT * FROM emp ORDER BY sal+IFNULL(comm,0)  DESC LIMIT 0,1;-- 14：平均薪水最高的部门的部门编号    SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1-- 15：求平均薪水最高的部门的部门名称    SELECT emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1-- 16：查询手下有员工的领导的信息        SELECT DISTINCT IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;-- mysql联表查询练习：-- 1.查询销售部工资大于20000的所有人；    SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.dname='销售部' AND emp.sal >20000;-- 2.查询每个部门的人数，要求显示部门名称；SELECT dept.deptno,dept.dname,dept.loc,IFNULL(d1.co,0) FROM dept LEFT JOIN (  SELECT deptno,COUNT(*) co FROM emp GROUP BY deptno) d1  ON dept.deptno=d1.deptno;-- 3.查询每个部门的最高工资，平均工资，最低工资 ，要求显示部门名称；SELECT * FROM dept LEFT JOIN (  SELECT deptno ,MAX(sal),AVG(sal) ,MIN(sal) FROM emp GROUP BY deptno) d2  ON dept.deptno = d2.deptno-- 4.查询教研部中入职时间最早的员工信息，要显示部门名称，姓名，入职时间；    SELECT * FROM emp,dept WHERE emp.deptno =dept.deptno AND dept.dname='教研部'  ORDER BY hiredate  LIMIT 1;-- 5.查询当前没有员工的部门信息；SELECT * FROM (  SELECT dept.deptno,dept.dname ,dept.loc  ,emp.empno FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno) a WHERE a.empno IS NULL;五。课后作业：-1.学生表Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--创建测试数据create table Student(Sid varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));insert into Student values('01' , '赵雷' , '1990-01-01' , '男');insert into Student values('02' , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-05-20' , '男');insert into Student values('04' , '李云' , '1990-08-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '女');insert into Student values('06' , '吴兰' , '1992-03-01' , '女');insert into Student values('07' , '郑竹' , '1989-07-01' , '女');insert into Student values('08' , '王菊' , '1990-01-20' , '女');--2.课程表Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 教师编号create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));insert into Course values('01' , '语文' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03');--3.教师表Teacher(Tid,Tname) --Tid 教师编号,Tname 教师姓名create table Teacher(Tid varchar(10),Tname varchar(10));insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');--4.成绩表SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '02' , 30);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);课后作业：-1.学生表Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--创建测试数据create table Student(Sid varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));insert into Student values('01' , '赵雷' , '1990-01-01' , '男');insert into Student values('02' , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-05-20' , '男');insert into Student values('04' , '李云' , '1990-08-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '女');insert into Student values('06' , '吴兰' , '1992-03-01' , '女');insert into Student values('07' , '郑竹' , '1989-07-01' , '女');insert into Student values('08' , '王菊' , '1990-01-20' , '女');--2.课程表Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 教师编号create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));insert into Course values('01' , '语文' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03');--3.教师表Teacher(Tid,Tname) --Tid 教师编号,Tname 教师姓名create table Teacher(Tid varchar(10),Tname varchar(10));insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');--4.成绩表SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '02' , 30);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩SELECT st.sid,st.sname,AVG(sc.score) avgScore FROM student st ,sc WHERE st.sid=sc.Sid GROUP BY sid  HAVING avgScore>60;--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩SELECT st.sid,st.sname ,COUNT(sc.cid),SUM(sc.score) FROM student st ,sc WHERE st.sid=sc.sid GROUP BY st.sid;--6、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩SELECT * FROM student st,sc,course co,teacher  te WHERE st.sid=sc.sid AND sc.cid=co.cid AND co.tid=te.tid AND te.tname='张三' ORDER BY sc.score DESC LIMIT 1;--7、查询本周过生日的学生SELECT  * FROM student WHERE  WEEKOFYEAR(DATE_ADD(sage,INTERVAL YEAR(NOW())-YEAR(sage) YEAR))=WEEKOFYEAR(NOW()) ;--8、查询下周过生日的学生SELECT  * FROM student WHERE  WEEKOFYEAR(DATE_ADD(sage,INTERVAL YEAR(NOW())-YEAR(sage) YEAR))=WEEKOFYEAR(NOW())+1 ;--9、查询本月过生日的学生SELECT * FROM student WHERE MONTH(sage) =MONTH(NOW());--10、查询下月过生日的学生SELECT * FROM student WHERE MONTH(sage) =MONTH(NOW())+1;--11、查询每门功成绩最好的前两名SELECT  sid,cid,scoreFROM sc r1     WHERE  (SELECT COUNT(*) FROM sc r2 WHERE r2.cid=r1.cid AND r1.score <= r2.score) <=2 ORDER BY cid ;--12、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 SELECT  cid,COUNT(*) qty FROM sc GROUP BY cid HAVING qty>5 ORDER BY qty DESC ;--13、检索至少选修两门课程的学生学号SELECT sid ,COUNT(*) FROM sc GROUP BY sid HAVING COUNT(*) >=2;--14、查询选修了全部课程的学生信息SELECT sid ,COUNT(*) aa  FROM sc GROUP BY sid HAVING aa=(SELECT COUNT(*) FROM course);--15、查询各学生的年龄SELECT * ,YEAR(NOW())-YEAR(sage) FROM student--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数SELECT  a.sid,a.score,b.score FROM (SELECT st.sid,sc.cid,sc.score FROM student st ,sc WHERE st.sid=sc.sid AND sc.Cid=01 ) a,(SELECT st.sid,sc.cid,sc.score FROM student st ,sc WHERE st.sid=sc.sid AND sc.cid=02) b  WHERE a.sid=b.sid  AND a.score >b.score;--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩SELECT DISTINCT s1.Sid,s1.Sname,s2.avgs FROM student s1 INNER JOIN(SELECT sid,AVG(score)avgs FROM sc GROUP BY sid HAVING avgs>=60)s2 ON s1.Sid=s2.Sid ;--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩SELECT DISTINCT s1.Sid,s1.Sname,s2.avgs FROM student s1 INNER JOIN(SELECT sid,AVG(score)avgs FROM sc GROUP BY sid HAVING avgs<60)s2 ON s1.Sid=s2.Sid ;--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩SELECT DISTINCT s1.Sid,s1.Sname,s2.cc,s2.ss FROM student s1 INNER JOIN(SELECT sid,COUNT(cid)cc,SUM(score)ss FROM sc GROUP BY sid) s2 ON s1.Sid=s2.Sid;--6、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩SELECT DISTINCT * FROM student s1 INNER JOIN (SELECT * FROM sc WHERE cid=01 ORDER BY score DESC LIMIT 2) s2 ON s1.sid=s2.sid;--7、查询本周过生日的学生--8、查询下周过生日的学生--9、查询本月过生日的学生--10、查询下月过生日的学生--11、查询每门功成绩最好的前两名(SELECT *FROM sc WHERE cid =01 ORDER BY score DESC LIMIT 2)UNION(SELECT *FROM sc WHERE cid =02 ORDER BY score DESC LIMIT 2)UNION(SELECT *FROM sc WHERE cid =03 ORDER BY score DESC LIMIT 2);--12、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列SELECT cid,COUNT(sid) cs FROM sc GROUP BY cid HAVING cs>5 ORDER BY cs DESC;--13、检索至少选修两门课程的学生学号SELECT sid FROM sc GROUP BY sid HAVING COUNT(cid)>=2;--14、查询选修了全部课程的学生信息SELECT * FROM Student s1 INNER JOIN (SELECT sid,COUNT(cid) cc FROM sc GROUP BY sid HAVING cc=3) s2 ON s1.sid=s2.sid--15、查询各学生的年龄--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数