背景

最近打算学习一点数据分析的内容，下图中尽管广告成分有点多，然而技多不压身个，都说人人应该有一点产品思维，并对数据放弃敏感。

看了一些培训机构的介绍，波及到的知识点还挺多的，有工具、思维、实操及最初的报告。果然一口吃不了个瘦子，缓缓学吧。

数据分析框架

上面是一整套数据分析计划，分为 5 个步骤：明确问题、获取数据、数据荡涤、剖析数据最初出现报告。

SQL 语言在数据分析畛域扮演着重要的角色，包含数据的存取、数据荡涤、数据挖掘都会用到 SQL 语句。明天我分享一下我是如何学习 SQL 语言。学习 SQL 语言也分初、中、高三个阶段：

• 高级，大抵学习一下 SQL 语言的根本语法，而后联合一些场景进行练习，能解决日常工作生存中的很多问题了
• 中级篇，一些高级 SQL 语法，一些 SQL 语句罕用标准，这里还不须要理解数据内核自身是如何存储数据、如何创立索引、如何解析 SQL 语句的，咱们只须要依照前人的教训和标准来写简单 SQL 就能避过不少的坑，到这个阶段就数据分析人员来说也曾经很高级了，能够解决大部分场景了
• 高级篇，对底层操作系统内核、文件系统、数据库内核有肯定理解，晓得如何高效的定义构造创立索引，晓得执行器如何执行效率更高，可能对 SQL 执行的长短进行剖析和调优。这个事件就交给业余的计算机或数据库系统工程师来做就行，毕竟数据分析师也有本人的业余事件要做。

总结起来就是一句话：一直的学习实践进行实际，再学习再实际。所以上面的内容也分为实践和实际两块内容来说

实践篇（SQL 语言内容较多，不必全看，留着查阅就行，能够间接跳到上面的实际篇）

SQL 除了查问之外还有别的性能，包含定义数据结构、批改数据库中的数据以及阐明安全性约束条件

SQL 查询语言概览

美国国家标准化组织（ANSI）和国际标准化组织（ISO）公布了 SQL 规范
SQL 语言有几个局部：

• 数据定义语言（Data-definition languageDDL），定义关系模式、批改关系以及删除关系模式
• 数据操纵语言（Data-manipulation language DML），查问信息、插入元组、批改元组和删除元组
• 完整性（integrity），SQL DDL 蕴含了定义完整性束缚的命令，保留在数据库中的数据必须满足完整性束缚的定义
• 视图定义（view definition），SQL DDL 蕴含了定义视图的命令
• 事务管制（transaction control），SQL 包含了定义事务开始和完结的命令
• 嵌入式 SQL 和动静 SQL（embedded SQL and dynamic SQL），定义 SQL 如何嵌入到通用编程语言中
• 受权（authorization），定义对关系和视图的拜访权限

本章中介绍的 SQL 特色基于 SQL-92 规范

SQL 数据定义

根本类型

• char(n)：固定长度的字符串，用户指定长度 n。也能够应用全称 character
• varchar(n)：可变长度的字符串，用户指定最大长度 n，等价于全称 character varying
• int: 整数类型（和机器相干的整数的无限本人），等价于全称 integer
• unmeric（p，d）：定点数，经度由用户指定。这个数有 p 位数字（加上一个符号位），其中 d 位数字在小数点左边。
• real，double precision：浮点数与双精度浮点数，经度与机器相干
• float（n）：精度至多为 n 位的浮点数
  char 类型，在理论长度小于指定长度时，会在尾部填充空格，而 varchar 则不会

根本模式定义

上面列出完整性束缚：

• primary key
• foreign key（A1，A2，An）reference
• not null

insert 语句中值的程序须要和属性在关系表中的程序统一。
drop table 删除整个关系表，而 delete table 只删除表中的记录

SQL 查问的根本构造

SQL 查问的节本构造由三个子句形成：select，from 和 where。查问的输出在 from 子句中列出的关系

单关系查问

