SQL Server面试题

（一）SQLServer数据库

1.SQL有哪三种注入方式？SQL安全

动态SQL拼装注入、SQL溢出漏洞、获取管理员权限、

2.数据库事务及隔离级别【中恒互联】

隔离级别：脏读、幻读、一致读、不可重复读、更新丢失

1.更新丢失（Lost update）：两个事务都同时更新一行数据但是第二个事务却中途失败退出导致对数据两个修改都失效了这是系统没有执行任何锁操作因此并发事务并没有被隔离开来

2.脏读（Dirty Reads）：一个事务开始读取了某行数据但是另外一个事务已经更新了此数据但没有能够及时提交。这是相当危险很可能所有操作都被回滚

3.不可重复读（Non-repeatable Reads）：一个事务对同一行数据重复读取两次但是却得到了不同结果。例如在两次读取中途有另外一个事务对该行数据进行了修改并提交

4.两次更新问题（Second lost updates problem）：无法重复读取特例，有两个并发事务同时读取同一行数据然后其中一个对它进行修改提交而另一个也进行了修改提交这就会造成第一次写操作失效

5.幻读（Phantom Reads）：也称为幻像（幻影）。事务在操作过程中进行两次查询，第二次查询结果包含了第一次查询中未出现的数据（这里并不要求两次查询SQL语句相同）这是因为在两次查询过程中有另外一个事务插入数据造成的

3.事务四大属性

原子性、一致性、隔离性、持久性。

4.说说存储过程定义，并描述一下优点和缺点？【奇谷网络】

语法：

CREATE PROCEDURE getUserInfo_PROC

AS

begin

//过程体

end

GO

优点：

Ø减轻网络流量

Ø可被作为一种安全机制来利用

Ø允许标准组件式编程

Ø能够实现较快的执行速度

缺点：

Ø可移值性差

Ø重构复杂

5.数据库中为什么要映射主外健?什么是事务处理?【海天华光】

主外键：保持数据完整性。

数据库事务是指作为单个逻辑工作单元执行的一系列操作。事务可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。事务内相关操作组合为一个要么全部成功要么全部失败，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID(原子性、一致性、隔离性和持久性)属性。

6.SqlServer的完整性约束?

–主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空

–唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。

–检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束

–默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”

–外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列

7.一张学生表student，有字段班级classid，学号id，成绩grade

（1）求各个班的平均成绩

Select classid,avg(grade) from student group by classid

（2）查询出比该班平均成绩高的学生的所有信息

select w.id,w. grade,b.avg_ grade from student w,(

select classid,avg(grade) avg_grade from student group by classid) b

where w. classid =b. classid and w.grade >b.avg_grade;

8.写一条SQL语句，查询姓张学生中平均成绩大于75的学生信息

select * from student where name in (select name from student

where name like '张%' group by name having avg(score) > 75)

9.查一下每门课程都大于80的学生姓名

学生表student分数表grade

select s.name from student s where s.id not in(select g.studentid from grade g where g.marks<=80)

或者

select s.name from student s where not exists(select 1 from grade g where g.studentid=s.id and g.marks<=80)

10.truncate与delete的区别？（delete from table和truncate table的区别！）【天晟科技面试题】

truncate是DDL語言.delete是DML語言DDL語言是自動提交的.命令完成就不可回滾.truncate的速度也比delete要快得多.

详细说明：

相同点:truncate和不带where子句的delete,以及drop都会删除表内的数据

不同点:

1. truncate和delete只删除数据不删除表的结构(定义)

drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index);依赖于该表的存储过程/函数将保留,但是变为invalid状态.

2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.

truncate,drop是ddl,操作立即生效,原数据不放到rollback segment中,不能回滚.操作不触发trigger.

3.delete语句不影响表所占用的extent,高水线(high watermark)保持原位置不动

显然drop语句将表所占用的空间全部释放

truncate语句缺省情况下见空间释放到minextents个extent,除非使用reuse storage;   truncate会将高水线复位(回到最开始).

4.速度,一般来说: drop> truncate > delete

5.安全性:小心使用drop和truncate,尤其没有备份的时候.否则哭都来不及

使用上,想删除部分数据行用delete,注意带上where子句.回滚段要足够大.

想删除表,当然用drop

想保留表而将所有数据删除.如果和事务无关,用truncate即可.如果和事务有关,或者想触发trigger,还是用delete.

11.一学生表！有班及id，学号！成绩！一求平均成绩！二求比平均成绩高的学生的所有信息

select id,avg(成绩)  from table group by  id

