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

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为1的商品发表过评论的用户

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

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

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

6、品的数量信息存在于商品表中，此处查询应该去商品表，在商品数量指定列中求值最大者。使用 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语句，注意需将全部显示的列名写在JOIN语句左边。(2 )查询列出所有

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

9、格进行顺序排列。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的用户，如果存在，列出此用户的信息。分析：EXISTS关键字后面的参数是一个任意的子查询，系统对于查询进行运算以判断它是

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

12、 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语 句分隔开，执行完毕之后把输出结果组合为单个的结果集，并删除重复的记录。(2 )查询所有价格小于5

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

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

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

16、结果分析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.e dept jrchar( ) DEFAULT NULL.* classno* varchar( )

17、 DEFAULT NULL, PRIMARY KEY (no) ENGINE=InnoDB DEFAULT CHARSET=utf8;Vi ERI 1邓。salient, 妃上世CM ,嘻 了，n 项,，(仙防叩邮i/;拙、电此据:，此处我们会发现乱码问题，老师给的代码中写的是Latin，我们改成utf -8 ，便解决了中文乱码问题。创建数据库语句，create table +数据库名 (属性名 属性类型约束1. 5. 2数据库操作-一属性查询查询表结构：即各个属性和属性的完整性约束等。(desc student )mysql desc.,A .student；Field；+AType；Nul

18、l ； Key ； Default ； Extra ；一 4 +snosnamei sex；bdate；depti classno；uarchar；uarchar；uarchar；datetime；uarchari uarchar；NO ；VES ；YES ；YES ；YES :YES；PRI ；NULL；1,11NULL；1,11NULL；1,11NULL；1,11NULL；1,11NULL；b rows in set alter table student modify classno uarchar default null； Query OK. 0 rows affected Reco

19、rds: 0 Duplicates: 0 Warnings: 0我们在数据插入操作中进行检测。1. 5.4数据库操作一数据插入例如，我们加入一个学生信息，以自己为例：insert into student values （33333,茹兴康，男，1997/7/7 8:00,计算机，314 6 ）;mysql insert into student ualitesC33333茹兴康男1997/7/7,计算机 3146，；Query OK. 1 row affected 插入成功,因为插入的classno为3146是4位的，所以说明数据库结构修改成功。我们查询一下：nysql select * f

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

21、据为例，修改他的名字,Update student set sname = abc where sno=30201;riysql update student set snane=/ abc vine re sno=30201 ； buery 0K 1 row affected 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 ；男 ；1980-01-

22、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 nysql Select * From student where sno=30201； Empty set 因为我们已经删除了该数据，所以查询为空。1.5.8 视图操作视图创建视图，用来更方便的展示某些信息，或者为了更安全的选择展示或隐藏某些信息。更方我们将s

23、tudent表，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 studentDatabase changedpysql create uiew uiev/1 as- select snosnanedept,ane,.grade.classno- f

24、rom student natural join sc natural join course；Muery OK. 0 rows affected Create or replace uiew uiewl as select snosnaneFdepteno,cnanegrade from student natural join sc natural join course； Query OK. 0 rows affected piFsql desc uiewl ；Fieldii JUType-4-11Null；Key A11-Defaulti Extra sno11uarchar11NO1

25、111NULL01snane11uarchar11YES1111NULL11dept11uarchar11YES1111NULL11eno11uarchar01NO1111NULL11cnane11uarchar01YES1111NULL11grade1 I01YES1111NULL11_!板 rows in set 显然，viewl视图结构中没有了 classno属性。1.5.11 视图操作-一视图删除最后，我们删除视图viewl ,采用语句drop view ； 语句: drop view if exists viewl;结果：hysql drop uiev/ if exists uiew

26、l ；buery OK. 0 rows affected pysql desc uiewl；ERROR 1146 : Table 9 student-uiewl1 doesnt exist 删除成功，然后我们在查询视图viewl时产生错误，验证了 viewl的删除。1.6实验总结我们进行了对数据库表的基本操作；对视图的基木操作。让我们将数据库的理论知识和具体操作很好的 结合。虽然，我们刚开始进行的实验操作都比较基础，适用语句都比较简单，但是我充分感受到了数据库 的美妙，同时也大大增加了我对命令行操作的兴趣。在对数据库表和视图操作中，我经历了书上查阅，网上mysql命令行使用教程查阅，网上mysql命 令语句查询，最终完成了此次实验，让我对数据库的基础语句使用以及常用语句使用有了更大的接 触。同时，实验也让我对视图的作用最直接的了解，让我对理论课的学习有了更明确的目标性。