最新国家开放大学电大《MySQL数据库应用》网络核心课实验训练2及4答案

1、国家开放大学试题最新国家开放大学电大MySQL数据库应用网络核心课实验训练2及4答案盗传必究本课程分为形成性考核和终结性末考两种考核形式,形成性考核占50% (满分100分),末考为网络考试占 50% (满分100),其中形成性考核为完成课程网站上的形考作业任务,形考作业均为主观题,包括四个实 训:实训一:在MYSQL创建数据库和表,25分;实训二:数据查询操作,25分;实训三:数据增删改操 作,25分;实训四:数据库系统维护,25分。实验训练2:数据查询操作答案:实验目的:基于实验1创建的汽车用品网上商城数据库Shopping ,理解MySQL运算符、函数、谓词,练习Select 语句的操作

2、方法。实验内容:1. 单表查询【实验2.1】字段查询(1 )查询商品名称为 "挡风玻璃的商品信息。分析:商品信息存在于商品表,而且商品表中包含商品名称此被查询信息,因此这是只需要涉及一个表就 可以完成简单单表查询。(2 )查询ID为1的订单。分析:所有的订单信息存在于订单表中,而且订单用户ID也存在于此表中,因此这是只需要查询订单表 就可以完成的查询。【实验2.2】多条件查询查询所有促销的价格小于1000的商品信息。分析:此查询过程包含两个条件,第一个是是否促销,第二个是价格,在商品表中均有此信息,因此这是 一个多重条件的查询。【实验2.3】DISTINCT(1 )查询所有对商品ID

3、为1的商品发表过评论的用户ID o分析:条件和查询对象存在于评论表中,对此商品发表过评论的用户不止一个,而且一个用户可以对此商 品发表多个评论,因此,结果需要进行去重,这里使用DISTINCT实现。(2 )查询此汽车用品网上商城会员的创建时间段,1年为一段。分析:通过用户表可以完成查询,每年可能包含多个会员,如果把此表中的创建年份都列出来会有重复, 因此使用DISTINCT去重。【实验2.4】ORDER BY(1 )查询类别ID为1的所有商品,结果按照商品ID降序排列。分析:从商品表中可以查询出所有类别ID为1的商品信息,结果按照商品ID的降序排列,因此使用 ORDER BY语句,降序使用DE

4、SC关键字。(2 )查询今年新增的所有会员,结果按照用户名字排序。分析:在用户表中可以完成查询,创建日期条件设置为今年,此处使用语句ORDER BY o【实验2.5】GROUP BY(1 )查询每个用户的消费总金额(所有订单)。分析:订单表中包含每个订单的订单总价和用户ID。现在需要将每个用户的所有订单提取出来分为一类, 通过SUMO函数取得总金额。此处使用GROUP BY语句和SUM()函数。(2 )查询类别价格一样的各种商品数量总和。分析:此查询中需要对商品进行分类,分类依据是同类别和价格,这是 "多列分组,较上一个例子 更为复杂。2. 聚合函数查询【实验 2.6 】 COUNT

5、 ()(1 )查询类别的数量。分析:此查询利用COUNT()函数,返回指定列中值的数目,此处指定列是类别表中的ID (或者名称均 可)。(2 )查询汽车用品网上商城的每天的接单数。分析:订单相关,此处使用聚合函数COUNT()和Group by子句。【实验2.7 】 SUMO查询该商城每天的销售额。分析:在订单表中,有一列是订单总价,将所有订单的订单总价求和,按照下单日期分组,使用SUMO函 数和Group by子句。【实验2.8】AVGO(1 )查询所有订单的平均销售金额。分析:同上一个相同,还是在订单表中,依然取用订单总价列,使用AVGO函数,对指定列的值求平均 数。【实验2.9】MAX。

6、(1 )查询所有商品中的数量最大者。分析:商品的数量信息存在于商品表中,此处查询应该去商品表,在商品数量指定列中求值最大者。使用 MAXO函数。(2 )查询所有用户按字母排序中名字最靠前者。分析:MAX()或者MIN()也可以用在文本列,以获得按字母顺序排列的最高或者最低者。同上一个实验 一样,使用MAXO函数。【实验2.10 】 MINO(1 )查询所有商品中价格最低者。分析:同MAX ()用法相同,找到表和列,使用MINO函数。3. 连接查询【实验2.11 】内连接查询(1) 查询所有订单的发出者名字。分析:此处订单的信息需要从订单表中得到,订单表中主键是订单号,外键是用户ID ,同时查询

