【转载】从零开始入门数据分析-SQL篇

该文章主要用于知识点整理，没有引用大量实例，方便小伙伴们巩固知识点。

有不足的地方还请交流指正，谢谢！

一、数据查询语言知识点(DQL: Data Query Language)

1.语句书写顺序及执行顺序

(1) 语句书写顺序：select-distinct-from-join-on-where-group by-having-order by-limit

(2) 语句执行顺序：from-on-join-where-group by-having-select-distinct-order by-limit

注：group by后不能加字段别名。（但在MySQL中可以使用，因为MySQL会优先将select后的字段进行类似扫描的工作）。

2.排序用法

(1) 单列排序：order by 字段1 （desc）；

(2) 多列排序：order by 字段1 （desc），字段2（desc）。

注：括号内desc代表降序。

3.模糊查询

（1）like用法：like后可以在字符中添加%或下划线'_', %号代表任意字符，下划线代表一个占位符。

例如：select name from tableA where name like '%什锦%'

上面语句是将name字段中包含'什锦'两个字符的name都查询出来。

（2）正则表达式regexp常见用法：

regexp '^M' 该语句查询某字段中以M开头的行;

regexp 'M$' 该语句查询某字段中以M结尾的行；

regexp 'M' 该语句查询某字段中包含M的行；

regexp 'p1|p2|p3' 该语句查询某字段中包含p1或p2或p3的行。

regexp '[abc]' 该语句查询某字段中包含a或b或c的行。

regexp '^[abc]' 该语句查询某字段中以a或b或c为首的行。

4.判断语句case...when...用法

(1)常用语法：

(case 字段 when 条件A then B

字段 when 条件C then D else G END ) as 别名

上面语句进行判断，当满足条件A时返回结果为B，当不满足A但满足C时，返回D，否则返回G。

注：D和G的类型必须相同，即当D为字符串类型而G为数值类型时，会报错。

(2)和聚合函数共同使用：sum（case 字段 when 条件A then B else C END) as 别名

5.窗口函数

(1) 排名类窗口函数：rank(), dense_rank(), row_number()

聚合类窗口函数：sum(),count(),max(),min(),avg()...

注：排名类窗口函数三者的区别，当出现排名并列的情况(以并列第一为例)，三者输出的结果如下：

rank():1、1、3、4...

dense_rank():1、1、2、3...

row_number():1、2、3、4...

(2) ntile(n) over(partition by A order by B) :用于将分组数据按照顺序切分成n片，返回当前切片值

n:切分的片数；A:分组的字段名称；B:排序的字段名称

(3) 偏移分析函数：lead(字段名，n)和lag(字段名，n)

lead将行向上移动n行，默认值为1

lag将行向下移动n行，默认值为1

6.常用其它函数

6.1日期函数

(1)curdate() :返回当前日期；

(2)curtime() :返回当前时间；

(3)timestampdiff(时间单位,start_time,end_time) :日期字段相减，其中常用时间单位为year,month,week,day,minute...

(4)datediff(日期字段1，日期字段2) :计算两个日期之间间隔的天数，单位为日；

(5)date_add(日期，INTERVAL n 时间单位) date_sub(日期，INTERVAL n 时间单位)；

(6)last_day() :返回某个日期的当月最后一天；

(7)date_format(日期字段,'%Y-%m-%d') :转换日期字段格式；

(8)to_date(字符串字段) :将字符串字段转换为日期类型。

6.2字符串函数

(1)concat(a,b): 将字符串拼接，连接为一个字符串；

(2)left(s,n): 返回从字符串s开始的n最左字符；

(3)trim(s): 移除字符串开头或者结尾的空格；

(4)substring(s,n,len): 截取字符串s中第n个位置开始，长度为len的字符串。注：起始从1开始，末尾从-1开始；

(5)substring_index(字段，分隔符号，n): 返回字符中，在分隔符号第n次出现位置之前的字符串。注意：当n为正数，则取分隔符号左边的所有字符。当n为负数，则取分隔符号右边的所有字符；

(6)substring_replace(str,'旧字符串','新字符串')：字符串替换。

6.3数学函数

(1)ABS(x): 返回绝对值；

(2)floor(x):向下取整；

(3)ceil(x):向上取整；

(4)mod(x,y):返回x除以y后的余数。

6.4转换函数

1.CAST()

