数据库知识总结

1.数据库

---

1.分类

---

关系型数据库：

通过表和二维表来进行数据存储，

常见：mysql|Orcal

优点：结构简单、可以对事务进行处理、可以使用SQL语句对数据进行操作

非关系型数据库：

通过特殊的文档来进行数据存储,

常见：redis|mongoDB

优点：查询速度快\

2.MYSQL 和 Orcale

---

- 相同:

都遵循Sql98的标准，也就是 ddl、dml、dql语句都一样

- 不同

主键生成策略：

MySQL：一般使用自动增长类型;

Orcale没有自动增长

Oracle增加了表空间的概念

MySQL:一个用户可以访问一个datespace里面的不同数据库

Oracle：一个datespace里面有多个表空间tablespace，一个tablespacey有多个用户

Oracle是关系型数据库

1.数据类型的差异：

1）日期：

MySQL中Date只表示年月日，Time只表示时分秒，Datetime表示年月日时分秒Oracle中Date表示年月日时分秒和MySQL中Datetime一样

2）自增列：

MySQL通过在表上设置自增键实现自增，Oracle是通过序列实现自增建一个序列初始值最大值等

2.数据库的操作：

1）参数查询：Oracle：show parameter 参数

MySQL：show variables like ‘%参数%’

2）数据库：MySQL可以创建数据库，Oracle只是创建实例，用户在实例里面，或者容器数据库

3.表操作

4.权限差异：

1）创建用户：Oracle在创建用户的时候就需要确定权限；

MySQL需要对权限单独赋予，也可以全部赋予。grant

5.函数差异

字符串函数：

- Oracle可以指定插入位置，从2开始插入。MySQL从默认位置插入 -- insert('aaa','bb',2)----insert('aaa','bb')

- Oracle截取字符串:substr('aaa',2,1)MySQL截取字符串:subtring('aaa',2,1)

2.关系型数据库-MYSQL

---

1.什么是MYSQL

---

- 他是关系型数据库

- 企业级开发中使用

- 开源免费，方便扩展

- 默认端口号：3306

- 存储引擎是MyISAM InnoDB

​

InnoDB采用B+树不使用B树的原因？

考虑的是IO对性能的影响。B树的每个节点都存储数据，B +树 只有叶子节点存储数据，所以在查找相同的数据的情况下，B树高度更高，IO更加频繁

2.约束

---

- 非空约束：not null

- 唯一约束: unique

- 主键约束：primary key

- 外键约束：constraint foreign key references

3.函数

---

- 数值函数：

ceil 向上取整、floor 向下取整、random

MAX()、MIN()、AVG()、SUM()、Count(*)

- 字符串函数：

字符长度：length('字符串');

将所有字符改成小写：LOWER(字符串);

将所有字符改成大写：UPPER(字符串);

将字符串反转：REVERSE(字符串);

字符拼接：concat(字符串1,字符串2,,,);

截取字符串subStr(字符串，开始索引，长度)

​

FIND_IN_SET(s1,s2)返回在字符串s2中与s1匹配的字符串的位置

EG:select find_in_set("c","a,b,c,d") 3

​

​

​

- 时间函数：

CURDATE() - 2022-10-08

CURTIME() - 18:07:30

NOW() - 2022-10-08 18:07:30

日期的计算

DATE_ADD(NOW(),INTERVAL +-1 year/quarter/month/week/day/hour) +-1年/季度/月/周/日/小时

- Last_Insert_Id()获取自增主键

要求执行insert语句的connection 必须和 执行last_insert_id()语句的connection 是同一个

4.DDL DML DQL

---

DDL 定义数据库: alter table add/drop/change/modify/rename

DML 管理数据库:insert into /delete from /update

DQL 查询数据库:select

2.1 DDL

data defination language 数据库定义语言

增(create)删(drop)改(alter)查(show)

altertable 表名 add 列名 数据类型

altertable 表名 modify 列名 数据名称

altertable 表名 change 旧字段 新字段 数据类型

查： showtables

