|
4 ~8 ?3 G8 v9 W 第四关的内容是我期盼已久的，虽然学过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题找出所有首都和其國家名字,而首都要有國家名字中出現。
3 M3 g1 H; `0 ? SELECT capital,name FROM world WHERE capital LIKE concat(%,name,%);
: ~. }" U, o3 s G6 z7 d4 P% A" U 2、第1章：第15题"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville"，顯示國家名字，及其延伸詞，如首都是國家名字的延伸。
* J: |9 V$ M% q7 J: E& l' U SELECT name,REPLACE(capital,name, ) FROM world WHERE capital LIKE concat(name,_%);
( L! |6 Y# g0 `5 J4 n3 H+ \* E9 F' F 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。
7 n; X& q9 p' l2 k: U, ?- F7 o SELECT name,continent,
- O$ F! T! o0 ~2 e) C+ e3 Z CASE WHEN continent=Oceania THEN Australasia
; F& M9 j/ i! z4 p8 d2 ^ WHEN continent=Eurasia THEN Europe/Asia, M9 H& U# V6 p( D2 @7 F, Y% ?
WHEN name=Turkey THEN Europe/Asia" y4 N2 I( |5 ~8 D1 j
WHEN continent=Caribbean AND name LIKE B% THEN North America$ A% u {1 r- I2 t
WHEN continent=Caribbean AND name NOT LIKE B% THEN South America
; e0 y1 Z) ~* l6 t7 z. }+ W" z B ELSE continent END: _, @7 s/ x7 u! m
FROM world
# V2 S1 ]4 F2 W% J: K: h7 _5 {' V ORDER BY name;* k+ |) w8 \4 @( {7 v
' i( _ k7 d* }( F
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.
# A# D+ D8 q8 F SELECT winner,subject
4 _0 p9 H, g' G; r" G FROM nobel
3 p" u; h4 s9 u) K. \) A/ O9 K WHERE yr=1984
0 W7 g! t4 f2 e8 o/ ]7 c5 }) k ORDER BY subject IN (Physics,Chemistry),subject,winner;
# Z! v6 \1 O9 g
/ ?0 p4 z; u o( _0 N7 n# n! ^) p0 J" Y 5、第4章：第5题顯示歐洲的國家名稱name和每個國家的人口population百分比，以德國的人口的百分比作人口顯示。
' m$ R, v* d8 V) ~/ g SELECT name,
& R% L/ i Z0 B2 w, P5 G8 Z4 i CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name=Germany),0),%)
5 Q9 x3 A) w/ U K AS population
" A( m" n8 ]" _- M. c% U FROM world WHERE continent=Europe;
7 a0 x$ z5 y- j4 T! ?& K
' a2 A6 |& {2 u* n# L 6、第5章：关于nobel表的SUM和COUNT练习第11题列出誰獲得多於一個獎項(Subject)。 ?" l5 T/ U3 Y
SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT(subject))>1;
~- h# f" f" b6 q# K0 w) J. O 7、第6章：第12题每一場德國GER有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。原题要求列出的是德国的入球次数，所以此处必须添加条件teamid=GER 2 h1 a) H* ]- H, O/ P
SELECT matchid,mdate,count(teamid)
( |8 X' t) T! j( z3 E FROM game JOIN goal ON matchid=id! F9 a0 [% V0 d7 q0 \# X- s* E
WHERE (team1 = GER OR team2 = GER) aAND teamid=GER GROUP BY matchid,mdate;' j* T/ L! Y' P( g7 R
5 t* i4 F. w! B6 c6 \$ @/ n$ z6 [ 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.
/ ` f1 K" C6 _# v o- \ SELECT mdate,team1,
, Q- C4 }+ |, l% X6 L2 P S! e+ K1 U; h SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END)
" u! {" B5 L6 D' M/ m+ g' b1 D4 w; o score1,& {) l m' J( r: t! k
team2,
, a% V* g6 `: q: `7 v* J+ v4 m SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END )! y( c% Z- [1 D7 ?4 M
score2% \* d4 o& s6 k: r* B
FROM game LEFT JOIN goal ON matchid = id
: C( S# t9 I6 ?7 g GROUP BY mdate, matchid, team1,team2
5 C {: h" O% Q! S3 d" F ORDER BY mdate, matchid, team1,team2. R8 }. Q" r* |4 x
总结SQLZOO里的题目做了90%，有个别很复杂的我没有做，大概有五六道。经过这一段时间的学习以及最近在公司做的数据分析项目，对SQL的应用，我个人的理解如果不是专门做数据库的人员，不需要掌握太复杂的，但是基本的知识应该反复温习、练习、复习以及应用。
- m: g3 |9 ~1 ^- R1 J k 最近做的项目就是根据数据的唯一编号从数据库里取数据，公司的数据库管理员已经写好很多Views，其实我是从Views里取的数据。 用SQL做上海地区出租房屋情况的分析分析需求：
$ @! {0 W- z! M: Y 1、上海出租房分布情况：各区域房源数目，平均面积，楼层情况，交通方便情况。在某如网站上爬取的数据，由于网站限制，租金字段爬不下来，只能就分布区域，面积等分析。
* S% E1 w/ F) v" D& d 2、爬取数据后先在excel里做数据处理、数据清洗，在这里有的字符型数据需要通过“分列”转化为数字型数据。另存为csv格式，然后导入Navicat里。 6 z% u: k! N" W0 L5 D
1 G& c* H% A+ V2 n 3、总数据量。 ( ?5 ^$ H% W3 M9 a& o+ L
+ U4 ^$ g3 }) }9 p/ y9 j. g6 c
4、分析各个区域出租房屋数及占比。
) _, p2 {7 C; s2 c
2 `1 y( i# U) \) R ] 5、各个区域出租房屋的平均面积。
8 e6 H0 {# O* z0 _# w! T4 K6 q6 R+ _ : D% M" [5 j4 o4 S0 s: A( F3 X
6、出租房屋的层级划分。按整楼高。
% Z% ?8 c) P% Q V+ u ]% G; K& }- V+ f0 ]' g% H9 K
7、距地铁站的距离。 6 h/ a6 l4 ~' v
项目总结爬取到的数据量有些太少了，并且感觉很不全面。要想真正提高自己的技术水平，还需要多应用。自己接触的数据分析项目还是比较少，感觉有点无从下手，不知道应该分析哪些东西才是有价值的，现实情况中，做数据分析还需多学习些业务知识，与业务相结合的分析才能分析对方向，才能分析出有价值的东西。 ^& j# D" z [8 k3 o
2 a4 N) X- [5 x' h9 X$ a/ ^0 N6 E
" |+ {" A+ Y) _) y* i& B9 w$ ~) Y9 ~) z7 ~5 M3 { N @
# Z8 F X6 a# s1 U7 N% t | 
