MySQL basic learning Summary

MySQL basic learning Summary
1. Basic concepts of MySQL

The logical architecture of mysql is as follows:

Each client connection will have a thread in the server, and the query of this connection will only be executed in this separate thread.

MySQL is a layered architecture. The upper layer is the server layer service and query execution engine, and the lower layer is the storage engine. Although there are many plug-in APIs with different functions, the storage engine API is the most important. If you can understand how MySQL interacts back and forth through APIS when processing queries between the storage engine and the service layer, You can grasp the essence of MySQL's core infrastructure.

The database system implements various Deadlock Detection and deadlock timeout mechanisms. InnoDB currently processes deadlocks by rolling back transactions that hold at least row-level locks. The MySQL service layer does not manage transactions. transactions are implemented by the underlying storage engine.

Note that SQL statements generally end with ';' or '\ G.

• Database: a container that stores organized data.
• Table: a structured list of specific types of data.
• Column: a field in a table. All Tables are composed of one or more columns.
• Data Type (datatype): The allowed data type. Each table column has a corresponding data type, which limits the types stored in this class.
• Row: a record in a table.
• Primary key: A column (or a group of columns). Its values can uniquely distinguish each row in the table.

 

2. Use MySQL

• Mysql-h host-u username-p establishes a connection with the database
• Use database_name select database
• Show databases show all databases
• Show tables: displays all tables in the current database
• Show columns from table_name: displays the column information of the table. The function is the same as that of desc table_name.

Other show statements supported by MySQL include:

• Show status: displays the MySQL server status.
• Show create database_name and show create table_name respectively display the MySQL statements for creating a specific database and data table
• Show grants shows the security permissions granted to users
• Show errors and show warnings Display Server error or warning information

 

3. search data

The most common SQL statement is the select statement, which is used to retrieve data in one or more tables. The example of select is as follows:

• Select column_name from table_name retrieve the column_name column from the table_name data table and retrieve a single column
• Select column1_name, column2_name from table_name to retrieve multiple columns
• Select * from table_name retrieve all columns

Note: select returns all matched rows. But what if we don't want each value to appear every time? For example, if you want to make the output id unique, you can add the distinct keyword in the select statement, select distinct id from goods. The result is as follows:

Sometimes we want to limit the output results. For example, to return the first or first few rows, use the limit clause, as shown below:

• Select * from goods limit 2 returns the results of the first two rows. Note that when the number of rows is insufficient, only the limited number of rows in the data table can be returned.
• Select * from goods limit 2, 2 returns two rows starting from row 2

Of course, we can also use full restrictions for data retrieval:

Select goods. id, goods. name from goods output all records in the goods table

 

4. sort and retrieve data

Sort data mainly uses the order by clause of the select statement to sort the retrieved data as needed. by default, the results returned by the select statement are not in a specific order, you can also specify the direction of sorting when sorting and retrieving data, such as ascending or descending. The order by clause is in ascending order by default.

(This is a descending order column)

What if we need to sort multiple columns? You can run the following SQL statement: select * from goods order by id desc, name. Note that IDs are sorted in descending order. If the IDs are the same, sort names in ascending order. To sort multiple columns in descending order, you must specify the desc keyword for each column.

Using the combination of order by and limit, we can find the highest or lowest value in a column. For example, here we also use the goods table for testing, add a num field (alter table goods add num int) to the goods table, add the corresponding value, and the content of the goods table is:

 

Find the most remaining fruit:

Note: The order by clause of the select statement sorts the retrieved data. This clause must appear in the last clause of the select statement. I don't know why...

 

5. filter data

In a select statement, data can be filtered Based on the filter conditions specified by the where clause. The where clause is given after the table name (from clause). For example, select id, name from goods where id = 2. This statement only displays the id and name of the record whose id is 2. NOTE: If both the where and order by clauses are used, the order by clause should be placed after the where clause; otherwise, an error will occur.

The where clause operator is as follows:

• = Equal
• <> Not equal
• ! = Not equal
• <Less
• > Greater
• > = Greater than or equal
• Between is between the specified two values (for example, select * from goods where id between 1 and 2)

When creating a table, we can specify whether the column can contain no value. When a column does not contain a value, its value is null, And the select statement has a special where clause, it is used to detect columns with null values. For example, if select * from goods where num is null, The num is null and worth record is printed.

 

 

