MySQL must know reading notes-8 (table manipulation, views, stored procedures, cursors, triggers)

Operation of the list

Create a table

When creating a table, make sure that the table does not exist before, and if it does, it should be deleted and then created

Auto_increment indicates automatic increase

With default values, the default constants are added after the column definition, which means that the defaults are initialized to a constant

Update a table

Add a column

Delete a column

Defining foreign keys

Delete a table

Rename a table

2--View

What is a view

A view is a virtual table that is not the same as a normal table, and the view contains only queries that use Dynamic Data.

Example:

A query statement that searches for information about a customer who has bought a product, and we can wrap it up as a virtual table productcustomers,

Use this virtual table to easily retrieve the same data

This is the view

Why use views?

Rules and restrictions for views

Use of the 3--view

Create a View

Example:

Create a view that is a virtual table that contains the customer's corresponding order and its corresponding order content

CREATE VIEW  as SELECT Cust_name, cust_contact, prod_id  from customers, orders, OrderItems WHERE = orders.cust_id  and   = Orders.order_num

Search for customers who have ordered TNT through this view

SELECT Cust_name, Cust_contact  from productcustomers WHERE = ' TNT2 ';

This shows that the view simplifies the use of complex SQL code and also improves the reusability of the code.

Reformat the retrieved data using a view

Using Views to filter data

Filter customers without a mailbox

Working with views and calculated fields

List the total price of each item in an Order Item table

CREATE VIEW  as SELECT order_num, prod_id, quantity, Item_price, quantity* as expanded_price  from OrderItems

SELECT *  from orderitemsexpanded WHERE = 20005;

4--View Update

A table can be updated (insert,update and delete)

Virtual tables as views are also available, but are subject to some limitations

If you have the following actions in the view, you cannot update the

5--Stored Procedures

A stored procedure is a collection of SQL languages that are saved for later use, like a block of code

Role

Writing stored Procedures

Write a stored procedure that returns the average price of a product

CREATE PROCEDURE productpricing () BEGIN    SELECT AVG  as priceaverage      from Products ; END;

Execute it

Call Productpricing ();

Deletion of stored procedures

Stored procedure with parameters

Calculates the lowest, highest, and average prices in the product table (only parameters are passed in, not transmitted)

CREATE PROCEDUREproductpricing (out P1DECIMAL(8,2), out phDECIMAL(8,2), out PADECIMAL(8,2))BEGIN    SELECT Min(Prod_price) intoP1 fromProducts ; SELECT Max(Prod_price) intoph fromProducts ; SELECT AVG(Prod_price) intoPA fromProducts ;END;

Where the keyword out represents the outgoing parameter, and if it is in, it represents the incoming parameter, and inout represents the incoming and outgoing

Executing stored procedures
Call Productpricing (@pricelow@pricehigh@priceaverage)
SELECT @priceaverage

Enter order number to return the total price of the order (incoming and outgoing parameters)

CREATE PROCEDUREOrderTotal (inch  Number INT, out TotalDECIMAL(8,2))BEGIN    SELECT Sum(Item_price*quantity) fromOrderItemsWHEREOrder_num=  Number   intoTotal ;END;

Call OrderTotal (20005@total); SELECT @total;

6--stored procedure with conditional statements

If we want to calculate the total price of the order, we should increase the sales tax on the total price, but this sales tax is only for customers who meet certain conditions

CREATE PROCEDUREOrderTotal (inch  Number INT,    inchTaxable BOOLEAN, #布尔值, decide whether to increase taxes out of totalDECIMAL(8,2))BEGIN    DECLARETtotalDECIMAL(8,2); DECLARETaxRateINT DEFAULT 6; #税率初始化SELECT Sum(Item_price*quantity) fromOrderItemsWHEREOrder_num=  Number     intoTtotal; IFTaxable Then        SELECTTtotal+(Ttotal/ -*TaxRate) intoTtotal; END IF; SELECTTtotal intoTotal ;END;

Call OrderTotal (20005@total); SELECT @total;

Call OrderTotal (20005@total); SELECT @total;

7--Checking stored Procedures

8--Cursors

In MySQL, cursors apply only to stored procedures and functions.

A cursor is a database query stored on a MySQL server that is not a SELECT statement but a result set retrieved by the statement.

After the cursor is stored, the application can scroll or browse the data as needed.

Use of Cursors

Check the total price of all orders

1 CREATE PROCEDUREprocessorders ()2 BEGIN3     DECLAREDone BOOLEANDEFAULTFALSE;4     DECLAREOINT;5     DECLARETDECIMAL(8,2);6 7     DECLAREOrdernumbersCURSOR #定义一个游标, read out all order_num in orders8      for9     SELECTOrder_num fromorders;Ten  One     DECLARE CONTINUEHANDLER forSQLSTATE'02000' SETDone=true;# ' 02000 ' is a condition that has not been found and has been set to TRUE after this condition has been reached. A  -     CREATE TABLE IF  not EXISTSOrderTotals -(Order_numINT, totalDECIMAL(8,2)); the  -     OPENordernumbers; #使用游标前必须打开它, to close after use -      - REPEAT #反复执行 +         FETCHOrdernumbers intoo; #使用FETCH读取这个游标的一行, read the next line when you execute it again -Call OrderTotal (O,1, T); #这个存储过程是上一节的例子 +         INSERT  intoordertotals (Order_num, total) A         VALUES(o,t); atUNTIL DoneENDREPEAT; -  -     CLOSEordernumbers; - END; -  - Call processorders (); in  - SELECT *  to  fromOrderTotals;

This code is to find out all order numbers through a cursor, calculate the sum of each order number by looping through each output, and then add the data to the new table OrderTotals.

9--Trigger

In MySQL, a trigger is a processing action that can be automatically responded to when a delete, INSERT, UPDATE statement is executed.

For example, every time we order a product from the warehouse, the number of corresponding products in the warehouse will be one less.

Create a Trigger

Each trigger name must be unique, and only the base table supports triggers

Example:

Each order is deleted and the order is added to the Order record table.

#新建订单记录表
CREATE TABLEarchive_orders (Order_numint       not NULL, Order_datedatetime  not NULL, cust_idint       not NULL ,  PRIMARY KEY(Order_num)) ENGINE=InnoDB;CREATE TRIGGERDeleteorder beforeDELETE  onorders forEach ROWBEGIN    INSERT  intoarchive_orders (Order_num, Order_date, cust_id)VALUES(Old.order_num, Old.order_date, old.cust_id);END;

Because there are foreign key restrictions in the order table, these statements cannot be used directly, as a demonstration only.

MySQL must know reading notes-8 (table manipulation, views, stored procedures, cursors, triggers)