MySQL练习题

最新推荐文章于 2022-05-20 10:31:42 发布

BigDevil_

最新推荐文章于 2022-05-20 10:31:42 发布

阅读量612

点赞数 2

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.youkuaiyun.com/BigDevil_/article/details/103864981

版权

MySQL 专栏收录该内容

6 篇文章

订阅专栏

文章目录

练习题1：

准备数据

## 练习题 的库

CREATE DATABASE test1;
USE test1;

##部门表
#DROP IF EXISTS TABLE 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 IF EXISTS TABLE EMP;
CREATE TABLE EMP(
	EMPNO INT  PRIMARY KEY,	#员工编号
	ENAME VARCHAR(10),	#员工姓名
	JOB VARCHAR(9),		#员工工作
	MGR INT, 		#员工直属领导编号
	HIREDATE DATE, 		#入职时间
	SAL DOUBLE,		#工资
	COMM DOUBLE,		#奖金
	DEPTNO INT		#对应dept表的外键
);  
## 添加 部门 和 员工 之间的主外键关系
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);

单表查询15题

# 1.查找部门是30的员工详细信息。
# 2.找出从事clerk工作的员工的编号、姓名、部门号。
# 3.检索出奖金多于基本工资的员工信息。
# 4.检索出奖金多于基本工资60%的员工信息。
# 5.找出10部门的经理、20部门的职员 的员工信息。
# 6.找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。
# 7.找出获得奖金的员工的信息。
# 8.找出奖金少于100或者没有获得奖金的员工的信息。
# 9.找出姓名以A、B、S开始的员工信息。
# 10.找到名字长度为6个字符的员工信息。
# 11.名字中不包含R字符的员工信息。
# 12.返回员工的详细信息并按姓名排序。
# 13.返回员工的信息并按工作降序工资升序排列。
# 14.计算员工的日薪(按30天)。
# 15.找出姓名中包含A的员工信息。
#

单表查询15题答案

## 1.查找部门是30的员工详细信息。
SELECT * FROM emp WHERE DEPTNO = 30;

## 2.找出从事clerk工作的员工的编号、姓名、部门号。
SELECT empno,ename,deptno FROM emp WHERE JOB = 'CLERK';

## 3.检索出奖金多于基本工资的员工信息。
SELECT * FROM emp WHERE comm > sal;

## 4.检索出奖金多于基本工资60%的员工信息。
SELECT * FROM emp WHERE comm > sal*0.6;

## 5.找出10部门的经理、20部门的职员 的员工信息。
SELECT * FROM emp WHERE (deptno=10 AND job='manager') OR (deptno = 20 AND job = 'clerk');

## 6.找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。
SELECT * FROM emp WHERE (deptno=10 AND job='manager') OR (deptno = 20 AND job = 'clerk') OR (job != 'manager' AND job != 'clerk' AND sal>2000);

## 7.找出获得奖金的员工的信息。
SELECT * FROM emp WHERE comm  IS NOT NULL AND comm != 0;
# 答案
SELECT job FROM emp WHERE comm > 0;

## 8.找出奖金少于100或者没有获得奖金的员工的信息。
SELECT * FROM emp WHERE comm IS NULL OR comm < 100

## 9.找出姓名以A、B、S开始的员工信息。
SELECT * FROM emp WHERE ename LIKE'A%' OR ename LIKE'B%' OR ename LIKE'S%'

## #############10.找到名字长度为6个字符的员工信息。
SELECT * FROM emp WHERE ename LIKE '______'; #6个下划线，代表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 ASC

## 13.返回员工的信息并按工作降序工资升序排列。
SELECT * FROM emp ORDER BY job DESC , sal ASC

## 14.计算员工的日薪(按30天)。
SELECT ename,sal/30 AS 日薪 FROM emp

## 15.找出姓名中包含A的员工信息。
SELECT * FROM emp WHERE ename LIKE '%A%'

注意：

like ， not like；
in ， not in；
is null , is not null;
length(字段名) 该字段的长度

多表15题

#多表
#1.返回拥有员工的部门名、部门号。

#2.工资水平多于smith的员工信息。

#3.返回员工和所属经理的姓名。

#4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名

#5.返回员工姓名及其所在的部门名称。

#6.返回从事clerk工作的员工姓名和所在部门名称。

#7.返回部门号及其本部门的最低工资。

#8.返回销售部(sales)所有员工的姓名。

#9.返回工资水平多于平均工资的员工。

#10.返回与SCOTT从事相同工作的员工。

#11.返回与30部门员工工资水平相同的员工姓名与工资。

#12.返回工资高于30部门所有员工工资水平的员工信息。

#13.返回部门号、部门名、部门所在位置及其每个部门的员工总数。

