SQLStatement
General sequence GHOL:Group by, having, order by, limit
For grouping, sort the grouping fields.GroupSyntax
Limit start, length
Remove duplicate recordsDefault Value:All
Select distinctFieldFrom
Select distinct * fromUseless (The combination of all fields is not the same.), Take one of the record values when they are exactly the same
UnionQuery
Put twoSelectResultUnionUp
(SelectStatement1) union (selectStatement2)
For students with the highest English and mathematics scoresId, name, class
Note that brackets
Merging with duplicate records
PressEnglishFrom high to lowMerge with low-to-high results
The order by function is affected in the statement that meets the requirements.
Substatement sorting
1.Enclose the sub-statement in parentheses
2.Sub-StatementOrder
InOrderCooperationLimitUse Time.
The reason is:UnionWhen a sub-statement is executedLimitOfOrderOptimization(Ignore)
Sort all results
You only needSelectAdd after statementSort the logs.
Substatement parentheses are not requiredThe last sorting is based on all results by default.
UnionThe number of retrieved fields must be the same.(No error), The same data type(Type conversion)The column name is composed of the firstSelectRetrieve column names
Subquery
Internal query statement
Find information about the students with the highest English score
Subquery not required:
However2Students with the highest English scoreAnd consistent. When you do not know the data in the tableYou cannot use this query method.
Train of Thought: first find the value with the largest English score, and then find out which students have the same English score with the maximum value. Perform the query step by step.
Only one field can be used as a value. Only one field must be retrieved.
Subquery category
Different Categories have different usage methods.
Classification criteria:
Position of the subquery
Return Value form of subquery
Return Value Type:
Single value, single column, multiple columns, multiple rows and multiple columns(Table)
Location:
WhereType,WhereAfter
FromTypeFromAfter
ExistsType
Usage:
Scalar: returns a value and uses Relational operators for computation.(>>=, <=<>)
Column subquery(Just1Column): Get a columnIt is usually a column value of a row.(A collection)
UseIn, not inOperator
Find the classPhp101Information of all students in
Note: This syntax should not appear in actual development.
Some
The syntax is the same as that of any.
Returns a row.
You can use parentheses to create a row during the comparison.
(Field1, field2 ,...)
Table Data
Now we want to query the hehe8In the same classAndMathInformation of students with the same score
Subquery
Returns a table.
If it is used in the from clause, it is required to be a table
The query result is now returned. An alias must be provided for this query result.
Table Data
Query the information of students who fail to pass the english Course in class php103.
Aliases are required.
If the subquery can return data, true is returned. Otherwise, false is returned.
Two tables are available:
Table B
Connection Query
Join
Each entity, one table
A business logic that uses data from multiple entities
Multiple tables should be used together to connect the records of multiple tables
TeacherTable
Teacher_class1Table
Find out substitute instructor Information
Descartes(Crossover)Connection
Internal Connection Processing
Equivalent
You can also use
Cartesian Product
The results are the same.
On data connection conditions
Where data filtering Condition
However, where is first connected to cartesian products.
Then filter the data, and on checks the data during connection.
The preceding table shows the connection conditions.2Items
The following table shows
Filter Condition2Items
The following table filters each
Using:The field names of the two entities to be connected must be consistent.
Note:
No matter whether it is a connection condition or a connection query multi-field list, you do not need to write the table name..Field syntax. Whether to write depends on whether a conflict occurs. It is recommended to write.
Alias
Table alias,Ensure simplicity and clarity.
Column alias
External Connection:
Category
Left Outer Join
Outer right connection
All external connections(Currently not supported)
Left join
If the data in the left table cannot be connected to the right table during the connection, the data in the left table is retained in the final result. If the data in the right table cannot be connected to the left mark, the data in the right table is discarded.
Because internal connections do not have left or right connections,Left outer joinMediumOuterCan be omitted.
Cannot be used in external connectionWhereAvailable connection conditionsOn, using
The table alias can be used in the connection condition, but the field alias cannot.
After the table alias is started, aliases must be used in filtering or connection conditions. The original name cannot be used.
Left tableTeacherData inSun Wu connection is not retained.
All external connectionsLeft outer and right outerUnion(Union set)
The inner connection is the left outer right outer intersection.
UsingThe repeated fields in the result are removed and placed at the beginning of the column.
External connections cannot use connections without conditions(Unlike inner join, Cartesian product is formed)
Natural connection
PassMysqlComplete the connection process by yourself. You do not need to specify the connection conditions,MysqlThe same fields in multiple tables are used as the join conditions.
TableOneData
TwoTable Data
Natural connections also have internal and external points
Internal:Natural join
External:Left outerNatural left join, Right outerNatural right join