Day10 - SQL高级查询与数据库设计精要

## 一、多表连接

#### 1、自连接

自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，操作与多表连接一样。

##### 查询每个员工上司的姓名

```sql
SELECT worker.ename,worker.mgr,manager.EMPno,manager.ename
FROM emp worker,emp manager
WHERE worker.mgr = manager.EMPno
```

##### 查询所有工作在New York和CHICAGO的员工姓名、员工编号以及他们的经理姓名、经理编号

```sql
SELECT e.ename '员工姓名',e.EMPno '员工编号',m.ename '经理姓名',m.EMPno '经理编号',dept.loc '工作地点'
FROM emp e,emp m,dept
WHERE e.mgr = m.EMPno
AND e.DEPTno=dept.DEPTno
AND dept.loc IN ('NEW YORK','CHICAGO');
```

##### 查询入职日期在1981-05-05以后的所有员工的姓名，上级领导名字、入职日期，部门名称，并按照入职时间升序排列

```sql
SELECT e.ename '员工姓名',m.ename '领导名字',e.hiredate '入职日期',dept.dname '部门名称'
FROM emp e,emp m,dept
WHERE e.mgr = m.EMPno
AND e.DEPTno = dept.DEPTno
AND e.hiredate>'1981-05-05'
ORDER BY e.hiredate;
```

#### 2、外连接

以`JOIN`为分界线区分左右表

左外连接,以左表为主表把左表与相交部分取出

右外连接,以右表为主表把右表与相交部分取出

**语法**

```sql
SELECT <字段>
FROM LEFT_TABLE
LEFT JOIN RIGHT_TABLE
ON <连接条件>
WHERE <判断条件>
```

##### 查询所有雇员姓名,部门编号,部门名称,包括没有部门的员工也要显示出来

以emp表为主表与dept表进行关联查询

新增一个没有部门的数据:

```sql
INSERT INTO emp VALUES(NULL,'王五','CLERK',7788,'2025-10-09',2000.00,100.00,NULL)
```

**左连接**

```sql
SELECT *
FROM emp
LEFT JOIN dept
ON emp.DEPTno=dept.DEPTno
```

**内连接无法查询到为NULL的记录**

```sql
SELECT *
FROM dept
JOIN emp
ON emp.DEPTno=dept.DEPTno
```

**右连接**

```sql
SELECT *
FROM dept
RIGHT JOIN emp
ON emp.DEPTno=dept.DEPTno 
```

##### 查询所有雇员姓名,部门编号,部门名称,包括没有员工的部门也要显示出来

```sql
SELECT emp.ename,dept.DEPTno,dept.dname
FROM emp
RIGHT JOIN dept
ON emp.DEPTno=dept.DEPTno
```

#### 3、全连接

虽然在MySQL中语法不支持`FULL JOIN`关键字，但可以通过对表数据的操作来实现全连接的效果

① 取出两个表的所有数据,除去重复

② A表左连接B表

③ UNION去重

④ A表右连接B表

```sql
SELECT *
FROM emp
LEFT JOIN dept
ON emp.DEPTno=dept.DEPTno
UNION
SELECT *
FROM emp
RIGHT JOIN dept
ON emp.DEPTno=dept.DEPTno;
```

#### 4、左外连接

取出左表所有内容去除与右表相交的部分

##### 显示没有部门的员工

```sql
SELECT *
FROM emp
LEFT JOIN dept
ON emp.DEPTno=dept.DEPTno
WHERE dept.DEPTno IS NULL;
```

#### 5、右外连接

取出右表所有内容去除与左表相交的部分

##### 查询部门中没有员工的信息

```sql
SELECT *
FROM emp
RIGHT JOIN dept
ON emp.DEPTno=dept.DEPTno
WHERE emp.EMPno IS NULL;
```

#### 6、自然连接(慎用)

自然连接完全依靠表字段自动查询匹配条件,所以不需要on连接

只有同名才能使用自然连接

如果连接条件不成立,会产生笛卡尔积

**自然内连接**

```sql
SELECT <字段列表>
FROM TABLE_01
NATURAL JOIN TABLE_02;
```

**自然外连接**

```sql
SELECT <字段列表>
FROM TABLE_01

NATURAL LEFT/RIGHT JOIN TABLE_02;
```

##### 显示没有部门的员工

