|
+ T9 E8 Z- ^. h/ `% N- \8 h$ u
第四关的内容是我期盼已久的，虽然学过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题找出所有首都和其國家名字,而首都要有國家名字中出現。 % Q& z$ u2 t2 e- c4 G
SELECT capital,name FROM world WHERE capital LIKE concat(%,name,%);
& _( v; R8 d9 \7 m 2、第1章：第15题"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville"，顯示國家名字，及其延伸詞，如首都是國家名字的延伸。
. ~$ Y( L% d7 e7 k4 |2 i SELECT name,REPLACE(capital,name, ) FROM world WHERE capital LIKE concat(name,_%);
: j# D. ?3 @4 z 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。
/ d( v P0 X% W; b SELECT name,continent,3 K- n+ C f/ J5 d2 c
CASE WHEN continent=Oceania THEN Australasia
: I; z" d% d! ]# W+ B WHEN continent=Eurasia THEN Europe/Asia
, D1 [$ u/ P Y2 V+ }' O WHEN name=Turkey THEN Europe/Asia
c( S6 b, J0 X7 `9 Y9 L WHEN continent=Caribbean AND name LIKE B% THEN North America$ p% ^8 f! `* i& \9 ?6 A
WHEN continent=Caribbean AND name NOT LIKE B% THEN South America0 _6 _/ X0 n+ A' s" g3 s @$ f
ELSE continent END
h/ N) Q9 {9 E. F2 z! h _! {, H FROM world
. A/ ~: C* s1 h# X, g3 B ORDER BY name;" N* ]8 h; l, U% h* W* X1 T
. p( r, G' ]! R" q
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. - v! D. N' B& L1 N- j% N6 L0 f% Q* h' c0 a
SELECT winner,subject0 k. k! e1 `1 i' B+ u# T9 R! g- l
FROM nobel' f& f! w# _7 b8 r& U' q
WHERE yr=1984) ^1 o# {$ }( H! i& v
ORDER BY subject IN (Physics,Chemistry),subject,winner;
0 r1 d' Q4 ] c7 ]9 s* m4 c
4 R3 H6 @* D1 u( Y ^3 |& l3 g 5、第4章：第5题顯示歐洲的國家名稱name和每個國家的人口population百分比，以德國的人口的百分比作人口顯示。 / r5 W4 k+ a" Q# u1 {% k5 J
SELECT name,/ y2 X9 D$ W3 e1 K
CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name=Germany),0),%)1 A3 z4 [! A7 ]+ V" j) o; s; u
AS population( y6 I* e: m" A& A+ e
FROM world WHERE continent=Europe;
' @% ?) `+ e) o3 {" Z. j# J& g6 } , h% a& [/ }) O7 A& h6 i
6、第5章：关于nobel表的SUM和COUNT练习第11题列出誰獲得多於一個獎項(Subject)。 % L c0 a, W0 e7 n5 j+ I9 J
SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT(subject))>1; 4 T @/ y/ A' j1 l" }
7、第6章：第12题每一場德國GER有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。原题要求列出的是德国的入球次数，所以此处必须添加条件teamid=GER 4 i+ V. c f* u
SELECT matchid,mdate,count(teamid)% A4 G! _" _5 B
FROM game JOIN goal ON matchid=id3 w0 k3 p) _. t$ q; R
WHERE (team1 = GER OR team2 = GER) aAND teamid=GER GROUP BY matchid,mdate;
0 \9 R* z$ s: @* A5 F- j' E
- E! m9 ?7 ?# c) i4 q: K+ Q 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.
& R; l1 ^' J9 a( M$ D* [ SELECT mdate,team1,
0 T0 C% o) \' @" y J+ Q SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END)
) O/ C6 w3 f8 C0 L score1,
) ]7 k/ D- l7 N team2,& `' h# H' h [% B; u$ g! h
SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END )
$ J* N5 v/ x. Y: `1 M/ o score2
; b( `& M2 l" o% k. Y FROM game LEFT JOIN goal ON matchid = id- x( H) T7 q$ t7 e: u) T
GROUP BY mdate, matchid, team1,team2
0 b3 z/ _7 X# s$ s* T ORDER BY mdate, matchid, team1,team2
8 Q1 Q$ p& t: q; A! L2 f- v4 b/ X 总结SQLZOO里的题目做了90%，有个别很复杂的我没有做，大概有五六道。经过这一段时间的学习以及最近在公司做的数据分析项目，对SQL的应用，我个人的理解如果不是专门做数据库的人员，不需要掌握太复杂的，但是基本的知识应该反复温习、练习、复习以及应用。 # ^3 E& a1 r' j
最近做的项目就是根据数据的唯一编号从数据库里取数据，公司的数据库管理员已经写好很多Views，其实我是从Views里取的数据。 用SQL做上海地区出租房屋情况的分析分析需求：
" O( h1 z0 H0 x3 v3 E. q3 ~* h 1、上海出租房分布情况：各区域房源数目，平均面积，楼层情况，交通方便情况。在某如网站上爬取的数据，由于网站限制，租金字段爬不下来，只能就分布区域，面积等分析。 - F7 X3 i$ ~" i X' k
2、爬取数据后先在excel里做数据处理、数据清洗，在这里有的字符型数据需要通过“分列”转化为数字型数据。另存为csv格式，然后导入Navicat里。 " g3 ~' c# O% N) @! `
0 n8 i& _$ X4 ~) F2 ^' ~* h# ^
3、总数据量。 9 l G I# `& @' v$ [7 h
8 l* r' t- `; y: a
4、分析各个区域出租房屋数及占比。
3 |0 d' D( V! ~8 G- d, C; [ 3 z+ u5 ? T0 N- g# B' L7 L
5、各个区域出租房屋的平均面积。
' b/ M; i8 [* P% K% ` 8 B4 K% j) h% K' [3 V2 r
6、出租房屋的层级划分。按整楼高。
/ W. u3 H( @. o; r- ~7 K
' ^, C. z. E' d1 ]( v$ P. w 7、距地铁站的距离。 - g/ w+ }5 z e$ h2 m- F7 H; X
项目总结爬取到的数据量有些太少了，并且感觉很不全面。要想真正提高自己的技术水平，还需要多应用。自己接触的数据分析项目还是比较少，感觉有点无从下手，不知道应该分析哪些东西才是有价值的，现实情况中，做数据分析还需多学习些业务知识，与业务相结合的分析才能分析对方向，才能分析出有价值的东西。
, t" A9 J+ N& R$ _- ^1 P9 i+ W9 @6 x2 d' x: O+ K4 C) L. O3 V
$ i$ [7 A. ?1 l; s4 `
3 k9 D: I [8 E" v" o9 I$ w1 N4 j, Y- k
| 
