92 lines
1.4 KiB
SQL
92 lines
1.4 KiB
SQL
/*
|
|
Author: Fromsko
|
|
DATE: 2024.09.10 20:30
|
|
*/
|
|
|
|
-- 更新赵敏的数据
|
|
UPDATE EMP_WHC
|
|
SET
|
|
MGR = 1002
|
|
WHERE
|
|
ENAME = '赵敏';
|
|
|
|
-- 总数居
|
|
SELECT
|
|
*
|
|
FROM
|
|
EMP_WHC;
|
|
|
|
-- 谁是张无忌的下属？ (MGR=EMPNO=1002)
|
|
SELECT
|
|
ENAME
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
MGR IN (
|
|
SELECT
|
|
EMPNO
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
ENAME='张无忌'
|
|
);
|
|
|
|
--
|
|
|
|
/*
|
|
子查询:
|
|
子查询的条件是单列还是多列没关系,
|
|
关键是要分清返回的是单行还是多行
|
|
|
|
- 如果是单行, 用单行比较运算符, =, >, < 这些
|
|
- 如果是多行, 用IN, >ALL, >ANY, <ALL, <ANY 这些
|
|
*/
|
|
|
|
-- 每个部门拿最高薪水的是谁?
|
|
|
|
--- 表外连接查法
|
|
SELECT
|
|
E.ENAME,
|
|
E.SALARY,
|
|
E.DEPTNO
|
|
FROM
|
|
EMP_WHC E
|
|
JOIN (
|
|
SELECT
|
|
DEPTNO,
|
|
MAX(SALARY) NEW_SALARY
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
DEPTNO IS NOT NULL
|
|
GROUP BY
|
|
DEPTNO
|
|
) R
|
|
ON E.DEPTNO=R.DEPTNO
|
|
WHERE
|
|
E.SALARY=R.NEW_SALARY
|
|
ORDER BY
|
|
E.DEPTNO;
|
|
|
|
--- 子查询法
|
|
SELECT
|
|
ENAME,
|
|
SALARY,
|
|
JOB,
|
|
DEPTNO
|
|
FROM
|
|
EMP_WHC
|
|
WHERE -- 顺序必须对上
|
|
(DEPTNO, SALARY) IN (
|
|
SELECT
|
|
DEPTNO,
|
|
MAX(SALARY) NEW_SALARY
|
|
FROM
|
|
EMP_WHC
|
|
WHERE
|
|
DEPTNO IS NOT NULL
|
|
GROUP BY
|
|
DEPTNO
|
|
)
|
|
ORDER BY
|
|
DEPTNO; |