showdatabase

desc 表 查看数据库的表结构

2.2 DML

data Manage Language 管理数据的语句

增(insertinto)删(delete)改(update)查(select)

/*新增单列insert into 表名 values()*/

/*新增多列insert into 表名 (查询结果集) */

/*删除单列delete from 表名 where 条件*/

/*修改单列 update 表名 set 列名 = 修改后的值 where 条件*/

/* 修改多列 update 表名 set 列名1=修改后的值1,列名1=修改后的值1,...列名n=修改后的值n where 条件*/

​

2.3 DQL

dataquerylanguage 数据库查询语句

select 列名 from 表名 where 条件 groupby ***orderby

1.去重：distinct

2.模糊查询

3.in 字句 notin

4.为空 isnull | isnotnull

5.groupby 分组查

select * fromuser a groupby a.name

6.orderby 排序

select * from goods a orderby a.name

2.4 DCL

data control language 数据库控制语句

控制数据库、表、字段、用户的访问权限和安全级别。

grant赋予权限 revoke

createuser'lixi'@'%' identified by '123456'; -- 创建一个新用户+密码

grantallprivilegeson goods.* to'lixi'@'%'; -- 赋予该用户googs数据库的所有权限

showgrantsfor'lixi'@'%'; -- 查看该用户的所有权限

revokedeleteon goods.* from'lixi'@'%'; -- 收回该用户的删除权限

5.单个插入|多个插入怎么实现

---

- insert into 表名 (,,) values (,,)

- 多个插入：可以将select语句作为values的语句 省略values;或者是采用<foreach>标签进行循环插入

6.区别

---

#truncate 和 delete的区别

deletefrom 表名 where 条件

truncatetable 表名

（1）delete删除语句执行的时候是一行一行执行删除，truncate先将整个表删除 drop 再建表 （2）delete当删除量很大时，删除速度慢，truncate删除快

（3）delete删除需要经过事务的处理，truncate不需要 delete有事务回滚，truncate没有

（4）delete后面可以跟条件进行删除，truncate不可以

# where 和 having 的区别

(1)执行顺序上：先执行where条件过滤在进行分组；

先分组后执行having 基本上时对聚合函数的判断处理

(2)位置：having后面可以跟聚合函数（一般都是），where不可以

7.视图

---

# 视图和表的区别

1）视图没有真实的物理记录或者物理结构，但是表有真实的物理结构

2）视图是已经编译好的sql语句，而表不是

3）表只占用物理空间，而视图只是逻辑概念的存在，因此如果我们要修改视图中的数据，只能修改所查询的表的数据，视图数据无法直接修改

4）视图的修改只会影响视图本身，不会影响基本表

5）从安全角度出发 视图是查看表数据的一种方式，不需要关心表字段具体由哪些来组成

​

操作视图

1）创建视图：

createview 视图名 as(select 表1 unionselect 表2)

createorreplace 视图名 as(select 表1 unionselect 表2)

2）查询视图：select * from 视图名

3）删除视图：drop view 视图名称

8.触发器

---

事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行。

9.索引

---

1.分类？

按存储位置：

聚集：表中的数据按照索引中的顺序存储，聚集索引只能有一个

非聚集:表中的数据不按照索引中的顺序存储，非聚集索引可以有多个；

按应用位置：

唯一索引

主键索引 - 聚集

联合索引

使用：alter table t add INDEX aa(id,name);

联合索引是按照最左前缀原则来进行的：从最左边开始选，左边的选完再往右选；在模糊查询的适合也用到了最左前缀

​

2.什么时候需要用到索引？索引的添加原则：

1）主键自动建立唯一索引

2）频繁作为查询条件的字段应该创建索引;

3）查询中与其他表有关联的字段，例如外键关系；

4）查询中经常用来排序的字段

5）频繁更新的字段不适合创建索引；

6）索引列不允许计算、不允许使用函数；

7）索引不能加在有null值的；

8）不允许使用组合排序

​

3.索引的优缺点