```sql
SELECT *
FROM emp
NATURAL LEFT JOIN dept
WHERE dept.DEPTno IS NULL;
```

#### 7、USING关键字

引用在JOIN,LEFT JOIN,RIGHT JOIN

代替ON,直接引用两张表相同的字段

##### 查询员工所有信息及其部门信息

```sql
SELECT *
FROM emp,
JOIN dept
USING (DEPTno);
```

#### 8、关于多表连接的一些问题

**① 内连接与外连接的区别**

内连接使用INNER JOIN关键字，外连接使用OUTER JOIN关键字；

内连接查询两表之间的交集部分；

外连接分为左外连接与右外连接，分别使用LEFT JOIN，RIGHT JOIN

如果是左外连接则以左表为主表，加上交集部分，作为查询结果展示出来

如果是右外连接则以右表为主表，加上交集部分，作为查询结果展示出来

**② 左连接与右连接区别**

左外连接使用LEFT JOIN，右外连接使用RIGHT JOIN

左外连接则以左表为主表，加上交集部分，作为查询结果展示出来

右外连接则以右表为主表，加上交集部分，作为查询结果展示出来

**③ 多表连接的方式都有哪些(SQL89和SQL92的区别)**

在连接方式上有内连接、外连接、自然连接

在书写方式上可以使用SQL92、SQL89标准

如果使用SQL89则直接使用WHERE关键字连接

如果使用SQL92则使用JOIN和ON关键字连接

**④ ON、WHERE、HAVING三个关键字的执行顺序**

ON最先执行，生成临时表

WHERE次之，对临时表进行筛选

HAVING最后，WHERE条件筛选后对分组后的数据再次筛选

**⑤ 多表查询是，WHERE和ON可以同时出现吗？**

可以

**⑥ UNION与UNION ALL的区别**

表示联合查询，UNION去重，UNION ALL不去重

## 二、子查询

子查询或称为内部查询、嵌套查询

在SELECT、FROM、WHERE子句中嵌入查询语句

#### 1、分类

##### 按位置分类

FROM子查询：做数据源

WHERE、HAVING子查询：做数据条件

##### 按子查询的结果分类

标量子查询(单行单列)：多用于在WHERE后作为查询条件使用

多行子查询(多行单列)

单行子查询(单行多列)

表子查询(多行多列)

#### 2、标量子查询(单列单行)

##### 在WHERE中使用子查询：查询emp表中比JONES的工资高的其他雇员

```sql
SELECT *
FROM emp
WHERE sal > (
  SELECT emp.sal
  FROM emp
  WHERE emp.ename='JONES'
);
```

##### 查询显示工作在NEW YORK的所有员工信息

```sql
SELECT *
FROM emp
WHERE emp.DEPTno=(
  SELECT dept.DEPTno
  FROM dept
  WHERE dept.loc = 'NEW YORK'
);
```

##### 在子查询中使用分组函数：查询工资最低的员工姓名、岗位、及工资

```sql
SELECT emp.ename,emp.job,emp.sal
FROM emp
WHERE emp.sal=(
  SELECT MIN(sal)
  FROM emp
);
```

##### 在HAVING中使用子查询：查询每个部门最低工资，显示比20部门最低工资高的部门编号及最低工资

```sql
SELECT emp.DEPTno,MIN(emp.sal)
FROM emp
GROUP BY emp.DEPTno
HAVING MIN(emp.sal)>(
  SELECT MIN(emp.sal)
  FROM emp
  WHERE emp.DEPTno=20
);
```

#### 3、多行子查询(单列多行)

多使用 IN 操作符

##### 查询所有领导的名字和薪资

```sql
SELECT emp.ename,emp.sal
FROM emp
WHERE emp.EMPno IN (
  SELECT emp.mgr
  FROM emp
  WHERE emp.mgr IS NOT NULL
);
```

##### **ANY 关键字**

表示和子查询的任意一行结果进行比较，有一个满足条件即可

`< ANY` :小于最大值
`> ANY` :大于最小值
`= ANY` :等于任何一个都可

##### 查询是经理的员工姓名，工资

```sql
SELECT emp.ename,emp.sal
FROM emp
WHERE emp.EMPno = ANY (
  SELECT emp.mgr
  FROM emp
  WHERE emp.mgr IS NOT NULL
);
```

##### 查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资

