学习数据库笔记六

一

总结了些解题模板，一些情况下可以套用

模型一  group by + 统计函数组合

例
求每个栏目下商品的数量
selct cat_id,count(*) from goods group by cat_id;

思路1.每个后面的用group by来分组，
    2.xx的数量、平均分、最高分用统计函数来求


其它类似的还有
每个班级。。。平均分     avg(score) ... group by class
每个部门。。。最高工资   max(gongzi) ... group by dept
每个小组。。。最低分     min(score) ... group by xiaozu

加上limit还可以取出前几名

模型二   结果集 + 和结果集比较

例
查出比市场价格便宜200块以上的商品
select goods_id,goods_name,(market_price-shop_price)as sheng from goods having sheng>=200;

思路1.比xx便宜多少，用加减算出结果集
    2.便宜的结果集中，有哪些是大于200的

其它类似的还有
比。。。便宜100以上  (a_price-b_price) as sheng  ...  sheng>100
比。。。贵2000以上   (a_price-b_price) as gui  ...  gui>2000
比。。。低50分以上   (a_score-b_score) as di  ...  di>50

因为用到了结果集，所以要用having取

模型三   order by + limit

例
取出价格最高(倒序排列)的前三名(limit)商品
selct * from goods order by shop_price desc limit 0,3;

思路1.价格最高，把价格倒序排序
    2.前三名，用limit截取3个


其它类似的还有
取出学习最好的前三名
select * from student order by score desc limit 0,3;  

模型四    结果集 + group by + 结果集比较

例
查出2门及2门以上不及格者的平均分
select name,sum(score<60) as gk,avg(score) as pj from result group by name having gk>=2;



思路1.查出xxx的平均分（group by + 统计函数组合 模板一）
    2.x门以上不及格者（结果集 + 和结果集比较 模板二）

    
    步骤1.select avg(score) from result group by name;
    步骤2.1.select sum(score<60) as gk from result group by name; 根据名字分组，求出挂科总门数
    步骤2.2 select sum(score<60) as gk from result group by name having gk>=2;

    把它们结合起来就是
    select name,sum(score<60) as gk,avg(score) as pj from result group by name having gk>=2;



模板四是模型一和二的结合



二

网上的面试题及解决思路

1.用一条SQL语句 查询出每门课都大于80分的学生姓名

name   kecheng   fenshu
张三     语文       81
张三     数学       75
李四     语文       76
李四     数学       90
王五     语文       81
王五     数学       100
王五     英语       90

解
SELECT name,`subject`,score,sum(score>80) as jg from result GROUP BY `name` HAVING jg=(SELECT COUNT(DISTINCT rs.subject) from result rs );

求出共几门课select COUNT(DISTINCT `subject`) from result;

2.
自动编号   学号   姓名 课程编号 课程名称 分数
1        2005001  张三  0001      数学    69
2        2005002  李四  0001      数学    89
3        2005001  张三  0001      数学    69

删除除了自动编号不同,其他都相同的学生冗余信息


解
CREATE TABLE tmp1 as
    SELECT s.code FROM student s GROUP BY
    s.`name`,s.kname,s.scode,s.kcode,s.score;

DELETE FROM student WHERE CODE not in (SELECT CODE FROM tmp1);

drop TABLE tmp1;


删除冗余信息，其实超级简单，用分组来解决
把字段挨着分组，只要有内容不一样的就不会被分到一组。
如果都在一条语句中写，需要建立一个临时表来放



思路：

查出冗余信息的编号是很容易的：
select code from student s group by s.name,s.kname,s.scode,s.kcode,s.score;这
这些是没有冗余的编号，冗余的只要not in一下这个语句就能得出

但是，删除语句是不能删除以上结果集的内容的（结果集的内容在内存中不在硬盘中）

所以需要建立一个临时表，就是上面的tmp1，把查出的内容放在临时表里，使用完再删除掉临时表。

3.一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.

SELECT * FROM
department INNER JOIN department as a
ON
department.`name` <> (a.`name`)