数据库系统simple-query.pdf

《数据库系统simple-query.pdf》由会员分享，可在线阅读，更多相关《数据库系统simple-query.pdf（16页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、实验（第五周） 单表查询：基于“感动 中国年度人物” 数据库 1. 实验目的 (1) 掌握基本SQL查询语句格式。 (2) 掌握单表查询方法。 (3) 掌握聚集函数查询方法 2. 实验环境（写清硬件配置和软件版本） (1) 硬件： 笔记本 Intel Core i5-8250U CPU 8.00 GB Memory Intel UHD Graphics (2)操作系统：Microsoft Windows 10 (3)数据库管理系统：pgAdmin 4/PostgreSQL 11 3. 实验内容 (1) 使用SQL基本查询语句进行单表查询，主要有：查询指定属性、查询指定元组、查询空值、 多条件的

2、查询、分组查询、对查询结果去重、对查询结果排序等。 (2) 使用聚集函数对数据表进行查询，主要有：COUNT()、SUM()、AVG()、MAX()、MIN()。 属性描述类型长度 aid获奖编号varchar20 atitle获奖称号varchar20 aname获奖人姓名varchar20 asex获奖人性别varchar4 aage获奖时的年龄smallint ayear获奖年份smallint description颁奖辞text 4. 实验数据 本次实验使用“感动中国年度人物”数据库，主要涉及人物获奖表award，包括获奖编号（aid）, 获奖称号（atite），获奖人姓名（anam

3、e），获奖人性别（asex），获奖时的年龄（aage）， 获奖年份（ayear），颁奖辞（description）。 5. 实验作业 (1) 登录PostgreSQL，用 SQL语句创建数据表award。 表award结构 执行结果如下： aidatitleanameasexaageayeardescription 2018001 临危 岂顾 生 杜富国男272018 你退后/让我来/六个字 铁骨铮铮/以血肉挡住危 险/哪怕自己坠入深渊/ 无法还给妈妈一个拥抱/ 无法再见妻子明媚的笑 脸/战友们拉着手趟过雷 场/你听/那嘹亮的军歌/ 是对英雄的礼赞 2018002 见义 勇必 为 吕保民男49

4、2018NULL (2) 向award表中插入如下数据。 表award 中记录 - 创建 award 表 create table award ( aid varchar(20) not null, atitle varchar(20), aname varchar(20), asex varchar(4), aage smallint, ayear smallint, description text, primary key(aid) ); aidatitleanameasexaageayeardescription 2018003 孤云 心浩 然 王仕花女562018 浪的执着/礁的顽强

5、/民 的本分/兵的责任/岛再 小也是国土/家未立也要 国先安/三十二年驻守/ 三代人无言付出/两百面 旗帜收藏了太多风雨/涛 拍孤岛岸/风颂赤子心 2017001 天意 怜幽 草， 人间 重晚 晴 卢永根男872017 种得桃李满天下，心唯 大我育青禾。是春风， 是春蚕，更化作护花的 春泥。热爱祖国，你要 把自己燃烧。稻谷有 根，深扎在泥土，你也 有根 扎根在人们心里。 2017002 际会 中兴 日， 四海 两心 知 卢丽安女492017 台湾的女儿有大气概， 祖国为大乡愁不改，把 握现在开创未来。分离 再久，改不了我们的血 脉。海峡再深，挡不住 人民追求福祉的路。 2017003 芳兰 生

6、贵 里， 山河 澄正 气 廖俊波男492017 人民的樵夫，不忘初 心。上山寻路，扎实工 作，廉洁奉公，牢记党 的话，温暖群众的心。 春茶记住你的目光，青 山留下你的足迹，谁把 人民扛在肩上，人民就 把谁装进心里。 aidatitleanameasexaageayeardescription 2016001 星斗 焕文 章 孙家栋男872016 少年勤学，青年担纲， 你是国家的栋梁。导 弹、卫星、嫦娥、北 斗，满天星斗璀璨，写 下你的传奇。年过古稀 未伏枥，犹向苍穹寄深 情。 2016002 芳兰 振蕙 叶 支月英女552016 你跋涉了许多路，总是 围绕大山。吃了很多 苦，但给孩子们的都是

7、甜。坚守才有希望，这 是你的信念。三十六 年，绚烂了两代人的童 年，花白了你的麻花 辫。 2016003 君子 通大 道 秦玥飞男312016NULL 执行结果如下： (3) 使用SQL语句查询award表中所有行的atitle、aname和ayear。 - 向 award 表插入值 insert into award values (2018001, 临危岂顾生, 杜富国, 男, 27 , 2018 , 你退后/让我来/六个字铁骨铮铮/以血肉挡住危险/哪 怕自己坠入深渊/无法还给妈妈一个拥抱/无法再见妻子明媚的笑脸/战友们拉着手趟过雷场/你听/那嘹亮的军歌/是对英雄的 礼赞), (20180

8、02, 见义勇必为, 吕保民, 男, 49 , 2018 , NULL), (2018003, 孤云心浩然, 王仕花, 女, 56 , 2018 , 浪的执着/礁的顽强/民的本分/兵的责任/岛再小也是 国土/家未立也要国先安/三十二年驻守/三代人无言付出/两百面旗帜收藏了太多风雨/涛拍孤岛岸/风颂赤子心), (2017001, 天意怜幽草，人间重晚晴, 卢永根, 男, 87 , 2017 , 种得桃李满天下，心唯大我育青禾。是 春风，是春蚕，更化作护花的春泥。热爱祖国，你要把自己燃烧。稻谷有根，深扎在泥土，你也有根 扎根在人们心里。), (2017002, 际会中兴日，四海两心知, 卢丽安,

9、女, 49 , 2017 , 台湾的女儿有大气概，祖国为大乡愁不 改，把握现在开创未来。分离再久，改不了我们的血脉。海峡再深，挡不住人民追求福祉的路。), (2017003, 芳兰生贵里，山河澄正气, 廖俊波, 男, 49 , 2017 , 人民的樵夫，不忘初心。上山寻路，扎 实工作，廉洁奉公，牢记党的话，温暖群众的心。春茶记住你的目光，青山留下你的足迹，谁把人民扛在肩上，人民就把谁 装进心里。), (2016001, 星斗焕文章, 孙家栋, 男, 87 , 2016 , 少年勤学，青年担纲，你是国家的栋梁。导弹、卫 星、嫦娥、北斗，满天星斗璀璨，写下你的传奇。年过古稀未伏枥，犹向苍穹寄深情。

10、), (2016002, 芳兰振蕙叶, 支月英, 女, 55 , 2016 , 你跋涉了许多路，总是围绕大山。吃了很多苦，但给 孩子们的都是甜。坚守才有希望，这是你的信念。三十六年，绚烂了两代人的童年，花白了你的麻花辫。), (2016003, 君子通大道, 秦玥飞, 男, 31 , 2016 , NULL); (4) 使用SQL语句查询award表中获奖年份为2018年的所有获奖人姓名和性别。 - 查询award表中所有行的atitle、aname和ayear select atitle, aname, ayear from award; (5) 使用SQL语句查询award表中aid为20

