SQL入门基础

SQL的基本逻辑入门，看完B站戴戴大师兄讲课后形成的课堂笔记，包括基础框架，表连接，子查询，窗口函数知识点。

SQL

• 基础框架
  • 语法结构：select--from--where--group by--having--order by--limit 运行顺序：（select确定表头--）from--where--group by--having--order by--limit--select展示
  • select (as) 字段名：取什么字段（as可以省略）
  • from 表格名：数据源，即取自哪个表
  • where 条件代码：取数的范围
    • 当查询条件为字符串时，要用英文单引号 '' 框住要查询的字符串，数字则不用 与having结合起来看：where是聚合前筛选，having是聚合后筛选
    • 运算符
      • 
      • =、>、<、>=（大于等于）、<=（小于等于）、 <>或!=（不等于）
        • eg, where name = 'China'; 即：在数据库中查找中国的数据
      • and（和）、or（或）、not（非，一般与其他连用）
        • and和not一起用时，and的运算优先级高于not； and和or一起用时，and运算优先级高于or。 建议用括号括起来，eg: where (name like '%a%a%a%' and area >=600000) or (population >130000000 and area >= 5000000)
      • in：筛选出字段值与括号内数据相等的行，in (XXX, XXX, XX)
        • in 多条件逻辑是 or，满足其一即可 eg, where name in ( 'China', 'Japan', 'Danmark'); 即：包括中国、日本、丹麦。 以上逻辑与下述一致： where name ='China' or name ='Japan' or name ='Danmark'
      • not in：不在该范围内
        • eg, where name not in ('China', 'Japan', 'Danmark'); 即：除了中国、日本丹麦以外的国家
      • between...and...：在指定的两个值之间（包含这两个值）
        • between and 的逻辑是 and，适用于包含边界的范围判断 eg, where GDP beween 1 and 10; 即：gdp为 [1，10]
      • is null：用于查询空值
        • 注意区分空值与0、 'null' 字符串！ 数据库中的空值null前后会有符号<>，即<null>
    • 模糊查找 like '通配符+字符'
      • eg, 查询条件：name字段含有3个a且面积大于60万，或者人口大于13亿且面积大于500万 代码： where (name like '%a%a%a%' and area >= 600000) or (population > 1300000000 and area >= 5000000);
      • %：任意字符出现任意次数
        • '%t%'：字符串位数不限，含有字母 t
      • _：任意字符出现1次
        • '_t_'：字符串为3位，且第二位是字母t '_t%'：字符串位数不限，第二位是字母t
  • order by 字段名 desc|asc：排序
    • eg, order by yr desc, winner asc. 即：按年份升序、获奖者降序排列
    • asc：升序（默认，可不写）
    • desc：降序
    • in (XXX, XX)：含有（XXX,XX）的字段记为1，不含则为0，并按0到1进行排序。
      • order by 字段名 in (XXX, XX) 可理解为（XXX,XX）为特殊值排在最后 eg, order by subject in ('chemistry','physics'), subject, winner 即：chemistry、physics排在最后，其余按subject、winner升序排 举一反三：order by subject not in ('chemistry','physics') 即chemistry、physics排在最前
  • limit 偏移量x, 显示行数n：从X+1行起取前N行数据。limit 0,1 =limit 1
    • eg, order by population desc limit 3,4; 查询人口排名第(3+1)=4到第(3+4)=7的 理解为：在X行下面画一条线开始取N行数据
  • 聚合函数和group by：依据哪个字段分组聚合，会自动忽略空值
    • 锁死！select中有聚合函数的一定要有group by！ 但是！group by的字段不一定需要select展示出来！
    • 聚合函数：相当于数据透视表中的值计算
      • 常见聚合函数： AVG( )：返回某列均值 COUNT( ) ：返回某列的行数，count (*)计算表格行数 MAX( )：返回某列的最大值 MIN( )：返回某列的最小值 SUM( )：返回某列的和
    • group by字段1,字段2,字段n having 字段运算函数：对字段1,2,n进行去重分组（类似于建立数据透视表，子句中字段相当于行标签），再用having进行筛选（类似于数据透视表中对行标签进行筛选）
      • 与数据透视表类比： group by建立数据透视表并确定行标签， having对行标签进行筛选， order by对∑值进行排序， limit确定显示结果的行数， select选取数据透视表中哪些字段需要最终展示。 eg, select continent, count(name) from world group by continent having sum(population)>1000000000; 从world表中查找总人口大于10亿的洲及其下国家数量（表中仅有国家人口数） 
      • 有多个字段时按序分组，即group by A, B 与 group by B, A 不一样
      • 使用group by时，select、having后的字段只能写group by中出现过的字段或聚合函数（聚合函数中的字段可以是未出现在group by里面的）
        • 原因：group by已经把要计算的字段生成了一个新的表，having对这个新表进行筛选，最后再从新表筛选过的数据中select出其中需要展示的字段。 SQL的运行顺序为from--where--group by--having--order by--limit--select
      • having 子句和where 子句一样用于条件筛选，两者的操作符一致，但不同的是where是在指定分组前对数据进行筛选过滤，而having对分组后的数据进行筛选过滤
  • 常见函数
    • 字符处理函数 round(字段名，小数指定位数)：round(3.15,1)返回数据3.2 concat(字段名1,字段名2,'字符串')：字符串拼接函数：concat('123',' ','ABC')返回数据123 ABC left(字符串,n)：left('123456',2)返回12 right(字符串,n)：right('123456',3)返回456 substring(字符串,x,y)：substring('123456,2,3)返回234，substring('123456,-2,3)返回56 cast(字段名 as type)：将字段从原来的数据类型转换成指定的数据类型，比如日期date，整数型int等，在type的位置填写指定的参数即可 条件函数 if(expr,v1,v2)：创建新的一列字段，表达式expr为true返回值v1，否则返回v2：if(1<2,'y','n');返回y，if(1>2,'y','n')返回n。注意：表达式expr中顺序为 字段名-运算符-值 case when： case expr when v1 then r1 [when v2 then r2]... else rn end：创建新的一列字段，当某字段等于v1返回r1 [等于v2返回r2]，否则返回rn：case 2 when 1 then 'one' when 2 then 'two' else 'more' end; 返回two case when v1 then r1 [when v2 then r2]... else rn end：此时v1为表达式，当v1结果为真返回r1，假则返回rn：case when 1<0 then 'T' else 'F' end; 返回F 日期函数 year(date)：对日期/时间格式的字段提取年份，month、day、hour、minute、second等函数同理：year('2021-06-01');返回数据2021 now()：返回当前的日期和时间 date_add(date,interval 值 type)、date_sub(date,interval 值 type)： 对日期进行加减：date_add('2021-06-10',interval 2 day); 返回2021-06-12，date_sub('2021-06-10',interval 2 day); 返回2021-06-08 datediff(date1,date2)：返回两个日期之间的天数，date1-date2：datediff('2021-06-08','2021-06-01')返回7，datediff('2021-06-01','2021-06-08'); 返回-7。注意：date1 和 date2 代表是日期或日期加时间的表达式，且只有值的日期部分参与计算 date_format(date,format)：以不同格式显示日期/时间数据：date_formate('2021-06-01','%m/%d/%Y'); 返回06/01/2021 
