01 为什么要学SQL

首先，说下为什么要学SQL，目前职场中不少的求职岗位都需要会sql的，尤其是数据分析相关岗位，是必备的能力之一，也是做数据分析利器，也是入门必学。

只要在工作中需要数据反馈的，都离不开SQL的使用。一般想通过SQL寻求岗位需要掌握以下几点就可以了

1）会利用SQL操作开关系数据库mysql进行查询
2）数据库的分组、聚合、排序
3）存储过程
4）对于增删改、约束、索引、数据库范式均大致了解即可，不需要深入

02学SQL的路径

再来讲讲，学习SQL的路径是怎么样，回忆起来自己从刚开始不会SQL，到现在SQL 写的非常熟练，结合自己学习工作的经验，总结SQL学习路径，如下：

1）首先，先了解SQL 使用场景，数据库等相关概念。

只要跟数据相关的工作其实都可以用到SQL，特别是数据量较大的情况下，Excel 处理不了的情况下。

2）其次，学习SQL基本的语法，掌握一些简单的SQL书写。

包括select、where、group by、having、order by、delete、insert、join、update等，可以做日常的取数或简单的分析（该水平运营、产品等非数据岗就够了）;

3）再学习掌握并熟练使用SQL高阶语法。

比如集合、分组聚合、子查询、条件逻辑、字符串函数、算术函数、日期时间函数，并且知道MySQL、Oracle、SQL Server等数据库的语法差异；

4）之后，进阶学习如何优化SQL语句，以期达到最高查询效率。

了解事务、锁、索引、约束、视图、元数据等概念，并且学会使用hive sql、spark sql、pymysql等工具，多表查询，窗口函数等~

5）最终，需要根据业务需求，将需求转化成SQL。

业务理解足够深，即知道需要用什么样的数据指标来分析、解决业务问题。比如说，统计一个班级的及格和不及格占比，统计考试中前20排名，

总之，SQL语言是数据分析工具之一，最终是为了服务于业务的。建⽴这个认知后以及基本的入门知识，之后你需要做的就是练习，练习，在练习。

《SQL必知必会》这本书的优点是可以快速入门,适合新手小白，语言通俗易懂，读着不会犯困。全书200来页,覆盖的知识点也很全：

·了解SQL(SQL基础概念)

·检索数据(Select语句)

·过滤数据(where 子句)

·创建计算字段（concat函数、||的用法）

·汇总数据（常见的聚合函数，如COUNT、MAX、SUM、AVG等）

·分组数据（Groupby、having）

·使用子查询（in & exits）

·表连接（left join 、right join 、full outer join）

·数据增删改(DDL、DML)

·使用视图

·使用存储过程

·使用游标

·····

那么，接下来，让我们来一起回顾一下：

什么是数据库，数据库就是存数据管理数据的一个东西系统，称之为数据库管理系统（Database Management System，DBMS），DBMS根据保存的格式分为5种，

• 层次数据库（Hierarchical Database，HDB）

• 关系数据库（Relational Database，RDB）

• 面向对象数据库（Object Oriented Database，OODB）

• XML数据库（XML Database，XMLDB）

• 键值存储系统（Key-Value Store，KVS），举例：MongoDB

我们最常用的就是关系型数据库：

• Oracle Database：甲骨文公司的RDBMS

• SQL Server：微软公司的RDBMS

• DB2：IBM公司的RDBMS

• PostgreSQL：开源的RDBMS

• MySQL：开源的RDBMS

数据库中存储的表结构类似于excel中的行和列，行称为记录，它相当于一条记录，列称为字段，主键：是这一列唯一标识表里的每一行，在这一列中没有重复的。

SQL是什么呢？SQL是跟数据库交流的语言，用SQL指挥数据库去干这个干那个，想让他干啥就干啥。

SQL的语言分三类，这个大概就了解一下，

第一类：DDL

DDL（Data Definition Language，数据定义语言） 用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令。

• CREATE ：创建数据库和表等对象

• DROP ：删除数据库和表等对象

• ALTER ：修改数据库和表等对象的结构

第二类：DML

DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录。变更:DML 包含以下几种指令。

• SELECT ：查询表中的数据

• INSERT ：向表中插入新数据

• UPDATE ：更新表中的数据

• DELETE ：删除表中的数据

第三类：DCL（了解）

DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令。

• COMMIT ：确认对数据库中的数据进行的变更

