【mysql】约束、外键约束、多对多关系

1、约束

DROP TABLE IF EXISTS emp;


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

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

show databases;

show tables;

select * from emp;

-- 演示主键约束：非空且唯一
INSERT INTO emp(id, ename, joindate, salary, bonus) values (null, '张三', '1999-11-11', 8800, 5000);

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);

-- 演示	非空约束
INSERT INTO emp(id, ename, joindate, salary, bonus) values (3, null, '1999-11-11', 8800, 5000);


-- 演示	非唯一约束
select * from emp;
INSERT INTO emp(id, ename, joindate, salary, bonus) values (3, '李四', '1999-11-11', 8800, 5000);


-- 演示	默认约束
INSERT INTO emp(id, ename, joindate, salary) values (3, '王五', '1999-11-11', 8800);

-- 演示	主键自增
INSERT INTO emp(ename, joindate, salary) values ('张三', '1999-11-11', 8800);
INSERT INTO emp(ename, joindate, salary) values ('李四', '1999-11-11', 8800);
INSERT INTO emp(ename, joindate, salary) values ('王五', '1999-11-11', 8800);
INSERT INTO emp(ename, joindate, salary) values ('赵六', '1999-11-11', 8800);

2、外键约束

DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS emp;

-- 部门表
CREATE TABLE dept (
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);

-- 员工表
CREATE TABLE emp (
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,
	
	-- 添加外键 dep_id, 关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
	
	-- CONSTRAINT fk_emp_dept FOREIGN KEY (dep_id) REFERENCES dept(id)
);

-- 添加 2 个部门
insert into dept (dep_name, addr) values
('研发部', '广州'), ('销售部', '深圳');

-- 添加员工, dep_id 表示员工所在的部门
insert into emp (name, age, dep_id) values
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);


-- 查询
select * from emp;
select * from dept;

-- 直接删不掉
delete from dept WHERE id = 1;


-- 需要先删除对外键的依赖才能删除
delete from emp WHERE dep_id = 1;
delete from dept WHERE id = 1;

-- 删除外键
alter table emp drop foreign key fk_emp_dept;

desc emp;

-- 创建完成表后, 添加外键
alter table emp add constraint fk_emp_dept foreign key(dep_id) REFERENCES dept(id);

3、多对多的关系

DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;

-- 订单表
CREATE TABLE tb_order (
	id int primary key auto_increment,
	payment double(10, 2),
	payment_type TINYINT,
	status TINYINT
);

-- 商品表
CREATE TABLE tb_goods (
	id int primary key auto_increment,
	title varchar(100),
	price double(10, 2)
);

-- 订单商品中间表
CREATE TABLE tb_order_goods (
	id int primary key auto_increment,
	order_id int,
	goods_id int,
	count int
);

-- 建表完后,添加外键
alter table tb_order_goods add constraint fk_order_id foreign key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add constraint fk_goods_id foreign key(goods_id) REFERENCES tb_goods(id);