2023-02-01 10:20 Java 发布于广东

关注

sql面试个人总结

1.sql语句的分类--结构化查询语言

DDL语言(数据库定义语言) 是对表、视图。索引操作。对逻辑结构进行操作

代表的关键字为ALTER CREATE DROP;主要是对数据库进行操作;

DML(数据库操作语言)对数据进行操作

代表关键字为 insert、 delete 、 update。主要的对表进行操作;

DQL(数据库查询语言）

select为关键字,from子句，where子句构成查询快。

where 条件 group by 分组 older by 排序 having 结果聚合函数；

DCL（数据控制语言）用来授予或回收访问数据库的某种权限,并控制数据库操纵事务发生的时间及其效果，对数据库实行监视。（简单理解为权限控制）关键字为grant授权 revoke收回权限；
TCL 经常被用于快速原型开发,脚本编程,测试等方面，commit提交 rollback回滚；

2.where和having的区别:

俩者起作用的地方不一样

where 作用与表和视图是表和视图的件查询条件 ;

having作用于分组后的记录用于满足条件的组；

子句有区别

where 后面不能接聚合函数,可以接单行函数；

（单行函数就是使用函数查询返回一条结果如时间转换,转换函数）

having 在group by之后执行,可以接聚合函数；

执行的顺序不一样

where的搜索条件是在语句执行执行分组前进行应用；

having的搜索条件是分组条件后执行；

总结：简单来说where后面接表或者视图，having后面是结果。having和where并没有谁好谁不好的区别，我觉得最重要的是看需要，适合用哪个就用哪个

3.什么是视图

视图也被称做虚表,是虚拟的表。是一组数据的逻辑表示,其本质是对应于一条select语句，结果集被赋予一个名字,就是视图的名字

特点：

视图中的数据并不属于视图本身，而是属于基本的表，对视图可以像表一样进行insert,update,delete操作。

*视图不能被修改，表修改或者删除后应该删除视图再重建。

*视图的数量没有限制，但是命名不能和视图以及表重复，具有唯一性。

*视图可以被嵌套，一个视图中可以嵌套另一个视图。

*视图不能索引，不能有相关联的触发器和默认值，sql server不能在视图后使用order by排序。

视图的功能:

简化用户操作

能以不同的角度观察同一个数据库

对重构数据库提供了逻辑独立性：利用视图将需要的数据合并或者筛选，但是不影响原表的数据和结构

4. 说一下常用的聚合函数

AVG（）返回数值列的平均值。
COUNT（）返回匹配指定条件的行数。
FIRST（）返回指定的列中第一个记录的值。
LAST（）返回指定的列中最后一个记录的值。
MAX（）返回指定列的最大值。
MIN（）返回指定列的最小值。
SUM（）返回数值列的总数。
GROUP BY 用于结合聚合函数，根据一个或多个列对结果集进行分组。
HAVING 让我们筛选分组后的各组数据。
EXISTS 用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。
UCASE（）把字段的值转换为大写。
LCASE（）把字段的值转换为小写。
MID（）用于从文本字段中提取字符。
LEN（）返回文本字段中值的长度。
ROUND（）用于把数值字段舍入为指定的小数位数。
NOW（）返回当前系统的日期和时间。
FORMAT（）用于对字段的显示进行格式化。

5.说说数据库约束有哪些？

数据库sql约束有：主键约束，外键约束，唯一约束，非空约束，默认约束，检查约束

6.说说数据库连接查询有哪些？

自连接（交集），外连接（左外连接、右外连接、全连接（并集）），交叉连接（乘集）。

所谓左外联接，是以左表为主表，右表为辅表，将两表的数据进行连接，然后将左表没有对应项显示为NULL；换种说法：就是根据左表的记录，在被连接的右表中找出符合条件的记录与之匹配，如果找不到与左表匹配的，用null表示。

7.where 和 on 的区别？

(1) 筛选的顺序 on 优先于 where ;

(2) 语意 on 是两张表的连接条件(外键关联), where会把前面的结果集进行筛选(可以筛选所有字段条件)

8.什么是触发器？

触发器是一种特殊类型的存储过程，它会在指定的表/视图数据发生变化时自动执行，一般用于INSERT、UPDATE、DELETE操作配合使用。

比如说有张表有俩个字段一个name,一个为age 有zs,ls;

我们自定义触发器 trigger 在发生 delete操作是会触发触发器添加一条数据

name：删除,age:0;

进行查询第一次数据有zs,ls数据;然后执行delete操作

第二次查询ls被删除表中有俩条数据第一条是zs,第二条为 name：删除,age:0;

1.常用触发器的原因一般是：操作某一表数据时需要对其他表进行联合操作。

如：学生表删除学生，同时需要删除成绩表、选课表等其他表中该学生的数据。

2.使用触发器注意For和Instead Of的使用区别！

3.目前我使用的触发器类型一般是DML类型触发器（表数据的插入、更新、删除），另外还有DDL类型（建表/视图、改表/视图、删表/视图）

9.mysql有哪些数据库存储引擎，有什么区别？

存储引擎是用来把数据存储在文件或内存的技术

mysql存储引擎总共有九种，常用的数据引擎有MyISAM、InnoDB（5.5默认）、MEMORY记忆；

MyISAM和InnDB的区别:

MyISAM存放的方式:MyISAM这种引擎不支持事务,不支持行级锁,只支持并发插入的表锁,主要用于高负载的select.
索引的方式:MyISAM也是使用B+tree索引但是和InnoDB的在具体实现上有些不同
优缺点:MyISAM的优势在于占用空间小,处理速度快,缺点就是不支持事务的完整性和并发性

innoDB支持事务,支持外键,支持表锁,支持全文索引,读取数据快

InnoDB存放的方式:

innodb支持自增长列(auto_increment),自增长列的值不能为空【如果在使用的时候为空的话会进行自动存现有的值开始增值】。
innodb存储引擎支持外键(foreign key)【外键所在的表称为子表而所依赖的表称为父表】
innodb存储引擎最重要的是支持事务,以及事务相关联的功能.支持mvcc的行级锁
索引的方式:innodb存储引擎使用的是B+Tree
优缺点:InnoDB的优势在于提供了良好的事务处理,崩溃修复能力和并发控制,缺点是读写效率较差,占用的数据空间相对较大。

{重要：MVCC多版本并发控制在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。说白了 MVCC 就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现

MVCC 就是因为大佬们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出的解决方案，所以在数据库中，因为有了 MVCC，所以我们可以形成两个组合：

MVCC + 悲观锁

MVCC解决读写冲突，悲观锁解决写写冲突

MVCC + 乐观锁

MVCC 解决读写冲突，乐观锁解决写写冲突

这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题 }

9.1 innodb为什么要用自增id作为主键

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来重建表并优化填充页面。

10.mysql哪些字段适合建立索引?

1.表的主键、外键必须有索引

2.数据量一般达到500的应该有索引

3.在 where、 group by、 order by,on从句中的列添加索引

4.索引应该在选择性高的字段上;

5.索引应该小字段上,对于大文本的字段,不适合建立索引

6.复合索引的建立需要进行仔细分析;尽量考虑单字段索引代替

设计索引的一个重要原则就是能用窄索引不用宽索引

7.经常与其他表进行连接的表，在连接字段上应该建立索引；

8.不适合的字段

a.表记录少不建议影响性能

b.经常插入的、修改的、删除的表。不要建立太多的索引;

c.删除无用的索引,避免对执行计划造成负面影响

10.1 为什么使用索引？

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
帮助服务器避免排序和临时表
将随机IO变为顺序IO。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义

11.mysql的索引失效有哪些情况？

1.索引列为Null

2.mysql查询只使用一个索引，多个索引，后面失效

联合索引没有遵循最左原则

3. like % 百分号前置

4.Not in不会走索引

5.对索引列进行函数操作,字符串作为条件要引号引起来否者不走索引

6.表连接时俩个表的编码格式不一致

7.where条件是Or /not in/!=

----------------------------------------------------------------------------------------------------------------------

避免过度索引
避免重复和冗余索引
删除不需要使用的索引
最常用作限制条件的列放在最左边
合理使用短索引
索引字段越小越好

12.有没有参数过表设计？如何进行表设计？对表设计有什么心得？

合理的数据库命名规范
合理的字段类型选择规范
合理的表结构规范

水平拆分

把同一张表中的数据拆分到不同的数据库中进行存储，需要用到mycat

或者把一张表拆分成 n`` 多张小表`

垂直拆分

拆分成不同的数据库，比如会员数据库、订单数据库、支付数据库、消息数据库等，垂直拆分在大型电商项目中使用比较常见。

13.什么是数据库的三范式？什么是反三范式？

1NF，列不可拆分，具有原子性。
2NF，满足1NF，有主键，非主键列需要完全依赖主键，不能依赖部分。
3NF，满足2NF，非主键需要直接依赖主键，不能传递依赖。
反三范式就是通过增加冗余、聚合的手段来提升性能。

14.说说数据的调优和sql语句的优化你是怎么做的？

我会先开启慢查询日志,统计看看那些是慢sql,然后进行exp lain执行计划进行分析查询。然后后进行相关的调优。

15.Mysql中为什么用b+tree，不用b树（索引）

15.1用没用过索引呀索引怎么用的呀

首先我会对这张表进行判定,这张表是不是经常进行查询,查询频率很高，修改的频率很低的话。则这张表就很适合添加索引。然后我会进行判定我这张表查询时哪些字段会经常做为查询条件作为使用。

是的话我就会把当前的字段作为索引字段。

15.2 对于mysql的索引是你是怎么理解的？

对于mysql中索引来说它使用的是b+树这种结构来形成的。b+树是一个多路平衡查找树

b+树的特点是非叶子节点保存key,叶子节点保存值。这就是b+树的特点。

补充：（15问）重要

首先b树走的是中序遍历，需要遍历树。虽然不是遍历整个树,但是它的速度相对来说还是比较慢的
而b+树内部来说我们把数据放到叶子结点中,使得树的结构变矮了。只需要对链表进行遍历即可,链表的查询效率更快，对于mysql数据库来说是允许。所以b+树能够做到这一点，而b树做不到。

15.3 MyISAM和InnoDB实现B树索引方式的区别是什么？

MyISAM，B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为“非聚簇索引”
InnoDB，其数据文件本身就是索引文件，相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，这被称为“聚簇索引”或者聚集索引，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。

在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

16.MySQL有哪些索引？

普通索引 : 唯一的作用是加快对数据的访问熟读,因此应该只为那些经常出现在查询条件或排序条件中的数据列创建索引

唯一索引 : 列中的值都是唯一的不可重复的,简化了mysql对这个索引的管理工作,因此变得更有效率,同时在插入数据时会自动检查是否存在,如果存在则不进行插入.

主键索引 : 主键索引是一种特殊的索引,不允许有空值

复合索引: 复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用,使用符合索引需要遵循最左前缀法则

空间索引: 空间索引是对空间数据类型的字段简历的索引,mysql中的空间数据类型有4重,geometry,point,linestring,polygon,mysql使用spatial关键字进行扩展,使得创建正规索引类型的语法创建空间索引,创建空间索引的列必须生命非空约束,空间索引只能在存储引擎为myisam的表中创建

17.关系型和非关系型数据库的区别？