1. General query

1 SELECT the column field to be displayed FROM table name [WHERE condition];    
Two cases: select userid, username from userinfo where user age < 30;
3. Query all fields: SELECT * FROM table name;

2. order by asc | desc

1 SELECT * FROM userinfo ORDER BY userage ASC;        //Ascending order
2 SELECT * FROM userinfo ORDER BY userage,userid DESC;        //Descending order

3. Interval query between | and

1 SELECT * FROM userinfo WHERE userage>=18 AND userage<=40;
2 SELECT * FROM userinfo WHERE userage BETWEEN 18 AND 40;

4. Range query in | not in

1 SELECT * FROM userinfo WHERE useraddress IN ('Wuhan','Rome');
2 SELECT * FROM userinfo WHERE useraddress NOT IN ('Wuhan','Rome');

5. Fuzzy query like% | (underline can only match any character at a time)

1 SELECT * FROM userinfo WHERE username LIKE 'Zhang%';    ##Data beginning with sheet
2 SELECT * FROM userinfo WHERE username LIKE '%bright';    ##Data with bright end
3 SELECT * FROM userinfo WHERE username LIKE '%bright%';   ##Contains bright data
4 SELECT * FROM userinfo WHERE username LIKE '__civil';   ##The third word is people's data.

6. Null query is null | is not null

1 SELECT * FROM userinfo WHERE useraddress IS NOT NULL;

7. Multi criteria query and or

1 SELECT * FROM userinfo WHERE usersex='male' AND userage>20;
2 SELECT * FROM userinfo WHERE usersex='male' OR userage>20;

8. Unique query

1 SELECT DISTINCT useraddress FROM userinfo; 

9. Group query group by field having multi condition expression

1 #Find out how many people there are in each city, display their names and count the total number of people
2 SELECT useraddress,COUNT(*) ,GROUP_CONCAT(username) FROM userinfo GROUP BY useraddress WITH ROLLUP;
3 #Find out how many men and women there are in each city
4 SELECT useraddress,usersex,COUNT(*) FROM userinfo GROUP BY useraddress,usersex;
5 #Find out two cities with people over 40 years old.
6 SELECT  * FROM userinfo WHERE userage>40  GROUP BY useraddress HAVING COUNT(*)=2;
7 ##where，order by，limit Order of
8 ##Check the top 10 boys,Sort by age    SELECT * FROM userinfo WHERE usersex='male' ORDER BY userage LIMIT 0,10;
9 ##How to filter rows in a query:where；How to filter columns in a query:select column from

10. Paging query limit? ,? (first? : index starts from (0), the second? : several in total)

1 SELECT * FROM userinfo LIMIT 3,7;
2 ##Display 10 items per page, how to display page 2; page: page display: num
3 SELECT * FROM userinfo LIMIT (page-1)*num,num;

11. Alias query as

1 SELECT userid AS No., username AS name, usersex AS gender FROM userinfo u;

12. Function query

 1 #Mathematical function
 2 SELECT ABS(-4),ABS(5);  ##absolute value
 3 SELECT BIN(5),OCT(13),HEX(21);
 4 SELECT CEILING(5.4),FLOOR(5.7),ROUND(5.7);
 5 SELECT GREATEST(6,5,4,6,7,3),LEAST(6,5,34,7,8,2);
 6 SELECT MOD(7,5);
 7 SELECT PI();
 8 SELECT RAND();  ##random number
 9 SELECT ROUND(); ##Rounding
10 #Aggregate function
11 SELECT COUNT(*) FROM userinfo;    ##Total number of rows in the table
12 SELECT MAX(field),MIN(field) FROM userinfo;
13 SELECT SUM(field),AVG(field) FROM userinfo;    ##Sum and average
14 #Character function
15 SELECT ASCII('a');
16 SELECT LENGTH('a than cD'),CHAR_LENGTH('a than cD'),BIT_LENGTH('a than cD');    ##Count strings
17 SELECT CONCAT('a','b','c','d'),CONCAT_WS(',','a','b','c','d');        ##Connection string
18 SELECT LOWER('aBcd'),UPPER('aBcd');    ##Convert case
19 SELECT INSERT('abcdefg',2,3,'888'),REPLACE('abcdefg','cd','888');    ##Replace string
20 SELECT INSTR('abcdefg','c'),POSITION('c' IN 'abcdefg'),FIND_IN_SET('21',userage) FROM userinfo;
21 SELECT LEFT('abcdefg',3),RIGHT('abcdefg',3);    ##Returns the specified number of characters on the left or right
22 SELECT LPAD('hello',10,'???'),RPAD('hello',10,'???');    ##Fill a string on the left or right
23 SELECT LTRIM('  a   b   c   '),RTRIM('  a   b   c   '),TRIM('  a   b   c   ');    ##Remove Spaces
24 SELECT TRIM('xy' FROM 'asdasdxyadsfdas');    ##Delete specified string
25 SELECT REVERSE('abcd');    ##Reverse string
26 SELECT SUBSTR('abcdefg',2,3);
27 ##Time date function
28 SELECT NOW();    ##Get current system date and time
29 SELECT CURDATE(),CURRENT_DATE();    ##Get current system date
30 SELECT CURTIME(),CURRENT_TIME();    ##Get system current time
31 SELECT YEAR(NOW()),MONTH(NOW());
32 SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());    ##Get the hour, minute and second of the current time
33 SELECT DAYOFWEEK(NOW()),DAYOFYEAR(NOW()),DAYOFMONTH(NOW());    ##Back today is a week/year/Day of the month
34 SELECT DATE_ADD(NOW(),INTERVAL -3 MONTH);
35 SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW());    ##Returns a timestamp in nuix format
36 SELECT FROM_UNIXTIME('1566396366');    ##Converting timestamps in unix format to normal format time
37 ##System function(Type conversion) cast(value as new type) 
38 SELECT CAST('-029' AS SIGNED INTEGER);
39 SELECT CAST(NOW() AS CHAR);
40 SELECT CAST(NOW() AS SIGNED INTEGER);
41 ##Encryption function
42 SELECT PASSWORD('12346');
43 SELECT MD5('123456');
44 SELECT ENCODE ('123456','123');    ##encryption
45 SELECT DECODE(ENCODE('123456','123'),'123');    ##Decrypt
46 ##Other functions
47 SELECT FORMAT (12313.1313313,3);    ##Format the number and retain the specified number of digits after the decimal point
48 SELECT INET_ATON('192.168.0.3'),INET_NTOA('3232235523');    ##Conversion between string network point address and numerical network address

