mysql必知必会

Posted on 2017-11-21 19:31:55

WHERE

distinct,(作用于所有列而非被前置的) all
limit offset
DESC ASC
<>
AND优先级高于OR
IN (合法值,分隔)

LIKE

  • %
    任何字符任何次数
  • _
    通配单个字符
    通配符耗时更长,若必须使用,尽量不放在搜索模式开始处

    REGEXP

  • 使用//转义
  • 字符类

    [:alnum:] 任意字母数字
    [:alpha:]任意字母
    [:blank:] 空格和制表
    [:cntrl:]控制字符
    [:digit:]数字字符
    [:graph:]图形字符
    [:lower:]小写
    [:print:]图形或空格字符
    [:punct:]不在alnum和cntrl中的任意字符
    [:space:]空格、制表符、新行、和回车
    [:upper:]大写
    [:xdigit:]十六进制数

  • {n,m}
  • 定位符

    [[:<:]]词首
    LIKE,匹配整个列,REGEXP在列值内匹配

SELECT

简单查询

计算字段
运行时在SELECT语句创建

  • 拼接
    Contact()
  • 格式化
    RTrim()去除值右边所有空格
    LTrim()
    Trim()
  • 别名
    AS
  • 算术计算

    数据处理函数

  • 文本函数
函数 说明
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转换为大写

Soundex将文本串转换为描述其语音表示的字母数字模式,从而比较发音。

  • 日期和时间函数
函数 说明
AddDate() 增加一个日期(天、周)
AddTime() 增加一个时间(时,分)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期计算函数
Date_Format 返回一个格式化的日期或字符串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个时间的月份部分
Now() 返回当前日期
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分

比较日期/时间时,使用Date()指明,而非直接比较。

    Select column 
    from table
    Where Date(order_date) = '2016-09-08';
    Select column
    from table
    Where order_date = '2016-09-09';/*当order_date为datetime时必然失败*/
  • 数值函数
函数 说明
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个数的正切

汇总函数

聚集函数

汇总数据而不实际检索出来。

函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某个列之和
  • AVG()只用于单个列,忽略null
  • COUNT()使用*时计算所有行,作用单个列忽略null
  • MAX()使用于文本数据时,如果数据按相应的列排序,MAX()返回最后一行。
  • SUM()可用于多列计算。
  • 可在其中指定ALL/DISTINCT

    分组数据

    将数据分为多个逻辑组,以便分别聚集计算。
    GROUP BY
    指示分组数据,对每个组而非整个结果进行聚集。
    例如
    SELECT vend_id,COUNT(*) AS num_prods
    FROM products
    GROUP BY vend_id;
    +---------+-----------+     
    | vend_id | num_prods |     
    +---------+-----------+     
    | 1001 | 3 |     
    | 1002 | 2 |     
    | 1003 | 7 |     
    | 1005 | 2 |     
    +---------+-----------+
    
  • 可以包括任意数目的列,对分组进行更细致控制。
  • 必须出现在WHERE之后,ORDER BY之前。
  • 分组列中的NULL将分为一组。
  • SELECT中的列只能是聚集、计算语句或已在GROUP BY中给出
  • GROUP中的列必须是检索列或者有效表达式(不能是聚集函数),若SELECT中使用表达式,则必须在group by子句中指定相同的表达式,不能使用别名(次点存疑,本地mysql5.7中指定表达式中使用的列名或别名并不报错)。
  • 如果在group by子句中嵌套了分组,数据将在最后规定的分组上进行汇总。
    输出顺序并不一定是分组的顺序。
    过滤分组
    HAVING(分组后计算)子句中支持所有WHERE(分组前计算)操作符。
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >=3;
+---------+-----------+     
| vend_id | num_prods |     
+---------+-----------+     
| 1001 | 3 |     
| 1003 | 7 |     
+---------+-----------+

复杂查询

子查询

嵌套在其他查询中的查询,由内向外处理。
并非最有效的方法

进行过滤

例如

SELECT cust_id 
FROM orders 
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id='TNT2');

应确保WHERE与子查询中SELECT具有相同数目的列。

