本总结基于“耿彦晓”童鞋的总结，配上老师复习的PPT综合而成。

感谢在百忙之中无私奉献的“耿彦晓”童鞋，及为本文奉献的骚年们。

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

第五章 数据查询

连接查询概述

交叉连接	无条件连接
内连接	有条件连接
外连接	保留舍弃记录
自连接	与自身连接

课后习题：P120页2 和3

⑴  假如需要查找出生日期在 1987 年 1 月 1 日以前，并且入学成绩大于等于 500 或入 学成绩小于等于 560的学生，哪条查询语句最合适？  D

A. SELECT name FROM stud_info

WHERE birthday>'01/01/1987' AND (mark<500 OR mark >560)

B. SELECT name FROM stud_info

WHERE birthday<'01/01/1987' AND mark <500 AND mark >560

C. SELECT name FROM stud_info

WHERE birthday<'01/01/1987' AND mark <500 OR mark >560

D. SELECT name FROM stud_info

WHERE birthday<'01/01/1987' AND mark BETWEEN 500 AND 560

 

⑵  针对下列查询语句，有什么错误？  C   

SELECT stud_info.name, stud_id FROM stud_info s WHERE stud_id = '0401040123'

A.  当表没有被连接时，在查询中不能使用表的别名。

B.  当定义了一张表的别名时，必须用别名限制所有列名。

C.  假如一张表定义了别名，不能使用表名限制一列名。

D.  在 SELECT 子句中，不能其中一列有限制，而其他列没有限制。应该是所有列要么都限制，要么都不限制。

 

⑶  当下列查询成功执行时，将返回的最大的行数是多少？   B   

SELECT teacher_id, name FROM teacher_info WHERE age = (SELECT MAX(age) FROM teacher_info)

A. 1

B. 不确定

C. 0 或 1 

D. 256

 

⑷  下列函数中哪个不是聚合函数？  C  

A. SUM

B. AVG

C. GREATEST

D. COUNT

 

⑸  下列代码中哪一行有错？[    D    ]

①  SELECT g.name, e.name

②  FROM stud_info e, stud_grade g

③  WHERE e.stud_id=g.stud_id

④  ORDER BY 89

A.  第 1 行

B.  第 2 行

C.  第 3 行

D.  第 4 行

 

⑹  在一个查询中，哪一个子句将限制返回的行？[    B    ]

A. ORDER BY

B. WHERE

C. SELECT

D. FROM

 

⑺  哪一个运算符可以替代 WHERE 子句中的 OR 运算符？[    A    ]

A. IN 

B. >= 

C. LIKE

D. <=

 

⑻  下列 SQL 语句中哪一行有错？[    C    ]

①  SELECT gender, COUNT(gender)

②  FROM stud_info

③  GROUP BY gender

④  HAVING COUNT(gender)=

⑤  (SELECT MAX(COUNT(gender))

⑥  FROM stud_info

⑦  GROUP BY gender)

A.  第 3 行 

B.  没有错

C.  第 5 行

D.  第 7 行

 

⑼  下列 SQL 语句的哪一行有错误？[    C    ]

①  SELECT stud_id, name

②  FROM stud_info

③  ORDER BY mark

④  WHERE mark> (SELECT mark FROM stud_info WHERE name LIKE N'%军')

A.  第 4 行

B.  第 2 行

C.  第 3 行

D.  没有错误

3.  按照下列题目要求，写出对应的 SQL 语句

 

⑴  列出 stud_info 学生信息表中所有记录的 name、gender 和 address 列。

SELECT name 姓名, gender AS 性别,家庭地址=address

FROM stud_info

 

⑵  将下列学生成绩插入到 stud_grade 学生成绩表中。

--在‘成绩表’中插入

INSERT INTO stud_grade

VALUES('0401010717','代燕','0401010104',60)

 

⑶  显示 stud_grade 学生成绩表中成绩为 85，86 或 88 的记录。

SELECT *

FROM stud_grade

WHERE grade IN (85,86,88)

 

⑷  显示 stud_grade 学生成绩表中成绩在 60 到 80 之间的所有记录。

SELECT *

FROM stud_grade

WHERE grade BETWEEN 60 AND 90

 

⑸  显示 stud_grade 学生成绩表中的最高分的学生学号和课程号。

SELECT stud_id AS 学号,course_id AS 课程编号

FROM stud_grade

WHERE grade=(

    SELECT grade=MAX(grade)

    FROM stud_grade

    )

 