```sql
SELECT emp.EMPno,emp.ename,emp.job,emp.sal
FROM emp
WHERE emp.DEPTno<>10
AND emp.sal > ANY (
 SELECT emp.sal
 FROM emp
 WHERE emp.DEPTno=10
);
```

##### **ALL 关键字**

表示和子查询的所有行结果进行比较，每一行必须都满足条件

`< ALL` :小于最小值
`> ALL` :大于最大值
`= ALL `:所有行都相等

##### 查询部门编号不为20，且工资比20部门任何一个员工工资高的员工编号，姓名，职位，工资

```sql
SELECT emp.EMPno,emp.ename,emp.job,emp.sal
FROM emp
WHERE emp.DEPTno<>20
AND emp.sal > ALL (
 SELECT emp.sal
 FROM emp
 WHERE emp.DEPTno=20
);
```

##### 查询部门编号不为10，且工资比10部门任何一个员工工资低的员工编号，姓名，职位，工资

```sql
SELECT emp.EMPno,emp.ename,emp.job,emp.sal
FROM emp
WHERE emp.DEPTno<>10
AND emp.sal < ALL (
 SELECT emp.sal
 FROM emp
 WHERE emp.DEPTno=10
);
```

#### 4、单行子查询(一行多列)

**语法**

```sql
SELECT <字段列表>
FROM TABLENAME
WHERE (COLUMN1,COLUMN2,...) = (
  SELECT COLUMN1,COLUMN2,...
  FROM TABLENAME
  WHERE <子查询条件>
)
```

**注意**

子查询返回的结果只能是一行多列

主查询中WHERE后的字段列表需要与子查询创造的结果表中的列表顺序保持一致

##### 查找职位和部门都和WARD相同的员工信息

```sql
SELECT *
FROM emp
WHERE (emp.DEPTno,emp.job) = (
  SELECT emp.DEPTno,emp.job
  FROM emp
  WHERE emp.ename='WARD'
);
```

##### 查询领导和部门都与SMITH相同的员工信息

```sql
SELECT *
FROM emp
WHERE (emp.mgr,emp.DEPTno) = (
  SELECT emp.mgr,emp.DEPTno
  FROM emp
  WHERE emp.ename='SMITH'
);
```

##### 查询不是经理的员工姓名

```sql
SELECT *
FROM emp
WHERE emp.EMPno NOT IN (
  SELECT emp.mgr
  FROM emp
  WHERE emp.mgr IS NOT NULL
);
```

#### 5、表子查询(多行多列)

**在FROM子句中使用子查询**

##### 查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

```sql
SELECT emp.ename,emp.sal,emp.DEPTno,AVG(emp.sal)
FROM emp,(
  SELECT emp.DEPTno,AVG(emp.sal) avgS
  FROM emp
  GROUP BY emp.DEPTno
) avgSal
WHERE emp.DEPTno=avgSal.DEPTno
AND emp.sal>avgSal.avgS
GROUP BY emp.ename;
```

##### 查询部门平均薪资所在的等级

```sql
SELECT dept.DEPTno,salgrade.grade
FROM dept,salgrade,(
  SELECT emp.DEPTno, AVG(emp.sal) avgS
  FROM emp
  GROUP BY emp.DEPTno
) avgSal
WHERE dept.DEPTno=avgSal.DEPTno
AND avgSal.avgS BETWEEN salgrade.losal AND salgrade.hisal;
```

## 三、数据库基本介绍

#### 1、数据库范式

##### ① 第一范式 (1NF) - 原子性基础

核心要求： 表中的每个列（字段）都必须是不可再分的基本数据项。即每个字段的值都必须是原子性的，不能是集合、数组或组合值。

解决的问题： 保证最基本的字段值原子性，消除重复的列。

违反示例：

列值非原子（一个单元格存多个值）

| **违规表：`员工技能表`** |           |      |
| :----------------------- | :-------- | ---- |
| **员工ID (PK)**          | **技能**  | 姓名 |
| E001                     | Java, SQL | 张三 |
| E002                     | Python    | 李四 |

- **违反点：** `技能` 字段存储了多个值（非原子）。

解决方法：拆分非原子列

| **修正后满足1NF的表1：`员工表`** |          |
| :------------------------------- | :------- |
| **员工ID (PK)**                  | **姓名** |
| E001                             | 张三     |
| E002                             | 李四     |

