MySQL Single table query

Simple query:

SELECT * FROM Tb_name

Select Field1,field2 from Tb_name;

SELECT * from Tb_name where qualification;

Example: Select Name,age from students where age+1>20;

Select name from student where age>20 and gender= ' M ';

Select name from student where age>20 or gender= ' M ';

Select name from student where not age>20 and not gender= ' M ';

Select name from student where not (age>20 or gender= ' M ');

Select name from student where age>=20 and age<=50; or select name from student where age between 25;

Select name from student where like name ' Y% ';

The regular expression can be followed REGEXP or rlike:

Select name from student where name Relike ' ^[mny].*$ '; matches any character after the beginning of M or N or y.

In usage: Names of students aged 18, 20, 25

Select name from student where age in (18,20,25);

Show student table in the CID is null classmate;

Select name from student, where CID is null;

is not the notation for null values:

Select name from student where CID is not null;

Sort the data according to the query:

Select name from student where CID is not null order by name;

Three formats for data storage: Heap files, sequential files, hash

to alias a field;

Select name as Student_name from student where CID was not null order by name;

Limit the number of lines displayed (limits);

Example: select Name as Student_name from student as Xiaohai limit 2

Limit only two lines are displayed.

Skip the first two lines:

Select name as Student_name from student as Xiaohai limit 2,3

Skip the first two lines to show three rows.

Aggregation calculation:

Average age of all students:

Select AVG (age) from student;

Select Max (age) from student;

Select min (age) from student;

Select SUM (age) from student; Sum

Select count (age) from student; Find the number

GROUP BY: Grouping

Select Age,gender from student group by gender;

Grouping student tables by gender (i.e. male, female) shows age gender

Select AVG (age) from student group by gender;

Divide the table student by sex and calculate their average.

The primary purpose of grouping is to aggregate:

Note: Filtering on the group by condition must be done with having the function equivalent to where

Select COUNT (cid1) as persons,cid from student group by CID1 have persons>=2;

Having the function is to filter the results of group by again.

This article is from the "Wangconggao" blog, make sure to keep this source http://wangconggao.blog.51cto.com/3253868/1579692

MySQL Single table query