7、需要得 到订单发出者的姓名,也就是用户名,因此需要将订单表和用户表通过用户ID进行连接。使用内连接的 (INNER) JOIN 语句。(2 )查询每个用户购物车中的商品名称。分析:购物车中的信息可以从购物车表中得到,购物车表中有用户ID和商品ID两项,通过这两项可以 与商品表连接,从而可以获得商品名称。与上一个实验相似,此查询使用(INNER) JOIN语句。【实验2. 12】外连接查询(1 )查询列出所有用户ID ,以及他们的评论,如果有的话。分析:此查询首先需列出所有用户ID ,如果参与过评论的话,再列出相关的评论。此处使用外查询中的 LEFT (OUTER) JOIN语句,注意需将全部显

8、示的列名写在JOIN语句左边。(2 )查询列出所有用户ID ,以及他们的评论,如果有的话。分析:依然是上一个实验,还可以使用RIGHT (OUTER) JOIN语句,注意需将全部显示的列名写在JOIN语 句右边。【实验2. 13】复合条件连接查询(1 )查询用户ID为1的客户的订单信息和客户名。分析:复合条件连接查询是在连接查询的过程中,通过添加过滤条件,限制查询的结果,使查询的结果更 加准确。此查询需在内查询的基础上加上另一个条件,用户iD为1 ,使用AND语句添加精确条件。(2 )查询每个用户的购物车中的商品价格,并旦按照价格顺序排列。分析:此查询需要先使用内连接对商品表和购物车表进行连接

9、,得到商品的价格,在使用ORDER BY语句 对价格进行顺序排列。4. 嵌套查询【实验2.14 】 IN(1 )查询订购商品ID为1的订单ID ,并根据订单ID查询发出此订单的用户ID o分析:此查询需要使用IN关键字进行子查询,子查询是通过SELECT语句在订单明细表中先确定此订单 ID ,在通过SELECT在订单表中查询到用户ID。(2 )查询订购商品ID为1的订单ID ,并根据订单ID查询未发出此订单的用户ID o分析:此查询和前一个实验相似,只是需使用NOT IN语句。【实验2. 15】比较运算符(1 )查询今年新增会员的订单,并且列出所有订单总价小于100的订单ID o分析:此查询需

10、要使用嵌套,子查询需先查询用户表得到今年创建的用户信息,在将用户ID匹配找打订 单信息,其中使用比较运算符提供订单总价小于100的条件。(2 )查询所有订单商品数量总和小于100的商品ID ,并将不在此商品所在类别的其他类别的ID列 出来。分析:此查询需要进行嵌套查询,子查询过程需要使用到SUM()函数和GROUP BY求出同种商品的所有 被订数量,使用比较运算符得到数量总和小于100的商品ID ,再使用比较运算符 "不等于得到 非此商品所在类的类别ID o【实验 2. 16 】 EXISTS(1 )查询表中是否存在用户ID为100的用户,如果存在,列出此用户的信息。分析:EXIST

11、S关键字后面的参数是一个任意的子查询,系统对于查询进行运算以判断它是否返回行,如 果至少返回一行,那以EXISTS的结果为TRUE,此时外层查询语句将进行查询。此查询需要对用户ID进 行EXIST操作。(2 )查询表中是否存在类别ID为100的商品类别,如果存在,列出此类别中商品价格小于5的商 品ID。分析:与上一个实验相似,此实验在外查询过程添加了比较运算符。【实验2.17 】 ANY查询所有商品表中价格比订单表中商品ID对应的价格大的商品ID o分析:ANY关键字在一个比较操作符的后面,表示若与子查询返回的任何值比较为TRUE ,则返回TRUE。 此处使用ANY来引出内查询。【实验2.18

