目录

目录

目录

数据库安装

数据库基本概念

数据库管理系统（DBMS）的分类

SQL 语句

创建、删除、更新操作

创建数据库（CREAT DATABASE）

创建表

删除表

更新表

查询、筛选操作

选取数据选取唯一值

筛选条件

分组、筛选、排序操作

分组（GROUP BY）

筛选（HAVING）

排序(ORDER BY)

数据类型

运算符

算术运算符

比较运算符

逻辑运算符

函数

算术函数

字符串函数

日期函数

转换函数

聚合函数

视图与子查询

视图（VIEW）

创建视图（CREATE VIEW）

修改视图(ALTER VIEW)

更新视图(UPDATE VIEW)

删除视图(DROP VIEW)

子查询

谓词

LIKE（模糊匹配）

BETWEEN （范围查询）

IS NULL(IS NOT NULL)

IN 

EXISTS

写在前面：本文是在参加Datawhale组队学习过程中，写下的读书笔记，部分内容参考Datawhale课程内容。Datawhale是个非常棒的开源社群，想要一起学习进步的同学欢迎关注。

Datawhale github地址：Datawhale (github)

数据库安装

数据库基本概念

数据库是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。该数据集合称为数据库（Database，DB）。用来管理数据库的计算机系统称为数据库管理系统（Database Management System，DBMS）。

数据库管理系统（DBMS）的分类

  • 层次数据库（Hierarchical Database，HDB）
  • 关系数据库（Relational Database，RDB）
  • 面向对象数据库（Object Oriented Database，OODB）
  • XML数据库（XML Database，XMLDB）
  • 键值存储系统（Key-Value Store，KVS）

常见的如Oracal，SQL server，MySQL等都是关系数据库的代表，其特点是由行和列组成的二维表来管理数据，这种类型的DBMS称为关系数据库管理系统（Relational Database Management System，RDBMS）

SQL 语句

SQL语句是为了操作数据库而开发的语言，按照功能不同可以将SQL语句分为三类：

  • DDL(Data Definition Language，数据定义语言) 。主要用来创建或删除数据，包括：
    • CREAT ：创建数据库和表等对象
    • DROP  ： 删除数据库和表等对象
    • ALTER ： 修改数据库和表等对象的结构
  • DML（Data Manipulation Language，数据操纵语言）。主要用来查询和变更数据
    • SELECT ：查询表中的数据
    • INSERT ：向表中插入新数据
    • UPDATE ：更新表中的数据
    • DELETE ：删除表中的数据
  • DCL（Data Control Language，数据控制语言）。主要用来确认或取消对数据的变更。
    • COMMIT      ： 确认对数据库中的数据进行的变更
    • ROLLBACK ： 取消对数据库中的数据进行的变更
    • GRANT        ： 赋予用户操作权限
    • REVOKE     ： 取消用户的操作权限

创建、删除、更新操作

下面介绍一些常用的SQL语句

创建数据库（CREAT DATABASE）

CREAT DATABASE <数据库的名称>

创建表<CREAT TABLE>

CREAT TABLE <表的名称>

删除表<DROP TABLE>

DROP TABLE <表的名字>

更新表<ALTER TABLE>

可以对选中的表内增加一列，列的具体信息从列定义中给出。

ALTER TABLE <表的名字> ADD COLUMN <列定义>

也可以将表中的某列删除。注意增加和删除的不同。

ALTER TABLE<表的名字> DROP COLUMN <列名>

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

查询、筛选操作

在实际应用SQL过程中，从表中选取合适的数据占了非常重要的一部分，通过SELECT语句将我们需要的数据选取出来的过程称为匹配查询或查询（query）。

选取数据<SELECT>

--最基础的SELECT语句
SELECT <列名>
FROM <表名> ;

SELECT * FROM <表名>

SELECT 语句用来选择数据。当不需要指定某列时，用符号 *表示对所有列 

选取唯一值<SELECT DISTINCT>

当数据有很多重复值，但只需要返回不同值时，DISTINCT 用来返回唯一不同值

SELECT DISTINCT <列名>
FROM <表名> ;

筛选条件<WHERE>

当我们需要给筛选的数据加上一些限定条件时，WHERE语句用来筛选满足条件的记录。

SELECT <列名>
FROM <表名>
WHERE <表达式> ;

分组、筛选、排序操作

分组（GROUP BY）

