数据库面试宝典

浏览数：37 / 时间：2015年06月12日

一、数据库问答题
1. SQL语言包括哪些类型？
数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index等
数据操纵：Select ,insert,update,delete,
数据控制：grant,revoke

2. 内联接,外联接区别？
内连接是保证两个表中所有的行都要满足连接条件，而外连接则不然。
在外连接中，某些不满条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另一个表的行。分左连接、右连接、全连接三种。

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

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

5. 索引的作用？和它的优点缺点是什么？
答：索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

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

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

8. 什么是事务？什么是锁？
答：事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。
锁：在所以的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

9. 事务的隔离级别有哪些？
事务隔离级别包括：
原子性，即不可分割性，事务要么全部被执行，要么就全部不被执行；
一致性或可串性，事务的执行使得数据库从一种正确状态转换成另一种正确状态；
隔离性，在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务；
持久性,事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存

10. 什么叫视图？游标是什么？
答：视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

11. 什么是主键?什么是外键?
主键是表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。

12. 对一个投入使用的在线事务处理表格有过多索引需要有什么样的性能考虑?
你正在寻找进行与数据操控有关的应聘人员。对一个表格的索引越多，数据库引擎用来更新、插入或者删除数据所需要的时间就越多，因为在数据操控发生的时候索引也必须要维护。

你可以用什么来确保表格里的字段只接受特定范围里的值?
这个问题可以用多种方式来回答，但是只有一个答案是"好"答案。您希望听到的回答是Check限制，它在数据库表格里被定义，用来限制输入该列的值。
触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。因此，微软建议使用Check限制而不是其他的方式来限制域的完整性。
如果应聘者能够正确地回答这个问题，那么他的机会就非常大了，因为这表明他们具有使用存储过程的经验。
返回参数总是由存储过程返回，它用来表示存储过程是成功还是失败。返回参数总是INT数据类型。
OUTPUT参数明确要求由开发人员来指定，它可以返回其他类型的数据，例如字符型和数值型的值。(可以用作输出参数的数据类型是有一些限制的。)您可以在一个存储过程里使用多个OUTPUT参数，而您只能够使用一个返回参数。

13. 什么是相关子查询?如何使用这些查询?
经验更加丰富的开发人员将能够准确地描述这种类型的查询。
相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会真正请求外部查询的值，从而形成一个类似于循环的状况。

14. 使用索引查询一定能提高查询的性能吗？为什么
通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.
索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
基于一个范围的检索,一般查询返回结果集小于表中记录数的30%宜采用;
基于非唯一性索引的检索
索引就是为了提高查询性能而存在的,如果在查询中索引没有提高性能,只能说是用错了索引,或者讲是场合不同

15. 列举几种表连接方式，并写出SQL语句?
Answer：等连接（内连接）、非等连接、自连接、外连接（左、右、全）
Or hash join/merge join/nest loop(cluster join)/index join
1)内连接:只连接匹配的行
select A.c1,B.c2 from A join B on A.c3 = B.c3;
2)左外连接:包含左边表的全部行（不管右边的表中是否存在与它们匹配的行）以及右边表中全部匹配的行
select A.c1,B.c2 from A left join B on A.c3 = B.c3;
3)右外连接:包含右边表的全部行（不管左边的表中是否存在与它们匹配的行）以及左边表中全部匹配的行
select A.c1,B.c2 from A right join B on A.c3 = B.c3;
4)全外连接:包含左、右两个表的全部行，不管在另一边的表中是否存在与它们匹配的行
select A.c1,B.c2 from A full join B on A.c3 = B.c3;
5)（theta）连接:使用等值以外的条件来匹配左、右两个表中的行
select A.c1,B.c2 from A join B on A.c3 != B.c3;
6)交叉连接:生成笛卡尔积——它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行一一匹配
select A.c1,B.c2 from A,B;

