【MYSQL数据库综合练习】第二部分

– 创建部门表

DROP TABLE IF EXISTS dept;

CREATE TABLE dept(

– 部门编号

deptno int PRIMARY KEY,

– 部门名称

dname VARCHAR(14),

– 部门所在地

loc VARCHAR(13)

);

– 向部门表插入数据

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’);

– 创建员工表

DROP TABLE IF EXISTS emp;

CREATE TABLE emp(

– 员工编号

empno int PRIMARY KEY,

– 员工姓名

ename VARCHAR(10),

– 工作岗位

job VARCHAR(9),

– 直属领导

mgr int,

– 入职时间

hiredate DATE,

– 工资

sal double,

– 奖金

comm double,

– 所属部门

deptno int

);

– 为员工表表添加外键约束

ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY EMP(deptno) REFERENCES dept (deptno);

– 向员工表插入数据

INSERT INTO emp VALUES(7369,‘SMITH’,‘CLERK’,7902,“1980-12-17”,800,NULL,20);

INSERT INTO emp VALUES(7499,‘ALLEN’,‘SALESMAN’,7698,‘1981-02-20’,1600,300,30);

INSERT INTO emp VALUES(7521,‘WARD’,‘SALESMAN’,7698,‘1981-02-22’,1250,500,30);

INSERT INTO emp VALUES(7566,‘JONES’,‘MANAGER’,7839,‘1981-04-02’,2975,NULL,20);

INSERT INTO emp VALUES(7654,‘MARTIN’,‘SALESMAN’,7698,‘1981-09-28’,1250,1400,30);

INSERT INTO emp VALUES(7698,‘BLAKE’,‘MANAGER’,7839,‘1981-05-01’,2850,NULL,30);

INSERT INTO emp VALUES(7782,‘CLARK’,‘MANAGER’,7839,‘1981-06-09’,2450,NULL,10);

INSERT INTO emp VALUES(7788,‘SCOTT’,‘ANALYST’,7566,‘1987-07-03’,3000,NULL,20);

INSERT INTO emp VALUES(7839,‘KING’,‘PRESIDENT’,NULL,‘1981-11-17’,5000,NULL,10);

INSERT INTO emp VALUES(7844,‘TURNER’,‘SALESMAN’,7698,‘1981-09-08’,1500,0,30);

INSERT INTO emp VALUES(7876,‘ADAMS’,‘CLERK’,7788,‘1987-07-13’,1100,NULL,20);

INSERT INTO emp VALUES(7900,‘JAMES’,‘CLERK’,7698,‘1981-12-03’,950,NULL,30);

INSERT INTO emp VALUES(7902,‘FORD’,‘ANALYST’,7566,‘1981-12-03’,3000,NULL,20);

INSERT INTO emp VALUES(7934,‘MILLER’,‘CLERK’,7782,‘1981-01-23’,1300,NULL,10);

– 创建工资等级表

DROP TABLE IF EXISTS salgrade;

CREATE TABLE salgrade(

– 等级

grade int,

– 最低工资

losal double,

– 最高工资

hisal double

);

– 向工资等级表插入数据

INSERT INTO salgrade VALUES (1,700,1200);

INSERT INTO salgrade VALUES (2,1201,1400);

INSERT INTO salgrade VALUES (3,1401,2000);

INSERT INTO salgrade VALUES (4,2001,3000);

INSERT INTO salgrade VALUES (5,3001,9999);

– 01、查询部门编号为30的部门的员工详细信息

select * from emp where deptno='30'

– 02、查询从事clerk工作的员工的编号、姓名以及其部门号

select empno,ename,deptno from emp where job='clerk'

– 03、查询奖金多于基本工资的员工的信息

select * from emp where comm>sal

--询奖金小于于基本工资的员工的信息

select * from emp where comm is null or comm<sal

– 04、查询奖金多于基本工资60%的员工的信息

select * from emp where comm>(sal*0.6)

– 05、查询部门编号为10的部门经理 (job=manager) 和部门编号为20的部门中工作为CLERK的职员信息

select * from emp where (deptno=10 and job='manager') or (deptno=20 and job='clerk')

– 06、查询部门编号为10的部门经理或部门编号为20的部门工作为CLERK的职员信息或者既不是经理也不是CLERK但是工资高于2000的员工信息

select * from emp where (deptno=10 and job='manager') or (deptno=20 and job='clerk') 

or (sal>2000 and job not in ('manager','clerk'))

自己做的：

SELECT * FROM emp
WHERE deptno='10' AND job='manager' OR deptno='20' AND job='CLERK' OR job!='manager' AND job!='CLECK' AND sal>2000

– 07、查询获得奖金的员工的信息(1、信息都在员工表中；获得奖金==comm is not null and comm>0)

SELECT * FROM emp
WHERE comm IS NOT NULL AND comm>0
-- 因为奖金可能不是null而是0

– 08、查询奖金少于100或者没有获得奖金的员工的信息

select * from emp where comm<100 or comm is null

– 09、查询姓名以A、B、S开头的员工的信息

