Special MySql syntax

LIMIT

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET

ON DUPLICATE KEY UPDATE

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    SET col_name={expr | DEFAULT}, ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

HANDLER

HANDLERTbl_nameOPEN [Alias]
HANDLERTbl_nameREADIndex_name{=| >=|<=|< }(Value1,Value2,...)

 

DO

DOExpr[,Expr]...

DO is used to execute expressions, but no results are returned. DO Is SELECT Expr. One advantage of DO is that if you are not very concerned about the results, DO is faster.

DO is mainly used to execute functions with side effects, such as RELEASE_LOCK ().

 

Join

In MySQL, cross join is equivalent to inner join. Multiple tables are separated by commas. It has the same semantics as inner join without union.

You can use STRAIGHT_JOIN to force the left table to be read before the right table. STRAIGH_JOIN can be used to arrange tables in wrong order.

MYSQL extends the standard SQL syntax and can be connected using the following method:

SELECT * FROM t1 left join (t2, t3, t4) ON (t2.a = t1.a AND t3. B = t1. B AND t4.c = t1.c)

Equivalent:

SELECT * FROM t1 left join (t2 inner join t3 inner join t4) ON (t2.a = t1.a AND t3. B = t1. B AND t4.c = t1.c );

You can use USING (Column_list) Clause is used to name a series of columns. These columns must exist in both tables. If both tables a and B contain columns c1, c2, and c3, the following Federation compares the columns corresponding to the two tables:

A left join B USING (c1, c2, c3) "=" a left join B on. c1 = B. c1 and. c2 = B. c2 and. c3 = B. c3

 

DELETE

You can use delete quick and optimize table to speed up deletion;

You can use LIMIT to LIMIT the number of records to be deleted each time to ensure that a DELETE statement does not take too much time, but LIMIT and order by cannot be used for deleting multiple tables;

You can delete records from multiple tables at a time (you can query multiple tables ):

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;

Or:

Delete from t1, t2 USING t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;

When you search for rows to be deleted, these statements use all three tables, but only delete the corresponding rows from Table t1 and table t2.

 

UNION

SELECT...

UNION [ALL | DISTINCT]

SELECT ...

[UNION [ALL | DISTINCT]

SELECT ...]

By default, UNION represents union distinct.

 

Multiple-table Update

UPDATE [LOW_PRIORITY] [IGNORE]Table_references

    SET col_name1=expr1 [, col_name2=expr2 ...]

    [WHERE where_definition]

Example:

UPDATE items,month SET items.price=month.price

WHERE items.id=month.id;