Mysql增刪改查之查詢記錄

在講解查詢前，我為大家準備了一個資料表。這個表中存放著銀行的餘額和使用者的基本資料。

我們定義了一個表格結構，表名為money。

「建立表格的語句如下：

CREATE TABLE money (
#id INT NOT NULL AUTO_INCREMENT ,
username VARCHAR(50) NOT NULL ,
#balance FLOAT NOT NULL ,
#balance FLOAT NOT NULL ,
age
TINYINT UNSIGNED NOT NULL , sex
TINYINT NOT NULL , PRIMARY KEY (id
(10))
) ENGINE = InnoDB CHARACTER SET utf8;

表格結構與資料顯示如下：120.02##2#范冰冰260.23山東40#03黃曉明150.86山東40#14井柏然 #810##20.15 313#12345623.4##湖南36010#趙本山345611

##id	#username	balance	province	age	sex
1				#1	王寶強
上海	29	1



遼寧	27	1		#5	李冰冰
黑龍江	43	0		6	成龍
山東	63	1		#7	楊冪
北京	30	0		8	劉詩詩
北京	29	1		9	柳岩
9	柳岩	23.4
		##遼寧	63	1

#Wwa

#34.32

北京

44112

##########212######天津#######43 ######1###############註：###balance 是指餘額###province 是指省份######基礎查詢#### ###########類別######詳細解示###################基本語法######select * from表;############範例######select * from money;############範例說明######查詢money表中所有欄位中的所有結果############

註：”*” 是一種正規表示式的寫法，表示匹配所有，上面的查詢語句和下面的是等價：

mysql> select * from money;
+- ---+-----------+---------+-----------+-----+-----+
| id | username | balance | province | age | sex |
+----+-----------+---------+--- --------+-----+-----+
| 1 | 王寶強 | 120.02 | 湖北 | 29 | 1 |
| 2 | | 0 |
| 3 | 黃曉明 | 150.86 | 山東 | 40 | 1 |
| 4 | 汝 5 | 李冰冰 | 20.15 | 黑龍江 | 43 | 0 |
| 6 | 成龍 | 313 | 山東 | 63 | 1 |
| 7 | 楊冪 | 1 |
| 7 | 楊冪 | 12 北京劉 |詩詩 | 456 | 北京 | 29 | 1 |
| 9 | 柳岩 | 23.4 | 湖南 | 36 | 0 |
#| 10 | 趙本山 | 3456 |115 | 34.32 | 北京 | 44 | 1 |
| 12 | 郭德綱 | 212 | 天津 | 43 | 1 |
+----+-----------+---------+------ -----+-----+-----+
12 rows in set (0.00 sec)

#指定欄位查詢

類別基本語法範例範例說明

mysql> select id,username, balance from money;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 1 | 王寶強 | 120.02 |
| 。 | 313 |
| 7 | 楊冪 | 123 |
| 8 | 劉詩詩 | 456 |
| 9 | 柳岩 # 11 | 汪峰 | 34.32 |
| 12 | 郭德綱 | 212 |
+----+-----------+---------+
12 rows in set (0.00 sec)

查詢單一欄位不重複記錄distinct

類別

詳細解示

詳細解示
select 欄位from 表;
select id,username, balance from money;
查詢money表中id,username, balance欄位中的所有結果

select distinct 欄位from 表;mysql> select distinct age deptno from money;| deptno |+--------+| 29 |

	基本語法
#範例	select distinct age deptno from money;
範例說明	查詢money表中年齡唯一的所有結果
	+--------+

| 40 |

| 27 |
| 43 |
| 63 |
| 30 |
| 36 |
| 44 |
+--------+
8 rows in set (0.00 sec )

條件查詢where

類別

詳細解示##基本語法select 欄位from 表where where條件;範例select * from money where age = 29;範例說明查詢money表中年齡為29的所有結果#

mysql> select * from money where age = 29;
+----+-----------+---------+----- -----+-----+-----+
| id | username | balance | province | age | sex |
+----+------- ----+---------+----------+-----+-----+
| 1 | 李文凱 | 120.02 | 湖北 | 29 | 1 |
| 8 | 劉詩詩 | 456 | 北京 | 29 | 1 |
+----+-----------+--------- +----------+-----+-----+
2 rows in set (0.00 sec)

where後可接的條件

