转载声明：本文章为个人总结笔记，主要参考韩老师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;

练习基于的数据表

• 数据表模型

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

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

  创建员工表 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