12、 】 ALL查询所有商品表中价格比订单表中所有商品ID对应的价格大的商品ID o分析:使用ALL时需要同时满足所有内层查询的条件。ALL关键字在一个比较操作符的后面,表示与子 查询返回的所有值比较为TRUE ,则返回TRUE。此处使用ALL来引出内查询。【实验2. 19】集合查询(1 )查询所有价格小于5的商品,查询类别ID为1和2的所有商品,使用UNION连接查询结果。分析:由前所述,UNION将多个SELECT语句的结果组合成一个结果集合,第1条SELECT语句查询价 格小于5的商品,第2条SELECT语句查询类别ID为1和2的商品,使用UNION将两条SELECT语 句分隔开,执行完毕之

13、后把输出结果组合为单个的结果集,并删除重复的记录。(2 )查询所有价格小于5的商品,查询类别ID为1和2的所有商品,使用UNION ALL连接查询 结果。分析:使用UNION ALL包含重复的行,在前而的例子中,分开查询时,两个返回结果中有相同的记录, 使用UNION会自动去除重复行。UNION ALL从查询结果集中自动要返回所有匹配行,而不进行删除。实验要求:1 .所有操作必须通过MySQL Workbench完成;2每执行一种查询语句后,均要求通过MySQL Workbench查看执行结果;3将操作过程以屏幕抓图的方式拷贝,形成实验文档。实验训练4:数据库系统维护答案:1.1 实验目的通过

14、创建数据库、并进行相应的维护,了解并掌握Mysql数据库的创建和维护的不同方法和途径,并通 过这一具体的数据库理解实际数据库所包含的各要素。1.2 实验内容1 .数据库创建与维护1创建数据库表创建学生选课数据库中所包含的数据库表,数据库表结构如下:学生学号,姓名,性别,年龄,系别,班级课程课程编号,课程名称,学分选修学号,课程编号,学生成绩要求为各数据库表属性选择合适的数据类型。列名、数据类型长度列、精度、小数位数项、是否允许空值等。2查看和修改表结构。选择一个数据库表,练习对其表结构进行相应的修改。3练习用交互式的SQL语句分别完成以上操作。4对学生数据库中的三张表分别插入数据;5对表中的数

15、据进行修改;6对表中的数据进行删除操作2 . 1 建立如下视图:学生选修课程信息视图,包括以下内容:学生学号、姓名、所在系、选修的课程编号、课程名称、成绩2 修改以上视图,增加学生所在班级信息。3 对以上视图删除。1.3 实验要求1 .数据库创建与维护实验则要求而向具体应用领域,利用SQL Server相关机制,创建并维护数据库系统,为后续各个实验提供前期准备2 .要求学生根据以上要求确定实验步骤,独立完成以上实验内容。并在安装和数据库运行后熟悉SQLSERVER的各种运行管理。3.实验完成后完成实验报告国家开放大学试题Windows8 pc 机Mysql配置的环境Mysql命令行工具Work

16、bench可视化 工具1.5 实验步骤及结果分析1. 5. 1 student数据库创建打开命令行工具,输入密码,用use语句使用相应数据。U se student使用老师的给的数据库。在 workbench中我们可以看到创建数据库的代码:卜而是输入的数据I CREATE TABLE wstudent' (% sno' varchar(6) NOT NULL,'5name' varchar(3) DEFAULT NULL* sex' va* : "( ) DEFAULT NULL j %bdate* datetime DEFAULT NULL.

