数据库知识点查漏补缺

电玩女神 2022-06-07 03:49 204阅读 0赞

### 独占锁和共享锁的区别 ###

*  **共享锁（S锁）：**如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排它锁。获准共享锁的事务只能读数据，不能修改数据。
 *  **排他锁（X锁）：**如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
 *  共享锁下其它用户可以并发读取，查询数据。但不能修改，增加，删除数据。资源共享.

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

### sql中的 IF 条件语句的用法 ###

IF( expr1 , expr2 , expr3 )
    # expr1 的值为 TRUE（即不为0），则返回值为 expr2 
    # expr2 的值为FALSE，则返回值为 expr3
    
    SELECT IF(TRUE,1+1,1+2);
    -> 2
    
    SELECT IF(FALSE,1+1,1+2);
    -> 3
    
    SELECT IF(STRCMP("111","222"),"不相等","相等");
    -> 不相等

### IFNULL 表达式 ###

IFNULL( expr1 , expr2 )
    # 在 expr1 的值不为 NULL的情况下都返回 expr1，否则返回 expr2，如下：
    
    SELECT IFNULL(NULL,"11");
    -> 11
    
    SELECT IFNULL("00","11");
    -> 00

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

### 嵌入式SQL介绍 ###

> [http://blog.csdn.net/prayforever/article/details/2778495][http_blog.csdn.net_prayforever_article_details_2778495]

在SQL语句中引用宿主变量时，为了区别数据库中变量，宿主变量前须加“：”，它可与数据库中变量同名。在宿主语言语句中，宿主变量可与其它变量一样使用，不须加冒号。当宿主变量的数据类型与数据库中不一致时，由系统按约定转换。

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

### 聚集索引 ###

聚集索引是一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员 ID 列 emp\_id 查找特定雇员的最快速的方法，是在 emp\_id 列上创建聚集索引或 PRIMARY KEY 约束。

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

### 内连接外连接 ###

内连接返回的结果集是两个表中所有相匹配的数据，不包含没有匹配的行。

外连接有三种：左外连接，右外连接，全外连接。外连接不仅包含符合连接条件的行，还包含左表（左外连接）、右表（右外连接）或两个表（全外连接）中的所有数据行。对于没有匹配的行就用NULL值来填充。

因此，外连接中：既包含相匹配的行也包括不相匹配的行、不相匹配的行就用NULL值填充、外连接中也可以只有不匹配的行。

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

### 什么是存储过程？有哪些优缺点？ ###

存储过程是一些预编译的SQL语句。

更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

*  存储过程是一个预编译的代码块，执行效率比较高
 *  一个存储过程替代大量T\_SQL语句 ，可以降低网络通信量，提高通信速率
 *  可以一定程度上确保数据安全

如果你对存储过程不熟悉，建议阅读：[存储过程详解-博客园][-]

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

### 索引是什么？有什么作用以及优缺点？ ###

索引是对数据库表中一或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构

你也可以这样理解：索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

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

*  索引加快数据库的检索速度
 *  索引降低了插入、删除、修改等维护任务的速度
 *  唯一索引可以确保每一行数据的唯一性
 *  通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
 *  索引需要占物理和数据空间

如果你对索引还不太熟悉，建议阅读：[漫谈数据库索引][Link 1]

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

### 什么是事务？ ###

事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

如果你对索引还不太熟悉，建议阅读：[数据库事务 - Mr. David 专栏][- Mr. David]

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

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

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

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

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

如果你对两种锁还不太熟悉，建议阅读：[深入理解乐观锁与悲观锁][Link 2]

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

### 使用索引查询一定能提高查询的性能吗？为什么 ###

通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.

索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:

*  基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
 *  基于非唯一性索引的检索

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

### 简单说一说drop、delete与truncate的区别 ###

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

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

如果你对这三者的用法还不太熟悉，建议阅读： [drop、truncate和delete的区别][drop_truncate_delete]

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

### drop、delete与truncate分别在什么场景之下使用？ ###

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

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

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

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

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

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

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

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

### 什么是视图？以及视图的使用场景有哪些？ ###

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

*  只暴露部分字段给访问者，所以就建一个虚表，就是视图。
 *  查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异

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

### 说一说三个范式 ###

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在”A → B → C”的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y

如果你对三个还不太了解，建议阅读：[解释一下关系数据库的第一第二第三范式？][Link 3]

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

参考：[https://zhuanlan.zhihu.com/p/23713529][https_zhuanlan.zhihu.com_p_23713529]

[http_blog.csdn.net_prayforever_article_details_2778495]: http://blog.csdn.net/prayforever/article/details/2778495
[-]: http://www.cnblogs.com/knowledgesea/archive/2013/01/02/2841588.html
[Link 1]: http://kb.cnblogs.com/page/45712/
[- Mr. David]: http://link.zhihu.com/?target=http://blog.csdn.net/zdwzzu2006/article/details/5947062
[Link 2]: http://link.zhihu.com/?target=http://www.open-open.com/lib/view/open1452046967245.html
[drop_truncate_delete]: http://link.zhihu.com/?target=http://blog.csdn.net/ws0513/article/details/49980547
[Link 3]: https://www.zhihu.com/question/24696366
[https_zhuanlan.zhihu.com_p_23713529]: https://zhuanlan.zhihu.com/p/23713529