数据库面试问题汇总

数据库面试问题汇总

1.数据库管理命令

1.1查询所有数据库

---- show databases;

1.2创建数据库，并将数据库字符集默认为utf-8

----create database day814

----default character set utf8

-----;

1.3查看数据库的默认字符集

----show create database day814;

1.4 删除数据库

---- drop database day15;

1.5 修改数据库(即修改数据库的默认字符集)

-----alert database day814 default character set gbk;

2.表管理命令

2.1选择数据库命令

----- use day814;

2.2查看所有表

---- show tables;

2.3 创建表

---- create table student(

-----sid int,

-----sage int,

-----sname varchar(20)

-----);

2.4 查看表结构

------desc student;

2.5 删除表

----- drop table student;

2.6 修改表

添加字段

-----alter table student add column sgender varchar(2);

删除字段

-----alter table studnet drop column s gender;

修改字段类型

-----alter table student modify column sgender varchar(100);

修改字段名称

-----alter table student change column sgender gender varchar(100); 修改表名称

-----alter table student rename to teacher;

3.数据管理

3.1增删改数据

-----增加数据

INSERT INTO student VALUES(1,’tom’,20,2);//要严格按照表的字段顺序插入数据

增加部分数据

INSERT INTO student (id,name) VALUES(2,’tina’);

-----删除数据

条件删除

DELETE FROM student WHERE id=2;

注意：DELETE FROM和TRUNCATE FROM的区别

DELETE FROM:可以全表删除，也可以条件删除，只能删除数据，不能删除约束，最后删除之后，可以回滚事务；

TRUNCATE FROM: 只能全表删除，不能条件删除，即可以删除表的数据，也可以删除表的约束，不能回滚事务；

-----修改数据

带条件修改

UPDATE student SET age=1,name=’tom’ WHERE id=2;

3.2查询数据（重点）

3.2.1查询所有列(*通配符)

---SELECT * FROM student;

3.2.2查询指定列

---SELECT sid,sname, gender FROM student;

3.2.3查询时添加常量列

---SELECT sid,sname,gender,sage,’java_Class’ AS ‘sgrade’FROM student;

//在查询student表时，添加一个sgrade列，内容为java_Class;

3.2.4查询时合并列

---SELECT sid,sname,(servlet+jsp) AS ‘总成绩’FROM student;

3.2.5查询时去除重复记录

---SELECT DISTINCT gender FROM student;

---SELECT DISTINCT address FROM student;

3.2.6条件查询（模糊条件查询）

--- 逻辑条件AND(与) OR(或)

SELECT * FROM student WHERE sid=2 AND sname=’c++’; //交集

SELECT * FROM student WHERE sid=2 OR sname=’张三’; //并集

--- 比较条件<, >,<= 最重要的<>不等于，另外BETWEEN AND

SELECT * FROM student WHERE jsp<=90 AND jsp>=75;

--- 判空条件

需求：查询地址为null的学生（包括null和空字符串）

注意：NULL 表示没有值

空字符串表示有值的；

LIKE 表示模糊条件; %表示任意字符

SELECT * FROM student WHERE sname LIKE ‘j%’;

3.2.7聚合查询

常用的聚合函数：sum() \ avg() \ max() \ min() \ count() \

SELECT MAX(jsp) AS ‘max_score’FROM student;

SELECT AVG(jsp) AS ‘average score ‘ FROM student;

这里注意：count统计数据不包含null的数据

3.2.8 分页查询

查询第1,2 条记录

SELECT * FROM student LIMIT 0,2;

//注意：LIMIT 0:起始行行数，2：总行数；

3.2.9 查询排序

ASC: 升序；DESC 降序

SELECT * FROM student ORDER BY sid ASC;

SELECT * FROM student ORDER BY servlet ASC;

SELECT * FROM student ORDER BY servlet ASC,jsp DESC;

3.2.10分组查询

-- 2.11 分组查询(group by)

-- 需求：查询男女的人数

-- 预期结果：

-- 男 3

--- 女 2

--算法步骤：1) 把学生按照性别分组(GROUP BY gender)

-- 2) 统计每组的人数(COUNT(*))

SELECT gender,COUNT(*) FROM student GROUP BY gender;

3.2.11分组查询后筛选

-- 2.12 分组查询后筛选

-- 需求：查询总人数大于2的性别

-- 算法：1) 查询男女的人数

-- 2）筛选出人数大于2的记录(having)

--- 注意：分组之前条件使用where关键字，分组之后条件使用having关键字SELECT gender,COUNT(*) FROM student WHERE GROUP BY gender HAVING COUNT(*)>2;

综上，所有的SQL语句指令，主要包含3个部分:数据库管理、表管理、数据管理。SQL语句分类：数据定义语言、数据查询语言、数据操作语言。

