12-mysql基础-mysql中的DDL-约束

create table 表名(
  字段名 字段类型 列级约束,
  字段名 字段类型,
  表级约束
);


含义：一种限制，用于限制表中的数据，保证表中的数据的准确和可靠性
复制

1.要求在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3.主表的关联列必须是一个key(一般是主键或唯一)
4.插入数据时，先插入主表，再插入从表
  删除数据时，先删除从表，在删除主表
复制

#创建表时添加约束
#1.添加列级约束
/*
语法：
直接在字段名和类型后追加约束类型即可
只支持：默认，非空，主键和唯一
*/
CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
  id INT PRIMARY KEY,#主键
  stuName VARCHAR(20) NOT NULL, #非空
  gender CHAR(1) CHECK(gender='男' OR gender='女'), #检查
  seat INT UNIQUE, #唯一约束
  age INT DEFAULT 20, #默认约束
  majorId INT REFERENCES major(id) #外键
);


CREATE TABLE major(
  id INT PRIMARY KEY,
  majorName VARCHAR(20)
);


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


#2.添加表级约束
/*
语法：在各个字段的最下面
[constraint 约束名] 约束类型(字段名)
其中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), #主键，主键名改名后名字仍会被默认为PRIMARY
  CONSTRAINT uq UNIQUE(seat), #唯一键
  CONSTRAINT ck CHECK(gender='男' OR gender='女'), #检查
  CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
  #以上constraint 约束名可以省略，写为primary key(id)...，此时默认名为()中的名
);
SHOW INDEX FROM stuinfo;
复制

#通用的写法：
CREATE TABLE IF NOT EXISTS stuinfo(
  id INT PRIMARY KEY,
  stuname VARCHAR(20) NOT NULL,
  gender CHAR(1),
  age INT DEFAULT 20,
  seat INT UNIQUE,
  majorid INT,
  CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
复制

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


2.添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
  id INT,
  stuname VARCHAR(20),
  gender CHAR(1),
  seat INT,
  age INT,
  majorid INT
);
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;


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


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


#4.添加唯一
#列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);


#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major 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;


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

/*
可创建随意emp2(仿照)表用于练习
*/
#向表emp2的id列中添加primary key约束(my_emp_id_pk)
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;


#向表dept2的id列中添加primary key约束(my_dept_id_pk)
ALTER TABLE emp2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);
#向表emp2中添加列dept_id，并在其中定义foreign key约束，与之相关联的列是dept2表中的id
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);


#列级约束：在列的后面，语法都支持，但外键没有效果，不可以起约束名
#表级约束：所有列下面，默认和非空不支持，其他支持，可以其约束名，但主键无效果
复制

又称为自增长列
含义：可以不用手动插入值，系统提供默认的序列值


特点：
1.标识列不一定和主键搭配，但要求是一个key来搭配
2.一个表中至多一个标识列
3.标识列类型只能是数值型
4.标识列可以通过 SET auto_increment_increment=3; 设置步长
  也可以通过 手动插入值 设置起始值
复制

#创建表时设置标识列
USE test;
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);


INSERT INTO tab_identity VALUES(NULL,'test');  #执行时不需要每次更改id值，默认从1开始
#或 insert into tab_identity(name) values('mysql');
#如果要设置起始值不从1开始，
#可以首次插入值使用INSERT INTO tab_identity VALUES(10,'test');随后使用(NULL,'test');


SELECT * FROM tab_identity;
TRUNCATE TABLE tab_identity;




SHOW VARIABLES LIKE 'auto_increment%'; 
#auto_increment_increment=1，auto_increment_offset=1 步长和起始偏移量
# mysql中不支持offset的设置
SET auto_increment_increment=3; #设置步长值为3，为全局量


#修改表时设置标识列
USE test;
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
  id INT,
  NAME VARCHAR(20),
  seat INT
);
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
DESC tab_identity;


#修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
DESC tab_identity;
复制