MySQL查詢資料之：單表查詢-mysql教程-PHP中文網

MySQL查詢資料之：單表查詢

單表查詢指從一張表資料中查詢所需的資料。
(1)查詢所有欄位
(2)查詢指定欄位
(3)查詢指定記錄
(4)帶in關鍵字的查詢
(5)帶between and的範圍的查詢
(6)帶like的字元匹配查詢
(7)查詢空值
(8)帶and的多條件查詢
(9)帶or的多條件查詢
(10)查詢結果不重複
(11)對查詢結果排序
(12)分組查詢
(13)使用limit限制查詢結果的數量

(免費學習推薦：mysql影片教學)

#(1)查詢所有欄位

#1.select * from fruits
2.select f_id,s_id,f_name,f_price from fruits;

上面兩個語句執行後的結果都是查詢所有欄位:

mysql> select * from fruits;+------+------+------------+---------+| f_id | s_id | f_name | f_price |+------+------+------------+---------+| 12 | 104 | lemon | 6.40 || a1 | 101 | apple | 5.20 || a2 | 103 | apricot | 2.20 || b1 | 101 | blackberry | 10.20 || b2 | 104 | berry | 7.60 || b5 | 107 | xxxx | 3.60 || bs1 | 102 | orange | 11.20 || bs2 | 105 | melon | 8.20 || c0 | 101 | cherry | 3.20 || m1 | 106 | mango | 15.70 || m2 | 105 | xbabay | 2.60 || m3 | 105 | xxtt | 11.60 || o2 | 103 | coconut | 9.20 || t1 | 102 | banana | 10.30 || t2 | 102 | grape | 5.30 || t4 | 107 | xbabay | 3.60 |+------+------+------------+---------+16 rows in set (0.00 sec)

登入後複製

(2)查詢指定欄位

1.查詢單一欄位:select 欄位名稱from 表名;

【範例】查詢fruits表中f_name列所有水果名稱,SQL語句如下:

mysql> select f_name from fruits;+------------+| f_name |+------------+| lemon || apple || apricot || blackberry || berry || xxxx || orange || melon || cherry || mango || xbabay || xxtt || coconut || banana || grape || xbabay |+------------+16 rows in set (0.00 sec)

登入後複製

2.查詢多個欄位:select 欄位名稱1,欄位名稱2,...欄位名稱n from 表名;
【範例】從fruits表中取得名稱為f_name和f_price兩列,SQL語句如下:

mysql> select f_name,f_price from fruits;+------------+---------+| f_name | f_price |+------------+---------+| lemon | 6.40 || apple | 5.20 || apricot | 2.20 || blackberry | 10.20 || berry | 7.60 || xxxx | 3.60 || orange | 11.20 || melon | 8.20 || cherry | 3.20 || mango | 15.70 || xbabay | 2.60 || xxtt | 11.60 || coconut | 9.20 || banana | 10.30 || grape | 5.30 || xbabay | 3.60 |+------------+---------+16 rows in set (0.00 sec)

登入後複製

(3)查詢指定記錄

select 字段名1，字段名2，。。。，字段名n from 表名 where 查询条件

登入後複製

#【例1】查詢價格為10.2元的水果名稱，SQL語句如下：

mysql> select f_name,f_price -> from fruits -> where f_price = 10.2;+------------+---------+| f_name | f_price |+------------+---------+| blackberry | 10.20 |+------------+---------+1 row in set (0.00 sec)

登入後複製

【例2】找出名稱為"apple"的水果價格，SQL語句如下：

mysql> select f_name,f_price -> from fruits -> where f_name = 'apple';+--------+---------+| f_name | f_price |+--------+---------+| apple | 5.20 |+--------+---------+1 row in set (0.00 sec)

登入後複製

【範例3】查詢價格小於10的水果名稱，SQL語句如下：

