SQL笔记

发表于 更新于 本文字数： 4.9k 阅读时长 ≈ 18 分钟

以MySQL为准，复习SQL，记录增删改查操作、常用函数以及其他功能。本文SQL语句均在MySQL中实验。

SQL笔记

0 连接

主要是按照CYC2018的CS-Notes的结构进行学习记的笔记：

CS-Notes SQL

菜鸟教程一贯的适合查阅，快速领悟：

MySQL 教程

1 增

1.1 新建数据库（create database）

1
2
CREATE DATABASE temp;
USE temp;
  • 新建一个名为temp的临时数据库。
  • 选中、使用该数据库。

1.2 新建表（create table）

1
2
3
4
5
6
7
8
9
10
/*
  创建一个news表，记录标题、作者、时间。
*/
CREATE TABLE news(
	id INT UNSIGNED AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(40) NOT NULL DEFAULT 'Anonymous',
    time DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

1.3 插入行（insert into）

1
2
INSERT INTO news(title)
VALUES('news1');

插入结果：

id title author time
1 ABC Anonymous 2020-07-03 14:41:01
  • id初始为1；
  • title是插入的指定值；
  • author虽未插入时指定，但有建表时的默认值填充；
  • time未在插入时指定，但有建表时默认以当前时间戳填充。

2 删

2.1 删除数据库（drop database）

1
DROP DATABASE temp;

2.2 删除表（drop table）

1
DROP TABLE news;

2.3 删除行（delete）

删除一行：

1
2
DELETE FROM news 
WHERE id=1;

删除所有行（清空表）：

1
TRUNCATE TABLE mytable;

3 改

3.1 改表结构（alter table）

插入列：

1
2
ALTER TABLE news
ADD COLUMN isValid bool;

删除列：

1
2
ALTER TABLE news
DROP COLUMN isValid;

3.2 改行数据（update）

1
2
3
UPDATE news
SET author = 'Tom'
WHERE id = 1;

4 查

4.1 查行数据（select）

4.1.1 基本查询(select ... from table)

1
2
3
4
5
-- 查询全部属性（*）
SELECT * FROM news;

-- 查询指定属性
SELECT title, author FROM news;
属性别名（as）
1
2
SELECT title AS head, author AS reporter
FROM news;
  • 返回结果中，两列被命名为head和reporter。

4.1.2 条件查询（where）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查询满足指定条件的指定属性
SELECT title, author 
FROM news
WHERE author='Anonymous';

-- BETWEEN 1 AND 2限制范围为 1 <= id <= 2
SELECT *
FROM news
WHERE id BETWEEN 1 AND 2;

-- 既有BETWEEN AND条件，又有title条件
SELECT *
FROM news
WHERE ID BETWEEN 0 AND 1 AND title='news1';
1）比较
操作符 说明
= 等于
< 小于
> 大于
<>或 != 不等于
<=或 !> 小于等于（即不大于）
>=或 !< 大于等于（即不小于）
BETWEEN 在两个值之间
IS NULL 为 NULL 值
2）字符串的大小写敏感

默认的字符串比较是不区分大小写的，在类型为字符串的属性或字符串参数前使用BINARY操作符修饰，可以将字符串逐字节（byte by byte）比较。

3）逻辑

逻辑与、或、非（AND / OR / NOT）

1
2
3
SELECT *
FROM news
WHERE id=1 OR (title='news2' AND time IS NOT NULL);
  • 可以用小括号来决定优先级
4）通配符

通过LIKE即可使用通配符：

  • %：通配>=0个任意字符；
  • _：通配1个任意字符；
  • \：escape符，取消通配符的转义，使用其字面值。
1
2
3
SELECT *
FROM news
WHERE title LIKE "news%";

备注：网上有笔记说LIKE子句可以用形如[a-z]的通配符，经实际测试不行，查MySQL文档中也没说LIKE有这个通配符。应该需要正则表达式REGEXP子句才对。

5）正则表达式

通过REGEXP即可使用正则表达式：

1
2
3
SELECT *
FROM news
WHERE title REGEXP "^[a-z]{4}[0-9]$";
  • 以4个英文字母开头，1个数字结尾的正则。

4.1.3 去重（distinct）

1
2
3
4
5
6
7
-- 用DISTINCT去重
SELECT DISTINCT author
FROM news;

-- 如果有多列，需要多列内容都相同，才认定为需要去重
SELECT DISTINCT title, author
FROM news;

4.1.4 限制数量（limit）

1
2
3
4
5
6
7
8
9
-- 用LIMIT限制返回结果数量
SELECT * 
FROM news
LIMIT 1, 1;

-- 或写明OFFSET
SELECT * 
FROM news
LIMIT 1 OFFSET 1;
  • LIMIT index, offset，从结果的第index项开始，偏移offset行作为返回范围。
  • index从0开始，因此本例会返回结果的第二项。

4.1.5 排序（order by）

升序（asc, ascending）：

1
2
3
4
SELECT *
FROM news
ORDER BY id ASC
LIMIT 1, 1;
  • 返回升序排序的第二名，从小到大的第二名，即第二小的id。

降序（desc, descending）：

1
2
3
4
SELECT *
FROM news
ORDER BY id DESC
LIMIT 1, 1;
  • 返回降序排序的第二名，从大到小的第二名，即第二大的id。

4.1.6 分组（group by）

使用group by进行分组，此时select查询的属性必须是统计型的，不能每组内不同的属性。

1
2
3
SELECT author, COUNT(*)
FROM news
GROUP BY author;

较复杂的，使用where子句设置每个行的条件，使用having子句设置每个分组的条件：

1
2
3
4
5
SELECT author, COUNT(*) as count
FROM news
WHERE id>1
GROUP BY author
HAVING count>1;

4.2 函数查询

以MySQL为例，有各类函数：

4.2.1 统计

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

复杂一些的，结合distinct去重和count计数，如：

1
2
SELECT COUNT(DISTINCT(author))
FROM news;
  • 统计有多少个作者姓名

4.2.2 文本处理

函数 说明 备注
LEFT(str, len) 左边的字符
RIGHT(str, len) 右边的字符
LOWER(str) 转换为小写字符
UPPER(str) 转换为大写字符
LTRIM(str) 去除左边的空格
RTRIM(str) 去除右边的空格
LENGTH(str) 长度
SOUNDEX(str) 转换为语音值 Sound Index：Knuth和Kant都对应K530

4.2.3 日期与时间

函 数 说 明 备注
ADDDATE(date, days) 增加一个日期（天、周等）
ADDTIME(time, time) 增加一个时间（时、分等）
CURDATE() 返回当前日期 CURRENT_DATE()
CURTIME() 返回当前时间 CURRENT_TIME()
NOW() 返回当前日期和时间
DATE(datetime) 返回日期时间的日期部分
TIME(datetime) 返回日期时间的时间部分
YEAR(date) 返回一个日期的年份部分
MONTH(date) 返回一个日期的月份部分
DAY(date) 返回一个日期的天数部分
DAYOFWEEK(date) 对于一个日期，返回对应的星期几
HOUR(time) 返回一个时间的小时部分
MINUTE(time) 返回一个时间的分钟部分
SECOND(time) 返回一个时间的秒部分
DATEDIFF(date, date) 计算两个日期之差
DATE_ADD(date, days) 高度灵活的日期运算函数
DATE_FORMAT(date, format) 返回一个格式化的日期或时间串
  • 日期格式：YYYY-MM-DD
  • 时间格式：HH:MM:SS

获取当前日期+时间、当前日期、当前时间、当前时间戳：

1
2
SELECT NOW(), CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP();
-- 2020-07-03 16:45:39	2020-07-03	16:45:39	2020-07-03 16:45:39

从当前日期中取出年份：

1
SELECT YEAR(CURDATE());

4.2.4 数学

函数 说明 备注
SIN(x) 正弦 三角函数输入弧度（radians），不是角度
COS(x) 余弦
TAN(x) 正切
ABS(x) 绝对值
SQRT(x) 平方根
MOD(m, n) 余数 m % n
EXP(x) 指数 e^x
PI() 圆周率
RAND([n]) 随机数 浮点数，0<=rand<1.0；参数n可指定随机数种子，可选项。
FLOOR(x) 下取整
CEILING(x) 上取整 别名CEIL(x)

组合rand和floor可以获取任意[i, j)区间的随机整数：

1
SELECT FLOOR(7 + (RAND() * 5));
  • [7, 12)

4.2.5 其他

函数 说明 备注
IFNULL(expr1, expr2) 如果式1不为NULL，则返回式1，若式1为NULL，则返回式2 如果式1查询结果为空，也会返回式2

4.3 嵌套查询

父查询依赖于子查询，子查询只能返回一个字段的数据，可用于where子句的条件。

1
2
3
4
5
6
7
-- 使用IN子句，col1必须在查询出的col2结果中
SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);
               