- 优点：索引在查询操作的时候，会让查询速度增快

- 缺点：索引占有一定的物理内存，在进行修改、新增、删除操作的时候速度会慢

​

4.索引失效

1）如果条件中有or，索引失效

2）对于复合索引，如果不使用前列，后续列也将无法使用，类电话簿。【复合索引没有使用左列字段】

3）like查询是以%开头，索引失效

4）存在索引列的数据类型隐形转换，索引失效。

比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5）where 子句里对索引列上有数学运算，索引失效

6）where 子句中有对索引列使用函数，索引失效

7）如果全表扫描比索引扫描快，索引失效

10.索引优化

---

表查询是怎么优化的？

explain + 语句 可以查看SQL语句的执行策略

看type ref主键扫描>index索引扫描>all全表扫描

看rows 该列表示MySQL估算要找到我们所需的记录，需要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值

看filtered 这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

看 extra:

Using where : 表示使用了where条件过滤

Using temporary: 表示是否使用了临时表,性能特别差，需要重点优化。一般多见于group by语句，或者union语句。

【全表扫描改成索引扫描；缓存；分库分表；读写分离

扫描时满足最左前缀原则；

不能在添加索引的列上进行计算以及类型转换】

​

- 索引扫描:添加索引的条件

- 缓存:执行查询语句的时候，会先查询缓存

- 读写分离:经典数据库拆分方案，主库负责写，从库负责读

- 如果有in字句改成join字句

数据量大的时候建议使用join查询，因为使用in的时候循环的次数远远大于join，而在有order by的情况下，建议使用exists或者in子查询代替。

11.数据库连接池

---

创建连接后，将其放置在池中，并再 次使⽤它，因此不必建⽴新的连接。连接池还减少了⽤户建⽴与连接数据库的时间 。

12.多表查询

---

1）隐式内连接：

select * from goods a,goods_detail b where a.detail_id=b.id

2）显示内连接：

select * from goods a inner join goods_detail b on a.detail_id = b.id

3）左外连接：

select * from goods a left join goods_detail b on a.detail_id=b.id

4）右外连接：

select * from goods_detail b right join goods a on b.id = a.detail_id

5）子查询

- 子查询（内查询）在主查询之前一次执行完成。

- 子查询的结果被主查询（外查询）使用 。

- 将子查询放在比较条件的右侧

1.隐式内连接 和 显示内连接

隐式内连接 语法更简单，显示内连接 效率更高，两个在查询结果上来说是相同的

2.左外连接 和 右外连接

（1）左外连接：是A与B的交集，然后连接A的所有数据。

（2）右外连接：是A与B的交集，然后连接B的所有数据。

3.in join exists

在查询的两个表大小相当的情况下，3种查询方式的执行时间通常是：EXISTS <= IN <= JOIN

如果两个表中一个较小，一个较大，则子查询表大的用exists，子查询表小的用in

数据量大的时候建议使用join查询，因为使用in的时候循环的次数远远大于join，而在有order by的情况下，建议使用exists或者in子查询代替。

Join在 慢SQL的解决中用到

UNION 和 UNIONALL

相同点：都是对结果集进行并集操作

UNION：不包括重复行，会按照默认规则进行排序

UNION ALL：将所有的结果全部显示出来，不管是不是重复。不进行排序

13.mysql常用的数据类型

---

int:整型

bigint：较大的整型

char：字符型

varchar：字符串型

text：文本类型

BLOB：二进制大对象，是一个可以存储二进制文件的容器，比如一张照片、一个声音文件。

int 和 bigint

int:22亿左右，存储占4字节；

bigint:44亿左右，存储占8字节

在我们设计mysql数据库表时，单表规模一般保持在千万级，不会达到22亿上限。如果要加大预留量，可以把主键改为改为bigint，上限为42亿，这个预留量已经是非常的充足了。

使用bigint，会占用更大的磁盘和内存空间，由于内存空间有限，无效的占用会导致更多的数据换入换出，增加了IO的压力，不利于数据库的性能。所以如果我们使用自增主键bigint类型，可以用int unsigned类型代替，从而优化数据库的性能。