mysql> select f_name,f_price -> from fruits -> where f_price<10.00;+---------+---------+| f_name | f_price |+---------+---------+| lemon | 6.40 || apple | 5.20 || apricot | 2.20 || berry | 7.60 || xxxx | 3.60 || melon | 8.20 || cherry | 3.20 || xbabay | 2.60 || coconut | 9.20 || grape | 5.30 || xbabay | 3.60 |+---------+---------+11 rows in set (0.00 sec)

登入後複製

(4)帶in關鍵字的查詢

in操作子用來查詢滿足指定範圍內的條件的記錄，使用in運算符，將所有檢索條件用括號括起來，檢索條件之間用逗號分隔開，只要滿足條件範圍內的一個值即為匹配項。

【例1】s_id為101和102的記錄，SQL語句如下;

mysql> select s_id,f_name,f_price -> from fruits -> where s_id in(101,102) -> order by f_name;+------+------------+---------+| s_id | f_name | f_price |+------+------------+---------+| 101 | apple | 5.20 || 102 | banana | 10.30 || 101 | blackberry | 10.20 || 101 | cherry | 3.20 || 102 | grape | 5.30 || 102 | orange | 11.20 |+------+------------+---------+6 rows in set (0.00 sec)

登入後複製

【例2】查詢所有s_id既不等於101也不等於102的記錄， SQL語句如下：

mysql> select s_id,f_name,f_price -> from fruits -> where s_id not in (101,102) -> order by f_name;+------+---------+---------+| s_id | f_name | f_price |+------+---------+---------+| 103 | apricot | 2.20 || 104 | berry | 7.60 || 103 | coconut | 9.20 || 104 | lemon | 6.40 || 106 | mango | 15.70 || 105 | melon | 8.20 || 105 | xbabay | 2.60 || 107 | xbabay | 3.60 || 105 | xxtt | 11.60 || 107 | xxxx | 3.60 |+------+---------+---------+10 rows in set (0.00 sec)

登入後複製

(5)帶between and的範圍的查詢

between and 用來查詢某個範圍內的值，該運算子需要兩個參數，即範圍的開始值和結束值。

【例1】查詢價格在2.00元到10.20元之間的水果名稱和價格，SQL語句如下：

mysql> select f_name,f_price from fruits where f_price between 2.00 and 10.20;+------------+---------+| f_name | f_price |+------------+---------+| lemon | 6.40 || apple | 5.20 || apricot | 2.20 || blackberry | 10.20 || berry | 7.60 || xxxx | 3.60 || melon | 8.20 || cherry | 3.20 || xbabay | 2.60 || coconut | 9.20 || grape | 5.30 || xbabay | 3.60 |+------------+---------+12 rows in set (0.00 sec)

登入後複製

【例2】查詢價格在2.00元到10.20元以外的水果名稱和價格，SQL語句如下;

mysql> select f_name,f_price -> from fruits -> where f_price not between 2.00 and 10.20;+--------+---------+| f_name | f_price |+--------+---------+| orange | 11.20 || mango | 15.70 || xxtt | 11.60 || banana | 10.30 |+--------+---------+4 rows in set (0.00 sec)

登入後複製

(6)帶like的字符匹配查詢

1.%通配符，匹配任意長度的字符，甚至包括零字元。
【例1】找出所有以"b"字母開頭的水果，SQL語句如下：

mysql> select f_id,f_name -> from fruits -> where f_name like 'b%';+------+------------+| f_id | f_name |+------+------------+| b1 | blackberry || b2 | berry || t1 | banana |+------+------------+3 rows in set (0.00 sec)

登入後複製

【例2】在fruits表中，查詢f_name中包含字母"g"的記錄，SQL語句如下：

mysql> select f_id,f_name -> from fruits -> where f_name like '%g%';+------+--------+| f_id | f_name |+------+--------+| bs1 | orange || m1 | mango || t2 | grape |+------+--------+3 rows in set (0.00 sec)

登入後複製

【例3】查詢以"b"開頭，並以'y'結尾的水果的名稱，SQL語句如下：

