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
- 避免标识为唯一索引的列的列值出现重复 , 多个列都可以标识唯一索引
- 与主键的区别:
- 主键不允许空值,唯一索引允许空值
- 主键只允许一个,唯一索引允许多个
- 主键产生唯一的聚集索引,唯一索引产生唯一的非聚集索引
-
常规索引 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
- 未登录的情况下