MySQL权限管理 1、用户管理SQL命令操作用户表：mysql.user本质：对这张表进行增删改查-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码' CREATE USER ceshi IDENTIFIED BY '123456'; -- 修改密码（修改当前用户密码） SET PASSWORD = PASSWORD('1234567'); -- 修改指定用户密码 SET PASSWORD FOR ceshi = PASSWORD('1234567'); -- 重命名 RENAME USER 原用户名 TO 新用户名; RENAME USER ceshi TO ceshi01; -- 用户授权 GRANT ALL PRIVILEGES 全部的权限 库.表 -- ALL PRIVILEGES 此处的授权与root用户有区别（除了给别人授权，其他的都能干） GRANT ALL PRIVILEGES ON *.* TO ceshi; -- 查询权限 SHOW GRANTS FOR ceshi; -- 查看指定用户的权限 SHOW GRANTS FOR root@localhost; -- root用户的权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销 REVOKE ALL PRIVILEGES ON *.* FROM ceshi; -- 删除用户 DROP USER ceshi;

MySQL备份 为什么要备份：保证重要的数据不丢失数据转移MySQL数据备份的方式：直接拷贝物理文件在可视化工具中手动导出使用命令行导出 mysqldump# 导出 # mysqldump -h 主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql # mysqldump -h 主机 -u用户名 -p密码 数据库 表名1 表名2 表名3 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student student01 student02 >D:/b.sql # mysqldump -h 主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql #导入 #登录的情况下，切换到指定数据库 # source 备份文件 source D:/a.sql

索引 MySQl官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干：就可以得到索引的本质：索引是数据结构。1、索引的分类在一个表中，主键索引只能有一个，唯一索引可以有多个主键索引 （PRIMARY KEY）唯一的标识，主键不可重复，只能有一个列作为主键唯一索引 （UNIQUE KEY）避免重复的列出现，唯一索引可以重复，多个列都可以标识为 唯一索引常规索引（KEY/INDEX）-默认的，通过index，key关键字来设置全文索引（FullText）在特定的数据库引擎下才有，MyISAM快速定位数据基础语法-- 索引的使用 -- 1、在创建表的时候给字段增加索引 -- 2、创建完毕后，增加索引 -- 显示所有的索引信息 SHOW INDEX FROM account; -- 增加一个全文索引 （索引名） 列名 ALTER TABLE shop.account ADD FULLTEXT INDEX `name`(`name`); -- EXPLAIN 分析sql执行的状况 EXPLAIN SELECT * FROM account; -- 非全文索引 EXPLAIN SELECT * FROM account WHERE MATCH(name) AGAINST('a');2、测试索引CREATE TABLE `test_user`( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) DEFAULT '' COMMENT '用户名', email VARCHAR(50) NOT NULL COMMENT '邮箱', phone VARCHAR(50) DEFAULT '' COMMENT '手机号', gender TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0：男，1：女）', passwd 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=utf8 COMMENT '用户表'; -- 插入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 test_user(`name`,`email`,`phone`,`gender`,`passwd`,`age`) VALUES(CONCAT('用户',i),'281463547@qq.com', CONCAT('13',FLOOR(RAND()*((999999999-1000000)+1000000))), FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100)); SET i=i+1; END WHILE; RETURN i; END; SELECT mock_data(); SELECT * FROM test_user WHERE `name` = '用户19998'; -- 查询时间3.622s SELECT * FROM test_user WHERE `name` = '用户19998'; -- 查询时间2.349s EXPLAIN SELECT * FROM test_user WHERE `name` = '用户19998'; -- rows 992262 -- id_表名_字段名 -- 语法： CREATE INDEX 索引名 ON 表（字段名） CREATE INDEX id_test_user_name ON test_user(`name`); -- 创建用时18.442s SELECT * FROM test_user WHERE `name` = '用户19998'; -- 创建索引后查询用时0.028s SELECT * FROM test_user WHERE `name` = '用户19998'; -- 0.026s EXPLAIN SELECT * FROM test_user WHERE `name` = '用户19998'; -- rows 1注：索引在小数据量的时候用处不大，但在大数据的时候，区别十分明显3、索引原则索引不是越多越好不要对经常变动的数据加索引小数据量的表不需要加索引索引一般加在经常查询的字段上索引的数据结构Hash类型的索引BTree：InnoDB的默认数据结构

数据表的类型 -- 关于数据库引擎 /*INNODB 默认使用 MYISAM 早年使用 */ MYISAMINNODB事务支持NoYes数据行锁定NoYes外键约束NoYes全文索引YesNo表空间的大小较小较大，约为2倍常规使用操作：MYISAM 节约空间，速度较快INNODN 安全性高，事务的处理，多表多用户操作

