MySQL Notes---view, stored procedures, getting started with triggers

Sophomore database, just vaguely heard the teacher mentioned the view ah, stored procedures AH, trigger ah what, but just a faint remember the name, and then do small projects, little programs, also no use, are just simple to build tables, related tables and so on, My understanding of these things can only stay in the name stage. Recently read a thin book called <mysql will Know, and remember a lot of notes, but also to practice some of their own, and finally to understand some fur.

Here are some examples of how to understand these things, first of all my sample table is like this.

Views: What is a view?

A view is a virtual table that does not itself contain data and, through an example, a better understanding of the view:

Assuming that I need to query the product information provided by the specified vendor in the above table, SQL needs to write:

　　

This is just a simple two-table association, if we actually need to have a lot of similar but more complex SQL, then the use of the view is a good choice, if I wrap the entire query into a virtual table (view) named Prodvend, then you can easily retrieve the same data.

As you can see, we can select, filter, and manipulate the view as if it were a normal table, and views are simply a facility for viewing data stored elsewhere, the view itself contains data, the data they return is retrieved from other tables, and after the changes are made to those tables, The view also synchronizes updates.

How to use Views:

View creation uses the CREATE VIEW statement.

In the example above, the view Prodvend creation statement is as follows:

CREATE VIEW  as SELECT Prod_name,prod_price  from products,vendors where products.vend_id=vendors.vend_id;

Delete the view using the Drop VIEW statement.

Some rules of the view:

• As with the table name, the view name must be unique
• Views can create an unlimited number of
• To create a view, you need to have the CREATE VIEW permission for the user.
• Views can be used with tables, and you can write a SELECT statement that joins tables and views.
• Views can be nested, meaning that you can create a view that uses another view.

　　The above example shows that the most important function of a view is to simplify complex joins .

Application of the view: simplifying complex junctions (most important); Reformat the retrieved data with a view:

As shown in the table above, suppose we need to query the information of an item and want to get this format "product name (commodity price)". The SELECT statement should read like this:

SELECT CONCAT (Prod_name,'(', Prod_price,')') as ProductInfo  from the products;

The result of the query is this:

If you often need the results of this format, you can create a view that you want to use every time you need it.

CREATE VIEWProdinfo asSELECTProd_id,concat (Prod_name,'(', Prod_price,')') asProductInfo fromProducts ;--later if you want to query this format of product information can be directly usedSELECT *  fromProdinfo;--if you want to query only one item, you canSELECT *  fromProdinfoWHEREprod_id=1;

Use views to simplify calculated fields:

Similarly, if you need to use calculations frequently in queries, such as the product of the quantity and unit price in the order table, you can create a view to save the operation, and you can query the view directly when you need it, so you do not show it here.

Update Issues for Views:

Views are typically used to retrieve data, which is used with select, but the view can also be updated, and you can update (Delete,update,insert) the views. If the view is modified, the base table is actually added and deleted. However, if MySQL does not correctly determine the base data that needs to be updated, then the update is not allowed. For example, if the view definition has the following actions, the update is not allowed:

Groups, joins, subqueries, aggregate functions (MIN (), SUM ()), and so on. In fact, many views are not updatable, but we need to know that the view itself is used to query. There is no need to update the view in general.

Stored procedures: What are stored procedures?

In practice, a complete requirement usually needs to be done with multiple SQL statements, which require multiple SQL statements for more than one table, and when writing code, you can write each statement individually and execute additional statements conditionally based on the result. One drawback of this is that You have to do so much work every time you need this processing. You can then create a stored procedure to complete the encapsulation and reuse of requirements.

A stored procedure is simply a collection of one or more SQL statements that are saved for later use. You can treat it as a batch file.

Using stored procedures: Creating Stored procedures:

