数据库重点知识总结(二)

第六章数据操作语言

1、例6-12：between...and和not between...and

Eg1：查询1991年6月~8月出生的学生的姓名和出生日期

Select sname,birthdate from student 
where birthdate between ‘1991/6/1’ and ‘1991/8/31’

2、例6-15 ：字符串匹配like/not like

语法格式：列名  [not] like <匹配符>

匹配符中包含的4中通配符：

1）_(下划线)：匹配任意一个字符。

2）%（百分号）：匹配零个或多个字符。

3）[]:匹配[]中的任意一个字符。如[acdg]表示匹配a、c、d和g中的任何一个。若要比较的字符是连续的，则可以用连字符“-”表示。如，要匹配b、c、d、e中的任何一个字符，则可以表示为[b-e]。

4）[^ ]:表示不匹配[]中的任何一个。

Eg2：查询姓“张”的学生的详细信息

Select * from student
Where sname like ‘张%’

3、例6-20：空值（null）在数据库中有特殊的含义，它表示当前不确定或未知的值。Null不能与任何确定的值进行比较。

判断列取值为空的子句为：列名is null。   Grade=null

判断列取值不为空的字句为：列名 is not null=not（列名is null）

Eg3：查询还没有考试的学生的学号和对应的课程号

Select sno ，cno from sc
Where grade is null

4、多表连接查询：内连接，左外连接，右外连接，全外连接，交叉连接（三表连接）

内连接：from 表1 [inner] join 表2 on <查询条件>

例6-39：查询“信息管理系”选修了“计算机文化学”课程的学生信息，要求列出学生的姓名和考试成绩。

Select sname,grade
from student s (inner) join sc on s.sno=sc.sno 
join course c on c.cno=sc.cno 
Where dept=’信息管理系’ and cname=’计算机文化学’

5、例6-60简单插入语句：将一条新生信息插入到student表中。

insert into student
values(‘08211’,’陈冬’，’男’,’1991/6/23’,’英语系’)

6、例6-61插入多行数据：（由于未sc表提供了所有的列的值并按表中各列的顺序列出这些值，因此不必在列中指定列名）

Insert into sc values (‘08211’,’c001’,90),
               values(‘08211’,’c002’,80),
               Values(‘08211’,’c003’,null)

7、例6-62：按与表列顺序不同的顺序插入数据

Eg：将一条新生信息插入到student表中，该新生学号为0811，姓名为李丽，性别为女，出生日期暂缺，计算机系学生

insert into student(sno,sname,sex,dept)
            values(‘0811’,’李丽’,’女’,’计算机系’)

8、更新数据：

例6-66：无条件的更新

Eg：将所有学生的成绩加10

update sc set grade=grade+10

例6-67：有条件更新

Eg：基于本表条件的更新。将“c001”号课程学分改为5

update course set credit=5 where cno=’c001’

Eg:基于其他表条件的更新。将计算机系全体学生的成绩加5分。

update sc set grade=grade+5
from sc join student on sc.sno=student.sno
where dept=’计算机系’

Eg:同时更改多个列的值。将java课程改为第二学期开设，3学分。

update course set semester=2,credit=3
where cname=’java’

9、删除数据

例6-71无条件删除。删除employee表总的全部数据。

delete employee   //employee成空表

例6-72：有条件删除。

Eg：基于本表的删除。删除所有考试成绩不及格的学生的选课记录。

delete（from）sc where grade<60  //但不会删除grade is null的值。

第七章高级查询

1、@例7-15：查询第二学期开设的学分最高的课程的课程名和学分。

Select cname,credit from course
Where semester=2 and credit=(
Select max(credit) from course
Where semester=2
)

2、Row_number()函数

Row_number()函数返回结果集中每个分区内行的序列号，每个分区的第一行从1开始。

例7-52：查询“计算机文化学”课程的考试情况，列出学号，姓名，所在系，考试成绩及成绩排名。

Select s.sno,sname,dept,grade,
Row_number() over(order by grade desc) as ‘number’
from student s join sc on s.sno=sc.sno
join course c on c.cno=sc.cno
Where c.name=’计算机文化学’

例7-53：查询“计算机文化学”的考试情况，列出学号，姓名，所在系，考试成绩，及每个学生在每个系中的成绩排名。(分区)

Select s.sno,sname,dept,grade,row_number() over (partition by dept order by grade desc)as ‘dept_number’
From student s join sc on s.sno=sc.sno join course c on c.cno=sc.cno
Where c.cname=’计算机文化学’

第八章索引

1、在数据库中建立索引是为了加快数据的查询速度。（目的）

2、利用索引提高查询效率是以空间和增加了数据更改的时间为代价的。

3、聚集索引的B-树是自下而上建立的，最下层的叶级节点存放的是数据。（包含数据和索引项）。

聚集索引：对数据按索引关键字进行物理排序。B-树：平衡树。

4、建立索引的目的是为了提高查询效率，但存储索引需要空间的开销，维护索引需要时间的开销。索引不是越多越好。

5、索引分为聚集索引和非聚集索引两大类，SQL Sever 对索引均采用B-树存储结构。建立聚集索引时，数据库系统首先按聚集索引键的值对数据进行物理排序，然后再再次基础上建立缩索引的B-树。

6、在一个表上只能建立一个聚集索引，但可以建立多个非聚集索引。@聚集索引和非聚集索引都可以是唯一所以。唯一索引的作用是保证索引键所包含的列的取值不能重复。

第九章视图

1、什么是视图：视图是由从数据库的基本表中选出来的数据组成的逻辑窗口。

