数据分析-3-SQL-CSDN博客

本文链接：https://blog.csdn.net/2401_86353890/article/details/140468077

第一节提及，数据分析师需要对数据进行储存和分析，将数据储存起来，便得到了“数据库”，分析时我们需要对数据进行操作，便涉及到了对数据库的操作。SQL正是这样的工具。

SQL是Structured Query Language的缩写，即结构化查询语言。通过SQL，我们可以实现对数据库的各种操作。

我们把一堆数据放到一起，便是数据库，比如一个班级的各次月考成绩；数据库中有一个或者多个表，就比如我们可以把每次月考的成绩作为单独的表；一个数据表有行和列，就比如月考成绩有各个科目作为列，各个同学作为行。其实这和Excel是一样的，只不过Excel是可视化的版本而已：一个数据库便是一个xlsx文件，一个表便是文件中的一个sheet，每个sheet都有自己的行和列。在操作数据库时，完全可以想象自己是在操作一个xlsx文件。关于更多Excel的知识，详见第四节。

如果想要在自己的电脑上学习，需要安装MySQL，SQL Server等数据库系统；同时我们也可以在线上学习，比如SQL Online Compiler - Next gen SQL Editor。这里使用了本地的MySQL数据库系统，安装方法不再赘述。

SQL的学习资料有：

假设我们已经在Windows上连接了MySQL数据库系统，现在让我们来学习如何一步步地操作数据，并在这个过程中学习语法和使用。

我们可以在MySQL命令行中逐行输入命令来进行操作，命令行读取到“;”时才会执行命令，所以命令行也是可以输入多行命令的，但是换行到下一行后便不能修改上一行了。如果想要一次执行很多命令，我们可以将命令保存在“script.sql”文件中，然后在MySQL命令行中使用以下命令来执行

source C:\path\to\your\script.sql

注意这里不用加分号，路径也不用加冒号。

数据库操作

查看数据库

show databases; --查看已有数据库

SQL语句以分号结尾，虽然这不一定是必须的，但初学期间不必记忆特殊情况，加分号学习即可；

SQL语句对大小写不敏感，上述语句还可写为

SHOW DATABASES;

实际中大小写的使用是出于习惯和便利的考虑，一般关键字，函数名等会大写，数据库的名字、表的名字等会小写。

SQL语句注释分为单行注释和多行注释，单行注释以“--”开头，直到行尾的内容都会被忽略，需要注意MySQL中“--”后需要加一个空格，多行注释则以“/*”开头，“*/”结尾，比如：

/*
多行注释
多行注释
*/
SHOW DATABASES;

创建数据库

CREATE DATABASE dbname; --创建一个名为dbname的数据库

选择数据库

USE dbname; --使用名为dbname的数据库

删除数据库

DROP DATABASE dbname; --删除名为dbname的数据库

数据表操作

查看数据表

SHOW TABLES; --查看选定数据库下的所有数据表

创建数据表

CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,
    ...
);
--创建一个名为table_name,列分别为column1,column2等的数据表

我们看到创建表的时候需要给定表名(table_name)，表字段名(column1,column2)和每个表字段的数据类型(datatype)。表字段名也就是列。

数据类型

数据类型有三类，分别是数值、时间日期和字符串。

数值类型
类型	字节大小	有符号范围	无符号范围
tinyint	1	(-128, 127)	(0, 255)
smallint	2	(-32 768, 32 767)	(0, 65 535)
mediumint	3	(-8 388 608, 8 388 607)	(0, 16 777 215)
int	4	(-2 147 483 648, 2 147 483 647)	(0, 4 294 967 295)
bigint	8	(-9 223 372 036 854 775 808, 9 223 372 036 854 775 807)	(0, 18 446 744 073 709 551 615)
float	4	(-3.402 823 466 E+38, -1.175 494 351 E-38), 0, (1.175 494 351 E-38, 3.402 823 466 351 E+38)	0, (1.175 494 351 E-38, 3.402 823 466 E+38)
double	8	(-1.797 693 134 862 315 7 E+308, -2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)	0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)
decimal	-	-	-

时间日期
类型	字节大小	格式	范围
date	3	YYYY-MM-DD	1000-01-01/9999-12-31
time	3	HH:MM:SS	-838:59:59/838:59:59
year	1	YYYY	1901/2155
datetime	8	YYYY-MM-DD hh:mm:ss	1000-01-01 00:00:00/9999-12-31 23:59:59
timestamp	4	YYYY-MM-DD hh:mm:ss	1970-01-01 00:00:01/2038-01-19 03:14:07

