bachzart's Blog
0%

SQL 的一些基础知识点

Posted on Edited on In ,

记录一下 SQL 的一些相关知识。

Concept

SQL，全称 Structured Query Language，直译就是结构化的查询语言。按照百度百科的解释，这是一种特殊目的的编程语言，是一种数据库查询和程序设计语言。目前，对它的认识到这里就可以了。

Composition

一个数据库通常包含一个或多个表。每个表由一个名字标识（有点像变量），表内包含带有数据的记录，使用 SQL 语句就可以对这些数据进行增、删、改、查。

SQL Language

SQL 可以分成两个部分：数据操作语言（DML）和数据定义语言（DDL）。注意，SQL 对大小写不敏感！
查询和更新指令构成了 SQL 的 DML 部分，包括：SELECT、UPDATE、DELETE、INSERT INTO 等。
数据定义语言（DDL）使用户可以创建或删除表格，或者是定义索引（键），规定表之间的链接，以及施加表间的约束。这类语句包括：CREATE DATABASE、ALTER DATABASE、CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE INDEX、DROP INDEX 等。

SELECT

SELECT 语句用于从表中选取数据。

1
2
3
SELECT 列名称 FROM 表名称
SELECT * FROM 表名称
SELECT name1,name2 FROM 表名称

DISTINCT

DISTINCT 算是一个关键词，用于返回唯一不同的值，可以用来修饰 SELECT。

1
SELECT DISTINCT 列名称 FROM 表名称

WHERE

如果需要有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句。

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

WHERE 能使用的运算符包括以下几种：

操作符 描述
= 等于
<> 不等于

| 大于
< | 小于
= | 大于等于
<= | 小于等于
BETWEEN | 在某个范围内
LIKE | 搜索某种模式

注意：在某些版本的 SQL 中，<> 也可以写成 !=。
一般语法：

1
SELECT * FROM 表名称 WHERE 列名称='xxx'

注意：SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号），数据就不用使用引号了。

AND & OR

AND 和 OR **运算符(没错，这是俩运算符，至少在 SQL 里面是的)**用于基于一个以上的条件对记录进行过滤。一般语法：

1
2
SELECT * FROM 表名称 WHERE 列名称='xxx' OR\AND 列名称='xxx'
SELECT * FROM 表名称 WHERE (列名称='x' OR 列名称='y') AND 列名称='z'

ORDER BY

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

1
2
3
4
SELECT 列名称 FROM 表名称 ORDER BY 列名称
SELECT 列名称 FROM 表名称 ORDER BY 列名称1,列名称2
SELECT 列名称 FROM 表名称 ORDER BY 列名称 DESC
SELECT 列名称 FROM 表名称 ORDER BY 列名称1 DESC,列名称2 ASC

INSERT INTO

INSERT INTO 用于向表格中插入新的行，一般语法：

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

UPDATE

UPDATE 语句用于修改表中的数据，一般语法：

1
2
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
UPDATE 表名称 SET 列名称 = 新值,... WHERE 列名称 = 某值

DELETE

DELETE 语句用于删除表中的行，一般语法：

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

如果需要删除所有行，可以写成：

1
2
DELETE FROM 表名称
DELETE * FROM 表名称

此时，表的结构、属性和索引都是完整的。

通配符

通配符的作用有点类似正则表达式，需要与 LIKE 运算符一起使用，包含以下几种：

通配符 描述
% 代表零个或多个字符
  • | 仅替代一个字符
    [charlist] | 字符列中的任何单一字符
    [^charlist] 或者 [!charlist] | 不在字符中的任何单一字符

一般语法：

1
2
3
4
5
6
7
8
9
10
11
12
# 选出存在以 xx 开头的列的行
SELECT * FROM 表名称 WHERE 列名称 LIKE 'xx%'
# 选出存在包含 xx 的列的行
SELECT * FROM 表名称 WHERE 列名称 LIKE '%xx%'
# 选出存在第一个字符后是 xx 的列的行
SELECT * FROM 表名称 WHERE 列名称 LIKE '_xx%'
# 选出存在形如 X_x 的列的行
SELECT * FROM 表名称 WHERE 列名称 LIKE 'X_x%'
# 选出存在 X/Y/Z 开头的列的行
SELECT * FROM 表名称 WHERE 列名称 LIKE '[XYZ]%'
# 选出不存在 X/Y/Z 开头的列的行
SELECT * FROM 表名称 WHERE 列名称 LIKE '[!XYZ]%'

IF

IF 用来构成条件表达式，一般语法：

1
2
# expr1 为 true，返回值为 expr2, 否则为 expr3
IF(expr1, expr2, expr3)

IFNULL

作用类似 IF，一般语法：

1
2
# expr1 不为 null 时，返回 expr1，否则返回 expr2
IFNULL(expr1, expr2)

AS

简单来讲，AS 就是用来修改名字的，一般语法：

1
2
# SELECT 列名称 AS 别名 FROM 表名称
SELECT xx AS yy FROM 表名称

CASE WHEN

CASE WHEN 的用法与一般编程语言的 switch 语句类似，一般语法：

1
2
3
4
5
6
CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE result
END;

不同的是，switch 语句需要用 break，但是 CASE WHEN 只会返回一个 result。

GROUP BY

GROUP BY 的作用是根据一个或多个列对结果集进行分组，一般语法：

