国开mysql数据库应用形考任务1-4_国开MySQL数据库应用形考任务1-4.docx

国开MySQL数据库应用形考任务1-4.docx

实验训练 1 在 MySQL 中创建数据库和表请到电脑端查看实验目的熟悉 MySQL 环境的使用，掌握在 MySQL 中创建数据库和表的方法，理解 MySQL 支持的数据类型、数据完整性在 MySQL 下的表现形式，练习 MySQL 数据库服务器的使用，练习CREATE TABLE， SHOW TABLES，DESCRIBE TABLE，ALTER TABLE，DROP TABLE 语句的操作方法。实验内容【实验 1-1】MySQL 的安装与配置。参见 4.1 节内容，完成 MySQL 数据库的安装与配置。【实验 1-2】创建“汽车用品网上商城系统”数据库。用 CREATE DATABASE 语句创建 Shopping 数据库，或者通过 MySQL Workbench 图形化工具创建 Shopping 数据库。【实验 1-3】在 Shopping 数据库下，参见 3.5 节，创建表 3-4表 3-11 的八个表。可以使用 CREATE TABLE 语句，也可以用 MySQL Workbench 创建表。【实验 1-4】使用 SHOW、DESCRIBE 语句查看表。【实验 1-5】使用 ALTER TABLE、RENAME TABLE 语句管理表。【实验 1-6】使用 DROP TABLE 语句删除表，也可以使用 MySQL Workbench 删除表。(注意删除前最好对已经创建的表进行复制。 )【实验 1-7】连接、断开 MySQL 服务器，启动、停止 MySQL 服务器。【实验 1-8】使用 SHOW DATABASE、USE DATABASE、DROP DATABASE 语句管理“网上商城系统” Shopping 数据库。实验要求1 配合第 1 章第 3 章的理论讲解，理解数据库系统。2 掌握 MySQL 工具的使用，通过 MySQL Workbench 图形化工具完成。3 每执行一种创建、删除或修改语句后，均要求通过 MySQL Workbench 查看执行结果。4 将操作过程以屏幕抓图的方式复制，形成实验文档。实验训练 2数据查询操作请到电脑端查看实验目的基于实验 1 创建的汽车用品网上商城数据库 Shopping，理解 MySQL 运算符、函数、谓词，练习 Select 语句的操作方法。实验内容1.单表查询【实验 2.1】字段查询(1 )查询商品名称为“挡风玻璃 ”的商品信息。分析商品信息存在于商品表，而且商品表中包含商品名称此被查询信息，因此这是只需要涉及一个表就可以完成简单单表查询。(2 )查询 ID 为 1 的订单。分析所有的订单信息存在于订单表中，而且订单用户 ID 也存在于此表中，因此这是只需要查询订单表就可以完成的查询。【实验 2.2】多条件查询查询所有促销的价格小于 1000 的商品信息。分析此查询过程包含两个条件，第一个是是否促销，第二个是价格，在商品表中均有此信息，因此这是一个多重条件的查询。【实验 2.3】DISTINCT(1 )查询所有对商品 ID 为 1 的商品发表过评论的用户 ID。分析条件和查询对象存在于评论表中，对此商品发表过评论的用户不止一个，而且一个用户可以对此商品发表多个评论，因此，结果需要进行去重，这里使用 DISTINCT 实现。(2 )查询此汽车用品网上商城会员的创建时间段，1 年为一段。分析通过用户表可以完成查询，每年可能包含多个会员，如果把此表中的创建年份都列出来会有重复，因此使用 DISTINCT 去重。【实验 2.4】ORDER BY(1 )查询类别 ID 为 1 的所有商品，结果按照商品 ID 降序排列。分析从商品表中可以查询出所有类别 ID 为 1 的商品信息，结果按照商品 ID 的降序排列，因此使用 ORDER BY 语句，降序使用 DESC 关键字。(2 )查询今年新增的所有会员，结果按照用户名字排序。分析在用户表中可以完成查询，创建日期条件设置为今年，此处使用语句 ORDER BY。【实验 2.5】GROUP BY(1 )查询每个用户的消费总金额(所有订单) 。分析订单表中包含每个订单的订单总价和用户 ID。现在需要将每个用户的所有订单提取出来分为一类，通过 SUM函数取得总金额。此处使用 GROUP BY 语句和 SUM函数。(2 )查询类别价格一样的各种商品数量总和。分析此查询中需要对商品进行分类，分类依据是同类别和价格，这是“多列分组” ，较上一个例子更为复杂。2.聚合函数查询【实验 2.6】COUNT(1 )查询类别的数量。分析此查询利用 COUNT函数，返回指定列中值的数目，此处指定列是类别表中的ID(或者名称均可) 。(2 )查询汽车用品网上商城的每天的接单数。分析订单相关，此处使用聚合函数 COUNT和 Group by 子句。【实验 2.7】 SUM查询该商城每天的销售额。分析在订单表中，有一列是订单总价，将所有订单的订单总价求和，按照下单日期分组，使用 SUM函数和 Group by 子句。【实验 2.8】AVG(1 )查询所有订单的平均销售金额。分析同上一个相同，还是在订单表中，依然取用订单总价列，使用 AVG函数，对指定列的值求平均数。【实验 2.9】MAX(1 )查询所有商品中的数量最大者。分析商品的数量信息存在于商品表中，此处查询应该去商品表，在商品数量指定列中求值最大者。使用 MAX函数。(2 )查询所有用户按字母排序中名字最靠前者。分析MAX 或者 MIN也可以用在文本列，以获得按字母顺序排列的最高或者最低者。同上一个实验一样，使用 MAX函数。【实验 2.10】MIN(1 )查询所有商品中价格最低者。分析同 MAX()用法相同，找到表和列，使用 MIN函数。3.连接查询【实验 2.11】内连接查询(1 )查询所有订单的发出者名字。分析此处订单的信息需要从订单表中得到，订单表中主键是订单号，外键是用户 ID，同时查询需要得到订单发出者的姓名，也就是用户名，因此需要将订单表和用户表通过用户ID 进行连接。使用内连接的 INNER JOIN 语句。(2 )查询每个用户购物车中的商品名称。分析购物车中的信息可以从购物车表中得到，购物车表中有用户 ID 和商品 ID 两项，通过这两项可以与商品表连接，从而可以获得商品名称。与上一个实验相似，此查询使用INNER JOIN 语句。【实验 2.12】外连接查询(1 )查询列出所有用户 ID，以及他们的评论，如果有的话。分析此查询首先需列出所有用户 ID，如果参与过评论的话，再列出相关的评论。此处使用外查询中的 LEFT OUTER JOIN 语句，注意需将全部显示的列名写在 JOIN 语句左边。(2 )查询列出所有用户 ID，以及他们的评论，如果有的话。分析依然是上一个实验，还可以使用 RIGHT OUTER JOIN 语句，注意需将全部显示的列名写在 JOIN 语句右边。【实验 2.13】复合条件连接查询(1 )查询用户 ID 为 1 的客户的订单信息和客户名。分析复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。此查询需在内查询的基础上加上另一个条件，用户 iD 为 1，使用AND 语句添加精确条件。(2 )查询每个用户的购物车中的商品价格，并且按照价格顺序排列。分析此查询需要先使用内连接对商品表和购物车表进行连接，得到商品的价格，在使用ORDER BY 语句对价格进行顺序排列。4.嵌套查询【实验 2.14】 IN(1 )查询订购商品 ID 为 1 的订单 ID，并根据订单 ID 查询发出此订单的用户 ID。分析此查询需要使用 IN 关键字进行子查询，子查询是通过 SELECT 语句在订单明细表中先确定此订单 ID，在通过 SELECT 在订单表中查询到用户 ID。(2 )查询订购商品 ID 为 1 的订单 ID，并根据订单 ID 查询未发出此订单的用户 ID。分析此查询和前一个实验相似，只是需使用 NOT IN 语句。【实验 2.15】比较运算符(1 )查询今年新增会员的订单，并且列出所有订单总价小于 100 的订单 ID。分析此查询需要使用嵌套，子查询需先查询用户表得到今年创建的用户信息，在将用户ID 匹配找打订单信息，其中使用比较运算符提供订单总价小于 100 的条件。(2 )查询所有订单商品数量总和小于 100 的商品 ID，并将不在此商品所在类别的其他类别的 ID 列出来。分析此查询需要进行嵌套查询，子查询过程需要使用到 SUM函数和 GROUP BY 求出同种商品的所有被订数量，使用比较运算符得到数量总和小于 100 的商品 ID，再使用比较运算符“不等于”得到非此商品所在类的类别 ID。【实验 2.16】EXISTS(1 )查询表中是否存在用户 ID 为 100 的用户，如果存在，列出此用户的信息。分析EXISTS 关键字后面的参数是一个任意的子查询，系统对于查询进行运算以判断它是否返回行，如果至少返回一行，那以 EXISTS 的结果为 TRUE，此时外层查询语句将进行查询。此查询需要对用户 ID 进行 EXIST 操作。(2 )查询表中是否存在类别 ID 为 100 的商品类别，如果存在，列出此类别中商品价格小于 5 的商品 ID。分析与上一个实验相似，此实验在外查询过程添加了比较运算符。【实验 2.17】ANY查询所有商品表中价格比订单表中商品 ID 对应的价格大的商品 ID。分析ANY 关键字在一个比较操作符的后面，表示若与子查询返回的任何值比较为 TRUE，则返回 TRUE。此处使用 ANY 来引出内查询。【实验 2.18】ALL查询所有商品表中价格比订单表中所有商品 ID 对应的价格大的商品 ID。分析使用 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 的商品，查询类别 ID 为 1 和 2 的所有商品，使用 UNION ALL 连接查询结果。分析使用 UNION ALL 包含重复的行，在前面的例子中，分开查询时，两个返回结果中有相同的记录，使用 UNION 会自动去除重复行。UNION ALL 从查询结果集中自动要返回所有匹配行，而不进行删除。实验要求1所有操作必须通过 MySQL Workbench 完成；2每执行一种查询语句后，均要求通过 MySQL Workbench 查看执行结果；3将操作过程以屏幕抓图的方式拷贝，形成实验文档。实验训练 3 数据增删改操作请到电脑端查看实验目的基于实验 1 创建的汽车用品网上商城数据库 Shopping，练习 Insert、Delete、TRUNCATE TABLE、Update 语句的操作方法，理解单记录插入与批量插入、DELETE 与 TRUNCATE TABLE语句、单表修改与多表修改的区别。实验内容【实验 3-1】插入数据(1 )使用单记录插入 Insert 语句分别完成汽车配件表 Autoparts、商品类别表 category、用户表 Client 、用户类别表 Clientkind 、购物车表 shoppingcart、订单表 Order、订单明细表order_has_Autoparts、评论 Comment 的数据插入，数据值自定；并通过 select 语句检查插入前后的记录情况。(2 )使用带 Select 的 Insert 语句完成汽车配件表 Autoparts 中数据的批量追加；并通过select 语句检查插入前后的记录情况。【实验 3-2】删除数据(1 )使用 Delete 语句分别完成购物车表 shoppingcart、订单表 Order、订单明细表Order_has_Autoparts、评论 Comment 的数据删除，删除条件自定；并通过 select 语句检查删除前后的记录情况。(2 )使用 TRUNCATE TABLE 语句分别完成购物车表 shoppingcart、评论 Comment 的数据删除。【实验 3-3】修改数据使用 Update 分别完成汽车配件表 Autoparts、商品类别表 category、用户表 Client、用户类别表 Clientkind、购物车表 shoppingcart、订单表 Order、订单明细表Order_has_Autoparts、评论 Comment 的数据修改，修改后数据值自定，修改条件自定；并通过 select 语句检查修改前后的记录情况。实验要求1所有操作必须通过 MySQL Workbench 完成；2每执行一种插入、删除或修改语句后，均要求通过 MySQL Workbench 查看执行结果及表中数据的变化情况；3将操作过程以屏幕抓图的方式拷贝，形成实验文档。实验训练 4数据库系统维护请到电脑端查看实验目的基于实验 1 创建的汽车用品网上商城，练习创建用户、权限管理，数据库备份与恢复方法，数据导出导入的方法，体会数据库系统维护的主要工作。实验内容1数据库安全性【实验 6-1】建立账户创建一个用户名为Teacher密码为T99999 的用户；创建一个用户名为Student 密码为S11111的用户。【实验 6-2】用户授权将 Shopping 数据库上 SELECT、INSERT、DELETE、UPDATE 的权限授予Teacher 用户；将 Shopping 数据库上 SELECT 的权限授予Student用户。【实验 6-3】以Teacher 用户身份连接 Shopping 数据库，分别执行SELECT、INSERT、DELETE、UPDATE、CREATE 操作，查看执行结果；以 Student用户身份连接 Shopping 数据库，执行 SELECT、INSERT、DELETE、 UPDATE 操作，查看执行结果。2数据库备份与恢复【实验 6-4】使用 mysqldump 工具对 Shopping 数据库进行备份，查看备份文件。【实验 6-5】对 Shopping 数据库启用二进制日志，并且查看日志。【实验 6-6】使用 mysqldump 工具对 Shopping 数据库进行恢复，查看恢复前后 Shopping 数据库的数据状态。3数据导入导出【实验 6-7】分别使用 SELECT INTO、MySQL 命令、MySQL Workbench 完成 Shopping 数据库中会员表和汽车配件表的导出，查看导出结果。【实验 6-8】分别使用 LOAD DATA、MySQLIMPORT 、MySQL Workbench 完成 Shopping 数据库中会员表和汽车配件表的导入，查看导入结果。实验要求1所有操作均在命令行和 MySQL Workbench 中完成；2将操作过程以屏幕抓图的方式拷贝，形成实验文档。