SQL 查询与操作综合练习-优快云博客

1. 条件更新与查询

1.1 工资调整查询

```sql

-- 为所有员工涨工资，按不同工资范围不同比例调整

SELECT empno, ename, sal,

CASE

WHEN sal < 1000 THEN sal 2

WHEN sal BETWEEN 1000 AND 3000 THEN sal 1.8

WHEN sal > 3000 THEN sal 1.5

END AS 增长后的工资

FROM emp;

```

1.2 特定年份入职查询

```sql

-- 查询所有81年入职的员工信息（三种方式）

SELECT FROM emp WHERE hiredate BETWEEN '1981-01-01' AND '1981-12-31';

SELECT FROM emp WHERE YEAR(hiredate) = 1981;

SELECT FROM emp WHERE hiredate LIKE '1981%';

```

1.3 年薪计算与排序

```sql

-- 计算所有员工的年薪并倒序排列

SELECT ename, sal 12 + IFNULL(comm, 0) AS 年薪

FROM emp

ORDER BY 年薪 DESC;

```

1.4 复杂条件查询

```sql

-- 查询没有奖金，工资在2000-3000之间或者经理编号不是7369的员工信息

SELECT FROM emp

WHERE (comm IS NULL OR comm = 0)

AND (sal BETWEEN 2000 AND 3000)

OR mgr != 7369;

```

2. 数据更新操作

```sql

-- 修改所有不是10号部门的员工工资，提升10%，岗位改为默认值

UPDATE emp SET sal = sal 1.1, job = DEFAULT WHERE deptno != 10;

```

3. 日期函数应用

```sql

-- 查询名字不以a开头员工的转正日期，并提前3天提醒

SELECT ename, hiredate,

DATE_ADD(hiredate, INTERVAL 3 MONTH) AS 转正日期,

DATE_ADD(DATE_ADD(hiredate, INTERVAL 3 MONTH), INTERVAL -3 DAY) AS 提醒日期

FROM emp

WHERE ename NOT LIKE 'a%';

```

4. 字符串操作与排序

```sql

-- 查询名字是4位长度，并且第三位不是a的员工信息

SELECT FROM emp

WHERE LENGTH(ename) = 4 AND ename NOT LIKE '__a_';

-- 按姓名长度和首字母排序

SELECT FROM emp

ORDER BY LENGTH(ename) DESC, SUBSTRING(ename, 1, 1) ASC;

-- 姓名只显示开头和结尾字母，中间用替代

SELECT REPLACE(ename, SUBSTRING(ename FROM 2 FOR LENGTH(ename)-2), '') AS 隐藏姓名

FROM emp;

```

5. 多表连接查询

```sql

-- 查询员工编号、姓名、部门信息

SELECT empno, ename, emp.deptno, dname, loc

FROM emp, dept

WHERE emp.deptno = dept.deptno;

-- 使用JOIN连接

SELECT empno, ename, emp.deptno, dname, loc

FROM emp

INNER JOIN dept ON emp.deptno = dept.deptno;

```

6. 表结构操作

```sql

-- 添加字段、查看表结构和建表语句

ALTER TABLE emp ADD sex VARCHAR(1) AFTER hiredate;

DESC emp;

SHOW CREATE TABLE emp;

```

7. 排序查询

```sql

-- 按入职时间倒序排序

SELECT FROM emp ORDER BY hiredate DESC;

-- 按工作时长排序

SELECT , DATEDIFF(NOW(), hiredate) AS 工作天数

FROM emp

ORDER BY DATEDIFF(NOW(), hiredate);

-- 查询入职超过10年的员工信息

SELECT FROM emp

WHERE DATE_ADD(hiredate, INTERVAL 10 YEAR) < NOW();

```

8. 聚合函数与分组查询

8.1 基本聚合函数

```sql

SELECT

MAX(sal) AS 最高工资,

MIN(sal) AS 最低工资,

AVG(sal) AS 平均工资,

SUM(sal) AS 工资总额,

COUNT(empno) AS 员工总数

FROM emp;

```

8.2 分组查询示例

```sql

-- 各部门人数统计

SELECT deptno, COUNT(empno) AS 人数 FROM emp GROUP BY deptno;

-- 各经理管理员工数

SELECT mgr, COUNT(empno) AS 管理人数 FROM emp GROUP BY mgr;

-- 各岗位人数统计

SELECT job, COUNT(empno) AS 人数 FROM emp GROUP BY job;

```

8.3 HAVING子句应用

```sql

-- 平均工资低于2000的部门

SELECT deptno, AVG(sal) AS 平均工资

FROM emp

GROUP BY deptno

HAVING 平均工资 < 2000;

-- 工资最小值小于2000的职位

SELECT job, MIN(sal) AS 最低工资

FROM emp

GROUP BY job

HAVING 最低工资 < 2000;

-- 各部门工资低于1000的员工人数（人数≥2，按部门倒序）

SELECT deptno, COUNT(empno) AS 人数

FROM emp

WHERE sal < 1000

GROUP BY deptno

HAVING 人数 >= 2

ORDER BY deptno DESC;

```

9. 高级多表查询

9.1 工资等级查询

```sql

-- 查询员工工资等级

SELECT empno, ename, sal, grade

FROM emp e

INNER JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal

ORDER BY grade DESC;

```

9.2 多表连接

```sql

-- 三表连接查询

SELECT ename, sal, grade, loc, dname

FROM emp e

INNER JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal

INNER JOIN dept d ON e.deptno = d.deptno;

```

9.3 外连接查询

```sql

-- 左外连接：显示所有员工，包括没有部门的员工

SELECT ename, sal, loc, d.deptno

FROM emp e

LEFT JOIN dept d ON e.deptno = d.deptno;

```

9.4 自连接查询

```sql

-- 查询所有经理信息（自连接方式）

SELECT DISTINCT e2.

FROM emp e1

INNER JOIN emp e2 ON e1.mgr = e2.empno;

```

10. 子查询应用

10.1 不相关子查询

```sql

-- 比CLARK工资高的员工

SELECT FROM emp

WHERE sal > (SELECT sal FROM emp WHERE ename = 'CLARK');

-- 比10号部门所有员工工资都高的员工

SELECT FROM emp

WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 10);

```

10.2 相关子查询

```sql

-- 工资是本部门最高的员工

SELECT FROM emp e1

WHERE sal = (SELECT MAX(sal) FROM emp e2 WHERE e2.deptno = e1.deptno);

-- 工资高于本部门平均工资的员工

SELECT FROM emp e2

WHERE sal > (SELECT AVG(sal) FROM emp e1 WHERE e1.deptno = e2.deptno);

```

10.3 UNION联合查询

```sql

-- 各部门工资高于本部门平均工资的员工

SELECT FROM emp

WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 10) AND deptno = 10

UNION

SELECT FROM emp

WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 20) AND deptno = 20

UNION

SELECT FROM emp

WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 30) AND deptno = 30;

```

总结

本练习涵盖了SQL的多个重要方面，包括：

1. 条件查询：使用WHERE子句和各种运算符

2. 函数应用：字符串函数、日期函数、数学函数等

3. 聚合与分组：GROUP BY和HAVING的使用

4. 多表连接：INNER JOIN、LEFT JOIN等连接方式

5. 子查询：相关子查询和不相关子查询

6. 数据更新：UPDATE语句的使用

这些练习有助于深入理解SQL的各种功能和用法，是数据库操作和数据分析的重要基础。