【SQL】SQL Server面试题

冷不防 2022-02-25 02:55 242阅读 0赞

### 1. 理论题 ###

1.  触发器的作用？

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

1.  什么是存储过程？用什么来调用？

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

1.  索引的作用？和它的优点缺点是什么？

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

1.  什么是内存泄漏？

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

1.  维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？

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

1.  什么是事务？什么是锁？

答：事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

锁：在所以的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

1.  什么叫视图？游标是什么？

答：视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要

1.  你能向我简要叙述一下SQL Server 2000中使用的一些数据库对象吗?

你希望听到的答案包括这样一些对象:表格、视图、用户定义的函数，以及存储过程;如果他们还能够提到像触发器这样的对象就更好了。如果应聘者不能回答这个基本的问题，那么这不是一个好兆头。

1.  NULL是什么意思?

NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。假设您的SQL Server数据库里有ANSI\_NULLS，当然在默认情况下会有，对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 UNKNOWN值进行比较，并在逻辑上希望获得一个答案。您必须使用IS NULL操作符。

1.  什么是索引?SQL Server 2000里有什么类型的索引?

简单地说，索引是一个数据结构，用来快速访问数据库表格或者视图里的数据。在SQL Server里，它们有两种形式:聚集索引和非聚集索引。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表格的哪个(或哪些)字段，这些字段都会按顺序被保存在表格。由于存在这种排序，所以每个表格只会有一个聚集索引。非聚集索引在索引的叶级有一个行标识符。这个行标识符是一个指向磁盘上数据的指针。它允许每个表格有多个非聚集索引。

1.  什么是主键?什么是外键?

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

1.  什么是触发器?SQL Server 2000有什么不同类型的触发器?

触发器是一种专用类型的存储过程，它被捆绑到SQL Server 2000的表格或者视图上。在SQL Server 2000里，有INSTEAD-OF和AFTER两种触发器。INSTEAD-OF触发器是替代数据操控语言(Data Manipulation Language，DML)语句对表格执行语句的存储过程。例如，如果我有一个用于TableA 的INSTEAD-OF-UPDATE 触发器，同时对这个表格执行一个更新语句，那么INSTEAD-OF-UPDATE触发器里的代码会执行，而不是我执行的更新语句则不会执行操作。

AFTER触发器要在DML语句在数据库里使用之后才执行。这些类型的触发器对于监视发生在数据库表格里的数据变化十分好用。

1.  您如何确一个带有名为Fld1字段的TableB表格里只具有Fld1字段里的那些值，而这些值同时在名为TableA的表格的Fld1字段里?

使用外键限制。外键限制用来维护引用的完整性。它被用来确保表格里的字段只保存有已经在不同的(或者相同的)表格里的另一个字段里定义了的值。这个字段就是候选键(通常是另外一个表格的主键)。

1.  你可以用什么来确保表格里的字段只接受特定范围里的值?

Check限制，它在数据库表格里被定义，用来限制输入该列的值。

1.  什么是相关子查询?如何使用这些查询?

相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会真正请求外部查询的值，从而形成一个类似于循环的状况。

1.  什么是SQL注入式攻击？

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

⑴ 某个ASP.NET Web应用有一个登录页面，这个登录页面控制着用户是否有权访问应用，它要求用户输入一个名称和密码。

⑵ 登录页面中输入的内容将直接用来构造动态的SQL命令，或者直接用作存储过程的参数。下面是ASP.NET应用构造查询的一个例子：

System.Text.StringBuilder query = new System.Text.StringBuilder(
       "SELECT * from Users WHERE login = '")
       .Append(txtLogin.Text).Append("' AND password='")
       .Append(txtPassword.Text).Append("'");

⑶ 攻击者在用户名字和密码输入框中输入”’or ’1’=’1”之类的内容。

⑷用户输入的内容提交给服务器之后，服务器运行上面的ASP.NET代码构造出查询用户的SQL命令，但由于攻击者输入的内容非常特殊，所以最后得到的SQL命令变成：SELECT \* from Users WHERE login = ‘’ or ‘1’=‘1’ AND password = ‘’ or ‘1’=‘1’。