select * from emp 
where ename like 'A%' or ename like 'B%' or ename like 'S%'

SELECT * FROM emp
WHERE ename LIKE 'A%' OR 'B' OR 'S'
--不能直接用OR，每一个模糊查询都要用ename LIKE ‘B%’,不然查不完全

--使用字符函数，取第一个姓名的第一个字符

select * from emp where left(ename,1) in ('A','B','S')

– 10、查询找到姓名长度为6个字符的员工的信息

select * from emp where length(ename)=6

– 11、查询姓名中不包含R字符的员工信息。

select * from emp where ename not like '%R%'

– 12、查询员工的详细信息并按姓名排序

select * from emp order by ename

– 13、查询员工的信息并按工作降序工资升序排列

select * from emp order by job desc, sal asc

– 14、计算员工的日薪(按30天计)

select *,sal/30 as daysal from emp 

– 15、查询姓名中包含字符A的员工的信息

select * from emp where ename like '%A%'

– 16、查询拥有员工的部门的部门名和部门号

select empno,ename,job,mgr,hiredate ,sal,comm,a.deptno,b.dname
from emp a inner join dept b on a.deptno=b.deptno
--自己做的

SELECT dname,deptno FROM dept
WHERE deptno IN (SELECT deptno FROM emp)

– 17、查询工资多于smith的员工信息。

select * from emp where sal>(select sal from emp where ename='smith')

– 18、查询员工和及其所属经理的姓名(同一个部门，job=manager)—自连接

select a.empno,a.ename,a.job,b.ename as manager
from emp a inner join emp b on a.deptno=b.deptno
where b.job='manager' and a.mgr is not null

\---------------------

select a.empno,a.ename,a.job,b.ename as manager
from emp a inner join emp b on a.mgr=b.empno

自己做的：

SELECT e1.ename,e1.job,e2.ename FROM emp AS e1 JOIN emp AS e2 ON e1.deptno=e2.deptno
WHERE e2.job='MANAGER'

订正

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rBT9MWRy-1672283415595)(file:///C:\Users\ASUS\AppData\Local\Temp\ksohtml55464\wps7.jpg)]

– 19、查询雇佣日期早于其经理雇佣日期的员工及其经理姓名

select a.empno,a.ename,a.job,b.ename as manager
from emp a inner join emp b on a.deptno=b.deptno
where b.job='manager' and a.mgr is not null and a.hiredate<b.hiredate

--自己做的：
SELECT e1.ename,e1.job,e2.ename as manager FROM emp AS e1 INNER JOIN emp AS e2 ON e1.deptno=e2.deptno
WHERE DATEDIFF(e2.hiredate,e1.hiredate)>0 AND e2.job='MANAGER'

– 20、查询员工姓名及其所在的部门名称

--注意：一般在多表联查的时候，放在前面的表-称为主表，join的表-称为从表

--一般会将数据量小的表作为主表，数据多的作为从表--->加快查询效率

select ename,dname from dept a inner join emp b on a.deptno=b.deptno

--自己做的：

SELECT emp.ename,dept.dname FROM emp,dept
WHERE emp.deptno=dept.deptno

– 21、查询工作为clerk的员工姓名及其所在部门名称

select ename,dname from dept a inner join emp b on a.deptno=b.deptno where job='clerk'

--自己做的：
SELECT e.ename,d.dname FROM emp AS e,dept AS d
WHERE e.deptno=d.deptno AND e.job='clerk'

– 22、查询各部门号及其部门中的最低工资

select deptno,min(sal) as minsal from emp group by deptno

自己做的：
SELECT deptno,MIN(sal) FROM emp
GROUP BY deptno

– 23、查询销售部sales的所有员工的姓名

select ename from emp a inner join dept b on a.deptno=b.deptno where b.dname='sales'

– 24、查询工资水平大于平均工资的员工

select * from emp where sal>(select avg(sal) from emp)

---------工资等级等于或者高于平均工资等级----------

--平均等级
select grade from salgrade where losal<=(select avg(sal) from emp) and hisal>=(select avg(sal) from emp)


--查询员工信息，并查询出其工资等级
select a.*,b.grade from emp a inner join salgrade b on a.sal between b.losal and b.hisal

where b.grade>=(select grade from salgrade where losal<=(select avg(sal) from emp) and hisal>=(select avg(sal) from emp))

-- 自己做的：
-- 平均等级
SELECT grade FROM salgrade
WHERE losal<=(SELECT AVG(sal) FROM emp) AND hisal>=(SELECT AVG(sal) FROM emp)

-- 平均等级最低和最高工资
SELECT losal,hisal FROM salgrade
WHERE grade=(SELECT grade FROM salgrade
WHERE losal<=(SELECT AVG(sal) FROM emp) AND hisal>=(SELECT AVG(sal) FROM emp))

SELECT * FROM emp
WHERE sal>=(SELECT losal FROM salgrade
WHERE grade=(SELECT grade FROM salgrade
WHERE losal<=(SELECT AVG(sal) FROM emp) AND hisal>=(SELECT AVG(sal) FROM emp)))

– 25、查询与SCOTT从事相同工作的员工。

select * from emp where job=(select job from emp where ename='scott')

– 26、查询与部门编号为30的员工工资水平相同的员工信息

--1）部门30的平均工资等级；
select grade from salgrade where losal<=(select avg(sal) from emp where deptno=30) 
and hisal>=(select avg(sal) from emp where deptno=30)

----2)查询员工信息以及员工的工资等级，然后再用员工的工资等级和平均等级进行比较
select a.*,b.grade from emp a inner join salgrade b on a.sal between b.losal and b.hisal
where b.grade=(select grade from salgrade where losal<=(select avg(sal) from emp where deptno=30) 
and hisal>=(select avg(sal) from emp where deptno=30))