mysql> select f_id,f_name -> from fruits -> where f_name like 'b%y';+------+------------+| f_id | f_name |+------+------------+| b1 | blackberry || b2 | berry |+------+------------+2 rows in set (0.00 sec)

登入後複製

2._通配符，一次只能匹配任意一個字元。

【例】在fruits表中，查詢以字母'y'結尾，且'y'前面只有4個字母的記錄，SQL語句如下：

mysql> select f_id,f_name -> from fruits -> where f_name like '____y';+------+--------+| f_id | f_name |+------+--------+| b2 | berry |+------+--------+1 row in set (0.00 sec)

登入後複製

(7)查詢空值

資料表建立時，可以指定某列中是否可以包含空值null。空值不同於0，也不同於空字串。空值一般表示資料位置、不使用或將在以後新增資料。在select語句中使用is null子句，可以查詢某個欄位內容為空記錄。

下面建立資料表customers來示範：

create table customers( c_id int not null auto_increment, c_name char(50) not null, c_address char(50) null, c_city char(50) null, c_zip char(10) null, c_contact char(50) null, c_email char(255) null, primary key (c_id) );

登入後複製

插入下列語句:

mysql> insert into customers(c_id,c_name,c_address,c_city,c_zip,c_contact,c_email) -> values -> (10001,'redhool','200 Street ','Tianjin','300000','LiMing','LMing@163.com'), -> (10002,'Stars','333 Fromage Lane','Dalian','116000','Zhangbo','Jerry@hotmail.com'), -> (10003,'Netbhood','1 Sunny Place','Qingdao','266000','LuoCong',null), -> (10004,'JOTO','829 Riverside Drive','Haikou','570000','YangShan','sam@hotmail.com');Query OK, 4 rows affected (0.06 sec)Records: 4 Duplicates: 0 Warnings: 0

登入後複製

【範例1】查詢customers表中c_email為空的記錄的c_id 、c_name和c_email欄位值，SQL語句如下：

mysql> select c_id,c_name,c_email from customers where c_email is null;+-------+----------+---------+| c_id | c_name | c_email |+-------+----------+---------+| 10003 | Netbhood | NULL |+-------+----------+---------+1 row in set (0.00 sec)

登入後複製

【例2】查詢customers表中c_email不為空的記錄的c_id、c_name、和c_email欄位值，SQL語句如下：

mysql> select c_id,c_name,c_email -> from customers -> where c_email is not null;+-------+---------+-------------------+| c_id | c_name | c_email |+-------+---------+-------------------+| 10001 | redhool | LMing@163.com || 10002 | Stars | Jerry@hotmail.com || 10004 | JOTO | sam@hotmail.com |+-------+---------+-------------------+3 rows in set (0.00 sec)

登入後複製

(8)帶and的多條件查詢

【例1】在fruits表中查詢s_id=101，且f_price大於5的水果的價格和名稱，SQL語句如下：

mysql> select f_id,f_price,f_name -> from fruits -> where s_id = '101' and f_price>=5; +------+---------+------------+ | f_id | f_price | f_name | +------+---------+------------+ | a1 | 5.20 | apple | | b1 | 10.20 | blackberry | +------+---------+------------+ 2 rows in set (0.00 sec)

登入後複製

【例2】在fruits表中查詢s_id=101或102,且f_price大於5,且f_name='apple'的水果價格和名稱，SQL語句如下：

mysql> select f_id ,f_price , f_name -> from fruits -> where s_id in ('101','102') and f_price >=5 and f_name = 'apple';+------+---------+--------+| f_id | f_price | f_name |+------+---------+--------+| a1 | 5.20 | apple |+------+---------+--------+1 row in set (0.05 sec)

登入後複製

(9)帶or的多條件查詢

【例1】查詢s_id=101或s_id=102的水果供應商的f_price和f_name，SQL語句如下：

