mysql视图

注意:用索引后能节省检索的时间但是也会影响插入的速度,因为插入数据后系统会按照索引进行排序。如果有大量记录需要插入最好先删除表中的索引然后插入数据。插入后再创建索引。
  普通索引:创建索引时,不附加任何限制条件。
  唯一索引:创建该索引时,限制该索引的值必须是唯一的。主键是一种特殊唯一性索引。
  全文索引:使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在char、varchar或text类型字段上。只有MyISAM存储引擎支持全文检索。默认情况下,全文索引搜索执行方式不区分大小写。但索引列使用二进制排序后可以执行区分大小写的全文索引。
  单列索引:在单个字段上创建索引。只根据该字段进行索引。单列索引可以是普通索引,也可以是唯一性索引,还可以是全文索引。只要保证该索引只对应一个字段即可。
  多列索引:在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段,可以通过这几个字段进行查询。但是,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。如,在表中的id、name和sex字段上建立一个多列索引,那么只有查询条件使用了id字段时该索引才会被使用。
  空间索引:使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上,这样可以提高系统获取空间数据的效率。空间数据类型包括GEOMETRY、POINT、LINESTRING、POLYGON等。目前只有MyISAM存储引擎支持空间检索且索引的字段不能为空值。
  
一、创建表时创建索引格式:create table index1(
id int,
name varcahr(10),
[unique|fulltext|spatial] index | key [别名] ( 属性名1 [(长度)] [asc|desc])
)
注:index和key参数用来指定字段为索引的,两哲选择其中之一就行,作用是一样的。‘别名’也是可选的参数,用来给索引取新名称,属性名1参数知道索引对应的字段的名称,该字段必须是前面定义好的字段,‘长度’是可选参数指索引的长度,必须是字符串类型才可以使用。asc|desc都是可选参数表示升降序排列。
  ##查看索引是否被使用:explain select * from index1 where 索引列=某值 \G,结果中possible_keys 和key值都为索引名,说明该索引已经存在,extra:using index condition 表示已经开始起作用。
例:
1、普票索引
create table index1(
id int,
name varchar(20),
sex boolean,
index(id)
)
2、创建唯一索引
create table index1(
id int unique,
name varchar(20),
sex boolean,
unique index index2_id(id asc)
)
注:这里的id字段可以没有进行唯一性约束,也可以在该字段上成功创建唯一性索引。但是这样可能达不到提高查询速度的目的。
3、创建全文索引
create table index3(
id int,
info varchar(20),
fulltext index index3_info(info)
)engine=MyISAM;
4、创建单列索引
在表的单个字段上创建索引
create table index4(
id int,
subject varchar(30),
index index4_st(subject(10))
)
注:subject字段长度为30而索引长度只有10,目的是为了提高查询速度。对于字符型数据可以不用查询全部信息,而只查询其前面的若干字符信息。
5、创建多列索引
create table index5(
id int,
name varchar(20),
sex char(4),
index index5_ns(name,sex)
)
二、在已经存在的表上创建索引
1、create语句创建索引格式:
create [unique|fulltext|spatial] index 索引名 on 表名 (属性名 [(长度)] [asc|desc]);
例:创建普通索引create index index7_id on example0(id);
2、alter table语句创建索引格式:
alter table 表名 add [unique|fulltext|spatial] index 索引名(属性名 [(长度)] [asc|desc]);
例:创建普通索引 alter table example0 add index index13_name(name(20));
三、删除索引
1、格式:drop index 索引名 on 表名; 注:查看索引名可以用show create table 表名  \G
2、格式:alter table 表名 drop index 索引名; 
---------------------------------
视图是虚拟表,是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。数据库中只存放视图的定义,没有视图中的数据,这些数据都存放在原来的表中。视图不支持输入记录的功能
创建视图格式:
create [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
			VIEW 视图名 [(属性清单)]
			AS SELECT 语句
			[WITH [CASCADED|LOCAL] CHECK OPTION];
注:ALGORITHM是可选参数表示视图选择的算法,属性清单是可选参数指定了视图中各个属性的名词,默认情况下与select语句中查询的属性相同;WITH CHECK OPTION是可选参数表示更新视图时要保证在该视图的权限范围内。
ALGORITHM中undefined选项表示mysql将自动选择所要使用的算法;merge选项表示将使用视图的语句与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分;temptable选项表示将视图的结果存入临时表,然后用临时表执行语句。
CASCADED表示更新视图时要满足所有相关视图和表的条件,该参数为默认值;local表示更新视图时,要满足该视图本身的定义的条件即可。
未指定属性清单的情况下视图的属性名与select语句查询的属性名相同。
注一:创建视图时最好加上WITH CHECK OPTION参数而且最好加上CASCADED参数。这样,从视图上派生出来的新视图后,更新新视图需要考虑其父视图的约束条件。这张方式比较严格可以保证数据安全性。
注二:创建视图要有create view的权限,同时要有select权限。在mysql数据库下面的user表中保存这些权限信息,可以使用select语句查询。
select Select_priv,Create_view_priv from mysql.user where user='用户名';
一、查看视图:describe语句或show  create view语句 或show table status语句或information_ schema数据库下的views表
二、修改视图
1、create or replace 语句来修改视图
create or replace [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
			VIEW 视图名 [(属性清单)]
			AS SELECT 语句
			[WITH [CASCADED|LOCAL] CHECK OPTION];
			注:create or replace语句不仅可以修改已经存在的视图还可以创建新的视图。
2、alter语句修改视图
alter [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
			VIEW 视图名 [(属性清单)]
			AS SELECT 语句
			[WITH [CASCADED|LOCAL] CHECK OPTION];
			注:alter只能修改已经存在的视图。
三、更新视图
更新视图指通过视图来insert、update、delete表中的数据。视图更新都会转换到基本表来更新。更新视图只能更新权限范围内的数据,超出范围就不能更新了。
例:update 视图名 set name='a';更新后基本表内容也被更新
有以下情况不能更新视图:1、包含sum()、count()、max()、min()等函数2、union、union all、distinct、group by、having等关键字。3、常量视图4、视图中的select中包含子查询5、由不可更新的视图导出的视图6、创建视图时,ALGORITHM为TEMPTABLE类型,该类型是系统临时表是不能更新的。7、视图对应的表上存在没有默认值的列,而且该列没有包含在视图里。因为,在更新视图时,这个没有默认值的记录将没有值插入,也没有null值插入。数据库系统是不会允许这样的情况出现的,会阻止视图更新。8、WITH [CASCADED|LOCAL] CHECK OPTION也将决定视图能否更新。local参数表示更新视图时满足该视图本身的定义条件即可;CASCADED参数表示更新视图时要满足所有相关视图和表的条件。没有指明时默认为CASCADED。
四、删除视图
注:删除视图不会影响基本表中的数据
格式:drop view [if exists] 视图名列表 [restrict|cascade]; if exists参数指判断视图存在,如果存在则执行不存在则不执行,“视图名列表”参数表示要删除的视图的名称列表,各视图名用逗号隔开。
一、查询是否存在drop权限
select drop_priv from mysql.user where user='用户名';

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。