SQL 容许在关系以及 SQL 表达式后果中呈现反复。如果想强行删除反复，可在 select 后退出 distinct
···
select distinct（A1，A2，An）from table
···

select 子句能够带含有 +、-、*、/ 运算符的算术表达式，运算对象能够是常数或元组的属性。
where 子句容许选出那些在 from 子句的后果关系中满足特定谓词的元组
where 子句中容许应用逻辑连词 and、or 和 not。逻辑连词的运算对象能够是蕴含比拟运算符 <、<=、>、>=、= 和 <> 的表达式
比拟运算符能够用来比拟字符串、算术表达式以及非凡类型，如日期类型

多关系查问

属性同时呈现在多个关系表中时，关系明被用作前缀来阐明咱们应用的是哪个属性。这种常规在某些状况下可能有问题，比方当须要把来自同一个关系的两个不同元组的信息进行组合时。
一个 SQL 查问能够报货三种类型的子句：

• select 子句用于列出查问后果中所须要的属性
• from 子句是一个查问求值中须要拜访的关系列表
• where 子句是一个作用在 from 子句中关系的属性上的谓词
  查问语句的运算程序首先是 from，而后是 where，最初是 select
  通常说来，一个 SQL 查问的含意能够了解如下：
• 为 from 子句中列出的关系产生笛卡尔积（多个关系中每条元组的各种可能排列组合）
• 在步骤 1 的后果上利用 where 子句中指定的谓词
• 对于步骤 2 后果中的每个元组，输入 select 子句中指定的属性
  而在 SQL 的理论实现中，他会通过（尽可能）只产生满足 where 子句谓词的笛卡尔积元素来进行优化。

天然连贯

from 子句中的匹配条件在最通常状况下须要在所有匹配名称的属性上相等。
为了简化这种状况下 SQL 语句编写，SQL 反对一种被称作为天然连贯的运算。
天然连贯（natural join）运算作用于两个关系，并产生一个关系作为后果。不同于两个关系上的笛卡尔积，他将第一个关系的每个元组与第二个关系的所有元组进行连贯；天然连贯只思考哪些在两个关系模式中都呈现的属性上取值雷同的元组对。（在两个关系表中列名雷同的列上取值雷同的元组）
为了防止不必要的同名属性列带来的危险，SQL 容许用户指定须要哪些列上相等，通过join r2 using（A1，A2）

附加的根本运算

更名运算

后果中的属性名来自 from 子句中关系的属性名，但有的状况不能用这个办法派生名字，其起因点：

• 首先，from 子句的两个关系中可能存在同名属性；
• 其次 select 子句中应用算术表达式，那么后果属性就没有名字；

• 再次，心愿要扭转后果中的属性名字。
  SQL 提供了一个重命名后果关系中属性的办法即应用 as 子句

  old-name as new-name

as 子句还能够重命名关系表，重命名关系表出于两个起因：

• 把长的关系名替换成短的，不便在查问的其余中央应用
• 为了实用于须要比拟同一个关系中的元组的状况，比方本人和本人进行笛卡尔积运算

被用来重命名关系的标识符在 SQL 规范中被称作 相干名称（correlation name），也被称作 表别名（table alias）或者 相干（correlation variable）或者 元组变量（tuple variable）

字符串运算

SQL 应用一对单引号来标示字符串，如果单引号是字符串的组成部分，那就用两个单引号字符来标示。
SQL 规范中，字符串上相等运算是大小写敏感的。局部数据库系统实现时不辨别大小写，这种默认的形式是能够在数据库级或特定属性级被批改的。
SQL 还容许在字符串有多重函数，例如串联、提取淄川、计算字符串长度、大小写转换、去掉字符串前面的空格
like 操作符实现模式匹配：

• 百分号（%）：匹配任意子串
• 下划线（_）：匹配任意一个字符

模式是大小写敏感的
like 比拟运算中应用 escape 关键词来定义转义字符，举例

