MySQL Simple query detailed

Source: Internet
Author: User


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.