Take the above table for example, suppose we now need to know the most expensive goods in the product list, the cheapest goods, and the average price of the goods (although that doesn't make any sense). Of course we can write a SELECT statement, or three of these statements are queried separately. Use stored procedures should write:

CREATE PROCEDUREProductsprice (--the type of the P1,P2,P3 parameter that accepts three parameters is decimal (8,2), respectively.        --The Out keyword is used to return to the callerOut P1DECIMAL(8,2), out P2DECIMAL(8,2), out P3DECIMAL(8,2))BEGIN    SELECT MIN(Prod_price) intoP1 fromProducts ; SELECT Max(Prod_price) intoP2 fromProducts ; SELECT AVG(Prod_price) intoP3 fromProducts ;END;

To execute a stored procedure:

To invoke the stored procedure created above, you must specify three variable names (the MySQL variable must begin with @ ), as follows:

　　Note : When a stored procedure is called, this statement does not display the data, it returns the variables that can be displayed, so you can query the variable names directly using the SELECT statement.

Stored procedures can be seen a function, the function has a return value, of course, you can also pass in parameters, the following is a stored procedure with both parameters and return values, he can receive an item ID, return to the city of the product's supplier:

CREATE PROCEDUREFindcitybyid (--incoming parameters, item ID            inchProdIDINT,          --return variable commodity supplier's CityOut vendcityVARCHAR( the))BEGIN    SELECTv.vend_city fromProducts P,vendors vWHEREp.vend_id=v.vend_id andp.prod_id=ProdID intovendcity;END;

Use this stored procedure must pass two parameters to Findcitybyid, the first parameter is the commodity ID, the second parameter is a variable name to receive the supplier city, as follows:

　　

In fact, the above-written stored procedures are simply encapsulated SQL statements, so simple requirements to use stored procedures only things become more complex, only in the stored procedure contains complex business planning, and intelligent processing of its power can be displayed.

To delete a stored procedure:

You can use the drop procedure+ name.

Benefits of stored procedures (effects):

　Simplify complex operations by encapsulating processing in easy-to-use units.

Because there is no requirement to repeatedly establish a series of processing steps, you can prevent errors, because the more steps required, the greater the likelihood of errors, and the integrity and consistency of the data can be ensured by using stored procedures.

Simplifies management of changes, if the table name, column name, or business logic changes, only the code in the stored procedure needs to be changed, and the person using the stored procedure does not even need to know about the changes.

Trigger:What is a trigger?　

　　Both SQL statements and stored procedures are executed when you need them, and triggers, as the name implies, are automatically executed when an event is started. For example:

　　　Each time a product is ordered, the quantity of the ordered item needs to be subtracted from the inventory quantity.

In practice, whenever you delete a row of records, you need to save a copy of the deleted row record in an archive table.

When inserting a column with an English name, make sure that the English is always capitalized, regardless (whether the name given in insert or update is uppercase or lowercase).

One common denominator of the above example is the need to automatically process a certain logic when a table is changed. This is the trigger. Triggers can only be executed when the table is altered, that is, only the delete,update,insert statement.

To create a trigger:

To create a trigger, you need to give 3 messages:

1. Unique trigger name (keep the trigger name unique under each database).

2. Tables associated with triggers

3. The trigger should be active (delete,update or insert) and when it should be executed (before or after).

I am now going to create a simple trigger that displays a word "Insert success!" when inserting a product record in the product table.

Note: In triggering events for triggers, you cannot write only select ' Insert Success ' because the trigger does not allow a result set to be returned.

CREATE TRIGGER INSERT  on  Products  for  BEGIN    SELECT'Insert success' into @info ; END ;

Note: Only tables support triggers, views are unsupported, and up to 6 triggers in a table occur before or after insert/delete/update.

• Insert Trigger, you can refer to a virtual table named new to access the inserted row.
• Delete trigger, you can refer to a virtual table named old to access the row to be deleted.
• Update triggers, both new and old, save the records before and after the update.

Here is an example of using the rows you want to delete into a backup table named backup:

CREATE TRIGGER DELETE  on  Products  for  BEGIN    INSERT into back_up (Prod_id,vend_id,prod_name,prod_price)     Values (old.prod_id,old.vend_id,old.prod_name,old.prod_price); END;

Execute delete a commodity with a primary key of 1,delete from product where prod_id=1; You will find that there is a record in the Back_up table that is the item that was deleted.

To delete a trigger:

Delete with drop trigger + name.

The above can be understood even if it is the introduction of the three database of the more important knowledge, which is where I am now in the stage, I have every example of the book to achieve their own, but not guaranteed to be correct, I also need in the future work in-depth study ~ ~ ~

MySQL Notes---view, stored procedures, getting started with triggers