MySQL表的约束与基本查询图文详解
一、表的约束详解
1. 约束的作用
约束用于确保数据库中数据的完整性、一致性和准确性，防止无效数据进入数据库。
2. 常用约束类型
(1) PRIMARY KEY（主键约束）
-- 创建表时指定主键
CREATE TABLE students (
id INT PRIMARY KEY, -- 方式1：列级约束
name VARCHAR(50)
);
-- 或
CREATE TABLE students (
id INT,
name VARCHAR(50),
PRIMARY KEY (id) -- 方式2：表级约束
);
-- 复合主键
CREATE TABLE enrollments (
student_id INT,
course_id INT,
enroll_date DATE,
PRIMARY KEY (student_id, course_id)
);
特点：
- 唯一标识表中的每一行
- 不能为NULL
- 一个表只能有一个主键
(2) FOREIGN KEY（外键约束）
-- 创建部门表
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
-- 创建员工表，包含外键
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
ON DELETE CASCADE -- 级联删除
ON UPDATE CASCADE -- 级联更新
);
外键约束图示：
┌─────────────────┐ ┌─────────────────┐
│ departments │ │ employees │
├─────────────────┤ ├─────────────────┤
│ dept_id (PK) │◄──────│ dept_id (FK) │
│ dept_name │ │ emp_id (PK) │
│ │ │ emp_name │
└─────────────────┘ └─────────────────┘
主表 从表
外键操作选项：
ON DELETE CASCADE - 主表删除时，从表相关记录也删除ON DELETE SET NULL - 主表删除时，从表外键设为NULLON DELETE RESTRICT - 有外键引用时禁止删除ON UPDATE CASCADE - 主表更新时，从表同步更新
(3) UNIQUE（唯一约束）
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE, -- 列级约束
username VARCHAR(50)
);
-- 或表级约束
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100),
username VARCHAR(50),
UNIQUE (email) -- 表级约束
);
特点：
- 确保列值唯一
- 允许NULL值（但只能有一个NULL）
(4) NOT NULL（非空约束）
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
(5) CHECK（检查约束） - MySQL 8.0+支持
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT CHECK (age >= 18 AND age <= 65),
salary DECIMAL(10,2) CHECK (salary > 0)
);
(6) DEFAULT（默认值约束）
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20) DEFAULT 'pending'
);
(7) AUTO_INCREMENT（自增约束）
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50)
);
-- 设置自增起始值
ALTER TABLE users AUTO_INCREMENT = 1000;
3. 约束管理
-- 添加约束
ALTER TABLE students ADD PRIMARY KEY (id);
ALTER TABLE employees ADD FOREIGN KEY (dept_id) REFERENCES departments(dept_id);
ALTER TABLE users ADD CONSTRAINT uc_email UNIQUE (email);
ALTER TABLE products MODIFY COLUMN name VARCHAR(100) NOT NULL;
-- 删除约束
ALTER TABLE employees DROP FOREIGN KEY fk_dept; -- 需要知道约束名
ALTER TABLE users DROP INDEX uc_email; -- 删除唯一约束
ALTER TABLE students DROP PRIMARY KEY;
二、基本查询图文详解
1. SELECT基础查询结构
┌─────────────────────────────────────────────────────┐
│ SELECT 语句结构 │
├─────────────────────────────────────────────────────┤
│ SELECT [DISTINCT] 列1, 列2, ... │
│ FROM 表名 │
│ [WHERE 条件] │
│ [GROUP BY 分组列] │
│ [HAVING 分组条件] │
│ [ORDER BY 排序列 [ASC|DESC]] │
│ [LIMIT 数量]; │
└─────────────────────────────────────────────────────┘
2. 基本查询示例
示例数据表结构：
-- 创建示例表
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT,
salary DECIMAL(10,2),
hire_date DATE,
job_title VARCHAR(50)
);
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
-- 插入示例数据
INSERT INTO departments VALUES
(1, '技术部'), (2, '销售部'), (3, '人事部');
INSERT INTO employees VALUES
(1, '张三', 1, 8000, '2020-01-15', '工程师'),
(2, '李四', 1, 9000, '2019-05-20', '高级工程师'),
(3, '王五', 2, 7000, '2021-03-10', '销售代表'),
(4, '赵六', 2, 8500, '2018-11-30', '销售经理'),
(5, '钱七', 3, 6000, '2022-02-28', 'HR专员');
3. 查询类型详解
(1) 简单查询
-- 查询所有列
SELECT * FROM employees;
-- 查询特定列
SELECT emp_id, emp_name, salary FROM employees;
-- 使用别名
SELECT emp_id AS 员工编号,
emp_name AS 姓名,
salary * 12 AS 年薪
FROM employees;
-- 去重查询
SELECT DISTINCT dept_id FROM employees;
(2) WHERE条件查询
-- 比较运算符
SELECT * FROM employees WHERE salary > 8000;
SELECT * FROM employees WHERE hire_date >= '2020-01-01';
-- 逻辑运算符
SELECT * FROM employees
WHERE salary > 7000 AND dept_id = 1;
SELECT * FROM employees
WHERE dept_id = 1 OR dept_id = 2;
-- BETWEEN范围查询
SELECT * FROM employees
WHERE salary BETWEEN 7000 AND 9000;
-- IN查询
SELECT * FROM employees
WHERE dept_id IN (1, 2);
-- LIKE模糊查询
SELECT * FROM employees
WHERE emp_name LIKE '张%'; -- 张开头
SELECT * FROM employees
WHERE emp_name LIKE '%三'; -- 三结尾
SELECT * FROM employees
WHERE emp_name LIKE '%四%'; -- 包含四
-- IS NULL判断
SELECT * FROM employees WHERE job_title IS NULL;
(3) ORDER BY排序
-- 单列排序
SELECT * FROM employees ORDER BY salary DESC;
-- 多列排序
SELECT * FROM employees
ORDER BY dept_id ASC, salary DESC;
-- 按表达式排序
SELECT *, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;
(4) LIMIT限制
-- 限制行数
SELECT * FROM employees LIMIT 3;
-- 分页查询
SELECT * FROM employees LIMIT 2, 3; -- 跳过2条，取3条
-- 与ORDER BY结合
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 3; -- 查询工资最高的3人
4. 聚合函数查询
-- 常用聚合函数
SELECT
COUNT(*) AS 员工总数,
AVG(salary) AS 平均工资,
MAX(salary) AS 最高工资,
MIN(salary) AS 最低工资,
SUM(salary) AS 工资总额
FROM employees;
-- COUNT的不同用法
SELECT COUNT(*) FROM employees; -- 所有行数
SELECT COUNT(emp_id) FROM employees; -- 非NULL值计数
SELECT COUNT(DISTINCT dept_id) FROM employees; -- 去重计数
5. GROUP BY分组查询
-- 按部门分组统计
SELECT
dept_id,
COUNT(*) AS 人数,
AVG(salary) AS 平均工资,
MAX(salary) AS 最高工资
FROM employees
GROUP BY dept_id;
-- GROUP BY执行流程图示：
┌─────────────────────────────────────────┐
│ GROUP BY 流程 │
├─────────────────────────────────────────┤
│ 1. FROM employees │
│ 2. WHERE 筛选数据 │
│ 3. GROUP BY dept_id 分组 │
│ 4. 对每组应用聚合函数 │
│ 5. SELECT 选择显示的列 │
│ 6. ORDER BY 排序 │
└─────────────────────────────────────────┘
6. HAVING分组后筛选
-- HAVING vs WHERE
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 7500; -- 分组后筛选
-- WHERE和HAVING一起使用
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01' -- 分组前筛选
GROUP BY dept_id
HAVING AVG(salary) > 7000; -- 分组后筛选
WHERE vs HAVING区别：
┌───────────────────┬───────────────────┐
│ WHERE │ HAVING │
├───────────────────┼───────────────────┤
│ 分组前筛选 │ 分组后筛选 │
│ 作用于原始记录 │ 作用于分组结果 │
│ 不能使用聚合函数 │ 可以使用聚合函数 │
│ 先执行 │ 后执行 │
└───────────────────┴───────────────────┘
7. 多表查询（JOIN）
(1) INNER JOIN（内连接）
-- 显式内连接
SELECT e.emp_name, d.dept_name, e.salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- 隐式内连接
SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;
(2) LEFT JOIN（左连接）
-- 左连接：包含左表所有记录，右表匹配不到则为NULL
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
JOIN类型图示：
内连接 (INNER JOIN) 左连接 (LEFT JOIN)
┌─────────┐ ┌─────────┐
│ A │ │ A │
│ A∩B │ │ A∪(B∩A)│
│ B │ │ B │
└─────────┘ └─────────┘
(3) 其他JOIN类型
-- RIGHT JOIN（右连接）
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
-- FULL OUTER JOIN（MySQL不支持，可用UNION模拟）
SELECT e.emp_name, d.dept_name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.emp_name, d.dept_name
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id;
8. 子查询
-- 标量子查询（返回单个值）
SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 列子查询（返回一列）
SELECT emp_name, dept_id
FROM employees
WHERE dept_id IN (
SELECT dept_id FROM departments
WHERE dept_name LIKE '%技术%'
);
-- 行子查询
SELECT * FROM employees
WHERE (dept_id, salary) = (
SELECT dept_id, MAX(salary)
FROM employees
GROUP BY dept_id
LIMIT 1
);
-- 表子查询
SELECT dept_id, avg_salary
FROM (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
) AS dept_stats
WHERE avg_salary > 7000;
9. UNION联合查询
-- UNION合并结果集（去重）
SELECT emp_name, salary FROM employees WHERE dept_id = 1
UNION
SELECT emp_name, salary FROM employees WHERE salary > 8000;
-- UNION ALL合并（不去重）
SELECT emp_name FROM employees WHERE dept_id = 1
UNION ALL
SELECT emp_name FROM employees WHERE dept_id = 2;
三、查询优化建议
索引优化
- 为WHERE、JOIN、ORDER BY涉及的列创建索引
- 避免在索引列上使用函数或计算
查询优化
-- 避免使用SELECT *
SELECT id, name FROM users; -- 好
SELECT * FROM users; -- 差（除非确实需要所有列）
-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM employees WHERE dept_id = 1;
-- 避免在WHERE中使用函数
WHERE YEAR(hire_date) = 2023; -- 差
WHERE hire_date >= '2023-01-01'; -- 好
分页优化
-- 传统分页（大数据量性能差）
SELECT * FROM big_table LIMIT 10000, 20;
-- 优化分页（使用索引列）
SELECT * FROM big_table
WHERE id > 10000
ORDER BY id
LIMIT 20;
四、总结
约束是数据库完整性的保证，合理使用约束可以避免脏数据
SELECT查询是SQL核心，掌握各种查询技巧是数据库操作的基础
理解查询执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
多表连接时要明确连接类型和条件
合理使用子查询和聚合函数可以实现复杂的数据分析需求
始终考虑查询性能，特别是处理大数据量时
