MySQL Select查询练习

笔记：

严重提醒：注意审题，不要一股脑select。
1.使用group by 后Select 语句字段只能包含分组函数和分组字段 .
例如：

（3）查询总销量最低的商品的商品名和制造商

select gno,fact from goods where gno in
 (select gno from sales
      group by gno having sum(qty)<=all
    (select sum(qty) from sales
            where group by gno));

2.关于exists的理解:
如果内层查询找到这样的一个元组就证明存在，即返回真.

1.查询所有选修了1号课程的学生姓名

select sname 
from student
where exists
(select * from sc where sno=student.sno and cno='1');

解释：

可以在Student中依次取每个元组的Sno值，用此值去检查SC表，若SC表中存在这样的元组，其Sno值等于Student.Sno值,并且其Cno='1',则取此Student.Sname送入结果表。
使用Exists后，若内层查询结果非空,则外层的where字句返回真值，否则返回假值。

查询的查询条件依赖于外层父查询的某个属性值(student中的Sno值),因此也是相关子查询。
3.减法操作: 也就是 不选或者没有 等等词时
例如: 此题中的 查询未销丶查询不生产,或者Student表中的 查询未选择2号课程
用到的只有两种写法：

1.not in
2.not exists

题目：

完成下列查询题目：
已知某数据库系统中包含3个基本表：
商品表:GOODS（G#,GNAME,PRICE,TYPE,FACT） 商品号，商品名，单价，型号，制造商：
商场表：SHOPS(S#,SNAME,MANAG,ADDR)      商场号，商场名，经理，地址；
销售表：SALES(S#,G#,QTY)                商场号,商品号,商品所在商场的销量

其中，G#,GNAME,PRICE,TYPE,FACT，分别代表商品号，商品名，单价，型号，制造商：
S#,SNAME,MANAG,ADDR，分别代表商场号，商场名，经理，地址；
QTY代表销售量。
使用SQL语句完成下列查询：
（1）查询南华厂生产的产品的销售总量
（2）查询电视机在不同商场的销售情况，按销售量降序排列
（3）查询总销量最低的商品的商品名和制造商
（4）找出销量不超过100的且在北京海淀区销售的商品的基本情况
（5）查询不生产电视机的制造商
（6）查询销售所有商品的商场号和商场名
（7）查询未销售南华厂的产品的商场名和经理
（8）查询至少在S02和S03两个商场销售的商品名、型号和制造商
（9）查询每种商品在所有商场的总销售量。输出结果按总销量降序排列
（10）查询平均销售量最高的商品号。

注:这里由于字体问题,将G#换为GNO ,S#换为SNO;
考试时手写请以题目要求为依据,此为反面教材。

Select语句：

已知某数据库系统中包含3个基本表：
商品表:GOODS（G#,GNAME,PRICE,TYPE,FACT） 商品号，商品名，单价，型号，制造商：
商场表：SHOPS(S#,SNAME,MANAG,ADDR)      商场号，商场名，经理，地址；
销售表：SALES(S#,G#,QTY)                商场号,商品号,商品所在商场的销量
注:这里由于字体问题,将G#换为GNO ,S#换为SNO;

（1）查询南华厂生产的产品的销售总量
select sum(qty) from sales where gno in(select gno from goods where fact='南华厂');
//这里注意销售总量是指这个厂生产的，但是 是所有商场里卖的销售总量。用聚合函数sum计算.

（2）查询电视机在不同商场的销售情况，按销售量降序排列
select gname,sname,addr,qty from goods,sales,shops where goods.gno=sales.gno and sales.sno=shops.sno and sname like '%电视机' order by qty desc;

（3）查询总销量最低的商品的商品名和制造商
select gno,fact from goods where gno in (select gno from sales group by gno having sum(qty)<=all(select sum(qty) from sales where group by gno));
//划重点，group by 不需要前面选择，也可以以其为分组条件.

（4）找出销量不超过100的且在北京海淀区销售的商品的基本情况
1.嵌套查询: select * from goods where gno in(select gno from sales where sno in(select sno from shops where addr='北京海淀区') and qty<=100);
2.连接查询: select goods.gno,gname,price,type,fact from goods,shops,sales where goods.gno=sales.gno and sales.sno=shops.sno and qty<=100 and addr='北京海淀区';
//此题在做时只注重了不超过100，结果忘记了地址‘北京海淀区’的事情。注意答全.

（5）查询不生产电视机的制造商
select fact from goods where exists(select * from goods where cname not like '%电视机');
select fact from goods x where not exists (select * from goods y where x.gno=y.gno and y.cname like '%电视机');
（6）查询销售所有商品的商场号和商场名
1.select sno,sname from shops where not exists(select * from goods where not exists (select * from sales where sales.sno=shops.sno and sales.gno=goods.gno));
//在sales表中有完整全部的商品号.

（7）查询未销售南华厂的产品的商场名和经理
1.select sname,manag from shops where sno not in (select sno from sales where gno in(select gno from goods where fact='南华厂'));
//not in需要匹配sno,而not exists不需要,直接select *;
2.select sname,manag from shops where not exists(select * from sale where sales.sno=shops.sno where gno in(select * from goods where fact='南华厂'));
3.select sname,manag from shops where not exists (select * from sales,goods where sales.gno and goods.gno and sales.sno=shops.sno and fact='南华厂');
//这里的内层中的能和外层连接
（8）查询至少在S02和S03两个商场销售的商品名、型号和制造商
select gname,type,fact from goods where gno in(select gno from sales where sno='S02' and gno in(select gno from sales where sno='S03'));

（9）查询每种商品在所有商场的总销售量。输出结果按总销量降序排列
1.连接查询: select good.gno,sum(qty) from goods,sales where goods.gno=sales.gno group by goods.sno order by sum(qty) desc;
2.嵌套查询: select good.gno,sum(qty) from goods,sales where goods.gno=sales.gno group by gno order by sum(qty) desc;

（10）查询平均销售量最高的商品号。
select gno from goods where gno in(select gno from sales group by gno having avg(qty)<=all (select avg(qty) from sales group by gno));
//group by不需要前面选择