16. 为管理业务培训信息，建立3个表：
S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号，学员姓名，所属单位，学员年龄
C(C#,CN)C#,CN分别代表课程编号，课程名称
SC(S#,C#,G) S#,C#,G分别代表学号，所选的课程编号，学习成绩
1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?
答案：select s# ,sn from s where S# in(select S# from c,sc where c.c#=sc.c# and cn=’税收基础’)
2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?
答：select sn,sd from s,sc where s.s#=sc.s# and sc.c#=’c2’
3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?
答：select sn,sd from s where s# not in(select s# from sc where c#=’c5’)
4)查询选修了课程的学员人数
答：select 学员人数=count(distinct s#) from sc
5) 查询选修课程超过5门的学员学号和所属单位?
答：select sn,sd from s where s# in(select s# from sc group by s# having count(distinct c#)>5)

17. 查询表A中存在ID重复三次以上的记录，查询语句请写出来？
select * from table T where T.ID in((select ID from table group by ID having count(ID)>3))

18. 查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不连续增长的列，
查询语句请写出来？
select top 10 * from A where ID >(select max(ID) from (select top 30 ID from A order by A ) T) order by A

19. 适用于MySql的分页查询语句?
--limit函数，从数据库表中的m条记录开始，检索n条记录。
select
*
from
表名
limit m,n;

20. 适用于 sql server 2000/2005的分页查询语句?
select
top 页大小 *
from
table1
where
id >(select
isnull(max(id),0)
from
(select
top 页大小*(页数-1) id
from table1
order by id) a
)
order by id;
--或：
select
top 页大小 *
from
table1
where
id not in(select
top 页大小*(页数-1) id
from table1
order by id)
order by id;

21. 适用于 sql server 2005的分页查询语句?
select
top 页大小 *
from
(
select
row_number() over (order by id) as rownumber,*
from table1
) a
where rownumber > 页大小*(页数-1);

--从m条开始，检索n条记录。
select
*
from
(select
top n *
from
(select
top(m + n - 1) *
from 表名
order by 主键 desc
) t1
) t2
order by 主键 asc;

22. 适用于Oracle的分页查询语句?
--从m条开始，检索n条记录。
select
*
from
(select
rownum r,a.*
from 表名称 a
where
rownum < m + n) b
where
b.r >= m;

23. 如何优化数据库，如何提高数据库的性能？
1)给数据库做索引，合理的索引能立即显著地提高数据库整个系统的性能。
2)在适当的情况下，尽可能的用存储过程而不是SQL查询。因为前者已经过了预编译，运行速度更快。
3)优化查询语句，通过高性能的查询语句提高数据库的性能。

24. 设计数据库应注意那些问题
首先应尽量满足三范式的要求，在一定程度上打破三范式的要求以提高数据库的性能。

25. 表与表之间的关联关系
分为3种：一对一、一对多、多对多。

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

27. 游标的作用？如何知道游标已经到了最后？
游标用于定位结果集的行，通过判断全局变量@@FETCH_STATUS可以判断是否到了最后，通常此变量不等于0表示出错或到了最后。

28. 事前触发和事后触发有何区别？语句级触发和行级触发有何区别？
事前触发器运行于触发事件发生之前，而事后触发器运行于触发事件发生之后。通常事前触发器可以获取事件之前和新的字段值。语句级触发器可以在语句执行前或后执行，而行级触发在触发器所影响的每一行触发一次。

29. 数据库设计的必要性及设计步骤
好的数据库结构有利于：节省数据的存储空间，能够保证数据的完整性，方便进行数据库应用系统的开发
设计不好的数据库结构将导致：数据冗余、存储空间浪费和内存空间浪费
不管数据库的大小和复杂程度如何，可以用下列基本步骤来设计数据库：收集信息--标识对象--设计数据模型--标识每个对象--存储的信息类型–标识对象之间的关系

30. 什么是数据模型？什么是规范化？
数据模型是一种标识实体类型及其实体间联系的模型。典型的数据模型有网状模型、层次模型和关系模型。
从关系数据库的表中，除去冗余数据的过程称为规范化。包括：精简数据库的结构，从表中删除冗余的列，标识所有依赖于其它数据的数据

31. 谈谈数据库设计的三范式
第一范式的定义：如果一个表中没有重复组（即行与列的交叉点上只有一个值，而不是一组值），则这个表属于第一范式（常记成1NF）。简而言之："每一字段只存储一个值"。例如:职工号，姓名，电话号码组成一个表（一个人可能有一个办公室电话和一个家里电话号码）
第二范式的定义：如果一个表属于1NF，任何属性只依赖于关键字，则这个表属于第二范式（常记成2NF ）。简而言之：必须先符合1NF的条件，且每一行都能被唯一的识别。将1NF转换成2NF的方法是添加主键。例如：学号，姓名，课程名，成绩
第三范式的定义：如果一个表属于2NF，且不包含传递依赖性，则这个表是第三范式（常记成 3NF）。满足3NF的表中不包含传递依赖。简而言之：没有一个非关键属性依赖于另一个非关键属性。例如：表一：学号，课程号，成绩。表二：学号，姓名，所在系，系名称，系地址。表三：课程号，课程名，学分

32. 说出一些数据库优化方面的经验?
用PreparedStatement 一般来说比Statement性能高：一个sql 发给服务器去执行，涉及步骤：语法检查、语义分析，编译，缓存
“inert into user values(1,1,1)”-?二进制
“inert into user values(2,2,2)”-?二进制
“inert into user values(?,?,?)”-?二进制

有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就去掉外键。（比喻：就好比免检产品，就是为了提高效率，充分相信产品的制造商）
（对于hibernate来说，就应该有一个变化：empleyee->Deptment对象，现在设计时就成了employee?deptid）

看mysql帮助文档子查询章节的最后部分，例如，根据扫描的原理，下面的子查询语句要比第二条关联查询的效率高：
1)select e.name,e.salary where e.managerid=(select id from employee where name=‘zxx‘);
2)select e.name,e.salary,m.name,m.salary from employees e,employees m where
e.managerid = m.id and m.name=‘zxx‘;

