SQL笔记

以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填充的。

<<<<<<< HEAD
另外,如果增加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;

=======

ae62d74b142ce1c9a976398b7a5f2c7c1cad7d18

右外连接(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;
  • 特定的列;
  • 特定的存储过程。
支持我的写作!(Support my writing!)

欢迎关注我的其它发布渠道