比較運算子結果集中將符合條件的記錄列出來。在上面的例子中，where 後面的田間是一個字段的 ‘=’。

除此之外，還可以使用>、<、>=、<=、!=等比較運算子；

符號	說明
>	大於
<	小於
>=	大於等於
<=	小於等於
!=	不等於
=	等於

#邏輯運算子

多個條件也可以使用or 、 and 等邏輯運算子進行多條件聯合查詢說明或and

	符號
	or

並且

類型詳細內容範例select * from money where id <10 and#說明

我們來看多個條件的範例：

查詢所有欄位要求id小於10 且province='湖北'

mysql> select * from money where id <10 and province='湖北';
+----+-----------+---------+----------+-----+----- +
| id | username | balance | province | age | sex |

+----+-----------+---------+----------+-----+----- +

| 1 | 王寶強 | 120.02 | 湖北 | 29 | 1 |

+----+-----------+---------+----------+-----+----- + 1 row in set (0.00 sec)類別#詳細解示

結果集排序

###### #############基本語法######select 欄位from 表格order by 欄位排序關鍵字############範例####### select id,username, balance from money order by balance desc;############範例說明######查詢money表中的id,username,balance字段，依照餘額進行降序排序############

排序用到的關鍵字：

關鍵字	說明

asc	升序排列，從小到大（預設）

desc

降序排列，從大到小

在select 出來之後的結果集中排序使用order by ，其中desc 和asc 是排序順序中的關鍵字。 desc 表示依照欄位進行降序排列，asc 表示升序排列，如果不寫關鍵字預設升序排列。

mysql> select id,username, balance from money order by balance desc;
+----+-----------+-------- -+
| id | username | balance |
+----+-----------+---------+
| 10 | 趙本山| 3456 |
| 4 | 井柏然 | 810 |
| 8 | 劉詩詩 | 456 |
| 16 | 成龍冰 1 260.23 |
| 12 | 郭德綱| 212 |
| 3 | 黃曉明 | 150.86 |
| 7 | 楊冪 | 123 | ## 102 王寶強 |10. 34.32 |
| 9 | 柳岩| 23.4 |

| 5 | 李冰冰 | 20.15 |

+----+-----------+---------+

##12 rows in set (0.00 sec)

多重欄位排序order by 後面可以跟多個不同的欄位排序，排序欄位的不同結果集的順序也不同，如果排序欄位的值一樣，則值相同的欄位會依照第二個排序欄位進行排序。類別詳細解示基本語法 #select 欄位from 表order by 欄位1 排序關鍵字,... ...欄位n desc|asc;範例select id,username, balance from money order by balance desc,age asc;範例說明查詢money表中的id,username,balance字段，依照餘額進行降序排序,若餘額全都一樣，則再使用age進行升序排序

##########

* 註：如果第一個欄位已經將結果給排好。第二個字段排序字段不生效。本例中，第二個欄位無效。 *

mysql> select id,username, balance from money order by balance desc,age asc;
+----+-----------+---------+
# | id | username | balance |
+----+-----------+---------+
# | 10 | 趙本山 | 3456 |
| 4 | 井柏然 | 810 |
| 8 | 劉詩詩 | 456 |
| 6 | 成龍 | 313 |
| 2 | 範冰冰 | 260.23 |
| 12 | 郭德綱 | 212 |
| 3 | 黃曉明 | 150.86 |
| 7 | 楊冪 | 123 |
| 1 | 王寶強 | 120.02 |
| 11 | 汪峰 | 34.32 |
| 9 | 柳岩 | 23.4 |
| 5 | 李冰冰 | 20.15 |
+----+-----------+---------+
# 12 rows in set (0.00 sec)

結果集限制

對於查詢或排序後的結果集，如果希望只顯示一部分而不是全部，使用limit 關鍵字結果集數量限制。

類別	詳細解示
基本語法	#select 欄位from 表 limit 數量;
範例	#select id,username, balance from money limit 5;
#範例說明	顯示前五個使用者

mysql> select * from money limit 5;
+----+-----------+---------+------- ----+-----+-----+
| id | username | balance | province | age | sex |
+----+-------- ---+---------+-----------+-----+-----+
| 1 | 王寶強 | 120.02 | 湖北 | 29 | 1 |
| 2 | 範冰冰 | 260.23 | 山東 | 40 | 0 |
#| 3 | 黃曉栠 40 | 0 |
| 3 | 黃小頠 | 4 | 井柏然 | 810 | 遼寧 | 27 | 1 |
| 5 | 李冰冰 | 20.15 | 黑龍江 | 43 | 0 |
+----+-----------+---------+- ----------+-----+-----+
5 rows in set (0.00 sec)

