[数据库复习] SQL 语句

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

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

连接问题

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

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

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

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

创建

1
2
3
4
5
6
7
8
9
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`)
);

创建好的表如下所示:

修改

添加列

1
2
ALTER TABLE test_table
ADD new_col VARCHAR(20);

删除 col3 这一列:

1
2
ALTER TABLE test_table
DROP COLUMN col3;

删除 test_table 表:

1
DROP TABLE test_table;

插入

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

1
2
INSERT INTO test_table(col1)
VALUES (114514);

将查询到的结果插入表:

1
2
3
INSERT INTO test_table(col1, col2)
SELECT col1, col2
FROM test_table;

拷贝表(只会拷贝数据和类型,不会拷贝表结构,比如主键等):

1
2
3
CREATE TABLE new_table AS
SELECT *
FROM test_table

更新

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

1
2
3
UPDATE new_table
SET col2='col2_val'
WHERE id = 8;

删除

删除 id 为 10 的记录:

1
2
3
DELETE
FROM test_table
WHERE id = 10

清空 test_table 表:

1
TRUNCATE TABLE test_table;

查询

DISTINCT

相同值只出现一次,列值完全相同才算做相同:

1
2
SELECT DISTINCT col1, col2
FROM new_table;

LIMIT

限制返回的行数。

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

显示查询的前三行:

1
2
3
SELECT *
FROM new_table
LIMIT 3;

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

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

1
2
3
SELECT *
FROM new_table
LIMIT 2, 5;

排序

  • ASC 升序(默认)
  • DESC 降序

e.g.

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

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

查询结果如图所示:

过滤

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

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

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

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

e.g.

1
2
3
SELECT *
FROM test_table
WHERE col1 != 114514;

计算字段

使用 AS 来取别名,比如:

1
2
SELECT col1 * id AS temp
FROM test_table;

否则列名会变成 col1 * id

CONCAT 可将字段进行连接,比如:

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

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

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

函数

汇总

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

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

1
2
SELECT AVG(col1)
FROM test_table;

结果为 467347.6000

计算 col1 所有列值的平均值(不包含重复列值):

1
2
SELECT AVG(DISTINCT col1)
FROM test_table;

结果为 408542.0000

字符串

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

e.g.

在表中新添加以下记录:

1
2
3
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() 返回一个日期的年份部分
1
2
3
4
SELECT now();

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

数值

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

分组

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

e.g.

表中数据如下:

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

查询结果:

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

e.g.

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

查询结果只有以下一条:

子查询

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

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

1
2
3
SELECT *
FROM test_table
WHERE col1 IN (SELECT col2 FROM test_table)

连接

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

内连接

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

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

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

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

自连接

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

e.g.

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

子查询:

1
2
3
4
5
6
SELECT name
FROM employee
WHERE department = (
SELECT department
FROM employee
WHERE name = "Jim");

自连接:

1
2
3
4
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. *

1
2
3
4
5
6
7
SELECT col1
FROM test_table
WHERE col1 = 1
UNION
SELECT col1
FROM test_table
WHERE col1 = 114514;

视图

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

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

视图具有如下好处:

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

e.g.

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

1
2
3
4
5
6
7
8
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 中指定该保留点,则会回退到该保留点。

1
2
3
4
5
6
7
START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

参考:
CyC2018/CS-Notes

Author: Boli Tao
Link: https://www.bolitao.xyz/archives/90ed28ae.html
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.