Mysql Basic Syntax 3

One, other data operations (advanced operations)

2. worm Replication (insert into table name (field list) Select *| Field List from table name)

4. primary KEY repeat:insert into table name (field list)values(Value list)on duplicate key update field 1= value 1     , field 2= value 2 ... Replace into table name (field list) values(Value list)

6. Both the order by and the limit clauses can be added when deleting and modifying

8. Select option (all,distinct, default is all )

10. aliases: Fields | An expression | Table Name | Sub-query

Two, five clauses

1. where:

1. where syntax and flow

2. Mysql operators (relational operators, logical operators, between and,in, and not in)

3. Other forms of the WHERE clause: null query (is[not] null), fuzzy query (like, wildcard _ and %)

the essence of a 4.where clause is to filter data from disk to memory, and having clauses to filter the data in memory again

A statistical function cannot be used after a 5.where clause, but a having clause is possible, because only in-memory data can participate directly in the Operation

B.group BY clause: Grouped statistical query statements are often used in conjunction with statistical functions (aggregate functions)

1. multi-field grouping

2. statistical functions:max,min,sum,avg,count

3. Statistical functions can be used alone, this is equivalent to dividing the entire table into a group

4. backtracking statistics (up statistics), withRollup

c.having clause

D.order by clause: Sort by a field or sort by multiple fields (ascending ASC, also default, descending desc)

E.limit clause:limitoffset,length, where the offset is starting from 0 , and if omitted, the default is 0! The principle of paging is: limit ($pageNum-1) * $rowsPerPage, $rowsPerPage

Third, joint query

1. Keyword:Union,the Union option is all and distinct, the default value is distinct

2. Application Scenarios:

   1, when the business logic conflict or difficult to implement in the same select statement, the business logic needs to be split, with multiple SELECT statements are implemented separately and then joined together;

   2, when the data volume of a table is relatively large (especially more than 1000w), it is often necessary to "split the table" (the structure of multiple tables is the same, only the data stored in the same), if you want to query the data, Often multiple tables are queried and then joined together

Mysql Basic Syntax 3