(1)定义：CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

(2)语法：CAST (expression AS data_type)

(3)例子：

SELECT CAST('9.5' AS decimal(10,2))  - -结果：9.50 
SELECT CAST((NOW() AS DATE)          - -结果：2023-06-04

2.CONVERT()

(1)定义：日期转换为新数据类型的通用函数，也可以用不同的格式显示日期/时间数据

(2)语法：convert(data_type(length),data_to_be_converted,style)

(3)例子：

select convert(varchar(50),getdate(),20) 
- -输出结果：2019-12-16 13:45:35

3.to_char()

(1)定义：该函数只能在Oracle中使用，是将数值型或日期型转化为字符型。

(2)语法：

7.表连接

用一张图说明Join的各种用法：

8.将JSON改为列的形式

（1）语法：regexp_replace(json_extract_scalar(列名, '$.键名'), '[^\d.]+', '')

二、数据定义语言知识点DDL(Data Definition Language）

DDL主要用于创建数据库、创建表及修改表结构等。作为业务部门涉及到使用DDL语句的场景比较少，所以放在第二部分讲解。

数据库相关

1.创建数据库

（1）create database 数据库名：创建指定名称的数据库；

（2）create database 数据库名 character set 字符集：创建指定名称的数据库，并指定字符集（一般都指定utf8）

2.查看/选择数据库

（1）use 数据库：切换数据库；

（2）select database():查看当前正在使用的数据库；

（3）show databases：查看Mysql中都有哪些数据库；

（4）show create database 数据库名：查看一个数据库的定义信息。

3.修改数据库

（1）alter database 数据库名 character set 字符集：数据库的字符集修改操作。

4.删除数据库

（1）drop database 数据库名：从Mysql中永久的删除某个数据库。

数据表相关

一个数据库中可以有n个数据表，每个数据表中可以有n个字段（列），在创建表的时候需要定义好字段的数据类型。

1.常用数据类型

其中varchar与char的区别在于：

• char类型是固定长度的，根据定义的字符串长度分配足够的空间（例如用于身份证号，手机号等）；

• varchar类型是可变长度的，只是用字符串长度所需的空间（例如用于收入金额等）。

两者的特点：

• char 耗费更多的空间，但是查询效率高；

• varchar 耗费更少的空间，但是查询效率低。

2.创建表

（1）语法：create table 表名（

字段名1 字段类型（长度），

字段名2 字段类型（长度）

）；

（2）例子：创建一个表名为test1，两个字段分别为tid，tdate,字段类型分别为INT，DATE。

                   create  table test1 (
                    tid   INT,
                    tdate DATE
                                       )；

3.快速创建一个表结构相同的表（复制表结构）

（1）creat table 新表名 like 旧表名；

（2）例子： create table test2 like test1；

desc test2; --查看表结构

4.查看表

（1）show tables ：查看当前数据库中的所有表名；

（2）desc 表名：查看数据表的结构；

（3）show create table category:查看创建表的SQL语句。

5.删除表

（1）drop table 表名：从数据库中永久删除某一张表；

（2）drop table if exists 表名：判断表是否存在，存在的话就删除，不存在就不执行删除。

6.修改表

（1）修改表名：rename table 旧表名 to 新表名；

（2）向表中添加列：alter table 表名 add 字段名称 字段类型；

（3）修改表中某列的数据类型或者长度：alter table 表名 modify 字段名称 字段类型；

例如：将category表中的cdesc字段改为varchar（50）类型

代码：alter table category modify cdesc varchar（50）；

（4）修改列名：alter table 表名 change 旧列名 新列名 类型（长度）；

（5）删除列：alter table 表名 drop 列名。

三、数据操作语言DML(Data management language)

DML主要用于对表中的数据进行增删改操作，不会对表的结构进行改变。可以简单理解为DML是针对表中的"行"进行操作，而DDL往往是针对"表"和"列"进行操作。

1.插入数据

向一个表中插入数据，有三种方法：

（1）方法1：插入全部字段，将所有字段名都写出来

INSERT INTO 表名（字段1，字段2，字段3...) values(内容1，内容2，内容3...)；

(2) 方法2：插入全部字段，不写字段名（内容和字段自动一一对应）

INSERT INTO 表名 values（内容1，内容2，内容3...） ；

(3) 方法3：插入指定字段的值

INSERT INTO 表名（字段1） values（内容1）；

插入数据有以下需要注意的点：

• 值与字段必须要对应，即个数相同且数据类型相同；

• 值得数据大小，必须在字段值指定的长度范围内；

• varchar、char、date类型的值必须使用单引号；

• 如果要插入空值，可以忽略不写或者插入null；

• 如果插入指定字段的值，必须要写上列名。

2.更改数据

（1）不带条件的修改：update 表名 set 列名=值；

（2）带条件的修改：update 表名 set 列名=值 where 条件表达式；

例如：将tableA中所有uid为2的数据，将age改为20，address改为北京

代码：update tableA set age=20,address = "北京" where uid = 2

3.删除数据

（1）删除所有数据：delete from 表名 或者truncte table 表名

两者的区别：

• delete from 表名：不推荐，会逐行执行删除操作，效率低；

• truncate table 表名：推荐，先删除整张表，然后在创建一张一摸一样的表，效率高，当一个表中条数非常多，又要做删除动作的时候，如果用delete会很慢，所以truncate更佳。

（2）指定条件删除数据：delete from表名 where 字段名 = 值

四、SQL约束

1.约束的定义

对表中的数据进行进一步限制，从而保证数据的正确性、有效性、完整性，违反约束的不正确数据，将无法插入到表中。

2.主键约束

（1）定义： 不可重复，唯一非空，用来表示数据库中的每一条记录；

（2）语法：create table A(列名 INT PRIMARY KEY);

(3) 删除主键约束：alter table A drop primary key;

(4) 主键的自增：主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值。

关键字 auto increment

         create table A（
              eid int primary key auto increment
                                  );

