MySQL上机测验题习题答案.docx
《MySQL上机测验题习题答案.docx》由会员分享，可在线阅读，更多相关《MySQL上机测验题习题答案.docx（11页珍藏版）》请在冰豆网上搜索。
MySQL上机测验题习题答案
MySQL上机测验题（习题答案）
————————————————————————————————作者：
————————————————————————————————日期：
MySQL上机考试题（答案）
建表详情：
/*
NavicatMySQLDataTransfer
SourceServer:
root
SourceServerVersion:
50067
SourceHost:
localhost:
3306
SourceDatabase:
product
TargetServerType:
MYSQL
TargetServerVersion:
50067
:
65001
Date:
2013-02-2117:
22:
07
*/
SETFOREIGN_KEY_CHECKS=0;
------------------------------
--Tablestructurefor`pros`
------------------------------
DROPTABLEIFEXISTS`pros`;
CREATETABLE`pros`（
`产品编号`varchar（20）NOTNULL,
`产品名称`varchar（50）NOTNULL,
`价格`decimal（10,2）NOTNULL,
`库存量`int（11）defaultNULL,
PRIMARYKEY（`产品编号`）
）ENGINE=InnoDBDEFAULTCHARSET=gbk;
------------------------------
--Recordsofpros
------------------------------
INSERTINTO`pros`VALUES（'0001','风筝','18.80','1024'）;
INSERTINTO`pros`VALUES（'0002','杯子','9.90','800'）;
INSERTINTO`pros`VALUES（'0003','帽子','19.80','980'）;
INSERTINTO`pros`VALUES（'0004','项链','9868.99','266'）;
INSERTINTO`pros`VALUES（'0005','钻戒','18999.68','520'）;
INSERTINTO`pros`VALUES（'0112358','洗发露','20.59','420'）;
INSERTINTO`pros`VALUES（'0112478','毛巾','6.50','210'）;
INSERTINTO`pros`VALUES（'0112568','棉被','200.86','300'）;
INSERTINTO`pros`VALUES（'0112690','墨水','5.50','800'）;
INSERTINTO`pros`VALUES（'0112691','钢笔','86.99','128'）;
INSERTINTO`pros`VALUES（'0112965','毛笔','35.48','480'）;
INSERTINTO`pros`VALUES（'0221545','枕头','63.68','520'）;
------------------------------
--Tablestructurefor`pro_sal`
------------------------------
DROPTABLEIFEXISTS`pro_sal`;
CREATETABLE`pro_sal`（
`销售日期`dateNOTNULLCOMMENT'销售产品的日期',
`产品编号`varchar（20）NOTNULL,
`销售商编号`varchar（20）NOTNULL,
`数量`int（11）NOTNULL,
`销售额`decimal（10,0）NOTNULL,
PRIMARYKEY（`产品编号`,`销售商编号`）,
KEY`销售商编号`（`销售商编号`）,
CONSTRAINT`销售商编号`FOREIGNKEY（`销售商编号`）REFERENCES`saler`（`销售商编号`）,
CONSTRAINT`产品编号`FOREIGNKEY（`产品编号`）REFERENCES`pros`（`产品编号`）
）ENGINE=InnoDBDEFAULTCHARSET=gbk;
------------------------------
--Recordsofpro_sal
------------------------------
INSERTINTO`pro_sal`VALUES（'2013-02-06','0112358','000061','120','5890'）;
INSERTINTO`pro_sal`VALUES（'2013-02-18','0112690','037102','50','9853'）;
INSERTINTO`pro_sal`VALUES（'2013-02-04','0112691','087412','15','1421'）;
------------------------------
--Tablestructurefor`saler`
------------------------------
DROPTABLEIFEXISTS`saler`;
CREATETABLE`saler`（
`销售商编号`varchar（20）NOTNULL,
`销售商名称`varchar（50）NOTNULL,
`地区`varchar（10）NOTNULLCOMMENT'销售商所有地',
`负责人`varchar（10）defaultNULL,
`电话`varchar（20）defaultNULL,
PRIMARYKEY（`销售商编号`）
）ENGINE=InnoDBDEFAULTCHARSET=gbk;
------------------------------
--Recordsofsaler
------------------------------
INSERTINTO`saler`VALUES（'000061','山东大德日用','华中','刘威华',''）;
INSERTINTO`saler`VALUES（'000145','北京天成','华北','刘茂',''）;
INSERTINTO`saler`VALUES（'000165','喜田有限公司','华北','王总',''）;
INSERTINTO`saler`VALUES（'001547','丹尼斯','华南','胡总',''）;
INSERTINTO`saler`VALUES（'037102','天景商贸','华北','王磊',''）;
INSERTINTO`saler`VALUES（'059741','成建股份有限公司','华中','徐总',''）;
INSERTINTO`saler`VALUES（'087412','沐阳集团','西部','鲁宣',''）;
INSERTINTO`saler`VALUES（'089412','新建德','华南','赵总','40025684562'）;
数据详情：
表名：
pros产品表
表名：
saler销售商表
表名：
pro_sal销售表
一、基于product数据库的三个表，用SQL语言完成以下查询
（1）查询所有产品的产品编号和销售额，并将销售额增加10%后设置别名为“增长额"
查询语句：
selectps.`产品编号`,ps.`销售额`,ps.`销售额`*0.1AS'增长额'frompro_salps
查询结果：
（2）查询“华中、华北、东南”地区的销售商名称和地区。
查询语句：
select销售商名称,地区fromsaler
where地区in（'华中','华北','东南'）;
查询结果：
（3）查找销售商名称的第二个字符是’建’并且只有三个字符的销售商编号和名称。
查询语句：
selects.`销售商编号`,s.`销售商名称`fromsalers
wheres.`销售商名称`like'_建_';
查询结果：
（4）查询有电话的销售商信息。
查询语句：
select*fromsalerwhere电话isnotnull;
查询结果：
（5）查询销售额在2000到5000的并且数量大于100的产品编号和销售日期。
查询语句：
selectps.`产品编号`,ps.`销售日期`frompro_salps
whereps.`销售额`BETWEEN2000and5000
andps.`数量`>100;
查询结果：
（本无结果，为达到效果另插入一条数据）
（6）求销售了产品的销售商总数。
查询语句：
selectDISTINCTcount（ps.`销售商编号`）as'销售商总数'frompro_salps
查询结果：
（7）求产品编号为“0001”的产品的平均销售额、最高销售额、最低销售额。
查询语句：
selectAVG（ps.`销售额`）平均,MAX（ps.`销售额`）最高,MIN（ps.`销售额`）最低frompro_salps
whereps.`产品编号`=0001;
查询结果：
（8）查询销售产品种类超过2类的销售商编号和种类数。
并按购买种类数从大到小排序。
查询语句：
select*from（
selectps.`销售商编号`,count（ps.`产品编号`）销售种类frompro_salps
GROUPBYps.`销售商编号`
ORDERBYCOUNT（DISTINCT产品编号）DESC）s
wheres.`销售种类`>1;
查询结果：
（9）查询销售商的销售商编号和名称、销售的产品编号和数量。
查询语句：
selects.`销售商编号`,s.`销售商名称`,ps.`产品编号`,ps.`数量`from
salers,pro_salps
WHEREs.`销售商编号`=ps.`销售商编号`;
查询结果：
（10）查询至少销售过1次“0001”号产品的销售商编号和购买次数，并按购买次数多少降序排列。
查询语句：
selectps.`销售商编号`,COUNT（*）购买次数
frompro_salps
whereps.`产品编号`=0001
GROUPBYps.`销售商编号`
ORDERBYcount（*）desc;
查询结果：
（11）查询与‘喜田有限公司’在同一地区的销售商名称，地区和负责人。
查询语句：
SELECTs.`销售商名称`,s.`地区`,s.`负责人`fromsalers
wheres.`地区`like（select地区fromsalerWHERE`销售商名称`='喜田有限公司'）
ands.`销售商名称`!
='喜田有限公司';
查询结果：
（12）查询销售商的销售情况，要求包括销售了产品的销售商和没有销售的销售商，显示他们的销售商编号、销售商名称、产品编号、销售日期。
查询语句：
selects.`销售商编号`,s.`销售商名称`,ps.`产品编号`,ps.`销售日期`
fromsalers,pro_salps
WHEREs.`销售商编号`=ps.`销售商编号`;
查询结果：
（13）查询销售额小于平均销售额的产品编号、产品名称和价格、销售额。
查询语句：
selectp.`产品编号`,p.`产品名称`,p.`价格`,ps.`销售额`
fromprosp,pro_salps
WHEREp.`产品编号`=ps.`产品编号`
andps.`销售额`<（SELECTAVG（销售额）frompro_sal）
查询结果：
（14）查询没被销售商销售过的产品信息。
查询语句：
SELECT*fromprosp
wherep.`产品编号`notin（selectps.`产品编号`frompro_salps）
查询结果：
（15）查询销售了“0001”但没有销售“0002”号产品的销售商编号和产品编号。
查询语句
selectps.`销售商编号`,ps.`产品编号`frompro_salps
whereps.`产品编号`=0001
andps.`销售商编号`notin（SELECT`销售商编号`frompro_salwhere`产品编号`=0002）
查询结果
---------------------------------------------------------------------------------------------------------------------
产品销售数据库中包含三个表，分别是产品表、销售商表和产品销售表。
产品表（产品编号，产品名称，价格，库存量）
销售商表（销售商编号，销售商名称，地区，负责人，电话）
产品销售表（销售日期，产品编号，销售商编号，数量，销售额）
产品表、销售商表和产品销售表的表结构如表1、2和3所示。
表1 产品表结构
列名
说明
数据类型
约束
产品编号
产品唯一的产品编号
字符串，长度为20
主键
产品名称
产品的产品名称
字符串，长度为50
非空值
价格
产品的价格
货币数据类型
非空值
库存量
产品的库存量
整型
空值
表2 销售商表结构
列名
说明
数据类型
约束说明
销售商编号
销售商唯一编号
字符串，长度为20
主键
销售商名称
销售商名称
字符串，长度为50
非空值
地区
销售商所在的地区
字符串，长度为10
非空值
负责人
销售商单位负责人
字符串，长度为20
空值
电话
销售商的联系电话
字符串，长度为20
空值
表3 产品销售表结构
列名
说明
数据类型
约束说明
销售日期
销售产品的日期
日期型
非空值
产品编号
产品的编号
字符串，长度为20
外键，引用销售商表的主键
销售商编号
销售商的编号
字符串，长度为20
外键，引用产品表的主键
数量
销售商品的数量
整型
非空值
销售额
销售额
货币数据类型
非空值
主键为：
（销售日期,产品编号,客户编号）
二、基于基于product数据库的三个表，完成以下操作（略）
*
（1）为销售商表的“地区”字段添加一个默认值约束”华中”，约束名为address
（2）向产品表插入一个新产品，（‘0006’，‘节能灯’，5，100）
（3）把节能灯的价格增加2元。
升级会员 