MySQL高级之约束

MySQL高级之约束

一、主键约束

1、创建表时添加主键

创建表学生表st1, 包含字段(id, name, age)将id做为主键

CREATE TABLE st1 (
	id INT PRIMARY KEY,
	NAME VARCHAR(10),
	age INT
);

INSERT INTO st1 VALUES (1, '刘德华', 60);
INSERT INTO st1 VALUES (2, '郭富城', 58);

2、演示主键约束: 唯一非空

INSERT INTO st1 VALUES (2, '黎明', 60); -- Duplicate entry '2' for key 'PRIMARY': 主键重复啦
INSERT INTO st1 VALUES (NULL, '黎明', 60); -- Column 'id' cannot be null: 主键不能为NULL

3、主键删除和添加（了解）

-- 删除主键约束(了解)
ALTER TABLE st1 DROP PRIMARY KEY;

-- 在已有表中添加主键约束(了解)
ALTER TABLE st1 ADD PRIMARY KEY(id);

4、主键自动增长

(1)创建学生表st2, 包含字段(id, name, age)将id做为主键并自动增长

CREATE TABLE st2 (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	age INT
);

(2)主键默认从1开始自动增长: i++

INSERT INTO st2 (NAME, age) VALUES ('貂蝉', 28);
INSERT INTO st2 (NAME, age) VALUES ('西施', 18);
INSERT INTO st2 (NAME, age) VALUES ('王昭君', 26);
INSERT INTO st2 (NAME, age) VALUES ('杨玉环', 22);

(3)修改自动增长的开始值(面试题)

ALTER TABLE st2 AUTO_INCREMENT = 10000;
INSERT INTO st2 (NAME, age) VALUES ('西施', 18);

5、唯一约束、非空约束、默认值约束

CREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT, -- 员工id，主键且自增长
	ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
	joindate DATE NOT NULL, -- 入职日期，非空
	salary DOUBLE(7,2) NOT NULL , -- 工资，非空
	bonus DOUBLE(7,2) DEFAULT 1000 -- 奖金，如果没有奖金默认为1000
);

INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES(1, '张三', '1999-11-11', 8800, 5000);
INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES(2, '李四', '1999-11-11', 8800, 5000);

(1)演示非空约束		Column 'ename' cannot be null: ename不能为NULL

INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES(3, NULL, '1999-11-11', 8800, 5000);

(2)演示唯一约束		Duplicate entry '李四' for key 'ename': 李四重复了

INSERT INTO emp(id, ename, joindate, salary, bonus) VALUES(3, '李四','1999-11-11', 8800, 5000);

(3) 演示默认约束

INSERT INTO emp(id, ename, joindate, salary) VALUES(3, '王五', '1999-11-11', 8800);

二、外键约束

6、外键约束1

-- 准备数据: 
-- 创建部门表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

-- 创建员工表
-- 创建 employee 并添加外键约束
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
	CONSTRAINT fk_abc FOREIGN KEY(dep_id) REFERENCES department(id)
);

添加数据

-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');

-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES 
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

演示：

(1)添加不正常的数据,报错: Cannot add or update a child row: a foreign key constraint fails  

INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 6);

(2)删除外键约束(了解)

ALTER TABLE employee DROP FOREIGN KEY employee_ibfk_1;

-- 在已有表添加外键约束, 外键约束可以省略: CONSTRAINT 外键约束名 (了解)
-- 省略CONSTRAINT外键约束名 数据库会自动设置外键约束的名字,我们要到 `3信息` 中查找
ALTER TABLE employee ADD FOREIGN KEY(dep_id) REFERENCES department(id);