Python正课97 —— 数据库 进阶2

本文内容皆为作者原创，如需转载，请注明出处：https://www.cnblogs.com/xuexianqi/p/12830084.html

一：约束条件

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.在创建表的时候 一定要先创建被关联表
	2.在插入数据的时候 也要先插入被关联表
	3.操作数据的时候 会出现多种限制：同步更新 同步删除

表关系：

表与表之间的关系最多只有四种：
	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 旧表名 where id>3;