第十六周复习.docx
- 文档编号:27250358
- 上传时间:2023-06-28
- 格式:DOCX
- 页数:15
- 大小:188.60KB
第十六周复习.docx
《第十六周复习.docx》由会员分享,可在线阅读,更多相关《第十六周复习.docx(15页珍藏版)》请在冰豆网上搜索。
第十六周复习
《数据库应用》教学讲义(第十六讲)
本讲主要内容:
复习数据表的操作
⏹数据表的操作:
●创建表(同时建立字段的约束)
●修改表(改表名;加入列;修改列;删除列)
●建立表中字段的“约束”(5种)
●建立“规则”并绑定到指定的列
●表的更新(插入、删除、修改)
●表的查询(对象、范围、条件、分组、统计、排序)
●查询的保存(形成表、建立视图、建立存储过程)
数据表的操作
建立两个表(table):
“成绩表”和“工资表”基本要求如下(根据提供的资料,可以定义字段、数据类型及长度、并且可以设置相关的“约束”、或者建立“规则”并将其绑定到指定的列):
成绩表:
学号
姓名
班级
性别
数学
英语
计算机
总分
平均分
等级
3位数
3个字
会1
男/女
0-100
0-100
0-100
0-300
0-300
2字
工资表
工号
单位
姓名
性别
工作日期
职称
基本
工资
职务工资
奖金
应发
扣除
实发
3位数
3个字
3个字
男/女
4种
按年龄
分职务
按单位
交税
操作过程:
(以”成绩表”为例)
1.打开指定的数据库:
USE综合复习
GO
2.创建由前7个字段组成的表:
CREATETABLE成绩表
(
学号CHAR(3)PRIMARYKEY,--主键约束
姓名CHAR(6)NOTNULL,--非空
班级CHAR(8),
性别CHAR
(2)DEFAULT('女'),--默认约束
数学SMALLINT,
英语SMALLINT,
计算机SMALLINT
)
3.向已建立的表中增加后面3个字段(列):
ALTERTABLE成绩表ADD总成绩INT
ALTERTABLE成绩表ADD平均分NUMERIC(5,1)--长度5,1位小数
ALTERTABLE成绩表ADD等级CHAR(6)--最多3个汉字
4.
分别为”姓名”建立唯一性约束;为”性别”建立检查约束:
ALTERTABLE成绩表ADDCONSTRAINTIX_不重名UNIQUE(姓名)
ALTERTABLE成绩表ADDCONSTRAINTCK_性别
CHECK(性别IN('男','女'))
结果如图所示:
5.建立考试的评分”规则”(0-100),并绑定到3门考试课程上:
CREATERULE评分规则--建立规则
AS@FBETWEEN0AND100
--通过执行存储过程,绑定规则到指定的列
EXECsp_bindrule'评分规则','成绩表.数学'
GO
EXECsp_bindrule'评分规则','成绩表.英语'
GO
EXECsp_bindrule'评分规则','成绩表.计算机'
GO
向表中添加数据记录(行):
“成绩表”中加入10名你熟悉的同学姓名;2个班级(分出男/女生);3门考试课的原始成绩需要同时填入。
“工资表”中加入10个人(可使用)前表中的编号、姓名、性别,单位可自己确定(如:
商学院、理学院、工学院等),职称(助教、讲师、副教授、教授4级),出生日期可分别填写为50、60、70、80年代的日期。
操作过程:
向表中添加数据(行)
USE综合复习--打开指定的数据库
INSERTINTO成绩表(学号,姓名,班级,性别,数学,英语,计算机)
VALUES('001','张三','一班','男',100,90,80)
…
INSERTINTO成绩表(学号,姓名,班级,性别,数学,英语,计算机)
VALUES('010','李老四','一班','男',99,95,70)
…
INSERTINTO成绩表(学号,姓名,班级,性别,数学,英语,计算机)
VALUES('011','王老五','二班','男',60,55,30)
或者
--省略清单的插入语句(注意与上述命令的区别)
INSERT成绩表
VALUES('111','孙悟空','二班','男',55,55,55,NULL,NULL,NULL)
INSERT成绩表
VALUES('000','张某某','一班','女',100,100,100,NULL,NULL,NULL)
结果如图所示:
数据更新(修改原始数据,数据分布合理,有一定的代表性,为后面的处理打好基础。
)
“成绩表”的计算:
利用UPDATE计算总分、平均分;根据平均分给每一个学生评定等级(4级分)。
操作过程:
1.修改列名:
将”总成绩”改名为”总分”
EXECsp_rename'成绩表.总成绩','总分','COLUMN'
2.计算”总分”和”平均分”
UPDATE成绩表
SET总分=数学+英语+计算机,平均分=总分/3
3.根据”平均分”评定”等级”
UPDATE成绩表
SET等级='优秀'
WHERE平均分>=85AND平均分<=100
UPDATE成绩表
SET等级='良好'
WHERE平均分>=70AND平均分<85
UPDATE成绩表
SET等级='不及格'
WHERE平均分<60
结果如图所示
“工资表”的完善与计算:
1.按工龄确定“基本工资”:
工龄年*100;
2.按职称确定“职务工资”:
1000为基数,级差500;
3.按单位确定“奖金”:
分别为1000、2000、3000;
4.按应发额扣税:
3000以下免;
3000-5000扣10%;
5000以上扣20%。
5.计算实发。
表的查询:
1.查询的范围:
前3名、后30%。
操作过程:
USE综合复习
GO
SELECT*FROM成绩表--查询全部数据
G0
SELECTTOP3*FROM成绩表–-查询前3名数据
GO
SELECTTOP30PERCENT*--查询最后面的30%数据
FROM成绩表
ORDERBY学号DESC
2.查询的条件:
分为精确条件、非精确条件、复合条件。
如:
指定班级、单位或性别;姓名中包含某个汉字;考试成绩在指定的范围内;工资的范围等。
操作过程:
--查询姓名含有”老”或”小”的学生数据并按姓名的第2个字排序.
SELECT*FROM成绩表
WHERE姓名LIKE'%老%'OR姓名LIKE'%小%'
ORDERBYSUBSTRING(姓名,2,2)
3.查询结果排序:
按分数或工资的高低;按班级、单位、职称、性别、工龄等的先后、大小、长短等排序。
操作过程:
--查询’一班男生’且按分数高低排序
SELECT*FROM成绩表
WHERE班级='一班'AND性别='男'
ORDERBY总分DESC
4.统计:
对于数值型字段的计算:
最大、最小、合计、平均值等;对非数值型字段的统计:
数数,如:
男/女人数;班级/单位的人数;各类职称人数等;也可统计:
分数段内的人数。
操作过程:
--分班级,按性别统计人数和考试的平均成绩(无明细)
SELECT班级,性别,COUNT(*)AS人数,
STR(AVG(平均分),5,1)AS平均成绩
FROM成绩表
GROUPBY班级,性别
ORDERBY班级DESC
5.分组计算与统计:
各班级男/女生考试成绩的统计;不同单位、不同职称人员的工资统计等。
--分班级,按性别统计人数和考试的平均成绩
操作过程:
--分班级,按性别统计人数和考试的平均成绩(有明细)
SELECT班级,姓名,性别,平均分
FROM成绩表
ORDERBY班级DESC,性别
COMPUTECOUNT(班级),AVG(平均分)BY班级,性别
查询过程及结果的保存:
1.将查询的结果保存为“视图”:
创建视图的方法。
如:
对“成绩表”建立3个视图,分班级排名次;按性别排名次;分班级且按性别排名次。
操作过程:
--创建视图分班级排名次
CREATEVIEW分班级排名次
AS
--分班级汇总总分并排名次,合并显示
SELECT班级,姓名,总分
FROM成绩表
WHERE班级='一班'
UNION
SELECT班级,姓名,总分
FROM成绩表
WHERE班级='二班'
UNION
SELECT'---','平均值',AVG(总分)
FROM成绩表
--ORDERBY班级DESC,总分DESC(创建视图时,此句无效)
--创建按性别排名次的视图
CREATEVIEW按性别排名
AS
--按性别汇总总分并排名次,合并显示
SELECT性别,姓名,总分
FROM成绩表
WHERE性别='男'
UNION
SELECT性别,姓名,总分
FROM成绩表
WHERE性别='女'
UNION
SELECT'---','平均值',AVG(总分)
FROM成绩表
--ORDERBY性别DESC,总分DESC(创建视图时无效)
--创建视图分班级,按性别排名次
CREATEVIEW分班级按性别排名
AS
--将4个查询与计算结果合并显示
SELECT班级,性别,姓名,总分
FROM成绩表
WHERE班级='一班'AND性别='男'
UNION
SELECT班级,性别,姓名,总分
FROM成绩表
WHERE班级='一班'AND性别='女'
UNION
SELECT班级,性别,姓名,总分
FROM成绩表
WHERE班级='二班'AND性别='男'
UNION
SELECT班级,性别,姓名,总分
FROM成绩表
WHERE班级='二班'AND性别='女'
UNION
SELECT'---','---','平均值',AVG(总分)
FROM成绩表
--ORDERBY班级DESC,性别DESC,总分DESC(此句无效)
2.将查询问题保存为“存储过程”。
如:
查询某个学生的考试情况,且显示他在本班级,同性别的学生中的排名。
(执行查询时,输入其姓名,显示的是他所在班级中,与他同性别同学的考试成绩,从中可见他所处的名次。
)
操作过程:
/*建立带姓名作为以参数的存储过程*/
CREATEPROCEDURE查分
@nameCHAR(6)=''--定义参数
AS
/*查询学生在班级中,同性别同学中的排名程序*/
SELECT*FROM成绩表
WHERE班级=(
SELECT班级FROM成绩表
WHERE姓名=@name)AND性别=(
SELECT性别FROM成绩表
WHERE姓名=@name)
ORDERBY总分DESC
IF@@rowcount=0--此处系统函数返回"命令影响行数"
PRINT'查无此人!
'
3.编写如下存储过程:
完成查看考试结果;
操作过程:
/*创建查看的存储过程*/
CREATEPROC查看
AS
/*查看当前成绩表,分班级,按总分从高到低*/
USE综合复习
(EXEC重算--此处可以执行一个存储过程)
SELECT*FROM成绩表
ORDERBY班级DESC,总分DESC
对于总评成绩”不及格”的学生,指定一门课程,确定一个幅度(如:
0.1或-0.1),提分或降分;
操作过程:
(同理可对其它课程编写提分程序)
/*提分程序*/
CREATEPROC计算机提分
@freal=0--定义参数,确定提分的幅度与方向
AS
UPDATE成绩表SET计算机=计算机+计算机*@f
WHERE等级='不及格'
IF@@rowcount<>0--此处系统函数返回"命令影响行数"
PRINT'已经改变了指定的课程考试不及格者的成绩,请重新评级并查看。
'
ELSE
PRINT'没有提高该课程的成绩。
'
重新完成总分、平均分、等级的计算。
操作过程:
CREATEPROC重算--创建存储过程
AS
/*重新计算总分、平均分和评定等级*/
UPDATE成绩表
SET总分=数学+英语+计算机,
平均分=(数学+英语+计算机)/3
UPDATE成绩表
SET等级='优秀'WHERE平均分>=85
UPDATE成绩表
SET等级='良好'
WHERE平均分>=70AND平均分<85
UPDATE成绩表
SET等级='及格'
WHERE平均分>=60AND平均分<70
UPDATE成绩表
SET等级='不及格'
WHERE平均分<60
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第十六 复习