char 和 varchar 和 text

char:定长 浪费空间 效率高

varchar:不定长 节省空间 效率低

text:不设定长度 不知道存储的最长字长的时候用

char浪费空间：初始预计字符串再加上一个记录字符串长度的字节

varchar：实际字符串再加上一个记录字符串长度的字节

text:跟varchar的区别是text需要2个字节空间记录字段的总字节

char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。

varchar可变长度，可以设置最大长度；适合用在长度可变的属性。“居住地址”适合用varchar

text不设置长度， 当不知道属性的最大长度时，“个人简介”适合用text。

char能存储中文：

Java默认UnionCode编码，两个字节，unicode编码字符集中包含了汉字，一个中文字符也是两个字节

14.数据库关系

---

1:1:在任意一方添加外键，指向另一个的主键

1:n:在多的一方建立外键指向一的一方

n:n:创建中间表

15.表级锁和行级锁触发条件

---

- 行级锁：对操作的改行上锁

- 表级锁：操作行 也会将整个表锁定

16.分页

---

limit 10 检索前10行数据

limit 5,10 从第5行开始，检索10行数据 检索6-15行数据

3.非关系型数据库-Redis

---

1.简单介绍

---

- redis属于非关系型数据库

- redis是一个高性能的 key-value 数据库，

- 是使用C语言开发的数据库，

- 它和传统数据库不一样的是redis的数据存在内存中，也就是说，它是内存数据 库，所以读写速度很快，

- 因此redis经常用于缓存。

​

redis的优点：

1）redis的数据存在内存上：查询比较快

2）redis的数据可以存在硬盘上，相较于memcached优秀的地方

3）redis采用单线程，没有多线程的上下文切换等问题

4）IO多路复用，根据一个套接字连接不同的事件，根据不同的 事件，找到对象的事件处理器

2.原理

---

- 用户发送请求后，会先查询缓存中是否有对应的数据，如果有就直接返回。

- 如果没有，再去数据库中查询对应数据，如果没有要查的数据，就返回空数据。

- 如果有，就更新缓存，然后返回对应的数据。

3.为什么要用Redis

---

- 高性能:

如果用户第一次访问数据库中的某些数据，过程会很慢。但是用户访问的数据如果是高频数据而且不经常改变的 话，我们就可以把它存到缓存中。这样可以保证用户下一次再访问这些数据的时候，访问速度更快

- 高并发:

直接操作缓存可以承受的数据库请求数量远大于直接访问数据库的，所以我们可以把数据库中的部分数据转移 到缓存中，这样一部分的请求会直接到缓存，不用经过数据库，我们也就提高了系统整体的并发能力。

4.redis常见数据结构

---

String:简单的key-value类型,一般用在需要计数的场景，【用户的访问次数、热点文章的点赞转发数量】。

set:无序不可重复的元素 【共同好友】

list:有序可重复

hash:类似于hashMap.【用户信息、商品信息】

sorted_set:添加了权重参数score，是集合中的元素能按照 score有序排列【分数、朋友圈点赞】

5.分布式缓存

---

redis和Memcached的区别和共同点

相同点:

- 都是内存数据库,一般用来做缓存

- 都有过期策略

- 性能都高

不同点:

- redis支持多种数据类型 memcached支持k/v数据类型

- redis支持数据的持久化，可以把内存中的数据保存到磁盘，重启后，可以再加载使用，这就是灾难恢复机制,同时,在服务器内存使用完后，可以把不用的数据放到磁盘。而Memcached把数 据全部保存到内存中。在服务器内存使用完后，会直 接报异常。

- Memcached是多线程，非阻塞I/O复用网络模型；Redis是单线程的多路I/O复用模型。

- Memcached过期数据的删除策略，只有惰性删除；而Redis同时使用了惰性删除和定期删除。

Redis是单线程的多路I/O复用模型:

