收藏本站 劰载中...网站公告 | 吾爱海洋论坛交流QQ群:835383472

SQL带你漫游数据海洋

[复制链接]
) n" u" X) G& ~$ u, 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题找出所有首都和其國家名字,而首都要有國家名字中出現。

; G) R: Z* f: z' [% U3 F2 N4 p
SELECT capital,name FROM world WHERE capital LIKE concat(%,name,%);
) }3 a% l' B0 `0 q1 b

2、第1章:第15题"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville",顯示國家名字,及其延伸詞,如首都是國家名字的延伸。

: | K2 [, _6 Q2 ?, E% I) a8 \" H
SELECT name,REPLACE(capital,name, ) FROM world WHERE capital LIKE concat(name,_%);
1 w4 O& y o% R# e

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。

6 N1 W: Q2 G( e
SELECT name,continent, . `7 `4 p, k1 k$ P$ i8 m( G2 Z$ ^9 k CASE WHEN continent=Oceania THEN Australasia0 _* M# a$ P! O0 _% |; a/ L h, _ WHEN continent=Eurasia THEN Europe/Asia # v* l3 X$ I; l. x WHEN name=Turkey THEN Europe/Asia ; x( F5 i6 c' ?8 M, k. p6 Q! q WHEN continent=Caribbean AND name LIKE B% THEN North America1 _5 Y/ N7 N5 _9 I6 x! c4 Q WHEN continent=Caribbean AND name NOT LIKE B% THEN South America ' ~; q @8 A2 h. W ELSE continent END , Q) v) O2 V4 W FROM world: L; {# C3 X- c C- V1 g ORDER BY name; & p$ T; i$ n# j. a" I7 F
5 a) t" x4 a& Z5 z& x [2 K

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.

- n" k1 @; A0 |! s; R
SELECT winner,subject5 n8 Y1 M% F# r2 h- z" @& I% I FROM nobel) s8 I8 B# e( W3 ?' s# w8 r WHERE yr=1984 + ~$ j P# P' ~' [9 x4 N; ] ORDER BY subject IN (Physics,Chemistry),subject,winner;) B; ]3 C+ i& D/ C( R
7 c$ ~, j, r6 \5 J1 }

5、第4章:第5题顯示歐洲的國家名稱name和每個國家的人口population百分比,以德國的人口的百分比作人口顯示。

' _" J: F, k6 E$ F
SELECT name, & {; Q4 U( b \6 a7 K CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name=Germany),0),%)6 E2 I( ]2 O ~. T; a @& C2 l AS population 9 p& J- c' j: n! v+ Z1 w. X; f FROM world WHERE continent=Europe; 5 t3 b" l: q6 N( W* \
9 L5 i/ b& A0 X! y( c! N. E% M

6、第5章:关于nobel表的SUM和COUNT练习第11题列出誰獲得多於一個獎項(Subject)。

4 p: J% f$ O5 Y" c; E
SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT(subject))>1;
4 ^/ r1 u% c: {7 N

7、第6章:第12题每一場德國GER有參與的賽事中,列出賽事編號 matchid, 日期date 和德國的入球數字。原题要求列出的是德国的入球次数,所以此处必须添加条件teamid=GER

0 _. e: e, j! L \7 W2 `8 {& K
SELECT matchid,mdate,count(teamid) , R8 \' G; r" d& W: Z' o g4 w! K FROM game JOIN goal ON matchid=id : I5 F; S+ x3 v& r: h WHERE (team1 = GER OR team2 = GER) aAND teamid=GER GROUP BY matchid,mdate;# i, g6 x1 P# G8 {
# [; }1 n" C6 b) A8 j+ k! 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.

5 w+ z% F6 f) t7 v2 c, p9 V! R
SELECT mdate,team1, J! t/ w7 }0 w2 M% N1 [ SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) + F) h8 H. R. V( [: ~0 }, h, j score1,* t$ l8 k) z4 q5 z, d" _4 p team2,7 M7 q5 t% R5 L( W9 S: h3 g SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END )! @2 o0 F# k# d& @, r* N score2 $ U: D4 m* e$ w- j K" F: q$ x& g, k FROM game LEFT JOIN goal ON matchid = id 4 u" y- ^! G9 ?: q4 f& n0 i GROUP BY mdate, matchid, team1,team2" _4 d0 i) }. g: K8 A ORDER BY mdate, matchid, team1,team2 ( Y! B4 P7 N, a, o! @8 H- ^( e
总结

SQLZOO里的题目做了90%,有个别很复杂的我没有做,大概有五六道。经过这一段时间的学习以及最近在公司做的数据分析项目,对SQL的应用,我个人的理解如果不是专门做数据库的人员,不需要掌握太复杂的,但是基本的知识应该反复温习、练习、复习以及应用。

0 h" S7 B1 P# T+ J; W& N- y

最近做的项目就是根据数据的唯一编号从数据库里取数据,公司的数据库管理员已经写好很多Views,其实我是从Views里取的数据。

用SQL做上海地区出租房屋情况的分析

分析需求:

5 v0 B- m( @& D2 k; I1 `

1、上海出租房分布情况:各区域房源数目,平均面积,楼层情况,交通方便情况。在某如网站上爬取的数据,由于网站限制,租金字段爬不下来,只能就分布区域,面积等分析。

+ z1 i) e/ f) |- M6 L% ]

2、爬取数据后先在excel里做数据处理、数据清洗,在这里有的字符型数据需要通过“分列”转化为数字型数据。另存为csv格式,然后导入Navicat里。

" n9 e3 B9 H# X
6 k* s3 Z- z. }+ p- @

3、总数据量。

) I7 ]3 a- k2 j
5 Q: q1 c' k- i+ s& U. x4 z l% n

4、分析各个区域出租房屋数及占比。

4 o/ P- k& z% _. R8 E
- ?" L$ U+ T4 D7 }" N0 l# z9 G8 K- [

5、各个区域出租房屋的平均面积。

; _& v' T a3 F! Y, ]. T5 l
% ?2 k' J' c# n2 w4 O* `" e

6、出租房屋的层级划分。按整楼高。

' ^- l. P" c. r H3 }1 \7 E3 H
. H, z4 E- V, s& H% j1 t

7、距地铁站的距离。

% m) J/ i1 p1 T( _1 \# j
项目总结

爬取到的数据量有些太少了,并且感觉很不全面。要想真正提高自己的技术水平,还需要多应用。自己接触的数据分析项目还是比较少,感觉有点无从下手,不知道应该分析哪些东西才是有价值的,现实情况中,做数据分析还需多学习些业务知识,与业务相结合的分析才能分析对方向,才能分析出有价值的东西。

- ?( G8 t$ Y2 @ 6 W' @! P( c# c0 v 2 k( h& ?9 [6 |6 ? . A7 }1 l: V. s: s $ R9 B% f' h# w& J4 D: O! p
回复

举报 使用道具

相关帖子

全部回帖
暂无回帖,快来参与回复吧
懒得打字?点击右侧快捷回复 【吾爱海洋论坛发文有奖】
您需要登录后才可以回帖 登录 | 立即注册
超级爸爸
活跃在2026-4-4
快速回复 返回顶部 返回列表