⑸ 服务器执行查询或存储过程，将用户输入的身份信息和服务器中保存的身份信息进行对比。

⑹ 由于SQL命令实际上已被注入式攻击修改，已经不能真正验证用户身份，所以系统会错误地授权给攻击者。

如果攻击者知道应用会将表单中输入的内容直接用于验证身份的查询，他就会尝试输入某些特殊的SQL字符串篡改查询改变其原来的功能，欺骗系统授予访问权限。

系统环境不同，攻击者可能造成的损害也不同，这主要由应用访问数据库的安全权限决定。如果用户的帐户具有管理员或其他比较高级的权限，攻击者就可能对数据库的表执行各种他想要做的操作，包括添加、删除或更新数据，甚至可能直接删除表

1.  如何防范SQL注入式攻击？

好在要防止ASP.NET应用被SQL注入式攻击闯入并不是一件特别困难的事情，只要在利用表单输入的内容构造SQL命令之前，把所有输入内容过滤一番就可以了。过滤输入内容可以按多种方式进行。

⑴ 对于动态构造SQL查询的场合，可以使用下面的技术：

第一：替换单引号，即把所有单独出现的单引号改成两个单引号，防止攻击者修改SQL命令的含义。再来看前面的例子，"SELECT \* from Users WHERE login = ‘’’ or ‘‘1’’=’‘1’ AND password = ‘’’ or ‘‘1’’=’‘1’"显然会得到与"SELECT \* from Users WHERE login = ‘’ or ‘1’=‘1’ AND password = ‘’ or ‘1’=‘1’"不同的结果。

第二：删除用户输入内容中的所有连字符，防止攻击者构造出类如"SELECT \* from Users WHERE login = ‘mas’ – AND password =’’"之类的查询，因为这类查询的后半部分已经被注释掉，不再有效，攻击者只要知道一个合法的用户登录名称，根本不需要知道用户的密码就可以顺利获得访问权限。

第三：对于用来执行查询的数据库帐户，限制其权限。用不同的用户帐户执行查询、插入、更新、删除操作。由于隔离了不同帐户可执行的操作，因而也就防止了原本用于执行SELECT命令的地方却被用于执行INSERT、UPDATE或DELETE命令。

⑵用存储过程来执行所有的查询。SQL参数的传递方式将防止攻击者利用单引号和连字符实施攻击。此外，它还使得数据库权限可以限制到只允许特定的存储过程执行，所有的用户输入必须遵从被调用的存储过程的安全上下文，这样就很难再发生注入式攻击了。

⑶ 限制表单或查询字符串输入的长度。如果用户的登录名字最多只有10个字符，那么不要认可表单中输入的10个以上的字符，这将大大增加攻击者在SQL命令中插入有害代码的难度。

⑷检查用户输入的合法性，确信输入的内容只包含合法的数据。数据检查应当在客户端和服务器端都执行——之所以要执行服务器端验证，是为了弥补客户端验证机制脆弱的安全性。

在客户端，攻击者完全有可能获得网页的源代码，修改验证合法性的脚本（或者直接删除脚本），然后将非法内容通过修改后的表单提交给服务器。因此，要保证验证操作确实已经执行，唯一的办法就是在服务器端也执行验证。你可以使用许多内建的验证对象，例如 RegularExpressionValidator，它们能够自动生成验证用的客户端脚本，当然你也可以插入服务器端的方法调用。如果找不到现成的验证对象，你可以通过CustomValidator自己创建一个。

⑸ 将用户登录名称、密码等数据加密保存。加密用户输入的数据，然后再将它与数据库中保存的数据比较，这相当于对用户输入的数据进行了”消毒”处理，用户输入的数据不再对数据库有任何特殊的意义，从而也就防止了攻击者注入SQL命令。System.Web.Security.FormsAuthentication类有一个 HashPasswordForStoringInConfigFile，非常适合于对输入数据进行消毒处理。

