一、SQL语言分类
DDL（数据定义语言）
功能：定义/修改数据库结构（表、索引、视图）。
核心语句：CREATE, ALTER, DROP。
DML（数据操纵语言）
功能：操作数据（增删改查）。
核心语句：INSERT, UPDATE, DELETE, SELECT。
DCL（数据控制语言）
功能：管理权限。
核心语句：GRANT, REVOKE。
二、数据定义语言（DDL）
1. 表操作
CREATE TABLE
功能：创建新表，定义列、数据类型及约束。
示例：
CREATE TABLE Employee (
Eno CHAR(4) PRIMARY KEY, — 主键约束
Ename VARCHAR(10) NOT NULL, — 非空约束
Age INT CHECK (Age >= 18), — 检查约束（年龄≥18）
Dno CHAR(2) REFERENCES Department(Dno) — 外键约束
);
ALTER TABLE
功能：修改表结构（增删列、约束）。
示例：
ALTER TABLE Employee ADD COLUMN Phone VARCHAR(11); — 新增列
ALTER TABLE Employee DROP COLUMN Phone; — 删除列
ALTER TABLE Employee ALTER COLUMN Age TINYINT; — 修改列类型
DROP TABLE
功能：删除表（结构+数据）。
对比：
DELETE FROM Employee：仅删除数据，保留表结构。
TRUNCATE TABLE Employee：快速清空表，不可回滚。
2. 索引（Index）
CREATE INDEX
功能：加速查询，支持排序/分组。
类型：
聚集索引：数据物理排序与索引一致（如字典目录）。
非聚集索引：独立存储索引和数据地址（如图书馆书名索引）。
示例：
CREATE CLUSTERED INDEX idx_dno ON Employee(Dno); — 聚集索引
CREATE NONCLUSTERED INDEX idx_ename ON Employee(Ename); — 非聚集索引
设计原则：
适用于大表、高频查询列（如WHERE、JOIN条件列）。
避免在小表或低选择性列（如性别）建索引。
3. 视图（View）
CREATE VIEW
功能：基于查询结果的虚拟表，简化复杂操作。
示例：
CREATE VIEW V_Emp_02 AS
SELECT * FROM Employee WHERE Dno = '02' WITH CHECK OPTION; — 限制更新仅限部门02
作用：
简化查询：将多表连接结果保存为视图。
数据安全：隐藏敏感列（如仅允许查询工资范围）。
逻辑独立性：基表结构变化时，仅需调整视图定义。
限制：
含聚合函数（AVG）、DISTINCT或分组的视图不可更新。
三、数据操纵语言（DML）
1. 数据插入（INSERT）
功能：向表中添加数据。
示例：
— 单行插入
INSERT INTO Employee (Eno, Ename, Age) VALUES ('1001', '张三', 25);
— 多行插入（从其他表导入）
INSERT INTO Engineer SELECT * FROM Employee WHERE Title = '工程师';
2. 数据更新（UPDATE）
功能：修改现有数据。
示例：
— 条件更新
UPDATE Employee SET Age = 30 WHERE Ename = '张三';
— 子查询更新（技术科员工工资翻倍）
UPDATE Salary SET Basepay = Basepay * 2
WHERE Eno IN (SELECT Eno FROM Employee WHERE Dno = '技术科');
3. 数据删除（DELETE）
功能：删除数据。
示例：
— 删除指定行
DELETE FROM Employee WHERE Age > 60;
— 清空表（高效但不可回滚）
TRUNCATE TABLE Employee;
4. 数据查询（SELECT）
基础查询：
SELECT Ename, Age FROM Employee WHERE Dno = '02' ORDER BY Age DESC;
条件查询：
比较：WHERE Salary > 5000
范围：WHERE Age BETWEEN 20 AND 30
模糊匹配：WHERE Ename LIKE '张%'（匹配“张三”“张伟”）
聚合与分组：
SELECT Dno, AVG(Age) AS AvgAge
FROM Employee
GROUP BY Dno
HAVING AVG(Age) > 30; — 筛选部门平均年龄>30
连接查询：
— 内连接（匹配两表关联数据）
SELECT E.Ename, D.Dname
FROM Employee E JOIN Department D ON E.Dno = D.Dno;
— 左外连接（保留左表未匹配行）
SELECT E.Ename, D.Dname
FROM Employee E LEFT JOIN Department D ON E.Dno = D.Dno;
子查询：
— 嵌套子查询（技术科员工）
SELECT * FROM Employee
WHERE Dno = (SELECT Dno FROM Department WHERE Dname = '技术科');
— EXISTS子查询（检查是否存在关联数据）
SELECT * FROM Employee E
WHERE EXISTS (SELECT 1 FROM Item_Emp WHERE Eno = E.Eno);
四、重点与难点
复杂查询：
多表连接（如Employee与Department）。
子查询嵌套（如EXISTS判断是否存在数据）。
性能优化：
合理使用索引，避免全表扫描。
避免在WHERE中对列使用函数（如YEAR(Date)导致索引失效）。
视图更新：
简单视图（行列子集）可更新，复杂视图（含分组/聚合）不可更新。
五、总结
DDL：定义结构（表、索引、视图），需注意约束和索引设计。
DML：操作数据，重点掌握复杂查询（连接、子查询、分组）。
索引：加速查询但需权衡维护成本。
视图：简化操作、增强安全性和逻辑独立性。
以下是结合知识库内容整理的SQL知识点，重点补充了自身连接、左右连接、复杂嵌套子查询的示例与讲解，以及WHERE与HAVING的区别：
六、连接查询
1. 自身连接（Self-Join）
定义：同一张表的不同实例进行连接，通过别名区分。
用途：处理表内数据的层级关系或自反关系（如员工与经理）。
示例：
— 查询每个员工的姓名及其对应的经理姓名
SELECT e.Ename AS Employee, m.Ename AS Manager
FROM Employee e
LEFT JOIN Employee m ON e.MgrEno = m.Eno; — MgrEno是经理编号
说明：
通过别名e（员工）和m（经理）区分同一表的两个实例。
使用LEFT JOIN确保无经理的员工也显示（如CEO）。
2. 左右连接（LEFT/RIGHT JOIN）
左外连接（LEFT JOIN）：
功能：返回左表所有记录，右表匹配的记录；未匹配的右表字段显示NULL。
示例：
— 查询所有部门及员工（即使部门无员工）
SELECT D.Dname, E.Ename
FROM Department D
LEFT JOIN Employee E ON D.Dno = E.Dno;
右外连接（RIGHT JOIN）：
功能：返回右表所有记录，左表匹配的记录；未匹配的左表字段显示NULL。
示例：
— 查询所有员工及其部门（即使员工未分配部门）
SELECT E.Ename, D.Dname
FROM Employee E
RIGHT JOIN Department D ON E.Dno = D.Dno;
对比：
LEFT JOIN关注左表完整性，RIGHT JOIN关注右表完整性。
通过交换表顺序，二者可互相转换。
七、复杂嵌套子查询
1. 嵌套子查询
定义：子查询独立执行，结果传递给外部查询。
示例：
— 查询部门“技术科”的员工姓名
SELECT Ename
FROM Employee
WHERE Dno = (SELECT Dno FROM Department WHERE Dname = '技术科');
2. 相关子查询
定义：子查询依赖外部查询的值，逐行执行。
示例：
— 查询比部门平均年龄大的员工
SELECT Ename, Age
FROM Employee E1
WHERE Age > (SELECT AVG(Age) FROM Employee E2 WHERE E2.Dno = E1.Dno);
3. EXISTS子查询
功能：判断子查询是否返回结果，常用于存在性检查。
示例：
— 查询参与过项目的员工
SELECT Ename
FROM Employee E
WHERE EXISTS (SELECT 1 FROM Item_Emp WHERE Eno = E.Eno);
八、WHERE与HAVING的区别
对比项
WHERE
HAVING
作用对象
行（基表或视图）
组（分组后的结果）
执行时间
分组前过滤
分组后过滤
聚合函数
不可使用（如AVG, SUM）
可使用聚合函数
示例
WHERE Age > 30
HAVING AVG(Age) > 30
典型场景：
— WHERE过滤行，HAVING过滤组
SELECT Dno, AVG(Age) AS AvgAge
FROM Employee
WHERE Age > 20 — 过滤年龄>20的员工
GROUP BY Dno
HAVING AVG(Age) > 30; — 过滤平均年龄>30的部门
九、重点总结
自身连接：通过别名处理表内层级关系（如员工与经理）。
左右连接：
LEFT JOIN保留左表所有行，RIGHT JOIN保留右表所有行。
未匹配的字段显示为NULL。
复杂子查询：
嵌套子查询独立执行，相关子查询逐行依赖外部查询。
EXISTS用于存在性判断。
WHERE vs HAVING：
WHERE在分组前过滤行，不可用聚合函数。
HAVING在分组后过滤组，必须与GROUP BY搭配使用。