sql基本训练

sql基本训练

一、基本款

CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
`empno` INT(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_dept(id,deptName,address) VALUES(1,'华山','华山');
INSERT INTO t_dept(id,deptName,address) VALUES(2,'丐帮','洛阳');
INSERT INTO t_dept(id,deptName,address) VALUES(3,'峨眉','峨眉山');
INSERT INTO t_dept(id,deptName,address) VALUES(4,'武当','武当山');
INSERT INTO t_dept(id,deptName,address) VALUES(5,'明教','光明顶');
INSERT INTO t_dept(id,deptName,address) VALUES(6,'少林','少林寺');

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010);

第一条：查询所有有部门的员工及其部门信息

第二条：列出所有用户，并显示其机构信息

第三条：列出所有部门，并显示其部门的员工信息

第四条：查询没有加入任何部门的员工

第五条：查询没有任何员工的部门

第六条：查询所有员工和所有部门

第七条：查询没有加入任何部门的员工，以及查询出部门下没有任何员工的部门

---

  第一条：查询所有有部门的员工及其部门信息
	  select *
	  from t_emp e
	  inner join t_dept d
	  on e.deptId=d.id
  第二条：列出所有用户，并显示其机构信息
      select *
	  from t_emp e
	  left join t_dept d
	  on e.deptId=d.id
  第三条：列出所有部门，并显示其部门的员工信息
      select *
	  from t_emp e
	  right join t_dept d
	  on e.deptId=d.id
  第四条：查询没有加入任何部门的员工
      select *
	  from t_emp e
	  left join t_dept d
	  on e.deptId=d.id
	  where d.id is null
  第五条：查询没有任何员工的部门
      select *
	  from t_emp e
	  right join t_dept d
	  on e.deptId=d.id
	  where e.deptId is null
	  
  第六条：查询所有员工和所有部门
      select *
	  from t_emp e
	  left join t_dept d
	  on e.deptId=d.id
	  
	  union
	  
	  select *
	  from t_emp e
	  right join t_dept d
	  on e.deptId=d.id
	  
	  为何会重复？因为union是纵向连接。
	  union all:不可以去重,要求：纵向连接的表的列数要保持一致，对应的列的类型保持一致。
	  union:可以去重,要求：纵向连接的表的列数要保持一致，对应的列的类型保持一致。
	  
    第七条：查询没有加入任何部门的员工，以及查询出部门下没有任何员工的部门
	
      select *
	  from t_emp e
	  left join t_dept d
	  on e.deptId=d.id
	  where d.id is null
      union 
      select *
	  from t_emp e
	  right join t_dept d
	  on e.deptId=d.id
	  where e.deptId is null

​

二、拓展

1、求各个门派对应的掌门人名称

2、求所有当上掌门人的平均年龄

3、求所有人，对应的掌门是谁？

    拓展1：求各个门派对应的掌门人名称
	       select t_dept.*,t_emp.name from t_dept left join t_emp on t_dept.ceo=t_emp.id
    拓展2：求所有当上掌门人的平均年龄
	       select avg(t_emp.age) from t_dept inner join t_emp on t_dept.ceo=t_emp.id;
    
	拓展3：求所有人，对应的掌门是谁？
		第一种写法：
		  select e.id,e.name,ee.name
		  from t_emp e
		  left join t_dept d
		  on e.deptId=d.id
		  left join t_emp ee
		  on d.ceo = ee.id
		第二种写法：先求出每个门派的掌门人名称，再求每个人对应的 掌门人名称
	
			select *
			from t_emp ee
			left join (
			  select d.id,d.deptName,e.name
			 from t_dept d
			 left join t_emp e
			 on d.ceo = e.id
			) tmp 
			on ee.deptId=tmp.id
		
		第三种写法：子查询:select 子查询，每个人对对应的掌门id,再用掌门id求掌门人名称	
         explain select e.id,e.name,(select name from t_emp where id = d.ceo) ceoname
          from 	t_emp e
          left join t_dept d
          on e.deptId=d.id		

       第四种写法：子查询:from 子查询，每个人对对应的掌门id,再用掌门id求掌门人名称	
		select tmp.id,tmp.name,ee.name
		from (
			  select e.id,e.name,d.ceo
				  from 	t_emp e
				  left join t_dept d
				  on e.deptId=d.id	
		)	tmp 
		left join t_emp ee 
		on tmp.ceo = ee.id