mysql 索引类型 锁类型_Mysql数据库的数据类型、索引、锁、事务和视图

Mysql数据库的数据类型、索引、锁、事务和视图

数据的类型

1)数据类型：

数据长什么样？

数据需要多少空间来存放？

系统内置数据类型和用户定义数据类型

2)MySql 支持多种列类型：

数值类型

日期/时间类型

字符串(字符) 类型

3)选择正确的数据类型对于获得高性能至关重要，三大原则：

更小的通常更好，尽量使用可正确存储数据的最小数据类型

简单就好，简单数据类型的操作通常需要更少的CPU 周期

尽量避免NULL，包含为NULL的列，对MySQL更难优化

4)整型

tinyint(m) 1节个字节，范围(-128~127)

smallint(m) 2节个字节，范围(-32768~32767)

mediumint(m) 3节个字节，范围(-8388608~8388607)

int(m) 4节个字节，范围(-2147483648~2147483647)

bigint(m) 8节个字节，范围(+-9.22*10 的18 次方)

integer

decimal

注：

1》取值范围如果加了unsigned ，则最大值翻倍，如tinyint unsigned的取值范围为(0~255)。

2》int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围。

3》MySQL可以为整型类型指定宽度，例如Int(11) ，对绝大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具(例如MySQL 命令行客户端)用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的。

5)浮点型，近似值

float(m,d) 单精度浮点型8位精度(4字节)m总个数，d小数位

double(m,d) 双精度浮点型16位精度(8字节)m总个数，d小数位

real

bit

示例

设一个字段定义为float(6,3) ，如果插入一个数123.45678,实 实际数据库里存的是123.457 ，但总个数还以实际为准，即6位。

4)定点数

1》在数据库中存放的是精确值, 存为十进制。

2》decimal(m,d)参数m<65是总个数，d<30且d

3》MySQL5.0 和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如，decimal(18,9) 小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节

4》浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节

5》因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal。例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal。

5)字符型(char,varchar,_text)

char(n) 固定长度，最多255个字符，不区分大小写

varchar(n) 可变长度，最多65535个字符

tinytext 可变长度，最多255个字符

text tinytext，text，mediumtext，longtext。可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

binary(M) 固定长度，可存二进制或字符，允许长度为0-M字节，区分大小写

varbinary(M) 可变长度，可存二进制或字符，允许长度为0-M字节

blobtinyblob，blob，mediumblob，longblob

char和varchar：

1》char(n)若存入字符数小于n ，则以空格补于其后，查询之时再将空格去掉。所以char 类型存储的字符串末尾不能有空格，varchar不限于此。

2》char(n)固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节(n< n>255) ，所以varchar(4), 存入3 个字符将占用4 个字节。

3》char类型的字符串检索速度要比varchar 类型的快

varchar和text：

1》varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节(n< n>255) ，text是实际字符数+2个字节。

2》text类型不能有默认值

3》varchar可直接创建索引，text创建索引要指定前多少个字符，varchar查询速度快于text。

6)二进制数据：blod

BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写。

BLOB存储的数据只能整体读出

TEXT可以指定字符集，BLOB不用指定字符集

7)日期时间类型

date 日期 '2008-12-2'

time 时间 '12:25:36'

datetime 日期时间 '2008-12-2 22:06:44'

timestamp 自动存储记录修改时间

year(2), year(4)年份

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间

8)修饰符

NULL 数据列，可包含NULL值

NOT NULL 数据列，不允许包含NULL值

DEFAULT 默认值

PRIMARY KEY 主键

UNIQUE KEY 唯一键

CHARACTER SET name 指定一个字符集

AUTO_INCREMENT 自动递增，适用于整数类型

UNSIGNED 无符号

类型修饰符：

字符型：not null，null，defalut ‘string’，characet set ‘charset’，collation ‘collocation'

整型：not null， null， defalut value, auto_increment, unsigned

日期时间型：not null， null， default

9)内建类型：

enum枚举,

enum('sun','mon','tue','wed')

