数据库试题

1.MySQL数据库存储引擎有哪些?及使用场景？

1.InnoDB 引擎(MySQL5.5以后默认使用)

MySQL 5.5 及以后版本中的默认存储引擎，他的优点如下：

灾难恢复性好

支持事务

使用行级锁

支持外键关联

支持热备份

对于InnoDB引擎中的表，其数据的物理组织形式是簇表（Cluster Table），主键索引和数据是在一起的，数据按主键的顺序物理分布

实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取

支持热备份

2.MyISAM引擎

特性如下：

不支持事务

使用表级锁，并发性差

主机宕机后，MyISAM表易损坏，灾难恢复性不佳

可以配合锁，实现操作系统下的复制备份、迁移

只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳

数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能

3.MEMORY 存储引擎

提供内存表，也不支持事务和外键。显著提高访问数据的速度，可用于缓存会频繁访问的、可以重构的数据、计算结果、统计值、中间结果。

缺点如下：

使用表级锁，虽然内存访问快，但如果频繁的读写，表级锁会成为瓶颈

只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型，浪费空间

不支持TEXT、BLOB字段。当有些查询需要使用到临时表（使用的也是MEMORY存储引擎）时，如果表中有TEXT、BLOB字段，那么会转换为基于磁盘的MyISAM表，严重降低性能

由于内存资源成本昂贵，一般不建议设置过大的内存表，如果内存表满了，可通过清除数据或调整内存表参数来避免报错

服务器重启后数据会丢失，复制维护时需要小心

MySQL存储引擎MyISAM与InnoDB如何选择

补充说明：

1.两种存储引擎的大致区别表现在：

1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用

3）InnoDB支持外键，MyISAM不支持

4）从MySQL5.5.5以后，InnoDB是默认引擎

5）InnoDB不支持FULLTEXT类型的索引

6）InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表。

7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。

8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。

9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

有人说MYISAM只能用于小型应用，其实这只是一种偏见。

如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，读写分离，而不是单纯地依赖存储引擎。

现在一般都是选用InnoDB了，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。

2.创建索引的原则？

索引不是越多越好

不要对经常变动数据加索引

小数据量的表不需要加索引

索引一般加在常用来查询的字段上

3.索引的分类?

普通索引（举例）

是最基本的索引，它没有任何限制。它有以下几种创建方式：
直接创建索引

CREATE INDEX index_name ON table(column(length))

修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

创建表的时候同时创建索引

CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) CHARACTER NOT NULL ,

    `content` text CHARACTER NULL ,

    `time` int(10) NULL DEFAULT NULL ,

    PRIMARY KEY (`id`),

    INDEX index_name (title(length))

)

删除索引

DROP INDEX index_name ON table

唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

创建表的时候直接指定

CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) CHARACTER NOT NULL ,

    `content` text CHARACTER NULL ,

    `time` int(10) NULL DEFAULT NULL ,

    UNIQUE indexName (title(length))

);

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) NOT NULL ,

    PRIMARY KEY (`id`)

);

组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
创建表的适合添加全文索引

CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) CHARACTER NOT NULL ,

    `content` text CHARACTER NULL ,

    `time` int(10) NULL DEFAULT NULL ,

    PRIMARY KEY (`id`),

    FULLTEXT (content)

);

修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)

补充总结索引语法格式：

