一、通配符
通配符也是用在过滤语句中,但它只能用于文本字段。
-
% 匹配 >=0 个任意字符;
-
_ 匹配 ==1 个任意字符;
-
[ ] 可以匹配集合内的字符,例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定,也就是不匹配集合内的字符。
使用 Like 来进行通配符匹配。
SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本
不要滥用通配符,通配符位于开头处匹配会非常慢。
二、计算字段/删除操作
计算字段通常需要使用 AS 来取别名,否则输出的时候字段名为计算表达式。
SELECT col1 * col2 AS alias
FROM mytable;
CONCAT() 用于连接两个字段。
delete
删除整张表的数据或者部分数据:每次删除一行,都在事务日志中为所删除的每行记录一项。事务提交之后才生效,可以回滚;删除表中数据而不删除表的结构(定义)。
truncate
只能操作表,一次性地从表中删除所有的数据:通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放;操作立即生效,自动提交,不能回滚,操作不触发trigger;删除内容、释放空间但不删除表的结构(定义)。
drop
1、drop语句将删除表的结构,触发器(trigger):drop也属于DDL语言,立即执行,执行速度最快;删除内容和定义,释放空间。
区别
1、表和索引所占空间:
TRUNCATE操作,将表和索引所占用的空间会恢复到初始大小;
DELETE操作不会减少表或索引所占用的空间;
DROP语句将表所占用的空间全释放掉。
2、应用范围:
TRUNCATE 只能对table;
DELETE可以是table和view。
3、执行速度: drop > truncate > delete
4、delete from删空表后,会保留一个空的页,truncate在表中不会留有任何页。
6、当使用行锁执行 DELETE 语句时,将锁定表中各行以便删除。truncate始终锁定表和页,而不是锁定各行。
7、如果有identity产生的自增id列,delete from后仍然从上次的数开始增加,即种子不变;
使用truncate删除之后,种子会恢复到初始值。
三、函数
各个 DBMS 的函数都是不相同的,因此不可移植,以下主要是 MySQL 的函数。直接当作已经实现的方法使用就行。
汇总
函 数 | 说 明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
AVG() 会忽略 NULL 行。
使用 DISTINCT 可以汇总不同的值。
SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;
四、分组
把具有相同的数据值的行放在同一组中,使用汇总函数进行处理,例如求分组数据的平均值等。
指定的分组字段除了能按该字段进行分组,也会自动按该字段进行排序。
GROUP BY 自动按分组字段进行排序,ORDER BY 也可以按汇总字段来进行排序。
SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;
WHERE 过滤行,HAVING 过滤分组,行过滤应当先于分组过滤,也就是说顺序上面where优先于having
SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;
分组规定:
- GROUP BY 子句出现在 WHERE 子句之后,ORDER BY 子句之前;
五、连接
连接用于连接多个表,使用 JOIN 关键字,并且条件语句使用 ON 而不是 WHERE。
连接可以替换子查询,并且比子查询的效率一般会更快。
可以用 AS 给列名、计算字段和表名取别名,给表名取别名是为了简化 SQL 语句以及连接相同表。
内连接
内连接:又称等值连接,使用 INNER JOIN 关键字;也可以直接使用where。 结果表中删除与其他被连接表中没有匹配行的所有行,所以内连接可能会丢失信息。
SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;
外连接
左外连接:如果左表的某行在右表中没有匹配行,则将为右表返回空值。右外连接相反。
全外链接:在结果中除了显示满足连接的条件的行外,还显示了join两侧表中所有满足检索条件的行。
六、组合查询
使用 UNION 来组合两个查询,如果第一个查询返回 M 行,第二个查询返回 N 行,那么组合查询的结果一般为 M+N 行。
每个查询必须包含相同的列、表达式和聚集函数。
默认会去除相同行,如果需要保留相同行,使用 UNION ALL。
只能包含一个 ORDER BY 子句,并且必须位于语句的最后。
SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;
七、存储过程
存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单,就是数据库SQL语言层面的代码封装与重用。类比面向对象编程的类,可以看成是对一系列 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;
end //
delimiter ;
call myprocedure(@ret);
select @ret;
八、触发器
触发器会在某个表执行以下语句时而自动执行: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 是只读的。
九、事务
事物特性
原子性(A):事务是最小单位,不可再分
一致性(C):事务要求所有的DML语句操作的时候,必须保证同时成功或者同时失败
隔离性(I):事务A和事务B之间具有隔离性
持久性(D):是事务的保证,事务终结的标志(内存的数据持久到硬盘文件中)
基本术语
- 开启事务(Start Transaction):任何一条DML语句(insert、update、delete)执行,标志事务开启。
- 回退(rollback)指撤销指定 SQL 语句的过程;
- 提交(commit)指将未存储的 SQL 语句结果写入数据库表,可改变底层数据库数据。
- 保留点(savepoint)指事务处理中设置的临时占位符(placeholder),你可以对它发布回退(与回退整个事务处理不同)。
MySQL 的事务提交默认是隐式提交,每执行一条语句就把这条语句当成一个事务然后进行提交。在事物进行过程中,未结束之前,DML语句是不会更改底层数据,只是将历史操作记录一下,在内存中完成记录。只有在事物结束的时候,而且是成功的结束的时候,才会修改底层硬盘文件中的数据。
如果没有设置保留点,ROLLBACK 会回退到 START TRANSACTION 语句处;如果设置了保留点,并且在 ROLLBACK 中指定该保留点,则会回退到该保留点。
START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT
事务四大特性之一隔离性(isolation)
1 事物A和事物B之间具有一定的隔离性
2 隔离性有隔离级别(4个)
读未提交:read uncommitted
读已提交:read committed
可重复读:repeatable read
串行化:serializable
3 事务隔离级别的作用范围分为两种:
全局级:对所有的会话有效
会话级:只对当前的会话有效
十、权限管理
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');
权限部分以后会进行修改