set集合

数据库的索引

1.数据库索引

1)数据库索引

1》数据库的索引使数据库程序无须对整个表进行扫描，就可以在其中找到所有数据。

2》索引是某个表中一列或者若干列值的集合，以及是物理标识这些值的数据页的逻辑指针清单。

3》提取表上字段中的数据来创建索引，构建出一个独特的数据结构。也就是把表中某个或某些字段的数据提取出来另存为一个特定数据结构的数据，这些字段是在where字句中的用到的字段。

2)索引的作用

1》设置了合适的索引后，数据库利用各种快速的定位技术，就能够大大加快查询速率，特别是表特别大的时候，或者是涉及多个表的时候。

2》可以降低数据库的I/O成本，并且索引还可以降低数据库的排序成本。

3》通过创建唯一性索引保证数据表数据的唯一性，可以加快表和表间的连接。

4》在使用分组和排序时，可以大大减少分组和排序时间。

5》加速查询操作，副作用是降低写操作性能。

3)索引类型：b+ tree，hash

b+ tree索引：

顺序存储，每一个叶子结点到根结点的距离相同；

左前缀索引，适合于范围类型的数据查询；

适用于b+ tree索引的查询类型：全键值、键值范围、键前缀；

全值匹配：精确匹配某个值；

where column = 'value';

匹配最左前缀：只精确匹配起头的部分；

wehre column like 'prefix%';

匹配范围值：

精确匹配某一列，范围匹配另一列；

只用访问索引的查询：覆盖索引；

index(name)

select name from students where name like 'l%';

不适用b+ tree索引：

如果查询条件不是从最左侧列开始，索引无效；

index(age,fname), where fname='jerry'; , where age>30 and fname='smith';

不能跳过索引中的某列；

index(name,age,gender)

where name='black' and age > 30;

where name='black' and gender='f';

如果查询中的某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询；

where age>30 and fname='smith';

hash索引：

基于哈希表实现，特别适用于值的精确匹配查询；

适用场景：

只支持等值比较查询，例如=, in(), <=>

不用场景：

所有非精确值查询；mysql仅对memory存储引擎支持显式的hash索引；

4)索引的细分

1》普通索引

这是最基本的索引类型，而且没有唯一性之类的限制。

2》唯一性索引

索引的列的所有值都只能出现一次，即必须唯一，但可以为空NULL。

3》主键索引、辅助索引

主键索引是一种特殊的唯一索引，必须指定primary key，具有唯一性的同时不能为空null。

4》全文索引

mysql从3.23.23版开始支持全文索引和全文检索。在mysql中，全文索引的类型为fulltext，全文索引可以在varchar或text类型的列上创建。

5》单列索引与多列索引

索引可以是单列上创建索引，也可以是在多列上创建索引。

6》聚集索引、非聚集索引

索引是否与数据存在一起；

7》稠密索引、稀疏索引

是否索引了每一个数据项；

5)索引的优点

降低需要扫描的数据量，减少io次数；

可以帮助避免排序操作，避免使用临时表；

帮助将随机io转为顺序io；

2.创建索引的原则

1)表的主键、外键必须有索引

2)数据超过300行的表应该有索引

3)经常与其他表进行连接的表，在连接字段上应该建立索引

4)唯一性太差的字段不适合建立索引

5)更新太频繁的字段不适合建立索引

6)经常出现在where字句中的字段，特别是大表的字段，应该建立索引

7)索引应该建立在选择性高的字段上

8)索引应该建立在小字段上，对于大的文本字段甚至超长字段，不要建立索引

3.索引的管理

1)创建索引

help create index

create [online|offline] [unique|fulltext|spatial] index index_name [index_type] on tbl_name (index_col_name,...) [index_option] ...

index_col_name:字段

col_name [(length)] [asc | desc]

index_type:

using {btree | hash}

index_option:

key_block_size [=] value|index_type| with parser parser_name|comment 'string'

1》创建普遍索引

create index index_name on tbl_name(字段);

alter table table_name add index index_name (column_list);

