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、增加冗余字段