字符串
类型	字节大小
char	0-255
varchar	0-65 535
tinyblob	0-255
tinytext	0-255
blob	0-65 535
text	0-65 535
mediumblob	0-16 777 215
dediumtext	0-16 777 215
longblob	0-4 294 967 295
longtext	0-4 294 967 295

我们可以在类型后添加“(n)”来指定字符串的长度，比如“char(n)”或“varchar(n)”。

我们来看一个实例：

CREATE TABLE grades(
    name char(5),
    chinese int,
    math int
);

指定字段的数据类型后，我们还可以指定字段的属性。常用的属性有：

属性	描述
signed	有符号数值
unsigned	无符号数值
null	默认为空
not null	不能为空
auto_increment	自增，一般用来定义主键
default	默认值

实际上，按以上方式创建数据表后得到的只是具有一定结构的空表，我们还需要向其中插入数据。

显示表信息

DESCRIBE table_name; --查看名为table_name的表的详细信息

使用该语句可以看到数据表的结构信息，比如列的名称，数据类型和数据属性。

“DESCRIBE”在某些情况下可以简写为“DESC”。

删除数据表

DROP TABLE table_name; --删除名为table_name的数据表

查询数据表

在Excel中我们可以直接看到数据，并且滑动滚轮看更多的数据，而在MySQL中我们需要通过代码来完成。需要注意的是，以下我们只是在查询数据表和操作输出，并没有对数据表本身做出改动。

查询

基本的查询语句为

SELECT column1, column2, ... FROM table_name;
--查看table_name中的column1,column2等这些列

为了方便，我们经常写为

SELECT column1, column2, ... 
FROM table_name;

如果我们想查看所有的列，则使用

SELECT * FROM table_name; --查看table_name中的所有列

正如前面提到的，我们只是在操作如何输出我们想要的信息，所以column的名字我们可以随意写，甚至可以重复。

某些时候，我们查询的数据中有重复的行，如果我们希望只列出不重复的行，可以使用“distinct”关键字：

SELECT DISTINCT column_name
FROM table_name;

示例：从employees表中选择所有员工的姓名(name)和部门ID(department_id)。
SELECT name, department_id
FROM employees;

范围

有些时候我们只希望看到查询结果的前几条，这时候需要使用“limit”子句

SELECT *
FROM table_name
LIMIT n;

其中的“n”定义了返回的行数。

别名

为了让可读性更强，SQL可以为表名称或列名称指定别名。此时需要使用“as”关键字。

为表名称指定别名：

SELECT column_name
FROM table_name AS alias_name;

为列名称指定别名：

SELECT column_name AS alias_name
FROM table_name;

一般情况下不需要指定别名，直接用数据表内原有的名称即可。不过在对列进行操作时，比如合并列、对列求和，此时不指定别名的话会默认显示带函数的名字，指定别名便能提高可读性。

在大部分情况下，“as”关键字可以省略。

筛选

有些时候，我们只希望看到满足特定条件的数据，这时需要使用“where”子句，

SELECT column1, column2, ...
FROM table_name
WHERE condition;

这里的筛选条件“conditions”涉及一系列逻辑运算。

常用的操作符为：

操作符	描述
>	大于
<	小于
=	等于
<>/!=	不等于
>=	大于等于
<=	小于等于

数据类型全部可以使用；时间日期类型需要用引号括起来，也是全部操作符都能使用；字符串类型需要用引号括起来，可以使用“=”和“<>/!=”。

我们还有以下条件操作：

“not”条件：否定后面的条件，比如筛选出“amount”不为“2”的条件为“not amount = 2”；
“is null”条件，“is not null”条件：当数据类型为“null”时，是不参与前面的条件匹配的，需要用此处的条件；

示例：假设orders表包含订单信息，字段有order_id、customer_id、order_date和total_amount。请编写一个查询，找出2023年销售额超过10000的客户的ID。
SELECT customer_id
FROM orders
WHERE YEAR(order_date)=2023 AND total_amount>10000;
这里的“YEAR”是个函数，返回日期的年份。

排序

排序需要使用“order by”子句，

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

其中的“asc”和“desc”分别表示升序和降序。

分组

实际中我们经常需要把一个表中的数据按照某一列来分组再进行处理。考虑这样的情况，假设我们有一张表，上面记录着各个同学的成绩，这张表有三列，第一列是学生名字，第二列是科目，第三列是对应成绩。如果我们需要求取每个同学的总成绩，便需要用到分组了。分组需要使用“group by”子句，

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

其中的“aggregate_function”称为聚合函数，和“group by”一起使用，在分组后对每一组的数据进行处理。注意，分组后多行数据其实被整合为了一行，所以需要对其余行使用聚合函数，然后得到的数据被填入到了整合后的行的末尾。