| **修正后满足1NF的表2：`技能表`** |               |
| :------------------------------- | :------------ |
| **员工ID (PK/FK)**               | **技能 (PK)** |
| E001                             | Java          |
| E001                             | SQL           |
| E002                             | Python        |

##### ② 第二范式 (2NF) - 在1NF基础上消除部分依赖

核心要求：

必须先满足1NF。

表中的所有非主键字段必须完全依赖于整个主键，而不能只依赖于主键的一部分（即消除部分依赖）。只有拥有复合主键的表才需要检查是否满足2NF。

解决的问题： 消除非主键字段对部分主键的依赖，减少冗余和更新异常。

违反示例（复合主键场景）：

存在部分依赖 (非主属性仅依赖于部分主键)
*(前提：该表有复合主键)*

| **违规表：`订单明细表`** |                  |              |          |
| :----------------------- | :--------------- | :----------- | :------- |
| **订单ID (PK1)**         | **产品ID (PK2)** | **产品名称** | **数量** |
| ORD001                   | P1001            | 手机         | 2        |
| ORD001                   | P2002            | 耳机         | 1        |

- **违反点：**
  - `产品名称` 只依赖于 `产品ID` (主键的一部分)，而非整个主键 `(订单ID, 产品ID)`。
  - （如果表里还有 `订单日期`，则只依赖于 `订单ID`，也属部分依赖）。

解决方法：拆表消除部分依赖

| **修正后满足2NF的表1：`订单表`** |              |
| :------------------------------- | :----------- |
| **订单ID (PK)**                  | **订单日期** |
| ORD001                           | 2023-10-01   |

| **修正后满足2NF的表2：`产品表`** |              |
| :------------------------------- | :----------- |
| **产品ID (PK)**                  | **产品名称** |
| P1001                            | 手机         |
| P2002                            | 耳机         |

| **修正后满足2NF的表3：`订单明细表`** |                    |          |
| :----------------------------------- | :----------------- | :------- |
| **订单ID (PK/FK)**                   | **产品ID (PK/FK)** | **数量** |
| ORD001                               | P1001              | 2        |
| ORD001                               | P2002              | 1        |

##### ③ 第三范式 (3NF) - 在2NF基础上消除传递依赖

核心要求：

必须先满足2NF。

表中的所有非主键字段之间不能有传递依赖关系（即非主键字段不能依赖于其他非主键字段）。

解决的问题： 进一步消除传递依赖，减少冗余和更新异常，确保数据一致性。

违反示例：

存在传递依赖 (非主属性A依赖于非主属性B)

| **违规表：`员工表`** |          |            |              |              |
| :------------------- | :------- | :--------- | :----------- | :----------- |
| **员工ID (PK)**      | **姓名** | **部门ID** | **部门名称** | **部门地址** |
| E001                 | 张三     | D01        | 研发部       | 北京A座      |
| E002                 | 李四     | D01        | 研发部       | 北京A座      |

- **违反点：**
  - `部门名称` 和 `部门地址` 依赖于 `部门ID` (非主属性)，而非直接依赖于主键 `员工ID`。
    (即：`员工ID -> 部门ID -> (部门名称, 部门地址)` 形成传递依赖链)。

解决方法：拆表消除传递依赖

| **修正后满足3NF的表1：`部门表`** |              |              |
| :------------------------------- | :----------- | :----------- |
| **部门ID (PK)**                  | **部门名称** | **部门地址** |
| D01                              | 研发部       | 北京A座      |

| **修正后满足3NF的表2：`员工表`** |          |                 |
| :------------------------------- | :------- | :-------------- |
| **员工ID (PK)**                  | **姓名** | **部门ID (FK)** |
| E001                             | 张三     | D01             |
| E002                             | 李四     | D01             |

##### ④ 逆规范化

逆规范化（Denormalization）是数据库设计中的一种**性能优化策略**，核心目的是**牺牲部分数据冗余来提升查询效率**，尤其适用于高并发查询或复杂分析的场景。

#### 2、数据库数据类型

#### 3、数据库引擎