⑹  以入学成绩 mark 降序显示 stud_info 学生信息表中所有记录。

SELECT *

FROM stud_info

ORDER BY mark DESC

 

⑺  首先显示"计算机工程系"、"计算机网络技术专业"、02 班全体学生的基本信息，然后再统计"计算机工程系"、"计算机网络技术专业"、02 班的学生人数。

--显示班全体学生的基本信息

SELECT *

FROM stud_info

WHERE

    SUBSTRING(stud_id,3,2)=

    (

       SELECT deptcode

       FROM dept_code

       WHERE deptname='计算机工程系'

    )

    AND

    SUBSTRING(stud_id,1,6)=

    (

       SELECT speccode

       FROM specialty_code

       WHERE specname='计算机网络技术'

    )

    AND

    SUBSTRING(stud_id,7,2)='02'

 

--统计班总人数

SELECT COUNT(stud_id) AS '02班总人数'

FROM stud_info

WHERE

    SUBSTRING(stud_id,3,2)=

    (

       SELECT deptcode

       FROM dept_code

       WHERE deptname='计算机工程系'

    )

    AND

    SUBSTRING(stud_id,1,6)=

    (

       SELECT speccode

       FROM specialty_code

       WHERE specname='计算机网络技术'

    )

    AND

    SUBSTRING(stud_id,7,2)='02' --02班

GROUP BY SUBSTRING(stud_id,1,8)

⑻  显示课程号"0401010106"的平均分。

SELECT course_id AS 课程号,avg(grade) AS 平均分

FROM stud_grade

WHERE course_id='0401010106'

GROUP BY course_id

 

⑼  在学生成绩表中，显示最低分大于 60，最高分小于 80 的 stud_id 列。

SELECT stud_id

FROM stud_grade

WHERE grade <</span>80 AND grade >60

 

⑽  显示成绩高于学号为"0401030101"、课程号为"0401010105"的所有成绩记录。

SELECT stud_id AS 学号, name AS 姓名, course_id AS 课程号, grade AS 成绩

FROM stud_grade

WHERE grade>(

    SELECT grade

    FROM stud_grade

    WHERE stud_id='0401030101' AND course_id='0401010105'

    )

 

⑾  显示出与学号为"0401040112"的学生同年出生的所有学生的学号、姓名和出生日期。

SELECT stud_id AS 学号, name AS 姓名, birthday AS 出生日期

FROM stud_info

WHERE YEAR(birthday)=(

    SELECT YEAR(birthday)

    FROM stud_info

    WHERE stud_id='0401040112'

    )

 

⑿  显示课程号为"0401010105"的同学人数和老师姓名。

SELECT  COUNT(stud_id) AS 同学人数,ti.name AS 老师姓名

FROM stud_grade AS sg,teach_schedule AS ts,teacher_info AS ti

WHERE sg.course_id=ti.course_id AND

      ts.course_id=ti.course_id

GROUP BY ti.name,sg.course_id

HAVING sg.course_id='0401010105'

⒀  列出所有任课老师的姓名和所在院系。

SELECT ti.name AS 任课老师姓名,dc.deptname AS 院系

FROM teacher_info AS ti,dept_code AS dc

WHERE SUBSTRING(ti.teacher_id,1,2)=dc.deptcode

 

⒂  显示"计算机工程系"老师所教课程的成绩表。

SELECT name AS 学生姓名,course_id AS 课程编号,grade AS 成绩

FROM stud_grade

WHERE course_id IN(

    SELECT course_id

    FROM teacher_info

    WHERE SUBSTRING(teacher_id,1,2)=(

       SELECT deptcode

       FROM dept_code

       WHERE deptname='计算机工程系'

       )

    )

ORDER BY course_id ASC

 

⒃  显示"计算机工程系"和"机电工程系"职称为"助教"的教师的姓名和性别。

SELECT name AS 教师姓名,gender AS 性别

FROM teacher_info

WHERE tech_title='助教' AND

    SUBSTRING(teacher_id,1,2) IN(

       SELECT deptcode

       FROM dept_code

       WHERE deptname IN('计算机工程系','机电工程系')

       )

⒅  将 stud_grade 学生成绩表中的分数,按班级和学号顺序显示。

SELECT SUBSTRING(stud_id,7,2) AS 班级,stud_id AS 学号,grade AS 成绩

FROM stud_grade

ORDER BY  SUBSTRING(stud_id,7,2),SUBSTRING(stud_id,9,2)

 

⒆  将 stud_grade 学生成绩表中课程号为"0401010106"的成绩加5。

