MySQL查询练习

# MySQL查询练习
基于emp/dept表，基本说明：
- emp员工表(
empno员工号, ename员工姓名, job工作, mgr上级编号,
hiredate受雇日期, sal薪金, comm佣金, deptno部门编号);
- dept部门表(deptno部门编号, dname部门名称, loc地点)；

# 数据准备
- 当一个表中有外键约束时，先要创建外键引用的那个表
- 这里应先创建dept，然后创建emp

	drop table if exists emp;
	create table emp(
		empno int(4) not null,
		ename varchar(10) default null,
		job varchar(9) default null,
		mgr int(4) default null,
		hiredate date default null,
		sal int(7) default null,
		comm int(2) default null,
		deptno int(2) default null,
		primary key (empno),
		key fk_deptno (deptno),
		constraint fk_deptno foreign key (deptno) references dept (deptno)
	)engine=innodb default charset=utf8;
	
	insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values
	(7369, 'smith', 'clerk', 7902, '1980-12-17', 800, null, 20),
	(7499, 'allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30),
	(7521, 'ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30),
	(7566, 'jones', 'manager', 7839, '1981-04-02', 2975, null, 30),
	(7654, 'martin', 'salesman', 7698, '1981-09-28', 1250, 1400, 30),
	(7698, 'blake', 'manager', 7839, '1981-05-01', 2850, null, 30),
	(7782, 'clark', 'manager', 7839, '1981-06-09', 2450, null, 10),
	(7788, 'scott', 'analyst', 7566, '1987-04-19', 3000, null, 20),
	(7839, 'king', 'president', null, '1981-11-17', 5000, null, 10),
	(7844, 'turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30),
	(7876, 'adams', 'clerk', 7788, '1987-05-23', 1100, null, 20),
	(7900, 'james', 'clerk', 7698, '1981-12-03', 950, null, 30),
	(7902, 'ford', 'analyst', 7566, '1981-12-03', 3000, null, 20),
	(7934, 'miller', 'clerk', 7782, '1982-01-23', 1300, null, 10);
	
	
	drop table if exists dept;
	create table dept(
		deptno int(2) not null,
		dname varchar(14) default null,
		loc varchar(13) default null,
		primary key (deptno)
	)engine=innodb default charset=utf8;
	
	insert into dept(deptno, dname, loc) values
	(10, 'accounting', 'new york'),
	(20, 'research', 'dallas'),
	(30, 'sales', 'chicago'),
	(40, 'operations', 'boston');

# 练习题

## 1. 列出至少有一个员工的所有部门
-- 联结
select dept.deptno,dept.dname,count(*) as num_emp
	from emp left outer join dept on emp.deptno = dept.deptno
    group by emp.deptno
    having count(*) >= 0;

-- 子查询
select deptno, count(*) as num_emp 
    from emp group by deptno;

select deptno, dname 
	from dept where deptno in (
		select deptno
		from emp group by deptno);

## 2. 列出薪金比smith多的所有员工
select max(emp.sal) from emp where emp.ename = 'smith';
select emp.sal from emp where emp.ename = 'smith';
-- 分析：有两个smith怎么办？唯一化：如增加约束所属部门，或求聚合如最大、平均
-- 子查询

select emp.empno, emp.ename, emp.sal
	from emp
    where emp.sal > (select max(emp.sal) from emp where emp.ename = 'smith');

## 3. 列出所有员工的姓名及其直接上级的姓名
select e1.ename as emp_name, e2.ename as mgr_name
	from emp as e1 left outer join emp as e2
    on e1.mgr = e2.empno;

-- ?? 子查询
select ename, (select ename from emp where empno in (a.mgr)) from emp a;

## 4. 列出受雇日期早于其直接上级的所有员工
select e1.ename as emp_name, e1.hiredate, e2.ename as mgr_name, e2.hiredate
	from emp as e1 left outer join emp as e2
    on e1.mgr = e2.empno
    where e1.hiredate < e2.hiredate;

-- 子查询（可以对两列进行比较）
select empno, ename from emp as a where hiredate<(
		select hiredate from emp where empno in (a.mgr));
        
## 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select dept.dname, emp.ename
	from emp left outer join dept on emp.deptno = dept.deptno
union		# 同时列出那些没有员工的部门
select dept.dname, emp.ename
	from dept left outer join emp on emp.deptno = dept.deptno;

## 6．列出所有“CLERK”（办事员）的姓名及其部门名称
select emp.ename, dept.dname, emp.job
	from emp left outer join dept on emp.deptno = dept.deptno
    where emp.job like 'clerk';

## 7. 列出最低薪金大于1500的各种工作
select distinct emp.job, emp.sal
	from emp where emp.sal > 1500;
    
## 8. 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
select emp.ename, dept.dname
	from emp left outer join dept on emp.deptno = dept.deptno
    where dept.dname = 'sales';

## 9. 列出薪金高于公司平均薪金的所有员工
select emp.ename, emp.sal
	from emp where emp.sal > (select avg(sal) from emp);
-- 平均工资：select avg(sal) from emp;

## 10. 列出与“SCOTT”从事相同工作的所有员工
select emp.ename, emp.job
	from emp where emp.job like (select job from emp where emp.ename like 'scott');

## 11. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
select emp.ename, emp.sal
	from emp where emp.sal in (select sal from emp where deptno = 30);

## 12. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
select emp.ename, emp.sal
	from emp where emp.sal > (select max(sal) from emp where deptno = 30);

## 13. 列出在每个部门工作的员工数量、平均工资和平均服务期限          
select dept.dname, count(*), avg(emp.sal)
	from emp left outer join dept on emp.deptno = dept.deptno
    group by emp.deptno;

## 14. 列出所有员工的姓名、部门名称和工资
select dept.dname, emp.ename, emp.sal
	from emp left outer join dept on emp.deptno = dept.deptno;

## 15. 列出所有部门的详细信息和部门人数
select dept.*, count(*)
	from dept right outer join emp on dept.deptno = emp.deptno
    group by emp.deptno;

## 16. 列出各种工作的最低工资
select emp.job, min(sal) 
	from emp group by emp.job;
    
## 17. 列出各个部门的MANAGER（经理）的最低薪金
select emp.deptno, dept.dname, min(sal)
	from emp left outer join dept on dept.deptno = emp.deptno where job = 'manager'
    group by deptno;

## 18. 列出所有员工的年工资,按年薪从高到低排序
select (12*sal) as annuary_sal from emp order by annuary_sal desc;

## TIPS：
-- 先写出要查询的信息：select xx, xx from
-- 再联结各种信息所在的表，在过滤，分组，获得所需的查询信息！！
-- 或通过子查询！！