数据库事务ACID 事务：要么都成功，要么都失败1、A给B转账 A 1000 -->200 B 2002、B收到A的钱 A 800 --> B 400将sql放在一个批次中去执行事务ACID原则：原子性，一致性，隔离性，持久性原子性（Atomicity）要么都成功，要么都失败一致性（Consistency）事务前后的数据完整性要保持一致（A、B一共有1000块钱，不论A和B怎么转账，最终两人钱的总和是1000块）隔离性（Isolation）多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离持久性（Durability）事务一旦提交则不可逆，事务提交的结果被持久化存储到数据库中隔离所导致的一些问题脏读：指一个事务读取了另一个事务未提交的数据。不可重读：在一个事务内读取表中的某一行数据，多次读取的结果不同。（这个不一定是错误，需具体场景具体分析）虚读（幻读）：指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。-- 事务 -- mysql是默认开启事务自动提交的 SET autocommit = 0; /*关闭*/ SET autocommit = 1; /*开启*/ -- 手动处理事务 -- 1、关闭自动提交 SET autocommit = 0; -- 2、事务开启 START TRANSACTION; -- 标记一个事务的开始，从这之后的sql都在同一个事务内 INSERT xx INSERT xxx -- 提交：持久化（成功） COMMIT -- 回滚：返回之前的状态（失败） ROLLBACK -- 3、事务结束 SET autocommit = 1; -- 开启自动提交 SAVEPOINT 保存点名称 -- 设置一个事务的保存点 ROLLBACK TO SAVEPOINT -- 回滚到保存点 RELEASE SAVEPOINT 保存点名称 -- 撤销保存点模拟转账实例-- 转账 CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci; use shop; CREATE TABLE `account`( id int(11) NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, money DECIMAL(9,2) NOT NULL, PRIMARY KEY (id) )ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO account(name,money) VALUES('a',1000.00),('b',500.00); -- 模拟转账：事务 SET autocommit = 0; -- 关闭自动提交 START TRANSACTION -- 开启事务（一组事务） UPDATE account SET money=money-500 where name='a'; -- a减500 UPDATE account SET money=money+500 where name='b'; -- b加500 COMMIT; -- 提交事务，提交之后数据被持久化存储 ROLLBACK; -- 回滚 SET autocommit = 1; -- 恢复默认提交

MD5加密算法 MD5：主要增强算法复杂度和不可逆性。-- 测试MD5 CREATE TABLE `testmd5`( id INT(11) NOT NULL, name VARCHAR(20) NOT NULL, pwd VARCHAR(255) NOT NULL )ENGINE=INNODB DEFAULT CHARSET=utf8; -- 明文密码 INSERT INTO testmd5 VALUES(1,'孙笑川','12345'),(2,'刘波','12345'),(3,'Giao哥','12345'); -- 加密 UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1; -- 插入的时候加密 INSERT INTO testmd5 VALUES(4,'孙笑川',MD5('12345')); -- 如何校验：将用户传递进来的密码，进行加密，然后比对加密后的值 SELECT * FROM testmd5 WHERE name='孙笑川' AND pwd=MD5('12345');

Ubuntu 18.04安装MySQL 5.7 1、安装mysql服务sudo apt-get install mysql-server-5.7 2、安装依赖：sudo apt install libmysqlclient-dev 3、检查状态：sudo netstat -tap | grep mysql检查一下mysql服务是否处在监听状态4、设置mysql的密码切换到root用户下再输入mysql5、切换数据库输入use mysql;切换数据库update user set plugin = 'mysql_native_password' where user='root'; update user set authentication_string=PASSWORD('password') where user='root'; 设置完成root密码完成以上步骤先别退出mysql，输入FLUSH PRIVILEGES;刷新权限,再输入quit退出mysql6、重启mysql服务sudo /etc/init.d/mysql restart 或者service mysql restart 8、设置允许root用户远程登录mysql(1)输入命令mysql -uroot -p 输入密码之后正常登录数据库(2)输入use mysql;切换一下数据库(3)为用户授权GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES; 完成以上步骤之后打开Navicat客户端连接服务器里的mysql依然会报2003错误，如下图这时，切换到/etc/mysql/mysql.conf.d/目录下，输入sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf编辑mysql配置文件，找到bind-address = 127.0.0.1这一行，并将其注释掉，完成以上步骤之后，重启mysql服务，同时还需要放行3306端口，输入ufw allow 3306此时回到Navicat客户端，即可连接成功。

新增数据时自动添加时间 1、MySql将create_time的类型改为datetime，左下角勾选根据当前时间戳更新，默认值填写为CURRENT_TIMESTAMP2、SpringBoot Jpa(以userinfo表为例)@CreatedDate @Column(name = "createtime",updatable = false,nullable = false) @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") private Date createtime; //添加记录时间并且在XXXApplication中添加@EnableJpaAuditing@SpringBootApplication @EnableJpaAuditing public class XXXApplication { public static void main(String[] args) { SpringApplication.run(XXXApplication.class, args); } }