17、e dept jrchar( ) DEFAULT NULL.* classno* varchar( ) DEFAULT NULL, PRIMARY KEY ('no') ENGINE=InnoDB DEFAULT CHARSET=utf8;Vi ERI 1邓。salient, ¥妃上世CM ,嘻 了",n 项,",(仙防叩邮i/;拙、电此据:,此处我们会发现乱码问题,老师给的代码中写的是Latin,我们改成utf -8 ,便解决了中文乱码问题。创建数据库语句,create table +'数据库名' ('属性名 '

18、属性类型约束1. 5. 2数据库操作-一属性查询查询表结构:即各个属性和属性的完整性约束等。(desc student )mysql desc.,A .student;Field;+AType;Null ; Key ; Default ; Extra ;一 4 +snosnamei sex;bdate;depti classno;uarchar<6>uarchar<6>uarchar<2>datetime;uarchar<8>i uarchar<3>NO ;VES ;YES ;YES ;YES :YES;PRI ;NULL;1,11N

19、ULL;1,11NULL;1,11NULL;1,11NULL;1,11NULL;b rows in set <0.00 sec)该语句给我们展示了我们创建的数据的所有(属性名+类型+默认初始情况+约束等)信息。1. 5. 3 数据库操作-数据结构修改在老师给的默认数据库中,我发现最后一个属性classno的属性为varchar ( 3 );但是数据库数据 中classno本来是4位数,所以导致存储时与原数据不匹配。因此,我们修改一下该属性。即:将它改 为4位长度的:alter table student' modify classno' varchar(4)DEFAULT

20、 NULL;国家开放大学试题nysql> alter table student modify classno uarchar<4> default null; Query OK. 0 rows affected <0.12 sec>Records: 0 Duplicates: 0 Warnings: 0我们在数据插入操作中进行检测。1. 5.4数据库操作一数据插入例如,我们加入一个学生信息,以自己为例:insert into ' student' values '33333','茹兴康','男,'1

21、997/7/7 8:00','计算机',314 6 'mysql> insert into "student" ualitesC'33333'茹兴康男'1997/7/7,计算机 >3146,;Query OK. 1 row affected <0.09 sec>插入成功,因为插入的classno为3146是4位的,所以说明数据库结构修改成功。我们查询一下:nysql> select * from student where sno=33333;+ sno sname+ sex,1 bdat

22、ef;dept4+;classno ;33333 ;茹兴康4;男;1997-07-07 08:00:00*;计算机:3146;士1 row in set <0.00 sec>插入成功,数据库结构修改成功。1. 5. 5数据库操作-一数据查询例如:我们查询学号为30201的学生的所有信息。采用语句结构:select+查询的属性from+要查找的表where +约束条件:S elect * from student where sno=30201;1.5.6 数据库操作-一数据修改我们按学号为30201 ,即第一个数据为例,修改他的名字,Update student set sname

23、 = ' abc" where sno=30201;国家开放大学试题riysql> update student set snane=/ abc vine re sno=30201 ; buery 0K> 1 row affected <0.09 sec>hows matched: 1 Changed: 1 Warnings: 0riysql> select * from student where sno=30201 ;sno; snane ; sex ; bdate; dept ;classno;/4 4 4- 4 -4;30201: abc

24、 ;男 ;1980-01-02 00:00:00 ;电信 ;302;1.5.7 数据库操作-一数据删除我们按学号为30201 ,即第一个数据为例:delete from 'student' where sno=,30201nysql> delete f ron "student% v/here sno = 30201' Query OK. 1 row affected <0.10 sec>nysql> Select * From student where sno=30201; Empty set <0.00 sec>因为我

25、们已经删除了该数据,所以查询为空。1.5.8 视图操作视图创建视图,用来更方便的展示某些信息,或者为了更安全的选择展示或隐藏某些信息。更方我们将student表,sc表,course表连接起来创建新的视图,从而让学生信息更直观, 便。创建视图所需语句:create view +视图名+as +查询语句。create view viewl asselect sno,sname, dept, eno,cname, grade, classnofrom student natural join sc natural join course;pysql> use student【Database

26、 changedpysql> create uiew uiev/1 as-> select snosnanedept,ane,.grade.classno-> from student natural join sc natural join course;Muery OK. 0 rows affected <0.06 sec我们在下面的视图查询 操作中检测试图是否创建成功。1.5.9 视图操作-一视图查询由于在上述实验中我们将第一个学生的数据删除掉了,所以我们选择查询后面的同学,查询视图操作代码和查询表格是一样的。代码:select * from view

27、l where sno=30203;其中viewl是我们创建的视图名称;由于该同学选择了三门不同的课程,因此在进行表连接的时候,增加了该同学的元组数量,最终查询出3 条结果。1. 5. 10视图操作一-视图结构修改在视图创建中,我们创建了视图的classno班级信息,现在我们删除这一属性°使用语句: create or replace view。C reate or replace view viewl asselect sno, sname, dept, eno, cname, gradefrom student natural join sc natural join course;然后我们在查询一下viewl的结构,同table操作一样,用desc语句。结果如下:mysql> Create or replace uiew uiewl as> select snosnaneFdept>eno,cnane>grade> from student natural join sc natural join course; Query OK