DDL(数据定义)语言的学习(2)
目录
- 创建表时添加约束
- 修改表时添加约束
- 修改表时删除约束
- 标识列(自增长列)
#常见约束
含义: 一种限制,用于限制表中的数据,为了保证表中数据的准确和可靠性
分类: 六大约束
1、NOT NULL:非空,用于保证该字段的值不能为空
比如姓名、学号等
2、DEFAULT:默认,用于保证该字段有默认值
比如性别
3、PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空
比如学号、员工编号等
4、UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空
比如座位号
5、CHECK:检查约束【mysql中不支持】
比如年龄、性别限制它们的范围类型
6、FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束,用于引用主表中某列的值
比如学生表的专业编号,员工表的部门编号,员工表的工种编号
注意:添加约束的时机:(必须在未添加数据之前)
1、 创建表时
2、 修改表时
约束的添加分类:
1、 列级约束:
六大约束语法都支持,但(FOREIGN KEY)外键约束没有效果
2、 表级约束:
除了非空、默认,其他的都支持
语法:
create table 表名(
字段名 字段类型 列级约束,
字段名 字段类型 列级约束,
表级约束
);
一、 创建表时添加约束
1、添加列级约束
语法:
直接在字段名和类型后面追加约束类型即可。
注意: 只支持默认、非空、主键、唯一
主键和唯一的大对比:
区别 | 保证唯一性 | 是否允许为空 | 一个表中可以有多少个 | 是否允许组合 |
主键 | 是 | 否 | 至多有一个 | 是,但不推荐 |
唯一 | 是 | 是 | 可以有多个 | 是,但不推荐 |
注意: 组合约束 constraint pk primary key(id,stuname)要id和stuname同时重复才起效果
外键:
1、 要求在从表设置外键关系(例如从表stuinfo:majorid,主表major:id,majorName)
2、 从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3、 主表的关联列必须是一个key(一般是主键或唯一)
4、 插入数据时,先插入主表,再插入从表
5、 删除数据时,先删除从表,再删除从表
#创建student表
create database students;
use students;
create table stuinfo(
id int primary key,#主键
stuName varchar(20) not null,unique#非空且唯一
gender char(1) check(gender='男'or gender='女'),#检查(mysql不支持check ,sql server支持)
seat int unqiue,#唯一
age int default 18,#默认约束
majorId int references major(id)#外键
);
#创建major表
create table major(
id int primary key,
majorName varchar(20)
);
desc stuinfo;#查看表结构
#查看stuinfo表中所有的索引,包括主键、外键、唯一
show index from stuinfo;
2、添加表级约束
语法:
在各个字段的最下面
【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),#主键
constaint uq unique(seat),#唯一键
constraint ck check(gender='男' or gender='女'),
constraint fk_stuinfo_major foreign key(majorid) references major(id)#外键
);
#通用的写法:
create table if not exists stuinfo(
id int primary key,
stuname varchar(20) not null,
age int default 18,
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 约束名】 约束类型(字段名)【外键的引用】
create table stuinfo(
id int,
stuname varchar(20),
gender char(1),
seat int,
age int,
majorid int
);
1、添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;
2、添加默认约束
alter table stuinfo modify column age int default 18;
3、添加主键
(1)列级约束
alter table stuinfo modify column id int primary key;
(2)表级约束
alter table stuinfo add primary key(id);
4、添加唯一
(1)列级约束
alter table stuinfo modify column saet int unique;
(2)表级约束
alter table stuinfo add unique(seat);
5、添加外键
alter table stuinfo add foregin key(majorid) references major(id);
6、级联删除
#先执行alter
alter table stuinfo add constraint fk_stu_major foreign key(majorid)
references major(id) on delete cascade;
#再执行delete
delete from major where id=3;#删除专业表的3号专业
7、级联置空
#先执行alter
alter table stuinfo add constraint fk_stu_major foreign key(majorid)
references major(id) on delete set null;
#再执行delete
delete from major where id=3;#删除专业表的3号专业
三、修改表时删除约束
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;
练习
1、向emp2的id列中添加primary key约束(my_emp_id_pk)
#方式一
alter table emp2 add constraint my_emp_id_pk primary key(id);
#方式二
alter table emp2 modify column id int primary key;
2、向emp2表中添加列dept_id,并在其中定义foreign key约束,与之相关联的列是dept2表中的id列
#方式一
alter table emp2 add column dept_id int;
#方式二
alter table emp2 add foreign key(dept_id) references dept2(id);
区别 | 位置 | 支持的约束类型 | 是否可以起约束名 |
列级约束 | 列的后面 | 语法都支持,但外键没有效果 | 不可以 |
表级约束 | 所有列的下面 | 默认和非空不支持,其他支持 | 可以(主键无效果) |
四、标识列
标识列:又称为自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
1、标识列必须和主键搭配吗?不一定,但要求是一个可以
2、一个表可以有几个标识列?至多一个
3、标识列的类型只能是数值型(int,float,double)
4、标识列可以通过set auto_increment_increment=3;设置步长
5、可以通过手动插入一个值,后续默认为null来改变起止值
一、创建表时设置标识列
create table tab_identity(
id int primary key auto_increment,# id为主键且自增长
name varchar(20)
);
二、修改表时设置标识列
alter table tab_identity modify column id int primary key auto_increment;
三、修改表时删除标识列
alter table tab_identity modify column id int;