第17章数据库与SQL语言

第17章

第17章

数据库与SQL语言

数据库面试题主要包括范式、事物、存储过程、SQL语言，以及索引等诸方面。

17.1　数据库理论

面试例题1：设有关系R(S,D,M)，其函数依赖集F={S→D,D→M}。则关系R至多满足__。［美国某著名搜索引擎公司面试题］

A．1NF

B．2NF

C．3NF

D．BCNF

解析：数据库模式的4个范式问题。

1NF：第一范式。如果关系模式R的所有属性的值域中每一个值都是不可再分解的值，则称R属于第一范式模式。如果某个数据库模式都是第一范式的，则称该数据库模式属于第一范式的数据库模式。

第一范式的模式要求属性值不可再分裂成更小部分，即属性项不能是属性组合或由组属性组成。

2NF：第二范式。如果关系模式R为第一范式，并且R中每一个非主属性完全函数依赖于R的某个候选键，则称R为第二范式模式。如果某个数据库模式中每个关系模式都是第二范式的，则称该数据库模式属于第二范式的数据库模式。（注：如果A是关系模式R的候选键的一个属性，则称A是R的主属性，否则称A是R的非主属性。）

3NF：第三范式。如果关系模式R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R是第三范式的模式。如果某个数据库模式中的每个关系模式都是第三范式，则称R为3NF的数据库模式。

BCNF：BC范式。如果关系模式R是第一范式，且每个属性都不传递依赖于R的候选键，那么称R为BCNF的模式。

4NF：第四范式。设R是一个关系模式，D是R上的多值依赖集合。如果D中成立非平凡多值依赖X→→Y时，X必是R的超键，那么称R是第四范式的模式。

上题属于传递依赖，所以至多满足第二范式。

答案：B

面试例题2：存储过程和函数的区别是什么？［美国某著名搜索引擎公司面试题］

答案：存储过程是用户定义的一系列SQL语句的集合，涉及特定表或其他对象的任务，用户可以调用存储过程。而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值，并且不涉及特定用户表。

面试例题3：What is database transaction?（什么是数据库事务？）

答案：数据库事务是指作为单个逻辑工作单元执行的一系列操作，这些操作要么全做要么全不做，是一个不可分割的工作单位。

事务的开始与结束可以由用户显式控制。如果用户没有显式地定义事务，则由DBMS按默认规定自动划分事务。事务具有原子性、一致性、独立性及持久性等特点。

●　事务的原子性是指一个事务要么全部执行，要么不执行。也就是说一个事务不可能只执行了一半就停止了。比如你从银行取钱，这个事务可以分成两个步骤（1）存折减款，（2）拿到现金。不可能存折钱少了，而钱却没拿出来。这两步必须同时完成，要么就都不完成。

●　事务的一致性是指事务的运行并不改变数据库中数据的一致性。例如，完整性约束了a+b=10，一个事务改变了a，那么b也应该随之改变。

●　事务的独立性是指两个以上的事务不会出现交错执行的状态。因为这样可能会导致数据不一致。

●　事务的持久性是指事务运行成功以后，就系统的更新是永久的。不会无缘无故的回滚。

面试例题4：游标的作用是什么，如何知道游标已经到了最后？［中国某著名计算机金融软件公司面试题］

答案：游标用于定位结果集的行。通过判断全局变量@@FETCH_ STATUS可以判断其是否到了最后。通常此变量不等于0表示出错或到了最后。

面试例题5：触发器分为事前触发和事后触发，这两种触发有何区别？语句级触发和行级触发有何区别？［美国某著名计算机软件公司面试题］

答案：事前触发器运行于触发事件发生之前，而事后触发器运行于触发事件发生之后。语句级触发器可以在语句执行前或后执行，而行级触发在触发器所影响的每一行触发一次。

面试例题6：什么叫做SQL注入式攻击，如何防范？［中国台湾某著名杀毒软件公司面试题］

答案：所谓SQL注入式攻击，就是攻击者把SQL命令插入到Web表单的输入域或页面请求的查询字符串中，欺骗服务器执行恶意的SQL命令。在某些表单中，用户输入的内容直接用来构造（或者影响）动态SQL命令，或作为存储过程的输入参数，这类表单特别容易受到SQL注入式攻击。

防范SQL注入式攻击闯入并不是一件特别困难的事情，只要在利用表单输入的内容构造SQL命令之前，把所有输入内容过滤一番就可以了。过滤输入内容可以按多种方式进行。

●　替换单引号，即把所有单独出现的单引号改成两个单引号，防止攻击者修改SQL命令的含义。

●　删除用户输入内容中的所有连字符，防止攻击者顺利获得访问权限。

●　对于用来执行查询的数据库账户，限制其权限。用不同的用户账户执行查询、插入、更新、删除操作。由于隔离了不同账户可执行的操作，因而也就防止了原本用于执行SELECT命令的地方却被用于执行INSERT、UPDATE或DELETE命令。

