Mysql增刪改查之查詢記錄
在講解查詢前，我為大家準備了一個數(shù)據(jù)表。這個表中存放著銀行的余額和用戶的基本信息。
我們定義了一個表結構，表名為money。
創(chuàng)建表的語句如下：
CREATE TABLE
money(
idINT NOT NULL AUTO_INCREMENT ,
usernameVARCHAR(50) NOT NULL ,
balanceFLOAT NOT NULL ,
provinceVARCHAR(20) NOT NULL ,
ageTINYINT UNSIGNED NOT NULL ,
sexTINYINT NOT NULL ,
? ? ?PRIMARY KEY (id(10))
? ? ?) ENGINE = InnoDB CHARACTER SET utf8;
表結構和數(shù)據(jù)展示如下：
| id | username | balance | province | age | sex |
|---|---|---|---|---|---|
| 1 | 王寶強 | 120.02 | 上海 | 29 | 1 |
| 2 | 范冰冰 | 260.23 | 山東 | 40 | 0 |
| 3 | 黃曉明 | 150.86 | 山東 | 40 | 1 |
| 4 | 井柏然 | 810 | 遼寧 | 27 | 1 |
| 5 | 李冰冰 | 20.15 | 黑龍江 | 43 | 0 |
| 6 | 成龍 | 313 | 山東 | 63 | 1 |
| 7 | 楊冪 | 123 | 北京 | 30 | 0 |
| 8 | 劉詩詩 | 456 | 北京 | 29 | 1 |
| 9 | 柳巖 | 23.4 | 湖南 | 36 | 0 |
| 10 | 趙本山 | 3456 | 遼寧 | 63 | 1 |
| 11 | 汪峰 | 34.32 | 北京 | 44 | 1 |
| 12 | 郭德綱 | 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 | 范冰冰 ? ?| ?260.23 | 山東 ? ? ?| ?40 | ? 0 |
| ?3 | 黃曉明 ? ?| ?150.86 | 山東 ? ? ?| ?40 | ? 1 |
| ?4 | 井柏然 ? ?| ? ? 810 | 遼寧 ? ? ?| ?27 | ? 1 |
| ?5 | 李冰冰 ? ?| ? 20.15 | 黑龍江 ? ?| ?43 | ? 0 |
| ?6 | 成龍 ? ? ?| ? ? 313 | 山東 ? ? ?| ?63 | ? 1 |
| ?7 | 楊冪 ? ? ?| ? ? 123 | 北京 ? ? ?| ?30 | ? 0 |
| ?8 | 劉詩詩 ? ?| ? ? 456 | 北京 ? ? ?| ?29 | ? 1 |
| ?9 | 柳巖 ? ? ?| ? ?23.4 | 湖南 ? ? ?| ?36 | ? 0 |
| 10 | 趙本山 ? ?| ? ?3456 | 遼寧 ? ? ?| ?63 | ? 1 |
| 11 | 汪峰 ? ? ?| ? 34.32 | 北京 ? ? ?| ?44 | ? 1 |
| 12 | 郭德綱 ? ?| ? ? 212 | 天津 ? ? ?| ?43 | ? 1 |
+----+-----------+---------+-----------+-----+-----+
12 rows in set (0.00 sec)
指定字段查詢
| 類別 | 詳細解示 |
|---|---|
| 基本語法 | select 字段 from 表; |
| 示例 | select id,username, balance from money; |
| 示例說明 | 查詢money表中id,username, balance字段中的所有結果 |
mysql> select id,username, balance from money;
+----+-----------+---------+
| id | username ?| balance |
+----+-----------+---------+
| ?1 | 王寶強 ? ?| ?120.02 |
| ?2 | 范冰冰 ? ?| ?260.23 |
| ?3 | 黃曉明 ? ?| ?150.86 |
| ?4 | 井柏然 ? ?| ? ? 810 |
| ?5 | 李冰冰 ? ?| ? 20.15 |
| ?6 | 成龍 ? ? ?| ? ? 313 |
| ?7 | 楊冪 ? ? ?| ? ? 123 |
| ?8 | 劉詩詩 ? ?| ? ? 456 |
| ?9 | 柳巖 ? ? ?| ? ?23.4 |
| 10 | 趙本山 ? ?| ? ?3456 |
| 11 | 汪峰 ? ? ?| ? 34.32 |
| 12 | 郭德綱 ? ?| ? ? 212 |
+----+-----------+---------+
12 rows in set (0.00 sec)
查詢單個字段不重復記錄 distinct
| 類別 | 詳細解示 |
|---|---|
| 基本語法 | select distinct 字段 from 表; |
| 示例 | select distinct age deptno from money; |
| 示例說明 | 查詢money表中年齡唯一的所有結果 |
mysql> select distinct age deptno from money;
+--------+
| deptno |
+--------+
| ? ? 29 |
| ? ? 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 等邏輯運算符進行多條件聯(lián)合查詢
| 符號 | 說明 |
|---|---|
| or | 或者 |
| and | 并且 |
我們來看一下多個條件的例子：
| 類型 | 詳細內(nèi)容 |
|---|---|
| 示例 | 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 |
| ?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)
多字段排序
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進行升序排序 |
* 注：如果第一個字段已經(jīng)將結果給排好。第二個字段排序字段不生效。本例中，第二個字段無效。*
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 關鍵字結果集數(shù)量限制。
| 類別 | 詳細解示 |
|---|---|
| 基本語法 | select 字段 from 表 ?limit 數(shù)量; |
| 示例 | 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 | 黃曉明 ? ?| ?150.86 | 山東 ? ? ?| ?40 | ? 1 |
| ?4 | 井柏然 ? ?| ? ? 810 | 遼寧 ? ? ?| ?27 | ? 1 |
| ?5 | 李冰冰 ? ?| ? 20.15 | 黑龍江 ? ?| ?43 | ? 0 |
+----+-----------+---------+-----------+-----+-----+
5 rows in set (0.00 sec)
限制結果集并排序
| 類別 | 詳細解示 |
|---|---|
| 基本語法 | select 字段 from 表 order by 字段 關鍵詞 limit 數(shù)量 |
| 示例 | 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 | 范冰冰 ? ?| ?260.23 |
+----+-----------+---------+
5 rows in set (0.00 sec)
結果集區(qū)間選擇
假設我從第0條開始取了3條記錄。又想再從第3條開始取3條記錄。再想從第6條開始取4條記錄怎么辦？
這時候就需要使用到結果集區(qū)間選擇。
| 類別 | 詳細解示 |
|---|---|
| 基本語法 | select 字段 from 表 ?limit 偏移量,數(shù)量 |
| 示例 | 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
依此類推... ...
統(tǒng)計類函數(shù)使用
- 如果我們想知道總用戶數(shù)怎么辦？
- 查詢誰是數(shù)據(jù)表里的首富怎么辦？
- 如果我們想知道用戶的平均金額怎么辦？
- 如果我們想知道所有用戶的總金額怎么辦？
統(tǒng)計類函數(shù)最常用的我們有四個：
| 函數(shù) | 說明 |
|---|---|
| sum | 求和 |
| count | 統(tǒng)計總數(shù) |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
注：當然你知道其他的mysql函數(shù)也可以使用。不過，在實際工作中，大公司的很多大中型項上很少使用，他們都有專門的計數(shù)服務器。因為，mysql的計算量本身很大，為了減少壓力通常我們將實際的計算任務交給業(yè)務服務器或其他服務器來完成。
| 類別 | 詳細解示 |
|---|---|
| 基本語法 | select 函數(shù)(字段) from 表 |
| 示例 | select count(id) from money |
| 示例說明 | 查詢money表的id總數(shù) |
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
我們拿金額表里面的省份進行分組數(shù)據(jù)，分組數(shù)據(jù)后你會發(fā)現(xiàn)。有相同的省份會去掉。即，一個省份為一個組。
| 類別 | 詳細解示 |
|---|---|
| 基本語法 | select * from 表 group by 字段 |
| 示例 | select * from money group by province; |
| 示例說明 | 按照地區(qū)進行分組 |
mysql> select * from money group by province;
+----+-----------+---------+-----------+-----+-----+
| id | username ?| balance | province ?| age | sex |
+----+-----------+---------+-----------+-----+-----+
| ?7 | 楊冪 ? ? ?| ? ? 123 | 北京 ? ? ?| ?30 | ? 0 |
| 12 | 郭德綱 ? ?| ? ? 212 | 天津 ? ? ?| ?43 | ? 1 |
| ?2 | 范冰冰 ? ?| ?260.23 | 山東 ? ? ?| ?40 | ? 0 |
| ?1 | 王寶強 ? ?| ?120.02 | 湖北 ? ? ?| ?29 | ? 1 |
| ?9 | 柳巖 ? ? ?| ? ?23.4 | 湖南 ? ? ?| ?36 | ? 0 |
| ?4 | 井柏然 ? ?| ? ? 810 | 遼寧 ? ? ?| ?27 | ? 1 |
| ?5 | 李冰冰 ? ?| ? 20.15 | 黑龍江 ? ?| ?43 | ? 0 |
+----+-----------+---------+-----------+-----+-----+
統(tǒng)計分組（分類）各總數(shù)：
mysql> select deptno, count(1) from emp group by deptno;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| ? ? ?1 | ? ? ? ?1 |
| ? ? ?2 | ? ? ? ?5 |
| ? ? ?3 | ? ? ? ?1 |
| ? ? ?5 | ? ? ? ?4 |
+--------+----------+
4 rows in set (0.04 sec)
統(tǒng)計省份數(shù)量后再進行分組顯示
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)
在分組基礎上進行統(tǒng)計
with rollup用的很少。這個知識點設置為了解級別。
它的主要功能是對于分組的數(shù)據(jù)進行統(tǒng)計后，再進行一次總數(shù)統(tǒng)計。
| 類別 | 詳細解示 |
|---|---|
| 基本語法 | select * from 表 group by 字段 with rollup |
| 示例 | select count(province),province ?from money group by province with rollup; |
| 示例說明 | 對分組的數(shù)再次進行統(tǒng)計 |
在上面的基礎上統(tǒng)計總數(shù)，下例結果中，最后多了一個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有相似之處但也有區(qū)別,都是設定條件的語句。
having 是篩選組 而where是篩選記錄。
| 類別 | 詳細解示 |
|---|---|
| 基本語法 | select * from 表 group by 字段 having 條件 |
| 示例 | select count(province) as result ,province ?from money group by province having result >2; |
| 示例說明 | 對地區(qū)分組并統(tǒng)計總數(shù)，將分組結果中大于2的分組地區(qū)顯示出來 |
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
我們在上面的語句中都是單一使用的某些語句，沒有整體使用過。
我們現(xiàn)在將語句進行整合后，配合使用一次。整體的SQL語句配合使用的語法結構如下：
SELECT
? ? ?[字段1 [as 別名1],[函數(shù)(字段2) ,]......字段n]
?FROM 表名
?[WHERE where條件]
?[GROUP BY 字段]
?[HAVING where_contition]
?[order 條件]
?[limit 條件]
注：上面的語句中可以[] 代表可選。
最終的語法總結如下：
| 關鍵詞 | 說明 |
|---|---|
| select | 選擇的列 |
| from | 表 |
| where | 查詢的條件 |
| group by | 分組屬性 ?having 分組過濾的條件 |
| order by | 排序?qū)傩?/td> |
| limit | 起始記錄位置，取記錄的條數(shù) |
我們進行一次整體的給合使用，查詢money表字段：id,username,balance,province 要求id>1 余額大于50，使用地區(qū)進行分組。我們使用用戶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)
