All operations for MySQL queries

data Query Language DQL
Basic Query
Grammatical form
SELECT [All | distinct] field or expression list [FROM clause] [WHERE clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause] [limit clause];

Select AVG (degree), CNO from score where CNO like "3%" group by CNO have Count (CNO) >=5;

Explanatory notes:
Select statement, which is used to find (remove) certain data from the data source and act as the return result (dataset) of the statement
Data Source:
Typically, a data source is a "table." But:
You can also have no data source, but instead use direct data (or function execution results).

[All | distinct]
Used to set whether the selected data is allowed to duplicate rows (identical rows of data)
All: Allow to appear--default does not write is all (allowed).
Distinct: Not allowed--is the so-called "eliminate duplicate Rows".
FROM clause
Is the source of the specified data, in fact, is the "table", can be a table name, or can be more than one table-multi-table query.

WHERE clause
A concept: Where clause, equivalent to the IF condition statement in PHP or JS: The end result is a Boolean value (True/false)
Php:if ($n% 4 = = 0 && $n%! = 0 | | $n% 400 = 0) {}
The
where true, where 1; where 1=1; Both represent true
where false, where 0; where 1<>1; Both indicate false

The operators available in Where:
Arithmetic operators: +-*/%
Comparison operators: > >= < <= = (equals) <> (not equal)
= = (equals, MySQL extension),! = (not equal to, MySQL extension)
Logical operators: and (with) or (or) not (non)

How Boolean values are judged:
Boolean: Essentially, a boolean is just an "alias" for an integer, 0 for false, and not 0 for true.
Judge True:xx is True
Judge Fale:xx is False
XX should be a field name, and its type should be an integer.
In practice, Boolean judgments are seldom used because the mathematical size can be used directly.

How to judge Null values:
Judge Null:xx is null
Judged to be non-null: XX is NOT null
XX should be a field name
Between syntax:
XX between value 1 and value 2;
Meaning: The value of the field xx is between the value 1 and the value 2 (inclusive), equivalent to: xx >= value 1 and xx<= value 2;
In syntax: XX in (1,2,3,4,5) xx not in ()
xx = 1 or xx = 2 or xx =3 ...
XX in (value 1, value 2, ...) ；
Meaning: xx equals any one of the listed values is counted, equivalent to:
xx = value 1 or xx = value 2 or xx = value 2
Note: The value 1 is usually a "direct value", but it can also be the "query result value" that you want to learn later
Like syntax (Fuzzy Lookup):
Grammar form: xx like ' to find characters ';
Description
1,like syntax (Fuzzy Lookup) for character matching for a field of character type lookup
2, to find the characters, there are 2 special meanings of the characters:
2.1:% It means: Any character that represents any number
2.2: _ The meaning is: any character representing 1
2.3: The character here refers to a "symbol" that is visible in reality, not a byte.
3, the actual application of fuzzy search, usually is this: like '% keyword% ';

If you are looking for characters that contain "%" or "_", "'", you can simply escape them by:
Like '%ab\%cd% '//What you're looking for here: characters that contain AB%CD characters
Like ' \_ab% '//What you're looking for here: _ab the beginning of the character
Like '%ab\ ' cd% '///What you're looking for here: characters containing ab ' CD characters

The WHERE clause must precede a FROM clause. Although 2 of them can be omitted, there is a from can without where, and where must have from.

    Select AVG (degree), CNO from score where CNO like "3%" group by CNO have Count (CNO) >=5;

GROUP BY group Clauses
Form:
Group By field 1 Sort Way 1, field 2 sort by 2, .....
Typically, only one field is grouped.
Meaning:
What do you mean by grouping? is to divide the data into different "groups" based on the value of a field.
The result of grouping is usually:
1, the data result can only be "group"--the individual without the data itself
2, data results can be "lost" many features, such as no gender, height, name, and so on.
3, in fact, the result is usually only "group" as the overall information:
The first is the group's own basis value,
In addition, these several possible values: the number of members within the group, the maximum value of some fields within the group, the minimum, the average, and the sum value.
Other fields, which are usually not available.
4, if it is 2 fields or more groupings, it is actually equivalent to the group in the previous grouping, and then the next basis for grouping.
The result of the above description is actually reflected in the SELECT statement, that is, the "Fetch item" (Output Item) of SELECT, basically only the above information is left

