3_一套Oracle面试题笔试题及参考答案

完成下列操作，写出相应的SQL语句

创建表空间neuspace，数据文件命名为neudata.dbf，存放在d:\data目录下，文件大小为200MB，设为自动增长，增量5MB，文件最大为500MB。(8分)
答：create tablespace neuspace datafile ‘d:\data\neudata.dbf’ size 200m auto extend on next 5m maxsize 500m;
2. 假设表空间neuspace已用尽500MB空间，现要求增加一个数据文件，存放在e:\appdata目录下，文件名为appneudata，大小为500MB，不自动增长。（5分）
答：alter tablespace neuspace add datafile ‘e:\appdata\appneudata.dbf’ size 500m;
3. 以系统管理员身份登录，创建账号tom，设置tom的默认表空间为neuspace。为tom分配connect和resource系统角色，获取基本的系统权限。然后为tom分配对用户scott的表emp的select权限和对SALARY, MGR属性的update权限。(8分)
答：create user tom identified by jack default tablespace neuspace;
Grant connect, resource to tom;
Grant select, update(salary, mgr) on scott.emp to tom;
4. 按如下要求创建表class和student。（15分）
属性
类型(长度)
默认值
约束
含义

CLASSNO 数值 (2) 无 主键 班级编号
CNAME 变长字符 (10) 无 非空 班级名称
属性
类型(长度)
默认值
约束
含义

STUNO 数值 (8) 无 主键 学号
SNAME 变长字符 (12) 无 非空 姓名
SEX 字符 (2) 男 无 性别
BIRTHDAY 日期 无 无 生日
EMAIL 变长字符 (20) 无 唯一 电子邮件
SCORE 数值 (5, 2) 无 检查 成绩
CLASSNO 数值 (2) 无 外键，关联到表CLASS的CLASSNO主键 班级编号
答：create table class
(classno number(2) constraint class_classno_pk primary key,
cname varchar2(10) not null);
create table student
(stuno number(8) constraint student_stuno_pk primary key,
sname varchar2(12) not null,
sex char(2) default ‘男’,
birthday date,
email varchar2(20) constraint student_email_uk unique,
score number(5,2) constraint student_score_ck check(score>=0 and score<=100),
classno number(2) constraint student_classno_fk references class(classno)
);
5. 在表student的SNAME属性上创建索引student_sname_idx（5分）
答：create index student_sname_idx on student(sname);
6. 创建序列stuseq，要求初值为20050001，增量为1，最大值为20059999。（6分）
答：create sequence stuseq increment by 1 start with 20050001 maxvalue 20059999 nocache nocycle;
7. 向表student中插入如下2行。（5分）
STUNO SNAME SEX BIRTHDAY EMAIL SCORE CLASSNO
从stuseq取值 tom 男 1979-2-3 14:30:25 tom@ 89.50 1
从stuseq取值 jerry 默认值 空 空 空 2
答：insert into student values(stuseq.nextval, ’tom’, ’男’, to_date(‘1979-2-3
14:30:25’, ’yyyy-mm-dd fmhh24:mi:ss’), ’tom@’, 89.50, 1);
insert into student (stuno, sname, classno) values(stuseq.nextval, ’jerry’, 2);
8. 修改表student的数据，将所有一班的学生成绩加10分。（4分

）
答：update student set score=score+10 where classno=1;
9. 删除表student的数据，将所有3班出生日期小于1981年5月12日的记录删除。（4分）
答：delete from student where classno=3 and birthday > ’12-5月-81’;
10. 完成以下SQL语句。(40分)
(1) 按班级升序排序，成绩降序排序，查询student表的所有记录。
答：select * from student order by classno, score desc;
(2) 查询student表中所有二班的成绩大于85.50分且出生日期大于1982-10-31日的男生的记录。
答：select * from student where classno=2 and score>85.50 and birthday < ’31-10月-82’ and sex=’男’;
(3) 查询student表中所有三班成绩为空的学生记录。
答：select * from student where classno=3 and score is null;
(4) 表student与class联合查询，要求查询所有学生的学号，姓名，成绩，班级名称。（使用oracle与SQL 99两种格式）
答：select s.stuno, s.sname, s.score, ame from student s, class c where s.classno=c.classno;
(5) 按班级编号分组统计每个班的人数，最高分，最低分，平均分，并按平均分降序排序。
答：select classno, count(*), max(score), min(score), avg(score) from student group by classno order by avg(score) desc;
(6) 查询一班学生记录中所有成绩高于本班学生平均分的记录。
答：select * from student where classno=1 and score > (select avg(score) from student where classno=1);
(7) 统计二班学生中所有成绩大于所有班级平均分的人数。
答：select count(*) from student where classno=2 and score > all (select avg(socre) from student group by classno);
(8) 查询平均分最高的班级编号与分数。
答：select classno, avg(score) from student group by classno having avg(score) = (select max(avg(score)) from student group by classno);
(9) 查询所有学生记录中成绩前十名的学生的学号、姓名、成绩、班级编号。
答：select stuno, sname, score, classno from (select * from student order by score desc) where rownum<=10;
(10) 创建视图stuvu，要求视图中包含student表中所有一班学生的stuno, sname, score, classno四个属性，并具有with check option限制。
答：create view stuvu
as
select stuno, sname,score,classno from student where classno=1 with check option;