2》创建唯一性索引

create unique index index_name on tal_name(字段);

alter table table_name add unique (字段);

3》创建主键索引

create table tbl_name([...],primary key(字段));##用于建表时创建主键

alter table tal_name add primary key(字段);##建表时忘记创建主键可以进行修改

2)查看索引

show {index | indexes | keys} {from | in} tbl_name [{from | in} db_name] [where expr];

show index from tbl_name;

show keys from tbl_name;

示例：

show index from mytable from mydb;

show index from mydb.mytable;

Table：表的名称。

Non_unique：如果索引不能包括重复词，则为0。如果可以，则为1。

Key_name：索引的名称。

Seq_in_index：索引中的列序列号，从1开始。

Column_name：列名称。

Collation：列以什么方式存储在索引中。在MySQL中，有值‘A’(升序)或NULL(无分类)。

Cardinality：索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。

Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO。

Index_type：用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

Cmment：注释，说明

3)删除索引

drop [online|offline] index index_name on tbl_name;

drop index index_name on tbl_name;

alter table tbl_name drop index index_name;

alter table tbl_name drop primary key;##每个表只允许有一个primary key，所以不用指明名称。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

4.对MySQL数据库建立索引的事项及提高性能的手段

1)注意事项：

首先，应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据，在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

其次，在对建立索引的时候要对表进行加锁，因此应当注意操作在业务空闲的时候进行。

2)性能调整方面：

首先，要考虑因素便是磁盘I/O。物理上，应当尽量把索引与数据分散到不同的磁盘上(不考虑阵列的情况)。逻辑上，数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。

其次，我们知道，在建立索引的时候要对表进行全表的扫描工作，因此，应当考虑调大初始化参数db_file_multiblock_read_count的值。一般设置为32或更大。

再次，建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作，因此，应当调整排序区的大小。9i之前，可以在session级别上加大sort_area_size的大小，比如设置为100m或者更大。9i以后，如果初始化参数workarea_size_policy的值为TRUE，则排序区从pga_aggregate_target里自动分配获得。

最后，建立索引的时候，可以加上nologging选项。以减少在建立索引过程中产生的大量redo，从而提高执行的速度。

3)高性能索引策略：

(1) 在where中独立使用列，尽量避免其参与运算；

where age+2 > 32 ;

(2) 左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估

索引选择性：不重复的索引值和数据表的记录总数的比值；

(3) 多列索引：

and连接的多个查询条件更适合使用多列索引，而非多个单键索引；

(4) 选择合适的索引列次序：选择性最高的放左侧；

5.设计MySql索引的时候有一下几点注意：

1，创建索引

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

2，复合索引

比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3，索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4，使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

5，排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6，like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

7，不要在列上进行运算

8，不使用NOT IN和操作

NOT IN和大于等于操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id

6.explain 来分析索引的有效性

explain来分析索引有效性：

help explain

explain [explain_type] select select_options

explain_type:

extended | partitions

explain tbl_name

输出结果：

id: 1

select_type: simple

table: students

type: const

possible_keys: primary

key: primary

key_len: 4

ref: const

rows: 1

extra:

id：当前查询语句中，第几个select语句的编号；

复杂的查询的类型主要三种：

简单子查询

用于from中的子查询

联合查询

注意：联合查询的分析结果会出现一个额外的匿名临时表；

select_type：查询类型：

简单查询：simple

复杂查询：

简单子查询：subquery

用于from中的子查询：derived

联合查询中的第一个查询：primary

联合查询中的第一个查询之后的其它查询：union

联合查询生成的临时表：union result

table：查询针对的表；

type：关联类型，或称为访问类型，即mysql如何去查询表中的行

all：全表扫描；

index：根据索引的顺序进行的全表扫描；但同时如果extra列出现了"using index”表示使用了覆盖索引；

range：有范围限制地根据索引实现范围扫描；扫描位置始于索引中的某一项，结束于另一项；

ref：根据索引返回的表中匹配到某单个值的所有行(匹配给定值的行不止一个)；