#14.返回员工的姓名、所在部门名及其工资。

#15.返回员工的详细信息。(包括部门名)

多表答案

#多表
#1.返回拥有员工的部门名、部门号。
SELECT d.`DEPTNO`,d.`DNAME` FROM emp e,dept d WHERE e.deptno =  d.`DEPTNO` GROUP BY e.`DEPTNO`;
# 答案
SELECT DISTINCT d.dname,d.deptno FROM dept d ,emp e WHERE d.deptno = e.deptno;

#2.工资水平多于smith的员工信息。
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

#3.返回员工和所属经理的姓名。
SELECT e1.ename,e2.ename marname 
  FROM emp e1 LEFT JOIN emp e2 
  ON e1.mgr = e2.empno;

#4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
SELECT e1.*,e2.ename marname,e2.`HIREDATE` mrgdate 
  FROM emp e1 ,emp e2 
  WHERE e1.mgr = e2.empno AND e1.`HIREDATE` < e2.`HIREDATE`;

#5.返回员工姓名及其所在的部门名称。
SELECT e.`ENAME`,d.`DNAME` 
  FROM emp e , dept d 
  WHERE e.`DEPTNO` = d.`DEPTNO`;

#6.返回从事clerk工作的员工姓名和所在部门名称。
SELECT e.`ENAME`,d.`DNAME`,e.`JOB` 
  FROM emp e , dept d 
  WHERE e.`DEPTNO` = d.`DEPTNO` AND e.`JOB`='clerk';

#7.返回部门号及其本部门的最低工资。
SELECT d.`DEPTNO`,MIN(sal)
  FROM emp e,dept d 
  WHERE e.`DEPTNO` = d.`DEPTNO` 
  GROUP BY e.`DEPTNO`;
# 答案
SELECT deptno,MIN(sal) FROM emp GROUP BY deptno;