1.简单SQL
1.1 T-SQL中的Switch语句
Q：case
第一种用法:直接对列进行值的判断,相当于程序中的Switch 取单个值
注意：then的值必须是同一种类型（因为列的类型只能有一个）
SELECT pId,
CASE pTypeId
WHEN 1 then '最爱'
WHEN 2 THEN '亲爱'
else '默认'
end,
pName,pCellPhone from PhoneNum

第二种用法,一般用来为列的取值范围，相当于,if else 取一个值的,取值范围
select sid,成绩=｛
CASE
when english between 90 and 100 THEN 'A'
whe

n english <60 THAN 'E'
ELSE '缺考'
end
｝
from score

例子：select *,
(case TotalMoney
when TotalMoney>6000 then '金牌'
when TotalMoney>5000 then '银牌'
else '普通'
end)
from table

1.2 T-SQL中的子查询
Q :在结果集的基础上，再次查询,
注意:一定以要给结果集取别名,否则会报错（取别名有两种方法：1，.....as 别名； 2，别名=（....））
例子：select * from (selec * from bc) as temp
select * from temp = (selec * from bc)

1.3 T-SQL中的Exists用法
Q：1.3.1 先执行 的是主查询，然后再执行子查询，将匹配行的数据显示出来
例子：select * from tbl where exists(select * from phonTYpe where tbl.pId = phonType.pId)

1.3.2 判断结果集是否存在(效率低)
Q ：例子：if exists(select * from tbl where ptId='001')
select 1
else
select 2

1.4 T-SQL中的Any
Q: /*Any or的意思*/
例子：select * from PhoneNum where pTypeId = any(select pTypeId from b)
select * from PhoneNum where pTypeId = 1 or pTypeId = 2 ....
注意：in/any/all关键字匹配结果集的时候，结果集只能有一个列
any相当于条件是很多个or
all相当于条件是很多个and

1.5 T-SQL中的All
Q：/*ALL and的意思*/
例子：select * from PhoneNum where pTypeId = all(select pTypeId from b)
select * from PhoneNum where pTypeId = 1 and pTypeId = 2 ....
ANY例子：
SELECT * from PhoneNum where pTypeId = any（select ptId from PhoneType）
ALL例子：
SELECT * from PhoneNum where pTypeId = all（select ptId from PhoneType）

2.SQL分页
2.1 SQL2008 微软官方推荐分页方式
Q：例子：select * from
(
select ROW_NUMBER() over (order by ar_id) as rnum,* from Area
) as t
where runm between 10 and 14

3.SQL中表连接查询
3.1 等值连接(内连接) inner join
Q：任何一方都必须满足连接条件,如果有一方不满足连接条件就不显示
例子：select * from PhoneNum n inner join PhoneType t on n.pTypeId = t.ptId

3.2 左连接(左外连接) left join
Q：保证左边的数据都有，根据左边数据匹配
例子：select * from PhoneNum n left join PhoneType t on n.pTypeId = t.ptId

3.3 右连接(右外连接) right join
Q：保证右边的数据都有，根据右边数据匹配
例子：select * from PhoneNum n right join PhoneType t on n.pTypeId = t.ptId

3.4 交叉连接cross join(两张表的乘积)（ 笛卡尔积）
Q :例子：select * from student cross join Score

3.5 全连接 full join（就是不管两边的表是否满足条件，全都显示，对方如果没有对应的数据，则显示null）
Q：左右两边的数据都进行匹配，相当于左连接和右连接相加。和i

nner join刚好相反
例子：select * from PhoneNum n full join PhoneType t on n.pTypeId = t.ptId

4. SQL进阶
4.1 SQL中变量
4.1.1 声明变量
declare @age int

4.1.2 变量赋值
set @age=3

4.1.3 声明并赋值
select @age = 29

4.1.4 如果表数据出现多行，则将最后一行的列赋值给变量（相当于循环赋值，自然取到最后一个值）
select @age=age from Student

4.1.5 输出变量的值
print @age

4.1.6 局部变量---先声明，再赋值
全局变量---系统变量，系统已经定义好的，用户不能定义和修改，只能读取