• ROLLBACK ：取消对数据库中的数据进行的变更

• GRANT ：赋予用户操作权限

• REVOKE ：取消用户的操作权限

实际使用的 SQL 语句当中有 90% 属于 DML，已经认识数据库的基本信息了，那我们可以创建一个数据库。

#创建数据库-- 语法：CREATE DATABASE < 数据库名称 > ;CREATE DATABASE guagua;

创建好数据库之后还需要创建个表，数据是存储在表里，那就创建一个学生的表

#创建表/*
语法：CREATE TABLE < 表名 >( < 列名 1> < 数据类型 > < 该列所需约束 > ,  < 列名 2> < 数据类型 > < 该列所需约束 > ,  < 列名 3> < 数据类型 > < 该列所需约束 > ,  < 列名 4> < 数据类型 > < 该列所需约束 > ,  .  .  .  < 该表的约束 1> , < 该表的约束 2> ,……);
*/
CREATE TABLE students(     sid int NOT NULL AUTO_INCREMENT,      sname VARCHAR(100) NOT NULL,      course VARCHAR(32) NOT NULL,      score decimal(18,1) NOT NULL,      sex varchar(10) ,      class varchar(10) NOT NULL,      Sage datetime,     PRIMARY KEY(sid) )  ;

这里可以稍微提一下命名规则：

• 只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称
• 名称必须以半角英文字母开头

书写规则：

• SQL语句要以分号（ ; ）结尾
• SQL 不区分关键字的大小写，但是插入到表中的数据是区分大小写的
• win 系统默认不区分表名及字段名的大小写
• linux / mac 默认严格区分表名及字段名的大小写
• 本教程已统一调整表名及字段名的为小写，以方便初学者学习使用。
• 常数的书写方式是固定的

数据库创建的表中都需要必须指定数据类型，如果创建错误会出现报错的情况。

四种最基本的数据类型

• INTEGER 型：用来指定存储整数的列的数据类型（数字型），不能存储小数。

• CHAR型用来存储定长字符串，当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足，由于会浪费存储空间，所以一般不使用。

• VARCHAR 型用来存储可变长度字符串，定长字符串在字符数未达到最大长度时会用半角空格补足，但可变长字符串不同，即使字符数未达到最大长度，也不会用半角空格补足。

• DATE 型用来指定存储日期（年月日）的列的数据类型（日期型）。

指定数据类型之后后面还有一个not null，对存储的数据进行限制了，我们把这种叫做约束，约束分两种，一种是非空约束，就是必须输入数据，另一种是主键约束，代表唯一值，就是刚刚提到的。

AUTO_INCREMENT自增约束：每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量

DEFAULT：默认值设定

表的其他操作可以熟悉一下

# 修改表-- 添加列 ALTER TABLE < 表名 > ADD COLUMN < 列名 >;ALTER TABLE students ADD COLUMN aaa VARCHAR(100);
-- 删除列 ALTER TABLE product DROP COLUMN aaa;
ALTER TABLE students DROP COLUMN aaa ;
# 删除表-- 整个表删除 DROP TABLE < 表名 > ;-- 删除的表是无法恢复的，只能重新插入，请执行删除操作时无比要谨慎
-- 清空表TRUNCATE TABLE TABLE_NAME;-- 优点：相比drop``/``delete，truncate用来清除数据时，速度最快。

# 注意：ALTER TABLE 语句和 DROP TABLE 语句一样，执行之后无法恢复

创建好数据库了然后可以把数据添加到数据库了，这样要用到insert into￥

# 插入数据-- INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);  # 可以省略列名，会默认按照从左到右的顺序赋给每一列，不建议用insert into students (sname , course, score , sex , class , Sage ) values('赵雷' , '语文', 98 , '男', '一班','1990-12-21');insert into students (sname , course, score , sex , class , Sage ) values('赵雷' , '数学', 34 , '男', '一班','1990-12-21');insert into students (sname , course, score , sex , class , Sage ) values('赵雷' , '英语', 69 , '男', '一班','1990-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李四' , '语文', 76 , '女', '二班','1991-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李四' , '数学', 45 , '女', '二班','1991-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李四' , '英语', 56 , '女', '二班','1991-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李五' , '语文', 33 , '女', '三班','1994-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李五' , '数学', 56 , '女', '三班','1994-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李五' , '英语', 88 , '女', '三班','1994-12-21');