| **存储引擎**         | **事务支持** | **外键支持** | **数据缓存**            | **索引特性**                                     | **崩溃恢复**     |
| :------------------- | :----------- | :----------- | :---------------------- | :----------------------------------------------- | :--------------- |
| InnoDB (默认引擎)    | ✅ ACID 事务 | ✅           | ✅ 缓冲池（内存缓存）   | 聚簇索引（主键高效查询），全文索引（需FULLTEXT） | ✅ Redo/Undo日志 |
| MyISAM (历史常用)    | ❌           | ❌           | ❌ 仅缓存索引           | 非聚簇索引（数据与索引分离），支持空间索引       | ❌ 易损坏需修复  |
| Memory (内存引擎)    | ❌           | ❌           | ❌ 内存存储（无持久化） | Hash/B-Tree 索引                                 | ❌ 重启丢失数据  |
| Archive (归档引擎)   | ❌           | ❌           | ❌                      | 仅支持自增索引                                   | ❌               |
| CSV (文件引擎)       | ❌           | ❌           | ❌                      | 无索引                                           | ❌               |
| Blackhole (黑洞引擎) | ❌           | ❌           | ❌                      | 无数据存储                                       | ❌               |

## 四、DDL操作

#### 1、创建数据库

```sql
CREATE DATABASE [IF NOT EXISTS] DATABASENAME [可选选项];
-- 可选选项：
-- CHARACTER SET 字符集(utf-8)
-- charset [=] 编码集(Unicode)
```

#### 2、查看数据库

```sql
SHOW DATABASES;
```

#### 3、使用数据库

```sql
USE DATABASENAME;
```

#### 4、查看数据库创建命令

```sql
SHOW CREATE DATABASE DATABASENAME;
```

#### 5、查看正在使用的数据库名字

```sql
SELECT DATABASE();
```

#### 6、数据库修改

理论上数据库一旦确定，不允许修改，但若为空表可以修改

```sql
ALTER DATABASE DATABASENAME DEFAULT CHARACTER SET [字符集];
```

#### 7、数据库删除

**数据库的删除操作一定要谨慎！**

```sql
DROP DATABASE DATABASENAME;
```

#### 8、创建表

```sql
CREATE TABLE TABLENAME(
    字段1 数据类型,
    字段2 数据类型,
    ...
)[可选参数];
-- charset [=] 编码集(Unicode)
-- ENGINE [INNODB]
-- engine [=] INNODB
```

#### 9、显示所有的数据库表

```sql
SHOW TABLES;
-- 显示指定数据库的所有表
SHOW TABLES FROM DATABASENAME;
-- 模糊查找数据库中的表
SHOW TABLES LIKE '%KEY%';
```

#### 10、显示数据库表的创建命令

```sql
SHOW CREATE TABLE TABLENAME;
```

#### 11、查看表结构

```sql
DESC TABLENAME;
```

#### 12、复制表

```sql
CREATE TABLE NEWTABLE LIKE OLDTABLE
CREATE TABLE NEWTABLE SELECT 语句
```

## 一、多表连接

#### 1、自连接

自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，操作与多表连接一样。

##### 查询每个员工上司的姓名

```sql
SELECT worker.ename,worker.mgr,manager.EMPno,manager.ename
FROM emp worker,emp manager
WHERE worker.mgr = manager.EMPno
```

##### 查询所有工作在New York和CHICAGO的员工姓名、员工编号以及他们的经理姓名、经理编号

##### 查询入职日期在1981-05-05以后的所有员工的姓名，上级领导名字、入职日期，部门名称，并按照入职时间升序排列

#### 2、外连接

以`JOIN`为分界线区分左右表

左外连接,以左表为主表把左表与相交部分取出

右外连接,以右表为主表把右表与相交部分取出

**语法**

```sql
SELECT <字段>
FROM LEFT_TABLE
LEFT JOIN RIGHT_TABLE
ON <连接条件>
WHERE <判断条件>
```

##### 查询所有雇员姓名,部门编号,部门名称,包括没有部门的员工也要显示出来

以emp表为主表与dept表进行关联查询

新增一个没有部门的数据:

```sql
INSERT INTO emp VALUES(NULL,'王五','CLERK',7788,'2025-10-09',2000.00,100.00,NULL)
```

**左连接**

```sql
SELECT *
FROM emp
LEFT JOIN dept
ON emp.DEPTno=dept.DEPTno
```

**内连接无法查询到为NULL的记录**

```sql
SELECT *
FROM dept
JOIN emp
ON emp.DEPTno=dept.DEPTno
```

