MySQl 4-5-Database View-MySQL-mysql tutorial

MySQl 4-5-Database View bitsCN.com 1. when modifying, querying, or deleting a record, the system will prompt how many records are affected, but no table creation prompt will be displayed. When the table data is modified, it is reflected in the view. The command for modifying, querying, and deleting a view is the same as that for creating a table. A view is a table exported from one or more tables (or views. View is the point of view for database users. You can define the data structure required by the physical database based on their different needs. The data structure defined by the user is the view. The View and Table (sometimes different from the view, also known as the basic Table-Base Table) are different. The View is a virtual Table, that is, the data corresponding to the view is not actually stored, the database only stores view Definitions. when operating on view data, the system operates the basic table associated with the view according to the view definition. As defined, you can query, modify, delete, and update tables. 2. View has the following advantages: (1) centralized data for users, simplifying data query and processing. Sometimes the data you need is scattered in multiple tables, and the definition view can combine them to facilitate data query and processing. (2) shielding the database complexity. You do not have to understand the table structure in a complex database, and changes to the database tables do not affect your use of the database. (3) simplify the management of user permissions. You only need to grant the user the permission to use the view, without having to specify that the user can only use specific columns of the table, which also increases the security. (4) facilitate data sharing. Each user does not have to define and store the data they need, and can share the data in the database. in this way, the same data only needs to be stored once. (5) data can be reorganized to output to other applications. 3. use the create view statement to CREATE a view. Syntax: create [re replace] [algorithm = {undefined | merge | temptable}] view Name [(column_list)] as select_statement [with [cascaded | local] check option] Example: create view v_xs as select * from xs; (created under xsdb) | xsdb. xs (view of the table in xsdb created under the flying xsdb database) description: ● column_list: to define a clear name for the column in the view, you can use the optional column_list clause, list the names of columns separated by commas. The number of names in column_list must be equal to the number of columns retrieved by the SELECT statement. Column_list can be omitted if the column name is the same as that in the source table or view. ● Or replace: given the or replace clause, the statement can replace the existing view with the same name. ● Algorithm clause: the optional ALGORITHM clause is an extension of standard SQL MySQL. it specifies the MySQL algorithm and the ALGORITHM will affect the MySQL processing view. ALGORITHM can have three values: MERGE, TEMPTABLE, or UNDEFINED. Without the ALGORITHM clause, the default ALGORITHM is UNDEFINED (UNDEFINED ). If the MERGE option is specified, the text of the statements that reference the view is merged with the view definition, so that a part of the view definition replaces the corresponding part of the statement. The MERGE algorithm requires that the rows in the view and the rows in the base table have a one-to-one relationship. if the relationship does not exist, use a temporary table instead. If the TEMPTABLE option is specified, the view result is placed in a temporary table and then executed using it. ● Select_statement: SELECT statement used to create a view. multiple tables or views can be queried in a SELECT statement. However, the SELECT statement has the following restrictions: (1) Users who define a view must have the query permission on the referenced table or view (the SELECT statement can be executed); (2) subqueries in the FROM clause cannot be included; (3) system or user variables cannot be referenced; (4) preprocessing statement parameters cannot be referenced; (5) the table or view referenced in the definition must exist. (6) If the reference is not a table or view of the current database, add the database name before the table or view. (7) you can use order by in the view definition. However, if you select a specific view and the View uses a statement with its own order, order by in the view definition is ignored. (8) for other options or clauses in the SELECT statement, if the view also contains these options, the effect is not defined. For example, if the view definition contains the LIMIT clause and the SELECT statement uses its own LIMIT clause, MySQL does not define which LIMIT to use. ● With check option: indicates that all modifications made to the updatable view must comply WITH the constraints specified by select_statement. This ensures that after the data is modified, you can still view the modified data in the view. When a view is defined based on another view, with check option provides two parameters: LOCAL and CASCADED. They determine the scope of the inspection test. The Local keyword enables check option to only CHECK the defined View. cascaded checks all views. If no keyword is specified, the default value is CASCADED. 4. Note the following when using a view: (1) by default, a new view will be created in the current database. To explicitly create a view in a given database, you must specify the name db_name.view_name when creating the view. (2) The View name must follow the naming rules of the flag, and cannot have the same name as the table. The View name must be unique for each user, that is, for different users, even if they define the same view, you must also use different names. (3) rules, default values, or triggers cannot be associated with views. (4) you cannot create any indexes on The View, including full-text indexes. 5. Example 1: assume that the current database is TEST, and create the CS_KC view on the XSCJ database, including the student ID of each computer major, the course number and the score it chooses. Make sure that all modifications to this view meet the condition that the professional name is computer. Createor replace view xscj. CS_KC as select xs. student ID, course number, score FROMXSCJ. XS, XSCJ. XS_KC where xs. student ID = XS_KC. student id and xs. professional name = 'compute' with check option; Example 2: Find the student ID and average score of the students WITH an average score of over 80. In this example, the average score View XS_KC_AVG is created, including the student ID (num in the view) and average score (score_avg in the view ). Create the average score View XS_KC_AVG: CREATEVIEW XS_KC_AVG (num, score_avg) as select student ID, AVG (score) FROMXS_KC group by student ID, and then query the XS_KC_AVG view. SELECT * FROM XS_KC_AVG WHEREscore_avg> = 80; FROM the two examples above, we can see that creating a view can hide complex table connections to the end user, simplifying the user's SQL program design. Note: If a new field is added to the basic table associated with the view, the view does not contain the new field. For example, the columns in the view CS_XS are associated with all columns in The XS table. if the XS table adds the "nationality" field, the data of the "nationality" field cannot be queried in the CS_XS view. If the table or view associated with the view is deleted, the view cannot be used any more. 6. to UPDATE basic table data through a updatable view, you must ensure that the view is updatable, that is, you can use them in INSET, UPDATE, or DELETE statements. For updatable views, the rows in the view and the rows in the base table must have a one-to-one relationship. There are some other specific structures that make the views unupdatable. If a view contains any of the following structures, it cannot be updated: (1) aggregate function; (2) DISTINCT keyword; (3) group by clause; (4) order by clause; (5) HAVING clause; (6) UNION operator; (7) subquery in the selected list; (8) FROM Clause contains multiple tables; (9) the SELECT statement references non-updatable views. (10) subqueries in the WHERE clause reference the tables in the FROM clause. (11) the ALGORITHM option is set to TEMPTABLE (using a temporary table will always make the view unupdatable ). 7. INSERT data using the INSERT statement INSERT data into the base table through the view example: create the view CS_XS, The View contains information about computer students, and INSERT a record to the CS_XS view: ('123456 ', 'limu ', 'computer', 1, '2017-10-21 ', 50, NULL, NULL ). First, create a VIEW CS_XS: (This table is used for the deletion and modification below) createor replace view CS_XS as select * from xs where professional name = 'compute' with check option; note: when creating a View, add the with check option clause because the with check option clause checks whether the new data meets the WHERE clause conditions in the view definition when updating data. The with checkoption clause can only be used WITH a newer version. Next, insert the record: INSERTINTO CS_XS VALUES ('201312', 'limum', 'put', 1, '2017-10-14 ', 50, NULL, NULL). note: here, when inserting records, the professional name can only be "computer ". In this case, you can use the SELECT statement to query the CS_XS view and basic table XS. you can find that this record has been added to the XS table. When a view depends on multiple basic tables, data cannot be inserted into the view, because this will affect multiple basic tables. For example, you cannot insert data to the view CS_KC because CS_KC depends on two basic tables: XS and XS_KC. There is also a restriction on the INSERT statement: The SELECT statement must contain all columns that cannot be empty in the specified table in the FROM clause. For example, if the "name" field is not added when the CS_XS view is defined, an error occurs during data insertion. 8. use the UPDATE statement to modify the data of the basic table in the view. for example, increase the total credits of all students in the CS_XS View by 8. UPDATECS_XS SET total credits = total credits + 8; this statement actually adds 8 to the original total credits field value of all records in the basic table XS on which the CS_XS view depends. If a view depends on multiple basic tables, only the data of one basic table can be changed at a time. For example, change the 081101 course score of students whose CS_KC view is 101 to 90. UPDATECS_KC SET score = 90 WHERE student ID = '000000' AND course number = '000000'; in this example, the view CS_KC depends on two basic tables: xs and XS_KC, A modification to the CS_KC view can only change the student ID (derived from the XS table) or course number and score (derived from the XS_KC table ). The following changes are incorrect: UPDATECS_KC SET student ID = '000000', course number = '000000' WHERE score = 90; 9. you can use the DELETE statement to DELETE data from a basic table through the view: DELETE records of female students in CS_XS. DELETEFROM CS_XS WHERE gender = 0; note: you cannot use the DELETE statement for views that depend on multiple basic tables. For example, you cannot execute the DELETE statement on the CS_KC view to DELETE data from the related basic tables XS and XS_KC. 10. use the ALTER Statement to modify the definition of an existing view. Syntax format: ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] check option] syntax of the ALTERVIEW statement is similar to create view. for example, modify the CS_XS VIEW to only contain the student ID, name, and total credits of a computer major. USEXSCJ; ALTERVIEW CS_XS as select student ID, name, total credits from xs where professional name = 'compute'; 11. delete View syntax using SQL statement format: dropVIEW [IF EXISTS] View Name 1 [, View Name 2]... [RESTRICT | CASCADE] declares if exists. IF the view does not exist, no error message is displayed. You can also declare restrict and cascade, but they have no impact. You can use drop view to delete multiple views at a time. For example, drop view CS_KC and CS_XS will delete the views CS_KC and CS_XS. Author tianyazaiheruanbitsCN.com