Java面试题--数据库

 

存储过程

CREATE  PROCEDURE  num_from_employee (IN emp_id INT, OUT count_num INT )  

          READS SQL DATA  

          BEGIN  

              SELECT  COUNT(*)  INTO  count_num  

              FROM  employee  

              WHERE  d_id=emp_id ;  

          END

上述代码中，存储过程名称为num_from_employee；输入变量为emp_id；输出变量为count_num。SELECT语句从employee表查询d_id值等于emp_id的记录，并用COUNT(*)计算d_id值相同的记录的条数，最后将计算结果存入count_num中。

 

函数

CREATE  FUNCTION  name_from_employee (emp_id INT )  

          RETURNS VARCHAR(20)  

          BEGIN  

              RETURN  (SELECT  name  

              FROM  employee  

              WHERE  num=emp_id );  

          END

触发器

CREATE TRIGGER trigger_t1

AFTER INSERT ON table2

FOR EACH ROW

UPDATE table1 SET table1.count=table1.count+1;

创建索引

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

 

什么是事务？什么是锁？

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

 锁：在所以的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

 

什么是回滚？

    为了保证在应用程序、数据库或系统出现错误后，数据库能够被还原，以保证数据库的完整性 ，所以需要进行回滚。回滚（rollback）就是在事务提交之前将数据库数据恢复到事务修改之前数据库数据状态。

 

数据库的乐观锁和悲观锁是什么？

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

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

 

什么是封锁？封锁的基本类型有哪几种？含义是什么？

 

所谓封锁就是当一个事务在对某个数据对象（可以是数据项、记录、数据集、以至整个数据库）进行操作之前，必须获得相应的锁，以保证数据操作的正确性和一致性。

基本的封锁类型有两种：排它锁和共享锁

（1）排它锁：排它锁又称写锁，简称为X锁，其采用的原理是禁止并发操作。 

（2）共享锁：共享锁又称读锁，,简称为S锁，其采用的原理是允许其他用户对同一数据对象进行查询，但不能对该数据对象进行修改。

 

事务四大特性

原子性，要么执行，要么不执行

隔离性，所有操作全部执行完以前，其它会话不能看到过程

一致性，事务前后，数据总额一致

持久性，一旦事务提交，对数据的改变就是永久的

 

数据库隔离级别，每个级别会引发什么问题，mysql默认是哪个级别

脏读：事务B读取事务A还没有提交的数据

不可重复读：两次事务读的数据不一致

幻读:事务A修改了数据，事务B也修改了数据，这时在事务A看来，明明修改了数据，昨不一样，

隔离级别 读数据一致性 脏读 不可重复读 幻读

未提交读 最低级别 是 是 是

已提交读 语句级 否 是 是

事务级 事务级 否 否 是

串行化 最高级别 否 否 否

 

如何维护数据库的完整性和一致性？

尽可能使用约束，如check,主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

 

MYSQL的两种存储引擎区别（事务、锁级别等等），各自的适用场景

MYISAM 不支持事务，不支持外键，表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描

INNODB 支持事务，支持外键，行锁，查表总行数时，全表扫描

 

Oracle和Mysql的区别？

1）库函数不同。

2）Oracle是用表空间来管理的，Mysql不是。 

3）显示当前所有的表、用户、改变连接用户、显示当前连接用户、执行外部脚本的语句的不同。

4）分页查询时候时候，mysql用limit oracle用rownum

 

数据库的优化

1.sql尽量使用索引

2.对sql语句优化

子查询变成left join

limit 分布优化，先利用ID定位，再分页

or条件优化，多个or条件可以用union all对结果进行合并（union all结果可能重复）

不必要的排序

where代替having,having 检索完所有记录，才进行过滤

避免嵌套查询

对多个字段进行等值查询时，联合索引

 

索引分类

普通索引：最基本的索引，没有任何限制

唯一索引：与”普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引：它是一种特殊的唯一索引，不允许有空值。

全文索引：针对较大的数据，生成全文索引很耗时好空间。

组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则

 

数据库的主从复制

默认异步复制，容易造成主库数据和从库不一致

一个数据库为Master,一个数据库为slave,通过Binlog日志

slave两个线程，一个线程去读master binlog日志，写到自己的中继日志

一个线程解析日志，执行sql

master启动一个线程

给slave传递binlog日志

 

半同步复制

只有把master发送的binlog日志写到slave的中继日志，这时主库

才返回操作完成的反馈，性能有一定降低

 

并行复制

slave 多个线程去请求binlog日志

 

varchar和char的使用场景

用来存储字符

varchar适用字符长度经常变的

char适用字符长度固定的

 

Varchar2和varchar有什么区别？

