MySQL视图
一、什么是视图
视图是存放数据的一个接口，也可以说是虚拟表，这些数据可以是从一个或几个基表（视图）的数据，也可是用户自己定义的数据，其实视图里面不存放数据，数据据还是存在基表里面，基表数据发生变化，视图里的数据也随之变量，视图里数据变化，基表也会变化。
二、视图的作用
1. 视图可以让查询变得很清楚（复杂的SQL语句变得很简单）
2. 保护数据库的重要数据， 给不同的人看不同的数据
三、创建视图
create [or replace] [algorithm={merge|temptable|undefined}]
view view_name [(column_list)]
as select_statement
[with [cascaded|local] check option]
----------------------创建视图实例------------------------------------
--学生表
create table student (
id int primary key auto_increment,
name varchar(20) not null,
age int(3) not null,
email varchar(50) not null
);
insert into student values(null,'张三',30,'zhangsan@aa.com');
insert into student values(null,'李四',30,'lisi@aa.com');
insert into student values(null,'王五',50,'wangwu@aa.com');
insert into student values(null,'李岩',35,'liyan@aa.com');
insert into student values(null,'赵六',90,'zhaoliu@aa.com');
insert into student values(null,'孙七',15,'sunqi@aa.com');
mysql> select * from student;
+----+------+-----+-----------------+
| id | name | age | email |
+----+------+-----+-----------------+
| 1 | 张三 | 30 | zhangsan@aa.com |
| 2 | 李四 | 30 | lisi@aa.com |
| 3 | 王五 | 50 | wangwu@aa.com |
| 4 | 李岩 | 35 | liyan@aa.com |
| 5 | 赵六 | 90 | zhaoliu@aa.com |
| 6 | 孙七 | 15 | sunqi@aa.com |
+----+------+-----+-----------------+
6 rows in set (0.00 sec)
--成绩表
create table score (
id int primary key auto_increment,
cid int not null, --学生id
english int not null,
computer int not null,
math int not null,
constraint fk_id foreign key(cid) references student(id) --建立外键
);
insert into score values(null,2,80,70,50);
insert into score values(null,3,100,50,80);
insert into score values(null,4,90,30,55);
insert into score values(null,5,90,77,40);
insert into score values(null,6,66,72,89);
insert into score values(null,1,81,71,31);
mysql> select * from score;
+----+-----+---------+----------+------+
| id | cid | english | computer | math |
+----+-----+---------+----------+------+
| 1 | 2 | 80 | 70 | 50 |
| 2 | 3 | 100 | 50 | 80 |
| 3 | 4 | 90 | 30 | 55 |
| 4 | 5 | 90 | 77 | 40 |
| 5 | 6 | 66 | 72 | 89 |
| 6 | 1 | 81 | 71 | 31 |
+----+-----+---------+----------+------+
6 rows in set (0.00 sec)
--多表联合查询
mysql> select s1.name,s1.age,s2.english,s2.computer,s2.math from student s1 join score s2 on s1.id = s2.id;
+------+-----+---------+----------+------+
| name | age | english | computer | math |
+------+-----+---------+----------+------+
| 张三 | 30 | 80 | 70 | 50 |
| 李四 | 30 | 100 | 50 | 80 |
| 王五 | 50 | 90 | 30 | 55 |
| 李岩 | 35 | 90 | 77 | 40 |
| 赵六 | 90 | 66 | 72 | 89 |
| 孙七 | 15 | 81 | 71 | 31 |
+------+-----+---------+----------+------+
6 rows in set (0.00 sec)
--创建视图
create view stusc_view as select s1.name,s1.age,s2.english,s2.computer,s2.math from student s1 join score s2 on s1.id = s2.id;
--查询视图
mysql> select * from stusc_view;
+------+-----+---------+----------+------+
| name | age | english | computer | math |
+------+-----+---------+----------+------+
| 张三 | 30 | 80 | 70 | 50 |
| 李四 | 30 | 100 | 50 | 80 |
| 王五 | 50 | 90 | 30 | 55 |
| 李岩 | 35 | 90 | 77 | 40 |
| 赵六 | 90 | 66 | 72 | 89 |
| 孙七 | 15 | 81 | 71 | 31 |
+------+-----+---------+----------+------+
6 rows in set (0.00 sec)
视图有三种类型
Merge: 会将引用视图的语句的文本与视图定义结合起来，使得视图定义的某一部分取代语句的对应部分
Temptable: 不可更新，只有与基表一一对应才可以更新。
undefined ： 默认选项，一般倾向于选择 Megre
algorithm=merge
-----例子-------
mysql> create view stu3_view as select * from student where age > 30;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from stu3_view where age < 50;
+----+------+-----+--------------+
| id | name | age | email |
+----+------+-----+--------------+
| 4 | 李岩 | 35 | liyan@aa.com |
+----+------+-----+--------------+
1 row in set (0.00 sec)
----说明----
merge:查询stu3_view视图，相当于查询基表select * from student where age > 30 and age < 50;
with local check option [local]只要满足本视图的条件就可以更新
with cascaded check option [cascaded]则是必须满足所有针对视图的条件，才可以更新
------说明----------
---创建学生1视图
create view stu1_view as select * from student where age > 30;
--在学生1视图基础上创建学生2视图
create view stu2_view as select * from stu1_view where age < 90;
插入或更新stu2_view视图，如果是local只需满足age<90即可。如果是cascaded则需要满足age>30 and age <90;
四、视图的操作
五、更改视图
alter [or replace] [algorithm={merge|temptable|undefined}]
view view_name [(column_list)]
as select_statement
[with [cascaded|local] check option]
六、删除视图
drop view 视图名;
mysql> drop view stu3_view;
Query OK, 0 rows affected (0.00 sec)
七、查看视图
show tables;
--查看建视图语句
mysql> show create view stu1_view G;
*************************** 1. row ***************************
View: stu1_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu1_view` AS select `student`.`id` AS
ame` AS `name`,`student`.`age` AS `age`,`student`.`email` AS `email` from `student` where (`student`.`age` > 30)
---查看表是否是视图，看最后一行
mysql> show table status like 'stu1_view' G;
*************************** 1. row ***************************
Name: stu1_view
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
八、视图在WEB开发中的应用（PHP中使用视图）
<?php
$mysqli = new mysqli('localhost','root','root','xsphp');
if(mysqli_connect_errno()) {
printf('连接数据库出错，出错原因：%s',mysqli_connect_error());
exit;
}
$mysqli->set_charset('gbk');
$sql = 'select * from stusc_view';
$result = $mysqli->query($sql);
echo '<table border="1" align="center">';
//输出列名
echo '<tr>';
while($field = $result->fetch_field()) {
echo "<th>{$field->name}</th>";
}
echo '</tr>';
//输出数据
while($row = $result->fetch_assoc()) {
echo '<tr>';
foreach($row as $value) {
echo "<td>{$value}</td>";
}
echo '</tr>';
}
echo '</table>';
$mysqli->close();