MySQL 表约束设计与查询进阶

对于整数类型的主键，可以使用 AUTO_INCREMENT 自增（从 1 开始往上增加）。

CREATE TABLE 表名 (
    列名 1 类型 PRIMARY KEY AUTO_INCREMENT,
    列名 2 类型
);

5. FOREIGN KEY 外键约束

外键约束描述两表之间的关联关系。子表通过父表约束。例如学生选课，课程必须是学校存在的。

前提： 两个表都必须存在主键，通过主键建立联系。指定外键时，父表中被关联的列必须是主键或唯一约束。

CREATE TABLE 子表名 (
    列名 1 类型 (主键),
    列名 2 类型,
    FOREIGN KEY (列名 3) REFERENCES 父表名 (父表主键列名)
);

注意： 使用外键约束后，不能直接修改父表的值；若约束存在，不可先删除父表。

6. CHECK 约束

CHECK 约束限制列的值只能在特定范围内。

CREATE TABLE 表名 (
    列名 1 类型 CHECK (列名 1 = '值 1' OR 列名 1 = '值 2'),
    列名 2 类型
);

表的设计

1. 一对一

例如一个学生对应一个账号，一个账号对应一个学生。

student(student_Id VARCHAR(20), name VARCHAR(20), account_Id VARCHAR(20));
account(account_Id VARCHAR(20), username VARCHAR(20), password VARCHAR(20));

2. 一对多

例如一个班级包含多个学生，但一个学生只能在一个班级中。

class(id INT, name VARCHAR(20));
student(id INT, name VARCHAR(20), class_Id INT);

3. 多对多

例如一个学生选多门课，一门课被多个学生选。需通过中间表实现。

student(id INT, name VARCHAR(20));
course(id INT, course_name VARCHAR(20));

4. 无关联

以上三种均为相关联表，无关联即无外键约束。

查询

1. 聚合查询

聚合查询针对行与行之间进行运算。

1.1 聚合函数

• COUNT()：计数
  • COUNT(*)：统计总行数（包含空值）。
  • COUNT(列名)：统计该列非空行数。
  • COUNT(DISTINCT 列名)：去重且去空值计数。

  注意：COUNT 和括号之间有空格时会报错。

• SUM()：求和
  • SUM(列名)：数值列求和。
  • 字符串类型需转换后再计算。
• AVG()：平均值
• MAX() / MIN()：最大值 / 最小值

1.2 GROUP BY 分组

将多组数据按条件分成几组，常用于配合聚合函数计算每组的统计值。

语法顺序： WHERE 在 GROUP BY 之前。

1.3 HAVING

HAVING 用于筛选分组后的结果，通常配合 GROUP BY 使用，且位于末尾。

示例：平均工资低于 8000 的部门。

2. 联合查询/多表查询

2.1 内连接 (INNER JOIN)

多表联合查询涉及笛卡尔积，需通过条件筛选有效数据。

-- 基础写法
SELECT student.name, course.course 
FROM student, course 
WHERE student.id = course.student_id;

-- 推荐写法
SELECT student.name AS studentName, course.course AS courseCourse 
FROM student INNER JOIN course ON student.id = course.student_id;

2.2 外连接

• 左外连接 (LEFT JOIN)：保留左表所有记录。
• 右外连接 (RIGHT JOIN)：保留右表所有记录。
• 全外连接 (FULL JOIN)：MySQL 不支持，了解即可。

2.3 自连接

自己和自己做笛卡尔积，需注意别名区分。

SELECT * FROM 表 1 AS 新名 1 JOIN 表 1 AS 新名 2 ON 新名 1.id = 新名 2.id;

2.4 子查询

嵌入其他 SQL 语句中的 SELECT 语句。

• 单行子查询：返回单个值。

SELECT name, class_id FROM student 
WHERE class_id = (SELECT class_id FROM student WHERE name = '不想毕业') 
AND name != '不想毕业';

• 多行子查询：返回多个值，使用 IN 或 EXISTS。

SELECT score.student_id, score.score 
FROM score 
WHERE score.course_id IN (SELECT id FROM course WHERE name = '语文' OR name = '英文');

2.5 合并查询 (UNION)

合并多个 SQL 查询结果集，列数和类型需一致。默认去重，可用 UNION ALL 保留重复。

SELECT 字段名 FROM 表 1 UNION SELECT 字段名 FROM 表 2;

核心要点

• 约束：NULL, UNIQUE, DEFAULT, PRIMARY KEY, FOREIGN KEY, CHECK
• 表设计：一对一，一对多，多对多
• 查询：聚合函数，GROUP BY, HAVING, 连接查询，子查询，UNION

SQL 关键字执行顺序：FROM > ON > JOIN > WHERE > GROUP BY > WITH > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT

小知识：MySQL 注释可使用 # 或 -- （双横线加空格）。