, i' S( I$ L* i4 \! A% f3 `& e
第四关的内容是我期盼已久的,虽然学过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题找出所有首都和其國家名字,而首都要有國家名字中出現。 4 o) ~. B! B" Z/ g4 z) W2 o
SELECT capital,name FROM world WHERE capital LIKE concat(%,name,%); ! h; P( g8 I: z6 C3 l
2、第1章:第15题"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville",顯示國家名字,及其延伸詞,如首都是國家名字的延伸。
4 z% ~) p/ a3 W) k1 r SELECT name,REPLACE(capital,name, ) FROM world WHERE capital LIKE concat(name,_%);
& b1 I7 M! `" z, N 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。
& p7 f$ g4 @% b SELECT name,continent,; Z, Y! b5 I& g( P3 h; T' g
CASE WHEN continent=Oceania THEN Australasia, w+ _1 J* h; Y4 q9 B
WHEN continent=Eurasia THEN Europe/Asia7 j: N2 }* Y+ _& p4 }: `# L1 {2 L
WHEN name=Turkey THEN Europe/Asia
7 A% r9 M% v8 B9 x WHEN continent=Caribbean AND name LIKE B% THEN North America
8 h; N, _+ w i7 G, T d: M3 z WHEN continent=Caribbean AND name NOT LIKE B% THEN South America
" {: X$ ?$ {0 g- m4 E ELSE continent END* ?# s8 K! c2 E" H2 x; f
FROM world
5 t; R/ t% G6 m ORDER BY name;
/ g8 P8 i/ ~$ F1 Z+ E) C # t9 [) L7 |% }/ {+ A5 M1 X) p
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.
5 m( l9 @' r' t2 O9 Y SELECT winner,subject6 f: R; Y2 O$ |& k
FROM nobel/ L7 _- o" H. b
WHERE yr=1984
. _. V: L$ l1 z6 w ORDER BY subject IN (Physics,Chemistry),subject,winner;5 X6 A" ~4 t4 W# ]5 }* _- P1 M
# I2 _) P# n2 r1 T' S y; R
5、第4章:第5题顯示歐洲的國家名稱name和每個國家的人口population百分比,以德國的人口的百分比作人口顯示。 . a% N6 c9 G( N5 W5 a8 o% h
SELECT name,
C6 x0 R9 g' f6 K k2 P CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name=Germany),0),%)0 p% i8 @8 d. ]; P' s$ J+ p* U9 ]
AS population
& X, [/ R* v, x4 \3 X FROM world WHERE continent=Europe;7 ]9 A, D; X- A; F
% x( v1 b* j- w' {8 z 6、第5章:关于nobel表的SUM和COUNT练习第11题列出誰獲得多於一個獎項(Subject)。
2 S @& z( ]4 E# H SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT(subject))>1; 5 H+ Z! d' f/ Y; t! L
7、第6章:第12题每一場德國GER有參與的賽事中,列出賽事編號 matchid, 日期date 和德國的入球數字。原题要求列出的是德国的入球次数,所以此处必须添加条件teamid=GER
( ]! Y- Z/ t) v, v& T SELECT matchid,mdate,count(teamid)
( n3 H1 C/ u( N2 n$ Z, k6 G FROM game JOIN goal ON matchid=id- w, Y$ D& Z, V# M( ?
WHERE (team1 = GER OR team2 = GER) aAND teamid=GER GROUP BY matchid,mdate;! I3 ~& L2 G2 E/ d
9 Q/ o3 R; T$ @ r3 ~ 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. 3 W( U# b; Z& ?, Z6 R1 {0 c% ]" |
SELECT mdate,team1,) o+ ?: m; l% c
SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END)" H" ~! F) o' L; |
score1,9 c1 \! K g( a- U+ Q: Z' T
team2,$ g9 S- e1 d" r( a: E6 R z
SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END ); P& S# _: W7 h4 Q4 f6 t: v9 \
score2) I3 \; P9 Z2 g& _1 F2 E
FROM game LEFT JOIN goal ON matchid = id
" P0 i# L! v7 v! L% Z* j6 L; H( P GROUP BY mdate, matchid, team1,team2
$ E* \$ S, c1 f& E0 A& e. W ORDER BY mdate, matchid, team1,team2
. z- D$ q4 w! r 总结SQLZOO里的题目做了90%,有个别很复杂的我没有做,大概有五六道。经过这一段时间的学习以及最近在公司做的数据分析项目,对SQL的应用,我个人的理解如果不是专门做数据库的人员,不需要掌握太复杂的,但是基本的知识应该反复温习、练习、复习以及应用。 ; G* I) N! a0 V& q, A8 { [
最近做的项目就是根据数据的唯一编号从数据库里取数据,公司的数据库管理员已经写好很多Views,其实我是从Views里取的数据。 用SQL做上海地区出租房屋情况的分析分析需求:
4 F0 |0 S& _! j. P! L$ [ 1、上海出租房分布情况:各区域房源数目,平均面积,楼层情况,交通方便情况。在某如网站上爬取的数据,由于网站限制,租金字段爬不下来,只能就分布区域,面积等分析。 ! B y+ g: N0 {: U& V0 ]
2、爬取数据后先在excel里做数据处理、数据清洗,在这里有的字符型数据需要通过“分列”转化为数字型数据。另存为csv格式,然后导入Navicat里。
" u: x/ B8 P6 S* b. }' G
0 o8 }7 `$ D! M4 {, L 3、总数据量。
7 W: {) M4 O8 Z6 ~' `; e: t% Y0 ], ~
5 R8 ?2 f4 X5 K2 b4 U 4、分析各个区域出租房屋数及占比。
4 A5 f9 m' n5 z1 C* o" @- b5 h0 G 5 B- x, O7 L8 ]
5、各个区域出租房屋的平均面积。 8 [! u6 ^9 `* `2 G4 ~$ y& o% t
' u* V5 w) |- r- |! c* I7 D+ S
6、出租房屋的层级划分。按整楼高。 4 L, [6 t# S# Z/ R+ i/ b9 N
/ X% D/ U' r" @ d' m! g
7、距地铁站的距离。
& ^, p$ Y; s" Q4 ?9 F 项目总结爬取到的数据量有些太少了,并且感觉很不全面。要想真正提高自己的技术水平,还需要多应用。自己接触的数据分析项目还是比较少,感觉有点无从下手,不知道应该分析哪些东西才是有价值的,现实情况中,做数据分析还需多学习些业务知识,与业务相结合的分析才能分析对方向,才能分析出有价值的东西。
6 s5 Q. q; c8 r7 @! W D
# N4 h8 J2 } K' R; {# N9 D7 M3 r1 ?
" a1 j' p) `+ E( E- k" a/ R. `6 e8 U8 \5 r+ ?/ e
|