mysql新手进阶03

当年忠贞为国酬，何曾怕断头？
如今天下红遍，*靠谁守？
业未就，身躯倦，鬓已秋。
你我之辈，忍将夙愿，付与东流？

数据库结构如下：
仓库（仓库号, 城市, 面积）
订购单（职工号, 供应商号, 订购单号, 订购日期）
供应商（供应商号, 供应商名, 地址）
职工（仓库号, 职工号, 工资）

具体数据如下：

仓库表：
仓库号 城市 面积
WH1 北京 370
WH2 上海 500
WH3 广州 200
WH4 武汉 400

订购单表：
职工号 供应商号 订购单号 订购日期
E3 S7 OR67 06/23/01
E1 S4 OR73 07/28/01
E5 S4 OR76 05/25/01
E6 S6 OR77 05/26/01
E3 S4 OR79 06/13/01
E1 S2 OR80 08/29/01
E3 S3 OR90 09/01/01
E3 S3 OR91 07/13/01

供应商表：
供应商号 供应商名 地址
S2 名硕电子公司 苏州
S3 振华电子厂 西安
S4 华通电子公司 北京
S6 607厂 郑州
S7 爱华电子厂 北京

职工表：
仓库号 职工号 工资
WH2 E1 1220
WH1 E3 1210
WH2 E4 1250
WH3 E5 1230
WH1 E6 1250

使用SQL语句完成：
DDL
1.写出创建上述表的语句
命令：
create table 仓库(仓库号varchar(10) primary key,
城市varchar(10),
面积int);
create table 供应商(供应商号varchar(10) primary key,
供应商名varchar(30),
地址varchar(10));
create table 职工(仓库号varchar(10),
职工号varchar(10) primary key,
工资int);
create table 订购单(职工号varchar(10) references 职工(职工号),
供应商号varchar(10) references 供应商(供应商号),
订购单号varchar(10) primary key,
订购日期varchar(10));
DML
2.给出插入上述数据的insert语句
命令：
insert into 仓库values('WH1', '北京', 370);
insert into 仓库values('WH2', '上海', 500);
insert into 仓库values('WH3', '广州', 200);
insert into 仓库values('WH4', '武汉', 400);

insert into 供应商values('S2', '名硕电子公司', '苏州');
insert into 供应商values('S3', '振华电子厂','西安');
insert into 供应商values('S4', '华通电子公司', '北京');
insert into 供应商values('S6', '607厂', '郑州');
insert into 供应商values('S7', '爱华电子厂', '北京');

insert into 职工values ('WH2', 'E1', 1220);
insert into 职工values ('WH1', 'E3', 1210);
insert into 职工values ('WH2', 'E4', 1250);
insert into 职工values ('WH3', 'E5', 1230);
insert into 职工values ('WH1', 'E6', 1250);

insert into 订购单values ('E3', 'S7', 'OR67', '06/23/01');
insert into 订购单values ('E1', 'S4', 'OR73', '07/28/01');
insert into 订购单values ('E5', 'S4', 'OR76', '05/25/01');
insert into 订购单values ('E6', 'S6', 'OR77', '05/26/01');
insert into 订购单values ('E3', 'S4', 'OR79', '06/13/01');
insert into 订购单values ('E1', 'S2', 'OR80', '08/29/01');
insert into 订购单values ('E3', 'S3', 'OR90', '09/01/01');
insert into 订购单values ('E3', 'S3', 'OR91', '07/13/01');
单表查询
3.检索职工关系中的所有信息
命令：
select * from 职工;
结果：

4.检索供应商关系中的所有信息
命令：
select * from 供应商;
结果：

5.检索六月之后的所有订单
命令：
select * from 订购单where 订购日期like '06%' or 订购日期like '07%'or 订购日期like '08%'or 订购日期like '09%'or 订购日期like '10%'or 订购日期like '11%'or 订购日期like '12%';

结果：

6.检索面积大于400的仓库
命令：
select * from 仓库where 面积> 400;
结果：