表中允许适当冗余，譬如，主题帖的回复数量和最后回复时间等
将姓名和密码单独从用户表中独立出来。这可以是非常好的一对一的案例哟！
sql语句全部大写，特别是列名和表名都大写。特别是sql命令的缓存功能，更加需要统一大小写，sql语句?发给oracle服务器?语法检查和编译成为内部指令?缓存和执行指令。根据缓存的特点，不要拼凑条件，而是用?和PreparedStatment
还有索引对查询性能的改进也是值得关注的。

33. union和union all有什么不同?
假设我们有一个表Student，包括以下字段与数据：
drop table student;
create table student
(
id int primary key,
name nvarchar2(50) not null,
score number not null
);
insert into student values(1,‘Aaron‘,78);
insert into student values(2,‘Bill‘,76);
insert into student values(3,‘Cindy‘,89);
insert into student values(4,‘Damon‘,90);
insert into student values(5,‘Ella‘,73);
insert into student values(6,‘Frado‘,61);
insert into student values(7,‘Gill‘,99);
insert into student values(8,‘Hellen‘,56);
insert into student values(9,‘Ivan‘,93);
insert into student values(10,‘Jay‘,90);
commit;
Union和Union All的区别。
select *
from student
where id < 4
union
select *
from student
where id > 2 and id < 6
结果将是
1 Aaron 78
2 Bill 76
3 Cindy 89
4 Damon 90
5 Ella 73
如果换成Union All连接两个结果集，则返回结果是：
1 Aaron 78
2 Bill 76
3 Cindy 89
3 Cindy 89
4 Damon 90
5 Ella 73
可以看到，Union和Union All的区别之一在于对重复结果的处理。

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

34. 用一条SQL语句查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三语文 81
张三数学 75
李四语文 76
李四数学 90
王五语文 81
王五数学 100
王五英语 90

准备数据的sql代码：
create table score(id int primary key auto_increment,name varchar(20),subject varchar(20),score int);
insert into score values
(null,‘张三‘,‘语文‘,81),
(null,‘张三‘,‘数学‘,75),
(null,‘李四‘,‘语文‘,76),
(null,‘李四‘,‘数学‘,90),
(null,‘王五‘,‘语文‘,81),
(null,‘王五‘,‘数学‘,100),
(null,‘王五 ‘,‘英语‘,90);

提示：当百思不得其解时，请理想思维，把小变成大做，把大变成小做，

答案：
A: select distinct name from score where name not in (select distinct name from score where score<=80)

B:select distince name t1 from score where 80< all (select score from score where name=t1);

35. 所有部门之间的比赛组合
一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.

答：select a.name, b.name
from team a, team b
where a.name < b.name

36. 每个月份的发生额都比101科目多的科目
请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意：TestDB中有很多科目，都有1－12月份的发生额。
AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。
数据库名：JcyAudit，数据集：Select * from TestDB
准备数据的sql代码：
drop table if exists TestDB;
create table TestDB(id int primary key auto_increment,AccID varchar(20), Occmonth date, DebitOccur bigint);
insert into TestDB values
(null,‘101‘,‘1988-1-1‘,100),
(null,‘101‘,‘1988-2-1‘,110),
(null,‘101‘,‘1988-3-1‘,120),
(null,‘101‘,‘1988-4-1‘,100),
(null,‘101‘,‘1988-5-1‘,100),
(null,‘101‘,‘1988-6-1‘,100),
(null,‘101‘,‘1988-7-1‘,100),
(null,‘101‘,‘1988-8-1‘,100);
--复制上面的数据，故意把第一个月份的发生额数字改小一点
insert into TestDB values
(null,‘102‘,‘1988-1-1‘,90),
(null,‘102‘,‘1988-2-1‘,110),
(null,‘102‘,‘1988-3-1‘,120),
(null,‘102‘,‘1988-4-1‘,100),
(null,‘102‘,‘1988-5-1‘,100),
(null,‘102‘,‘1988-6-1‘,100),
(null,‘102‘,‘1988-7-1‘,100),
(null,‘102‘,‘1988-8-1‘,100);
--复制最上面的数据，故意把所有发生额数字改大一点
insert into TestDB values
(null,‘103‘,‘1988-1-1‘,150),
(null,‘103‘,‘1988-2-1‘,160),
(null,‘103‘,‘1988-3-1‘,180),
(null,‘103‘,‘1988-4-1‘,120),
(null,‘103‘,‘1988-5-1‘,120),
(null,‘103‘,‘1988-6-1‘,120),
(null,‘103‘,‘1988-7-1‘,120),
(null,‘103‘,‘1988-8-1‘,120);
--复制最上面的数据，故意把所有发生额数字改大一点
insert into TestDB values
(null,‘104‘,‘1988-1-1‘,130),
(null,‘104‘,‘1988-2-1‘,130),
(null,‘104‘,‘1988-3-1‘,140),
(null,‘104‘,‘1988-4-1‘,150),
(null,‘104‘,‘1988-5-1‘,160),
(null,‘104‘,‘1988-6-1‘,170),
(null,‘104‘,‘1988-7-1‘,180),
(null,‘104‘,‘1988-8-1‘,140);
--复制最上面的数据，故意把第二个月份的发生额数字改小一点
insert into TestDB values
(null,‘105‘,‘1988-1-1‘,100),
(null,‘105‘,‘1988-2-1‘,80),
(null,‘105‘,‘1988-3-1‘,120),
(null,‘105‘,‘1988-4-1‘,100),
(null,‘105‘,‘1988-5-1‘,100),
(null,‘105‘,‘1988-6-1‘,100),
(null,‘105‘,‘1988-7-1‘,100),
(null,‘105‘,‘1988-8-1‘,100);
答案：
select distinct AccID from TestDB
where AccID not in
(select TestDB.AccIDfrom TestDB,
(select * from TestDB where AccID=‘101‘) as db101
where TestDB.Occmonth=db101.Occmonth and TestDB.DebitOccur<=db101.DebitOccur
);