1
2
3
SELECT 列名称, 功能函数 FROM 表名称
WHERE xxx
GROUP BY 列名称

MIN

MIN 函数用来返回一列中的最小值，NULL 不包括在内，一般语法：

1
SELECT MIN(列名称) FROM 表名称

UPPER

将字符转换为小写，一般语法：

1
UPPER(字符串)

LOWER

将字符转换为大写，一般语法：

1
LOWER(字符串)

CONCAT

用于将 2 个字符串连接起来，一般语法：

1
CONCAT(string1, string2)

GROUP_CONCAT

GROUP_CONCAT 用来连接多个字符串（字段），一般用法：

1
2
3
4
5
6
# 会将所有列名称连接起来，默认用 , 隔开
GROUP_CONCAT(列名称)
# 会将所有不同列名称连接起来，默认用 , 隔开
GROUP_CONCAT(DISTINCT 列名称)
# 会将所有列名称连接起来，自行设置分隔符
GROUP_CONCAT(列名称, 分隔符)

SUBSTRING

用于截取字符串，一般语法：

1
2
3
4
5
6
7
8
# 截取 string 的第 1 个字符后的所有字符
SUBSTRING(string, 1)
# 截取 string 的第 1 个字符后的 3 个字符
SUBSTRING(string, 1, 3)
# 截取 string 的倒数第 3 个字符
SUBSTRING(string, -3)
# 截取 string 从倒数第 3 个字符开始取 2 个字符
SUBSTRING(string, -3, 2)

还有很多其他的用法，不一一列举了。

LEFT

算是 SUBSTRING 的简化版，一般语法：

1
2
# 截取 string 左边的 4 个字符
LEFT(string, 4)

算是 SUBSTRING 的简化版，一般语法：

1
2
# 截取 string 右边的 4 个字符
RIGHT(string, 4)

COUNT

COUNT 函数用来返回指定了列的值的数目（NULL 不计入），一般语法：

1
2
3
4
5
SELECT COUNT(列名称) FROM 表名称
# 返回表中的记录数
SELECT COUNT(*) FROM table_name
# 返回指定列的不同值的数目
SELECT COUNT(DISTINCT 列名称) FROM 表名称

IN / NOT IN

这个两个操作符与字面意思一样，判断数据在不在子集合内，一般用法：

1
2
SELECT * FROM 表名称 WHERE 列名称 IN ('xx', 'yy', 'zz')
SELECT * FROM 表名称 WHERE 列名称 NOT IN ('xx', 'yy', 'zz')

UNION / UNION ALL

UNION 操作符用来合并两个或多个 SELECT 语句的结果集。但是要注意，UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。一般用法：

1
2
3
SELECT 列名 FROM 行名
UNION
SELECT 列名 FROM 行名

UNION ALL 与 UNION 的用处是一样的，但是 UNION ALL 会列出所有值，包括重复的，UNION 则不会。

HAVING

HAVING 的用处类似 WHERE，也是在按条件选取数据。但是 WHERE 关键字无法与合计函数一起使用，一般用法：

1
2
3
4
SELECT 列名, 函数()
FROM 表名
GROUP BY 列名
HAVING 函数()

LIMIT

LIMIT 一般用来查询数据，一般用法：

1
2
3
4
# 读取 y 条数据
LIMIT y
# 跳过 x 条数据，读取 y 条数据
LIMIT x, y

OFFSET

OFFSET 一般和 LIMIT 一起使用：

1
2
# 跳过 x 条数据，读取 y 条数据
LIMIT y OFFSET x

JOIN

JOIN 用于根据两个或多个表种的列之间的关系，从这些表种查询数据。它还有 3 位兄弟：LEFT JOIN、RIGHT JOIN 和 FULL JOIN，区别是：

  1. JOIN：如果表种至少一个匹配，则返回行。
  2. LEFT JOIN：即使右表种没有匹配，也从左表中返回所有的行。
  3. RIGHT JOIN：即使左表中没有匹配，也从右表中返回所有的行。
  4. FULL JOIN：只要其中一个表中存在匹配，就返回行。

插一张图：
不同连接的区别
它们的用法都差不多：

1
2
3
4
SELECT xx
from 表名1
JOIN 表名2
ON 条件

DATEDIFF

DATEDIFF 是用来计算时间差的 SQL 函数，一般用法：

1
2
3
4
5
# startdate 和 enddate 参数是合法的日期表达式
# datepart 参数可以是年、月、日、季度等值
SELECT DATEDIFF(datepart, startdate, enddate) AS DIFFDATE
# 返回结果为 1 
SELECT DATEDIFF(day, '2008-12-29', '2008-12-30') AS DIFFDATE

YEAR

YEAR 函数用来获取字符串中的年份，一般用法：

1
2
# 返回 2018
SELECT YEAR('2018-10-10')

WITH AS

WITH AS 用来定义一个 SQL 片段，这个片段会被整个 SQL 语句所用到，有点类似 C 语言里面的预编译，一般用法：

1
2
3
# 执行 select * from xxx 得到一个片段，这个片段名叫 A
# 注意，这个片段实际上也是一个表
WITH A AS (select * from xxx)

MAX/MIN

两个最值函数，功能不说了，一般用法：

1
2
SELECT MAX(列名) FROM 表名
SELECT MIN(列名) FROM 表名