#8.返回销售部(sales)所有员工的姓名。
SELECT e.`ENAME` FROM emp e WHERE e.`DEPTNO` = (SELECT d.`DEPTNO` FROM dept d WHERE d.`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');
# 注意：不要他本人，只要同事。(不能用员工名判断，因为可能同名)
#答案 ***(精髓)
SELECT * FROM emp e1 , (SELECT * FROM emp WHERE ename='scott') e2 
   WHERE e1.job = e2.job AND e1.empno != e2.empno;


#11.返回与30部门员工工资水平相同的员工姓名与工资。
SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30);

#12.返回工资高于30部门所有员工工资水平的员工信息。
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

#13.返回部门号、部门名、部门所在位置及其每个部门的员工总数。
SELECT d.`DEPTNO`,d.`DNAME`,d.`LOC`,COUNT(e.`EMPNO`) 员工数 FROM emp e RIGHT JOIN dept d ON e.`DEPTNO` = d.`DEPTNO` GROUP BY d.`DEPTNO`;

#14.返回员工的姓名、所在部门名及其工资。
SELECT e.`ENAME`,d.`DNAME`,e.`SAL` FROM emp e,dept d WHERE e.`DEPTNO`=d.`DEPTNO`;

#15.返回员工的详细信息。(包括部门名)
SELECT e.*,d.`DNAME` FROM emp e,dept d WHERE e.`DEPTNO`=d.`DEPTNO`;

注意：

返回与SCOTT从事相同工作的员工。(注意：不要他本人，只要同事。(不能用员工名判断，因为可能同名))

#10.返回与SCOTT从事相同工作的员工。
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename ='scott');
# 注意：不要他本人，只要同事。(不能用员工名判断，因为可能同名)
#答案 ***(精髓)
SELECT * FROM emp e1 , (SELECT * FROM emp WHERE ename='scott') e2 
   WHERE e1.job = e2.job AND e1.empno != e2.empno;

#13.返回部门号、部门名、部门所在位置及其每个部门的员工总数。
SELECT d.`DEPTNO`,d.`DNAME`,d.`LOC`,COUNT(e.`EMPNO`) 员工数 FROM emp e RIGHT JOIN dept d ON e.`DEPTNO` = d.`DEPTNO` GROUP BY d.`DEPTNO`;

返回每个部门的员工总数。或者返回每个部门的平均工资，一类的。一定要用分组group by。
分组是，按字段分组后，虽然只返回每组的第一个，但是，每组的数据是在虚拟表中存在的。

四表联查

数据准备：

#部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, 
  dname VARCHAR(50), 
  loc VARCHAR(50) 
);

#添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

#职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

#添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

#员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

#添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

#工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

#添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

练习题

#练习1：查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述

#练习2：查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

#练习3：查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

#练习4：查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

#练习5：查询出部门编号、部门名称、部门位置、部门人数

#练习6：查询所有员工信息。显示员工信息和部门名称，没有员工的部门也要显示

#练习7：查询所有员工信息。显示员工姓名，员工工资，职务名称，工资等级，并按工资升序排序

#练习8：列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示

#练习9：查询入职期早于直接上级的所有员工编号、姓名、部门名称

#练习10：查询工资高于公司平均工资的所有员工信息。显示员工信息，部门名称，上级领导，工资等级

答案

#练习1：查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述
SELECT e.id,e.ename,e.salary,j.jname,j.description FROM emp e,job j WHERE e.job_id = j.id;

#练习2：查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT e.id,e.ename,e.salary,j.jname,j.description,d.dname,d.loc FROM emp e,job j, dept d WHERE e.job_id = j.id AND e.dept_id = d.id;

#练习3：查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc,s.grade FROM emp e,salarygrade s,job j,dept d WHERE (e.salary BETWEEN s.losalary AND s.hisalary) AND e.job_id = j.id AND e.dept_id = d.id;

#练习4：查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc,s.grade FROM emp e,salarygrade s,job j,dept d WHERE (e.salary BETWEEN s.losalary AND s.hisalary) AND e.job_id = j.id AND e.dept_id = d.id AND e.job_id = 2;

#练习5：查询出部门编号、部门名称、部门位置、部门人数
SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.id GROUP BY e.dept_id

SELECT *,COUNT(*) FROM emp e,dept d WHERE e.dept_id = d.id GROUP BY e.dept_id

SELECT d.dname ,COUNT(*) FROM emp e,dept d WHERE e.dept_id = d.id GROUP BY e.dept_id



 练习6：查询所有员工信息。显示员工信息和部门名称，没有员工的部门也要显示
SELECT e.*,d.dname FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id; 

# 练习7：查询所有员工信息。显示员工姓名，员工工资，职务名称，工资等级，并按工资升序排序
SELECT e.ename,e.salary,j.jname,s.grade FROM emp e,job j,salarygrade s WHERE e.job_id = j.id AND (e.salary BETWEEN s.losalary AND s.hisalary) ORDER BY e.salary;

# 练习8：列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示
SELECT e.ename,u.ename FROM emp e LEFT JOIN (SELECT e.id,e.ename FROM emp e) u ON e.mgr = u.id;

# 查询入职期早于直接上级的所有员工编号、姓名、部门名称
SELECT e.*,u.ename FROM emp e ,(SELECT e.id,e.ename,e.joindate FROM emp e) u WHERE e.mgr = u.id AND e.joindate < u.joindate;
## 答案：自连接
SELECT e.*,u.ename FROM emp e ,emp u WHERE e.mgr = u.id AND e.joindate < u.joindate;
## ename重复，别名需要
SELECT em.id,em.ename,d.dname FROM (SELECT e.*,u.ename AS uname FROM emp e ,(SELECT e1.id,e1.ename,e1.joindate FROM emp e1) u WHERE e.mgr = u.id AND e.joindate < u.joindate ) em, dept d WHERE em.dept_id = d.id;

#练习10：查询工资高于公司平均工资的所有员工信息。显示员工信息，部门名称，上级领导，工资等级
SELECT AVG(salary) FROM emp e
SELECT * FROM emp WHERE salary >(SELECT AVG(salary) FROM emp) 

SELECT e.*,e1.ename,d.dname,s.grade FROM (SELECT * FROM emp WHERE salary >(SELECT AVG(salary) FROM emp))e LEFT JOIN emp e1 ON e.mgr = e1.id,dept d,salarygrade s WHERE d.id = e.dept_id
AND e.salary BETWEEN s.losalary AND s.hisalary

# 答案
SELECT e.*,d.dname,l.ename,s.grade 
FROM emp e LEFT JOIN emp l ON e.mgr = l.id,
dept d,salarygrade s 
WHERE e.dept_id = d.id 
AND  e.salary BETWEEN s.losalary AND s.hisalary 
AND e.salary > (SELECT AVG(salary) FROM emp);

注意

查询出部门编号、部门名称、部门位置、部门人数。
每个部门的人数，要用分组

外连接内连接一起用。第十题；

练习10：查询工资高于公司平均工资的所有员工信息。显示员工信息，部门名称，上级领导，工资等级

先左连接直接on ，on条件结束之后内连接其他表

 SELECT e.* ,d.dname 部门名称,m.ename 上级领导 ,s.grade 工资等级
 FROM emp e
 LEFT OUTER JOIN emp m ON e.mgr=m.id 
 INNER JOIN dept d ON e.dept_id=d.id 
 INNER JOIN salarygrade s ON e.salary 
 BETWEEN s.losalary AND hisalary 
 AND e.salary>( SELECT AVG(salary) FROM emp) LIMIT 0, 1000;