mysql数据库的使用（下）

1、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
提取句子主干，就可以得到索引的本质：索引是数据结构。

1.1、索引的分类

• 主键索引 (Primary Key)
• 唯一索引 (Unique)
• 常规索引 (Index)
• 全文索引 (FullText)

1.2、主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

1.3、唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别：

• 主键索引只能有一个
• 唯一索引可能有多个

1.4、常规索引

作用 : 快速定位特定数据

注意 :

• index 和 key 关键字都可以设置常规索引
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

1.5、全文索引

作用 : 快速定位特定数据

全文索引的版本、存储引擎、数据类型的支持情况

• MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
• MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
• 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

如何创建索引

-- 方法一：创建表时
CREATE TABLE 表名 ( 
字段名1 数据类型 [完整性约束条件…], 
字段名2 数据类型 [完整性约束条件…], 
[UNIQUE | FULLTEXT ] INDEX | KEY 索引名 (字段名 [ASC |DESC]) 
);

-- 方法二：表已经存在（使用CREATE创建索引）
CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名 (字段名 [ASC |DESC]);

-- 方法三：表已经存在（使用ALTER创建索引）
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT] INDEX 索引名 (字段名 [ASC |DESC]);

删除主键索引

ALTER TABLE 表名 DROP PRIMARY KEY;

删除其他索引

DROP INDEX 索引名 ON 表名字;

显示索引信息

SHOW INDEX FROM student;

EXPLAIN : 分析SQL语句执行性能

explain select * from student where studentname='张伟';

会显示如下：

关于explain

使用全文索引

-- 增加全文索引
ALTER TABLE student ADD FULLTEXT INDEX studentname (StudentName);

-- 全文搜索通过 MATCH() 函数完成。 -- 搜索字符串做为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个 记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文 本之间的相似性尺度。 
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('张');

1.6、测试索引

-- 建表app_user
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=utf8;

-- 批量插入数据：100w条
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), '24736743@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;$$
DELIMITER ;
SELECT mock_data();

delimiter

delimiter是mysql的分隔符，在mysql客户端中分隔符默认是分号（;）。
如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的分隔符。

explain select * from app_user where name='用户9999';

-- 给字段name添加索引
create index id_app_user_name on app_user(name);

explain select * from app_user where name='用户9999';

比较rows可以发现，添加索引后只查询了一次。

1.7、索引准则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

索引的本质

2.权限管理

2.1、用户管理

基本命令

用户信息表：mysql.user

-- 创建用户
create user 用户名 identified by '密码';

-- 为当前用户设置密码
SET PASSWORD = PASSWORD('密码');
-- 为指定用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码');

-- 重命名用户
RENAME USER 旧用户名 TO 新用户名;

-- 删除用户
DROP USER 用户名;

-- 用户授权
GRANT 权限 ON 表名 TO 用户名
-- 给用户liucong授予student表的所有权限
grant all privileges on student to liucong;

-- 查看权限
show grants for 用户名;

-- 查看当前用户权限 
SHOW GRANTS; 
-- 或 SHOW GRANTS FOR CURRENT_USER;

-- 撤消权限
revoke 权限 on 表名 from 用户名;

2.2、MySQL备份

为什么要数据库备份：

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

MySQL数据库备份方法：

• mysqldump备份工具
• 数据库管理工具，进行转储
• 直接拷贝数据库文件和相关配置文件

mysqldump（命令行下执行）

导出：

-- 1. 导出一张表
mysqldump -u用户名 -p密码 库名 表名 >物理磁盘位置/文件名(D:/a.sql)

-- 2. 导出多张表
mysqldump -u用户名 -p密码 库名 表1 表2 表3 >物理磁盘位置/文件名(D:/a.sql)

-- 3. 导出所有表
mysqldump -u用户名 -p密码 库名 >物理磁盘位置/文件名(D:/a.sql)

导入：

-- 1. 在登录mysql的情况下：
source 备份文件
--如：source d:/a.sql

-- 2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件

3、视图

什么是视图？

视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。
数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍放在原来的基本表中。
视图可以像基本表一样进行增、删、改和查。

3.1、定义视图

建立视图

create view 视图名 [ 列名 [,列名]···]
as 子查询
[with check option];

with check option表示对视图进行update、insert和delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

在下列情况下必须明确指定组成视图的所有列名：

• 某个目标列不是单纯的属性名，而是聚合函数或表达式
• 多表连接时选出了几个同名列作为视图的字段
• 需要在视图中为某个列启用新的更合适的名字

create view student_view 
as 
select studentno,studentname,sex 
from student;

删除视图

drop view 视图名 [cascade];

使用cascade级联删除语句会把该视图和由它导出的所有视图一起删除。

注意：

• 基本表删除之后，由该基本表导出的所有视图均无法使用
• 这些视图的定义没有从字典中清除，删除这些视图定义需要显示地使用drop view语句

3.2、查询视图

select 字段1[,字段2···]
from 视图名
where 条件

对视图的查询会转换成等价的对基本表的查询，然后再执行修改了的查询。这一转换过程称为视图消解。

select studentno,sex
from student_view
where studentname='张三';
--转换为
select studentno,sex
from student
where studentname='张三';

3.3、更新视图

通过视图来插入、删除和修改数据。对视图的更新操作最终会转换为对基本表的更新操作。

为了防止用户通过视图对数据进行增加、删除、修改时，对不属于视图范围内的基本表数据进行操作，可在定义视图时加上with check option子句。这样在更新时，数据库管理系统会检查视图定义中的条件，若不满足条件则拒绝执行该操作。

-- 修改数据
update student_view
set studentname='张潇'
where studentno='1001';

-- 增加数据
insert
into student_view
values('1005','肖湾',0);

-- 删除数据
delete
from student_view
where studentno='1005';

为什么使用视图？

• 视图能简化用户的操作，使用户把注意力集中在所关心的数据上。
• 视图使用户能以多种角度看待同一数据。
• 视图能对机密数据提供安全保护，对用户不展示机密数据。

4、规范化数据库设计

为什么需要数据库设计

糟糕的数据库设计 :

• 数据冗余,存储空间浪费
• 数据更新和插入的异常
• 程序性能差

良好的数据库设计 :

• 节省数据的存储空间
• 能够保证数据的完整性
• 方便进行数据库应用系统的开发

三大范式

不合规范的表设计会导致的问题：

• 信息重复
• 更新异常
• 插入异常
• 删除异常

三大范式的概念