DB设计
指导db设计的思想,避免表中出现大量的冗余的数据.但是实际开发中表中是允许出现冗余的字段的.
冗余数据的好处 - 安全,方便查询.坏处->delete和update操作
1NF - 第一范式-保证列的原子性,保证列不可再切割
s_emp(id,name) - 发现name不具备原子性,可以被再次切割(fist_name,last_name)
s_ord(id,address) - address显示为省市区 - 江苏省苏州市园区
address再次分成(province,city,area)
2
3
4
5
6
7
8
select * from s_ord where address like '%苏州%';
select * from s_ord where city = '苏州';
-- address,city加索引.
-- 模糊查询可能导致索引失效.但是精确匹配是走索引的.2NF - 基于1NF,要求非关键列要全部依赖于关键列. 不存在非关键列部分依赖于关键列
关键列 - 主键列 - 非空且唯一 - table中的id设置成主键列.
student(sid,sname,cid,cname);//关键列(sid,cid) - 确定唯一的元组(行) - 复合主键
sname,cname - 非关键列.
(sid,cid) -> 唯一确定 -> sname/cname
sid -> sname,cid -> cname
2
3
course(cid,cname);
sc(sid,cid,kpi);//(sid,cid) -> kpi3NF- 基于2NF,要求非关键列不能传递依赖于关键列.
student(sid,sname,uid,uv_name,u_phone);
u_phone传递依赖于sid -
原因sid -> uid -> u_phone
2
university(uid,u_name,u_phone);
练习
- 用户表t_user
- 视频表t_video
- 评论表和回复表设计
用户对视频进行评论(t_comment)和回复(t_repy)的场景:
tom 发布了 搞笑的视频
admin 评论: 😁😎
li评论admin: 你笑什么!
lei回复@li: 管你什么事情!
x回复@li: 你想表达什么!
y回复@x: ….
k 评论admin:你笑什么!
jack 评论: 😄
t_user
| id | usernmae | |
|---|---|---|
| 1 | tom | |
| 2 | admin | |
| 3 | li | |
| 4 | lei | |
| 5 | x | |
| 6 | y | |
| 7 | k |
t_video
一个用户可以发布多个视频
| id | video | user_id |
|---|---|---|
| 1 | 佩洛西 | 1 |
t_comment
| id | video_id | content | comment_id | user_id |
|---|---|---|---|---|
| 1 | 1 | 😁😎 | 2 | |
| 2 | 1 | 你笑什么! | 1 | 3 |
| 3 | 1 | 你笑什么! | 1 | 7 |
t_reply
| id | comment_id | conent | reply_id | user_id |
|---|---|---|---|---|
| 1 | 2 | 管你什么事情! | 4 | |
| 2 | 2 | 你想表达什么! | 5 | |
| 3 | 2 | … | 2 | 6 |
DDL语言
Data Definition Language - 数据定义语言.
包括的命令:create drop alter comment rename truncate
数据类型
数字类型
- 整数 - int/bigint
- 小数 - float/double/decimal
要点
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
create table t_type(
id int(7)
);
此处的7并不是代表此列的最大长度.误区:最大可保存的值是9999999
实际上该列可以存储的最大值是由类型决定了.
配置zerofill 0填充
create table t_type(
id int(7) zerofill
);
insert into t_type values(1);
不足7位,会采用0来填充
+---------+
| id |
+---------+
| 0000001 |
+---------+
drop table t_type;
create table t_type(
c1 float(5,2),
c2 double(5,2),
c3 decimal(5,2)
);
float(m,n) => m总长度,n代表的小数点
insert into t_type(c1) values(12.345);
mysql> select * from t_type;
-- 小数保留2位,并且四舍五入
+-------+------+------+
| c1 | c2 | c3 |
+-------+------+------+
| 12.35 | NULL | NULL |
+-------+------+------+
mysql> insert into t_type(c2) values(123.5); //ok
mysql> select * from t_type;
+-------+--------+------+
| c1 | c2 | c3 |
+-------+--------+------+
| 12.35 | NULL | NULL |
| NULL | 123.50 | NULL |
+-------+--------+------+
mysql> insert into t_type(c2) values(1234.5);
`原因:1234.5 => 1234.50 已经超过5位了`
ERROR 1264 (22003): Out of range value for column 'c2' at row 1
float/double => 非标准 decimal标准 - 对精度有特别要求的
insert into t_type(c3) values(123.589);关于日期类型
date
datetime
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create table t_type(
id int,
create_date date default now()
);
ERROR 1067 (42000): Invalid default value for 'create_date'
如果某个列期望默认值是now(),一定要设置成datetime
drop table t_type;
create table t_type(
id int,
create_date datetime default now()
);
insert into t_type(id) values(1);
-- 满足日期能够支持的字符串格式
drop table t_type;
create table t_type(
id int,
create_date date default '2012-09-08 12:12:12'
);
insert into t_type(id) values(1);字符串类型
char和varchar区别
char(n),varchar(n) => 代表的字符的个数
区别: char是定长,varchar是可变长.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create table t_type(
s1 char(2),
s2 varchar(2)
);
insert into t_type(s1) values('ab');
insert into t_type(s2) values('abc');
char(2) => 实际存储1个'a',实际消耗了2个
varchar(2) => 假设'a',实际仅仅消耗了1个,最大消耗2个.
varchar更加节约空间. char效率会更高一点 - 不推荐用char
-- oracle中数据中
drop table t_type;
create table t_type(
s1 char(2)
);
insert into t_type(s1) values('a');
-- oracle查询为empty
-- mysql中空直接比较的时候剔除,大小写比较都是一样的.
select * from t_type where s1='a';text - 0-65 535 bytes - 长文本
约束类型
主键约束 primary key 非空且唯一
主键列可以是由多列共同组成 - 符合主键列
但是表中只能出现一个主键
外键约束 foreign key
外键列允许为null
外键列是多出现在多的一方中.建立表与表之间的关系的
开发中不需要设置外键 - 造成表与表之间的耦合.
表和表之间的关系是在”心中”!
非空约束 - not null
唯一约束 - unique
default - 默认值
mysql中不支持自检约束,oracle中支持的check检查约束.但是mysql中可以使用触发器来实现.
创建表语法
1 | DROP table 表名; |
列级添加方式
直接在列的后面的增加关于该列的约束
2
3
4
5
6
7
8
create table t_user(
id int(7) primary key,
username varchar(20) unique not null,
birthday date default '1991-09-08'
);
insert into t_user(id) values(1);
表级添加方式
1 | -- not null仅仅支持列级添加 |
建表的其他语法
利用一张已经存在的表来构建另外一张表
保留原来表中的数据
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table t_user(
id int(7),
username varchar(20) not null,
birthday date,
constraint t_user_id_pk primary key(id),
constraint t_user_username_uq unique(username)
);
insert into t_user values(1,'tom','2021-09-08');
insert into t_user values(2,'jack','2022-09-08');
-- 克隆
CREATE TABLE 表名 AS SELECT查询语句;
create table t_user_copy as select * from t_user;保留原表的结构,但是不保留数据
2
3
create table t_user_copy as select * from t_user where 1=2;
查看建表语句-DDL
如果是一个1:n
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
drop table t_customer;
create table t_customer(
id int(7),
cname varchar(20) not null,
constraint t_customer_id_pk primary key(id)
);
create table t_ord(
id int(7) primary key,
ord_no varchar(20) not null unique,
total double(7,2),
customer_id int(7),
constraint t_ord_customer_id_fk foreign key(customer_id) references t_customer(id)
);
-- 一定是父级表t_customer
-- 删表 - 先删除子表,多的一方,外键所在的那张表.n:n
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table t_student;
drop table t_course;
create table t_student(
id int primary key
);
create table t_course(
id int primary key
);
create table t_sc(
sid int,
cid int,
primary key(sid,cid),
foreign key(sid) references t_student(id),
foreign key(cid) references t_course(id)
);
自学的知识点
四种分区方式 - 测试
DML操作
insert语句
给表中所有的列都插入数据,但是需要注意的是顺序/类型/约束
给指定的列插入数据
批量插入
delete语句
delete from 表名;//删除表中的数据
delete from 表名 where 条件;
外键约束会对delete产生影响,一定是先删除子记录,然后才能够删除父记录.
先删除多的一方,然后才能够删除一的一方.
级联删除 - 删除的一方之前先将这个一方的子记录全部删除
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
drop table t_customer;
create table t_customer(
id int(7),
cname varchar(20) not null,
constraint t_customer_id_pk primary key(id)
);
insert into t_customer values(1,'admin');
insert into t_customer values(2,'tom');
create table t_ord(
id int(7) primary key,
ord_no varchar(20) not null unique,
total double(7,2),
customer_id int(7),
constraint t_ord_customer_id_fk foreign key(customer_id) references t_customer(id) on delete cascade
);
insert into t_ord values(1,'1001',300,1);
insert into t_ord values(2,'1002',300,null);delete from t_customer where id=1;
update语句
- update 表 set 列名=列值,列名2=列值;//更新表中所有行
- update 表 set 列名=列值,列名2=列值 WHERE条件;//
truncate和delete和drop
共同点
1.truncate和不带where子句的delete、以及drop都会删除表内的数据。
2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。
不同点
delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger
速度，一般来说: drop> truncate > delete
分页
- limit m // 前m行
- limit m,n // m从下标0开始(第一行),n显示行数
分页的公式
1 | public Page<Book> page(Integer pageNum,Integer pageSize){ |
分页的查询效率问题
limit m,n 其中的m叫做偏移量. 偏移量越大,分页的性能越低.
比如表1-100000,正好id=1,10000
select * from 表 where id>条件 limit 2;
条件查询(减少偏移量) + limit
事务
用来执行一组SQL,是db中完成业务的基本单位.
事务ACID特性
原子性（Atomicity) - 事务不可再分割.
事务要么同时成功,同时失败 - 比如转账业务.
一致性(Consistency) - 事务一旦提交.内存中的数据要和磁盘上的数据要一致,保证数据的完整性.
比如tom-500,jack-500.无论转多少,转多少次.反正总的钱是1000
隔离性(isolation): 事务与事务之间的彼此隔离的,互不干扰的.一个事务的结束,意味着下一个事务的开始.
持久性(Durability) - 事务一旦提交,数据应该永久保存在磁盘中.即使发生一些故障,应该可以用一些恢复的技术进行恢复.
事务的分类
- 本地事务 - 一个项目(单体架构)连接一个数据库
- 分布式事务
- 消息中间件 - rabbitmq,rocketmq
- 阿里的框架seata
事务的隔离级别
① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
锁表.
② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
③ Read committed (读已提交)：可避免脏读的发生。
一个事务只能读取到另外一个事务已经提交的数据.
④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。产生脏读.
一个事务可以读取到另外一个事务尚未提交的数据
查询当前会话的隔离级别
mysql默认的是可重复读的隔离级别
1 | select @@tx_isolation; |
事务TCL命令
begin(开启一个事务),commit(提交一个事务),savepoint 事务点
set autocommit=0;//手动开启一个事务
DML操作默认都是会自动提交事务.
补充一点
rollback - 回滚事务 - 结束一个事务
不能回滚已经commit之后的事务.
savepoint 事务点
rollback to 事务点
如果么有设置事务点 - 回滚当前事务中所有的操作.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Query OK, 0 rows affected (0.00 sec)
mysql> update t_acc set balance=balance+1000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> savepoint t1;
Query OK, 0 rows affected (0.00 sec)
mysql> update t_acc set balance=balance+1000 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> rollback to t1;
Query OK, 0 rows affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
多事务并发带来的问题
多个事务对同一个数据进行操作的时候,会产生一些问题
2
3
4
5
6
create table t_acc(
id int,
balance double(7,2)
);
insert into t_acc values(1,10000),(2,10000);
脏读
一个事务读到了另外一个事务没有提交的数据 - 绝对不允许的.
不可重复读
set session transaction isolation level read committed;
一个事务A在读取一次之后,另外一个事务B在进行update操作.并且commit.
A事务在没有自己没有提交之前,读取到了B事务提交之后的数据.导致A事务在当前事务中多次读取到的结果不一样.
幻读
它和不可重复读类似.侧重点不同.不可重复读强调的是一个事务在查询.另外一个事务在修改(update)
幻读强调的是一个事务在修改.另外一个事务在插入(insert).
set session transaction isolation level read committed;
事务A在修改,事务B-insert并且提交了.事务A在本事务中再次查询,发现了好像有”更新失败”的数据.就像发生了幻觉一样.
可重复读
默认的隔离级别
set session transaction isolation level repeatable read;
事务A读取一次,事务B执行update操作.事务A在没有结束当前事务之前,多次读取到的结果是一样的.
必须要commit之后,才能够读取到B事务提交之后的修改数据.
总结
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| 读未提交 | √ | √ | √ |
| 读已提交 | × | √ | √ |
| 可重复读 | × | × | √ |
| 串行化 | × | × | × |