-- 另外，如果子查询结果是一个基本变量，也可以用等于（=）之类的关系符比较

从Customer和Orders两个表中，检索每个客户的订单数量：

1
2
3
4
5
6
SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

对每个Customer行，将其cust_id作为子查询的where条件。

4.4 连接查询（join）

连接多个表，语法为：join ... on ...。

效率一般比子查询更快。

可以用AS给表、计算字段、列取别名。

Employee表：

id name deptId
1 Tom 1
2 Mike 1
3 Sandy 2
4 Wendy 3
5 Lily 0
  • Employee有一个对不上Dept的Lily员工

Dept表：

deptId deptName
1 Dev
2 Deploy
3 Finance
4 HR
  • Dept有一个对不上Employee的HR部

4.4.1 内连接（inner join）

1）等值连接

比较运算符为=时，是等值连接。

只有等值的、对的上的会起来，对不上的就丢弃（一个表有，而另一个表没有的）。内连接可以自主决定判定的属性和条件。

以deptId为连接条件进行内连接：

1
2
3
4
5
6
7
8
9
-- 以ON子句设定连接条件
SELECT * 
FROM Employee INNER JOIN Dept
ON Employee.deptId = Dept.deptId;	-- 等值连接

-- 相当于是等值条件下的多表查询
SELECT Employee.*, Dept.*
FROM Employee, Dept
WHERE Employee.deptId = Dept.deptId;

