Detailed example of MySQL database view

This article brings you relevant knowledge about mysql, which mainly organizes issues related to database views, including the introduction and role of views, creation of views, modification of views, views Updates, renaming and deleting views, practicing views, etc. Let’s take a look at them together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

1 Introduction and role of views

Views Introduction:

• View view is a virtual table, which does not exist in real life. Its essence is to obtain dynamic data sets based on SQL statements and name them, Users only need to use the view name to obtain the result set and use it as a table.
• Only the definition of the view is stored in the database, but the data in the view is not stored. The data still exists in the original data table.
• When using a view to query data, the database system will retrieve the corresponding data from the original table. Therefore, the data in the view is dependent on the data in the original table. When the data in the table changes, the data in the view will also change accordingly.

The role of the view:

• Simplify the code, We can reuse the query Encapsulating it into a view for reuse canmake complex queries easy to understand;
• more secure,For example, If there is a lot of data in a table and a lot of information you don't want others to see, you can use views to use different views for different users.

2 View creation

The syntax for creating a view is as follows:

create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]

Parameter description:

1. algorithm： Represents the algorithm for view selection, optional;
2. view_name: The name of the created view;
3. column_list: Specifies the noun of each attribute in the view. By default, it is the same as the one queried in the SELECT statement. The attributes are the same;
4. select_statement: represents a complete query statement and imports the query records into the view;
5. [ with [cascaded | local] check option]: means that when updating the view, ensure that the view is within the permission scope.

3 View modification

Modifying a view means modifying the definition of an existing table in the database. When some fields in the base table change, You can maintain consistency between the view and the base table by modifying the view.

Syntax format:

alter view 视图名 as select语句;

4 View update

Not all views can be updated. Views can be used in UPDATE, DELETE, or INSERT statements to update the contents of the underlying table. For an updateable view, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. If the view contains any of the following structures, the view is not updateable :

• Aggregation function (SUM(), MIN(), MAX(), etc.);

• DISTINCT;

• HAVING;

• UNION or UNION ALL;

• Subquery located in the select list ;

• JOIN;

• Non-updatable view in FROM clause;

• ##WHERE sub- subquery in the FROM clause, referencing the table in the FROM clause;

• uses only literal values ​​(in this case, there is no base table to update).

Note: Although data can be updated in the view, there are many restrictions.
In general, it is best to use views as virtual tables for querying data rather than updating data through views.
When a field in the view is modified in the real table, the view needs to be updated, otherwise the view will become an invalid view!

Rename the view:

rename table 视图名 to 新视图名;

Delete the view ：

drop view if exists 视图名;

When deleting a view, only the definition of the view is deleted, but the data in the real table is not deleted

drop view if exists 视图名1, 视图名2, 视图名3...;

create table college(
    cno   int         null,
    cname varchar(20) null);

create table student(
    sid     int         null,
    name    varchar(20) null,
    gender  varchar(20) null,
    age     int         null,
    birth   date        null,
    address varchar(20) null,
    score   double      null);

两表的基本数据如下图所示：

6.2 查询平均分最高的学校名称

结合之前学过的知识可以 尝试使用子查询和连接查询 来实现，参考代码如下：

SELECT cname
FROM (SELECT cname, rank() over (order by avg_score desc ) item
      FROM (SELECT cname, avg(score) avg_score
            FROM student
                     JOIN college ON sid = cno
            GROUP BY cname) t) tt
WHERE item = 1;

在上述代码中，先将student 与 college两表关联，将关联的查询作为子表，并根据子表进行平均数的排序，平均数序号为1的平均分数最高，再以此为子表进行子查询，查询出了平均分最高的学校。具体结果如下：

这种方式虽然能够解决问题，但是相对复杂，不容易看懂，为了简化代码，我们可以将每一个子查询创建为一个视图

视图解决方式代码：

-- 1 视图一，连接两表并计算平均数
CREATE VIEW t_view AS
SELECT cname, avg(score) avg_score
FROM student
         JOIN college ON sid = cno
GROUP BY cname;

-- 2 视图二，利用视图一对平均分数进行排序标号
CREATE VIEW tt_view AS
SELECT cname, rank() over (order by avg_score desc ) item
FROM (t_view);

-- 3 利用视图查询
SELECT cname
FROM (tt_view)
WHERE item = 1;

在创建完视图后，如果想要查询平均分前三名学校，则方便很多，创建好的视图可以直接使用！

参考代码及结果：

SELECT cnameFROM (tt_view)WHERE item = 1
   OR item = 2
   OR item = 3;

推荐学习：mysql视频教程

The above is the detailed content of Detailed example of MySQL database view. For more information, please follow other related articles on the PHP Chinese website!