MySQL Basics trapping Notes

In the MySQL tutorial | The Novice Tutorial system Review Some of the knowledge points have not mastered or the way of thinking has not fully followed the place, wrote a note, reasoning this note for any reader other than me has no practical value, Only for my review of the MySQL foundation process in the leakage of the vacancy.

MySQL query:

MySQL does not support full connection, so use left join connect +union+ right connection to achieve full connection.

The union operator in MySQL combines the result of connecting more than two SELECT statements into a single query result.

When multiple SELECT statements are union, duplicate data is deleted.

Null value handling:

Null=null returns false in MySQL, so use is null/is not NULL to manipulate a field with a value of NULL.

Any value =null returns false.

Regular Expressions:

Give a regular pattern for the regexp operator

Example of use of the regexp operator: Querying the fields in the user table all records with the name beginning with Wu

SELECT * from user where name regexp ' ^wu ';

MySQL transaction:

Usage Scenario: Handles data that is large in size and high in complexity. Large amount of operation refers to the data which is more correlated.

Only libraries and tables that use the InnoDB database engine in MySQL support transactions.

Use function: Maintain data integrity

Transactions meet 4 features (ACID):

Atomicity, consistency, isolation, persistence

Consistency: The integrity of the data is not compromised until the start of the transaction and after the end of the transaction

Isolation: Controls the level of concurrent access to transactions

MySQL Stored procedures:

Input parameter in:

The stored procedure can access the initial values of the input parameters and can be modified inside the stored procedure, but the results cannot be returned.

Output parameters out:

The stored procedure cannot access the initial value of the output parameter, it can be modified inside the stored procedure, and the result can be returned.

The difference between the input parameter and the output parameter can be understood as the difference between the value passing and the reference passing of the parameter passed in Java.

Input and OUTPUT parameters InOut:

The stored procedure can access both the initial value of the InOut parameter, and the value can be modified inside the stored procedure and the result returned.

Index:

Indexes are used to quickly access specific information in a table, which is a structure that sorts the values of one or more columns in a table, similar to a catalog of books.

Creating an index can improve query efficiency, but it can reduce the efficiency of updating tables because updating data not only preserves data, but also maintains index files in the table, so the use of the index is in a table with a large amount of data and less update operations.

MySQL sequence:

You can set a custom field other than the primary key.

The above is the knowledge point of the leak, the following is about the process of processing data in the way the application of thinking.

Duplicate data operations:

Prevent duplicate data from appearing: table design can be restricted by combining primary key restrictions on one or more columns that are not allowed to be duplicated, or by uniquely setting a unique value attribute for a single column.

Statistical data duplication: Statistics in the person table LastName, FirstName are not repeated and the number of records more than 1 data

Select COUNT (*) as rep, lastname,firstname from person

GROUP BY Lastname,firstname

Have on rep>1;

Mode of thinking:

Determine the repeating column LastName FirstName

Lists the repeating column and count (*) in the column selection list (the field after select)

List duplicate columns in GROUP by

Having clause sets the number of repetitions limit

Two actions to filter duplicate data:

1.select distinct ...

2.select ... group by x, y

Two actions to remove duplicate data:

1. Through temporary tables

Filter duplicate data into a staging table

Delete the original table

Rename a temporary table to the original table

2. Delete duplicate data in a table by adding an index or a primary key

Alter IGNORE table person add primary key (Lastname,firstname);--Multiple columns not duplicated

ALTER TABLE person add unique (firstname);--single row non-repeating

MySQL Basics trapping Notes