笔试数据库 就业强化测试

就业强化测试

1.共享锁与排它锁的区别

共享锁【S锁】

又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A,其他

事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务

可以读A,但在T释放A上的S锁之前不能对A做任何修改。

排他锁【X锁】

又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A,其他事

务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁

之前不能再读取和修改A。

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

触发器触发时机可分为：before|after.触发的顺序为：before触发器，约束检查，更新表，after触发器

行级触发器是目标表（触发器所在的表）中的每一行数据发生变化都激发一次触发器，创建时候添加子句foreachrow,而语

句级触发器对应一个语句只执行一次，比如在批量修改、删除时候，语句触发器只执行一次，行级触发器触发多次。

行级触发器|语句级触发器

每一行触发|不管影响多少行，只触发一次..

若事件没有产生更改影响，不触发|不论是否更改了任何行，都触发

after行触发器锁定数据行|并不锁定行

3.有表A(fa,fb,fc,fd)和B(fa,fb,fc,fd)令其连接(A.fa=B.faandA.fb=B.fb)通过更新A表的fc更

新B表的fc写出SQL语句。

思路：

对表A建一个触发器，当更新A时候，同时更新满足A.fa=B.faandA.fb=B.fb要的B中fc列的值

实现：

createtableA（

faint,

fbint,

fcint,

fdint

)

createtableB(

faint,

fbint,

fcint,

fdint

)

insertintoAvalues(1,1,1,1);

insertintoAvalues(2,2,2,2);

insertintoAvalues(3,3,3,3);

insertintoBvalues(1,1,3100,90);

insertintoBvalues(2,2,3000,2);

insertintoBvalues(3,3,9000,3);

createtriggera2bonAforupdate

as

declare@tempaint,@tempbintz@tempcint;

if(exists(select*frominsertedxinnerjoinByonx.fa=y.faandx.fb=y.fb))

begin

select@tempa=fa,@tempb=fbz@tempc=fcfrominserted;

updateBsetfc=@tempcwherefa=@tempaandfb=@tempb;

end

updateAsetfc=345wherefa=1;

updateAsetfc=9090wherefa=2;

select*fromA;

select*fromB;

droptriggera2b;

droptableA;

droptableB;

4,写一个自动编号的存储过程如:200408010001?

1、测试数据

createtablexx(

idnvarchar(13),

xxxnvarchar(lO)

)

2、这里使用一个自定义函数函数

createviewv_getdateasselectdt=convert(varchar,getdate()z012)

createfunctionIdGenerator()

returnsVarchar(13)

As

Begin

Declare@idnvarchar(10)z@dtvarchar(8)

select@dt=dtfromv_getdate

select@id=idfromxx;

IF@idIsnull

Set@id=@dt+,OOOl,

Else

