MySQL常用命令

发表于 | 更新于: | 分类于 | 本文总阅读量 次
字数统计: 15.9k | 阅读时长 ≈ 60
MySQL一些通用命令

图片还没有哦

Mysql

需要下载的工具
MySQL 本地服务器
MySQL Administrator （MySQL管理器）
MySQL Query Browser （为一个图形交互客户机）

使用完全限定的表名

SQL也可能会使用完全限定的名字来引用列（同时使用表名和列字）。请看以下例子：

1
SELECT products.prod_nam FROM products;

这条SQL语句在功能上等于本章最开始使用的那一条语句，但这里指输出定了一个完全限定的列名。

表名也可以是完全限定的，如下所示：

1
SELECT products.prod_name FROM crashcourse.products;

这条语句在功能上也等于刚使用的那条语句（当然，假定products表确实位于crashcourse数据库中）。

dataname 数据库的名字
tablename 表示表的名字
columns 列名

显示查看

命令 作用
USE dataname; 选择数据库
SHOW DATABASES; 返回可用数据库的一个列表。包含在这个列 表中的可能是MySQL内部使用的数据库
SHOW TABLES; 获得一个数据库内的表的列表
SHOW COLUMNS FROM tablename; 用来显示表列1
DESCRIBE tablename; 用来显示表列2
SHOW CREATE dataname; 显示创建特定数据库
SHOW CREATE tablename; 显示创建表的MySQL语句
SHOW STATUS; 用于显示广泛的服务器状态信息
SHOW GRANTS; 用来显示授予用户（所有用户或特定用户）的安全权限
SHOW ERRORS; 用来显示服务器错误
SHOW WARNINGS; 用来显示服务器警告消息

查找

命令 作用
SELECT * FROM tablename; 查找表中所有数据
SELECT columns FROM tablename; 单列数据查询
SELECT columns1, columns2 FROM tablename; 多列数据查询 可搭配as
SELECT DISTINCT columns FROM tablename; DISTINCT去重

限制

命令 作用
SELECT columns FROM tablename LIMIT 5; 返回结果不超过5条
SELECT columns FROM tablename LIMIT 5,5; 限制结果 (LIMIT 5,5;第一个数为开始位置，第二个数为要检索的行数) 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 关于MYSQL的LIMIT使用变量

-- 注意：

-- LIMIT ?表示求的结果集的点几行

-- LIMIT ?,? 表示结果集从第几行到第几行

-- 初识MySql不知道limit后面怎么可以用变量动态的显示需要的记录，后来才发现可以这样做

DELIMITER $$

DROP PROCEDURE IF EXISTS `p_span_get_Babao_Furnace_rank`$$

CREATE PROCEDURE `p_span_get_Babao_Furnace_rank`(_span_serverid INT, _limit INT)
    COMMENT '得到八宝炉副本排行数据的存储过程'
_get_rank:BEGIN -- 给个标签
	-- SELECT * FROM t_span_rank_babao_furnace WHERE spanserverid = _span_serverid and score != 0 ORDER BY score DESC LIMIT _limit; --  _limit 这里 不能使用变量

    PREPARE s1 FROM 'SELECT * FROM t_span_rank_babao_furnace WHERE spanserverid = ? and score != 0 ORDER BY score DESC LIMIT ?'; -- _limit 不能用变量 使用 PREPARE 预处理 用下面这个处理就行 预加载一下
    set @a=_span_serverid;
    set @b=_limit;
    EXECUTE s1 USING @a,@b; -- 赋予参数值
    DEALLOCATE PREPARE s1; -- 销毁预处理
    END _get_rank$$

DELIMITER ;

-- 注意：需要传参数的地方一定要用"?"号，第一个FRom后面的语句要用''括起。

排序

命令 作用
SELECT columns FROM products ORDER BY columns; ORDER BY 按照指定列名进行排序 单指定 升序
SELECT columns FROM products ORDER BY columns1, columns2; ORDER BY 按照指定列名进行排序 多指定 (用逗号隔开即可) 当columns1相同的时候才会选择columns2排序 升序
SELECT columns FROM products ORDER BY columns DESC; DESC 按照指定列名进行排序 单指定 (DESC降序) (ASC 升序 默认的 指不指定都没有影响)
SELECT columns FROM products ORDER BY columns1 DESC, columns2; 按columns1降序排序 columns2 升序排序(条件列后面加了DESC的就为降序排序)
SELECT columns FROM products ORDER BY columns LIMIT 1; 取出一个最小值 排序配合限制结果 可查找出最大值和最小值
SELECT columns FROM products ORDER BY columns DESC LIMIT 1; 取出一个最大值

ORDER BY子句的位置 在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息
在查数据的时候 如果在最后考虑排序和限定结果 就可以大大避免语句顺序混乱的情况了

条件判断-筛选

命令 作用
SELECT columns FROM tablename WHRER columns = xxx; WHRER 指定条件(应该位于FROM之后)返回columns = xxx的选项
SELECT columns FROM tablename WHRER columns BETWEEN xxx AND xxx; 返回范围内的值包括起始和结束位置
SELECT columns FROM tablename WHRER IS NULL; IS NULL 返回列中有NULL值的选项 配合if使用 就是 是否为空 if n_sid is null then end if; 如果 n_sid 为空 就进入循环
SELECT columns FROM tablename WHRER IS NOT NULL; IS NOT NULL 返回列中不为NULL值的选项 配合if使用 就跟 not 一样的效果 if n_sid is not null then end if; 如果 n_sid 不为空 就进入循环
SELECT columns FROM tablename WHRER IN(xxx,xxx); IN 返回xxx和xxx对应的值 可以代替OR(1 or 2 = in(1,2))
SELECT columns FROM tablename WHRER NOT IN(xxx,xxx); NOT(否定判断) 返回xxx和xxx以外所有值
SELECT columns FROM tablename WHRER EXISTS(select columnsEx from tablenameEx …); EXISTS 用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

EXISTS在两个表中查找是否有一样的值 返回一张虚表
SQL中EXISTS的用法

WHERE子句操作符

操作符 作用
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在指定两个值之间

BETWEEN的使用 位于xxx和xxx两值之间的选项 BETWEEN匹配范围中所有的值，包括指定的开始值和结束值
WHERE子句的位置 在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误

通配符过滤