select * from table where成绩> (select成绩from（select id,avg(成绩)成绩from table group by  id）as a )

12.查询出M页的N行数据(分页的实现，求第M也的记录数)

Select top N  from  table  where id not in (select top  (M-1)*N  id  from table )

13.数据库三范式是什么?

第一范式（1NF）：

字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式）

数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。

第二范式（2NF）：

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

第三范式（3NF）：

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

所以第三范式具有如下特征：

1，每一列只有一个值

2，每一行都能区分。

3，每一个表都不包含其他表已经包含的非主关键字信息。

例如，帖子表中只能出现发帖人的id，而不能出现发帖人的id，还同时出现发帖人姓名，否则，只要出现同一发帖人id的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。

14.join与left join的区别：

inner join(等值连接)只返回两个表中联结字段相等的行

left join(左联接)返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接)返回包括右表中的所有记录和左表中联结字段相等的记录

15.用sql语句分页：

Mysql数据库：

SELECT TOP页大小*  FROM table1    WHERE id NOT IN   (

SELECT TOP页大小*(页数-1) id FROM table1 ORDER BY id

)  ORDER BY id

Oracle数据库：

在ORACLE大数据量下的分页解决方法。一般用截取ID方法，还有是三层嵌套方法。

截取ID的方法

select * from emp a, (select empno,rownum as num from emp)b  where a.empno=b.empno and b.num between 5 and 7;

三层嵌套

SELECT * FROM  ( SELECT A.*, rownum r  FROM  ( SELECT *  FROM emp ) A WHERE rownum <=7 ) B WHERE r >5;

16.SQL编程题1：

有表Table_1 ,数据如下

ID    NAM    JIEGUO

1        A         WIN

2        A         LOST

3        A         WIN

4        A         LOST

6        B         WIN

5        A         WIN

7        B         LOST

8        B         LOST

要求统计出，如下结果

NAM    Win_num  Lost_num

A              3            2

B             1            2

答：

select nam,

count(case jieguo when  'WIN'  THEN 1 ELSE Null END) as Win_num,

count(case jieguo when  'LOST' THEN 1 ELSE Null END) as Lost_num

from TABLE_1

group by nam

或者

select nam,

sum(case jieguo when 'WIN' THEN 1 ELSE 0 END) as Win_num,

sum(case jieguo when 'LOST' THEN 1 ELSE 0 END) as Lost_num

from TABLE_1

group by nam

17.SQL编程题2：

给下面这样的一个表记录：

------------------------------------

CUSTOMER_NAME      TRADE_NAME    NUM

A甲2

B乙4

C丙1

A丁2

B丙5

给出所有购入商品为两种或两种以上的购物人记录。

答：

方法一（推荐，使用了count(distinct列名)）

select * from T1

where customer_name in

(select customer_name

from T1 as b

group by customer_name

having count(distinct trade_name)>=2)

说明：

having count，发生在分组之后，对每个分组包含的行，进行统计count(distinct trade_name)，根据这个统计值（每个分组一个），确定是否输出此分组。

方法二（容易看迷糊，用了2次group by）

select * from T1 WHERE CUSTOMER_NAME IN

(SELECT * FROM

(select CN1 as CN from (select customer_name AS CN1,trade_name AS TN1 from T1 as B group by customer_name,trade_name) as A group by CN1

having count(TN1)>=2) as B )

说明：

1.group by customer_name,trade_name：用于排除（customer_name + trade_name）重复的行，只留一行）。

2.group by CN1 having count(TN1)>=2：用于排除只买了一种商品的顾客。

18.SQL编程题3：

Student表有三列,分别是姓名、课程、成绩(见下表)，其中stu_name列有约1000行，course列除了已经列出来的马哲、数学、英语、语文外，还有一些学科种类没有列完（即学科种类数不定），未参加考试的学生Mark列值为0。

stu_name      course       mark

城南马哲70

城南数学65

城南英语58

城南语文79

李四马哲61

李四数学80

李四英语77

李四语文80

王朝马哲52

王朝数学55

王朝英语59

王朝语文90

张三马哲66

张三数学88

张三英语61

张三语文70

要求查询：

1、每一门课程都及格的学生的姓名

2、总分排名在前三名的学生的姓名

答：

1、

SELECT stu_name

FROM T2

GROUP BY stu_name

HAVING (COUNT(CASE WHEN mark >= 60 THEN 1 ELSE NULL END) = COUNT(Mark))

说明：此题的关键点是“学科种类数不定”和“每一门都及格”，此点用COUNT(CASE WHEN mark >= 60 THEN 1 ELSE NULL END) = COUNT(Mark)来实现。