like 'ab\%d' escape '\'  匹配所有以“ab%cd”结尾的字符串
like 'ab\\cd%' escape '\' 匹配所有以“ab\cd”结尾的字符串

SQL 容许应用 not linke 比拟运算符搜查不匹配项。
SQL1999 还提供 similar to 操作，语法相似于 UNIX 中的正则表达式。

select 子句中的属性阐明

型号“*”示意“所有属性”，星号后面能够加上表名. 代表表中所有属性，举例

select instructor.* from instructor, teaches where instructor.ID = teaches.ID;

排列元组的显示秩序

order by子句默认应用升序，能够用 desc 示意降序，或者用 asc 示意升序

where 子句谓词

between比拟运算符来阐明一个值是小于或等于某个值，同时大于或等于另一个值。
not between
where 子句中减少一个额定条件能够用and
SQL 容许在元组上使用比拟运算符，按字典程序进行比拟，例如：

select name, course_id
from instructor,teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

汇合运算

SQL 作用在关系上的 union、intersect、except 运算对应于数学集合论中的并、交、差

并运算

union 会主动去除反复，如果想保留所有反复，须要应用 union all

(select course_id
from section
where semster = 'Fall' and year = 2009)
union all
(select course_id
from secition
where semestr = 'Spring' and year = 2010);

交运算

如果想保留反复，必须应用 intersect all 代替 intersect

(select course_id
from section
where semster = 'Fall' and year = 2009)
intersect all
(select course_id
from section
where semester = 'Spring' and year = 2010);

差运算

如果想保留反复，必须应用 except all 代替 except

(select course_id
from section
where semster = 'Fall' and year = 2009)
except all
(select course_id
from section
where semester = 'Spring' and year = 2010);

空值

• 如果算术表达式的任一输出为空，则该算术表达式后果 (+、-、*、/) 为空
• SQL 将设计管制的任何比拟运算的后果视为 unknown，这创立了除 true 和 false 之外的第三个逻辑值

• 布尔运算中：

  • and：true and unknown 的后果为 unknown，false and unknown 后果是 false，unknown and unknown 的后果是 unknown
  • or：true or unknown 的后果是 true，false or unknown 的后果是 unknown，unknown or unknown 的后果是 unknown
  • not：not unknown 的后果是 unknown
• 如果 where 子句谓词对一个元组计算出 false 或 unknown，那么该元组不能被退出到后果集中
• 谓词中应用非凡的关键词 null 测试空值
• 如果谓词 is not null 所作用的值非空，那么它为真
• 某些 SQL 实现还反对 is unknown 和 is not unknown 来测试一个表达式后果是否为 unknown
• select distinct 时，如果比拟的两个值非空且值相等，或者都是空，则会被认定为雷同，而谓词中“null=null”会范畴 unknown，上述形式利用于汇合的并、交和差运算

汇集函数

以值的一个汇合（集或多重集）为输出，返回单个值的函数，SQL 提供五个固有的汇集函数

• 平均值：avg
• 最小值：min
• 最大值：max
• 综合：sum
• 计数：count

根本汇集

select count(*)
from course;

select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2010;

select avg(aalary)
from instructor
where dept_name = 'Comp.Sci.';

分组汇集

在 group by 子句中的所有属性上取值雷同的元组将被分在一个组中
任何没有呈现在 group by 子句中的属性如果呈现在 select 子句中的话，它只能呈现在汇集函数外部
在一个特定分组（通过 dept_name 定义）中的每位老师都有一个不同的 ID，既然每个分组只输入一个元组，那就无奈确定选哪个 ID 值作为输入。其后果就是 SQL 不容许这样状况呈现

having 子句

having 子句中的谓词在造成分组后才起作用。其限定条件是针对 group by 子句形成的分组
···
select dept_name, avg(salary) as org_salary
from instructor
group by dept_name
having avg(salary) > 42000;
···

group by 或 having 子句的查问的含意可通过下述操作序列来定义：

