1. 约束

# 约束

CREATE TABLE tb_user2(
  id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
  name varchar(10) NOT NULL UNIQUE COMMENT '姓名',
  age int COMMENT '年龄',
  status char(1) default '1' COMMENT '状态',
  gender char(1) COMMENT '性别'
) comment '用户2表';
-- check (age > 0 && age <= 120)
-- mysql8.0.16版本之后才支持check


insert into tb_user2(name,age,status,gender) values ('Tom1',19,'1','男'),('Tom2',25,'0','男');
insert into tb_user2(name,age,status,gender) values ('Tom3',19,'1','男');
insert into tb_user2(name,age,status,gender) values ('Tom4',80,'1','男');
insert into tb_user2(name,age,gender) values ('Tom5',120,'男');

insert into tb_user2(name,age,status,gender) values (null,19,'1','男'); -- 插入失败[23000][1048] Column 'name' cannot be null
insert into tb_user2(name,age,status,gender) values ('Tom3',19,'1','男'); -- 插入失败[23000][1062] Duplicate entry 'Tom3' for key 'name'
-- insert into tb_user2(name,age,status,gender) values ('Tom5',-1,'1','男');
-- insert into tb_user2(name,age,status,gender) values ('Tom5',121,'1','男');



# 外键约束

create table dept2(
                   id int auto_increment comment 'ID' primary key,
                   name varchar(50) not null comment '部门名称'
)comment '部门2表';

INSERT INTO dept2(id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办');

create table emp2(
                  id int auto_increment comment 'ID' primary key,
                  name varchar(50) not null comment '姓名',
                  age int comment '年龄',
                  job varchar(20) comment '职位',
                  salary int comment '薪资',
                  entrydate date comment '入职时间',
                  managerid int comment '直属领导ID',
                  dept_id int comment '部门ID'
)comment '员工2表';

INSERT INTO emp2(id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
       (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
       (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
       (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
       (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
       (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1);





-- 添加外键fk_emp_dept_id
/*
alter table emp2
add constraint fk_emp_dept_id
foreign key (dept_id)
references dept2(id);
*/

-- 删除外键fk_emp_dept_id
alter table emp2 drop foreign key fk_emp_dept_id;


-- 外键约束的删除/更新行为
-- 由于NO ACTION 是默认行为，前面语法演示的时候，已经测试过了，就不再演示了，这里只演示其他的两种行为：CASCADE、SET NULL
alter table emp2 add constraint fk_emp_dept_id
foreign key (dept_id) references dept2(id)
on update cascade on delete cascade ;


alter table emp2 add constraint fk_emp_dept_id
foreign key (dept_id) references dept2(id)
on update set null on delete set null ;

2. 多表查询

3. 事务