MySQL中的视图、索引

MySQL视图

---

1.MySQL视图（view）：是一种虚拟存在的表，和真实表一样，视图也是由列和行构成的，但是视图中并不存在实际的数据，实际数据在原有表中。

​ 数据库中只存放了视图的定义，并没有存放视图中的数据库，这些数据都存放在定义视图查询所引用真实表中。

USE myschool;
SELECT * FROM student;
-- 创建语法 CREATE VIEW 视图name AS select语句
CREATE VIEW vi_student_grade
AS
SELECT student.studentNo,
			 student.studentName,
			 student.sex,
			 FLOOR(DATEDIFF(NOW(),student.bornDate)/365.2422) as age,
			 grade.gradeName
FROM student
JOIN grade
ON student.gradeId=grade.gradeId
WHERE student.sex='男';

-- 删除视图
DROP VIEW vi_student_grade

-- 修改真实表中数据，视图中数据也会改变
UPDATE student set studentName='韩晨光' WHERE studentNo=30013;

-- 不可执行
DELETE FROM vi_student_grade WHERE studentNo=30013;

-- 查询视图
SELECT * FROM vi_student_grade WHERE age>=26

-- 查看已创建的视图信息
SHOW CREATE VIEW vi_student_grade;

-- 修改视图
ALTER VIEW vi_student_grade
AS
SELECT student.studentNo,
			 student.studentName,
			 student.sex,
			 FLOOR(DATEDIFF(NOW(),student.bornDate)/365.2422) as age,
			 grade.gradeName
FROM student
JOIN grade
ON student.gradeId=grade.gradeId

​ 使用视图查询数据时，数据库会从真实的表中取出对应数据，因此视图中的数据依赖真实表中的数据的，一旦真实表中数据发生改变，视图中的数据也会发生改变。

​ 视图可以从原有表中选取对用户有用的信息，对用户没有用的信息或者用户没有权限了解的信息，都可以屏蔽掉，作用类似于筛选，优点：使应用简化，也保证了数据的安全。

2.视图并不同于数据表，区别：

a. 视图不是数据库中的真实表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的

b. 存储在数据库中的查询操作SQL 语句定义了视图的内容，列的数据 和行的数据都来自视图查询的实际表

c. 视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所对应的真实表中

d. 视图是数据的窗口，而表是内容，表是实际数据存放的单位，而视图只是以不同的方式展示数据

e. 视图是查看数据的一种方法，可以查询的数据由 查询SQL语句决定的，从安全的角度来看视图的数据更安全，使用视图的用户不接触数据表，不知道表结构

f. 视图的建立和删除只影响视图本身，不影响对应的基本表

3.视图的优点

视图与表在本质上虽然不同，但是视图经过定义以后，结构和形式和表一样，可以进行查询，修改，更新，删除等操作

a.定制用户数据

b.简化操作

c.提高数据的安全性

d.共享所有数据

e.更改数据的格式

f.重用SQL语句

4.使用视图要注意的问题：

a.创建视图需要有足够的权限

b.创建视图的数目没有限制

c.视图可以嵌套，即从其他视图中检索数据 查询 来形成新的视图

d.视图不能使用索引，不能有关联的触发器，默认值，规则

e.视图可以和表一起使用

f.ORDER BY 子句可以用在视图中，但若是视图检索数据的select语句中也包含ORDER BY 子句，则该视图中的ORDER BY 子句将被覆盖

g.视图的更新：不可更新的内容

聚合函数 SUM ，MIN，MAX， COUNT、DISTINCT 关键字、GROUP BY 子句、HAVING 子句等，更新视图 会影响原有表数据

---

---

---

索引

---

是一种有效的组合数据的方式，为了快速的查找到指定的数据，作用是可以大大提高数据库的检索速度、改善数据的性能

MySQL索引按照存储类型分类： B树索引（InnoDB，MYISAN）和哈希索引

常用的索引类型：

1. 普通索引：基本索引类型，允许在定义索引的列中插入重复值和空值

2. 唯一索引：索引列的数据不能重复，允许有空值

3. 主键索引：主键列中的每个值都是非空的、唯一的，一个主键将自动的创建主键索引

4. 复合索引：将多列合作作为索引

5. 全文索引：支持值得全文查找，允许有重复值和空值

6. 空间索引：堆空间数据类型的列建立的索引

常规索引（INDEX）作用在于可以快速定位数据，index和key关键字都可以设置常规索引，应该加在查询条件的字段上，不宜添加太多的常规索引，影响数据的插入、删除、修改等操作

-- 索引：
-- 主键索引（只能有一个）
CREATE TABLE greade(
	GradeId int(11) PRIMARY KEY,
	# ... ...
	# 添加字段后 添加主键
	PRIMARY KEY(`GradeId`)
	UNIQUE KEY `GradeId`(GradeId)# 唯一索引
)

# 创建表后 追加主键

ALTER TABLE greade add PRIMARY KEY(`GradeId`);

-- 唯一索引（可以有多个）
ALTER TABLE student add UNIQUE INDEX(identityCard);

-- 常规索引（INDEX）
CREATE TABLE result(
	-- id int(10) ...
	index/key `id` (studentNo,subjectNo)
)
ALTER TABLE `result` ADD INDEX `ind`(studentNo,subjectNo);
SELECT * FROM result WHERE studentNo=1000

-- 全文索引，只能用于MyISAM类型的数据表
-- 只能用于char、VARCHAR、text、数据列类型，适合大型数据集

-- 创建 用于演示全文索引的表
CREATE TABLE  IF NOT EXISTS articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT (title,body)
)ENGINE = MyISAM;

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

-- 修改表类型
ALTER TABLE student_test engine = MyISAM;
ALTER TABLE student_test add FULLTEXT(studentName);
SELECT * FROM student_test WHERE MATCH(studentName) AGAINST('李文才');
SELECT * FROM articles

-- 使用全文索引
SELECT * FROM articles where MATCH(title,body) AGAINST('database');

-- EXPLAIN 分析函数，分析当前定义的内容
EXPLAIN  SELECT * FROM articles where MATCH(title,body) AGAINST('database');

-- 总结 

-- 添加索引语法格式
ALTER TABLE 表名 ADD 索引类型(列名)

-- 删除索引语法格式 
DROP INDEX 索引名称 ON 表名
ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE 表名 DROP PRIMARY KEY
-- 查询当前表所拥有的索引
SHOW INDEX FROM student

注意准则： 1.索引不是越多越好；2.不要多经常变动的列添加索引；3.小量数据也建议不添加索引；4.索引一般添加在查询条件的列上