MySQL上机考试题(习题-答案)

MySQLMySQL 上机考试题上机考试题 答案答案 建表详情 Navicat MySQL Data Transfer Source Server root Source Server Version 50067 Source Host localhost 3306 Source Database product Target Server Type MYSQL Target Server Version 50067 File Encoding 65001 Date 2013 02 21 17 22 07 SET FOREIGN KEY CHECKS 0 Table structure for pros DROP TABLE IF EXISTS pros CREATE TABLE pros 产品编号 varchar 20 NOT NULL 产品名称 varchar 50 NOT NULL 价格 decimal 10 2 NOT NULL 库存量 int 11 default NULL PRIMARY KEY 产品编号 ENGINE InnoDB DEFAULT CHARSET gbk Records of pros INSERT INTO pros VALUES 0001 风筝 18 80 1024 INSERT INTO pros VALUES 0002 杯子 9 90 800 INSERT INTO pros VALUES 0003 帽子 19 80 980 INSERT INTO pros VALUES 0004 项链 9868 99 266 INSERT INTO pros VALUES 0005 钻戒 18999 68 520 INSERT INTO pros VALUES 0112358 洗发露 20 59 420 INSERT INTO pros VALUES 0112478 毛巾 6 50 210 INSERT INTO pros VALUES 0112568 棉被 200 86 300 INSERT INTO pros VALUES 0112690 墨水 5 50 800 INSERT INTO pros VALUES 0112691 钢笔 86 99 128 INSERT INTO pros VALUES 0112965 毛笔 35 48 480 INSERT INTO pros VALUES 0221545 枕头 63 68 520 Table structure for pro sal DROP TABLE IF EXISTS pro sal CREATE TABLE pro sal 销售日期 date NOT NULL COMMENT 销售产品的日期 产品编号 varchar 20 NOT NULL 销售商编号 varchar 20 NOT NULL 数量 int 11 NOT NULL 销售额 decimal 10 0 NOT NULL PRIMARY KEY 产品编号 销售商编号 KEY 销售商编号 销售商编号 CONSTRAINT 销售商编号 FOREIGN KEY 销售商编号 REFERENCES saler 销售商编号 CONSTRAINT 产品编号 FOREIGN KEY 产品编号 REFERENCES pros 产品编号 ENGINE InnoDB DEFAULT CHARSET gbk Records of pro sal INSERT INTO pro sal VALUES 2013 02 06 0112358 000061 120 5890 INSERT INTO pro sal VALUES 2013 02 18 0112690 037102 50 9853 INSERT INTO pro sal VALUES 2013 02 04 0112691 087412 15 1421 Table structure for saler DROP TABLE IF EXISTS saler CREATE TABLE saler 销售商编号 varchar 20 NOT NULL 销售商名称 varchar 50 NOT NULL 地区 varchar 10 NOT NULL COMMENT 销售商所有地 负责人 varchar 10 default NULL 电话 varchar 20 default NULL PRIMARY KEY 销售商编号 ENGINE InnoDB DEFAULT CHARSET gbk Records of saler INSERT INTO saler VALUES 000061 山东大德日用 华中 刘威华INSERT INTO saler VALUES 000145 北京天成 华北 刘茂INSERT INTO saler VALUES 000165 喜田有限公司 华北 王总 03254898684 INSERT INTO saler VALUES 001547 丹尼斯 华南 胡总INSERT INTO saler VALUES 037102 天景商贸 华北 王磊INSERT INTO saler VALUES 059741 成建股份有限公司 华中 徐总 02568941565 INSERT INTO saler VALUES 087412 沐阳集团 西部 鲁宣INSERT INTO saler VALUES 089412 新建德 华南 赵总 40025684562 数据详情 表名 pros 产品表 表名 saler 销售商表 表名 pro sal 销售表 一 基于 product 数据库的三个表 用 SQL 语言完成以下查询 1 查询所有产品的产品编号和销售额 并将销售额增加 10 后设置别名为 增长额 查询语句 select ps 产品编号 ps 销售额 ps 销售额 0 1 AS 增长额 from pro sal ps 查询结果 2 查询 华中 华北 东南 地区的销售商名称和地区 查询语句 select 销售商名称 地区 from saler where 地区 in 华中 华北 东南 查询结果 3 查找销售商名称的第二个字符是 建 并且只有三个字符的销售商编号和名称 查询语句 select s 销售商编号 s 销售商名称 from saler s where s 销售商名称 like 建 查询结果 4 查询有电话的销售商信息 查询语句 select from saler where 电话 is not null 查询结果 5 查询销售额在 2000 到 5000 的并且数量大于 100 的产品编号和销售日期 查询语句 select ps 产品编号 ps 销售日期 from pro sal ps where ps 销售额 BETWEEN 2000 and 5000 and ps 数量 100 查询结果 本无结果 为达到效果另插入一条数据 6 求销售了产品的销售商总数 查询语句 select DISTINCT count ps 销售商编号 as 销售商总数 from pro sal ps 查询结果 7 求产品编号为 0001 的产品的平均销售额 最高销售额 最低销售额 查询语句 select AVG ps 销售额 平均 MAX ps 销售额 最高 MIN ps 销售额 最低 from pro sal ps where ps 产品编号 0001 查询结果 8 查询销售产品种类超过 2 类的销售商编号和种类数 并按购买种类数从大到小排序 查询语句 select from select ps 销售商编号 count ps 产品编号 销售种类 from pro sal ps GROUP BY ps 销售商编号 ORDER BY COUNT DISTINCT 产品编号 DESC s where s 销售种类 1 查询结果 9 查询销售商的销售商编号和名称 销售的产品编号和数量 查询语句 select s 销售商编号 s 销售商名称 ps 产品编号 ps 数量 from saler s pro sal ps WHERE s 销售商编号 ps 销售商编号 查询结果 10 查询至少销售过 1 次 0001 号产品的销售商编号和购买次数 并按购买次数多少 降序排列 查询语句 select ps 销售商编号 COUNT 购买次数 from pro sal ps where ps 产品编号 0001 GROUP BY ps 销售商编号 ORDER BY count desc 查询结果 11 查询与 喜田有限公司 在同一地区的销售商名称 地区和负责人 查询语句 SELECT s 销售商名称 s 地区 s 负责人 from saler s where s 地区 like select 地区 from saler WHERE 销售商名称 喜田有限公司 and s 销售商名称 喜田有限公司 查询结果 12 查询销售商的销售情况 要求包括销售了产品的销售商和没有销售的销售商 显示 他们的销售商编号 销售商名称 产品编号 销售日期 查询语句 select s 销售商编号 s 销售商名称 ps 产品编号 ps 销售日期 from saler s pro sal ps WHERE s 销售商编号 ps 销售商编号 查询结果 13 查询销售额小于平均销售额的产品编号 产品名称和价格 销售额 查询语句 select p 产品编号 p 产品名称 p 价格 ps 销售额 from pros p pro sal ps WHERE p 产品编号 ps 产品编号 and ps 销售额 SELECT AVG 销售额 from pro sal 查询结果 14 查询没被销售商销售过的产品信息 查询语句 SELECT from pros p where p 产品编号 not in select ps 产品编号 from pro sal ps 查询结果 15 查询销售了 0001 但没有销售 0002 号产品的销售商编号和产品编号 查询语句 select ps 销售商编号 ps 产品编号 from pro sal ps where ps 产品编号 0001 and ps 销售商编号 not in SELECT 销售商编号 from pro sal where 产品编号 0002 查询结果 产品销售数据库中包含三个表 分别是产品表 销售商表和产品销售表 产品表 产品编号 产品名称 价格 库存量 销售商表 销售商编号 销售商名称 地区 负责人 电话 产品销售表 销售日期 产品编号 销售商编号 数量 销售额 产品表 销售商表和产品销售表的表结构如表 1 2 和 3 所示 表 1 产品表结构 列名说明数据类型约束 产品编号产品唯一的产品编号字符串 长度为 20 主键 产品名称产品的产品名称字符串 长度为 50非空值 价格产品的价格货币数据类型非空值 库存量产品的库存量整型空值 表 2 销售商表结构 列名说明数据类型约束说明 销售商编号销售商唯一编号字符串 长度为 20 主键 销售商名称销售商名称字符串 长度为 50非空值 地区销售商所在的地区字符串 长度为 10非空值 负责人销售商单位负责人字符串 长度为 20空值 电话销售商的联系电话字符串 长度为 20空值 表 3 产品销售表结构 列名 说明数据类型约束说明 销售日期销售