簡述什么是視圖

1）是一種虛擬存在的表
2）內容與真實的表相似，包含一系列帶有名稱的列和行數據。
3）視圖并不在數據庫中以存儲的數據的形式存在。
4）行和列的數據來自定義視圖時查詢所引用的基本表，并且在具體引用視圖時動態生成。
更新視圖的數據，就是更新基表的數據
更新基表數據，視圖的數據也會跟著改變

2 視圖的優點有哪些

簡單： 使用視圖的用戶完全不需要關心視圖中的數據是通過什么查詢得到的。
視圖中的數據對用戶來說已經是過濾好的符合條件的結果集。
安全：
用戶只能看到視圖中的數據。
數據獨立：
一旦視圖的結構確定了，可以屏蔽表結構變化對用戶的影響

視圖的基本使用

1）把/etc/passwd文件的內容存儲到db庫下的user表里

[root@mysql_59 ~]# cp /etc/passwd /var/lib/mysql-files/
[root@mysql_59 ~]# ls /var/lib/mysql-files/
mysql> create table db.user(
-> username char(64),
-> password char(1),
-> uid int(2),
-> gid int(2),
-> comment char(64),
-> homedir char(64),
-> shell char(64));
Query OK, 0 rows affected (0.30 sec)

mysql> load data infile "/var/lib/mysql-files/passwd" into table db.user fields terminated by ":" lines terminated by "\n";
Query OK, 41 rows affected (0.18 sec)
Records: 41 Deleted: 0 Skipped: 0 Warnings: 0

mysql> alter table db.user add id int(2) primary key auto_increment first;
Query OK, 0 rows affected (0.72 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> use db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc user;
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| id | int(2) | NO | PRI | NULL | auto_increment |
| username | char(64) | YES | | NULL | |
| password | char(1) | YES | | NULL | |
| uid | int(2) | YES | | NULL | |
| gid | int(2) | YES | | NULL | |
| comment | char(64) | YES | | NULL | |
| homedir | char(64) | YES | | NULL | |
| shell | char(64) | YES | | NULL | |
+----------+----------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

創建視圖語法

create view 視圖名聲 as SQL查詢語句;

create view 視圖名稱（字段列表） as SQL查詢語句 ;

查看當前庫下所有表的信息狀態
show table status\G;
show table status where comment="view"\G;

mysql> create
3）創建視圖v1 結構及數據user表的字段、記錄一樣

mysql> create view v1 as select * from user;
Query OK, 0 rows affected (0.10 sec)

查看視圖具體命令
show create view 視圖名稱\G;

視圖的使用：
查詢記錄
select 字段名列表 from 視圖名 where 條件;
mysql> select username from v1 where id=10;
+----------+
| username |
+----------+
| operator |
+----------+
1 row in set (0.00 sec)

插入記錄
insert into 視圖名（字段列表） values(字段值列表);
mysql> insert into user(username,uid) values("jack",88);
Query OK, 1 row affected (0.15 sec)
Update 視圖名 set 字段名=值 where 條件;

更新記錄
update 視圖名 set 字段名=值 where 條件 ;
ysql> update v1 set comment="AAAA" where username="root";
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from v1 where username="root";
+----+----------+----------+-------+------+---------+---------+-----------+
| id | username | password | uid | gid | comment | homedir | shell |
+----+----------+----------+-------+------+---------+---------+-----------+
| 1 | root | x | 44444 | 0 | AAAA | /root | /bin/bash |
+----+----------+----------+-------+------+---------+---------+-----------+
1 row in set (0.00 sec)

+----+----------+----------+-------+------+---------+---------+-----------+
| id | username | password | uid | gid | comment | homedir | shell |
+----+----------+----------+-------+------+---------+---------+-----------+
| 1 | fuck | x | 44444 | 0 | AAAA | /root | /bin/bash |
+----+----------+----------+-------+------+---------+---------+-----------+
1 row in set (0.00 sec)

刪除記錄
delete from 視圖名 where 條件

mysql> delete from v1 where username="fuck";
Query OK, 1 row affected (0.07 sec)

4）創建視圖v2 只有user表shell是/bin/bash用戶信息
mysql> create view v2 as select shell from user;
Query OK, 0 rows affected (0.12 sec)
5）刪除視圖 drop view 視圖名;
mysql> drop view v2;
Query OK, 0 rows affected (0.19 sec)

設置字段別名
視圖中的字段別名不可以重復 所以要頂一別名
格式為：
create view 視圖名
as
select 表別名.源字段名 as 字段別名
from 源表名 表別名 left join 源表名 表別名
on 條件|;

delimiter //
create procedure db.p11()
begin
if 1=2 then
select from db.user where id=2;
else
select from db user where id=2;
end if;
end
//
delimiter ;call dbp11(1)

mysql> delimiter //

mysql> create procedure db.p18()
-> begin
-> declare x int default 1;
-> while x<=10 do
-> select x;
-> set x=x+1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.06 sec)

mysql> call db.p18();

mysql> delimiter //
mysql> create procedure db.p23()
-> begin
-> declare x int default 1;
-> loab1:while x<=10 do;
-> select x;
-> set x=x+1;
-> if x=3 then
-> leave loab1;
-> end if;
-> end while;
-> end
-> //