目录
1 基础
模式定义了数据如何存储、存储什么样的数据以及数据如何分解等信息,数据库和表都有模式。
主键的值 不允许修改,也不允许复用(不能将已经删除的主键值赋给新数据行的主键)。
SQL(Structured Query Language),标准 SQL 由 ANSI 标准委员会管理,从而称为 ANSI SQL。各个 DBMS 都有自己的实现,如 PL/SQL、Transact-SQL 等。
SQL 语句不区分大小写,但是数据库表名、列名和值 是否区分 依赖于 具体的 DBMS 以及配置。
SQL 支持以下三种注释:
# 注释1
SELECT * FROM mytable; -- 注释2
/* 注释3
注释3 */
数据库创建与使用:
CREATE DATABASE 库名;
USE 库名;
注意:下面的示例中, mytable代表表名。col代表列名,val代表值
2 创建表
CREATE TABLE mytable (
# int 类型,不为空,自增
id INT NOT NULL AUTO_INCREMENT,
# int 类型,不可为空,默认值为 1,不为空
col1 INT NOT NULL DEFAULT 1,
# 变长字符串类型,最长为 45 个字符,可以为空
col2 VARCHAR(45) NULL,
# 日期类型,可为空
col3 DATE NULL,
# 设置主键为 id
PRIMARY KEY (`id`));
3 修改表
添加列
ALTER TABLE 表名
ADD 列名 CHAR(20);
删除列
ALTER TABLE 表名
DROP COLUMN 列名;
删除表
DROP TABLE 表名;
4 插入
普通插入
INSERT INTO mytable(col1, col2)
VALUES(val1, val2);
插入检索出来的数据
INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;
将一个表的所有内容,插入到一个新表
CREATE TABLE newtable AS
SELECT * FROM mytable;
5 更新
UPDATE mytable
SET col = val
WHERE id = 1;
6 删除
删除表中的特定行
DELETE FROM mytable
WHERE id = 1;
truncate [trʌŋˈkeɪt] v.截短,缩短,删节(尤指掐头或去尾)
TRUNCATE TABLE 可以清空表,也就是删除所有行。
TRUNCATE TABLE mytable;
使用更新和删除操作时一定要用 WHERE 子句,不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试,防止错误删除。
7 查询
DISTINCT (去重)
相同值 只会出现一次。它作用于所有列,也就是说所有列的值都相同 才算相同。
SELECT DISTINCT col1, col2
FROM mytable;
LIMIT
限制返回的行数。可以有两个参数,第一个参数为起始行,从 0 开始;第二个参数为返回的总行数。
返回前 5 行:
SELECT *
FROM mytable
LIMIT 5;
SELECT *
FROM mytable
LIMIT 5;
返回第 3 ~ 5 行:(注意:第一行的行号为0)
SELECT *
FROM mytable
LIMIT 2, 3;
8 排序
- ASC :升序(默认)
- DESC :降序
可以按多个列 进行排序,并且可以为每个列 指定不同的排序方式:
SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;
9 过滤
不进行过滤的数据非常大,导致通过网络传输了多余的数据,从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据,而不是传输所有的数据到客户端中 然后由客户端进行过滤。
SELECT *
FROM mytable
WHERE 列名 IS NULL; --此条语句返回 所有指定列值为空的行
下表显示了 WHERE 子句可用的操作符
操作符 | 说明 |
---|---|
= | 等于 |
< | 小于 |
> | 大于 |
<> != | 不等于 |
<= !> | 小于等于 |
>= !< | 大于等于 |
BETWEEN | 在两个值之间 |
IS NULL | 为 NULL 值 |
应该注意到,NULL 与 0、空字符串都不同。
AND 和 OR 用于连接多个过滤条件。优先处理 AND,当一个过滤表达式涉及到多个 AND 和 OR 时,可以使用 () 来决定优先级,使得优先级关系更清晰。
IN 操作符用于匹配一组值,其后也可以接一个 SELECT 子句,从而匹配子查询得到的一组值。
NOT 操作符用于否定一个条件。
10 通配符
通配符也是用在过滤语句中,但它只能用于文本字段。
-
% 匹配 >=0 个任意字符;
-
_ 匹配 ==1 个任意字符;
-
[ ] 可以匹配 集合内的字符,例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定,也就是不匹配集合内的字符。
使用 Like 来进行通配符匹配。
SELECT *
FROM mytable
WHERE 列名 LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本
不要滥用通配符,通配符 位于开头处 匹配会非常慢。
11 计算字段
在数据库服务器上完成数据的转换和格式化的工作 往往比客户端上快得多,并且转换和格式化后的数据量更少的话可以减少网络通信量。
计算字段通常需要使用 AS 来取别名,否则输出的时候字段名 为 计算表达式。
SELECT col1 * col2 AS 别名
FROM mytable;
CONCAT() 用于连接两个字段。许多数据库会使用空格 把一个值填充为列宽,因此连接的结果会出现一些不必要的空格,使用 TRIM() 可以去除首尾空格。
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;
12 函数
各个 DBMS 的函数都是不相同的,因此不可移植,以下主要是 MySQL 的函数。
汇总
函 数 | 说 明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
AVG() 会忽略 NULL 行。
在聚集函数里面,使用 DISTINCT 可以去重,从而汇总不同的值。
SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;
文本处理
函数 | 说明 |
---|---|
LEFT() | 左边的字符 |
RIGHT() | 右边的字符 |
LOWER() | 转换为小写字符 |
UPPER() | 转换为大写字符 |
LTRIM() | 去除左边的空格 |
RTRIM() | 去除右边的空格 |
LENGTH() | 长度 |
SOUNDEX() | 转换为语音值 |
其中, SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。
SELECT *
FROM mytable
WHERE SOUNDEX(col1) = 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();
2018-4-14 20:25:11
数值处理
函数 | 说明 |
---|---|
SIN() | 正弦 |
COS() | 余弦 |
TAN() | 正切 |
ABS() | 绝对值 |
SQRT() | 平方根 |
MOD() | 余数 |
EXP() | 指数 |
PI() | 圆周率 |
RAND() | 随机数 |
13 分组
把具有相同的数据值 的行 放在同一组中。
GROUP BY 列名 //把指定列的值相同的数据行,分到同一组。
例如 GROUP BY 分数。会把几个考100分的分一组,几个考99分的分一组。几个考98分的分一组
可以对同一分组数据 使用汇总函数 进行处理,例如求分组数据的平均值等。
指定的分组字段除了能按 该字段 进行分组,也会 自动按该字段 进行排序。
SELECT 列名, COUNT(*) AS 别名
FROM mytable
GROUP BY 列名;
GROUP BY 自动按分组字段进行排序,ORDER BY 也可以按汇总字段来进行排序。
SELECT 列名, COUNT(*) AS num
FROM mytable
GROUP BY 列名
ORDER BY num;
WHERE 过滤行。
HAVING 过滤分组。
行过滤 应当先于 分组过滤。(WHERE过滤 先于 HAVING过滤)
SELECT 列名, COUNT(*) AS 汇总字段别名
FROM 表名
WHERE 列名 > 2
GROUP BY 列名
HAVING 汇总字段别名 >= 2;
分组规定:
- GROUP BY 子句 出现在 WHERE 子句之后,ORDER BY 子句之前;
- 除了汇总字段外,SELECT 语句中的每一字段(即要查询的字段) 都必须在 GROUP BY 子句中给出;
- NULL 的行 会单独分为一组;
- 大多数 SQL 实现不支持 GROUP BY 列 具有可变长度的数据类型。
14 子查询
子查询中 只能返回 一个字段的数据。
可以将子查询的结果 作为 WHRER 语句的过滤条件:
SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
FROM mytable2); /* 子查询中 只能查一个字段 */
下面的语句可以检索出 每个客户的订单数量,子查询语句 会对 外层查询 检索出的每个客户(Customers.cust_id) 执行一次:
SELECT cust_name, (SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id)
AS orders_num
FROM Customers
ORDER BY cust_name;
15 连接
连接用于连接多个表,使用 JOIN 关键字,并且条件语句 使用 ON 而不是 WHERE。
连接可以替换子查询,并且比子查询的效率 一般会更快。
可以用 AS 给列名、计算字段和表名取别名,给表名取别名是为了 简化 SQL 语句以及连接相同表。
不要连接不必要的表。连接的表越多,性能下降越快
连接 比 子查询 要快
内连接
内连接又称等值连接,使用 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; /* 直接用两个表的字段 进行相等测试,就可以达到等值连接的效果,不用显示使用INNER JOIN */
自连接
自连接可以看成内连接的一种,只是连接的表是 自身而已。
一张员工表,包含员工姓名和员工所属部门,要找出与 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";
自然连接
自然连接是 把同名列通过等值测试连接起来的,同名列可以有多个。
内连接 和 自然连接的区别:内连接中,相同的列可能多次出现;而自然连接排除多次出现,使每个列只返回一次。(要达到自然连接的效果,需要我们手动指定唯一的列,这样就能达到每个查询出来的列不重复的效果)
SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;
外连接
外连接 保留了 没有关联的那些行。分为左外连接,右外连接以及全外连接。
左外连接 就是 保留左表没有关联的行。
检索 所有顾客的订单信息,包括 还没有订单信息的顾客。
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id; /* 保留了左表Customers中 没有关联的行 */
customers 表:
cust_id | cust_name |
---|---|
1 | a |
2 | b |
3 | c |
orders 表:
order_id | cust_id |
---|---|
1 | 1 |
2 | 1 |
3 | 3 |
4 | 3 |
左连接结果:
cust_id | cust_name | order_id |
---|---|---|
1 | a | 1 |
1 | a | 2 |
3 | c | 3 |
3 | c | 4 |
2 | b | NULL |
分析:通过左连接,将左表(Customers ,顾客表)中的所有顾客都查出来。即使有的顾客没有订单,也会被查出来。例如上表中的 cust_id=2 的顾客,虽然他的order_id=NULL ,但这个顾客还是被查询出来了。
16 组合查询
使用 UNION 来 组合 两个查询,如果第一个查询返回 M 行,第二个查询返回 N 行,那么组合查询的结果一般为 M+N 行。
每个查询 必须包含 相同的列、表达式和聚集函数。(SELECT关键字 后面跟的东西,要一模一样)
默认会 去除 相同行。如果需要 保留 相同行,使用 UNION ALL。
只能包含一个 ORDER BY 子句,并且必须位于语句的最后。
SELECT 列名
FROM mytable
WHERE 列名= 1
UNION
SELECT 列名
FROM mytable
WHERE 列名=2; /* 两条语句查的字段,查的表,都一样。但查询条件不一样 */
17 视图
视图是虚拟的表,本身不包含数据,也就不能对其进行 索引操作。
对视图的操作 和 对普通表的操作 一样。
视图具有如下好处:
- 简化复杂的 SQL 操作,比如复杂的连接;(用视图来代替一条复杂的SQL查询)
- 只使用实际表的一部分数据;
- 通过只给用户访问视图的权限,保证数据的安全性;
- 更改数据格式和表示。
CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;
18 存储过程
存储过程可以看成是对一系列 SQL 操作的批处理。
使用存储过程的好处:
- 代码封装,保证了一定的安全性;
- 代码复用;
- 由于是预先编译,因此具有很高的性能。
命令行中创建存储过程 需要自定义分隔符,因为命令行是以 ; 为结束符,而存储过程中也包含了分号,因此会错误把这部分分号当成是结束符,造成语法错误。
包含 in、out 和 inout 三种参数。
给变量赋值 都需要用 select into 语句。
每次只能给一个变量赋值,不支持集合的操作。
delimiter // /* 更改结束符为// */
create procedure myprocedure( out ret int )
begin
declare y int;
select sum(col1)
from mytable
into y;
select y*y into ret; /* 将y*y 赋值给 ret */
end //
delimiter ; /* 恢复结束符为 ; */
call myprocedure(@ret); /* 调用存储过程 */
select @ret;
数据库中的 “@” 是局部变量声明。如果没有"@"的字段代表是列名;
eg:
声明变量: declare @name varchar(8)
赋值: set @name= '张三'
查询: select * from stuInfo where stuName = @name
由set 和 select 进行赋值;
select一般用于查询数据,然后再赋值变量。
还有@@error 等是全局变量,系统自定义的,我们只读,不能改!!
19 游标
在存储过程中使用游标,可以对一个结果集 进行移动遍历。
游标主要用于交互式应用,其中用户需要对数据集中的任意行进行浏览和修改。
使用游标的四个步骤:
- 声明游标,这个过程没有实际检索出数据;
- 打开游标;
- 取出数据;
- 关闭游标;
delimiter //
create procedure myprocedure(out ret int)
begin
declare done boolean default 0;
/* 声明游标 */
declare mycursor cursor for
select col1 from mytable;
# 定义了一个 continue handler,当 sqlstate '02000' 这个条件出现时,会执行 set done = 1
declare continue handler for sqlstate '02000' set done = 1;
open mycursor; /* 打开游标 */
/* 遍历结果集 */
repeat
fetch mycursor into ret;
select ret;
until done end repeat;
close mycursor; /* 关闭游标 */
end //
delimiter ;
20 触发器
触发器会在某个表执行以下语句时 而自动执行:DELETE、INSERT、UPDATE。
触发器必须指定在语句执行之前还是之后自动执行,之前执行使用 BEFORE 关键字,之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化,AFTER 用于审计跟踪,将 修改 记录到另外一张表中。
INSERT 触发器 包含一个名为 NEW 的虚拟表。
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 语句,也就是不能调用存储过程。
21 事务管理
基本术语:
- 事务(transaction)指一组 SQL 语句;
- 回退(rollback)指撤销指定 SQL 语句的过程;
- 提交(commit)指将未存储的 SQL 语句结果写入数据库表;
- 保留点(savepoint)指 事务处理中设置的临时占位符(placeholder),你可以对它发布回退(与回退整个事务处理不同)。
不能回退 SELECT 语句,回退 SELECT 语句也没意义;也不能回退 CREATE 和 DROP 语句。
MySQL 的事务提交默认是隐式提交,每执行一条语句 就把这条语句 当成一个事务然后进行提交。
当出现 START TRANSACTION 语句时,会关闭隐式提交;当 COMMIT 或 ROLLBACK 语句执行后,事务会自动关闭,重新恢复隐式提交。
设置 autocommit 为 0 可以取消 自动提交;autocommit 标记是针对每个连接,而不是针对服务器的。
如果没有设置保留点,ROLLBACK 会回退到 START TRANSACTION 语句处;
如果设置了保留点,并且在 ROLLBACK 中指定该保留点,则会回退到 该保留点。
START TRANSACTION
// ...
SAVEPOINT delete1 /* 指定安全点 */
// ...
ROLLBACK TO delete1 /* 回退到安全点 */
// ...
COMMIT
22 字符集
基本术语:
- 字符集为字母和符号的集合;
- 编码为某个字符集成员的内部表示;
- 校对字符指定如何比较,主要用于排序和分组。
除了给表指定字符集和校对外,也可以给列指定:
CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
可以在排序、分组时 指定校对:
SELECT *
FROM 表名
ORDER BY 列名 COLLATE latin1_general_ci;
collate [kəˈleɪt] v. 核对,校勘,对照(不同来源的信息);整理(文件或书等)
23 权限管理
MySQL 的账户信息保存在名为 mysql 这个数据库中。
USE mysql;
SELECT user FROM user;
创建账户
新创建的账户没有任何权限。
CREATE USER myuser IDENTIFIED BY 'mypassword';
修改账户名
RENAME USER myuser TO newuser;
删除账户
DROP USER myuser;
查看权限
SHOW GRANTS FOR myuser;
授予权限
账户用 username@host 的形式定义,username@% 使用的是默认主机名。
GRANT SELECT, INSERT ON mydatabase.* TO myuser;
删除权限
GRANT 和 REVOKE 可在几个层次上控制访问权限:
- 整个服务器,使用 GRANT ALL 和 REVOKE ALL;
- 整个数据库,使用 ON database.*;
- 特定的表,使用 ON database.table;
- 特定的列;
- 特定的存储过程。
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;
更改密码
必须使用 Password() 函数进行加密。
SET PASSWROD FOR myuser = Password('new_password');
参考资料
- BenForta. SQL 必知必会 [M]. 人民邮电出版社, 2013.