-- 自己做的：
-- 1）部门30的平均工资等级；
SELECT grade FROM salgrade
WHERE losal<=(SELECT AVG(sal) FROM emp WHERE deptno='30') 
AND hisal>=(SELECT AVG(sal) FROM emp WHERE deptno='30')

-- 2)查询员工信息以及员工的工资等级，然后再用员工的工资等级和平均等级进行比较
SELECT e.* FROM emp AS e INNER JOIN salgrade AS s ON e.sal BETWEEN losal AND hisal
WHERE grade=(SELECT grade FROM salgrade
WHERE losal<=(SELECT AVG(sal) FROM emp WHERE deptno='30') 
AND hisal>=(SELECT AVG(sal) FROM emp WHERE deptno='30'))

– 27、查询工资高于部门编号为30的部门所有员工工资水平的员工信息

--1）找到30部门最高工资水平
select grade from salgrade where losal<=(select max(sal) from emp where deptno=30) 
and hisal>=(select max(sal) from emp where deptno=30)

\--------

select a.*,b.grade from emp a inner join salgrade b on a.sal between b.losal and b.hisal
where b.grade>(select grade from salgrade where losal<=(select max(sal) from emp where deptno=30) 
and hisal>=(select max(sal) from emp where deptno=30))

-- 自己做的：

-- 1）找到30部门最高工资水平
SELECT grade FROM salgrade WHERE losal<=(SELECT MAX(sal) FROM emp
WHERE deptno='30') AND hisal>=(SELECT MAX(sal) FROM emp
WHERE deptno='30')

-- 只要工资高于30部门最高工资水平的最高工资hisal

SELECT * FROM emp 
WHERE sal>(SELECT hisal FROM salgrade
WHERE grade=(SELECT grade FROM salgrade WHERE losal<=(SELECT MAX(sal) FROM emp
WHERE deptno='30') AND hisal>=(SELECT MAX(sal) FROM emp
WHERE deptno='30')))

– 28、查询部门号、部门名、部门所在位置及其每个部门的员工总数

select a.deptno,dname,loc,count(1) as countemp
from dept a inner join emp b on a.deptno=b.deptno group by a.deptno,dname,loc

-- 自己做的：
SELECT d.deptno,d.dname,d.loc,COUNT(empno) FROM emp AS e INNER JOIN dept AS d ON e.deptno=d.deptno
 GROUP BY d.deptno,d.dname,d.loc

– 29、查询员工的姓名、工资及其所属部门

select ename,sal,dname from emp a inner join dept b on a.deptno=b.deptno

– 30、查询员工的详细信息(含部门名)

select empno,ename,job,mgr,hiredate ,sal,comm,a.deptno,b.dname
from emp a inner join dept b on a.deptno=b.deptno

-- 自己做的：
SELECT emp.*,dept.dname FROM emp,dept
WHERE emp.deptno=dept.deptno

– 31、查询各工作名称以及从事此工作的最低工资

select job,min(sal) as minsal from emp group by job

– 32、计算员工的年薪并且以年薪排序

select ename,sal*12 as yearsal from emp order by yearsal desc

– 33、查询工资为第4级别的员工的姓名及其工资

SELECT ename,sal,grade FROM emp AS e INNER JOIN salgrade AS s ON e.sal BETWEEN losal AND hisal
WHERE grade=4

– 34、查询工资为第3等级的职员名字、工资、所属部门以及部门所在地

SELECT e.ename,e.sal,s.grade,d.dname,d.loc FROM emp AS e INNER JOIN salgrade AS s ON e.sal BETWEEN losal AND hisal 
INNER JOIN dept AS d ON e.deptno=d.deptno;
WHERE grade=3

– 35、查询工资等级大于smith的员工的信息

SELECT * FROM emp
WHERE sal> ANY(SELECT hisal FROM salgrade WHERE grade=(SELECT grade FROM salgrade
WHERE losal<=(SELECT sal FROM emp WHERE ename='smith') AND 
hisal>=(SELECT sal FROM emp WHERE ename='smith')))

-- smith员工信息
SELECT grade FROM salgrade
WHERE losal<=(SELECT sal FROM emp WHERE ename='smith') AND 
hisal>=(SELECT sal FROM emp WHERE ename='smith')