5. SQL中的事务 （ACID特性：原子性，一致性，隔离性，持久性）
begin transaction 开始事务
rollback transaction 回滚事务（当事务中语句出现异常）
commit transaction 提交事务（更新数据）

银行事务转账列子:
declare @err int
set @err = 0
begin transaction
begin
print '开始事务'
update bank set balance=balance-1000 where cid='0001'
set @err=@err+@@ERROR
update bank set balance=balance + 1000 where cid='0002'
set @err=@err+@@ERROR
if(@err>0)
begin
rollback transaction
print '回滚事务'
end
else
begin
commit transaction
print '提交事务'
end
end

6. SQL中存储过程
6.1 如何创建存储过程
例子：Create procedure usp_transferMoney
@intPerson varchar(20)
@outPerson varchar(20) '123' --可以给默认值，当参数有默认值的时候，执行的时候可以不传该参数
@abcPerson varchar(20)
as
select @intPerson,@outPerson,@abcPerson

6.2 存储过程带输出参数
Create procedure usp_outPut
@maxEnglish int output
as
begin
select * from Score
select @maxEnglish = Max(English) from Score
end

declare @maxEnglish int
select @maxEnglish = 0
exec usp_outPut @maxEnglish output
print @maxEnglish

6.3 调用存储过程
exec usp_transferMoney --不带参数
exec usp_transferMoney ‘001’--带参数
exec usp_transferMoney @abcPerson='234' --指定参数
注意：在执行存储过程的时候，如果带输出参数output，则在执行的时候必须显示执行，即加上“exec”

6.4 C#中如果参数是一个整型值，那么最好通过如下方式创建和赋值
SqlParameter p = new SqlParameter("@id",SqldbType.Int); //注意：这里可以直接给参数的值，但是如果是0的话，就有个漏洞，它会默认认为是个枚举
p.Direction = ParameterDirection.OutPut;
return p.value;

7. SQL中触发器
7.1 触发器定义
Q： (1)触发器是一种特殊的 存储过程
(2)触发器不能传参数，通过事件进行触发执行
例子：Create trigger tg_class on class--表名
AFTER--instead of（操作完成之

前--替换语句）|after(操作完成之后)
INSERT --、instead、update、delete
AS
BEGIN
--触发器代码
end
Select * from inserted --保存了引发新增触发器的新增数据，只能在触发器中访问
--删除触发器
drop trigger tg_class

7.2 触发类型:
Q :(1)after和for是在 执行操作后 触发
(2)instead of 是 执行操作之前 触发(替换触发器)，但是不会执行原语句

7.3 触发器触发条件:
Q:(1)update
(2)insert
(3)delete

7.4 触发器实例
---针对tbL_abc表的新增之后的触发器
Q：例子：Create Trigger triggerName on tbL_abc
after
insert
as
begin
select * from inserted --保存了引发新增触发器的新增数据，只能在触发器中访问
end

8. 索引
8.1 什么是索引
Q：就是为某个表，某个列建立一个查找目录
如果没有目录，汉语词典就要一页一页的翻。
有了目录直接翻目录，快速定位到查找位置。

8.2 索引类型（索引必须建立在某个列上面）
Q： （1）：聚集索引(类似于字典的拼音目录):
数据的排列顺序，按照聚集索引排列(控制表的物理顺序)
因此每个表只能建立一个聚集索引
（当为某个字段建立聚集索引后，那么表的数据就会按照当前这个字段排序）
（2）：非聚集索引(类似于字典的偏旁部首目录):
非聚集索引不会改变表的物理顺序（索引的存储顺序与实际的物理顺序无关）
每个表可以建立多个非聚集索引
（3）：唯一索引
当为某个列建立唯一索引后，那么这列数据就不能有重复

9. 填充因子
Q：索引树的基本单位是索引页
填充因子 ----- 就是为每页索引设置预留空间，
在将来加入新索引的时候，就只需要更新
当前索引页，而不需要更新索引树。填充因子就是指占用当前索引页的大小。

例如：如每页索引 2M 大小，当填充因子设置为60%，
那么每个索引页只存放2M*60%的数据，剩下2M*40%预留空间留给将来要加入索引项使用。

10. 临时表
Q：10.1 临时表是存在缓存中，而不是写在文件中，可以在系统数据库-->tempdb-->中查找
数据库连接断开，临时表就会自动清除。

10.2 局部临时表
Q：生命周期在当前会话，当前会话结束就销毁临时表
相当于C#的局部成员
创建时表名前加一个 ,#号
create table #tempUsers
(
id int identity(1,1),
name varchar(20)
)

10.3 全局临时表
Q：多个用户可以共享这个全局临时表
当所有会话都退出的时候，这个全局临时表才会被销毁
相当于C#的static 静态成员
创建时表名前加两个 ,##号
crea

te table ##tempUsers
(
id int identity(1,1),
name varchar(20)
)