常用SQL语法

常用SQL语法

一个数据库通常包含一个或多个表。每个表由一个名字标识（例如“客户”或者“订单”）。表包含带有数据的记录(行)。

一、基础篇

1、CREATE DATABASE - 创建数据库

CREATE DATABASE 库名称;

2、CREATE TABLE – 创建表

CREATE TABLE 语句用于创建数据库中的表。

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
);

数据类型	描述
integer(size),int(size),smallint(size),tinyint(size)	仅容纳整数、在括号内规定数字的最大位数
decimal(size,d),numeric(size,d)	容纳带有小数的数字、“size” 规定数字的最大位数、“d” 规定小数点右侧的最大位数
char(size)	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）、在括号中规定字符串的长度
varchar(size)	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）、在括号中规定字符串的最大长度
date(yyyymmdd)	容纳日期

可使用 INSERT INTO 语句向空表写入数据。

3、INSERT – 插入数据

INSERT INTO 语句用于向表格中插入新的行。

INSERT INTO 表名称 VALUES (值1, 值2,....);

也可以指定所要插入数据的列：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....);

4、SELECT – 查询数据

SELECT 语句用于从表中选取数据，结果被存储在一个结果表中（称为结果集）。

SELECT * FROM 表名称;

我们也可以指定所要查询数据的列：

SELECT 列名称 FROM 表名称;

注意： SQL 语句对大小写不敏感，SELECT 等效于 select。

注意： 星号（*）是选取所有列的快捷方式。

5、DISTINCT – 去除重复值

如果一张表中有多行重复数据，如何去重显示呢？可以了解下 DISTINCT 。

SELECT DISTINCT 列名称 FROM 表名称;

6、WHERE – 条件过滤

如果需要从表中选取指定的数据，可将 WHERE 子句添加到 SELECT 语句。

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值;

下面的运算符可在 WHERE 子句中使用：

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

注意： 在某些版本的 SQL 中，操作符 <> 可以写为 !=。

7、AND & OR – 运算符

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

• 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
• 如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

AND 运算符实例：

SELECT * FROM 表名称 WHERE 列 运算符 值 AND 列 运算符 值;

OR 运算符实例：

SELECT * FROM 表名称 WHERE 列 运算符 值 OR 列 运算符 值;

8、ORDER BY – 排序

ORDER BY 语句用于根据指定的列对结果集进行排序，默认按照升序对记录进行排序，如果您希望按照降序对记录进行排序，可以使用 DESC关键字。

SELECT * FROM 表名称 ORDER BY 列1,列2 DESC;

默认排序为 ASC 升序，DESC 代表降序。

9、UPDATE – 更新数据

Update 语句用于修改表中的数据。

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值;

10、DELETE – 删除数据

DELETE 语句用于删除表中的行。

DELETE FROM 表名称 WHERE 列名称 = 值;

删除所有行：

可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：

DELETE FROM table_name;

11、 TRUNCATE TABLE – 清除表数据

如果我们仅仅需要除去表内的数据，但并不删除表本身，那么我们该如何做呢？

可以使用 TRUNCATE TABLE 命令（仅仅删除表格中的数据）：

TRUNCATE TABLE 表名称;

12、DROP TABLE – 删除表

DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）。

DROP TABLE 表名称;

二、高级语言篇

1、LIKE – 模糊查询

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SELECT 列名 FROM 表名称 WHERE 列名称 LIKE/NOT LIKE 值;

在 LIKE 模糊查询中，我们使用通配符来代表零个或多个字符，从而能够快速地找到匹配的数据。

• 百分号（%）：表示任意长度的任意字符序列。
• 下划线（_）：表示任意单个字符。

2、IN – 锁定多个值

IN 操作符允许我们在 WHERE 子句中规定多个值。

SELECT 列名/(*) FROM 表名称 WHERE 列名称 IN (值1,值2,值3);

3、BETWEEN – 选取区间数据

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

SELECT 列名/(*) FROM 表名称 WHERE 列名称 BETWEEN 值1 AND 值2;

不同的数据库对 BETWEEN…AND 操作符的处理方式是有差异的。

某些数据库会列出介于 “Adams” 和 “Carter” 之间的人，但不包括 “Adams” 和 “Carter” ；某些数据库会列出介于 “Adams” 和 “Carter” 之间并包括 “Adams” 和 “Carter” 的人；而另一些数据库会列出介于 “Adams” 和 “Carter” 之间的人，包括 “Adams” ，但不包括 “Carter” 。

4、AS – 别名

通过使用 SQL，可以为列名称和表名称指定别名（Alias），别名使查询程序更易阅读和书写。