4.1 默认值

4.2非空4.3唯一

4.4 主键（==非空+唯一）

5.数据库设计：三大范式

第一范式：要求表的每个字段必须是不可分割的独立单元；

比如student : name

Java\狗娃

应该改为name oldname

Java 狗娃

第二范式：在第一张范式基础上，要求每张表只表达一个意思，表的每个字段都和主键有依赖；

employee（员工）: 员工编号员工姓名部门名称订单名称--违反第二范式

员工表：员工编号员工姓名部门名称

订单表：订单编号订单名称-- 符合第二范式

第三范式：在第二范式的基础上，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系；

员工表：员工编号（主键）员工姓名部门编号部门名--符合第二范式，违反第三范式（数据冗余高）

员工表：员工编号（主键）员工姓名部门编号--符合第三范式（降低数据冗余）部门表：部门编号部门名

解析：个人对数据库三大范式的理解：首先数据表中的每个字段都必须是不可分割的独立单元；其次，在第一范式的基础上，要求每张表只表达一个意思，表的每个字段都和主键有依赖；最后，在第二范式的基础上，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

6.存储过程

带有逻辑的SQL语句，存储过程带上流过程控制语句（if else for while）;

(1)存储过程执行效率非常快，因为是在服务端执行；

(2)存储过程移植性比较差，不同数据的存储过程是不能移植的；

7.数据库索引实现原理

1.关于数据库索引采用的数据结构

题目1：Mysql数据库用过吧？l里面的索引是基于什么数据结构。

答：主要是基于Hash表和B+树

题目2：很好请你说一下B+树的实现细节是什么样的？B-树和B+树有什么区别？联合索引在B+树中如何存储？

答: 首先，数据库使用树型结构来增加查询效率，并保持有序。那么，为什么不使用二叉树来实现数据结构呢，二叉树算法时间复杂度是lg(N)，查询速度和比较次数都是较小的。

实际上，查询索引操作最耗资源的不在内存中，而是磁盘IO。索引是存在磁盘上的，当数据量比较大的时候，索引的大小可能达到几个G。那么，我们利用索引进行查询的时候，不可能把索引直接加载到内存中，只能一次读取一个磁盘页，一个磁盘页对应着一个节点，一次读取操作时一个磁盘io。

在二叉树查询时，最坏的情况下查找的次数是树的高度，即io次数为树的高度。B-树就是比二叉树“矮胖”的树。

二叉树的特征如下：

1. 根节点至少有两个子女

2. 每个中间节点包含k-1个元素和k个孩子，其中m/2 <= k <= m

3. 每个叶子节点包含k-1个元素，其中m/2 <= k <= m

4. 所有叶子节点位于同一层

5. 节点中的元素从小到大排列，正好是孩子节点的值域。（就是孩子节点的元素都比父节点中元素的最小值大，比父节点元素的最大值小）

B-树查询的次数并不比二叉树的次数小，但是相比起磁盘io速度，内存中比较的耗时就不足为提了。所以只要树的高度足够低，io次数少，就可以提升查找性能。而每个节点中有多个元素，都只在内存中操作。

而B+树是基于B-树的，增加了如下规则：

1. 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

2. 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

所以，B+树对比B-树有如下好处：

io次数少：b+树中间节点只存索引，不存在实际的数据，所以可以存储更多的数据。索引树

更加的矮胖，io次数更少。

性能稳定：b+树数据只存在于叶子节点，查询性能稳定

范围查询简单：b+树不需要中序遍历，遍历链表即可。

B树：有序数组+平衡多叉树；

B+树：有序数组链表+平衡多叉树；

数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

8.数据库三大引擎之间差异

(1)MyISAM

特性：不支持事务、MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能适应；表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但也同时大大降低了并发性能；

读写互相阻塞：不仅会在写入时阻塞读取，还会在读取时，阻塞写入，但读本身并不会阻塞另外的读。

只会缓存索引：MyISAM可以通过key_Buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。

(2)InnoDB

特性：具有较好的事务支持，支持4个事务隔离级别，支持多版本读；

行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响；

读写阻塞与事务隔离级别相关；

具有非常高效的缓存特性，能缓存特性，也能缓存数据；

整个表和主键以Cluster方式存储，组成一颗平衡树；

所有Secondary Index都会保存主键信息；

(3)NDBCluster

分布式：分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分；

支持事务：和Innodb一样，支持事务；

可与MySqld不在同一主机：可以和MysqlId分开存在于独立的主机上，然后通过网络和MysqlId通信交互；

内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存放在与内存中；

9.脏读和幻读

在说数据库的脏读和幻读时，首先要说数据库的事务，事务的4大特性：

（1）原子性：事务是数据库的逻辑工作单位，它对数据库的修改，要么全部执行，要么全部不执行；

