MYSQL学习笔记one

MYSQL 笔记

好久没写博客了，感觉时间过的好快，虽然没更文，但是自己没有放弃记录输出，这次记录下学习 MySQL 的一些知识。

1.MYSQL 中 NULL 和空值区别

1. 空值长度为 0，null 没有长度，显示 null
2. 函数 count 默认把列中 null 行去掉，空值会计算，函数 ifnull(parm1,parm2) 意为当 parm1 为 null 值时，结果为 parm2，不为null 时，结果为 parm1
3. MYSQL 中，普通索引和复合索引有值为 null 时是走索引的，资料上说空间索引不走
4. explain 可以查看语句执行详情
5. show keys(indexes) from table 查看表索引
6. 全部插入 null 会报错

-- 1048 - Column 'one' cannot be null

1. 添加索引

-- ALTER TABLE table_name ADD INDEX index_name(col_name);
--ALTER TABLE tb_test ADD INDEX index_oat (one, two);
--ALTER TABLE tb_test add INDEX index_two(two);

2.MYSQL触发器

1. 显示库触发器 show triggers;

SHOW TRIGGERS [FROM schema_name];
其中可选参数 schema_name 即 Schema 的名称，在 MySQL 中 Schema 和 Database 相关，查看时可以指定数据库名。指定后会从指定的数据库查询，不定则从 USE database_name;切换的，当前工作数据库查询。

1. 创建触发器

**CREATE TRIGGER <触发器名称>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名称>
FOR EACH ROW
<触发的SQL语句>**
触发器名称 -- 触发器名字，最多 64 个字符，其命令规则和 MySQL 中其他对象的命名方式类似
{ BEFORE | AFTER } -- 触发器执行时间：可以设置为事件发生前或后
{ INSERT | UPDATE | DELETE } -- 触发事件：可以设置为在执行 INSERT、UPDATE、DELETE 操作时触发
表名称 -- 触发器所属表：触发器属于某一个表，当在这个表上执行 INSERT、UPDATE、DELETE 操作的时就会使触发器触发，一张表的同一个事件只能有一个触发器
FOR EACH ROW -- 触发器的执行间隔：FOR EACH ROW 子句通知触发器，每行执行一次动作
触发的 SQL 语句 -- 事件触发时所要执行的SQL语句。
例:
mysql> DELIMITER $ //将SQL语句的结束符设置为#
mysql> CREATE TRIGGER t_after_insert_userRoles
AFTER INSERT ON
userRoles
FOR EACH ROW
BEGIN

update users set isManager = 1 where userId = new.userId and new.roleId = 1;

END$
mysql> DELIMITER ; //将 SQL 语句的结束符设重置为;以不影响后续使用

1. 删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

3.MYSQL 数据库的事务

特性:

1. 原子性（Atomicity）事务中的程序对数据库的修改操作要么全部成功，要么全部失败
2. 一致性（Consistency）事务执行前和执行后数据的来源和去向保持平衡
3. 隔离性（Isolation）并发时每个事务相互不影响，互相隔离
4. 持久性（Durubility）事务一旦提交成功，应保证数据的完整存在

   隔离级别:

A.读取未提交
B.读取已提交
C.可重复读(mysql默认级别)
D.可串行化(会导致大量超时现象和锁竞争)

oracle数据库支持两种B和D，默认为B

4.MYSQL 数据库 SQL 优化

1. explain+sql查看语句执行计划，type是sql语句查询的类型all是没有走索引，ref、eq_ref是走索引，type为index_merge，extra有intersect是并集索引
2. create index 名字_index on table(加索引字段,…)，建单个或者联合索引
3. show warnings;是查询优化后的语句
4. 查询的结果集为个别时，where后的条件都加索引
5. 多表连接的字段上需要建立索引
6. where条件字段上需要建立索引
7. 排序字段上需要建立索引
8. 分组字段上需要建立索引
9. Where条件上不要使用运算函数，以免索引失效
10. 多列索引还有最左前缀的特性，即索引的第一个字段sex要出现在where条件中
11. 当where后的条件联合起来查询结果区分度高时推荐使用联合索引

MYSQ逻辑处理步骤如下图