数据库实训.docx
- 文档编号:10569713
- 上传时间:2023-02-21
- 格式:DOCX
- 页数:27
- 大小:25.49KB
数据库实训.docx
《数据库实训.docx》由会员分享,可在线阅读,更多相关《数据库实训.docx(27页珍藏版)》请在冰豆网上搜索。
数据库实训
1创建学生成绩视图,要求查询所有选课学生的学号、姓名、课程名称及分数
createview学生成绩视图
asselect学生表.学号,姓名,课程名称,分数
from学生表,成绩表,课程表
where学生表.学号=成绩表.学号
and成绩表.课程号=课程表.课程号
select*from学生成绩视图
2创建班级学生视图,要求显示班级名称,学号,姓名,性别。
createview班级学生视图
asselect班级名称,学生表.学号,姓名,性别
from班级表,学生表
where班级表.班级号=学生表.班级号
3通过班级学生视图,添加学号为,姓名为张三,性别为男的学生信息。
insertinto班级学生视图(学号,姓名,性别)
values('001','张三','男')
4通过班级学生视图,将学号为的学生性别改为女。
alterview班级学生视图
asselect性别,学号
from学生表
where学号='001'and性别='女'
5通过班级学生视图,删除学号为的学生信息。
deletefrom班级学生视图
where学号='001'
6创建教师任课门数统计视图,
要求查询每位教师的教师号及其任课的门数。
createview教师任课门数统计视图
asselect教师表.教师号,count(课程号)as任课门数
from成绩表,教师表
groupby教师号
7创建学生课程视图,
要求查询选修两门以上课程的学生学号和选课门数。
createview学生课程视图
asselect学生表.学号,count(课程号)
from学生表,成绩表
where课程号>'2'and学生表.学号=成绩表.学号
8修改学生成绩视图,要求查询选课在三
门以上且各门课程均及格的学生的学号及其总成绩。
alterview学生成绩视图
asselect学号,sum(分数)from成绩表
havingavg(分数)>60
9删除学生成绩视图。
1.在学生表上创建一个触发器:
[修改学号],当修改该表中的学号时,
提示用户不允许修改该表中的主键,并回滚事务。
createtrigger修改学号on学生表
for
delete
as
ifupdate(学号)
begin
raiserror('你不能修改学号',16,1)
rollbacktransaction
end
2.在学生表上先建立一个约束,约束性别的取值只能为男或女。
然后再创建两个触发器,一个after一个insteadof触发器,
禁止修改性别列,并提示用户不允许修改该表中的性别列,并回滚事务
然后修改任一行的性别值为“母”,体验after和insteadof的区别。
createtable学生表
(性别nchar
(1)check(性别='男'or性别='女'))
createtriggerss1on学生表
afterupdate
as
begin
raiserror('用户不允许修改该表中的性别列',16,1)
rollbacktransaction
end
update学生表
set性别='母'
视图的创建
1.创建学生成绩视图,要求查询所有选课学生的学号、姓名、课程名称及分数。
createview学生成绩视图
asselect学生表.学号,姓名,课程名称,分数
from学生表,成绩表,课程表
where学生表.学号=成绩表.学号
and成绩表.课程号=课程表.课程号
2.创建班级学生视图,要求显示班级名称,学号,姓名,性别。
createview班级学生视图
asselect班级名称,学号,姓名,性别
from班级表,学生表
where班级表.班级号=学生表.班级号
3.通过班级学生视图,添加学号为001,姓名为张三,性别为男的学生信息。
insertinto班级学生视图(学号,姓名,性别)
values('001','张三','男')
4.通过班级学生视图,将学号为001的学生性别改为女。
update班级学生视图set性别='女'where学号='001'
5.通过班级学生视图,删除学号为001的学生信息。
deletefrom班级学生视图where学号='001'
6.创建教师任课门数统计视图,要求查询每位教师的教师号及其任课的门数。
createview教师任课门数统计视图
asselect教师号,count(distinct课程号)as门数
from成绩表groupby教师号
select*from教师任课门数统计视图
7.创建学生课程视图,要求查询选修两门以上课程的学生学号和选课门数。
createview学生课程视图
asselect学号,count(distinct课程号)as选课门数
from成绩表groupby学号
havingcount(distinct课程号)>=2
select*from学生课程视图
8.修改学生成绩视图,要求查询选课在三门以上且各门课程均及格的学生学号及其总成绩。
createview学生成绩视图
asselect学号,count(distinct课程号)as选课门数,sum(分数)as总成绩
from成绩表
where分数>=60groupby学号
havingcount(distinct课程号)>=3
select*from学生成绩视图
9.删除学生成绩视图。
drop学生成绩视图
数据查询与操作
1查询全体学生的学号、姓名和年龄。
select学号,姓名,年龄
from学生表
2查询学生的全部信息。
select*
from学生表
3查询选修了课程的学生的学号。
select学号
from学生表
4查询选修课程号为‘C1’的学生的学号和成绩。
select分数,学生表.学号
from学生表,成绩表
where学生表.学号=成绩表.学号
and课程号='c1'
5查询成绩高于分的学生的学号、课程号和成绩。
select学生表.学号,课程号,分数
from学生表,成绩表,课程表
where学生表.学号=成绩表.学号
and课程表.课程号=成绩表.课程号
and分数>85
6查询选修C1或C2且分数大于等于分学生的的学号、课程号和成绩。
select学生表.学号,课程号,分数
from学生表,成绩表,课程表
where学生表.学号=成绩表.学号
and课程表.课程号=成绩表.课程号
and课程号in('c1','c2')
and分数>=85
7查询工资不在至之间的教师的教师号、姓名及职称。
select教师号,姓名,职称
from教师表
where工资notbetween1000and1500
8查询没有选修C1,也没有选修C2的学生的学号、课程号和成绩
select学生表.学号,课程号,分数
from学生表,课程表,成绩表
where课程号notin('c1'&'c2')
9.查询所有姓杜的教师号和姓名
select教师号,姓名
from教师表
where姓名like'杜%'
10.求学号为s1学生的总分和平均分
selectsum(分数)as总分,avg(分数)as平均分
from成绩表
where学号='s1'
11.求选修c1号课程的最高分,最低分及之间相差的分数
selectmax(分数)as最高分,min(分数)as最高分,
max(分数)-min(分数)as分数差
from成绩表
where课程号='c1'
12.求班学生的总分
selectcount(*)
from学生表
where班级号='200301'
13.查询选修c1的学生学号和成绩,并按成绩降序排列
select学号,成绩
from成绩表
where课程号='c1'
orderby分数desc
14.查询选修c2,c3,c4或c5课程的学号,课程号和成绩,查询结果按学号升序排列,学号相同再按成绩降序排列
select学号,课程号,分数
from成绩表
where课程号in('c2','c3','c4','c5')
orderby学号asc,分数desc
15.查询杜老师所讲授的课程名称
select课程名称
from课程表,教师表,成绩表
where教师表.教师号=成绩表.教师号
and成绩表.课程号=课程表.课程号
and姓名like'杜%'
16.查询讲授号为c5的教师姓名
select姓名
from教师表,成绩表
where教师表.教师号=成绩表.教师号
and课程号='c5'
17.查询选修c1课程的学生的姓名与课程号
select姓名,课程号
from学生表,成绩表
where学生.学号=成绩表.学号
and课程号='c1'
18.查询所有选课学生的学号,姓名,选课名称及成绩
select学生表.学号,姓名,课程名称,分数
from学生表,课程表,成绩表
where学生表.学号=成绩表.学号
and成绩表.课程号=课程表.课程号
20.查询各位老师的教师号及其任课门数
select教师号,count(distinct课程号)as门数
from成绩表
groupby教师号
创建我班同学库
1.建立一个数据库,数据库名为:
我班同学库。
createdatabase我班同学库
2.
(1).同学表(学号char(6),姓名nchar(4),性别nchar
(1),出生日期datetime,身高decimal(5,2),民族nchar(5),身份证号char(18),宿舍编号char(6))。
要求:
姓名非空,性别取值在男女之间,名族默认为汉族。
createtable同学表
(学号char(6)primarykey,
姓名nchar(4)notnull,
性别nchar
(1)check(性别='男'or性别='女'),
出生日期datetime,
身高decimal(5,2),
民族nchar(5)default'汉族',
身份证号char(18),
宿舍编号char(6)foreignkey(宿舍编号)references宿舍表(宿舍编号))
(2).宿舍表(宿舍编号char(6),宿舍电话号码char(12))。
要求:
宿舍电话号码必须是以’0551-‘开头的7位数字。
createtable宿舍表
(宿舍编号char(6)primarykey,
宿舍电话号码char(12)check(宿舍电话号码like
‘0551-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
(3).课程表(课程号char(6),课程名称varchar(10))
createtable课程表
(课程号char(6)primarykey,
课程名称varchar(10))
(4).选课表(学号char(6),课程号char(6),分数decimal(5,2))
createtable选课表
(学号char(6),
课程号char(6)foreignkey(课程号)references课程表(课程号),
分数decimal(5,2),
primarykey(学号,课程号))
(5).创建一默认值m-性别,默认取男,并将其应用到同学表的性别列上。
createdefaultm_性别as'男'
execsp_bindefaultm_性别,'同学表.性别'
(6).创建一规则r_分数,要求分数必须是在0-100之间的数值,并将其应用到选课表的分数列上。
createruler_分数as@分数between0and100
execsp_bindruler_分数,'选课表.分数'
4.用T-SQL语句实现以下几题
(1)向同学表中插入学号为s10,姓名为张三,性别为男,身高为1.60的数据。
insertinto同学表(学号,姓名,性别,身高)values('s10','张三','男',1.60)
(2)将学号为s10的学生的性别改为女。
update同学表set性别='女’where学号='s10'
(3)删除学号为s10的学生记录。
delete同学表where学号='s10'
创建触发器
1.在学生表上创建一个触发器:
[修改学号],当修改该表中的学号时,提示用户不允许修改该表中的主键,并回滚事务。
altertable学生表
addconstraintssssssprimarykey(学号)
createtriggerddddddon学生表
forupdate
as
begin
ifupdate(学号)
raiserror('对不起,不允许修改该表中的主键',16,1)
rollbacktransaction
end
2.在学生表上先建立一个约束,约束性别的取值只能为男或女。
然后再创建两个触发器,一个after一个insteadof触发器,禁止修改性别列,并提示用户不允许修改该表中的性别列,并回滚事务,然后修改任一行的性别值为“母”,体验after和insteadof的区别。
altertable学生表
addconstraint性别check(性别in('男','女'))
createtriggersson学生表
forupdate
as
begin
ifupdate(性别)
raiserror('对不起,不允许修改该表中的性别列',16,1)
rollbacktransaction
end
createtriggeraaon学生表
insteadof
update
as
begin
ifupdate(性别)
raiserror('对不起,不允许修改该表中的性别列',16,1)
rollbacktransaction
end
创建存储过程与自定义函数
1.创建一个用户自定义函数:
[查询某班学生信息],通过输入班级名称,查询学生的姓名、性别、班级编号、班级名称。
并调用该函数查询。
creatfunction查询某班学生信息(@班级名nchar(10))
returnstable
asreturn(select姓名,性别,班级表.班级号,班级名称
from班级表,学生表
where班级表.班级号=学生表.班级号
and班级名称=@班级名)
select*from查询某班学生信息('03电子商务')
2.创建一个带参数的存储过程:
[查询某老师带过的学生],通过输入老师姓名,查询某某老师带过的学生的姓名、老师姓名、成绩的存储过程。
并带参数,执行该存储过程。
creatprocedure查询某老师带过的学生
@教师名varchar(20)
asselect学生表.姓名as学生姓名,教师表.姓名as老师姓名,分数
from学生表,教师表,成绩表
where学生表.学号=成绩表.学号
and教师表.教师号=成绩表.教师号
and教师表.姓名=@教师名
exec查询某老师带过的学生'郭老师'
3.创建一个带参数的存储过程:
[查询某课程不及格的学生],通过输入课程名称,查询选修某课程的学生的学号、课程名称、成绩的存储过程。
并带参数,执行该存储过程。
createprocedure查询某课程不及格的学生
@课程名varchar(20)
asselect学号,课程名称,分数
from成绩表,课程表
where成绩表.课程号=课程表.课程号
and课程名称=@课程名
and分数<60
exec查询某课程不及格的学生'大学语文’
4.创建一个带参数的存储过程:
[查询某姓学生信息],通过输入学生的姓,来查询学生的基本信息的存储过程。
并带参数,执行该存储过程
creatprocedure查询某姓学生信息
@姓varchar(4)
asselect*from学生表
where姓名like@姓+'%'
exec查询某姓学生信息'刘'
5.编写一个自定义函数,根据出生日期计算年龄。
createfunction出生日期计算年龄(@姓名varchar(6))
returnstable
asreturn(selectdatediff(yy,出生日期,getdate())as年龄
from学生表
where姓名=@姓名)
select*from出生日期计算年龄('于华')
什么是视图?
视图是从一张或多张表中导出的表,所以称为虚拟表,没有自己的真实数据.
视图的优点:
优点简化查询,能够进行模块化,提高封装性
视图的分类:
标准视图,索引视图,分区视图
存储过程概念:
是在数据库服务器端执行的T-SQL语句的集合,经编译后存放在数据库服务器中
存储过程分类:
系统存储过程,用户自定义存储过程,扩展存储过程
存储过程优点:
1.可以镶嵌使用,支持代码重用.2.可以接受并使用参数动态执行其中SQL语句
3.比一般的sql语句执行速度快4.具有安全特性和所有权链接,以及可以附加到他们的证书5允许模块化程序设计6.可以减少网络流量7.可以强制应用程序的安全性。
触发器概念:
触发器是一种特殊类型的存储过程,他在插入,删除或修改特定表中的数据时触发执行。
触发器类型:
UPDARE触发器INSERT触发器DELETE触发器INSTEADOFAFTER
触发器优点1.自动执行。
2.级联更新。
3.强化约束。
4.跟踪变化。
5.强制业务逻辑。
数据库系统的分类:
数据库DB(数据),数据库管理系统DBMS(软件),数据库管理员DBA(人员),硬件平台(硬件),软件平台(软件)
数据库设计方法的4个阶段:
(1)需求分析:
(2)概念结构设计:
(3)逻辑结构设计:
(4)物理分析:
HAVING与WHERE的区别:
having在groupby的后面,where在groupby的前面
椭圆是属性,矩形是实体,菱形是联系
数据库三级模式:
模式,内模式,外模式
三级模式之间映射:
外模式/模式:
保证数据与的逻辑独立性
内模式/模式:
定义数据库的全局逻辑结构与存储结构的对应关系
数据模型:
数据结构:
对系统静态描述,描述对象包括数据类型,内容,性质,和数据之间的关系
数据操作:
对系统动态描述,对数据库中各种对象事例的操作。
完整性约束:
完整性规律集合。
定义给定数据结构模型中数据及其联系所具有制约和依存规则
数据文件类型:
主要数据文件:
.mdf次要数据文件:
.ndf事物日志文件:
.ldf
系统数据库:
Master数据库tempdb数据库Model数据库Msdb数据库资源系统数据库
创建一个用户自定义函数:
[查询某班学生信息],
通过输入班级名称,查询学生的姓名、性别、班级编号、班级名称。
并调用该函数查询。
createfunction某班学生信息(@班级名varchar(20))
returnstableas
return(select姓名,性别,班级表.班级号,班级名称
from学生表,班级表
where学生表.班级号=班级表.班级号
and班级名称=@班级名)
select*from某班学生信息('03电子商务')
创建一个带参数的存储过程:
[查询某老师带过的学生],通过输入老师姓名,
查询某某老师带过的学生的姓名、老师姓名、成绩的存储过程。
并带参数,执行该存储过程
createprocedure某老师带过的学生(@教师姓名varchar(20))
asselect学生表.姓名as学生姓名,教师表.姓名as教师姓名,分数
from学生表,教师表,成绩表
where学生表.学号=成绩表.学号
and成绩表.教师号=教师表.教师号
and教师表.姓名=@教师姓名
某老师带过的学生'计算机'
创建一个带参数的存储过程:
[查询某课程不及格的学生],通过输入课程名称,
查询选修某课程的学生的学号、课程名称、成绩的存储过程。
并带参数,执行该存储过程。
createprocedure某课程不及格的学生@班级名varchar(20)
asselect学号,课程名称,分数
from成绩表,课程表
where成绩表.课程号=课程表.课程号
and课程名称=@班级名
某课程不及格的学生'大学语文'
创建一个带参数的存储过程:
[查询某姓学生信息],通过输入学生的姓,
来查询学生的基本信息的存储过程。
并带参数,执行该存储过程。
createprocedure某姓学生信息c(@班级名varchar(20))
asselect*from学生表
where姓名like'@班级名%'
某姓学生信息'李'
编写一个自定义函数,根据出生日期计算年龄
createfunction出生日期计算年龄(@出生日期datetime)
returnstableas
return(selectdatediff(yy,出生日期,getdate())as年龄
from学生表
where年龄=@出生日期)/
createproc查询某姓学生信息@学生的姓varchar(20)
as
select*
from学生表
where姓名like@学生的姓+'%'
exec查询某姓学生信息'王'
(5)编写一个自定义函数,根据出生日期计算年龄。
createfunction根据出生日期计算年龄(@出生日期datetime)
returnsintas
begin
returndatediff(yy,@出生日期,getdate())
end
selectdbo.根据出生日期计算年龄('1992-1-2')
视图的创建
1.创建学生成绩视图,要求查询所有选课学生的学号、姓名、课程名称及分数。
createview学生成绩视图
asselect学生表.学号,姓名,课程名称,分数
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库
![提示](https://static.bdocx.com/images/bang_tan.gif)