|
0 y+ x V7 k) K( C
第四关的内容是我期盼已久的，虽然学过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, X* ]0 K7 C: ?, H, e& L1 ^ SELECT capital,name FROM world WHERE capital LIKE concat(%,name,%);
" T7 G! \# }" \ 2、第1章：第15题"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville"，顯示國家名字，及其延伸詞，如首都是國家名字的延伸。
- G" S2 W8 g6 Z o: q# H SELECT name,REPLACE(capital,name, ) FROM world WHERE capital LIKE concat(name,_%);
/ @6 m# Z( T# U& m1 ~, O 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。
2 U; A# U- J3 Y$ R1 T Y6 V SELECT name,continent,; b5 k7 d v @$ Z- t1 p* ^5 Z, s9 _% |
CASE WHEN continent=Oceania THEN Australasia
. K2 \1 `: Y$ R3 p8 b1 g. r WHEN continent=Eurasia THEN Europe/Asia& Q1 D0 |) K1 c7 C8 T
WHEN name=Turkey THEN Europe/Asia) [& o" r( [% w# T# D
WHEN continent=Caribbean AND name LIKE B% THEN North America6 T. Q( ^; V% C$ I) Q. n' @( b
WHEN continent=Caribbean AND name NOT LIKE B% THEN South America- H3 }, C- |' g G
ELSE continent END5 p1 C6 q2 K, D" i) |9 n
FROM world) _' ]6 d* z0 g+ l1 |, _
ORDER BY name;9 [2 S4 a% R \- T: s
5 Y7 r: M8 _, V: N
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; e# ~, L% I SELECT winner,subject
" j3 ^% U8 |% b FROM nobel
& ]5 E$ O' u) }) T+ ? WHERE yr=1984- u( k) t3 `/ P0 |8 ~' n
ORDER BY subject IN (Physics,Chemistry),subject,winner;
. d/ T5 l+ R9 p. i$ z, _
+ l. d5 N6 r# w/ u+ P0 _7 a5 x 5、第4章：第5题顯示歐洲的國家名稱name和每個國家的人口population百分比，以德國的人口的百分比作人口顯示。
1 _3 v: q& G" J% Q+ a3 _9 e6 [3 i SELECT name,
2 R1 P' \/ `$ `' Q8 m- M% e1 U CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name=Germany),0),%)& D+ Y2 s+ i. y8 |& }* p) `5 K
AS population
6 B9 n( I. w% d1 S* ` FROM world WHERE continent=Europe;& v& d9 z6 S8 }5 k, q4 L( v
B, l. g: A; g! R2 n
6、第5章：关于nobel表的SUM和COUNT练习第11题列出誰獲得多於一個獎項(Subject)。 - ~) _3 g1 d" p" q0 k3 s& r
SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT(subject))>1;
' T: |5 ]* | p 7、第6章：第12题每一場德國GER有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。原题要求列出的是德国的入球次数，所以此处必须添加条件teamid=GER
! ], A! [) D t- N" T* I! G* [ SELECT matchid,mdate,count(teamid)8 d: _: W7 J0 s& k+ B7 [2 q8 t
FROM game JOIN goal ON matchid=id# G0 T# h/ F2 }) W# g4 t& D
WHERE (team1 = GER OR team2 = GER) aAND teamid=GER GROUP BY matchid,mdate;
) Z& w9 o& O+ k- e% Y
1 j% o. c+ `& X1 T6 A' k' ~ 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.
# K% u2 T4 a% c- M. { SELECT mdate,team1,- u5 C R' k# a l' ?
SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END)
0 r- W9 p' c V# C, |% C) x- k score1,$ A- @" Z x* A' z" z0 K, \
team2,# g- |, L0 D9 j5 Q( [0 X) A. Q# d. i
SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END )$ B; P: @2 ^+ t' D4 S4 s
score2) Q( H% {+ d$ i( I: H- q- [' u
FROM game LEFT JOIN goal ON matchid = id/ L( c* n/ q$ h/ s; F! ?2 ?" M
GROUP BY mdate, matchid, team1,team2. l7 S) F$ B8 I4 o- t6 F
ORDER BY mdate, matchid, team1,team2: u* f% C7 t3 N, w+ z( c
总结SQLZOO里的题目做了90%，有个别很复杂的我没有做，大概有五六道。经过这一段时间的学习以及最近在公司做的数据分析项目，对SQL的应用，我个人的理解如果不是专门做数据库的人员，不需要掌握太复杂的，但是基本的知识应该反复温习、练习、复习以及应用。
5 r" v! U1 w7 n; Q 最近做的项目就是根据数据的唯一编号从数据库里取数据，公司的数据库管理员已经写好很多Views，其实我是从Views里取的数据。 用SQL做上海地区出租房屋情况的分析分析需求：
# ~3 S- m2 A* i4 |4 [8 {8 U+ v 1、上海出租房分布情况：各区域房源数目，平均面积，楼层情况，交通方便情况。在某如网站上爬取的数据，由于网站限制，租金字段爬不下来，只能就分布区域，面积等分析。 4 G4 E1 m8 v& R# i* I8 ]% T
2、爬取数据后先在excel里做数据处理、数据清洗，在这里有的字符型数据需要通过“分列”转化为数字型数据。另存为csv格式，然后导入Navicat里。
4 n7 w- K7 H1 Q7 k2 K9 d6 @
: P% u! Q. @( N 3、总数据量。
% [; o; e# Z# C. a5 C* L
/ w: W+ ]: V, J6 a- S+ X 4、分析各个区域出租房屋数及占比。 # V" ~! Y/ H6 @: |" g+ K
3 H5 Y$ {5 l; H$ k' w& v( j7 p: f 5、各个区域出租房屋的平均面积。
0 i6 }) ]# Z; ^4 k2 M0 b
3 t$ W: y% o; P. g 6、出租房屋的层级划分。按整楼高。 7 S/ {8 W7 v) M) i
3 h# j( t; ?8 T6 B, E5 ` 7、距地铁站的距离。 5 @. V5 x9 l1 e+ k8 N+ w3 Q6 Q, ?
项目总结爬取到的数据量有些太少了，并且感觉很不全面。要想真正提高自己的技术水平，还需要多应用。自己接触的数据分析项目还是比较少，感觉有点无从下手，不知道应该分析哪些东西才是有价值的，现实情况中，做数据分析还需多学习些业务知识，与业务相结合的分析才能分析对方向，才能分析出有价值的东西。
8 F1 [% h7 v5 q; q( g$ `# L) t5 w) L+ k- S: M
5 s9 s- l T* M& X8 [$ b. I; @0 ~- e& ~' ^8 P, [1 R+ G1 W& [
) C- G" Z- B$ q5 `
| 
