7.视图、索引

• 视图
  
  • 概述
    
    • 视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
      
    • 特点
      
      • 1、简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
        
      • 2、安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
        
      • 3、数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
        
    • 用途
      
      • 保密
        
      • 复杂的查询语句
        
    • 简单视图和复杂视图
      
      • 
        
  • 语法
    
    • 创建视图
      
      • CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
        
        • OR REPLACE：表示替换已有视图
          
        • ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法 ；merge合并；temptable临时表
          
        • select_statement：表示select语句
          
        • [WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内
          
        • 　　cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件
          
        • 　　local表示更新视图的时候，要满足该视图定义的一个条件即可
          
        • TIPS：推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项，可以保证数据的安全性
          
        • 基本格式：create view 视图名 as select语句
          
        • ps：视图关联基表属性时用中文view(属性1，属性2)，不加单引号。但一般不会用中文。
          
        • 如果创建视图时不明确指定视图的列名，那么列名就和定义视图的select子句中的列名完全相同；
          
        • 　　如果显式的指定视图的列名就按照指定的列名。
          
        • 注意：显示指定视图列名，要求视图名后面的列的数量必须匹配select子句中的列的数量。
          
    • 查看视图结构
      
      • describe 视图名;
        
      • show table status like '视图名';查看视图基本信息
        
      • show create view语句查看视图详细信息
        
      • 视图一旦创建完毕，就可以像一个普通表那样使用，视图主要用来查询
        
        • select * from view_name;
          
      • ps:有关视图的信息记录在information_schema数据库中的views表中
        
    • 修改视图
      
      • create or replace view 视图名 as select语句;
        
        • 在视图存在的情况下可对视图进行修改，视图不在的情况下可创建视图
          
      • ps：修改视图是指修改数据库中已存在的表的定义，当基表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致
        
      • 使 ALTER 修改
        
        • alter view 视图名 as select查询语句
          
      • ps：REATE OR REPLACE VIEW 语句不仅可以修改已经存在的机图，还可以创建新的视图，ALTER 语句只能修改已经存在的视图，因此通常选择 ATE OR REPLACE VIEW 语句修改视图。
        
    • 更新视图
      
      • 插入，更新会修改基表！！！实际是对数据表进行操作
        
      • 使用方法：insert、update、delete语句
        
        • 把表名替换为视图名
          
        • 插入insert into 表名[字段名] values (值1,[值2]);
          
        • 更新update 表名 set 修改内容 where 修改条件;（后面这个其实就是一个匹配的条件，set后为修改的数据）
          
        • 删除某一列delete from 表名 [where 字段=值];
          
    • 删除视图
      
      • 删除视图只是删除视图的定义，并不会删除基表的数据
        
      • drop view 视图名
        
    • 查询
      
      • select * from 视图名
        
        • 多表连查
          
• 索引
  预习存储过程和触发器,btree
  练习：
  • 概述
    
    • 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可以提高数据库中特定数据的查询速度。
      
    • 所有 MySQL 列类型都可以被索引，对相关列使用索引是减少查询操作时间的最佳途径。
      
  • 插入查询结果
    
    • 
      
  • 创建索引
    
    • 普通索号｜是最基本的 索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度
      
      • 创建表时加索引直接写在后面index(字段...)即可
        
      • 创建表后加索引
        
        • alter table 表名 add index 索引名(字段...);
          
        • 或create index 表名 on 索引名(字段.....)
          
    • 查看表结构
      
      • show create table 表名
        
  • 查询索引
    
    • show create table 表明；其实就是查看表结构
      
  • 删除索引
    
    • drop inedx 索引名 on表明
      
    • 或者alter table 表名 drop index 索引名
      
  • ps：MySQL 提供了一个 EXPLAIN 命令,它可以对 SELECT 语句进行分析，获取优化器对当前查询的执行计划，以供开发人员针对相关 SQL 进行优化。 在 SELECT 语句前加上 Explain 就可以查看到相关信息
    
  • 索引种类
    
    • 唯一索引unique
      
      • 列值唯一，允许空值
        
        • 使用：关键字都是加在在index前，下面几个都是
          
    • 全文索引fulltext
      
      • 全文索引可以用于全文搜索。只有 MyISAM 存储引擎支持全文索引 ，并且只为 CHAR VARCHAR。TEXT 全文索寻 只能添加到整个字段上，不支持局部（前缀）索引。
        
    • 等等，空间索引spatial
      

• SELECT * FROM course;
  SELECT * FROM score;
  SELECT * FROM student;
  SELECT * FROM teacher;
  SELECT * FROM youtable;
  SELECT cou.c_id ,cou.c_name ,cou.t_id ,sco.s_id ,sco.s_score 
  FROM course cou JOIN score sco ON cou.c_id=sco.c_id;
  #创建视图
  CREATE VIEW newshitu AS SELECT * FROM course;
  SELECT * FROM newshitu;
  #创建视图
  CREATE VIEW shitu1 AS SELECT cou.c_id ,cou.c_name ,cou.t_id ,sco.s_id ,sco.s_score 
  FROM course cou JOIN score sco ON cou.c_id=sco.c_id;
  #查看视图
  SELECT * FROM shitu1;
  SHOW TABLES;
  #删除视图
  DROP VIEW shitu1;
  #修改视图 会修改原表数据
  UPDATE shitu1 SET s_score = 0 WHERE c_id =01 ;
  SHOW FIELDS FROM score;
  #显示当前数据库列表
  SHOW DATABASES;
  PRIMARY
  #显示数据库中的数据表
  SHOW TABLES;
  #显示当前use的数据库名
  SHOW DATABASE();
  #创建视图属性关联基表属性
  CREATE VIEW shitu2(mingzi) AS SELECT s_name FROM student;
  DROP VIEW shitu3;
  #创建视图多个属性关联基表多个属性
  CREATE VIEW shitu3(xuhao,mingzi,shengri,xingbie) AS SELECT s_id,s_name,s_birth,s_sex FROM
  student;
  SELECT * FROM shitu3;
  #索引
  #首先创建一张表的时候加索引：
  CREATE TABLE testTable(
  id INT NOT NULL,
  username VARCHAR(16) NOT NULL,
  INDEX suoyin (id,username)
  );
  #添加元素
  INSERT INTO testTable VALUES(1,"张飞");
  INSERT INTO testTable VALUES(2,"赵云");
  INSERT INTO testTable VALUES(3,"关羽");
  INSERT INTO testTable VALUES(4,"黄忠");
  INSERT INTO testTable VALUES(5,"马超");
  INSERT INTO testTable VALUES(6,"张辽");
  SELECT * FROM testTable;
  DESC testTable;
  DROP TABLE testTable;
  #创建表之后加索引：
  ALTER TABLE testTable ADD INDEX index_1(id);
  CREATE INDEX index_2 ON testTable(username);
  #根据索引查询
  SELECT * FROM testTable WHERE username LIKE '张%';
  #删除索引
  DROP INDEX index_2 ON testTable;
  ALTER TABLE testTable DROP INDEX index_1;
  #查询语句使用索引
  EXPLAIN SELECT * FROM testTable WHERE id = 1;