命令 作用
SELECT columns FROM tablename WHERE name LIKE ‘%xxx’ % 匹配任意多个字符 只要后面有xxx内容的行就会被返回 如果后面有空格 将匹配不了 最好的方法 就是在后面也加一个%
SELECT columns FROM tablename WHERE name LIKE ‘xxx%’ % 匹配任意多个字符 只要前面有xxx内容的行就会被返回
SELECT columns FROM tablename WHERE name LIKE ‘%xxx%’ % 匹配任意多个字符 只要中间有xxx内容的行就会被返回
SELECT columns FROM tablename WHERE name LIKE ‘_xxx’ _ 匹配任意单个字符
SELECT columns FROM tablename WHERE name LIKE ‘xxx_’ _ 匹配任意单个字符
SELECT columns FROM tablename WHERE name LIKE ‘xxx‘ _ 匹配任意单个字符

LIKE不能匹配NULL
LIKE ‘%xxx’ 后面如果有空格 就会造成失误 最好的方法就是%xxx%

正则表达式

命令 作用
SELECT columns FROM tablename WHERE name REGEXP ‘xxx’ REGEXP xxx输入正则表达式即可
SELECT columns FROM tablename WHERE name REGEXP BINARY xxx BINARY 区分大写写可用此关键字
SELECT columns FROM tablename WHERE name REGEXP ‘xxx yyy’ xxx和yyy都匹配 相当于OR
SELECT columns FROM tablename WHERE name REGEXP ‘[123] xxx’ 范围匹配(会匹配1 xxx 2xxx 3xxx)
SELECT columns FROM tablename WHERE name REGEXP ‘[^1]’ ^ 匹配特定字符1 以外的任何东西 用在[]中才有否定的作用
SELECT columns FROM tablename WHERE name REGEXP ‘[1-5]’ 匹配1到5 相当于 [1 2 3 4 5] [a-z] 匹配a到z
SELECT columns FROM tablename WHERE name REGEXP ‘.’ 匹配任意字符
SELECT columns FROM tablename WHERE name REGEXP ‘\.’ 匹配带有’.’的字符 \转义 .本来是有特殊含义的 用了\之后就变成了普通的字符’.’

SELECT xxx REGEXP ‘yyy’; 可以在不依赖数据库表的情况来测试正则表达式 如果能匹配会返回1 如果不能则返回0
SELECT ‘hello’ REGEXP ‘1’; 返回0
SELECT ‘hello’ REGEXP ‘h’; 返回1

为更方便工作，可以使用预定义的字符集类

类 说 明
[:alnum:] 任意字母和数字（同[a-zA-Z0-9]）
[:alpha:] 任意字符（同[a-zA-Z]）
[:blank:] 空格和制表（同[\t]）
[:cntrl:] ASCII控制字符（ASCII 0到31和127）
[:digit:] 任意数字（同[0-9]）
[:graph:] 与[:print:]相同，但不包括空格
[:lower:] 任意小写字母（同[a-z]）
[:print:] 任意可打印字符
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符（同[\f\n\r\t\v]）
[:upper:] 任意大写字母（同[A-Z]）
[:xdigit:] 任意十六进制数字（同[a-fA-F0-9]）

拼接字符串

命令 作用
SELECT Concat(xxx, ‘ (‘,xxx,’)’ ) FROM tablename ORDER BY name; Concat字符串连接函数 一般sql都用 + 和 双竖 来实现拼接 MySQL中用的是Concat要注意
SELECT Concat(RTrim(xxx), ‘ (‘,RTrim(xxx),’)’ ) FROM tablename ORDER BY name; RTrim()函数去掉值右边的所有空格。通过使用RTrim()，各个列都进行了整理。
SELECT Concat(xxx, ‘ (‘,xxx,’)’ ) AS title_name FROM tablename ORDER BY name; 连接之后返回的字符串是没有名字的 客户端没有名字的列是不能使用的 这里使用AS关键是别名 即可解决此问题

RTrim 去掉右边所有空格 LTrim() 去掉值右边的所有空格 Trim()去掉左右两边的空格。

计算

命令 作用
SELECT aaa, bbb, aaa * bbb AS num FROM tablename; aaa bbb 命名别名为 num 表示出来(num就是aaabbb的结果)

计算支持 + - /
SELECT 可用于测试 比如：SELECT 56; 返回30 SELECT Now() 返回当前时间 SELECT Trim(‘ aaa ‘) 返回aaa

函数

文本处理函数命令 作用
SELECT columns, Upper(columns) AS xxx FROM tablename ORDER BY xxx; Upper函数将结果转为大写
SELECT columns FROM tablename WHERE Soundex(columns) = Soundex(‘读音’) 这里他会返回读音相似的结果值 如果有时候一个串输入错误 例如 (‘Sam’) 和 (‘San’) 这里把n写成了m 就可以用这个函数处理 找出对应的结果

常用的文本处理函数

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

常用日期和时间处理函数

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

基本的日期格式应yyyy-mm-dd

常用数值处理函数

函 数 说 明
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切

SQL聚集函数

函 数 说 明
AVG() 返回某列的平均值 忽略NULL 可搭配DISTINCT使用 AVG(DISTINCT xxx)
COUNT() 返回某列的行数 如果COUNT(xxx) 忽略NULL的行 如果是COUNT(*)则不忽略
MAX() 返回某列的最大值 忽略NULL
MIN() 返回某列的最小值 忽略NULL
SUM() 返回某列值之和 忽略NULL SUM(DISTINCT xxx)

组合-分组

命令 作用
SELECT columns, COUNT(*) AS num_prods FROM tablename GROUP BY columns columns按这个列分组 把每一组的总行数给返回出来(num_prods 每一组的总行数)
SELECT columns, COUNT(*) AS num_prods FROM tablename GROUP BY columns WITH ROLLUP WITH ROLLUP：在group分组字段的基础上再进行统计数据
SELECT columns, COUNT() FROM tablename GROUP BY columns HAVING COUNT() >= 2; HAVING(过滤分组)支持所有WHERE操作符 进行分组之后 这里只显示返回行数大于等于2的分组
SELECT columns, COUNT() FROM tablename WHERE columns2 = xxx GROUP BY columns HAVING COUNT() >= 2; 也可以和WHERE 搭配使用

SELECT子句及其顺序

子 句 说 明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否

使用子查询

命令 说 明
SELECT columns FROM tablename WHERE columns IN (SELECT columns2 FROM tablename2 WHERE columns2 = ‘TNT2’) 把()内的查询结果当做第一条的查询限制(子查询) 如果不这样做就只有分两步查询

子句可嵌套使用(但是如果比较复杂的查询虞姬阅读性较差
使用嵌套虽然没有限制 但是在实际使用中会有性能限制 还是少使用为好
使用的时候子查询返回的结果一定要与外部语句查询相同数目的列)
任何时候只要列名可能有多义性 就要使用完全限定列明

联结表

