数据库进阶知识
数据库进阶知识
MD5加密
=========测试MD5加密======:
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTo testmd5 VALUES(1,'zhangsan' ,'123456') , (2,'lisi' ,'123456'), (3,'wangwu' ,'123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1
-- 加密全部的密码
UPDATE testmd5 SET pwd=MD5(pwd)
-- 插入的时候加密
INSERT INTO testmd5 VALUES(4，'xiaoming',MD5('123456'))
-- 如何校验: 将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE name='xiaoming' AND pWd=MD5('123456')
事务
简单来讲就是操作要么都执行，要么都不执行！
事务的特性:ACID
原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）
-
原子性：事务的整个操作是一个整体，不可以分割，要么全部成功，要么全部失败。
-
一致性：事务操作的前后，数据表中的数据没有变化。
类似物理守恒定律。比如此次事务中操作的所有数据加起来是200，那么等这次操作结束后所有数据加起来还是200
-
隔离性：事务操作是相互隔离不受影响的。
比如A和B同时可能对一条数据操作，这样是不行的，会导致下面谈到的问题
-
持久性：数据一旦提交，不可改变，永久的改变数据表数据。
简单理解就是落子无悔，事务结束后数据不会随着外界原因(停电，重启，宕机等)丢失。
事务的隔离可能导致的问题
企业开发中，事务最复杂问题都是由事务隔离性引起的。当多个事务并发时，利用加锁和阻塞来可以保证事务之间不同等级的隔离性。一般情况下，完全的隔离性是不现实的，完全的隔离性要求数据库同一时间只执行一条事务，这样会严重影响性能。想要理解SQL Server中对于隔离性的保障，首先要了解并发事务之间是如何干扰的.
脏读
一个事务读取了另一个事务未提交的数据。
举例:A有1000转账给了B200，但是同时C给A转账了200，C读取到了A这时候有1000，那么A最后成了1200，，，
不可重复读
在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。
举例:本来读取的A的数据是800，但是再读取之后A成了1000，因为这时候其他角色对A的数据加了200，但是这样导致了两次结果不一致
虚读
当事务不是独立执行时发生的一种现象
举例:例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样.
更新丢失
两个事务同时读取同一条记录，A先修改记录，B也修改记录（B是不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。
隔离级别
博客园有详解，之后补充
事务操作流程
- 管理(关闭)自动提交
- 开启一个事务
- 提交或者回滚
- 如果提交成功啧开启自动提交 如果回滚则关闭自动提交
-- mysgl 是默认开启事务白动提交的
SET autocommit = 0 ;/* 关闭 */
SET autocommit = 1 ;/*开启(默认的)*/
-- 手动处理事务
SET autocommit = 0; -- 关闭自动提交
-- 事务开启
START TRANSACTION;
-- 标记一个事务的开始，从这个之后的 sgl 都在同一个事务内
INSERT,UPDATE,DELETE.......
-- 提交:持久化 (成功! )
COMMIT
-- 回滚:回到的原来的样子 (失败! )
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
流程模拟
-- 事务操作的整个流程
-- 1 创建数据量
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 2 调用自己刚创建的数据量
USE shop;
-- 3 在shop数据库中创建自己想要的表
CREATE TABLE IF NOT EXISTS `account`(
`id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '这是初始ID',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`money` DECIMAL(9,2) NOT NULL COMMENT '金额',
PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8
-- 4 插入部分数据
INSERT INTO `account` (`name`,`money`)
VALUES
('张三','2000.12'),
('李四','1000.20'),
('王五','234.98'),
('赵六','3457.11'),
('钱七','100.00')
-- 5 查看数据
SELECT * FROM account;
-- 以下才开始开启事务 (模拟转账)
-- 6 关闭自动提交
SET autocommit = 0; -- 关闭自动提交
-- 7 开启事务
START TRANSACTION;
-- 8 进行想要的操作(转账)
UPDATE `account` SET `money` = `money` + 500 WHERE `id` = 1;
UPDATE `account` SET `money` = `money` - 500 WHERE `id` = 2;
-- 以下9和10是手动操作的步骤
-- 8结束后如果进行9则8的操作相当于此次事务结束，因为持久化的关系，即使再回滚也没用
-- 8结束后如果进行10则8的操作相当于没进行
-- 9 提交
COMMIT;
-- 10 回滚
ROLLBACK;
-- 11 事务结束 开启自动提交
SET autocommit = 1;
索引
本质就是可以加快数据搜索的方式，通过索引查询更快，且索引是数据结构
重点，索引是数据结构
SHOW INDEX FROM 表名 可以查看所有索引
索引的分类
-
主键索引 （PRIMARY KEY）
- 是唯一的标识，不可重复，只能有一个列作为主键
-
唯一索引 （UNIQUE KEY）
- 可以重复，避免重复的列出现，即可以有多个唯一索引但是不能是同一个名字(结构)
-
常规索引 （KEY/INDEX）
- 默认索引，index，key等来实现
-
全文索引 （FullText）
-
特定的数据库引擎下才有用 MylSAM
-
快速定位数据
-
索引的创建和使用
索引需要看一下索引的数据结构
基础语法
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM account;
-- 增加一个全文索引 (索引名)列名 INDEX后面的是自己创建的索引的名字，括号里面的是本来的列明
ALTER TABLE school.student ADD FULLTEXT INDEX studentName(`studentName`);
-- EXPLAIN 分析sg1执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
-
EXPLAIN 查询的结果如下
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 选择标识符 查询的类型 输出结果集的表 匹配的分区 表的连接类型 查询时，可能使用的索引 实际使用的索引 索引字段的长度 列与索引的比较 扫描出的行数 按表条件过滤的行百分比 执行情况的描述和说明
测试以及结果
-- 1 创建一个测试用表
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
-- 2 插入1000000条数据(navicat可以直接数据生成且指定规则，或者用PLSQL也可以)
-- 3 测试这次查询需要多久 结果和之前的查询时间差距较大
SELECT * FROM `app_user` WHERE `name` = 'Adam Coleman';
EXPLAIN SELECT * FROM `app_user`;
-- 4 下面执行后的结果，rows是995405说明这么多条才完成此次查询
EXPLAIN SELECT * FROM `app_user` WHERE `name` = 'Adam Coleman';
-- 5 给name列添加索引，id_app_user_name 是索引的名字 括号里面是列名
CREATE INDEX id_app_user_name ON `app_user`(`name`);
-- 6 这次的rows是4，时间很快
EXPLAIN SELECT * FROM `app_user` WHERE `name` = 'Adam Coleman';
创建索引的三种方法
-- 1 最开始创建表格的时候创建
-- 2 ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(列名)
ALTER TABLE school.student ADD FULLTEXT INDEX studentName(`studentName`);
-- 3 CREATE INDEX 索引名 ON 表名(列名);
CREATE INDEX id_app_user_name ON `app_user`(`name`);
索引的原则
-
索引不是越多越好
-
不要对进程变动数据加索引
-
小数据量的表不需要索引
-
索引一般加在常用来查询的字段上
权限管理和用户管理
可视化管理
可视化管理的话，点击用户图标就可以进行操作
使用命令管理
其实用户管理包括权限也是在一张表上，就是数据库自带的MySQL下的user表
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY'密码'
CREATE USER xuanli IDENTIFIED BY 'xuanlichaobang'
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('xuanlichaobang')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR xuanli = PASSWORD('xuanlihenbang')
-- 重命名RENAME USER 原来名字 TO 新的名字
RENAME USER xuanli To lianru
-- 用户授权 ALL PRIVILEGES 全部的权限，库.表
-- ALL PRIVILEGES 除了给别人授权，其他都能够干
GRANT ALL PRIVILEGES ON *.* TO lianru
-- *.*是库.表 星号代表的是全部，即全部的库全部的表
-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给xx用户撤销
REVOKE ALL PRIVILEGES ON *.* FROM lianru
-- 查询权限
SHOW GRANTS FOR lianru
-- 删除用户 DROP USER 用户
DROP USER lianru
浙公网安备 33010602011771号