I like reading books in the evening. I can write down some notes clearly and quietly, which only I can understand.

(This junk was found)

  • Basis of essays

  • MySQL characters are case insensitive

  • Escape: Method 1: \ backslash used to escape wildcards example: Li _ -> should be written as’ Li \ _ ‘

Method 2: use keywords ESCAPE an chestnuts: li _ – > should be written as’ l | _ ‘ESCAPE’ | ‘

They all mean the same thing, they all say that the next one needs to be escaped

  • REGEXP operator regular expression -> see MySQL > P106 (no book random baidu, or click the address of the blog -> link <- Baidu find other people’s blog, not always valid, actually use less, and regular rules are the same)
  • GROUP BY

GROUP CONCAT (SEPARATOR “”) -> Specifies the symbol to use for the concatenation

WITH ROLLUP

In the end added a column of content for the total amount/total to see the demo text can not describe (SAO operation, very good)

www.cnblogs.com/phpper/p/93…

www.cnblogs.com/phpper/p/69… So let’s combine that

  • Join queries

Inside left outside right outside cross

The nice thing about UNION query [ALL] is that it is mostly used in statistical SQL statements, and it has the advantage of using upper indexes (in the case of non-abandonment) and reducing the need to write subqueries. ORDER BY, LIMIT also works, just put it at the end.

  • ANY SOME ALL MySQL has been used for a long time, I didn’t know there were these keywords…

The functions are as follows:

PS: The commodity table should also have a field associated with the left table commodity type, not reflected, know OK

Examples of keyword usage:

“ANY” and “SOME” satisfy only one of them. “ALL” satisfy ALL of them

  • Correlation subquery

The EXISTS keyword

For example: a user table an order table queries users who have placed orders

SELECT * FROM user 

         WHERE EXISTS ( SELECT * FROM order WHERE user.uid = order.uid )

LEFT JOIN and INNER JOIN can also be used to blindly guess which one is more efficient.

The index

InnoDB and MyISAM’s cookie-cutter theory

MyISAM is suitable for data storage

InnoDB supports transactions to make the difference

MyISAM has a spatial index in its index type

This is the same thing, which is probably used for coordinate geolocation-dependent tables, what OpenGIS is involved, and the corresponding data type is also used for spatial data types… So let’s stop talking about that

InnoDB engine default

Supported indexes

InnoDB only supports the BTREE index method. InnoDB only supports the BTREE index method.

But even if you do, it will default to BTREE because it doesn’t support HASH

Index type:

Plain indexes simply allow null and duplicate

Unique indexes do not allow duplicates. Values are allowed to be null (since duplicates are not allowed, there must be only one null)

Composite index (composite index/multi-column index) A composite index has three columns from left to right. To use the index, you must use the left column of the composite index. For example, 1, 2, 3, 123, 12, 1, 13, no, 23, no, 3

Full text index/spatial index omitted… (Insufficient height)

Index design principles keep in mind and practice the rest

View:

Advantages:

(1) Database views allow you to simplify complex queries: Database views are defined by SQL statements associated with many underlying tables. With the database view, you only need to use simple SQL statements instead of complex SQL statements with multiple connections

It’s just a package that’s supposed to go slow

(2) Database views help restrict data access to specific users. You may not want all users to be able to query a subset of sensitive data. You can use database views to display non-sensitive data only to specific groups of users.

Seeing only the view’s fields without knowing the library/table limits the permissions on what data to see

(3) Database views provide an additional layer of security. Security is an important part of any relational database management system. The database view provides additional security for the database management system. Database views allow you to create read-only views to expose read-only data to specific users. Users can only retrieve data in a read-only view, but cannot update it

For example, in a business scenario, I bind the ID of the user table in the order table, and at the same time redundant user names, or some other high frequency fields, there is no need to associate when querying, but in this case, the maintenance of the data becomes a problem. You can use views to solve these scenarios. But several companies I have worked for have not done so (I don’t know the specific reason).

The advantage of updating the aspect view is that when you change the view data, the data in the database table also changes.

(4) Database view to achieve backward compatibility. Suppose you have a central database that many applications are using. One day, you decide to redesign your database to accommodate new business requirements. Drop some tables and create new ones, and don’t want the changes to affect other applications. In this case, you can create a database view of the same schema as the old table to be dropped

This approach enables logical data isolation, creating views or changing tables to ensure that the original application is still available. (Fields already defined in the view cannot be deleted or changed)

Disadvantages:

Performance: Still running the original complex statement just wrapped

Table dependencies: Create a view based on the underlying tables of the database. So when you change the table, you also change the view.

Write efficient data queries

Optimize data access without requesting unwanted data

Query overhead (response time query scanned rows + return rows)

2 EXPLAIN Execution plan See the possible_keys + type column

The higher the level of the Type column, the better the performance

Possible_keys indicates the index used

If the key column has a value, it indicates that the key value is a better index. You can optimize the query by modifying query conditions

Continuously updated… Until I finish reading this book