有的时候我们需要对数据以某列进行分组，这时候就需要使用GROUP BY 操作。

-- 最基本的GROUP BY 
SELECT <列名>
FROM <表名>
WHERE <条件>
GROUP BY <列名>

GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，子句顺序为：

1 SELECT → 2. FROM → 3. WHERE → 4. GROUP BY → 5.HAVING → 6.ORDER BY

但注意，书写顺序和执行顺序是不同的，执行顺序为：

1.FROM → 2.WHERE → 3.GROUP BY → 4.HAVING → 5.SELECT → 6.ORDER BY 

所以执行顺序不同，GROUP BY 在SELECT 的前面，则 SELECT中定义的别名 不能在GROUP BY 中使用。

筛选（HAVING）

HAVING同样是对数据进行限制，但HAVING和WHERE的区别是：

"Where" 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。. "Having"是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

从执行顺序上可以方便理解，WHERE先筛选符合条件的记录，先对整体的数据有一个约束，符合约束的才能进行下一步操作，再GROUP BY 分组。WHERE不能使用聚合函数，因为WHERE是逐行在筛选确认是否满足限制条件。而聚合函数是对列进行操作，需要已经确定的列才可以进行操作。所以聚合函数放在HAVING中

SELECT <列名>
FROM <表>
WHERE <筛选条件>
GROUP BY <列名>
HAVING <限制条件>

排序(ORDER BY)

在SELECT操作后，对匹配好的数据进行排序，有ASC（升序）、 DESC（降序）两种。且ORDER BY 中可以使用SELECT 中定义的别名，原因就是执行顺序ORDER BY 在SELECT 后面。

SELECT <列名>
FROM <表>
WHERE <筛选条件>
GROUP BY <列名>
HAVING <限制条件>
ORDER BY <列名>

数据类型

数据类型可以分为如下四种：

  • INTEGER

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

  • CHAR

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

  • VARCHAR        

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

  • DATE

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

运算符

SQL中可以使用的运算符有：比较运算符、算术运算符、逻辑运算符

算术运算符

代表含义运算符
加法+
减法 -
乘法*
除法/

比较运算符

含义运算符
求和或相等=
不相等<>
大于等于>=
大于>
小于等于<=
小于<

逻辑运算符

含义运算符
并且，取交集AND
或，取并集OR
非，取反，表示不是NOT

函数

SQL内置函数大概分为如下几类：

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

对于函数，数量较多，在应用过程中忘记了可以查文档，自然会越用越熟练，所以不用去死记硬背，多练习即可。

算术函数

在四则运算符之外，还有算术函数用于我们对数据的一些数学计算。其中包括求

  • 绝对值函数ABS（数值）, 若参数为NULL,则返回NULL
  • 求余数MOD（被除数，除数），只能对整数列进行取余数。另：SQL Server中用%取余。
  • ROUND函数（数值，保留几位），四舍五入保留位数的函数。

字符串函数

