10.20 MySQL Find summary

A, conditional judgment where

SELECT * FROM table where ID > 1 and name! = ' Alex ' and num = 12;select * FROM table where ID between 5 and 16;select * FROM table where ID in (11,22,33) select * FROM table where ID not in (11,22,33) select * FROM table where ID in (select Nid from Table)

B, wildcard like (Fuzzy Lookup)

SELECT * FROM table where name like ' liu% '  # Liu starts all (multiple strings) select * FROM table where name like ' Liu_ '  #liu开头的所有 (one character) Sele CT * FROM table where name like '%liu_ ' select * from table where name like ' _liu_ ' select * from table where name like '%liu% ' select * From table where name like ' _liu% '

c, limiting limit (paging)

SELECT * from table limit 5;            -First 5 lines select * from table limit 4,5;          -5 lines starting from line 4th select * FROM table limit 5 offset 4    -5 lines starting from line 4th

D, Sort Asc,desc

SELECT * FROM table ORDER BY column ASC              -arranges from small to large from "column" SELECT * from table ORDER BY column Desc             -rank from large to small from "column" SELECT * FROM Table order By column 1 desc, column 2 ASC    -ranked from large to small according to "column 1", if same, sort by column 2 from small to large

E. GROUP BY

Select Num from table GROUP by Numselect Num,nid from table group by Num,nidselect Num,nid from table  where nid > Ten GROUP by n Um,nid ORDER by Nid descselect Num,nid,count (*), SUM (score), Max (score), Min (score) from table GROUP by Num,nidselect Num from table GROUP BY NUM has max (ID) > 10 Special: Group by must be in where, before order by

F, combined Union, UNION ALL

combination, automatic processing coincident (minus same)    select nickname from a union select name from B combination, do not handle coincident (all displayed)    Select Nickname from a UNION All Select name from B

g, join table joins

No correspondence does not show    select A.num, A.name, b.name from A, Where A.nid = B.nid No corresponding relationship does not show    select A.num, A.name, b.name from A INNER JOIN B on    A.nid = B.nid A table All display, if there is no correspondence in B, then the value is null    select A.num, A.name, B.name from a left join B on a.nid = B.nid b Table All display, if there is no correspondence in B, then the value is null    select A.num, A.name, b.name from A right join B on A.nid=b.nid

10.20 MySQL Find summary