SQL笔试常识

1.关系型数据库中表S结构为：S (SN, CN, grade), 其中SN为学生名，CN为课程名。前两者均为字符型，grade为数值型。
问：更正王二的化学成绩为85分。
答：
UPDATE S SET grade = 85
WHERE SN = ‘王二’
AND CN = ‘化学’
点：数值型数据不用加引号。

2.关系型数据模型中，（二维）表格可以被称为关系模式。

3.内存数据丢失与系统相关，外存上的数据丢失和存储介质相关。

4.NOSQL数据库：

• 键值Key-Value: Redis, Oracle BDB, Google BigTable, Amazon DynamoDB
• 列存储: Cassandra, HBase
• 文档型: MongoDb, SequoiaDB
• 图形Graph: Neo4J, InfoGrid

5.在MySQL中有存储过程proc1（员工编号，月份）用来查询员工的工资（均为字符类型）
方法1: call Proc1 ( ‘emp001’, ‘201601’);
方法2: call Proc1 ( ‘emp001’, null);
点：1.exec是sql server的存储调用方式。2.call调用时必须要有参数或者为null。

6.SQL语句可以嵌入在PHP中，也可以像命令一样执行。

7.子查询是嵌入到另一个查询语句之中的查询语句。

8.数据库系统一般由数据库、数据库管理系统、应用系统、数据库管理员构成。

9.1模式与外模式映射：模式描述的是数据的全局逻辑结构，外模式描述的是数据的局部逻辑结构。当模式改变时，数据库管理员对各个外模式/模式映射作出的相应改变，可以保证数据与程序的逻辑独立性。

9.2模式与内模式映射：数据库只有一个内模式，模式/内模式映射定义了数据全局逻辑结构与存储结构之间的对应关系。当数据库存储结构改变时，数据库管理员对模式/内模式映射作出的相应改变，可以使应用程序保持不变。从而保证了数据与程序的物理独立性。

10.数据库ACID性质：

• 原子性(Atomicity)：事务是一个不可分割的工作单位，这组操作要么全部发生，否则全部不发生。
• 一致性(Consistency)：数据库在事务操作前和事务处理后，其中的数据必须都满足业务规则约束。
• 隔离性(Isolation)：数据库允许多个并发事务同时对齐数据进行写和修改的能力，但要求系统必须保证事务不受其他并发执行但事务的影响。
• 持久性(Durability)：事务处理结束后，它对数据库的改变必须是永久的，即便是数据库发生故障也不会丢失。

11.数据库事务的隔离级别有4个，由低到高依次为：1.Read uncommitted；2.Read committed；3.Repeatable read；4.Serialisable。
（其中1可以解决“脏读”；1和2可以解决“不可重复读”；1, 2, 3可以解决“幻读”。）

12.候选键(candidate key)：不含有多余属性的超键称为候选键。在关系中能唯一标识元组的属性集，称为关系模式的超键盘。

13.数据库实施阶段：

• 数据载入：建立库结构和加载数据
• 系统调试：编码和调试

14.通过视图用户只能查询和修改他们所能见到的数据，因此视图可以保证数据的安全性。

15.数据库系统的存储模式如有改变，概念模式无需改动。

16.SQL中LIMIT后的第一个参数是输出记录的初始位置，第二个参数偏移量，偏移多少，输出的条目就是多少。如：

#执行
SELECT * FROM ids LIMIT 10, 3
#输出
id
---
11
12
13

17.数据库系统的数据独立性体现在:不会因为数据存储结构与数据逻辑结构的变化而影响应用程序。

18.数据库系统中：1.一个内模式。2.多个外模式。3.外模式是（概念）模式的一个子集。

19.在授权的定义中数据对象的范围越小，授权子系统就越灵活。

20.触发器(trigger) 是指事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行。1.它可以强化约束，来维护数据的完整性和一致性（跟踪数据库内的操作从而不允许未经许可的更新和变化）；2.也可以联级运算（某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发）。

触发器包含三个要素，分别为：

• 事件类型：增删改，即insert、delete和update；
• 触发时间：事件类型前和后，即before和after；
• 触发对象：表中的每一条记录（行），即整张表。

注: 每张表只能拥有一种触发时间的一种事件类型的触发器，即每张表最多可以拥有 6 种触发器。

