: `9 F+ C4 A2 g4 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题找出所有首都和其國家名字,而首都要有國家名字中出現。 9 l2 P- I2 W: b5 t2 D
SELECT capital,name FROM world WHERE capital LIKE concat(%,name,%);
5 D$ D; I. {: U7 }- F 2、第1章:第15题"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville",顯示國家名字,及其延伸詞,如首都是國家名字的延伸。
: r- Q- {. B3 s% s# E SELECT name,REPLACE(capital,name, ) FROM world WHERE capital LIKE concat(name,_%);
. n4 w5 o1 }! p3 a# Q 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。 , J5 a& g K. F5 E/ r
SELECT name,continent,; ]8 R) ]9 x9 s; h
CASE WHEN continent=Oceania THEN Australasia
4 x! @# R- |4 q. u- z/ q& M% U WHEN continent=Eurasia THEN Europe/Asia9 [& Q5 o$ O) r! }
WHEN name=Turkey THEN Europe/Asia( E% S! t+ K+ p( G
WHEN continent=Caribbean AND name LIKE B% THEN North America& h \; Q1 @4 G
WHEN continent=Caribbean AND name NOT LIKE B% THEN South America( p% {+ D9 G$ T5 w
ELSE continent END' o q0 y! ~8 a+ L3 ]) Q2 ?- B
FROM world
, @9 \% N& [3 n ORDER BY name;& c+ ]/ |) m: l$ j& S% j
- f( d9 Z" m d! t
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% Y3 G* e! _: `: v
SELECT winner,subject1 H! \$ _3 J) Y1 L0 \
FROM nobel4 O7 }: J! I4 Q$ w4 V6 c8 U& D8 X
WHERE yr=1984
" V: u2 _ e d0 T( E0 n ORDER BY subject IN (Physics,Chemistry),subject,winner;" n# H9 p* p) [5 e% n
( o+ x5 W# M1 f
5、第4章:第5题顯示歐洲的國家名稱name和每個國家的人口population百分比,以德國的人口的百分比作人口顯示。 & q" V/ D/ Z8 ^8 H4 U
SELECT name,
; n* T$ o/ t1 `% w/ @ CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name=Germany),0),%)* u8 s; j2 ~" v0 d& I8 J5 H
AS population
2 T$ O1 p6 D' J% y+ D FROM world WHERE continent=Europe;/ R8 b0 F5 z4 d( R/ f( i) N
4 R6 b, t6 m) r. Z5 H+ U4 C6 i 6、第5章:关于nobel表的SUM和COUNT练习第11题列出誰獲得多於一個獎項(Subject)。 $ W8 h% o/ f! p! S2 I" n* v( ~
SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT(subject))>1;
" z# Z8 b4 w/ v. n+ E5 p8 o 7、第6章:第12题每一場德國GER有參與的賽事中,列出賽事編號 matchid, 日期date 和德國的入球數字。原题要求列出的是德国的入球次数,所以此处必须添加条件teamid=GER ; R* O- e! l# S( h8 O
SELECT matchid,mdate,count(teamid)# ]5 C" x7 L) T" b' P5 b/ _
FROM game JOIN goal ON matchid=id
* ^, F, M; M# a6 g9 F WHERE (team1 = GER OR team2 = GER) aAND teamid=GER GROUP BY matchid,mdate;. L9 F1 {# J0 l
/ l" @9 e- V. E* p) m
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. ' G8 N( ]) y1 y1 b) }
SELECT mdate,team1,
" H: G- q6 X# m6 J" h* M SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END). V) R% {2 O C
score1,
& ~. Y9 o3 Z" |4 w& q( X7 e team2,$ M; z& ?2 D/ d8 s% G
SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END )
; j" K; J4 X" N7 n: d score27 y$ r: a# P& q6 j
FROM game LEFT JOIN goal ON matchid = id
+ y/ ?$ b1 Y# c% ?5 v GROUP BY mdate, matchid, team1,team2
' i) d! u# s: `6 [/ G5 B ORDER BY mdate, matchid, team1,team2) X2 ^9 Y( o4 H; T
总结SQLZOO里的题目做了90%,有个别很复杂的我没有做,大概有五六道。经过这一段时间的学习以及最近在公司做的数据分析项目,对SQL的应用,我个人的理解如果不是专门做数据库的人员,不需要掌握太复杂的,但是基本的知识应该反复温习、练习、复习以及应用。
+ k0 V# o9 J7 r6 u$ v 最近做的项目就是根据数据的唯一编号从数据库里取数据,公司的数据库管理员已经写好很多Views,其实我是从Views里取的数据。 用SQL做上海地区出租房屋情况的分析分析需求:
. o- G6 [% \4 ^6 g 1、上海出租房分布情况:各区域房源数目,平均面积,楼层情况,交通方便情况。在某如网站上爬取的数据,由于网站限制,租金字段爬不下来,只能就分布区域,面积等分析。 ( P2 s3 ^- O% o( v: p9 e6 `
2、爬取数据后先在excel里做数据处理、数据清洗,在这里有的字符型数据需要通过“分列”转化为数字型数据。另存为csv格式,然后导入Navicat里。 Y/ P& z! e8 C8 X& t- p; o! Z% B8 b
7 u1 c% D A$ R' u Z6 q3 W# t; |% m
3、总数据量。
9 ] ^+ x/ e) \% Q/ z# q. ~
( U3 G- M) V. T6 k6 V 4、分析各个区域出租房屋数及占比。
& o0 f# |! v! _
- V1 w3 v s; ]. f# w6 d 5、各个区域出租房屋的平均面积。
2 i) l1 o/ h1 ~' h& c2 T) s$ p
5 M A2 g. v2 ?* ~- ] 6、出租房屋的层级划分。按整楼高。
- ^; v0 j3 B6 Y0 z. l: f; ~( S
7 T; m/ w* R1 p, X: y. ]! X 7、距地铁站的距离。 5 e8 r6 |8 O4 m# t: S
项目总结爬取到的数据量有些太少了,并且感觉很不全面。要想真正提高自己的技术水平,还需要多应用。自己接触的数据分析项目还是比较少,感觉有点无从下手,不知道应该分析哪些东西才是有价值的,现实情况中,做数据分析还需多学习些业务知识,与业务相结合的分析才能分析对方向,才能分析出有价值的东西。
7 z/ i; |5 o; h R7 ~
" |% Y; {9 ^1 ?5 Z% H: m
7 @* d6 c6 f8 y7 A/ m& B W5 C; V8 I: K+ a. N( f
7 i( I2 Q. c) M# o1 z- X
|