数据库整理

8月16号数据库整理

1、什么是三范式，什么是反三范式

三范式（时间换空间）：

第一范式：列不可再分

第二范式：非主属性列完全依赖于主键，不能部分依赖

第三范式：非主属性列只依赖于主键

反三范式：通过增加冗余或者重复数据，提高数据库的读性能（空间换时间）

2、MySQL的数据类型

整数类型：

tinyint（8位二进制）

smallint（16为二进制）

mediumint（24为二进制）

int(integer)（32为二进制）

小数类型：

float（单精度浮点数）

double（双精度浮点数）

decimal(m,d)（m是一共有多少位数，d是小数有多少位数）

日期类型：

year YYYY 1901~2155

time HH:MM:SS

date YYYY-MM-DD

datetime YYYY-MM-DD HH:MM:SS

3、约束(5种)

约束：

主键约束：一张表中只有一个主键约束，主键不能为空

唯一约束：对字段的取值约束，使该字段内的所有值不能重复，可以为null

外键约束：另一张表通过外键约束与本表进行关联

非空约束：对字段约束，字段值不能为空

检测约束

4、什么是事务，为什么需要事务，四大特征，操作事务的语句（开启，提交，回滚）

事务：事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位。

为什么需要事务：使数据库从一种一致性状态变成另一种一致性状态

事务的四大特征：

原子性：事务是执行的最小单位，不可分割；事务提交后，对数据库的操作要么都成功，要么都不成功。

一致性：事务提交前后， 数据库应该是一致的

隔离性：事务与事务之间不能相互影响

持久性：事务对数据库的改变是持久的

操作事务的语句：

开启：begin

提交：commit

回滚：rollback

5、事务的四种隔离级别

读未提交：读还没有提交的数据。读未提交会有脏读、幻读、不可重复读的危险

读已提交：读已经提交的数据。读已提交会有幻读、不可重复读的危险

可重复读：可以重复读数据，读取数据的结果应该保持一致。可重复读会有幻读的危险

串行化：所有事务逐行执行。串行化解决了脏读、幻读、不可重复读，最安全。

6、你对索引的理解

什么是索引

索引是一种数据结构，目的是为了更快速的进行数据库查询，常用B树以及B+树

优缺点

优点：在查询方面使用索引能更快的查询数据

缺点：创建索引和删除索引需要耗费性能，增删改的效率会降低

索引的种类

主键索引：使用表中主键作为索引。不允许重复、不允许为null，只有一个主键索引

唯一索引：使用任意字段作为索引。不允许重复、允许为null，可以有多个唯一索引

普通索引：使用任意字段作为索引。允许重复、允许为null，可以有多个普通索引

全文索引：搜索引擎的一种技术

索引的数据结构为什么是B+树

B树与B+树的区别，B+树与Hash的区别

B树是节点中存储了键和值，B+树是节点中存储键，值在叶子节点中形成了一条链。

B+树查询时是通过二分查找法查询，Hash查询是通过计算好的哈希码直接找到对应的物理地址；B+树支持范围查询，hash只能等值查询；B+树支持模糊查询，hash不支持模糊查询。

聚簇与非聚簇的区别

聚簇：将数据存储和索引放在一起

非聚簇：将数据存储和索引分开

什么叫回表查询

使用非聚簇索引中的索引进行部分或者全字段查询，查到的具体字段不足的时候，回到使用对应主键作为索引的B+树将字段查询完

最左前缀原则

创建多列索引的时候，将where子句中使用最频繁的字段放到最左边

索引失效的场景

索引失效：如果索引列是可空的，那么索引值是小于总数据值，可能会进行全表查询；

在查询的where子句中没有使用到建立了索引的字段，也会导致索引失效；

使用左模糊查询也会导致索引失效

索引的优化

优化：在where子句中使用like进行模糊查询，只能右模糊；

在where子句中使用联合索引要遵守最左前缀原则；

在where子句中尽量不要使用负向条件查询

不要在索引列上做任何计算操作；

7、数据库锁的种类

按照是否独占式：共享锁（读锁）、排他锁（写锁）

按照锁的粒度：行级锁、页级锁、表级锁

8、什么是死锁，怎么解决

死锁：两个或多个事务对同一个资源相互占用，并请求对方的资源，造成恶性循环

9、什么是乐观锁，悲观锁

乐观锁：假设不会发生冲突，只在提交时检查是否违反数据完整性；

悲观锁：假设会发生冲突，屏蔽一切可能违反数据库完整性的操作

10、内连接与外连接的语法，区别

内连接语法：

