Oracle数据库相关知识

1.基础知识点：

1NF:第一范式。如果关系模式R的所有属性的值域中每一个值都是不可再分解的值，则称R属于第一范式模式。第一范式的模式要求属性值不可再分裂成更小部分，即属性项不能是属性组合或由组属性组成。

2NF:第二范式。如果关系模式R为第一范式，并且R中每一个非主属性完全函数依赖于R的某个候选键，则称R为第二范式模式。(如果A是关系模式R的候选键的一个属性，则称A是R的主属性，否则称A是R的非主属性。) 

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

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

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

2.试题

1) 设有关系R(S,D,M)，其函数依赖集F={S→D,D→M}。则关系R至多满足2NF。

 解析：属于依赖关系，所有至多满足第二范式。

2) 找出表ppp里面num最小的数，不能使用min函数。

  答：select * from ppp where num<=all(select num from ppp) 或者：select top 1 num from ppp order by num。

3)找出表ppp里面最小的数，可以使用min函数。 

答：select * from ppp where num=（select Min(num) from ppp）。

4)选择表ppp2中num重复的记录。 

答： select * from ppp2 where num in(select num from ppp2 group by num having(count(num)>1))。

5) 写出复制表、拷贝表和四表联合的SQL语句。

  答：复制表(只复制结构，源表名：A,新表名：B): select * into B from A where 1=0   拷贝表(拷贝结构，源表名：A,新表名：B): select * into B from A  四表联查： select * from A,B,C,D where 关联条件

6) 在SQL Server中如何用SQL语句建立一张临时表？  

答： create table #Temp(字段1 类型，字段2 类型…)   注意，临时表要在表名前面加 “＃”。

7) %代表任意长度(长度可以为0)的字符串，_ 代表任意单个字符。如果用户要查询的语句本身就含有%或_，这时就要使用escape’<换码字符>’短语对通配符进行转义了。

8)    集函数SUM,AVG中的列必须是数值型。

9) 每一个部门的每个工种的工资最大值？ select dept_id,job_cat,max(salary)from employee group by dept_id,job_cat;

10)在primary 和unique两种约束的情况下，Oracle数据库会隐形创建一个唯一索引。

11)在select语句中包含一个where关键词，group by关键词一般在where关键词之后，order by 关键词一般在select语句中所有关键词之后。

12)select last_name,salary from employees order by salary; select last_name,salary from employees order by 2 asc; 这两个SQL语句是相同的结果。

13)如果你想把时间显示成像“2005111014：44： 17”这样的格式，SQL语句： select to_char(sydate,’yyyymmdd hh24:mi:ss’)from dual;

14)select语句中用来连接字符串的符号是+。

15)如果要从字符串“Hello world”中提取出“ello world”这样的结果，SQL语句： select lower(trim(‘H’from ‘Hello world’))from dual;

3.存储过程和函数的区别是什么？

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

4.事务是什么？

答：事务是作为一个逻辑单元执行的一系列操作。一个逻辑工作单元必须有4个属性，称为ACID(原子性、一致性、隔离性和持久性)属性，只有这样才能称为一个事务。

5.游标的作用是什么？如何知道游标已经到了最后？

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

6.触发器分为事前触发和事后触发，这两种触发有何区别？语句级触发和行级触发有何区别？

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

7.什么叫做SQL注入式攻击？如何防范？

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

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

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

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

     第三，对于用来执行查询的数据库账户，限制其权限。

     第四，用存储过程来执行所有的查询。

     第五，检查用户输入的合法性，确信输入的内容只包含合法的数据。数据检查应当在客户端和服务器端都执行。

     第六，将用户登录名称、密码等数据加密保存。

     第七，检查提取数据的查询所返回的记录数量。

8.为了防止在查询记录的时候被其他用户更改记录，应该采用什么方法？如何用查询语句实现该方法？

  答: 添加一个“时间戳”类型的字段就可以了。timestamp这种数据类型会根据当前时间自动产生一个时间字符串，确保这些数在数据库中是唯一的。timestamp一般用做给表行加版本戳的机制，存储大小为8个字节。一个表只能有一个timestamp列。每次插入或更新包含timestamp列的行时，timestamp列中的值均会更新。这一属性使timestamp列不适合作为键使用，尤其是不能作为主键使用。对行的任何更新都会更改timestamp值，从而更改键值。

