mysql提高(二十六视图）

来源：互联网发布：职称计算机模拟软件编辑：程序博客网时间：2024/05/22 07:06

1：视图:(可以隐藏表的实现表的实现字段)

select * from myview;
+-------+---------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME   | JOB       | MGR | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+---------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH   | CLERK     | 7902 | 2017-05-13 | 800.00 |    NULL |     20 |
| 7499 | ALLEN   | SALESMAN | 7698 | 2017-05-13 | 1600.00 | 300.00 |     30 |
| 7521 | WARD    | SALESMAN | 7698 | 2017-05-13 | 1250.00 | 500.00 |     30 |
| 7566 | JONES   | MANAGER   | 7839 | 2017-05-13 | 2975.00 |    NULL |     20 |
| 7654 | MARTIM | SALESMAN | 7698 | 2017-05-13 | 1250.00 | 1400.00 |     30 |
| 7698 | BLAKE   | MANAGER   | 7839 | 2017-05-13 | 2850.00 |    NULL |     30 |
| 7782 | CLARK   | MANAGERAN | 7839 | 2017-05-13 | 2450.00 |    NULL |     10 |
| 7788 | SCOLL   | ANALIST   | 7566 | 2017-05-13 | 3000.00 |    NULL |     20 |
| 7839 | KING    | PRESIDENT | NULL | 2017-05-13 | 5000.00 |    NULL |     10 |
| 7844 | IUSRNER | SALESMAN | 7698 | 2017-05-13 | 1500.00 |    NULL |     30 |
| 7876 | ADAMS   | CLECK     | 7788 | 2017-05-13 | 1100.00 |    NULL |     20 |
| 7900 | JAMES   | CLECK     | 7698 | 2017-05-13 | 950.00 |    NULL |     30 |
| 7902 | FORD    | ANALYST   | 7566 | 2017-05-13 | 3000.00 |    NULL |     20 |
| 7934 | MILLER | CLERY     | 7782 | 2017-05-13 | 1300.00 |    NULL |     10 |
+-------+---------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select empno,ename from myview;
+-------+---------+
| EMPNO | ENAME   |
+-------+---------+
| 7369 | SMITH   |
| 7499 | ALLEN   |
| 7521 | WARD    |
| 7566 | JONES   |
| 7654 | MARTIM |
| 7698 | BLAKE   |
| 7782 | CLARK   |
| 7788 | SCOLL   |
| 7839 | KING    |
| 7844 | IUSRNER |
| 7876 | ADAMS   |
| 7900 | JAMES   |
| 7902 | FORD    |
| 7934 | MILLER |
+-------+---------+
14 rows in set (0.00 sec)

mysql> delete from myview;
Query OK, 14 rows affected (0.08 sec)

mysql> select * from emp;
Empty set (0.00 sec)

2:创建视图（保护数据的隐藏性，隐藏数据表中的字段名，给数据表起别名形成视图，通过视图可以完成增删改，create view as后面只能跟select语句）

create view deptview as select deptno as a,dname as b,loc as c from dept;
Query OK, 0 rows affected (0.11 sec)

mysql> select * from deptview;
+----+------------+-----------+
| a | b          | c         |
+----+------------+-----------+
| 10 | ACCOUNTING | BeiJing   |
| 20 | RESEARCH   | ShangHai |
| 30 | SALES      | ShenZhen |
| 40 | OPERATIONS | GuangZhou |
+----+------------+-----------+
4 rows in set (0.00 sec)

mysql> select a from deptview;
+----+
| a |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
+----+
4 rows in set (0.00 sec)

mysql> select deptno from deptview;
ERROR 1054 (42S22): Unknown column 'deptno' in 'field list'

阅读全文

0 0