7.2测试索引
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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表' -- 插入100万数据.DROP FUNCTION IF EXISTS mock_data;DELIMITER $$CREATE FUNCTION mock_data()RETURNS INT DETERMINISTIC-- RETURNS INT DETERMINISTIC 必须在RETURNS INT后加 DETERMINISTIC 不然会报错BEGINDECLARE num INT DEFAULT 1000000;DECLARE i INT DEFAULT 0;WHILE i < num DOINSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)VALUES(CONCAT('用户', i), '24736743@', 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(); -- 执行此函数 生成一百万条数据SELECT * FROM app_user WHERE `name` = '用户9999' -- 耗时 0.483 secSELECT * FROM app_user WHERE `name` = '用户9999' -- 耗时 0.631 secEXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';SELECT * FROM student-- id_表名_字段名-- CREATE INDEX 索引名 on 表（字段）CREATE INDEX id_app_user_name ON app_user(`name`);SELECT * FROM app_user WHERE `name` = '用户9999' ; -- 耗时0.007 secEXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
CREATE FUNCTION mock_data() RETURNS INT DETERMINISTIC – RETURNS INT DETERMINISTIC 必须在RETURNS INT后加 DETERMINISTIC 不然会报错
没加索引
SELECT * FROM app_user WHERE `name` = '用户9999' -- 耗时 0.631 sec
加了索引 ：时间变短
CREATE INDEX id_app_user_name ON app_user(`name`);SELECT * FROM app_user WHERE `name` = '用户9999' ; -- 耗时0.007 secEXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
索引在小数据量的时候，用处不大。但是在大数据的时候，区别非常明显~
7.3 索引原则
索引不是越多越好
不要对进程变动数据加索引
小数据量不需要索引
索引一般加在常用来查询的字段上
索引的数据结构
数据结构索引背后的数据结构及算法原理地址（面试问题）：/article/p-hdzzoigp-pb.html
Hash 类型的索引
Btree: InnoDB 的默认数据库
8 数据管理和备份
8.1用户管理
SQL yog可视化管理
这里密码填的是：1234567
SQL命令操作
系统用户表：mysql.user
MySQL 执行set password = password(‘newpassword’)方法一直在报错
原因：因为版本不同 高版本要使用
-- 修改密码（修改当前用户密码）alter user '用户名'@'主机名' identified by '新密码';-- 修改密码 （修改指定用户密码）SET PASSWORD FOR '用户名'@'主机名' = '新密码';
测试连接的时候hostaddress不能使用% 这个是默认的值 要使用localhost
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'CREATE USER kuangshen2 IDENTIFIED BY '123456'-- 修改密码（修改当前用户密码）ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';-- 修改密码 （修改指定用户密码）SET PASSWORD FOR 'kuangshen2'@'%' = '123456';SET PASSWORD FOR'root'@'localhost' = '123456';-- 重命名 RENAME USER 原来的名字 TO 新的名字RENAME USER kuangshen2 TO kuangshen3-- 用户授权 ALL PRIVILEGES 全部的权限，库.表-- ALL PRIVILEGES 除了给别人授权，其余的权限都有GRANT ALL PRIVILEGES ON *.* TO kuangshen3 -- 授予kuangshen3全部的权限-- 查询权限SHOW GRANTS FOR kuangshen3 -- 查看指定用户权限SHOW GRANTS FOR root@localhost-- Root 用户权限 ：GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销REVOKE ALL PRIVILEGES ON *.* FROM kuangshen3-- 删除用户DROP USER kuangshen
8.2 MySQL备份
为什么要备份：
保证重要的数据不丢失
数据转移
MySQL数据库备份的方式
直接拷贝物理文件
在sqlyog这种可视化工具中手动导出
在想要导出的表或者库中，右键，选择备份或导出
使用命令行导出 mysqldump 使用命令行(cmd以管理员身份运行才能执行)
#mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名 -- 导出一张表C:\windows\system32>mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql下面这个语句显示导出成功：mysqldump: [Warning] Using a password on the command line interface can be insecure.#mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名-- 导出多张表C:\windows\system32>mysqldump -hlocalhost -uroot -p123456 school student result >D:/b.sql下面这个语句显示导出成功：mysqldump: [Warning] Using a password on the command line interface can be insecure.C:\windows\system32>mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql# 导入一张表# 登录的情况下，切换到指定的数据库（推荐使用）#source 备份文件source d:/a.sql
# 没有导入时命令行操作mysql -u用户名 -p密码 库名<备份文件
备份数据库的原因：
防止数据丢失
把数据库给别人 sql文件即可
9 规范数据库设计
9.1为什么需要设计
当数据库比较复杂的时候，我们就会需要设计了
糟糕的数据库设计：
数据冗余，浪费空间
数据库插入和删除都会很麻烦 有可能产生异常【屏蔽物理外键】
良好的数据库设计：
节省内存空间
保证数据库的完整性
方便我们开发系统
软件开发中，关于数据库的设计
分析需求：分析业务和需要处理的数据库的需求
概要设计：设计关系图E-R图
设计数据库的步骤：（以个人博客举例）
收集信息，分析需求
用户表（用户登录注销，用户的个人信息，写博客，创建分类）分类表（文章分类，谁创建的）文章表（文章的信息）评论表有链表（友情链接）自定义表（系统信息，某个关键的字，或者一些主字段） key :value说说表（发表心情… id…content…create_time） 标识实体类（把需求落地到每个字段） 标识实体之间的关系写博客: user—>blog创建分类: user —>category关注：user—>user友链：links评论:user-user-blog
快捷键 当打出 - - 或者-就会出现小圆点 只打出- 在实心黑点后面 会出现实心方块
