參考表：student

?

多字段查詢

mysql> select id,name,birth from student;

?

所有字段查詢

mysql> select * from student;

?

where指定查詢

mysql> select * from student where id=901; mysql> select * from student where id>=904; mysql> select name from student where department='計算機系';

?

in指定集合查詢

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

?

not in非范圍查詢

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

?

between and指定范圍查詢

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

?

not between and不在指定范圍的查詢

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

?

like字符串匹配查詢

mysql> select * from student where name like '_三'; mysql> select * from student where name like '張三'; mysql> select * from student where name like '張%';

?

not like不匹配查詢

mysql> select * from student where name not like '張%';

?

null查詢

mysql> select * from student where address is null;

?

and多條件查詢

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

?

or多條件查詢

mysql> select * from student where id=905 or birth=1988; mysql> select * from student where id=905 or birth=1988 or sex='女';

?

distinct查詢結果不重復

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

?

order by查詢結果排序

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分組查詢

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

?

正則表達式查詢

mysql> select * from student where birth regexp '1988|1990';

?

limit限制查詢結果數量

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

?

?

轉載于:https://www.cnblogs.com/nerxious/archive/2012/12/29/2839250.html

總結

以上是生活随笔為你收集整理的MySQL学习笔记7：基本查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。