命令 作用
SELECT columns1, columns2 FROM tablename1, tablename2 WHERE tablename1.xxx = tablename2.xxx; 连结表 利用表一的键(key)值作为表2的外键(foreign key) 来进行匹配查找结果 tablename表不限量
SELECT columns1, columns2 FROM tablename1 INNER JOIN tablename2 ON tablename1.zzz = tablename2.zzz; INNER JOIN xxx ON yyy 在xxx和yyy两个表中查找zzz的值是否一样 如果一样就返回 和上一条命令是一样的

联结是不限表个数的(可使用多连接查询) 但连结越多性能消耗就越大
SQL语句多表inner join用法

高级联结表

命令 作用
SELECT columns1, columns2 FROM tablename1 AS t1, tablename2 AS t2 WHERE t1.xxx = t2.xxx; 这里使用了 AS 简短了sql语句
SELECT t1.*, columns2 FROM tablename1 AS t1, tablename2 AS t2 WHERE t1.xxx = t2.xxx; 这个语句只有t1使用了通配符 所有其他列明确列出，所以没有重复的列被检索出来
SELECT tablename1.xxx,tablename2.xxx FROM tablename1 LEFT OUTER JOIN tablename2 ON tablename1.xxx = tablename2.xxx; OUTER JOIN外连接 有LEFT(检查左边表所有的值)、RIGHT(检查左边表所有的值)、FULL(全外连接 左右都有)

表别名不仅能用于WHERE子句，它还可以用于SELECT的列表、ORDER BY子句以及语句的其他部分。应该注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机
DB2数据库的外连接(OUTER JOIN)，内连接(INNER JOIN)和交叉连接(CROSS JOIN)区别

组合查询

命令 作用
SELECT columns1 FROM tablename1 WHERE columns1 = xxx UNION SELECT columns2 FROM tablename2 WHERE columns2 = xxx … UNION组合查询 可把两条SELECT的结果返回 UNION 默认去除两条SELECT查询到的重复结果 如果需要返回全部 用UNION ALL(返回所有匹配行 包括重复选项)即可
SELECT columns1 FROM tablename1 WHERE columns1 = xxx UNION SELECT columns2 FROM tablename2 WHERE columns2 = xxx ORDER BY zzz 组合查询加排序 这个排序只能使用在最后的一条SELECT语句(对组合查询的结果进行排序的)

UNION规则
UNION可组合不同的表但是每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
如果遵守了这些基本规则或限制，则可以将并用于任何数据检索任务。

全文本搜索

并非所有引擎都支持全文本搜索 MySQL支持几种基本的数据库引擎。两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。如果你的应用中需
要全文本搜索功能，应该记住这一点。
MyISAM (不支持事务 搜索快) InnoDB(支持事务 搜索相对MyISAM较慢)
MyISAM与InnoDB两者之间区别与选择，详细总结，性能对比

一、启用全文本搜索支持:在创建表的时候加上(也可以在以后的表 更新表 加上FULLTEXT即可) FULLTEXT(xxx,yyy,...) 对一个列进行索引(这个列就是那个文本) 可使用多个索引。
在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

不要在导入数据时使用FULLTEXT 更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）

二、在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式

全局搜索

命令 作用
SELECT columns FROM tablename1 WHERE Match(columns) Against(‘…’); 使用条件全局搜索 返回行等级不为0的选项(结果排序按行等级来排序) 行等级是由表达式在文本中的先后顺序以及匹配程度来决定的 越前的行等级越高
SELECT columns Match(columns) Against(‘…’) AS rank FROM tablename1 使用全局搜索 返回匹配所有的行 行等级为0也会匹配(可以清晰地看出行等级)

查询扩展

在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：

  • 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
  • 其次，MySQL检查这些匹配行并选择所有有用的词。
  • 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
  • 利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词
命令 作用
SELECT columns FROM tablename WHERE Match(columns) Against(‘…’ WITH QUERY EXPANSION); WITH QUERY EXPANSION 查询扩展 返回只要包含了表达式的结果 优限级按匹配的数量

使用查询扩展的时候行越多越好 表中的行越多（这些行中的文本就越多），使用查询扩展返回的结果越好。

布尔文本搜索

以布尔方式，可以提供关于如下内容的细节：

  • 要匹配的词；
  • 要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
  • 排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
  • 表达式分组；
  • 另外一些内容。
  • 即使没有FULLTEXT索引也可以使用 布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）
命令 作用
SELECT columns FROM tablename WHERE Match(columns) Against(‘…’ IN BOOLEAN MODE); IN BOOLEAN MODE 布尔文本搜索 排列而不排序行(在布尔方式中，不按等级值降序排序返回的)
SELECT columns FROM tablename WHERE Match(columns) Against(‘… -xxx*’ IN BOOLEAN MODE); 排查带有xxx的 (-)排除 (* 截断)词尾通配符
全文本布尔操作符
布尔操作符 说 明
+ 包含，词必须存在
— 排除，词必须不出现
> 包含，而且增加等级值
< 包含，且减少等级值
() 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~ 取消一个词的排序值
* 词尾的通配符
“” 定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）
使用例子
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT columns FROM tablename WHERE Match(columns) Against('+aaa +bbb' IN BOOLEAN MODE);
-- 这个搜索匹配包含词aaa和bbb的行

SELECT columns FROM tablename WHERE Match(columns) Against('aaa bbb' IN BOOLEAN MODE);
-- 没有指定操作符，这个搜索匹配包含aaa和bbb中的至少一个词的行

SELECT columns FROM tablename WHERE Match(columns) Against('"aaa bbb"' IN BOOLEAN MODE);
-- 这个搜索匹配短语aaa bbb而不是匹配两个词aaa和bbb

SELECT columns FROM tablename WHERE Match(columns) Against('>aaa <bbb' IN BOOLEAN MODE);
--匹配aaa和bbb，增加前者的等级，降低后者的等级

SELECT columns FROM tablename WHERE Match(columns) Against('+aaa +(<bbb)' IN BOOLEAN MODE);
--这个搜索匹配词aaa和bbb，降低后者的等级
全文本搜索的某些重要的说明
  • 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为
    那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
  • MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
  • 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
  • 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
  • 忽略词中的单引号。例如，don’t索引为dont。
  • 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
  • 如前所述，仅在MyISAM数据库引擎中支持全文本搜索。

  • 没有邻近操作符 邻近搜索是许多全文本搜索支持的一个特性，它能搜索相邻的词（在相同的句子中、相同的段落中或者在特定数目的词的部分中，等等）。MySQL全文本搜索现在还不支持邻近操作符，不过未来的版本有支持这种操作符的计划

  • 布尔文本搜索中的 > 升级 和 < 降级有什么用

插入数据

