[数据库复习]SQL语句

数据库 SQL 语句的一些复习笔记。

最近看 leetcode 上的数据库题时，感觉又拾回了上数据库课写那种脑筋急转弯题目一样的感觉。

连接问题

我电脑 Windows 下 IDEA、datagrip、JDBC 使用默认设置会连接不上本地 MySQL，jdbc 参数后加 ?characterEncoding=UTF-8&serverTimezone=GMT%2B8 即可。

第一个参数是指定编码为 UTF-8，第二个指定时区为 GMT+8。

时区错误可能是之前我为了解决双系统时间不同步，使用了偏方，修改了 Windows 默认计时方式。

本人测试在 Ubuntu 和 mscOS 下可以不添加这些参数。

创建

CREATE TABLE test_table
(
    id   INT         NOT NULL AUTO_INCREMENT,
    col1 INT         NOT NULL DEFAULT 1,
    col2 VARCHAR(45) NULL,
    col3 DATE        NULL,
    # 主键
    PRIMARY KEY (`id`)
);

创建好的表如下所示：

修改

添加列

ALTER TABLE test_table
    ADD new_col VARCHAR(20);

删除 col3 这一列：

ALTER TABLE test_table
    DROP COLUMN col3;

删除 test_table 表：

DROP TABLE test_table;

插入

向 test_table 插入 col1 值为 114514 的一条记录：

INSERT INTO test_table(col1)
VALUES (114514);

将查询到的结果插入表：

INSERT INTO test_table(col1, col2)
SELECT col1, col2
FROM test_table;

拷贝表（只会拷贝数据和类型，不会拷贝表结构，比如主键等）：

CREATE TABLE new_table AS
SELECT *
FROM test_table

更新

将 id 为 8 的 col2 记录改为 col2_val：

UPDATE new_table
SET col2='col2_val'
WHERE id = 8;

删除

删除 id 为 10 的记录：

DELETE
FROM test_table
WHERE id = 10

清空 test_table 表：

TRUNCATE TABLE test_table;

查询

DISTINCT

相同值只出现一次，列值完全相同才算做相同：

SELECT DISTINCT col1, col2
FROM new_table;

LIMIT

限制返回的行数。

接受两个参数，第一个表示偏移量，起始为 0，可省略，第二个参数表示返回记录的最大数。

显示查询的前三行：

SELECT *
FROM new_table
LIMIT 3;

上面的查询语句等价为 LIMIT 0, 3。

偏移 2，最多返回五条记录，即返回 3 - 7 行，从第三行开始只有四条记录，所以只返回四条：

SELECT *
FROM new_table
LIMIT 2, 5;

排序

ASC 升序（默认）
DESC 降序

e.g.

查询 test_table 中所有数据，优先按 col1 降序排列，col1 相等时按 col2 升序排列：

SELECT *
FROM test_table
ORDER BY col1 DESC, col2 ASC;

查询结果如图所示：

过滤

操作符	说明
=	等于
<	小于
>	大于
!= <>	不等于
<= !>	小于等于
>= !<	大于等于
BETWEEN	在两个值之间
IS NULL	为 null 值

AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。

IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。

NOT 操作符用于否定一个条件。

e.g.

SELECT *
FROM test_table
WHERE col1 != 114514;

计算字段

使用 AS 来取别名，比如：

SELECT col1 * id AS temp
FROM test_table;

否则列名会变成 col1 * id。

CONCAT 可将字段进行连接，比如：

SELECT CONCAT(id, ', ', col1, '(', col2, ')') AS result
FROM test_table;

某些数据库会用空格把某个值进行填充，使用 TRIM() 可以去除首尾空格，e.g.

SELECT CONCAT(id, ', ', TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM test_table;

函数

汇总

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

计算 col1 所有列值的平均值：

SELECT AVG(col1)
FROM test_table;

结果为 467347.6000。

计算 col1 所有列值的平均值（不包含重复列值）：

SELECT AVG(DISTINCT col1)
FROM test_table;

结果为 408542.0000。

字符串

函数	说明
LEFT()	左边的字符
RIGHT()	右边的字符
LOWER()	转换为小写字符
UPPER()	转换为大写字符
LTRIM()	去除左边的空格
RTRIM()	去除右边的空格
LENGTH()	长度
SOUNDEX()	转换为语音值

e.g.

在表中新添加以下记录：

SELECT *
FROM test_table
WHERE SOUNDEX(col2) = SOUNDEX('apple')

日期和时间

日期格式：YYYY-MM-DD
时间格式：HH:MM:SS

函数	说明
ADDDATE()	增加一个日期（天、周等）
ADDTIME()	增加一个时间（时、分等）
CURDATE()	返回当前日期
CURTIME()	返回当前时间
DATE()	返回日期时间的日期部分
DATEDIFF()	计算两个日期之差
DATE_ADD()	高度灵活的日期运算函数
DATE_FORMAT()	返回一个格式化的日期或时间串
DAY()	返回一个日期的天数部分
DAYOFWEEK()	对于一个日期，返回对应的星期几
HOUR()	返回一个时间的小时部分
MINUTE()	返回一个时间的分钟部分
MONTH()	返回一个日期的月份部分
NOW()	返回当前日期和时间
SECOND()	返回一个时间的秒部分
TIME()	返回一个日期时间的时间部分
YEAR()	返回一个日期的年份部分

SELECT now();

# 结果：
2019-06-05 21:56:04

数值

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

分组

把具有相同数值的某查询记录放在同一组中，可以方便进行统计、求值等操作。

e.g.

表中数据如下：

SELECT col1, COUNT(*) AS `count`
FROM test_table
GROUP BY col1;

查询结果：

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

e.g.

SELECT col1, COUNT(*) AS `count`
FROM test_table
WHERE col1 > 1
GROUP BY col1
HAVING count > 1;

查询结果只有以下一条：

子查询

子查询中只能返回一个字段的数据。

可以将子查询的结果作为 WHRER 语句的过滤条件：

SELECT *
FROM test_table
WHERE col1 IN (SELECT col2 FROM test_table)

连接

连接可以替换子查询，一般效率比子查询快。

内连接

内连接又称等值连接，使用 INNER JOIN 关键字。

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

自连接

可看成是内连接，只是连接对象变成了自己。

e.g.

一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。

子查询：

SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

自连接：

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

自然连接

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

组合查询

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

*e.g. *

SELECT col1
FROM test_table
WHERE col1 = 1
UNION
SELECT col1
FROM test_table
WHERE col1 = 114514;

视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

视图具有如下好处：

简化复杂的 SQL 操作，比如复杂的连接；
只使用实际表的一部分数据；
通过只给用户访问视图的权限，保证数据的安全性；
更改数据格式和表示。

e.g.

创建一个 test_view 视图，其结果和上一个例子一致。

CREATE VIEW test_view AS
SELECT col1
FROM test_table
WHERE col1 = 1
UNION
SELECT col1
FROM test_table
WHERE col1 = 114514;

事务

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

参考： CyC2018/CS-Notes