⑹ 检查提取数据的查询所返回的记录数量。如果程序只要求返回一个记录，但实际返回的记录却超过一行，那就当作出错处理

### 2.实践题 ###

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嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?

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)

2.（1）查询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

（2）要求是查询表A中存在ID重复三次以上的记录,完整的查询语句如下：

SELECT \* from A WHERE ID in(select ID from A group by ID having COUNT(ID)>3)

3.连接查询示例

create table testtable1
    (
     id int IDENTITY,
     department varchar(12)
    )
    select * from testtable1
    insert into testtable1 values('设计')
    insert into testtable1 values('市场')
    insert into testtable1 values('售后')
    
    create table testtable2
    (
     id int IDENTITY,
     dptID int,
     name varchar(12)
    )
    insert into testtable2 values(1,'张三')
    insert into testtable2 values(1,'李四')
    insert into testtable2 values(2,'王五')
    insert into testtable2 values(3,'彭六')
    insert into testtable2 values(4,'陈七')

答案是：

SELECT testtable2.*  , ISNULL(department,'无工作')
    FROM testtable1 right join testtable2 on testtable2.dptID = testtable1.ID

3.题目2:

问题描述:

S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名
     
    C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
     
    SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

1.  找出没有选修过“李明”老师讲授课程的所有学生姓名

Select SNAME FROM S Where NOT EXISTS
    ( Select * FROM SC,C Where SC.CNO=C.CNO AND CNAME='李明' AND SC.SNO=S.SNO)

1.  列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE) 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

1.  列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

Select S.SNO,S.SNAME 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

1.  列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号

Select S.SNO,S.SNAME 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

1.  列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

Select S.SNO,S.SNAME,SC.[1号课成绩],SC.[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

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

select name from table group by name having min(fenshu)>80

（2） 学生表 如下:  
自动编号 学号 姓名 课程编号 课程名称 分数  
1 2005001 张三 0001 数学 69  
2 2005002 李四 0001 数学 89  
3 2005001 张三 0001 数学 69  
删除除了自动编号不同, 其他都相同的学生冗余信息

delete tablename where 自动编号 not in(select min( 自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

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

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

6.行转列  
原数据：  
![在这里插入图片描述][20190331152739480.png]  
静态行转列方法：

select name as 姓名,
    SUM(case [subject] when '语文' then score else 0 end) as '语文',
    SUM(case [subject] when '数学' then score else 0 end ) as '数学',
    SUM(case [subject] when '英语' then score else 0 end ) as '英语'
    from student group by name

变为：  
![在这里插入图片描述][20190331152923289.png]  
7.列转行  
原数据：  
![在这里插入图片描述][20190331152851822.png]

select * from (
        select name,课程='语文',分数=语文 from student
        union all
        select name,课程='数学',分数=数学 from student
        union all
        select name,课程='英语',分数=英语 from student
    ) t order by name, case 课程 when '语文' then 1 when '数学' then 2 when '英语' then 3 end

变为：  
![在这里插入图片描述][20190331152947252.png]

最后总结一下：  
如果你对此文有任何疑问，如果你也需要接口项目实战，如果你对软件测试、接口测试、自动化测试、面试经验交流感兴趣欢迎加入：软件测试技术群：**593462778**，群里的免费资料都是笔者十多年测试生涯的精华。还有同行大神一起交流技术哦。

作者：暗潮汹涌  
原创不易，欢迎转载，但未经作者同意请保留此段声明，并在文章页面明显位置给出原文链接。

[20190331152739480.png]: /images/20220225/ab1af0c88df2429db2d28a3964442154.png
[20190331152923289.png]: /images/20220225/c0f16f2bc2244a99a492f3d08dfe3c41.png
[20190331152851822.png]: /images/20220225/ea7ccf1d2a234b149d1ab3331319c4b2.png
[20190331152947252.png]: /images/20220225/3fea9247b45d4559af6a9a95b7e2fa1d.png