●　用存储过程来执行所有的查询。SQL参数的传递方式将防止攻击者利用单引号和连字符实施攻击。此外，它还使得数据库权限可以被限制到只允许特定的存储过程执行，所有的用户输入必须遵从被调用的存储过程的安全上下文，这样就很难再发生注入式攻击了。

●　检查用户输入的合法性，确信输入的内容只包含合法的数据。数据检查应当在客户端和服务器端都执行。之所以要执行服务器端验证，是为了弥补客户端验证机制脆弱的安全性。在客户端，攻击者完全有可能获得网页的源代码，修改验证合法性的脚本（或者直接删除脚本），然后将非法内容通过修改后的表单提交给服务器。因此，要保证验证操作确实已经执行，唯一的办法就是在服务器端也执行验证。

●　将用户登录名称、密码等数据加密保存。加密用户输入的数据，然后再将它与数据库中保存的数据比较，这相当于对用户输入的数据进行了“消毒”处理。用户输入的数据不再对数据库有任何特殊的意义，从而也就防止了攻击者注入SQL命令。

●　检查提取数据的查询所返回的记录数量。如果程序只要求返回一个记录，但实际返回的记录却超过一行，那就当做出错处理。

面试例题7：Explain the difference between clustered and non-clustered indexes. How does index affect the query?（解释聚集索引和非聚集索引之间的区别）

答案：经典教科书对聚集索引的解释是：聚集索引的顺序就是数据的物理存储顺序，而对非聚集索引的解释是索引顺序与数据物理排列顺序无关。正是因为如此，所以一个表最多只能有一个聚集索引。

在SQL Server中，索引是通过二叉树的数据结构来描述的，我们可以这么理解聚集索引：索引的叶节点就是数据节点。而非聚集索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

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

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

使用非聚集索引，非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。

有索引就一定检索得快吗？答案是否定的。有些时候用索引还不如不用索引快。比如说我们要检索表中的所有8000条记录，如果不用索引，需要访问8000条×1000字节/8K字节=1000个页面，如果使用索引的话，首先检索索引，访问8000条×10字节/8K字节=10个页面得到索引检索结果，再根据索引检索结果去对应数据页面，由于是检索所有数据，所以需要再访问8000条×1000字节/8K字节=1000个页面将全部数据读取出来，一共访问了1010个页面，这显然不如不用索引快。

17.2　SQL语言

面试例题1：找出表ppp里面num最小的数，不能使用min函数。［中国某著名软件外包企业2004年面试题］

答案：

alt

或者：

alt

面试例题2：找出表ppp里面最小的数，可以使用min函数。［中国某著名软件外包企业2004年面试题］

答案：

alt

面试例题3：选择表ppp2中num重复的记录。［中国某著名软件外包企业2004年面试题］

答案：

alt

面试例题4：写出复制表、拷贝表和四表联查的SQL语句。［中国某著名软件外包企业2004年面试题］

答案：

复制表（只复制结构，源表名：A，新表名：B）：

alt

拷贝表（拷贝数据，源表名：A，新表名：B）：

alt

四表联查：

alt

面试例题5：在SQL Server中如何用SQL语句建立一张临时表？［中国某著名软件外包企业2004年面试题］

答案：

alt

注意，临时表要在表名前面加“#”。

面试例题6：有数据表A，有一个字段LASTUPDATETIME，是最后更新的时间，如果要查最新更新过的记录，如何写SQL语句？［中国某著名软件外包企业2008年面试题］

答案：

alt

面试例题7：Let's say we have a database with 1 one-column table. It contains 1000 same records. Could you please give at least 1 solution to help get records between line 5 and 7. No line number, row id or index etc.（有一个数据库，只有一个表，包含着1000个记录，你能想出一种解决方案来把第5到第7行的记录取出来么？不要使用航标和索引。）［德国某著名软件咨询企业2005年面试题］

答案：

建立数据库：

alt

删除数据库：

alt

提取数据：

alt

或者：

alt

或者：

alt

面试例题8：要查数据表中第30到40条记录，有字段ID，但是ID并不连续。如何写SQL语句？［德国某著名软件咨询企业2007年面试题］

答案：

SQL语句如下：

alt

面试例题9：请问下列语句是否可以正确运行，为什么？［德国某著名软件咨询企业2007年面试题］

alt

答案：不能。

alt

这一句返回的是一个表结果集，而update语句少了条件值。返回的是结果集，更新的结果并没有指定，会出现非一对一情况。

17.3　SQL语言客观题

面试例题1：Which statement shows the maximum salary paid in each job category of each department?（下面哪个SQL语句描述了每一个部门的每个工种的工资最大值？）［中国某著名计算机金融软件公司2005年面试题］

A．select dept_id, job_cat,max(salary) from employees where salary > max (salary);