Redis 的⽂件事件处理器使⽤ I/O 多路复⽤（multiplexing）程序来同时监听多个套接字，并根据 套接字⽬前执行的任务来为套接字关联不同的事件处理器.

单线程编程容易并且更容易维护；

多线程就会存在死锁、线程上下⽂切换等问题，甚⾄会影响性能。

Redis 的性能瓶颈不再 CPU ，主要在内存和⽹络；

Redis6.0 引⼊多线程主要是为了提⾼⽹络 IO 读写性能,Redis6.0 的多线程默认是禁⽤的，只使⽤主线程。如需开启需要修改 redis 配置文件 io-threads-do-reads:yes.开启多线程后，还需要设置线程数，否则是不⽣效的:io-threads 4

删除策略:

1）惰性删除 ：只会在取出key的时候才对数据进行过期检查。

2）定期删除 ： 每隔⼀段时间抽取⼀批 key 执行删除过期key操作。

​

定期删除和惰性删除漏掉 了很多过期 key 的情况 。

解决办法:Redis内存淘汰机制

从已设置过期时间的数据集

淘汰最近最少使用的;

淘汰最不经常使用的;

淘汰即将过期的;

任意选择进行淘汰

当内存不足时:

淘汰最近最少使用的;

淘汰最不经常使用的;

任意选择进行淘汰

内存不⾜以容纳新写⼊数据时，新写⼊操作会报 错

6.过期时间

---

1.Redis 给缓存数据设置过期时间有啥⽤？

因为内存是有限的，如果缓存中的所有数据都是⼀直保存的话，分分钟直接Out of memory内存溢出

很多时候，我们的业务场景就是需要某个数据只在某⼀时间段内存在

2.Redis是如何判断数据是否过期的呢

Redis 通过⼀个叫做过期字典（可以看作是hash表）来保存数据过期的时间.

过期字典的键指向 Redis数据库中的某个key(键)，过期字典的值是⼀个long long类型的整数，这个整数保存了key所 指向的数据库键的过期时间

7.持久化的实现

---

将数据持久化到磁盘上

快照：redis默认开启的，用于获取某个时间点的数据

只追加文件：AOF持久化的实时性更好

always 每次有数据修改发⽣时都会写⼊AOF⽂件中

every 每秒钟同步一次

no 让操作系统决定何时进行同步

8.缓存中常出现的问题以及解决方案

---

缓存穿透：

高并发情况下，由于缓存和数据库中都没有需要的数据

解决办法：

客户端授权

缓存中添加空的数据

布隆过滤器

缓存击穿：

大量并发访问同一个数据，缓存中没有，但是数据库中有（可能失效了）

解决方案

热点数据永不失效（不推荐）

互斥锁

缓存雪崩

大量并发访问不同的数据，缓存中没有数据，但数据库中有。

解决方案

- 针对redis服务不可用：将热点数据均匀分布在不同的缓存数据库中，设置热点数据永不过期；限流，避免同时处理大量请求。

- 针对缓存失效的情况：设置不同的失效时间，比如随机设置缓存失效时间。

9.缓存降级

---

缓存降级是指缓存失效或缓存服务器挂掉的情况下，不去访问数据库，直接返回默认数据或访问服务的内存数据。降级一般是有损的操作，所以尽量减少降级对于业务的影响程度。

10.Redis集群

---

主从模式：

- 主表负责读写，新数据会将自动同步给从表

- 从表主要负责读

- 一个主表多个从表

- 从表挂了，不影响读写；主表挂了，redis将不提供写服务，不会新推选主表，知道主表恢复了，才提供写服务。

​

哨兵模式：

Cluster分片模式：

11.Redis的常用命令

---

set[key][value] ，存储键值对

get[key] ，获取键对应的值

strlen，获取值长度

keys*，查询所有键

type[key] ，查询键的类型

lpush[list][vlaue] ，list存元素

ttl，查询失效时间

expire[key]30 ，设置过期时间

persist[key] ，设置key永不过期

12.有个list存储在redis中，如何进行内存分页

---

LRANGE key start stop