MySQL 21-29 highlights: views, triggers, stored procedures, cursors, and transactions

21. create and operate tables

21. 1. Create a table

Create table:

1) Name of the new table, after the keyword CREATETABLE

2) names and definitions of table columns, separated by commas

Create table MERs

(

Cust_id int not null AUTO_INCREMENT,

Cust_name char (50) NOTNULL,

Cust_address char (50) NULL,

Cust_city char (50) NULL,

Cust_state char (5) NULL,

Cust_zip char (10) NULL,

Cust_country char (50) NULL,

Cust_contact char (50) NULL,

Cust_email char (255) NULL,

PrimaryKEY (cust_id)

) ENGINE = InnoDB;

Note the following:

1) The primary key can also be composed of multiple columns. For example, the primary key in the orderitems table is as follows:

Primary key (order_num, order_item)

Primary keys can only use columns that do not allow NULL values.

2) Each table only allows one AUTO_INCREMENT column. You can use SELECTlast_insert_id () to obtain the last AUTO_INCREMENT value.

3) use DEFAULT to specify the DEFAULT value

4) engine type

InnoDB is a reliable transaction processing engine that does not support full text search.

MEMORY is functionally equivalent to MyISAM, but because the data is stored in the MEMORY, the speed is very fast (especially suitable for temporary tables)

MyISAM is a highly personalized engine that supports full text search, but does not support transaction processing.

Engines can be mixed, but foreign keys cannot boast of engines.

21.2 update a table

The following information must be provided:

1) Name of the TABLE to be changed after ALTER TABLE

2) list of changes

Alter table vendors ADD vend_phoneCHAR (20); // ADD a new column

Alter table vendors DROP vend_phone; // delete a column

Define Foreign keys:

Alter table orderitems ADD CONSTRAINTfk_orderitems_orders foreign key (order_num) REFERENCE orders (order_num );

21.3 delete a table

Drop table MERs MERS;

21.4 rename a table

Rename table backup_customers TO mers MERS;

22. Use View

A view is a virtual table. Unlike tables that contain data, a view only contains queries that Dynamically Retrieve data when used.

A view does not contain any columns or data in the table. It contains an SQL query. A view is only used to view data stored elsewhere. Views do not contain data, so the data they return is retrieved from other tables. When adding or modifying the data of the Chinese mainland, the view returns the changed data. Each time you use a view, you must process any search required for query execution.

The view cannot be indexed or associated triggers or default values.

Usage View:

1) create view is used to CREATE a VIEW.

2) use show create VIEWviewname; to view the statement for creating a view.

3) Use DROP to delete a view. Its syntax is DROPVIEW viewname;

4) when updating a VIEW, you can use DROP and then CREATE, or use createor replace view directly.

22.1 use views to simplify complex connections

Mysql> create view productcustomers ASSELECT cust_name, cust_contact, prod_id FRO

M customers, orders, orderitems WHEREcustomers. cust_id = orders. cust_id AND order

Items. order_num = orders. order_num;

Query OK, 0 rows affected (0.13 sec)

To search for customers who have purchased the product TNT2, perform the following operations:

Mysql> SELECT cust_name, cust_contactFROM productcustomers WHERE prod_id = 'tnt2

';

+ ---------------- + -------------- +

| Cust_name | cust_contact |

+ ---------------- + -------------- +

| Coyote Inc. | YLee |

| Yosemite Place | Y Sam |

+ ---------------- + -------------- +

2 rows in set (0.00 sec)

22.2 reformat the retrieved data with the view

SELECT * FROM productcustomers;

22.2 view update

Sometimes, a view is updatable (INSERT, UPDATE, and DELETE can be used for them). adding or deleting rows to or from a view is actually adding or deleting rows to or from its base table.

However, you cannot update a view if the view definition has the following operations:

1) Grouping (using group by and HAVING)

2) join

3) subquery

4) and