结果为：

id name deptId deptId deptName
1 Tom 1 1 Dev
2 Mike 1 1 Dev
3 Sandy 2 2 Deploy
4 Wendy 3 3 Finance
  • 没有部门可连接的Lily员工在连接中被丢弃了。
  • 没有员工可供连接的HR部门在连接中被丢弃了。

也可以为被连接的表起别名，并限制查询的列：

1
2
3
SELECT E.id, E.name, E.deptId, D.deptName
FROM Employee as E INNER JOIN Dept as D
ON E.deptId = D.deptId;

结果为：

id name deptId deptName
1 Tom 1 Dev
2 Mike 1 Dev
3 Sandy 2 Deploy
4 Wendy 3 Finance
2）非等值连接

比较运算符为!=, <>, <, >, <=, >=这些非等值比较时，是非等值连接。

3）自然连接（natural join）

不难发现，自然连接是等值连接的一种。

自然连接是写起来最“自然”的，自然连接自动对比属性名相同的列，把相同的值进行连接，而且自然连接会去重用于连接的相同的列：

1
2
SELECT * 
FROM Employee NATURAL JOIN Dept;

连接结果丢弃了连不上的部分（左表有但右表对不上，或右表有但左表对不上）：

deptId id name deptName
1 1 Tom Dev
1 2 Mike Dev
2 3 Sandy Deploy
3 4 Wendy Finance
  • 没有部门可连接的Lily员工在连接中被丢弃了。
  • 没有员工可供连接的HR部门在连接中被丢弃了。
4）自连接

一个表自己和自己连接。

例如，只需要根据Edployee表就可以查询同部门的同事关系：

1
2
3
4
5
6
7
8
SELECT *
FROM Employee as E1 inner join Employee as E2
ON E1.deptId = E2.deptId AND E1.id != E2.id;

-- 相当于多表查询
SELECT *
FROM Employee as E1, Employee as E2
WHERE E1.deptId = E2.deptId AND E1.id != E2.id;

结果为：

id name deptId id name deptId
2 Mike 1 1 Tom 1
1 Tom 1 2 Mike 1
  • Mike和Tom是双向的同事关系

4.4.2 外连接（outer join）

左外连接（left outer join）

保留左表中对不上的多余行，空白部分用NULL填充（右表多余的行则丢弃）。

1
2
3
SELECT *
FROM Employee as E LEFT OUTER JOIN Dept as D
ON E.deptId = D.deptId;

结果为：

id name deptId deptId deptName
1 Tom 1 1 Dev
2 Mike 1 1 Dev
3 Sandy 2 2 Deploy
4 Wendy 3 3 Finance
5 Lily 0 NULL NULL
  • 右表的HR部因为在左表中没有可连接行而被丢弃。
  • 右表的deptId尽管在右表中是主键，不可为NULL，但是在查询结果中是可以用NULL填充的。