UPDATE stud_grade

SET grade=grade+5

WHERE course_id='0401010106'

 

⒇  删除 stud_info 学生信息表中姓名为"张源"的学生记录。

--删除信息表中的数据

DELETE FROM stud_info

WHERE name='张源'

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

第六章 视图及其应用

视图的概念（简答）

视图是基于某个查询结果的一个虚拟表，只是用来查看数据的窗口而已。

视图与真正的表很类似，也是由一组命名的列和数据行所组成，其内容由查询所定义。但是视图并不是以一组数据的形式存储在数据库中，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基表中。

当基表中的数据发生变化时，从视图中查询出来的数据也随之改变。

 

视图的优点和缺点（简答）

优点：数据保密、简化数据查询操作 、保证数据的逻辑独立性。

缺点：当更新视图中的数据时，实际上是对基表的数据进行更新。当从视图中插入或者删除时，情况也是这样。但是，某些视图是不能更新数据的

 

书上例题 P125页

【示例6.1】针对表stud_info创建一个简单视图。

CREATE VIEW stud_view2

AS

SELECT stud_id,name,address,telcode,zipcode

FROM stud_info

 

课后习题 P135页

(1) 用哪个选项建立的视图，保证通过视图加到表中的行可以通过视图访问？[    D    ]

A. WHERE

C. WITH CHECK OPTION

B. WITH ENCRYPTION

D. CREATE VIEW

 

(2)  用下列代码建立一个视图，对该视图允许做什么操作？[    A,B,C,D    ]

CREATE VIEW stud_view

AS

SELECT * FROM stud_info

WHERE substring(stud_id,3,2) = '01'

A. SELECT

C. SELECT, DELETE

B. SELECT, UPDATE

D. SELECT, INSERT

 

(3)  建立视图的哪一个选项，将加密 CREATE VIEW 语句的文本？[    D    ]

A. WITH UPDATE

C. WITH CHECK OPTION

B. WITH READ ONLY

D. WITH ENCRYPTION

 

(4)  在 CREATE  VIEW 命令中哪个选项将强制所有通过视图修改的数据必须满足代码

中的 SELECT 语句中指定的选择条件？[    A    ]

A. WITH CHECK OPTION

C. WITH NO UPDATE

B. WITH READ ONLY

D. 没有这样的选项，假如用户对数据表有权限，用户可以更新视图。

 

(5)  执行哪一个系统存储过程，可以查看视图的定义信息？[    A    ]

A. sp_helptext           

B. sp_depends           

C. sp_help              

D. sp_rename

 

(6)  下列 SQL 语句中哪一行可能出错？[    C    ]

①  CREATE VIEW stud_view

②  AS SELECT stud_id stud_id,

③  name stud_name

④  FROM stud_info

⑤  WHERE stud_id = '0401020201'

⑥  ORDER BY birthday

A.  第 5 行                

B.  第 2 行                

C.  第 6 行                

D.  没有错误

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




  

第七章 索引

索引的概念（简答）

索引是以数据表的列为基础建立的数据库对象，它保存着表中排序的索引列，并且记录了索引列在数据表中的物理存储位置，实现了表中数据的逻辑或物理排序。

 

索引的优缺点（简答）

优点：

一，通过唯一性索引，可保证数据的唯一性。

二，加快数据检索的速度。

三，加速表和表之间的连接。

缺点：

一，创建和维护耗费时间。

二，占物理空间。

三，对表中的数据维护时，索引也要动态的维护，降低数据的维护速度。

 

聚集索引、非聚集索引（简答）

聚集索引：行的物理存储顺序与索引顺序完全相同，即索引的顺序决定了表中行的存储顺序，因为行是经过排序的，所以每个表中只能有一个聚集索引。

非聚集索引：并不在物理上排列数据，即索引中的逻辑顺序并不等同于表中行的物理顺序，索引仅仅记录指向表中行的位置的指针，这些指针本身是有序的，通过这些指针可以在表中快速地定位数据。

 

（选择）

一个数据表中至多有建立一个聚集索引。

 

建立索引的思路

1.主键字段

2.外键字段

3.经常在where中用到的字段

4.较少涉及、重复值较多，不建立索引

5.text、ntext、image、bit类型，不建立索引

 

索引视图

具有聚集索引的视图称为索引视图。

 

创建索引视图

Create view view_name

With schemabinding

As

Select_statment

 

P161页

1.(4)用下列语句建立表adresses。当表被建立时，将自动建立多少个索引？