6. Data Filtering

The commonly used select clause uses a single condition when filtering data. To Improve Filtering Control, you can use multiple where clauses. These clauses can be used in either of the following ways: use the and clause and the or clause.

• Select * from goods where id = 2 and num> 10 retrieve records whose id is 2 and whose num is greater than 10
• Select * from goods where id = 3 or num> 15 retrieve records whose id is 3 and whose num is greater than 15

If multiple and or statements are put together, the and operator is given priority. In this case, parentheses can be used to change the priority. Parentheses can also specify the condition range of the in operator. Each condition in the range can be matched.

The not operator in the where clause only has the effect of denying any condition after it.

 

7. Filter with wildcards

You can use the like operator for wildcard search to filter complex data.

% Indicates the number of times any character appears.

The underscore (_) wildcard used to match a single character rather than multiple characters.

Wildcards are useful, but they are costly. It generally takes longer time to search for wildcards than other wildcards. Here are some tips:

• Do not over-use wildcards. If other operators can achieve the same purpose, they should be used.
• When you really need to use wildcards, unless absolutely necessary, do not use them at the beginning of the search mode, put the wildcards at the beginning, and the search is the slowest.
• Note the wildcard location. If the location is incorrect, the expected result may not be returned.

 

8. Regular Expression

A regular expression matches text and compares a pattern (Regular Expression) with a text string. Mysql's where clause provides preliminary support for regular expressions, allowing you to specify regular expressions to filter data retrieved by select. Let's take a look at the table record first:

• Select * from goods where name regexp 'xiang' order by num desc retrieve all records with 'xiang' in name
• Select * from goods where name regexp 'incense. 'To retrieve all records with 'incense' in name, '.' indicates matching any character
• Select * from goods where name regexp 'fragrant | all records with 'fragrant 'or 'gua' in the name of the gua' search site

 

If the record matches the regular expression, it will be retrieved. The following regular expression can be used to repeat metacharacters for stronger control.