21.存储过程是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

优点：

• 减少网络通信量：如果存储过程包含上百行SQL 语句，那么存储过程性能绝对比一条一条的调用SQL 语句要高得多。
• 执行速度更快：首先，存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。
• 更强的适应性： 由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。
• 分布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

缺点：

• 如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则仍需要更新程序集中的代码以添加参数、更新GetValue() 调用，等等，这时候估计比较繁琐了。
• 可移植性差：由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。

22.索引是一种特殊的查询表。它可以加速对数据的检索。与书的目录相似，索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。（唯一、不为空、经常被查询的字段适合建索引）

23.索引类型：
Single column 单行索引
Concatenated 多行索引

Unique 唯一索引
NonUnique 非唯一索引

Function-based 函数索引
Domain 域索引 物理上：

Partitioned 分区索引
NonPartitioned 非分区索引

B-tree ：
Normal 正常型B树
Rever Key 反转型B树 Bitmap 位图索引

24.锁在所以的DBMS中是实现事务的关键，锁可以保证事务的完整性和并发性。

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

26.主键在本表中是唯一的、不可唯空的，外键可以重复可以唯空；外键和另一张表的主键关联，不能创建对应表中不存在的外键。

27.在数据库中查询语句速度很慢，如何优化？

• 建索引。优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,把数据量大的表排在前面。
• 减少表之间的关联
• 简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据
• 尽量用PreparedStatement来查询，不要用Statement。

28.数据库三范式：

• 第一范式（1NF）：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式。数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。
• 第二范式（2NF）：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。
• 满足第三范式（3NF）：要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，帖子表中只能出现发帖人的id，而不能出现发帖人的id还同时出现发帖人姓名，否则就是数据冗余。
  所以第三范式具有如下特征：
  1. 每一列只有一个值；
  2. 每一行都能区分；
  3. 每一个表都不包含其他表已经包含的非主关键字信息。

29.order by与group by的区别

• order by 排序查询、asc升序、desc降序
• group by 分组查询、having 只能用于group by子句、作用于组内。having条件子句可以直接跟函数表达式; 使用group by 子句的查询语句需要使用聚合函数。

30.查看MySQL表结构等命令：
desc 表名；
show columns from 表名；
describe 表名；
show create table 表名；
use information_schema select * from columns where table_name = ‘表名’；

31.关系型数据中，字段——属性、行——元组、二维表——关系模式、表格形式——实体类型和实体间等联系是关系类型等特征。

32.1.脏读：
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

32.2.不可重复读：
是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）

例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。

32.3.幻读:
是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。

32.4丢失修改：
丢失数据就是两个事物同时对数据读取并修改，事物2的修改掩盖了事物1 的修改。

33.数据的独立性是数据库系统的最基本的特征之一 。数据独立性是指应用程序和数据结构之间相互独立, 互不影响。在三层模式体系结构中数据独立性是指数据库系统在某一层次模式上的改变不会使它的上一层模式也发生改变的能力。正是三级模式间的两层映像保证了数据库系统中的数据具有较高的数据独立性。数据独立性包括数据逻辑独立性和数据物理独立性。

34.关系模式R中的属性全部是主属性，则R的最高范式必定是3NF；2NF消除了非主属性对键的部分函数依赖；BCNF在3NF的基础上消除了主属性对键的传递函数依赖。

35.比较Statement和PreparedStatement：

1. PreparedStatement有预编译，而 Statement如果执行多变则需要多次编译SQL，因此 PreparedStatement 比 Statement 效率高。
2. PreparedStatement PreparedStatement 接口继承 Statement，PreparedStatement 实例包含已编译的 SQL 语句，所以其执行速度要快于 Statement 对象。
3. 作为 Statement 的子类，PreparedStatement 继承了 Statement 的所有功能。三种方法 execute、executeQuery 和 executeUpdate 已被更改以使之不再需要参数。
4. 尽量使用 PreparedStatement 代替 Statement：1.代码的可读性和可维护性。虽然用 PreparedStatement 来代替 Statement 会使代码多出几行，但这样的代码无论从可读性还是可维护性上来说都比直接用 Statement 的代码高很多档次。2. 最重要的一点是极大地提高了安全性