MySQL上機考試題(習(xí)題答案)

1、mysql上機考試題(答案)建表詳情：/*navicat mysql data transfersource server : rootsource server version : 50067source host : localhost:3306source database : producttarget server type : mysqltarget server version : 50067file encoding : 65001date: 2013-02-21 17:22:07*/set foreign_key_checks=0;- - table structure fo

2、r pros- -drop table if exists pros;create table pros ( 產(chǎn)品編號 varchar(20) not null, 產(chǎn)品名稱 varchar(50) not null, 價格 decimal(10,2) not null, 庫存量 int(11) default null, primary key (產(chǎn)品編號) engine=innodb default charset=gbk;- - records of pros- -insert into pros values (0001, 風(fēng)箏, 18.80, 1024);insert into pro

3、s 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, 洗發(fā)露, 20.59, 420);insert into pros values (0112478, 毛巾, 6.50, 210);insert into pros values

4、 (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 tab

5、le pro_sal ( 銷售日期 date not null comment 銷售產(chǎn)品的日期, 產(chǎn)品編號 varchar(20) not null, 銷售商編號 varchar(20) not null, 數(shù)量 int(11) not null, 銷售額 decimal(10,0) not null, primary key (產(chǎn)品編號,銷售商編號), key 銷售商編號 (銷售商編號), constraint 銷售商編號 foreign key (銷售商編號) references saler (銷售商編號), constraint 產(chǎn)品編號 foreign key (產(chǎn)品編號) refe

6、rences pros (產(chǎn)品編號) 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

7、for saler- -drop table if exists saler;create table saler ( 銷售商編號 varchar(20) not null, 銷售商名稱 varchar(50) not null, 地區(qū) varchar(10) not null comment 銷售商所有地, 負(fù)責(zé)人 varchar(10) default null, 電話 varchar(20) default null, primary key (銷售商編號) engine=innodb default charset=gbk;- - records of saler- -insert i

8、nto saler values (000061, 山東大德日用, 華中, 劉威華,;insert into saler values (000145, 北京天成, 華北, 劉茂,;insert into saler values (000165, 喜田有限公司, 華北, 王總, 03254898684);insert into saler values (001547, 丹尼斯, 華南, 胡總,;insert into saler values (037102, 天景商貿(mào), 華北, 王磊,

9、;insert into saler values (059741, 成建股份有限公司, 華中, 徐總, 02568941565);insert into saler values (087412, 沐陽集團, 西部, 魯宣,;insert into saler values (089412, 新建德, 華南, 趙總, 40025684562);數(shù)據(jù)詳情：表名：pros 產(chǎn)品表表名：saler 銷售商表表名：pro_sal 銷售表一、基于product數(shù)據(jù)庫的三個表，用sql語言完成以下查詢（1） 查詢所有產(chǎn)品的產(chǎn)品編號和銷售額，并將銷售額增加10%后設(shè)置別名為“增長

10、額 查詢語句：select ps.產(chǎn)品編號,ps.銷售額,ps.銷售額*0.1 as 增長額 from pro_sal ps查詢結(jié)果： （2） 查詢“華中、華北、東南”地區(qū)的銷售商名稱和地區(qū)。查詢語句：select 銷售商名稱 ,地區(qū) from saler where 地區(qū) in(華中,華北,東南);查詢結(jié)果：（3） 查找銷售商名稱的第二個字符是建并且只有三個字符的銷售商編號和名稱。查詢語句：select s.銷售商編號,s.銷售商名稱 from saler swhere s.銷售商名稱 like_建_;查詢結(jié)果：（4） 查詢有電話的銷售商信息。查詢語句：select * from saler

11、 where 電話 is not null;查詢結(jié)果：（5） 查詢銷售額在2000到5000的并且數(shù)量大于100的產(chǎn)品編號和銷售日期。查詢語句：select ps.產(chǎn)品編號,ps.銷售日期 from pro_sal pswhere ps.銷售額 between 2000 and 5000 and ps.數(shù)量100;查詢結(jié)果：（本無結(jié)果，為達到效果另插入一條數(shù)據(jù)）（6） 求銷售了產(chǎn)品的銷售商總數(shù)。查詢語句：select distinct count(ps.銷售商編號) as 銷售商總數(shù) from pro_sal ps查詢結(jié)果：（7） 求產(chǎn)品編號為“0001”的產(chǎn)品的平均銷售額、最高銷售額、最低銷

12、售額。查詢語句：select avg(ps.銷售額) 平均 , max(ps.銷售額) 最高,min(ps.銷售額) 最低 from pro_sal pswhere ps.產(chǎn)品編號=0001;查詢結(jié)果：（8） 查詢銷售產(chǎn)品種類超過2類的銷售商編號和種類數(shù)。并按購買種類數(shù)從大到小排序。 查詢語句：select * from(select ps.銷售商編號,count(ps.產(chǎn)品編號) 銷售種類 from pro_sal psgroup by ps.銷售商編號order by count(distinct 產(chǎn)品編號) desc) swhere s.銷售種類1;查詢結(jié)果：（9） 查詢銷售商的銷售商編

13、號和名稱、銷售的產(chǎn)品編號和數(shù)量。查詢語句：select s.銷售商編號,s.銷售商名稱,ps.產(chǎn)品編號,ps.數(shù)量 from saler s,pro_sal pswhere s.銷售商編號=ps.銷售商編號;查詢結(jié)果：（10） 查詢至少銷售過1次“0001”號產(chǎn)品的銷售商編號和購買次數(shù)，并按購買次數(shù)多少降序排列。查詢語句：select ps.銷售商編號 ,count(*) 購買次數(shù)from pro_sal pswhere ps.產(chǎn)品編號=0001group by ps.銷售商編號order by count(*) desc;查詢結(jié)果：（11） 查詢與喜田有限公司在同一地區(qū)的銷售商名稱，地區(qū)和負(fù)

14、責(zé)人。查詢語句：select s.銷售商名稱,s.地區(qū),s.負(fù)責(zé)人 from saler swhere s.地區(qū) like(select 地區(qū) from saler where 銷售商名稱=喜田有限公司)and s.銷售商名稱 !=喜田有限公司;查詢結(jié)果：（12） 查詢銷售商的銷售情況，要求包括銷售了產(chǎn)品的銷售商和沒有銷售的銷售商，顯示他們的銷售商編號、銷售商名稱、產(chǎn)品編號、銷售日期。查詢語句：select s.銷售商編號,s.銷售商名稱,ps.產(chǎn)品編號,ps.銷售日期from saler s,pro_sal pswhere s.銷售商編號=ps.銷售商編號;查詢結(jié)果：（13） 查詢銷售額小于

15、平均銷售額的產(chǎn)品編號、產(chǎn)品名稱和價格、銷售額。查詢語句：select p.產(chǎn)品編號,p.產(chǎn)品名稱,p.價格,ps.銷售額from pros p,pro_sal pswhere p.產(chǎn)品編號=ps.產(chǎn)品編號and ps.銷售額(select avg(銷售額) from pro_sal)查詢結(jié)果：（14） 查詢沒被銷售商銷售過的產(chǎn)品信息。查詢語句：select * from pros pwhere p.產(chǎn)品編號 not in(select ps.產(chǎn)品編號 from pro_sal ps )查詢結(jié)果：（15） 查詢銷售了“0001”但沒有銷售“0002”號產(chǎn)品的銷售商編號和產(chǎn)品編號。查詢語句sele

16、ct ps.銷售商編號,ps.產(chǎn)品編號 from pro_sal pswhere ps.產(chǎn)品編號=0001and ps.銷售商編號 not in (select 銷售商編號 from pro_sal where 產(chǎn)品編號=0002)查詢結(jié)果-產(chǎn)品銷售數(shù)據(jù)庫中包含三個表，分別是產(chǎn)品表、銷售商表和產(chǎn)品銷售表。產(chǎn)品表（產(chǎn)品編號，產(chǎn)品名稱，價格，庫存量）銷售商表（銷售商編號，銷售商名稱，地區(qū)，負(fù)責(zé)人，電話）產(chǎn)品銷售表（銷售日期，產(chǎn)品編號，銷售商編號，數(shù)量，銷售額）產(chǎn)品表、銷售商表和產(chǎn)品銷售表的表結(jié)構(gòu)如表1、2和3所示。表1產(chǎn)品表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束產(chǎn)品編號產(chǎn)品唯一的產(chǎn)品編號字符串，長度為20 主鍵產(chǎn)品名稱產(chǎn)品的產(chǎn)品名稱字符串，長度為50非空值價格產(chǎn)品的價格貨幣數(shù)據(jù)類型非空值庫存量產(chǎn)品的庫存量整型空值表2銷售商表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束說明銷售商編號銷售商唯一編號字符串，長度為20 主鍵銷售商名稱銷售商名稱字符串，長度為50非空值地區(qū)銷售商所在的地區(qū)字符串，長度為10非空值負(fù)責(zé)人銷售商單位負(fù)責(zé)人字符串，長度為20空值電話銷售商的聯(lián)系電話字符串，長度為20空值表3產(chǎn)品銷售表結(jié)構(gòu)列名 說明