刘刚刚的个人博客

mysql必知必会


用了将近4年的mysql,今天在想加强下时,发现平时多是用ORM,偶尔一些为了提高效率或特殊要求会写sql,所以准备读下这本经典。也对MySql基础做个查缺补漏。

本文章中不会对最基础的语句用法做简介,适合查缺

第一章 了解 MySql

本章中主要介绍了有关数据库的一些概念:数据库、表、行、列、数据类型、主键、sql语句

tip:

  1. 数据类型不仅可以限制数据录入的格式,还在磁盘优化方面有重要作用
  2. 主键使用的好习惯:

    • 是更新主键列中的值
    • 不重用主键列中的值
    • 不使用可能变更的数据作为主键的值

第二章 MySql简介

介绍了MySql的客户端及服务端,以及使用的工具.

tip:

在写这本书时,mysql最新版为5.0版本,目前(2020)已经到了8.0版,常用的工具为navicat

第三章 使用MySql

#显示数据库列表
SHOW DATABASES

#切换数据库
USE 数据库名

#显示数据库内的表
SHOW TABLES

#显示表的列
SHOW COLUMNS FROM 表名

#显示创建数据库或者表信息 (在创建后使用)
SHOW CREATE DATABASE 创建好的数据库名
SHOW CREATE TABLE 创建好的表名

#显示服务器的信息
SHOW STATUS

#显示所有用户的安全权限
SHOW GRANTS

#显示服务器的错误、警告信息
SHOW ERRORS
SHOW WARNINGS

第四章 检索数据

SQL语句需要以分号结尾

#从表中检索多列
SELECT 列1,列2  FROM 表名;
#从表中检索所有
SELECT * FROM 表名;
#检索字段值不重复的行(检索出来的数据列1和列2的都不相同)
SELECT DISTINCT 列1,列2 FROM 表名;
#限定检索的条数
SELECT 列 FROM 表名 LIMIT 起始条数,数据数量
#使用完全限定的表名
SELECT 表名.字段名  FROM 数据库名.表名

tip:

  1. 使用*号会降低检索和应用程序的性能

第五章 排序检索数据

本章主要介绍了 ORDER BY 子句的使用.

子句:SQL语句由子句构成,有些子句是可选的,有些是必须的,子句有关键字和提供的数据组成.例如:FROM 是 SELECT的子句

# 对单个字段进行排序
SELECT 列1 FROM 表名 ORDER BY 列1

#对多个字段进行排序(只有在列1中的数据相同时,才会根据列2排序)
SELECT 列1,列2 FROM 表名 ORDER BY 列1,列2

#不同字段按照不同的规则排序(默认使用正序排序(ASC))
SELECT 列1,列2 FROM 表名 ORDER BY 列1 DESC,列2

tip:

  1. sql语句如果不指定排序,则检索出来的语句的顺序无意义,是由底层表中数据的顺序决定的,一般为插入的顺序,但如果出现过删除和更新,则会收到MYSQL回收利用空间的影响.所以不指定排序时,应理解为无序.
  2. 一般数据库中对大小写字母不敏感,因此如果需要根据大小写排序,需要进行配置.
  3. 在和 LIMIT 一起使用时, LIMIT 需要放在最后

第六章 过滤数据

本章主要介绍了 WHERE 子句的筛选功能

WHERE 子句操作符:= 、 <> 、 != 、< 、<= 、 > 、 >= 、BETWEEN

# 基本语法
SELECT 列 FROM 表名 WHERE 字段 判断条件(操作符)  值

# BETWEEN
SELECT 列 FROM 表名 WHERE 字段 BETWEEN   值1 AND 值2

# 空值检查(NULL)
SELECT 列 FROM 表名 WHERE 字段 IS NULL

