MySQL视图知识总结

来源：互联网发布：淘宝客服异地兼职编辑：程序博客网时间：2024/05/18 06:24

(一)什么是视图？

1.视图是基表的投影，视图是从一个或几个基本表中导出来的，但与基本表不同，它是一个虚表。
2.数据库中只存放视图的定义，却不存放视图对应的数据，因此视图中不存放数据，数据依然在基本表中。
3.一旦视图被定义，可以像数据表一样操作(CRUD)。
4.将使用频繁的SQL语句建立视图查询。查询视图就是执行定义视图的select语句。
5.查询视图就是重复使用一次写好的视图select语句。

(二)定义视图

语法：

create view 视图名as  select语句

1.select语句可以是一下形式：

这里写图片描述

2.在books表中建立视图

#1创建books表CREATE TABLE `books` (  `BookID` smallint(6) NOT NULL,  `BookTitle` varchar(60) NOT NULL,  `Copyright` year(4) NOT NULL,  `BookPrice` double(10,0) DEFAULT NULL,  PRIMARY KEY (`BookID`),  KEY `BookTitle` (`BookTitle`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql> select*from books;+--------+--------------+-----------+-----------+| BookID | BookTitle    | Copyright | BookPrice |+--------+--------------+-----------+-----------+|  12786 | Java         |      1934 |        60 ||  13331 | MySQL        |      1919 |        78 ||  14356 | PHP          |      1966 |        49 ||  15729 | PERL         |      1932 |        36 ||  17695 | Pl/SQL       |      1980 |        98 ||  18673 | Oracle       |      1996 |       108 ||  18923 | MySQL        |      2012 |        98 ||  19264 | JavaScript   |      1992 |        49 ||  19323 | PHP          |      2017 |        56 ||  19452 | java编程思想 |      1998 |       118 ||  20332 | Oracle       |      1999 |       128 ||  21203 | Java         |      1959 |        39 ||  21233 | MySQL        |      2017 |       118 |+--------+--------------+-----------+-----------+13 rows in set#2在books表上创建视图SELECT    `books`.`BookTitle` AS `BookTitle`,    `books`.`BookID` AS `BookID`FROM    `books`WHERE    (        (`books`.`BookTitle` = 'PHP')        OR (`books`.`BookID` > 20000)    )注：可以使用创建视图工具创建视图，有工具创建视图为什么不使用工具呢？#3查询视图(等同于执行select语句)mysql> select*from books_view;+-----------+--------+| BookTitle | BookID |+-----------+--------+| Java      |  21203 || MySQL     |  21233 || Oracle    |  20332 || PHP       |  14356 || PHP       |  19323 |+-----------+--------+5 rows in set

3.在select语句中添加group by 条件

#分组SELECT    `books`.`BookTitle` AS `BookTitle`,    `books`.`BookID` AS `BookID`FROM    `books`GROUP BY    `books`.`BookTitle`#分组前mysql> select  BookID, BookTitle  from books;+--------+--------------+| BookID | BookTitle    |+--------+--------------+|  12786 | Java         ||  21203 | Java         ||  19264 | JavaScript   ||  19452 | java编程思想 ||  13331 | MySQL        ||  18923 | MySQL        ||  21233 | MySQL        ||  18673 | Oracle       ||  20332 | Oracle       ||  15729 | PERL         ||  14356 | PHP          ||  19323 | PHP          ||  17695 | Pl/SQL       |+--------+--------------+13 rows in set#分组后mysql> select*from books_view;+--------------+--------+| BookTitle    | BookID |+--------------+--------+| Java         |  12786 || JavaScript   |  19264 || java编程思想 |  19452 || MySQL        |  13331 || Oracle       |  18673 || PERL         |  15729 || PHP          |  14356 || Pl/SQL       |  17695 |+--------------+--------+8 rows in set

4.筛选满足条件的记录

