mysql-笔记

1.事务原则

原子性:

• 要么都成功, 要么都失败

一致性:

• 事务前后的数据都要完整性都要保持一致

隔离性

• 多个用户并发访问数据库时, 数据库为一个用户开启事务, 不能被其他事务的操作数据所干扰, 多个并发事务之间要有隔离性

持久性

• 事务一旦提交就不可逆, 被持久化到数据库中

脏读

• 指从一个事务中读取到另一个事务未提交的数据

不可重复读

• 在一个事务内读取表中的某一行数据, 多次读取结果不同(这不是一个错误, 只是某些场合不对)

虚读(幻读)

• 是指一个事务内读取到了别的事务插入的数据, 导致前后读取不一致

2.执行事务

• 事务默认是自动提交的 set transaction =1

• 关闭自动提交 set transaction = 0

• 手动提交事务 为 commit

• 回滚是 rollback

• 开启事务

  set transaction =1;  -- 关闭自动
  start transcation; -- 开启事务
  insert into (....)
  update table from (...)
  commit; -- 提交事务
  set transaction =1;  -- 恢复自动
  

• 设置保存点 savepoint

• rollback to savepoint 回滚到保存点

3. Mysql的两个重要的数据引擎

	MYISAM	INNODB
事务的支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大,约为2倍

• INNODB : 安全性能高 支持事务的处理, 多表多用户的操作
• MYISAM : 节约空间,速度较快,适合单机操作

4.索引

4,1 索引的分类

• 主键索引 Primary key

  • 唯一标识, 主键不可重复,只能有一个列作为主键 , 主键列在创建时，已经默认为非空值 + 唯一索引了

• 唯一索引 Unique key

  • 避免标识为唯一索引的列的列值出现重复 , 多个列都可以标识唯一索引
  • 与主键的区别:
    1. 主键不允许空值，唯一索引允许空值
    2. 主键只允许一个，唯一索引允许多个
    3. 主键产生唯一的聚集索引，唯一索引产生唯一的非聚集索引

• 常规索引 Key/index

  • 默认的 , index / key 关键字来设置

• 全文索引 FullText

  • 在特定的数据引擎下才有, MYISAM
  • 快速定位数据

基础语法

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2. 创建完毕之后,增加索引

-- 显示所有索引信息
SHOW INDEX FROM student;

-- 添加 一个全文索引                       索引名         列名
ALTER TABLE student ADD FULLTEXT INDEX address_fulltext (address);

-- explain 分析sql执行情况

EXPLAIN SELECT * FROM student;  -- 非全文索引

-- 使用全文索引数据量小的情况下体现不出来 
EXPLAIN SELECT * FROM student WHERE MATCH(address) AGAINST('广西');  

4.2 测试索引

-- 创建一个app_user 表
CREATE TABLE `app_user` (
  `id`  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` VARCHAR(50) DEFAULT  '' COMMENT '用户昵称',
  `email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
  `phone` VARCHAR(20) DEFAULT '' COMMENT '用户手机号',
  `gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别 (0: 男 , 1: 女)',
  `password` VARCHAR(100) NOT NULL COMMENT '密码',
  `age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, -- 默认为当前时间戳
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';

-- 插入100万条数据
DELIMITER $$  -- 写函数前必须写的的标志
CREATE FUNCTION mock_data () RETURNS INT 
BEGIN
  DECLARE num INT DEFAULT 1000000 ; -- 声明变量
  DECLARE i INT DEFAULT 0 ; -- 声明变量
  WHILE i < num DO -- 插入语句
  INSERT INTO app_user (
      `name`,
      `email`,
      `phone`,
      `gender`,
      `password`,
      `age`
    ) 
    VALUES
      (
        CONCAT('用户', i),
        CONCAT(
          FLOOR(RAND() * ((9999999999-100000) + 100000)),
          '@qq.com'
        ),
        CONCAT(
          '18',
          FLOOR(
            RAND() * ((999999999-100000000) + 100000000)
          )
        ),
        FLOOR(RAND() * 2),
        UUID(),
        FLOOR(RAND() * 100)
      );
  SET i = i + 1 ;
  END WHILE;
  RETURN i;
END;

SELECT mock_data();  -- 执行函数

-- 没有建立索引前 查询耗时0.602毫秒 需要遍历992269条数据才能查找出来
SELECT * FROM app_user WHERE `name` = '用户99999';
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999'; 
      
-- 创建索引 
CREATE INDEX id_app_user_name ON app_user (`name`);       

-- 创建索引之后查询只要0毫秒 只用遍历一次就能获取到数据
SELECT * FROM app_user WHERE `name` = '用户99999';
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999';

索引在数据量小的时候,用处不大,但是在大数据量的时候,区别十分明显

4.3索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上

5.权限管理和用户备份

5.1用户管理

-- 创建用户
CREATE USER test_user IDENTIFIED BY '123'; 

-- 删除用户
DROP USER test2_user 

-- 修改密码(当前用户密码)
SET PASSWORD = '111';
-- 修改密码 (修改指定用户密码)
SET PASSWORD FOR test2_user='text';

-- 重新命名用户名
RENAME USER test_user TO test2_user;

-- 用户授权 只有root用户才能进行授权
-- all privileges 全部权限  *.* (所有库.所有表)
GRANT ALL PRIVILEGES ON  data.* TO test2_user;

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.*  FROM test2_user;
-- 查看权限
SHOW GRANTS FOR test2_user;
SHOW GRANTS FOR root@localhost;

5.2用户导入与导出

• 导出 (命令方式在cmd下使用)
  • mysqldump -h 主机 -u 用户名 -p密码 数据库 表名 > 磁盘位置/文件名
  • -h 在本机下导出可以忽略不用写 远程导出才需要加上
  • 如果导出整个数据库就不用写后面的表名
  • 如果是要导出库中某几张表就在表名后面加上空格继续下一张表名

• 导入

  • 登录的情况下 选择对应的数据库
  • source d:/user.sql

  

  • 未登录的情况下