MySQL 数据库

-- 创建表时定义主键 CREATE TABLE users ( user_id INT PRIMARY KEY, username VARCHAR(50) ); -- 复合主键 CREATE TABLE course_registration ( student_id INT, course_id INT, reg_date DATE, PRIMARY KEY (student_id, course_id) ); -- 添加主键约束 ALTER TABLE students ADD PRIMARY KEY (id); -- 删除主键约束 ALTER TABLE students DROP PRIMARY KEY;

-- 创建部门表 CREATE TABLE departments ( dept_id INT PRIMARY KEY, dept_name VARCHAR(100) ); -- 创建员工表，添加外键 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 -- 级联更新 ); -- 外键操作选项 -- ON DELETE CASCADE 删除主表记录时，子表相关记录也删除 -- ON DELETE SET NULL 删除主表记录时，子表外键设为NULL -- ON DELETE RESTRICT 如果子表有相关记录，阻止删除主表记录 -- ON DELETE NO ACTION 与RESTRICT类似

-- NOT NULL约束 CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL ); -- UNIQUE约束 CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(100) UNIQUE, username VARCHAR(50) UNIQUE ); -- 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), gender VARCHAR(10) CHECK (gender IN ('男', '女', '其他')) ); -- DEFAULT约束 CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE DEFAULT (CURRENT_DATE), status VARCHAR(20) DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

-- 查看表约束 SHOW CREATE TABLE students; -- 添加约束到现有表 ALTER TABLE students ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 120); -- 添加唯一约束 ALTER TABLE students ADD CONSTRAINT uk_email UNIQUE (email); -- 添加外键约束 ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id); -- 删除约束 ALTER TABLE students DROP CONSTRAINT chk_age; ALTER TABLE students DROP INDEX uk_email;