等值连接：select u1.id,p1.name from user u1,product p1 where u1.id=p1.id;

不等值连接：select u1.id,p1.name from user u1,product p1 where u1.id>p1.id;

自连接：select * from user u1 inner join user u2 on u1.id=u2.pid;

外连接语法：

左外连接：select * from user u1 left join product p1 on u1.id=p1.id;

右外连接：select * from user u1 right join product p1 on p1.id=u1.id;

内连接和外连接的区别：

内连接是A表与A表或者A表与B表进行连接，只包含符合连接的行；

外连接是A表与A表或者A表与B表进行连接，其中一个表是主表，包含符合连接以及不符合连接的行。

11、左外连接与右外连接的语法，区别

左外连接：select * from user u1 left join product p1 on u1.id=p1.id;

右外连接：select * from user u1 right join product p1 on p1.id=u1.id;

区别：左外连接以左边的表为主，保留所有数据，右边的表根据连接条件保留符合的行；

右外连接以右边的表为主，保留所有数据，左边的表根据连接条件保留符合的行

12、子查询的语法

语法：select 字段名 from 表名 where 查询条件 in（子查询）

13、分组查询的语法

语法：select 字段名 from 表名 group by 以...分组 having 分组条件

14、where与having的区别

where是普通查询语句时使用where后添加查询条件；

having是使用分组后使用having添加查询条件；

15、说出几个常用的数据库函数

日期：curdate() 当前年月日

curtime() 当前时分秒

now() 当前年月日时分秒

unix_timestamp() 当前时间戳

from_unixtime() 时间戳转日期

week() 一年中的第几周

year() 一年中的年

datediff() 日期差值

数学：ABS() 返回返回值

pi() 返回圆周率

sqrt() 平方根函数

mod() 求余函数

ceil()、ceiling() 向上取整

floor() 向下取整

rand() 返回一个随机浮点数(0-1之间)

round() 对值进行四舍五入

truncate(x,y) 截取和保留小数点后面y位

sign(x) 值为负返回-1，值为0返回0，值为正数返回1

pow(x,y) 对x的值进行y次方运算

字符串：concat() 返回多个字符串连接后的字符串

lower() 将字符串转换为小写形式

upper() 将字符串转换为大写形式

length() 返回字符串的长度

substring(str,pos,len) str表示字符串，返回从pos开始的子串，len表示返回的长度

trim([remstr from] str) 返回删除str字符串两端remstr的字符后的子串

replace(str,from_str,to_str) 用于将字符串中所有的from_str替换成to_str

reverse() 将字符串中的字符进行反转

聚合（统计）：count() 求指定列的总行数

max() 求指定列的最大值

min() 求指定列的最小值

sum() 求指定列的和

avg() 求指定列的平均值

16、count(*) count(id) count(1)的区别

count(*)表示求所有列的总数，不会忽略null；

count(id)表示求列名为id的字段的总行数，会忽略null；

count(1)表示求所有记录的行数，不会忽略null

17、创建表的语法

create table 表名 (

字段名 数据类型 （约束可加可不加）

...

)engine=innoDB default charset=utf8mb4 commit='表名';

18、创建索引的语法

创建表的时候创建索引：

在字段名的数据类型后面添加not null、unique、primary key、fulltext、foreign key

建表后添加索引：

alter table 表名 add index 索引名(表中要加索引的字段名)

19、drop、delete以及truncate的区别

drop：删除表、表结构、表数据

delete：删除单行或者多行数据

truncate：删除表中所有的数据

20、增删改查的语法

增：insert into 表名(可以写具体要添加的字段) values(具体的值)；

删：delete from 表名 where 查询条件；

改：update 表名 set 字段名=设值，... where 条件

查：select 字段名 from 表名 where 查询条件

21、数据库备份和还原

冷备份：数据库关闭再并行备份，适合所有数据库。

热备份：在数据库正常读取数据和修改数据时备份，不会中断数据库的操作。当数据库发生异常的时候，可以重新执行SQL语句

温备份：在读取数据库数据时备份，备份期间不允许修改数据。

22、数据库SQL语句优化

优化：1、查询时尽量不要使用count(*)进行查询，要具体到哪个字段；

2、尽量避免子查询；

3、进行like模糊查询的时候只能使用右模糊查询；

4、limit要先定位再扫描显示的行数；

5、使用or的SQL语句都不会走索引，所以可以用union all合并两端的结果集；

6、能用where尽量用where，减少having的使用；

7、用varchar替代char

23、数据库优化

1、将字段很多的表分解成多个表；

2、增加中间表；

3、增加冗余字段