5) Aggregate functions

6) DISTINCT

7) Export (calculate) Columns

23. Use stored procedures

A stored procedure is a collection of one or more MySQL statements for future use. It can be considered as a batch file, although they are not limited to batch processing.

Stored Procedures have three main advantages: simplicity, security, and high performance.

23.1 execute the Stored Procedure

MySQL calls the execution of stored procedures as a CALL. Therefore, MySQL calls the statement for executing stored procedures. CALL accepts the name of a stored procedure and any parameters that need to be passed to it.

CALL producpricing (@ pricelow, @ pricehigh, priceaverage );

The stored procedure named productpricing calculates and returns the lowest, highest, and average prices of the product. Stored Procedures can display results or do not display results.

23.2 creation process

Create procedure productpricing ()

BEGIN

SELECTAve (prod_price) AS priceaverage

FROMproducts;

END;

This stored PROCEDURE is named productricing and defined using the create procedure productpricing () statement. If the Stored Procedure accepts parameters, they are listed in. This stored procedure does not have parameters, but is still required after. The BEGIN and END statements are used to limit the stored procedure bodies. The procedure bodies are only a simple SELECT statement.

When MySQL processes this code, it creates a new storage process productpricing. No data is returned because this code does not call the stored procedure. It is only created for future use.

If you use the MySQL command line program, because the default MySQL statement Terminator is;, it is stored in the body. The characters will not eventually become the component of the stored procedure, which leads to syntax errors. The solution is to use the new statement separator. DELIMITER //

Create procedure productpricing ()

BEGIN

SELECTAvg (prod_price) AS priceaverage

FROMproducts;

END //

DELIMITER;

Here, DELIMITER // tells the command line utility to use // as the new statement Terminator. You can see that the END defines END // instead of END as the END of the stored procedure. Finally, use DELIMITER; to restore the original statement Terminator. The call result is as follows:

Mysql> CALL productpricing ();

+ -------------- +

| Priceaverage |

+ -------------- +

| 1, 16.133571 |

+ -------------- +

1 row in set (0.30 sec)

Query OK, 0 rows affected (0.30 sec)

23.2 delete a stored procedure

Drop procedure productpricing; // an error is returned if the error does not exist.

Drop procedure productpricing if exists; // this operation is only deleted IF it EXISTS. IF it does not exist, no error is returned.

23.3 use parameters

Generally, the stored procedure does not display the result, but returns the result to the specified variable.

Variable: a specific location in the memory, used to temporarily store data. All MySQL variables must start.

Create procedure order (IN onumberINT, OUT ototal DECIMAL (8, 2 ))

BEGIN

SELECTSum (item_price * quantity)

FROMorderitems

WHEREorder_num = onumber

Using ototal;

END;

This stored procedure accepts two parameters, each of which must specify the parameter type. The keyword "IN" indicates that the parameter is used to pass to the stored procedure, including "OUT" and "INOUT ). The stored procedure code is in the BEGIN and END statements.

To call this stored procedure, use the following statement:

CALL ordertotal (2005, @ total );

To display this collection, as shown below

SELECT @ total;

To obtain the total information of another order, you need to call the stored procedure again

CALL ordertotal (2009, @ total );

SELECT @ total;

23.4 create a smart storage process

Only when the Stored Procedure contains business rules and Intelligent Processing, their power is truly apparent.

Create procedure ordertotal (

INonumber INT,

INtaxable BOOLEAN,

OUTototal DECIMAL (8, 2)

) COMMENT 'obtain order total, optionallyadding tax'

BEGIN

-- Declare variable for total

DECLARE total DECIMAL (8, 2 );

-- DECLARE tax percentage

DECLARE taxrate int default 6;

-- Getthe order total

SELECT Sum (item_price * quantity)

FROM orderitems

WHERE order_num = onumber

INTO total;

-- Is this taxable?

IFtaxable THEN