作为计算字段
SELECT cust_name,
        cust_state,
        (SELECT COUNT(*)
         FROM
         orders
         WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

涉及外部查询注意使用完全限定的列名,限制歧义。

联结

SELECT语句中关联表。

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;

WHERE对笛卡尔积进行过滤。

内部联结

INNER JOIN使用ON子句指定联结条件。

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
高级联结

允许使用AS起表别名

自联结

必须使用表别名和完全限定名以避免二义性。
通常替代子查询,也不绝对,还需具体比较性能。

自然联结

重复的列只返回一次。

SELECT * from tag natural join tagmap;
+-------+---------+----+-----+
| tagid | tagname | id | aid |
+-------+---------+----+-----+
| 1 | 一 | 1 | 1 |
| 1 | 一 | 2 | 2 |
| 1 | 一 | 3 | 3 |
| 1 | 一 | 4 | 4 |
| 2 | 二 | 5 | 5 |
| 2 | 二 | 6 | 6 |
| 2 | 二 | 7 | 7 |
| 3 | 三 | 8 | 8 |
+-------+---------+----+-----+
SELECT * from tag inner join tagmap on tag.tagid=tagmap.tagid ;
+-------+---------+----+-------+-----+
| tagid | tagname | id | tagid | aid |
+-------+---------+----+-------+-----+
| 1 | 一 | 1 | 1 | 1 |
| 1 | 一 | 2 | 1 | 2 |
| 1 | 一 | 3 | 1 | 3 |
| 1 | 一 | 4 | 1 | 4 |
| 2 | 二 | 5 | 2 | 5 |
| 2 | 二 | 6 | 2 | 6 |
| 2 | 二 | 7 | 2 | 7 |
| 3 | 三 | 8 | 3 | 8 |
+-------+---------+----+-------+-----+
外部联结

OUTER JOIN指定类型,包含没有关联行的行,需要使用LEFTRIGHT指定表。

SELECT customers.cust_id,orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id=orders.cust_id;
+---------+-----------+     
| cust_id | order_num |     
+---------+-----------+     
| 1001 | 3 |     
| 1002 | 2 |     
| 1003 | NULL |     
| 1005 | 2 |     
+---------+-----------+

组合查询

执行多个查询,并将结果作为单个查询结果集返回.
使用于

  • 单个查询从多个表返回类似结构的数据
  • 对单个表执行多个查询,按单个查询返回数据(通常与多个WHERE子句功能相同)
    规则:
  • UNION必须由两条及以上SELECT语句组成,语句间使用关键字UNION分隔。
  • 各查询必须包含相同的列、表达式或聚集函数,不要求次序
  • 列数据类型必须兼容
    重复的行自动取消
    可使用UNION ALL返回所有匹配行。

    排序

    只能使用一条ORDER BY,出现在最后一条SELECT语句之后

    全文本搜索

    InnoDB不支持该功能,而MyISAM支持。

    Like和正则的不足:

  • 性能
    尝试匹配表中所有行,非常耗时
  • 明确控制
    其实还是做得到的,就是麻烦
  • 智能化的结果
    不能智能化的选择结果
    启用
    使用全文本搜索,需要索引被搜索的列。
    创建表时启用全文本搜索,FULLTEXT给出被索引列的列表(逗号分隔)。
    CREATE TABLE productnotes
    (
      note_id int NOT NULL AUTO_INCREMENT,
      prod_id char(10) NOT NULL,
      note_date datetime NOT_NULL,
      note_text text NULL,
      PRIMARY(note_id),
      FULLTEXT(note_text)
    )ENGINE=MyISAM;
    
    Mysql将自动维护该索引,当增加、更改或删除行时,索引自动更新。
    导入数据后再启用FULLTEXT索引!
    使用
    函数Match()指定被搜索的列,Against()指定搜索表达式
    SELECT note_text
    from productnotes
    WHERE match(note_text) against('rabbit');
    
    传递给Match()的值必须与FULLTEXT中的值完全相同(包括次序),结果不区分大小写,以匹配良好程度排序。
    查询扩展WITH QUERY EXPANSION
    放宽返回结果的范围,返回与搜索结果有关的其他行。
  1. 全文本搜索,找出匹配行,
  2. 检查匹配行,选择所有有用词,
  3. 再次全文本搜索,加上这些有用词。
    SELECT note_text
    from productnotes
    WHERE match(note_text) against('anvils' with query expansion);
    
    布尔文本搜索IN BOOLEAN MODE
    布尔方式的搜索可以给定:
  • 要匹配的词
  • 要排斥的词
  • 排列提示(提示某些词比较重要)
  • 表达式分组
  • 其他
    布尔文本搜索条件中的操作符
符号 说明
+ 包含,词必须出现
- 排斥,词必须不出现
> 包含,并且增加等级
< 包含,并且减少等级
() 将词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~ 取消一个词的排序值
* 词尾的通配符
“ “ 定义一个短语(匹配时匹配整个短语)

举例说明
包含heavy并且不包含任何一rope开头的词

SELECT note_text
from productnotes
WHERE match(note_text) against('heavy -rope*' in boolean mode);

包含rabbit和bait中至少一个词(未使用操作符)

SELECT note_text
from productnotes
WHERE match(note_text) against('rabbit bait' in boolean mode);

匹配短语rabbit bait

SELECT note_text
from productnotes
WHERE match(note_text) against('"rabbit bait"' in boolean mode);

搜索safe和combination,降低后者的等级

SELECT note_text
from productnotes
WHERE match(note_text) against('+safe +(<combination)' in boolean mode);
几点说明
  • 索引时,短词(3个及以下字符的词)被忽略且从索引排除
  • Mysql有一个非用词(stopword)列表,总是被忽略
  • 出现在50%以上的行中的词,作为非用词忽略,不用于IN BOOLEAN MODE
  • 表中行数少于3行,不返回结果,由上条推出
  • 忽略词中单引号
  • 不具有词分隔符的语言不能恰当返回搜索结果
  • 仅在MyISAM支持。

INSERT

插入完整行

INSERT INTO table_name (
    column1, 
    column2,
    ...)
VALUES (
    value1, 
    value2,
    ....);

可以但不建议去除列名列表,会导致语句高度依赖表中的定义次序,容易出错。
显然,能够省略部分列,前提是允许为NULL或者拥有默认值
可以使用LOW_PRIORITY降低INSERT优先级,如INSERT LOW_PRIORITY INTO

插入多行

使用分隔多组值即可。

插入检索结果

INSERT INTO customers_new (cust_name, 
    cust_address, 
    cust_city, 
    cust_state, 
    cust_zip, 
    cust_country,
    cust_contact, 
    cust_email)
SELECT cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country,
    cust_contact,
    cust_email
FROM customers;

不要求列名匹配,SELECT可包含WHERE进行过滤。

UPDATE & DELETE

UPDATE

UPDATE语句由以下三部分组成:

  1. 要更新的表
  2. 列名和新值
  3. 过滤条件
UPDATE table_name 
SET column1=new-value1,
    column2=new-value2,
    ......
[WHERE Clause];

也可以使用子查询

UPDATE customers, (
    SELECT cust_name
    FROM customers
    WHERE cust_id = 10004
) temp
SET customers.cust_name = temp.cust_name
WHERE cust_id = 10005;

若用UPDATE更新多行,过程中某行出现错误,整个操作都将被取消,可使用IGNORE,出现错误亦继续更新,UPDATE IGNORE customers

DELETE

DELETE FROM customers 
WHERE cust_id = 10006;

若要删除所有行,使用TRUNCATE TABLE语句速度更快。

  • 记住WHERE
  • 使用之前尽量先用SELECT测试
  • 注意完整性约束

表操作

创建表

基本语法

CREATE TABLE table_name (column_name column_type);
CREATE TABLE vendors
(
  vend_id int NOT NULL AUTO_INCREMENT,
  vend_name     char(50) NOT NULL,
  vend_address char(50) ,
  PRIMARY KEY (vend_id)
)ENGINE=InnoDB;

表名必须不存在
列默认允许NULL值,必需字段可以指定NOT NULL

定义主键

使用PRIMARY(column_name)定义主键,必须唯一,可用多个列,注意不能允许NULL

AUTO_INCREMENT

当增加一行时,该列值自动赋予下一个可用值(+1)。
每个表只允许一个AUTO_INCREMENT列,且必需被索引。
使用SELECT last_incert_id()获取最后一个AUTO_INCREMENT值。

默认值

使用DEFAULT

引擎

形如ENGINE=InnoDB
MySQL具有多个引擎

  • InnoDB

    事务处理引擎,不支持全文本搜索

  • MEMORY

    功能等同MyISAM,但数据存储在内存,特别适用临时表

  • MyISAM

    高性能,但不支持事务处理

可以混用引擎,但外键(强制实施引用完整)不能跨引擎

更新表

原则:表中存储数据后,尽量不再更新表
使用ALTER TABLE,给出

  1. 在alter table 之后给出要更改的表名(该表必须存在,否则报错)
  2. 所做更改的列表

可用的更改有

  • ADD
    可以增加字段、主键、外键….
    ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_customers 
    FOREIGN KEY(cust_id) REFERENCES customers(cust_id);
    
  • MODIFY
    修改字段类型
  • CHANGE
    修改字段(包括字段名)
  • DROP
    删除字段
    对于复杂的表结构,直接删了建新的吧…..

    删除表

    DROP TABLE table_name;
    

    重命名

    RENAME TABLE table_name TO new_table_name;
    

视图

虚拟的表,不包含数据,只包含使用时动态检索数据的查询。
优点:

  • 重用SQL语句
  • 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节。
  • 使用表的组成部分而不是整个表。
  • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
    规则:
  • 与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字)
  • 对于可以创建的视图数目没有限制
  • 为了创建视图,必须具有足够的访问权限
  • 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图
  • ORDER BY可以用在视图中,但如果从该视图检索数据SELECT中也含有ORDER BY,那么该视图中的ORDER BY将被覆盖
  • 视图不能索引,也不能有关联的触发器或默认值
  • 视图可以和表一起使用

    使用

  • 视图用CREATE VIEW语句来创建
  • 使用SHOW CREATE VIEW viewname;来查看创建视图的语句
  • 用DROP删除视图,其语法为DROP VIEW viewname;
  • 更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在,则第二条更新语句会创建一个视图,如果要更新的视图存在,则第2条更新语句会替换原来的视图。

    简化复杂的联结

    CREATE VIEW productcustomers AS 
    SELECT
      cust_name,
      cust_contact,
      prod_id
    FROM
      customers,
      orders,
      orderitems
    WHERE
      customers.cust_id = orders.cust_id
      AND orderitems.order_num = orders.order_num;
    
    SELECT * FROM productcustomers;
    
    一次编写,多次使用,居家旅行,必备良品。

    重新格式化检索出的数据

    CREATE VIEW vendorlocation AS
    SELECT Concat(Rtrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
    FROM vendors
    ORDER BY vend_name;
    
    使用
    SELECT * FROM vendorlocation
    

    更新视图

    视图通常可更新,对于视图的更新,将作用于其基表
    显然,mysql不能正确地作用于基表时,不能更新。
    也即,
  • 分组
  • 联结
  • 子查询
  • 聚集
  • DISTINCT
  • 导出(计算)列
    均不能更新

存储过程

完整的操作需要多条语句实现(不就是事务嘛),使用存储过程,为以后的使用而保存的一条或多条语句的集合。
简单、安全、高性能。

使用

CALL productpricing();

创建

CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END

在mysql命令行运行需注意,存储过程中的;会被解释,从而出现语法错误
可以通过临时修改语句分隔符解决

DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END//

删除

DROP PROCEDURE productpricing;

使用参数

通常存储过程不显示结果而是返回给指定的变量(内存中的特定位置,存储临时数据)

CREATE PROCEDURE sp_name ([ proc_parameter ]) [ characteristics..] routine_body

proc_parameter指定存储过程的参数列表,列表形式如下:

 [IN|OUT|INOUT] param_name type

其中in表示输入参数,out表示输出参数,inout表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型

只有输出

-- 使用PROCEDURE定义只带输出参数的存储过程;
CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
    SELECT Min(prod_price)
    INTO pl
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT AVG(prod_price)
    INTO pa
    FROM products;
END;
    -- 使用CALL调用存储过程;
CALL productpricing(@pricelow,    
                    @pricehigh,
                    @priceaverage);
    -- 显示检索出的产品最低,最高,平均价格
SELECT @pricehigh,@pricelow,@priceaverage;

变量需以@开头
参数数据类型要与表中数据类型匹配,记录集不允许

有输入输出

-- 使用PROCEDURE定义带输入,输出参数的存储过程;
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO ototal;
END;
    -- 使用CALL调用存储过程;
CALL ordertotal(20005, @total);
-- 显示检索出的产品最低,最高,平均价格
SELECT @total;
-- 这样还可以方便的得到另一个订单的合计过程
CALL ordertotal(20009,@total);
SELECT @total;

放入逻辑

-- 定义智能存储过程
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEN,
    OUT otatol DECIMAL(8,2)
)
BEGIN
    -- 声明变量
    DECLARE total DECIMAL(8,2);
    DECLARE taxrate INT DEFAULT 6;
    -- 得到全部订单
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num=onumber
    INTO total;
    -- IF 判断
    IF taxable THEN
        SELECT total+(total/100*taxarate) INTO total;
    END IF;

    SELECT total INTO ototal;
