Day06 狂神说Java-MySQL＞P31-34

P31 SQL编程创建100万条数据测试索引

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

-- 插入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),'1545525@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();
-- 索引在大数据查询时，效率很高
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';
SELECT * FROM student;
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name`='用户9999';

P32 索引原则和明日安排

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

索引的数据结构
InnoDB默认的是Btree

P33 数据库用户管理

权限管理和备份
用户管理
规范数据库设计

系统用户表
用户管理就是对这个表进行增删改查

-- 创建用户
CREATE USER rye IDENTIFIED BY '123456';
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('XXXXXX')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR rye = PASSWORD('123456')
-- 重命名
RENAME USER rye TO antares
-- 用户授权
GRANT ALL PRIVILEGES ON *.* TO antares
-- 查询权限
SHOW GRANTS FOR antares -- 查看指定用户权限
SHOW GRANTS FOR root@localhost
-- 插销权限
REVOKE ALL PRIVILEGES ON *.* FROM antares
-- 删除用户
DROP USER antares

P34 MySQL备份

• 保证重要数据不丢失
• 数据迁移

MySQL数据库备份的方式

• 拷贝物理文件
• 在数据库管理工具中手动导出
• 使用命令行导出mysqldump

导出

mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql

导入

登录的前提下
source D:/a.sql