mysql> select s_id,f_name,f_price -> from fruits -> where s_id=101 or s_id=102;+------+------------+---------+| s_id | f_name | f_price |+------+------------+---------+| 101 | apple | 5.20 || 101 | blackberry | 10.20 || 102 | orange | 11.20 || 101 | cherry | 3.20 || 102 | banana | 10.30 || 102 | grape | 5.30 |+------+------------+---------+6 rows in set (0.00 sec)

登入後複製

【例2】查詢s_id= 101或s_id=102的水果供應商的f_price和f_name，SQL語句如下：

mysql> select s_id,f_name,f_price -> from fruits -> where s_id in(101,102);+------+------------+---------+| s_id | f_name | f_price |+------+------------+---------+| 101 | apple | 5.20 || 101 | blackberry | 10.20 || 102 | orange | 11.20 || 101 | cherry | 3.20 || 102 | banana | 10.30 || 102 | grape | 5.30 |+------+------------+---------+6 rows in set (0.00 sec

登入後複製

注意
or可以和and一起使用，但是要注意兩者的優先級，由於and的優先權高於or，旖旎次先對and兩邊的操作數進行操作，再與or中的操作數結合。

(10)查詢結果不重複

語法格式：select distinct 欄位名稱from 表名；

【範例】查詢fruits表中s_id欄位的值，傳回s_id欄位值且不得重複,SQL語句如下;

mysql> select distinct s_id from fruits;+------+| s_id |+------+| 104 || 101 || 103 || 107 || 102 || 105 || 106 |+------+7 rows in set (0.05 sec)

登入後複製

(11)對查詢結果排序

1.單列排序order by

【範例】查詢fruits表的f_name欄位值，並對其進行排序，SQL語句如下：

mysql> select f_name from fruits order by f_name;+------------+| f_name |+------------+| apple || apricot || banana || berry || blackberry || cherry || coconut || grape || lemon || mango || melon || orange || xbabay || xbabay || xxtt || xxxx |+------------+16 rows in set (0.00 sec)

登入後複製

2.多列排序

多列排序首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

【例】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL语句如下：

mysql> select f_name,f_price -> from fruits -> order by f_name, f_price;+------------+---------+| f_name | f_price |+------------+---------+| apple | 5.20 || apricot | 2.20 || banana | 10.30 || berry | 7.60 || blackberry | 10.20 || cherry | 3.20 || coconut | 9.20 || grape | 5.30 || lemon | 6.40 || mango | 15.70 || melon | 8.20 || orange | 11.20 || xbabay | 2.60 || xbabay | 3.60 || xxtt | 11.60 || xxxx | 3.60 |+------------+---------+16 rows in set (0.00 sec)

登入後複製

3.指定排序方向desc

desc是降序排列，与之对应的是asc升序排列，但asc是默认的，可以不加。

【例1】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序，SQL语句如下：

mysql> select f_name,f_price -> from fruits -> order by f_price desc;+------------+---------+| f_name | f_price |+------------+---------+| mango | 15.70 || xxtt | 11.60 || orange | 11.20 || banana | 10.30 || blackberry | 10.20 || coconut | 9.20 || melon | 8.20 || berry | 7.60 || lemon | 6.40 || grape | 5.30 || apple | 5.20 || xxxx | 3.60 || xbabay | 3.60 || cherry | 3.20 || xbabay | 2.60 || apricot | 2.20 |+------------+---------+16 rows in set (0.00 sec)

登入後複製

【例2】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下：

mysql> select f_price,f_name -> from fruits -> order by f_price desc,f_name;+---------+------------+| f_price | f_name |+---------+------------+| 15.70 | mango || 11.60 | xxtt || 11.20 | orange || 10.30 | banana || 10.20 | blackberry || 9.20 | coconut || 8.20 | melon || 7.60 | berry || 6.40 | lemon || 5.30 | grape || 5.20 | apple || 3.60 | xbabay || 3.60 | xxxx || 3.20 | cherry || 2.60 | xbabay || 2.20 | apricot |+---------+------------+16 rows in set (0.00 sec)

登入後複製

(12)分组查询

分组查询是对数据按照某个或多个字段进行分组，MySQL中使用group by 关键字对数据进行分组，基本语法形式为：[group by 字段][having<条件表达式>]
字段值为进行分组时所依据的列名称，"having <条件表达式> "指定满足表达式限定条件的结果将被显示。

1.创建分组

【例1】根据s_id对fruits表中的数据进行分组，SQL语句如下;

mysql> select s_id,count(*) as total -> from fruits -> group by s_id;+------+-------+| s_id | total |+------+-------+| 104 | 2 || 101 | 3 || 103 | 2 || 107 | 2 || 102 | 3 || 105 | 3 || 106 | 1 |+------+-------+7 rows in set (0.05 sec)

登入後複製

可以看到group by 子句按照s_id排序并对数据分组。

如果需要查看每个供应商提供的水果种类名称，可以在group by子句中使用group_concat()函数，将每个分组中各个字段的值显示出来。

【例2】根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来，SQL语句如下：

mysql> select s_id,group_concat(f_name) as Names -> from fruits -> group by s_id;+------+-------------------------+| s_id | Names |+------+-------------------------+| 101 | apple,blackberry,cherry || 102 | orange,banana,grape || 103 | apricot,coconut || 104 | lemon,berry || 105 | melon,xbabay,xxtt || 106 | mango || 107 | xxxx,xbabay |+------+-------------------------+7 rows in set (0.05 sec)

登入後複製

2.使用having过滤分组

groub by可以和having一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。
having和where都是用来过滤数据的，having在数据分组之后进行过滤来选择分组，而where在分组之前用来选择记录。where排除的记录不再包括在分组中。

【例】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息，SQL语句如下：

mysql> select s_id,group_concat(f_name) as Names -> from fruits -> group by s_id having count(f_name) >1;+------+-------------------------+| s_id | Names |+------+-------------------------+| 101 | apple,blackberry,cherry || 102 | orange,banana,grape || 103 | apricot,coconut || 104 | lemon,berry || 105 | melon,xbabay,xxtt || 107 | xxxx,xbabay |+------+-------------------------+6 rows in set (0.00 sec)

登入後複製

3.在group by 子句中使用with rollup

使用with rolluo关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

【例】根据s_id对fruits表中的数据进行分组，并显示记录数量，SQL语句如下：

mysql> select s_id,count(*) as Total -> from fruits -> group by s_id with rollup;+------+-------+| s_id | Total |+------+-------+| 101 | 3 || 102 | 3 || 103 | 2 || 104 | 2 || 105 | 3 || 106 | 1 || 107 | 2 || NULL | 16 |+------+-------+8 rows in set (0.05 sec)

登入後複製

4.多字段分组

使用group by可以对多个字段进行分组，group by 关键字后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组，以此类推。

【例】根据s_id和f_name字段对fruits表中的数据进行分组，SQL语句如下：

mysql> select * from fruits group by s_id,f_name;+------+------+------------+---------+| f_id | s_id | f_name | f_price |+------+------+------------+---------+| 12 | 104 | lemon | 6.40 || a1 | 101 | apple | 5.20 || a2 | 103 | apricot | 2.20 || b1 | 101 | blackberry | 10.20 || b2 | 104 | berry | 7.60 || b5 | 107 | xxxx | 3.60 || bs1 | 102 | orange | 11.20 || bs2 | 105 | melon | 8.20 || c0 | 101 | cherry | 3.20 || m1 | 106 | mango | 15.70 || m2 | 105 | xbabay | 2.60 || m3 | 105 | xxtt | 11.60 || o2 | 103 | coconut | 9.20 || t1 | 102 | banana | 10.30 || t2 | 102 | grape | 5.30 || t4 | 107 | xbabay | 3.60 |+------+------+------------+---------+16 rows in set (0.00 sec)

登入後複製

5.group by 和order by一起使用

某些情况下需要对分组进行排序，order by用来对查询的记录排序，如果和group by一起使用可以完成对分组的排序。

创建数据表演示：

mysql> create table orderitems -> ( -> o_num int not null, -> o_item int not null, -> f_id char(10) not null, -> quantity int not null, -> item_price decimal(8,2) not null, -> primary key (o_num,o_item) -> );Query OK, 0 rows affected (0.13 sec)mysql> insert into orderitems(o_num,o_item,f_id,quantity,item_price) -> values(30001,1,'a1',10,5.2), -> (30001,2,'b2',3,7.6), -> (30001,3,'bs1',5,11.2), -> (30001,4,'bs2',15,9.2), -> (30002,1,'b3',2,20.0), -> (30003,1,'c0',100,10), -> (30004,1,'o2',50,2.50), -> (30005,1,'c0',5,10), -> (30005,2,'b1',10,8.99), -> (30005,3,'a2',10,2.2), -> (30005,4,'m1',5,14.99);Query OK, 11 rows affected (0.06 sec)Records: 11 Duplicates: 0 Warnings: 0

登入後複製

【例】查询订单价格大于100的订单号和总订单价格，SQL语句如下;

mysql> select o_num,sum(quantity * item_price) as orderTotal -> from orderitems -> group by o_num -> having sum(quantity * item_price) >=100;+-------+------------+| o_num | orderTotal |+-------+------------+| 30001 | 268.80 || 30003 | 1000.00 || 30004 | 125.00 || 30005 | 236.85 |+-------+------------+4 rows in set (0.00 sec)

登入後複製

可以看到orderTotal列的总订单价格并没有按照一定的顺序显示，接下来使用order by关键字按总订单价格排列显示结果，SQL语句如下：

mysql> select o_num,sum(quantity * item_price) as orderTotal -> from orderitems -> group by o_num -> having sum(quantity * item_price)>=100 -> order by orderTotal;+-------+------------+| o_num | orderTotal |+-------+------------+| 30004 | 125.00 || 30005 | 236.85 || 30001 | 268.80 || 30003 | 1000.00 |+-------+------------+4 rows in set (0.00 sec)

登入後複製

可以看到，group by 子句按订单号对数据进行分组，sum()函数便可以返回总的订单价格，having子句对分组数据进行过滤，使得只返回总价格大于100的订单，最后使用order by子句排序输出。

(13)使用limit限制查询结果的数量

select返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用limit关键字，基本语法如下：

limit [位置偏移量，] 行数

登入後複製

第一个"位置偏移量"参数只是MySQL从哪一行开始显示，是一个可选参数，如果不指定"位置偏移量",将会从表中的第一条记录开始(第一条记录的位置偏移量是0,第二条记录的位置偏移量是1,以此类推);第二个参数"行数指示返回的记录条数"。

【例1】显示fruits表查询结果的前4行，SQL语句如下：

mysql> select * from fruits limit 4;+------+------+------------+---------+| f_id | s_id | f_name | f_price |+------+------+------------+---------+| 12 | 104 | lemon | 6.40 || a1 | 101 | apple | 5.20 || a2 | 103 | apricot | 2.20 || b1 | 101 | blackberry | 10.20 |+------+------+------------+---------+4 rows in set (0.00 sec)

登入後複製

【例2】在fruits表中，使用limit子句，返回从第5个记录开始的、行数长度为3的记录，SQL语句如下：

mysql> select * from fruits limit 4,3;+------+------+--------+---------+| f_id | s_id | f_name | f_price |+------+------+--------+---------+| b2 | 104 | berry | 7.60 || b5 | 107 | xxxx | 3.60 || bs1 | 102 | orange | 11.20 |+------+------+--------+---------+3 rows in set (0.00 sec)

登入後複製

也可以使用"limit 4 offset 3"也是获取从第5条记录开始后面的3条记录。