#可以使用INSERT … SELECT 语句从其他表复制数据-- 将学生表的数据复制到学生表复制表中INSERT INTO students (sname , course, score , sex , class , Sage )SELECT sname , course, score , sex , class , SageFROM students;  

执行一次 INSERT 语句会插入一行数据。插入多行时，通常需要循环执行 INSERT 语句，数据添加好了，想修改数据咋办，update...set...$

# 修改数据/*UPDATE <表名>SET <列名> = <表达式> [, <列名2>=<表达式2>...];  WHERE <条件>;  -- 可选，非常重要。*/-- 注意添加 where 条件，否则将会将所有的行按照语句修改，-- 把李五的性别改成女的，班级改成四班UPDATE students   SET sex = '女', -- 可以讲null当做一个值来使用   class = '四班'  WHERE sname = '李五';  
-- UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。

# 删除数据
-- 删除数据跟修改数据语法相似，delect from <表名> where 条件 （非常重要）

删除数据跟修改数据相似就不在重复说了，重点就是不要忘记where。

1、简单查询

#从表中提取数据select * from students;select sname from students;select distinct sname  from students;

值得一提的是如果

从表中选取数据：SELECT <列名>,<列名>,<列名> FROM <表名>;

注意：DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列

想从中筛选出符合条件的列用where字句指定查询数的条件，where后面要跟条件表达式 

算数运算符：四则运算

注意：如果有括号优先处理

还有 is null/is not null

#运用# 查询及格的信息select * from students where score>=60;#查询出一班和二班里及格的学生select * from students where score>=60  and (class='一班' or class='二班' ) ;-- 其他的方法自行探索

2、聚合查询

• COUNT：计算表中的记录数（行数）
• SUM：计算表中数值列中数据的合计值
• AVG：计算表中数值列中数据的平均值
• MAX：求出表中任意列中数据的最大值
• MIN：求出表中任意列中数据的最小值

注意：聚合函数对null是排除在外的，COUNT(*)除外

COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数

# 计算最高分和最低分select max(score),min(score) from  students;# 计算里面多少个学生select count(distinct sname)  from students;

这些聚合函数运行的时候计算的整体的数，那我想分组汇总怎么办，那就可以用group by。

3、分组

group by

SELECT <列名1>,<列名2>, <列名3>, ……  FROM <表名> GROUP BY <列名1>, <列名2>, <列名3>, ……;
# 计算每个考试科目里面的最高分和最低分select course,max(score),min(score) from  students group by course;

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

group by 能不能使用别名，可以，where 不能使用别名

with rollup是用来在分组统计数据的基础上再进行统计汇总

having：过滤分组

分组统计之后想再添加条件过滤，那就用到having

# having 过滤分组# 语文和数学考试中最高分和最低分分别是多少select course,max(score),min(score) from  students group by course having course in ('数学','语文') ;

where 和having非常类似，唯一的差别是WHERE过滤行，而HAVING过滤分组。HAVING支持所有WHERE操作符。

4、排序

order by：排序

SELECT <列名1>, <列名2>, <列名3>, ……  FROM <表名> ORDER BY <排序基准列1>, <排序基准列2>, ……

SQL中的执行结果是随机排列的，当需要按照特定顺序排序时，可已使用ORDER BY子句。

order by中可以使用别名

ps：语句的执行顺序

1、函数

函数大致分为如下几类：

●算术函数 （用来进行数值计算的函数）
●字符串函数 （用来进行字符串操作的函数）
●日期函数 （用来进行日期操作的函数）
●转换函数 （用来转换数据类型和值的函数）
●聚合函数 （用来进行数据聚合的函数）

算术函数

●ABS – 绝对值
●MOD – 求余数，语法：MOD( 被除数，除数 )
●ROUND – 四舍五入，语法：ROUND( 对象数值，保留小数的位数 )

字符串函数

●CONCAT – 拼接，语法：CONCAT(str1, str2, str3)
●LENGTH – 字符串长度，语法：LENGTH( 字符串 )
●LOWER – 小写转换
●REPLACE – 字符串的替换，语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
●LETF-从左开始截取字符串，语法：LEFT(被截取字段，截取长度)
●RIGHT-从右开始截取字符串，语法：right（被截取字段，截取长度）
●mid-自定义截取，语法：mid（被截取字段，从那位置开始，截取几个字符数）
●SUBSTRING – 截取字符串，substring（被截取字段，从第几位开始截取） ，substring（被截取字段，从第几位开始截取，截取长度）
●SUBSTRING_INDEX –按关键字截取字符串，语法：substring_index（被截取字段，分隔符，关键字出现的次数）
●group_concat，字符串分组拼接

