MySQL learning notes

MySQL learning notes

Database: A database is a collection of data stored in an organized manner. It is essentially a container, usually one or a group of files.

 

Table: Represents a structured file that can be used to store certain types of data.

 

Mode: Describes the specific tables in the database and the relationship between the entire database and the tables in the database. Tables have some features that define how data is stored in tables, what data can be stored, how data is decomposed, and how information about each part is named.

 

Column: A field in a table. All Tables are composed of one or more columns.

 

LineA row is an independent record in a table that contains information about all columns.

 

Data Type: Data type restrictions can be stored in the type red data type, and can help sort data correctly, and play an important role in optimizing disk usage.

 

Databases, tables, columns, users, permissions, and other information are stored in data and tables. mysql databases store user and permission information, the information_schema database stores databases, tables, columns, stored procedures, and other related information.

 

Retrieve Data: The SELECT statement is used to retrieve data from one or more tables. When using SELECT, at least two pieces of information must be provided: What to retrieve and where to retrieve.

 

If the query results are not explicitly sorted, the returned data sequence has no special significance. Unless you do need each column in the table, you are advised not to use the * wildcard. Retrieving unwanted columns usually reduces the search and application performance.

 

Restrict duplicate data: The DISTINCT keyword can be used to restrict the display of different worthy lists. The DISTINCT keyword is applied to all retrieved columns.

 

Restrict returned results: Use the LIMIT clause to LIMIT the number and content returned by the search results.

 

Sort and retrieve data: The order by clause can be used to sort the returned results. When multiple columns are sorted, they are sorted according to the specified column ORDER, that is, sort by the first column first, sort by the second column if the first column has the same sorting result, and so on. By default, the sorting direction of each column is ascending. If you specify a descending order, you must specify a special one. The DESC keyword is only applied to the column name directly before it.

Search criteria also become filter condition)

 

You can use SQL or application filtering to retrieve data. The application filter database server will return excess data, resulting in a waste of network bandwidth. At the same time, the client's processing of the database will greatly affect the application performance, in addition, the created application is completely not scalable. Therefore, we recommend that you filter data on the database server as much as possible.

 

The Orderby clause is located after the where clause.

 

Available operators of Where clauses include: =, <> ,! =, <,>, <=, >=,

 

Mysql is case-insensitive during matching.

All values in the between matching range, including the specified start value and end value

 

The NULL values and fields contain 0, empty strings (empty), or spaces. Check whether the values are NULL using the is null clause. For example: SELECT * FROM Ttable_nameWHERE column_name is null;

 

Query condition usage! = NULL value rows are not returned. To return NULL value rows, the is null clause must be used.

 

By default, AND has a higher operation priority than OR in SQL. In any WHERE clause that uses AND OR, the grouping operator specified by parentheses should be used. Do not eliminate ambiguity by default.

 

The IN operator performs the same functions as the OR operator. The biggest advantage of IN is that it can contain other SELECT statements, so that it can create a WHERE clause more dynamically.

 

NySQL allows NOT pairIN, BETWEEN, EXISTSStatement Inversion

 

To use wildcards in search clauses, you must use the LIKE keyword. LIKE indicates mysql, followed by the search mode that uses wildcard matching instead of direct equality matching for comparison.

 

Wildcard: % Percent indicates any number of occurrences of any character. NULL values cannot be matched.

_ The underline always matches any single character.

 

It takes more time to search by using wildcards than to perform direct equality matching.

 

Retrieving converted, computed, or formatted data directly from the database is much faster than performing a program on the client, because the database management system is designed to quickly and effectively complete such processing.

 

Concatenation field: In the MySQL statement of the Concat () function, you can use the Concat () function to concatenate multiple strings. Each string is separated by a comma.

 

Remove spaces: RTrim () removes spaces on the right, LTrim () removes spaces on the left, and Trim () removes spaces on both sides.

 

The preferred date format for MySQL is yyyy-mm-dd, for example, 2016-11-09.

The SQL data type is datetime, indicating the time and Date. If you only extract the time, you can use the Date () function to extract only the Date part.

