SQL 数据库¶

Cheat Sheet

单表查询¶

`SELECT`¶

SQL

SELECT
    name
    price
    price*0.9 AS 'discouted price'
FROM
    tablename
WHERE
    NOT (age < 10 OR (price > 100 AND city=Shanghai))
ORDER BY
    name

SQL语句不区分大小写，即SELECT、Select、select是同样的。但建议关键词用大写，方便阅读，让代码更规范。
可以在SELECT中做算术运算，生成新的列，并用AS自定义列名。
WHERE用于筛选。其中，AND的优先级比OR高。也就是说，age < 10 OR price > 100 AND city=Shanghai相当于age < 10 OR (price > 100 AND city=Shanghai)。

`WHERE` 筛选¶

不允许在 WHERE 语句后面引用别名，可以用 HAVING 代替 WHERE 。

题目

https://www.nowcoder.com/practice/d8a624021183454586da94d280cc8046

易错点，执行顺序是 from -> where -> group by -> having -> select -> order by -> limit。

所以如果在 where 后面限制 total_price，这时候 select 里的 SUM 都没开始计算，OrderItems里没有 total_price，所以会报错 where 找不到 total_price。

`DISTINCT`：提取唯一值¶

SQL

-- 选择所有不同的特定列（这里是 country 列）的唯一值
SELECT DISTINCT country
FROM customers;

在上述代码中，我们使用了 SELECT DISTINCT 命令来从 customers 表中提取唯一的 country 列数据。DISTINCT 关键字告诉数据库只返回不同的值，而不是重复的值。

如果 customers 表中有多个相同的国家，那么该查询将仅返回一个该国家的唯一值，而不是每个重复的实例。

这是适用于许多情况的有用的功能，例如从表中查找唯一值以进行分析或过滤掉重复值，以使结果更具可读性。

`IN`：等于多个值中的一个¶

SQL

SELECT *
FROM customers
WHERE state IN ('VA', 'NY', 'CA') # (1)!

1. IN 后面用圆括号，而不是方括号。

等价于

SQL

SELECT *
FROM customers
WHERE state = 'VA' OR state = 'NY' OR state = 'CA'

`BETWEEN`：介于两者之间¶

SQL

SELECT *
FROM customers
WHERE points BETWEEN 100 AND 200

等价于（两端是大于等于和小于等于，而不是大于和小于）

SQL

SELECT *
FROM customers
WHERE points >= 100 AND points <= 200

`LIKE`：筛选字符串¶

SQL

SELECT *
FROM customers
WHERE first_name LIKE 'b%'

%代表任意长度的字符，可以是 0 个。
这句话意思是筛选出first_name是以b或B开头的数据。

SQL

SELECT *
FROM customers
WHERE first_name LIKE '%b%'

这句话意思是筛选出first_name是包含b或B的数据，b或B的前后可以有任何字符。

SQL

SELECT *
FROM customers
WHERE first_name LIKE '_b'

_代表一个长度的字符，不能多也不能少，必须刚好一个长度。
这句话意思是筛选出first_name是以b或B结尾的，且前面有一个字符的数据。

`REGEXP`：正则表达式筛选字符串¶

SQL

SELECT *
FROM customers
WHERE first_name REGEXP '^a' # (1)!

REGEXP 后面用单引号将正则表达式放入其中。
^: beginning of a string
$: end of a string
|: logical OR
[abc]: match any single characters
[a-d]: any characters from a to d，这样就不用把 [abcd] 全部写出来了。

`SUBSTRING`：从一个字符串中提取一个子字符串¶

SUBSTRING 需要至少两个参数：要提取的字符串和要提取的子字符串的起始位置。

SUBSTRING 的语法如下：

SQL

SUBSTRING(string, start_position, length)

其中，

string 是要提取子字符串的原始字符串。
start_position 是子字符串的起始位置，可以是一个整数值或一个表达式。
length 是要提取的子字符串长度，可以是一个整数值或一个表达式。如果省略此参数，则将提取从起始位置到字符串末尾的所有字符。