tip:

  1. WHERE 子句和 ORDER BY 子句使用时,ORDER BY子句需要写在WHERE 之后。
  2. 在使用筛选时,如果某一列的值含有部分为NULL的数据,则在筛选时,如果没有使用 IS NULL 需要注意,结果不包含和null相关的数据。

第七章 数据过滤

本章主要介绍了使用WHERE子句操作符实现更强大的数据过滤功能

  1. AND可以给WHERE子句附加条件

    SELECT 字段 FROM 表名 WHERE 条件1 AND 条件2 
  2. OR

    # 一般使用
    SELECT 字段 FROM 表名 WHERE 条件1 OR 条件2 
    #与AND一起使用时,最好加上括号
    SELECT 字段 FROM 表名 WHERE ( 条件1 OR 条件2 ) 
  3. IN

    SELECT 字段 FROM 表名 WHERE 字段 IN (值1,值2)

    使用IN 操作符的优点:

    • 在值较多的情况下,使用IN的语法更加清楚直观
    • 使用IN时,语句的运行次序更容易管理
    • 使用IN效率比多个OR的高
    • 可以包含SELECT语句
  4. NOT

    NOT可以对IN、BETWEEN、EXISTS条件进行否定

    SELECT 字段 FROM 表名 WHERE 字段 NOT IN (值1,值2)

tip:

  1. 在没有括号时,AND的优先级高级OR

第八章 使用通配符进行过滤

通配符:用来匹配值的一部分的字符串,需要结合LIKE操作符进行使用

   1. % 可以匹配0个、1个或者多个字符
   2. _  可以匹配一个字符

搜索模式:由字面值、通配符两者组合成的搜索条件

# 搜索条件可以为: 'a%' 以a开头的   '%a%' 含有a的 等
SELECT 字段 FROM 表名 WHERE 字段名 LIKE 搜素条件 

tip:

  1. 在使用通配符时,不会匹配值为NULL的列
  2. 其他操作符能达到目的时,不要使用通配符
  3. 通配符较其他操作符效率慢,尤其是将通配符放在搜素模式开始处时

第九章 用正则表达式进行搜索

在WHERE子句中使用正则表达式可以更好的控制数据进行过滤。Mysql仅支持一部分的正则表达式

#基本使用:
SELECT 字段 FROM 表名 WHERE 字段名 REGEXP '正则表达式'

# Mysql中REGEXP默认不区分大小写,如果想要区分需要使用 BINARY
SELECT 字段 FROM 表名 WHERE 字段名 REGEXP BINARY '正则表达式'

常见的正则表达式:

1. or   '值1|值2'
2. 匹配几个字符之一(与OR类似)  '[值1值2]'
3. 匹配范围(值可以为数字或字母)  '[起始值-结束值]'  
4. 匹配特殊字符,比如:. 、 [] 、 | 、 - 、元字符等    '\\特殊字符'      
5. 预定义字符集,匹配字符类   
6. 匹配多个实例

常见的元字符:

  1. \\f 换页
  2. \\n 换行
  3. \\r 回车
  4. \\t 制表
  5. \\v 纵向制表

匹配字符类:

image20200727182254595.png

image20200727182342511.png

image20200727182409212.png

在不使用正则表达式的情况下进行正则表达式匹配测试:

SELECT '测试的字符串' REGEXP  '正则表达式'

tip:

  1. 当匹配的只有一个值时,正则表达式通过定位符可以实现和LIKE类似的效果,LIKE通过%可以实现正则的效果
  2. 在匹配特殊字符时,两个斜杆一个代表SQL中的转义字符,一个代表正则表达式中的转义字符

## 第十章 创建计算字段

# 拼接字段
SELECT Concat(列1,需要拼接的字段,列2) AS 字段名 FROM 表名
# 计算字段
SELECT 列1 算术运算符 列2  AS 字段名 FROM 表名

AS 别名的作用:

1. 为没有名字的字段命名

   2. 为命名混乱或者不规范的列重命名

支持的算术运算符:+ - * /