| 命令 | 作用 |
| —- | —- ||(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
INSERT INTO tablename VALUES(xxx,xxx,…) | 插入数据1 xxx的数据要按列的顺序来的 如果没有值就用NULL(允许NULL的字段) 不安全的
INSERT INTO tablename(columns1,columns2,…) VALUES(xxx1,xxx2,…) | 插入数据2 更安全的更繁琐的
INSERT INTO tablename(columns1,columns2,…) VALUES(xxx1,xxx2,…),(xxx1,xxx2,…),(xxx1,xxx2,…) | 可以进行多条插入 也可以这样后面的用，隔开（MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快）
INSERT INTO tablename(columns1,columns2,…) VALUES(xxx1,xxx2,…) SELECT columns1,columns2,… FROM tablename | 可把查询的结果当做数据插入到相应的表中

注意事项：

  • 一般不要使用没有明确给出列的列表的INSERT INTO语句。使用列的列表能使SQL代码继续发挥作用，即使表结构发生了变化

  • 不管使用哪种INSERT语法，都必须给出VALUES的正确数目。如果不提供列名，则必须给每个表列提供一个值。如果提供列名，则必须对每个列出的列给出一个值。如果不这样，将产生一条错误消息，相应的行插入不成功

  • (省略列)如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件。

    • 该列定义为允许NULL值（无值或空值）。
    • 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

  • 如果对表中不允许NULL值且没有默认值的列不给出值，则MySQL将产生一条错误消息，并且相应的行插入不成功

  • 数据库经常被多个客户访问，对处理什么请求以及用什么次序处理进行管理是MySQL的任务。INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。 如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之间添加关键字 LOW_PRIORITY，指示MySQL降低INSERT语句的优先级 同样适用于 UPDATE和DELETE

  • INSERT和SELECT语句中不要求列名匹配。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。这对于从使用不同列名的表中导入数据是非常有用的

  • INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据

更新数据/删除数据

命令 作用
UPDATE tablename SET columns1 = value1,columns2 = value2 WHERE xxx = xxx 如果没有WHERE 将会对所有的列进行赋值value(是很危险的行为) 更新多个列用，隔开即可
UPDATE columns a,(SELECT columns1 FROM tablename WHERE cust_id = 10001) b SET a.xxx = b.xxx WHERE a.cust_id = 10003; a是tablename的别名 b是这一条查询语句的返回结果集 SET 更新数据 WHERE 在更新数据前进行判断 在UPDATE中使用子查询 (a和b都是派生表)
UPDATE tablename SET columns1 = NULL WHERE xxx = xxx 删除莫一个值 可以使用UPDATE赋值为NULL来实现

DELETE FROM tablename WHERE columns = xxx 删除掉过滤的行(这里是删除行 不是删除列 如果要用删除列 请使用上面的命令)
TRUNCATE TABLE tablename | 删除表的所有行

更新/删除注意事项：

  • 可使用子查询来当做数据更新 UPDATE tablename SET columns1 = (SELECT ...) WHERE xxx = xxx
  • IGNORE关键字 如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，示例：UPDATE IGNORE customers…
  • 删除表的内容而不是表 DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。
  • 如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。
  • 如果是整理表内部的碎片，可以用truncate跟上reuse stroage

删除的总结：

  • DROP删表，表结构将删了，当然数据也不存在了
  • TRUNCATE和DELETE删数据，表结构还在
  • DELETE可以带条件删除，TRUNCATE是全部删除
  • DELETE删除会写日志，TRUNCATE不写
  • DELETE效率低，数据可以恢复，TRUNCATE效率高，数据不可恢复

更新和删除总结：
执行UPDATE而不带WHERE子句，则表中每个行都将用新值更新。类似地，如果执行DELETE语句而不带WHERE子句，表的所有数据都将被删除。

  • 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
  • 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
  • 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
  • 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。小心使用 MySQL没有撤销（undo）按钮。应该非常小心地使用UPDATE和DELETE，否则你会发现自己更新或删除了错误的数据

创建、更改表、删除表

1
2
3
4
5
6
7
CREATE TABLE tablename (columns1 typedef value UNIQUE KEY ,columns2 typedef value,...,PRIMARY KEY(columns)) ENGINE=(InnoDB/MyISAM) DEFAULT CHARSET=utf8 COMMENT='说明' --创建表
ALTER TABLE tablename ADD columns typedef value --添加一个列 修改表结构
ALTER TABLE tablename DROP COLUMN columns --删除上面的列 修改表结构
alter table tablename add constraint primary key(sid) -- 添加主键
alter table tablename drop primary key; -- 删除主键
DROP TABLE tablename --删除表（删除整个表而不是内容）
RENAME TABLE tablename TO newtablename, tablename1 TO newtablename1 --对tablename的重命名 用逗号隔开用于多个表的重命名
  • PRIMARY KEY 关键字指定（不能为NULL值）可使用组合键(组合起来的必须是唯一的) PRIMARY KEY(columns1,columns2)
  • UNIQUE KEY 设置字段的唯一约束（保持字段的唯一性 阔以有多个唯一约束） 保持除主键外值的唯一可用唯一约束
  • typedef 数据类型
  • value 是值的属性并不是值(比如：这个值能否为NULL值等) NULL（可以为NULL值） NOT NULL(不能为NULL值) AUTO_INCREMENT（自动增量）DEFAULT（默认值） 每个表只允许一个 AUTO_INCREMENT 列，而且它必须被索引（如，通过使它成为主键）。
  • 在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。（AUTO_INCREMENT值会被插入的键值给覆盖 如果为NULL则不影响）
  • SELECT last_insert_id() 函数获得 AUTO_INCREMENT（自动增量）的值 （仅会在INSERT 和UPDATE语句使用后才可以返回这的自动增量值）
  • ENGINE 搜索引擎（请看上面的全文本搜索）
    • InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索
    • MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）
    • MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理
  • 混用引擎类型有一个大缺陷。外键（用于强制实施引用完整性）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键
    命令|作用
  • ADD 添加一个列
  • DROP 删除一个列
    —|—|—
    CREATE TABLE tablename (columns1 typedef NOT NULL AUTO_INCREMENT,columns2 typedef NULL DEFAULT 1,…,PRIMARY KEY(columns) ENGINE=(InnoDB/MyISAM) | 创建表
    ALTER TABLE 主表的名字 ADD CONSTRAINT 外键的名字默认系统定义 FOREIGN KEY (外键字段（从表的主键值）) REFERENCES (参考表)从表的表名 (参考字段(和从表的主键一样就行了)) ON DELETE CASCADE ON UPDATE CASCADE; | 定义表的外键 CASCADE 就是当关联的表更新后， emp中的dept_id也随着更新。删除表也是选择 CASCADE

外键：主表和从表 (外键是从表的主键值 另一个则就是主表 从表的主键只是主表的一个普通的字段(列))
Navicat在MySQL中添加外键详细过程
定义外键

视图

命令 作用
CREATE VIEW 视图name AS SELECT 一条查询语句 把这个结果当做一个视图返回 创建视图 这个视图表可以进行和普通表一样的操作更改查询等
SHOW CREATE VIEW 视图name 查看视图
DROP VIEW 视图name 删除视图
  • 视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
  • 为什么使用视图
    • 重用SQL语句。
    • 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
    • 使用表的组成部分而不是整个表。
    • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
    • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
  • 视图极大地简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用。
  • 如果从视图检索数据时使用了一条WHERE子句，则两组子句（一组在视图中，另一组是传递给视图的）将自动组合。
  • 视图对于简化计算字段的使用特别有用。
  • 视图是根据基表的内容来定的 所有如果表中的数据发生了更改 视图是会更改的。
  • 更新一个视图将更新其基表（视图本身没有数据 相当于基表和视图的数据是一起的 视图一般不进行更改删除等操作 只进行数据检索）
  • 并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：
    • 分组（使用GROUP BY和HAVING）；
    • 联结；
    • 子查询；
    • 并；
    • 聚集函数（Min()、Count()、Sum()等）；
    • DISTINCT；
    • 导出（计算）列。
  • 这看上去好像是一个严重的限制，但实际上不是，一般视图用于检索（SELECT语句）而不用于更新（INSERT、UPDATE和DELETE）

总结：视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据

存储过程

  • 使用存储过程有3个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，在将SQL代码转换为存储过程前，也必须知道它的一些缺陷
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 一个完整的存储过程 要有注释
-- Name: ordertotal
-- Parameters:
    -- onumber = order
    -- numbertaxable = 0 if not taxable, 1 if taxable
    -- ototal = order total variable
CREATE PROCEDURE ordertotal
(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax' -- COMMENT 这个表加注释 用在字段后面就是给字段加注释
BEGIN
    -- Declare variable for tota?
    DECLARE total DECIMAL (8,2); -- 声明total DECIMAL类型
    -- Declare tax percentage
    DECLARE taxrate INT DEFAULT 6; -- 声明taxrate为int类型 默认值为6
    -- Get the order total
    SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total; -- 求和

    -- Is this taxable?
    IF taxable THEN -- 是否计算税
        -- Yes, so add taxrate to the total
        SELECT total+(total/100*taxrate) INTO total;
    END IF;

    -- And finally, save to out variable
    SELECT total INTO ototal; -- 保存结果返回出去
END;

-- 注1：SHOW CREATE PROCEDURE procedure_name; 查看检查一个存储过程
-- 注2：SHOW PROCEDURE STATUS; 查看存储过程列表（何时、由谁创建等详细信息）
-- 注3：SHOW PROCEDURE STATUS LIKE 'ordertotal'; 可使用过滤模式 进行过滤

游标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE PROCEDURE processorders()
BEGIN
    -- DECLARE local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT; -- 声明局部变量 o
    DECLARE t DECIMAL(8, 2);

    -- DECLARE cursor，
    DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; -- 创建游标

    -- DECLARE CONTINUE HANDLER 这里的阔以自己设定 这里只是方便遍历到游标的最后而已（这个写法是固定的）
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 当SQLSTATE为02000时设置done值为1(当fetch游标到了数据库表格最后一行的时候)

    CREATE TABLE IF NOT EXISTS ordertotals(order_num INT , total DECIMAL(8, 2)); -- 如果没有ordertotals表则创建表

    OPEN ordernumbers; -- 打开游标

    -- Loop through all rows
    REPEAT -- 循环浏览所有行

        -- GET order number
        FETCH ordernumbers INTO o; -- 取订单号 赋值给 o

        -- GET the total for this order
        CALL ordertotal(o, 1, t); -- 计算出来的结果给t

        -- Insert order and total into ordertotals
        INSERT INTO ordertotals(order_num, total) VALUES(o, t); -- 将结果存入到新表中

    UNTIL done -- 循环条件 满足条件退出 当done为1的时候退出 也阔以用 while

    END REPEAT; -- 结束循环

    CLOSE ordernumbers; -- 关闭游标
END;
  • 存储过程处理完成后，游标就消失（因为它局限于存储过程）。
  • 游标用OPEN CURSOR语句来打开
  • 游标用CLOSE CURSOR语句来打开（如果不明确关闭游标，MySQL将会在到达END语句时自动关闭它-BEGIN对应的END）
  • 在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）
  • SQLSTATE ‘02000’是一个未找到条件
  • 如果调用这个存储过程，它将定义几个变量和一个CONTINUE HANDLER，定义并打开一个游标，重复读取所有行，然后关闭游标。
  • 如果一切正常，你可以在循环内放入任意需要的处理（在FETCH语句之后，循环结束之前）。

触发器

  • 触发器(事件：比如删除了一个用户 每删除一个用户就保存一个副本的操作 就可以用触发器来完成)
  • 触发器只能创建在永久表上、临时表上不行、视图也不行
  • 注意触发器涉及触发表自身的操作时，用before 不然会报错(1442 - Can't update table 'orders' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.)
  • 每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）
  • 单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器
  • 触发器失败 如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）

触发器只支持下面三种操作：

  • DELETE
  • INSERT
  • UPDATE

触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：

  • 监视地点(table)
  • 监视事件(insert/update/delete)
  • 触发时间(after/before)
  • 触发事件(insert/update/delete)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建触发器
/*
CREATE TRIGGER用来创建名为 'newproduct' 的新触发器。触发器
可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，
所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR
EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product
added将对每个插入的行显示一次。
*/
CREATE TRIGGER newproduct BEFORE INSERT ON products FOR EACH ROW SELECT 'Product added'; -- 这里省略了BEGIN END 因为只有一条语句

-- 触发器SQL语法
CREATE TRIGGER 触发器名字
AFTER/BEFORE INSERT/UPDATE/DELETE ON 表名
FOR EACH ROW -- 这句话在mysql是固定的
BEGIN
    -- 触发的sql语句;
END;
  • AFTER 之后操作
  • BEFORE 操作之前
  • FOR EACH ROW
    • for each row 每行受影响，触发器都执行，叫行级触发器。
    • oracle 触发器中分行级触发器和语句级触发器，可不写for each row，无论影响多少行都只执行一次。
    • mysql不支持语句触发器，所以必须写for each row

删除触发器：

  • 触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。
1
DELETE TRIGGER newprodduct;

触发器中所谓NEW，OLD只是指INSERT DELETE UPDATE操作执行前的表和执行后的表，其实都是触发器所在的那个表。当你执行了一个操作的时候，有BEFORE触发器的时候，会先运行触发器，这个时候表的状态是OLD，你在触发器中可以取OLD的内容，然后也可以指定NEW的内容，而默认NEW的内容是你操作时更新的数据内容，如果没有指定，则还是OLD的内容，最后执行操作，按照NEW的数据更新进表

INSERT触发器：

  • 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行
  • 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）
  • 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值
1
CREATE TRIGGER neworder BEFORE INSERT ON orders FOR EACH ROW SELECT NEW.order_num; -- NEW 是个虚拟表(刚刚插入的值)

DELECT触发器：

  • 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
  • OLD中的值全都是只读的，不能更新。
1
CREATE TRIGGER neworder BEFORE DELECT ON orders FOR EACH ROW SELECT OLD.order_num; -- OLD 是个虚拟表(刚刚插入的值)

UPDATE触发器：

  • 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值
  • 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）
  • OLD中的值全都是只读的，不能更新
  • 任何数据净化都需要在UPDATE语句之前进行，就像下面这个例子中一样。每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换。
1
CREATE TRIGGER neworder BEFORE UPDATE ON orders FOR EACH ROW SELECT NEW.vend_state = Upper(NEW.vend_state);

总结：

  • Before与After区别(如果要操作触发表中的数据 最好用Before 不然应该会报错 用的时候需要注意 最好经过测试再进行)
    • before：(insert、update)可以对new进行修改，after不能对new进行修改，两者都不能修改old数据。
  • 对于INSERT语句, 只有NEW是合法的
  • 对于DELETE语句，只有OLD才合法
  • 对于UPDATE语句，NEW、OLD可以同时使用
  • after—是在记录操纵之后触发，是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作（所以如果要修改当前表的值 都用before 不然会报错 导致更新失败等）
  • before—是在记录操纵之前触发，是先完成触发，再增删改，触发的语句先于监视的增删改，我们就有机会判断，修改即将发生的操作，如：我们在触发之前需要判断new值和old值的大小或关系，如果满足要求就触发，不通过就修改再触发；如：表之间定义的有外键，在删除主键时，必须要先删除外键表，这时就有先后之分，这里before相当于设置了断点，我们可以处理删除外键。

用触发器的操作其实都阔以用 存储过程来实现 触发器相当于隐式的存储过程（触发器和存储过程其实都差不多 但是对于一个不熟悉数据库操作系统的人来看的话肯定是有存储过程看着有逻辑关系些更容易懂 如果是触发器的话 太不方便查找 所有还是尽量用存储过程比较好）

参考：
mysql触发器不能对同一张表进行修改操作的总结
数据库中触发器before与after认识
MySql中的存储过程和触发器的使用和区别
关于mysql触发器、存储过程的一点儿小见解
MySQL触发器更新和插入操作/存储过程和触发器的区别

事务处理

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句。（这样做也没有什么意义。）你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销

  • 事务（transaction）指一组SQL语句；
  • 回退（rollback）指撤销指定SQL语句的过程；
  • 提交（commit）指将未存储的SQL语句结果写入数据库表；
  • 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。保留点越多，你就越能按自己的意愿灵活地进行回退。
  • 保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放 也可以用 RELEASESAVEPOINT 明确地释放保留点。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- START TRANSACTION 标识事务的开始 可使用 BEGIN
-- ROLLBACK 回退
-- COMMIT 提交事务
-- SAVEPOINT 保留点（可以理解为备份点 快照）

-- 使用ROLLBACK
SELECT * FROM ordertotals; -- 检查 ordertotals 是否为空
START TRANSACTION; -- 开始事务
DELETE FROM ordertotals; -- 删除 ordertotals 表
SELECT * FROM ordertotals; -- 检查表是否为空
ROLLBACK; -- 回退操作
SELECT * FROM ordertotals; -- 查看表数据

-- 使用COMMIT
START TRANSACTION; -- 开始事务
DELETE FROM orders where order_num = 20010;
ROLLBACK;
DELETE FROM orderitems where order_num = 20010;
COMMIT; -- 提交事务

-- 使用SAVEPOINT
SAVEPOINT delete1; -- delete1 保留点的名字 回退的时候可以明确的退到哪儿

-- 回退到保留点
ROLLBACK TO delete1;

-- 更改默认的提交行为 这个AUTOCOMMIT标志是针对每个连接而不是服务器的
SET AUTOCOMMIT=1; -- 设置提交行为设为 ON 自动
SET AUTOCOMMIT=0; -- 设置提交行为设为 OFF 不自动 需要手动 COMMIT 不然的话都可回退

-- 查看提交行为
show variables like 'autocommit';
  • 一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句。
    MySQL的自动提交模式
    MySQL–当事务遇到DDL命令

事件(EVENT)

创建事件

  • 查看mysql事件是否开启：show variables like '%scheduler%';
  • 开启mysq事件：set global event_scheduler='on';
  • 查看定时器信息 SELECT * FROM information_schema.events;
  • 查看事件列表 show events;

开启事件/查看事件

事件（EVENT）既“时间触发器”，与触发器（TRIGGER）的事件触发不同，EVENT类似与 LINUX CRONTAB 计划任务，用于时间触发。通过单独或调用存储过程使用，在某一特定的时间点，触发相关的SQL语句或存储过程。

  • 对于每隔一段时间就有固定需求的操作，如创建表，删除数据等操作，可以使用EVENT来处理。

开启定时器：
要使event起作用，MySQL的常量 GLOBAL event_scheduler 必须为ON或者是1。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 查看是否开启定时器
SHOW VARIABLES LIKE 'event_scheduler';

-- 开启定时器 0：OFF(关) 1：ON(开)
SET GLOBAL event_scheduler = 1;

-- 创建事件 语法
CREATE EVENT
[IF NOT EXISTS] -- 标注1 如果没有这个时间才创建这个事件

EVENT_NAME -- 标注2 事件名字

ON SCHEDULE schedule -- 标注3 计划任务 多久执行

[ON COMPLETION [NOT] PRESERVE] -- 标注4 当这个事件不会发生的时候会被默认删除drop掉 PRESERVE 这个参数就是保存事件不被drop掉 便于查看事件的具体信息 建议使用这个参数

[ENABLE | DISABLE] -- 标注5 设定事件状态 ENABLE(系统将执行该事件 启用) DISABLE(系统不执行该事件 禁用) ALTER EVENT event_name ENABLE/DISABLE 可用这个语句进行更新状态 默认系统调用

[COMMENT 'comment'] -- 标注6 注释 查看定时器信息可见备注

DO sql_statement -- 标注7 需要执行的存储过程(也阔以再后面追加存储过程语句)

-- 示例 每隔一秒自动调用的e_test存储过程
CREATE EVENT
IF NOT EXISTS

e_test

ON SCHEDULE EVERY 1 SECOND -- EVERY 1 SECOND 每一秒执行

ON COMPLETION NOT PRESERVE

ENABLE

COMMENT 'comment'

DO SELECT 'a';

CREATE EVENT IF NOT EXISTS e_test ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'comment' DO SELECT 'a';

请特别注意：当你设定事件计划为0 或OFF，即关闭事件计划进程的时候，不会有新的事件执行，但现有的正在运行的事件会执行到完毕。对于我们线上环境来说，使用EVENT时，注意在主库上开启定时器，从库上关闭定时器，EVENT触发所有操作均会记录日志(BINLOG)进行主从同步，从库上开启定时器很可能造成卡库。切换主库后之后记得将新主库上的定时器打开。

说明标注：

  1. [IF NOT EXISTS]
    • 使用 IF NOT EXISTS，只有在同名event不存在时才创建，否则忽略。建议不使用以保证EVENT创建成功。
  2. EVENT_NAME
    • 事件名称 最大长度可以是64个字节。名字必须是当前Dateabase中唯一的，同一个
      数据库不能有同名的event。使用event常见的工作是创建表、插入数据、删除
      数据、清空表、删除表。为了避免命名规范带来的不便，最好让事件名称具有描
      述整个事件的能力。建议命名规则如下为：动作名称_（INTO/FROM_）表名_TIME。
    • 例如：
      • 每月创建（清空/删除）fans表
        create(truncate/drop)_table_fans_month;
      • 每天从fans表插入（删除）数据：
        insert(delete)_into(from)_fans_day;

  3. ON SCHEDULE

    • ON SCHEDULE 计划任务，有两种设定计划任务的方式：

      1. AT 时间戳，用来完成单次的计划任务。
      2. EVERY 时间（单位）的数量时间单位[STARTS 时间戳] [ENDS 时间戳]，用 来完成重复的计划任务。

    • 在两种计划任务中，时间戳可以是任意的 TIMESTAMP 和 DATETIME 数据类型，时间戳需要大于当前时间。在重复的计划任务中，时间（单位）的数量可以是任意非空（Not Null）的整数式，时间单位是关键词：YEAR，MONTH，DAY，HOUR，MINUTE 或者 SECOND。

    • 提示：其他的时间单位也是合法的如：QUARTER, WEEK, YEAR_MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND, MINUTE_SECOND，不过不建议使用这些不标准的时间单位。

  4. [ON COMPLETION [NOT] PRESERVE]

    • ON COMPLETION 参数表示”当这个事件不会再发生的时候”，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被Drop掉，建议使用该参数，以便于查看EVENT具体信息
  5. [ENABLE | DISABLE]
    • 参数Enable和Disable表示设定事件的状态。Enable表示系统将执行这个事件。Disable表示系统不执行该事件。可以用如下命令关闭或开启事件：ALTER EVENT event_name ENABLE/DISABLE
  6. [COMMENT 'comment']
    • 注释会出现在元数据中，它存储在information_schema表的COMMENT列，最大长度为64个字节。'comment'表示将注释内容放在单引号之间，建议使用注释以表达更全面的信息。

  7. DO sql_statement

    • DO sql_statement字段表示该event需要执行的SQL语句或存储过程。这里的SQL语句可以是复合语句，例如：

      1
      2
      3
      4
      5
      BEGIN
      CREATE TABLE test1;//创建表（需要测试一下）
      DROP TABLE test2;//删除表
      CALL proc_test1();//调用存储过程
      END

    • 使用BEGIN和END标识符将复合SQL语句按照执行顺序放在之间。当然SQL语句是有限制的，对它的限制跟函数Function和触发器Trigger 中对SQL语句的限制是一样的，如果你在函数Function和触发器Trigger中不能使用某些SQL，同样的在EVENT中也不能使用。明确的来说有下面几个：

      1
      2
      3
      4
      5
      LOCK TABLES
      UNLOCK TABLES
      CREATE EVENT
      ALTER EVENT
      LOAD DATA

修改事件

使用ALTER EVENT 来修改事件，与创建事件的语法类似：

1
2
3
4
5
6
7
8
ALTER EVENT
event_name -- 原事件名字
ON SCHEDULE schedule -- 任务计划
[RENAME TO new_event_name] -- 重命名 还能用来移动事件
[ON COMPLETION [NOT] PRESERVE] -- PRESERVE 事件结束可以保存事件信息 方便查看
[ENABLE | DISABLE] -- 事件状态 ENABLE（启动） DISABLE（禁用）
[COMMENT 'comment'] -- 注释
DO sql_statement -- 存储过程语句

删除事件

EVENT使用DROP EVENT来删除已经创建的事件，语法如下：

1
2
3
4
5
DROP EVENT
[IF EXISTS]
event_name -- 事件名字

DROP EVENT IF EXISTS event_name;

但当一个事件正在运行中时，删除该事件不会导致事件停止，事件会执行到完毕为止。使用 DROP USER 和 DROP DATABASE 语句同时会将包含其中的事件删除。

参考：
mysql的event(事件)用法详解1
mysql的event(事件)用法详解2
mysql事件之修改事件（ALTER EVENT）&禁用事件（DISABLE）&启用事件（ENABLE）&事件重命名&数据库事件迁移

MySQL的字符集和校对规则

  • 字符集为字母和符号的集合
  • 编码为某个字符集成员的内部表示
  • 校对为规定字符如何比较的指令
  • 查看所支持的字符集完整列表，可使用 SHOW CHARACTER SET;
  • 查看所支持校对的完整列表，可使用 SHOW COLLATION; 此语句显示所有可用的* 校对，以及它们适用的字符集
  • 查看当前使用的字符集SHOW VARIABLES LIKE 'CHARACTER%';
  • 查看当前使用的校对表SHOW VARIABLES LIKE 'COLLATION%';
1
2
3
4
5
6
7
8
9
10
11
12
-- 创建表的时候就指定字符集和校对表 （校对表必须要与字符集兼容）
CREATE TABLE tablename (
    column1 INT,
    column2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci; -- 指定该表的字符集和校对规则

-- 除了对一个表规定 H还可以对当个列使用
CREATE TABLE tablename (
    column1 INT,
    column2 VARCHAR(10),
    column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci -- 对单独的列进行指定
) DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci; -- 对整个表指定

校对在对用ORDER BY子句检索出来的数据排序时起重要的作用。COLLATE（校对）还可以用于GROUP BY、HAVING、聚集函数、别名等。

  • 如果指定CHARACTER SET和COLLATE两者，则使用这些值。
  • 如果只指定CHARACTER SET，则使用此字符集及其默认的校对（如SHOW CHARACTER SET的结果中所示）。
  • 如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认。
  • 如果绝对需要，串可以在字符集之间进行转换。为此，使用Cast()或Convert()函数(转换数据类型的两个函数)

下面这个链接在mysql中样式是没用的
SQL中的cast和convert的用法和区别 convert函数样式

安全管理

用户(user_name)定义为 user@host MySQL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%（授予用户访问权限而不管主机名）。

  • 查看数据库的权限

    1
    2
    use mysql; -- 选择数据库
    select user from user; -- 查看用户权限

  • 创建用户账号

    1
    2
    3
    4
    5
    6
    7
    8
    -- IDENTIFIED BY PASSWORD
    CREATE USER user_name IDENTIFIED BY 'xxx'; -- IDENTIFIED 给出了一个口令（密码）

    -- 设置口令(密码) 不指定用户则更改的是当前登录用户
    SET PASSWORD = 'xxx';

    -- 设置指定用户口令(密码)
    SET PASSWORD FOR user@host = password('新密码');

  • 重命名用户账号

    1
    RENAME USER old_user_name TO new_user_name

  • 删除用户账号

    1
    DROP USER user_name

  • 设置用户权限

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    SHOW GRANTS FOR user_name; -- 查看用户的权限

     /*
     GRANT：
         1 要授予的权限
         2 被授予访问权限的数据库或表
         3 用户名
     */

     -- 设置权限
     GRANT SELECT ON crashcourse.* TO user_name; -- crashcourse 数据库的所有表上授予SELECT访问权限 该用户(user_name)对 crashcourse 数据库中的所有数据具有只读访问权限

     -- 撤销权限 取消上面的授权 如果撤销的权限没有会报错
     REVOKE SELECT ON crashcourse.* FROM user_name;

     /*
     GRANT 和 REVOKE 可在几个层次上控制访问权限：
         * 整个服务器，使用 GRANT ALL 和 REVOKE ALL
         * 整个数据库，使用 ON database.*
         * 特定的表，使用 ON database.table；
         * 特定的列；GRANT UPDATE (columns) ON database.table TO user_name 对columns列给予更行权限
         * 特定的存储过程 ON PROCEDURE `procedure_name` TO 'user_name'
         * 撤销存储过程权限 把GRANT 变为 REVOKE 把 TO 变为 FROM
         * 刷新权限 FLUSH PRIVILEGES;
     */

     -- 可通过列出各权限并用逗号分隔，将多条GRANT语句串在一起，如下所示
     GRANT SELECT, UPDATE, INSERT ON crashcourse.* TO user_name;

MySQL之权限管理
MySQL列权限和程序权限管理
MySQL 简单权限控制(授予某个用户执行某一个存储过程的权限

可以授予或撤销的每个权限。

权 限 说 明
ALL 除GRANT OPTION外的所有权限
ALTER 使用ALTER TABLE
ALTER ROUTINE 使用ALTER PROCEDURE和DROP PROCEDURE
CREATE 使用CREATE TABLE
CREATE ROUTINE 使用CREATE PROCEDURE
CREATE TEMPORARY
TABLES 使用CREATE TEMPORARY TABLE
CREATE USER 使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW 使用CREATE VIEW
DELETE 使用DELETE
DROP 使用DROP TABLE
EXECUTE 使用CALL和存储过程 执行权限
FILE 使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION 使用GRANT和REVOKE
INDEX 使用CREATE INDEX和DROP INDEX
INSERT 使用INSERT
LOCK TABLES 使用LOCK TABLES
PROCESS 使用SHOW FULL PROCESSLIST
RELOAD 使用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 无访问权限
  • 在使用GRANT和REVOKE时，用户账号必须存在，但对所涉及的对象没有这个要求。这允许管理员在创建数据库和表之前设计和实现安全措施。这样做的副作用是，当某个数据库或表被删除时（用DROP语句），相关的访问权限仍然存在。而且，如果将来重新创建该数据库或表，这些权限仍然起作用。

数据库的维护

  • 数据库的备份
    • 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
    • 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）。
    • 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据以用RESTORE TABLE来复原。
  • 首先刷新未写数据 为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句。

FLUSH TABLES的作用

  • 查看数据库正确性和正常运行

    1
    2
    3
    ANALYZE TABLE tablename -- 返回状态

    CHECK TABLE tablename -- 发现和修复问题

  • 如果MyISAM表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决。

  • 如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能
  • 在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL服务器自身通过在命令行上执行mysqld启动。下面是几个重要的mysqld命令行选项：
    • –help显示帮助——一个选项列表
    • –safe-mode装载减去某些最佳配置的服务器
    • –verbose显示全文本消息（为获得更详细的帮助消息与–help联合使用）
    • –version显示版本信息然后退出

REPAIR TABLE和OPTIMIZE TABLE

  • 查看日志
    • 主要的日志文件有以下几种。
      • 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用--log-error命令行选项更改。
      • 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用--log命令行选项更改。
      • 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用--log-bin命令行选项更改。注意，这个日志文件是MySQL5中添加的，以前的MySQL版本中使用的是更新日志。
      • 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log ，位于 data 目录中。此名字可以用--log-slow-queries命令行选项更改。
  • 在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

性能优化

  • 一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
  • MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES;和SHOWSTATUS;。）
  • MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST 显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
  • 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法。
  • 使用EXPLAIN(注释)语句让MySQL解释它将如何执行一条SELECT语句。
  • 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。
  • 应该总是使用正确的数据类型。
  • 决不要检索比需求还要多的数据。换言之，不要用SELECT *（除非你真正需要每个列）。
  • 有的操作（包括INSERT）支持一个可选的DELAYED(延时插入)关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
  • 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括FULLTEXT索引），然后在导入完成后再重建它们。
  • 必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
  • 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接它们的UNION语句，你能看到极大的性能改进。
  • 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
  • LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
  • 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
  • 最重要的规则就是，每条规则在某些条件下都会被打破。

MySQL文档 一定要多查看这些非常有价值的资料。

本文标题:MySQL常用命令

文章作者:游戏人生

发布时间:2019年03月22日 - 15:03

最后更新:2020年12月27日 - 12:12

原始链接:http://www.tjl-myblog.cn/Mysql常用命令.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

-------------本文结束感谢您的阅读-------------