In a group query, basically all depends on a few functions (aggregate functions, statistical functions):
COUNT (*): Counts the number in a group, usually with "*" Parameters
Max (field name): Gets the maximum value in the Group for this field.
Min (field name): Gets the minimum value in the Group for this field.
sum (field name): Gets the sum of the fields in the group.
AVG (field name): Gets the average of the field in the group.
Group_concat (field name): Gets all the information for this field within the group, separated by commas

    Select AVG (degree), CNO from score where CNO like "3%" group by CNO have Count (CNO) >=5;

HAVING clause
The HAVING clause is exactly the same as the WHERE clause:
Where is a "conditional judgment" on the value of a table's field
Having is only for the groupby after the "group" of data to judge the condition, that is,
It cannot be used: Field name >10
But you can use: count (field name) >10, or Max (price) > 2000, but if the field is grouped by, you can.
Of course, you can also use valid field aliases in select, such as:
Select COUNT (*) as F1, Max (F1) as F2 from TAB1 Group by F3 have F1 > 5 and F2 < 1000;

    Select AVG (degree), CNO from score where CNO like "3%" group by CNO have Count (CNO) >=5;

ORDER BY clause
form:
Order by sort field 1 [Sort by], sort field 2 [Sort by], .....
Description:
To specify that the previous data (with the FROM clause, WHERE clause, group clause, all results of the HAVING clause) be arranged (sorted) by the size of a field, there are only 2 ways of sorting:
Positive order: ASC (default), you can omit
Reverse: DESC
If you specify more than one field to sort (although uncommon), it means that the same data in the previous field is sorted, followed by the size of the next field.
limit clause
form:
Limit [start line number start], number of rows to be fetched num
Description:
Indicates that the previously obtained data has been previously queued (if any) and is specified as " Locally contiguous number of "data."
Start line number start: The line number of the first row is 0, which can be omitted, or the default line number (0).
Number of rows to get: If the result set starts from the specified line number to the end without so many rows, only the last one is taken.

This clause is useful-primarily for one of the most common requirements (phenomena) on a Web page: paging.　　
Paging principle:
The premise of paging: a person to specify the number of bars displayed per page, $pageSize = 3;
Show (get) 1th page data: SELECT * from table name limit 0, $pageSize;
Show (GET) 2nd page data: SELECT * from table name limit 3, $pageSize;
Show (GET) 3rd page data: SELECT * FROM table name limit 6, $pageSize;
..... .... ..... ..... ..... ..... .....................
Show (GET) page $n data: SELECT * from table name limit ($n-1) * $pageSize, $pageSize;
Connection Query
Basic meaning
connection means two or more than 2 tables (data sources) "Connecting to become a data source."
In fact, a complete connection of two tables is a process where
each row of the left table is the result of all the rows of data that are obtained after each row of the table on the right and 22 cross-docking.
Note: After the connection, a new data table is not formed, but only a "memory pattern".
Basic form of connection syntax
from table 1 [connection method] Join table 2 [on join condition];
The result of the connection can be used as a "table". The following connections are commonly used:
cross-connect:
In fact, a cross-join is a result of a connection that does not set any criteria for two tables.
Cross-joins are also often called "Cartesian product"-more mathematically.
Syntax:
from table 1 join table 2;//visible cross join just no on condition.
Internal connection:
Syntax:
from table 1 join table 2 on table 1. field 1= table 2. Field 2;
Meaning: Find (filter) The value of the field 1 in table 1 in the result table of the cross join equals the field of table 2 2 of the values of those rows.