另外，如果增加WHERE条件，还可以选出只有左表才有的行（即对应的右表IS NULL）：

1
2
3
4
SELECT *
FROM Employee as E LEFT OUTER JOIN Dept as D
ON E.deptId = D.deptId
WHERE D.deptID IS NULL;
右外连接（right outer join）

保留右表中对不上的多余行，空白部分用NULL填充（左表多余的行则丢弃）。

1
2
3
SELECT *
FROM Employee as E RIGHT OUTER JOIN Dept as D
ON E.deptId = D.deptId;

结果为：

id name deptId deptId deptName
1 Tom 1 1 Dev
2 Mike 1 1 Dev
3 Sandy 2 2 Deploy
4 Wendy 3 3 Finance
NULL NULL NULL 4 HR
  • 左表的Lily因为在右表中没有可连接行而被丢弃。
  • 左表的id尽管在右表中是主键，不可为NULL，但是在查询结果中是可以用NULL填充的。

4.4.3 交叉连接（cross join）

交叉连接即笛卡尔积，不考虑连接匹配关系，只管将所有排列组合都列出来。

1
2
3
4
5
6
SELECT *
FROM Employee cross join Dept;

-- 相当于无条件的多表查询
SELECT *
FROM Employee, Dept;

结果为：

id name deptId deptId deptName
1 Tom 1 1 Dev
1 Tom 1 2 Deploy
1 Tom 1 3 Finance
1 Tom 1 4 HR
2 Mike 1 1 Dev
2 Mike 1 2 Deploy
2 Mike 1 3 Finance
2 Mike 1 4 HR
3 Sandy 2 1 Dev
3 Sandy 2 2 Deploy
3 Sandy 2 3 Finance
3 Sandy 2 4 HR
4 Wendy 3 1 Dev
4 Wendy 3 2 Deploy
4 Wendy 3 3 Finance
4 Wendy 3 4 HR
5 Lily 0 1 Dev
5 Lily 0 2 Deploy
5 Lily 0 3 Finance
5 Lily 0 4 HR
  • 全排列组合，共4×5=20行。

4.5 组合查询（union）

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

1
2
3
4
5
6
7
SELECT * 
FROM Employee
WHERE deptId = 1
UNION
SELECT *
FROM Employee
WHERE deptId = 2;
  • 查询1返回2行，查询2返回1行，组合结果总共3行。
  • 被组合的查询必须有相通性，要有相同的列、表达式或聚集函数才行。（不然没法组合到一起）。
  • 默认去重，去除相同的行，可以通过UNION ALL来保留相同行。
  • 被组合的查询不可以分组（order by），只能对组合查询的结果进行分组。

5 其他功能

5.1 视图（view）

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

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

视图具有如下好处：

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

5.1.1 新建视图（create view）

1
2
3
4
5
6
7
8
CREATE VIEW Developers AS 
SELECT * 
FROM Employee
WHERE deptId in (
	SELECT deptId
	FROM Dept
	WHERE deptName = 'Dev'
	);
  • 将查询Dev部门的开发者名单的嵌套查询作为视图

5.1.2 查询视图（select）

查询的时候就像查询表一样容易：

1
2
SELECT *
FROM Developers;

5.1.3 删除视图（drop view）

1
DROP VIEW Developers;

5.2 存储过程（procedure）

相当于SQL的批处理程序，将一批SQL封装成一个存储过程。

使用存储过程的好处：

  • 代码封装，保证了一定的安全性；
  • 代码复用；
  • 由于是预先编译，因此具有很高的性能。

5.2.1 新建存储过程（create procedure）

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 声明语句结束符
delimiter //
-- 新建存储过程，定义INT类型的OUT输出参数ret
create procedure myprocedure( out ret int )
    begin	-- 过程开始
        declare y int;	-- declare变量定义
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;	-- 结果select into输出参数
    end //	-- 过程结束

delimiter ;	-- 存储过程定义完毕，将结束符恢复为封号;

存储过程的参数可以从三种中选：

  • IN：输入参数；
  • OUT：输出参数；
  • INOUT：即输入，也输出的参数。

5.2.2 调用存储过程（call）

1
2
3
4
5
-- 如果是传入参数，可以通过SET定义
-- 如：SET @name='Tom'

call myprocedure(@ret);	-- 调用
select @ret;	-- 使用返回结果变量

5.2.3 修改存储过程（alter procedure）

