|
7 D7 J/ }9 ], d7 y+ ~ 第四关的内容是我期盼已久的，虽然学过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题找出所有首都和其國家名字,而首都要有國家名字中出現。 " p& m; j( w" D& F6 V/ A& Z7 E( D
SELECT capital,name FROM world WHERE capital LIKE concat(%,name,%);
8 H0 U6 F1 R8 l! y. p/ ]7 Q 2、第1章：第15题"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville"，顯示國家名字，及其延伸詞，如首都是國家名字的延伸。 1 N9 h: ]1 R; t$ E* o, i& c
SELECT name,REPLACE(capital,name, ) FROM world WHERE capital LIKE concat(name,_%); ) Y6 j1 Z o, N; X
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。 8 n6 P, d( U6 Q* g6 b( F5 d
SELECT name,continent,
+ L. ?* }9 w5 w z, }* ~ CASE WHEN continent=Oceania THEN Australasia
) z3 |7 E& g* W# s3 ]( J WHEN continent=Eurasia THEN Europe/Asia
% M) [7 I' t! v1 s5 q9 D WHEN name=Turkey THEN Europe/Asia
2 e# O, M) _$ R4 L! D0 G WHEN continent=Caribbean AND name LIKE B% THEN North America& ~7 o( G0 s5 V3 c6 {* V
WHEN continent=Caribbean AND name NOT LIKE B% THEN South America
& `- x7 d/ i7 |" }/ i8 b7 P& k s ELSE continent END- D" L @1 t$ A @
FROM world/ ]+ N# R# w9 c, @3 e
ORDER BY name;
& ^4 ?3 x! Z4 x2 p0 f W
4 v/ T3 e7 Q+ {7 P% L W 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. 2 h0 _7 v, y8 @6 J# o+ U+ Y, u
SELECT winner,subject
* M1 c, { x$ k) E1 V FROM nobel
( Q+ o6 f, V* N2 }8 g WHERE yr=1984, h* c3 G; h2 e* ^% d9 e; R
ORDER BY subject IN (Physics,Chemistry),subject,winner;9 G( G* n/ _5 A3 U% ]8 |& }% h
. U+ d8 `$ t" S9 j) ~8 w/ ]7 q
5、第4章：第5题顯示歐洲的國家名稱name和每個國家的人口population百分比，以德國的人口的百分比作人口顯示。 - d) K* |- `7 j0 G9 S/ O5 `' E
SELECT name,
! p( U' E. s0 [$ B' j) x CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name=Germany),0),%)! r$ V. B/ t! l6 S; B1 i+ ^
AS population
& x# p' V$ J0 H FROM world WHERE continent=Europe;
6 Q* K5 x0 |. r/ q7 r
4 z% m8 D+ d. [5 c( g! L' ~) @ 6、第5章：关于nobel表的SUM和COUNT练习第11题列出誰獲得多於一個獎項(Subject)。
& ~9 o3 X( B. c( b1 h SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT(subject))>1; 2 B$ {) o! H8 t* c8 @) p5 y
7、第6章：第12题每一場德國GER有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。原题要求列出的是德国的入球次数，所以此处必须添加条件teamid=GER , G* `% v8 B3 `, b
SELECT matchid,mdate,count(teamid)
4 b4 x2 c9 m) w9 D7 U7 P FROM game JOIN goal ON matchid=id% e2 V/ g1 b. p+ N
WHERE (team1 = GER OR team2 = GER) aAND teamid=GER GROUP BY matchid,mdate;
6 U! ~' w4 }& i _& l
+ R# Z1 ]2 F, i, I | 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. 0 F% S1 R9 s& ?2 d- `% _
SELECT mdate,team1,. h; h( O1 `6 p* T4 ], B
SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END)
6 p0 ^5 Q& p1 [/ z5 Z" h# b* X score1,
0 ]0 C0 ~3 E8 p* a0 O. G% k team2,
# Q: Z* U& }; x' e1 x4 L& Z1 [ SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END )
# z1 `9 N `: B+ p# Q* \, o score2' q* e! k2 x) s! G. o3 Z2 R3 M
FROM game LEFT JOIN goal ON matchid = id8 @& c( i( R7 J0 o# m1 C
GROUP BY mdate, matchid, team1,team2 `& m6 e7 a, u0 M, u: A
ORDER BY mdate, matchid, team1,team2 i% A- M& o0 U
总结SQLZOO里的题目做了90%，有个别很复杂的我没有做，大概有五六道。经过这一段时间的学习以及最近在公司做的数据分析项目，对SQL的应用，我个人的理解如果不是专门做数据库的人员，不需要掌握太复杂的，但是基本的知识应该反复温习、练习、复习以及应用。
" V# n0 m& u2 X6 M0 M7 o$ e 最近做的项目就是根据数据的唯一编号从数据库里取数据，公司的数据库管理员已经写好很多Views，其实我是从Views里取的数据。 用SQL做上海地区出租房屋情况的分析分析需求： - \4 Y! w ?. A1 r# N
1、上海出租房分布情况：各区域房源数目，平均面积，楼层情况，交通方便情况。在某如网站上爬取的数据，由于网站限制，租金字段爬不下来，只能就分布区域，面积等分析。 , v4 L2 |. @4 n$ u5 e4 L4 ]
2、爬取数据后先在excel里做数据处理、数据清洗，在这里有的字符型数据需要通过“分列”转化为数字型数据。另存为csv格式，然后导入Navicat里。
) n) z7 R0 p# W0 E5 u
! N- g0 m- _5 ?: X 3、总数据量。 . s8 a0 s6 g- n% a
2 o. x& k; R. U; F! z 4、分析各个区域出租房屋数及占比。 0 B! Z' s, o: P0 M0 h
$ K8 ^# A2 F- n( y6 P, o8 D# M
5、各个区域出租房屋的平均面积。
/ t. Q0 g) ]8 l* L- B& ? 5 R9 L8 }6 T- ` G; R+ P
6、出租房屋的层级划分。按整楼高。 9 E2 U8 R+ ?; e( `% M4 s% ~9 ]1 Z
' C' _7 x- l1 {0 ?5 r3 a+ {
7、距地铁站的距离。
5 |( Q6 c- y( a) {1 C6 n8 [ 项目总结爬取到的数据量有些太少了，并且感觉很不全面。要想真正提高自己的技术水平，还需要多应用。自己接触的数据分析项目还是比较少，感觉有点无从下手，不知道应该分析哪些东西才是有价值的，现实情况中，做数据分析还需多学习些业务知识，与业务相结合的分析才能分析对方向，才能分析出有价值的东西。
0 _/ _6 {% C# o8 B$ j7 M6 n" K- y# Q! _
; q) S" x4 g4 b) S, W( q! _( |0 N, n w6 T
/ X# x+ f% \; X7 V5 a9 O, c
| 