tip:

  1. 字段与列的区别:字段通常描述计算的字段,而列通常指表中存在的列
  2. 计算字段的测试方式: SELSECT 1+2

第十一章 使用数据处理函数

  1. 文本处理函数

    # 函数可以用在结果或者条件中
    SELECT 列1,函数(列2) AS 别名  FROM 表名
    SELECT 列 FROM 表名  WHERE  函数(列) = 值

    常见的字符处理函数:

image20200821204702697.png

  1. 日期和时间处理函数

    在条件中直接使用 WHERE 列1 = 时间 时,需要时间完全匹配,使用日期和时间可以进行更多的匹配

    # 基础使用
    SELECT 列 FROM 表名  WHERE Date(列1) = 日期
    
    #查询某月的行信息
    #方法1:
    SELECT 列 FROM 表名  WHERE Date(列1) BETWEEN  '起始日期' AND '结束日期'
    #方法2:
    SELECT 列 FROM 表名  WHERE Yate(列1) = '年份' AND Month(月份) = '月份'

    image20200728190320508.png

  2. 数值处理函数

    image20200728190406791.png

tip:

在数据库中进行运算可以获得更快的速度,但也会给数据库带来相应的压力,因此需要综合考虑。

第十二章 汇总数据

image20200729165914684.png

常见的使用:

# 基本使用
SELECT 函数(列) AS 别名 FROM 表 
# 多个字段总和
SELECT SUM(列1+ 列2) AS 别名 FROM 表 
# 使用DISTINCT 聚合不同的值,不使用则默认我ALL
SELECT SUM(DISTINCT 列1 ) AS 别名 FROM 表 

tip:

  1. 在使用count(*) 时,会统计NULL的部分,如果conut(列)则不会统计值为NULL的行
  2. 使用DISTINCT 时,必须指定列名

第十三章 数据分组

GROUP BY

# 基本使用
SELECT COUNT(列) AS 别名 FROM 表  GROUP BY 列

# 使用 WITH ROLLUP 对分组进行汇总统计,会在返回时的最后一行,返回一个对各个分组数据汇总的数据
SELECT COUNT(列) AS 别名 FROM 表  GROUP BY 列 WITH ROLLUP

tip:

  1. GROUP BY后边可以跟多个列名,此时当两行的多个列都相同时才会分为一组
  2. GROUP BY后跟的列必须为检索列或者表达式(不能是聚集函数),如果SELECT 后使用的为表达式,那么GROUP BY 后边也应该跟表达式,不能使用别名。
  3. SELECT 后跟的列,除聚集函数外,其他的都应该在 GROUP BY 后给出
  4. 如果GROUP BY 的列存在值NULL的情况,则NULL将为一组
  5. GROUP BY 必须跟在WHERE子句之后,ORDER BY的子句之前

HAVING

HAVING是对分组进行筛选,支持WHERE后所有的条件。

#基本使用
SELECT Count(*) AS 别名 FROM 表  GROUP BY 列1 HAVING Count(*) > 1 

#与WHERE结合,会先进行WHERE筛选再进行GROUP BY
SELECT Count(*) AS 别名 FROM 表  WHERE 条件 GROUP BY 列1 HAVING Count(*) > 1 

tip:

  1. 不应依赖GROUP BY的排序,如果需要对字段进行排序,需要在最后边使用ORDER BY

SELECT 子句顺序

SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY ->LIMIT

第十四章 使用子查询

可以在SELECT 中嵌套其他的SELECT 语句作为子语句,即以SELECT的结果作为表或者条件

tip:

  1. 不应嵌套过多,会限制运行效率
  2. 在使用子查询时,需要注意列名的歧义

第十五章 联结表

  • 联结是一种机制,用来在一条SELECT 语句中关联表
  • 通过关系表,可以将信息拆解为多个表,一类信息一个表,有效的减少了值的重复,可以让数据更有效的存储和更方便的处理。拥有更好的伸缩性
