DML索引，备份恢复以及视图

一、索引

1.概念：
索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。
类似于图书的目录，方便快速定位，寻找指定的内容。

优点：提高查询数据的速度。
缺点：创建和维护索引的时间增加了，同时占用硬盘空间。

2.分类：

1. 普通索引：最基本的索引，它没有任何限制。
2. 唯一索引：与普通索引类似，但索引列的值必须唯一，允许有空值。如果是组合索引，则组合列的值必须唯一。
3. 主键索引：唯一，不允许有空值。
4. 组合索引：多个字段上建立的索引。在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
5. 全文索引：关键字FULLTEXT，全文索引只能创建在CHAR,VARCHAR,TEXT类型的字段上。

3.创建索引
①建表后创建索引：CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(列名[排序类型]);

#普通索引：
CREATE INDEX index_name ON table(column(length));

#唯一索引：
CREATE UNIQUE INDEX index_name ON table(column(length));

#组合索引：
CREATE INDEX index_name ON table(column1,column2);

#全文索引：
CREATE FULLTEXT INDEX index_name ON table(column1,column2);

②修改表方式创建索引：ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT] INDEX 索引名(列名[排序类型]);

#普通索引：
ALTER TABLE table_name ADD INDEX index_name(column(length));

#唯一索引：
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column(length));

#组合索引：
ALTER TABLE table_name ADD INDEX index_name(column1,column2);

#全文索引：
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column1,column2);

③创建表同时创建索引：

CREATE TABLE table_name (
	列1 列类型(length) 约束 ,
	列2 列类型(length) 约束 ,
	列2 列类型(length) 约束 ,
	[UNIQUE|FULLTEXT] INDEX index_name (column(length)...)
);

4.删除索引
语法：DROP INDEX 索引名 ON 表名;

DROP INDEX index_name ON table_name;

二、数据库备份与恢复

1.备份
导出整个数据库结构和数据：
mysqldump -h localhost -uroot -p123456 database > dump.sql

导出整个数据库结构（不包含数据）：
mysqldump -h localhost -uroot -p123456 -d database > dump.sql

导出单个数据表结构和数据：
mysqldump -h localhost -uroot -p123456 database table > dump.sql

导出单个数据表结构（不包含数据）：
mysqldump -h localhost -uroot -p123456 -d database table > dump.sql

2.恢复
①将导出的本地文件导入到指定数据库：

系统命令行：
mysql -uusername -ppassword db1 <tb1tb2.sql

mysql命令行：
mysql>
user db1;
source tb1_tb2.sql;

②恢复整个数据库：
mysql -u b_user -h 101.3.20.33 -p’H_password’ -P3306 < all_database.sql

三、视图

含义：虚拟表，和普通表一样使用。

作用：

1. 预编译表连接
2. 编码更加便捷

视图和表的区别：

	创建关键字	占用物理空间	增删改查
视图	view	只保存了sql逻辑	一般不用
表	table	保存了数据	√

1.创建视图
语法：create view 视图名 as 查询语句;
例：

#创建视图
CREATE VIEW v_emp_dpt_job AS 
SELECT
	last_name,
	department_name,
	job_title 
FROM
	t_mysql_employees e
INNER JOIN t_mysql_departments d ON e.department_id = d.department_id
INNER JOIN t_mysql_jobs j ON e.job_id = j.job_id;

#1、使用视图查询姓名中包含a字符的员工名、部门名和工种信息
SELECT
	* 
FROM
	v_emp_dpt_job 
WHERE
	last_name LIKE '%a%';
	
#2、使用视图查询部门名为‘IT’的员工名、部门名和工种信息
SELECT
	* 
FROM
	v_emp_dpt_job 
WHERE
	department_name = 'IT';
	

2.修改视图
①修改视图查询语句：
语法一：CREATE OR REPLACE VIEW 视图名 AS 查询语句;

语法二：ALTER VIEW 视图名 AS 查询语句;

②修改视图数据：

#插入：
INSERT INTO view_name VALUES(value1,value2...);
#修改：
UPDATE view_name SET last_name = '张无忌' WHERE last_name='张飞';
#删除：
DELETE FROM view_name WHERE last_name = '张无忌';

3.删除视图
删除 MySQL 数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。

语法：drop view 视图名;
例：

DROP VIEW emp_v1,emp_v2,myv3;

4.查看视图
语法1：DESC 视图名;
语法2：SHOW CREATE VIEW 视图名;

注意：

1. 视图被删除后，基于被删除的其他视图或应用无效。
2. 包含分组函数、distinct、group by、having、union或者union all无法更新。
3. 只有视图所有者和具备DROP VIEW权限的用户可以删除视图。
4. 视图定义中的select语句不能包含：order by子句(除非在select语句的选择列中也有一个top子句)、一个top子句、into关键字、引用临时表或表变量。