7.检索哪些职工的工资多于1210
命令：
select * from 职工where 工资> 1210;
结果：

8.检索仓库是“WH1”或“WH2”并且面积大于400的城市
命令：
select 城市from 仓库where (仓库号= 'WH1' or 仓库号= 'WH2') and 面积> 400;
结果：

9.找出仓库面积在400到600的仓库
命令：
select * from 仓库where 面积between 400 and 600;
结果：

10.找出名中包含 “厂”的所有供应商的名
命令：
select 供应商名from 供应商where 供应商名like '%厂%';
结果：

11.找出不在西安的供应商
命令：
select * from 供应商where 地址!= '西安';
结果：

12.找出不在北京的仓库
命令：
select * from 仓库where 城市!= '北京';
结果：

13.按工资降序排列出所有职工的信息
命令：
select * from 职工order by 工资desc;
结果：

14.先按仓库号升序排列，再按工资降序排列
命令：
select * from 职工order by 仓库号asc, 工资desc;
结果：

15.在仓库表中统计一下有几个仓库
命令：
select count(*) 仓库数from 仓库;
结果：

16.在职工表中统计一下有几个仓库
命令：
select count(distinct 仓库号) 仓库数from 职工;
结果：

17.求总的仓库面积
命令：
select sum(面积) 总面积from 仓库;
结果：

聚合查询
18.每个职工的订单数
命令：
select 职工号, count(订购单号) 订单数from 订购单group by 职工号;
结果：

19.订单数大于3的职工
命令：
select 职工号, count(订购单号) 订单数from 订购单group by 职工号having count(订购单号) > 3;
结果：

多表查询
20.找出在面积大于400的仓库中工作的职工
命令：select 职工号from 职工, 仓库where 职工.仓库号= 仓库.仓库号and 面积> 400;

结果：

21.找出在北京工作的职工和他们的工资情况
命令：select 职工号, 工资from 职工, 仓库where 职工.仓库号= 仓库.仓库号and 城市= '北京';

结果：

22.找出工资大于1215的职工和他们所在的城市
命令：select 职工号, 城市from 职工, 仓库where 职工.仓库号= 仓库.仓库号and 工资> 1215;

结果：

子查询
23.哪些城市至少有一个订单（从仓库角度考虑）
命令：select 城市from 仓库, 订购单, 职工where 订购单.职工号= 职工.职工号and 仓库.仓库号= 职工.仓库号group by 城市having count(*) >= 1;

结果：

24.找出没有任何订单的城市
命令：select 城市from 仓库where 仓库号not in (select 仓库号from 职工where 职工号in (select 职工号from 订购单));

结果：

25.找出和E4有同样工资的所有职工
命令：
select * from 职工where 工资= (select 工资from 职工where 职工号= 'E4');
结果：

26.找出仓库面积大于400的仓库的所有职工
命令：select * from 职工where 仓库号in (select 仓库号from 仓库where 面积> 400);

结果：

27.找出供应商在西安的职工和他们的工资情况
命令：

结果：

28.找出不在北京仓库里工作的职工
命令：select 职工号, 工资from 职工where 仓库号not in (select 仓库号from 仓库where 城市= '北京');

结果：

29.找出在北京仓库里工作的职工
命令：select 职工号, 工资from 职工where 仓库号in (select 仓库号from 仓库where 城市= '北京');

结果：

30.求广州和上海仓库职工的总工资
命令：select sum(工资) 总工资from 职工where 仓库号in (select 仓库号from 仓库where 城市in('广州', '上海'));

结果：

31.求所有职工工资都大于1210的仓库的平均面积
命令：select avg(面积) from 仓库where 仓库号in (select 仓库号from 职工 group by 仓库号having min(工资) > 1210);

结果：

32.求上海仓库中职工的最高工资
命令：select max(工资) 最高工资from 职工where 仓库号in (select 仓库号from 仓库where 城市= '上海');

结果：

33.订单数大于或等于2的职工的工资
命令：select 工资from 职工where 职工号in (select 职工号from 订购单group by 职工号having count(*) >= 2);
结果：