【MySQL】mysql学习笔记04 事务

– =事务============
– 事务原则：ASID原则
– 原子性
– 一致性
– 隔离性
– 持久性
– mysql是自动开启事务提交的
1.建表

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAr(30) NOT NULL,
`money`DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

2、插入数据

INSERT INTO account(`name`,`money`) VALUES('诗怀雅',100000),('杰西卡',99996) 
INSERT INTO account(`name`,`money`) VALUES('Dr.dkt',0) 

– 模拟转账

SET autocommit =0; -- 关闭自动提交
START TRANSACTION  -- 开启事务
UPDATe account SET `money`=`money`-168 WHERe `name`='杰西卡' -- a-168
UPDATE account SET `money`=`money`+168 WHERe `name`='Dr.dkt' -- b+168
COMMIT;            -- 提交事务，持久化
ROLLBACK;          -- 失败回滚
SET autocommit =1; -- 恢复

– ======索引=

USE school
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用户表'

– ===插入一百万条数据=

DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data2()
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),'3402962193@qq.com',CONCAt(15,FLOOR(RAND()*((999999999-10000000)+10000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
     SET i=i+1;
  END WHILE;
  RETURN i;
END;

查询

SELECT mock_data2()
SELECT * FROM `app_user`WHERe `name`='用户9999'
EXPLAIN SELECt * FROM `app_user`WHERe `name`='用户9999'-- 未添加索引耗时近1秒

– 添加索引
– 命名格式： id_表名_字段名

CREATE INDEX id_app_user_name ON `app_user`(`name`);
SELECt * FROM `app_user`WHERe `name`='用户9999' -- 添加后 0.001秒

– ==== 用户=====

CREATE USER zjm IDENTIFIED BY '123456'    -- 创建用户
SET PASSWORD = PASSWORd('654321')         -- 修改密码
SET PASSWORD FOR zjm = PASSWORd('123456') -- 修改指定用户密码
RENAME USER zjm TO zjm2                   -- 重命名
GRANT ALL PRIVILEGES ON *.* TO zjm        -- 授权 all privileges 全部权限 *.* 全部库表
SHOW GRANTS FOR zjm                       -- 查看指定用户权限
SHOW GRANTS FOR root@localhost            -- 查看root用户权限
REVOKE ALL PRIVILEGES ON *.* TO zjm       -- 撤销权限
DROP USER zjm                             -- 删除用户