[Sorting] MySQL depth

1. View

A view is a virtual table. Unlike tables that contain data, a view only contains queries for dynamically retrieved data during use (that is, including an SQL query). It is only a facility for viewing data stored elsewhere.

Basic View operations: (1) Create: Create view; (2) view: Show create view viewname; (3) Delete: Drop view viewname; (4) Update: Create or replace view.

Views can be nested, but cannot be indexed, or associated triggers or default values. Not all views are updatable. If MySQL cannot correctly determine the updated basic data, update (including insertion and deletion) is not allowed ).

Views cannot be updated: (1) grouping, using group by and having; (2) join; (3) subquery; (4) join; (5) clustering function, MIN/count/SUM; (6) distinct; (7) Export column.

Common View applications: (1) Hide complex SQL statements, which usually involve joins; (2) reformat the retrieved data; (3) filter unwanted data; (4) simplify the use of calculated fields.

Eg: Create view productcustomers
As

Select cust_name, cust_contact, prod_id

From MERs, orders, orderitems

Where customers. cust_id = orders. cust_id

And orderitems. order_num = orders. order_num;

Select cust_name, cust_contact from
Productcustomers where prod_id = 'tnt2 ';

 

2. Stored Procedure

A stored procedure is a set of one or more MySQL statements (actually a function) stored for future use. It can be considered as a batch processing file, although they are not limited to batch processing. There are three main advantages of using stored procedures: simplicity, security, and high performance.

(1) Run: Call procedure_name (parameter_list );

(2) create: Create procedure procedure_name (parameter_list) Begin ...... end;

(3) Delete: Drop procedureif exists procedure_name;

(4) view: Show create prodecure procedure_name; show procedure statur like '***';

Variable: a specific location in the memory for temporary data storage. All MySQL variables must start.

Eg: Create procedure
Productpricing (

Out PL decimal (8, 2), Out pH decimal (8, 2), out pa decimal (8, 2)

)

Begin

Select min (prod_price) into Pl from products;

Select max (prod_price) into pH from products;

Select AVG (prod_price) into PA from products;

End;

Call: Call productpricing (@ pricelow, @ pricehigh, @ priceaverage );

3. cursor

Cursor Is a database query stored on the MySQL server. It is not a SELECT statement but a result set retrieved by this statement, it is mainly used for interactive applications (locate the rows in the result set and determine whether the global variable @ fetch_status reaches the final state. Generally, when the variable is not equal to 0, it indicates an error or the final state is reached ), the user needs to scroll the data on the screen and browse or modify the data. MySQL cursors can only be used for stored procedures (and functions ).

Procedure for using a cursor: (1) Before using a cursor, you must declare/define it. This process does not actually retrieve data, but only defines the SELECT statement to be used. (2) once declared, you must open the cursor for use. In this process, use the SELECT statement defined earlier to retrieve the data. (3) for a cursor with data filled in, retrieve/retrieve rows as needed. (4) When the cursor ends, you must close the cursor.

The cursor is created using the declare statement. The declare name the cursor and define the corresponding SELECT statement. The where and other clauses are included as needed. Local variables defined with declare must be defined before any cursor or handle is defined, and the handle must be defined after the cursor.

Eg: Create procedure
Processorders ()

Begin

Declare ordernumbers cursor

For

Select order_num from orders;

End;

Open cursor: Open ordernumbers; # execute a query when processing an open statement to store the Retrieved Data for browsing and scrolling.

Close the cursor: Close ordernumbers; # Close to release all internal memory and resources used by the cursor

Use cursor data: After a cursor is opened, you can use the fetch statement to access each row of the cursor.

 

4. triggers

A trigger is a MySQL statement automatically executed by MySQL in response to the delete, insert, or update statement (or a group of statements between in and end ). Only tables support triggers, views do not, and temporary tables do not. A trigger is defined based on each event in each table. Each event in each table allows only one trigger at a time, therefore, each table supports a maximum of six triggers (before and after each insert, update, and delete ). A single trigger cannot be associated with multiple events or tables.

When creating a trigger, four pieces of information must be provided: (1) Unique trigger name; (2) Table associated with the trigger; (3) activity that the trigger should respond to; (4) when the trigger is executed (before or after processing ). If the before trigger fails, MySQL will not execute the requested operation; if the before trigger or statement itself fails, MySQL will not execute the after trigger. The trigger cannot be updated or overwritten. MySQL triggers do not support the call statement, that is, they cannot call stored procedures from triggers.

(1) Create: Create trigger newproduct after insert on products for each row select 'product added ';

(2) Delete: Drop trigger newproduct;

Insert trigger: run the insert statement before or after execution. (1) In the insert trigger code, you can use a virtual table named new to access the inserted rows. (2) In the before insert trigger, the value in new can also be updated, that is, the inserted value can be changed. (3) for the auto_increment column, new contains 0 before the insert operation, after an insert statement is executed, it contains a new automatically generated value.

Delete trigger: it is executed before or after the delete statement is executed. (1) In the delete trigger code, you can reference a virtual table named Old to access the deleted rows. (2) All values in old are read-only and cannot be updated.

Update trigger: the trigger is executed before or after the update statement is executed. (1) In the update trigger code, you can reference a virtual table named Old to access the previous value and reference a new virtual table to access the new value. (2) in the Before update trigger, the value in new may also be updated, that is, the value to be used in the update statement can be changed. (3) All values in old are read-only and cannot be updated.

Trigger should be used to ensure data consistency (case and format): the advantage of executing this type of processing in the trigger is that it always performs this processing and is transparent, it has nothing to do with client applications.

 

5. Transaction Processing

Transaction Processing (Transaction Processing) is used to maintain the integrity of the database and ensure that batch MySQL operations are either fully executed or completely not executed. Not all engines Support transaction processing. MyISAM does not support clear transaction processing management, whereas InnoDB does. 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.

Transaction: A group of SQL statements that are atomic, consistent, independent, and persistent.

Rollback: The process of revoking a specified SQL statement. rollback can only be used in one transaction, that is, after executing a start transaction command. Transaction processing is used to manage insert, update, and delete statements. You cannot roll back select statements or create or drop operations. You can use these two statements in transaction processing. However, if you perform rollback, they will not be revoked.

Commit: writes the unstored SQL statement structure to the database table. In the transaction processing block, commit is not implicitly performed and must be explicitly committed using commit. After a commit or rollback statement is executed, the transaction is automatically closed and future changes are implicitly committed.

Savepoint: a temporary placeholder set in transaction processing. It can be released and rolled back (different from the process of rolling back the entire transaction ); the retention point is automatically released after the transaction processing is completed (a rollback or commit is executed). You can also use the release savepoint to explicitly release the retention point.

Change default submission behavior: Set autocommit = 0; The autocommit flag determines whether to automatically submit the change, whether or not there is a commit statement; the autocommit flag is for each connection rather than the server.

 

6. Index

Clustered indexes determine the physical storage order of data in a table. A table can only contain one clustered index, but this index can contain multiple columns (composite indexes ). Clustered indexes are particularly effective for columns that frequently search for range values. When a clustered index is used to locate the row that contains the first value, it can be determined that the row containing the subsequent index value is physically adjacent.

The order of non-clustered indexes is independent of the physical order of data. indexes are stored in one place, data is stored in another place, and indexes are directed to the data storage location. Items in the index are stored in the order of the index key value, and the information in the table is stored in another order (as specified by the clustered index ). If no clustered index is created in the table, these rows cannot be in any specific order.