Application of basic query for MySQL notes

Reference Table: student

Multi-field Query
Copy codeThe Code is as follows:
Mysql> select id, name, birth from student;


Query all fields
Copy codeThe Code is as follows:
Mysql> select * from student;


Where specified Query
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
Mysql> select * from student where birth in (1988,1990 );
Mysql & gt; select * from student where id in (903,906 );

Not in non-Range Query
Copy codeThe Code is as follows:
Mysql> select * from student where birth not in (1990,1998 );


Between and specified range query
Copy codeThe Code is as follows:
Mysql> select * from student where birth between 1986 and 1988;


Not between and not in the specified range
Copy codeThe Code is as follows:
Mysql> select * from student where id not between 904 and 906;


Like string matching Query
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
Mysql> select * from student where name not like 'sheet % ';


Null Query
Copy codeThe Code is as follows:
Mysql> select * from student where address is null;


And multi-condition Query
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
Mysql> select distinct sex from student;
Mysql> select distinct department from student;


Order by query result sorting
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
Mysql> select sex, group_concat (name) from student group by sex;
Mysql> select sex, count (name) from student group by sex;


Regular Expression Query
Copy codeThe Code is as follows:
Mysql> select * from student where birth regexp '2017 | 100 ';


Limit the number of query results
Copy codeThe Code is as follows:
Mysql> select * from student limit 2;
Mysql> select * from student limit 1, 3;