#限制結果集並排序

類別	詳細解示
#基本語法	select 欄位from 表order by 欄位關鍵字limit 數量
#範例	select id,username, balance from money order by balance desc limit 5;
#範例說明	依照錢來排序，顯示前五個最有錢的用戶

mysql> select id,username, balance from money order by balance desc limit 5;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 10 | 趙本山 | 3456 |
| 4 | 井柏然 | 810 |
| 8 | 劉詩詩 | 456 |
| 6 | 成龍 | 313 |
| 2 | 範冰---------+
5 rows in set (0.00 sec)

結果集區間選擇

假設我從第0條開始取了3筆記錄。又想再從第3條開始取3筆記錄。再想從第6筆開始取4筆記錄怎麼辦？

這時候就需要使用到結果集區間選擇。

類別詳細解示基本語法 #select 欄位from 表 limit 偏移量,數量範例select id,username, balance from money limit 0,3;#範例說明從第一筆開始取三筆記錄#

註：第一筆記錄為0。

mysql> select id,username, balance from money limit 0,3;
+----+-----------+---------+
# | id | username | balance |
+----+-----------+---------+
# | 1 | 王寶強 | 120.02 |
| 2 | 範冰冰 | 260.23 |
| 3 | 黃曉明 | 150.86 |
+----+-----------+---------+
# 3 rows in set (0.00 sec)

從第三條開始再取三條呢？

mysql> select id,username, balance from money limit 3,3;
+----+-----------+---------+
# | id | username | balance |
+----+-----------+---------+
# | 4 | 井柏然 | 810 |
| 5 | 李冰冰 | 20.15 |
| 6 | 成龍 | 313 |
+----+-----------+---------+
# 3 rows in set (0.00 sec)

透過上面的這個思路，顯示就完成了分頁。
每頁顯示10筆記錄，則：

第1頁為 limit 0,10
第2頁為 limit 10,10
第3頁為 limit 20,10

依此類推... ...

統計類別函數使用

如果我們想知道總使用者數怎麼辦？
查詢誰是資料表裡的首富怎麼辦？
如果我們想知道用戶的平均金額怎麼辦？
如果我們想知道所有用戶的總金額怎麼辦？

統計類別函數最常用的我們有四個：

##avg

#函數	說明
#sum	總和
count	統計總數
#max	最大
min	最小值

avg
#平均值

註：當然你知道其他的mysql函數也可以使用。不過，在實際工作中，大公司的許多大中型項上很少使用，他們都有專門的計數伺服器。因為，mysql的計算量本身很大，為了減少壓力通常我們將實際的運算任務交給業務伺服器或其他伺服器來完成。類別#詳細解示基本語法select 函數(字段) from 表範例#select count(id) from money

###範例說明######查詢money表的id總數############

mysql> select count(id) from money;
+-----------+
| count(id) |
+-----------+
| 12 |
+-----------+
1 row in set (0.00 sec)

你也可以為欄位取別名喲！使用as關鍵字。

mysql> select count(id) as zongshu from money;
+---------+
| zongshu |
+---------+
| 12 |
+---------+
1 row in set (0.00 sec)

查詢平均金額

mysql> select avg(balance) from money;
+--------------------+
| avg(balance) |
+--------------------+
| 498.24833393096924 |
+--------------------+
1 row in set (0.00 sec)

查詢總金額

mysql> select sum(balance) from money;
+-----------------+
| sum(balance) |
# +-----------------+
| 5978.980007171631 |
+-----------------+
1 row in set (0.00 sec)

查詢最大金額

mysql> select max(balance) from money;
+--------------+
| max(balance) |
+--------------+
| 3456 |
+--------------+
1 row in set (0.00 sec)

查詢最小金額

mysql> select min(balance) from money;
+--------------------+
| min(balance) |
# +--------------------+
| 20.149999618530273 |
+--------------------+
1 row in set (0.00 sec)

分組 group by

我們拿金額表裡面的省份進行分組數據，分組數據後你會發現。有相同的省份會去掉。即，一個省份為一個組。