37. 统计每年每月的信息
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

提示：这个与工资条非常类似，与学生的科目成绩也很相似。

准备sql语句：
drop table if exists sales;
create table sales(id int auto_increment primary key,year varchar(10), month varchar(10), amount float(2,1));
insert into sales values
(null,‘1991‘,‘1‘,1.1),
(null,‘1991‘,‘2‘,1.2),
(null,‘1991‘,‘3‘,1.3),
(null,‘1991‘,‘4‘,1.4),
(null,‘1992‘,‘1‘,2.1),
(null,‘1992‘,‘2‘,2.2),
(null,‘1992‘,‘3‘,2.3),
(null,‘1992‘,‘4‘,2.4);

答案一、
select sales.year ,
(select t.amount from sales t where t.month=‘1‘ and t.year= sales.year) ‘1‘,
(select t.amount from sales t where t.month=‘1‘ and t.year= sales.year) ‘2‘,
(select t.amount from sales t where t.month=‘1‘ and t.year= sales.year) ‘3‘,
(select t.amount from sales t where t.month=‘1‘ and t.year= sales.year) as ‘4‘
from sales group by year;

38. 显示文章标题，发帖人、最后回复时间
表：id,title,postuser,postdate,parentid
准备sql语句：
drop table if exists articles;
create table articles(id int auto_increment primary key,title varchar(50), postuser varchar(10), postdate datetime,parentid int references articles(id));
insert into articles values
(null,‘第一条‘,‘张三‘,‘1998-10-10 12:32:32‘,null),
(null,‘第二条‘,‘张三‘,‘1998-10-10 12:34:32‘,null),
(null,‘第一条回复1‘,‘李四‘,‘1998-10-10 12:35:32‘,1),
(null,‘第二条回复1‘,‘李四‘,‘1998-10-10 12:36:32‘,2),
(null,‘第一条回复2‘,‘王五‘,‘1998-10-10 12:37:32‘,1),
(null,‘第一条回复3‘,‘李四‘,‘1998-10-10 12:38:32‘,1),
(null,‘第二条回复2‘,‘李四‘,‘1998-10-10 12:39:32‘,2),
(null,‘第一条回复4‘,‘王五‘,‘1998-10-10 12:39:40‘,1);

答案：
select a.title,a.postuser,
(select max(postdate) from articles where parentid=a.id) reply
from articles a where a.parentid is null;

39. 航空网的几个航班查询题：
表结构如下：
flight{flightID,StartCityID ,endCityID,StartTime}
city{cityID, CityName)
实验环境：
create table city(cityID int auto_increment primary key,cityName varchar(20));
create table flight (flightID int auto_increment primary key,
StartCityID int references city(cityID),
endCityID int references city(cityID),
StartTime timestamp);
//航班本来应该没有日期部分才好，但是下面的题目当中涉及到了日期
insert into city values(null,‘北京‘),(null,‘上海‘),(null,‘广州‘);
insert into flight values
(null,1,2,‘9:37:23‘),(null,1,3,‘9:37:23‘),(null,1,2,‘10:37:23‘),(null,2,3,‘10:37:23‘);

1)查询起飞城市是北京的所有航班，按到达城市的名字排序

参与运算的列是我起码能够显示出来的那些列，但最终我不一定把它们显示出来。各个表组合出来的中间结果字段中必须包含所有运算的字段。

select * from flight f,city c
where f.endcityid = c.cityid and startcityid =
(select c1.cityid from city c1 where c1.cityname = "北京")
order by c.cityname asc;

mysql> select flight.flightid,‘北京‘ startcity, e.cityname from flight,city e wh
ere flight.endcityid=e.cityid and flight.startcityid=(select cityid from city wh
ere cityname=‘北京‘);

mysql> select flight.flightid,s.cityname,e.cityname from flight,city s,city e wh
ere flight.startcityid=s.cityid and s.cityname=‘北京‘ and flight.endCityId=e.cit
yID order by e.cityName desc;

