MySQL Point of knowledge

1. Order of execution

Statement Template:
(8) SELECT (9) DISTINCT (one) <top num> <select list>
(1) from [left_table]
(3) <join_type> join <right_table>
(2) On <join_condition>
(4) WHERE <where_condition>
(5) GROUP by <group_by_list>
(6) With <cube | Rollup>
(7) Having (10) ORDER by <order_by_list>

Execution order:
1.FROM: Performs a cartesian product (Cartesian product) (cross join) on the first two tables in the FROM clause, generating the virtual table VT1
2.ON: Apply the on filter to the VT1. Only those lines that make <join_condition> true are inserted into the VT2.
3.OUTER (JOIN): If OUTER join is specified (relative to cross JOIN or (INNER join), the reserved table (preserved table: Left outer join marks the left table as a reserved table, right outer join marks the right table as a reserved table, A full outer join that marks two tables as a reserved table) does not find a matching row to be added to VT2 as an outer row, generating VT3. If the FROM clause contains more than two tables, repeat steps 1 through 3 for the result table and the next table that were generated for the previous join until all the tables have been processed.
4.WHERE: Apply the WHERE filter to the VT3. Only rows that make <where_condition> true are inserted into the VT4.
5.GROUP by: Groups The rows in VT4 by the list of columns in the GROUP BY clause, generating VT5.
6.cube| ROLLUP: Inserts a Hyper-group (suppergroups) into VT5, generating VT6.
7.HAVING: Have a have filter applied to VT6. Only groups that make 8.SELECT: Processes the SELECT list, producing VT8.
9.DISTINCT: Removes duplicate rows from VT8, resulting in VT9.
10.ORDER by: The rows in VT9 are sorted by the list of columns in the ORDER by clause, resulting in a cursor (VC10).
11.TOP: Selects the specified number or scale of rows from the beginning of the VC10, generates the table VT11, and returns the caller.

2, MySQL in the type of int date and datetime type conversion to each other

In MySQL storage time, we can use the DateTime format, timestamp format, can also be used in int format.
The datetime default format is "Yyyy-mm-dd HH:MM:SS", which represents the 00:00:00-9999-12-31 23:59:59 from 1000-01-01.
The timestamp format is also ' Yyyy-mm-dd HH:MM:SS ', unlike DateTime, where its year range is 1970-2037.
int is the form of integers, it can control the number of bits, generally we set to 10 bits on it.

So when to use DateTime, when the format to be displayed is fixed, that is, the date of the month and minute, and so on, the way, timestamp, although the range of the datetime is small, but generally we think his range is big enough, In addition, it can be set to update the time, automatically modify the value, that is, when we modify the database, such as INSERT or UPDATE, when we set the Current_timestamp on update Current_timestamp, It will automatically fill in the current time.

Then when the use of int, originally int should not be the time format, but we have a lot of programming practice found that we usually in PHP programming, often need to format display time, such as only display date, if the database is an int type of time, Then we can directly use the PHP built-in function date () to convert, very convenient, and for time comparison is also more easy, because it is an int type, you can compare the size of the time can be compared directly.

MySQL Point of knowledge