1
2
-- 修改存储过程名
ALTER PROCEDURE proc1 RENAME TO proc2;

5.2.4 删除存储过程（drop procedure）

1
DROP PROCEDURE proc2;

5.3 游标（cursor）

游标可以一行一行处理，前进、后退一行。

游标的性能不会很好，逐行处理不如并发，且占用带宽，锁定资源，代码量也比批量select更多。

Mysql游标入门

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create procedure myyoubiao ()
BEGIN
    declare done boolean default 0; --循环标记
    declare tmp int; -- 临时存储变量
    declare t DECIMAL(8,2); -- 同上
    
    declare myCursor CURSOR for select id from user;
    declare continue handler for sqlstate '02000' set done = 1;

    create table if not exists mytable -- 表不存在是创建， 存在时跳过
    (uId int, total decimal(8,2));

    open myCursor;
    
    REPEAT
        fetch myCursor into tmp;
            call getTotalByUser2(tmp, 1, t); -- 根据用户id获取该用户总订单金额， 含税
            insert into mytable(uId,total) values(tmp,t); --插入新表
    UNTIL done end REPEAT;
    CLOSE myCursor;
END

5.4 触发器（trigger）

触发器会在条件触发时自动执行，条件包括：对表的delete、insert和update。

可以在触发条件执行前或执行后执行触发器操作。之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

1
2
3
4
5
-- 触发器在对表进行插入操作后执行
CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 获取结果

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

5.5 事务管理（transaction）

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

保存点（savepoint）：事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

回退（rollback）：撤销指定 SQL 语句的过程。

隐式自动提交（autocommit）：

  1. MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。
  2. 当出现 START TRANSACTION 语句时，会自动关闭隐式提交；
  3. 当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。

保存点（savepoint）与回滚（rollback）：

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

5.6 字符集与字符序（charset & collation）

再见乱码：5分钟读懂MySQL字符集设置

MySQL支持多种字符集 与 字符序。

  1. 一个字符集对应至少一种字符序（一般是1对多）。
  2. 两个不同的字符集不能有相同的字符序。
  3. 每个字符集都有默认的字符序。

5.6.1 查询支持的字符集：

1
SHOW CHARACTER SET;

结果：

Charset Description Default collation Maxlen
big5 Big5 Traditional Chinese big5_chinese_ci 2
dec8 DEC West European dec8_swedish_ci 1
cp850 DOS West European cp850_general_ci 1
略…… …… …… ……

5.6.2 查询支持的字符序：

1
SHOW COLLATION;

结果：

Collation Charset Id Default Compiled Sortlen
big5_chinese_ci big5 1 Yes Yes 1
big5_bin big5 84 Yes 1
dec8_swedish_ci dec8 3 Yes Yes 1

5.6.3 设置字符集和字符序

创建表时设置：

1
2
3
4
CREATE TABLE mytable(
    col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci 
)
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

排序、分组时设置：

1
2
3
SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;

6 权限与安全

6.1 用户管理（user）

6.1.1 查询用户

查询系统表获取用户清单：

1
2
USE mysql;
SELECT user FROM user;

查询当前用户：

1
SELECT current_user();
  • 返回heary@localhost。

6.1.2 新建用户（create user）

1
CREATE USER myuser IDENTIFIED BY 'mypassword';

6.1.3 修改用户

1
RENAME USER myuser TO newuser;

6.1.4 删除用户（drop user）

1
DROP USER myuser;

6.1.5 设置密码（set password for）

1
SET PASSWROD FOR myuser = Password('new_password');
  • 必须使用Password函数对密码进行加密。

6.2 权限管理（grant）

6.2.1 查询权限（show grants for）

1
2
3
4
5
SHOW GRANTS FOR myuser;

-- 例如查询当前用户权限
SHOW GRANTS FOR current_user();
-- GRANT ALL PRIVILEGES ON *.* TO 'heary'@'localhost' WITH GRANT OPTION

6.2.2 授予权限（grant）

1
2
3
GRANT SELECT, INSERT ON mydatabase.* TO myuser;

-- GRANT ALL PRIVILEGES ON *.* TO 'heary'@'localhost';

6.2.3 撤销权限（revoke）

1
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

GRANT 和 REVOKE 可在几个层次上控制访问权限：

  • 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
  • 整个数据库，使用 ON database.*；
  • 特定的表，使用 ON database.table；
  • 特定的列；
  • 特定的存储过程。