三十一、数据库相关2

xuxiaoxu1

于 2022-08-16 18:39:40 发布

阅读量282

点赞数

文章标签：数据库 sql java

本文链接：https://blog.csdn.net/weixin_68531269/article/details/126366280

版权

一主键

1.1 primary key

主键，但从约束条件上来看，等同于非空且唯一 not null unique
InnoDB存储引擎规定一张表有且只能有一个主键
如果创建的表中没有主键也没有一个非空且唯一的字段，存储引擎就会采用一个隐藏的字段作为主键，如果创建的表中没有主键但有非空且唯一的字段，那么存储引擎就会自动将该字段设置成主键。
创建表的时候都应该有一个id字段，并且该字段应该作为主键值。

create table t1(
	id int primary key,
	name varchar(32)
);

create table t2(
	pid int,
	uid int,
	primary key(pid,uid)
);  # 联合主键

1.2 auto_increment

该约束条件不能单独使用，必须跟在键后面(主要配合主键一起使用)

# 创建表
create table t3(
	id int primary key auto_increment,
	name varchar(32) not null unique,
	password varchar(32)
);

# 插入数据
insert into t3(name,password) values('jasper','123'),('lili','321');

# 删除数据
delete from t3 where id=2;

# 重新插入一条数据
insert into t3(name,password) values('lili','123');
select * from t3;

自增的操作不会因为执行删除操作而回退或者重置，如果想要重置需要使用关键字truncate 表名

truncate t3;  # 删除数据并重置主键值

二外键(foreign)

2.1 外键简介

创建一张员工表

create table t4(
	id int primary key auto_increment,
	name varchar(32) not null unique,
	gender enum('male','female') default 'male',
	dep_name varchar(32),
	dep_describe varchar(32)
);

# 插入数据
insert into t4(name,gender,dep_name,dep_describe) values('jasper','male','讲师部','教书育人'),
('lili','female','财务部','发工资');

上表的缺陷:

表结构不清晰，又是员工表又是部门表
字段数据反复存取，浪费存储空间
表的扩展性极差

优化操作：将表拆开，添加外键。

表数据关系判定:换位思考
针对员工表和部门表
先站在员工表的角度：

问：一条员工表中的数据能否对应多条部门表数据
答：否

在站在部门表中的角度

问：一条部门表的数据能否对应多条员工表的数据
答：是

得出结论：员工表和部门表属于一对多关系

如何创建一对多的关系表?

先写普通字段
最后再写外键字段

2.2 表关系之一对多

创建一对多关系表


create table staff(
	id int primary key auto_increment,
	name varchar(32) not null unique,
	gender enum('male','female') default 'male',
	department_id int,
	foreign key(department_id) references department(id)
);

create table department(
	id int primary key auto_increment,
	dep_name varchar(32),
	dep_describe varchar(32)	
);

级联更新、级联删除(被关联的数据一旦变动，关联的数据跟着变动)

create table staff(
	id int primary key auto_increment,
	name varchar(32) not null unique,
	gender enum('male','female') default 'male',
	department_id int,
	foreign key(department_id) references department(id)
	on update cascade 
	on delete cascade
);

create table department(
	id int primary key auto_increment,
	dep_name varchar(32),
	dep_describe varchar(32)	
);

插入数据(先插入被关联的表)

insert into department(dep_name,dep_describe) values('讲师部','教书育人'),
('财务部','发工资'),('安保部','保卫安全');

insert into staff(name,department_id) values('jasper',1),('lili',2),('jack',3),('jarry',1);

2.3 表关系之多对多

作者表和书籍表
先站在作者表的角度：

问：一条作者表中的数据能否对应多条书籍表数据
答：是

在站在书籍表中的角度

问：一条书籍表的数据能否对应多条作者表的数据
答：是

得出结论：作者表与书籍表是多对多关系

创建表

create table author(
	id int primary key auto_increment,
	name varchar(32)
);

create table book(
	id int primary key auto_increment,
	name varchar(32),
	price float(10,2)
);

create table book_author(
	id int primary key auto_increment,
	book_id int,
	foreign key(book_id) references book(id)
	on update cascade 
	on delete cascade,
	author_id int,
	foreign key(author_id) references author(id)
	on update cascade 
	on delete cascade
);

插入数据

insert into book(name, price) values('水浒传',49.9),('西游记',59.9),('三国演义',39.9),('红楼梦',29.9),('葵花宝典',0);

insert into author(name) values('吴承恩'),('曹雪芹'),('罗贯中'),('施耐庵'),('张三'),('李四');

insert into book_author(book_id,author_id) values(1,4),(2,1),(3,3),(4,2),(5,5),(5,6);

2.4 表关系之一对一

用户表和用户详情表
先站在用户表的角度：

问：一条用户表中的数据能否对应用户详情表数据
答：否

在站在用户详情表的角度

问：一条用户详情表的数据能否对应多条用户表的数据
答：否

用户表和用户详情表之间存在关系
得出结论：用户表和用户详情表是一对一关系

创建表

create table user(
	id int primary key auto_increment,
	name varchar(32),
	password varchar(32),
	info_id int unique,
	foreign key(info_id) references info(id) 
	on update cascade 
	on delete cascade
);

create table info(
	id int primary key auto_increment,
	address varchar(32),
	phone_num varchar(32)
);

插入数据

insert into info(address,phone_num) values('上海','110'),('北京','120'),('深圳','130');

insert into user(name,password,info_id) values('jasper','123',1),('lili','321',2),('jack','666',3);

练习

判断下列表数据关系并自定义创建出表
有些表数据关系不是确定根据具体业务可能有变化

服务器表与应用程序表
课程表与班级表
学生表与班级表
老师表与课程表
书籍表与出版社表

# 1 一对多关系
create table server(
	id int primary key auto_increment,
	ip varchar(32),
	port int
);

create table client(
	id int primary key auto_increment,
	ip varchar(32),
	port int,
	unique(ip,port),
	server_id int,
	foreign key(server_id) references server(id) 
	on update cascade 
	on delete cascade
);

# 2 多对多关系
create table class(
	id int primary key auto_increment,
	name varchar(32)
);

create table course(
	id int primary key auto_increment,
	name varchar(32)
);

create table class_course(
	id int primary key auto_increment,
	class_id int,
	foreign key(class_id) references class(id) 
	on update cascade 
	on delete cascade,
	course_id int,
	foreign key(course_id) references course(id) 
	on update cascade 
	on delete cascade
);

# 3 一对多关系
create table student(
	id int primary key auto_increment,
	name varchar(32),
	age int,
	gender enum('male','female'),
	class_id int,
	foreign key(class_id) references class1(id)
);

create table class1(
	id int primary key auto_increment,
	name varchar(32)
);

# 4 多对多关系
create table teacher(
	id int primary key auto_increment,
	name varchar(32),
	gender enum('male','female') default 'male'
);

create table course1(
	id int primary key auto_increment,
	name varchar(32)
);

create table teacher_course(
	id int primary key auto_increment,
	teacher_id int,
	foreign key(teacher_id) references teacher(id) 
	on update cascade 
	on delete cascade,
	course_id int,
	foreign key(course_id) references course1(id) 
	on update cascade 
	on delete cascade
);

# 5 一对多关系
create table publishing(
	id int primary key auto_increment,
	name varchar(32),
	book_id int,
	foreign key(book_id) references book1(id) 
	on update cascade 
	on delete cascade
);

create table book1(
	id int primary key auto_increment,
	name varchar(32),
	price float(10,2)
);