数据库方面面试题
1. 存储过程和函数的区别
存储过程是用户定义的一系列sql语句的集合,涉及特定表或其它对象的任务,用户可以调用存储过程,而函数通常是数据库已定义的方法,它接收参数并返回某种类型的值并且不涉及特定用户表。
2. 事务是什么?
事务是作为一个逻辑单元执行的一系列操作,一个逻辑工作单元必须有四个属性,称为 ACID(原子性、一致性、隔离性和持久性)属性,只有这样才能成为一个事务:
原子性
事务必须是原子工作单元;对于其数据修改,要么全都执行,要么全都不执行。
一致性
事务在完成时,必须使所有的数据都保持一致状态。在相关数据库中,所有规则都必须应用于事务的修改,以保持所有数据的完整性。事务结束时,所有的内部数据结构(如 B 树索引或双向链表)都必须是正确的。
隔离性
由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态,要么是另一并发事务修改它之前的状态,要么是另一事务修改它之后的状态,事务不会查看中间状态的数据。这称为可串行性,因为它能够重新装载起始数据,并且重播一系列事务,以使数据结束时的状态与原始事务执行的状态相同。
持久性
事务完成之后,它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。
3. 游标的作用?如何知道游标已经到了最后?
游标用于定位结果集的行,通过判断全局变量@@FETCH_STATUS可以判断是否到了最后,通常此变量不等于0表示出错或到了最后。
4. 触发器分为事前触发和事后触发,这两种触发有和区别。语句级触发和行级触发有何区别。
事前触发器运行于触发事件发生之前,而事后触发器运行于触发事件发生之后。通常事前触发器可以获取事件之前和新的字段值。
语句级触发器可以在语句执行前或后执行,而行级触发在触发器所影响的每一行触发一次。
5:左连接,右连接,全连接,内连接
我们介绍了表连接,更确切的说是inner joins內连接。內连接仅选出两张表中互相匹配的记录.因此,这会导致有时我们需要的记录没有包含进来。
为更好的理解这个概念,我们介绍两个表作演示。苏格兰议会中的政党表(party)和议员表(msp)。
party(Code,Name,Leader) Code: 政党代码 Name: 政党名称 Leader: 政党领袖 |
msp(Name,Party,Constituency) Name: 议员名 Party: 议员所在政党代码 Constituency: 选区 |
在介绍左连接、右连接和全连接前,有一个数据库中重要的概念要介绍一下,即空值(NULL)。有时表中,更确切的说是某些字段值,可能会出现空值, 这是因为这个数据不知道是什么值或根本就不存在。
空值不等同于字符串中的空格,也不是数字类型的0。因此,判断某个字段值是否为空值时不能使用=,<>这些判断符。必需有专用的短语:IS NULL 来选出有空值字段的记录,同理,可用 IS NOT NULL 选出不包含空值的记录。
例如:下面的语句选出了没有领导者的政党。(不要奇怪,苏格兰议会中确实存在这样的政党)
SELECT code, name FROM party WHERE leader IS NULL |
又如:一个议员被开除出党,看看他是谁。(即该议员的政党为空值)
SELECT name FROM msp WHERE party IS NULL |
好了,让我们言归正传,看看什么叫左连接、右连接和全连接。A left join(左连接)包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。同理,也存在着相同道理的 right join(右连接),即包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。
而full join(全连接)顾名思义,左右表中所有记录都会选出来。讲到这里,有人可能要问,到底什么叫:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。
我们来看一个实例:
SELECT msp.name, party.name FROM msp JOIN party ON party=code |
这个是我们上一节所学的Join(注意:也叫inner join),这个语句的本意是列出所有议员的名字和他所属政党。
很遗憾,我们发现该查询的结果少了两个议员:Canavan MSP, Dennis。为什么,因为这两个议员不属于任何政党,即他们的政党字段(Party)为空值。那么为什么不属于任何政党就查不出来了?这是因为空值在作怪。因为议员表中政党字段(Party)的空值在政党表中找不到对应的记录作匹配,即FROM msp JOIN party ON party=code 没有把该记录连接起来,而是过滤出去了。在该短语中,msp在Join的左边,所有称为左表。party在Join的右边,所有称为右表。
现在再看看这句话,“包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录”,意思应该很明白了吧。执行下面这个语句,那两个没有政党的议员就漏不了了。
SELECT msp.name, party.name FROM msp LEFT JOIN party ON party=code |
关于右连接,看看这个查询就明白了:
SELECT msp.name, party.name FROM msp RIGHT JOIN party ON msp.party=party.code |
这个查询的结果列出所有的议员和政党,包含没有议员的政党,但不包含没有政党的议员。 那么既要包含没有议员的政党,又要包含没有政党的议员该怎么办呢,对了,全连接(full join)。
SELECT msp.name, party.name FROM msp FULL JOIN party ON msp.party=party.code |
题目1:
======
为管理岗位业务培训信息,建立3个表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
1. 使用标准SQL嵌套语句查询选修课程名称为 税收基础 的学员学号和姓名
Where [S#] IN ( Select [S#] FROM C,SC
Where C.[C#]=SC.[C#] AND CN=N'税收基础')
2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
Where S. [ S# ] = SC. [ S# ] AND SC. [ C# ] = ' C2 '
3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
Where [ S# ] NOT IN
( Select [ S# ] FROM SC
Where [ C# ] = ' C5 ' )
4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
Select SN,SD FROM S
Where [S#] IN ( Select [S#] FROM SC RIGHT JOIN
C ON SC.[C#]=C.[C#]
GROUP BY [S#]
HAVING COUNT(*)=COUNT([S#] ) )
经过调试验证的正确答案:
SELECT SN, SD FROM S
WHERE S#
IN ( SELECT SC.S#
FROM SC RIGHT JOIN C
ON SC.C# = C.C#
GROUP BY SC.S# -- 在结果集中以学生分组,分组后的 SC.C#选课数=C.C#课程数 即为全部课程
HAVING COUNT ( distinct (SC.C#)) -- 注意:一个学生同一门课程可能有多条成绩记录,需要distinct
= ( select count ( * ) from C ) -- 注意:HAVING条件不能用COUNT(distinct(SC.C#)) = COUNT(distinct(C.C#)
)--子查询获得选修全部课程的学生学号
5. 查询选修了课程的学员人数
6. 查询选修课程超过5门的学员学号和所属单位
Where [ S# ] IN ( Select [ S# ] FROM SC
GROUP BY [ S# ]
HAVING COUNT ( DISTINCT [ C# ] ) > 5 )
题目2:
======
已知关系模式:
S (SNO,SNAME) 学生关系。SNO 为学号,SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
1. 找出没有选修过“李明”老师讲授课程的所有学生姓名
Where NOT EXISTS ( Select * FROM SC,C
Where SC.CNO = C.CNO
AND CNAME = ' 李明 '
AND SC.SNO = S.SNO)
2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
FROM S , SC ,
( Select SNO FROM SC
Where SCGRADE < 60
GROUP BY SNO
HAVING COUNT ( DISTINCT CNO) >= 2 ) A
Where S.SNO = A.SNO AND SC.SNO = A.SNO
GROUP BY S.SNO,S.SNAME
3. 列出既学过“1”号课程,又学过“2”号课程的所有学生姓名
FROM S,
( Select SC.SNO FROM SC,C
Where SC.CNO = C.CNO
AND C.CNAME IN ( ' 1 ' , ' 2 ' )
GROUP BY SNO
HAVING COUNT ( DISTINCT CNO) = 2
)SC
Where S.SNO = SC.SNO
4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
FROM S,
( Select SC1.SNO FROM SC SC1,C C1,SC SC2,C C2
Where SC1.CNO = C1.CNO AND C1.NAME = ' 1 '
AND SC2.CNO = C2.CNO AND C2.NAME = ' 2 '
AND SC1.SCGRADE > SC2.SCGRADE ) SC
Where S.SNO = SC.SNO
5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
FROM S,
( Select SC1.SNO, [ 1号课成绩 ] = SC1.SCGRADE, [ 2号课成绩 ] = SC2.SCGRADE
FROM SC SC1,C C1,SC SC2,C C2
Where SC1.CNO = C1.CNO AND C1.NAME = ' 1 '
AND SC2.CNO = C2.CNO AND C2.NAME = ' 2 '
AND SC1.SCGRADE > SC2.SCGRADE) SC
Where S.SNO = SC.SNO
题目3:
======
有如下表记录:
ID Name EmailAddress LastLogon
100 test4 test4@yahoo.cn 2007-11-25 16:31:26
13 test1 test1@yahoo.cn 2007-3-22 16:27:07
19 test1 test1@yahoo.cn 2007-10-25 14:13:46
42 test1 test1@yahoo.cn 2007-11-20 14:20:10
45 test2 test2@yahoo.cn 2007-4-25 14:17:39
49 test2 test2@yahoo.cn 2007-5-25 14:22:36
用一句sql查询出每个用户最近一次登录的记录(每个用户只显示一条最近登录的记录)
SELECT a. * from users a inner join
( SELECT [ Name ] , LastLogon = MAX (LastLogon) FROM users GROUP BY [ Name ] ) b
on a. [ Name ] = b. [ Name ] and a. [ LastLogon ] = b. [ LastLogon ]
方法二:
SELECT a. * from users a inner join
( SELECT Name, MAX (LogonID) LogonID FROM users GROUP BY [ Name ] ) b
on a.LogonID = b.LogonID
-- where a.LogonId=b.LogonId