MySQL(基础篇)之视图
一: 视图的简介,作用,优点 1. 什么是视图? a. 视图是一种虚拟的表 ,是从数据库中一个或者多个表中导出的表 b. 数据库只存放了视图的定义,而并没有存放视图中的数据, 这些数据存放在原来的表中 c. 使用视图查询数据时,数据库系统会从原来的表中取出对应的记录
一: 视图的简介,作用,优点
1. 什么是视图?
a. 视图是一种虚拟的表,是从数据库中一个或者多个表中导出的表
b. 数据库只存放了视图的定义,而并没有存放视图中的数据,这些数据存放在原来的表中
c. 使用视图查询数据时,数据库系统会从原来的表中取出对应的记录
2. 视图的作用:
a. 使操作简便化
b. 增加数据的安全性
c. 提高表的逻辑独立性
3. 视图的优点:
a. 视点集中
b. 简化操作
c. 定制数据
d. 合并分割数据
· e. 安全性
二: 创建视图
CREATE [ ALGORITHM ={ UNDEFIEND | MERGE | TEMPTABLE }]
VIEW 视图名[ ( 属性清单) ]
AS SELECT 语句
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ];
ALGORITHM 是可选参数,表示视图选择的算法;
“视图名”参数表示要创建的视图的名称;
“属性清单”是可选参数,其指定了视图中各种属性的名词,默认情况下与SELECT 语句中查询的属性相同;
SELECT 语句参数是一个完整的查询语句,标识从某个表查出某些满足条件的记录,将这些记录导入视图中;
WITH CHECK OPTION 是可选参数,表似乎更新视图时要保证在该视图的权限范围之内;
ALGORITHM 包括3 个选项UNDEFINED、MERGE 和TEMPTABLE。
UNDEFINED 选项表示MySQL 将自动选择所要使用的算法;
MERGE 选项表示将使用视图的语句与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分;
TEMPTABLE 选项表示将视图的结果存入临时表,然后使用临时表执行语句;CASCADED是可选参数,表示更新视图时要满足所有相关视图和表的条件,该参数为默认值;
LOCAL 表示更新视图时,要满足该视图本身的定义条件即可;
例: 创建一个名为v1的视图,其中包含了t_book表的全部记录
Mysql>CREATE VIEW v1
AS
SELECT * FROM t_book;
接下来我们就可以对这个视图进行操作,查询视图中id为2的记录
Mysql>SELECT * FROM v1 WHERE id=2;
例: 创建一个名为v2的视图,其中只显示书名和价格信息
Mysql>CREATE VIEW v2
AS
SELECT bookName,price FROM t_book;
Mysql>SELECT * FROM v2;
现在v2视图中就只包含书名和价格的信息
例: 这样写的话,你会发现字段名变成了书名和价格
Mysql>CREATE VIEW v3(书名,价格)
AS
SELECT bookName,price FROM t_book;
Mysql>SELECT * FROM v2;
当然,也可以这么写:
Mysql>CREATE VIEW v4
AS
SELECT bookName AS '书名',price AS '价格' FROM t_book;
例: 根据t_book和t_booktype表建立视图,查询书名和书的类别
Mysql>CREATE VIEW v5
AS
SELECT bookName,bookTypeName FROM t_book t1,t_booktype t2 WHERE t1.bookTypeId=t2.id;
三:查看视图
4.1 DESCRIBE 语句查看视图基本信息
例: 查看v4视图的基本信息
Mysql>DESC v4;
4.2 SHOW TABLE STATUS LIKE 语句查看视图基本信息
例: 查看v5视图的基本信息
Mysql>SHOW TABLE STATUS LIKE 'v5';
可以很明显的看出 视图是虚表
4.3 SHOW CREATE VIEW 语句查看视图详细信息
例: 查看v5视图的详细信息
Mysql>SHOW CREATE VIEW v5;
4.3 在views 表中查看视图详细信息
Mysql>USE information_schema;
Mysql>SELECT * FROM views;
这样 我们就能看到所有已经创建的视图的信息
四: 修改视图
4.1 CREATE OR REPLACE VIEW 语句修改视图
CREATE OR REPLACE [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW 视图名[( 属性清单)]
AS SELECT 语句
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ];
如果视图存在就REPLACE该视图,如果不存在 就Create 该视图
例: Mysql>CREATE OR REPLACE VIEW v3
AS
SELECT * FROM t_book WHERE id =3;
4.2 ALTER 语句修改视图
ALTER [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW 视图名[( 属性清单)]
AS SELECT 语句
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ];
只能修改视图,不能创建视图
例: Mysql>ALTER VIEW v3
AS
SELECT * FROM t_book;
五: 更新视图
更新视图是指通过视图来插入(INSERT)、更新(UPDATE)和删除(DELETE)表中的数据。因为视图是一个虚拟的表,其中没有数据。通过视图更新时,都是转换基本表来更新。更新视图时,只能更新权限范围内的数据。超出了范围,就不能更新。
5.1 插入(INSERT)
例: Mysql>INSERT INTO v4 VALUES(NULL,'心理学',45,'三张',4);
5.2 更新(UPDATE)
例: Mysql>UPDATE v4 SET bookName='心理学2' WHERE id =5;
5.3 删除(DELETE)
Mysql>DELETE FROM v4 WHERE id =5;
通过视图来插入(INSERT)、更新(UPDATE)和删除(DELETE)时,都是对原表来进行操作.
六: 删除视图
删除视图是指删除数据库中已存在的视图。删除视图时,只能删除视图的定义,不会删除数据;
DROP VIEW [ IF EXISTS ] 视图名列表[ RESTRICT | CASCADE ]
例: 如果存在名为v4的视图,那么就删除.
Mysql>DROP VIEW IF EXISTS v4;
七: 总结
视图到这就结束了,大家好好消化下视图,下节将为大家带来触发器的使用,谢谢大家!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.

PHP provides the following methods to delete data in MySQL tables: DELETE statement: used to delete rows matching conditions from the table. TRUNCATETABLE statement: used to clear all data in the table, including auto-incremented IDs. Practical case: You can delete users from the database using HTML forms and PHP code. The form submits the user ID, and the PHP code uses the DELETE statement to delete the record matching the ID from the users table.
