WHERE 专业= '通信工程' COMPUTE COUNT(学号)
【例4.67】 将学生按专业排序，并汇总各专业人数和平均学分。 SELECT 学号, 姓名, 出生时间, 总学分 FROM XSB ORDER BY 专业 COMPUTE COUNT(学号), AVG(总学分) BY 专业
【例4.74】 创建CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业为计算机这个条件。
CREATE VIEW CS_KC WITH ENCRYPTION AS SELECT XSB.学号, 课程号, 成绩 FROM XSB, CJB WHERE XSB.学号 =CJB.学号 AND 专业 = '计算机' WITH CHECK OPTION
【例4.75】 创建计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。 CREATE VIEW CS_KC_AVG(num,score_avg) AS SELECT 学号, AVG(成绩) FROM CJB GROUP BY 学号
【例4.77】 查找平均成绩在80分以上的学生的学号和平均成绩。
本例首先创建学生平均成绩视图XS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。
CREATE VIEW XS_KC_AVG ( num,score_avg ) AS SELECT 学号, AVG(成绩) FROM CJB GROUP BY 学号
第5章要求掌握例题：
【例5.18】 将学号为081101的学生的总学分使用循环修改到大于等于60，每次只加2，并判断循环了多少次。 DECLARE @num INT SET @num=0
WHILE (SELECT 总学分 FROM XSB WHERE 学号='081101')<60 BEGIN UPDATE XSB SET 总学分=总学分+2 WHERE 学号= '081101' SET @num=@num+1 END
SELECT @num AS 循环次数
26
【例5.31】 创建用户定义函数，实现计算全体学生某门功课的平均成绩的功能。 CREATE FUNCTION average(@cnum char(20)) RETURNS int AS BEGIN DECLARE @aver int SELECT @aver= ( SELECT avg(成绩) FROM CJB WHERE 课程号=@cnum GROUP BY 课程号 ) RETURN @aver END
【例5.32】 调用例5.31定义的函数average。新建一个查询，输入以下语句并执行： USE PXSCJ /*假设用户函数average在此数据库中已定义*/ GO
DECLARE @course1 char(20) /*定义局部变量*/ DECLARE @aver1 int
SELECT @course1 = '101' /*给局部变量赋值*/ /*调用用户函数将返回值赋给局部变量*/ SELECT @aver1=dbo.average(@course1)
SELECT @aver1 AS '101课程的平均成绩' /*显示局部变量的值*/
第6章要求掌握例题： 【例6.11】 首先在PXSCJ数据库中定义表book及名为 today 的默认值，然后将其绑定到 book表的hire_date列。 CREATE TABLE book ( book_id char(6), name varchar(20) NOT NULL, hire_date datetime NOT NULL ) GO
CREATE DEFAULT today AS getdate() GO
EXEC sp_bindefault 'today', 'book.hire_date'
【例6.13】 建立表KCB2，同时定义总学分的约束条件为0～60。 CREATE TABLE KCB2
( 课程号 char(6) NOT NULL, 课程名 char(8) NOT NULL, 学分 tinyint CHECK (学分>=0 AND 学分<=60) NULL /*通过CHECK子句定义约束条件*/)
【例6.16】 修改XSB2表，向其中添加一个“身份证号码”字段，对该字段定义UNIQUE约束。对“出生时间”字段定义UNIQUE约束。
27
ALTER TABLE XSB2 add 身份证号码 char(20)
constraint sf_uk unique nonclustered(身份证号码) GO
ALTER TABLE XSB2
add constraint CJSJ_UK UNIQUE NONCLUSTERED(出生时间)
【例6.19】 创建一个表student1，只考虑学号和出生日期两列，出生日期必须大于1980年1月1日，并命名CHECK约束。 CREATE TABLE student1 ( 学号 char(6) NOT NULL, 出生时间 datetime NOT NULL,
CONSTRAINT DF_student1_cjsj
CHECK(出生时间>'1980-01-01') )
【例6.21】 通过修改PXSCJ数据库的CJB表，增加成绩字段的CHECK约束。
ALTER TABLE CJB
ADD CONSTRAINT cj_constraint
CHECK(成绩>=0 AND 成绩<=100)
【例6.23】 如下程序创建一个规则，并绑定到表KCB的课程号列，用于限制课程号的输入范围。
CREATE RULE kc_rule AS @range like '[1-5][0-9][0-9]' GO
EXEC sp_bindrule 'kc_rule', 'KCB.课程号' GO
【例6.24】 创建一个规则，用以限制输入到该规则所绑定的列中的值只能是该规则中列出的值。
CREATE RULE list_rule AS @list IN ('C语言', '离散数学', '微机原理') GO
EXEC sp_bindrule 'list_rule', 'KCB.课程名' GO
【例6.25】 定义CJB外键。 create table cjb1
( 学号 char(6) not null foreign key (学号) references XSB(学号) ,
课程号 char(3) not null foreign key (课程号) references KCB(课程号) ,
28
成绩 int null default 0 check(成绩>=0 and 成绩<=100), primary key(学号,课程号))
alter table CJB
add foreign key (学号) references XSB(学号)
alter table CJB
add foreign key (课程号) references KCB(课程号)
第7章要求掌握例题： 【例7.2】 从PXSCJ1数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。
CREATE PROCEDURE student_info1 @name char (8), @cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, t.学分 FROM XSB a INNER JOIN CJB b ON a.学号 = b.学号 INNER JOIN KCB t ON b.课程号= t.课程号 WHERE a.姓名=@name and t.课程名=@cname 或
CREATE PROCEDURE student_info1 @name char (8), @cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, t.学分 FROM XSB a ， CJB b，KCB t WHERE a.姓名=@name and t.课程名=@cname and a.学号 = b.学号and b.课程号= t.课程号
【例7.4】 从三个表的连接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。 CREATE PROCEDURE st_info @name varchar(30) = '李%' AS SELECT a.学号,a.姓名,c.课程名,b.成绩 FROM XSB a INNER JOIN CJB b ON a.学号 =b.学号 INNER JOIN KCB c ON c.课程号= b.课程号 WHERE 姓名 LIKE @name EXECUTE st_info '王%'
【例7.11】 创建触发器，当向CJB表中插入一个学生的成绩时，将XSB表中该学生的总学分加上添加的课程的学分。
CREATE TRIGGER cjb_insert ON CJB AFTER INSERT AS
29
BEGIN DECLARE @num char(6), @kc_num char(3) DECLARE @xf int SELECT @num=学号, @kc_num=课程号 from inserted SELECT @xf=学分 FROM KCB WHERE 课程号=@kc_num UPDATE XSB SET 总学分=总学分+@xf WHERE 学号=@num PRINT '修改成功' END
INSERT INTO cjb VALUES('081101','208',65)
【例7.12】 创建触发器，当修改XSB表中的学号时，同时也要将CJB表中的学号修改成相应的学号（假设XSB表和CJB表之间没有定义外键约束）。 CREATE TRIGGER xsb_update ON XSB AFTER UPDATE AS BEGIN DECLARE @old_num char(6), @new_num char(6) SELECT @old_num=学号 FROM deleted SELECT @new_num=学号 FROM inserted UPDATE CJB SET 学号=@new_num WHERE 学号=@old_num END
UPDATE XSB SET 学号='081120' WHERE 学号='081101'
【例7.13】 在删除XSB表中的一条学生记录时将CJB表中该学生的相应记录也删除。 CREATE TRIGGER xsb_delete ON XSB AFTER DELETE AS BEGIN DELETE FROM CJB WHERE 学号 IN ( SELECT 学号 FROM deleted ) END
DELETE FROM xsb WHERE 学号='081120' 【例7.14】 在KCB表中创建UPDATE和DELETE触发器，当修改或删除KCB表中的课程号字段时，同时修改或删除CJB表中的该课程号。
CREATE TRIGGER kcb_trig ON KCB AFTER UPDATE, DELETE AS BEGIN IF (UPDATE(课程号)) UPDATE CJB SET 课程号=(SELECT 课程号 FROM inserted) WHERE 课程号=(SELECT 课程号 FROM deleted) ELSE DELETE FROM CJB
30
WHERE 课程号 IN(SELECT 课程号 FROM deleted) END
Update kcb set 课程号='401' where 课程号 ='101'
31