背景

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

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

数据分析框架

上面是一整套数据分析计划，分为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_idfrom instructor,teacheswhere (instructor.ID, dept_name) = (teaches.ID, 'Biology');

汇合运算

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

并运算

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

(select course_idfrom sectionwhere semster = 'Fall' and year = 2009)union all(select course_idfrom secitionwhere semestr = 'Spring' and year = 2010);

交运算

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

(select course_idfrom sectionwhere semster = 'Fall' and year = 2009)intersect all(select course_idfrom sectionwhere semester = 'Spring' and year = 2010);

差运算

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

(select course_idfrom sectionwhere semster = 'Fall' and year = 2009)except all(select course_idfrom sectionwhere 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 teacheswhere semester = 'Spring' and year = 2010;select avg(aalary)from instructorwhere 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_idfrom sectionwhere 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 namefrom instructorwhere salary > some (select salay                                   from instructor                                   where dept_name = 'Biology');

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

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

空关系测试

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

select corse_idfrom section as Swhere 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.namefrom student as Swhere 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_idfrom coursewhere dept_name = 'Biology')

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

(select T.course_idfrom takes as Twhere S.ID = T.ID)

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

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

反复元组存在性测试

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

select T.course_idfrom course as Twhere 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_salaryfrom (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_salaryfrom 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 budgetfrom department, max_budgetwhere 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_namefrom dept_total, dept_total_avgwhere 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 rwhere P;

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

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

delete from instructorwhere 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 instructorset salary = salary * 1.05where salary > 100000;update instructorset salary = salary * 1.05where salary <= 100000;

能够换成带case的语法

update instructorset 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 Sset 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高手。

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