SQL数据库基础

1、数据库三范式是什么？

第一范式：字段不能有冗余信息，所有字段都是必不可少的。

第二范式：满足第一范式并且表必须有主键。

第三范式：满足第二范式并且表引用其他的表必须通过主键引用。

 

2、触发器的作用？

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

 

3、索引的作用？和它的优点缺点是什么？

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

 

4、说一下SQLServer中索引的两种类型？

聚簇(或者叫做聚集，cluster)索引和非聚簇索引。

字典的拼音目录就是聚簇(cluster)索引，笔画目录就是非聚簇索引。这样查询“G到M的汉字”就非常快，而查询“6划到8划的字”则慢。

聚簇索引是一种特殊索引，它使数据按照索引的排序顺序存放表中。聚簇索引类似于字典，即所有词条在字典中都以字母顺序排列。聚簇索引实际上重组了表中的数据，所以你只能在表中建立一个聚簇索引。

当数据按值的范围查询时，聚簇索引就显得特别有用。因为所有SQLServer都必需先找到所查询范围的第一行，然后依次下去，直到该范围的最后一个值找到为止，并且保证了所有其他值也落在这个范围内。举一个例子，一个应用程序要查找首字母位于G和P之间的姓名列表，SQLServer首先找到以字母G开头的名字，取出所有记录，直到找到以字母P开头的名字为止，这种方法使得查询过程非常高效。

进行大量数据改动的表不适宜用聚簇索引，因为SQLServer将不得不在表中维护行的次序。如果要索引的值极少，例如一个列包含的全都是1和0，创建聚簇索引就不是个好主意。如果表经常由一个指定的列来排序，该列将是簇索引的最佳候选列。这是因为表中的数据已经为你排好序了。如果访问一个表并使用BETWEEN、<、>、>=或<=操作符来返回一个范围的值时，应该考虑使用聚簇索引。

 

5、什么是事务？什么是锁？

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

 

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

 

6、什么叫视图？游标是什么？

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

 

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

 

7、什么是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("'");

 

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

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

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

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

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

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

 

8、如何防范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，非常适合于对输入数据进行消毒处理。

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

 

9、事务是什么？

事务是作为一个逻辑单元执行的一系列操作，一个逻辑工作单元必须有四个属性，称为 ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务：

1)原子性

事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

2)一致性

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。

3)隔离性

由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。

4)持久性

事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

 

10、写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键,注意：ID可能不是连续的。

解1: select top 10 * from A where id not in (select top 30 id from A)

演变步骤：

1）select top 30 id from T_FilterWords--取前条

2）select * from T_FilterWords

where id not in (select top 30 id from T_FilterWords)--取id不等于前三十条的

--也就是把前条排除在外

3）select top 10 * from T_FilterWords

where id not in (select top 30 id from T_FilterWords)

--取把前条排除在外的前条，也就是-40条

解2: select top 10 * from A where id > (select max(id) from (select top 30 id from A )as A)

解答3：用ROW_NUMBER实现

 

11、横表、纵表转换

纵表结构 TableA 

Name	Course	Grade
张三	语文	75
张三	数学	80
张三	英语	90
李四	语文	95
李四	数学	55

 横表结构 TableB

Name	语文	数学	英语
张三	75	80	90
李四	95	55	0

先理解：

select Name，

 (case Course when ‘语文‘ then Grade else 0 end) as 语文，

 (case Course when ‘数学‘ then Grade else 0 end) as 数学，

 (case Course when ‘英语‘ then Grade else 0 end) as 英语

from TableA

然后理解标准答案：

select Name，

sum(case Course when ‘语文‘ then Grade else 0 end) as 语文，

sum(case Course when ‘数学‘ then Grade else 0 end) as 数学，

sum(case Course when ‘英语‘ then Grade else 0 end) as 英语

from TableA

group by Name

 

横表转纵表的"SQL"示例

横表结构: TEST_H2Z

      ID      姓名    语文        数学       英语      

      1       张三     80         90         70            

      2       李四     90         85         95          

      3       王五     88         75         90          

 

转换后的表结构:  

      ID     姓名     科目     成绩  

      1       张三     语文     80  

      2       张三     数学     90  

      3       张三     英语     70  

      4       李四     语文     90  

      5       李四     数学     80    

      6       李四     英语     99  

      7       王五     语文     85  

      8       王五     数学     96  

      9       王五     英语     88  

 

横表转纵表SQL示例:
SELECT   姓名,'语文'   AS     科目,语文   AS   成绩   FROM   TEST_H2Z   UNION   ALL 
SELECT   姓名,'数学'   AS     科目,数学   AS   成绩   FROM   TEST_H2Z   UNION   ALL 
SELECT   姓名,'英语'   AS     科目,英语   AS   成绩   FROM   TEST_H2Z
ORDER BY 姓名,科目 DESC;

 

12、删除姓名、年龄重复的记录

Id  name  age  salary

1   yzk    80  1000

2   yzk    80  2000