例如，以下代码将从字符串 "Hello, world!" 中提取子字符串 "world"：

SQL

SELECT SUBSTRING('Hello, world!', 8, 5);

输出：

world SUBSTRING() 还支持在 WHERE 子句中使用，以过滤基于子字符串的查询结果。

`CONCAT()`：连接两个或多个字符串¶

在 SQL 中，CONCAT() 函数用于将两个或多个字符串连接在一起。它接受任意数量的字符串参数，并将它们连接成一个字符串。

语法：

SQL

CONCAT(string1, string2, ...)

参数：

string1, string2, ...：要连接的字符串。

返回值：

连接后的字符串。

示例：

SQL

SELECT CONCAT('Hello', ' ', 'World') AS Greeting;

输出：

Text Only

Greeting
Hello World

注意：

如果任何一个参数为 NULL，则整个结果为 NULL。
如果参数都是数字，则它们将被转换为字符串并连接在一起。

`IS NULL`：筛选空值¶

SQL

SELECT *
FROM customers
WHERE phone IS NULL

`ORDER BY`：排序¶

SQL

SELECT *
FROM customers
ORDER BY state, first_name DESC

ORDER BY两个字段，实现双重排序。
DESC是按降序排列。

还可以按多列排序，如下所示：

SQL

SELECT *
FROM table_name
ORDER BY column1 ASC, column2 DESC;

其中，column1 表示第一列，ASC 表示升序排列，column2 表示第二列，DESC 表示降序排列。

注意

ORDER BY 语句必须放在 WHERE 之后。

`LIMIT`：取前几条数据¶

SQL

SELECT *
FROM customers
LIMIT 3

限制返回的结果条数。最终只返回 3 条数据。

SQL

SELECT *
FROM customers
LIMIT 6, 3

先跳过前 6 条数据，再返回第 7 至 9 条数据。

连接¶

`INNER JOIN`：内连接¶

SQL

SELECT c.customer_id, first_name, last_name
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id

FROM customers c是给customers起一个别名c，之后就可以用c来代替customers，起到简化代码的作用。
因为内连接的逻辑是按照c.customer_id = o.customer_id进行连接，所以得到的表有两列都是customer_id。这个时候，如果我们想SELECT customer_id，就必须指定customer_id是来自哪个表（其实来自哪个表都一样，因为本来就是内连接的，所以结果中的customer_id必然一样）。例如上面的代码就指定来自c.customer_id。如果不指定，则会报错说 ambiguous。

从不同的数据库中跨表连接¶

SQL

SELECT *
FROM customers c
JOIN sql_inventory.product p
    ON c.customer_id = o.customer_id

由于product在sql_inventory数据库，所以customers和product不在同一个数据库，在对它们进行连接时需要指定product在sql_inventory数据库。
只需要指定那些不在当前数据库中的数据表。如果数据表本来就在当前数据库，则不需要指定了。

将同一张表当作两张表进行连接（自身连接）¶

一张表中有员工 ID和员工对应的经理，我们想找到员工和经理的对应关系。

SQL

SELECT
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM emploees e
JOIN emploees m
    ON e.reports_to = m.employee_id

可以将同一张表设置多个别名，当成多个表来使用。

`OUTER JOIN`：外连接¶

JOIN默认是INNER JOIN。
有两种类型的OUTER JOIN：LEFT JOIN和RIGHT JOIN。
LEFT JOIN会保留左边的表的所有数据，不论后面的连接配对是否成功。
RIGHT JOIN会保留右边的表的所有数据，不论后面的连接配对是否成功。
LEFT JOIN等价于LEFT OUTER JOIN，中间的OUTER可以省略不写。

SQL

SELECT *
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id

因为阅读代码的顺序是从上到下，因此建议用LEFT JOIN，这样可以更容易理解连接的逻辑。
如果用RIGHT JOIN，则需要先想一下RIGHT JOIN后面的表长什么样子，再想前面的表，这样比较麻烦。

