MySQL Basic Learning Summary

1. mysql Basic concept

The MySQL logical schema is as follows:

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

MySQL is a layered architecture. The upper layer is the server-level service and query execution engine, and the lower tier is the storage engine. While there are many different plug-in APIs that work, the storage Engine API is the most important. If you can understand how MySQL interacts with the API as it processes queries between the storage engine and the service layer, you can capture the essence of MySQL's core infrastructure.

The database system implements a variety of deadlock detection and deadlock time-out mechanisms, InnoDB is currently dealing with a deadlock mechanism is to hold the least row-level exclusive lock transactions to rollback. The MySQL service layer does not manage transactions, and transactions are implemented by the underlying storage engine.

Note that SQL statements generally take the words '; ' or ' \g ' ends.

• Database (database): A container that holds organized data.
• Table (table): A structured list of data of a particular type.
• column (column): A field in a table in which all tables are made up of one or more columns.
• Data Type (datatype): The type of data allowed, each table column has a corresponding data type, which restricts the types stored by the class.
• Line (row): is a record in the table.
• PRIMARY Key (primary key): is a column (or set of columns) whose value uniquely distinguishes each row in the table.

2. Using MySQL

• Mysql-h host-u username-p Connection to database
• Use database_name Select a database
• Show databases shows all the databases
• Show tables shows all the tables under the current database
• Show columns from TABLE_NAME displays the column information for the table, and the effect is the same as DESC table_name

Other show statements supported by MySQL are:

• Show status displays MySQL server state information
• Show create database_name and show create table_name separate MySQL statements for creating specific databases and data tables
• Show grants displays the security permissions granted to the user
• Show errors and show warnings display server error or warning messages

3. Retrieving data

The most common use of SQL statements is the SELECT statement, which is used to retrieve data in one or more tables, and the select uses the following example:

• Select COLUMN_NAME from table_name retrieving the COLUMN_NAME column from the TABLE_NAME data table, retrieving a single column
• Select Column1_name, column2_name from table_name retrieving multiple columns
• SELECT * FROM table_name retrieve all columns

Note that select returns all matching rows, but what if we don't want each value to appear every time? For example, to make the ID of the output unique, you can add the DISTINCT keyword to the SELECT statement, select DISTINCT ID from goods, and the result is as follows:

Sometimes we want to limit the results of the output, such as returning the first or previous rows, using the limit clause, as shown here:

• SELECT * FROM goods limit 2 returns the first 2 rows of results, note that when the number of rows is insufficient, only a limited number of rows in the data table can be returned
• SELECT * from goods limit 2, 2 returns 2 rows starting from line 2

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

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

4. Sort and retrieve data

Sort retrieves the data primarily using the ORDER BY clause of the SELECT statement, sorting the retrieved data as needed, the SELECT statement returns the result without a specific order by default, and when sorting retrieves the data, you can also specify the direction of the sort, such as ascending or descending, and the ORDER BY clause is sorted by default in ascending order.

(This is a descending column)

Sometimes, what do we need to sort through multiple columns? This can be done using the following SQL statement, select * from goods ORDER BY id DESC, name, note, here is the ID in descending order, if the ID is the same, the name is sorted in ascending order. If you want to sort multiple columns in descending order, you need to specify the DESC keyword for each column.

Using the combination of order by and limit, we were able to find the highest or lowest value in a column, for example, using the goods table as a test, adding a num field to the Goods table (ALTER TABLE goods add NUM int) and adding the corresponding value. The final goods table contains:

Find the fruit with the most remaining quantity:

Note: The ORDER BY clause of the SELECT statement sorts the retrieved data, which must appear in the last sentence in the SELECT statement. As for why, this I do not know yet ha ...

5. Filtering data

Select statement, the data can be filtered according to the filter criteria specified by the WHERE clause, given after the table name (FROM clause), for example, select ID, name from goods where id = 2, the statement displays only the ID and name of the record with ID 2. Note : If you use the WHERE and order by clauses at the same time, you should leave the ORDER BY clause behind where, or an error will result.

The WHERE clause operator is as follows:

• = equals
• <> Not equal to
• ! = does not equal
• < less than
• > Greater than
• >= greater than or equal to
• Between between the specified two values (e.g. SELECT * from goods where ID between 1 and 2)