-- Yes, so add taxrate to total

SELECT total + (total/100 * taxrate) INTO total;

ENDIF;

-- And finally, save to out variable

SELECT total INTO ototal;

END;

The distinct nt keyword is not required here, but if it is given, it will be displayed in the result of show procedure statu.

In addition to the IF statement, MySQL also specifies the ELSEIF and ELSE clauses (ELSEIF must also use THEN, ELSE is not used)

The following result 1 is displayed:

Mysql> CALL ordertotal (20005, 0, @ total );

Query OK, 0 rows affected (0.05 sec)

Mysql> SELECT @ total;

+ -------- +

| @ Total |

+ -------- +

| 1, 149.87 |

+ -------- +

1 row in set (0.00 sec)

The following result 2 is displayed.

Mysql> CALL ordertotal (20005, 1, @ total );

Query OK, 0 rows affected, 1 warning (0.00sec)

Mysql> SELECT @ total;

+ -------- +

| @ Total |

+ -------- +

| 1, 158.86 |

+ -------- +

1 row in set (0.00 sec)

23.5 check stored procedures

To display the CREATE statement used to CREATE a stored PROCEDURE, use the show create procedure statement:

Show create procedure ordertotal;

To obtain a list of stored procedures that contain detailed information such as when and who created them, use show procedure status and use LIKE to specify a filter mode. For example

Show procedure status like 'ordertotal ';

24. Use a cursor

Sometimes, you need to forward or backward one or more rows in the retrieved rows. This is why the cursor is used. A cursor is a database query stored on the MySQL server. It is not a SELECT statement but a structure set retrieved by this statement. After the cursor is stored, the application can scroll or browse or change the data as needed.

24.1 use a cursor

Steps:

1) You must declare a cursor before using it. This process does not actually retrieve data. It is only a SELECT statement used by ing Pharmaceuticals.

2) once declared, the cursor must be opened for use. In this process, the data is actually retrieved using the SELECT statement defined earlier.

3) for the cursor with data filled, retrieve the rows as needed

4) The cursor must be closed when the end cursor is used.

24.2 create and use cursor data

After a cursor is opened, you can use the FETCH statement to access each row of the cursor. FETCH specifies the data to be searched and where the retrieved data is stored. It also moves the internal row pointer in the cursor forward so that the next FETCH statement can retrieve the next row.

Example:

Create procedure processorders ()

BEGIN

-- Declarelocal variables

DECLAREdone boolean default 0;

DECLAREo INT;

DECLAREt DECIMAL (8, 2 );

-- Declarethe cursor

DECLAREordernumbers CURSOR

FOR

SELECTorder_num FROM orders;

-- Declarecontinue handler

Declarecontinue handler for sqlstate '000000' SET done = 1;

-- Createa table to store the results

Createtable if not exists ordertotals

(Order_numINT, total DECIMAL (8, 2 ));

-- Openthe cursor

OPENordernumbers;

-- Loopthrough all rows

REPEAT

-- Getorder number

FETCH ordernumbers INTO o;

-- Getthe total for this order

CALL ordertotal (o, 1, t );

-- Insertorder and total into ordertotals

INSERTINTO ordertotals (order_num, total)

VALUES (o, t );

-- ENDOF LOOP

UNITLdone end repeat;

-- Closethe cursor;

CLOSEordernumbers;

END;

In this example, FETCH is within REPEAT, so it is executed repeatedly UNTIL done is true (as specified by UNTIL done end repeat ). To make it work, define the variable done with a DEFAULT 0. Then declare continue handler for sqlstate '000000' SET done = 1; this statement defines a CONTINUEHANDLER, which is the code executed when the condition appears. Here, it indicates that SET done = 1 when SQLSTATE '200' appears. SQLSTATE '200' is an unfound condition. This condition occurs when REPEAT cannot continue because there are no more row loops. Another Stored Procedure CALL ordertotal (o, 1, t) is also called here; this is the stored procedure created in the previous chapter to calculate the total tax amount of each order. This stored procedure does not return data, but it can create and populate another table.

You can run the stored procedure and view the stored results using the following statement:

Mysql> CALL processorders ();

Mysql> SELECT * FROM ordertotals;

+ ----------- + --------- +

| Order_num | total |

+ ----------- + --------- +

| 20005/158.86 |

| 20006/58.30 |

| 20007/1060.00 |

| 20008/132.50 |

| 20009/40.78 |

+ ----------- + --------- +

6 rows in set (0.00 sec)

In this way, we get a complete working example of stored procedures, cursors, row-by-row processing, and stored procedures calling other storage projects.

25. Use Triggers

25.1 trigger

A trigger is a MySQL statement automatically executed by MySQL in response to any statement (or a group of statements between the in and END statements ):

DELETE

INSERT

UPDATE

25.2 create a trigger

When creating a trigger, you must provide four pieces of information:

1) Unique trigger name;

2) tables associated with the trigger

3) events that the trigger should respond to (DELETE, INSERT, or UPDATE)

4) trigger execution time (before or after processing)

Only tables support triggers, but views do not. Temporary tables are not supported ).

Create trigger newproduct after insert ONproducts for each row select 'd ';

Create trigger is used to CREATE a new TRIGGER named newproduct. The trigger can be executed before or AFTER an operation. The after insert statement is provided, so the trigger will be executed AFTER the INSERT statement is successful. This trigger also defines the for each row, so the code executes for each inserted ROW. In this example, the text Product added is displayed once for each inserted row. PS. In my MySQL version, if the implementation fails, the system will prompt that the result set Not allowed to return a result set from a trigger error cannot be returned. I do not know whether it is a MySQL version issue or an error that is required by MySQL.

A trigger is defined at each time of each table. Each table can only define one trigger at a time. Therefore, a table supports a maximum of six triggers (before or after INSERT, UPDATE, and DELETE ).

25.3 Delete trigger:

Drop trigger newproduct;

25.4 use a trigger

25.4.1 INSERT trigger

1) In the INSERT trigger code, you can reference a virtual table named NEW to access the inserted row;

2) In the before insert trigger, the value in NEW can be updated (the inserted value can be changed)

3) for the AUTO_INCREMENT column, NEW contains 0 before INSERRT execution, and NEW automatically generated values after execution.

Create trigger neworder after insert ONorders for each row select new. ORDER_num;

25.4.2DELETE trigger

1) In the DELEYE trigger code, you can reference a virtual table named OLD to access the deleted row.

2) values in OLD are all read-only and cannot be updated.

Create trigger deleteorder before delete ONorders

FOR EACH ROW

BEGIN

INSERT

Archive_orderss (order_num, order_date, cust_id)

VALUES (OLD. order_num, OLD. order_date, OLD. cust_id );

END;

25.5 UPDATE trigger

1) You can use the OLD virtual table to access the previous value, or use the NEW virtual table to access the NEW value.

2) In the beffore update trigger, the value of NEW may also be updated.

3) All values in OLD are read-only and cannot be updated.

26. Manage Transaction Processing

Transaction processing can be used to maintain the integrity of the database. It ensures that batch MySQL operations are either fully executed or completely not executed.

Terms:

Transaction: refers to a group of SQL statements.

Rollback: cancels a specified SQL statement.

Submit: writes unstored SQL statement results to the database table.

Reserved point: the temporary placeholder set in the value transaction processing. You can release a rollback for it.

26.1 control transaction processing

The key to managing transaction processing is to split SQL statement components into logical blocks and specify when data should be rolled back and when data should not be rolled back.

Start transaction // mark as the START of the TRANSACTION

26.2 use ROLLBACK

ROLLBACK is used to roll back MySQL statements.

SELECT * FROM ordertotals;

