|
% J8 q# {3 ^9 T( G1 ~! v4 Z 第四关的内容是我期盼已久的，虽然学过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题找出所有首都和其國家名字,而首都要有國家名字中出現。 M# ?5 Z& f( ^ L+ `
SELECT capital,name FROM world WHERE capital LIKE concat(%,name,%); $ T" d6 O7 S b1 B8 k. p
2、第1章：第15题"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville"，顯示國家名字，及其延伸詞，如首都是國家名字的延伸。
, W2 ~1 j4 ^0 n, K8 F0 x& g SELECT name,REPLACE(capital,name, ) FROM world WHERE capital LIKE concat(name,_%); ) l0 S' b. N7 a0 j
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。 ) W: j3 c3 W- o) f( z) z1 [
SELECT name,continent,
" l0 e( }8 y" Y+ E CASE WHEN continent=Oceania THEN Australasia
8 H8 h8 m5 f: H/ S) V4 A9 X2 B- _ WHEN continent=Eurasia THEN Europe/Asia
/ ?) A3 V* q0 c' C( { WHEN name=Turkey THEN Europe/Asia
0 {/ D, P. [" { WHEN continent=Caribbean AND name LIKE B% THEN North America
3 x8 p G2 G4 D WHEN continent=Caribbean AND name NOT LIKE B% THEN South America# P) l" w1 _* A5 d8 Y. P- X7 J5 G
ELSE continent END6 N5 d: b b: i G& |& R
FROM world
! o1 z# V9 |5 @; B7 C* V ORDER BY name;( z( E5 m8 s& P8 k; Q
( o1 ~! U$ a( j3 Z: c6 y
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. / i. M1 j8 I! D" B3 i8 V& T- Y# ^
SELECT winner,subject
4 X( f! ?5 y9 F& |1 z% _ FROM nobel8 c7 s j% G1 ]: ^ C% J
WHERE yr=1984
+ w4 `0 H. h7 _" Y% }- O- ? ORDER BY subject IN (Physics,Chemistry),subject,winner;
H% _6 C& Y& x7 _- I: E. F( K
; }1 H& o! ~3 B& L 5、第4章：第5题顯示歐洲的國家名稱name和每個國家的人口population百分比，以德國的人口的百分比作人口顯示。 ! X1 B5 ?: Q7 ]' m5 G
SELECT name,
/ q/ x: N2 s7 T& P& ~; b# u7 H CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name=Germany),0),%) J4 z( `7 B5 @2 M! ^" T
AS population
3 D4 [& p, b& y FROM world WHERE continent=Europe;3 k" u$ `4 E# l
- Q8 D& y' s- G# A! j
6、第5章：关于nobel表的SUM和COUNT练习第11题列出誰獲得多於一個獎項(Subject)。 9 I. f. D* M, a, j D
SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT(subject))>1; + @; \! q+ N5 i0 [% N7 ~5 R( n( ~
7、第6章：第12题每一場德國GER有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。原题要求列出的是德国的入球次数，所以此处必须添加条件teamid=GER . U2 o3 _2 z1 {. m: e
SELECT matchid,mdate,count(teamid), B# w6 @0 K9 S0 u f
FROM game JOIN goal ON matchid=id C- X% a- T6 y/ s
WHERE (team1 = GER OR team2 = GER) aAND teamid=GER GROUP BY matchid,mdate;1 N4 w* \" @6 c# G
; {0 Z( |1 Z9 f0 C0 ?$ q' z* Z3 V
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. ; b$ l6 ]9 R- v: l8 V# r, A9 X, s* f
SELECT mdate,team1,. y/ t- w: C9 I" f0 A4 ^# o
SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END)6 `& \0 f. z. k8 s6 {7 V1 f L: Q
score1,
2 I$ B5 x4 F$ D1 J$ N team2,$ { N) J6 H- o% j
SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END )
0 Z8 u c' Q+ w+ \* J- A score2! ^8 E! C1 I! b0 R- }# Y
FROM game LEFT JOIN goal ON matchid = id4 W9 V; h6 T9 P' S/ P/ L
GROUP BY mdate, matchid, team1,team2# M: Q; k) a3 \: e4 g3 g4 P
ORDER BY mdate, matchid, team1,team2' W3 k. A% Y1 Q0 b0 L/ r
总结SQLZOO里的题目做了90%，有个别很复杂的我没有做，大概有五六道。经过这一段时间的学习以及最近在公司做的数据分析项目，对SQL的应用，我个人的理解如果不是专门做数据库的人员，不需要掌握太复杂的，但是基本的知识应该反复温习、练习、复习以及应用。
. J/ u2 l) _) {, m 最近做的项目就是根据数据的唯一编号从数据库里取数据，公司的数据库管理员已经写好很多Views，其实我是从Views里取的数据。 用SQL做上海地区出租房屋情况的分析分析需求：
3 N* k$ a$ J. h$ J8 z" z' s 1、上海出租房分布情况：各区域房源数目，平均面积，楼层情况，交通方便情况。在某如网站上爬取的数据，由于网站限制，租金字段爬不下来，只能就分布区域，面积等分析。 3 r6 P+ \5 N% v1 x3 [3 _ ?
2、爬取数据后先在excel里做数据处理、数据清洗，在这里有的字符型数据需要通过“分列”转化为数字型数据。另存为csv格式，然后导入Navicat里。
$ g. G: }6 v' h3 Z* y. }
9 D; u ~& ?* c5 k 3、总数据量。 - B" w5 i' r6 m: B. T" b
; p* e: C# T- j6 \( |* A
4、分析各个区域出租房屋数及占比。 1 v9 v- m- ~2 i- c8 c" Y, F1 k
8 }- g! c+ D) t4 h2 @
5、各个区域出租房屋的平均面积。
" J4 y8 s' u& [! X) Y+ Y; v2 x ' F: i# k' H6 }/ b/ d# u. S8 O
6、出租房屋的层级划分。按整楼高。
# g9 u- u9 S% M6 u5 ~# H : F5 ]* m9 @3 x! \3 ~0 |6 ?
7、距地铁站的距离。 : }8 P2 o; M* L: e4 X8 a3 w
项目总结爬取到的数据量有些太少了，并且感觉很不全面。要想真正提高自己的技术水平，还需要多应用。自己接触的数据分析项目还是比较少，感觉有点无从下手，不知道应该分析哪些东西才是有价值的，现实情况中，做数据分析还需多学习些业务知识，与业务相结合的分析才能分析对方向，才能分析出有价值的东西。
t+ F- k3 ]; W* N6 K( H/ b0 \& Q4 p# r8 y- x
( A9 F1 {3 A, C- ]! b! p' o
) @' B( V5 e* @9 t% k0 o
: s. z, F, v5 }8 T6 P$ C& q( k6 s( r | 