When we create a table, we can specify whether the column can contain no value, when a column does not contain a value, its value is null Null,select the statement has a special WHERE clause that detects columns with null values, such as: SELECT * from goods where Num is Null The num is empty is worth recording to print out.

6. Data filtering

A common SELECT clause uses a single condition when filtering data, and for stronger filtering control, you can place multiple where clauses, which are used in the same way as and clauses and OR clauses.

• SELECT * from goods where id = 2 and num > 10 to retrieve records with ID 2 and num greater than 10
• SELECT * from goods where id = 3 or num > 15 to retrieve records with ID 3 and num greater than 15

If multiple and and or statements are placed together, the and operator is treated preferentially, and parentheses can be used to change their precedence. Parentheses can also specify the condition range of the in operator, and each condition in the range can be matched.

The not operator in the WHERE clause only has the effect of denying any conditions after it.

7. Filter with wildcard characters

Use the LIKE operator for a wildcard search to allow for complex filtering of the data.

Percent percent (%) operator search,% indicates any number of occurrences of any character.

The underscore (_) wildcard character, which is used to match a single character instead of multiple characters.

Wildcards are useful, but this is a cost, and the wildcard search process generally takes longer than other searches, and here are some tips:

• Do not overuse wildcards, and if other operators can do the same, you should use a different operator.
• When you do need to use wildcards, do not use them at the beginning of the search pattern, unless absolutely necessary, to place the wildcard characters at the beginning and to search for the slowest.
• Note the wildcard position, which is incorrect, may not return the desired result.

8. Regular expressions

The function of a regular expression is to match text and compare 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 the data retrieved by select. First look at the table records:

• SELECT * from goods where name regexp ' incense ' ORDER BY num DESC retrieves all records with ' incense ' in name
• SELECT * from goods where name regexp ' incense. ' Retrieve all records with ' incense ' in Name, '. ' Indicates a match to any one character
• SELECT * from goods where name regexp ' incense | Melon ' retrieves all records of ' incense ' or ' melon ' in name

If a record matches a regular expression, it is retrieved and the following regular expression repeating metacharacters can be used for greater control.

• * Match 0 or more
• + Match 1 or more (equals {1,})
• ? Match 0 or 1 (equals {0,1})
• {n} The matching of the specified bibliography
• {n,} not less than the match of the specified data
• {N,m} matches the range of the specified data (m not more than 255)
• ^ Beginning of the text
• The end of the $ text
• [[: <:]] The beginning of the word
• [[:;:]] The end of the word

Note: RegExp and like have a similar effect, unlike the regexp and the similarity in that the like matches the entire string and the regexp matches the substring, using the locator, starting each expression with ' ^ ', ending each expression with ' $ ', so that the function of the regexp is the same as that of the type.

9. Create a calculated field

Stitching connects values together to form a single value, and in MySQL's SELECT statement, you can use the concat () function to splice two columns.

From here we can see that the name of the new computed column is just a value, and we can use aliases to make it more meaningful. It is also possible to perform arithmetic operations on the retrieved data, which is supported by subtraction.

10. Using Data processing functions

Common text-processing functions

Function	Description
Left ()	Returns the word to the left of the string
Length ()	Returns the length of a string
Locate ()	Find a substring of a string
Lower ()	Convert a string to lowercase
LTrim ()	Remove the left space of the string
Right ()	Returns the character to the right of the string
RTrim ()	Returns the character to the right of the string
Soundex ()	Returns the Soundex value of a string
SUBSTRING ()	Returns the character of a substring
Upper ()	Convert a string to uppercase

11. Summary data

It is often necessary to summarize data instead of retrieving them, and for this purpose MySQL provides specialized functions for analysis and report generation. Common examples are: determining the number of rows in a table, getting the rows of a table group, and finding the maximum value (minimum or average) of a table column. The aggregation function runs on the row group, computes and puts back a single worthwhile function, and MySQL provides 5 aggregation functions, which are generally much faster than the computations in their own client applications.

• AVG () returns the average 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 a column's sum

Using the keyword DISTINCT allows you to select only records of different NUM to participate in the calculation.

12. Grouped data

SQL aggregation functions can be used to summarize data, which allows us to count rows, calculate averages, and get the maximum minimum value without retrieving all the data. Creating groupings allows you to divide the data into logical groups so that each group can be clustered. It involves the GROUP BY clause and the HAVING clause.