还有更加简单、易理解的解答：

从原始表中排出那些不及格的人，就可以啦。

SELECT Distinct name  FROM StudentScore

WHERE name   NOT   IN

(Select Distinct name from StudentScore where score<60);

2、

select top 3 stu_name,sum(mark) as总分

from T2

group by stu_name

order by sum(mark) desc

还有一种解答，SQL2005以上版本才能用的。

SELECT

ROW_NUMBER()  OVER(ORDER BY SUM(SCORE) DESC)  AS  ID,

NAME,

SUM(SCORE)  AS  TOTAL

FROM StudentScore

GROUP BY name;

19.SQL编程题4：

表className中有如下分类:

classID   className

1衣服

2裤子

5帽子

10鞋子

表productInfo有如下记录:

productID    productName     parentID            clickNum

1男士衣服1                         90     --衣服类别中点击率最高

2女士衣服1                         80

3男士裤子2                         70

4女士裤子2                         90     --裤子类别中点击率最高

5男士帽子5                         15

6女士帽子5                         30     --帽子类别中点击率最高

7男士鞋子10                       65     --鞋子类别中点击率最高

8女士鞋子10                       52

9女士鞋子1                    10                       54

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

productID             productName            clickNum

1男士衣服90

4女士裤子90

7男士鞋子65

6女士帽子30

答：

select productid,productname,clicknum

from ProductInfo

inner join

(select parentid as P_id,max(clicknum) as Maxclick

from  ProductInfo  group by parentid) as A

on T3.parentid=A.P_id and T3.clicknum=A.Maxclick

order by clicknum desc,productid asc

20.SQL编程题5：

有三个表：

一张老师表TecTable，字段是T_ID,T_NAME；

一张学生表StuTable，字段是S_ID,S_NAME；

一张班级表ClassTable，字段是T_ID,S_ID,C_NAME，其中C_NAME的取值只有‘大班’和‘小班’。

请查询出符合条件的老师的名字，条件是老师在大班中带的学生数大于此老师在小班中带的学生数。

答：

未验证

select

TecTable.Techer_ID,

Techer_NAME,

count(case Class_name when '大班' then 1 else null end) as Big_stu_num,

count(case Class_name when '小班' then 1 else null end) as Little_stu_num

from TecTable,StuTable,ClassTable

where

TecTable.Techer_ID=ClassTable.Techer_ID and StuTable.Stu_id=ClassTable.Stu_id

group by Techer_ID,Techer_NAME

having Big_stu_num > Little_stu_num

网上还有一种解法：

select * from T,

(select count(*) as x,T_ID from C where c_name='小班' group by T_ID) a,

(select count(*) as x,T_ID from C where c_name='大班' group by T_ID) b where b.x >a.x and a.T_ID=b.T_ID and T.T_ID=b.T_ID

group by后面还要多加个班级名。

21.SQL程序题6（行列转换）

已知一个表T4,结构为：

stu_name     course     result

张三语文20

张三数学30

张三英语50

李四语文70

李四数学60

李四英语90

问：怎样通过SQL的Select语句输出以下结构的数据

stu_name语文数学英语

张三20        30        50

李四70        60        90

答：

SELECT

A1.STU_NAME,A1.RESULT AS '语文',A2.RESULT AS '数学',A3.RESULT AS '英语'

FROM

T4 A1 INNER JOIN T4 A2 ON A1.STU_NAME=A2.STU_NAME

INNER JOIN T4 A3 ON A2.STU_NAME=A3.STU_NAME

WHERE

A1.COURSE='语文' AND A2.COURSE='数学' AND A3.COURSE='英语'

22.SQL编程题7：

有一个学生表，结构如下：

id  name course score

1

学生1

语文

88.50

2

学生1

数学

95.00

3

学生1

英语

85.50

4

学生2

语文

100.00

5

学生2

数学

76.00

6

学生2

英语

70.50

7

学生3

语文

68.50

8

学生3

数学

77.50

9

学生3

英语

91.50

10

学生4

语文

72.50

11

学生4

数学

86.50

12

学生4

英语

77.50

要求进行行转列，得出如下结果：

name语文数学英语

学生2   100.00   76.00    70.50

学生1   88.50     95.00    85.50

学生4   72.50     86.50    77.50

学生3   68.50     77.50    91.50

答：

select

name,

sum(case cource when '语文' then score else 0 end) as '语文',

sum(case cource when '数学' then score else 0 end) as '数学',

sum(case cource when '英语' then score else 0 end) as '英语'

from Score

group by name

order by '语文' desc