B．select deptid, job_cat,max(salary) from employees group by dept id, job_cat;

C．select dept_id, job_cat,max(salary) from employees;

D．select dept_id, job_cat,max(salary) from employees group by dept_id;

E．select deptid, job_cat,max(salary) from employees group by dept id, job_cat,salary;

答案：B

面试例题2：Description of the students table（以下是学生表的字段描述）：

sid_id	number
start_date	date
end_date	date

which two function are valid on the start_date column?（关于对start_date字段的使用，以下哪两个函数是合法的？）［中国某著名计算机金融软件公司2005年面试题］

A．sum(start_date)

B．avg(start_date)

C．count(start_date)

D．avg(start_date,end_date)

E．min(start_date)

F．maximum(start_date)

答案：C，E。

面试例题3：For which two constraints does the Oracle server implicitly create a unique index?（以下哪两种约束的情况下，Oracle数据库会隐性创建一个唯一索引？）［中国某著名计算机金融软件公司2005年面试题］

A．not null

B．primary

C．foreign key

D．check

E．unique

答案：B，E。

面试例题4：In a select statement that includes a where clause, where is the group by clause placed in the select statement?（在select语句中包括一个where关键词，请问group by关键词一般在select语句中什么位置？）［中国某著名计算机金融软件公司2005年面试题］

A．immediately after the select clause（紧跟select关键词之后）

B．before the where clause（在where关键词之前）

C．before the from clause（在from关键词之前）

D．after the order by clause（在order by关键词之后）

E．after the where clause（在where关键词之后）

答案：E

面试例题5：In a select statement that includes a where clause, where is the order by clause placed in the select statement?（在select语句中包括一个where关键词，请问order by关键词一般在select语句中什么位置？）［中国某著名计算机金融软件公司2005年面试题］

A．immediately after the select clause（紧跟select关键词之后）

B．before the where clause（在where关键词之前）

C．after all clause（在所有关键词之后）

D．after the where clause（在where关键词之后）

E．before the from clause（在from关键词之前）

答案：C

面试例题6：Evaluate there two SQL statements.（对比下面两个SQL语句。）［中国某著名计算机金融软件公司2005年面试题］

Select last_name,salary from employees order by salary;

Select last_name,salary from employees order by 2 asc;

A．the same result（相同的结果）

B．different result（不同的结果）

C．the second statement returns a syntax error（第二个结果会显示错误）

答案：A

面试例题7：You would like to display the system date in the format“20051110 14：44：17”。Which select statement should you use?（如果你想把时间显示成像“20051110 14：44：17”这样的格式，下面哪个select语句应该被使用？）［中国某著名计算机金融软件公司2005年面试题］

A．select to_date(sydate,'yearmmdd hh:mm:ss')from dual;

B．select to_char(sydate,'yearmonthday hh:mi:ss')from dual;

C．select to_date(sydate,'yyyymmdd hh24:mi:ss')from dual;

D．select to_char(sydate,'yyyymmdd hh24:mi:ss')from dual;

E．select to_char(sydate,'yy-mm-dd hh24:mi:ss')from dual;

答案：D

面试例题8：Which select statement will the result 'ello world'from the string 'Hello world'?（如果要从字符串“Hello world”中提取出“ello world”这样的结果，下面的哪条SQL语句适合？）［中国某著名计算机金融软件公司2005年面试题］

A．select substr('Hello World',1)from dual;

B．select substr(trim('Hello World',1,1))from dual;

C．select lower(substr('Hello World',1))from dual;

D．select lower(trim('H'from 'Hello World'))from dual;

答案：D

面试例题9：which are DML statements(choose all that apply)?（下面哪一个是DML（Data Manipulation Language，数据操纵语言）的执行状态？）［中国某著名计算机金融软件公司2005年面试题］

A．commit

B．merge

C．update

D．delete

E．creat

F．drop

答案：C，D。

面试例题10：Select语句中用来连接字符串的符号是__。［中国某著名计算机金融软件公司2005年面试题］

A．+

B．&

C．||

D．|

答案：A

面试例题11：Given the following CREATE TABLE statement（如下列表）：

alt

Which of the following statement prevents two departments from being assigned the same DEPTID, but allows null values?（下列哪个选项防止两个部门（相同名称）被分配在DEPTID字段，但允许NULL值？）

A．ALTER TABLE department ADD CONSTRAINT dpt_cst PRIMARY KEY(改变department表，增加主键约束)

B．CREATE INDEX dpt_idx ON department(deptid)（创建department索引）

C．ALTER TABLE department ADD CONSTRAINT dpt_cst UNIQUE（创建唯一约束）

D．CREATE UNIQUE INDEX dpt_idx ON department(deptid) —（在department唯一索引）

解析：A选项有主键，肯定不允许为NULL。B、D选项也显然不对。

unique约束能够约束一列保证该列值唯一，但允许该列有空值。故选C。

答案：C