Before using the GROUP BY clause, you need to know some important provisions:

• The GROUP BY clause can contain any number of columns, which makes it possible to nest groupings and provide finer control over the grouping of data.
• If grouping is nested within the GROUP BY clause, the data is summarized on the last set of groupings. In other words, when a grouping is established, all the specified columns are evaluated together.
• Each column listed in the GROUP BY clause must be a retrieval column or a valid expression, but not a clustered function.
• If there is a null value in the grouping column, NULL is returned as a group, and if there are multiple null values, they are grouped together.
• The GROUP BY clause must appear after the WHERE clause before the ORDER BY clause.

In addition to using group by grouping data, MySQL also allows filtering of groupings, including which groupings to exclude and which groups are excluded. The filter group uses the HAVING clause, because where filters rows rather than groupings. Note: Where is the filter before data is grouped, having it filtered after the data is grouped.

Group BY and order by often do the same work, but the two are very different:

ORDER BY	GROUP BY
Output produced by sorting	Grouped rows, but the output may not be in the order of grouping
Any column can be used (not even a selected column)	Only select columns or expression columns may be used, and each selection column expression must be used
does not necessarily need	If you use a column (or an expression) with a clustered function, you must use the

The following is the order of the sub-clauses in the SELECT statement, in the order in which they must be followed when used in select.

Clause	Description	Whether you must use
Select	The column or expression to return	Is
From	Table from which data is retrieved	Use only when selecting data from the table
where	Row-level filtering	Whether
GROUP BY	Group description	Use only when clustering is calculated by group
Having	Group-level filtering	Whether
ORDER BY	Output sort order	Whether
Limit	Number of rows to retrieve	Whether

13. Using sub-query

What are sub-queries? A subquery is a query that is nested in another query, and a subquery in the WHERE clause should ensure that the SELECT statement has the same number of columns as the WHERE clause, and typically the subquery returns a single column and matches a single column.

14. Junction table

A foreign key is a column in a table that contains the primary key value of another table and defines a relationship of two tables. If the data is stored in more than one table, the required data can be retrieved using a single SELECT statement that connects. You should ensure that all connections have a WHERE clause, otherwise MySQL will return much more data than you want because the number of rows retrieved at this time is the first table row multiplied by the second table row count.

Joins are the most important and powerful feature in SQL. The design of a relational table is to ensure that the information is broken into multiple tables, a class of data a table. Tables are related to each other through some commonly used values, that is, relationship 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

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

A join based on an equality test between two tables is called an inner join . In fact, for this kind of join you can use a slightly different syntax to explicitly specify the type of the junction, and the following SELECT statement returns exactly the same data as the previous example.

SELECT Vend_name, Prod_name, prod_price from vendors INNER JOIN products on vendors.vend_id = products.vend_id;

15. Create an advanced junction

You can alias a table in addition to its alias for column names and calculated fields.

In addition to simple links that use an inner join (or an equivalent join), you can also use self-joins, natural joins, and external links.

• self-junction : You can reference the same table more than once in a single SELECT statement.
• Natural coupling : Whenever a table is joined, there should be at least one list that is now in more than one table (the joined column), and the standard join (inner join) returns all the data, even the same column multiple times. Natural join exclusions occur multiple times so that each column is returned only once.
• outer joins : Many junctions associate rows in one table with rows in another table, but sometimes need to contain rows that have no associated rows.

• SELECT * from persion as haha; Using Table aliases
• Select proid_id, prod_name from products where vend_id = (SELECT vend_id from products where prod_id = ' dtntr '); Locate the supplier that produced the item with the ID DTNTR, and then find the other items produced by this supplier. The subquery is used in this way, and the following is an operation that uses a self-junction :
• 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 customers.cust_id, orders.order_num from customers INNER JOIN orders on customers.cust_id = orders.cust_id; This is the inner join method, the following is the external coupling method
• SELECT customers.cust_id, Orders.order_num from customers left OUTER JOIN orders on customers.cust_id = orders.cust_id;

16. Combination Query

The results of multiple SELECT statements are combined into a single result set using the union operator and combining multiple SQL queries. Note that the Union must consist of 2 or more than 2 SELECT statements, and each query in the Union must contain the same column, expression, or aggregate function (although each column does not need to be listed in the same order). The union automatically removes the duplicate rows, which is the default behavior, and if you don't want to, use union all instead of union.

