数据库相关-面试高频

数据库1

面试

必学

(59条消息) 有关SQL_大数据coder的博客-CSDN博客

oracle之增删改查

数据定义语言(Data Definition Language, DDL): 用于管理和定义数据对象，包括数据库，数据表等
create: 创建数据库和表
drop: 删除表对象
alter: 修改表结构
数据操作语言(Data Manipulation Language, DML): 用于操作数据库对象中所包含的数据
insert: 添加数据
delete: 删除数据
update: 修改数据
数据查询语言(Data Query Language, DQL): 用于查询数据库对象中所包含的数据
select: 查询数据
事务控制语言(Transaction Control Language, TCL): 包括提交事务、事务回滚
commit: 提交事务
rollback: 事务回滚
savepoint: 事务节点
数据控制语言(Data Control Language, DCL): 用来管理数据库，包括管理权限以及数据更改
grant: 授权
revoke: 收回权限

• 增：

新建：必须要有主键.新增t2表，主键字段id类型为number2字节大小不能为空，name字段为varchar2类型

create table t2(
    id number(2) not null,  
    name varchar2(2) not null
);

添加：t2表添加数据id为1，name为A

insert into t2(id,name) values(1,'A');

• 删：

delete from tt2;#清空内容
drop table t2; #删除表

• 查：select

select table_name from user_tables;

• 改：

update t2 set id=2 where id=1;#修改表数据 修改字段id列的值 需要Commit和Rollback，否则提交的结构无效
Alter table test Add(id,int).#修改表结构，向表中增加表的字段 不需要Commit和Rollback。

数据库日志

① 错误日志：用来记录 MySQL 服务器运行过程中的错误信息，比如，无法加载 MySQL 数据库的数据文件，或权限不正确等。默认开启，无法禁止。默认存储地址hostname.err，其中 hostname 为服务器主机名

**② 查询日志：**记录了数据库执行的所有命令，不管语句是否正确，都会被记录(update/insert/delete)默认关闭，因为并发场景下查询信息多导致IO很大，影响有SQL性能。优点：有助于帮助我们分析哪些语句执行密集，有助于帮我们分析问题

③ 慢日志：让 MySQL 记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。慢查询会导致 CPU、IOPS、内存消耗过高。默认关闭，

**④ redo log（重做日志）**一种缓存机制，先将数据写入内存中，再批量把内存中的数据统一刷回磁盘。因为掉电或系统故障带来的数据丢失问题

⑤ undo log（回滚日志）：用于存储日志被修改前的值，从而保证如果修改出现异常，可以使用 undo log 日志来实现回滚操作

⑥ bin log（二进制日志）：是一个二进制文件，主要记录所有数据库表结构变更(CREATE、ALTER)

B树和B+树的区别

B树又叫平衡多路查找树，一般用来查找速度比较快，B+树是B树的一种加强树。