# 字符串截取select substring_index('赵雷-一班-一年级',"-",1) ; -- 从左边关键字出现第1次，截取字符串之前所有select substring_index('赵雷-一班-一年级',"-",2) ; -- 从左边关键字出现第2次，截取字符串之前所有select substring_index('赵雷-一班-一年级',"-",-1) ; -- 从右边关键字出现第1次，截取字符串之前

日期函数

• DATE()，返回日期。格式：YYYY-MM-DD

• TIME()，返回日期。格式：HH-mm-ss

• TIMESTAMP()，返回日期时间。格式：YYYY-MM-DD HH-mm-ss

• NOW()、CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()，返回语句开始执行的时间

• SYSDATE() 返回的是这个函数执行时候的时间

select SLEEP(1),NOW(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(),SYSDATE();

• DATE_FORMAT()，将日期根据指定的格式返回为对应的字符串

• date_sub，时间减

• date_add，时间加

select SLEEP(1),NOW(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(),SYSDATE();
SELECT DATE_FORMAT('2022-01-01 22:00:00','%Y-%m-%d');SELECT DATE_ADD('2022-01-01 22:00:00',interval 2 day);#往前加两天SELECT DATE_ADD('2022-01-01 22:00:00',interval -2 day);#往后加两天SELECT DATE_ADD('2022-01-01 22:00:00',interval 2 month);#往前加两月

其他函数

• like函数，通配符

• between，注意：左右两边是闭区间

• is null，is not null

• in和not in 支持子查询

• case 表达式

通配符：用来匹配值的一部分的特殊字符，使用通配符就必须使用like

# %通配符：%表示任何字符出现任意次数# 匹配李开头的学员信息select * from students where sname like '李%'
# _ 通配符，只匹配单个字符# 匹配李某select * from students where sname like '李_'

case表达式

#case when then语法CASE WHEN <求值表达式> THEN <表达式>     WHEN <求值表达式> THEN <表达式>     WHEN <求值表达式> THEN <表达式>     .     .     .ELSE <表达式>END 
# 添加一列及格和不及格# case函数select *,  (case when score >= 60 then '及格' else '不及格' end) from students;
# 行转列,科目放在表头select sname,  (case when course ='语文' then score  else null  end) as '语文',  (case when course ='数学' then score  else null  end) as '数学',  (case when course ='英语' then score  else null  end) as '英语'    from students ;

  select sname,  sum((case when course ='语文' then score  else null  end)) as '语文',  sum((case when course ='数学' then score  else null  end)) as '数学',  sum((case when course ='英语' then score  else null  end)) as '英语'    from students  group by sname ;

2、视图

什么是视图？虚拟表，视图是依据SELECT语句来创建的，所以说是select语句生产的虚拟表叫视图。

# 创建视图CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS 
CREATE view st (sname,score) asselect sname,score from students;

3、子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询

子查询的结果可以作为一个表，也可以作为一个过滤条件

# 作为一个表--嵌套子查询select 列名 from (select 列名 from 表1 where 列 where 表达式) as 子查询 -- 结果作为一个表
# 作为一个结果-- 标量子查询select 列名 from 表1 where 列名 = (select max(列名) from 表1  )# 成绩高于平均值select * from students where score>= (select avg(score) from students )

还有一种是关联子查询，有关联两个字就意味着查询与子查询之间存在着联系，那么联系是如何建立起来的

# 各个科目中高于该科目的成绩select sname,course,score from students as a where score>=(select avg(score) as avg_score from students as bwhere a.course=b.course )

关键字：union ，union all，join，iinner join，outer join，left join，right join。

1、上下拼接

用UNION将多条SELECT语句组合成一个结果集

注意：UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔；

union 是去重，union all 是不去重的，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。列需要进行对齐操作。

# unionselect 123 as 数字 union select 456
# 排序select 123 as 数字 union select 456 order by 数字 desc

2、联结

·内连接

-- 内连结FROM  INNER JOIN  ON 

INNER 关键词表示使用了内连结

注意：

• 必须使用 ON 子句来指定连结条件，在进行内连结时 ON 子句是必不可少的，ON 子句是专门用来指定连结条件的。

• SELECT 子句中的列最好按照 表名.列名 的格式来使用。如果两张表有其他名称相同的列， 则必须使用上述格式来选择列名,，否则查询语句会报错。

如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选，则需要把 WHERE 子句写在 ON 子句的后边。

where的使用方法：

• 子查询，上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件.

• on后面加where，查询的执行顺序:FROM 子句->WHERE 子句->SELECT 子句，在做完 INNER JOIN … ON 得到一个新表后, 才会执行 WHERE 子句

select 列 from 表1 inner join 表2 on 列1=列2 where 列条件
select 列 from (select 列 from 表1 inner join 表2 on 列1 = 列2) as 表3 where 列条件

·自然联结NATURAL JOIN

会按照两个表中都包含的列名来进行等值内连结，此时无需使用 ON 来指定连接条件。使用自然连结还可以求出两张表或子查询的公共部分。

SELECT * FROM 表1 NATURAL JOIN 表2

·外连接

• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

左右联结最终要的一个点是选要把哪张表作为主表，使用 LEFT 时 FROM 子句中写在左侧的表是主表，使用 RIGHT 时右侧的表是主表。

-- 左连结     FROM  LEFT   JOIN  ON -- 右连结     FROM  RIGHT  JOIN  ON -- 全外连结，mysql不支持全外联结FROM  FULL   JOIN  ON -- 交叉连结，CROSS JOIN(笛卡尔积)FROM  CROSS JOIN  

什么是笛卡尔积： 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合。

没有了ON子句的限制，会对左表和右表的每一行进行组合。

窗口函数称之为OLAP，对数据进行实时分析处理，常规的SELECT语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。

窗口函数的通用公式：

<窗口函数> OVER ([PARTITION BY <列名>] ORDER BY <排序用列名>) 

红色的地方可以省略，最关键的是PARTITON BY，PARTITON英文是分割的意思，可以理解为PARTITON BY是用来分组，这个分组的意思是表中哪些行是一组。

order by是用来排序的，决定在窗口内，哪些规则是来排序的

1、排序类的窗口函数

• RANK函数：如果存在相同位次的记录，则会跳过之后的位次，1134...

• DENSE_RANK函数：即使存在相同位次的记录，也不会跳过之后的位次。11234

• ROW_NUMBER函数：连续位次，依次排序不会重复，1233

# 英语成绩排名

select sname  ,course  ,score  ,rank() over (order by score desc) as ranking  ,dense_rank() over (order by score desc) as dense_ranking  ,row_number()  over (order by score desc) as row_numfrom students where course='英语';  # 根据科目进行排序
select sname  ,course  ,score  ,rank() over (PARTITION BY course order by score desc) as ranking  ,dense_rank() over (PARTITION BY course  order by score desc ) as dense_ranking  ,row_number()  over (PARTITION BY course  order by score desc) as row_numfrom students

2、聚合函数使用

出来的结果是一个累计的聚合函数值，根据分组情况进行累计计算，比如求和，平均，最大，最小，计数

• sum，累计求和

• avg，移动平均

• count

• max

• min

elect sname  ,course  ,score  ,sum(score) over (  order by score ) as current_sum  ,avg(score) over (  order by score  ) as current_avg  ,count(*)  over ( order by score desc ) as count_  ,max(score)  over ( order by score desc ) as max_score  ,min(score)  over ( order by score ) as min_scorefrom students 
# 分组排序select sname  ,course  ,score  ,sum(score) over ( PARTITION BY course order by score desc ) as current_sum  ,avg(score) over ( PARTITION BY course order by course  ) as current_avg  ,count(*)  over ( PARTITION BY course order by score desc) as count_  ,max(score)  over ( PARTITION BY course order by  score desc ) as max_score  ,min(score)  over (PARTITION BY course  order by course ) as min_scorefrom students

3、移动平均

<窗口函数> OVER (ORDER BY <排序用列名>                 ROWS n PRECEDING )                   <窗口函数> OVER (ORDER BY <排序用列名>                 ROWS BETWEEN n PRECEDING AND n FOLLOWING)

PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”

# 移动平均select sname  ,course  ,score  ,AVG(score) OVER (ORDER BY course                               ROWS 2 PRECEDING) AS moving_avg -- 之前2行  ,AVG(score) OVER (ORDER BY course                               ROWS BETWEEN 1 PRECEDING                                         AND 1 FOLLOWING) AS moving_avg  -- 前一行，后一行from students