**右连接**

```sql
SELECT *
FROM dept
RIGHT JOIN emp
ON emp.DEPTno=dept.DEPTno 
```

##### 查询所有雇员姓名,部门编号,部门名称,包括没有员工的部门也要显示出来

```sql
SELECT emp.ename,dept.DEPTno,dept.dname
FROM emp
RIGHT JOIN dept
ON emp.DEPTno=dept.DEPTno
```

#### 3、全连接

虽然在MySQL中语法不支持`FULL JOIN`关键字，但可以通过对表数据的操作来实现全连接的效果

① 取出两个表的所有数据,除去重复

② A表左连接B表

③ UNION去重

④ A表右连接B表

```sql
SELECT *
FROM emp
LEFT JOIN dept
ON emp.DEPTno=dept.DEPTno
UNION
SELECT *
FROM emp
RIGHT JOIN dept
ON emp.DEPTno=dept.DEPTno;
```

#### 4、左外连接

取出左表所有内容去除与右表相交的部分

##### 显示没有部门的员工

```sql
SELECT *
FROM emp
LEFT JOIN dept
ON emp.DEPTno=dept.DEPTno
WHERE dept.DEPTno IS NULL;
```

#### 5、右外连接

取出右表所有内容去除与左表相交的部分

##### 查询部门中没有员工的信息

```sql
SELECT *
FROM emp
RIGHT JOIN dept
ON emp.DEPTno=dept.DEPTno
WHERE emp.EMPno IS NULL;
```

#### 6、自然连接(慎用)

自然连接完全依靠表字段自动查询匹配条件,所以不需要on连接

只有同名才能使用自然连接

如果连接条件不成立,会产生笛卡尔积

**自然内连接**

```sql
SELECT <字段列表>
FROM TABLE_01
NATURAL JOIN TABLE_02;
```

**自然外连接**

```sql
SELECT <字段列表>
FROM TABLE_01

NATURAL LEFT/RIGHT JOIN TABLE_02;
```

##### 显示没有部门的员工

```sql
SELECT *
FROM emp
NATURAL LEFT JOIN dept
WHERE dept.DEPTno IS NULL;
```

#### 7、USING关键字

引用在JOIN,LEFT JOIN,RIGHT JOIN

代替ON,直接引用两张表相同的字段

##### 查询员工所有信息及其部门信息

```sql
SELECT *
FROM emp,
JOIN dept
USING (DEPTno);
```

#### 8、关于多表连接的一些问题

**① 内连接与外连接的区别**

内连接使用INNER JOIN关键字，外连接使用OUTER JOIN关键字；

内连接查询两表之间的交集部分；

外连接分为左外连接与右外连接，分别使用LEFT JOIN，RIGHT JOIN

如果是左外连接则以左表为主表，加上交集部分，作为查询结果展示出来

如果是右外连接则以右表为主表，加上交集部分，作为查询结果展示出来

**② 左连接与右连接区别**

左外连接使用LEFT JOIN，右外连接使用RIGHT JOIN

左外连接则以左表为主表，加上交集部分，作为查询结果展示出来

右外连接则以右表为主表，加上交集部分，作为查询结果展示出来

**③ 多表连接的方式都有哪些(SQL89和SQL92的区别)**

在连接方式上有内连接、外连接、自然连接

在书写方式上可以使用SQL92、SQL89标准

如果使用SQL89则直接使用WHERE关键字连接

如果使用SQL92则使用JOIN和ON关键字连接

**④ ON、WHERE、HAVING三个关键字的执行顺序**

ON最先执行，生成临时表

WHERE次之，对临时表进行筛选

HAVING最后，WHERE条件筛选后对分组后的数据再次筛选

**⑤ 多表查询是，WHERE和ON可以同时出现吗？**

可以

**⑥ UNION与UNION ALL的区别**

表示联合查询，UNION去重，UNION ALL不去重

## 二、子查询

子查询或称为内部查询、嵌套查询

在SELECT、FROM、WHERE子句中嵌入查询语句

#### 1、分类

##### 按位置分类

FROM子查询：做数据源

WHERE、HAVING子查询：做数据条件

##### 按子查询的结果分类

标量子查询(单行单列)：多用于在WHERE后作为查询条件使用

多行子查询(多行单列)

单行子查询(单行多列)

表子查询(多行多列)