(5) 设置主键自增的起始值：默认的主键起始值是1，假如想设置初始值为100

        create  table A(
              eid int) auto_increment=100;

3.非空约束

（1）定义：某一列不能为空。

（2）语法：字段名 字段类型 not null。

create table A(
ename varchar(20) not null);

4.唯一约束

（1）定义：某一列不能重复。

（2）语法：字段名 字段类型 unique。

5.外键约束

（1）语法：

在新建表时添加外键约束：

constraint 外键约束名称 foregin key （外键字段名称） references 主表名（主字段名）；

向已有表添加外键：

alter table 从表 add constraint 外键约束名称 foregin key (外键字段名)

references 主表（主键字段名）；

注：外键字段类型要和主表的主键字段类型保持一致。

（2）删除外键约束：

alter table 从表名 drop foregin key 外键约束名称

6.默认值约束

（1）定义：用来指定某列的默认值

（2）语法：字段名 字段类型 DEFAULT 默认值

- - 创建带有默认值的表
CREATE TABLE emp4(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    sex CHAR(1) DFAULT '女'，
);

五、SQL调优

1.避免使用select*

原因：select*不会走覆盖索引，会有大量的回表操作。

回表：普通索引找不到我们想要的完整信息，迫不得已要执行回表查询，再回到主键索引或者聚集索引中查询数据。

覆盖索引：建立联合索引，就是索引覆盖。

2.用union all 代替union

原因：union会进行遍历和判断，但是如果出现大量重复数据需要清除，所以在确定被union的两个表没有重复行时，则要用union all 代替union。

3.用小表驱动大表

首先讲一下in和exists关键字的区别：

in：优先执行in右边括号内的语句，因此如果in括号内的数据量少，则查询速度会快；

exists：限制性exists左边的语句，即主查询语句，然后会把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据，匹配不上则会被过滤掉。

总结：in适用于左边大表，右边小表；exists适用于左边小表右边大表。

4.join使用的注意事项

（1）使用inner join的时候，数据库会自动选择两张表中的小表去驱动大表，所以性能上不会有太大的问题。

（2）使用left join的时候，数据库会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，则会出现性能问题。因此在使用left join时，尽量使用左边小表右边大表，如果能使用inner join，则尽量少用left join。

5.使用where先将数据量缩小，在进行分组等操作

我们通过之前提到的SQL语句执行顺序可知，where在join之后就执行了，所以可以将一些筛选条件放在where后面，将数据量减少，之后在进行group by等操作。

至此本篇文章完结，在熟练运用的情况下，本篇文章足以解决业务中70~80%的问题。后续会更新一些典型SQL题目的思路及核心代码的文章。