（2）一致性：事务前后，数据库的状态都满足所有的完整性约束；

（3）隔离性：并发执行的N个事务是隔离的，一个不影响一个，一个事务在没有commit 之前，被修改的数据不可能被其他事务看到（如何实现的呢，通过设置数据库的隔离级别）（4）持久性：持久性意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失，持久性主要在于DBMS的恢复性能；（系统故障，数据不丢失）

脏读：又称无效数据读出，一个事务读取另外一个事务还没有提交的数据叫做脏读；

例如：事务T1修改了一行数据，但是还没有提交，这时候事务T2读取了被事务T1修改后的数据，之后T1因某种原因回滚了，那么事务T2读取的数据就是脏的；

解决办法：把数据库的事务隔离级别调整到READ_COMMITTED；

不可重复读：不可重复读是指同一个事务内，两个相同的查询返回了不同的结果；

例如：事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而在此读取该数据，那么T1发现两次读取的结果不同；

解决办法：把数据库的事务隔离级别调整到REPEATABLE_COMMITTED；

幻读：例如：当系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这叫幻读。

解决办法：把数据库的事务隔离级别调整到SERIALIZABLE_READ；

综上：重点注意：不可重复读和幻读的区别，不可重复读强调的是查询修改，幻读强调的是插入和删除。

10.Spring事务管理

→什么是事务：事务（transaction）是由一系列操作序列构成的程序执行单元，这些操作要么都做，要么都不做，是一个不可分割的工作单位

→事务的4个基本性质：原子性、一致性、隔离性、持久性

→数据库并发操作可能引起的问题：

①脏读：事务A读取了事务B中尚未提交的数据，如果事务B回滚，则A读取使用了错误的数据（读取了未提交的数据）

②不可重复读：事务A多次的读取过程中，事务B对数据进行了修改，导致事务A多次读取的数据不一致。

③丢失更新：在事务A期间，事务B对数据进行了更新，在事务A撤销之后，覆盖了事务B 提交的数据。

（又叫做回滚丢失）

④幻读：在事务A多次读取过程中，事务B对数据进行了新增操作，导致事务A多次读取的数据不一致。（幻读和不可重复读的区别在于：不可重复读是针对数据的更新操作，只要再记录上加上写锁，就可避免，幻读是对记录的insert操作，要禁止幻读必须加上全局的写锁，比如在表上加写锁。）

→SQL92定义的4中隔离级别：

①未提交读（Read uncommitted）：写操作加写锁，读操作不加锁。禁止丢失更新（回滚

丢失），但是会出现所有其他数据并发问题。

②提交读（Read committed）：写操作加写锁，读操作加读锁。禁止丢失更新（回滚丢失）

和脏读，是大多数关系型数据库的默认隔离级别。

③可重复读（Read repeatable）：对于读操作加读锁到事务结束，其他事务的更新操作

只能等到事务结束之后进行。和提交读的区别在于，提交读的读操作时加读锁到本次读操作结束，可重复读的粒度更大。禁止两类丢失更新，禁止脏读和不可重复读，但可能出现幻读。

④序列化（Serializable）：读操作加表级读锁至事务结束。可禁止幻读。

→隔离级别越高，数据一致性的保证越强，但同时数据库的并发能力也越差。

2.存储过程的作用？

存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。

存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

可以出于任何使用SQL 语句的目的来使用存储过程，它具有以下优点：

可以在单个存储过程中执行一系列SQL 语句。

可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL 语句快。

3.查询的优化

1）.根据查询条件建立合适的index

2）.因为SQL是从右向左解析，多表查询时，记录数少的表放在右边

3）.多个条件时，收敛快的条件放在右边。

4）.避免使用复杂的集合函数，象not in等。

5）.避免在条件中对字段进行函数操作

6）.尽量避免使用select *，应该写出需要查询的字段

7）.在java中尽量使用preparestatement执行sql，从而共享执行计划"

4.如何取一个没有排序的查询结果中的前10条记录？

select fielda from tablea where rownum<=10;

5.问：如何取一个有排序的查询结果中的前10条记录？

select fielda from (select fielda from tablea order by fieldb ) where rownum<=10;

错误写法：select fielda from tablea where rownum<=10 order by fieldb;

6.如何取一个没有排序的查询结果中的第11条到第20条记录？

select fielda from

(select fielda,rownum rn from tablea where rownum<=20)

where rn>=11;

7.如何取一个有排序的查询结果中的第11条到第20条记录？

select fielda from (select fielda ,rownum rn from (select fielda from tablea order by fieldb ) where rownum<=20) where rn>=11;

8.数据库设计中，主键的作用？

主键的作用保持数据表记录的唯一性,建立主键索引和作为外键使用.使用主键与外键的配对来表示实体间的连接.