Set@id=@dt+Right(,0000'+Cast(Cast(Right(@id,3)AsInt)+1AsVarchar),4)

return@id

End

3、调用

insertintoxxvalues(dbo.IdGenerator()/9090opop');

5.unionall和union的区别？

union取并集后去掉重复，unionall取并集后不去重复

6.请问在SQL2000中怎么区分登入，用户，角色，并用例子举例说明。再问：

(1)登入ID是不是就是用户名？(当我创建了一个登入，我在点击登入所对应的数据，实例中的用户一

栏看到登入ID与用户名一致)

(2)一个登入ID是不是只能对应一个用户？

1、登录ID仅能使您连接到SQLServer实例。

登录帐户映射到您有权访问的任何数据库中的用户

例如sa是一个登录，那么他在每个数据库中用户名叫做dbo

角色如：public/db_owner/db_datareader/db_datawriter等

只有给用户赋予角色，该用户才有相应的操作数据库的权限

角色：完成特定的、与服务器相关的管理任务所需的权限，一个用户可以属于多个角色。

2、同一数据库中，一个登录只能对应一个用户，一个登录，在不同的数据库中，允许对应不同的用户

7.a部门表b员工表

a表字段（

id一部门编号

departmentName-部门名称

)

b表字段(

id一部门编号

employee-员工名称

)

问题:如何一条sql语句查询出每个部门共有多少人？

selectcount(*)人数,a.departmentNamefromaleftjoinbona.id=b.idgroupbydepa

rtmentName

8.用一条SQL语句查询出表STU每门课都大于80分的学生姓名

namekechengfenshu

张三语文81

张三数学75

李四语文76

李四数学90

王五语文81

王五数学100

王五英语90

selectdistinctnamefromstuwherenamenotin(

selectfromstualeftouterjoin(select*fromstuwherefenshu>80)bon=

anda.kecheng=b.kechenganda.fenshu=b.fenshuwhereb.fenshuisnull

)

9.要求在查询分析器中完成，题目如下：

表className中有如下分类：

classIDclassName

1衣服

2裤子

表productInfo有如下记录：

productIDproductNameparentIDclickNum

1男士衣服190一衣服类别中这条记录的点击率最高

2女士衣服180

3男士裤子270

4女士裤子290一裤子类别中这条记录点击率最高

现在要求分别把衣服,裤子,帽子,鞋子这些类别中点击率最高的一条记录找出来,然后再降序排列，结果应

如下：

productIDproductNameclickNum

1男士衣服90

4女士裤子90

selectproductID,productNclickNumfromproductinfopinnerjoin

(

selectmax(clickNum)cn,classIDfromclassName

leftouterjoinproductinfoonparentID=classID

groupbyclassic!

)b

onp.parentld=b.classldandp.clickNum=

orderbydesc

有一张表,字段的值是这样的:其中的

10.namevarchar2(20),sexchar(2),scorenumber(3)0S

CORE字段为分数字段。请用一条SQL语句把九十分以上的显示为A。九十到七十分的为B。七十分

以下的为C。

selectname,sex,

(case

whenscore>90then'A'

whenscore>=70andscore<=90then'B'

whenscore<70then'C'end)score

fromstu

11.有一个表，其中有一个字段为自动增长的数据类型。请在ORACLE中实现。

1、思路：使用Oracle数据库的序列

2、实现过程：

一创建序歹从1000开始，至I」99999999结束

createsequenceseq_aminvalue1000maxvalue99999999startwith1000incrementby1nocache;

一查询序列

selectseq_a.nextvalfromdual;

一创建测试表

createtableseq_t(

tidinteger,

tnamevarchar2(20)

)；

一插入数据

insertintoseq_tvalues(seq_a.nextval,‘郭靖')；

insertintoseq_tvalues(seq_a.nextval,‘黄蓉')；

一查询

select*fromseq_t;

--删除表

droptableseq_t;

一删除序列

dropsequenceseq_a

参考资料:CREATESEQUENCEsequence_name[INCREM

WITHn]4[{MAXVALUEn}]4[{MINVALUEn)]&[{CYCL

CHEn|NOCACHE}];

INCREMENTBY：指定步长。$

STARTWITH：指定初始值。4

MAXVALUE：指定序罪」可生成的最大值。6

MINVALUE：指定序列的最小值。&

CYCLE■•配置序列在达到界限值时重复编号&

NOCYCLE--达到界限值时不重复编号，一直累加，不循环，这是黑

CACHE--定义在内存中保留的序列编号块的大小，默认值为20.6

NOCACHE一强制数据词典对于生成的每个序列编号进行更新，保i

缺,但这样会降低性能.

12.建立一个用户和表空间，在这个用户和表空间下建立一张表。并授予SCOTT用户查询权利。

—createuserdemouseridentifiedbydemouserdefaulttablespacedemospacequota10Mondemospace;

一已经有用户demouser现在删除

—dropuserdemousercascade;

-3、给用户授权，以便用户可以登录到oracle而且可以建表

—grantdbatodemouser;

・・4、切换用户登录，创建表demotable,并在表中插入数据

-connectdemouser/demouser;

—createtabledemotable(

--idint,

—namevarchar2(10)

-)

-insertintodemotablevalues(1,‘张飞')；

-insertintodemotablevalues(2,‘关羽')；

一5、使用scott登录，查询数据

connscott/tiger;

select*fromdemouser.demotable;

一视图不存在，证明没有权限

--6、现在再给scott授予在demotable上的查询权限

-grantselectondemouser.demotabletoscott;

13.写一个过程，计算某个月有多少天。

计算当前年，某个月中有多少天，Oracle中写存储过程：

createorreplaceprocedurecaulDcount(mininteger)

is

cvarchar2(3);

begin

selectto_char(last_day(to_date(to_char(sysdate/YYYY')11'-'11m11'-Ol'/YYYY-MM-DD'JJ/DD')intocfromdua

I；

dbms_output.put_line(c);

end;

调用：

setserveroutputon;

executecaulDcount(4);

30

PL/SQL过程已成功完成。

14.有一章表，字段为name,sex,score,score字段为分数字段，查询出这个班的第五名到第七名的人

的姓名。

准备数据：

createtablestu(

namenvarchar(lO),

sexnvarchar(2),

scorefloat

)

insertintostuvalues男’,80);

insertintostuvalues(七》「女',78.5);

insertintostuvalues('cc','男',38.);

insertintostuvalues('dd','女',90);

insertintostuvalues('ee','女',100);

insertintostuvalues('ff,'男',30);

insertintostuvalues('gg','男’,80);

insertintostuvalues('hh','男',98);

insertintostuvalues男',94);

insertintostuvalues('jj'J男',92);

对比调用:

select*from(

selecttop3*from(

selecttop7*fromstuorderbyscoredesc

)torderbyscoreasc

)tlorderbyscoredesc

select*fromstuorderbyscoredesc

15.做一个外键关联的两个表。然后用触发器做级联更新。

知识点：

如果一个表的外键包含对定义的DELETE/UPDATE操作级联，则不能为表上定义msteadofDELETE/UPDATE触发器

请参考附件案例

此题实现思路

createtablenewObj(

idnvarchar(20)notnull,

namenvarchar(50),

contentnvarchar(400)z

constraintPK_nobjprimarykey(ID)

)

createtablemyNews(

idnvarchar(20)notnull,

objidnvarchar(20)notnull,

titlenvarchar(60)notnull,

authornvarchar(40),

fromwherenvarchar(60),

constraintpk_mynewsprimarykey(id));

insertintonewobj(id,name)values('10'「最新新闻')

insertinto171丫11«\/\/5。€1,0印€1,俄上加己1g5('10'/10','春运火车票开售');

insertintomynews(id,objid,title)values('20')10','我的世界只有你');

createtriggert4updateonnewobj

forupdate

as

begin

updatemynewssetobjid=(selectidfrominserted)whereobjid=(selectidfromdeleted);

end;

select*fromnewOBJ

select*frommynews

updatenewObjsetid='3000'whereid='10'

16.ORACLE所支持的事务隔离级别

ORACLE的隔离级别

ORACLE提供了SQL92标准中的readcommitted和serializable,同时提供了非SQL92标准的read-only。

1、readcommitted

这是ORACLE缺省的事务隔离级别。

事务中的每一条语句都遵从语句级的读一致性。

保证不会脏读；但可能出现非重复读和幻像。

2、serializable

2.1、简单地说，serializable就是使事务看起来象是-一个接着一个地顺序地执行。

2.2、仅仅能看见在本事务开始前由其它事务提交的更改和在本事务中所做的更改。

2.3、保证不会出现非重复读和幻像。

2.4、Serializable隔离级别提供了read-only事务所提供的读一致性（事务级的读一致性），同时又允许DML操作。

2.5、如果有在serializable事务开始时未提交的事务在serializable事务结束之前修改了serializable事务将要修改的行并进二

e事务不会读到这些变更，因此发生无法序列化访问的错误。（换一种解释方法：只要在serializable事务开始到结束之间有其，

务要修改的东西进行了修改并提交了修改，则发生无法序列化访问的错误。）

3、read-only

遵从事务级的读一致性，仅仅能看见在本事务开始前由其它事务提交的更改。

不允许在本事务中进行DML操作。

readonly是serializable的子集。它们都避免了非重复读和幻像。区别是在readonly中是只读：而在serializable中可以支

ExportwithCONSISTENT=Ysetsthetransactiontoread-only.

4、readcommitted和serializable的区别和联系

事务1先于事务2开始，并保持未提交状态。事务2想要修改正被事务1修改的行。事务2等待。如果事务1回滚，则事务2

ed还是serializable方式）进行它想要做的修改。如果事务1提交，则当事务2是read8mmitted方式时，进行它想要做住

Sizable方式时，失败并报错''Cannotserializeaccess",因为事务2看不见事务1提交的修改，且事务2想在事务一修改的

17.写出ORACLE将A表中全部数据转移到B表的SQL.

createtableAasselect*fromB

18.ORACLE查看表结构的命令

desctable_name

比如：descemp;就是查看表emp的结构

19.取数据库的前几条记录的平均数据

1、在oracle中，我们取emp表前三条数据，然后计算前三条数据中工资的平均值。

selectavg(sal)fromempwhererownum<=3;

2、在SQLServer2000中

selecttop3avg(sal)fromemp;

3、在SQLserver2005中

selectavg(sal)fromempwhererow_number()<=3

4、MySql中

selectavg(sal)from(select*fromemplimit0,3)e;

20.存储过程和函数的区别

1、存储过程是使用EXEC命令独立调用的，而用户自定义函数是在另一个SQL语句中调用的。

2、存储程序是允许用户和程序去使用存储过程，而不是允许其存取表格，这样能够增强程序安全性。与标

准的SQLServer相比，存储程序限制用户行动权限方面更为细化。例如，如果你有一个货存表格，每次

卖出一个货物收银员都要对表格进行更新一次(从货存中把该货品减去一件)。你可以给收银员设置权限，

允许其使用decrementjtem存储过程，而不是允许他们有任意修改或村表格的权限。

3、函数必须始终返回一个值(一个标量值或一个表格)。而存储过程可以返回一个标量值、一个表值或无需

返回值。

21.游标的作用？如何知道游标已经到了最后？

游标的作用：

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果;每个游标区都有一个名字；用户可以用SQL语句逐一从游

变量，交由主语言进一步处理;主语言是面向记录的，一组主变量一次只能存放一条记录；仅使用主变量并不能完全满足SQL语，

要求；嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式

在oracle中，当执行完fetch后判断游标的％notfound是true的话就没有纪录了.

在sqlserver中，当执行完fetch后判断游标的@@FETCH.STATUS<>0则说明已经没有纪录了

22.学生Student(student_id,stLident_name)借阅书Books(student_id,book_name)ffl

notexist关键字查询未借阅过书的学生的学生信息列表。

--学生Student(studentjd,student_name)

一借阅书

Books(student_idzbook_name)

--用notexist关键字查询未借阅过书的学生的学生信息列表。

createtableStudent(

studentjdintprimarykey,

studetn_namenvarchar(lO)

)；

createtableBooks(

studentjdintz

book_namenvarchar(lO)

)

insertintoStudentvalues(1/张三')；

insertintoStudentvalues(2,'李浩');

insertintoStudentvalues(3/赵某')；

insertintoStudentvalues(4「李宇春')；

insertintoStudentvalues(5/张杰');

insertintoStudentvalues(6「凤凰传奇')；

insertintoBooksvalues(1,'伤城')；

insertintoBooksvalues(1/家')；

insertintoBooksvalues(5/故乡')；

select*fromStudentwherenotexists(select*frombookswherestudentjd=

Student.student_id);

23.在Mysql5数据库中，有2张表：A[id,name]和B[id,aid,name,others],B表中的字段aid与A

表中字段id外键关联。请写出一条sql语句，查出表A中存在，但表B中存在的记表？

createtableA(

idint,

namenvarchar(lO)

)

createtableB(

idint,

aidint,

namenvarchar(lO),

othersnvarchar(lO)

)

deletefroma;

insertintoavalues(1/鲁迅')；

insertintoavalues(2「巴金')；

insertintoavalues(3「贾平凹');

insertintobvalues(100,1/三味书屋','qq');

insertintobvalues(101,1,'狂人日记

insertintobvalues(102z2/^'/qq');

select*fromb;

・・a、b中都有的记录

select*fromainnerjoinbona.id=b.aid;

中存在，b中不存储的记录

select*fromawherenotexists(select*frombwhereb.a