MySQL Simple query detailed

Query operation for MySQL

Single-table query: Simple query

Multi-table query: Connection query

Federated queries

Boolean conditional expression operator

= equivalent comparison <=>: Comparison with null value does not produce the equivalent of additional information <>: not equivalent to <: <=: > >= is null-is-not null-like: Wildcard characters supported:% (any character of any length), _ (any single character) Rlike,regexp: supports the use of regular expressions in: Determines whether the value of the specified field is given in the list; between ... And ...: Between specified ranges (x>=10 and x<=40--X between and 20)

Example

Creates a new table mysql> CREATE TABLE tests (SID int unsigned auto_increment NOT null unique Key,name char (in) not null,age Tin Yint unsigned not null,gender enum (' F ', ' M ') is not null,tutor char (30));

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8B/A2/wKiom1hTNDLBAEgaAAApZBokJ-s251.png-wh_500x0-wm_3 -wmp_4-s_925550764.png "title=" 1.png "alt=" Wkiom1htndlbaegaaaapzbokj-s251.png-wh_50 "/>

Add a few users mysql> insert into tests values (1, ' Guo Jing ', ', ' m ', ' Song Jiang '), (2, ' Yang Guo ', ', ' m ', ' Hu Sanniang '), (3, ' Gu    o Polu ', +, ' M ', ' Jia Baoyu '); Mysql> INSERT into tests values (4, ' Xue Baochai ', ' + ', ' f ', ' Rong Momo '), (5, ' Xia Yuhe ', Notoginseng, ' f ', ' Shi Qian '), (6, ' Wu Yo Ng ', Wuyi, ' M ', ' Lin Daiyu ');

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8B/A2/wKiom1hTNMfyCL4zAAAkRowALu0842.png-wh_500x0-wm_3 -wmp_4-s_2816630872.png "title=" 1.png "alt=" Wkiom1htnmfycl4zaaakrowalu0842.png-wh_50 "/>

Between ... And ... Syntax demo mysql> Select Name,age from tests where age between and 40;

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8B/9E/wKioL1hTNQugwUMJAAAwjSSZK5A453.png-wh_500x0-wm_3 -wmp_4-s_3601734811.png "title=" 1.png "alt=" Wkiol1htnqugwumjaaawjsszk5a453.png-wh_50 "/>

In Syntax demo mysql> select Name,age from tests where age in (25,26,27,28,29);

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8B/A2/wKiom1hTNTaj4SA-AAArigU_LR8193.png-wh_500x0-wm_3 -wmp_4-s_312419588.png "title=" 1.png "alt=" Wkiom1htntaj4sa-aaarigu_lr8193.png-wh_50 "/>

Like syntax demo mysql> select name from tests where name "X";

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/8B/9E/wKioL1hTNWngiLSRAAAlWHYtHM8572.png-wh_500x0-wm_3 -wmp_4-s_2770957590.png "title=" 1.png "alt=" Wkiol1htnwngilsraaalwhythm8572.png-wh_50 "/>

Rlike Syntax demo mysql> select name from tests where name rlike ' ^x.* ';

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8B/9E/wKioL1hTNZWgwvzBAAAUnsT5KQM953.png-wh_500x0-wm_3 -wmp_4-s_3545015822.png "title=" 1.png "alt=" Wkiol1htnzwgwvzbaaaunst5kqm953.png-wh_50 "/>

Add two rows of new data//null not strings need not be quoted mysql> insert into tests values (7, ' Tom ', One, ' m ', ' Jerry '), (8, ' Tomy ', ', ' m ', NULL);

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/8B/A2/wKiom1hTNbvDAH16AABaK5CMRbE229.png-wh_500x0-wm_3 -wmp_4-s_995579512.png "title=" 1.png "alt=" Wkiom1htnbvdah16aabak5cmrbe229.png-wh_50 "/>

Is nullmysql> select Name,tutor from tests where tutor is null;

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8B/A2/wKiom1hTNfiyJWd2AAAYaYvv0c4906.png-wh_500x0-wm_3 -wmp_4-s_2763567198.png "title=" 1.png "alt=" Wkiom1htnfiyjwd2aaayayvv0c4906.png-wh_50 "/>

Not nullmysql> Select Name,tutor from tests where tutor are NOT null;

Combination condition test

Not,! And, &&or, | |

Example

Mysql> Select Name,gender,age from tests where Age > gender= ' M ';

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8B/9E/wKioL1hTNlmR8FESAABEs5MJBwY441.png-wh_500x0-wm_3 -wmp_4-s_4243278516.png "title=" 1.png "alt=" Wkiol1htnlmr8fesaabes5mjbwy441.png-wh_50 "/>

Sort

Order BY ' sort field ' defaults to ascending: ASC Descending: DESC

Example

Mysql> Select Name,gender,age from Tests where > gender= ' M ' ORDER by name;mysql> Select Name,gender,ag E from tests where Age > gender= ' M ' ORDER by name Desc;

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8B/A2/wKiom1hTNp-Tan6qAAB-_2XPQVw799.png-wh_500x0-wm_3 -wmp_4-s_1864437225.png "title=" 1.png "alt=" Wkiom1htnp-tan6qaab-_2xpqvw799.png-wh_50 "/>