CREATE TABLE addresses

(

    NAME varchar(40) PRIMARY KEY,

    STREET varchar(40),

    CITY varchar(40),

    STATE char(2) REFERENCES STATE(ST-CODE),

    ZIP decimal(6,0) NOT NULL,

    PHONE varchar(15) UNIQUE

)

答：当表被建立时，将自动建立 2 个索引(PRIMARY KEY, UNIQUE)。

 

P161页

2.(1)为课程信息表(lesson_info)的课程号(course_id)创建索引course_id_idx。

答：

CREATE INDEX course_id_idx

ON lesson_info(course_id)

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

 

第八章 存储过程与触发器

存储过程（简答）

当开发一个应用程序时，为了易于修改和扩充方便，我们经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句，即是“过程”。

SQLServer的存储过程是一组完成特定功能的T-SQL语句集，经编译后以特定的名称存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。

 

存储过程特点（简答）

1.允许模块化的程序设计 例子

2.更快的执行速度

3.有效降低网络流量

4.较好的安全机制

 

存储过程类型（选择或填空）

1.系统存储过程：

系统存储过程放在master数据库中。

sp_helptext：显示存储过程和其他对象的文本。

2.本地存储过程

3.临时存储过程：

1)本地临时存储过裎：

         以“#”开头

2)全局临时存储过程：

         以“##”开头

4.远程存储过程

 

存储过程创建方法（程序）

 

1.不带参数的存储过程

格式：

CREATE  PROC  存储过程名

AS  增删改查语句

 

存储过程的执行格式：

EXEC 存储过程名

 

例题：P187页 2(1)

创建一个存储过程，使其调用后能返回年龄为 40 以上职称为"副教授"的教师基

本信息。

——创建

CREATE PROC teacher_proc1

AS

SELECT * FROM teacher_info

WHERE age>40 AND tech_title='副教授'

——执行

EXEC teacher_proc1

 

2.带参数的存储过程，带返回值的存储过程

CREATE  PROC  存储过程名