表别名：

SELECT 列名称/(*) FROM 表名称 AS 别名;

列别名：

SELECT 列名称 as 别名 FROM 表名称;

注意： 实际应用时，这个 AS 可以省略，但是列别名需要加上 " "

5、JOIN – 多表查询

JOIN 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。

在 SQL 中，关联查询是一种用于联合多个数据表中的数据的查询方式。

CROSS JOIN 是一种简单的关联查询，不需要任何条件来匹配行，它直接将左表的 每一行 与右表的 每一行 进行组合，返回的结果是两个表的笛卡尔积。

SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
CROSS JOIN departments d;

INNER JOIN 是一种常见的关联查询方式，它根据两个表之间的关联条件，将满足条件的行组合在一起。

注意，INNER JOIN 只返回两个表中满足关联条件的交集部分，即在两个表中都存在的匹配行。

SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
JOIN departments d ON e.department = d.department;

OUTER JOIN 是一种关联查询方式，它根据指定的关联条件，将两个表中满足条件的行组合在一起，并 包含没有匹配的行 。

在 OUTER JOIN 中，包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型，它们分别表示查询左表和右表的所有行（即使没有被匹配），再加上满足条件的交集部分。

SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
LEFT JOIN departments d ON e.department = d.department;

6、EXISTS - 子查询

子查询是指在一个查询语句内部 嵌套 另一个完整的查询语句，内层查询被称为子查询。子查询可以用于获取更复杂的查询结果或者用于过滤数据。

-- 主查询
SELECT name, total_amount
FROM customers
WHERE customer_id IN (
    -- 子查询
    SELECT DISTINCT customer_id
    FROM orders
    WHERE total_amount > 200
);

子查询中的一种特殊类型是 “exists” 子查询，用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据。

-- 主查询
SELECT name, total_amount
FROM customers
WHERE EXISTS (
    -- 子查询
    SELECT customer_id
    FROM orders
    WHERE orders.customer_id = customers.customer_id
);

和 exists 相对的是 not exists，用于查找不满足存在条件的记录。

7、UNION – 组合查询

UNION 操作：它用于将两个或多个查询的结果集合并， 并去除重复的行 。即如果两个查询的结果有相同的行，则只保留一行。

UNION 语法：

SELECT 列名 FROM 表A
UNION
SELECT 列名 FROM 表B;

UNION ALL 操作：它也用于将两个或多个查询的结果集合并， 但不去除重复的行 。即如果两个查询的结果有相同的行，则全部保留。

UNION ALL 语法：

SELECT 列名 FROM 表A
UNION ALL
SELECT 列名 FROM 表B;

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

8、NOT NULL – 非空

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

CREATE TABLE 表
(
列 int NOT NULL
);

拓展小知识：NOT NULL 也可以用于查询条件：

select * from persons where FirstName is not null;

9、LIMIT - 截断和偏移

LIMIT 后只跟一个整数，表示要截断的数据条数（一次获取几条）

select * from 表名 limit 2;

LIMIT 后跟 2 个整数，依次表示从第几条数据开始、一次获取几条

select * from 表名 limit 2, 2;

10、CASE WHEN - 条件分支

case when 支持同时指定多个分支，示例语法如下：

CASE WHEN (条件1) THEN 结果1
	   WHEN (条件2) THEN 结果2
	   ...
	   ELSE 其他结果 END 
	   AS 别名

11、GROUP BY – 分组聚合

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

语法：

SELECT 列名A, 统计函数(列名B)
FROM 表名
WHERE 查询条件
GROUP BY 列名A;

12、HAVING - 分组后过滤

HAVING 子句用于在分组聚合后对分组进行过滤。它允许我们对分组后的结果进行条件筛选，只保留满足特定条件的分组。

-- 查询订单总金额超过100的客户
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;

三、SQL常用函数篇

SQL 拥有很多可用于计数和计算的内建函数。

函数的使用语法：

SELECT function(列) FROM 表;

1、AVG – 平均值

AVG 函数返回数值列的平均值。NULL 值不包括在计算中。

语法：

SELECT AVG(列名) FROM 表名;

2、COUNT – 汇总行数

COUNT() 函数返回匹配指定条件的行数。

count() 中可以有不同的语法：

• COUNT(*) ：返回表中的记录数。
• COUNT(DISTINCT 列名) ：返回指定列的不同值的数目。
• COUNT(列名) ：返回指定列的值的数目（NULL 不计入）。

SELECT COUNT(*) FROM 表名;
SELECT COUNT(DISTINCT 列名) FROM 表名;
SELECT COUNT(列名) FROM 表名;