Char的长度是固定的，而varchar2的长度是可以变化的，比如，存储字符串“abc”对于char(20)，表示你存储的字符将占20个字节，包含17个空，而同样的varchar2（20）只占了3个字节，20只是最大值，当你存储的字符小于20时，按实际长度存储。 char的效率要被varchar2的效率高。 目前varchar是varchar2的同义词，工业标准的varchar类型可以存储空字符串，但是oracle不能这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型varchar2,这个类型不是一个标准的varchar，他将在数据库中varchar列可以存储空字符串的特性改为存储null值，如果你想有向后兼容的能力，oracle建议使用varchar2而不是varchar

 

数据库连接池的作用

维护一定数量的连接，减少创建连接的时间

更快的响应时间

统一的管理

 

数据库三范式

1NF 属性不可分

2NF 非主键属性，完全依赖于主键属性

3NF 非主键属性无传递依赖

 

关系型数据库和非关系型数据库区别

类型 特性

关系型数据库mysql 

1、关系型数据库，是指采用了关系模型来组织数据的数据库,

2、关系型数据库的最大特点就是事务的一致性；

 

优点

1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；

2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；

3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；

4、支持SQL，可用于复杂的查询。

缺点

1、为了维护一致性所付出的巨大代价就是其读写性能比较差；

2、固定的表结构；

3、不支持高并发读写需求；

4、不支持海量数据的高效率读写；

 

非关系型数据库 特性

HBase 

1、使用键值对存储数据；

2、分布式；

优点

无需经过sql层的解析，读写性能很高

基于键值对，数据没有耦合性，容易扩展

存储数据的格式：nosql的存储格式是key,value形式

缺点

不支持事务

不提供sql支持

 

触发器的作用？

触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

 

什么是存储过程？用什么来调用？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。 

调用： 

1）可以用一个命令对象来调用存储过程。 2）可以供外部程序调用，比如：java程序。

 

存储过程的优缺点？

优点： 

1）存储过程是预编译过的，执行效率高。 2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。 3）安全性高，执行存储过程需要有一定权限的用户。 4）存储过程可以重复使用，可减少数据库开发人员的工作量。

 缺点：移植性差

 

存储过程与函数的区别

 

存储过程	函数
用于在数据库中完成特定的操作或者任务（如插入、删除等）     程序头部声明用procedure     程序头部声明时不需描述返回类型     可以使用in/out/in out 三种模式的参数     可作为一个独立的PL/SQL语句来执行     可以通过out/in out 返回零个或多个值     SQL语句(DML 或SELECT)中不可调用存储过程	用于特定的数据（如选择） 程序头部声明用function 程序头部声明时要描述返回类型，而且PL/SQL块中至少要包括一个有效的return语句 可以使用in/out/in out 三种模式的参数 不能独立执行，必须作为表达式的一部分调用 通过return语句返回一个值，且改值要与声明部分一致，也可以是通过out类型的参数带出的变量 SQL语句(DML 或SELECT)中可以调用函数

 

索引的作用？和它的优点缺点是什么？

索引就一种特殊的查询表，数据库的搜索可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。

缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

 

什么样的字段适合建索引

唯一、不为空、经常被查询的字段

 

什么叫视图？游标是什么？

视图：是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。

 游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

 

使用游标的基本步骤有哪些？

1.声明游标2.打开游标3.读取游标中的数据4.关闭游标5.释放游标

 

视图的优缺点

优点： 1）对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。 2 ）用户通过简单的查询可以从复杂查询中得到结果。 3 ）维护数据的独立性，试图可从多个表检索数据。 4 ）对于相同的数据可产生不同的视图。 

缺点： 性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，那么就无法更改数据

 

union和union all有什么不同?

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。 UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。 从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL。

 

order by与group by的区别

order by 排序查询、asc升序、desc降序 

group by 分组查询、having 只能用于group by子句、作用于组内，having条件子句可以直接跟函数表达式。使用group by 子句的查询语句需要使用聚合函数。

 

commit在哪里会运用

oracle的commit就是DML语句提交数据（这里是释放锁不是锁表），在未提交前你前面的操作更新的都是内存，没有更新到物理文件中。 执行commit从用户角度讲就是更新到物理文件了，事实上commit时还没有写date file，而是记录了redo log file，要从内存写到data物理文件，需要触发检查点，由DBWR这个后台进程来写，这里内容有点多的，如果不深究的话你就理解成commit即为从内存更新到物理文件。

 

什么是内连接，左外连接，右外连接？

内联接（Inner Join）：匹配2张表中相关联的记录。

左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示。

右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。

在判定左表和右表时，要根据表名出现在Outer Join的左右位置关系。

 

Delete、truncaate、drop都是删除语句，它们有什么分别？

1).delete 属于DML语句，删除数据，保留表结构，需要commit，可以回滚，如果数据量大，很慢。

2).truncate 属于DDL语句，删除所有数据，保留表结构，自动commit，不可以回滚，一次全部删除所有数据，速度相对较快。

3).Drop属于 DDL语句，删除数据和表结构,不需要commit，删除速度最快。

 

 

什么是内存泄漏？

答：一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字new等创建对象时，就从堆中为它分配一块内存，使用完后程序调用free或者delete释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。

 

