-- 表名和字段名系统默认会转换大写，即不区分大小写
DROP TABLE t_student;
create table t_student
(
  id int PRIMARY KEY NOT NULL, -- 主键必须唯一，不能为空
  stu_no INT UNIQUE ,-- 唯一约束，可以为空，除非设置为not NULL，并且可以有多个null
  stu_name varchar(20) NOT NULL,-- 非空约束
  stu_addr varchar(100) ,
  stu_phone varchar(11),
    stu_sex SMALLINT DEFAULT 1 CHECK(stu_sex in(0,1)),-- 检查约束，check约束
  stu_age tinyint(4)  NOT NULL DEFAULT 30 CHECK (stu_age BETWEEN 20 AND 60) -- 默认值约束，check约束对数据验证没有任何作用 not null必须在默认值前，否则会报错
)

SELECT * FROM t_student;
-- 插入报错的insert
-- 主键唯一性
INSERT INTO t_student VALUES (1,2314,‘张三‘,‘深圳南山‘,‘13264581265‘,0,25);
-- Duplicate entry ‘1‘ for key ‘PRIMARY‘ 主键唯一，不能重复，报错
INSERT INTO t_student VALUES (1,2315,‘李四‘,‘深圳南山‘,‘13264581265‘,0,25);
-- Column ‘id‘ cannot be null 主键不能为空，报错
INSERT INTO t_student VALUES (null,2313,‘张三‘,‘深圳南山‘,‘13264581265‘,0,25);
-- UNIQUE 可以为空，除非设置为not NULL，并且可以有多个null，插入成功
INSERT INTO t_student VALUES (2,null,‘李四‘,‘深圳南山‘,‘13264581265‘,0,25);
INSERT INTO t_student VALUES (3,null,‘王五‘,‘深圳南山‘,‘13264581265‘,0,25);
-- 主键为空时，默认为0 ，第一条记录可以添加成功，第二天失败，因为主键唯一
INSERT INTO t_student (stu_no,stu_name) VALUES (7844,‘六六‘);
-- check约束对数据验证没有任何作用，插入成功，不报错
INSERT INTO t_student VALUES (4,2345,‘王五‘,‘深圳南山‘,‘13264581265‘,0,80);

DROP TABLE t_dept;
CREATE TABLE t_dept(
    dept_id INT(3) PRIMARY KEY,
    dept_no INT UNIQUE NOT NULL,
    dept_name VARCHAR(20) NOT NULL
);

DROP TABLE t_employee;
CREATE TABLE t_employee(
    emp_id INT(3) PRIMARY KEY,
    emp_no INT(3) UNIQUE NOT NULL,
    emp_name VARCHAR(10) NOT NULL,
    emp_age tinyint(4) NOT NULL DEFAULT 25 CHECK (emp_age BETWEEN 20 AND 60),
    sex VARCHAR(1) CHECK (sex in (‘男‘,‘女‘)),
    job VARCHAR(20),
    sal INT(10),
    -- inline写法
    -- REFERENCES 主表（主表字段）
    -- dept_no int  REFERENCES t_dept(dept_no)
    -- outline写法

  -- 删除外键时，引用外键的记录同步删除 
    -- dept_no int NOT NULL ,
    -- FOREIGN KEY(dept_no) REFERENCES t_dept(dept_no) ON DELETE CASCADE
    -- 删除外键时，引用外键的记录设置为空 
    dept_no int  ,
    FOREIGN KEY(dept_no) REFERENCES t_dept(dept_no) ON DELETE SET NULL
);


insert into t_dept values(1,10,‘IT技术部门‘);
insert into t_dept values(2,20,‘市场部‘);
insert into t_dept values(3,30,‘人事部‘);

-- 再插入员工数据


INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`)
VALUES(1,1,‘张三‘,25,‘男‘,‘软件开发‘,8500,10);
INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`)
VALUES(2,102,‘张天三‘,29,‘男‘,‘初级软件开发‘,4500,10);
INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`)
VALUES(3,103,‘张一‘,36,‘男‘,‘测试人员‘,7500,10);
INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`)
VALUES(4,104,‘王张三‘,63,‘男‘,‘人事主管‘,12500,20);
INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`)
VALUES(5,105,‘发呆张三‘,60,‘男‘,‘高级程序员‘,18500,10);
INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`)
VALUES(6,106,‘快乐张三‘,30,‘男‘,‘销售经理‘,10500,30);

SELECT * FROM t_employee;
SELECT * FROM t_dept;
-- MySQL设置了foreign key关联，造成无法更新或删除数据。可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况。
-- Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t_employee`, CONSTRAINT `t_employee_ibfk_1` FOREIGN KEY (`dept_no`) REFERENCES `t_dept` (`dept_no`))
DELETE FROM t_dept WHERE dept_no = ‘10‘;

-- 方法一：
SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM t_dept WHERE dept_no = ‘30‘;
-- 删除完后记得外键约束重新打开；
SET FOREIGN_KEY_CHECKS = 1;
-- 查mysql版本
select version() from dual;

-- 方法二：
定义表时

　　 -- 删除外键时，引用外键的记录同步删除 

     dept_no int NOT NULL ,
    FOREIGN KEY(dept_no) REFERENCES t_dept(dept_no) ON DELETE CASCADE


    -- 删除外键时，引用外键的记录设置为空 
    dept_no int  ,
    FOREIGN KEY(dept_no) REFERENCES t_dept(dept_no) ON DELETE SET NULL