3、MAX – 最大值

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。

语法：

SELECT MAX(列名) FROM 表名;

4、MIN – 最小值

MIN 函数返回一列中的最小值。NULL 值不包括在计算中。

语法：

SELECT MIN(列名) FROM 表名;

5、SUM – 求和

SUM 函数返回数值列的总数（总额）。

语法：

SELECT SUM(列名) FROM 表名;

6、UCASE/UPPER – 大写

UCASE/UPPER 函数把字段的值转换为大写:

语法：

select upper(列名) from 表名;

7、LCASE/LOWER – 小写

LCASE/LOWER 函数把字段的值转换为小写:

语法：

select lower(列名) from 表名;

8、ROUND – 数值取舍

ROUND 函数用于把数值字段舍入为指定的小数位数。

语法：

select round(列名,精度) from 表名;

9、LEN/LENGTH – 获取长度

LEN/LENGTH 函数返回文本字段中值的长度:

语法：

select length(列名) from 表名;

10、DATE/TIME - 时间函数

时间函数是用于处理日期和时间的特殊函数。

常用的时间函数有：

• DATE：获取当前日期
• DATETIME：获取当前日期时间
• TIME：获取当前时间

-- 获取当前日期
SELECT DATE() AS current_date;

-- 获取当前日期时间
SELECT DATETIME() AS current_datetime;

-- 获取当前时间
SELECT TIME() AS current_time;

11、Partition By - 窗口函数

partition by其实就是根据某个字段将数据分块，然后可以对该分块数据再做查询（包括聚合查询）

12、 Sum Over - 分组统计开窗函数

开窗函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息 。

开窗函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少。实现了分组统计。

SUM(计算字段名) OVER (PARTITION BY 分组字段名)

sum over order by，可以实现同组内数据的 累加求和 。

SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)

。

13、Rank - 排名开窗函数

Rank 开窗函数是 SQL 中一种用于对查询结果集中的行进行 排名 的开窗函数。它可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名。

当存在并列（相同排序值）时，Rank 会跳过后续排名，并保留相同的排名。

Rank 开窗函数的语法如下：

RANK() OVER (
  PARTITION BY 列名1, 列名2, ... -- 可选，用于指定分组列
  ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS rank_column

其中，PARTITION BY 子句可选，用于指定分组列，将结果集按照指定列进行分组；ORDER BY 子句用于指定排序列及排序方式，决定了计算 Rank 时的排序规则。AS rank_column 用于指定生成的 Rank 排名列的别名。

14、Row_number - 唯一连续排名开窗函数

Row_Number 开窗函数是 SQL 中的一种用于为查询结果集中的每一行 分配唯一连续排名 的开窗函数。

它与之前讲到的 Rank 函数，Row_Number 函数为每一行都分配一个唯一的整数值，不管是否存在并列（相同排序值）的情况。每一行都有一个唯一的行号，从 1 开始连续递增。

Row_Number 开窗函数的语法如下（几乎和 Rank 函数一模一样）：

ROW_NUMBER() OVER (
  PARTITION BY column1, column2, ... -- 可选，用于指定分组列
  ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS row_number_column

其中，PARTITION BY子句可选，用于指定分组列，将结果集按照指定列进行分组。ORDER BY 子句用于指定排序列及排序方式，决定了计算 Row_Number 时的排序规则。AS row_number_column 用于指定生成的行号列的别名。

15、Lag / Lead - 获取当前行前后列值函数

开窗函数 Lag 和 Lead 的作用是获取在当前行之前或之后的行的值，这两个函数通常在需要比较相邻行数据或进行时间序列分析时非常有用。

1）Lag 函数

Lag 函数用于获取 当前行之前 的某一列的值。它可以帮助我们查看上一行的数据。

Lag 函数的语法如下：

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)

参数解释：

• column_name：要获取值的列名。
• offset：表示要向上偏移的行数。例如，offset为1表示获取上一行的值，offset为2表示获取上两行的值，以此类推。
• default_value：可选参数，用于指定当没有前一行时的默认值。
• PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

2）Lead 函数

Lead 函数用于获取 当前行之后 的某一列的值。它可以帮助我们查看下一行的数据。

Lead 函数的语法如下：

LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)

参数解释：

• column_name：要获取值的列名。
• offset：表示要向下偏移的行数。例如，offset为1表示获取下一行的值，offset为2表示获取下两行的值，以此类推。
• default_value：可选参数，用于指定当没有后一行时的默认值。
• PARTITION BY和ORDER BY子句可选，用于分组和排序数据。