#### 2、标量子查询(单列单行)

##### 在WHERE中使用子查询：查询emp表中比JONES的工资高的其他雇员

```sql
SELECT *
FROM emp
WHERE sal > (
  SELECT emp.sal
  FROM emp
  WHERE emp.ename='JONES'
);
```

##### 查询显示工作在NEW YORK的所有员工信息

```sql
SELECT *
FROM emp
WHERE emp.DEPTno=(
  SELECT dept.DEPTno
  FROM dept
  WHERE dept.loc = 'NEW YORK'
);
```

##### 在子查询中使用分组函数：查询工资最低的员工姓名、岗位、及工资

```sql
SELECT emp.ename,emp.job,emp.sal
FROM emp
WHERE emp.sal=(
  SELECT MIN(sal)
  FROM emp
);
```

##### 在HAVING中使用子查询：查询每个部门最低工资，显示比20部门最低工资高的部门编号及最低工资

```sql
SELECT emp.DEPTno,MIN(emp.sal)
FROM emp
GROUP BY emp.DEPTno
HAVING MIN(emp.sal)>(
  SELECT MIN(emp.sal)
  FROM emp
  WHERE emp.DEPTno=20
);
```

#### 3、多行子查询(单列多行)

多使用 IN 操作符

##### 查询所有领导的名字和薪资

```sql
SELECT emp.ename,emp.sal
FROM emp
WHERE emp.EMPno IN (
  SELECT emp.mgr
  FROM emp
  WHERE emp.mgr IS NOT NULL
);
```

##### **ANY 关键字**

表示和子查询的任意一行结果进行比较，有一个满足条件即可

`< ANY` :小于最大值
`> ANY` :大于最小值
`= ANY` :等于任何一个都可

##### 查询是经理的员工姓名，工资

```sql
SELECT emp.ename,emp.sal
FROM emp
WHERE emp.EMPno = ANY (
  SELECT emp.mgr
  FROM emp
  WHERE emp.mgr IS NOT NULL
);
```

##### 查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资

```sql
SELECT emp.EMPno,emp.ename,emp.job,emp.sal
FROM emp
WHERE emp.DEPTno<>10
AND emp.sal > ANY (
 SELECT emp.sal
 FROM emp
 WHERE emp.DEPTno=10
);
```

##### **ALL 关键字**

表示和子查询的所有行结果进行比较，每一行必须都满足条件

`< ALL` :小于最小值
`> ALL` :大于最大值
`= ALL `:所有行都相等

##### 查询部门编号不为20，且工资比20部门任何一个员工工资高的员工编号，姓名，职位，工资

```sql
SELECT emp.EMPno,emp.ename,emp.job,emp.sal
FROM emp
WHERE emp.DEPTno<>20
AND emp.sal > ALL (
 SELECT emp.sal
 FROM emp
 WHERE emp.DEPTno=20
);
```

##### 查询部门编号不为10，且工资比10部门任何一个员工工资低的员工编号，姓名，职位，工资

```sql
SELECT emp.EMPno,emp.ename,emp.job,emp.sal
FROM emp
WHERE emp.DEPTno<>10
AND emp.sal < ALL (
 SELECT emp.sal
 FROM emp
 WHERE emp.DEPTno=10
);
```

#### 4、单行子查询(一行多列)

**语法**

```sql
SELECT <字段列表>
FROM TABLENAME
WHERE (COLUMN1,COLUMN2,...) = (
  SELECT COLUMN1,COLUMN2,...
  FROM TABLENAME
  WHERE <子查询条件>
)
```

**注意**

子查询返回的结果只能是一行多列

主查询中WHERE后的字段列表需要与子查询创造的结果表中的列表顺序保持一致

##### 查找职位和部门都和WARD相同的员工信息

```sql
SELECT *
FROM emp
WHERE (emp.DEPTno,emp.job) = (
  SELECT emp.DEPTno,emp.job
  FROM emp
  WHERE emp.ename='WARD'
);
```

##### 查询领导和部门都与SMITH相同的员工信息

```sql
SELECT *
FROM emp
WHERE (emp.mgr,emp.DEPTno) = (
  SELECT emp.mgr,emp.DEPTno
  FROM emp
  WHERE emp.ename='SMITH'
);
```

##### 查询不是经理的员工姓名