Start transaction;

Delete from ordertotals;

SELECT * FROM ordertotals;

ROLLBACK;

SELECT * FROM ordertotals;

Run the preceding statement in sequence to check that the deleted table content is rolled back.

ROLLBACK can only be used within one transaction (after executing a STARTTRANSACTION command ).

26.3 use COMMIT

Generally, MySQL runs and writes the database tables directly, which is an implicit commit. However, in the transaction processing block, commit is not performed implicitly. For explicit submission, use the COMMIT statement as follows:

Start tranaction;

Delete from orderitems WHERE order_num = 20010;

Delete from orders WHERE order_num = 20010;

COMMIT;

If the first entry takes effect and the second entry fails, the transaction is not processed, that is, the two DELETE operations are not committed.

26.4 use retention point

A simple ROLLBACK and COMMIT statement can be used to write or cancel the entire transaction. However, it is only for simple transaction processing that is easy to do. More complex transaction processing may require partial commit or rollback.

To support partial transaction rollback, you must use the retention point. You can use the SAVEPOINR statement as follows:

SAVEPOINT delete1;

Each reserved point represents its unique name, so that MySQL knows where to roll back.

Rollback to delete1;

26.5 change the default submission Behavior

To indicate that MySQL does not submit changes automatically, use the following statement:

SET autocommit = 0;

27. Globalization and Localization

CREATETABLE mytable

(

Column1 INT,

Column2 VARCHAR (0)

) Deault character set hebrew CLLATEhebrew_general_ci;

This statement creates a table and sets a character set and a checking order.

Show character set; // you can view the complete list of supported CHARACTER sets.

Show collation; // view the complete list of supported proofreaders

28. Security Management

Manage Users

USE mysql;

SELECT user FROM user;

Mysql database has a table named user, which contains all user accounts. The User table has a user column.

28.1 create a user account

Use the create user statement to CREATE a new USER account

Create user ben identified by 'p $ w0rd ';

Create a user named ben with the password P @ $ w0rd.

Rename user ben TO bforta;

28.2 delete a user account

Drop user bforta;

28.3 set Access Permissions

Mysql> show grants for bforta;

+ -------------------------------------------------------------------------------

------------------------ +

| Grants for bforta @ %

|

+ -------------------------------------------------------------------------------

------------------------ +

| Grant usage on *. * TO 'bforta '@' % 'identified by password' * A6210E6C376AADB5A6

9274F8C3D15B788433A9EB '|

+ -------------------------------------------------------------------------------

------------------------ +

1 row in set (0.00 sec)

The output shows that user bforta has a permission usage on *.*. USAGE indicates that you have no permissions, so usage on *. * indicates that you have no permissions ON anything in any database or table.

To use GRANT to set permissions, you must provide the following information:
1) permissions to be granted

2) databases or tables granted access permissions

3) User Name

Grant select on crashcourse. * TO bforta;

Grant the SELECT permission to bforta in crashcourse.

The permissions shown here are as follows:

Show grants for bforta;

+ -------------------------------------------------------------------------------

------------------------ +

| Grants for bforta @ %

|

+ -------------------------------------------------------------------------------

------------------------ +

| Grant usage on *. * TO 'bforta '@' % 'identified by password' * A6210E6C376AADB5A6

9274F8C3D15B788433A9EB '|

| Grant select on 'crashcourse'. * TO 'bforta '@' %'

|

+ -------------------------------------------------------------------------------

------------------------ +

2 rows in set (0.00 sec)

You can use REVOKE to cancel permissions.

Revoke select on crashcourse. * FROM bforta;

28.4 Change Password

Update User Password

Set password for bforta = Password ('n3wp @ $ w0rd ');

Set your own password

Set passwor = Password ('n3w p $ w0rd ');

Chapter 4 Database Maintenance

Analyze table: used to check whether the key is correct

Check table: used to CHECK tables for many problems