#使用WHERE进行联结
SELECT 表1.列,表2.列  FROM 表1,表2  WHERE 表1.列1 = 表2.列1

# 内部联结
SELECT 表1.列,表2.列  FROM 表1  INNER JOIN 表2  ON 表1.列1 = 表2.列1

tip:

  1. 当对两个没有进行的联结的表查询时,会得到两个表返回的笛卡尔积(即:表1的行数* 表2的行数)
  2. 在where和联结都可以使用时,优先使用联结

第十六章 创建高级联结

AS 使用AS可以对表起别名

表别名的好处:

  1. 缩短SQL语句,让SQL的使用更加简洁
  2. 对一个表进行多次操作更便捷
  3. 自联结

    自联结只返回两张表中满足条件的结果

    SELECT 列 FROM 表1 INNER JOIN 表2 ON 表1.列1 = 表2.列2
  4. 自然联结

    本书中此处内容未进行明确的讲解
  5. 外联结

    通过外联结可以将一个表中存在而另外一个表不存在数据查询出来

    LEFT JOIN 与 RIGHT JOIN可以通过调换 FROM 或WHERE表中的位置达到相同的效果

    SELECT 列 FROM 表1 LEFT JOIN 表2 ON 表1.列1 = 表2.列2

tip:

  1. 合理使用联结的效率比使用SELECT嵌套的效率要高
  2. 在多个表进行联结,为保证联结的结果,可以一个个写,测试好一个再写下一个联结

第十七章 组合查询

UNION 操作符可以将多条SELECT语句的结果组合成一个结果集,也称为并查询、复合查询

使用需要注意:

  1. 必须由2条以上的SELECT语句组成
  2. 各个SELECT返回的数据需要包含相同的列、表达式、聚集函数
  3. 各列的数据类型必须兼容(可以隐式转换)
SELECT 列1,列2 
FROM 表1 
WHERE 条件1
UNION
SELECT 列1,列2 
FROM 表2 
WHERE 条件2

tip:

  1. WHERE也可以完成UNION的操作,但对于复杂的过滤条件使用UNION处理可能会更简单
  2. 使用UNION时,默认会去除重复的行,如果需要保留,则应该使用 UNION ALL操作符

第十八章 全文本搜索

通过LIKE 关键字和正则表达式,可以达到与全文搜索类似的功能,但使用全文搜索可以更简单、快捷

、灵活的实现更多功能。MyISAM支持全文索引,InnoDB不支持。

创建索引

在使用全局搜索时,需要索引的支持,使用FULLTEXT可以使MySQL自动维护该索引

#创建带FULLTEXT索引的表
CREATE TABLE 表名
(
    content text  NULL,
    PRIMARY KEY (id),
    FULLTEXT(content)
) ENGINE = MyISAM;

Match()、Against() 的使用

match()指定要搜索的字段,指定的列必须与FULLTEXT()中的值相同;Against()指定要使用的搜索条件,在默认模式下,不区分大小写。

#相比于LIKE,全文搜索的返回的结果会根据搜索结果的匹配程度进行排序
SELECT 列 FROM 表 WHERE Match('列') Against('搜索条件') 

WITH QUERY EXPANSION 查询扩展

使用查询扩展可以获取更多和含关键词搜索结果相关的结果,但也增加了无用的数据

SELECT 列 FROM 表 WHERE Match('列') Against('搜索条件' WITH QUERY EXPANSION) 

IN BOOLEAN MODE 布尔文件搜索

使用boolean mode 可以更加灵活,可以不为其定义全文搜索索引,但速度会略慢

SELECT 列 FROM 表 WHERE Match('列') Against('搜索条件' IN BOOLEAN MODE)

image20200802162123370.png

操作符使用方式:

# 同时存在值1、值2的列
SELECT 列 FROM 表 WHERE Match('列') Against('+值1 +值2' IN BOOLEAN MODE)

