MySQL实战10 约束
2019-04-22 本文已影响1人
香沙小熊
前言
约束：限制，限制我们表中的数据，保证添加到数据表中的数据准确和可靠性！凡事不符合约束的数据，插入时就会失败，插入不进去的！
比如：学生信息表中，学号就会约束不可重复！
1.约束基本概念
1.1.约束的分类：
- not null：非空约束，保证值不能为空
- default：默认约束,保证字段会有默认值，即时没有插入值，都会有默认值。
-
primary key：主键约束，同时保证
唯一性和非空 - unique：唯一，保证唯一性但是可以为空
- check：检查性约束【MySQL不支持，语法不报错，但无效】
-
foreign key：外键约束，用于限制两个表的关系，保证从表该字段的值必须来自于
主表相关联的字段的值，不能无中生有！
比如：员工信息表中的"部门编号"的值，就必须有外键约束。
1.2.添加约束的时期：
创建表的时候
修改表的时候
1.3.约束的添加分类：
1.3.1列级约束
6种约束可以写，语法都支持，不报错，但外键约束写了等于白写
1.3.2表级约束
not null 非空、default默认不支持，其他都可以！
DROP TABLE IF EXISTS students;
CREATE TABLE students(
id INT PRIMARY KEY,#主键约束
s_name VARCHAR(10) NOT NULL,#非空约束
s_sex CHAR(1) NOT NULL DEFAULT '女',
s_birthday DATE ,
c_id INT REFERENCES courses(id),#外键约束,不生效
#表级约束
#CONSTRAINT pk PRIMARY KEY(id),
#CONSTRAINT uq UNIQUE(s_name),
CONSTRAINT fk_students_courses FOREIGN KEY(c_id) REFERENCES courses(id)
);
SHOW INDEX FROM students;
总结一下表级约束但语法：
【CONSTRAINT 约束名字】约束类型(字段名称) 【额外但东西，如外键】
2.约束详细：
2.1一般情况下MySQl的约束规范：
主键、非空、唯一性，默认等这些写在列级
外键约束写在表级
CREATE TABLE courses(
id INT PRIMARY KEY,
c_name VARCHAR(20) NOT NULL
);
2.2.主键和唯一性的区别：
2.2.1 主键(primary key)约束：同时保证唯一和非空
在同一个表中主键只能有一个在同一个表中可以将多个字段组合成一个主键(不推荐)
CONSTRAINT pk PRIMARY KEY(id,s_name),id和s_name组合起来成表的一个主键
2.2.2 唯一(unique)约束：
保证唯一但可以为空
在同一个表中唯一约束可有很多个
在同一个表中可以将多个字段组合成一个唯一但约束（不推荐）
2.3 外键的特点：
- 要求从表上设置外键约束
- 从表上的列和主表上的对应但关系列但数据类型必须一致，含义意义一致
- 主表中的关联列必须是一个key(一般是主键，很少时候也可以是唯一键)
- 插入数据时，先插入主表，再插入从表，删除的时候，先删除从表记录，再删除主表记录
DROP TABLE IF EXISTS students;
CREATE TABLE students(
id INT PRIMARY KEY,#主键约束
s_name VARCHAR(10) NOT NULL,#非空约束
s_sex CHAR(1) NOT NULL DEFAULT '女',
s_age INT(3),
s_birthday DATE ,
s_seat INT(3) ,
c_id INT REFERENCES courses(id),#外键约束,不生效
UNIQUE KEY s_seat (s_seat)
#表级约束
#CONSTRAINT pk PRIMARY KEY(id),
#CONSTRAINT uq UNIQUE(s_name),
CONSTRAINT fk_students_courses FOREIGN KEY(c_id) REFERENCES courses(id)
);
SHOW INDEX FROM students;
总结：表级约束但语法
【CONSTRAINT 约束名字】约束类型(字段名称) 【额外的东西，如外键】
CREATE TABLE courses(
id INT PRIMARY KEY,
c_name VARCHAR(20) NOT NULL
);
3.修改表时添加和删除约束
1.非空约束
ALTER TABLE students MODIFY COLUMN s_name VARCHAR(20) NOT NULL; #添加
DESC students;
ALTER TABLE students MODIFY COLUMN s_name VARCHAR(20) ; #删除
DESC students;
2.添加默认约束
ALTER TABLE students MODIFY COLUMN s_age int DEFAULT 18;
DESC students;
ALTER TABLE students MODIFY COLUMN s_age int;
DESC students;
3.添加主键
ALTER TABLE students MODIFY COLUMN id int PRIMARY KEY;
DESC students;
ALTER TABLE students MODIFY COLUMN id int ;#删除不掉主键
ALTER TABLE students DROP PRIMARY key;#删除主键
DESC students;
4.添加唯一键
ALTER TABLE students MODIFY COLUMN s_seat int UNIQUE;#添加
DESC students;
ALTER TABLE students DROP KEY s_seat;#删除
DESC students;
5.外键
ALTER TABLE students ADD FOREIGN KEY(c_id) REFERENCES courses(id); #添加
DESC students;
SHOW INDEX FROM students;
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='students'; #查找键名主键外键名
ALTER TABLE students DROP FOREIGN KEY students_ibfk_1; #删除
DESC students;
4.标识列
有自增长列，可以不用插入值，MySQl自动提供默认的序列值。
创建表的时候添加自增长列：
DROP TABLE IF EXISTS t_identity;
CREATE TABLE t_identity(
id int PRIMARY key AUTO_INCREMENT,
`name` VARCHAR(20)
);
有了自增长列，我们添加数据记录就可以:
INSERT INTO t_identity VALUES(null,'张三');
INSERT INTO t_identity VALUES(null,'李四');
INSERT INTO t_identity VALUES(null,'王二');
INSERT INTO t_identity VALUES(null,'王新');
INSERT INTO t_identity(name) VALUES(null,'王新');
修改表的时候添加自增长列：
ALTER TABLE t_identity MODIFY COLUMN id int AUTO_INCREMENT;
删除自增长
ALTER TABLE t_identity MODIFY COLUMN id int ;
总结：
- 自增长列必须和键(一般是主键)搭配
- 一个表中有且只能有一个自增长列
- 自增长列的类型只能数值型，一般情况用int
- 自增长列可以设置步长( set AUTO_INCREMENT_INCREMENT=3;)也可以手动插入一个数值改变起始值。