If you want to retrieve all the data in a Year or Month, there are two methods: 1. Use the BETWEEN keyword to limit the date range. 2. Use the Year (date) = 2016 AND Month (date) function as scheduled) = 12 combination limits, so you do not have to worry about the actual number of days of each month or the situation of a leap month.

 

 

Summary data

MySQL provides five Aggregate functions:

AVG () returns the average value of a column

COUNT () returns the number of rows in a column. count (*) includes NULL for all rows;

Count (column) Counts specified columns, excluding NULL values.

MAX () returns the maximum value of a column.

MIN () returns the minimum value of a column.

SUM () returns the SUM

In MySQL, Aggregate functions are used to aggregate data. The first version of their returned results is much faster than computing in client applications and saves more resources, whether it is time resources or hardware resources.

 

 

GROUP DATA

Groups allow data to be divided into multiple logical groups for clustering and computing for each group. With rollup, you can perform the same statistics (SUM, AVG, COUNT…) based on the group statistics ...). Example: select id, UPLOAD_TIME, COUNT (*) AS num_count FROM table_nbame WHEREMONTH (UPLOAD_TIME) = 12 GROUP BY ID WITHROLLUP

 

The WHERE clause is used to filter out rows that do not meet the matching conditions. In a group, you can use the HAVING clause to further filter out groups that do not meet the conditions. WHERE is used to filter data groups, while HAVING is used to filter data groups.

 

Order by and group by, group by are used to GROUP data, and order by is used to sort output data. When two clauses are used together, the order by clause is used after the group by clause, in addition, data is grouped before being sorted and output during query.

 

The writing sequence of each clause in a SELECT statement: SELECT-> FROM-> WHERE-> GROUPBY-> HAVING-> order by-> LIMIT

 

 

Subquery

The query statements nested in other queries are subqueries.

1. Place the subquery in the where clause. The subquery result can be used as the query condition to filter data.

2. Use the subquery as a calculated field and the query result as the returned data

The most common use of subqueries is IN the IN operator of the WHERE clause and is used to fill the computed column.

 

 

Table join

The foundation of the relational database design is to minimize repeated occurrences of the same data. A relational table is designed to ensure that information is divided into multiple tables. One type of data is a table, and each table is associated with each other through some common values. This also reflects the connotation of the relationship.

 

Based on the relational database design, the relevant data is stored in multiple tables, and an effective means to retrieve the desired data using a single SQL statement is to use the join.

 

The key to creating a join is to specify all joined tables and how they are associated. Ensure that all connections have a WHERE clause.

 

Cartesian product: the number of rows retrieved from a table without join conditions is the number of rows in several joined tables.

 

Equivalent join based on the equality test between two tables, also calledInternal Connections. Isojoin: selecttable2.column1, table2.column1 from table1, table2 where table1.column2 = tabl2.column2

Standard form of internal join: selecttable1.column1, table2.column1 from table1 inner join table2 on table1.column2 = table2.column2

The join condition uses the ON statement rather than the WHERE clause.

 

When using subqueries, you can also use joins to retrieve data.

 

Auto join:Auto join is the two joins of the same table. In the same table, it may take two queries to retrieve the desired data. You can use only one SELECT statement to perform this search using subqueries.Auto joinIt can retrieve data from the same table more efficiently.

UseSubqueryThe example may be as follows: SELECT id, name FROM products WHERE vend_id = (SELECTvend_id FROM product WHERE id = 123 ). This statement retrieves the product information of the same manufacturer as the product with the id of 123.

UseAuto joinThe format is AS follows: SELECT p1.id, p1.name FROM products AS p1, products AS p2 WHEREp1.vend _ id = p2.vend _ id AND p2.id = 123

 

 

 

External connections: An internal join is a join established based on equal tests in two tables. An external join is more powerful than an internal join. It can contain rows that are not associated with the relevant table. That is to say, with external Association, you can retrieve the field information that does not have relevant association records in another table.

 