除了数值型存储数据外，我们也经常要处理字符串数据，字符串函数就是处理字符串数据的相关函数，包括：

  • CONCAT(str1,str2,str3)  拼接函数，用来拼接多个字符串
  • LENGTH(str) 字符串长度函数，返回字符串的长度
  • LOWER(str) 将字符串转换为小写，只对英文有效果。本身就是小写的保持不变
  • UPPER(str) 将字符串转换为大写，其余同上
  • REPLACE(字符串，替换前的字符串，替换后的字符串） 可以将字符串中的元素进行替换
  • SUBSTRING(字符串 FROM 截取起始位置 FOR 截取字符数) 可以对字符串中的元素进行截取，从最左侧开始，起始索引为1

日期函数

日期函数主要包括：

  • CURRENT_DATE  获取当前日期
  • CURRENT_TIME  当前时间
  • CURRENT_TIMESTAMP  当前日期和时间
  • EXTRACT () 截取日期中的元素，可以将一个日期分别提取出其中的年月日小时分秒等。且返回值是数值类型不是日期类型。

#截取日期元素
SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

转换函数

转换函数主要有两种，一种是类型转换，例如将数值型的转换为字符型，将字符串类型转换为日期类型等。另一种是值转换，如将NULL转换为设定的值

  • CAST（转换前的值 AS 想要转换的数据类型） 
#类型转换，将字符串类型数据转换为数值型，并将其命名为int_col列
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
  • COALESCE （数据1，数据2，数据3....）

COALESCE 返回左侧开始的第一个不为NULL 的数据。

聚合函数

在应用SQL中，常常需要将数据进行汇总操作，如求和、求平均等等，SQL中有提供相应的汇总的函数，叫做聚合函数。常用的聚合函数为：

  • COUNT：计算表中的记录数（行数），即有多少个符合条件的行
  • SUM：计算表中数值列中数据的合计值
  • AVG：计算表中数值列中数据的平均值
  • MAX：求出表中任意列中数据的最大值
  • MIN：求出表中任意列中数据的最小值
--常见应用方式

-- 计算表table中一共有多少行
SELECT COUNT(*)
FROM table

-- 计算列name的行数
SELECT COUNT(name)
FROM table

-- 计算列price的总和
SELECT SUM(price)
FROM table

-- 计算列price的平均数
SELECT AVG(price)
FROM table

-- 计算列price的最大值和最小值
SELECT MAX(price),MIN(price)
FROM table

注：在使用聚合函数时有些细节需要注意：

  • COUNT(*) 计算全部行数的时候会包含有NULL值的行数，但如果是COUNT（列名），计算具体某列行数的时候则不会包含NULL值的行。
  • 所有聚合函数都会自动排除NULL，除了COUNT（*）。
  • MAX/MIN函数对于几乎所有数据类型都适用，例如DATE时间类型也可以使用。但SUM/AVG则只适用于数值类型。

视图与子查询

视图（VIEW）

视图是一个虚拟的表，即虚表，是根据SELECT语句创建的，本质上不是表，本质上对应的是SELECT语句，在《SQL基础教程第2版》中提出关于视图和表的区别：“是否保存了实际的数据。”

视图中并不存在真实数据，保存的是相关的SQL语句。可以将其作为表的一种窗口，透过这个窗口我们可以看到表的全部或部分内容。视图是基于表的虚表。

 介绍完什么是视图，接下来就要聊一聊为什么需要视图。视图是基于真实表的存在，当我们已经有表了为什么还需要视图呢？

原因是视图有如下的优点：

  • 简化工作，提高效率。

假如你面对一个较大的数据库，需要频繁的进行查询，聚合等等操作，但这些操作可能是重复的。这时候可以创建一个视图，将频繁反复使用的操作保存，这样我们再用时，直接SELECT*FROM VIEW 就可以了。

  • 安全性。

视图的存在可以保证，只给用户看一部分的数据，因为视图是一个窗口，用户在查询时自然也只能在窗口范围内。这样可以增加数据库的安全性。

  • 逻辑结构

当一整个表放在我们面前的时候，数据太多太复杂，我们很难从中得出结论，视图起到了一定的逻辑独立功能，可以更清晰直观的看清数据间的结构和逻辑。

创建视图（CREATE VIEW）

CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

 其中视图名称必须时在库中唯一存在的，不能有重复。SELECT语句要写在AS的后面。

  • 当SELECT语句只是基于单表建立的，且不包含任何函数运算、表达式或分组函数，叫做简单视图，此时视图是基表的子集；
  • 如果时基于单表但其中有函数、表达式、分组函数或是GROUP BY 子句，则是复杂视图；
  • 如果SELECT语句是基于多个表的，则称为连接视图。

修改视图(ALTER VIEW)

ALTER VIEW <视图名> AS <SELECT语句>

同样的，这里的视图名也必须是唯一存在，不能重复。 

更新视图(UPDATE VIEW)

更新视图，本质上是对基表的更新，因为视图里面不包含数据，所以能否更新视图需要看是否满足视图的行和表内的行一一对应。

如果视图包含下述结构中的任何一种，那么它就是不可更新的：

  • 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION或UNION ALL
  • 位于选择列表中的子查询
  • Join
  • FROM子句中的不可更新视图
  • WHERE子句中的子查询，引用FROM子句中的表。
  • 仅引用文字值（在该情况下，没有要更新的基本表）。
  • ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么 视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

删除视图(DROP VIEW)

DROP VIEW <视图名1> [ , <视图名2> …]

删除视图需要具有相应权限才可以。 

子查询

子查询就是在查询中又嵌套了查询，即将嵌套的查询称为子查询，简单写下就是

--简单的子查询
SELECT <列名>
FROM (SELECT *
      FROM ( SELECT <列名>
             FROM <表名>
             WHERE <条件>)
        
       WHERE <条件> )

上面的简单例子就是体现了子查询的结构，即查询中嵌套着查询 。但随着嵌套层数的逐渐增加，计算机执行的效率也会随之减少。如果不是特别有必要，尽量不要嵌套太多层。

谓词

谓词是指返回值为真值的函数，真值是（True、False、Unknow）这些。前面介绍的函数返回的多是数值或是日期或是字符串等等。谓词和其他函数的区别就在这了。谓词中主要包含如下：

  • LIKE
  • BETWEEN
  • IS NULL( IS NOT NULL)
  • IN 
  • EXISTS

LIKE（模糊匹配）

用于查询字符串中的部分一致，即模糊匹配。按照匹配的位置可以分为：

  • 前方一致  SELECT*FROM student WHERE studentname LIKE  'aaa%'
  • 中间一致  SELECT*FROM student WHERE studentname LIKE  '%aaa%'
  • 后方一致  SELECT*FROM student WHERE studentname LIKE  '%aaa'

 其中%代表0个或者多个任意字符串。所以可以看到中间一致是包括了前方一致和后方一致在内的。此外，可以用  '_'  来代替 '%'  下划线代表任意一个字符。

BETWEEN （范围查询）

--范围查询,学生成绩在60-100之间的

SELECT*FROM student 
WHERE score BETWEEN 60 AND 100

BETWEEN最大的特点在于查询时是闭区间，包括左右范围。 

IS NULL(IS NOT NULL)

选取含有NULL的数据，或是选取不含有NULL的数据

IN 

可以作为OR的简便用法，如果有很多条件并列时，需要写多条OR语句，但可以用IN来简单实现，但要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的。

EXISTS

EXISTS作用就是 “判断是否存在满足某种条件的记录”。如果存在这样的记录就返回（TRUE），如果不存在就返回假（FALSE）。

集合运算

表的加减法

求并集、加法（UNION）

语法结构：

--UNION 基本用法，连接两个表product和product2 
SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;
  • UNION是将两个表或多个表进行连接去并集的操作，且会默认去除掉重复的数据。如果不想去除掉重复的数据可以使用UNION ALL 。
  • 列数据的类型必须要可以兼容，可以不相同，但要可以隐式转换
  • 在使用UNION组合查询时，只能使用一条ORDER BY 子句，它必须出现在最后一条SELECT语句之后，即对于结果集不存在用一种方式排序一部分，而又用另一种方式排序另一种部分的情况，因此不允许使用多条ORDER BY子句。

求交集（INTERSECT）

很遗憾，对于MySQL  8.0，并不支持相应的INTERSECT操作，但可以通过INNER JOIN 来得到交集。

--通过INNER JOIN 来实现取交集，其中ON不能省略，是限制条件
SELECT p1.product_id, p1.product_name
FROM Product p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id

表的减法（EXCEPT）

同样的，对于MySQL8.0，也不支持EXCEPT的操作，但一样可以通过NOT IN 来实现表的减法。需要注意的是，对于表的减法，如有集合A和集合B，做集合A与B的减法，减去的是同时属于A和B的元素。

-- NOT IN 实现EXCEPT 从product表中选择product_id 不属于product2的
SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM Product2)

连接（JOIN）

连接就是使用某些关联限制条件，将其他表的列添加进来。

内连接（INNER JOIN）

--内连接的基本语法
SELECT * FROM <表1> 
INNER JOIN <表2> 
ON <限制条件>
WHERE <限制条件>

 注意：其中ON是必不可少的，用来对两个表关于哪个公共列进行连接。即被连接的两个表，要有一个公共列；另外，如果SELECT子句中有相关的列名最好写成 表名.列名的格式。方便阅读；如果要使用WHERE子句，WHERE要写在ON后面。

外连接(OUTER JOIN)

按照保留的行位于那张表，可以分为如下三种：

  • 左连接
--左连接

FROM <表1> LEFT  OUTER JOIN <表2> ON <约束条件>
  • 右连接
-- 右连接
FROM <表1> RIGHT OUTER JOIN <表2> ON <约束条件>
  • 全外连接
-- 全外连结
FROM <表1> FULL  OUTER JOIN <表2> ON <约束条件>

ps：本文持续更新中

更多推荐

SQL 入门,看这篇就够了 ---- 基础篇