`USING`：列名相同时只需指定一个列名¶

SQL

SELECT *
FROM customers c
JOIN orders o
USING (customer_id)

等价于

SQL

SELECT *
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id

`CROSS JOIN`：交叉连接，也叫笛卡尔乘积¶

SQL

SELECT *
FROM colors
CROSS JOIN sizes

若colors有 3 行，sizes有 4 行，则CROSS JOIN后有 12 行。

`UNION`：上下连接¶

SQL

SELECT name, address
FROM customers
UNION
SELECT name, address
FROM clients

UNION内部的每个SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个SELECT语句中的列的顺序必须相同。

函数¶

聚合函数¶

avg(expr) − average value for rows within the group
count(expr) − count of values for rows within the group
max(expr) − maximum value within the group
min(expr) − minimum value within the group
sum(expr) − sum of values within the group

插入数据¶

数据属性¶

点击图中的工具图标，查看每列数据的属性。

PK，Primary Key：主键，唯一确定一个数据。主键可以由多个变量组成。
NN，Not Null：是否必须非空。如果打勾，则说明这个变量不能是空值。
AI，Auto Incremental：自动递增。当有新的数据插入时，自动把这个变量加 1。这在主键上经常用，通常 ID 就是逐渐加 1 的。
Default：若不指定，则默认值是多少。
Datatype中的VARCHAR(50)是 Variable Characters，意思是最多 50 个字符的字符串。如果用CHAR(50)则说明必须是 50 个字符的字符串，如果不足 50 个，系统会自动在后面补上空格，这有时是很耗费空间的。
Datatype中的DECIMAL不存在精度损失，数据类型DECIMAL(p,s)需要分别指定小数的最大位数（p）和小数位的数量（s）：
例如DECIMAL(4,2)代表小数点左右两边所有的数字一共最多 4 个，小数点右边保留 2 个数字。
p (precision) ：指定小数的最大位数，小数点的左侧和右侧的数字的总数量不能超过 p，p 的取值范围是从 1 到 38，默认值为 18。
s (scale)：指定在小数点右侧的小数位数，p-s 是小数点左边的最大位数。s 必须是从 0 到 p 的值，只有在指定了精度的情况下才能指定 s，s 的默认值是 0，因此，0 <= s <= p。

`INSERT`：插入数据¶

SQL

INSERT INTO customers(first_name, phone, points)
VALUES ('Mosh', NULL, DEFAULT)

NULL代表空值。
DEFAULT代表默认值，默认值具体是多少，可以在上图中查看。

一次插入多行¶

SQL

INSERT INTO customers(first_name, phone, points)
VALUES
('Mosh', NULL, DEFAULT),
('Bob', '1234', 10)

将整个表插入到另外的表¶

SQL

CREATE TABLE orders_archived AS
SELECT *
FROM order

将order表格整个地复制到新表中，成为orders_archived。
新表不会记录原表格的主键等信息。

更新数据¶

SQL

UPDATE table_name
SET column_1 = 1, column_2 = 2
WHERE id IN (1, 2, 3)

用WHERE筛选出想要筛选的数据后，用SET更新想要更新的列数据。

删除数据¶

SQL

DELETE FROM table_name
WHERE ID in (1, 2, 3)

用WHERE筛选出想要筛选的数据后，用DELETE FROM删除想要删除的一整行数据。

SQL 数据库¶

单表查询¶

SELECT¶

WHERE 筛选¶

DISTINCT：提取唯一值¶

IN：等于多个值中的一个¶

BETWEEN：介于两者之间¶

LIKE：筛选字符串¶

REGEXP：正则表达式筛选字符串¶

SUBSTRING：从一个字符串中提取一个子字符串¶

CONCAT()：连接两个或多个字符串¶

IS NULL：筛选空值¶

ORDER BY：排序¶

LIMIT：取前几条数据¶

连接¶

INNER JOIN：内连接¶