第二章：SQL 进阶操纵与多表查询
1. 数据操纵语言（DML）1.1 插入数据（INSERT）
向表中插入新记载：
-- 插入单条数据（按列顺序）
INSERT INTO students (name, age, score)
VALUES ('张三', 20, 92);
-- 插入多条数据
INSERT INTO students (name, age, score)
VALUES ('李四', 22, 85), ('王五', 19, 95); 1.2 更新数据（UPDATE）
修改表中现有记载：
-- 将 id=1 的学生的分数改为 90
UPDATE students
SET score = 90
WHERE id = 1;
-- 批量更新年龄≥20的学生的分数（增加5分）
UPDATE students
SET score = score + 5
WHERE age >= 20; 1.3 删除数据（DELETE）
删除表中的记载：
-- 删除 id=3 的学生
DELETE FROM students
WHERE id = 3;
-- 删除分数低于60的学生
DELETE FROM students
WHERE score < 60; 2. 数据定义语言（DDL）
2.1 创建表（CREATE TABLE）
定义表结构和束缚（如主键、非空）：
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键，自增
name VARCHAR(50) NOT NULL, -- 非空约束
age INT CHECK (age >= 0), -- 年龄必须≥0
score DECIMAL(5,2), -- 小数类型（总位数5，小数位2）
enroll_date DATE DEFAULT CURRENT_DATE -- 默认值为当前日期
); 2.2 修改表（ALTER TABLE）
调整表结构：
-- 添加新列
ALTER TABLE students
ADD COLUMN gender CHAR(1) COMMENT '性别';
-- 修改列类型
ALTER TABLE students
MODIFY COLUMN name VARCHAR(100);
-- 删除列
ALTER TABLE students
DROP COLUMN enroll_date; 2.3 删除表（DROP TABLE）
彻底删除表：
DROP TABLE students;-- 谨慎使用！ 3. 多表查询与连接（JOIN）
3.1 表关系计划
[*]外键（Foreign Key）：关联其他表的主键。
[*]示例：students 表与 courses 表通过 student_id 关联。
3.2 内连接（INNER JOIN）
返回两个表中匹配的记载：
-- 查询学生及其选修的课程
SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.id = c.student_id; 3.3 左连接（LEFT JOIN）
返回左表全部记载及右表匹配的记载（不匹配则为 NULL）：
-- 查询所有学生（即使未选课）
SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.id = c.student_id; 3.4 右连接（RIGHT JOIN）
返回右表全部记载及左表匹配的记载（不匹配则为 NULL）：
-- 查询所有课程（即使无学生选）
SELECT s.name, c.course_name
FROM students s
RIGHT JOIN courses c ON s.id = c.student_id; 3.5 子查询（Subquery）
嵌套查询作为条件或暂时表：
-- 查询分数高于平均分的学生
SELECT name, score
FROM students
WHERE score > (SELECT AVG(score) FROM students); 4. 聚合函数与分组统计
4.1 常用聚合函数
[*]COUNT()：统计行数
[*]SUM()：求和
[*]AVG()：平均值
[*]MAX()：最大值
[*]MIN()：最小值
4.2 分组统计（GROUP BY）
按指定列分组后统计：
-- 统计每个年龄段的学生人数和平均分
SELECT age, COUNT(*) AS 人数, AVG(score) AS 平均分
FROM students
GROUP BY age; 4.3 分组后过滤（HAVING）
对分组结果进行筛选（类似 WHERE，但用于聚合后）：
-- 筛选平均分≥80的年龄段
SELECT age, AVG(score) AS 平均分
FROM students
GROUP BY age
HAVING 平均分 >= 80; 5. 束缚与索引
5.1 常见束缚
[*]PRIMARY KEY：主键（唯一且非空）
[*]FOREIGN KEY：外键
[*]UNIQUE：唯一值
[*]NOT NULL：非空
[*]CHECK：自定义条件束缚
5.2 索引（INDEX）
进步查询效率（但会降低插入/更新速度）：
-- 为 name 列创建索引
CREATE INDEX idx_name ON students (name);
-- 删除索引
DROP INDEX idx_name ON students; 6. 事务处理惩罚（ACID 特性）
确保数据操纵的原子性、一致性、隔离性、持久性：
-- 开始事务
START TRANSACTION;
-- 执行操作（例如转账）
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 提交事务（确认操作）
COMMIT;
-- 或回滚事务（撤销操作）
ROLLBACK; 7. 综合示例
-- 创建订单表和订单详情表
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_details (
detail_id INT PRIMARY KEY,
order_id INT,
product_name VARCHAR(50),
quantity INT,
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
-- 查询每个客户的订单总金额（假设单价为固定值）
SELECT o.customer_id, SUM(d.quantity * 100) AS 总金额-- 假设单价100元
FROM orders o
INNER JOIN order_details d ON o.order_id = d.order_id
GROUP BY o.customer_id
HAVING 总金额 > 500;
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]