END;
CALL ordertotal(20005,0,@tatol);
SELECT @total;
CALL ordertotal(20005,1,@tatol);
SELECT @total;

检查存储过程

显示创建该存储过程的语句

SHOW CREATE PROCEDURE <name>;

返回创建时间、创建者等详细信息

SHOW PROCEDURE STATUS;

以上返回所有过程状态,可使用LIKE过滤

游标

游标是存储在服务器上的数据库查询,不是语句,而是语句检索出的结果集,应用程序可以根据需要滚动或浏览其中数据。
只能用于存储过程

  • 需要预先声明(只是定义SELECT语句而不检索)
  • 声明后必须打开游标供使用,此过程检索数据
  • 对于填有数据的游标,根据需求取出
  • 结束使用时关闭
    使用DECLARE创建游标,并定义相应SELECT语句。
    使用OPENCLOSE关闭,到达END将会自动关闭。
    CREATE PROCEDURE processorders()
    BEGIN
      DECLARE ordernumbers CURSOR -- 声明游标
      FOR
      SELECT order_num FROM orders;
      -- 打开游标
      OPEN ordernumbers;
      -- 关闭游标
      CLOSE ordernumbers;
    END;
    

    使用游标

    使用FETCH分别访问游标各行,指定检索数据及其存储。
    REPEAT内反复执行,直到done为真(UNTIL done END REPEAT;规定),DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;定义句柄,在条件出现时执行。
    此处,’02000’是错误码,没有更多行供循环继续。
    CREATE PROCEDURE processorders()
    BEGIN
      -- 声明局部变量
      DECLARE done BOOLEN DEFAULT 0;
      DECLARE o INT;
      DECLARE t DECIMAL(8,2);
      -- 声明游标
      DECLARE ordernumbers CURSOR
      FOR
      SELECT order_num FROM order;
      -- 定义是否终止的条件
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
      -- 创建表
      CREATE TABLE IF NOT EXISTS ordertotals
      (order_num INT, total DECIMAL(8,2));
      -- 打开游标
      OPEN ordernumbers;
      -- 进入循环
      REPEAT
          FETCH ordernumbers INTO o; -- 得到游标具体的值
          CALL ordertotal(o, 1 ,t); -- 调用前一节的存储过程
          INSERT INTO ordertotals(order_num, total) -- 插入到新建的表中
          VALUES(o, t);
      UNTIL done END REPEAT;
      -- 关闭游标
      CLOSE ordernumbers;
      END
    -- 检索新建的表
    CALL processorders();
    SELECT * FROM ordertotals;
    

    触发器

    使某些语句在事件发生时自动执行(可以用来维护一致性约束嘛==)
    触发器:响应DELETEINSERTUPDATEBEGINEND间一组语句,而自动执行的一条SQL语句。
    只有表支持触发器,视图不支持。

    创建触发器

    使用CREATE TRIGGER语句创建
    在创建触发器时,需要给出四条信息:
  • (表中)唯一的触发器名;
  • 触发器关联的表;
  • 触发器应该响应的活动(DELETE,INSERT或UPDATE)
  • 触发器何时执行(处理之前或之后);
    CREATE TRIGGER newproduct AFTER INSERT ON PRODUCTS
    FOR EACH ROW SELECT 'Product added';
    
    创建了一个newproduct新触发器。在INSERT语句成功执行之后执行,并对每个插入行执行(FOR EACH ROW)。在这个例子中,文本‘Product added’在每个插入的显示一次
    触发器按每个表每个事件每次地定义,只允许一个触发器,所以最多支持6个触发器。
    BEFORE触发器失败,后续语句不发生,语句失败,AFTER触发器不发生。

    删除触发器

    触发器不能更新/覆盖,要修改,先删除。
    DROP TRIGGER newproduct;
    

    使用触发器

    INSERT触发器

  • INSERT触发器代码内,可引用一个名为NEW的虚拟表,访问被插入的行
  • BEFORE INSERT触发器中,NEW值也可以被更新(允许修改将被插入的值
  • 对于AUTO_INCREMENT列,NEWINSERT执行前包含0,执行之后包含新的自动生成值
    -- 定义INSERT触发器
    CREATE TRIGGER neworder AFTER INSERT ON orders
    FOR EACH ROW SELECT NEW.order_num;
    -- 测试触发器
    INSERT INTO orders(order_date, cust_id)
    VAlUES(Now(), 10001);
    
    通常,将BEFORE用于数据验证和净化。

DELETE触发器

  • DELETE触发器代码内,可引用OLD虚拟表,访问被删除的行
  • OLD只读
 CREATE TRIGGER deleteorder BEFORE DELETE ON orders
 FOR EACH ROW
 BEGIN
     INSERT INTO archive_orders(order_num, order_date, cust_id)
     VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;
-- 在任意订单被删除之前执行此触发器。见删除之保存删除行;

UPDATE触发器

  • UPDATE语句中可以引用OLD访问以前的值,引用NEW访问新更新的值;
  • BEFORE UPDATE触发器中,NEW值也可以被更新(允许修改将被更新的值)
    CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
    FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
    -- 任何数据净化都需要在UPDATE语句之前进行,每次更新一行时,都会通过Upper();
    

    小结

  • 显然,适用于维护数据一致性,不过,似乎很少用到(?本鶸没接触过)
  • 还能用于创建审计跟踪
  • 据本书说MySQL触发器不支持CALL语句,aka,不能调用存储过程

事务处理

事务:完成单一逻辑功能的操作集合

事务处理,用以维护数据库完整性,保证操作集合要么完全执行,要么完全不执行。

如果没有错误发生,整组语句提交给(写到)数据库表。如果发生错误,则进行回退(撤销)以恢复数据库导某个已知且安全的状态。

  • 事务(transaction):指一组SQL语句
  • 回退(rollback):指撤销指定SQL语句的过程
  • 提交(commit):指将未存储的SQL语句结果写入数据库表
  • 保留点(savepoint):指事务处理中设置的临时占位符,可以对它发布回退

    控制事务处理

    SQL语句组分解为逻辑块,并明确规定何时回退,合适不会退。

    START TRANSACTION
    

    开始事务

    ROLLBACK

    只能在事务内。

    START TRANSACTION;
    DELETE FROM orderitems;
    SELECT * FROM orderitems;
    ROLLBACK;
    SELECT * FROM orderitems;
    

    仅用于INSERTUPDATEDELETECREATEDROP不会被撤销。

    COMMIT

    一般的MySQL语句都是直接针对数据库表执行和编写的,提交(写或保存)操作是自动进行的(隐含提交)。
    在事务处理块中,需要进行明确的提交,使用COMMIT语句。
    隐含事务关闭
    当COMMIT或ROLLBACK语句执行后,事务会自动关闭

    SAVEPOINT

    部分提交或回退

    SAVEPOINT deletel;--保留点以名字唯一标识
    --一大堆
    ROLLBACK TO deletel;
    

    保留点于事务处理完成后自动释放,也可以使用RELEASE SAVEPOINT明确地释放保留点。

    更改默认的提交行为

    默认的MySQL行为是自动提交所有更改,任何时候执行一条MySQL语句,该语句实际上都是针对表执行的,而且所做的更改立即生效。
    为指示MySQL不自动提交更改,需要如下语句:

    SET autocommit = 0;
    

    autocommit标志决定是否自动提交更改,设置autocommit为0(假)指示MySQL不自动提交更改。

    使用COMMIT修改才会提交,也就是说,手残有救了,可以ROLLBACKCOMMIT了那就…┑( ̄Д  ̄)┍)。