B树的每个节点都存储key和data。由于key和data存在同一个节点，无法进行区间查询。查询最好时间复杂度为O(1）

B+树只在叶子节点中存储数据，可以进行区间查询，并且由于非叶子节点没有存储data，所以每一页加载到内存的信息量更大。查询时间复杂度固定为logN。

为什么数据库中索引的数据结构用B+树，第一点是因为其非叶子节点不存储数据；第二点是可以进行区间查询；第三点查询时间复杂度固定。

面试应用

查询所有重复的电子邮箱

select Email 
from person 
group by Email
having count(email)>1

请选出所有课程成绩都成绩大于90分的学生姓名

学生表和成绩表，ID相同

Select distinct name 
from score 
where name not in(Select name from score where grade <=90);

平均收入大于10w的公司id

SELECT company_profit.id
FROM company_profit,(select id, AVG(profit) avg_profit 
from company_profit 
where avg_profit > 100000 
GROUP BY id) temp_table
where company_profit.id= temp_table.id
GROUP BY id;

基本语句与应用

牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网

牛客真题整理来源

sql执行就是：先有表（from、join），对条件加以限定（where），再才能分组（group、having），再才能选择去重（select distinct），再才能排序（order by），最后才能分页（limit）

通用：创建数据库文件（create database）、创建数据表（create table）、修改数据表（alter）、删除数据表（drop）、向数据表中插入数据（insert into）、更新表中的数据（update）、查询表中的满足给定条件的数据（select）、删除表中的数据（delete）

去重 distinct: distinct university(取出学校的去重数据)

限制返回行数limit ：LIMIT 0，5/ LIMIT 5（选取前5行）; LIMIT n-1，m-n+1(第ｎ到ｍ行); LIMIT 10,-1（行 11-last）

查询后的列重新命名as：device_id as user_infors_example

似然LIKE：LIKE ‘%北京%’（学校中含有北京）；NOT LIKE ‘刘%’（不姓刘）； LIKE '[张李刘]%’（ 姓‘张’、姓‘李’和姓‘刘’的学生）； LIKE ‘张__’（姓张的学生）

最高值：select max(gpa) as gpa；order by gpa desc limit 1

和：count（非数值类型）；sum(数值类型)

保留小数位数round：round(avg(gpa),1) 保留一位小数

排序order by: desc(降序)

Where：age between 20 and 23(包含20-23)；university NOT IN (“复旦大学”)(学校不是复旦大学)；age is not NULL（不为空）

Having：聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；group by university having avg_question_cnt<5 or avg_answer_cnt<20

子查询/链接查询：

from question_practice_detail as qpd
inner join user_profile as up
on up.device_id=qpd.device_id  and  up.university='浙江大学'
order by  question_id

连接

• left join (左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
• right join (右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
• inner join (等值连接) 只返回两个表中联结字段相等的行

分别查看&结果不去重union all：所以直接使用两个条件的or是不行的，直接用union(会进行去重和排序)也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重

select 
    device_id, gender, age, gpa
from user_profile
where university='山东大学'
**union all**
select 
    device_id, gender, age, gpa
from user_profile
where gender='male'

分支case/if：用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

SELECT 
    CASE
        WHEN age<25 OR age IS null THEN "25岁以下"
        else "25岁及以上"
    END AS age_cut,
    count(device_id) AS number
FROM user_profile
GROUP BY age_cut

SELECT if(age<25 OR age IS null ,"25岁以下","25岁及以上") as age_cut,
     count(device_id) AS number
FROM user_profile
GROUP BY age_cut

日期函数date：2021年8月每天用户练习题目的数量

select
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where month(date)=8 and year(date)=2021
group by date

文本处理SUBSTRING_INDEX：统计文本中的性别

窗口函数SUBSTRING_INDEX：要找最大最小值对应的那条数据的其他字段信息，通常可以联系一下窗口函数取where rk=1。（直接max()或者min()查询到的其他字段是不对应的）

找出每个学校GPA最低的同学_牛客题霸_牛客网

基础知识

类别：关系型/非关系型

	举例	概念	优点	不足
关系型SQL	Oracle、MySql、Microsoft SQL Server、SQLite、PostgreSQL、IBM DB2	二维表格模型:由二维表以及表之间的关系所构成的一个数据集合；	支持通用的SQL（结构化查询语言）语句;文件格式一致，便于维护；用SQL句子多个表之间做非常繁杂的查询；	海量信息的读写性能差；高并发读写时，硬盘I/O存在瓶颈；可扩展性不足，不能简单的添加硬件和服务节点来拓展性能和负荷工作能力
非关系型NOSQL	键值：Redis、Memcached、Riak。列族：Bigtable、HBase、Cassandra。文档：MongoDB、CouchDB、MarkLogic。图形：Neo4j、InfoGrid	数据结构化储存的集合，可以是文档、键值、列、图;	数据存储格式多样；性能高；可扩展性强；低成本	不支持sql，学习和运用成本比较高；无事务处理机制；数据结构导致复杂查询不容易实现

	成本	查询效率	存储格式	可扩展性	数据一致性	事务处理
关系型SQL	相对高	数据存电脑硬盘，相对慢	基础类型	有join那样的多表查询机制，相对差	整个生命周期的强一致性	事务原子性粒度控制，且方便进行事务回滚
非关系型NOSQL	易部署，低	数据存缓存，快	文档、键值、列、图	数据中间沒耦合度，易水平拓展	最终一致性	事务处理，但可靠性不足，适合可扩展性和大数据量处理

基本概念

Data：数据，是数据库中存储的基本对象，是描述事物的符号记录。
Database：数据库，是长期储存在计算机内、有组织的、可共享的大量数据的集合。
DBMS：数据库管理系统，是位于用户与操作系统之间的一层数据管理软件，用于科学地组织、存储和管理数据、高效地获取和维护数据。
DBS：数据库系统，指在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统、应用系统、数据库管理员（DBA）构成。
数据模型：是用来抽象、表示和处理现实世界中的数据和信息的工具，是对现实世界的模拟，是数据库系统的核心和基础；其组成元素有数据结构、数据操作和完整性约束。
概念模型：也称信息模型，是按用户的观点来对数据和信息建模，主要用于数据库设计。
逻辑模型：是按计算机系统的观点对数据建模，用于DBMS实现。
物理模型：是对数据最底层的抽象，描述数据在系统内部的表示方式和存取方法，在磁盘或磁带上的存储方式和存取方法，是面向计算机系统的。
关系模式：从用户观点看，关系模式是由一组关系组成，每个关系的数据结构是一张规范化的二维表。
型/值：型是对某一类数据的结构和属性的说明；值是型的一个具体赋值，是型的实例。
数据库的三级系统结构：外模式—视图、模式—基本表、内模式—存储文件
数据库内模式：又称为存储模式，是对数据库物理结构和存储方式的描述，是数据在数据库内部的表示方式。一个数据库只有一个内模式。
数据库外模式（视图）：又称为子模式或用户模式，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图。通常是模式的子集。一个数据库可有多个外模式。

视图：是一个命名的虚表，它由一个SQL查询来定义，可以当做表使用，没有实际的物理存储

主键与外键

主键用于唯一表示一个元组，不能有重复，不允许为空，一个表只能有一个主键；

外键用来和其他表建立联系用，是另一个表的主键，可以重复，可以为空，一个表可以有多个外键

外键优缺点：外键即使在数据库服务器宕机或者出现其它问题的时候，也能够最大限度的保证数据的一致性和完整性。增加 ER图 的可读性；在一定程度上说明了业务逻辑

过分强调或者过分的使用主键/外键会增加开发难度，导致表过多等问题。

约束

约束 描述
非空约束（Not NULL） 内容不能为NULL
唯一约束（Unique） 内容不能重复，一个表允许有多个唯一约束
主键约束（Primary） 能唯一且完整标识数据对象的属性及其组合，一个表中只能有一个主键
外键约束（Foreign） 一个表中存在另一个表的主键即为此表的外键，防止破坏表之间连接的动作
检查约束（Check） 控制字段的值的范围

索引

概念：数据库表中一或多个列的值进行排序的结构

作用：可快速访问数据库表中的特定信息.

MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引

使用场景：中到大数据量表适合使用索引. 小数据量表，大部分情况全表扫描效率更高.

             **特大数据量表**，建立和使用索引的代价会随之增大，适合使用**分区或分库**.

缺点：索引需要占物理和数据空间；数据是排序的，排序也是要花时间的

联合索引（a,b,c）,相当于创建了索引（a）,(a,b),(a,b,c)有最左匹配原则

为什么有最左匹配原则：索引的底层是一颗 B+ 树，那么联合索引当然还是一颗 B+ 树，只不过联合索引的键值数量不是一个，而是多个。a 是有序的，而 b，c 都是无序的。但是当在 a 相同的时候，b 是有序的，b 相同的时候，c 又是有序的。遇到范围查询就会停止了。以 select * from t where a=5 and b>0 and c =1; #这样a,b可以用到（a,b,c），c不可以 为例子，当查询到 b 的值以后（这是一个范围值），c 是无序的。所以就不能根据联合索引来确定到底该取哪一行。

为什么使用联合索引？

1、减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
2、覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
3、效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

索引建得越多越好吗？

不是，因为更多的索引也需要更多的存储空间。

数据量小的表不需要建立索引，建立会增加额外的索引开销
不经常引用的列不要建立索引，因为不常用，即使建立了索引也没有多大意义
经常频繁更新的列不要建立索引，因为肯定会影响插入或更新的效率，数据变更需要维护索引，意味着索引越多维护成本越高。
数据重复且分布平均的字段，因此他建立索引就没有太大的效果（例如性别字段，只有男女，不适合建立索引）

CREATE UNIQUE CLUSTERED INDEX employeeID_ind
   ON emp_pay (employeeID)#添加聚集唯一索引 再次查询I/O开销小了很多，并且已根据索引进行了排序
DROP INDEX employeeID_ind ON emp_pay;#删除索引

SELECT * FROM test_tab WHERE name = 一个外部输入的数据//数据量小的话可以，数据量多的话建立索引会加快
CREATE INDEX idx_test4_name ON test_tab (name );//再执行上一句话会加快

//多列索引，不用访问表，直接访问索引就好了
CREATE INDEX idx_test_name_age ON test_tab (name, age);
那么SELECT * FROM  test_tab WHERE name LIKE '张%' AND age = 25

#MySQL 中有一个优化器，他会分析 SQL 语句，将其优化成索引可以匹配的形式
select * from t where b=1 and a=1;     #这样可以利用到定义的索引（a,b,c）,用上a,c（mysql有查询优化器）

事务/ACID属性

深入理解了MySQL中的事务和MVCC，我开始狂怼P6面试官_牛客博客 (nowcoder.net)

定义：事务（Transaction）是并发控制的基本单位，是数据库维护数据一致性的单位；要么全做，要么不做

• ACID属性

原子性（Atomicity）：指整个数据库事务是不可分割的工作单位。
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
隔离性（Isolation）：隔离性也叫做并发控制、可串行化或者锁。事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见，这通常使用锁来实现多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：表示事务一旦提交了，其结果就是永久性的，也就是数据就已经写入到数据库了，如果发生了宕机等事故，数据库也能将数据恢复。

• 四个隔离级别：

读取未提交(READ-UNCOMMITTED)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

读取已提交(READ-COMMITTED)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

可重复读(REPEATABLE-READ)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

可串行化(SERIALIZABLE)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

脏读（一个事务读取到另一个事务未提交的数据）

不可重复读（一个事务读取到另一个事务已经提交的数据）

幻读（一个事务多次查询整表数据，由于其他事务新增（删除）记录造成多次查询的记录条数不同

数据库存储引擎

数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据

InnoDB： 支持事务处理，支持外键，支持崩溃修复能力和并发控制，支持事务的提交（commit）和回滚（rollback）。适合需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票）
MyISAM： 插入数据快，空间和内存使用比较低。适合用于插入新记录和读出记录
MEMORY： 所有的数据都在内存中，数据的处理速度快，但是安全性不高。适合很快的读写速度，对数据的安全性要求较低，不能建立太大的表，适合查询临时的表。
同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择
在创建表的时候通过engine=…或type=…来指定所要使用的引擎；show table status from DBname来查看指定表的引擎。

锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

drop、delete与truncate的区别与应用场景

都表示删除

delete和truncate只删除表的数据不删除表的结构
速度,一般来说: drop> truncate >delete
delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate

mysql和oracle区别与应用场景

MYSQL里用双引号包起字符串，ORACLE里只可以用单引号包起字符串。 

Oracle是大型数据库，而mysql是中小型数据库。 

mysql的主键一般使用自动增长类型，在创建表时只要指定表的主键为auto increment，在插入记录时，不需要再指定该记录的主键值，主键将自动增长；oracle没有自动增长类型，主键一般使用序列， 插入记录时将序列号的下一个值赋给该字段即可。 

oracle查询时，from表名后面不能加上as，不然会报错的，而在mysql下是可以的，例如select t.username from test as t。 

对于翻页，mysql用limit 开始位置，记录个数；oracle内层用ROWNUM,用于标识小于的行数，用ROWNUM的别名标识的行数。

范式

关系规范化在数据库设计的（逻辑设计）阶段进行
各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式:第一范式(1NF)、第二范式 (2NF)、第三范式 (3NF)、巴斯-科德范式 (BCNF)、第四范式(4NF) 和第五范式(5NF, 又称完美范式)。
第一范式（1NF）：在数据库表中，要求每个属性值都是不可再分的
第二范式 (2NF)：每个非主属性完全函数依赖于主键，且该函数依赖集中存在传递依赖
第三范式（3NF）：如果某关系模式满足第二范式，而且它的任何一个非主属性都不传递依赖于任何关键字，则满足第三范式。
第四范式首先要求是第三范式，且在关系模式中，至多只有一个多值事实。实体中的多值属性，取实体的码和多值属性构成新增的关系模式，且该新增关系模式中，实体的码多值决定多值属性，属于平凡的多值依赖

SQL的优化方法

对查询进行优化：查询具体的字段而非全部字段，可以节省资源，减少网络开销，提高查询效率，而且显式地指定列后就可以清楚的知道究竟返回了哪些列（对于其他人不止编者自己，也更容易理解）

加上LIMIT限制，避免查询不需要记录

避免使用OR,而是分开写用uninon all，or可能导致索引失效，从而全表扫描

尽量采用**右模糊(like '大连%’)**查询，否则索引会失效

用数值类型代替字符串类型，因为字符串类型会逐个比较，数字型只需要比较一次；字符降低查询和连接的性能，并增加存储开销

使用varchar代替char，varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间

**三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小，**等值连接，返回的行数比较少，所以性能相对会好一点

先过滤再分组，having+group by

清空表truncate比delete快，truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放，delete语句每次删除一行，并在事务日志中为所删除的每行记录一项

操作delete或者update语句，加个limit或者循环分批次删除，加limit，删错也只是丢失部分数据，可以通过binlog日志快速恢复的；如果数据量太大的话，容易把CPU打满，越删越慢

通过建立索引对查询进行优化

SELECT * "效率低的原因及场景

• 不需要的列会增加数据传输时间和网络开销：“SELECT * ”数据库需要解析更多的对象、字段、权限、属性等相关内容，数据库造成沉重的负担。客户端与服务器之间的通信也需要额外的时间
• 对于无用的大字段，如 varchar、blob、text，会增加 io 操作：长度超过 728 字节的时候，会先把超出的数据序列化到另外一个地方，因此读取这条记录会增加一次 io 操作。
• 失去MySQL优化器“覆盖索引”策略优化的可能性：SELECT * 杜绝了覆盖索引的可能性，而基于MySQL优化器的“覆盖索引”策略又是速度极快，效率极高，业界极为推荐的查询优化方式。

调整数据结构的设计，对于经常访问的数据库表建立索引
调整SQL语句， ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。
调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。
调整硬盘Ｉ／Ｏ，DBA可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间Ｉ／Ｏ负载均衡。

参考链接

数据库常见考点与知识点总结_Ordinary_yfz的博客-CSDN博客_数据库考点

关系型数据库和非关系型数据库之间的区别