MySQL视图

 MySQL视图

 

什么是视图

视图是一个虚拟表，是存储在数据库中的查询语句。

视图是从一个或多个表或视图中导出的虚拟表，其结构和数据是建立在对表的查询基础上的。和表一样，视图也是包括几个被定义的数据列和多个数据行，这些数据列和数据行来源于其所引用的表。视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中，通过视图看到的数据只是存放在基本表中的数据。视图仅有.frm文件。

对视图的操作与对表的操作一样，可以对其进行查询、修改(有一定的限制)、删除。当对视图中的数据进行修改时，相应的基本表的数据也要发生变化，同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

视图的优点

简化用户操作

视图可以使用户把注意力集中在自己所关心的数据上来，比如一个比较复杂的多表查询，使用视图就可以将表与表之间的连接操作对用户隐藏起来，用户只需对一张虚拟的表进行简单的查询即可得到其想要的数据。

视图能够对机密数据提供安全保证

我们可以在设计数据库的时候，对不同的用户定义不同的视图，使机密数据不出现在不应该看到这些数据的用户所使用的视图中。比如：一张包含学校20个院系所有学生信息的student表，这样我们就可以定义20个视图，每个视图只包含一个院系的学生数据，这样就只允许每个院系的老师查询和更改本院系的学生信息。

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

对于一些功能需求，需要改动表的结构，如增加新的关系或对原有的关系增加新的字段，使用视图能够使用户的应用程序不会受影响。比如：一张员工表empa(empno,ename,deptno,dname,sal,loc)，拆分为两张表，emp(empno,ename,deptno,sal)，dept(deptno,dname,loc)，此时创建一个视图empa(empno,ename,deptno,dname,sal,loc)，这样尽管数据表的逻辑结构改变了，但程序不必更改。当然，视图只能在一定程度上提供数据的逻辑独立，比如由于视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因为基本表构造的改变而改变。

创建视图

create [or replace] [algorithm ={undefined | merge | temptable}] view view_name [(column_list)] asselect_statement [with [cascaded | local] check option];

column_list:列的列表。

with后面加视图的限定条件：cascaded是默认的，表示更新时必须满足所有相关视图和表的条件，local表示更新时仅满足该视图本身定义的条件即可。

algorithm用于设定视图的算法：

merge：直接合并视图，比如一个视图：create algorithm=mergeview v_user as select name,age from user;此时，我们查找视图内容：select* from v_user;MySQL会把该语句直接替换成：select * from (selectname,age from user);

temptable：按照此方式创建的视图，MySQL会先把视图里的内容提取出来放到一个临时表里，然后当调用视图的时候再从该临时表里调用，这样效率比较低。

undefined：就是不定义处理视图的方式，此时MySQL更倾向于选择merge。

 

查看视图

desc view_name;

show table status like 'view_name'\G;

查看创建视图的语法：

show create view view_name;

也可以在view表中查看视图:

select * frominformation_schema.views\G;

information_schema.tables查看表

 

修改视图

使用CREATE语句，相当于重新创建视图：

CREATE [OR REPLACE] [ALGORITHM ={UNDEFINED | MERGE | TEMPTABLE}]

   VIEW view_name [(column_list)]

AS SELECT_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

 

使用ALTER语句修改：

ALTER [ALGORITHM = {UNDEFINED | MERGE |TEMPTABLE}]

    VIEW view_name [(column_list)]

    AS SELECT_statement

    [WITH [CASCADED | LOCAL] CHECK OPTION]

 

更新视图

更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据，通过视图更新的时候都是转到基本表进行更新的，如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。

update view_name set ... where ...;

 

删除视图：

drop view [if exists] view_name;

 

 

使用视图的注意事项

视图不能和表重名

视图仅有.frm文件，没有数据文件

视图可以简化操作，提升安全性，屏蔽表结构变化对用户的影响

数据量巨大且频繁更改的表要慎用视图

当建立视图的sql语句中包含以下子句时，无法使用merge算法：

聚集函数、distinct、group by、having、集合操作(union,union all)、子查询

 

MySQL运算符

运算符是告诉MySQL执行特定算术或逻辑操作的符号，主要分为：算术运算符、比较运算符、逻辑运算符、位操作运算符。

算术运算符

“+”：加法运算

“-”：减法运算

“*”：乘法运算

“/”：除法运算

“%”：求余运算

 

比较运算符

等号“＝”：用来判断数字、字符串、表达式是否相等，如果相等返回1，否则返回0。

SELECT 1=3,'4'=4,'s'='s',(1+2)*2=2*3,NULL=NULL;

安全等于符“<=>”：可以用来判断NULL值。

SELECT2=3,'2'=2,'b'='b',(1+2)*2=2*3,NULL<=>NULL;

 

