第二章：SQL 进阶操纵与多表查询

1. 数据操纵语言（DML）​​

​​1.1 插入数据（INSERT）​​

向表中插入新记载：

1. -- 插入单条数据（按列顺序）
2. INSERT INTO students (name, age, score)
3. VALUES ('张三', 20, 92);
5. -- 插入多条数据
6. INSERT INTO students (name, age, score)
7. VALUES ('李四', 22, 85), ('王五', 19, 95);

复制代码

​​1.2 更新数据（UPDATE）​​

修改表中现有记载：

1. -- 将 id=1 的学生的分数改为 90
2. UPDATE students
3. SET score = 90
4. WHERE id = 1;
6. -- 批量更新年龄≥20的学生的分数（增加5分）
7. UPDATE students
8. SET score = score + 5
9. WHERE age >= 20;

复制代码

​​1.3 删除数据（DELETE）​​

删除表中的记载：

1. -- 删除 id=3 的学生
2. DELETE FROM students
3. WHERE id = 3;
5. -- 删除分数低于60的学生
6. DELETE FROM students
7. WHERE score < 60;

复制代码

---

​​2. 数据定义语言（DDL）​​

​​2.1 创建表（CREATE TABLE）​​

定义表结构和束缚（如主键、非空）：

1. CREATE TABLE students (
2.     id INT PRIMARY KEY AUTO_INCREMENT,   -- 主键，自增
3.     name VARCHAR(50) NOT NULL,           -- 非空约束
4.     age INT CHECK (age >= 0),            -- 年龄必须≥0
5.     score DECIMAL(5,2),                   -- 小数类型（总位数5，小数位2）
6.     enroll_date DATE DEFAULT CURRENT_DATE -- 默认值为当前日期
7. );

复制代码

​​2.2 修改表（ALTER TABLE）​​

调整表结构：

1. -- 添加新列
2. ALTER TABLE students
3. ADD COLUMN gender CHAR(1) COMMENT '性别';
5. -- 修改列类型
6. ALTER TABLE students
7. MODIFY COLUMN name VARCHAR(100);
9. -- 删除列
10. ALTER TABLE students
11. DROP COLUMN enroll_date;

复制代码

​​2.3 删除表（DROP TABLE）​​

彻底删除表：

1. DROP TABLE students;  -- 谨慎使用！

复制代码

---

​​3. 多表查询与连接（JOIN）​​

​​3.1 表关系计划​​

• ​​外键（Foreign Key）​​：关联其他表的主键。
  
• 示例：students 表与 courses 表通过 student_id 关联。
  

​​3.2 内连接（INNER JOIN）​​

返回两个表中匹配的记载：

1. -- 查询学生及其选修的课程
2. SELECT s.name, c.course_name
3. FROM students s
4. INNER JOIN courses c ON s.id = c.student_id;

复制代码

​​3.3 左连接（LEFT JOIN）​​

返回左表全部记载及右表匹配的记载（不匹配则为 NULL）：

1. -- 查询所有学生（即使未选课）
2. SELECT s.name, c.course_name
3. FROM students s
4. LEFT JOIN courses c ON s.id = c.student_id;

复制代码

​​3.4 右连接（RIGHT JOIN）​​

返回右表全部记载及左表匹配的记载（不匹配则为 NULL）：

1. -- 查询所有课程（即使无学生选）
2. SELECT s.name, c.course_name
3. FROM students s
4. RIGHT JOIN courses c ON s.id = c.student_id;

复制代码

​​3.5 子查询（Subquery）​​

嵌套查询作为条件或暂时表：

1. -- 查询分数高于平均分的学生
2. SELECT name, score
3. FROM students
4. WHERE score > (SELECT AVG(score) FROM students);

复制代码

---

​​4. 聚合函数与分组统计​​

​​4.1 常用聚合函数​​

• COUNT()：统计行数
  
• SUM()：求和
  
• AVG()：平均值
  
• MAX()：最大值
  
• MIN()：最小值
  

​​4.2 分组统计（GROUP BY）​​

按指定列分组后统计：

1. -- 统计每个年龄段的学生人数和平均分
2. SELECT age, COUNT(*) AS 人数, AVG(score) AS 平均分
3. FROM students
4. GROUP BY age;

复制代码

​​4.3 分组后过滤（HAVING）​​

对分组结果进行筛选（类似 WHERE，但用于聚合后）：

1. -- 筛选平均分≥80的年龄段
2. SELECT age, AVG(score) AS 平均分
3. FROM students
4. GROUP BY age
5. HAVING 平均分 >= 80;

复制代码

---

​​5. 束缚与索引​​

​​5.1 常见束缚​​

• PRIMARY KEY：主键（唯一且非空）
  
• FOREIGN KEY：外键
  
• UNIQUE：唯一值
  
• NOT NULL：非空
  
• CHECK：自定义条件束缚
  

​​5.2 索引（INDEX）​​

进步查询效率（但会降低插入/更新速度）：

1. -- 为 name 列创建索引
2. CREATE INDEX idx_name ON students (name);
4. -- 删除索引
5. DROP INDEX idx_name ON students;

复制代码

---

​​6. 事务处理惩罚（ACID 特性）​​

确保数据操纵的原子性、一致性、隔离性、持久性：

1. -- 开始事务
2. START TRANSACTION;
4. -- 执行操作（例如转账）
5. UPDATE accounts SET balance = balance - 100 WHERE id = 1;
6. UPDATE accounts SET balance = balance + 100 WHERE id = 2;
8. -- 提交事务（确认操作）
9. COMMIT;
11. -- 或回滚事务（撤销操作）
12. ROLLBACK;

复制代码

---

​​7. 综合示例​​

1. -- 创建订单表和订单详情表
2. CREATE TABLE orders (
3.     order_id INT PRIMARY KEY,
4.     customer_id INT,
5.     order_date DATE,
6.     FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
7. );
9. CREATE TABLE order_details (
10.     detail_id INT PRIMARY KEY,
11.     order_id INT,
12.     product_name VARCHAR(50),
13.     quantity INT,
14.     FOREIGN KEY (order_id) REFERENCES orders(order_id)
15. );
17. -- 查询每个客户的订单总金额（假设单价为固定值）
18. SELECT o.customer_id, SUM(d.quantity * 100) AS 总金额  -- 假设单价100元
19. FROM orders o
20. INNER JOIN order_details d ON o.order_id = d.order_id
21. GROUP BY o.customer_id
22. HAVING 总金额 > 500;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。