類別	詳細解示
基本語法	#select * from 表group by 欄位
#範例	select * from money group by province;
範例說明	依照地區進行分組

mysql> select * from money group by province;
+----+-----------+---------+------ -----+-----+-----+
| id | username | balance | province | age | sex |
+----+------- ----+---------+-----------+-----+-----+
| 7 | 楊冪 | 123 | 北京| 30 | 0 |
| 12 | 郭德綱 | 212 | 天津 | 43 | 1 |
| 2 1 | 王寶強 | 120.02 | 湖北 | 29 | 1 |
| 9 | 柳岩 | 23.4 | 湖南 | 36 | 0 |
| 4 | 井 # | 5 | 李冰冰 | 20.15 | 黑龍江 | 43 | 0 |
+----+-----------+---------+-----------+-----+ -----+

統計分組（分類）各總數：

mysql> select deptno, count(1) from emp group by deptno;

+----- ---+----------+

| deptno | count(1) |
+--------+----------+
| 1 | 1 |
#| 2 | 5 |
| 3 | ---- -----+
4 rows in set (0.04 sec)

統計省份數量後再進行分組顯示

mysql> select count(province),province from money group by province;

+-----------------+-----------+

| count(province) | province |

+-----------------+-----------+
| 3 | 北京 |
| 1 | 天津 |
| 3 | 山東 |
| 1 | 湖北 |
| 1 | 湖南 |
| 2 | 遼寧 |
| 1 | 黑色龍江 |
+-----------------+-----------+
7 rows in set (0.00 sec)

在分組基礎上進行統計
with rollup用的很少。這個知識點設定為了解等級。

它的主要功能是對於分組的資料進行統計後，再進行一次總數統計。

對分組的數再次進行統計

類別	詳細解示
#基本語法	select * from 表group by 欄位with rollup
範例	select count(province),province from money group by province with rollup;
	##範例說明

#在上面的基礎上統計總數，下例結果中，最後多了一個12 NULL。

mysql> select count(province),province from money group by province with rollup;
+-----------------+-----------+
| count(province) | province |
+-----------------+-----------+
| 3 | 北京 |
| 1 | 天津 |
| 3 | 山東 |
| 1 | 湖北 |
| 1 | 湖南 |
| 2 | 遼寧 |
| 1 | 黑色龍江 |
| 12 | NULL |
+-----------------+-----------+

8 rows in set (0.00 sec)

結果再過濾having

having子句與where有相似之處但也有區別,都是設定條件的語句。

having 是篩選組而where是篩選記錄。詳細解示 #select * from 表group by 欄位having 條件select count(province) as result ,province from money group by province having result >2;將地區分組並統計總數，將分組結果中大於2的分組地區顯示出來

	類別
	基本語法
	範例
	範例說明

######

mysql> select count(province) as result ,province from money group by province having result >2;
+--------+----------+
| result | province |
+--------+----------+
| 3 | 北京 |
| 3 | 山東 |
+--------+----------+
2 rows in set (0.00 sec)

整體使用SQL

我們在上面的語句中都是單一使用的某些語句，沒有整體使用過。

我們現在將語句進行整合後，搭配使用一次。整體的SQL語句配合使用的語法結構如下：

SELECT
[字段1 [as 別名1],[函數(字段2) ,]......字段n]
FROM 表名
[WHERE 其中條件]
[GROUP BY 字段]
[HAVING where_contition]
[order 條件]
[limit 條件]

附註：上面的語句中可以[] 代表可選。

最終的語法總結如下：

##order by#排序屬性limit起始記錄位置，取記錄的條數

關鍵字	說明
#select	選擇的欄位
from	表
where	查詢的條件
group by	分組屬性 having 分組篩選的條件

我們進行一次整體的給合使用，查詢money表格欄位：id,username,balance,province 要求id>1 餘額大於50，使用地區進行分組。我們使用用戶id進行降序，要求只準顯示3條。

最後將SQL語句寫成，查詢出來的結果如下：

mysql> select id,username,balance,province from money where id > 1 and balance > 50 group by province order by id desc limit 3;
+----+-----------+---------+----------+
| id | username | balance | province |
+----+-----------+---------+----------+
| 12 | 郭德綱 | 212 | 天津 |
| 7 | 楊冪 | 123 | 北京 |
| 4 | 井柏然 | 810 | 遼寧 |
+----+-----------+---------+----------+
3 rows in set (0.00 sec)

繼續學習