mysql 约束

/*
约束：
六大约束：
    not null:   非空，用于保证该字段的值不能为空，  比如姓名 学号等
    default：    默认， 用于保证该字段有默认值  比如性别
    primary key:    主键，用于保证该字段的值具有唯一性，并且非空， 比如学号
    unique： 唯一， 用于保证该字段的值具有唯一性
    check:      检查约束，mysql不支持 ， 比如年龄
    foreign key:    外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值， 比如 专业
                  在从表添加外键约束，用于引用主表中某列的值，  比如专业编号 部门编号 员工工种编号

添加约束的时机：
    1. 创建表时
    2. 修改表时
    
约束的添加分类：
    列级约束：
        六大约束语法上都支持，但外键约束没有效果
    表级约束：
        除了非空，默认，其他都支持
        
主键和唯一的对比：
        保证唯一性   是否允许为空      一个表中可以有几个   是否允许组合
    主键  √       ×           至多有一个       √ 但不推荐
    唯一  √       √(只允许一个为空)  可以有多个       √ 但不推荐
    
    insert into major values(1,'java');
    insert into major values(2,'h5');
    insert into stuinfo values(1,'john', '男', null, 19,1);
    insert into stuinfo values(2,'lily','男',null,19,2); 报错
        
外键：
    1. 要求在从表设置外键关系
    2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
    3. 主表的关联列必须是一个key(一般是主键或唯一)
    4. 插入数据时，应该先插入主表数据，再插入从表
       删除时，顺序相反
        
create table 表名(
    字段名 字段类型 列级约束
    字段名 字段类型
    表级约束
)
*/

create table Book(
  bid int primary key,
 bname varchar(20) unique not null,
price float default 10,
btypeId int,
foreign key(btypeid) reference bookType(id)
);

#添加列级约束
/*
 语法：
      直接在字段名和类型后面添加约束类型即可
      只支持：默认、非空、主键、唯一
*/
CREATE DATABASE student;
USE student;
CREATE TABLE stuinfo(
    id INT PRIMARY KEY,#主键
    stuName VARCHAR(20) NOT NULL,
    gender CHAR(1) CHECK(gender='男' OR gender='女'), # gender in ('男'，’女‘)
    seat INT UNIQUE,
    age INT DEFAULT 18,
    magiorid INT REFERENCES major(id)   
);

CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);
SELECT * FROM major;
SELECT * FROM stuinfo;
DESC stuinfo;

# 查看stuinfo表中所有的索引，包括主键，外键，唯一
SHOW INDEX FROM stuinfo;

#添加表级约束
/*
 语法：
 在各个字段的最下面
 [constraint 约束名] 约束类型(字段名)
      
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,#主键
    stuName VARCHAR(20),
    gender CHAR(1), 
    seat INT ,
    age INT ,
    majorid INT,
    CONSTRAINT pk PRIMARY KEY(id),#主键
    CONSTRAINT uq UNIQUE(seat), #唯一
    CONSTRAINT ck CHECK(gender='男' OR gender='女'),
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

SHOW INDEX FROM stuinfo;


CREATE TABLE stuinfo(
    id INT,#主键
    stuName VARCHAR(20),
    gender CHAR(1), 
    seat INT ,
    age INT ,
    majorid INT,
    PRIMARY KEY(id),#主键
    UNIQUE(seat), #唯一
    CHECK(gender='男' OR gender='女'),
    FOREIGN KEY(majorid) REFERENCES major(id)
);

# 通用的写法

CREATE TABLE IF NOT EXISTS stuinfo(
    id INT PRIMARY KEY,
    stuName VARCHAR(20) NOT NULL,
    sex CHAR(1),
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

# 修改表时添加约束
/*
1. 添加列级约束
alter table 表名 modify column 字段名 字段类型的约束;

2. 添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名)[外键的引用];

*/
CREATE TABLE stuinfo(
    id INT,#主键
    stuName VARCHAR(20),
    gender CHAR(1), 
    seat INT ,
    age INT ,
    majorid INT
);

# 添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL;

ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NULL;

# 添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

# 添加主键
#列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#添加唯一约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

ALTER TABLE stuinfo ADD UNIQUE(seat);

#添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);

#修改表时删除约束
#1. 删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2. 删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;

#3. 删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4. 删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

SHOW INDEX FROM stuinfo;

#5. 删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

        位置      支持的约束类型         是否可以起约束名
列级约束    列的后面    语法都支持，但外键没有效果   不可以
表级约束    所有列的下面  默认和非空不支持，其他支持   可以(主键没有效果)

# 标识列 自增长列
/*
 又称为自增长列
 含义：
    可以不用手动的插入值，系统提供默认的序列值
 
 特点：
   1. 标识列必须和主键搭配吗？不一定，但要求一定是key
   2. 一个表最多只能有一个标示列
   3. 标识列的类型只能是数值型 
   4. 标识列可以通过 set auto_increment_increment=3; 设置步长
      可以手动插入值设置起始值
     
*/
# 一，创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE IF NOT EXISTS tab_identity(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);

INSERT INTO tab_identity VALUES(NULL,'john');
INSERT INTO tab_identity(NAME) VALUES('lucy');

SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';

# 二。 修改表时设置标识列
ALTER TABLE tab_identiy MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

# 三。修改表时删除标识列
ALTER TABLE tab_identiy MODIFY COLUMN id INT;