Application of basic query for MySQL notes _ MySQL

Application of basic query for MySQL notes

Reference Table: student

Multi-field query

Mysql> select id, name, birth from student;


Query all fields

Mysql> select * from student;


Where specified query

Mysql & gt; select * from student where id = 901;
Mysql> select * from student where id> = 904;
Mysql> select name from student where department = 'computer system ';


In specified set query

Mysql> select * from student where birth in (1988,1990 );
Mysql & gt; select * from student where id in (903,906 );

Not in non-range query

Mysql> select * from student where birth not in (1990,1998 );


Between and specified range query

Mysql> select * from student where birth between 1986 and 1988;


Not between and not in the specified range

Mysql> select * from student where id not between 904 and 906;


Like string matching query

Mysql> select * from student where name like '_ 3 ';
Mysql> select * from student where name like 'Zhang San ';
Mysql> select * from student where name like 'Zhang % ';


Not like unmatched query

Mysql> select * from student where name not like 'sheet % ';


Null query

Mysql> select * from student where address is null;


And multi-condition query

Mysql> select * from student where name like 'Zhang % 'and birth> 1985;
Mysql> select * from student where name like 'Zhang % 'and birth> 1985 and id like' % 3 ';


Or multi-condition query

Mysql> select * from student where id = 905 or birth = 1988;
Mysql> select * from student where id = 905 or birth = 1988 or sex = 'femal ';

The query results of distinct are not repeated.

Mysql> select distinct sex from student;
Mysql> select distinct department from student;


Order by query result sorting

Mysql> select * from student order by birth;
Mysql> select * from student order by birth asc;
Mysql> select * from student order by birth desc;


Group by group query

Mysql> select sex, group_concat (name) from student group by sex;
Mysql> select sex, count (name) from student group by sex;


Regular expression query

Mysql> select * from student where birth regexp '2017 | 100 ';


Limit the number of query results

Mysql> select * from student limit 2;
Mysql> select * from student limit 1, 3;

BitsCN.com