MySQL表的约束与基本查询详解
在数据库开发中，保证数据的准确性和一致性是至关重要的。MySQL通过表的约束来规范写入数据的规则，同时通过强大的查询功能让我们灵活地获取数据。本文将详细梳理MySQL中常见的约束类型及其作用，并深入探讨基本的查询语法，帮助开发者夯实MySQL基础。
一、MySQL表的约束详解
约束（Constraint）是作用于表中字段上的规则，用于限制存储在表中的数据。合理的约束可以有效防止无效或错误数据的录入。MySQL中常用的约束有以下几种：
1. 主键约束 (PRIMARY KEY)
主键约束用于唯一标识表中的每一条记录。主键列的值必须唯一且不能为空。一个表只能有一个主键，主键可以是单字段，也可以是多个字段的组合（复合主键）。
-- 创建表时添加主键 CREATE TABLE students ( id INT AUTO_INCREMENT, student_name VARCHAR(50), age INT, PRIMARY KEY (id) );
2. 非空约束 (NOT NULL)
非空约束确保某列不能存储NULL值。如果试图插入NULL，数据库将报错。
CREATE TABLE users ( user_id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(30) NOT NULL -- 用户名不能为空 );
3. 唯一约束 (UNIQUE)
唯一约束保证某列或多列组合的值必须唯一，允许有NULL值，但只能有一个NULL。与主键不同，一个表可以有多个唯一约束。
CREATE TABLE employees ( emp_id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(100) UNIQUE -- 邮箱必须唯一 );
4. 默认值约束 (DEFAULT)
默认值约束用于在插入数据时，如果没有为该字段指定值，则自动填充预设的默认值。
CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, status VARCHAR(20) DEFAULT 'PENDING' -- 默认状态为PENDING );
5. 外键约束 (FOREIGN KEY)
外键约束用于建立两个表之间的关联，保证数据的一致性和完整性。外键列的值必须在主表的参照列中存在，或者为NULL。
CREATE TABLE departments ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(50) NOT NULL ); CREATE TABLE staff ( staff_id INT PRIMARY KEY AUTO_INCREMENT, staff_name VARCHAR(50) NOT NULL, dept_id INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id) -- 外键关联 );
6. 检查约束 (CHECK)
检查约束用于限制列中的值必须满足指定的条件。（注意：MySQL 8.0.16之前的版本对CHECK约束只是语法支持但不生效，8.0.16之后开始强制执行）。
CREATE TABLE products ( product_id INT PRIMARY KEY AUTO_INCREMENT, price DECIMAL(10, 2), CHECK (price > 0) -- 价格必须大于0 );
二、MySQL基本查询详解
数据查询是数据库操作中最核心的功能。DQL（Data Query Language）主要通过SELECT语句实现。下面我们由浅入深地拆解基本查询语法。
为了方便演示，我们假设有一张employees表，包含id, name, department, salary, hire_date等字段。
1. 基础查询
查询所有字段或指定字段的数据。
-- 查询所有员工的所有信息 SELECT * FROM employees; -- 查询指定的列（推荐，性能更好） SELECT name, salary FROM employees;
2. 条件查询 (WHERE)
使用WHERE子句过滤满足指定条件的记录。支持=, !=, >, <, AND, OR等操作符。
-- 查询薪资大于8000且部门为'研发部'的员工 SELECT name, salary FROM employees WHERE salary > 8000 AND department = '研发部';
3. 去重查询 (DISTINCT)
去除查询结果中的重复行。
-- 查询公司中现有的所有部门名称（去除重复） SELECT DISTINCT department FROM employees;
4. 排序查询 (ORDER BY)
对查询结果进行排序，ASC表示升序（默认），DESC表示降序。
-- 查询所有员工信息，按薪资降序排列，薪资相同则按入职日期升序排列 SELECT * FROM employees ORDER BY salary DESC, hire_date ASC;
5. 分页查询 (LIMIT)
在数据量较大时，用于截取指定位置的记录，常用于前端分页展示。
-- 每页显示10条记录，查询第2页的数据（偏移量为10） SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 10; -- 或者简写为: LIMIT 10, 10;
6. 聚合函数与分组查询 (GROUP BY & HAVING)
聚合函数用于对一组值进行计算，返回单个值，如COUNT(), SUM(), AVG(), MAX(), MIN()。GROUP BY用于分组，HAVING用于过滤分组后的结果。
-- 查询各部门的平均薪资和人数，仅显示平均薪资大于10000的部门 SELECT department, AVG(salary) AS avg_salary, COUNT(id) AS emp_count FROM employees GROUP BY department HAVING avg_salary > 10000;
三、综合实战演练
结合上述的约束与查询知识，我们来完成一个稍微复杂的实战场景。假设我们有一个在线商城的数据库，包含用户表和订单表。我们需要在建表时设置合理的约束，并执行多表关联查询。
-- 1. 创建用户表，添加主键、唯一、非空约束 CREATE TABLE customers ( cust_id INT AUTO_INCREMENT PRIMARY KEY, cust_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, register_date DATE DEFAULT (CURRENT_DATE) ); -- 2. 创建订单表，添加外键和检查约束 CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, cust_id INT, amount DECIMAL(10, 2) NOT NULL, order_date DATETIME DEFAULT CURRENT_TIMESTAMP, CHECK (amount > 0), FOREIGN KEY (cust_id) REFERENCES customers(cust_id) ); -- 3. 查询消费总金额超过5000的用户姓名及其总消费额（多表连接 + 分组 + 排序） SELECT c.cust_name, SUM(o.amount) AS total_amount FROM customers c JOIN orders o ON c.cust_id = o.cust_id GROUP BY c.cust_id HAVING total_amount > 5000 ORDER BY total_amount DESC;
在真实的业务开发中，您可以借助类似 www.ipipp.com 这样的在线数据库管理工具来快速验证您的建表约束和查询语句，从而提升开发效率。
四、总结
MySQL的约束机制是数据库数据的“守门员”，它从源头上杜绝了脏数据的产生；而灵活多变的查询语句则是挖掘数据价值的“利器”。在实际开发中，建表时一定要深思熟虑地添加约束，而在查询时则需注意SQL的执行效率（如避免SELECT *、合理使用索引与分页）。只有将约束和查询熟练掌握，才能设计出健壮且高效的数据库应用。