MySQL非基础性知识

相比于常见的MySQL学习，一些进阶补充的知识，上传图片总是失败，少了好多东西，因此贴上我的有道云笔记原文地址

我的MySQL有道云笔记《《《《点这里

like关键字中下划线"_"代表单个字符，百分号“%”可以代表多个或者0个字符

escape可以让某个符号或者字符可以转义，比如让%、_代表原来的意思：

null必须和is搭配，也包括is not null

注意distinct（用的时候在列名前面）对聚合函数avg，sum的影响，还有count

having后面的列名要么在聚合函数里，要么在group by里出现过

mysql中is好像只能和null连用

二、MySQL官方文档使用技巧

　　1、{}大括号括起来并且中间有个|管道符的代表 n 选一，必须输入

　　2、[]中括号里括起来的表示可有可无

　　3、小写字母代表变量，将来可以替换

　　4、大写字母代表关键字，必须输入

　　5、一般有 sepcification 后缀的表示这是一个规范，后边还会有详细的介绍

　　6、 |管道符连接的信息没有用{}大括号括起来代表可以有其中一个也可以同时都有

删除用户之后记得一定要刷新权限flush privileges; 不然重新创建同名用户时候会报错：

ERROR 1396 (HY000): Operation CREATE USER failed for 'XXXX'@'XXXX'

星号代表所有，*.*意思是所有数据库+点+所有表

权限授权

# Tips 注意：

　　使用 grant、revoke 的时候都会修改 user 数据表，也会同步修改内存中的 hash 结构，即时生效，也就没有必要跟着执行 flush privileges 命令；

　　flush privileges 使用场景：加载用户权限。直接使用 DML 操作系统权限表，会导致权限数据跟内存中的权限数据不一致，这时就是需要使用 flush privileges 重建内存数据，达到权限一致状态。

cast(expr as type)

cast将指定量expr转换为指定类型常量type；

int数据类型：

MySQL中单引号转义

实时更新记录修改时间

-> uptime timestamp on update current_timestamp

字符集编码方式设置

concat函数，字符串连接， concat_ws指定分隔符

concat拼接内容中含有null时会导致整个内容变null、

limit关键字 offset关键字

mysql中：

<=>运算符比较两端是否一致或者都是空值返回true或者false

regexp正则操作符

xor逻辑异或，两个条件正确性不同时返回true，否则false

常用字符串函数：

lower，upper，concat， concat_ws， instr， length（存储长度）， char_length（实际字符数），repalce（）替换

常用数值函数

日期比较函数

sql中if（判断，真返回该结果，假返回该结果）

ifnull（不空该值，空该值）

nullif（参数一， 参数二）两个相等返回空，不相等返回参数一

simple case：

　　①value可以是字面量、表达式或者列名

　　②CASE表达式的数据类型取决于跟在then或else后面的表达式的类型

类型必须相同（可尝试做隐式类型转换），否则出错

mysql> select userid,case salary                                           

    -> when 1000 then 'low'    -> when 2000 then 'med'    -> when 3000 then 'high'    -> else '无效值' end salary_grade

    -> from salary_tab;

search case：

　　如果某个when子句后面的条件condition为true，则返回相应的when子句后面的值result；

　　如果所有的when子句后面的条件condition都不为true，则返回else子句后面的值；

　　如果没有else部分则返回null。

mysql> select userid,case    -> when salary<=1000 then 'low'    -> when salary=2000 then 'med'    -> when salary>=3000 then 'high'    -> else '无效值' end salary_grade

    -> from salary_tab;

分组函数：

group_ocncat()分组中括号内 的指定列的值进行拼接

with rollup

　　如果有子句GROUP BY E1,E2,E3,E4 WITH ROLLUP

　　那么将分别执行以下分组：[E1,E2,E3,E4]、[E1,E2,E3]、[E1,E2]、[E1]、[]

子查询：

如果子查询的结果集中有null值，使用>ALL 和not in操作符时，必须去掉子查询结果集中的null值，否则查询结果错误

insert和replace用法类似，replace在主键重复时会覆盖原有行，不重复则新增一行

详细用法：https://www.cnblogs.com/geaozhang/p/6770115.html

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

注意：在SET子句的子查询中，不允许访问要更新的表。

UPDATE <表1>

　　SET amount = amount-(SELECT avg(amount) FROM 表1);

！！上述写法不允许

解决(分两步)：

mysql> SET @avg_amount = (SELECT avg(amount) FROM <表1>);

mysql> UPDATE <表1>

        SET amount = amount - @avg_amount;

多表同时更改时，where中进行连接，update关键字后面跟多个要修改的表，用逗号隔开，set关键字后要更改的表达式之间使用逗号隔开

清空表中数据时，尤其是数据量多时TRUNCATE要比delete快，delete按行删，TRUNCATE直接清空所有数据记录

MySQL中的保留字例如select如果有字段恰好重名，则需要利用反引号标注括起来，例如`select`;

