约束条件（补充）
概要
-
约束条件之主键
-
约束条件之自增
-
约束条件之外键
详情
-
约束条件之主键
主键:primary key 1.从约束条件层面来说,主键相当于 not null unigue(非空且唯一) create table t1(id int primary key); create table t2(id int not null unique); # 可知,t1表的id字段与t2表一样也具有非空且唯一的性质 2.主键是InnoDB存储引擎组织数据表的依据 (1) InnoDB规定一个表必须'有且只有'一个主键 (2) 如果未指定主键,则会自动采用隐藏字段作为主键 (3) 如果表中没有主键但存在'非空且唯一'的字段,则自上而下的第一个该类字段会自动变成该表的主键 (4) 主键具有索引的功能可以加快查询速度,如果是隐藏字段则无法加快查询速度,故不推荐使用此方式 eg: create table test_PK( id int, name char(16) age int not null unique, phone char(11) not null unique );
在SQL语句中,除了可以将单列设为主键以外,也可以将多列的组合设为主键 eg: create table test_mul_PK( id int, name char(4), primary key(id,name) );
-
约束条件之自增
假设有这样一张表 create table test_ai( id int primary key, name varchar(16) ); 如果表中已存在特别多的数据,若主键是手动增加再插入的序号,可能会令使用者难以知晓已存在的主键值,这时方便起见需要用到SQL语句的自增语句,配合主键一起使用 create table test_auto_increment( id int primary key auto_increment, name varchar(16) ); # 主键自增的表插入数据时无需对主键赋值 insert into test_auto_increment(name) values('Leoric'); insert into test_auto_increment(name) values('Leo'); # 创建表时,一般将id类字段作为主键,写为 id int primary key auto_increment
1.delete操作删除数据时不会重置或回退当前自增主键的值 eg: delete from test_auto_increment where id=2; 2.truncate操作清空数据时可以同时清空当前自增主键的值 eg: truncate test_auto_increment;
-
约束条件之外键
外键的由来
# 引入 如果存在这样一张员工表 | 编号 | 姓名 | 年龄 | 部门名 | 部门描述 | 这张表在使用上存在以下几点问题 1.表结构不清晰 这是张员工表还是部门表? 2.表数据重复 会有多个员工在同一个部门,部门信息重复? 3.数据扩展性很差 如果要修改部门名,需要修改的数据量? # 如果要缓解上述问题,需要将表拆开 1.员工表: 员工编号 员工姓名 年龄 2.部门表: 部门编号 部门名称 部门描述 # 这样两张表将毫无关联 """ 解决方案:可以在员工表中添加一个部门编号字段(外键) """
表关系的种类
外键：记录表与表之间数据关系的字段。
表与表之间数据关系分为四种： 一对多、多对多、一对一、无关系。 '''判断表关系遵循换位思考原则'''
1、一对多关系
# 判断表关系,以员工表和部门表为例 1.先站在员工表的层面 问:一个员工能否对应多个部门? 答:不可以 2.再站在部门表的层面 问:一个部门能否对应多个员工? 答:可以 3.由此可见 一方可以,另一方不可以,员工表与部门表的关系即为"一对多" "一对多"的表关系中,外键字段创建在'多'的那一方 eg:员工表与部门表 create table emp( id int primary key auto_increment, name char(16), age int, dep_id int, foreign key(dep_id) references dep(id) ); create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(32) );
外键的特性
# 创建表时先写基本字段类型与约束条件，然后在考虑外键关系 1.创建表时必须先创建被关联表(自身不存在外键字段的表,故上述先创建员工表会报错) 2.插入记录时必须先插入被关联表的记录,再插入关联表的记录 3.被关联表中的记录无法自由删除和修改 4.如果要实现被关联表和关联表外键字段的自由删除和修改,则需使用级联更新/级联删除功能 eg: create table emp_cas( id int primary key auto_increment, name char(16), age int, dep_id int, foreign key(dep_id) references dep(id) on update cascade # 级联更新 on delete cascade # 级联删除 ); 往上一节创建的dep部门表中插入记录 insert into dep(dep_name,dep_desc) values('技术部','高精尖技术持有者'); insert into dep(dep_name,dep_desc) values('财务部','金融管理者'); 往emp_cas级联员工表中插入记录 insert into emp_cas(name,age,dep_id) values('Leoric',18,'1'),('Jack',20,'2');
修改被关联表的主键 update dep set id=200 where id=2;
删除被关联的表的记录 delete from dep where id=222;
2、多对多关系
# 判断表关系,以书籍表和作者表为例 1.先站在书籍表层面 问:一本书籍能否对应多个作者? 答:可以 2.再站在作者表层面 问:一个作者能否对应多本书籍? 答:可以 3.由此可见 两方均可以,书籍表和作者表的关系即为"多对多" eg:书籍表与作者表 create table book( id int primary key auto_increment, name char(16), price int, author_id int, foreign key(author_id) references author(id) on update cascade on delete cascade ); create table author( id int primary key auto_increment, name char(16), age int, book_id int, foreign key(book_id) references book(id) on update cascade on delete cascade );
''' 这是错误操作！！！！！！ 由于两表互为外键关系,先创建任意一张都会报错 故多对多表关系中,外键字段需创建在第三张关系表中 ''' create table book( id int primary key auto_increment, name char(16), price int ); create table author( id int primary key auto_increment, name char(16), age int ); create table book_from_author( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade on delete cascade, foreign key(book_id) references book(id) on update cascade on delete cascade );
3、一对一关系
# 判断表关系,以用户表与用户详情表为例 1.先站在用户表层面 问:一个用户能否对应多个详情? 答:不可 2.再站在用户详情表层面 问:一个用户详情能否对应多个用户? 答:不可 3.由此可见 两方都不可以,表关系可能是"一对一"也可能没有关系 这里用户与用户详情显然是"一对一"关系 外键可以创建在任意一张表,最好创建在查询频率高的表中,这里由于一般通过用户去查询用户的详情,故建在用户表比较合适 create table user( id int primary key auto_increment, name varchar(16), detail_id int unique, foreign key(detail_id) references user_detail(id) on update cascade on delete cascade ); create table user_detail( id int primary key auto_increment, phone bigint, addr varchar(16) ); # 和一对多的表关系的区别在于外键具有唯一约束
4、表关系总结
判断思路: 1.先从表1看 问:表1的一条记录能否对应表2的多条记录? 答:可以/不可以 2.再从表2 问:表2的一条记录能否对应表1的多条记录? 答:可以/不可以 # 结论 若双方均为可以,则表关系为多对多。 若双方均为不可以,则表关系为一对一或者没有关系。 若一方可以,另一方不可以,则表关系为一对多。
修改表相关SQL语句
1.修改表名 alter table 旧表名 rename 新表名;
2.增加字段 alter table 表名 add 字段名 数据类型[完整性约束],add 字段名 数据类型[完整性约束]; # 增加多个字段 alter table 表名 add 字段名 数据类型[完整性约束] first; #在首位增加字段(很少用) alter table 表名 add 字段名 数据类型[完整性约束] after 字段名; # 在某字段后增加字段
3.删除字段 alter table 表名 drop 字段名;
4.修改字段 alter table 表名 modify 字段名 数据类型 [完整性约束]; alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束] alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束] modify关键字只能修改字段数据类型和完整性约束条件,不能修改字段名。 change关键字能修改字段名、字段数据类型和完整性约束条件。
书籍表与出版社表 1.先从书籍表层面看 问:一本书籍能否在多家出版社出版? 答:不可以 2.再从出版社表层面看 问:一家出版社能否出多本书籍 答:可以 3.由此可见,书籍表与出版社表是"一对多"的关系,外键要设在多的一方,即书籍表 create table publisher( id int primary key auto_increment, name varchar(16) ); create table books( id int primary key auto_increment, name varchar(16), pub_id int, foreign key(pub_id) references publisher(id) on update cascade on delete cascade ); insert into publisher(name) values('人民出版社'); insert into publisher(name) values('人民邮电出版社'); insert into books(name,pub_id) values('gxdxy',1); insert into books(name,pub_id) values('理想国',1); insert into books(name,pub_id) values('Python编程 从入门到实践',2);
老师表与课程表 1.先从老师表层面看 问:一名老师能否教多个课程? 答:不可以 2.再从课程表层面看 问:一个课程能否被多个老师教? 答:不可以 3.由此可见,老师表与课程表是"一对一"的关系,外键建在课程表 create table teachers( id int primary key auto_increment, name varchar(16) ); create table lessons( id int primary key auto_increment, name varchar(16), teach_id int unique, foreign key(teach_id) references teachers(id) on update cascade on delete cascade ); insert into teachers(name) values('无极剑圣'); insert into teachers(name) values('疾风剑豪'); insert into lessons(name,teach_id) values('阿尔法突袭',1); insert into lessons(name,teach_id) values('狂风绝息斩',2);
学生表与班级表 1.先从学生表层面看 问:一名学生能否有多个班级? 答:不可以 2.再从班级表层面看 问:一个班级能否有多个学生? 答:可以 3.由此可见,学生表与班级表是"一对多"的关系,外键建在学生表 create table classes( id int primary key auto_increment, name varchar(16) ); create table students( id int primary key auto_increment, name varchar(16), cla_id int, foreign key(cla_id) references classes(id) on update cascade on delete cascade ); insert into classes(name) values('高三3班'); insert into classes(name) values('高三4班'); insert into students(name,cla_id) values('Seven',1); insert into students(name,cla_id) values('Ace',2); insert into students(name,cla_id) values('Leo',2); insert into students(name,cla_id) values('Jack',2);
书籍表与作者表 1.先从书籍表层面看 问:一本书籍能否由多个作者创作? 答:不可以 2.再从作者表层面看 问:一个作者能否创作多本书籍? 答:可以 3.由此可见,书籍表与作者表是"一对多"的关系,外键要设在多的一方,即书籍表 create table authors( id int primary key auto_increment, name varchar(16) ); create table books2( id int primary key auto_increment, name varchar(16), auth_id int, foreign key(auth_id) references authors(id) on update cascade on delete cascade ); insert into authors(name) values('东野圭吾'); insert into authors(name) values('阿加莎-克里斯蒂'); insert into books2(name,auth_id) values('白夜行',1); insert into books2(name,auth_id) values('解忧杂货店',1); insert into books2(name,auth_id) values('鸽群中的猫',2);
作者表与作者详情表 1.先从作者表层面看 问:一名作者能否对应多个详情? 答:不可以 2.再从作者详情表层面看 问:一个作者详情能否对应多名作者? 答:不可以 3.由此可见,作者表与作者详情表是"一对一"的关系,外键要设在查询频率高的一方,即作者表 create table authors2( id int primary key auto_increment, name varchar(16), info_id int, foreign key(info_id) references authors_info(id) on update cascade on delete cascade ); create table authors_info( id int primary key auto_increment, phone char(11), addr varchar(16) ); insert into authors_info(phone,addr) values('66666666666','上海市嘉定区'); insert into authors_info(phone,addr) values('88888888888','上海市宝山区'); insert into authors2(name,info_id) values('Leo',1); insert into authors2(name,info_id) values('Ace',2);
浙公网安备 33010602011771号