#至少包含一个值的列
SELECT 列 FROM 表 WHERE Match('列') Against('值1 值2' IN BOOLEAN MODE)

#含有"值1 值2"这个字符串的列
SELECT 列 FROM 表 WHERE Match('列') Against('"值1 值2"' IN BOOLEAN MODE)

#增加或降低等级
SELECT 列 FROM 表 WHERE Match('列') Against('>值1  <值2' IN BOOLEAN MODE)

#含有值1、 值2,且降低值2的等级
SELECT 列 FROM 表 WHERE Match('列') Against('+值1 +(<值2)' IN BOOLEAN MODE)

在布尔搜索中不以等级进行排序

tip:

  1. 在导入带有FULLTEXT索引的表时,先将数据全部导进去,再添加索引的效率会更高
  2. 对索引词的忽略:

    • 3个或3个以下字符的词会被忽略
    • mysql中有一个非用词(stopword)表,此表中的词会被忽略
    • 在搜索中一个词如果出现在表里行中的次数超过50%,则会将其作为一个忽略词,因此如果表中的数据少于3行,则全文搜索不会返回结果(如果是在boolean mode中不会忽略)
    • 忽略单词的单引号
  3. 对于不具有词分隔符的语言(汉语、日语),不能恰当的返回结果

第十九章 插入语句

使用INSERT语句 可以插入:1. 完整的行 2. 行的一部分 3. 多行 4. SELECT的查询结果

# 1.只用值进行插入,需要对应表中的字段
INSERT INTO 表名 VALUES(NULL,值1,值2,值3)

# 2.使用列名和值插入(更安全),只要值和列名对应即可
INSERT INTO 表名(列1,列2,列3) VALUES(值1,值2,值3)

# 3.插入多条
INSERT INTO 表名(列1,列2,列3) VALUES(值1,值2,值3),VALUES(值1,值2,值3)

# 4.插入查询结果,需要注意两个表中的id 不能重复
INSERT INTO 表名(列1,列2,列3) SELECT 列1,列2,列3 FROM 表名2

在进行插入时,id输入为NULL,系统会自动进行自增。

插入时,如果想省略部分字段需要满足以下一个条件;

  1. 允许NULL值
  2. 表中对该字段设置了默认值

tip:

  1. 在插入内容较多,比较耗时的情况下,可以使用 INSERT LOW_PRIORITY INTO 代替 INSERT INTO 降低语句的优先级
  2. 在插入查询结果时,根据的是查询结果的值顺序插入,而不是列名或别名

第二十章 更新和删除数据

# 更新
UPDATE 表名 SET '列' = 值 WHERE 条件

# 删除
DELETE 表名 WHERE 列 = 值

tip:

  1. 在更新数据时,如果出现错误,那么整个语句的执行都会被撤销,如果想出错也继续,可以使用UPDATE IGNORE 代替 UPDATE
  2. 如果想删除整个表的数据,那么使用TRUNCATE 代替 DELETE速度会更快(DELETE是逐行删除,TRUNCATE是删除整个表,再把表建起来)
  3. 使用更新或删除时,建议先用select测试语句的条件

第二十一章 创建和操纵表

创建新表时,表名必须不存在,否则将会出错。在表名后使用 IF NOT EXISTS 判断表不存在再执行会更安全

# 基础使用
CREATE TABLE 表名 
(
    列名  数据类型  是否可为NULL 是否自增
    列名2 ...
    PRIMARY KEY(列名)
) ENGINE = InnoDB

# 不可为NULL,且指定默认值
列名 数据类型 NOT NULL DEFAULT 1

# 获取最后一次插入的id自增值
SELECT last_insert_id()

引擎类型

在创建表时,默认使用MyISAM引擎。常用的引擎包括:

  1. InnoDB,支持事务,不支持全文检索
  2. MyISAM,支持全文检索,不支持事务
  3. MEMORY 等同于MyISAM,数据存在内存中

