MySQL study notes 7: basic query

Reference Table: student

 

Multi-field Query

mysql> select id,name,birth from student;

 

Query all fields

mysql> select * from student;

 

Where specified Query

Mysql> 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> 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 = 'female ';

 

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 '1988|1990';

 

Limit the number of query results

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