eq_ref：根据索引返回的表中匹配到某单个值的单一行，仅返回一个行，但需要与某个额外的参考值比较，而不是常数；

const，system：与某个常数比较，且只返回一行；

possiable_keys：查询中可能会用到的索引；

key：查询中使用的索引；

key_len：查询中用到的索引长度；

ref：在利用key字段所显示的索引完成查询操作时所引用的列或常量值；

rows：mysql估计出的为找到所有的目标项而需要读取的行数；

extra：额外信息

using index：使用了覆盖索引进行的查询；

using where：拿到数据后还要再次进行过滤；

using temporary：使用了临时表以完成查询；

using filesort：对结果使用了一个外部索引排序；

7.查询缓存

缓存：k/v

key：查询语句的hash值

value：查询语句的执行结果

如何判断缓存是否命中：

通过查询语句的哈希值判断：哈希值考虑的因素包括

查询本身、要查询数据库、客户端使用的协议版本、...

select name from students where stuid=3;

select name from students where stuid=3;

哪些查询可能不会被缓存？

查询语句中包含udf

存储函数

用户自定义变量

临时表

mysql系统表或者是包含列级别权限的查询

有着不确定结果值的函数(now())；

查询缓存相关的服务器变量：

query_cache_limit：能够缓存的最大查询结果；(单语句结果集大小上限)

有着较大结果集的语句，显式使用sql_no_cache，以避免先缓存再移出；

query_cache_min_res_unit：内存块的最小分配单位；缓存过小的查询结果集会浪费内存空间；

较小的值会减少空间浪费，但会导致更频繁地内存分配及回收操作；

较大值的会带来空间浪费；

query_cache_size：查询缓存空间的总共可用的大小；单位是字节，必须是1024的整数倍；

query_cache_strip_comments

query_cache_type：缓存功能启用与否；

on：启用；

off：禁用；

demand：按需缓存，仅缓存select语句中带sql_cache的查询结果；

query_cache_wlock_invalidate：如果某表被其它连接锁定，是否仍然可以从查询缓存中返回查询结果；默认为off，表示可以；on则表示不可以；

状态变量：

mysql> show global status like 'qcache%';

+-------------------------+----------+

| variable_name | value |

+-------------------------+----------+

| qcache_free_blocks | 1 |

| qcache_free_memory | 16759688 |

| qcache_hits | 0 |

| qcache_inserts | 0 |

| qcache_lowmem_prunes | 0 |

| qcache_not_cached | 0 |

| qcache_queries_in_cache | 0 |

| qcache_total_blocks | 1 |

+-------------------------+----------+

命中率：

qcache_hits/com_select

数据库的锁

锁用于并发控制：

锁：lock

锁类型 ：

读锁：共享锁，可被多个读操作共享；

写锁：排它锁，独占锁；

锁粒度：

表锁：在表级别施加锁，并发性较低；

行锁：在行级别施加锁，并发性较高；维持锁状态的成本较大；

锁策略：在锁粒度及数据安全性之间寻求一种平衡机制；

存储引擎：级别以及何时施加或释放锁由存储引擎自行决定；

mysql server：表级别，可自行决定，也允许显式请求；

锁类别：

显式锁：用户手动请求的锁；

隐式锁：存储引擎自行根据需要施加的锁；

显式锁的使用：

(1) lock tables

lock tables tbl_name read|write, tbl_name read|write, ...

unlock tables

(2) flush tables

flush tables tbl_name,... [with read lock];

unlock tables;

(3) select cluase

[for update | lock in share mode]

数据库的事务

1.事务的概念

1)事务是一种机制，是一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组的数据库命令要么都执行，要么都不执行。

2)事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。

3)事务适用于多用户同时操作的数据库系统的场景，通过事务的完整性以保证数据的一致性。

4)事务是一组原子性的sql查询、或者是一个或多个sql语句组成的独立工作单元；

2.事务的ACID特点

事务具有四个属性:ACID

A：原子性，atomicity