Select Sno,cname,degree from score join course on SCORE.CNO=COURSE.CNO;

　　Left [outer] connection:
Form:
From table 1 left [outer] Join table 2 on join condition.

Description
1, here, left is the keyword.
2, the connection condition is the same as the inner connection.
3, meaning: Based on the results of the inner join, plus all the data in the left table that do not meet the join conditions, the corresponding position of the table on the right should be automatically added to the "null" value.
　　Right [outer] connection:
The right connection is exactly the opposite of left join:
Form:
From table 1 right [outer] join table 2 on join condition.
Description
1, here, right is the keyword.
2, the connection condition is the same as the inner connection.
3, meaning: On the basis of the results of the internal connection, plus all the data in the right table that do not meet the join conditions, the corresponding position of the table should be placed on the left side of the field will automatically fill the "Null" value.
　　Full [outer] connection:
Form:
From table 1 full [outer] Join table 2 on connection condition;
Description
1, meaning: In fact, is connected to the "set" (eliminate duplicates), that is, the result of the inner join, plus the left table does not meet the conditions of all rows (the right side of the corresponding null), plus,

All rows in the right table that do not meet the criteria (the left side corresponds to null).
2,mysql In fact do not know the whole [outer] connection syntax, that is, MySQL this software itself does not support the full-attached syntax.
3, this concept exists in other databases, understanding is possible.
Sub-query
What is sub-query
A query, which is usually a SELECT statement (that is, the SELECT keyword appears once)
However, if the SELECT query statement appears in a SELECT query statement, the latter is called "subquery", which is the "main query"
Form:
SELELCT field or expression or (subquery 1) [as Alias] from table name or (subquery 2) where field or expression or (subquery 3) Condition judgment

Select AVG (degree) from score where Sno in (select Sno from student where class= ' 95033 ');

Attention:
Sub-query results placed at each location should meet the data requirements for that location.
Usually:
Sub-query 1 should be a "data result".
Subquery 2 can be an "arbitrary result", a query result of this location, usually as a data source, can give an alias
Subquery 3 can be a data or a column of data or even a row of data

Sub-queries are categorized by result:
Table subquery: The result of a subquery returning is theoretically "multiple rows and columns". This can be used as a "table", usually placed behind the from.
Line-word query: The result of a subquery returning is theoretically "a row of multiple columns". This can be used as a "row", usually in "row comparison syntax".
Column query: The result of a subquery returned is theoretically "multiple rows and columns". This can be used as "multiple values", similar to this: (5, 17, 8, 22).
Scalar Quantum Query: The result of a subquery returning is theoretically "one row at a time". This can be used as a "value", similar to this: select 5 as C1; Or select ... where a = 17, or select ... where B > 8;
According to the use of occasions:
As the result data of the main query: Select C1, (select F1 from TaB2) as F11 from Tab1; #这里子查询应该只有一个数据 (row by column, scalar subquery)
Condition data as the main query: select C1 from TAB1 where C1 in (select F1 from TAB2); #这里子查询可以是多个数据 (more than one row,

Sub-queries, and scalar queries, actually row subqueries may, but rarely)
Source data as main query: Select C1 from (select F1 as C1, F2 from tab2) as T2; #这里子查询可以是任意查询结果 (Table sub-query).

Common sub-queries and related keywords
Using subqueries in comparison operators
The form is: operand comparison operator (scalar quantum query);
The operand is usually a field.
Meaning: Determines whether the value of this operand (field) satisfies the comparison result set by the comparison operator.
In fact, it is the simplest form: ID > 5;
Example:
Data Source:

Requirements: Find all items above the average price.
First step: Find the average price:
Select AVG (price) as avg_price from product;

Part II: Find Products:
SELECT * from product where price > 4287.7; ==〉
SELECT * FROM Product where price〉 (select AVG (price) as avg_price from product);

