學習資料庫筆記六，資料庫筆記

學習資料庫筆記六，資料庫筆記

一

總結了些解題模板，一些情況下可以套用

模型一  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`)