SQL语句练习（查询）

转载声明：本文为个人总结笔记，主要参考韩老师msql教程的SQL语句练习部分，转载需要注明参考文献的出处，尊重各位原创作者。

练习1

练习1语句

-- 写出查看DEPT表和EMP表的结构的sql语句：
-- (1)显示所有部门名称。

-- (2)显示所有雇员名及其全年收入13月（工资+补助）,并指定列别名“年收入”

-- 限制查询数据。
-- (1)显示工资超过2850的雇员姓名和工资。

-- (2)显示工资不在1500到2850之间的所有雇员名及工资。

-- (3)显示编号为7566的雇员姓名及所在部门编号。

-- (4)显示部门10和30中工资超过1500的雇员名及工资。

-- (5)显示无管理者的雇员名及岗位。

-- 排序数据。
-- (1)显示在1991年2月1日到1991年5月1日之间雇用的雇员名，岗位及雇佣日期，并以雇佣日期进行排序。

-- (2)显示获得补助的所有雇员名，工资及补助，并以工资降序排序

练习1语句参考答案

-- 写出查看DEPT表和EMP表的结构的sql语句
DESC `department`;
DESC `emp`

-- 使用简单查询语句完成：DEPT表和EMP表

-- (1)显示所有部门名称。
SELECT dname FROM `department`;

-- (2)显示所有雇员名及其全年收入13月（工资+补助）,并指定列别名“年收入”
/*
	注意：具体的数和null相加会变成null值
	思路：部分员工的补助值为null，与工资相加的和值为null，查询结果错误。
			应该在相加前对补助进行非空判断 
	IFNULL(expr1,expr2)：如果expr1的值为null，则返回expr2的值，如果expr1的值不为null，则返回expr1的值
*/
SELECT
	ename,(
	sal + IFNULL( comm, 0 ))* 13 AS '年收入' 
FROM
	`emp`;



-- 限制查询数据。

-- (1)显示工资超过2850的雇员姓名和工资。
SELECT ename,sal FROM `emp` WHERE sal> 2850;
-- (2)显示工资不在1500到2850之间的所有雇员名及工资。
select ename,sal from `emp`
	where sal not between 1500 and 2850;
	
select ename,sal from `emp`
	where sal < 1500 or sal > 2850;	
	
select ename,sal from `emp`
	where not(sal >= 1500 and sal <= 2850)
	
-- (3)显示编号为7566的雇员姓名及所在部门编号。
select ename,deptno 
	from `emp`  
	where empno = 7566;
	
-- (4)显示部门10和30中工资超过1500的雇员名及工资。
SELECT ename, sal 
	FROM `emp` 
	WHERE sal > 1500 AND deptno IN ( 10, 30 );
	
-- (5)显示无管理者的雇员名及岗位。
select ename,job 
	from `emp` 
	where mgr is null;
	
	
-- 排序数据。

-- (1)显示在1991年2月1日到1991年5月1日之间雇用的雇员名，岗位及雇佣日期，并以雇佣日期进行排序。
select ename,job,hiredate from `emp`
	where hiredate >= '1991-02-01' and hiredate <='1991-05-01'
	order by hiredate;

select ename,job,hiredate from `emp`
	where hiredate between '1991-02-01' and '1991-05-01'
	order by hiredate;
	
-- (2)显示获得补助的所有雇员名，工资及补助，并以工资降序排序
select ename,sal,comm from `emp` 
	where comm is not null  
	order by sal desc;

练习2

练习2语句

-- 根据：emp员工表
-- 1.选择部门30中的所有员工.

-- 2.列出所有办事员（CLERK)的姓名，编号和部门编号.

-- 3.找出佣金高于薪金的员工.

-- 4.找出佣金高于薪金60%的员工.

-- 5.找出部门10中所有经理（MANAGER)和部门20中所有办事员（CLERK)的详细资料.

-- 6.找出部门10中所有经理（MANAGER),部门20中所有办事员（CLERK),还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.

-- 7.找出收取佣金的员工的不同工作.

-- 8.找出不收取佣金或收取的佣金低于100的员工.

-- 9.找出各月倒数第3天受雇的所有员工.

-- 10.找出早于12年前受雇的员工.

-- 11.以首字母小写的方式显示所有员工的姓名.

-- 12.显示正好为5个字符的员工的姓名.

练习1语句参考答案

-- 根据：emp员工表 写出正确SQL homework03.sql

-- 1.查询出部门30中的所有员工.
select * from `emp` 
	where deptno = 30;
	
-- 2.列出所有办事员（CLERK)的姓名，编号和部门编号.
select ename,empno,deptno,job from `emp`
	where job = 'CLERK';

