(1) limit limit n, first n items; For example, 0,3 corresponds to 1, 2, and 3. 2 and 3 corresponds to 3, 4, and 5 when limit and offset are used together, there can be only one parameter after limit, indicating the number to be taken, and offset, indicating the number to be skipped. Select * from article LIMIT 3 OFFSET 1 select * from article LIMIT 3 OFFSET 1

Inner join left join = left outer join inner join left outer join = left outer join inner join left outer join = left outer join inner join left outer join = left outer join Right join = right outer join = right outer join = right outer join = right outer join = right outer join = right outer join = right outer join = right outer join = right outer join = right outer join Inner join: Combines the records in two tables and returns only the records whose association conditions match in the two tables. Full join: This type of join does not exist in mysql and can be implemented using union

SELECT * FROM `study1` s1 left join study2 s2 on s1.id=s2.id union all SELECT * FROM `study1` s1 RIGHT join study2 s2 on  s1.id=s2.id;Copy the code

(3) The “WHERE” and “having” sentences allow us to filter all kinds of data after grouping. The “WHERE” sentences filter records before aggregation

(4) Subquery and join subquery using not in is slow efficiency, can be replaced by left join + is NULL

(5) DISTINCT and DISTINCTROW DISTINCT: DISTINCTROW is removed based on only queried fields. DISTINCTROW is removed based on the whole row of queried records

(6) = and := Reference links

  • = = = = = = = = = = = = = = = = = For this reason, when implementing line numbers with variables, you must use :=
  • := Assigns not only to set and update, but also to SELECT.

(7) Date operation