SQL-技巧积累

联结 Join

联结 (Join) 指, 把两个或多个表的数据组合在一起.

内联结 Inner Join

返回两个表符合条件的行中的交集部分.

外联结 Outer Join

左联结

返回左表的所有行, 以及右表中满足条件的行.

右联结

返回右表的所有行, 以及左表中满足条件的行.

全联结

返回左表和右表中的所有行.

交叉联结 Cross Join

返回两个表中所有行的笛卡尔积.

区分 group by 和 order by

注意 group by 的 “分组”, 指的是将 “相同值合并”, 也就是说一个值只会出现一次. 此时, 值相同的就位于一个组中, 然后再对每一个组进行查询.

group by 的作用

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(50),
    amount NUMERIC(10, 2),
    order_date DATE
);

INSERT INTO orders (customer_name, amount, order_date) VALUES
    ('Alice', 100.50, '2023-01-01'),
    ('Bob', 200.75, '2023-01-02'),
    ('Alice', 150.25, '2023-01-03'),
    ('Charlie', 75.80, '2023-01-04'),
    ('Bob', 300.00, '2023-01-05');

SELECT customer_name, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_name;

结果为:

customer_name | order_count | total_amount
---------------+-------------+--------------
 Alice         |           2 |       250.75
 Bob           |           2 |       500.75
 Charlie       |           1 |        75.80

查看当前用户

SELECT USER();

已存在的数据库

SHOW DATABASES LIKE 'name';

进入一个数据库

USE databasename;

显示数据库中的所有表

SHOW TABLES;

显示一个表中的所有列

DESCRIBE tablename;

解决 mysql 无法存储中文

其问题在于没有将数据库的字符集设置为支持中文的编码, 如 utf8mb4.

需注意:

• 表的字符集和列的字符集都应该是相同的，并且应该与数据库字符集一致
• 连接 MySQL 时要设置正确的字符集，如在连接时执行 SET NAMES 'utf8mb4' (NAMES 指定之后的 'utf8mb4')

查看当前的编码格式:

SHOW VARIABLES LIKE '%char%';

查看一个数据库中一个表的字符集:

SHOW CREATE TABLE table_name;

直接设置

将一个数据库的字符集设置为 utf8mb4:

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

将一个表的字符集设置为 utf8mb4:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

通过修改配置文件来设置

修改 /etc/my.cnf 或 /etc/my.ini, 如:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
# ...
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

其中，[client] 和 [mysql] 用于客户端连接 MySQL 服务器时的默认字符集，[mysqld] 是 MySQL 服务器的默认字符集。

在上述配置中，utf8mb4 是比较常用的字符集编码，它是 UTF-8 的超集，支持更多的 Unicode 字符。同时还设置了与之对应的排序规则 utf8mb4_unicode_ci，保证数据库中存储的文本数据以及索引的排序符合 Unicode 标准。需要注意的是，如果要使用 utf8mb4 字符集，还需要确认 MySQL 服务器和客户端的版本都支持该字符集。

在修改完配置文件后，需要重启 MySQL 服务使得配置生效。

关于 Primary Key

这个列不能有相同元素.

关于 AUTO_INCREMENT

MySQL 中 AUTO_INCREMENT 属性只能作用于整数类型（INT、BIGINT、SMALLINT 和 TINYINT）的列。这是因为在实现自增 ID 的过程中，需要对该列的取值进行加一操作，只有整数类型的数据才能进行准确的加法计算。在 MySQL 中，关于 AUTO_INCREMENT 属性的使用还有一些限制：

表中最多只能有一个 AUTO_INCREMENT 列。
AUTO_INCREMENT 只能创建在单列上。
如果表中 AUTO_INCREMENT 列的值被手动修改，那么这个新值将被作为之后插入行的新起始值。

需要注意的是，虽然 AUTO_INCREMENT 列会按照自增规则生成下一个 ID 值，但并不能保证 ID 的连续性和不重复性。如果想要保证 ID 值的唯一性，请在该列上添加 UNIQUE 约束。