How do I sort the output of a UNION statement? When using the ORDER BY clause, after the last SELECT statement of the Union, there can be only one collation for the result set, so a feature multiple ORDER BY clause is not allowed.

17. Inserting data

INSERT statement inserts typically produce output, and typically only the number of rows affected is printed. If you do not provide a column name when you insert, you must provide a value for each table column, and if you provide a column name, you must give a value for each column listed, or you will get an error.

Insert a full line:

Insert Multiple lines:

18. Update and delete data

Update and delete data operations are updated and deleted using the UPDATE statement and the DELETE statement, respectively. Remove the data from the goods table first, and the goods table data is as follows:

• Update goods Set num = 1 WHERE name = ' Apple '; Num for Apple now changes to 1
• Delete from goods where num = 7; Delete the record for Num 7, which is the name of the orange.

Note that there are some considerations for using the UPDATE and DELETE statements:

• Never use an UPDATE or DELETE statement without a WHERE clause unless you are sure you want to update and delete each row.
• Ensure that each table has a primary key and use it as much as possible as a WHERE clause.
• Before you use the WHERE clause for an UPDATE or DELETE statement, you should test with select to ensure that the correct records are filtered to prevent the write where clause from being incorrect.
• MySQL does not have a undo function, you should use the Update or delete feature with care.

19. Create and manipulate tables

Creating tables using CREATE table must give the name of the table, given in the keyword CREATE TABLE, the name and definition of the table column, separated by commas.

Create TableVendors (vend_idint  not NULLauto_increment, Vend_nameChar( -) not NULL, Vend_addressChar( -)null default ' * ', Vend_cityChar( +)NULL,    Primary Key(vend_id)) engine=InnoDB;

This statement creates a vendors table, vend_id and vend_name are required, are specified as NOT NULL, others are non-mandatory, and are specified as null,null default settings. The Auto_increment keyword tells MySQL that the column value is unique each time you increment the row automatically. Only one auto_increment column is allowed per table, and it must be indexed (for example, by making it a primary key). Defaults is the default value.

Note that the primary key must be unique, and each row in the table must have a unique primary key, and if the primary key uses a single column, its value must be unique, and if multiple columns are used, the combination of the columns must be unique. Foreign keys do not overwhelm the engine. MySQL internally packs a variety of engines, the following are the engines you need to know:

• InnoDB is a reliable transaction processing engine that does not support full-text search.
• Memory is equivalent to MyISAM in function, but because the data is stored in RAM, all speed is very fast (especially suitable for temporary tables).
• Myisamysql is an extremely high-performance engine that supports full-text search, but does not support transactional processing.

Use the ALTER TABLE statement to update the table structure.

Delete the table (not its contents, but the entire table) using the drop statement. DROP TABLE table_name; This statement deletes the TABLE_NAME table, so long as it exists, note that deleting the table is not confirmed and cannot be undone.

Use the Rename table statement to rename the table.

20. View

A view is a virtual table, unlike a table that contains data, and the view contains only queries that retrieve data dynamically when used. Views are simply a facility for viewing data stored elsewhere, and the views themselves do not contain data, so the data they return is retrieved from other tables. The view provides a hierarchical package of MySQL SELECT statements that can be used to simplify data processing and reformat the underlying data or protect the underlying data.

Common applications for using views:

• Reusing SQL statements
• Simplify complex SQL operations, and after you write a query, you can easily reuse it without needing to know the exact details of the query
• Use parts of a table instead of the entire table
• Protect data, grant specific partial access to a customer table, not all
• Changing data formats and representations, views can return data that differs from the representation and format of the underlying table

Use the view to simplify complex junctions, the view is created using the CREATE VIEW statement, use the show create VIEW viewname to view the statement that created the view, delete the view with drop, and the syntax for the drop view viewname.

In general, views are updatable (you can use INSERT, UPDATE, and delete for them), and updating a view will update its base table because the view itself has no data. However, not all views are updatable, and if MySQL does not correctly determine which base data is being updated, it cannot be updated, that is, if there is an operation, the view does not allow updates: grouping, joins, subqueries, and aggregation functions, distinct, and so on.

Reference

1. mysql must know

2. SQL Tutorial

3. High-performance MySQL

MySQL Basic Learning Summary