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;
	
	

第五题说明：先查出人员的全部跟门派中是不是掌门的信息，在增加备注是否是掌门。