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、规范化数据库设计
为什么需要数据库设计
糟糕的数据库设计 :
- 数据冗余,存储空间浪费
- 数据更新和插入的异常
- 程序性能差
良好的数据库设计 :
- 节省数据的存储空间
- 能够保证数据的完整性
- 方便进行数据库应用系统的开发
三大范式
不合规范的表设计会导致的问题:
- 信息重复
- 更新异常
- 插入异常
- 删除异常