Mysql-数据分析端_数据库中有2张表 movie电影(id编号,title电影名称,yr首映年份,director号演,-优快云博客

第零章：前言

主要适用MySql数据库语法，用于数据分析

代码非最优！

SQL查询语句语法结构和运行顺序：

语法结构：select--from--where--group by--having--order by--limit

运行顺序：from--where--group by--having--order by--limit--select

SQL语言框架：

第一章：数据介绍

<world>

name-国家名称，continent-所在洲，area-地域面积，population-人口，gdp，capital-首都，tid-域名，flag-国旗

（该表用于基础筛选练习）

<nobel>

yr-年份，subject-科目，winner-获奖者

（该表用于基础筛选练习）

<covid>

name-国家名，whn-统计截止时间，confirmed-确证人数，deaths-累计死亡，recovered-累计治愈

<ge>

yr-年份，firstName-候选人名字，LastName-候选人姓氏，constituency-所在选区编号，party-所属党派，votes-投票数

<teacher，dept>

<teacher>

id-教师编号，dept-科目编号，name-教师名字，phone-电话，mobile-手机号码

<dept>

id-科目编号，name-科目名

（涉及多表连接）

<game，goal，eteam>

<game>

id-赛事id，mdate-赛事举办时间，stadium-赛事举办地点，team1，team2-参加比赛的两个队伍名

<goal>

player-一名进球球员的姓名，gtime-进球时间，teamid-所在队伍id，matchid-当时赛事的编号

<eteam>

id-队伍编号，teamname-队伍名，coach-教练名

（涉及多表连接）

<movie，casting，actor>

<movie>

id-电影编号，title-电影标题，yr-首映年份，director-导演，budget-制作费用，gross-票房收入

<casting>

movieid-电影编号，actorid-演员编号，ord-角色次序（1是第一主角，以此类推）

<actor>

id-演员编号，name-演员姓名

（涉及多表连接）

第二章：基础语法和运行原理

主知识点1：select&from

使用例题网址：SELECT from WORLD Tutorial - SQLZoo

(1)select 和 from 基本作用

select 字段名（决定这一段查询最后展示的字段）

from 表名（指定这段查询语句涉及的数据来源）

例题一：在<world>表中查询字段<name>,<continent>,<population>

SELECT name, continent, population FROM world

该例题展现从表中选取字段整列时的行为格式，注意在字段间用英文逗号隔开，在行为间用空格隔开。

字段用“*”就是全选

返回多个代码时，在每一个查询段使用“；”号。

（2）查询时修改表头名称

查询字段时可以修改名称：

select name as（这个去掉，仅使用空格也行）'国家',population '人口',continent '大洲' from world

（3）查询时去重

select distinct continent from world：

此时输出格式就是输出continent里的大洲类型（因为去重了）而不是输出整列。

逻辑就是在select字段时在字段前面加上“distinct”代表对该字段去重。

去重逻辑剖析：

如果你以下输入两个指令，你就会发现前一个可以输出，后一个报错：

select distinct name,continent from world

select name,distinct continent from world

这是因为在去重的逻辑中，第一个指令是尝试去重了name和continent两个字段，即只要有两行的name和continent字段都一样，那么就会去掉其中一个。

但是第二个指令仅仅是尝试去重了continent字段，而输出结果和name对应不上导致sql输出不了该表格。

所以使用distinct一定是在select后，而不是在字段前

（4）select的计算字段

例题：计算人均GDP

select gdp/population 人均gdp from world

类似的四则运算，只要字段内容是数值型都可以进行

类似的还可以直接使用函数进行类似的运算，格式与四则运算一致

主知识点2：where

（1）精确查询

where 表达式。走在select和from的后面，虽然select后可以直接进行简单四则运算查询，但是要查询更多条件得用where

in后用()

如果条件是字符串，使用 “ '' ”。

（2）模糊查询(模糊查询才会用通配符）

常见于一个不确定的要求，比如要查一个首字母C尾字母为ia的国家:

where name like'C%ia'

使用like，like的权重可以和“=”划等号不过：

where area like'2%'(表示用like查询时，后面的条件必须用英文''括进)

可得出所有数字首位是2的地域大小行。

模糊查询的通配符：%（表示任意数量的字符）_（表示一个字符）

得出名称第二个为t的所有国家：

where name like'_t%'

(3)多条件查询

例题：选择国家中含有三个a且面积达到600000以上的。

select name,area
from world
where name like'%a%a%a%' and area>600000

例题：选择国家中含有三个a且面积达到600000以上或者人口数达到13000000000以上且面积达到5000000以上的。

select name,area
from world
where name like'%a%a%a%' and area>600000
or
population>130000000 and area>5000000

【and比or的优先级要高】

多条件查询可以自己写，当然也可以用“in”或者“between...and...”替代

其中between...and...替代的是（or，or，or）and（or，or，or）的工作

in替代的是or，or，or的工作

主知识点3：order by

（1）无条件order by

select...from....where...order by 字段名 asc|desc(升序|降序)

在查询语句中的作用是排序,字段名后不写默认就是升序

order by讲求先后顺序，即若将查询结果先按降序的年份排，再按升序的姓名排

order by yr desc,name asc

这里年份是数值所以是数值降序，姓名是字符所以是按照原表的顺序排列

这里的逻辑是，先按照年份排序时，会有年份相同的组成一个分区，此时，第二次排序就在这个分区内进行。

（2）有条件order by

order by 字段 in('xx','xx'...)这里即会默认in后的为1，其他为0，而在排序时，1总是在0的后面，使用这个用法可以解决一下例题：将物理学奖和化学奖排在最后面，然后按照奖项排序

order by subject in('chemistry','physics'),subject

主知识点4：limit

limit n~返回数据前n行

limit x,n~返回从x+1行（包括该行）开始返回n行

比如从第四行开始返回到第7行

limit 3,4

[limit 仅可在MySQL中使用]

主知识点5：聚合函数&group by

AVG()~ 返回一列的平均值 COUNT()~返回一列的行数 MAX()~返回一列的最大值 MIN()~返回一列的最小值 SUM()~返回一列的求和值

以上称为聚合函数，标准语法是与group by 连接在一起使用（也可以自己用）

格式：

select..from...where

group by 字段名1

order by

limit

所谓group by就和sumif一样，在聚合时需要提供一个聚合的依据，这个依据就是group by后面跟着的这个字段：

例子：查询每个大洲（continent）和大洲内的国家（name）数量

select continent,count(name) from world

group by continent

就以continent为count的分组依据，输出continent一列和count（name）一列

如果没有加入group by分组依据，会默认输出count所有国家的一行，此时因为行行不对而报错。

【没有加group by的聚合函数是不分组聚合，即全部聚合】

例子2：查询2013至2015年每年每个科目的获奖人数，结果按年份从大到小，人数从大到小排序

select yr,subject,count(winner)
from nobel
where yr between 2013 and 2015
group by yr,subject【如果只有subject则yr只有2013，如果只有yr则subject只有physics（即只输出每个对应的第一个）】
order by yr desc,count(winner) desc

主知识点6：having&简单运行原理

select 字段名
from 表名
[where 表达式]
[group by 字段名]
[having 表达式]
[order by 字段名 asc|desc]
[limit [位置偏移量,]行数]

①只有使用了group by后才可以，才会使用having语句。

②having本质上是对group by分组的筛选。

③having表达式和where基本一致，但是可以使用聚合函数。

例子：查询总人口数至少为3亿的大洲和其平均gdp，其中只有gdp高于200亿且人口数大于6000万或者gdp低于80亿且首都中含有三个a的国家的计入计算，最后按国家数从大到小排序，只显示第一行

select continent,avg(gdp) '平均gdp'
from world
where
gdp>20000000000 and population>60000000
or
gdp<8000000000 and capital like'%a%a%a%'
group by continent
having sum(population)>=300000000
order by count(name) desc
limit 1

这是一道比较复杂（但是逻辑简单）的复合代码题，下面进行代码解释，首先拆解题目要求：①显示“大洲”和“平均gdp”②条件1是“gdp高于200亿且人口数大于6000万或者gdp低于80亿且首都中含有三个a的国家”③条件2是“大洲的总人口数至少为3亿”④条件1比条件2要优先，因为要首先确定计算入，才能计算比较总。⑤排序是“按国家数从大到小排序”⑥限制是“只显示第一行”

对于①，我们比较老实地交付就是select一个continent和一个avg（gdp） from world

对于②因为是先头条件（④），所以用where来写

由于我们先头有一个平均gdp使用了聚合函数，所以这里要用group by

对于③，条件2自然用group by后面跟着的having对group by后地继续进行筛选。

对于⑤，排序按照国家数，那也就是按照count（name） desc去做。

对于⑥，就是limit 1即只显示前1行

主知识点7：部分常见函数

四舍五入函数：

round(x,y)-将x按照精确到小数点后y位进行四舍五入，y为负值，保留左边对应相位，其余为0【round（1283，-1）→1280（-2）→1200...】

百分比函数：

concat(round(expr*100,2),'%')-concat，round组合函数，用于表示某个比率expr的百分比形式。其中round的作用是生成这个比率expr的一百倍后保留两位小数的结果，concat的作用是将round生成的数值和%结合在一起。

粘合函数：

concat(a,b,c)-将a，b，c变为abc，需要注意的是，其中的abc可以是一个字符也可以是一个数据列，组合时将不被削去其数据列属性。：where capital like concat('%',name,'%')可以在capital和name中，筛选出capital一列的数据带有name中字符的行。

替换函数：

replace（s，s1，s2）-使用s2替换s中的所有s1

返回函数：

left（s，n）/right（s，n）/substring（s，n，len）-返回字符串s一部分n的函数

left-返回s中最左边n个字符，right-返回s中最右边n个字符，substring-返回s中从第n个字符起取长度为len的字符串（即取n到n+len的子字符串）。

substring中，n可以为负值，即从s的倒数开始算，特别注意：‘abcdefg’取substring（-2，3）输出不是fed而是fg，这意味着从s的倒数开始算，但是取的时候还是正着取。包括right取的时候也是正着取。

转换数据类型函数：

cast（x as type）-将值x的类型转换为type，其中type可以为char（n），date，time，datetime，decimal等数据类型。

时间函数：

year/month/day（date）-分别获取date中的年月日数据

date_add/sub（date，interval expr type）-其中add是加，sub是减。expr是值，指偏移多少。type=second，minute，hour，day，week，month，quarter（季度），year。返回修改后的date值。：date_add(whn,interval 2 day)=在whn的时间基础上将所有数据加两天。

datediff（date1，date2）-计算date1和2之间间隔的“天数”。（左边date1减右边date2，所以也有负号）

date_format（date，format）-将日期和时间格式化，格式化的意思就是说，按照date中所给的信息，我按照format所给规则重写其格式。其中，format格式如下：

条件判断函数：

【if（expr，v1，v2）】指表达式expr为true返回v1，否则返回v2

case when

【case expr when v1 then r1 [when v2 then r2] ...[else rn] end】指case中的expr满足vn则返回rn

没有expr就直接看v，vn满足则返回rn，不满足则继续看vn+1

示例：

SELECT
name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
WHEN score >= 60 THEN 'D'
ELSE 'F'
END as grade
FROM students;

SELECT name, score
FROM students
ORDER BY
CASE
WHEN score >= 90 THEN 1
WHEN score >= 80 THEN 2
WHEN score >= 70 THEN 3
WHEN score >= 60 THEN 4
ELSE 5
END;

SELECT name, score
FROM students
WHERE
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
WHEN score >= 60 THEN 'D'
ELSE 'F'
END = 'A';

第三章：高级语句

Ⅰ窗口函数

over(partition by 字段名1 order by 字段名2 asc|desc)

其中partition by指定分区依据，order by指定排序依据。

先根据某个字段名1，将所有字段分组后，再按照字段名2，在各个分组内部进行排序。光是over了还不够，因为over实际上没有输出任何值，它只是按照规则在虚框中排了个表，所以over还需要衔接以下几个词才能作为窗口函数输出具体数值：

rank（），dense_rank（），row_number（）

rank()-将输出虚框表中从上到下对应的排名，相同排名数值相同，若第一第二名相同排名则记为1，第三名记为3。

dense_rank()-将输出从上到下对应的排名，相同排名数值相同，若第一第二名相同排名则记为1，第三名记为2。

row_number()-将输出虚框中从上到下对应的排名，相同排名数值相同，若第一第二名相同排名则记为随机的1和2，第三名记为3。

以上是在虚框中排完名之后成立了一个新行rank，听候后续指导的差遣。所以写排序窗口函数的时候一般这样写：

select

yr,party,votes,

rank()over(partition by yr order by votes desc) as rank
from ge
where constituency='S14000024'
order by party,yr

这样输出的结果就是筛选出constituency为S1400024的，先按照party排序（将相同party排到一起），再按照party的年份排序，默认为升序。输出年份yr，政党party，得票数votes和对应的“每年的得票数排名”rank。

另外，除了排序窗口的三个函数以外，还有两个“偏移分析函数”：

LAG（），LEAD（）

LAG(字段名,偏移量,默认值)-在over排序好虚框之后，我想要查看这个虚框中，这个数据其向上偏移对应“偏移值”后对应“字段名”的数据，如果它没有上一行，则输出“默认值”。

LEAD(字段名,偏移量,默认值)-在over排序好虚框之后，我想要查看这个虚框中，这个数据其向下偏移对应“偏移值”后对应“字段名”的数据，如果它没有下一行，则输出“默认值”。

写偏移分析函数的时候一般这样写：

select name,
date_format(whn,'%Y-%m-%d') as date,
confirmed as comfirmed_today,
lag(confirmed,1)over(partition by name order by whn) as confirmed_yesterday,
confirmed-lag(confirmed,1)over(partition by name order by whn) as confirmed_new
from covid
where name in ('France','Germany') and month(whn)='1'
order by name,whn

这段的意思就是说，查找法国和德国分别在一月份新冠按日期从上到下的每天确诊人数，昨天确诊人数以及新增（今天较昨天）确诊人数。

可以看到这段用confirmed-lag（confirmed，1）作为新增确诊是有效的，说明了over的“对应性”。即通过地名分组，按照地名中的时间排升序，返回该升序中上一个行对应的confrimed值，与该confirmed值序列一一对应。

这里还要再举个例子，说明窗口函数的性质：

例题：

查询意大利每周新增确诊数（显示每周一的数值 weekday(whn) = 0）

最后显示国家名，标准日期（2020-01-27），每周新增人数

按照截至时间排序

答案1：

select name,
date_format(whn,'%Y-%m-%d') as date,
confirmed,
confirmed-lag(confirmed,1)over(partition by name order by whn) as confirmed_new_weekly
from covid
where name='Italy' and weekday(whn)=0
order by date

输出结果：

可以看到正确的数据格式。

（错误）答案2：

select name,
date_format(whn,'%Y-%m-%d') as date,
confirmed,
confirmed-lag(confirmed,7)over(partition by name order by whn) as confirmed_new_weekly
from covid
where name='Italy' and weekday(whn)=0
order by date

输出结果：

答者错误地认为，当lag中输入的是向上调7，应该会输出原confirmed-上7天confirmed的数据，然而它就搞不出来了。

说明：窗口函数是在源代码运行从【select→from→where→group by→having】之后得到的表中再进行复制从而操作的，而不是在原表中复制进而进行操作的。

因此，在运行窗口函数之前，你就得将窗口函数所对应表搞清楚。

Ⅱ表连接

inner/left/right join（内连接，左连接和右连接）

一般格式：

select 字段名

from 表名1 inner/left/right join 表名2 on 表名1.字段名=表名2.字段名

将两表连接在一起，使用笛卡尔积连接法，例如左边有AA，右边有AA，则生成的表内有四个A（1*2=2，2*2=4）。没对应的部分（如左表有B，右表无B）显示为null。

如果是inner，在连接之后，就不允许两表中存在任何null值，left就不允许左边存在null，right就不允许右边存在null。

例题1：查询有球员名叫Mario进球的队伍1（team1），队伍2（team2）及球员姓名

[

<game>

id-赛事id，mdate-赛事举办时间，stadium-赛事举办地点，team1，team2-参加比赛的两个队伍编号

<goal>

player-一名进球球员的姓名，gtime-进球时间，teamid-所在队伍id，matchid-当时赛事的编号

<eteam>

id-队伍编号，teamname-队伍名，coach-教练名

]

select team1,team2,player

from game right join goal on game.id=goal.matchid

where player like'%Mario%'

例题2：查询队伍1（team1）的教练是“Fernando Santos”的球队名称（teamname）、比赛日期（mdate）和赛事编号（id）

select teamname,mdate,game.id
from eteam inner join game on eteam.id=game.team1
where coach='Fernando Santos'

可以看到所使用的on后面的连接字段不一致，字段基本是表与表之间的连接键。

小试牛刀：

查询每场比赛，每个球队的得分情况，列出举办时间（mdate）、队伍1（team1）、队伍1得分（score1）、队伍2（team2）、队伍2得分（score2）。并按照举办时间（mdate）、赛事编号（matchid）、队伍1（team1）和队伍2（team2）排序。

select

mdate,team1,sum(case when game.team1=goal.teamid then 1 else 0 end) score1,team2,sum(case when game.team2=goal.teamid then 1 else 0 end) score2

from game left join goal on game.id = goal.matchid

group by mdate,team1,team2

order by mdate, matchid, team1, team2

这是一道相对复杂的综合题。下面是它的逻辑解析：

首先按照它的要求列出举办时间，队伍名和队伍得分。

select mdate，team1，score1，team2，score2

可以看到在表中是不存在所谓的score字段的，为此，我们需要进行组合运算得出score的真实值。

首先，我们对两份数据的两份表进行连接,由于game的赛事id是总括而goal的赛事id是跟随记录进球的，所以会出现game的数据随着被goal扩展的情况，因此在去空时选择去左边的空：

from game left join goal on game.id=goal.matchid

其次，要得出score的真实值，就得知道在每场比赛中，都是哪个队伍赢下了比赛，在goal表中，明确记录了一场比赛中的teamid，该teamid明确对应了一场比赛matchid和一次进球。我们就可以知道，通过以比赛，team1，team2聚合，我们将分类好team1和team2的teamid进行计数，就可以得出单次比赛的得分，通过以上逻辑修改score1和score2的定义，并添加聚合条件。

sum(case when team1=teamid then 1 else 0 end) score1,sum(case when team2=teamid then 1 else 0 end) score2

...

group by mdate,team1,team2

因为用于显示的mdate，team1和team2都是非聚合字段，聚合字段只有score1和score2。

最后，按照要求排序：

order by mdate, matchid, team1, team2

Ⅲ子查询

子查询本身就是一段完整的查询语句，然后用英文括号()包裹嵌套在主查询语句中，子查询也可以根据需求进行多层嵌套。

由于子查询并非函数而是之前函数在更高维度上的运用，所以该小节并不包含对任何函数的解释，而仅仅用例题呈现子查询的用法和子查询的特征以供参考。

<world>

name-国家名称，continent-所在洲，area-地域面积，population-人口，gdp，capital-首都，tid-域名，flag-国旗

例题1：（where嵌套子查询）查询出gdp高于欧洲每个国家的所有国家名，有一些国家gdp值可能为NULL，请排除这些国家。

运行逻辑：

高于欧洲每个国家gdp的国家名，言下之意就是在欧洲国家这个子表中找到最高的gdp，然后在主表中进行筛选。

(select gdp from world where continent='Europe')

以上就是欧洲gdp单独列出来的表格

将这个表格中的gdp进行聚合

(select max(gdp) from(select gdp from world where continent='Europe'))

以上就是欧洲gdp中最大gdp的数值，那么要求输出高于这个数值的所有国家名并去掉gdp为null的值：

select name

from world

where gdp>(select max(gdp) from(select gdp from world where continent='Europe') as Europe_gdp) and gdp is not null

两种手段我们都可以得到结果：China，United state，Japan。

<ge>

yr-年份，firstName-候选人名字，LastName-候选人姓氏，constituency-所在选区编号，party-所属党派，votes-投票数

例题2：（from嵌套子查询）查询2017年所有在爱丁堡的选区当选议员所在选区（constituency）及其团队（party），已知爱丁堡选区编号为S14000021至S14000026，当选议员即各选区得票数最高的候选人

运行逻辑：

在窗口函数的学习中我们写了这样的一串代码：

select

yr,party,votes,

rank()over(partition by yr order by votes desc) as rank
from ge
where constituency='S14000024'
order by party,yr

它可以拿来进行修改得到我们想要的关于所谓“2017年爱丁堡所有选区分别的得票情况及其团队”

select
party,constituency,
rank()over(partition by constituency order by votes desc) as rank
from ge
where constituency in('S14000024','S14000026') and yr=2017
order by constituency,rank

在得到这个表之后，要记住它包含了团队，选区以及rank信息，年份和选区已经帮我们筛好了，此时，我们只要将rank筛选为1就可以得到各选区（不能在窗口函数里直接设置rank=1，原因详见窗口函数运行顺序）的实际当选名单

select constituency,party
from (

select
party,constituency,
rank()over(partition by constituency order by votes desc) as rank
from ge
where constituency in('S14000024','S14000026') and yr=2017
order by constituency,rank

) as rk
where rk.rank=1

在这里你会发现和where嵌套不同的地方，那就是我在输入这个子查询的同时加上了“as rk”作为子查询的定义，这是必要的一步，不能省略。

子查询还有很多练习题可供进行“多重嵌套”的练习，也算一种逻辑升级的考验，但是其基本逻辑跟以上大差不差，因此就不多赘述，读者可以自行找补。