超键、候选键、主键、外键分别是什么？

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：是最小超键，即没有冗余元素的超键。

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键：在一个表中存在的另一个表的主键称此表的外键。

 

优化SQL语句的一般步骤

1.通过show status命令了解各种SQL的执行频率

2.定位执行效率较低的SQL语句

3.通过explain分析低效SQL的执行计划

4.确定问题并采取相应的优化措施

数据库会死锁吗

 

产生死锁的原因主要是：

（1）系统资源不足。 

（2） 进程运行推进的顺序不合适。 

（3）资源分配不当等。

 

mysql 怎么解决死锁。

1）重启数据库（谁用谁知道） 

2）杀掉抢资源的进程： 

先查哪些进程在抢资源：SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 

杀掉它们：Kill trx_mysql_thread_id；

 

mysql 中 in 和 exists 区别。

如果查询的两个表大小相当，那么用in和exists差别不大。 

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

not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。 

1.EXISTS只返回TRUE或FALSE，不会返回UNKNOWN。

2.IN当遇到包含NULL的情况，那么就会返回UNKNOWN。

 

String能被继承吗？为什么？

不可以，因为String类有final修饰符，而final修饰的类是不能被继承的，实现细节不允许改变。

 

Sql

https://www.cnblogs.com/diffrent/p/8854995.html

https://www.cnblogs.com/zqm1/p/6559757.html

MongoDB 将数据存储为一个文档，数据结构由键值(key=>value)对组成。MongoDB 文档类似于 JSON 对象。字段值可以包含其他文档，数组及文档数组。

MongoDB优势与劣势

优势：

1、在适量级的内存的MongoDB的性能是非常迅速的，它将热数据存储在物理内存中，使得热数据的读写变得十分快。

2、MongoDB的高可用和集群架构拥有十分高的扩展性。

3、在副本集中，当主库遇到问题，无法继续提供服务的时候，副本集将选举一个新的主库继续提供服务。

4、MongoDB的Bson和JSon格式的数据十分适合文档格式的存储与查询。

劣势：

1、 不支持事务操作。MongoDB本身没有自带事务机制，若需要在MongoDB中实现事务机制，需通过一个额外的表，从逻辑上自行实现事务。

2、 应用经验少，由于NoSQL兴起时间短，应用经验相比关系型数据库较少。

3、MongoDB占用空间过大。

 

一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把 MySQL 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15？

一般情况下，我们创建的表的类型是 InnoDB ，如果新增一条记录（不重启 MySQL 的情况下），这条记录的 ID 是18 ；但是如果重启 MySQL 的话，这条记录的 ID 是 15 。因为 InnoDB 表只把自增主键的最大 ID 记录到内存中，所以重启数据库或者对表 OPTIMIZE 操作，都会使最大 ID 丢失。

但是，如果我们使用表的类型是 MyISAM ，那么这条记录的 ID 就是 18 。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里面，重启 MYSQL 后，自增主键的最大 ID 也不会丢失

索引的类型？

索引，都是实现在存储引擎层的。主要有六种类型：

1、普通索引：最基本的索引，没有任何约束。

2、唯一索引：与普通索引类似，但具有唯一性约束。

3、主键索引：特殊的唯一索引，不允许有空值。

4、复合索引：将多个列组合在一起创建索引，可以覆盖多个列。

5、外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。

6、全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。

 

MySQL 索引的“使用”注意事项？

1、应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

注意，column IS NULL 也是不可以使用索引的。

2、应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT id FROM t WHERE num = 10 OR num = 20 。

3、尽量避免在 WHERE 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

4、应尽量避免在 WHERE 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

5、不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

6、复合索引遵循前缀原则。

7、如果 MySQL 评估使用索引比全表扫描更慢，会放弃使用索引。如果此时想要索引，可以在语句中添加强制索引。

8、列类型是字符串类型，查询时一定要给值加引号，否则索引失效。

9、LIKE 查询，% 不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引

 

事务的并发问题？

实际场景下，事务并不是串行的，所以会带来如下三个问题：

1、脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。

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

 

MySQL 数据库 CPU 飙升到 500% 的话，怎么处理？

当 CPU 飙升到 500% 时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果此时是 IO 压力比较大，可以使用 iostat 命令，定位是哪个进程占用了磁盘 IO 。

如果是 mysqld 造成的，使用 show processlist 命令，看看里面跑的 Session 情况，是不是有消耗资源的 SQL 在运行。找出消耗高的 SQL ，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。一般来说，肯定要 kill 掉这些线程(同时观察 CPU 使用率是否下降)，等进行相应的调整(比如说加索引、改 SQL 、改内存参数)之后，再重新跑这些 SQL。

也可以查看 MySQL 慢查询日志，看是否有慢 SQL 。

也有可能是每个 SQL 消耗资源并不多，但是突然之间，有大量的 Session 连进来导致 CPU 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。