1. 与不带汇集的查问状况相似，最先依据 from 子句来计算出一个关系
2. 如果呈现了 where 子句，where 子句中的谓词将利用到 from 子句的后果关系上
3. 如果呈现了 group by 子句，满足 where 谓词的元组通过 group by 子句造成分组。如果没有 group by 子句，满足 where 谓词的整个元组集被当做一个分组
4. 如果呈现了 having 子句，他将利用到每个分组上；不满足 having 子句谓词的分组将被摈弃
5. select 子句利用剩下的分组产生出查问后果中的元组，即在分组上利用汇集函数来失去单个后果分组

对空值和布尔值的汇集

解决空值准则：除了 count(*)外的所有的汇集函数都疏忽输出汇合中的空值。规定空集的 count 运算值为 0，其余所有汇集运算在输出为空集的状况下返回一个空值。
SQL1999 输出的布尔类型有三种：true，false，unknown。有两个汇集函数：some 和 every

嵌套子查问

子查问签到在另一个查问中的 select-from-where 表达式。

汇合成员资格

子查问呈现在 where 子句中，通过连接词 in 或 not in 测试元组是否是汇合中的成员。

select distinct course_id
from section
where semester = 'Fall' and year = 2009 and
    course_id in ( select course_id
                           from section
                           where semester = 'Spring' and year = 2010);

汇合的比拟

SQL 容许 <some，<=some，>=some。=some 和 <>some。（至多比其中的某一个大、小、等于）
=some 等价于 in，而后 <>some 不等价于 not in
举例：找出工资至多比 Biology 系某一个教师工资高的所有老师的姓名

select name
from instructor
where salary > some (select salay
                                   from instructor
                                   where dept_name = 'Biology');

SQL 容许 <all，<=all，>=all，=all 和 <>all，<>all 等价于 not in，但 =all 并不等价于 in
举例：找出工资比 Biology 系每个教师工资高的所有老师的姓名

select name
from instructor
where salary >all (select salay
                                   from instructor
                                   where dept_name = 'Biology');

空关系测试

exists 构造在作为参数的子查问非空时返回 true 值
举例：找出在 2009 年秋季学期和 2010 年秋季学期同时开课的所有课程

select corse_id
from section as S
where semester = 'Fall' and year = 2009 and
       exists (select *
                  from section as T
                  where semester = 'Spring' and year = 2010 and 
                  S.course_id = T.course_id);

来自外层查问的一个相干名称（上述查问中的 S）能够用在 where 子句的子查问中。应用了来自外层查问相干名称的子查问被称作 相干子查问（correlated subquery）
在一个子查问中只能应用此子查问自身定义的，或者在蕴含此子查问的任何查问中定义的相干名称。如果一个相干名称既在子查问中定义，又在蕴含该子查问的查问中定义，则子查问中的定义无效。这条规定相似于编程语言中通用的变量作用于规定。
not exists 构造测试子查问后果集中是否不存在元组。能够应用 not exists 构造模仿汇合蕴含（即超集）操作。能够将“关系 A 蕴含关 B”写成“not exists（B except A）”
举例：找出选修了 Biology 系开设的所有课程的学生

select S.ID, S.name
from student as S
where not exists((select course_id
                             from course
                             where dept_name = 'Biology')
                              except
                              (select T.course_id
                                from takes as T
                                 where S.ID = T.ID));

这里，子查问

(select course_id
from course
where dept_name = 'Biology')

找出 Biology 系开始的所有课程汇合，子查问

(select T.course_id
from takes as T
where S.ID = T.ID)

找出 S.ID 选修的所有课程。这样，外层 select 对每个学生测试其选修的所有课程汇合是否蕴含 Biology 系开始的所有课程汇合

这个逻辑感觉了解不了！！！

反复元组存在性测试

unique 构造用来判断作为参数的子查问后果中有没有反复元组，如果没有返回 true 否则返回 flase
举例：找出所有在 2009 年最多开始一次的课程

select T.course_id
from course as T
where unique (select R.course_id
                      from section as R
                      where T.course_id = R.course_id and
                              R.year = 2009);

