Single Table query, multi-Table query, and sub-query, single table

Single Table query, multi-Table query, and sub-query, single table

Query statement type:
Simple Query
Multi-Table query
Subquery (nested query)

Query syntax:

SELECT field_name: field_name indicates the field name. SELECT the column to be queried.
FROM table_name: database_name indicates the database name. Select the data table to be queried.
[WHERE condition]: condition is a query condition. The result is output only when the condition after where is met.
[DISTINT]: filters out duplicate rows.
[Order by filed_name [ASC | DESC]: filed_name is the field name, which is sorted BY the specified column. ASC is in ascending ORDER (default) and DESC is in descending ORDER.
[HAVING condition]: condition is a query condition. having can only be used with group by to filter group results.
[LIMIT [offset,] Count]: [offset,] Count is displayed starting from the offset (note that the first action is 0). count is displayed.

 

Simple Query

1. SELECT * FROM tb_name; * indicates all columns. If only the specified field is output, replace "*" with the specified field name.
Query all data in the tb_book table: select * from tb_book;

　　　

2. SELECT field1, field2 FROM tb_name; only the specified output field is called projection

　　Only query the fields of id and books in the tb_book table: select id, books from tb_book;

　　 　

3. SELECT * FROM tb_name WHERE condition; only the rows that meet the condition are output.

Check that the sort column value in the tb_book table is a basic class record: select * from tb_book where sort = 'basic class ';

The same DISTINCT value is displayed only once (duplicate rows are filtered out)

There are several types of sort fields in the tb_book table: select distinct sort from tb_book; (use distinct to filter out duplicate rows)

The results without distinct are as follows:

 

WHERE clause: Followed by a Boolean Relational Expression

SELECT * FROM tb_name WHERE Condition
Comparison operator:
1. =,>, >=, <=, <values are not quoted, and strings are quoted.
For example:Select * from tb_book where sort = 'base class'; Check that the value of the sort column in The tb_book table is a record of the base class:

 　

2.BETWEEN value 1 AND value 2: Match BETWEEN value 1 AND value 2 (including value 1 AND value 2)
The id column value in the tb_book table is 20 ~ 30 intermediate records: select * from tb_book where id between 20 and 30;
　


　　　　3. LIKE ‘’
　　　　　　%: Match any character in length
_: Match any single character

4. REGEXP and RLIKE support regular expressions
5. IN ("value1", "value2 "......) Match any one of them in the list
6. is null determines whether it is null. If it is null, it matches
7. is not null determines whether it is not null. If it is not null, it will match.

Logical operators:
1. AND all conditions are met simultaneously
2. OR meets any condition
3. If NOT does NOT meet the specified conditions, it will match

Order by field_name {ASC | DESC}: sort the query results (ASC in ascending ORDER and DESC in descending ORDER). The default value is ASC.
Syntax: select column name from table name order by sort by column [ASC | DESC]

 

Field alias:
Select name as student_name from student; display student_name is not name

 

LIMIT clause:
LIMIT [offset,] Count is displayed starting from offset (note that the first action is 0), and count is displayed.

Aggregation:
SUM (): calculates the SUM of the values of a field.
MIN (): calculates the minimum value of a field.
MAX (): returns the maximum value of a field.
AVG (), calculate the average value of a Field Value
COUNT () returns the number of rows with non-NULL values in the selected column.

Group by: GROUP
HAVING condition (having can only be used with group by to filter group results)

 

Execution Order

 

Multi-Table query:

Join: cross join (Cartesian Product): concatenates each row in a table with each row in another table (inner join ): compare the values of the corresponding fields in the two tables one by one, and only list the Outer JOIN of those equivalent relations: LEFT Outer JOIN: select field name from table name 1 left join table name 2 ON table name 1. field name = table name 2. field name
The returned data includes non-conforming data in the left table (that is, the left Outer Join includes all data in the left table) add an empty outer right join to the corresponding column in the right table:Select field name from table name 1 right join table name 2 ON table name 1. Field name = table name 2. Field name
　　　　　　　  The returned data includes non-conforming data in the right table (that is, the right Outer Join includes all data in the right table) and the corresponding column in the left table is empty.

Self-join (aliases must be used for tables ):

Subquery:

Use the query results generated by the internal layer in the query at the external layer. When there is a multi-layer query, mysql starts from the inmost layer query and then moves outward from it to the outer layer (main) query. In this process, the result set generated by each query is assigned to its parent query.

1. Use subquery in comparison: Only one value can be returned for subquery;

Syntax: select column name from table name where field name comparison operator (subquery)
Comparison operators include = ,! =,>, >=, <, <=.
Note: use "()" to enclose subqueries. Only one value can be returned for the subquery results, and the comparison operator is applicable.
　　　　　

2. Use subquery IN (): If a subquery returns multiple results, the comparison operator must replace the keyword IN.

Syntax: select column name from table name where field name in (subquery)
Note: use "()" to enclose subqueries. The subquery results can contain multiple

　　　
3. subquery with the keyword EXISTS: When the keyword exists is used, the inner query statement returns a false value instead of a query record. If the inner query statement queries a record that meets the condition, returns a True value. Otherwise, a false value is returned ). When the returned value is true, the external query statement queries the data. If the returned value is false, the external query statement does not query the data or cannot query any records.

Syntax: select column name from table name where exists (subquery)
Note: use "()" to enclose subqueries. not exists is the opposite of EXISTS.

4. subquery with the keyword ANY: any indicates that ANY of the conditions is met. When the keyword any is used, as long as any of the results returned by the internal query statement is met, the external query can be executed using this condition.
Select column name from table name where field name comparison operator any (subquery)

5. subquery with the keyword "ALL": all indicates that ALL conditions are met. When the keyword all is used, only all results returned by the internal query statement can be executed.
Select column name from table name where field name comparison operator all (subquery)

 

Union query: the Union query result combines the query results of multiple select statements,

Keyword UNION: merge all query results and then remove the same records.
Keyword union all: simply merge the result together

(Select 1) UNION (select 2); combines the two query results

View: the stored SELECT statement. (It is of little significance in MySQL and is related to security)
Base table-based query results;

CREATE    [OR REPLACE]    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    [DEFINER = { user | CURRENT_USER }]    [SQL SECURITY { DEFINER | INVOKER }]    VIEW view_name [(column_list)]    AS select_statement    [WITH [CASCADED | LOCAL] CHECK OPTION]  

Displays the statements used to create a table, view, and database.

SELECT CREATE {DATABASE | TABLE | VIEW} NAME    

Generalized query:

DML:
DELETE
INSERT
UPDATE

INSERT INTO tb_name (col1, col2, ...) VALUES (val1, val2, ...)[,(val1, val2, ...),...]INSERT INTO tb_name SET col1= ,col2= ,....INSERT INTO tb_name (col1,col2...) SELECT ....

Character Type: single quotes numeric type: no quotation marks required date and time type: NULL ,''

Replace into is similar to INSERT.

DELETE：     DELETE FROM tb_name WHERE condition;

TRUNCATE tb_name: clear the table and reset the AUTOINCREMENT counter; UPDATE tb_name SET col1 =..., col2 =... WHERE

From http://www.cnblogs.com/xiaonengsho/p/8567937.html