[{@输入和输出参数的名称}  [=默认值]  [输出参数] [,…n]

AS 增删改查语句 

 

例题：P187页 2(2)

 

针对学生基本信息表(stud_info)，创建一个按性别统计人数的存储过程。

——创建

CREATE PROC people_num

@in_sex nchar(2),@out_num int OUTPUT

AS

BEGIN

IF @in_sex='男'

SELECT @out_num=count(gender)

FROM stud_info

WHERE gender='男'

ELSE

SELECT @out_num=count(gender)

FROM stud_info

WHERE gender='女'

END

——执行

DECLARE @out_num int

EXEC people_num '女', @out_num OUTPUT

SELECT @out_num

 

复习课例题：

ALTER PROC pro_test @xh varchar(10), @xm varchar(10), @cnt int OUTPUT

AS

SELECT @cnt=COUNT(*)

FROM stud_info

WHERE stud_id LIKE '%'+@xh+'%' OR

       name LIKE '%'+@xm+'%'

——执行

DECLARE @t int

EXEC pro_test '04',' 王', @t OUTPUT

SELECT @t

 

触发器（简答）

触发器是一种在数据表或视图被修改时自动执行的内嵌存储过程，主要通过事件进行触发的，而存储过程可以通过存储过程名字被直接调用。当对某个表进行UPDATE、INSERT、DELETE这些操作时，SQLServer就会自动执行触发器所定义的SQL语句。

触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。

 

书上例题 P179~P181

【示例8.12】在数据库student的表teacher_info上创建一个teacher_trigger1触发器，当执行INSERT操作时该触发器被触发。

CREATE TRIGGER teacher_trigger1

ON teacher_info

FOR INSERT

AS

RAISERROR('unauthorized',10,1)

 

【示例8.13】在数据库student的表teacher_info上建立一个名为teacher_trigger3的触发器，该触发器将被操作UPDATE所激活，该触发器不允许用户修改表的name列。

CREATE TRIGGER teacher_trigger3

ON teacher_info

FOR UPDATE

AS

    IF UPDATE(name)

    BEGIN

       RAISERROR('Unauthorized!',10,1)

       ROLLBACK TRANSACTION

    END

 

【练习8.9】在数据库student的表teacher_info上建立一个名为teacher_trigger4的DELETE触发器，该触发器将实现对表teacher_info中删除记录的操作给出报警，并取消当前的删除操作。

CREATE TRIGGER teacher_trigger4

ON teacher_info

FOR DELETE

AS

BEGIN

    RAISERROR('你不能删除记录行',10,1)

    ROLLBACK TRANSACTION

END

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




 

第九章 事物与游标

事务 （简答）

事务是由一系列的数据查询操作或更新操作构成的。从用户的观点来看，根据业务规则，这些操作是一个整体，不能分割，即要么所有的操作都顺利完成，要么一个也不要做。绝不能只完成了部分操作，而还有一些操作没有完成。事务中任何一个语句执行时出错，系统都会返回到事务开始前的状态。

 

事务的特性 （简答）

1.原子性

一个事务中的所有操作是一个逻辑上不可分割的单位。事务必须作为工作的最小单位，其所进行的操作要么全部执行，要么全部不执行。

2.一致性

事务结束时，必须使所有数据处于一致性状态。数据库中数据满足各种完整性规则。

3.隔离性

一个事务所做的修改，必须与其他事务所做的修改隔离。

4.持久性

一个事务一旦完成全部操作，它对数据库的所有更新操作的结果反映到数据库中。

 

书上例题 P190~P191

【示例9.2】

建立一个名为student_manager1的事务，事务将为课程号最后两位为06的多媒体技术课程、所有学生成绩进行FLOOR(SQRT(grade)*10)处理。

DECLARE @tranname varchar(20)

SELECT @tranname='student-manager1'

BEGIN TRAN @tranname

GO

UPDATE stud_grade SET grade=FLOOR(SQRT(grade)*10) WHERE course_id LIKE ''

GO

COMMIT TRAN @tranname

 

【示例9.3】

使用事务处理方式对表stud_grade执行更新操作，成功则提交事务，失败则取消事务。

BEGIN TRAN student_manager2

UPDATE stud_grade

SET grade=ROUND(SQRT(grade)*10,0) WHERE course_id LIKE ''

IF @@ERROR!=0

    ROLLBACK TRAN student_manager2

ELSE

    COMMIT TRAN student_manager2

锁 （简答）

锁作为一种安全机制，用以封锁正被一个事务修改的数据，防止其他用户访问到“不一致”的数据。锁机制能够控制多个用户的并发操作，可以防止用户读取正在由其他用户更改的数据或者多个用户同时修改同一数据，从而确保事务完整性和数据库一致性。

 

锁的控制方法

常用锁：排它锁和共享锁。

排它锁（X，写锁）

若事务T对数据对象A加上X锁，则只允许T读取和修改A。其它事务不能再对A加任何类型的锁，直到T释放A上的X锁。

排它锁用于数据修改操作，用以确保不会同时对同一资源进行并发修改。排它锁锁定的资源不能被其他并发事务读取或修改，形成独享。

共享锁（S，读锁）

若事务T对数据对象A加上S锁，则事务T可以读A，但不能修改A。其它事务职能再对A加上S锁，而不能加X锁，直到T释放A上的S锁。

共享锁用于只读操作，它允许多个并发事务读取一个资源，但是任何其他事务都不能修改锁定的数据。

 

锁的粒度

锁对象的大小称为锁粒度。锁对象可以是逻辑单位，这时的粒度可以是数据库、表、记录或列。

 

锁协议

锁协议是指每个事务的执行可以分为两个阶段：加锁阶段和解锁阶段。

 

游标及优点（简答）

游标是一种处理数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力。

优点：游标是基于逐行操作结果集的方法，它对SELECT语句的查询结果集中的记录行逐行处理，而不是整个结果集作同一处理，并基于游标的当前位置，更新或删除表或视图中的行。

 

使用游标步骤

1.声明或创建游标

2.打开游标

3.推进游标指针

4.逐行处理游标指针所指向的行数据。

5.关闭和释放游标。

 

书后习题 P204页

(7) 请用适当的语句完善下列程序。

USE student

GO

DECLARE teacher_cursor CURSOR

FOR SELECT * FROM teacher_info

--打开游标

OPEN teacher_currsor     

FETCH NEXT FROM teacher_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

    --提取数据

    FETCH NEXT FROM teacher_cursor

END

Close teacher_currsor

DEALLOCATE teacher_currsor

 

(8) 请用适当的语句完善下列程序。

USE student

GO

DECLARE teacher_cursor CURSOR

FOR SELECT * FROM teacher_info

OPEN teacher_cursor

FETCH NEXT FROM teacher_cursor

DELETE FROM teacher_info WHERE CURRENT OF teacher_cursor

CLOSE teacher_cursor

DEALLOCATE teacher_currsor

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