　　Using the in sub-query
In the basic syntax form is:
where operand in (value 1, value 2, ...). ）
The in sub-query is:
where operand in (the query for a clause);
Meaning:
Indicates that the operand (field value) is equal to either of the subqueries, even if the condition is met.

Example:
Find all products with "electricity" word category
The first step: Find out all the category IDs with "power":

Step two: Identify these categories of products based on the results:
SELECT * FROM product where protype_id in (1, 3); ==〉
SELECT * FROM product where protype_id in (
Select protype_id from Product_type where protype_name like '% electric% '
);

　　Using any subquery
form of Use:
where operand comparison operator any (for the query of a clause);
Description
1 operands are usually still field names
The 2 comparison operator is the regular 〉〉= <= = <>
A 3-column subquery can also be a scalar subquery, which means "several data values"
Meaning:
A value that represents the operand that satisfies a given comparison operation with any one of the values of the query, even if it satisfies the condition-that is, as long as there is one achievement.
Examine a specific situation:
where operand = any (for the query of the reference);
It is exactly equivalent to:
　　where operand in (the query for a clause);
Example:
Find all products with "electricity" word category

Using the all subquery
The where operand comparison operator all (for the query of a clause);
Description
1 operands are usually still field names
The 2 comparison operator is the regular 〉〉= <= = <>
A 3-column subquery can also be a scalar subquery, which means "several data values"
Meaning:
The value that represents the operand must satisfy the given comparison operation with all the values of the example query, before the condition is satisfied.

Example:
Find the highest-priced product in the product table.
Analysis: The price of high-priced products will be greater than equal to all product prices.
SELECT * FROM product where price >= all (
Select Price from Product
);

Method Two:
SELECT * FROM product where Price = (
Select Max (price) from product
);

　　Subqueries that use some
Some is a synonym for any. Use it as well.
Subqueries that use exists
Previous studies:
if exist
Or:
If not exists
It used to mean that there is a data (usually a table)

Now the word is also used for subqueries, meaning that the same meanings "exist"

Form:
　　where exists (sub-query);
Meaning:
True if the subquery has result data (no matter what data, as long as it is greater than or equal to 1 rows), otherwise false

Example:
Identify those categories that have goods for sale:
SELECT * from Product_type where exists (
SELECT * FROM product where product.protype_id=product_type.protype_id
);

　　Using NOT EXISTS subqueries
The meaning is the opposite of the exists subquery.
Example:
Find out which categories are not yet on sale:
SELECT * from Product_type where NOT exists (
SELECT * FROM product where product.protype_id=product_type.protype_id
);

To understand: In fact, this exists (or not EXISTS) subquery, if it involves 2 tables (or more), its interior will actually automatically "connect query", and

Its logical process is more responsible, and is not clear, often considered to be less efficient sub-query, as little as possible.

　　Federated queries
The key word for a federated query is: union
The key word for a connection query is: Join
However, in the MySQL manual, join this connection query is often translated as "federated query"
But in the vast majority of Chinese books and articles, join is translated as "connection query"

Basic meaning
A federated query is a "cascade" of query results from two SELECT statements into a "big result".
The prerequisite condition in which two query results can be "federated" is that the number of result fields is equal.

Grammatical form:
SELECT statement 1
Union [All | DISTINCT]
SELECT statement 2;
Description
1, the output segment (result field) of the two SELECT statement is the same as the number, and the usual type in the application makes sense.
2, the fields in the result set are subject to the fields of the first SELECT statement.
3, the field of the first SELECT statement can be aliased, but if an alias is made, subsequent where,group,order clauses should use that alias.
4, the Federated query by default is to eliminate duplicates (DISTINCT), if you want to not eliminate, you must be explicit "all."
5, if you want to sort or limit the entire Union result, you should add parentheses to the respective SELECT statement:
(SELECT statement 1)
Union
(SELECT statement 2)
Order by .... Limit .... ；

All operations for MySQL queries