```sql
SELECT *
FROM emp
WHERE emp.EMPno NOT IN (
  SELECT emp.mgr
  FROM emp
  WHERE emp.mgr IS NOT NULL
);
```

#### 5、表子查询(多行多列)

**在FROM子句中使用子查询**

##### 查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

##### 查询部门平均薪资所在的等级

## 三、数据库基本介绍

#### 1、数据库范式

##### ① 第一范式 (1NF) - 原子性基础

核心要求： 表中的每个列（字段）都必须是不可再分的基本数据项。即每个字段的值都必须是原子性的，不能是集合、数组或组合值。

解决的问题： 保证最基本的字段值原子性，消除重复的列。

违反示例：

列值非原子（一个单元格存多个值）

- **违反点：** `技能` 字段存储了多个值（非原子）。

解决方法：拆分非原子列

##### ② 第二范式 (2NF) - 在1NF基础上消除部分依赖

核心要求：

必须先满足1NF。

表中的所有非主键字段必须完全依赖于整个主键，而不能只依赖于主键的一部分（即消除部分依赖）。只有拥有复合主键的表才需要检查是否满足2NF。

解决的问题： 消除非主键字段对部分主键的依赖，减少冗余和更新异常。

违反示例（复合主键场景）：

存在部分依赖 (非主属性仅依赖于部分主键)
*(前提：该表有复合主键)*

解决方法：拆表消除部分依赖

##### ③ 第三范式 (3NF) - 在2NF基础上消除传递依赖

核心要求：

必须先满足2NF。

表中的所有非主键字段之间不能有传递依赖关系（即非主键字段不能依赖于其他非主键字段）。

解决的问题： 进一步消除传递依赖，减少冗余和更新异常，确保数据一致性。

违反示例：

存在传递依赖 (非主属性A依赖于非主属性B)

解决方法：拆表消除传递依赖

##### ④ 逆规范化

#### 2、数据库数据类型

#### 3、数据库引擎

## 四、DDL操作

#### 1、创建数据库

```sql
CREATE DATABASE [IF NOT EXISTS] DATABASENAME [可选选项];
-- 可选选项：
-- CHARACTER SET 字符集(utf-8)
-- charset [=] 编码集(Unicode)
```

#### 2、查看数据库

```sql
SHOW DATABASES;
```

#### 3、使用数据库

```sql
USE DATABASENAME;
```

#### 4、查看数据库创建命令

```sql
SHOW CREATE DATABASE DATABASENAME;
```

#### 5、查看正在使用的数据库名字

```sql
SELECT DATABASE();
```

#### 6、数据库修改

理论上数据库一旦确定，不允许修改，但若为空表可以修改

```sql
ALTER DATABASE DATABASENAME DEFAULT CHARACTER SET [字符集];
```

#### 7、数据库删除

**数据库的删除操作一定要谨慎！**

```sql
DROP DATABASE DATABASENAME;
```

#### 8、创建表

```sql
CREATE TABLE TABLENAME(
    字段1 数据类型,
    字段2 数据类型,
    ...
)[可选参数];
-- charset [=] 编码集(Unicode)
-- ENGINE [INNODB]
-- engine [=] INNODB
```

#### 9、显示所有的数据库表

```sql
SHOW TABLES;
-- 显示指定数据库的所有表
SHOW TABLES FROM DATABASENAME;
-- 模糊查找数据库中的表
SHOW TABLES LIKE '%KEY%';
```

#### 10、显示数据库表的创建命令

```sql
SHOW CREATE TABLE TABLENAME;
```

#### 11、查看表结构

```sql
DESC TABLENAME;
```

#### 12、复制表

```sql
CREATE TABLE NEWTABLE LIKE OLDTABLE
CREATE TABLE NEWTABLE SELECT 语句
```

最后修改：2025 年 10 月 22 日

👆请简单粗暴地爱我。

Day10 - SQL高级查询与数据库设计精要

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Spring框架依赖注入

MySQL知识扩展

Day12 - JDBC驱动安装及其简单使用方法

Java常用注解表

Electron的简介

Day14 - elm后端MyBatis版

Day1 - 从环境搭建到核心语法

Java文件读写操作

Day24 - SpringAOP与SpringMVC基础

Vue使用Echart5

Day10 - SQL高级查询与数据库设计精要

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Day10 - SQL高级查询与数据库设计精要

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款