9.有一个数据库，只有一个表，包含着1000个记录，你能想出一种解决方案来把第五行到第七行的记录取出来么？不要使用航标和索引。

答：第一步建立数据库：

declare @i int

set @i=1

create table #T(userid int)

while (@i<=10)

begin

insert into #T

select @i

set @i=@i+1

end

select userid from

(select top 3 userid from(selec top 7 userid from #T order by userid)Ta order by userid desc

)TB order by userid

删除数据库: drop table #T

提取数据：

select top 3 userid from T where userid not in(select top 4 userid from T order by userid)order by userid

或者：select top 7 userid from T where userid >ANY(select top 4 userid from T order by userid)order by userid

或者：select top 7 userid from T where userid >ALL(select top 4 userid from T order by userid)order by userid

10.什么是聚集索引？什么是非聚集索引？什么是主键？

答：表中经常有一个列或列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键。聚集索引确定表中数据的物理顺序。一个表中只能包含一个聚集索引。非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储。

11.现有一张表，有两个字段：ID,NAME。ID为主键。如果希望查询出所有拥有2个或更多ID 的NAME，查询语句应该如何写？

答：select [Name] from [table] group by(name) having(count([ID]>=2)。

12.设供应商供应零件的关系模式为SP(Sno,Pno,Qty),其中Sno表示供应商号，Pno表示零件号，Qty表示零件数量。整个数据库如下表：

Sno     Pno      Qty

168      r1        3

168      r2        4

168      r3        7

169      r2        1

169      r3        5

170      r4        8

171      r7        5

172      r2        1

172      r7        3

请问下面的SQL语句返回值是什么？

Select * from SP SPY where exists (select * from SP SPZ where Sno)

解析：在exists 中的子查询在这个例子中只返回一个值。因为从子查询中返回的行数至少有一行exits返回为true,这使得表中的所有记录都被显示了出来。

请问下面的SQL语句返回值是什么？

Select * from SP SPY where exists (select * from SP SPZ where Sno=’168’ and SPZ.Pno=SPY.Pno);

解析：返回与168号供应商所提供零件相同编号的情况。

若想得到与168号供应上所提供零件相同的全部供应商的全部产品情况？

解析：Select * from SP  where Sno in (select Sno from SP where Pno in (select Pno from SP from SP  where Sno=’168’));

或者：Select * from SP SPX where exists (select * from SP SPY where SPY.Sno=’168’ and exists (select * from SP SPZ where SPZ.Sno=SPX.Sno and SPZ.Pno=SPY.Pno));

13. 触发器怎么工作的?

答：触发器主要是通过事件进行触发而被执行的，当对某一表进行诸如UPDATE、 INSERT、 DELETE 这些操作时，数据库就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。

14.在一个文件中有 10G 个整数，乱序排列，要求找出中位数。内存限制为 2G。只写出思路即可。(腾讯笔试题)

答：(1)把整数分成256M段，每段可以用64位整数保存该段数据个数，256M*8 = 2G内存，先清0。

       (2)读10G整数，把整数映射到256M段中，增加相应段的记数。

       (3)扫描256M段的记数，找到中位数的段和中位数的段前面所有段的记数，可以把其他段的内存释放。

       (4)因中位数段的可能整数取值已经比较小(如果是32bit整数，当然如果是64bit整数的话，可以再次分段)，对每个整数做一个记数，再读一次10G整数，只读取中位数段对应的整数，并设置记数。

       (5)对新的记数扫描一次，即可找到中位数。

如果是32bit整数，读10G整数2次，扫描256M记数一次，后一次记数因数量很小，可以忽略不记。

解释一下：假设是32bit整数，按无符号整数处理整数分成256M段? 整数范围是0 - 2^32 - 1 一共有4G种取值，4G/256M = 16，每16个数算一段 0-15是1段，16-31是一段，...整数映射到256M段中?如果整数是0-15，则增加第一段记数，如果整数是16-31，则增加第二段记数，...其实可以不用分256M段，可以分的段数少一些，这样在扫描记数段时会快一些，还能节省一些内存。

15.一般数据库若出现日志满了，会出现什么情况，是否还能使用？

答：只能执行查询等读操作，不能执行更改，备份等写操作，原因是任何写操作都要记录日志。也就是说基本上处于不能使用的状态。

17.磁盘柜上有14块73G的磁盘， 数据库为200G 大小包括日志文件，如何设置磁盘（要说明这14磁盘是怎么用的）？

答：这个问题应该是考察硬件知识和数据库物理部署。首先需要知道这些磁盘是否要用于存放数据库备份文件和数据库性能（读/写）要求。来决定raid的级别。

     1）、如果偏重于性能考虑，而且不用存放数据库备份文件的话，考虑使用raid0+1，这样可使用的磁盘容量为：14*73*50%=511G。  

     2）、如果读/写性能要求不高，而且还比较抠门的话，可以考虑raid5，这样可使用的磁盘容量为：13*73=949G。至于如何使用应该是说数据库物理文件的部署。注意说出将tempdb,data file,log file分开存放以减少I/O竞争即可。其实现在的条带化磁盘一般都会自动将文件分存，人为的分布已经越来越不重要了。

18..有两服务器群集，分别为node1和node2 现在要打win200系统补丁，打完后，要重新启动，如何打补丁，不能影响用户使用（要用群集的术语详细说明）。

    答：这个具体操作有点忘了。大致是：首先看哪个节点正在使用，通过节点IP（私有）访问另一个空闲节点，为其打上补丁，然后在群集管理器中停止该节点（也可以用命令行方式），重新启动。等到启动完毕，将切换使用节点，为另一个节点打补丁。然后重新启动。

21..有一个数据库200G大小，每天增加50M 允许用户随时访问，制定备份策略（详细说明）。

   答：这种情况可以采用增量备份方式。每周日做一次全备份，周一到周六作增量备份（由于数据量较少，可以考虑每30分钟增量备份一次）。这样可以尽量减少性能消耗，而且如果transaction log丢失的情况下，可以保证最多丢失30分钟数据。

22..管理50台数据库，日常工作是检查数据库作业是否完成，你该如何完成这项检查工作？

   答：这个比较简单。在每台机器上建立linkserver，然后在DBA管理服务器上做个分布式视图，每次查询该视图，各个机器上的作业情况一目了然。分布式视图写法：

create view vw_job

as

select '机器一' as MName,* from linkserver1..sysjobactivity

union all

select '机器二' as MName,* from linkserver2..sysjobactivity

union all

select '机器三' as MName,* from linkserver3..sysjobactivity

…

23.自定义函数和存储过程的区别是什么，什么情况下只能用自定义函数，什么情况下只能用存储过程

答：这个应该是考察存储过程编写经验。一般自定义函数主要用于其他sql中的调用，如：

select yourfunc(...) from table 这种情况下，一般只能通过函数实现。存储过程的功能要远远强于函数，例如动态执行sql（sp_executesql）的使用和一些特殊的功能，自定义函数中是不支持的，只能用存储过程实现。

24.如果想配置SQL Mail 应该在服务器安装哪些软件！

答：需要哪些软件？安个outlook express就可以了。sql server提供接口存储过程，非常简单。

25.学生其存放在Student表中，学生选课情况存放在SC表中，课程情况存放在Course表中。（经典题目）

(1)查询与“刘晨”在同一个系学习的学生。

答：SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN(SELECT Sdept FROM Student  WHERE Sname=’刘晨’)；

或者：SELECT S1.Sno,S1.Sname,S1.Sdept FROM Student S1,Student S2 WHERE S1.Sdept=S2.Sdept AND S2.Sname=’刘晨’；

(2)查询选修了课程名为“信息系统”的学生学号和姓名

答：SELECT Sno,Sname FROM Student WHERE Sno IN(SELECT Sno FROM SC) WHERE Cno IN(SELECT Cno FROM Course WHERE Cname=’信息系统’)；

或者：SELECT Sno,Sname FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname=’信息系统’；