二进制以及图片存储方式：

表中主键自增列使用时，即使删除了表中的所有行，插入新行时，编号还是从原来的位置继续；要想再次从1开始，必须删除整个表并重新创建。

临时表与普通表重名时，临时表在被删除前无法看到同名普通表，会话结束后临时表消失，此时同名普通表可见；

if not exist使用时已存在同名表的话不会报错，但也不会覆盖，只是建表失败；

复制旧表CREATE TABLE new_tbl LIKE orig_tbl;将从源表复制列名、数据类型、大小、非空约束以及索引；而表的内容以及其它约束不会复制，新表是一张空表。

创建一张新表时如果同时引入其他表中的数据时，　　如果在表名后面指定的列名和原始表中的列名相同，则可以改变列的大小和非空约束；

　　如果在表名后面指定的列名和原始表中的列名不同，则它作为一个新的列。

例如：

INSERT和UPDATE语句中使用DEFAULT关键字显式地给列赋默认值：

mysql> insert into people values(3,'王五',default);

mysql> update people set sex=default where id=2;

null和null不相等；

常见约束删除：

主键：alter table xxx drop primary key;

外键：alter table xxx drop foreign key xxx;

①ON DELETE CASCADE：级联删除。当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么连同子行一起删除（很危险！！！）

　　②ON DELETE SET NULL：当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么不删除，而是将子行的外键列设置为null

定义数据库列时，可以使用ENUM（enumeration，枚举）和SET（集合）类型：变通的实现CHECK约束

两者的区别是：

　　使用ENUM，只能选一个值；

　　使用SET，可以选多个值；

ENUM和SET中的值都必须是字符串类型。

（字符自动忽视英文大小写，内部自己可以互转)

有下列内容之一，视图不能做DML操作：

　　①select子句中包含distinct

　　②select子句中包含组函数

　　③select语句中包含group by子句

　　④select语句中包含order by子句

　　⑤select语句中包含union 、union all等集合运算符

　　⑥where子句中包含相关子查询

　　⑦from子句中包含多个表

　　⑧如果视图中有计算列，则不能更新

　　⑨如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

with check option是为了利用视图对 相关表进行DML操作时不得违背该视图中WHERE后条件语句

嵌套视图；

存储过程中为了防止分号；被误解读，提前使用自定义分隔符：delimiter 符号，写完存储过程和触发器再使用delimiter ；改回去

局部变量：declare 变量名称 类型 [default]

用户变量：只在当前用户会话连接时保持有效；格式set @变量名:=值

或者select@变量名:=值

注意在非SET语句中=被视为一个比较操作符

系统变量分为：全局变量， 会话变量

系统变量：根据系统变量的作用域分为：全局变量与会话变量（两个@符号）

　　①全局变量(@@global.)

　　　　在MySQL启动的时候由服务器自动将全局变量初始化为默认值；

　　　　全局变量的默认值可以通过更改MySQL配置文件(my.ini、my.cnf)来更改。

　　②会话变量(@@session.)

　　　　在每次建立一个新的连接的时候，由MySQL来初始化；

　　　　MYSQL会将当前所有全局变量的值复制一份来做为会话变量（也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的）

存储过程流程控制语句：

调用函数的时候，函数需要出现在 = 的右边（也就是说调用函数需要有变量接收其结果）

create function 函数名（输入参数名 参数类型）

returns 返回类型

声明变量（与返回类型相同）

begin

一顿操作赋值

return 变量

end

select into 变量...中变量名不能和数据字段名相同

select into outfile 用法、load data infile 用法（输入输出文件）

存储过程异常处理：继续和退出

    -> 　　DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'

    -> 　　SET error = '23000';

    -> 　　DECLARE EXIT HANDLER FOR SQLSTATE '23000'

    -> 　　SET error = '23000';

SQLSTATE码对应的处理程序：

　　1、SQLWARNING处理程序：以‘01’开头的所有sqlstate码与之对应；

　　2、NOT FOUND处理程序：以‘02’开头的所有sqlstate码与之对应；

　　3、SQLEXCEPTION处理程序：不以‘01’或‘02’开头的所有sqlstate码，也就是所有未被SQLWARNING或NOT FOUND捕获的SQLSTATE(常遇到的MySQL错误就是非‘01’、‘02’开头的

1、未命名的基本格式：

BEGIN

　　DECLARE CONTINUE HANDLER FOR 1051

    -- body of handler

END;

2、有命名的基本格式：

BEGIN

　　DECLARE no_such_table CONDITION FOR 1051;

　　DECLARE CONTINUE HANDLER FOR no_such_table

    -- body of handler

END;

当有多层begin end的时候，每层都应该有自己完善的异常处理，做到：自己的异常，自己这层去处理。

用户要使用其他用户创建的procedure必须获得授权，例如使用

GRANT  EXECUTE

        ON PROCEDURE  <过程名>    TO  <user>