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