2、视图是一个虚表，数据库中只存储视图的定义，而不存储视图所包含的数据。

3、视图可以从一个基本表中提取数据，也可以从多个基本表中提取数据，甚至还可以从其他视图中提取数据，构成新的视图。但不管怎样，对视图数据的操作最终都会转换为对基本表的操作。

4、@视图是将物理分布在不同表中的数据组织在一起，使用户感觉是在一张表上进行操作，从而不必关心哪些数据放在哪个表中。

第十章存储过程和触发器

1、使用存储过程的好处：（存储在服务器端的过程）

允许模块化程序设计；‚改善性能；ƒ减少网络流量；④可以作为安全机制使用（可以保证事务安全）。

2、带输入参数的存储过程：(@)

例10-2：查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名、考试成绩。

create procedure p_StudentGrade2
@dept char(20)
as 
select sname,dept,cname,grade
from student s inner join sc on s.sno=sc.sno  
inner join course c on c.sno=sc.sno
where dept=@dept

execute p_StudentGrade2 ‘信息管理系’

3、带输出参数的存储过程(@)

例10-4：统计学生人数，并将统计结果用输出参数返回。

Creat proc p_Count
@total int output
As 
Select @total=count(*) from student

执行此存储过程示例：

Declare @res int
Exec p_Count @res output
Print @res

4、什么是触发器：触发器是一段由数据更改操作引发的自动执行的代码。

5、引发触发器的更改操作包括update、insert、delete。

6、触发器的功能：保证业务规则和数据完善性。

7、存储过程是一段可执行的代码块，该代码块经过编译后生成的可执行代码被保存在内存的专用区域里，这种模式可以极大地提高后续执行存储过程的效率。

8、DML触发器是由对表进行插入、删除、更改语句引发执行的代码，主要用于实现数据完整性约束和业务规则。

9、触发器有前触发器和后触发器两种类型。

第十一章函数和游标

@1、内联表值函数的返回值是一个表，该表的内容是一个查询语句的结果。

2、游标：游标结果集和游标当前行指针。

游标的一般使用过程：声明游标、打开游标、提取数据、处理完成？、关闭游标、释放游标。

声明游标：declare 游标名 cursor

打开游标：open cursor_name

提取数据：fetch..from ...

关闭游标:close cursor_name

释放游标：deallocate cursor_name

3、游标作用：利用游标可以实现对查询集合内部的操作。

第十二章安全管理

1、SQL Server对数据库的安全管理使用身份验证、数据库用户权限确认等措施来保护数据库的信息资源，以防这些数据被破坏。

2、SQL Server为不同的类型的登录账户提供了不同的身份认证模式，主要有Windows身份验证模式和混合身份验证模式两种。

3、@在SQL Server中，有两种建立登录账户的方法，一种是通过SQL Server 2008的SSMS工具图形化的实现，另一种是通过T—SQL语句实现。

4、@创建Windows身份验证模式的登录账户实际上就是将Windows用户映射到SQL Server中，使之能够连接到SQL Server实例上。

5、@只要是SQL Server用户，必须有密码。

6、例12-1：创建SQL Server身份验证的登录账户。登录名为：SQL_User2,密码为：a1b2c3XY

create login SQL_User2 with password=’a1b2c3XY’;

删除登录账户：drop login login_name；

7、@数据库用户是数据库级别上的主体。用户在具有了登录账户后，只能够连接收到数据库服务器上，并不具有访问任何用户数据库的权限，只有成为数据库的合法用户后，才能访问。

8、数据库用户一般都来自于服务器上已有的登录账户，让登录账户成为数据库用户的操作成为“映射”。一个登录账户可以映射多个数据库的用户。

9、例12-16：首先创建名为SQL_JWC且具有密码的SQL Server身份验证的登录名，然后在students数据库中创建与此登录名对应的数据库用户JWC.

create login SQL_JWC with password=’jkj13n0jsk84’
go
use student
go
create user JWC for login SQL_JWC

删除数据库用户：drop user user_name

10、@不能从数据库中删除拥有对象的用户

@为数据库用户授予数据库数据及对象的操作权限用来实现安全性。

@backup database 和backup log：具有备份数据库和备份日志的权限。

11、在T-SQL中用于管理权限的语句：

@crant：用于授予权限；

@revoke：用于收回或撤销权限；

@deny：用于拒绝权限；

例12-8：为用户user1授予student表的查询权限。

crant select on student to user1

例12-10：收回用户user1对student表的查询权限。

revoke select on student from user1

拒绝用户user1具有sc标的更改权限：deny update on sc to user1

12、角色是一组权限的总称。

13、角色分为系统预定义的固定角色和用户根据自己的需要定义的用户角色。系统角色又根据其使用范围不同分为固定的服务器角色和固定的数据库角色。服务器角色是为整个服务器设置的，而数据库角色是为具体的数据库设置的。

14、例12-20：将SQL_User2添加到student数据库的db_datawriter角色中。

exec sp_addrolemember ‘db_datawriter’,’SQL_User2’

第十三章备份和恢复数据库

1、备份数据库就是将数据库中的数据以及保证数据库系统正常运行的有关信息保存起来，已备系统出现问题时恢复数据使用。

2、备份数据库的主要目的是为了防止数据丢失。

3、完整数据库备份:备份数据库的全部信息，是恢复数据库的基线。

4、差异数据库备份是备份最近的完整备份之后数据库的全部变化的内容。

5、事务日志备份只备份从上次差异备份之后到当前备份时间发生的变化的日志内容。

6、使用事务日志备份，可以将数据恢复到故障点或特定的某个时间点。