C：一致性，consistency

I：隔离性，isolation

D：持久性，durability

1)原子性

事务是一个完整的操作，事务的各元素是不可分割的(原子的)，事务的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。

2)一致性

当事务完成时，数据必须处于一致状态：在事务开始之前，数据库汇总存储的数据处于一致状态；在正在进行的事务中，数据可能处于不一致的状态；当事务完成时，数据必须再次回到已知的一致状态。

3)隔离性

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应该以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同的数据的事务开始之前访问这些数据，或者在另一个使用相同的数据的事务结束之后访问这些数据。

4)持久性

事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。一旦事务被提交，事务的效果会被永久地保留在数据库中。

3.事务的操作

默认情况下mysql的事务是自动提交的，当sql语句提交时，事务便自动提交。

1)自动提交：单语句事务

mysql> select @@autocommit;

+------------------------+

| @@autocommit |

+------------------------+

| 1 |

+------------------------+

mysql> set @@session.autocommit=0；

2)手动对事务进行控制的方法：

事务处理命令控制

使用set设置事务处理方式

1》事务处理命令控制事务

start transaction ，begin：开始一个事务

commit：提交一个事务

rollback：回滚一个事务(撤销)

示例：

MariaDB [none]> use auther;

MariaDB [auther]> begin;

MariaDB [auther]> insert into users values('list',password('123456'));

MariaDB [auther]> insert into users values('wang',password('123456'));

MariaDB [auther]> select * from users;

+-----------+--------------------------------+

| user_name | user_passwd |

+-----------+--------------------------------+

| list | *6BB4837EB74329105EE4568DDA7DC |

| shen | *A4B6157319038724E3560894F7F93 |

| wang | *6BB4837EB74329105EE4568DDA7DC |

+-----------+--------------------------------+

MariaDB [auther]> commit;

MariaDB [auther]> select * from users;

+-----------+--------------------------------+

| user_name | user_passwd |

+-----------+--------------------------------+

| list | *6BB4837EB74329105EE4568DDA7DC |

| shen | *A4B6157319038724E3560894F7F93 |

| wang | *6BB4837EB74329105EE4568DDA7DC |

+-----------+--------------------------------+

MariaDB [auther]> begin;

MariaDB [auther]> update users set user_passwd=password('') where user_name='list';

MariaDB [auther]> select * from users;

+-----------+--------------------------------+

| user_name | user_passwd |

+-----------+--------------------------------+

| list | |

| shen | *A4B6157319038724E3560894F7F93 |

| wang | *6BB4837EB74329105EE4568DDA7DC |

+-----------+--------------------------------+

MariaDB [auther]> rollback; ##从begin开始的所有命令都将被撤销

MariaDB [auther]> select * from users;

+-----------+--------------------------------+

| user_name | user_passwd |

+-----------+--------------------------------+

| list | *6BB4837EB74329105EE4568DDA7DC |

| shen | *A4B6157319038724E3560894F7F93 |

| wang | *6BB4837EB74329105EE4568DDA7DC |

+-----------+--------------------------------+

2》使用set命令进行控制

set autocommit=0：禁止自动提交，不管你输入执行了几条命令，在没有输入commit前都是一个事务。

set autocommit=1 ：开启自动提交，每输入执行一条命令，那么就为一个事务。

4.补充

事务日志：

innodb_log_files_in_group

innodb_log_group_home_dir

innodb_log_file_size

innodb_mirrored_log_groups

事务支持savepoints：

savepoint identifier

rollback [work] to [savepoint] identifier

release savepoint identifier

事务隔离级别：

read-uncommitted：读未提交 --> 脏读；

read-committed：读提交--> 不可重复读；

repeatable-read：可重复读 --> 幻读；

serializable：串行化；

mysql> select @@session.tx_isolation;

+----------------------------------+

| @@session.tx_isolation |

+----------------------------------+

| repeatable-read |

+----------------------------------+

查看innodb存储引擎的状态信息：

show engine innodb status;

数据库的视图

