Mysql query statement (mysql study note 7 ),

Mysql query statement (mysql study note 7 ),

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

Table Data

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

The set operator also has

Any one of the Any (SET) Sets

= Any (SET) is equal to any one of the sets.

Equivalent to in

= All elements in the All (SET) Set

! = All (SET) is Not equal to all elements in the set. It is equivalent to Not in.

! = Any (SET) is not equal to any element in the set. It is true if it is not equal to one of the elements.

Note: This syntax should not appear in actual development.

Some

The syntax is the same as that of any.

Summary:

= Any is equivalent to in

! = All is equivalent to not in

Some is synonymous with any.

All, any, some can be used except = ,! = External operators, more powerful than in

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.

Exists

If the subquery can return data, true is returned. Otherwise, false is returned.

Two tables are available:

Table

Table B

Query records with IDs in B in.

Obtain the first row record of Table a, and compare the id of Table a with that of Table B in the subquery.

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

The connection conditions can be omitted during connection. This means that all left table data must be connected to the records in the right table.

A total of m * n connections exist

Cross join or Cartesian set

In this case, you can use cross join instead of inner join.

In Mysql, cross join is the same as inner join.

Inner join is the default connection method (inner omitted)

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.

The where clause cannot be used as the connection condition for the query condition and external connection.

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