3   tom    20  20000

4   tom    20  20000

5   im     20  20000

 

//取得不重复的数据

select * from Persons

where Id in

(

SELECT     MAX(Id) AS Expr1

FROM         Persons

GROUP BY Name, Age

)

 

根据姓名、年龄分组，取出每组的Id最大值，然后将Id最大值之外的排除。

 

删除重复的数据：

delete from Persons

where Id not in

(

SELECT     MAX(Id) AS Expr1

FROM         Persons

GROUP BY Name, Age

)

 

13、

表一：student_info

学号	姓名	性别	出生年月	家庭住址	备注
0001	张三	男	1981-8-9	北京	NULL

 

 

表二：curriculum

课程编号	课程名称	学分
0001	计算机基础	2
0002	C语言	2

 

表三：grade

学号	课程编号	分数
0001	0001	80
0001	0002	90

题目：

条件查询：

1. 在GRADE表中查找80-90份的学生学号和分数

select 学号,分数 from grade where 分数 between 80 and 90

1. 在GRADE 表中查找课程编号为003学生的平均分

   select avg(分数) from grade where 课程编号='003'

1. 在GRADE 表中查询学习各门课程的人数

Select课程编号,count(学号) as 人数from grade group by 课程编号

1. 查询所有姓张的学生的学号和姓名

   select  姓名,学号 from student_info where 姓名 like '张%'

 

嵌套查询：

1. 查询和学号’0001’的这位同学性别相同的所有同学的姓名和出生年月

 select 姓名,出生年月 from student_info where 性别 in(select 性别 from student_info where sno='0001')

1. 查询所有选修课程编号为0002 和0003的学生的学号、姓名和性别

select 学号,姓名,性别 from student_info where 学号 in(select 学号 from grade where 课程编号='0002' and 学号 in(select 学号 from grade where 课程编号='0001'))

1. 查询出学号为0001的学生的分数比0002号学生最低分高的课程编号的课程编号和分数

select 课程编号, 分数 from grade where 学号='0001' and 分数>(select min(分数) from grade where 学号='0002')

 

多表查询：

1. 查询分数在80-90分的学生的学号、姓名、分数

select student_info.学号,student_info.姓名,grade.分数 from student_info,grade where grade.分数 between 80 and 90

1. 查询学习了’C语言’课程的学生学号、姓名和分数

select student_info.学号,student_info.姓名,grade.成绩from student_info,grade,curriculum where student_info.学号=grade.学号and grade.课程号=curriculum.课程号and curriculum.课程名='C语言'

1. 查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选课的学生总成绩为空。

select grade.学号,student_info.姓名,sum(grade.成绩) as 总成绩from student_info,grade where grade.学号=student_info.学号group by grade.学号,student_info.姓名

 

14、活期存款中，“储户”通过“存取款单”和“储蓄所”发生联系。假定储户包括：账号，姓名，电话，地址，存款额；“储蓄所”包括：储蓄所编号，名称，电话，地址（假定一个储户可以在不同得储蓄所存取款）

1、写出设计以上表格的语句（4分）

2、创建一个触发器TR1完成下面内容：

当向“存取款单”表中插入数据时，如果存取标志＝１则应该更改储户表让存款额加上存取金额，如果存取标志＝0则应该更改储户表让存款额减去存取金额,如果余额不足显示余额不足错误。

 

CREATE TABLE CREATE TRIGGER tr1 on qukuan after insert

AS 

BEGIN

 

declare @sid nvarchar(50)

declare @type int

declare @qian int

declare @yuer int

select @sid=sid,@type=[type],@m=m from inserted

select @yuer=yuer from cunkuan

if(@type=1)

begin 

update cunkuan set yuer=yuer+@qian

end

else

begin

 if(@yuer<@qian)

   begin

     print '余额不足'

   end

 else

    begin

     update cunkuan set yuer=yuer-@qian

    end

end

 

 

END

GO

 

15、本题用到下面三个关系表：

CARD     借书卡:   (CNO 卡号，NAME  姓名，CLASS 班级)

BOOKS    图书:     (BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数 )

BORROW   借书记录: (CNO 借书卡号，BNO 书号，RDATE 还书日期

备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

要求实现如下处理：

写出自定义函数，要求输入借书卡号能得到该卡号所借书金额的总和：

 

CREATE FUNCTION getSUM

(

@CNO int

)

RETURNS int

AS

BEGIN

declare @sum int

select @sum=sum(price) from BOOKS where bno in (select bno from BORROW where cno=@CNO)

 

return @sum

END

GO

 

找出借书超过5本的读者,输出借书卡号及所借图书册数。

select CNO,count(BNO) as 借书数量from BORROW group by CNO having count(BNO)>3

 

查询借阅了"水浒"一书的读者，输出姓名及班级。

select name,class from card where cno in( select cno from borrow where bno in(

select bno from BOOKS where bname='水浒'))

 

查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

select CNO,BNO,RDATE from borrow where getdate()>