1.导入部门表
SET FOREIGN_KEY_CHECKS=0;
-- Table structure for dept
DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
deptno int(11) NOT NULL,
dname varchar(30) DEFAULT NULL,
loc varchar(30) DEFAULT NULL,
PRIMARY KEY (deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Records of dept
INSERT INTO dept VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO dept VALUES ('40', 'OPERATIONS', 'BOSTON');
2.导入员工表
SET FOREIGN_KEY_CHECKS=0;
-- Table structure for emp
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
empno int(11) NOT NULL,
ename varchar(30) DEFAULT NULL,
job varchar(30) DEFAULT NULL,
mgr int(11) DEFAULT NULL,
hiredate datetime DEFAULT NULL,
sal decimal(10,2) DEFAULT NULL,
comm decimal(10,2) DEFAULT NULL,
deptno int(11) DEFAULT NULL,
PRIMARY KEY (empno),
KEY FK_DEPTNO (deptno),
CONSTRAINT FK_DEPTNO FOREIGN KEY (deptno) REFERENCES dept (deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Records of emp
INSERT INTO emp VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17 00:00:00', '800.00', null, '20');
INSERT INTO emp VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20 00:00:00', '1600.00', '300.00', '30');
INSERT INTO emp VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22 00:00:00', '1250.00', '500.00', '30');
INSERT INTO emp VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02 00:00:00', '2975.00', null, '20');
INSERT INTO emp VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28 00:00:00', '1250.00', '1400.00', '30');
INSERT INTO emp VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01 00:00:00', '2850.00', null, '30');
INSERT INTO emp VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09 00:00:00', '2450.00', null, '10');
INSERT INTO emp VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19 00:00:00', '3000.00', null, '20');
INSERT INTO emp VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17 00:00:00', '5000.00', null, '10');
INSERT INTO emp VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08 00:00:00', '1500.00', '0.00', '30');
INSERT INTO emp VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23 00:00:00', '1100.00', null, '20');
INSERT INTO emp VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03 00:00:00', '950.00', null, '30');
INSERT INTO emp VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03 00:00:00', '3000.00', null, '20');
INSERT INTO emp VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23 00:00:00', '1300.00', null, '10');
3.练习题
use test001;
show tables;
1.列出至少有一个员工的所有部门
select dept.dname FROM dept GROUP BY deptno HAVING (SELECT COUNT(1) c FROM emp WHERE emp.deptno = dept.deptno ) > 3;
select d.dname from emp e,dept d where e.deptno = d.deptno group by d.deptno having count(1) >1 ;
select d.dname from emp right join dept d on emp.deptno = d.deptno group by d.dname having count(1) >1;
select ed.dname from (select e.ename,d.dname from emp e ,dept d where e.deptno = d.deptno) ed group by ed.dname having count(ed.dname) >3;
2.列出薪金比 smith多多员工
select ename from emp where sal > (select sal from emp where ename='ALLEN');
3.列出员工姓名和上级姓名
select e1.ename 员工,e2.ename 经理 from emp e1, emp e2 where e1.mgr = e2.empno;
4.列出受雇日期早于直接上级的所有员工
select e1.ename 员工,e1.hiredate,e2.ename,e2.hiredate from emp e1,emp e2 where e1.mgr = e2.empno and e1.hiredate<e2.hiredate;
select e1.ename 员工 from emp e1,emp e2 where e1.mgr = e2.empno and e1.hiredate<e2.hiredate;
select e1.ename 员工 from emp e1 join emp e2 on e1.mgr = e2.empno and e1.hiredate<e2.hiredate;;
5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select d.dname,emp.* from emp right join dept d on emp.deptno = d.deptno order by empno;
select dname,e.* from dept left join emp e on dept.deptno = e.deptno order by empno;
6.列出所有 clerk 岗位的姓名及其部门名称
select ename,dname from emp join dept d on emp.deptno = d.deptno and job='clerk';
7.列出最低薪金大于1500的各种工作
select job from emp group by job having max(sal) >1500;
8.列出在部门sales工作的员工姓名
select ename from emp where deptno = (select deptno from dept where dname='SALES');
select ename from emp inner join dept d on emp.deptno = d.deptno where dname='sales';
9.列出薪金高于公司平均薪金的所有员工
select * from emp where sal > (select avg(sal) from emp);
10.列出与 scott 从事相同工作的所有员工，排除自己
select * from emp where job = (select job from emp where ename='scott') and ename <> 'scott';
11. 列出薪金等于部门30中员工的薪金的所有员工姓名和薪金
select ename,sal,deptno from emp where sal <any (select sal from emp where deptno =30) and deptno <> 30;
12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
select ename,sal from emp where sal > (select max(sal) from emp where deptno = 30);
13.列出在每个部门工作的员工数量，平均工资，平均服务期限
select avg(sal) 平均工资,count(1) 员工数量, avg(datediff(sysdate(),hiredate) / 365) 平均年限 from emp group by deptno;
14.列出所有员工的姓名，部门名称，工资
select ename,(select dname from dept where dept.deptno=emp.deptno) 部门,sal from emp order by sal;
select e.ename,d.dname,e.sal from emp e, dept d where e.deptno = d.deptno order by e.sal;
15.列出所有部门到详细信息和部门人数
select *,(select count(1) from emp where emp.deptno = dept.deptno group by dept.deptno) 人数 from dept;
