MySQL笔记： 关于约束条件

-- 数据表字段的添加约束条件

-- 1. 主键约束 PRIMARY KEY
-- 唯一标识数据表中记录的字段
-- [例]
-- 创建一个学生表students(学号 主键,姓名,年龄);
DROP TABLE students;
-- CREATE TABLE students(stuId INT,stuName VARCHAR(20),stuAge INT);
CREATE TABLE students(stuId INT PRIMARY KEY,stuName VARCHAR(20),stuAge INT);
DESC students;

INSERT INTO students VALUES(1,'张三',18);
INSERT INTO students VALUES(1,'李四',20);-- 提示错误
SELECT * FROM students;

-- 2. 默认值约束 DEFAULT 
-- 插入数据没有提供该列字段数据时使用默认值填充
-- [例]
-- 创建一个学生表(学号,姓名,年龄 默认18岁);
DROP TABLE students;
CREATE TABLE students(stuId INT,stuName VARCHAR(20),stuAge INT DEFAULT 18);

INSERT INTO students VALUES(1,'张三',17);
INSERT INTO students VALUES(2,'李四',NULL);
INSERT INTO students(stuId,stuName) VALUES(3,'王五'); -- 没有提供数据，所以会是默认值
SELECT * FROM students;

-- 3. 非空约束
-- 限制插入数据时必须为该字段提供非空数据
-- [例]
-- 创建一个学生表(学号,姓名 非空,年龄);
DROP TABLE students;
CREATE TABLE students(stuId INT,stuName VARCHAR(20) NOT NULL,stuAge INT DEFAULT 18);

INSERT INTO students VALUES(1,NULL,17);

-- 4. 唯一约束
-- 所有行的数据该列不能有同值
-- [例]
-- 创建部门表(部门id，部门名称 唯一)

DROP TABLE dept;
CREATE TABLE dept(id INT,deptName VARCHAR(20) UNIQUE);

INSERT INTO dept VALUES (1,'工程部');
INSERT INTO dept VALUES (2,'销售部');
INSERT INTO dept VALUES (3,'工程部'); -- 插入不进去了
SELECT * FROM dept;

-- 5. 自增长约束 auto_increment
-- 该字段不需要赋值，数值会自动增长
DROP TABLE students;
CREATE TABLE students(sid INT PRIMARY KEY AUTO_INCREMENT,stuName VARCHAR(20),stuAge INT);
INSERT INTO students(stuName,stuAge) VALUES('张三',17);
INSERT INTO students(stuName,stuAge) VALUES('李四',NULL);
INSERT INTO students(stuName,stuAge) VALUES('王五',17);
INSERT INTO students(stuName,stuAge) VALUES('赵六',19);
SELECT * FROM students;

DELETE FROM students WHERE sid = 3;

DELETE FROM students; -- 只会删除数据，不会清楚自动增长的序号
TRUNCATE TABLE students; -- 清空数据，并充值自动增长的序号

DESC  students;

-- 6. 外键约束
-- 约束两张表的数据

-- dept 主表
DROP TABLE dept;
CREATE TABLE dept(id INT PRIMARY KEY,departName VARCHAR(20));
INSERT INTO dept VALUES(1,'工程部');
INSERT INTO dept VALUES(2,'销售部');
INSERT INTO dept VALUES(3,'客服部');

-- employee 是副表 employee的deptId依赖dept表中的id
DROP TABLE employee;
CREATE TABLE employee(	
	id INT,
	empName VARCHAR(20),
	deptId INT,
	-- 添加一个外键约束
	CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
	--         约束名字		       本表字段		   外表字段
	ON UPDATE CASCADE -- 级联修改
	ON DELETE CASCADE -- 级联删除
);

DELETE FROM employee;
-- 插入数据
INSERT INTO employee VALUES(1,'张三',1);
INSERT INTO employee VALUES(2,'李四',1);
INSERT INTO employee VALUES(3,'王五',1);
INSERT INTO employee VALUES(4,'乔布斯',3);
INSERT INTO employee VALUES(5,'比尔盖茨',2);
INSERT INTO employee VALUES(6,'克林顿',2);
INSERT INTO employee VALUES(7,'小布什',4); 
-- 插入不进去了，因为添加了外键约束 没有4的部门
-- Cannot add or update a child row: a foreign key constraint fails (`xxxcompany`.`employee`, CONSTRAINT `employee_dept_fk` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- INSERT INTO employee VALUES(7,'小布什',3);
SELECT * FROM employee;

-- 修改数据
UPDATE employee SET deptId = 5 WHERE id = 7; -- 修改不了. 因为外键约束

-- 级联操作问题:

-- 如果没有添加级联操纵，如果我要删除dept里的工程部，删不掉，因为第二张表里需要这条数据
-- 怎么解决? 为副表添加级联操作
UPDATE dept SET id = 5 WHERE id = 3; -- employee 中的deptId 也更正修改
DELETE FROM dept WHERE id = 1; -- employee 中的工程部人员也全部被删除