• 窗口函数 over
  • 高清图看链接 
  • 窗口函数可与放在select子句、order by子句、limit子句中（from、where、group by、having子句不可用）
  • 运行顺序：（select确定表头--）from--where--group by--having--窗口函数--order by--limit--select展示
  • over(partition by 字段名 order by 字段名 asc|desc)
    • partition by注意与group by区分，前者只分区不去重，后者分区去重作为行标签取数。 窗口函数无partition by时，即不对数据分区，整个表作为一个区； order by必须存在。 eg, rank()over(partitionby yr order by votes desc) as pson: partition by yr:对年份数据分区  order by votes desc：对分区内的选票数降序编号，得到pson列 
  • 排序窗口函数：rank()over( )、dense_rank()over( )、row_numbe()overr( )
    • eg, 对数值99，99，90，86进行排序： rank( )返回1，1，3，4 dense_rank( )返回1，1，2，3 row_number( )返回1，2，3，4
  • 偏移分析函数：lag | lead(字段名,偏移量n,默认值m)over( )
    • 字段中当前记录上面 | 下面第n行的值，没有值则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录的值，没有则默认值为null。 lag: 向上偏移；lead: 向下偏移 eg, lag(confirmed,1) over(partition by name order by whn)：即昨日确诊人数（原数据中有每日确诊人数）
• 表连接 join
  • 通过两个或多个表中的列之间的关系连接，再从连接的表中跨表查询数据
  • select字段名 from表1 inner | left | right join 表2 on 表1.字段n=表2.字段m：将表1字段n和表2字段m等值连接进行匹配连接，无法匹配的填充null（注1），并进行 内 | 左 | 右 连接，形成一张大表，再进行运算
    • 注1：表连接释意。留意字母E进行表连接后会形成2X2行数据  eg, select a.team1, a.team2, b.player from game a join goal b on a.id = b.matchid where b.player like 'Mario%' 查询有球员名叫Mario进球的team1、team2及球员姓名
  • inner join 内连接：剔除通过注1中形成的连接表中的全部空值（inner可省略不写）
    • 
  • left join 左连接：剔除通过注1中形成的连接表中的表1空值
    • 
  • right join 右连接：剔除通过注1中形成的连接表中的表2空值
    • 
  • 总结： 内连接：两表连接字段无空值， 左连接：左表连接字段无空值， 右连接：右表连接字段无空值。
  • 多表连接：from 表1 join 表2 on 表1.字段n=表2.字段m join 表3 on 表3.字段r=表2.字段q
• 子查询
  • 由括号包裹嵌套在主查询中，可以自己独立运行的一段完整查询语句，将查询结果作为主查询的一部分。子查询优先于主查询运行。类似于excel中=函数括号中套嵌的函数，可以用在select, from, where, having中（from子查询要给别名），可以多重嵌套
  • 运行顺序：子查询--（select确定表头--）from--where--group by--having--窗口函数--order by--limit--select展示
  • where子查询
    • eg, select name,continent from world where continent in (select continent from world where name in('Argentina','Australia')) order by name 括号中即为子查询，（'Argentina','Australia'所属的大洲）有哪些国家
  • from子查询
    • 必须给from子查询命名，在子查询的括号后+as别名，即给形成的这张新表一个名 eg, select constituency, party from (select constituency, party,votes, rank()over(partition by constituency order by votes desc) as posn from ge where constituency between 's14000021'and's14000026' and yr=2017) as rk where rk.posn=1 括号中即为子查询，形成了一张新表再进行查询。2017年（爱尔兰当选议员）所属选区及其团体。 注：当选议员=选区中得票数vote最高的，爱尔兰选区编号为s14000021至s14000026
• 例子都在注释里，大纲模式比较容易看