autocommit标志是针对每个连接而不是服务器的。

字符集

  • 字符集:为字母和符号的集合;
  • 编码:为某个字符集成员的内部表示;
  • 校对:为规定字符如何比较的指令;
-- 显示所有可用的字符集;
SHOW CHARACTER SET;

-- 查看所支持校对的完整列表;
SHOW COLLATION;

-- 确定在使用的字符集和校对;
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

-- 在创建表时,指定一个字符集和一个校对顺序;
CREATE TABLE matable
(
    columnn1 INT,
    columnn2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;
  • 指定CHARACTER SETCOLLATE,则使用这些值
  • 只指定CHARACTER SET,将使用此字符集的默认校对
  • 均不指定,使用数据库默认值

还允许对列单独设置。

可在SELECT语句中指定字符集和校对顺序

--使用COLLATE指定校对顺序
SELECT * FROM customers;
ORDER BY lastname,firstname COLLATE Latin1_general_cs;

安全管理

访问控制

  • 管理访问控制需要创建和管理用户账号
  • 有的用于管理,有的供用户使用,有的供开发人员使用
  • 不要使用root

管理用户

账户信息存储在数据库mysql.user中。

  • 创建用户账号

    CREATE USER ben IDENTIFIED BY 'p@$$w0rd';
    

    不建议通过对user表修改增加用户。

  • 重命名

    RENAME USER ben TO bforta;
    
  • 删除用户账号

    DROP USER bforta;
    

设置访问权限

SHOW GRATNS FOR fanyc; -- 显示权限

+-----------------------------------+
| Grants for fanyc@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'fanyc'@'%' |
+-----------------------------------+

USAGE表示无权限。

使用GRANT设置权限,给出

  • 要授予的权限
  • 被授予权限的数据库/表
  • 用户名
-- 添加SELECT权限
GRANT SELECT ON blog.* TO fanyc;
SHOW GRANTS FOR fanyc;
+-----------------------------------------+
| Grants for fanyc@%                      |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'fanyc'@'%'       |
| GRANT SELECT ON `blog`.* TO 'fanyc'@'%' |
+-----------------------------------------+

使用REVOKE撤销特定的权限。

--删除SELECT权限
REVOKE SELECT ON blog.* FROM fanyc;

SHOW GRANTS FOR fanyc;
+-----------------------------------+
| Grants for fanyc@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'fanyc'@'%' |
+-----------------------------------+

权限控制可用于

  • 整个服务器(GRANT ALLREVOKE ALL
  • 整个数据库
  • 特定的表
  • 特定的列
  • 特定的存储过程
权限 说明
ALL 除GRANT OPTION外的所有权限
ALTER 使用ALTER TABLE
ALTER ROUTINE 使用ALTER PROCEDURE和DROP PROCEDURE
CREATE 使用CREATE TABLE
CREATE ROUTINE 使用CREATE PROCEDURE
CREATE TEMPORARY 使用CREATE TEMPORARY TABLE
TABLES
CREATE USER 使用CREATE USER、DROP USER、RENAME USER、REVOKE ALLPRIVILEGES
CREATE VIEW 使用CREATE VIEW
DELETE 使用DELETE
DROP 使用DROP TABLE
EXECUTE 使用CALL和存储过程
FILE 使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION 使用GRANThe和REVOKE
INDEX 使用CREATE INDEX 和DROP INDEX
INSERT 使用INSERT
LOCK TABLES 使用LOCK TABLES
PROCESS 使用SHOW FULL PROCESSLIST
PELOAD 使用FLUSH
REPLICATION CLIENT 服务器位置的访问
REPLICATION SLAVE 由复制从属使用
SELECT 使用SELECT
SHOW DATABASES 使用SHOW DATABASES
SHOW VIEW 使用SHOW CREATE VIEW
SHUTDOWN 使用mysqladmin shutdown(用来关闭MySQL)
SUPER 使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允许mysqladmin调试登录
UPDATE 使用UPDATE
USAGE 无访问权限

通过分隔权限,简化授权

更改口令

-- 设置口令(不指定用户,则默认更新当前用户口令)
SET PASSWORD FOR fanyc = Password('1233456');

新口令必须传递至Password()函数加密。

数据库维护

备份

MySQL数据库是基于磁盘的文件。总是打开/使用,普通的文件备份不一定有效。

  1. 使用mysqldump转储到外部文件
  2. 使用mysqlhotcopy复制所有数据(部分引擎支持,没用过,┑( ̄Д  ̄)
  3. 使用BACKUP TABLESELECT INTO OUTFILE转储到外部文件(同没用过┑( ̄Д  ̄)┍

保证所有数据被写到磁盘,使用FLUSH TABLES

维护

Table 表名称
Op 进行检查
Msg_type 状态、错误、信息或错误之一
Msg_text 消息

ANALYZE TABLE,检查表键是否正确。

ANALYZE TABLE tag;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| blog.tag | analyze | status   | OK       |
+----------+---------+----------+----------+

CHECK TABLE检查一个表或多个表是否有错误,选项仅支持MyISAM

CHECK TABLE tbl_name[,tbl_name] ... [option] ... 
option= {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
类型 意义
QUICK 不扫描行,不检查错误的链接。
FAST 只检查没有被正确关闭的表。
CHANGED 只检查上次检查后被更改的表,和没有被正确关闭的表。
MEDIUM 扫描行,以验证被删除的链接是有效的。也可以计算各行的关键字校验和,并使用计算出的校验和验证这一点。
EXTENDED 对每行的所有关键字进行一个全面的关键字查找。这可以确保表是100%一致的,但是花的时间较长。

从表中大量删除数据,使用OPTIMIZE TABLE回收空间。

诊断启动问题

  • —help
  • —safe-mode (在我的5.7.20并没有该选项,倒是有--safe-updates)
  • —verbose显示全文本消息

日志

  • 错误日志
  • 查询日志,记录查询操作,默认情况下查询日志是关闭的。开启查询日志会增加很多磁盘 I/O, 所以如非出于调试目的,不建议开启查询日志。
  • 二进制日志,记录 MySQL 数据库中所有与更新相关的操作,即二进制日志记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,但是不包括数据查询语句。常用于恢复数据库和主从复制。
  • 缓慢查询日志,执行时长(包括等待CPU/IO的时间)超过 long_query_time 这个变量定义的时长的查询。慢查询日志开销比较小,可以用于定位性能问题。

可使用FLUSH LOGS刷新和重新开始日志文件。

可参考

性能

讲真就本鶸日常用到的还不至于有性能瓶颈。

  • 首先,MySQL(与所有的DBMS一样)具有特定的硬件建议。在学习和研究MySQL时,使用任何旧的计算机作为服务器都可以。但对于生产的服务器来时,应该坚持遵循这些硬件建议

  • 一般来说,根据的生产DBMS应该允许在自己的专业服务器上

  • MySQL是用一系列的默认设置预先配置的,从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等(为了查看当前配置,可使用SHOW VARIABLES;和 SHOW STATUS;)

  • 一个MySQL一个多用户多线程的DBMS,换言之,它经常同时执行多个任务。如果这些任务中的某个执行缓慢,则所有的请求都会执行缓慢。如果你遇到显著的性能不良,可使用SHOW PROCESSLIST显示所有活动进程(以及它们的线程ID和执行时间),你还可以用KILL命令终结某个特定的进程(使用这个命令需要作为管理员登陆)

  • 总是不止一种方法编写一条SELECT语句,应该实验联结、并、子查询等,找出最佳的方法

  • 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句

  • 一般来说,存储过程执行的比一条一条执行的其中的各条MySQL语句快

  • 应该总是使用正确的数据类型

  • 决不要检索比需求还要多的数据,换言之,不要用SELECT * FROM (除非真的需要)

  • 有的操作(包括INSERT )支持一个可选的DELAYED关键字,如果使用它,将把控制立即返回给条用程序,并且一旦有可能就现实执行该操作

  • 在导入数据时,应该关闭自动提交。你可能还想删除索引(包括FULLTEXT索引),然后再导入完成后再重建它们。

  • 必须索引数据库以改善数据检索的性能。确定索引什么不是一个微不足道的任务,需要分析使用的SAELECT语句以找出重复的WHERE和ORDER BY 子句。如果一个简单的WHERE子句返回结果所花的时间太长,则可以断定其中使用的列(或几个列)就需要索引的对象

  • 你的SELECT语句中有一系列复杂的OR条件吗?通过使用多条SELECT语句和联结它们的UNION语句,就需要索引的对象

  • 索引改善数据检索的性能,但损害数据插入、删除和更新的新能。如果你有一些表,它们世界数据库且不经常被搜索,则在有必要之前不要索引它们。

  • LIKE很慢,一般来说,最好的使用FULLTEXT而不是LIKE

  • 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改,理想的优化和配置也会发生改变。

  • 最重要的规则是,每条规则在某些条件下都会被打破。

完结撒花!