-- 3.找出佣金高于薪金的员工.
select * from `emp`
	where IFNULL(comm,0) > sal;

-- 4.找出佣金高于薪金60%的员工.
select * from `emp`
	where IFNULL(comm,0)  > (sal* 0.6);

-- 5.找出部门10中所有经理（MANAGER)和部门20中所有办事员（CLERK)的详细资料.
select * 
	from `emp`
	where (job = 'MANAGER' and deptno = 10)
	or (job = 'CLERK' and deptno = 20)

-- 6.找出部门10中所有经理（MANAGER),部门20中所有办事员（CLERK),还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
select * from 
	`emp`
	where (job = 'MANAGER' and deptno = 10)
	or (job = 'CLERK' and deptno = 20)
	or (job != 'CLERK' and job != 'MANAGER' and sal >= 2000)

select * from `emp` 
	where job = 'MANAGER' and deptno = 10
UNION
select * from `emp` 
	where job = 'CLERK' and deptno = 20
UNION 
select * from `emp` 
	where job not in('MANAGER','CLERK')
	and sal >=2000; 

-- 7.找出收取佣金的员工的不同工作.
select DISTINCT job
	from `emp`
	where comm is not null;

-- 8.找出不收取佣金或收取的佣金低于100的员工.
select ename,comm from `emp`
	where comm is null 
	or IFNULL(comm,0) < 100;

-- 9.找出各月倒数第3天受雇的所有员工.
-- 提示: LAST_DAY(date) 表示某日期所在月份的最后一天
 select * from
	`emp`
	where hiredate = LAST_DAY(hiredate) - 2 

-- 10.找出早于12年前受雇的员工.
/*
	-------------------------------
		|						 12             |
	start                         now
*/
SELECT *from `emp`
	where (YEAR(NOW()) - YEAR(hiredate) >= 12);