unique 谓词在空集合上计算出真值
not unique 构造测试在一个子查问后果中是否存在反复元组
留神 如果关系中存在两个元组 t1 和 t2，如果 t1 或 t2 的某个域为空时，判断 t1=t2 为假只管一个元组有多个正本，只有该元组一个属性为空，unique 测试就有可能为真

from 子句中的子查问

任何 select-from-where 表达式返回的后果都是关系，因而能够被插入到另一个 select-from-where 中任何关系能够呈现的地位
举例：找出哪些系中老师的平均工资超过 4200

select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
    from instructor
    group by dept_name)
where avg_salary > 42000;

举例：找出所有系中工资总额最大的系，此时 having 子句是无奈解决的智能用 from 子句中的子查问

select max(tot_salary)
from (select dept_name, sum(salary)
         from instructor
         group by dept_name) as dept_total(dept_name, totl_salary);

SQL2003 容许 from 子句中的子查问用关键词 lateral 作为前缀，以便拜访 from 子句中再它后面的表或子查问中的属性。举例：打印每位老师的姓名，以及他们的工资和所在系的均匀工作

select name, salary, avg_salary
from instructor I1, lateral(select avg(salary) as avg_salary
                                         from instructor I2
                                         where I2.dept_name = I1.dept_name);

with 子句

with 子句提供定义长期关系的办法，这个定义只对蕴含 with 子句的查问于晓
举例：找出具备最大估算值的系

with max_budget(value) as
       (select max(budget)
        from department)
select budget
from department, max_budget
where department, budget = max_budget.value;

举例：查出所有工资总额大于所有系均匀工资总额的系