更新表

# 添加列
ALTER     TABLE 表名 ADD 要增加的列名 类型 

# 删除列
ALTER     TABLE 表名 DROP 要增加的列名 类型 

# 增加外键

其他操作

# 删除表
DROP TABLE 表名

# 重命名表
RENAME TABLE 原名 TO 新名

tip:

  1. 在使用外键时需要注意,外键不能跨引擎
  2. 在使用对表的操作前,最好做个备份

第二十二章 使用视图

视图是虚拟的表,使用视图有以下优点:

  • 重用SQL语句,
  • 简化SQL语句,在使用时不需要知道基本的细节
  • 保护数据,可以只让用户通过视图使用表的一部分
  • 更改数据格式和表示,视图可返回与表中存放格式不一样的数据

视图的使用和限制:

  • 视图的名字和表名不能冲突
  • 视图可以嵌套,可以与表一起用在SELECT 语句中
  • 如果SELECT 语句中使用了ORDER BY 子句,那么视图中的ORDER BY 将失效
  • 需要有创建视图的权限
# 创建视图
CREATE VIEW 视图名 AS SQL语句(例:SELECT 表1.列1,表2.列1 FROM 表1,表2 WHERE 条件)

# 查看创建视图的语句
SHOW CREATE VIEW 视图名称

# 使用视图,和其他表一样
SELECT *  FROM  视图名  

# 更新视图
CREATE OR REPLACE VIEW 视图名 AS SQL语句(例:SELECT 表1.列1,表2.列1 FROM 表1,表2 WHERE 条件)

# 删除视图
DROP VIEW 视图名

视图可以被更新,但一般不用于更新。在更新的时候,视图的更新相当于对表的操作,但是在一些元素无法被准确确定时,视图是不能被更新的,比如使用了:GROUP BY、联结、子查询、并、聚合函数、DISTINCE、计算字段。

tip:

  1. 在使用视图时,每次都会对相关的表进行检索,因此在含有视图嵌套和复杂的筛选时,会导致性能下降。

第二十三章 使用存储过程

存储过程是一条或者多条MySQL语句的集合。

使用存储过程的好处:

  • 将复杂的操作封装,简化操作,也防止了在步骤过多时,代码书写错误的出现
  • 简化了对变动的管理,如果表名、列名、业务逻辑发生了变化,那么只需要改动存储过程即可
  • 性能与执行多条语句相比更高
# 创建存储过程
CREATE PROCEDURE 过程名称(
    OUT 输出的值1 数据类型,
    IN 传入的值2 数据类型,
) COMMENT '存储过程示例'
BEGIN 
    Declare 定义的字段1 数据类型
    
    SELECT  字段  
    INTO 定义的字段1
    FROM 表1;
    
    IF 传入值2 THEN
        SELECT  定义的字段1*2 INTO 定义的字段1;
    END IF;
    
    SELECT 定义的字段1 INTO 输出的值1
    
END;

# 使用存储过程(有的存储过程调用需要传参数)
CALL 过程名称(@变量)
# 如果存储过程的返回就是表,则不需要selsect
SELECT @变量

# 删除存储过程
DROP PROCEDURE 存储过程名称
# 如果存在则删除
DROP PROCEDURE IF EXISTS 存储过程名称

# 检查存储过程
SHOW CREATE PROCEDURE  -- 存储过程名称,可以使用like进行过滤
  1. 传入与接受参数

    可以使用 IN (传递给存储过程) 、 OUT(从存储过程返回) 、INOUT(传入且传出)

  2. mysql 命令行的分隔符

    命令行中分隔符默认为分号(;),如果存储过程需要解释自身内的分号字符,则需要临时更改命令行的分隔符。

    DELIMITER //
    -- 存储过程代码 -- 
    DELIMITER ;
    
  1. 记录集不是被mysql允许的类型,因此返回的参数有多个时,需要定义多个
  2. 存储过程也可以不返回数据,返回数据表,用来简化sql(不推荐)