CREATE TABLE table_name[col_name data type][unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

unique|fulltext为可选参数，分别表示唯一索引、全文索引
index和key为同义词，两者作用相同，用来指定创建索引
col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
asc或desc指定升序或降序的索引值存储

4.什么是触发器？触发器的使用场景有哪些？

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

可以通过数据库中的相关表实现级联更改。

实时监控某张表中的某个字段的更改而需要做出相应的处理。

例如可以生成某些业务的编号。

注意不要滥用，否则会造成数据库及应用程序的维护困难。

大家需要牢记以上基础知识点，重点是理解数据类型CHAR和VARCHAR的差异，表存储引擎InnoDB和MyISAM的区别。

5.超键，候选键，主键，外键分别是什么？

超键(super key): 在关系中能唯一标识元组的属性集称为关系模式的超键

候选键(candidate key): 不含有多余属性的超键称为候选键。也就是在候选键中，若再删除属性，就不是键了！

主键(primary key): 用户选作元组标识的一个候选键程序主键

外键(foreign key)：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

     参照：

https://www.cnblogs.com/lab-zj/p/12167280.html

6.sql的约束有哪几种？

  Not null

  Unique

  Primary key

  Foreign key

  Check 用于控制字段值的范围

7.六种关联查询的使用场景？

      参照：

  https://www.cnblogs.com/zhang19950924/p/13218166.html

8.varchar与char的区别？

      Varchar-可变长度字符串

      Char -固定长度字符串

9.int(20)，char(20)，varchar(20)区别是什么？

        int(20)：int类型数值，长度为20；

        char(20)：固定长度字符串，长度为20

        varchar(20)：可变长度字符串，长度为20

10.drop,delete,truncate区别？

truncate table 和 delete只删除数据(记录)不删除表的结构;drop语句将删除表的数据(记录)和表结构依赖的约束(constrain)，触发器(trigger),索引(index),保留依赖于该表的存储过程/函数；

delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚；

truncate之后的自增字段从1开始计数了[即truncate是摧毁原来的表重新创建一张结构相同的表(有原表的结构)],delete的仍保留原来的数值[即如果添加数据从原值+1开始],drop将表的结构也删除了(原表数据和结构都没有了)；

补充总结：

总结：

执行速度:drop> truncate > delete。

在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；
如果想删除表，当然用drop；
如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；
如果和事务有关，或者想触发trigger，还是用delete；

11.float和double区别是什么？

float可以存储最多八位的十进制，内存分配四个字节

double可以存储最多十八位的十进制，内存中分配八个字节

12.union和union all的区别

union all不会合并重复项，union可以合并重复项

union的效率高于union all

13.简述数据库优化的经验

1.有外键约束设计的会影响增删改的性能，如果程序可以保障数据库的完整性，就可以去除外键

2.sql语句全部大写（尤其是别名），因为sql语句发送给数据库，数据库首先将sql语句转换成大写

3.如果应用程序能够保证数据库的完整性，数据库的设计不需要按照数据库的三大范式去设计

4.索引无需创建太多（创建索引可以加快查询效率），索引会消耗磁盘空间

5.如果使用jdbc请使用preparedstatement，通过该执行器来创建sql，它的执行速度要快于sm，使用改执行器，sql会预编译在此对象中，便于多次高效执行

14.怎么优化sql查询语句

1.对查询进行优化，尽量避免进行全表扫描（有些不可避免），首先考虑在where及order by涉及的列上建立索引

2.通过创建索引提高查询效率

3.查询语句中全部大写SQL

4.尽量避免在where子句中对字段或列进行is，null等判断，否则会导致数据库引擎放弃使用索引，而使用全表扫描（is not ，is null）

5.where子句使用all作为连接条件也会导致数据库引擎放弃使用索引，使用全表扫描

6.in和not in也会导致全表扫描

15.sql的执行顺序

from：将数据从硬盘加载到数据缓冲区

where：从表或视图中选择满足条件的元组（不能使用聚合函数）

join：通过join连接方式查询数据

on：join on实现多表连接查询（推荐该方式，不推荐子查询）

group by：通常会和聚合函数一起使用

having：在数据中选出符合条件的元组（通常和group by一起使用）

select：查询数据

distinct：去除重复项

union：将多个查询结果合并（去除重复项）

order by：排序

limit：显示区间数据

16.sql的生命周期

1.客户端与数据库服务器（mysql）建立连接

2.数据库进程拿到请求sql

3.解析并执行

4.读取数据到内存，进行逻辑处理

5.通过步骤一发送结果给客户端

6.关闭连接，释放资源

17.mysql如何实现分页

limit可以强制select语句返回指定的记录条数

limit接受一个或两个数字参数，该参数必须是整数值，第一个参数指定起始，第二个参数指定返回记录的最大行数（数据的起始数据从0开始）

select * from 表名 limit 5，10；（结果显示从第6行到第15行）

select * from 表名 limit 0，-1；（从第1条到最后一条）

select * from 表名 limit 5；（5为最大行数，即0-5）

18.表创建为什么要设计主键

主键是数据库确保数据行在整张表中的唯一性，主键设定之后，对表删除，修改，查询的执行效率更高

19.什么是视图？为什么使用视图？视图有那些特点？视图的使用场景？

视图是一个虚拟的表，是一个表中的数据经过某种筛选显示的方式，视图是由一个预定义的查询select语句组成的

为了提高复杂的sql语句复用性和表操作安全性

视图的列可以来自不同的表，视图是由基本表产生的表，视图的创建和删除不影响基表，对视图内容的更新直接影响到基表（添加，删除，修改），视图数据来自多个基表时，通常不允许执行添加，删除，修改等操作

重用的sql语句，简化复杂的sql操作，使用表的部分内容，保护数据

20.count（1），count（*），count（列名）

*：所有的列，相当于统计行数，统计时不会排除列值为null

1：包括了被忽略的所有列，统计时不会忽略列值为null

列名：只包括指定列名的那一列，统计时会忽略列值为null

21.MySql,Oracle,SqlServer

Sqlserver只能在windows上使用

Mysql和Oracle可以在其他系统上使用

Mysql是开源的，Sqlserver和Oracle是需要付费的

从小到大排序：mysql，sqlserver，oracle

oracle支持大并发，大访问量

SQL server对于二者的支持一般

而MySQL最好的使用方式是集群和缓存搭配使用

Oracle支持多用户，不同权限进行操作

而MySQL只要具备登录权限就可以操作

做分页mysql使用的是limit

SQL serve使用的是top

Oracle使用的是row（伪页）

Oracle没有自动增长类型，MySQL和SQL serve有自动增长类型

MySQL、SqlServer、Oracle三种数据库区别在哪里？_我不生产数据，只是数据的搬运工-CSDN博客

22.数据库三大范式

1NF：

每个列不可以再拆分

2NF：

在1NF基础上，非主键列完全依赖于主键

3NF：

在2NF基础上非主键列只依赖于主键，不依赖于其他非主键列

23.MySQL有关权限的表

user权限表：记录允许连接到服务器的用户账号管理，其中的权限都是全局级别的

db权限表：记录了各个账号在各个数据库上的操作权限

table_ priv权限表：记录了数据表级的操作权限

columns权限表：记录数据列级权限

host权限表：配合db权限表对主机上数据库级的操作权限做更细致的控制，这个权限表不受grant和revoke影响

24.列举数据库常用函数

sum：返回指定列唯一值的和

max：返回最大值

min：返回最小值

avg：返回平均值

count：返回行数

date：表达式，返回指定表达式的日期值

25.事务的四大特性是什么？ACID

原子性：事务的最小单位，不可分割

一致性：数据要保持一致

隔离性：并发访问数据库时，一个用户的事务，不被其他事务所干扰，各并发事务之间的数据库是一致的

持久性：事务在提交之后对数据库中数据的改变是持久的

26.什么是脏读？什么是幻读？什么是不可重复？

脏读（DRITY READ）：某个事务更新了一份数据，而另外一个事务在此时读取同一份数据，当前一个事务操作rollback，后一个数据就会出现？？？？？？？

不可重复读：??????non repeatable read一个事务两次数据查询中，数据不一致，两次查询过程中，插入更新事务

幻读phantom read：一个事务两次查询中，数据不一致，譬如：一个事务查询时查到四列事务，而另一个事务插入了新的列，先前的事务在查询中发现多了新列数据

27.什么是事务的隔离级别？mysql默认的隔离级别是什么？

为了达到事务的四大特性，数据库定义的四种不同的隔离级别

1.read-uncommitted（读取未提交）：适用于（导致）脏读，不可重复读，幻读，隔离级别最低（允许读取尚未提交的数据）

2.read-committed（读取已提交）：适用于不可重复读和幻读，允许读取并发事务已经提交的数据，可以阻止脏读

3.repeatable-read（可重复读）：只适合幻读，对一个字段多次读取时，事务可以保存一致，可以阻止脏读和重复读

4.serializable（灭霸）：都不适用，隔离级别最高（串行：从最低到最高依次进行），如果使用该隔离级别，使用期间不能产生任何干扰

MySQL默认采用可重复读

Oracle默认采用读取已提交