Shulou(Shulou.com)06/01 Report--

This blog introduces the view functions and stored procedures of mysql as well as their functions and examples!

One. MySQL View VIEW

1. Brief introduction to View

The view function in MySQL is similar to the filtering function in Exel.

Popular understanding is to create a virtual table of columns and records that are often queried in a table. In fact, select statements are stored in the viewer view. The data seen in the view is updated dynamically as the original table is updated.

Filters that define views can come from one or more tables in the current or other database, or from other views. There are no restrictions on querying through views, and there are few restrictions on data modification through them.

View is a SQL query statement stored in the database, it is mainly for security reasons, the view can hide some data, such as: some sensitive information makes complex queries easy to understand and use.

two。 Create a view

Syntax: CREATE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW View name [(Field 1, Field 2 …)]

AS SELECT statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

Example: create a view called view_user to query the user, host, and password field information in the user table in the mysql library

CREATE VIEW view_user AS SELECT user,host,password FROM mysql.user

The above example is a single table view, and a view can also create a multi-table view, for example:

Create two tables in the test library, a product table product to store the product name and the corresponding price, and a purchasing purshase table to store product information and quantity

Product table preparation:

Purchase table preparation:

Create a multi-table view:

Mysql > create view purchase_detail as select product.name as name, product.price as price, purchase.quantity as quantity, product.price * purchase.quantity as total_value from product,purchase where product.name = purchase.name; # equivalent connection (that is, records with the same name are combined)

3. View view

The show tables # view name can be viewed by the table name, because it is essentially a virtual table

Show table status # View the status of the table

Example: view the view and all table details in the test library-> show table status from test\ G

Show create view # View view definition information

Example: show create view view name\ G

Desc # View View structure

Example: desc view name

4. Modify the view

Method 1: delete the view directly and then create a new view

Method 2: alter modifies the view

Syntax:

ALTER VIEW View name

AS SELECT statement

Example:

ALTER VIEW view_user

AS SELECT user,password FROM mysql.user

5. Delete View

Syntax: DROP VIEW view_name

Two. MySQL stored procedure

Function: define the data stored procedure, which allows you to add, delete, modify and check libraries, tables and records, similar to the functions in shell.

Brief introduction:

A stored procedure (Stored Procedure) is a set of SQL statements that are stored in the database in order to complete a specific function in a large database system. After the first compilation, the call does not need to be compiled again. The user executes it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters). Stored procedure is an important object in database.

Syntax:

Create procedure stored procedure name ()

BEGIN

A set of sql statements with certain functions

END

Call stored procedure name (); # call stored procedure

Example:

Requirements:

Create a library file named dba, and create a table named tb1 in the dba library with id and name fields in the table. Create a stored procedure named ad1, and the function of the ad1 stored procedure is to insert three records into the tb1 table.

1. Step to create the tb1 table

two。 Create ad1 stored procedures

3. Call the stored procedure after viewing the tb1 table, and look at the tb1 table this time to see what the difference is.

The main differences between stored procedures (procedure) and functions (function):

A stored procedure is a collection of SQL statements that process data records in a table, similar to shell scripts.

A function usually deals with the value of a field in a record.

The end of this article!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.