11、18001或2017001的获奖人姓名和年龄。(两种) 方法一 方法二 - 查询award表中所有行的atitle、aname和ayear select aname, asex from award where ayear = 2018; - 查询award表中获奖年份为2018年的所有获奖人姓名和性别，使用 IN select aname, aage from award where aid in (2018001, 2017001); (6) 使用SQL语句查询award表中获奖年龄在30岁到50岁之间的获奖人姓名、性别、年龄，并按 年龄升序排序。（两种） 方法一 方法二 - 查询awar

12、d表中获奖年份为2018年的所有获奖人姓名和性别，使用逻辑表达式 select aname, aage from award where aid = 2018001 or aid = 2017001; - 查询award表中获奖年龄在30岁到50岁之间的获奖人姓名、性别、年龄，并按年龄升序排序 - 使用 BETWEEN.AND. select aname, asex, aage from award where aage between 30 and 50 order by aage asc; (7) 使用SQL语句查询award表中编号以2016开头并且description为空的行。 (8

13、) 使用SQL语句查询award表中ayear属性的值，并返回不重复的ayear属性值。 - 查询award表中获奖年龄在30岁到50岁之间的获奖人姓名、性别、年龄，并按年龄升序排序 - 使用逻辑表达式 select aname, asex, aage from award where aage = 30 and aage 50; (13) 使用MAX()函数统计award表中各个年份获奖年龄的最大值并把最大值命名为max_age。 (14) 查询从孙家栋获奖的时间到现在过去了几年。 - 统计award表中不同年份，获奖年龄在50岁以上的获奖人的平均年龄 - 使用 AVG() select a

14、year, avg(aage) from award where aage 50 group by ayear; - 统计award表中各个年份获奖年龄的最大值并把最大值命名为max_age - 使用 MAX() select ayear, max(aage) as max_age from award group by ayear; 6. 问题与思考 (1) 在where子句中必须使用圆括号吗？ 使用逻辑运算符在 WHERE 子句中连接多个条件。可以使用圆括号将条件括起来，虽然这些 条件不一定是必须的，但是括起来更清晰； 如果 WHERE 后存在嵌套查询，嵌套查询必须被圆括号括起来。 本实验

15、中的 WHERE 关键字后多个条件并没有使用圆括号； 在第 (5) 问中，where 关键字后 in 关键字后使用了圆括号。 (2) having关键字和where关键字功能上有什么区别？ 1. 两关键字的定义： WHERE：用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果 集之前起作用。 HAVING：用于对 WHERE 和 GROUP BY 查询出来的分组经行过滤，查出满足条件的 分组结果。 - 查询从孙家栋获奖的时间到现在过去了几年 - 使用 EXTRACT, CURRENT_DATE select extract (year from current_date) -

16、( select ayear from award where aname = 孙家栋 ); 2. 两关键字执行顺序： 1. 执行 WHERE 子句查找符合条件的数据； 2. 使用 GROUP BY 子句对数据进行分组；或者对 GROUP BY 子句形成的组运行聚集函 数计算每一组的值； 3. 最后用 HAVING 子句去掉不符合条件的组。 3. 在第 (11) 问中，要求只显示平均年龄在50岁以上的分组 ，使用 GROUP BY 后，通过 HAVING 完成实验要求； 在第 (12) 问中，首先使用 WHERE 筛选出获奖年龄在 50岁以上的获奖人，之后使用 GROUP BY ，再通过 HA

17、VING 完成实验要求； 7. 实验体会 1. 通过“感动中国年度任务数据库”，掌握了基本的 SQL 查询语句格式、单表查询方法和聚集 函数查询方法； 2. SQL 的基本查询语句包括：查询指定属性、查询指定元组、查询空值、多条件的查询、分 组查询、对查询结果去重、对查询结果排序；聚集函数包括：COUNT()、SUM()、AVG()、 MAX()、MIN()； 3. 对于 WHERE 后的条件子句，其中的 OR 字句并不满足 C 语言中的条件短路效果，其中子 条件的执行顺序不确实； 4. WHERE关键字：用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结 果集之前起作用；HAVING关键字：用于对 WHERE 和 GROUP BY 查询出来的分组经行过 滤，查出满足条件的分组结果；使用 GROUP BY 子句对数据进行分组；或者对 GROUP BY 子句形成的组运行聚集函数计算每一组的值。