小白学数据分析-SQL每日练

2021/6/29 19:27:12

编程Tag： select 查询 SQL where 子句 数据分析 元组 小白学

本文主要是介绍小白学数据分析-SQL每日练，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

背景

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

image.png

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

image.png

数据分析框架

下面是一整套数据分析方案，分为5个步骤：明确问题、获取数据、数据清洗、分析数据最后呈现报告。

image.png

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查询的含义可以理解如下：
  1. 为from子句中列出的关系产生笛卡尔积（多个关系中每条元组的各种可能排列组合）
  2. 在步骤1的结果上应用where子句中指定的谓词
  3. 对于步骤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，登录上去之后有网页版的编辑器，可以直接写代码执行，省的要安装一堆软件。

image.png

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

image.png

用例内容

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语言执行了多长时间

image.png

SQL语言也是一门编程语言，不可能一天就学会，都要熟能生巧，持续练习，并在日常工作中经常使用，相信通过这样的方法，人人都能称为SQL高手。

你还有什么好的方法，欢迎大家分享！！！



这篇关于小白学数据分析-SQL每日练的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程