13. Table connection query join

 1 ා left outer connection (key)
 2 SELECT the column field to be displayed FROM left table LEFT [OUTER] JOIN right table ON connection condition [WHERE filter condition];
 3 SELECT * FROM classinfo c LEFT OUTER JOIN stuinfo s ON c.classid=s.classid;
 4. Right external connection
 5 SELECT the column field to be displayed FROM the left table RIGHT [OUTER] JOIN the right table ON connection condition [WHERE filter condition];
 6 SELECT * FROM classinfo c RIGHT OUTER JOIN stuinfo s ON c.classid=s.classid;
 7. Internal connection (key)
 8 SELECT the column field to be displayed FROM table A INNER JOIN table B ON connection condition [WHERE filter condition];
 9 SELECT the column field to be displayed FROM table A,... Table N WHERE connection condition [AND filter condition];
10 cases: select Province, city, area from provinces P, cities C, areas a where p.provinceid = c.provinceid and c.cityid = a.cityid;
12. Access to information of Zhejiang Province
13 select Province, city, area from provinces P, cities C, areas a where p.provinceid = c.provinceid and c.cityid = a.cityid and province = 'Zhejiang Province';
15 ා display name of province, city, district and county
16 SELECT province,city,AREA FROM cities c LEFT JOIN provinces p ON p.provinceid=c.provinceid LEFT JOIN areas a ON c.cityid=a.cityid;

14. Sub query: if the result of a query is regarded as a query condition, then the query is a sub query and the external query is a parent query.

1) subquery with any and some keywords: indicates that if any of the conditions are met, a result will be returned as the condition of the outer query.

1 Example:SELECT * FROM userinfo WHERE userage>ANY(SELECT userage FROM userinfo WHERE useraddress='Suzhou');

2) subquery with all keyword: when using all, all inner query conditions must be met at the same time.

3) subquery with exists keyword: the parameter after the exists keyword is an arbitrary subquery. If at least one row is returned, the exists result is true and external query is performed; if not, the exists result is false and external query is not performed.

1 Example:SELECT * FROM userinfo WHERE userage<30 AND EXISTS (SELECT username FROM userinfo WHERE username='White lotus girl');

4) sub query with in keyword: the inner query returns only one data column, and the values in this data column will be provided to the external query statement for comparison query.

1 example: query the person with the same address as' Wang Lu 'or' Guo Erdan '
2 select * from userinfo where useraddress in (select useraddress from userinfo where username = 'Wang Lu' OR username = 'Guo Erdan');

15. Merge query results -- > Union, union all, intersect, and minus

1 Example:SELECT userid FROM userinfo WHERE userid<10  
2   　UNION
3   　SELECT userid FROM userinfo WHERE userid BETWEEN 5 AND 14;

16. Use regular expression to query regexp

 1 character "^" matches text that begins with a specific character or string
 Two examples: select * from userinfo where username regenxp '^ discount';
 3 character "$" matches text that ends with a specific character or string
 4 examples: select * from userinfo where username regenxp 'female $';
 5 character "." matches any one character
 The 6 character "*" matches the preceding characters any number of times, including 0 times; "+" matches the preceding characters at least once
 7 matches multiple strings, separated by the separator "|"
 8 cases: select * from userinfo where username regenxp 'discount female';
 9 square brackets' [] 'match any one of the specified characters
10 examples: select * from userinfo where user age regenxp '[03]';
11 "[^ character set]" matches any character not in the specified set
12 examples: select * from userinfo where user age regenxp '[^ 50-60]';
13 the string {n,} means to match the string before at least N times; the string {n,m} means to match the string before at least N times, not more than m times
14 examples: select * from userinfo where user age regenxp '2 {1,}';