1.视图

1)视图是从一个或多个表中导出来的表，是一种虚拟存在的表。

2)视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。

3)数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。

4)使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

5)视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。

2.视图的作用

1)使操作简单化，可以对经常使用的查询定义一个视图，使用户不必为同样的查询操作指定条件

2)增加数据的安全性，通过视图，用户只能查询和修改指定的数据。

3)提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率

3.创建视图

语法：

help create view

create

[or replace]

[algorithm = {undefined | merge | temptable}]

[definer = { user | current_user }]

[sql security { definer | invoker }]

view view_name [(column_list)]

as select_statement

[with [cascaded | local] check option]

algorithm：

表示视图选择的算法(可选参数)

undefined：mysql将自动选择所要使用的算法

merge：将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分

temptable：将视图的结果存入临时表，然后使用临时表执行语句

column_list：

表示视图中的列名，默认与select查询结果中的列名相同(可选参数)

AS：

表示将后面SELECT 语句中的查询结果赋给前面的视图中

with check option：

表示更新视图时要保证在该试图的权限范围之内(可选参数)

cascaded：更新视图时要满足所有相关视图和表的条件

local：更新视图时，要满足该视图本身定义的条件即可

创建试图时最好加上with cascaded check option参数，这种方式比较严格，可以保证数据的安全性

示例：

1)在单表上创建视图

create view work_view(id,name,address) as select id,name,address from work;

2)在多表创建视图

create algorithm=merge view work_view2(id,name,salary) as select work.id,name,salary from work,salary where work.id=salary.id with local check option;

在多表中创建视图需要两表有指定联系，如上面的work.id=salary.id

3)官方示例

1>

mysql> create table t (qty int, price int);

mysql> insert into t values(3, 50);

mysql> create view v as select qty, price, qty*price as value from t;

mysql> select * from v;

+------+-------+-------+

| qty | price | value |

+------+-------+-------+

| 3 | 50 | 150 |

+------+-------+-------+

2>

mysql> create view v (mycol) as select 'abc';

mysql> set sql_mode = '';

mysql> select "mycol" from v;

+-------+

| mycol |

+-------+

| mycol |

+-------+

mysql> set sql_mode = 'ansi_quotes';

mysql> select "mycol" from v;

+-------+

| mycol |

+-------+

| abc |

+-------+

4.查看视图

1)select 查看视图

SELECT * FROM view_name;

此处的SELECT语句用法和其他表中的用法一样，别忘了，视图也是一张表，只不过它是虚拟的。

2)describe查看视图的基本信息

ESCRIBE view_name;

3)show table status查看视图基本信息

SHOW TABLE STATUS LIKE 'view_name'\G;

4)show create view 查看视图的详细信息

SHOW CREATE VIEW view_name\G;

5)在views表中查看视图的详细信息

SELECT * FROM information_schema.views\G;

information_schema.views表内包含了所有的视图定义信息

5.修改视图

修改视图是指修改数据库中已存在的表的定义，当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。

1)create or replace view来修改视图

create or replace algorithm=temptable view view_name(id,name) as select id,name from tbl_name;

2)alter来修改视图

alter view view_name(name,salary,addr) as select name,salary,address from tbl_name1,tbl_name2 where tbl_name1.id=tbl_name2.id;

help alter view

alter

[algorithm = {undefined | merge | temptable}]

[definer = { user | current_user }]

[sql security { definer | invoker }]

view view_name [(column_list)]

as select_statement

[with [cascaded | local] check option]

6.更新视图

更新视图是指通过视图来插入、更新和删除表中的数据，以为视图是一个虚拟表，其中没有数据。

通过视图更新时，都是转换到基本表来更新，相当于更新表：

update view_name set salary=8888 where name='名字';

等价于

update salary set salary=8888 where id='数字';

视图中虽然可以更新数据，但是有很多限制，一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据

7.删除视图

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

drop view if exists view_name;

drop view view_name；

help drop view

drop view [if exists] view_name [, view_name] ... [restrict | cascade]