This is the 16th day of my participation in the August More Text Challenge. For details, see:August is more challenging

The view,

1.1 an overview of the

1, a view is a virtual table, it said part of the data of a table or list of more comprehensive data, its structure and the data is based on query of table 2, the view is not to store data, but in view of the referenced original table (table) in 3, with a piece of the original table, according to the different needs of different users, you can create a different view

1.2 role

2. Prevent unauthorized users from accessing sensitive data 3. Increase data compatibility (database field name changes, the program can not change the field name) 4

1.3 Creating a View

Grammar:Create view as SELECT statement;Such as:

1.4 Modifying a View

Grammar:Alter view view name as select statement;Ex. :

1.5 Deleting a View

Grammar:Drop View [if exists] View 1, View 2,... ;Ex. :

1.6 Viewing View Information

Method 1: Show tables; Displays all tables and views

Method 2: View information is stored in the views table under information_schema.Query the structure of the view:

1.7 View Algorithm

To identify the highest performing boys and girls in Chinese: Method 1:Method 2:You can see that the default is merge.

The algorithm of the view is:

Merge the view statement and the outer statement before executing

2, temporary table algorithm (temptable)

Execute the view as a temporary table

View the statement that created the view:The default view is created with no algorithm defined. I wrote undefined, but I usually use merge in views. Method 2 Correct the temporary table algorithm:

Second, the transaction

2.1 an overview of the

A TRANSACTION is a group that either executes together or does not execute together. These operations are committed to the system as a whole, and either all or none of them are executed. 3. Transactions are an indivisible logical unit of work

2.2 Transaction Features

A transaction must have the following four properties (ACID properties) : 1. Atomicity: a transaction is a complete operation and its steps are indivisible (atomic). 2. Consistency: Data must be in a consistent state when the transaction completes. 3. Isolation: All concurrent transactions making changes to data are isolated from each other. 4. Permanent: After the transaction completes, its changes to the database are persisted forever.

2.3 Transaction Processing

1. Start the transaction

startThe transaction orbegin [work]
Copy the code

2. Commit the transaction

commit
Copy the code

3. Roll back transactions

rollback
Copy the code

Examples:

  1. First create test table and data:

Change the default execution symbol (previously, the default is semicolon execution)

  1. Start the transaction and change

3. The rolled backRollback to the initial state.It is useless to commit a transaction and then roll back.

4. Set the rollback point of the transaction

Each SQL statement is a separate transaction.

6. Summary:

3. Only InnoDB engine supports transactions. 4. A SINGLE SQL statement is an independent transactionCopy the code

Three, index,

3.1 an overview of the

Advantages: faster query speed

Disadvantages: 1. Tables with indexes require more storage space in the database. 2. Commands that manipulate data require longer processing time because they require indexes to be updated

3.2 Guidelines for Creating indexes

Columns suitable for creating indexes

1. This column is used for frequent searches. 2. This column is used for sorting data. 3Copy the code

Do not use the following columns to create indexes:

The table contains only a few rows. It may not be cost-effective to create an index for a small table because the amount of time mysql spends searching for data in the index is in the tableCopy the code

3.3 Creating an Index

Select * from primary key where primary key is created; select * from primary key where primary key is created; select * from primary key where primary key is created; select * from primary key where primary key is created;Select * from table where index = unique;Create unique index by modifying table:3. Common indexAdd a normal index to the tableModifying a common index4, summary

1, create a primary key to create a primary key index 2, create a unique key to create a unique index 3, the database automatically select the index according to the query statementCopy the code

3.4 Deleting an Index

Grammar:Drop Index Index name on Table name

Four, functions,

4.1 digital class

1. Get a random number

Scenario 1: Select a student at random2. Rounding

3. Intercept data4, take the round up the round

Take down the whole

4.2 String Classes

1, case conversion2. Intercept the string3. String concatenation

4,coalesce(str1, str2) Str1 is displayed if str1 has a value, and STR2 is displayed if str1 does not5. Length () indicates the length of bytes, and char_length() indicates the length of characters

4.3 time class

1. Time stamp

2. Format the timestamp

3. Obtain the current time

4, obtain year, month, day, hour, minute, second5, week, month, day6. Date subtraction

4.4 Encryption Functions

1, the md5 ()

2, sha ()

5. Pretreatment

Precompiled statements can be executed multiple times to solve the problem of frequent execution of a single SQL statement.

preparePreprocessing namefrom 'the SQL statement'
Copy the code

Perform preprocessing:

executePreprocessing name [usingVariable]Copy the code

1. Processing without parameters2. Preprocessing with one parameter

3, summary

In MySQL, variables start at @, and assign values to them via set. 2.? Represents a placeholder.Copy the code

On the way to learning MySQL, if you find this article helpful to you, then please focus on like and comment 3 times, thank you, your must be another support of my blog.