MySQL如何實現單表查詢

這篇文章給大家分享的是有關MySQL如何實現單表查詢的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

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

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)

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)

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)

• 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)

• 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)

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)

• 數據表創建時，可以指定某列中是否可以包含空值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)

【例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)

【例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中的操作數結合。

• 語法格式：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)

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)

• 分組查詢是對數據按照某個或多個字段進行分組，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子句排序輸出。

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條記錄。

感謝各位的閱讀！關于“MySQL如何實現單表查詢”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！