课堂实训
【实训目的】
1.熟悉并掌握用T-SQL语句对数据表进行单表查询、多表查询
2.掌握用查询结果对表数据进行修改
【实训内容】
(一)插入记录
1.在SSMS环境中,为书籍表插入10条记录(数据任意)。
2.用T-SQL语句为读者表、借阅表各插入10条记录。
(二) 单表查询
1.查询每本图书的所有信息。
select * from 书籍表
2.查看所有读者的全部信息。
select * from ________________
3.查询每本图书的图书编号、书名、作者、价格及出版社。
Select图书编号,书名,作者,价格,出版社 from______________________
4.查询每个读者的借书证号、姓名和单位。
select___________________ from 读者表
5.列出图书馆中所有藏书的书名及出版单位。
Select distinct 书名,出版社 From 书籍表 --过滤重复项
6.查询书名为“英语”的图书信息。
Select* from 书籍表 where书名=________
7.查询图书编号为“10006”的书名和作者。
select_____________________ from 书籍表 where图书编号_______________
8.查询每本图书总数量在5-10本之间的图书的图书编号和书名。
SELECT________________ from书籍表 where总数量>=5and 总数量<=10
9.查找价格介于10和20元之间的图书种类(去掉重复),结果按出版单位和单价升序排列。
Select______ 书名,作者,价格,出版社From书籍表 Where价格 between10 and 20 Order by 出版社,价格asc
10.查找藏书中,高等教育出版社和科学出版社的图书种类及作者。
selectdistinct 书名,作者,出版社 from书籍表 where出版社 in(‘高等教育出版社’,_________) -- In (相当于=…or=…)
11.查询计算机系或电子系的读者信息。
Select * from 读者表 where单位 in ('计算机系',____________)
12.查询姓张的读者信息。
Select * from 读者表 where姓名 like ‘张%’ -- like及通配符% _ *
13.找出姓李的读者姓名及其所在单位。
select 姓名,单位from 读者表 _____________________________
14.查找书名中有“基础”二字的图书和作者。
Select dist 书名,作者 From 书籍表 Where书名 like ‘%基础%’
15.查找书名以“计算机”打头的所有图书和作者。
Select distinct 书名,作者 From 书籍表_______________________
16.查询计算机系或电子系姓张的读者信息。
Select* from 读者表 where_______________ and _________________
17.查询尚未归还图书的借阅信息。
select* from 借阅表 where______________________
18.查询已归还图书的借阅信息。
select* from 借阅表 where_____________________
19.用英文字段名列出图书馆中科学出版社所有藏书的书名及出版单位。
select 书名 asBook,作者 as Author, 出版社as Publisher from 书籍表where 出版社=’科学出版社’ -- As:查询结果可以自定义列名
20.查找高等教育出版社的所有图书及单价,按单价降序排列。
select 书名,出版社,价格 from书籍表 where出版计=’高等教育出版社’
orderby 价格 desc -- Order by desc(降序)|asc(升序,默认)
21.统计每本书籍借阅的人数,要求输出图书编号和所借人数,查询结果按人数降序排列。
Select图书编号,COUNT(*) from 借阅表groupby _______ order byCOUNT(*) desc
22.查询在2013年以后出版的图书信息,并按时间进行排序。
Select* from 书籍表 whereyear(出版日期)_______________
(三)使用库函数查询
1.求图书馆所有藏书的册数。
Selectcount(*) as 藏书总册数 From ________
2.求读者总数。
selectCOUNT (*) from ________
3.求科学出版社图书的最高价、最低价、平均价。
Select出版社,max(价格)as 最高价,__________________from 书籍表 where出版社=’科学出版社’
4.统计男读者、女读者的人数。
Select性别 ,COUNT(*) from 读者表 groupby _____________
5.统计各类图书的平均定价以及总数量。
Select 种类,avg(价格) as 平均定价,SUM(库存量)as _________ from 书籍表 groupby _____________________
6.求各个出版社图书的最高价、最低价、平均价。
select 出版单位,max(价格)as 最高价,min(价格)as 最低价,avg(价格)as 平均价格
from __________________________
7.统计各个单位读者的数量,显示单位名和数量。
select_______,count(*) as数量 from_________group by _________
8.统计借书证号为“00701026”的读者借书的数量。
select_______from 借阅表where________='00701026'
9.列出已借出去的每本书的书号及借阅人数。
select _______,COUNT (*) FROM 阅借表 GROUPBY ________
(四) 多表查询
1.连接查询
(1)查找所有借阅了图书的读者姓名及所在单位。
selectdistinct 姓名,单位 from读者表 x,借阅表y where x.借书证号=y.借书证号
(2)查找所有借阅了图书的读者姓名及所在单位。
select distinct 姓名,单位 from 读者表 inner join 借阅表 on _ ___________________________
(3)找出李某所借的所有图书的书名及借书日期。
Select 姓名,书名,借书日期From书籍表,借阅表,读者表Where 读者表.借书证号=借阅表.借书证号and _____________________and 姓名_______________
(4)查找价格在22元以上已借出的图书,按单价升序排列。
Select * From 借阅表 r,书籍表 b Whereb.图书编号=r.图书编号and价格>=22 Order by ______________
(5)查询同时借阅了图书编号为112266和449901两本书的借书证号。
select x.借书证号,x.图书编号as first,y.图书编号 assecond from 借阅表 x, 借阅表y
wherex.借书证号=y.借书证号 and x.图书编号=’112266’ and y.图书编号=’449901’
(6)找出各个单位当前借阅图书的读者人次。
select 单位,count(读者表.借书证号)as 借书人次 from 借阅表,读者表
where 读者表.借书证号=借阅表.借书证号 group by ___________________
(7)分别找出借书人次超过1人的单位及人次数。
select 单位,count(*)as 超过1人次 from借阅表,读者表
where______________________ group by 单位havingcount(*)>=2
(8)查询每个读者的姓名和所借图书名。
select 姓名,书名from 读者表,借阅表,书籍表where读者表.借书证号=借阅表,借书证号and _____________________________
(9)查询借阅了“数据结构”的读者数量。
selectCOUNT(*) from 借阅表innerjoin 书籍表on_____________ where________________
(10)查询每个读者姓名,所借图书的图书编号,没有借书的读者也列出来。
Select 读者表.姓名,借阅表.图书编号from读者表 leftjoin 借阅表on________
2.嵌套查询
(1)查询2013年10月以后借书的读者借书证号、姓名和单位。
select 姓名,借书证号,单位from 读者表 where借书证号 in
(select 借书证号 from借阅表 where借书日期>=_________________
(2)找出与赵正义在同一天借书的读者的姓名、所在单位及借书日期。
select 姓名,单位,借书日期from 读者表,借阅表where借阅表.借书证号=读者表.借书证号and 借书日期 in (select 借书日期 from借阅表,读者表where借阅表.借书证号=读者表.借书证号and ___________________)
(3)查询2013年7月以后没有借书的读者的借书证号、姓名和单位。
select借书证号,姓名,单位from读者表 where借书证号 not in
(select 借书证号 from借阅表 where 借书日期>=___________________)
(4)找出当前至少借阅了2本图书的读者及所在单位。
Select 姓名,单位From 读者表
Where借书证号 in (select 借书证号 from借阅表groupby 借书证号 ____________)
(5) 查询借阅图书数量达到2本的读者信息。
select * from 读者表 where 借书证号 in(____________________________________)
(6) 查没有借书的读者的借书证号和姓名。
select 借书证号,姓名 from 读者表 where借书证号 not in(__________________)
(7)找出藏书中比高等教育出版社的所有图书单价更高的图书。
Select* From 书籍表Where价格>all (select 单价 from书籍表 where出版社=___________________)
(8)找出藏书中所有与“数据库导论”或“数据库基础”在同一出版单位出版的书。
Select dist 书名,价格,作者From 书籍表
Where 出版社=any (select 出版社 from 书籍表
where书名 in(______________________________))
(9) 查“李丽”和“张朝阳”都借阅了的图书的书号。
select a.图书编号 from 借阅表 as a,借阅表 as b where a.借书证号=(select借书证号 from读者表 where 姓名='李丽') and b.借书证号=(select 借书证号 from 读者表 where ______________) and a.图书编号=b.图书编号
(10)检索所有姓李的读者所借图书的书号
select______ from 借阅表 where_______in(select ______ from 读者表where____________________)
(11)查没有被借阅的图书信息
select* from 书籍表where图书编号 notin(select ___________ from借阅表)
(12)查询借阅了图书的读者信息
select* from 读者表where_______in(select ______ from 借阅表)
(五)利用查询结果更新表数据
1.创建读者表中的副本:读者表1。
select* into 读者表 from 读者表1
2.创建读者表中的副本,将读者表中的男生记录放到读者表1中,并按出生年月升序进行排序。
select* into 读者表1from 读者表 where性别_________order by 出生年月
3.创建书籍表中的副本,只有一个表结构,而无记录。
select* into 书籍副表 from书籍表 _____________________
4.创建书籍表中的副本:书籍表2,其字段为:图书编号,书名,价格,作者,出版社。
select____________into书籍表2 from书籍表
5.将“书籍表”中人民出版社出版的图书记录追加到“书籍副表”中。
insertinto 书籍副表 select*from书籍表where出版社='________'
6.将“书籍表”中电子工业出版社和机械工业出版社出版的图书记录追加到“书籍副表”中。
insertinto 书籍副表 __________________________________________
7.将书籍副表中人民版社出版的价格修改为与书号为’0011058’价格相同。
update书籍副表 set价格=(select格价 from书籍副表 where书号=’__________’)where 出版社=__________________________
8.将读者单位为计算机应用专业,借阅了书名为“高等数学”的记录删除。
delete借阅表 where图书编号 in
(select 图书编号from _____ where书名='________')and ______________
课外实训
一、查询
1.查询宿舍楼中所有学生的信息。
2.查询宿舍楼中姓李的学生的信息。
3.查询宿舍楼家住杭州的学生信息。
4.查询宿舍楼中年龄在18-20岁的学生的信息。
5.统计宿舍楼中男女生的人数。
6.查询计算机专业的男女生人数。
7.查询计算机专业及应用电子专业的男生信息。
8.查询计算机专业2班同学的信息。
9.查询2013年9月入住的学生住宿情况。
10.查询3号楼401宿舍的信息。
11.查询学号为’001012’同学的住宿情况。
12.统计各房间的住宿人数。
13.查询班主任王佳音老师所管理班级的学生住宿信息,要求显示楼号、房号、学生姓名。
14.查询3号楼401宿舍的住的是哪些学生,要求显示姓名、性别、专业、班级。
15.查询2班同学的住宿情况,要求显示楼号、房号、学生姓名。
16.查询3班女生的住宿情况,要要求显示楼号、房号、学生姓名。
17.查询与王芳同房间的舍友情况,要求显示姓名、性别、专业。
18.查询与学号为201101同学同一房间的舍友信息,要求显示姓名、性别、专业。
19.查询与李小明同一专业的学生信息。
20.查询与李小明同时入学的学生的信息。
二、删除、修改表数据
1.创建学生表中的副本:学生表1,要求只有表结构。
2.将学生表中的男生记录放到学生表1中,并按出生年月升序进行排序。
3.创建学生表中的副本,学生表2,其字段为:姓名,学号,性别,专业,班级。
4.将“学生表”中计算机应用专业的学生记录追加到“学生表2”中。
6.将“学生表”中电子商务专业和应用电子专业的记录追加到“学生表2”中。
7.将学生表2中与张三同一个专业的记录删除。