不等于运算符“<>”和“!=”：用于判断数字、字符串、表达式的不相等判断。如果不相等，返回值1，否则返回0

SELECT2!=3,'2'!=2,'b'<>'b',(1+2)*2<>2*3,NULL!=NULL;

 

此外还有小于（<）、小于等于（<=）、大于（>）、大于等于（>=）运算符。

 

空值判断运算符is null(isnull)和is not null用来判断值是否为空。

SELECT ISNULL(NULL),0 IS NULL,0 ISNOT NULL,NULL IS NOT NULL;

 

BETWEEN AND运算符：用来判断给定值是否在指定范围内

select 4 between 2 and 5;

 

LEAST运算符用来取出给定的多个值中的最小值

select least(2,4,1.1),least(1,null);

GREATEST运算符用来取出给定的多个值中的最大值

select greatest(2,4,1.1), greatest(1,null);

 

IN和NOT IN运算符用来判断给定值是否是指定的多个值中的任意一个

SELECT 3 IN(2,2.3,1),NULLIN(1,NULL);

 

LIKE运算符用来匹配字符串,“%”匹配任意多个字符,“_”匹配一个字符

SELECT 'abc' LIKE 'acc','abc' LIKE'a%','abc' LIKE 'a_c';

 

REGEXP运算符支持用正则表达式匹配字符串

SELECT 'mysql' REGEXP '^m','mysql'REGEXP 'l$';

 

逻辑运算符

AND或者&&都表示逻辑与（a>b and a>c）

mysql> SELECT 1 AND 1,0 AND 0,1AND 0,1 AND NULL;

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

| 1 AND 1 | 0 AND 0 | 1 AND 0 | 1AND NULL |

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

|   1    |       0 |        0|       NULL |

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

 

select * from t2;

select * from t2 where id=1 andage=29;

and前后的两个条件要同时成立才满足

 

 

OR或者||都表示逻辑或（给定的两个值中只要有任何一个值满足要求都可）

mysql> SELECT 1 OR 1,0 OR 0,1 OR0,1 OR NULL;

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

| 1 OR 1 | 0 OR 0 | 1 OR 0 | 1 ORNULL |

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

|      1 |      0 |      1 |        1   |

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

 

select * from t2 where id=1 orage=31;

 

NOT和！都表示逻辑非

SELECT !10,!1,!0,!(1-1),!(1+1),!NULL;

 

XOR用来表示逻辑异或。判断顺序由左至右

mysql> SELECT!10,!1,!0,!(1-1),!(1+1),!NULL;

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

| !10 | !1 | !0 | !(1-1) | !(1+1) |!NULL |

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

|  0 |  0 |  1 |      1|      0 | NULL |

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

 

两个条件都不满足

大于30的男性和小于30的女性

(age>30 and sex='m') or (age<30and sex='f')

age>30 xor sex='f'

 

位运算符

在二进制位字节位的测试与处理

“|”：位或运算符，将参与运算的两个数据，按照二进制方式逐位进行逻辑或运算，对应的二进制位有一个或两个为1则运算结果为1，否则为0。

10 = 1010

15 = 1111

or   1111

mysql> select 10|15;

+-------+

| 10|15 |

+-------+

|   15 |

+-------+

 

“&”：位与运算符，将参与运算的两个数据，按照二进制方式逐位进行逻辑与运算。对应的二进制位都为1则运算结果为1，否则为0。

10 = 1010

15 = 1111

and 1010

mysql> select 10&15;

+-------+

| 10&15 |

+-------+

|   10 |

+-------+

 “^”：位异或运算符，将参与运算的两个数据，按照二进制方式逐位进行逻辑与运算。对应的二进制位不同则运算结果为1，否则为0。

10 = 1010

15 = 1111

    0101

mysql> select 10^15;

+-------+

| 10^15 |

+-------+

|     5 |

+-------+

 “<<”：位左移运算符，使指定的所有位都左移指定的位数。移动之后左边高位丢弃，后边低位用0补齐。

4      =00000100

4<<2   =0000010000

SELECT 4<<2;

“>>”：位右移运算符，使指定的所有位都右移指定的位数 。移动之后左边高位用0补齐，右边低位丢弃。

SELECT 16>>2;

“～”：位取反运算，将参与运算的数据，按对应的二进制数逐位反转。但需要注意，MySQL经过位运算之后的数值是一个64位的无符号整数，因此1逐位取反后换算成十进制将是2的64次方-1。

2 = 00000010

取反11111101

3 = 00000011

mysql> SELECT 3&~2;

+------+

| 3&~2 |

+------+

|   1 |

+------+

 

运算符的优先级

由低至高：

赋值运算=,:=

||,OR

XOR

&&,AND

NOT

BETWEEN,CASE,WHEN,THEN,ELSE

比较运算符

|

&

<<,>>

-,+

*,/,%

^

-(负号)，~(反转)

!