1.所有有门派的人员信息 SELECT * from t_emp,t_dept where t_emp.deptId = t_dept.id; select * from t_emp a inner join t_dept b on a.deptId=b.id; 2.列出所有用户，并显示其门派信息 select t_emp.name,IFNULL(t_dept.deptName,'没有门派') as '门派' from t_emp LEFT JOIN t_dept on t_emp.deptId = t_dept.id; 3.列出所有门派 SELECT * from t_dept ; 4.所有不入门派的人员 SELECT name FROM t_emp where deptId is null ; SELECT * FROM t_emp LEFT JOIN t_dept on t_emp.deptId = t_dept.id where t_dept.id is null; 5.所有没人入的门派 SELECT * FROM t_dept left JOIN t_emp on t_emp.deptId = t_dept.id where t_emp.deptId is null; select * from t_dept b left join t_emp a on a.deptId= b.id where a.deptId is null; 6.列出所有人员和门派的对照关系 select * from t_emp,t_dept where t_emp.deptId = t_dept.id; #MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法 #left join + union(可去除重复数据)+ right join SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id UNION SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id 7.列出所有没入派的人员和没人入的门派 SELECT * from t_emp left JOIN t_dept on t_emp.deptId = t_dept.id where t_dept.id is null UNION SELECT * FROM t_dept LEFT JOIN t_emp on t_emp.deptId = t_dept.id where t_emp.deptId is null; 求各个门派对应的掌门人名称: select t_emp.name FROM t_dept LEFT JOIN t_emp on t_emp.id = t_dept.CEO ; select a.name from t_dept b left join t_emp a on b.CEO=a.id; 求所有当上掌门人的平均年龄: SELECT AVG(age) from t_dept LEFT JOIN t_emp on t_emp.id = t_dept.CEO ; 作业: 求所有人物对应的掌门名称 select t_emp.name,c.name from t_emp LEFT JOIN (SELECT t_emp.name,t_emp.deptId as id from t_dept LEFT JOIN t_emp on t_emp.id = t_dept.CEO )c on c.id = t_emp.deptId; 1、列出自己的掌门比自己年龄小的人员 SELECT t_emp.name FROM t_emp,(SELECT age,t_emp.deptId,t_emp.name FROM t_emp,t_dept WHERE t_emp.id = t_dept.ceo)a where t_emp.age > a.age GROUP BY t_emp.deptId; select a.name,a.age,c.name ceoName,c.age ceoAge from t_emp a left join t_dept b on a.deptId=b.id left join t_emp c on b.CEO=c.id where a.age>c.age 2、列出所有年龄低于自己门派平均年龄的人员 SELECT AVG(age) as age,deptName,deptId FROM t_emp,t_dept where t_emp.deptId = t_dept.id GROUP BY deptId; SELECT name FROM t_emp,(SELECT AVG(age) as age,deptName,deptId FROM t_emp,t_dept where t_emp.deptId = t_dept.id GROUP BY deptId)a WHERE t_emp.deptId = a.deptId and a.age > t_emp.age; 3、列出至少有2个年龄大于40岁的成员的门派 SELECT deptName FROM t_dept LEFT JOIN t_emp on t_emp.deptId = t_dept.id where t_emp.age > 40 GROUP BY t_dept.deptName HAVING COUNT(0) >= 2; 4、至少有2位非掌门人成员的门派 先找出非掌门人门派的名单 SELECT deptName,t_dept.id FROM t_dept LEFT JOIN t_emp on t_dept.id = t_emp.deptId and t_dept.CEO <> t_emp.id GROUP BY id HAVING COUNT(0) >=2; 5、列出全部人员，并增加一列备注“是否为掌门” 如果是掌门人显示是 不是掌门人显示否 SELECT t_emp.name,CASE WHEN t_dept.id is null THEN '否' ELSE '是' END '是否掌门人' FROM t_emp LEFT JOIN t_dept on t_emp.id = t_dept.ceo; 6、列出全部门派，并增加一列备注“老鸟or菜鸟”，若门派的平均值年龄>50 显示“老鸟”，否则显示“菜鸟” SELECT deptName,IF(AVG(t_emp.age)>50,'老鸟','菜鸟') '老鸟or菜鸟' FROM t_dept LEFT JOIN t_emp on t_dept.id = t_emp.deptId GROUP BY deptName; 7、显示每个门派年龄最大的人 SELECT MAX(age),name FROM t_emp,t_dept WHERE t_emp.deptId = t_dept.id GROUP BY deptName; 8.显示每个门派年龄第二大的人 SET @rank=0; SET @last_deptid=0; SELECT a.deptid,a.name,a.age FROM( SELECT t.*, IF(@last_deptid=deptid,@rank:=@rank+1,@rank:=1) AS rk, @last_deptid:=deptid AS last_deptid FROM t_emp t ORDER BY deptid,age DESC )a WHERE a.rk=2; 第五题说明：先查出人员的全部跟门派中是不是掌门的信息，在增加备注是否是掌门。 -------------------新的数据库：
查询"01"课程比"02"课程成绩高的学生的信息及课程分数 select a.*,b.s_score as 01_score,c.s_score as 02_score from student a,score b,score c where a.s_id=b.s_id and a.s_id=c.s_id and b.c_id='01' and c.c_id='02' and b.s_score>c.s_score 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 SELECT AVG(score.s_score) as the_score,student.s_id,student.s_name FROM score ,student WHERE score.s_id = student.s_id GROUP BY student.s_id HAVING the_score > 60; 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 -- (包括有成绩的和无成绩的) SELECT AVG(score.s_score) as the_score,student.s_id,student.s_name FROM score left JOIN student on score.s_id = student.s_id GROUP BY student.s_id HAVING the_score < 60 UNION SELECT 0 as the_score,student.s_id,student.s_name FROM student LEFT JOIN score on score.s_id = student.s_id where score.s_id is null; 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 SELECT student.s_id,student.s_name,COUNT(score.c_id) as total_count_num,SUM(score.s_score) as sum_score FROM student LEFT JOIN score on score.s_id = student.s_id GROUP BY student.s_name,student.s_id; 查询"李"姓老师的数量 SELECT COUNT(t_id) as num FROM teacher where t_name like '李%'; 查询学过"张三"老师授课的同学的信息 SELECT teacher.t_name,teacher.t_id,c_name FROM teacher,course WHERE teacher.t_id = course.t_id and teacher.t_name = '张三'; SELECT s_name,t_name,c_name FROM student,score,(SELECT teacher.t_name,teacher.t_id,c_name FROM teacher,course WHERE teacher.t_id = course.t_id and teacher.t_name = '张三')c where score.s_id = student.s_id and c.t_id = score.c_id and c_id = '01'; 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 SELECT * FROM student,score a,score b where student.s_id = a.s_id and student.s_id = b.s_id and a.c_id = '01' and b.c_id = '02'; 询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 SELECT * FROM student where student.s_id in (SELECT s_id FROM score where c_id = '01') and student.s_id not in(SELECT s_id from score where c_id='02'); 查询学生的总成绩并进行排名 SELECT SUM(s_score) as sum,student.s_name from student left JOIN score on score.s_id = student.s_id GROUP BY score.s_id ORDER BY sum DESC;
