1、第十六周复习数据库应用教学讲义 （第十六讲）本讲主要内容：复习数据表的操作 数据表的操作: 创建表(同时建立字段的约束) 修改表(改表名;加入列;修改列;删除列) 建立表中字段的“约束” (5种) 建立“规则”并绑定到指定的列 表的更新（插入、删除、修改） 表的查询（对象、范围、条件、分组、统计、排序） 查询的保存（形成表、建立视图、建立存储过程）数据表的操作 建立两个表(table):“成绩表”和“工资表”基本要求如下（根据提供的资料，可以定义字段、数据类型及长度、并且可以设置相关的“约束”、或者建立“规则”并将其绑定到指定的列）：成绩表:学号姓名班级性别数学英语计算机总分平均分等级3位数3
2、个字会1男/女0-1000-1000-1000-3000-3002字工资表工号单位姓名性别工作日期职称基本工资职务工资奖金应发扣除实发3位数3个字3个字男/女4种按年龄分职务按单位交税操作过程：(以” 成绩表”为例)1. 打开指定的数据库： USE 综合复习GO2. 创建由前7个字段组成的表：CREATE TABLE 成绩表 ( 学号 CHAR(3) PRIMARY KEY, -主键约束 姓名 CHAR(6) NOT NULL, -非空 班级 CHAR(8), 性别 CHAR(2) DEFAULT(女), -默认约束 数学 SMALLINT, 英语 SMALLINT, 计算机 SMALLINT
3、)3. 向已建立的表中增加后面3个字段(列):ALTER TABLE 成绩表 ADD 总成绩 INT ALTER TABLE 成绩表 ADD 平均分 NUMERIC(5,1) -长度5,1位小数ALTER TABLE 成绩表 ADD 等级 CHAR(6) -最多3个汉字4. 分别为” 姓名”建立唯一性约束;为” 性别”建立检查约束:ALTER TABLE 成绩表 ADD CONSTRAINT IX_不重名 UNIQUE(姓名)ALTER TABLE 成绩表 ADD CONSTRAINT CK_性别 CHECK(性别 IN(男,女)结果如图所示:5. 建立考试的评分”规则”(0-100),并绑定
4、到3门考试课程上:CREATE RULE 评分规则 -建立规则AS F BETWEEN 0 AND 100-通过执行存储过程,绑定规则到指定的列EXEC sp_bindrule 评分规则,成绩表.数学GOEXEC sp_bindrule 评分规则,成绩表.英语GOEXEC sp_bindrule 评分规则,成绩表.计算机GO 向表中添加数据记录（行）：“成绩表”中加入10名你熟悉的同学姓名；2个班级（分出男/女生）；3门考试课的原始成绩需要同时填入。“工资表”中加入10个人（可使用）前表中的编号、姓名、性别，单位可自己确定（如：商学院、理学院、工学院等），职称（助教、讲师、副教授、教授4级），
5、出生日期可分别填写为50、60、70、80年代的日期。操作过程：向表中添加数据(行)USE 综合复习 -打开指定的数据库INSERT INTO 成绩表(学号,姓名,班级,性别,数学,英语,计算机)VALUES(001,张三,一班,男,100,90,80)INSERT INTO 成绩表(学号,姓名,班级,性别,数学,英语,计算机)VALUES(010,李老四,一班,男,99,95,70)INSERT INTO 成绩表(学号,姓名,班级,性别,数学,英语,计算机)VALUES(011,王老五,二班,男,60,55,30)或者-省略清单的插入语句(注意与上述命令的区别)INSERT 成绩表VALUE
6、S(111,孙悟空,二班,男,55,55,55,NULL,NULL,NULL)INSERT 成绩表VALUES(000,张某某,一班,女,100,100,100,NULL,NULL,NULL)结果如图所示: 数据更新（修改原始数据，数据分布合理，有一定的代表性，为后面的处理打好基础。）“成绩表”的计算：利用UPDATE 计算总分、平均分；根据平均分给每一个学生评定等级（4级分）。操作过程：1. 修改列名:将”总成绩”改名为”总分”EXEC sp_rename 成绩表.总成绩,总分,COLUMN2. 计算”总分”和”平均分”UPDATE 成绩表SET 总分=数学+英语+计算机,平均分=总分/33
7、. 根据”平均分”评定”等级”UPDATE 成绩表SET 等级=优秀WHERE 平均分=85 AND 平均分=70 AND 平均分85UPDATE 成绩表SET 等级=不及格WHERE 平均分60结果如图所示“工资表”的完善与计算：1. 按工龄确定“基本工资”：工龄年*100；2. 按职称确定“职务工资”:1000为基数，级差500；3. 按单位确定“奖金”：分别为1000、2000、3000；4. 按应发额扣税：3000以下免；30005000扣10%；5000以上扣20%。5. 计算实发。 表的查询：1. 查询的范围：前3名、后30%。操作过程:USE 综合复习GOSELECT * FRO
8、M 成绩表 -查询全部数据G0SELECT TOP 3 * FROM 成绩表 -查询前3名数据GOSELECT TOP 30 PERCENT * - 查询最后面的30%数据FROM 成绩表 ORDER BY 学号 DESC 2. 查询的条件：分为精确条件、非精确条件、复合条件。如：指定班级、单位或性别；姓名中包含某个汉字；考试成绩在指定的范围内；工资的范围等。操作过程:-查询姓名含有”老”或” 小”的学生数据并按姓名的第2个字排序.SELECT * FROM 成绩表WHERE 姓名 LIKE %老%OR 姓名 LIKE %小% ORDER BY SUBSTRING(姓名,2,2)3. 查询结果
9、排序：按分数或工资的高低；按班级、单位、职称、性别、工龄等的先后、大小、长短等排序。操作过程:-查询一班男生且按分数高低排序SELECT * FROM 成绩表 WHERE 班级=一班AND 性别=男 ORDER BY 总分 DESC4. 统计：对于数值型字段的计算：最大、最小、合计、平均值等；对非数值型字段的统计：数数，如：男/女人数；班级/单位的人数；各类职称人数等；也可统计：分数段内的人数。操作过程:-分班级,按性别统计人数和考试的平均成绩(无明细)SELECT 班级,性别,COUNT(*) AS 人数,STR(AVG(平均分),5,1) AS 平均成绩FROM 成绩表GROUP BY 班
10、级,性别 ORDER BY 班级 DESC5. 分组计算与统计：各班级男/女生考试成绩的统计；不同单位、不同职称人员的工资统计等。-分班级,按性别统计人数和考试的平均成绩操作过程:-分班级,按性别统计人数和考试的平均成绩(有明细)SELECT 班级,姓名,性别,平均分FROM 成绩表ORDER BY 班级 DESC,性别COMPUTE COUNT(班级),AVG(平均分) BY 班级,性别 查询过程及结果的保存：1. 将查询的结果保存为“视图”：创建视图的方法。如：对“成绩表”建立3个视图，分班级排名次；按性别排名次；分班级且按性别排名次。操作过程:-创建视图分班级排名次CREATE VIEW
11、 分班级排名次AS-分班级汇总总分并排名次,合并显示SELECT 班级,姓名,总分 FROM 成绩表WHERE 班级=一班UNIONSELECT 班级,姓名,总分 FROM 成绩表WHERE 班级=二班UNIONSELECT -,平均值,AVG(总分)FROM 成绩表-ORDER BY 班级 DESC,总分 DESC (创建视图时,此句无效)-创建按性别排名次的视图CREATE VIEW 按性别排名AS-按性别汇总总分并排名次,合并显示SELECT 性别,姓名,总分 FROM 成绩表WHERE 性别=男UNIONSELECT 性别,姓名,总分 FROM 成绩表WHERE 性别=女UNIONSE
12、LECT -,平均值,AVG(总分)FROM 成绩表-ORDER BY 性别 DESC,总分 DESC(创建视图时无效)-创建视图分班级,按性别排名次CREATE VIEW 分班级按性别排名AS-将4个查询与计算结果合并显示SELECT 班级,性别,姓名,总分 FROM 成绩表WHERE 班级=一班AND 性别=男UNIONSELECT 班级,性别,姓名,总分 FROM 成绩表WHERE 班级=一班AND 性别=女UNIONSELECT 班级,性别,姓名,总分FROM 成绩表WHERE 班级=二班AND 性别=男UNIONSELECT 班级,性别,姓名,总分 FROM 成绩表WHERE 班级=
13、二班AND 性别=女UNIONSELECT -,-,平均值,AVG(总分)FROM 成绩表-ORDER BY 班级 DESC,性别 DESC,总分 DESC(此句无效)2. 将查询问题保存为“存储过程”。如：查询某个学生的考试情况，且显示他在本班级，同性别的学生中的排名。（执行查询时，输入其姓名，显示的是他所在班级中，与他同性别同学的考试成绩，从中可见他所处的名次。）操作过程:/*建立带姓名作为以参数的存储过程*/CREATE PROCEDURE 查分name CHAR(6) = -定义参数AS/*查询学生在班级中,同性别同学中的排名程序*/SELECT * FROM 成绩表WHERE 班级=
14、(SELECT 班级 FROM 成绩表WHERE 姓名=name) AND 性别=(SELECT 性别 FROM 成绩表WHERE 姓名=name)ORDER BY 总分 DESCIF rowcount=0 -此处系统函数返回命令影响行数 PRINT 查无此人!3. 编写如下存储过程: 完成查看考试结果;操作过程:/*创建查看的存储过程*/CREATE PROC 查看AS/*查看当前成绩表,分班级,按总分从高到低*/USE 综合复习(EXEC 重算 -此处可以执行一个存储过程)SELECT * FROM 成绩表ORDER BY 班级 DESC,总分 DESC 对于总评成绩”不及格”的学生,指定
15、一门课程,确定一个幅度(如:0.1或0.1),提分或降分;操作过程:(同理可对其它课程编写提分程序)/*提分程序*/CREATE PROC 计算机提分f real=0 -定义参数,确定提分的幅度与方向ASUPDATE 成绩表 SET 计算机=计算机+计算机*fWHERE 等级=不及格IF rowcount0 -此处系统函数返回命令影响行数 PRINT 已经改变了指定的课程考试不及格者的成绩,请重新评级并查看。ELSE PRINT 没有提高该课程的成绩。 重新完成总分、平均分、等级的计算。操作过程:CREATE PROC 重算 -创建存储过程AS/*重新计算总分、平均分和评定等级*/UPDATE 成绩表 SET 总分=数学+英语+计算机,平均分=(数学+英语+计算机)/3UPDATE 成绩表SET 等级=优秀 WHERE 平均分=85UPDATE 成绩表SET 等级=良好WHERE 平均分=70 AND 平均分=60 AND 平均分70UPDATE 成绩表SET 等级=不及格WHERE 平均分60
