23秋国家开放大学《MySQL数据库应用》实验训练1-4参考答案.docx
《23秋国家开放大学《MySQL数据库应用》实验训练1-4参考答案.docx》由会员分享，可在线阅读，更多相关《23秋国家开放大学《MySQL数据库应用》实验训练1-4参考答案.docx（30页珍藏版）》请在冰点文库上搜索。
国家开放大学《MySQL数据库应用》实验训练1-4参考答案
实验训练1在MySQL中创建数据库和表
参考答案：
步骤：
1、使用show语句找出在服务器上当前存在什么数据库：
mysql>showdatabases;
2、创建一个数据库test：
mysql>createdatabasetest;
3、选择你所创建的数据库：
mysql>usetest;
4创建一个数据表：
首先查看刚才创建的数据库中存在什么表：
mysql>showtables;
（说明刚才创建的数据库中还没有数据库表）
接着我们创建一个关于students的数据表：
包括学生的学号（id），姓名（name），性别（sex），年龄（age）。
mysql>createtablestudents（idintunsignednotnullauto_incrementprimarykey，namechar（8）notnull，sexchar（4）notnull，agetinyintunsignednotnull，）;
解释：
以"idintunsignednotnullauto_incrementprimarykey"行进行介绍:
"id"为列的名称;
"int"指定该列的类型为int（取值范围为-8388608到8388607），在后面我们又用"unsigned"加以修饰，表示该类型为无符号型，此时该列的取值范围为0到16777215;
"notnull"说明该列的值不能为空，必须要填，如果不指定该属性，默认可为空;
"auto_increment"需在整数列中使用，其作用是在插入数据时若该列为NULL，MySQL将自动产生一个比现存值更大的唯一标识符值。
在每张表中仅能有一个这样的值且所在列必须为索引列。
"primarykey"表示该列是表的主键，本列的值必须唯一，MySQL将自动索引该列。
下面的char（8）表示存储的字符长度为8，tinyint的取值范围为-127到128，default属性指定当该列值为空时的默认值。
创建一个表后，用showtables显示数据库中有哪些表：
mysql>showtables；
5、显示表结构：
mysql>describestudents;
6、在表中添加记录：
首先用select命令来查看表中的数据：
mysql>select*fromstudents;
（说明刚才创建的数据库表中还没有任何记录）
接着加入一条新纪录：
mysql>insertintostudentsvalue（‘01’，’Tom’，’F’，’18’）；
再用select命令来查看表中的数据的变化：
mysql>select*fromstudents;
7、用文本方式将数据装入一个数据库表：
创建一个文本文件“student.sql”，每行包括一个记录，用TAB键把值分开，并且以在createtable语句中列出的次序，例如：
02 Tony F 18
03 Amy M 18
04 Lisa M 18
将文本文件“student.sql”装载到students表中：
mysql>loaddatalocalinfile”e:
\\student.sql”intotablestudents;
再使用select命令来查看表中的数据的变化：
mysql>select*fromstudents;
实验训练2：
数据查询操作
实验内容：
1.单表查询
【实验2.1】字段查询
（1）查询商品名称为“挡风玻璃”的商品信息。
（2）查询ID为1的订单。
【实验2.2】多条件查询
查询所有促销的价格小于1000的商品信息。
【实验2.3】DISTINCT
（1）查询所有对商品ID为1的商品发表过评论的用户ID。
（2）查询此汽车用品网上商城会员的创建时间段，1年为一段。
【实验2.4】ORDERBY
（1）查询类别ID为1的所有商品，结果按照商品ID降序排列。
（2）查询今年新增的所有会员，结果按照用户名字排序。
【实验2.5】GROUPBY
（1）查询每个用户的消费总金额（所有订单）。
（2）查询类别价格一样的各种商品数量总和。
分析：
此查询中需要对商品进行分类，分类依据是同类别和价格，这是“多列分组”，较上一个例子更为复杂。
2.聚合函数查询
【实验2.6】COUNT（）
（1）查询类别的数量。
分析：
此查询利用COUNT（）函数，返回指定列中值的数目，此处指定列是类别表中的ID（或者名称均可）。
（2）查询汽车用品网上商城的每天的接单数。
分析：
订单相关，此处使用聚合函数COUNT（）和Groupby子句。
【实验2.7】SUM（）
查询该商城每天的销售额。
分析：
在订单表中，有一列是订单总价，将所有订单的订单总价求和，按照下单日期分组，使用SUM（）函数和Groupby子句。
【实验2.8】AVG（）
（1）查询所有订单的平均销售金额。
分析：
同上一个相同，还是在订单表中，依然取用订单总价列，使用AVG（）函数，对指定列的值求平均数。
【实验2.9】MAX（）
（1）查询所有商品中的数量最大者。
分析：
商品的数量信息存在于商品表中，此处查询应该去商品表，在商品数量指定列中求值最大者。
使用MAX（）函数。
（3）查询所有用户按字母排序中名字最靠前者。
分析：
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）查询每个用户的购物车中的商品价格，并且按照价格顺序排列。
分析：
此查询需要先使用内连接对商品表和购物车表进行连接，得到商品的价格，在使用ORDERBY语句对价格进行顺序排列。
4.嵌套查询
【实验2.14】IN
（1）查询订购商品ID为1的订单ID，并根据订单ID查询发出此订单的用户ID。
分析：
此查询需要使用IN关键字进行子查询，子查询是通过SELECT语句在订单明细表中先确定此订单ID，在通过SELECT在订单表中查询到用户ID。
（2）查询订购商品ID为1的订单ID，并根据订单ID查询未发出此订单的用户ID。
分析：
此查询和前一个实验相似，只是需使用NOTIN语句。
【实验2.15】比较运算符
（1）查询今年新增会员的订单，并且列出所有订单总价小于100的订单ID。
分析：
此查询需要使用嵌套，子查询需先查询用户表得到今年创建的用户信息，在将用户ID匹配找打订单信息，其中使用比较运算符提供订单总价小于100的条件。
（2）查询所有订单商品数量总和小于100的商品ID，并将不在此商品所在类别的其他类别的ID列出来。
分析：
此查询需要进行嵌套查询，子查询过程需要使用到SUM（）函数和GROUPBY求出同种商品的所有被订数量，使用比较运算符得到数量总和小于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的所有商品，使用UNIONALL连接查询结果。
分析：
使用UNIONALL包含重复的行，在前面的例子中，分开查询时，两个返回结果中有相同的记录，使用UNION会自动去除重复行。
UNIONALL从查询结果集中自动要返回所有匹配行，而不进行删除。
实验训练3数据增删改操作
一、实验内容与目的
1.使用SQL语言完成数据定义、数据查询、更新，删除。
2.利用SQL语言提供的INSERT语句、UPDATE语句和DELETE语句对所创建的表进行操作；
二、实验方法、步骤
1.运行数据库SQL sever 2008
2.新建数据库，并且创建新的表
3.利用相关语句进行数据查询，更新，删除，修改
三、实验过程原始记录
1）创建供应商表汽车配件表Autoparts，由以下属性组成：
零件代码PNO（CHAR型），零件名PNAME（CHAR型），颜色COLOR（CHAR型），价格Money（INT型）
CREATE TABLE Autoparts（
PNO CHAR（3），
PNAME CHAR（10），
COLOR CHAR（4），
Money INT）;
2）创建供应商表汽车配件表category，由以下属性组成：
轮胎
Tires（CHAR型），灯Lights（int型）。
CREATETABLEcategory（
TiresCHAR（20），
LightsCHAR（10））;
3）创建用户表Client，由以下属性组成：
姓名 NAME（CHAR型），TEL（int型）。
CREATETABLEClient（
NAMECHAR（20），
TELINT（10））;
4）创建用户类别表Clientkind，由以下属性组成：
普通Normal（CHAR型），贵宾VIP（CHAR型）。
CREATETABLEClientkind（
NormalCHAR（20），
VIPCHAR（20））;
5）创建购物车表shoppingcart，由以下属性组成：
名称 SHOPNAME（CHAR型），Money（int型）
CREATETABLEshoppingcart（
SHOPNAMECHAR（20），
MoneyINT（10））;
6）创建订单表Order，由以下属性组成：
订单号Order_ID（INT型），Money（INT型）
CREATETABLEOrder（
Order_IDINT（50），
MoneyINT（10））;
7）创建订单明细表order_has_Autoparts，由以下属性组成：
配件名称SHOPNAME（CHAR型），价格Money（INT型），姓名NAME（CHAR型），联系电话TEL（INT型），地址Address（CHAR型）
CREATETABLEorder_has_Autoparts（
SHOPNAMECHAR（50），
NAMECHAR（50），
TELINT（20），
AddressCHAR（50），
MoneyINT（10））;
8）创建评论表Comment，由以下属性组成：
订单号Order_ID（INT型），分数Fractions（INT型），内容Content（CHAR型），姓名NAME（CHAR型），联系电话TEL（INT型），地址Address（CHAR型）
CREATETABLEComment（
Order_IDINT（50），
FractionsINT（10）
ContentCHAR（500），
NAMECHAR（50），
TELINT（20），
AddressCHAR（50））;
二）数据操作
1.插入数据（以Autoparts表为例，其他表同理）
1）向Autoparts表插入下列数据:
P1，螺母，红，12 P2，螺栓，绿，17 P3，螺丝刀，蓝，14 P4，螺丝刀，红，14 P5，凸轮，蓝，40 P6，齿轮，红，30
INSERTINTOAutopartsVALUES（‘P1’，’螺母’，’红’，’12’）;
INSERTINTOAutopartsVALUES（‘P2’，’螺栓’，’绿’，’17’）;
INSERTINTOAutopartsVALUES（‘P3’，’螺丝刀’，’蓝’，’14’）;
INSERTINTOAutopartsVALUES（‘P4’，’螺丝刀’，’红’，’14’）;
INSERTINTOAutopartsVALUES（‘P5’，’凸轮’，’蓝’，’40’）;
INSERTINTOAutopartsVALUES（‘P6’，’齿轮’，’红’，’30’）;
2）修改数据:
将全部红色零件的颜色改成蓝色。
UPDATE Autoparts SET COLOR =’蓝’WHERE COLOR =’红’;
3）删除数据从Autoparts中删除 P1的纪录DELETE FROM Autoparts WHERE SNO =’P1';
四）、实验结果分析
在本次的试验中，使我对数据库的操作更加熟悉，也锻炼了我对数据表的更新，删除，查询等操作的熟练运用，对数据库有更进一步的了解。
实验训练4：
数据库系统维护
实验过程：
1、数据库安全性
【实验6-1】建立账户：
创建一个用户名为‘Teacher’密码为‘T99999’的用户；创建一个用户名为‘Student’密码为‘S11111’的用户。
找开MySQLWorkbench6.0CE，点击“server”下拉菜单，把鼠标移动到“UserandPrivileges”
单击弹出对话框，如下。
输入密码：
“111”弹出下图。
单击下方的“ADDAccount”，出现下图。
在LoginName处，输入：
Teacher
在Password处，输入：
T99999
在ConfirmPassword处，输入：
T99999，然后单击“Apply”完成“Teacher”用户的创建。
再用同样方法，创建“Student”用户的创建。
【实验6-2】用户授权：
将Shopping数据库上SELECT、INSERT、DELETE、UPDATE的权限授予‘Teacher’用户；将Shopping数据库上SELECT的权限授予‘Student’用户。
给‘Teacher’用户设置权限。
再用同样方法，给‘Student’用户设置权限。
【实验6-3】
以‘Teacher’用户身份连接Shopping数据库，分别执行SELECT、INSERT、DELETE、UPDATE、CREATE操作，查看执行结果；以‘Student’用户身份连接Shopping数据库，执行SELECT、INSERT、DELETE、UPDATE操作，查看执行结果。
以‘Teacher’用户身份连接Shopping数据库，点击下图中的按钮“ConnecttoDatabase”。
弹出对话框，如下图。
Username处，改为“Teacher”，单击OK按钮，弹出下图。
输入密码，单击Ok按钮，可以看到现在的用户就是“Teacher”，如下图。
执行SELECT操作；
执行INSERT操作；
现在有一个空表，名字为commentcopy2，如下图。
把表comment中的数据，批量添加到commentcopy2中。
输入命令：
insertintocommentcopy2select*fromcomment;
执行结果如下。
执行DELETE操作；
“commentcopy2”中有6条记录（如下图），现在把第二条删除了。
输入命令：
deletefromcommentcopy2whereComment_id=2;
执行结果如下，可以看到第二条被删除了。
执行UPDATE操作；
Autoparts表中（如下图），第一条记录中的“price”为90，现在使用UPDATE命令，使它变成150.
输入命令：
UPDATEautopartsSETprice=150whereApid=1;
执行结果如下图。
执行CREATE操作。
创建一个名为“tuser”的表，并包含一列。
输入命令：
createtabletuser（IDINT）;
执行结果如下。
以‘Student’用户身份连接Shopping数据库，执行SELECT、INSERT、DELETE、UPDATE操作，查看执行结果。
SELECT命令可以使用。
执行SELECT、INSERT、DELETE、UPDATE操作时出错，提示权限不够。
2．数据库备份与恢复
【实验6-4】使用mysqldump工具对Shopping数据库进行备份，查看备份文件。
打开文件夹，C:
\ProgramFiles\MySQL\MySQLServer5.5\bin，如下。
从上图中，可以看到mysqldump.exe。
在命令提示符下，运行这个软件，结果如下。
对整个Shopping数据库进行备份，备份到d:
\shopping文件夹下，文件名为shopping.sql。
输入如下命令：
mysqldump-hlocalhost-uroot-p111shopping>d:
\shopping\shopping.sql
执行结果如下图
打开文件夹“d:
\shopping”查看结果。
【实验6-5】对Shopping数据库启用二进制日志，并且查看日志。
输入命令：
showvariableslike'%log_bin%';
执行结果
从上图中，看到log_bin状态为OFF，也就是二进制日志没有启用。
找开“C:
\ProgramFiles\MySQL\MySQLServer5.5”文件夹下的文件“my.ini”,如下图。
在datadir="C:
/ProgramData/MySQL/MySQLServer5.5/Data/"下面再添加一行，内容为“log-bin="C:
/ProgramData/MySQL/MySQLServer5.5/Data/"”如下图。
保存文件“my.ini”然后重新启动电脑。
打开MySQLWorkbench6.0CE
输入命令：
showvariableslike'%log_bin%';
执行结果如下。
可以看到“log_bin”的值，变成了“ON”，也就是启用了二进制
升级会员 