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
  7. 添加索引
    – 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;切换的,当前工作数据库查询。
  2. 创建触发器
    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 语句的结束符设重置为;以不影响后续使用
  3. 删除触发器
    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逻辑处理步骤如下图

在这里插入图片描述