with dept_total(dept_name, value) as
        (select dept_name, sum(salary)
         from instructor
         group by dept_name),
        dept_total_avg(value) as
        (select avg(value)
          from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value >= dept_total_avg.value;

标量子查问

SQL 容许子查问呈现在返回单个值的表达式可能呈现的任何中央，只有该子查问只返回蕴含单个属性的单个元组；这样的子查问称为 标量子查问（scalar subquery）
举例：列出所有的系以及他们领有的老师数
···
select dept_name,

      (select count(*)
        from instructor
         where department.dept_name = instructor.dept_name )
        as num_instructors

from department;
···
该例子中子查问保障只返回单个值，因为他应用了不带 group by 的 count(*)汇集函数
标量子查问能够呈现在 select、where、having 子句中，在编译时并非总能判断一个子查问返回的后果中是否有多个元组，如果子查问被执行后其后果不止一个元组，则产生一个运行时谬误。

留神从技术上来讲标量子查问的后果类型依然是关系，只管其中只蕴含单个元组。而后当在表达式中应用标量子查问时，他呈现的地位是单个值呈现的中央，SQL 就从该关系中蕴含单属性的单元组中取出相应的值，并返回该值。

数据库的批改

删除

只能删除单个元组，不能只删除某些属性上的值

delete from r
where P;

delete 命令智能作用于一个关系，如果想从多个关系中删除元组，必须在每个关系上应用一个 delete 命令。
delete 的 where 子句中能够嵌套 select-from-where

举例：删除工资低于大学平均工资的老师记录

delete from instructor
where salary < (select avg(salary)
                           from instructor);

在删除之前先进行所有元组的测试很重要，在下面的例子中若有些元组在其余元组未被测试前被删除，则平均工资将会扭转，这样 delte 的最初后果将依赖于玉足被解决的程序。

插入

能够指定待插入的元组，或者写一条查问语句来生成待插入的元组汇合。
思考到用户可能不记得关系属性的排列程序，SQL 容许在 insert 语句中指定属性
···
insert into course(course_id, title, dept_name, credis)

            values('CS-437', 'Database System', 'comp Sei', 4);

···
查问后果插入，举例：让 Music 系每个修满 144 学分的学生成为 Music 系的老师，其工资为 18000 美元

insert into instructor
         select ID, name, dept_name, 18000
         from student
         where dept_name = 'Music' and tot_crd > 144;

更新

update instructor
set salary = salary * 1.05
where salary > 100000;
update instructor
set salary = salary * 1.05
where salary <= 100000;

能够换成带 case 的语法

update instructor
set salary = case
         when salary <= 100000 then  salary * 1.05
         else salary * 1.03
     end

case 语句个别格局如下：

case
    when pred1 then result1
    when pred2 then result2
    ……
     when predn then resultn
     else result0; 
end 

标量子查问能够用在 set 子句中
举例：把每个 student 元组的 tot_cred 属性设置为该生胜利学完的课程学分的综合，假如学生在某门课程上的问题既不是 ”F” 也不是空

update student S
set tot_cred = (select sum(credits)
                   from takes natural join course
                   where S.ID = takes.ID and
                             takes.grade <> 'F' and
                             takes.grade is not null);

如果不想把 totl_cred 属性设置为空能够应用上面办法

select case 
        when sum(credis) is not null then sum(credits)
         else 0
         end

总结

• SQL 蕴含几个局部

  • 数据定义语言 DDL，定义关系模式、删除关系以及批改关系模式
  • 数据操纵语言 DML，蕴含查询语言，以及往数据库中插入元组，从数据库中删除元组和批改数据库中元组命令
• SQL 的数据定义语言用于创立具备特定模式的关系，除了申明关系属性的名称和类型外，还容许申明完整性束缚，例如主码束缚和外码束缚
• SQL 提供多种用于查询数据库的语言构造，其中包含 select、from、where 子句，SQL 反对天然连贯操作
• SQL 还提供了对属性和关系重命名，以及对查问后果按特定属性进行排序的机制
• SQL 反对关系上的根本几何运算，包含并、交、查运算
• SQL 通过在通用真值 true 和 false 外减少“unknown”来解决蕴含空值的关系和查问
• SQL 反对汇集，能够把关系进行分组，在每个分组上独自使用汇集，SQL 还反对在分组上的汇合运算
• SQL 反对在外层查问的 where 和 from 子句中嵌套子查问。还在一个表达式返回单个值所容许呈现的任何中央反对标量子查问
• SQL 提供了用于更新、插入、删除信息的构造

实际篇

咱们须要找大量的场景来做练习，本人结构太难了，还好有不少的平台有题库，我用的是牛客网的题库，他整顿了 SQL 专题 https://www.nowcoder.com/ta/sql，这里有 90 多道题目能够做，我也制订了学习专栏 https://juejin.cn/column/6973102921622208543，把本人练习的过程记录起来，也能够用不同的写法来解题，不便前面总结和进步，

上面贴一个具体的例子

找出所有非部门领导的员工 emp_no

为了练习不便和记录历史，我这里用了一个收费的云数据库 MemFireDB，登录下来之后有网页版的编辑器，能够间接写代码执行，省的要装置一堆软件。

点击“SQL 查问”进入到网页版的 SQL 编辑器中

用例内容

drop table if exists  "dept_manager" ; 
drop table if exists  "employees" ; 
CREATE TABLE "dept_manager" ("dept_no" char(4) NOT NULL,
"emp_no" int NOT NULL,
"from_date" date NOT NULL,
"to_date" date NOT NULL,
PRIMARY KEY ("emp_no","dept_no"));
CREATE TABLE "employees" (
"emp_no" int NOT NULL,
"birth_date" date NOT NULL,
"first_name" varchar(14) NOT NULL,
"last_name" varchar(16) NOT NULL,
"gender" char(1) NOT NULL,
"hire_date" date NOT NULL,
PRIMARY KEY ("emp_no"));
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');

答案

select emp_no from employees where emp_no not in (select emp_no from dept_manager);

能够查看我这条 SQL 语言执行了多长时间

SQL 语言也是一门编程语言，不可能一天就学会，都要游刃有余，继续练习，并在日常工作中常常应用，置信通过这样的办法，人人都能称为 SQL 高手。

你还有什么好的办法，欢送大家分享！！！