第二十四章 使用游标

游标是一个被sql语句检索出来的结果集,游标只能用出存储过程中,可以用来一条条的处理数据.

# 在存储过程中使用游标
CREATE PROCEDURE 存储过程名称()
BEGIN 
    DECLARE done BOOLEAN DEFAULT 0
    DECLARE o INT
    
    # 定义游标,此时并不检索数据
    DECLARE 游标名称 CURSOR
    FOR
    SELECT 列 FROM 表;
    
    # 声明一个条件(SQLSTATE '02000' )被触发时执行的代码
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1
    
    # 打开游标
    OPEN 游标名称
    
    # 通过游标遍历数据
    REPEAT 
    
        FETCH 游标名称 INTO o
    # 当触发 sqlstate '02000' 的条件时,停止循环
    UNTIL done END REPEAT
    #
    #关闭游标,如果不主动关闭,则在存储过程的END中会自动关闭
    CLOSE 游标名称
    
END;

tip:

  1. 在使用游标的时候,declare定义的局部变量需要在游标之前,句柄需要在游标之后,否则会报错

第二十五章 使用触发器

在MySQL执行CREATE、UPDATE、DELETE时,可以使用触发器,执行一些语句。触发时间报错BEGIN、END两种。

# 创建触发器,在执行的动作中可以返回数据,也可以执行其他语句
CREATE TRIGGER 触发器名 AFTER 时间( BEFORE 或者 AFTER) ON  操作名( CREATE 、 UPDATE 、 DELETE)  表名
FOR EACH ROW 执行的动作

# 删除触发器
DROP TRIGGER 触发器名

在使用INSERT触发器时,可以引用一个名为NEW的虚拟表,访问被插入的行,一般在插入前进行数据的格式化或检查

在使用UPDATE触发器时,可以引用名为NEW和OLD的虚拟表,可以更新NEW中的值,但是OLD中的值只能读

在使用DELETE触发器时,可以引用一个名为OLD的虚拟表,一般在删除前进行数据的备份

tip:

  1. 触发器每次可以触发一个,根据触发条件,在每个表中都可以定义6个触发器。
  2. 每个表中的触发器不可以重名,一个数据库中可以有多个触发器,但不建议一个数据库中有重名的触发器。
  3. 只有表才支持触发器,表和视图不支持。
  4. 如果BEFORE触发器执行失败,则不再执行SQL语句,如果SQL语句失败则,不执行AFTER触发器。
  5. 触发器中不能使用存储过程

第二十六章 管理事务处理

通过事务处理可以用来维护数据库的完整性,保证多条SQL语句要么全部执行,要么不执行.MyISAM引擎不支持事务的处理.

只对INSERT、UPDATE、DELETE 语句生效,可以手动控制回滚,也可以使用COMMIT在失败时,自动回滚。

# 回滚方式1:
START TRANSACTION;
语句一;
语句二;
# 回滚
ROLLBACK;

# 回滚方式2:
START TRANSACTION;
语句一;
语句二;
# 可以设置多个保留点
SAVEPOINT 保留点名称;
# 回滚
ROLLBACK TO 保留点名称;

# 成功提交,失败回滚
START TRANSACTION;
语句一;
语句二;
# 提交
COMMIT ;

tip:

  1. 在数据库中除事务外,默认是自动提交所有修改的,如果想进行手动提交,可以使用:

    SET autocommit = 0  # 会改变当前会话的提交方式,非全局
  2. 回滚或提交后,事务即结束

第二十七章 全球化和本地化

在处理不同的字符时,如果采用不同的字符集和校对方式,输出的结果可能不同。因此,可以根据自己的需求进行调整。

字符集:字母和符号的集合

