目录
一、基础
模式定义了数据如何存储、存储什么样的数据以及数据如何分解等信息，数据库和表都有模式。
主键的值不允许修改，也不允许复用（不能使用已经删除的主键值赋给新数据行的主键）。
SQL（Structured Query Language)，标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。各个 DBMS 都有自己的实现，如 PL/SQL、Transact-SQL 等。
SQL 语句不区分大小写，但是数据库表名、列名和值是否区分依赖于具体的 DBMS 以及配置。
SQL 支持以下三种注释：
# 注释
SELECT *
FROM mytable; -- 注释
/* 注释1
注释2 */数据库创建与使用：
CREATE DATABASE test;
USE test;select语句
用来从一个或多个表中检索信息，使用时必须至少给出两条信息——想选择什么，以及从什么地方选择。
输入：
SELECT prod_name
FROM Products；
分析：
- 上述语句利用 SELECT 语句从 Products 表中检索一个名为 prod_name 的列。所需的列名在 SELECT 关键字之后给出，FROM关键字指出从其中检索数据的表名。
- 检索多个列时，SELECT 关键字后面给出多个列名，用逗号隔开即可，例： SELECT prod_id，prod_name，prod_price
- 检索所有列时，通过在实际列名的位置用星号（*）实现。
关于分组处理数据的语句顺序：
WHERE —> GROUP BY —> HAVING —>ORDER BY—>LIMIT
WHERE只能对整个表的数据进行筛选，分组后的过滤只能使用 having
其他
外键：是表中的一个列，其值必须在另一个表的主键中列出。定义中使用 reference 关键字。
索引：用来排序数据以加快搜索和排序操作的速度。
- 数据总是按主键排序的。但是搜索其他列中的值通常效率不高，这时就可以提前在一个或多个列上定义索引，使DBMS保存其内容的一个排过序的表。
- 索引改善检索操作的性能，但降低插入、修改与删除的性能，执行这些操作时，DBMS必须动态的更新索引。
二、创建表
CREATE TABLE mytable
(
id INT NOT NULL AUTO_INCREMENT,
col1 INT NOT NULL DEFAULT 1,
col2 VARCHAR(45) NULL,
col3 DATE NULL,
PRIMARY KEY (`id`)
);
create table test
(
name varchar(19),
id number,
value varchar(10),
primary key (name,id) //复合主键 指表中的主键由一个以上的字段组成
)三、修改表
添加列
ALTER TABLE Vendors
ADD vend_phone CHAR(20);删除列
ALTER TABLE Venders
DROP COLUMN vend_phone;删除表
DROP TABLE mytable;四、插入
1.普通插入
INSERT INTO Customers(cust_id, cust_name)
VALUES('100006', 'Tony Land');只给出了插入行的部分数据，则剩余列为默认或 NULL 值。
2.插入检索出来的数据
利用它可以将一条 SELECT 语句检索出来的结果插入表中
INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;3.将一个表的内容插入到一个新表
CREATE TABLE Custcopy AS
SELECT *
FROM customers;五、更新
结构由三部分组成：
- 要更新的表
- 列名和他们的新值
- 确定要更新那些行的过滤条件
省略 WHERE 子句时，会导致更新表中所有行的数据
UPDATE Customers
SET cust_email = 'kim@163.com'
WHERE cust_id = '10005';六、删除
DELETE FROM mytable
WHERE id = '10012';TRUNCATE TABLE 可以清空表，也就是删除所有行。
TRUNCATE TABLE mytable;使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。
七、查询
DISTINCT
相同值只会出现一次。当它作用于所有列时，也就是说所有列的值都相同才算相同。
SELECT DISTINCT col1, col2
FROM mytable;与 count 统计共用时：
select count(distinct name) from A; --表示表中name去重后的数目LIMIT
限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。
返回前 5 行：
SELECT *
FROM mytable
LIMIT 5;SELECT *
FROM mytable
LIMIT 0, 5;返回第 3 ~ 5 行：
SELECT *
FROM mytable
LIMIT 2, 3;八、排序
ORDER BY 子句
- 位置应是 SELECT 语句中的最后一条子句，该子句的次序如果不对将会出现错误消息。
- 可以使用非检索的列（不是所 select 的列），也就是说，用非检索的列排序数据是完全合法的。
- 可以对多个列进行排序，顺序完全按照指定的列的顺序进行。
- ORDER BY 应该在 WHERE 子句之后
- ASC ：升序（默认） ascend
- DESC ：降序 descend
可以按多个列进行排序，并且为每个列指定不同的排序方式：
SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;九、过滤
WHERE
- 在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过过滤。
- WHERE子句在表名（FROM子句）之后给出
- 同时使用 ORDER BY 与 WHERE 子句时，ORDER BY 应该在 WHERE 子句之后
例：
以下例子检查不是由供应商 DLL01 制造的所有产品：
不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。
SELECT *
FROM mytable
WHERE col IS NULL;下表显示了 WHERE 子句可用的操作符
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| < | 小于 |
| > | 大于 |
| <>，!= | 不等于 |
| <=，!> | 小于等于 |
| >=，!< | 大于等于 |
| BETWEEN | 在两个值之间 例：WHERE prod_price BETWEEN 5 AND 10 |
| IS NULL | 为 NULL 值 |
应该注意到，NULL 与 0、空字符串都不同。
AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。
IN 操作符用于指定条件范围，范围中的每个条件都可以匹配。IN 取合法值的由逗号分隔的清单，全部都括在圆括号中。
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'NOT 操作符用于否定一个条件。
not in和in后面接的子查询的结果是一组集合
!=和=后面接的子查询的结果是一个值
not in是说明前面的不包含在后面子查询的集合里
//查询id是1的数据
select * from table_name where id = '1'
//查询id是1，2，3点数据
select * from table_name where id in ('1','2','3')十、通配符
通配符也是用在过滤语句 WHERE 中，但它只能用于文本字段。
百分号 %
-
匹配 >=0 个任意字符；表示任何字符出现任意次数。
下划线 _
-
匹配 ==1 个任意字符；
方括号 [ ]
-
可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。
-
使用 Like 来进行通配符匹配。
SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本不要滥用通配符，通配符位于开头处匹配会非常慢。
十一、计算字段
字段：基本上与列的意思相同，经常互换使用。
- 在数据库中检索出转换、计算、或按要求格式化后的数据
- 计算字段不实际存在于数据库表中，是运行时在 SELECT 语句内创建的。
在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。
拼接
将值连接到一起构成单个值，类似于 c 语言中的字符串拼接 strcat。
别名
列别名是一个字段或值的替换名，计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。
SELECT col_price * col_num AS total
FROM mytable;上例中对检索出的数据进行算数计算，计算得到的列暂称为 alias ，并输出，列名为 total 。
CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;十二、函数
各个 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')在表 mytable 所有数据中查找发音类似 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 order_num
FROM Orders
WHERE YEAR(order_date) = 2004;2018-4-14 20:25:11
数值处理
| 函数 | 说明 |
|---|---|
| SIN() | 正弦 |
| COS() | 余弦 |
| TAN() | 正切 |
| ABS() | 绝对值 |
| SQRT() | 平方根 |
| MOD() | 余数 |
| EXP() | 指数 |
| PI() | 圆周率 |
| RAND() | 随机数 |
取模运算 根据SQL版本不同 两种使用方式为：
- mod(被除数，除数)
- 被除数 % 除数
十三、聚集数据
经常需要汇总数据而不用把它们检索出来，例如 确定表中行数、获得标中行组的和、找出表列的最大最小平均值等。
聚集函数
运行在行组上，计算和返回单个值
AVG( )函数
通过对表中行数计数并计算特定列值之和，求得该列的平均值。可以用来返回所有列的平均值，也可用来返回特定列或行的平均值（通过添加 where 语句实现）。
COUNT( )函数
- COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值( NULL )或非空值。
- COUNT(column)对特定列中具有值的行进行计数，忽略 NULL 值。
1. count(1) and count(*)
当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count(*)用时多了！
从执行计划来看，count(1)和count(*)的效果是一样的。 但是在表做过分析之后，count(1)会比count(*)的用时少些（1w以内数据量），不过差不了多少。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(1)，用count(*)，sql会帮你完成优化的 因此：count(1)和count(*)基本没有差别！
2. count(1) and count(字段)
两者的主要区别是
（1） count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
（2） count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
组合聚集函数
十四、分组
分组就是把具有相同的数据值的行放在同一组中。
GROUP BY 语句
- 也可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。
- GROUP BY 自动按分组字段进行排序，使用 ORDER BY 也可以按汇总字段来进行排序。
- GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。
SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;过滤分组
WHERE 过滤行，HAVING 过滤分组
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
- HAVING 和 WHERE 的差别：WHERE 在数据分组之前进行过滤，HAVING 在数据分组之后进行过滤。
- 应该仅在与 GROUP BY 子句结合时才是用 HAVING，而 WHERE 子句用于标准的行级过滤。
分组规定：
- GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
- 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
- NULL 的行会单独分为一组；
- 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。
利用 GROUP BY 的 WITH ROLLUP 子句做统计
使用GROUP BY的WITH ROLLUP字句可以检索出更多的分组聚合信息，它不仅仅能像一般的GROUP BY语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息，也就是说这一个 GROUP BY 语句执行完成后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。
十五、子查询
子查询：即嵌套在其他查询中的查询。
例：
货物表 OrderItems 订单表 Orders 客户信息表 Customers
- 作为子查询的 SELECT 语句只能查询单个列，企图检索多个列将返回错误。
例：可以将子查询的结果作为 WHRER 语句的过滤条件：
SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
FROM mytable2);下面的语句可以检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次：
SELECT cust_name, (SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id)
AS orders_num
FROM Customers
ORDER BY cust_name;十六、连接
图解MySQL 内连接、外连接、左连接、右连接、全连接
https://blog.csdn.net/plg17/article/details/78758593
连接是利用 SELECT 能执行的最重要的操作，用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。
关系表的设计就是要保证把信息分解成多个表，一类数据一个表，各表通过某些常用的值（即关系设计中的关系）互相关联。
例：Vendors表包含所有供应商的信息 Products表只存储产品信息
Vendors表中每个供应商占一行，主键可以是供应商ID。Products表中存储了供应商ID，这样就通过Vendors表的主键将两个表联系起来。
- 可以使得来自同一供应商的不同产品不用重复存储所有的供应商信息，节省时间和空间。
- 如果供应商信息变动，可以只更新 Vendors 表中的单个记录。
如果数据存储在多个表中，要使用单条 SELECT 语句检索出数据，就得用到连接。
连接是一种机制，用来在一条 SELECT 语句中关联表。
※ 连接可以替换子查询，并且比子查询的效率一般会更快。
创建连接
可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。
内连接
内连接又称等值连接，使用 INNER JOIN 关键字。
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;FROM 语句中以 INNER JOIN 指定了两个表之间的关系。使用 JOIN 时，连接条件要用特定的 ON 子句而不是 WHERE 子句给出。
可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。
SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;在没有条件语句的情况下返回笛卡尔积。
自连接
自连接可以看成内连接的一种，只是连接的表是自身而已。
一张员工表，包含员工姓名和员工所属部门，要找出与 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";当连接的表是自身时，ON 子句中会写出连接条件，此时必须利用别名实现，以区分要匹配的是同一个表中的部分。
自然连接
- 内连接返回所有的数据甚至相同的列会多次出现，自然连接排除多次出现，使每个列只返回一次。
- 自然连接中只能选择那些唯一的列，这一般是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的。
SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;外连接
外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。
检索所有顾客的订单信息，包括还没有订单信息的顾客。
全外连接的语法为 FULL OUTER JOIN，但是 Access、MySQL、SQL Server都不支持。
十七、组合查询
有两种基本情况可能会使用到组合查询：
- 在单个查询中从不同的表返回类似的结构数据
- 对单个表执行多个查询，按单个查询返回数据
使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果小于等于 M+N 行。
每个查询必须包含相同的列、表达式和聚集函数。
默认会去除相同行，如果需要保留相同行，使用 UNION ALL。
只能包含一个 ORDER BY 子句，并且必须位于语句的最后。
SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;十八、视图
视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。
对视图的操作和对普通表的操作一样。
视图具有如下好处：
- 简单：使用视图的用户不需要关心对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的条件的结果集；
- 安全：只使用实际表的一部分数据，使用视图的用户只能访问他们被允许查询的结果集通过只给用户访问视图的权限，保证数据的安全性；
- 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
视图操作
创建、修改、删除视图及查看视图定义。
create view myview as
select concat(col1, col2) as concat_col, col3*col4 AS compute_col
from mytable
where col5 = val;
alter view myview as
select...;
drop view myview;创建视图时，from 关键字后面不能包含子查询，这种情况可以先将子查询的内容定义为一个试图，然后对该视图在创建视图就可以实现类似的功能了。
视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的：
使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令,同样，在使用 SHOW TABLE STATUS 命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。
- 包含以下关键字的 SQL 语句：聚合函数（SUM、MIN、MAX、COUNT 等）、DISTINCT、GROUP BY、HAVING、UNION 或者 UNION ALL；
- 常量视图；
- select 中包含子查询；
- join；
- from 一个不能更新的视图；
- where 字句的子查询引用了 from 字句中的表。
十九、存储过程和函数
存储过程可以看成是对一系列 SQL 操作的批处理。是事先经过编译并存储在数据库中的一段 SQL 语句的集合。
函数必须有返回值，参数类型只能为 in；
存储过程没有返回值，参数类型为 in、out、inout。
使用存储过程的好处：
- 代码封装，保证了一定的安全性；
- 代码复用；
- 由于是预先编译，因此具有很高的性能。
命令行中创建存储过程需要自定义分隔符(DELIMITER $$ 语句将分隔符改成 $$)，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。
给 变量赋值 都需要用 select into 语句。
delimiter // //通过“DELIMITER $$”命令将语句的结束符从“;”修改成其他符号
create procedure myprocedure( in p_film_id int,in p_store_id int,out ret int )
begin
select invent_id
from inventory
where film_id = p_film_id
and store_id = p_store_id;
and inventory_in_stock(inventory_id);
select found_rows() into ret; //FOUND_ROWS()函数返回的是上一条 SELECT 语句
//（或 SHOW语句等）查询结果集的记录数。
end //
delimiter ;call myprocedure(2,2,@ret);
select @ret;
drop procedure myprocedure;调用存储过程与直接执行 SQL 的效果是相同的，但是存储过程的好处在于处理逻辑都封装在数据库端，调用者不需要了解中间的处理逻辑，一旦处理逻辑发生变化，只需要修改存储过程即可，而对调用者的程序完全没有影响。
查看存储过程或者函数的定义
show create procedure\function film_in_stock \G
二十、游标
在存储过程中使用游标可以对一个结果集进行移动遍历。
游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。
使用游标的四个步骤：
- 声明游标，这个过程没有实际检索出数据；
- 打开游标；
- 取出数据；
- 关闭游标；
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 ;二十一、触发器
触发器是与表有关的数据库对象，在满足条件时触发，并执行触发器中定义的语句集合。
触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。
触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。
INSERT 触发器包含一个名为 NEW 的虚拟表。
创建触发器
create trigger mytrigger
after insert on mytable //触发时间，可以是 before 或 after，
//触发事件，可以是 insert、update 或 delete
for each row
select NEW.col into @result;
SELECT @result; -- 获取结果
对同一个表的相同触发时间的相同触发事件，只能定义一个触发器。
DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。
UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。
MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。
删除触发器
drop trigger mytrigger;查看触发器
通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息，但是因为不能查询指定的触发器，所以每次都返回所有的触发器的信息，使用起来不是很方便。
show triggers \G; //返回所有的触发器的信息
/*************************** 1. row ***************************
//Trigger: customer_create_date
//Event: INSERT
//Table: customer
//Statement: SET NEW.create_date = NOW()
//Timing: BEFORE
//Created: NULL
//sql_mode:
//STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_//ZER
//O,TRADITIONAL,NO_AUTO_CREATE_USER
//Definer: root@localhost
//*************************** 2. row ***************************
另外一个查看方式是查询系统表的 information_schema.triggers 表，这个方式可以查询指定触发器的指定信息，操作起来明显方便很多。
desc triggers;
/*+----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| TRIGGER_CATALOG | varchar(512) | YES | | | |
| TRIGGER_SCHEMA | varchar(64) | NO | | | |
+----------------------------+--------------+------+-----+---------+-------+*/
select * from triggers
where trigger_name = 'ins_film_bef' \G;
二十二、锁定语句
MySQL 支持对 MyISAM 存储引擎的表级锁定，对 InnoDB 存储引擎的表的行级锁定。默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。
但是有的情况下，用户需要明确的进行锁表或者对事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。
LOCK TABLE 和 UNLOCK TABLE：
lock table mytable write; //加读锁
// ...
lock table mytable read; //加写锁
// ...
unlock tables;
一个获得表锁和释放表锁的简单例子，演示的是 film_text 表获得 read 锁的情况，其他 session 更新该表记录会等待锁（一直阻塞，没有其他提示），film_text 表释放锁以后，其他 session 可以进行更新操作（开始执行并完成之前的更新操作）。
二十三、事务管理
基本术语：
- 事务（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
commit and chain; //自动开始一个新的事务而不用使用 transaction如果在锁表期间，用 start transaction 命令开始一个新事务，会造成一个隐含的 unlock tables 被执行。
二十四、字符集
基本术语：
- 字符集为字母和符号的集合；
- 编码为某个字符集成员的内部表示；
- 校对字符指定如何比较，主要用于排序和分组。
除了给表指定字符集和校对外，也可以给列指定：
CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;可以在排序、分组时指定校对：
SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;二十五、权限管理
MySQL 的账户信息保存在 mysql 这个数据库中。
USE mysql;
SELECT user FROM user;创建账户
新创建的账户没有任何权限。
CREATE USER myuser IDENTIFIED BY 'mypassword';修改账户名
RENAME 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.
114
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
