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、规范化数据库设计

为什么需要数据库设计

糟糕的数据库设计 :

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

良好的数据库设计 :

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

三大范式

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

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

三大范式的概念