2)查询北京到上海的所有航班纪录（起飞城市，到达城市，起飞时间，航班号）
select c1.CityName,c2.CityName,f.StartTime,f.flightID
from city c1,city c2,flight f
where f.StartCityID=c1.cityID
and f.endCityID=c2.cityID
and c1.cityName=‘北京‘
and c2.cityName=‘上海‘

3)查询具体某一天（2005-5-8）的北京到上海的的航班次数
select count(*) from
(select c1.CityName,c2.CityName,f.StartTime,f.flightID
from city c1,city c2,flight f
where f.StartCityID=c1.cityID
and f.endCityID=c2.cityID
and c1.cityName=‘北京‘
and c2.cityName=‘上海‘
and 查帮助获得的某个日期处理函数(startTime) like ‘2005-5-8%‘

mysql中提取日期部分进行比较的示例代码如下：
select * from flight where date_format(starttime,‘%Y-%m-%d‘)=‘1998-01-02‘

40. 查出比经理薪水还高的员工信息：
Drop table if not exists employees;
create table employees(id int primary key auto_increment,name varchar(50)
,salary int,managerid int references employees(id));
insert into employees values (null,‘ lhm‘,10000,null), (null,‘ zxx‘,15000,1
),(null,‘flx‘,9000,1),(null,‘tg‘,10000,2),(null,‘wzg‘,10000,3);

Wzg大于flx,lhm大于zxx

解题思路：
根据sql语句的查询特点，是逐行进行运算，不可能两行同时参与运算。
涉及了员工薪水和经理薪水，所有，一行记录要同时包含两个薪水，所有想到要把这个表自关联组合一下。
首先要组合出一个包含有各个员工及该员工的经理信息的长记录，譬如，左半部分是员工，右半部分是经理。而迪卡尔积会组合出很多垃圾信息，先去除这些垃圾信息。

select e.* from employees e,employees m where e.managerid=m.id and e.sala
ry>m.salary;

41. 求出小于45岁的各个老师所带的大于12岁的学生人数
数据库中有3个表 teacher 表，student表，tea_stu关系表。
teacher 表 teaID name age
student 表 stuID name age
teacher_student表 teaID stuID
要求用一条sql查询出这样的结果
1)显示的字段要有老师name, age 每个老师所带的学生人数
2)只列出老师age为40以下，学生age为12以上的记录
预备知识：
1)sql语句是对每一条记录依次处理，条件为真则执行动作（select,insert,delete,update）
2)只要是迪卡尔积，就会产生“垃圾”信息，所以，只要迪卡尔积了，我们首先就要想到清除“垃圾”信息
实验准备：
drop table if exists tea_stu;
drop table if exists teacher;
drop table if exists student;
create table teacher(teaID int primary key,name varchar(50),age int);
create table student(stuID int primary key,name varchar(50),age int);
create table tea_stu(teaID int references teacher(teaID),stuID int references student(stuID));
insert into teacher values(1,‘zxx‘,45), (2,‘lhm‘,25) , (3,‘wzg‘,26) , (4,‘tg‘,27);
insert into student values(1,‘wy‘,11), (2,‘dh‘,25) , (3,‘ysq‘,26) , (4,‘mxc‘,27);
insert into tea_stu values(1,1), (1,2), (1,3);
insert into tea_stu values(2,2), (2,3), (2,4);
insert into tea_stu values(3,3), (3,4), (3,1);
insert into tea_stu values(4,4), (4,1), (4,2) , (4,3);

结果：2 3,3 2,4 3

解题思路：（真实面试答题时，也要写出每个分析步骤，如果纸张不够，就找别人要）
1)要会统计分组信息，统计信息放在中间表中：
select teaid,count(*) from tea_stu group by teaid;

2)接着其实应该是筛除掉小于12岁的学生，然后再进行统计，中间表必须与student关联才能得到12岁以下学生和把该学生记录从中间表中剔除，代码是：
select tea_stu.teaid,count(*) total from student,tea_stu
where student.stuid=tea_stu.stuid and student.age>12 group by tea_stu.teaid

3)接着把上面的结果做成虚表与teacher进行关联，并筛除大于45的老师
select teacher.teaid,teacher.name,total from teacher ,(select tea_stu.tea
id,count(*) total from student,tea_stu where student.stuid=tea_stu.stuid and stu
dent.age>12 group by tea_stu.teaid) as tea_stu2 where teacher.teaid=tea_stu2.tea
id and teacher.age<45;

42. 求出发帖最多的人：
select authorid,count(*) total from articles
group by authorid
having total=
(select max(total2) from (select count(*) total2 from articles group by authorid) as t);

select t.authorid,max(t.total) from
（select authorid,count(*) total from articles ）as t
这条语句不行，因为max只有一列，不能与其他列混淆。

select authorid,count(*) total from articles
group by authorid having total=max(total)也不行。

