mysql创建视图sql_SQL视图介绍-如何在SQL和MySQL中创建视图

mysql创建视图sql

什么是SQL视图？ (What is a View in SQL?)

A View is a database object that presents data existing in one or more tables. Views are used in a similar way to tables, but they don’t contain any data. They just “point” to the data that exists elsewhere (tables or views, for example).

视图是一个数据库对象，它显示一个或多个表中存在的数据。 视图的使用方式与表相似，但是它们不包含任何数据。 它们只是“指向”其他地方存在的数据(例如表或视图)。

我们为什么喜欢它们？ (Why do we like them?)

• Views are a way to limit the data presented. For example, the human resources department data filtered to only present sensitive information. Sensitive information in this case could be social security numbers, sex of employee, payrate, home address, etc.
  视图是一种限制呈现数据的方法。 例如，人力资源部门数据被过滤为仅显示敏感信息。 在这种情况下，敏感信息可能是社会保险号，员工性别，薪资，家庭住址等。
• Complex data across more than one table can be combined into a single “view.” This can make life easier for your business analysts and programmers.
  可以将多个表中的复杂数据合并为一个“视图”。 这可以使您的业务分析师和程序员的工作更加轻松。

重要安全提示 (Important Safety Tips)

• Views are managed by the system. When data in the related tables are changed, added, or updated, the View is updated by the system. We want to use these only when needed to manage use of system resources.
  视图由系统管理。 更改，添加或更新相关表中的数据时，系统将更新视图。 我们只想在需要管理系统资源使用时使用它们。
• In MySQL, changes to the table design (that is, new or dropped columns) made AFTER a view is created are not updated in the view itself. The view would have to be updated or recreated.
  在MySQL中，创建视图后对表设计(即新列或删除的列)的更改不会在视图本身中更新。 该视图将必须更新或重新创建。
• Views are one of the four standard database object types. The others are tables, stored procedures, and functions.
  视图是四种标准数据库对象类型之一。 其他是表，存储过程和函数。
• Views can usually be treated as you would a table, but updates are limited or not available when the view contains more than one table.
  视图通常可以像对待表一样对待，但是当视图包含多个表时，更新受到限制或不可用。
• There are many other details about views that are beyond the scope of this introductory guide. Spend time with your database managers manual and have fun with this powerful SQL object.
  关于视图的许多其他详细信息超出了本入门指南的范围。 花时间阅读数据库管理器手册，并使用这个功能强大SQL对象带来的乐趣。

Create View语句的语法(MySQL) (Syntax of the Create View Statement (MySQL))

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
	[WITH [CASCADED | LOCAL] CHECK OPTION]

This guide will cover this part of of the statement…

本指南将涵盖声明的这一部分……

CREATE
    VIEW view_name [(column_list)]
    AS select_statement

从学生表创建示例视图 (Sample View creation from the student tables)

Notes:

笔记：

• The name of the view has a “v” at the end. It’s recommended that the view name indicate that it’s a view in some way to make life easier for programmers and database administrators. Your IT shop should have its own rules on naming objects.
  视图名称的末尾带有“ v”。 建议视图名称以某种方式表明它是一个视图，以使程序员和数据库管理员的工作更加轻松。 您的IT部门应在命名对象方面有自己的规则。
• The columns in the view are limited by the SELECT and the rows of data by the WHERE clause.
  视图中的列受SELECT限制，数据行受WHERE子句限制。
• the ”`” character around the view names is required because of the ”-” in the names. MySQL reports an error without them.
  由于名称中的“-”，因此需要在视图名称周围添加“`”字符。 没有它们，MySQL报告一个错误。

create view `programming-students-v` as
select FullName, programOfStudy 
from student 
where programOfStudy = 'Programming';

select * from `programming-students-v`;

使用视图合并多个表中的数据的示例 (Sample of using a View to combine data from more than one table)

A Student demographics table was added to the database to demonstrate this usage. This view will combine these tables.

学生人口统计表已添加到数据库以演示此用法。 该视图将合并这些表。

Notes:

笔记：

• To “join” tables, the tables must have fields in common (usually primary keys) that uniquely identity each row. In this case it’s the student ID. (More on this in the SQL Joins guide.)

  要“联接”表，表必须具有唯一标识每一行的公用字段(通常是主键)。 在这种情况下，这是学生证。 (有关更多信息，请参见《 SQL Joins指南》。)

• Notice the “alias” given to each table (“s” for student and “sc” for student contact). This is a tool to shorten the table names and make it easier to identify which table is being used. It’s easier than typing long table names repeatedly. In this example, it was required because studentID is the same column name in both tables, and the system would present an “ambiguous column name error” without specifying which table to use.
  注意每个表的“别名”(学生用“ s”，学生联系用“ sc”)。 这是一个缩短表名并使其更容易识别正在使用的表的工具。 比重复输入长表名要容易。 在此示例中，这是必需的，因为StudentID在两个表中都是相同的列名，并且系统将显示“模棱两可的列名错误”而不指定要使用的表。

翻译自: https://www.freecodecamp.org/news/sql-create-view-mysql/

mysql创建视图sql