• * Match 0 or more
• + Match one or more (equal to {1 ,})
• ? Match 0 or 1 (equal to {0, 1 })
• {N} specifies the book target match
• {N,} is not less than the matching of specified data
• {N, m} matches the specified data range (m cannot exceed 255)
• ^ Start of Text
• $ End of Text
• [[: <:] Start of a word
• [[:>:] End of a word

Note: regexp and like play a similar role. The difference between regexp and like is that like matches the entire string and regexp matches the child string. Each expression is started through '^' using the positioning character, end each expression with '$' To make regexp play the same role as like.

 

9. Create a calculated Field

Concatenates values to form a single value. In mysql's select statement, you can use the concat () function to splice two columns.

We can see that the name of the new computed column is only a value, and we can use aliases to make it more meaningful. You can also perform arithmetic operations on the Retrieved Data. addition, subtraction, multiplication, and division are supported.

 

 

10. Use data processing functions

 

Common text processing functions

Function	Description
Left ()	Returns the word on the left of the string.
Length ()	Returns the length of a string.
Locate ()	Find a substring of a string
Lower ()	Convert string to lowercase
Ltrim ()	Remove spaces on the left of the string
Right ()	Returns the character on the right of the string.
Rtrim ()	Returns the character on the right of the string.
Soundex ()	Returns the soundex value of the string.
Substring ()	Returns the character of a substring.
Upper ()	Convert string to uppercase

 

11. Summary Data

Data needs to be summarized rather than retrieved. Therefore, mysql provides special functions for analysis and report generation. Common examples include determining the number of rows in the table, obtaining the sum of row groups in the table, and finding the maximum value (minimum or average) of the table column ). Clustering functions run on the row group, and calculate and put back a single worthy function. mysql provides five clustering functions, which are generally much faster than computing in their own client applications.

• AVG () returns the average value of a column
• COUNT () returns the number of rows in a column.
• MAX () returns the maximum value of a column.
• MIN () returns the minimum value of a column.
• SUM () returns the SUM of a column.

Use the distinct keyword to select only records of different num for calculation.

 

12. Group data

The SQL aggregate function can be used to aggregate data, so that we can count rows, calculate average values, and obtain the maximum and minimum values without retrieving all data. Creating groups allows you to divide data into multiple logical groups for aggregation and computing of each group. It involves the group by clause and having clause.

 

Before using the group by clause, you need to know some important rules:

• The group by clause can contain any number of columns, which enables nesting of groups and provides more detailed control for data groups.
• If a group is nested in the group by clause, the data is summarized in the group specified at the end. In other words, when a group is created, all specified columns are calculated together.
• Each column listed in the group by clause must be a search column or a valid expression, but not a clustering function.
• If a group column contains null values, null is returned as a group. If multiple null values exist, they are grouped into one group.
• The group by clause must appear after the where clause and before the order by clause.

In addition to grouping data with group by, mysql also allows filtering groups, specifying which groups are included and which groups are excluded. The having clause is used to filter groups, because where filters rows instead of groups. NOTE: where is used to filter data groups, while having is used to filter data groups.

Group by and order by often perform the same job, but they are very different:

Order	Group
Output by sorting	Group rows, but the output may not be the group order
Any columns can be used (or even non-selected columns)	Only select columns or expression columns can be used, and each selection column expression must be used.
Not necessarily required	If you use a column (or expression) with the aggregate function, you must use

The following is the sequence of the sub-statements in the select statement, which must be followed in the order used in the select statement.

Clause	Description	Required
Select	Column or expression to return	Yes
From	Table from which data is retrieved	Used only when selecting data from a table
Where	Row-level Filtering	No
Group	Group description	Used only when clustering is calculated by group
Having	Group-level Filtering	No
Order	Output sorting order	No
Limit	Number of rows to be retrieved	No

 

13. Use subquery

What is subquery? A subquery is a query nested in other queries. To use a subquery in a where clause, make sure that the select statement has the same number of columns as the where clause. Generally, the subquery returns a single column and matches it with a single column.

 

14. Join table

The foreign key is a column in a table. It contains the primary key value of another table and defines the relationship between the two tables. If the data is stored in multiple tables, you can use a single select statement to retrieve the required data. Ensure that all connections have a where clause. Otherwise, mysql returns more data than the desired one, because the number of rows retrieved is the number of rows in the first table multiplied by the number of rows in the second table.

Join is the most important and powerful feature in SQL. A relational table is designed to ensure that information is divided into multiple tables, one type of data and one table. Each table is associated with each other through some common values (relational in relational design.

SELECT vend_name, prod_name, prod_price FROM vecdors, products WHERE vendors. vend_id = products. vend_id order by vend_name, prod_name; create a connection table

The WHERE clause is used as a filter condition. It only contains rows that match the given condition. Without the WHERE clause, each row in the first table will be paired with each row in the second table, regardless of whether they can be paired logically. SQL has no limit on the number of tables that can be joined in a SELECT statement.

An internal join is a join based on the equality test between two tables. In fact, for this join, you can use a slightly different syntax to specify the join type. The following SELECT statement returns data exactly the same as the preceding example.

SELECT vend_name, prod_name, prod_price FROM vendors inner join products ON vendors. vend_id = products. vend_id;

 

15. Create an advanced connection

In addition to column names and calculated field aliases, you can also create aliases for tables.

In addition to simple links using internal connections (or equivalent connections), you can also use self-joins, natural joins, and external links.

• Auto join: You can reference the same table more than once in a single SELECT statement.
• Natural join: Whenever a table is joined, at least one table (joined column) should be listed. The standard join (internal join) returns all data, even the same column appears multiple times. Natural joins exclude multiple occurrences so that each column is returned only once.
• External join: Many joins associate rows in one table with rows in another table, but sometimes they need to contain rows without associated rows.

 

• Select * from persion as haha; use the table alias
• Select proid_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'dtntr '); find the supplier of the item whose production ID is DTNTR, find other items produced by the supplier. Subqueries are used in this method. The following operations are performed using auto-join:
• SELECT p1.prod _ id, p1.prod _ name FROM products AS p1, products AS p2 WHERE p1.vend _ id = p2.vend _ id AND p2.prod _ id = 'dtntr ';
• SELECT MERs. cust_id, orders. order_num FROM customers inner join orders ON customers. cust_id = orders. cust_id; this is an internal JOIN method. The following is an external JOIN method.
• SELECT MERs. cust_id, orders. order_num FROM customers left outer join orders ON customers. cust_id = orders. cust_id;

 

16. Combined Query

Using the union operator and combining multiple SQL queries, the results of multiple select statements are combined into a single result set. Note: union must contain two or more select statements, in union, each query must contain the same column, expression, or aggregate function (but each column does not need to be listed in the same order ). Union automatically removes duplicate rows. This is the default action. If you do not want this, use union all instead of union.

How can we sort the output of the union statement? When using the order by clause, it must be placed after the last select statement of union. For the result set, there can only be one sorting rule. Therefore, multiple order by clauses are not allowed.

 

17. insert data

Insert statements generally generate output. Generally, only the number of affected rows is printed. If no column name is provided during insert, a value must be provided for each column. If a column name is provided, a value must be given for each column listed. Otherwise, an error is returned.

Insert a complete row:

Insert multiple rows:

 

18. Update and delete data

Use the update statement and delete statement to update and delete data respectively. First, clear a portion of the data in the goods table. The data in the goods table is as follows:

• Update goods set num = 1 where name = 'apple'; at this time, the num of the corresponding Apple changes to 1
• Delete from goods where num = 7; delete records whose num is 7, that is, records whose name is orange.

Note: Here are some notes for using the update and delete statements:

• Unless you plan to update and delete each row, do not use an update or delete statement without the where clause.
• Make sure that each table has a primary key and try to use it like the where clause.
• Before using the where clause for an update or delete statement, you should first use select for testing to ensure that the correct records are filtered to prevent incorrect where clause.
• Mysql has no revocation function. Be careful when using the update or delete function.

 

19. Create and manipulate tables

When using create table to create a table, you must give the table name. Only the keyword create table is given. The table column names and definitions are separated by commas.

create table vendors (    vend_id int not null auto_increment,    vend_name char(50) not null,    vend_address char(50) null default '*',    vend_city char(40) null,    primary key (vend_id)) engine=InnoDB;

This statement creates a vendors table. vend_id and vend_name are required, and they are not null. Others are not required. null is the default value. The auto_increment keyword tells mysql that the column value is unique when a row is added. Each table can only have one auto_increment column, and it must be indexed (for example, by making it a primary key ). Default is the default value.

Note: The primary key must be unique, and each row in the table must have a unique primary key. If the primary key uses a single column, its value must be unique. If multiple columns are used, the combination of these columns must be unique. Foreign keys cannot break through the engine. Multiple engines are packaged in mysql. The following are the engines that need to be known:

• InnoDB is a reliable transaction processing engine that does not support full text search.
• MEMORY functions are equivalent to MyISAM, but because the data is stored in the MEMORY, all the speed is fast (especially suitable for using temporary tables ).
• MyISAMysql is a highly personalized engine that supports full text search, but does not support transaction processing.

Use the alter table statement to update the table structure.

 

Use the drop statement to delete a table (not its content, but the entire table. Drop table table_name; this statement deletes the table_name table. If it exists, note that the table is not confirmed and cannot be undone.

Rename the table using the rename table statement.

 

20. View

A view is a virtual table. Unlike a table that contains data, a view only queries data dynamically when used. A view is only a facility for viewing data stored elsewhere. A view itself does not contain data, so the data they return is retrieved from other tables. The view provides a MySQL SELECT statement-level encapsulation to simplify data processing, reformat basic data, or protect basic data.

Common applications using views:

• Reuse SQL statements
• Simplify complex SQL operations. After writing a query, You can reuse it easily without having to know the specific query details.
• Use the table components instead of the entire table
• Protect data and grant access permissions to specific parts of the customer table instead of all
• Changes the data format and format. The view can return data in a different format than the underlying table.

Use a view to simplify complex joins. Use the create view statement to create a view. Use show create view viewname to view statements for creating a view. Use drop to delete a view. The syntax is drop view viewname.

 

 

Generally, views are updatable (insert, update, and delete can be used for them). Updating a view updates its base table because the view itself does not have any data. However, not all views are updatable. If mysql cannot correctly determine the updated basic data, it cannot be updated. That is, if an operation is performed, the view cannot be updated: group, join, subquery, union, aggregate function, distinct, etc.

Reference

1. MySQL is required

2. High-Performance MySQL

This article permanently updates the link address: