JavaSE模块
mysql开发
sql文件(执行不了删除前面三行)
drop TABLE emp;
drop TABLE dept;
drop TABLE salgrade;
create table dept
(
deptno integer(4) not null,
dname varchar(14),
loc varchar(13)
);
alter table dept add constraint pk_dept primary key (deptno);
create table emp
(
empno integer(4) not null,
ename varchar(10),
job varchar(9),
mgr integer(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno integer(2)
);
alter table emp add constraint pk_emp primary key (empno);
alter table emp add constraint fk_deptno foreign key (deptno) references dept (deptno);
create table salgrade
(
grade integer(1),
losal decimal(7,2),
hisal decimal(7,2)
);
insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');
insert into SALGRADE (GRADE, LOSAL, HISAL) values (1, 700, 1200);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (5, 3001, 9999);
-- 下面要特别注意,%d-%m-%Y 里的 d 和 m 一定要小写
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980', '%d-%m-%Y'), 800.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-02-1981', '%d-%m-%Y'), 1600.00, 300.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-02-1981', '%d-%m-%Y'), 1250.00, 500.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('02-04-1981', '%d-%m-%Y'), 2975.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981', '%d-%m-%Y'), 1250.00, 1400.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('01-05-1981', '%d-%m-%Y'), 2850.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('09-06-1981', '%d-%m-%Y'), 2450.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, STR_TO_DATE('19-04-1987', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, STR_TO_DATE('17-11-1981', '%d-%m-%Y'), 5000.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('08-09-1981', '%d-%m-%Y'), 1500.00, 0.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, STR_TO_DATE('23-05-1987', '%d-%m-%Y'), 1100.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 950.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-01-1982', '%d-%m-%Y'), 1300.00, null, 10);
03.多表查询
3.5练习
-
写一个查询,显示所有员工姓名,部门编号,部门名称。
select ename,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno;
-
写一个查询,显示所有工作在CHICAGO并且奖金不为空的员工姓名,工作地点,奖金
select ename,loc,comm from emp,dept where comm is not Null and loc='CHICAGO';
-
写一个查询,显示所有姓名中含有A字符的员工姓名,工作地点。
select emp.ename,dept.loc from emp,dept where ename like '%A%';
3.8练习
-
查询每个员工的编号,姓名,工资,工资等级,所在工作城市,按照工资等级进行升序排序。
select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.loc from emp,dept,salgrade where (emp.sal between salgrade.losal and salgrade.hisal) and emp.deptno=dept.deptno order by salgrade.grade asc;
3.9练习
-
查询所有工作在NEW YORK和CHICAGO的员工姓名,员工编号,以及他们的经理姓名,经理编号。
select w.ename,w.empno,m.ename,m.empno from emp w,emp m,dept where loc in('NEW YORK','CHICAGO') and w.mgr=m.empno and w.deptno=dept.deptno and m.deptno=dept.deptno;
3.12课后作业
-
显示员工SMITH的姓名,部门名称,直接上级名称
select w.ename,d.dname,m.ename from emp w,emp m,dept d where w.ename='SMITH' and w.mgr=m.empno and w.deptno=d.deptno;
-
显示员工姓名,部门名称,工资,工资级别,要求工资级别大于4级。
select ename,dname,sal,grade from emp,dept,salgrade where sal>3000 and emp.deptno=dept.deptno and grade>4;
-
显示员工KING和FORD管理的员工姓名及其经理姓名。
KING
select w.ename,m.ename from emp w,emp m where m.ename='KING' and w.mgr=m.empno;
FORD
select w.ename,m.ename from emp w,emp m where m.ename='FORD' and w.mgr=m.empno;
-
显示员工姓名,参加工作时间,经理名,参加工作时间,要求参加时间比经理早。
select w.ename,w.hiredate,m.ename,m.hiredate from emp w,emp m where w.mgr=m.empno and w.hiredate<m.hiredate;
04高级查询
4.3练习
-
查询部门20的员工,每个月的工资总和及平均工资。
select sum(sal),avg(sal) from emp where deptno=20;
-
查询工作在CHICAGO的员工人数,最高工资及最低工资。
select count(*),max(sal),min(sal) from emp,dept where loc='CHICAGO' and emp.deptno=dept.deptno;
-
查询员工表中一共有几种岗位类型。
select count(distinct job) from emp;
4.4练习
-
查询每个部门的部门编号,部门名称,部门人数,最高工资,最低工资,工资总和,平均工资。
select d.deptno,dname,count(d.deptno),max(sal),min(sal),sum(sal),avg(sal) from emp e,dept d where e.deptno=d.deptno group by d.deptno;
-
查询每个部门,每个岗位的部门编号,部门名称,岗位名称,部门人数,最高工资,最低工资,工资总和,平均工资。
select e.job,d.deptno,dname,count(e.job),max(sal),min(sal),sum(sal),avg(sal) from emp e,dept d where e.deptno=d.deptno group by e.job;
-
查询每个经理所管理的人数,经理编号,经理姓名,要求包括没有经理的人员信息。
select count(w.mgr),m.empno,m.ename from emp m right outer join emp w on (w.mgr=m.empno) group by w.mgr;
4.5练习
-
查询部门人数大于2的部门编号,部门名称,部门人数。
select d.deptno,d.dname,count(*) from emp e,dept d where e.deptno=d.deptno group by d.deptno having count(*)>2;
-
查询部门平均工资大于2000,且人数大于2的部门编号,部门名称,部门人数,部门平均工资,并按照部门人数升序排序。
select d.deptno,d.dname,count(*),avg(sal) from emp e,dept d where e.deptno=d.deptno group by d.deptno having count(*)>2 and avg(sal)>2000 order by count(*) asc;
4.7练习
-
查询入职日期最早的员工姓名,入职日期
select ename,hiredate from emp where hiredate=(select min(hiredate) from emp);
-
查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名,工资,部门名称
select ename,sal,dname from emp,dept where sal>(select sal from emp where ename='SMITH') and loc='CHICAGO';
-
查询入职日期比20部门入职日期最早的员工还要早的员工姓名,入职日期
select ename,hiredate from emp where hiredate<(select min(hiredate) from emp where deptno=20);
4.8练习
-
查询入职日期比10部门任意一个员工晚的员工姓名、入职日期,不包括10部门员工
select ename,hiredate from emp where hiredate>any(select hiredate from emp where deptno=10) and deptno<>10;
-
查询入职日期比10部门所有员工晚的员工姓名、入职日期,不包括10部门员工
select ename,hiredate from emp where hiredate>all(select hiredate from emp where deptno=10) and deptno<>10;
-
查询职位和10部门任意一个员工职位相同的员工姓名,职位,不包括10部门员工
select ename,job from emp where job=any(select job from emp where deptno=10) and deptno<>10;
4.12课后作业
-
查询部门平均工资在2500元以上的部门名称及平均工资。
select d.dname,avg(sal) from emp e,dept d where e.deptno=d.deptno group by d.dname having avg(sal)>2500;
-
查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资,并按平均工资降序排序。
select job,avg(sal) from emp where job not like 'SA%' group by job having avg(sal)>2500 order by avg(sal) desc;
-
查询部门人数在2人以上的部门名称、最低工资、最高工资。
select d.dname,min(sal),max(sal) from emp e,dept d where e.deptno=d.deptno group by d.dname having count(*)>2;
-
查询岗位不为SALESMAN,工资和大于等于2500的岗位及每种岗位的工资和。
select job,sum(sal) from emp where job !='SALESMAN' group by job having sum(sal)>=2500;
-
显示经理号码和经理姓名,这个经理所管理员工的最低工资,没有经理的KING也要显示,不包括最低工资小于3000的,按最低工资由高到低排序
select m.empno,m.ename,min(m.sal) from emp w,emp m where w.mgr=m.empno group by m.empno having min(m.sal)>=3000 order by min(m.sal) desc;
-
查询工资高于编号为7782的员工工资,并且和7369号员工从事相同工作的员工的编号、姓名及工资。
select empno,ename,sal from emp where sal>(select sal from emp where empno=7782) and job=(select job from emp where empno=7369);
-
查询工资最高的员工姓名和工资。
select ename,sal from emp where sal=(select max(sal) from emp);
-
查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
select d.deptno,d.dname,min(sal) from emp e,dept d where e.deptno=d.deptno group by d.deptno having min(sal)>(select min(sal) from emp where deptno=10);
-
查询员工工资为其部门最低工资的员工的编号和姓名及工资。
select a.empno,a.ename,a.sal from emp a,(select deptno,min(sal) as'sal' from emp group by deptno) b where a.deptno=b.deptno and a.sal=b.sal;
-
显示经理是KING的员工姓名,工资。
select ename,sal from emp where mgr=(select empno from emp where ename='KING');
-
显示比员工SMITH参加工作时间晚的员工姓名,工资,参加工作时间。
select ename,sal,hiredate from emp where hiredate>(select hiredate from emp where ename='SMITH');