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.一对多关系
		在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

上一篇:使用docker容器运行mongodb


下一篇:mongodb添加账号密码