-- 11.以首字母小写的方式显示所有员工的姓名.
/*
	CONCAT(str1,str2) :拼接两个字符串
	SUBSTRING(str,start,length):截取从start开始包括start的字符
	LOWER(str)：将字符串str的字母都转为小写
*/
select CONCAT(LOWER(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) as '员工姓名' 
	from `emp`;

-- 12.显示正好为5个字符的员工的姓名.
select ename 
	from `emp`
	where LENGTH(ename) = 5 

练习3

练习3语句

-- 根据：emp员工表 写出正确SQL homework03.sql
-- 13.显示不带有"R"的员工的姓名.
-- 
-- 14.显示所有员工姓名的前三个字符
-- 
-- 15.显示所有员工的姓名，用a替换所有“A”
-- 
-- 16.显示满10年服务年限的员工的姓名和受雇日期.
-- 
-- 17.显示员工的详细资料，按姓名排序.
-- 
-- 18.显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面.
-- 
-- 19.显示所有员工的姓名、工作和薪金，按工作降序排序，若工作相同则按薪金排序.
-- 
-- 20.显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面.
-- 
-- 21.显示在一个月为30天的情况所有员工的日薪金，忽略余数.
-- 
-- 22.找出在（任何年份的）2月受聘的所有员工。
-- 
-- 23.对于每个员工，显示其加入公司的天数.
-- 
-- 24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
-- 
-- 25.以年月日的方式显示所有员工的服务年限.(大概）

练习3语句参考答案

-- 根据：emp员工表 
-- 13.显示不带有"R"的员工的姓名.
select * from `emp`
	where ename not like '%R%';

-- 14.显示所有员工姓名的前三个字符
/*
	LEFT(str,length)：字符串函数，返回具有指定长度的字符串的左边部分
		str是要提取子字符串的字符串。
		length是一个正整数，指定将从左边返回的字符数。
*/
select LEFT(ename,3) FROM `emp`;

select SUBSTRING(ename,1,3) as '姓名简称' from `emp`;

-- 15.显示所有员工的姓名，用a替换所有“A”
select REPLACE(ename,'A','a') from `emp`;

-- -16.显示满10年服务年限的员工的姓名和受雇日期
-- 提示：DATE_ADD(date,INTERVAL expr unit)：在date中加上日期或时间
select ename,hiredate from `emp`
	where DATE_ADD(hiredate,INTERVAL 10 year) <= NOW()

-- -17.显示员工的详细资料，按姓名排序.
select * from `emp` order by ename;

-- -18.显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面.
select ename,hiredate from `emp`
	order by hiredate asc;


-- -----19.显示所有员工的姓名、工作和薪金，按工作降序排序，若工作相同则按薪金排序.
select ename,job,sal
	from `emp`
	order by job desc,sal asc;

-- 20.显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面.
select ename,CONCAT(YEAR(hiredate),'-',MONTH(hiredate)) as '入职年月份' from `emp`
	order by MONTH(hiredate) asc,YEAR(hiredate) asc
	
-- -21.显示在一个月为30天的情况所有员工的日薪金，忽略余数.
select ename,ROUND(sal / 30) as '日薪资' from `emp`;

-- -22.找出在（任何年份的）2月受聘的所有员工。
select * from `emp`
	where (MONTH(hiredate))=2
	
-- -23.对于每个员工，显示其加入公司的天数.
select ename,DATEDIFF(NOW(),hiredate) as '工作天数' from `emp`;
 
-- -24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
select ename from `emp`
	where ename like '%A%';
	
-- -25.以年月日的方式显示所有员工的服务年限.(大概）
-- 提示：工作了xxx年xx月xxx日
select ename,
	FLOOR(DATEDIFF(NOW(),hiredate) / 365) as '工作年数',
	FLOOR(DATEDIFF(NOW(),hiredate) % 365 / 31) as '工作月数',
	DATEDIFF(NOW(),hiredate) % 31  as '工作天数' 
	from `emp`

练习4

练习4语句

-- 根据：emp员工表，dept部门表
-- 友情提示:工资=薪金+佣金

-- (1).列出至少有一个员工的所有部门

-- (2).列出薪金比“SMITH”多的所有员工。

-- (3).列出受雇日期晚于其直接上级的所有员工。

-- (4).列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

-- (5).列出所有“CLERK”（办事员）的姓名及其部门名称。

-- (6).列出最低薪金大于1500的各种工作。

-- (7).列出在部门“SALES”（销售部）工作的员工的姓名。

-- (8).列出薪金高于公司平均薪金的所有员工。

练习4语句参考答案

-- 根据：emp员工表，dept部门表，工资=薪金+佣金
-- (1).列出至少有一个员工的所有部门
select deptno,count(*) as number
		from `emp`
		group by deptno
		having number >=1;
		
-- (2).列出薪金比“SMITH”多的所有员工。
/*
	1.查询出员工名字为‘SMITH’的薪资 作为子查询条件
	2.查询工资比step1高的所有员工
*/
SELECT
	ename,sal 
	FROM `emp` 
	WHERE sal > (
	SELECT sal FROM `emp` 
		WHERE ename = 'SMITH')
	
-- (3).列出受雇日期晚于其直接上级的所有员工。
/*
	关键：自连接
	
*/
select worker.ename '员工名称',worker.hiredate '员工受雇日期',
	leader.ename '领导名称',leader.hiredate '领导受雇日期'
	from `emp` worker,`emp` leader
	where worker.mgr = leader.empno
	and worker.hiredate > leader.hiredate;

-- (4).列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select dname,e.*
	from `department` as d
	left join `emp` as e 
	on d.deptno = e.deptno; 
	 
-- (5).列出所有“CLERK”（办事员）的姓名及其部门名称。
select ename,dname,job
	from `emp` e,`department` d
	where e.deptno = d.deptno
		and job = 'CLERK';
		
-- (6).列出最低薪金大于1500的各种工作。
select min(sal) min_sal, job 
	from `emp`
	group by job having min_sal > 1500;
	
-- (7).列出在部门“SALES”（销售部）工作的员工的姓名。
select ename 
	from `emp` e, `department` d
	where e.deptno = d.deptno
	and dname = 'SALES';
	
-- (8).列出薪金高于公司平均薪金的所有员工。
select AVG(sal)
	from `emp`
	group by empno sal > AVG(sal);

练习5

练习5语句

-- 根据：emp员工表，dept部门表，工资=薪金+佣金

-- (9).列出与“SCOTT”从事相同工作的所有员工。

-- (10).列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

-- (11).列出在每个部门工作的员工数量、平均工资和平均服务期限。

-- (12).列出所有员工的姓名、部门名称和工资。

-- (13).列出所有部门的详细信息和部门人数。

-- (14).列出各种工作的最低工资。

-- (15).列出MANAGER(经理）的最低薪金。

-- (16).列出所有员工的年工资，按年薪从低到高排序。

练习5语句参考答案

-- (9).列出与“SCOTT”从事相同工作的所有员工。
/*
	1.查询员工“SCOTT”从事的工作
	2.step1作为子查询条件，查询相同工作的其他员工
*/
SELECT ename,job 
	FROM `emp` 
	WHERE	job = ( SELECT job FROM `emp` WHERE ename = 'SCOTT' ) and ename != 'SCOTT';
-- (10).列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
/*
	1.先查出30部门的最高工资
	2.step1作为子查询条件，查询比step1工资高的员工姓名和薪资
*/
select ename,sal 
	from `emp`
	where sal > (
		select max(sal) max_sal
		from `emp`
		where deptno = 30
	)

-- avg(DATEDIFF(NOW(),hiredate)
-- (11).列出在每个部门工作的员工数量、平均工资和平均服务期限。
/*
	1.每个部门 按部门分组
	2.查询每个部门的员工数量
	3.查询每个部门的平均工资
	4.查询每个部门的服务期限  DATEDIFF(expr1,expr2)
*/
select deptno,
	count(*) as '员工数量',
	avg(sal) as '平均工资',
	avg(DATEDIFF(NOW(),hiredate)) as '平均价格' 
	from `emp`
	group by deptno;
	
-- (12).列出所有员工的姓名、部门名称和工资。
select ename,dname,sal
	from `emp` e,`department` d 
	where e.deptno = d.deptno
	
-- (13).列出所有部门的详细信息和部门人数。
/*
	1.查询部门人数 作为临时表 tmp
	2.临时表tmp和部门表department联表查询 部门的详细信息和部门人数
*/
select deptno,dname,loc,number
	from `department`,(select count(*) number  from `emp` group by deptno) `tmp` 


-- (14).列出各种工作的最低工资。
select job,MIN(sal) as min_sal from `emp`
	group by job;
	
-- (15).列出MANAGER(经理）的最低薪金。
select MIN(sal) 
	from `emp` 
	where job = 'MANAGER' ;

-- (16).列出所有员工的年工资，按年薪从低到高排序。
/*
	1.查询员工的年工资
	2.按年薪降序升序
*/
select ename,( (sal + IFNULL(comm,0) ) * 12) as total_sal 
	from `emp`
	order by  total_sal asc;

练习基于的数据表

• 数据表模型

细节说明：为方面练习，并为设置主外健

• 练习所需的数据表相关数据

# 创建员工表 `emp`
CREATE TABLE `emp` (
	empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '员工编号',
	ename VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '员工名称',
	job VARCHAR ( 9 ) NOT NULL DEFAULT '' COMMENT '员工岗位',
	mgr MEDIUMINT UNSIGNED COMMENT '领导编号',
	hiredate DATE NOT NULL COMMENT '受雇日期',
	sal DECIMAL ( 7, 2 ) COMMENT '员工工资',
	comm DECIMAL ( 7, 2 ) COMMENT '员工补贴',
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门编号' 
);
# `emp`中插入数据
INSERT INTO emp
VALUES
	( 7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00, NULL, 20 ),
	( 7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30 ),
	( 7521, 'WARD', 'SALESMAN', 7968, '1991-2-22', 1250.00, 500.00, 30 ),
	( 7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00, NULL, 20 ),
	( 7654, 'MARTIN', 'SALESMAN', 7968, '1991-9-28', 1250.00, 1400.00, 30 ),
	( 7698, 'BLAKE', 'MANAGER', 7839, '1991-5-1', 2850.00, NULL, 30 ),
	( 7782, 'CLARK', 'MANAGER', 7839, '1991-6-9', 2450.00, NULL, 10 ),
	( 7788, 'SCOTT', 'ANALYST', 7566, '1991-4-19', 3000.00, NULL, 20 ),
	( 7839, 'KING', 'PRESIDENT', NULL, '1991-11-17', 5000.00, NULL, 10 ),
	( 7844, 'TURNER', 'SALESMAN', 7698, '1991-9-8', 1500.00, NULL, 30 ),
	( 7900, 'JAMES', 'CLERK', 7698, '1991-12-3', 950.00, NULL, 30 ),
	( 7902, 'FORD', 'ANALYST', 7566, '1991-12-3', 3000.00, NULL, 20 ),
	( 7934, 'MILLER', 'CLERK', 7782, '1991-1-23', 1300.00, NULL, 10 );

# 创建部门表`department`
CREATE TABLE `department` (
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门编号',
	dname VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '部门名称',
	loc VARCHAR ( 13 ) NOT NULL DEFAULT '' COMMENT '部门所在地' 
);
# `department`表中插入数据
INSERT INTO department
VALUES
	( 10, 'ACCOUNTING', 'BEIJING' ),
	( 20, 'RESEARCH', 'SHANGHAI' ),
	( 30, 'SALES', 'NANJING' ),
	( 40, 'OPERATIONS', 'CHENGDU' );
		

参考文献

• 韩老师MySQL教程：https://www.bilibili.com/video/BV1H64y1U7GJ?from=search&seid=8288461003915151927&spm_id_from=333.337.0.0
• 易百教程-SQL教程：https://www.yiibai.com/mysql