MySQL数据库（习题笔记）【1】
——Navicat
做题之前，先建表！！！ 添加数据！！！
目录
4，查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息:
5，查询所有工种不是MANAGER和CLERK，且工资大于或等于2000员工的详细信息：
8，查询员工信息，要求以首字母大写的方式显示所有员工的姓名:
10，显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序:
11，查询'JONES'员工及所有其直接、间接下属员工的信息:
1，查询从事同一种工作但不属于同一部门的员工信息: （一表——夺标查）
4，查询工资高于本部门平均工资的员工的信息及其部门的平均工资:
3，查询所有员工入职以来的工作期限，用“**年**月**日”的形式表示。
2，查询所有员工工资都大于1000的部门的信息及其员工信息:
4，查询所有工资都在900~3000之间的员工所在部门的员工信息:
7，查询工作等级为2级，1985年以后入职的工作地点为DALLAS的员工编号、姓名和工资：
1，将各部门员工的工资修改为该员工所在部门平均工资加1000:
-
emp 员工表
-- 创建表
CREATE TABLE `emp` (
`empno` int NOT NULL COMMENT '员工编号',
`ename` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名',
`job` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '岗位名称',
`mgr` int NULL DEFAULT NULL COMMENT '经理编号',
`sal` double(7, 2) NULL DEFAULT NULL COMMENT '工资',
`comm` double(7, 2) NULL DEFAULT NULL COMMENT '奖金',
`hiredate` date NULL DEFAULT NULL COMMENT '入职时间',
`deptno` int NULL DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`empno`) USING BTREE
)
-- 添加数据
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
-
dept 部门表
-- 创建表
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int NOT NULL COMMENT '部门编号',
`dname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门名称',
`loc` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '岗位所在地',
PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- 添加数据
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');-
salgrade 工资表
-- 创建表
CREATE TABLE `salgrade` (
`grade` int NOT NULL AUTO_INCREMENT COMMENT '工资等级',
`losal` double(7, 2) NOT NULL COMMENT '该等级里，最低工资',
`hisal` double(7, 2) NOT NULL COMMENT '该等级里，最高工资',
PRIMARY KEY (`grade`) USING BTREE
)
-- 添加数据
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);
练习！！！
一，试用SQL语言完成下列查询(单表查询)
1，查询20号部门的所有员工信息
select * from emp where deptno=202，查询奖金（COMM）高于工资（SAL）的员工信息：
select * from emp where comm>sal3，查询奖金高于工资的20%的员工信息:
select * from emp where comm>sal*0.24，查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息:
select * from emp where deptno=10 and job='manager' or deptno=20 and job='clerk'5，查询所有工种不是MANAGER和CLERK，且工资大于或等于2000员工的详细信息：
select * from emp where job not in ('manager','clerk') and sal>=20006，查询没有奖金或奖金低于100的员工信息:
select * from emp where comm is null or comm<1007，查询员工工龄大于或等于10年的员工信息:
select * from emp where (year(NOW())-year(hiredate))>=108，查询员工信息，要求以首字母大写的方式显示所有员工的姓名:
select CONCAT(UPPER(SUBSTRING(ename from 1 for 1)),LOWER(SUBSTRING(ename from 2))) from emp9，查询在2月份入职的所有员工信息:
select * from emp where month(hiredate)=210，显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序:
select ename,year(hiredate) y,month(hiredate) m from emp order by m,y11，查询'JONES'员工及所有其直接、间接下属员工的信息:
SELECT DISTINCT e1.*
FROM emp e1
JOIN emp e2 ON e1.empno = e2.mgr
JOIN emp e3 ON e2.empno = e3.mgr
WHERE e1.ename = 'JONES'
OR e2.ename = 'JONES'
OR e3.ename = 'JONES'12，查询SCOTT员工及其直接、间接上级员工的信息：
select DISTINCT e1.*
from emp e1
join emp e2 on e1.mgr=e2.empno
join emp e3 on e2.mgr=e3.empno
where e1.ename='SCOTT'
or e2.ename='SCOTT'
or e3.ename='SCOTT'二，试用SQL语言完成下列查询(多表查询)：
1，查询从事同一种工作但不属于同一部门的员工信息: （一表——夺标查）
select a.* from emp a,emp b where a.job=b.job and a.deptno<>b.deptno2，查询各个部门的详细信息以及部门人数、部门平均工资:
select e.*,d.*,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by e.deptno三，试用SQL语言完成下列查询(嵌套子查询)：
1，查询10号部门员工以及领导的信息:
select e.* from emp e
inner join emp ee
on e.mgr=ee.empno
where e.deptno=10
order by e.mgr,e.empno2，查询工资为某个部门平均工资的员工信息:
select * from emp where sal in (select avg(sal) from emp group by deptno)3，查询工资高于本部门平均工资的员工的信息:
select * from emp e where e.sal>(select avg(em.sal) from emp em where e.deptno=em.deptno)4，查询工资高于本部门平均工资的员工的信息及其部门的平均工资:
select e.*,a.av from emp e,(select avg(sal) av,deptno from emp GROUP BY deptno) a
where e.sal>a.av and e.deptno=a.deptno四，试用SQL语言完成下列查询(聚合函数)：
1，统计各个工种的人数与平均工资:
select job,count(*),avg(sal) from emp GROUP BY job
2，统计每个部门中各个工种的人数与平均工资:
select deptno,job,count(*),avg(sal) from emp group by deptno,job3，查询所有员工入职以来的工作期限，用“**年**月**日”的形式表示。
select concat(year(NOW())-year(hiredate),'年',
floor(mod((NOW()-hiredate),365)/30),'月',
floor(mod(mod((NOW()-hiredate),365),30)),'日')
from emp4，查询人数最多的部门信息：
select d.*,count(*) as ds
from emp e inner join dept d
on e.deptno=d.deptno
group by e.deptno
order by ds desc
limit 15，以树状结构查询所有员工与领导之间的层次关系：
SELECT
CONCAT(
IFNULL(e4.ename, ''),
IFNULL(CONCAT(' -> ', e3.ename), ''),
IFNULL(CONCAT(' -> ', e2.ename), ''),
IFNULL(CONCAT(' -> ', e1.ename), '')
) hierarchy
FROM
emp e1
LEFT JOIN
emp e2 ON e1.mgr = e2.empno
LEFT JOIN
emp e3 ON e2.mgr = e3.empno
LEFT JOIN
emp e4 ON e3.mgr = e4.empno
ORDER BY
e1.mgr, e1.empno;
6，部门平均薪水最高的部门编号:
select deptno,avg(sal) as avg
from emp
GROUP BY deptno
ORDER BY avg DESC
LIMIT 17，部门平均薪水最高的部门名称:
select d.dname,avg(e.sal) as avg
from dept d,emp e
WHERE d.deptno=e.deptno
GROUP BY d.deptno
ORDER BY avg DESC
limit 18，平均薪水最低的部门的部门名称:
select d.dname,avg(e.sal) as avg
from dept d,emp e
WHERE d.deptno=e.deptno
GROUP BY d.deptno
ORDER BY avg
limit 19，平均薪水等级最低的部门的部门名称:
select d.dname,s.grade
from emp e
inner join dept d on d.deptno=e.deptno
inner join salgrade s on e.sal BETWEEN s.losal and s.hisal
GROUP BY e.deptno
order by s.grade
limit 110，部门经理人中，薪水最低的部门名称
select d.dname,e.sal
from emp e,dept d
where e.deptno=d.deptno
AND sal in
(select sal from emp where job='manager')
group by e.deptno
ORDER BY e.sal
limit 111，比普通员工的最高薪水还要高的经理人名称:
select ee.ename,ee.job from emp ee,emp m where ee.sal>
(select max(e.sal) from emp e where e.job='CLERK' or e.job='SALESMAN')
and ee.empno=m.mgr五，试用SQL语言完成下列查询(嵌套子查询)：
1，查询所有员工工资都大于1000的部门的信息:
elect * from dept where deptno in (select deptno from emp where deptno in
(select deptno from emp where sal<1000))2，查询所有员工工资都大于1000的部门的信息及其员工信息:
select * from dept d inner join emp e
on d.deptno
in (select deptno from emp
where deptno not in
(select distinct deptno from emp where sal<1000))
and d.deptno=e.deptno3，查询所有员工工资都在900~3000之间的部门的信息
select * from dept where deptno not in
(select deptno from emp where sal not between 900 and 3000)4，查询所有工资都在900~3000之间的员工所在部门的员工信息:
select * from emp e where e.deptno not in
(select m.deptno from emp m where m.sal not between 900 and 3000)5，查询每个员工的领导所在部门的信息：
select * from dept where deptno in
(select e.deptno from emp e,emp m where e.mgr=m.empno)6，查询30号部门中工资排序前3名的员工信息
select * from emp where deptno=30 ORDER BY sal limit 37，查询工作等级为2级，1985年以后入职的工作地点为DALLAS的员工编号、姓名和工资：
select e.ename,e.empno,e.sal from emp e,dept d,salgrade s
where d.deptno=e.deptno
and (e.sal between s.losal and s.hisal)
and YEAR(e.hiredate)>1985
and d.loc='DALLAS'
and (s.grade=2)六，用SQL语句完成下列操作：
1，将各部门员工的工资修改为该员工所在部门平均工资加1000:
UPDATE emp e
join (select deptno,avg(sal)+1000 as ass
from emp GROUP BY deptno) a on e.deptno=a.deptno
set e.sal=a.ass2，删除重复部门，只留下一项:
delete e
from emp e
join emp ee on e.deptno=ee.deptno and e.empno>ee.empno3，更新员工工资为他的主管的工资,奖金:
update emp e
join emp ee on e.mgr=ee.empno
set e.sal=ee.sal,e.comm=ee.comm 原文地址：https://blog.csdn.net/2301_81819439/article/details/143730566
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！