Combined Query:The UNION keyword can be used to combine the query results of two or more SELECT statements into a single query result set. Ensure that the UNION keyword is used,Each SELECT statement must contain the same columns, expressions, or aggregate functions.That is to say, the content of each SELECT query must be the same so that it can be combined into a single result set. The data types of each query column must be compatible. UNION statementDuplicate rows are automatically removed from the result set by default.. If you need to include duplicate rows, you can use the union all keyword.

 

Data insertion and system security: Using the MySQL security mechanism, you can disable the use of INSERT statements for each table or user.

INSERT statements are used to INSERT data. Do not use INSERT statements without specifying a specific column. In this way, you do not need to consider the impact of changes in the column sequence in the table structure.

MySQL uses a single INSERT statement to process multiple inserts, which is much more efficient than using multiple INSERT statements.

Use INSERT... The SELECT statement inserts the retrieved data into the table.

 

Database Engine: The database engine is used to manage and process data (for details)

Common MySQL engines:

InnoDB: supports transaction processing and does not support full text search.

MyISAM: extremely high performance. It supports full text search and does not support transaction processing.

MWMORY: The function is similar to MyISAM, but the data is stored in the memory rather than on the disk. It is fast and suitable for temporary tables.

 

Tables that use foreign keys to maintain the relationship must have the same engine type.

 

View: MySQL added support for the view function after MySQL 5. A view is not a table and does not contain any data. It contains all queries for data as needed, that is, a SELECT statement. In essence, a view provides a MySQL SELECT statement-level encapsulation. The view function simplifies the processing of complex data.

Role of view:

 

 

Stored Procedure:MySQL5 adds support for stored procedures. Stored Procedure is actually a function stored on the database server. Therefore, the stored procedure name must be enclosed in parentheses (). The stored procedure is simply a set of one or more MySQL statements saved for future use. When you need to use MySQL statements that are complex, especially including business rules and smart processing, you can easily get results using stored procedures.

 

Reasons for using Stored Procedures:

 

Create a stored procedure:

 

 

This example is a good example to illustrate the stored procedure function. The CREATEPROCEDURE procedure_name () statement is used to create a stored procedure. If this function has parameters that are accepted or returned, the parameters that are received or returned must be listed in, use the key of IN, OUT, or INOUT to specify whether the parameter is input or output, and specify the parameter data type. The BEGIN and END statements are used to limit the Stored Procedure Bodies. In the Stored Procedure body, SELECT... INTO stores the data to be returned in the return variable specified in () of the stored procedure name.

 

 

Trigger: A trigger is defined on a database table. Certain operations can be performed automatically when the INSERT, DELETE, and UPDATE events occur. The trigger must be unique in each table. Only real physical tables support triggers. Each event in each table only supports one trigger. A trigger can be automatically triggered before or after an event occurs. Therefore, each table supports a maximum of six triggers.

 

 

Transaction processing:A transaction is a set of SQL statements. Transaction processing is a mechanism used to manage MySQL operations that must be executed in batches to ensure that the database does not contain incomplete results. Through transaction processing, you can ensure that a group of operations will not be stopped midway, they will be executed as a whole, or they will not be executed at all. The most famous example to explain the necessity of transaction processing is the bank account transfer problem.

 

Terms related to transactions:

 

A simple ROLLBACK uses a ROLLBACK statement TO cancel the entire transaction. For complex transaction processing, you can specify the reserved point and use rollback to roll back TO the specified reserved point, in this way, partial commit or rollback can be implemented.

 

Security Management: The Security Foundation of the MySQL server is to ensure that users have appropriate access permissions to the data they need by controlling the permissions of users. To manage access control, you must create and manage user accounts.

The user account information of the MySQL database is stored in the user table of the mysql database.

 

Create a user account: Create user user_name identified by 'Password'; CREATE a USER account and its password. The new user does not have any permissions.

 

 

Delete User Account: Drop user user_name;

Set Access Permissions: Use the GRANT statement to set permissions for users. The information provided by the permission statement includes:

Example: grant select on database_name. * TO user_name;

This statement grants the user_name user the read-only permission on all tables in the database_name database.

Revoke Access Permissions: The REVOKE statement is the inverse operation of GRANT.

 

The access permission levels that can be controlled by GRANT and REVOKE statements: