Basic concepts of MySQL and concepts of MySQL

Basic concepts of MySQL and concepts of MySQL

 

This article is based on MySQL required knowledge meeting (Ben Forta, 2009). It is based on MySQL-5 and can be used as an in-depth study of the gargle before MySQL. (Basic statements, regular expressions, joins, full text search, addition, deletion, modification, query, stored procedures, cursors, triggers, transactions, and indexes ).

 

Basic statement 

Limit: Use limit (x, y) for paging;

 

NULL judgment: Select xxx from yyy where xxx is null;

 

Priority: Select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003 and prod_price> = 10; And takes precedence over or;

 

Match: % And _. The latter matches a single character;

 

Go empty: RTrim (xxx), remove all spaces on the right of the value; LTrim (xxx), remove spaces on the left of the value; Trim (), remove spaces on both sides;

 

Splicing and renaming: Select concat (vend_name, '(', vend_country, ') as vend_file from vendors;

 

String processing: Left (), Length (), Locate () to find a SubString, Lower () to small, Right (), Soundex (), SubString (), Upper () small to big;

 

Date and Time: AddDate (), AddTime (), CurDate (), CurTime (), Date (), DateDiff (), Date_Add (), Date_Format (), Day (), DayOfWeek (), Hour (), Minute (), Month (), Now (), Second (), Time (), Year (). MySQL Date Format: yyyy-mm-dd;

 

Data Processing: Abs () absolute value, Cos (), Exp () index, Mod () Division remainder, Pi (), Rand () random, Sin (), Sqrt () square Root, Tan ();

 

Aggregation: AVG () average, COUNT () rows, MAX () Maximum, MIN () Minimum, SUM () SUM;

 

Select clause order: Select, from, where, group by, having, order by, limit

 

Regular Expression 

REGEXP: Prod_name contains text 1000: select prod_name from products where prod_name REGEXP '000000' order by prod_name;

 

Match any character:... Where prod_name REGEXP '. 000 ';

 

BINARY: Regular Expression matching is case-insensitive. BINARY is required for matching, for example, where prod_name regexp binary 'jetpack. 000 ';

 

OR match: Where prod_name REGEXP '2017 | 100 ';

 

Match one of several characters: Where prod_name REGEXP '[123] Ton ';

 

Matching range: Where prod_name REGEXP '[1-5] Ton ';

 

Escape for matching special characters: Where vend_name REGEXP '\\.';

 

\ Is also used for metacharacters: \ f tab, \ n line feed, \ t tabulation, \ r carriage return, \ v vertical tabulation;

 

Connection and combination 

Equijoin:

 

 

Cartesian Product:

Select vend_name, prod_name, prod_price from vendors, products order by vend_name, prod_name;

 

Internal Connections:

 

Auto join:

It is equivalent:

 

Natural join (exclude multiple occurrences, each column returns only once ):

 

External join (contains rows without associated rows in the relevant table ):

Left (right) outer join: select all rows in the Left (right) edge table;

 

Full text search 

Two common MySQL engines: MyISAM (full-text search supported) InnoDB (full-text search not supported );

 

Start full text search:

 

Method:Macth () + Against ()

It is equivalent:

 

The former is in descending order of levels, and the latter is not; the former has an index and is faster.

 

You can add a computed column in the select statement:

 

Query Extension: MySQL scans data and indexes twice, and performs basic full-text search for the first time to find all rows matching the condition. à MySQL checks these matching rows, select all useful words, such as MySQL, and then perform full-text search. Use the original condition + all useful words.

 

 

Add, delete, modify, and query 

Generally used for deletionDelete, Faster deletion and useTruncate table(Delete the original table and create a new table ).

 

Mysql does not have the Undo button. You must use update and delete carefully.

 

Create a table:

 

 

Create multiple primary keys:

 

If the primary key is a single key, the values of a single column are unique. If there are multiple primary keys, the values are unique in combination.

 

Select last_insert_id () returns the lastAUTO_INCREMENTValue.

 

Engine type: MySQL has an internal engine for data management and processing. It creates tables through the engine and internally processes your queries and other requests.

 

Three engines:InnoDBIs a reliable transaction processing engine that does not support full text search;MEMORYThe function is equivalent to MyISAM, but the data is stored in the memory (not in the disk), which is fast (suitable for temporary tables );MyISAMHigh performance, supports full text search, and does not support transaction processing;

 

Add Column: Alter table vendors add vend_phone char (20 );

 

Delete column: Alter table vendors drop column vend_phone;

 

Stored Procedure 

Create a stored procedure:

 

Call Stored Procedure:

 

Delete:

 

 

Parameters Used:

 

Call the stored procedure with Parameters:

 

Obtain results:

 

Use both IN and OUT:

 

Call IN and OUT stored procedures:

 

Smart Stored Procedure:

 

Displays the create statement used to create a stored procedure.:

Show create procedure ordertotal;

 

Obtain detailed information such as when and who created it.:

Show procedure status like 'ordertotal ';

 

Cursor 

A cursor is a database query stored on the MySQL server. It is the result set retrieved by the query statement. After the cursor is stored, the application can scroll or browse data as needed. MySQL cursors can only be used for stored procedures (and functions ).

 

Create a cursor:

 

After a stored procedure is processed, the cursor disappears (used only for stored procedures ).

 

 

Open cursor:

OPEN ordernumbers;

 

Close cursor:

CLOSE ordernumbers;

 

UseFETCHAccess each row of the cursor, specify the data (column) to be retrieved, where the retrieved data exists, and move the internal row pointer of the cursor forward to retrieve the next row:

 

Use cursor to retrieve data cyclically(You can put any necessary processing in the loop ):

 

Perform actual processing on the Retrieved Data:

 

Trigger 

When a table is changed (DELETE, INSERT, UPDATE), some tasks are automatically processed.

 

Create a trigger: Four pieces of information are required: Unique trigger name, associated table, activity to be responded (delete, insert, update), and execution time (before and after processing ).

 

Triggers only support tables (views and temporary tables are not supported ).

 

Delete trigger:

 

INSERT trigger: Use the NEW virtual table to access the inserted rows.

 

DELETE trigger: Reference the OLD virtual table to access the deleted row:

The above example uses BEGIN... END is not mandatory, BEGIN... The benefit of END: supports multiple SQL statements.

 

UPDATE trigger: Reference the value before the OLD access and the value after the NEW access.

 

Transactions 

Transaction processing is used to maintain the integrity of the database. It ensures that the batch of MySQL is either completely executed or completely not executed.

 

Terms for transactions: rollback, commit, and retention points (temporary placeholders set in transaction processing, which can be released for rollback ).

 

Start of transaction:

 

Use ROLLBACK:

 

Use COMMIT: Generally, MySQL is automatically submitted by default. However, in the transaction processing block, commit is not performed by default:

 

Use retention point: Simple ROLLBACK and COMMIT write or cancel the entire transaction. Complex transaction processing requires partial commit or rollback. In this case, place a placeholder in the proper position of the transaction processing block.

 

Change default submission Behavior:

 

Index 

Create an index(INDEX: normal; primary key: UNIQUE and cannot be blank; UNIQUE: UNIQUE and not allowed to be repeated ):

 

 

Delete Index:

 

Scan the QR code of your public account (it_pupil)