一:约束条件
default 默认值
# 补充知识点 插入数据的时候 可以指定字段顺序
create table t1(
id int,
name char(16)
);
insert into t1(name,id) values(‘jason‘,1);
# 设置默认值
create table t2(
id int,
name char(16) not null,
gender enum(‘male‘,‘female‘,‘others‘) default ‘male‘
);
insert into t2(name,id) values(‘jason‘,1);
insert into t2 values(2,‘egon‘,‘female‘);
Unique 唯一
# 单列唯一
create table t3(
id int unique,
name char(16)
);
insert into t3 values(1,‘jason‘),(1,‘egon‘); # 报错
insert into t3 values(1,‘jason‘),(2,‘egon‘); # 正确
# 联合唯一
ip 和 port
单个 都可以重复,但是 加在一起 必须是唯一的
create table t4(
id int,
ip char(16),
port int,
unique(ip,port)
);
insert into t4 values(1,‘127.0.0.1‘,8080);
insert into t4 values(2,‘127.0.0.1‘,8081);
insert into t4 values(3,‘127.0.0.2‘,8080);
insert into t4 values(4,‘127.0.0.1‘,8080); # 报错
Primary Key 主键
1.单单从约束效果上来看 primary key = notnull + unique
非空 并且 唯一
create table t5(id int paimary key);
insert into t5 values(null); # 报错
insert into t5 values(1),(1); # 报错
insert into t5 values(1),(2); # 正常
2.它除了有约束效果之外 它还是Innodb存储引擎 组织数据的依据
Innodb存储引擎在创建表的时候 必须有primary key
因为它类似于书的目录 能够帮助提高查询效率 并且也是建表的依据
① 一张表中 有且只有一个主键 如果没有设置主键 那么会从上往下搜索
直到遇到一个 非空且唯一的字段 将它自动生成主键
create table t6(
id int,
name char(16),
age int not null unique,
addr char(32) not null unique
);
② 如果表中没有主键 也没有其他任何的非空且唯一字段 那么Innodb会采用自己
内部提供的一个字段 作为主键,隐藏 意味着你无法使用到它 就无法提升查询速度
③ 一张表中 通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键
# 单个字段主键
create table t7(
id int,
name char(16)
);
# 联合主键(多个字段联合起来 作为表的主键 本质还是一个主键)
create table t8(
id int,
ip char(16),
port int,
primary key(ip,port)
);
也就意味着 以后我们在创建表的时候 id字段一定要加primary key
auto_increment 自增
# 当编号特别多的时候 人为地去维护太麻烦
create table t9(
id int primary key auto_increment,
name char(16)
);
insert into t9(name) values(‘jason‘);
insert into t9(name) values(‘egon‘);
insert into t9(name) values(‘tank‘);
# auto_increment只能加在主键上,不能给普通字段加
create table t10(
id int primary key auto_increment,
name char(16),
cid int auto_increment
);
# 报错:ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
结论:
以后再创建表的id(数据库的唯一表示id、uid、sid)字段的时候
id int primary key auto_increment
补充:
delete from 在删除表中数据的时候 主键的自增不会停止
truncate t1 清空表数据 并且重置主键
二:表与表之间 建立关系(约束)(重点)
定义一张员工表 表中有很多字段
id name gender dep_name dep_desc
# 1.该表的组织结构不是很清晰(可以忽视)
# 2.浪费硬盘空间(可忽视)
# 3.数据的扩展性极差(无法忽视的)
# 如何优化?
(上述问题类似于之前写的python代码都在一个py文件中)
将员工表拆分成 员工表 和 部门表
外键:
外键 就是用来帮助我们建立 表与表之间关系的
foreign key
表关系:
表与表之间的关系最多只有四种:
1.一对多关系
在MySQL的关系中 没有多对一这个概念
一对多 多对一 都是 一对多
2.多对多关系
3.一对一关系
4.没有关系
1.一对多关系
判断 表与表之间关系的时候 前期不熟悉的情况下
一定要换位思考 分别站在2张表的角度考虑
以 员工表 与 部门表 为例
站在员工表:
1个员工能否对应多个部门(1条员工数据 能否对应多条部门数据)
不能!!!
(不能直接得出结论 一定发要2张表都考虑完全)
站在部门表:
1个部门能都对应多个员工(1个部门数据 能否对应多条员工数据)
能!!!
得出结论:
员工表 与 部门表 是单向的 一对多
所以 表关系 就是 一对多
SQL语句建表
# 1.一对多表关系 外键字段建立在多的一方
# 2.在创建表的时候 一定要先建被关联表
# 3.在录入数据的时候 也必须先录入被关联表
# 创建部门表
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
# 创建员工表
create table emp(
id int primary key auto_increment,
name char(16),
gender enum(‘male‘,‘female‘,‘others‘) default ‘male‘,
dep_id int,
foreign key(dep_id) references dep(id)
);
insert into emp(name,dep_id) values(‘xxq‘,1); # 报错
insert into dep(dep_name,dep_desc) values(‘sb教学部‘,‘教书育人‘),(‘外交部‘,‘多人外交‘),(‘nb技术部‘,‘技术能力有限部‘);
insert into emp(name,dep_id) values(‘jason‘,2),(‘egon‘,1),(‘tank‘,1),(‘alex‘,3);
# 修改emp里面的dep_id字段 或者 dep表里面的id字段
update dep set id=200 where id=2; # 报错
# 删除dep表里面的数据
delete from dep; # 报错
# 1.先删除教学部对应的员工数据 再删除部门
操作太过繁琐
# 2.真正做到数据之间有关系
更新 就同步更新
删除 就同步删除
级联更新 级联删除
# 创建部门表
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
# 创建员工表
create table emp(
id int primary key auto_increment,
name char(16),
gender enum(‘male‘,‘female‘,‘others‘) default ‘male‘,
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
insert into dep(dep_name,dep_desc) values(‘sb教学部‘,‘教书育人‘),(‘外交部‘,‘多人外交‘),(‘nb技术部‘,‘技术能力有限部‘);
insert into emp(name,dep_id) values(‘jason‘,2),(‘egon‘,1),(‘tank‘,1),(‘alex‘,3);
update dep set id=200 where id=2; # 正常
delete from dep where id=1;
2.多对多关系
以 书本表 与 作者表 为例
站在书本表:
1本书能否对应多个作者(1条书本数据 能否对应多个作者数据)
能!!!
(不能直接得出结论 一定发要2张表都考虑完全)
站在作者表:
1本作者能否对应多本书(1条作者数据 能否对应多个书本数据)
能!!!
得出结论:
书本表 与 作者表 是双向的 一对多
所以 表关系 就是 多对多
SQL语句建表
# 建立图书表
create table book(
id int primary key auto_increment,
title varchar(32).
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 varchar(32).
age int,
book_id int,
foreign key(book_id) references book(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
按照上述的方式创建 一个都别想成功!!!
其实我们只是想记录 书籍和作者的关系
!针对多对多字段表关系 不能在2张原有的表中 创建外键
需要你再单独开设一张表 专门用来建立2张表之间的关系
# 建立图书表
create table book(
id int primary key auto_increment,
title varchar(32),
price int
);
# 建立作者表
create table author(
id int primary key auto_increment,
name varchar(32),
age int
);
# 建立关联表
create table book2author(
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.一对一关系
id name age addr phone hobby email
如果一个表的字段特别多 每次查询又不是所有的字段都能用到
将表一分为二:
用户表信息:
用户表:
id name age
详情表:
id addr phone hobby email
站在用户表:
一个用户能否对应多个用户详情 不能!!!
站在详情表:
一个详情能否属于多个用户 不能!!!
结论:单向的一对多 都不成立 那么这个时候两者之间的表关系
就是 一对一
或者 没有关系(好判断)
! 一对一 外键字段 建在任意一方都可以 推荐你建在查询评论比较高的表中
# 创建用户详情表
create table authordetail(
id int primary key auto_increment,
phone int,
address varchar(64)
);
# 创建用户表
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
address varchar(64),
authordetail_id int,
foreign key(authordetail_id) references authordetail(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
总结:
# 表关系的建立 需要用到 foreign key
一对多
外键字段 建立在多的一方
多对多
自己开设第三张表 存储
一对一
建立在任意一方都可以 推荐建立在查询评论高的表中
# 判断表之间关系的方式:(换位思考!!!)
员工 与 部门
图书 与 作者
作者 与 作者详情
三:修改表的完整语法大全(了解)
# MySQL是不区分大小写的
1.修改表名
alter table 表名 rename 新表名
2.增加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first; # 添加在最前面
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名; # 添加在字段后面
3.删除字段
alter table 表名 drop 字段名;
4.修改字段
alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
alter table 表名 chage 旧字段名 新字段名;
四:复制表(了解)
我们sql语句查询的结果 其实也是一张虚拟表
create table 新表名 select * from 旧表名; # 不能复制主键、外键、索引 , 只能复制表结构和数据
create table 新表明 select * from 旧表名 wgere id>3;
五:作业布置(如何判断表关系 及 如何建立表关系)
Python正课97 —— 数据库 进阶2