sql语句练习

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;