Aggregation functions

SUM (), AVG (), MAX (), MIN (), COUNT ()

Example

Mysql> select SUM (age) from Tests;mysql> Select AVG (age) from tests;mysql> select Max (age) from tests;mysql> s Elect count (age) from Tests;

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/9E/wKioL1hTNu2D21TaAABOWsrT0Io301.png-wh_500x0-wm_3 -wmp_4-s_2658315089.png "title=" 1.png "alt=" Wkiol1htnu2d21taaabowsrt0io301.png-wh_50 "/>

Mysql> Select COUNT (*) from tests where age > 25;//older than 25 have 4

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/8B/A2/wKiom1hTNxSB0us5AAAQiDn-Pbk525.png-wh_500x0-wm_3 -wmp_4-s_3275084230.png "title=" 1.png "alt=" Wkiom1htnxsb0us5aaaqidn-pbk525.png-wh_50 "/>

Mysql> select sum (age) of all ages from tests where ages > 25;//older than 25

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8B/9E/wKioL1hTNzaRvkPFAAARRWQNqtg946.png-wh_500x0-wm_3 -wmp_4-s_2924229284.png "title=" 1.png "alt=" Wkiol1htnzarvkpfaaarrwqnqtg946.png-wh_50 "/>

Group

GROUP BY

Example

Mysql> Select Gender,sum (age) from the tests group by gender;

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8B/A2/wKiom1hTN2-A_LB9AAAsH5KLCcQ659.png-wh_500x0-wm_3 -wmp_4-s_1254613737.png "title=" 1.png "alt=" Wkiom1htn2-a_lb9aaash5klccq659.png-wh_50 "/>

Mysql> ALTER TABLE tests add ClassID tinyint unsigned;//a new field for the table

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/8B/9E/wKioL1hTN5GAHik5AABm7GJgei8550.png-wh_500x0-wm_3 -wmp_4-s_307075469.png "title=" 1.png "alt=" Wkiol1htn5gahik5aabm7gjgei8550.png-wh_50 "/>

Insert Data mysql> Update tests set classid=1 where sid=1;

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8B/9E/wKioL1hTN8ajzhXgAAAsyme3mF0695.png-wh_500x0-wm_3 -wmp_4-s_3672451127.png "title=" 1.png "alt=" Wkiol1htn8ajzhxgaaasyme3mf0695.png-wh_50 "/>

Mysql> Select Classid,count (*) from tests group by CLASSID; or mysql> Select Classid,count (name) from tests GROUP by classid;//How many people per class

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8B/A2/wKiom1hTN-WS7XdHAAAjIIeeers342.png-wh_500x0-wm_3 -wmp_4-s_3617116206.png "title=" 1.png "alt=" Wkiom1htn-ws7xdhaaajiieeers342.png-wh_50 "/>

Mysql> Select Classid,count (name), sum (age) from the tests group by CLASSID;

Conditional filtering for GROUP by

Having note: Use having and not using where

Example

Mysql> Select Classid,count (*) from tests GROUP by CLASSID have Count (*) >= 2;

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/8B/9E/wKioL1hTODLwfk1vAAApEvuWx0M298.png-wh_500x0-wm_3 -wmp_4-s_2540424838.png "title=" 1.png "alt=" Wkiol1htodlwfk1vaaapevuwx0m298.png-wh_50 "/>

Mysql> Select Classid,count (*), sum (age) from the tests group by CLASSID have sum (age) <= 50;

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/8B/A2/wKiom1hTOFPi6yAyAAAXmB8wgME791.png-wh_500x0-wm_3 -wmp_4-s_612272994.png "title=" 1.png "alt=" Wkiom1htofpi6yayaaaxmb8wgme791.png-wh_50 "/>

Mysql> Select Classid,count (name), sum (age) from Tests where ClassID in (all) group by ClassID Havingsum (age) <= 50;/ /Note where the filter and having filter appear respectively

return only useful rows

LIMIT one number for the number of rows displayed two digits offset the first digit line, showing the second number

Example

Mysql> SELECT * FROM Tests limit 2;mysql> SELECT * from Tests limit 2, 3;

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/8B/9E/wKioL1hTOLfhtCADAABlZ1WzwZg558.png-wh_500x0-wm_3 -wmp_4-s_188824642.png "title=" 1.png "alt=" Wkiol1htolfhtcadaablz1wzwzg558.png-wh_50 "/>

SELECT statement

Distinct duplicate displays only once Sql_cache cached query results sql_no_cache not cached query results

Example

Mysql> Select age from Tests order by age; Mysql> SELECT DISTINCT age from Tests order by age;

Summarize

Execution flow of the SELECT statement

FROM clause--where clause--and group BY--have clause-->order by--

Select-->limit

Common usage

Selectfromorder by

Selectfromgroup byhaving

Selectfromwhere

Select--Invoke intrinsic function

Selectfromwheregroup Bylimit

This article is from the "Homecoming" blog, make sure to keep this source http://sixijie123.blog.51cto.com/11880770/1883224

MySQL Simple query detailed