43. 一个用户表中有一个积分字段，假如数据库中有100多万个用户，若要在每年第一天凌晨将积分清零，你将考虑什么，你将想什么办法解决?
alter table drop column score;
alter table add colunm score int;
可能会很快，但是需要试验，试验不能拿真实的环境来操刀，并且要注意，
这样的操作时无法回滚的，在我的印象中，只有inert update delete等DML语句才能回滚，
对于create table,drop table ,alter table等DDL语句是不能回滚。

解决方案一，update user set score=0;
解决方案二，假设上面的代码要执行好长时间，超出我们的容忍范围，那我就alter table user drop column score;alter table user add column score int。

下面代码实现每年的那个凌晨时刻进行清零。
Runnable runnable =
new Runnable(){
public void run(){
clearDb();
schedule(this,new Date(new Date().getYear()+1,0,0));
}
};

schedule(runnable,
new Date(new Date().getYear()+1,0,1));

44. 一个用户具有多个角色，请查询出该表中具有该用户的所有角色的其他用户。
select count(*) as num,tb.id
from
tb,
(select role from tb where id=xxx) as t1
where
tb.role = t1.role and tb.id != t1.id
group by tb.id
having
num = select count(role) from tb where id=xxx;

45. xxx公司的sql面试
Table EMPLOYEES Structure:
EMPLOYEE_ID NUMBER Primary Key,
FIRST_NAME VARCHAR2(25),
LAST_NAME VARCHAR2(25),
Salary number(8,2),
HiredDate DATE,
Departmentid number(2)
Table Departments Structure:
Departmentid number(2) Primary Key,
DepartmentName VARCHAR2(25).

1）基于上述EMPLOYEES表写出查询：写出雇用日期在今年的，或者工资在[1000,2000]之间的，或者员工姓名（last_name）以’Obama’打头的所有员工，列出这些员工的全部个人信息。
select * from employees
where Year(hiredDate) = Year(date())
or (salary between 1000 and 200)
or left(last_name,3)=‘abc‘;

2) 基于上述EMPLOYEES表写出查询：查出部门平均工资大于1800元的部门的所有员工，列出这些员工的全部个人信息。
mysql> select id,name,salary,deptid did from employee1 where (select avg(salary)
from employee1 where deptid = did) > 1800;

3) 基于上述EMPLOYEES表写出查询：查出个人工资高于其所在部门平均工资的员工，列出这些员工的全部个人信息及该员工工资高出部门平均工资百分比。
select employee1.*,(employee1.salary-t.avgSalary)*100/employee1.salary
from employee1,
(select deptid,avg(salary) avgSalary from employee1 group by deptid) as t
where employee1.deptid = t.deptid and employee1.salary>t.avgSalary;

二、数据库选择题：
1. 下面叙述正确的是___C___。
A、算法的执行效率与数据的存储结构无关
B、算法的空间复杂度是指算法程序中指令（或语句）的条数
C、算法的有穷性是指算法必须能在执行有限个步骤之后终止D、以上三种描述都不对

2. 以下数据结构中不属于线性数据结构的是__C____。
A、队列 B、线性表 C、二叉树 D、栈

3. 在一棵二叉树上第5层的结点数最多是__B____。
A、8 B、16 C、32 D、15

4. 在结构化方法中，用数据流程图（DFD）作为描述工具的软件开发阶段是__B__。
A、可行性分析 B、需求分析 C、详细设计 D、程序编码

5. 在软件开发中，下面任务不属于设计阶段的是___D___。
A、数据结构设计 B、给出系统模块结构 C、定义模块算法 D、定义需求并建立系统模型

6. 数据库系统的核心是___B___。
A、数据模型 B、数据库管理系统 C、软件工具 D、数据库

7. 下列叙述中正确的是___C___。
A、数据库是一个独立的系统，不需要操作系统的支持
B、数据库设计是指设计数据库管理系统
C、数据库技术的根本目标是要解决数据共享的问题
D、数据库系统中，数据的物理结构必须与逻辑结构一致

8. SQL语句中修改表结构的命令是__C____。
A、MODIFY TABLE B、MODIFY STRUCTURE C、ALTER TABLE D、ALTER STRUCTURE

9. 如果要创建一个数据组分组报表，第一个分组表达式是"部门"，第二个分组表达式是"性别"，第三个分组表达式是"基本工资"，当前索引的索引表达式应当是___B___。
A、部门+性别+基本工资 B、部门+性别+STR（基本工资）
C、STR（基本工资）+性别+部门 D、性别+部门+STR（基本工资）

10. 数据库DB、数据库系统DBS、数据库管理系统DBMS三者之间的关系是___A___。
A、DBS包括DB和DBMS B、DBMS包括DB和DBS
C、DB包括DBS和DBMS D、DBS就是DB，也就是DBMS

11. 要控制两个表中数据的完整性和一致性可以设置"参照完整性"，要求这两个表__A___。
A、是同一个数据库中的两个表 B、不同数据库中的两个表
C、两个自由表 D、一个是数据库表另一个是自由表

12. 在关系模型中，实现"关系中不允许出现相同的元组"的约束是通过___B___。
A、候选键 B、主键 C、外键 D、超键

