(未完)
第零章:前言
本文章全部基础来源于【课程2.0】SQL从入门到实战|云端数据库搭建|Excel&Tableau连接数据库_哔哩哔哩_bilibili
视频内容,详细教学,一步走请走该视频进行学习。
注意前情:
①主要适用MySql数据库语法
②只学习Sql语句的查询语句部分
③代码非最优!
④使用该教程能够解决80%数据分析时数据库运用场景
SQL查询语句语法结构和运行顺序:
语法结构:select--from--where--group by--having--order by--limit
运行顺序:from--where--group by--having--order by--limit--select
第一章:数据介绍
<world>
name-国家名称,continent-所在洲,area-地域面积,population-人口,gdp,capital-首都,tid-域名,flag-国旗
(该表用于基础筛选练习)
<nobel>
yr-年份,subject-科目,winner-获奖者
(该表用于基础筛选练习)
<covid>
name-国家名,whn-统计截止时间,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...】
替换函数:
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等数据类型。
时间函数:
date是包含年月日的数据
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值。
datediff(date1,date2)计算date1和2之间间隔的“天数”。(左边减右边,所以也有负号)
date_format(date,format)将日期和时间格式化,格式化的意思就是说,按照date中所给的信息,我按照format所给规则重写其格式。其中,format格式如下:
条件判断函数:
if(expr,v1,v2)指表达式expr为true返回v1,否则返回v2
case expr when v1 then r1 [when v2 then r2] ...[else rn] end-case中的expr满足vn则返回rn
没有expr就直接看v,vn满足则返回rn,不满足则继续看vn+1
第三章:高级语句
Ⅰ窗口函数
Ⅱ表连接
Ⅲ子查询
Ⅳ云端数据库配置
第四章:巩固和实践
在上述的知识点梳理之后,还需要做的一件事就是在题库里夯实基础
牛客网SQL真题:https://www.nowcoder.com/ta/sql
在这里的真题对应上面所有的知识点,只要你知识点有学透,题就会做
按照顺序把题库里的题全部刷一遍,要做到看到题目就能有思路,还能准确无误地写出代码,这样基本上能搞定80%以上的实际数据需求和SQL笔试题。
剩下的20%需要更加复杂的业务场景、数据和需求才能继续提升!
第五章:Excel连接数据库
第六章:Tableau连接数据库