数据库.docx
- 文档编号:30151980
- 上传时间:2023-08-05
- 格式:DOCX
- 页数:12
- 大小:17.73KB
数据库.docx
《数据库.docx》由会员分享,可在线阅读,更多相关《数据库.docx(12页珍藏版)》请在冰豆网上搜索。
数据库
SQL Server 2005实用教程
张 勃
日期:
2014年1月1日
一、创建student数据库。
文件保存路径为E:
\student,其余自定。
createdatabasestudent
onprimary
(name='student_data',
filename='e:
\student\student_data.mdf',
size=5mb,
maxsize=30mb,
filegrowth=2mb
)
logon
(name='student_log',
filename='e:
\student\yggh998877.ldf',
size=5mb,
maxsize=10mb,
filegrowth=5%
)
go
usestudent
go
二、在student数据库中,创建如下表:
1、学生信息表,结构如下:
学号char(11)姓名char(10)性别char
(2)出生日期datetime籍贯varchar(20)
要求:
'学号'设置为主键约束;'性别'的默认值为‘男’
usestudent
go
createtablestudent
(
学号char(11)primarykey,
姓名char(10)notnull,
性别char
(2)default('男'),
出生日期datetime,籍贯varchar(20)
)
2、图书信息表,结构如下:
图书编号char(17)名称varchar(20)作者varchar(20)类型char(10)出版日期datetime单价decimal(6,2)
要求:
设置'图书编号'为主键约束;'单价'为检查约束[0,100]
usestudent
go
createtable图书信息表
(
图书编号char(17)notnullprimarykey,
名称varchar(20)notnull,
作者varchar(20),
出版日期datetime,
单价decimal(6,2)check(单价>0and单价<=100)
)
go
3、借阅表
学号char(11)图书编号char(17)
借阅日期datetime
要求:
设置'图书编号'与'学号'为主键约束。
设置'学号'参照'学生信息表.学号'为外键约束;'图书编号'参照'图书信息表.图书编号'为外键约束.
createtable借阅表
(
学号char(11)notnull,
图书编号char(17)notnull,
借阅日期datetime
)
altertable借阅表
addconstraintpk_aa_asaprimarykey(学号,图书编号)
altertable借阅表
addconstraintfk_jybforeignkey(学号)referencesstudent(学号)
altertable借阅表
addconstraintfk_tsbforeignkey(图书编号)references图书信息表(图书编号)ondeletenoaction
三、利用insert语句向表中插入5条以上记录。
1、默认全部列赋值
select*fromstudent
select*from图书信息表
select*from借阅表
insertintostudent
values('0001','张三','男','1989-09-05','石家庄')
insertintostudent
values('0002','李四','男','1993-01-02','张家口')
insertintostudent
values('0003','王五','女','1994-02-09','保定')
insertintostudent
values('0004','赵六','男','1994-01-05','承德')
insertintostudent
values('0005','孙七','女','1994-06-30','邯郸')
go
2、部分列赋值
insertintostudent(学号,姓名)
values('0007','张七')
insertinto图书信息表(图书编号,名称,单价)
values('A03','操作系统',22)
go
四、修改表中的信息
1、修改学生信息表中张三同学的籍贯为‘张家口“;
updatestudentset籍贯='张家口'where姓名='张三'
2、将图书信息表中单价大于50元的的单价减少15%;
update图书信息表set单价=单价*0.85where单价>50
3、将图书信息表中名称为数据库原理且单价小于20元的图书单价增加10元。
update图书信息表set单价=单价+10where名称='数据库原理'and单价<20
五、删除表中的信息
1、删除图书信息表中作者为张三的记录;
delete图书信息表where姓名='张三'
2、删除学生信息表中籍贯为张家口的同学信息;
deletestudentwhere籍贯='张家口'
3、删除图书信息表名称为数据库原理且单价在[10,30]的记录;
delete图书信息表where单价>10and单价<=30and名称='数据库原理'
4、删除student表中的所有记录;
usestudent
go
5、删除表对象。
droptablestudent
go
六、查询信息
1、查询学生信息表中的所有同学的信息;
select*fromstudent
2、查询图书信息表中所有图书信息;
select*from图书信息表
3、查询所有男生的信息;
select*fromstudentwhere性别='男';
4、查询前10名男生的信息;
selecttop10*fromstudentwhere性别='男';
5、查询籍贯为'石家庄市'的男生信息;
select*fromstudentwhere性别='男'and籍贯='石家庄市';
6、查询单价介于[10,20]之间的图书信息;
select*from图书信息表where单价between10and20
7、查询年龄大于20的女生的信息。
(注:
年龄的计算方法:
year(getdate())-year(出生日期))
select*fromstudentwhereyear(getdate())-year(出生日期)>20and性别='女'
七、查询统计信息
1、统计男生女生人数,并别名显示;
select性别,count(*)as人数fromstudentgroupby性别
2、图书信息表中,查询单价大于20,并按升序排列;
select*from图书信息表where单价>20orderby单价asc
3、查询单价增加20%的结果;
select*,单价*1.2'单价增加20%'from图书信息表
4、查询学生信息表中,学生姓名中包含'红刘飞'等字符的同学信息;
select*fromstudentwhere姓名like'%[红刘飞]%'
5、查询男生并且籍贯中包含'石家庄'的同学信息;
select*fromstudentwhere性别='男'and籍贯like'%石家庄%'
6、查询每类图书的单价的平均值及数目;(图书信息表中有‘类别’列)
select类别,avg(单价),count(*)from图书信息表groupby类别
7、查询高于所有图书的平均值的图书的信息;
select*from图书信息表where单价>(selectavg(单价)from图书信息表)
8、查询哪类图书的单价平均值大于21;
select类别,avg(单价)from图书信息表
groupby类别havingavg(单价)>21
9、查询所有同学的平均年龄;
selectavg(year(getdate())-year(出生日期))fromstudent
10、查询比同学的平均年龄高的同学的信息。
select*fromstudentwhere
year(getdate())-year(出生日期)>(selectavg(year(getdate())-year(出生日期))fromstudent)
八、查询统计信息
1、查询单价最高的图书信息;
select*from图书信息表where单价=(selectmax(单价)fromstudent)
2、查询没有借阅过图书的同学信息;
select*fromstudentwhere学号notin(select学号from借阅表)
3、查询没有被借阅过的图书信息;
seelct*from图书信息表where图书编号notin(select图书编号from借阅表)
4、查询每一位同学借阅的详细信息,包括(学号,姓名,图书编号,图书名称,借阅时间);
selectstudent.学号,姓名,借阅表.图书编号,图书名称,借阅时间fromstudentjoin借阅表
onstudent.学号=借阅表.学号join图书信息表on图书信息表.图书编号=借阅表.图书编
5.查询张三同学所借图书的信息(学号,姓名,图书编号,图书名称,借阅时间);
selectstudent.学号,姓名,借阅表.图书编号,图书名称,借阅时间fromstudentjoin借阅表
onstudent.学号=借阅表.学号join图书信息表on图书信息表.图书编号=借阅表.图书编
where姓名='张三'
6、统计每位同学借阅图书的册数。
selectstudent.学号,姓名,count(*)借阅册数fromstudentjoin借阅表
onstudent.学号=借阅表.学号
groupbystudent.学号,姓名
九、创建视图
1、创建视图V_1,查询所有图书信息;
createviewv_1
as
select*from图书信息表
go
select*fromv_1
2、创建袖图V_2,查询男生信息;
createviewv_2
as
select*fromstudentwhere性别='男'
go
select*fromv_2
3、创建视图v_3,统计男女生人数;
createviewv_3
as
select性别,count(*)as人数fromstudentgroupby性别
go
select*fromv_3
4、创建视图v_4,查询每位同学所借图书信息(学号,姓名,图书编号,名称,借阅时间);
createviewv_4
as
selectstudent.学号,姓名,借阅表.图书编号,名称,借阅日期fromstudentjoin借阅表
onstudent.学号=借阅表.学号join图书信息表on图书信息表.图书编号=借阅表.图书编号
go
select*fromv_4
5、创建视图v_5,统计每位同学所借图书数量;
createviewv_5
as
selectstudent.学号,姓名,count(*)借阅册数fromstudentjoin借阅表
onstudent.学号=借阅表.学号
groupbystudent.学号,姓名
go
select*fromv_5
十、创建存储过程
1、创建存储过程,功能:
求1+2+3+……+n的和;
createprocedurepr_sum@nint
as
delect@sumint,@iint
set@sum=0
set@i=1
while(@i<=n)
deign
set@sum=@sum+@i
set@i=@i+1
end
print@sum
go
executepr_sum
go
2.创建存储过程pr_select,功能:
统计每位同学所借图书数量。
1)createprocedurepr_select
as
select学号,姓名,count(*)as借阅数量fromstudentjoin借阅表onstudent.学号=借阅表.学号
groupby学号,姓名
go
2)createprocedurepr_select
as
select学号,姓名,count(*)as借阅数量fromstudent,借阅表wherestudent.学号=借阅表.学号
groupby学号,姓名
go
3.创建带参数存储过程pr_name,功能:
按给定字符串信息,查询包含此字符串的图书信息。
createprocedurepr_name@chrvarchar
(10)
as
select*from图书信息表where名称like'%'+@chr+'%'
go
4.创建带参数存储过程pr_xingming,功能:
按给定姓名查询指定同学所借图书信息(包括学号,姓名,图书编号,名称及借阅时间)
1)createprocedurepr_xingming@name
varchar(6)
as
selectstudent.学号,姓名,借阅表.图书编号,图书名称,借阅时间fromstudentjoin借阅表
onstudent.学号=借阅表.学号join图书信息表on图书信息表.图书编号=借阅表.图书编号
where姓名=@name
go
2)createprocedurepr_xingming@namevarchar(6)
as
selectstudent.学号,姓名,借阅表.图书编号,图书名称,借阅时间fromstudent,借阅表,图书信息表
wherestudent.学号=借阅表.学号and图书信息表.图书编号=借阅表.图书编号and姓名=@name
go
5.删除以上的存储过程。
dropprocedurepr_select,pr_xingming
go
6.执行以上存储过程。
executepr_sum30
go
十一、创建触发器
1、创建一个触发器,如果向student表中插入数据时,显示“数据已成功录入”
createtriggertr_cgonstudentforinsert
as
print‘数据已成功录入’
go
2、创建一个触发器,功能:
修改单价的值不允许超出其+-20%
createtriggertr_opon图书信息表forupdate
as
declare@dj1decimal(5,2),@dj2decimal
(5,2)
select@dj1=单价
select@dj2=单价frominserted
if(@dj2>@dj1*1.2or@dj2<@dj1*0.8)
begin
print‘数据输入有误’
rollback
end
else
print‘数据正确’
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库