13. 只有满足联接条件的记录才包含在查询结果中，这种联接为___C___。
A、左联接 B、右联接 C、内部联接 D、完全联接

14. 索引字段值不唯一，应该选择的索引类型为___B___。
A、主索引 B、普通索引 C、候选索引 D、唯一索引

15. 从数据库中删除表的命令是__A____。
A、DROP TABLE B、ALTER TABLE C、DELETE TABLE D、USE

16. DELETE FROM S WHERE 年龄>60语句的功能是__B____。
A、从S表中彻底删除年龄大于60岁的记录 B、S表中年龄大于60岁的记录被加上删除标记
C、删除S表 D、删除S表的年龄列

17. SELECT-SQL语句是__B____。
A、选择工作区语句 B、数据查询语句 C、选择标准语句 D、数据修改语句

18. SQL语言是___C___语言。
A、层次数据库 B、网络数据库 C、关系数据库 D、非数据库

19. 在SQL中，删除视图用___C___。
A、DROP SCHEMA命令 B、CREATE TABLE命令 C、DROP VIEW命令 D、DROP INDEX命令

20. 在命令窗口执行SQL命令时，若命令要占用多行，续行符是__D____。
A、冒号(:) B、分号(;) C、逗号(,) D、连字符(-)

21. 设有图书管理数据库：
图书(总编号C(6),分类号C(8),书名C(16),作者C(6),出版单位C(20),单价N(6,2))
读者(借书证号C(4),单位C(8),姓名C(6),性别C(2),职称C(6),地址C(20))
借阅(借书证号C(4),总编号C(6),借书日期D(8))
对于图书管理数据库，查询0001号借书证的读者姓名和所借图书的书名。
SQL语句正确的是___A___。
SELECT 姓名,书名 FROM 借阅,图书,读者 WHERE;
借阅.借书证号="0001" AND;
______
______
A、图书.总编号=借阅.总编号 AND;
读者.借书证号=借阅.借书证号
B、图书.分类号=借阅.分类号 AND;
读者.借书证号=借阅.借书证号
C、读者.总编号=借阅.总编号 AND;
读者.借书证号=借阅.借书证号
D、图书.总编号=借阅.总编号 AND;
读者.书名=借阅.书名

22. 设有图书管理数据库：
图书(总编号C(6),分类号C(8),书名C(16),作者C(6),出版单位C(20),单价N(6,2))
读者(借书证号C(4),单位C(8),姓名C(6),性别C(2),职称C(6),地址C(20))
借阅(借书证号C(4),总编号C(6),借书日期D(8))
对于图书管理数据库，分别求出各个单位当前借阅图书的读者人次。下面的SQL语句正确的是___A___。
SELECT 单位,______ FROM 借阅,读者 WHERE;
借阅.借书证号=读者.借书证号 ______
A、COUNT(借阅.借书证号) GROUP BY 单位 B、SUM(借阅.借书证号) GROUP BY 单位
C、COUNT(借阅.借书证号) ORDER BY 单位 D、COUNT(借阅.借书证号) HAVING 单位

23. 设有图书管理数据库：
图书(总编号C(6),分类号C(8),书名C(16),作者C(6),出版单位C(20),单价N(6,2))
读者(借书证号C(4),单位C(8),姓名C(6),性别C(2),职称C(6),地址C(20))
借阅(借书证号C(4),总编号C(6),借书日期D(8))
对于图书管理数据库，检索借阅了《现代网络技术基础》一书的借书证号。下面SQL语句正确的是___B___。
SELECT 借书证号 FROM 借阅 WHERE 总编号=;
______
A、(SELECT 借书证号 FROM 图书 WHERE 书名="现代网络技术基础")
B、(SELECT 总编号 FROM 图书 WHERE 书名="现代网络技术基础")
C、(SELECT 借书证号 FROM 借阅 WHERE 书名="现代网络技术基础")
D、(SELECT 总编号 FROM 借阅 WHERE 书名="现代网络技术基础")

三、Oracle面试题
1. 解释冷备份和热备份的不同点以及各自的优点
解答：热备份针对归档模式的数据库，在数据库仍旧处于工作状态时进行备份。而冷备份指在数据库关闭后，进行备份，适用于所有模式的数据库。热备份的优点在于当备份时，数据库仍旧可以被使用并且可以将数据库恢复到任意一个时间点。冷备份的优点在于它的备份和恢复操作相当简单，并且由于冷备份的数据库可以工作在非归档模式下,数据库性能会比归档模式稍好。(因为不必将archive log写入硬盘)
2. 你必须利用备份恢复数据库，但是你没有控制文件，该如何解决问题呢?
解答：重建控制文件，用带backup control file 子句的recover 命令恢复数据库。
3. 如何转换init.ora到spfile?
解答：使用create spfile from pfile 命令.
4. 解释data block , extent 和 segment的区别(这里建议用英文术语)
解答：data block是数据库中最小的逻辑存储单元。当数据库的对象需要更多的物理存储空间时，连续的data block就组成了extent . 一个数据库对象
拥有的所有extents被称为该对象的segment.
5. 给出两个检查表结构的方法
解答：
1)DESCRIBE命令
2)DBMS_METADATA.GET_DDL 包
6. 怎样查看数据库引擎的报错
解答：alert log.

