1.什么是視圖寂诱？
view：站在不同的角度去看待同一份數(shù)據(jù)歉摧。
2.如何創(chuàng)建灿意、刪除視圖對(duì)象？
創(chuàng)建視圖對(duì)象：
create view dept_view as select * from dept;
刪除視圖對(duì)象：
drop view dept_view;
注意：只有DQL語句才能以view的形式創(chuàng)建扫沼。create view view_name as 這里的語句必須是DQL語句;
3.視圖的作用
可以面向視圖對(duì)象進(jìn)行增刪改查出爹，對(duì)視圖對(duì)象的增刪改查會(huì)導(dǎo)致原表被操作庄吼。（視圖的特點(diǎn)：通過對(duì)視圖的操作，會(huì)影響到原表數(shù)據(jù)以政。）
面向視圖查詢
select * from dept_view;
面向視圖插入
insert into dept_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');
查詢?cè)頂?shù)據(jù)
select * from dept;
結(jié)果：
+--------+------------+----------+
| DEPTNO | DNAME? ? ? | LOC? ? ? |
+--------+------------+----------+
|? ? 10 | ACCOUNTING | NEW YORK |
|? ? 20 | RESEARCH? | DALLAS? |
|? ? 30 | SALES? ? ? | CHICAGO? |
|? ? 40 | OPERATIONS | BOSTON? |
|? ? 60 | SALES? ? ? | BEIJING? |
+--------+------------+----------+
面向視圖刪除
delete from dept_view;
查詢?cè)頂?shù)據(jù)
select * from dept;
結(jié)果：Empty set (0.00 sec)
創(chuàng)建視圖對(duì)象
create
????view emp_dept_view
as
????select
????????e.ename,e.sal,d.dname
????from
????????emp e
????join
????????dept d
????on
????????e.deptno = d.deptno;
查詢視圖對(duì)象
select * from emp_dept_view;
結(jié)果：
+--------+---------+------------+
| ename? | sal? ? | dname? ? ? |
+--------+---------+------------+
| CLARK? | 2450.00 | ACCOUNTING |
| KING? | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
| SMITH? |? 800.00 | RESEARCH? |
| JONES? | 2975.00 | RESEARCH? |
| SCOTT? | 3000.00 | RESEARCH? |
| ADAMS? | 1100.00 | RESEARCH? |
| FORD? | 3000.00 | RESEARCH? |
| ALLEN? | 1600.00 | SALES? ? ? |
| WARD? | 1250.00 | SALES? ? ? |
| MARTIN | 1250.00 | SALES? ? ? |
| BLAKE? | 2850.00 | SALES? ? ? |
| TURNER | 1500.00 | SALES? ? ? |
| JAMES? |? 950.00 | SALES? ? ? |
+--------+---------+------------+
面向視圖更新
update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING';
原表數(shù)據(jù)被更新
select * from emp;
結(jié)果：
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME? | JOB? ? ? | MGR? | HIREDATE? | SAL? ? | COMM? ? | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|? 7369 | SMITH? | CLERK? ? | 7902 | 1980-12-17 |? 800.00 |? ? NULL |? ? 20 |
|? 7499 | ALLEN? | SALESMAN? | 7698 | 1981-02-20 | 1600.00 |? 300.00 |? ? 30 |
|? 7521 | WARD? | SALESMAN? | 7698 | 1981-02-22 | 1250.00 |? 500.00 |? ? 30 |
|? 7566 | JONES? | MANAGER? | 7839 | 1981-04-02 | 2975.00 |? ? NULL |? ? 20 |
|? 7654 | MARTIN | SALESMAN? | 7698 | 1981-09-28 | 1250.00 | 1400.00 |? ? 30 |
|? 7698 | BLAKE? | MANAGER? | 7839 | 1981-05-01 | 2850.00 |? ? NULL |? ? 30 |
|? 7782 | CLARK? | MANAGER? | 7839 | 1981-06-09 | 1000.00 |? ? NULL |? ? 10 |
|? 7788 | SCOTT? | ANALYST? | 7566 | 1987-04-19 | 3000.00 |? ? NULL |? ? 20 |
|? 7839 | KING? | PRESIDENT | NULL | 1981-11-17 | 1000.00 |? ? NULL |? ? 10 |
|? 7844 | TURNER | SALESMAN? | 7698 | 1981-09-08 | 1500.00 |? ? 0.00 |? ? 30 |
|? 7876 | ADAMS? | CLERK? ? | 7788 | 1987-05-23 | 1100.00 |? ? NULL |? ? 20 |
|? 7900 | JAMES? | CLERK? ? | 7698 | 1981-12-03 |? 950.00 |? ? NULL |? ? 30 |
|? 7902 | FORD? | ANALYST? | 7566 | 1981-12-03 | 3000.00 |? ? NULL |? ? 20 ||
|? 7934 | MILLER | CLERK? ? | 7782 | 1982-01-23 | 1000.00 |? ? NULL |? ? 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
4.視圖對(duì)象在實(shí)際開發(fā)中到底有什么用霸褒？
假設(shè)有一條非常復(fù)雜的SQL語句，而這條SQL語句需要在不同的位置上反復(fù)使用盈蛮。每一次使用這個(gè)sql語句的時(shí)候都需要重新編寫废菱，很長(zhǎng)，很麻煩抖誉，怎么辦殊轴？
可以把這條復(fù)雜的SQL語句以視圖對(duì)象的形式新建。
在需要編寫這條SQL語句的位置直接使用視圖對(duì)象袒炉，可以大大簡(jiǎn)化開發(fā)旁理。
并且利于后期的維護(hù)，因?yàn)樾薷牡臅r(shí)候也只需要修改一個(gè)位置就行我磁，只需要修改視圖對(duì)象所映射的SQL語句孽文。
我們以后面向視圖開發(fā)的時(shí)候，使用視圖的時(shí)候可以像使用table一樣夺艰。
可以對(duì)視圖進(jìn)行增刪改查等操作芋哭。視圖不是在內(nèi)存當(dāng)中，視圖對(duì)象也是存儲(chǔ)在硬盤上的郁副，不會(huì)消失减牺。