示例：假设我们的数据表名为“grades”，字段为“name”、“subject”和“grade”，则上面的例子可以用下面的代码实现：
SELECT name, SUM(grade)
FROM grades
GROUP BY name;
此时返回的结果便是含有字段“name”和“SUM(grade)”的表，里面按照“name”进行了分组并且将“grade”进行了求和。这里的“SUM()”便是聚合函数，返回数值列的总数。

为了方便，我们给求和后的列指定一个别名，所以代码为
SELECT name, SUM(grade) total_grade
FROM grades
GROUP BY name;

示例：假设sales_data表包含销售记录，字段有sale_date、product_id和amount。请编写一个查询，计算每个产品的总销售额。
SELECT product_id, SUM(amount)
FROM sales_data
GROUP BY product_id;

连接

我们还会遇到需要将两个或多个表合并的情况，比如我们有两份成绩表，一份是语文成绩，另一份是数学成绩，如果我们想整理一份语文和数学成绩都有的成绩单，就需要将两份成绩表连接起来。连接需要利用“join”子句，大致的语法为：

SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition;

但是在连接前还有一些问题需要考虑。如果两个表中的人名都是一样的，那很简单，连接只需要对照着人名把成绩拼在一起就行了。但现实的问题更为复杂，考试有可能有人缺考了，而且两门考试缺考的人还不一样，也就是说两张表的人名不完全一样了。也有可能制作成绩单时不小心把人名弄重复了，也就是说同一张表中同一个人名有多条记录。将这个例子中的人名换为两个表连接的标识，便可以推广到任何的表。总而言之，最普遍的情况是，我们有两张表，两张表中同一个标识可能有很多记录，而且两张表的标识还不完全一样。

于是我们有以下不同的连接方式。

第一种是内连接(inner join)，或等值连接。两张表中都存在的人的成绩才会出现在结果中，只要有一门考试缺考的人便不会出现。内连接的语法为：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

自然的，第二种是左连接(left join)。此时所有有语文成绩的人都会被出现在结果中，如果某个学生考了语文但是数学缺考了，其数学成绩会显示为“null”。左连接的语法为：

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

同理，第三种是右连接(right join)。此时所有有数学成绩的人都会被出现在结果中，如果某个学生考了数学但是语文缺考了，其语文成绩会显示为“null”。右连接的语法为：

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

同时，我们还有全连接(full join)。此时只要考虑语文和数学的其中一门，就会出现在结果中，缺考的则显示为“null”，语文和数学成绩单上出现过的人名都会出现在结果中。全连接的语法为：

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name=table2.column_name;

连接操作时经常给表使用别名，比如：

SELECT t1.column_name, t2.column_name
FROM table1 t1
INNER JOIN table2 t2
ON t1.column_name=t2.column_name;

示例：假设有两个表：employees（员工表，包含employee_id、name、department_id）和departments（部门表，包含department_id、department_name）。注意，员工可能没有分配部门，某部门也可能暂没有员工。请编写一个查询，列出所有已经有部门的员工的姓名和部门。
SELECT e.name, d.department_name  
FROM employees e  
INNER JOIN departments d 
ON e.department_id = d.department_id;
编写一个查询，找出没有分配部门的员工姓名。
SELECT e.name  
FROM employees e  
LEFT JOIN departments d 
ON e.department_id = d.department_id  
WHERE d.department_id IS NULL;

操作数据表

插入

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

更新

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

删除

如果我们想删除数据表中的某些数据，需要使用“delete”子句，

DELETE FROM table_name
WHERE condition;

函数

SQL提供了很多函数，大致分为聚合函数，字符串函数，日期时间函数，控制流函数等。

聚合函数

sum()：返回总和；

字符串函数

char_length()/character_length()：返回字符串的长度；

窗口函数

前面我们使用过分组和聚合函数，窗口函数与此类似但是又有所不同。继续使用与分组一致的例子，在分组和聚合中，本来每个同学的记录有好几行，聚合后就只有总成绩一行了。如果我们希望保留原有各个科目的成绩，在每行后添加一行总成绩，分组聚合便无法做到了，此时需要使用窗口函数。

窗口函数的语法为

SELECT column_name, window_function() OVER(expression)
FROM table_name;

如何理解这个表达式？“table_name”中的数据经过“expression”的处理之后，窗口函数“window_function”会为每一行添加一列，将其结果填入。

示例：使用和分组中同样的例子，我们可以这样添加总成绩：
SELECT name, SUM(grade) over(PARTITION BY name)
FROM grades;
这里的“PARTITION BY”起到了分区的作用，与“group by”类似但是不会合并为一行。然后“SUM(grade)”将总成绩填进了每一科目成绩的后面。

窗口函数有很多，我们可以使用窗口函数在新的列中写入排名、第n大的值等等。