7. 比较truncate和delete 命令
解答：两者都可以用来删除表中所有的记录。区别在于：truncate是DDL操作，它移动HWK，不需要 rollback segment .而Delete是DML操作, 需要rollback segment 且花费较长时间.

8. 使用索引的理由
解答：快速访问表中的data block

9. 给出在STAR SCHEMA中的两种表及它们分别含有的数据
解答：Fact tables 和dimension tables. fact table 包含大量的主要的信息而 dimension tables 存放对fact table 某些属性描述的信息

10. FACT Table上需要建立何种索引?
解答：位图索引 (bitmap index)

11. 给出两种相关约束?
解答：主键和外键

12. 如何在不影响子表的前提下，重建一个母表
解答：子表的外键强制实效，重建母表，激活外键

13. 解释归档和非归档模式之间的不同和它们各自的优缺点
解答：归档模式是指你可以备份所有的数据库 transactions并恢复到任意一个时间点。非归档模式则相反，不能恢复到任意一个时间点。但是非归档模式可以带来数据库性能上的少许提高.

14. 如何建立一个备份控制文件?
解答：Alter database backup control file to trace.

15. 给出数据库正常启动所经历的几种状态 ?
解答：
　　STARTUP NOMOUNT – 数据库实例启动
　　STARTUP MOUNT - 数据库装载
　　STARTUP OPEN – 数据库打开

16. 哪个column可以用来区别V$视图和GV$视图?
解答： INST_ID 指明集群环境中具体的某个instance 。

17. 如何生成explain plan?
解答：运行utlxplan.sql. 建立plan 表
针对特定SQL语句，使用 explain plan set statement_id = ‘tst1‘ into plan_table
运行utlxplp.sql 或 utlxpls.sql察看explain plan

18. 如何增加buffer cache的命中率?
解答：在数据库较繁忙时，适用buffer cache advisory 工具，查询v$db_cache_advice . 如果有必要更改，可以使用 alter system set db_cache_size 命令

19. ORA-01555的应对方法?
解答：具体的出错信息是snapshot too old within rollback seg , 通常可以通过
增大rollback seg来解决问题。当然也需要察看一下具体造成错误的SQL文本

20. 解释$ORACLE_HOME和$ORACLE_BASE的区别?
解答：ORACLE_BASE是oracle的根目录，ORACLE_HOME是oracle产品的目录。

21. 如何判断数据库的时区?
解答：SELECT DBTIMEZONE FROM DUAL;

22. 解释GLOBAL_NAMES设为TRUE的用途
解答：GLOBAL_NAMES指明联接数据库的方式。如果这个参数设置为TRUE,在建立数据库链接时就必须用相同的名字连结远程数据库

23。如何加密PL/SQL程序?
解答：WRAP

24. 解释FUNCTION,PROCEDURE和PACKAGE区别
解答：function 和procedure是PL/SQL代码的集合，通常为了完成一个任务。procedure 不需要返回任何值而function将返回一个值在另一
方面，Package是为了完成一个商业功能的一组function和proceudre的集合

25. 解释TABLE Function的用途
解答：TABLE Function是通过PL/SQL逻辑返回一组纪录，用于普通的表/视图。他们也用于pipeline和ETL过程。

26. 举出3种可以收集three advisory statistics
解答：Buffer Cache Advice, Segment Level Statistics, Timed Statistics

27. Audit trace 存放在哪个oracle目录结构中?
解答：unix $ORACLE_HOME/rdbms/audit Windows the event viewer

28. 解释materialized views的作用
解答：Materialized views 用于减少那些汇总，集合和分组的信息的集合数量。它们通常适合于数据仓库和DSS系统。

29. 当用户进程出错，哪个后台进程负责清理它
解答： PMON

30. 哪个后台进程刷新materialized views?
解答：The Job Queue Processes.

31. 如何判断哪个session正在连结以及它们等待的资源?
解答：V$SESSION / V$SESSION_WAIT

32. 描述什么是 redo logs
解答：Redo Logs 是用于存放数据库数据改动状况的物理和逻辑结构。可以用来修复数据库.

33. 如何进行强制LOG SWITCH?
解答：ALTER SYSTEM SWITCH LOGFILE;

34. 举出两个判断DDL改动的方法?
解答：你可以使用 Logminer 或 Streams

35. Coalescing做了什么?
解答：Coalescing针对于字典管理的tablespace进行碎片整理，将临近的小extents合并成单个的大extent.

36. TEMPORARY tablespace和PERMANENT tablespace 的区别是?
解答：A temporary tablespace 用于临时对象例如排序结构而 permanent tablespaces用来存储那些‘真实‘的对象(例如表，回滚段等)