#添加查询条件SELECT    `books`.`BookTitle` AS `BookTitle`,    `books`.`BookID` AS `BookID`FROM    `books`WHERE    (`books`.`BookID` > 10000)HAVING    (        `books`.`BookTitle` = 'MySQL'    )#查询结果mysql> select*from books_view;+-----------+--------+| BookTitle | BookID |+-----------+--------+| MySQL     |  13331 || MySQL     |  18923 || MySQL     |  21233 |+-----------+--------+3 rows in set

5.在视图中添加order by条件

#添加条件SELECT    `books`.`BookID` AS `BookID`,    `books`.`BookTitle` AS `BookTitle`,    `books`.`Copyright` AS `Copyright`,    `books`.`BookPrice` AS `BookPrice`FROM    `books`ORDER BY    `books`.`BookPrice`#查询前基表情况mysql> select*from books;+--------+--------------+-----------+-----------+| BookID | BookTitle    | Copyright | BookPrice |+--------+--------------+-----------+-----------+|  12786 | Java         |      1934 |        60 ||  13331 | MySQL        |      1919 |        78 ||  14356 | PHP          |      1966 |        49 ||  15729 | PERL         |      1932 |        36 ||  17695 | Pl/SQL       |      1980 |        98 ||  18673 | Oracle       |      1996 |       108 ||  18923 | MySQL        |      2012 |        98 ||  19264 | JavaScript   |      1992 |        49 ||  19323 | PHP          |      2017 |        56 ||  19452 | java编程思想 |      1998 |       118 ||  20332 | Oracle       |      1999 |       128 ||  21203 | Java         |      1959 |        39 ||  21233 | MySQL        |      2017 |       118 |+--------+--------------+-----------+-----------+13 rows in set#使用视图查询(这里按书的价格排序，升序！)mysql> select*from books_view;+--------+--------------+-----------+-----------+| BookID | BookTitle    | Copyright | BookPrice |+--------+--------------+-----------+-----------+|  15729 | PERL         |      1932 |        36 ||  21203 | Java         |      1959 |        39 ||  14356 | PHP          |      1966 |        49 ||  19264 | JavaScript   |      1992 |        49 ||  19323 | PHP          |      2017 |        56 ||  12786 | Java         |      1934 |        60 ||  13331 | MySQL        |      1919 |        78 ||  17695 | Pl/SQL       |      1980 |        98 ||  18923 | MySQL        |      2012 |        98 ||  18673 | Oracle       |      1996 |       108 ||  19452 | java编程思想 |      1998 |       118 ||  21233 | MySQL        |      2017 |       118 ||  20332 | Oracle       |      1999 |       128 |+--------+--------------+-----------+-----------+13 rows in set

6.在视图中使用limit限制查询条数或查询位置

#查询4条记录SELECT    `books`.`BookID` AS `BookID`,    `books`.`BookTitle` AS `BookTitle`,    `books`.`Copyright` AS `Copyright`,    `books`.`BookPrice` AS `BookPrice`FROM    `books`LIMIT 0,4#查询结果mysql> select*from books_view;+--------+-----------+-----------+-----------+| BookID | BookTitle | Copyright | BookPrice |+--------+-----------+-----------+-----------+|  12786 | Java      |      1934 |        60 ||  13331 | MySQL     |      1919 |        78 ||  14356 | PHP       |      1966 |        49 ||  15729 | PERL      |      1932 |        36 |+--------+-----------+-----------+-----------+4 rows in set

(三)视图的修改

1.视图的修改的对象是什么？修改视图就是修改select语句。

(四)视图的删除

1.语法格式和删除基本一样

drop 视图名；

(五)视图的作用

视图是在基表的基础上建立，且数据都在基表上，一切对视图的操作最终要转换为对基本的操作。个人觉得视图就是建立一条永久的SQL语句，我们不需要每一次去写一条庞大的SQL语句，因此建立视图写一次，可以重复使用，除非删除。

视图有什么作用？

1.视图简化用户的对数据库的操作
2.视图可以使用户多角度看待同一数据
3.视图对重构数据库提供一定程度的逻辑独立性
4.视图对机密数据提供安全保护
5.适当利用视图可以更清晰地表达查询

(六)总结

如果重复使用SQL语句查询数据表，建立视图，查询视图即可，无须重复写SQL语句。

阅读全文

0 0