编码:某个字符或符号在字符集内的表示方式

校对:规定字符如何比较,比如大小写,不同的字符

# 查看数据库支持的字符集
SHOW CHARACTER SET

# 查看数据库支持的校对方式
SHOW COLLATION

# 创建表时,指定字符集和校对方式
CREATE TABLE 表名
(
    列1 数据类型,
    列2 数据类型   
) DEFAULT CHARACTER SET 字符集
COLLATE 校对方式


# 查询时,临时指定校对方式(适用于GROUP BY 、 HAVING 、聚集函数 )
SELECT * FROM 表名 ORDER BY 字段 COLLATE 校对方式

tip:

  1. 如果在创建时,不指定字符集和校对方式,则会使用数据库默认的。
  2. 如果在创建时,只指定字符集则使用字符集默认的校对方式
  3. 如果需要,可以使用Cast() 、 Convert() 对串在字符集之间进行转换

第二十八章 安全管理

MySQL安全的基础时:用户对他们需要的数据具有适当的访问权限,不多也不少。

# 查看mysql的用户
USE mysql;
SELECT user FROM user;

# 创建用户账号,使用INENTIFIED 可以使存储在用户表中的密码不为明文
CREATE USER 用户名  IDENTIFIED BY '用户密码' 

# 设置访问权限
GRANT 权限1,权限2 FOR  用户名

# 查看用户的访问权限
SHOW GRANTS FOR BFORTA

# 重命名
RENAME USER 用户名  TO  新用户名

# 修改密码
# 为其他用户 
SET PASSWORD FOR 用户名 = Password('密码')
# 为自己
SET PASSWORD = Password('密码')

#删除用户名
DROP USER  用户名

22.jpg

tip:

  1. 控制用户的权限可以防止用户无意的操作错误。
  2. 日常中尽量不要使用root 用户操作。
  3. 不能给未创建的用户授权,但可以为用户对未创建的表或数据库授权。

第二十九章 数据库维护(不详细)

  1. 备份数据

    MySQL数据库是基于磁盘的文件,因此普通的文件备份也可以用来备份数据库,但是由于这些文件经常处于被使用状态,因此普通备份有时候无法正常运行。

    可使用命令:

    • mysqldump
    • mysqlcopy
    • backuptable table 、select into outfile
  2. 数据库维护

    • ANNLYZE TABLE
    • CHECK TABLE
  3. 启动问题

    在修改服务器或MySQL配置时,可能会发生启动错误。

    • 可以使用手动启动的方式,查看错误的信息
  4. 查看日志

    日志都在data 目录中,可以通过log相关的命令调整位置

    • 错误日志,hostname.err
    • 查询日志,hostname.log 记录所有mysql活动
    • 二进制文件,hostname-bin ,记录更新过数据的所有记录
    • 缓慢查询日志,hostname-slow

第三十章 改善性能

  1. 一条SELECT 语句可以尝试不同的写法,比如联结,并,子查询
  2. 只返回需要的数据
  3. 使用正确的数据类型
  4. 部分情况,可以使用DELAYED关键字
  5. 如果一个简单的WHERE子句查询时间过长,则能其中的几个列需要加为索引
  6. 可以通过多条SELECT 和 连接它们的UNION,提高复杂OR条件的性能。
  7. 索引能改善数据检索的性能,当时会降低插入、删除和更新的性能
# 查看当前MySQL设置
SHOW VARIABLES
SHOW STATUS

# 多进程时,查看所有活动进行的信息
SHOW PROCESSLIST

# 查看一条语句的执行
EXPLAIN SQL语句
tip:

所有的优化在某种条件下都可能失效,因此需与具体环境结合

我的名片

昵称:shuta

职业:后台开发(php)

邮箱:648949076@qq.com

站点信息

建站时间: 2020/2/19
网站程序: ANTD PRO VUE + TP6.0
晋ICP备18007778号