|
. z3 L2 }- U" [% L$ V6 k+ e' L 第四关的内容是我期盼已久的，虽然学过SQL，但是自己根本没有系统地掌握，也没有将其应用于实践的机会，一直是纸上谈兵。现在公司里用的就是MySQL，客户端用的是Workbench。跟着老师的课程，先在自己电脑上安好了MySQL和Navicat。先开始读书，并做一些读书笔记。 《SQL基础教程》关系数据库必须以行为单位进行数据读写。SQL语句以分号结尾。为了规范语句写法：关键字大写。字符串和日期常数要用单引号括起来，数字直接书写即可。要用半角空格作为单词的分隔符。COUNT 函数的结果根据参数的不同而不同。 COUNT (*) 会得到包含 NULL 的数据行数，而 COUNT (< 列名 >) 会得到 NULL 之外的数据行数。四则运算中如果存在 NULL ，结果一定是 NULL 。聚合函数，如果以列名为参数，那么在计算之前就已经把NULL 排除在外了。聚合函数会将 NULL 排除在外。但 COUNT (*)例外，并不会排除 NULL 。日期、字符串类型的数据能够使用 MAX/MIN 函数，但不能使用 SUM/AVG 函数。MAX / MIN 函数几乎适用于所有数据类型的列。 SUM / AVG 函数只适用于数值类型的列。SELECT → 2FROM → WHERE → GROUP BY→ HAVING→ ORDER BY。只有 SELECT 子句和 HAVING 子句（以及 ORDER BY 子句）中能够使用聚合函数。WHERE 子句 = 指定行所对应的条件，HAVING 子句 = 指定组所对应的条件。内联结中要用ON，ON要在FROM和WHERE之间。内联结只能取出同时存在于两张表中的数据。外联结：RIGHT OUTER JOIN代表右边为主表，LEFT OUTER JOIN代表左边为主表，取出单张表中的全部信息。最近比较忙，我也是刚报老师的班就很幸运的找到工作了，虽然没有相关工作经验，所以在公司里这一段时间也是在天天学各种东西，SQL一时就有点跟不上了。我的工作中现在对SQL技术要求不高，但是还是要自己会取数据的。终于把《SQL基础教程》里老师要求看的章节囫囵吞枣看完了，边看边练习，粗糙的过了一遍。现在开始练习SQL ZOO里的习题。选择一些比较有难度的题记到笔记里。 SQL ZOO练习题笔记1、第1章：第13题找出所有首都和其國家名字,而首都要有國家名字中出現。
2 y5 g* f# F" p5 \# p( c SELECT capital,name FROM world WHERE capital LIKE concat(%,name,%);
- Y7 r7 `' W) C H3 S( k 2、第1章：第15题"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville"，顯示國家名字，及其延伸詞，如首都是國家名字的延伸。 , _, p/ p7 N, F: |3 \8 I0 b
SELECT name,REPLACE(capital,name, ) FROM world WHERE capital LIKE concat(name,_%); % e. i) h0 s1 y0 A* @4 C
3、第2章中文版：第13题Oceania becomes Australasia，Countries in Eurasia and Turkey go to Europe/Asia，Caribbean islands starting with B go to North America, other Caribbean islands go to South America，Show the name, the original continent and the new continent of all countries。 4 v+ G( m; r: b& l
SELECT name,continent,( ^: S$ \+ n% s# @* e1 Y m5 m7 a
CASE WHEN continent=Oceania THEN Australasia/ O2 }. W9 x U$ ^
WHEN continent=Eurasia THEN Europe/Asia
^ |5 h1 Y A J8 L: ] WHEN name=Turkey THEN Europe/Asia
# H" Z+ X% C) g& }0 M { WHEN continent=Caribbean AND name LIKE B% THEN North America
( r/ T. \$ P' f: x" }) R& x WHEN continent=Caribbean AND name NOT LIKE B% THEN South America
" P% p2 q0 m2 x5 s) f8 Z ELSE continent END4 S, J$ R. O" R+ b5 D+ k. v3 W+ h
FROM world9 Z* ~ M4 e2 P/ Z1 y. z7 l, a0 t8 ?
ORDER BY name;( C9 n2 D4 G& m% H2 ?1 @$ n% J
* B3 P6 [/ q. y; u
4、第3章：第14题The expression subject IN (Chemistry,Physics) can be used as a value - it will be 0 or 1.Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
- c" A7 \ h4 w4 x' g j3 n( o SELECT winner,subject2 e# i& f: Y4 b2 z6 ~- S' C' l: C8 ]
FROM nobel
' I& Q W3 T. ]* G5 x' R E WHERE yr=1984
# K, d5 `# G7 E+ R7 R* a ORDER BY subject IN (Physics,Chemistry),subject,winner;
% r! ]* Z; A/ u( @+ m 9 J+ p) ^/ d4 I: [
5、第4章：第5题顯示歐洲的國家名稱name和每個國家的人口population百分比，以德國的人口的百分比作人口顯示。 % c5 g, @8 s. y- _1 g- m
SELECT name,
" o/ I1 Y" Z% D0 C4 [ CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name=Germany),0),%)3 P. b6 c( B$ J, P" _0 \
AS population- A6 g' P4 e* U/ w: h: j
FROM world WHERE continent=Europe;: A7 x2 Q. O$ F6 A
1 u- T) d! |- I [ 6、第5章：关于nobel表的SUM和COUNT练习第11题列出誰獲得多於一個獎項(Subject)。
& I) i, h/ _* A X' f! O% o SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT(subject))>1;
9 w, ^; n: I: S* _6 V# ~ 7、第6章：第12题每一場德國GER有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。原题要求列出的是德国的入球次数，所以此处必须添加条件teamid=GER
! ]2 T7 ]- x1 z! n5 Y# N SELECT matchid,mdate,count(teamid)
$ o) j3 b* E; O+ G# B FROM game JOIN goal ON matchid=id/ w+ e3 K& u% |: k, Q% B- I6 }
WHERE (team1 = GER OR team2 = GER) aAND teamid=GER GROUP BY matchid,mdate;) a4 \$ v. x+ i* l' i# s& E+ z; j
+ w* K, H( k P% B# |$ l9 D# g- A 8、第6章：第13题Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1.Sort your result by mdate, matchid, team1 and team2.
4 A S/ K$ l( ~0 C SELECT mdate,team1,
5 N7 F) T* S' s- D0 r! \ SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END)
/ L- C* n) c# T0 {( y$ [: }4 ] score1,
% P& L3 a. O3 i8 _ team2,6 L5 E& m/ z9 o6 s% x
SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END )6 L% I% f2 D0 q& N" l5 Q8 A' c/ l
score2. C4 l+ M" `! g6 }) X. i# D( o# _/ o
FROM game LEFT JOIN goal ON matchid = id7 ]5 O" \7 K q' k3 y
GROUP BY mdate, matchid, team1,team2& q# o) {* V: o( T: x: ? D* w
ORDER BY mdate, matchid, team1,team2
/ c1 S$ g- q0 N/ ?; n 总结SQLZOO里的题目做了90%，有个别很复杂的我没有做，大概有五六道。经过这一段时间的学习以及最近在公司做的数据分析项目，对SQL的应用，我个人的理解如果不是专门做数据库的人员，不需要掌握太复杂的，但是基本的知识应该反复温习、练习、复习以及应用。 ! j. I, F8 D7 x( I7 g
最近做的项目就是根据数据的唯一编号从数据库里取数据，公司的数据库管理员已经写好很多Views，其实我是从Views里取的数据。 用SQL做上海地区出租房屋情况的分析分析需求： 0 b+ `3 e1 Z* A+ c i
1、上海出租房分布情况：各区域房源数目，平均面积，楼层情况，交通方便情况。在某如网站上爬取的数据，由于网站限制，租金字段爬不下来，只能就分布区域，面积等分析。 & q: J' m r4 C* @% W1 w8 E
2、爬取数据后先在excel里做数据处理、数据清洗，在这里有的字符型数据需要通过“分列”转化为数字型数据。另存为csv格式，然后导入Navicat里。 - ^2 Q' ?4 h2 g* m/ S) P
# D& {9 c9 @0 x n: u$ @
3、总数据量。 3 v# g) a X0 I5 M+ E1 v) ~5 _9 t5 r
( O. w7 N1 S! v4 m V/ ?& L. a
4、分析各个区域出租房屋数及占比。
6 r% t4 N- P2 C8 ?1 C0 |* T
' C# p4 W+ c) s, ^% j 5、各个区域出租房屋的平均面积。 " h& v2 z' F" ] l+ B
, k) k4 i* s, V! E# @/ | 6、出租房屋的层级划分。按整楼高。
9 M. |' l9 _8 L1 l 7 f1 |% I6 K; |- g9 P! \% A
7、距地铁站的距离。 8 |; D8 ^; M: l1 X2 ^
项目总结爬取到的数据量有些太少了，并且感觉很不全面。要想真正提高自己的技术水平，还需要多应用。自己接触的数据分析项目还是比较少，感觉有点无从下手，不知道应该分析哪些东西才是有价值的，现实情况中，做数据分析还需多学习些业务知识，与业务相结合的分析才能分析对方向，才能分析出有价值的东西。
6 U0 f4 P8 n5 o' m, q: B( P4 W; T) |- ?4 E! u2 Y5 L+ d( }5 v
3 X3 W; Z M" { X6 Q% m" K
" [# c4 R! |4 n- T% Q2 }3 I3 ?6 g- s6 ~3 F x/ A# O; a- i
| 
