查詢記錄
查詢記錄
在講解查詢前，我為大家準(zhǔn)備了一個數(shù)據(jù)表。這個表中存放著銀行的余額和用戶的基本信息。
我們定義了一個表結(jié)構(gòu)，表名為money。
創(chuàng)建表的語句如下：
CREATE TABLE money ( id INT NOT NULL AUTO_INCREMENT , username VARCHAR(50) NOT NULL , balance FLOAT NOT NULL , province VARCHAR(20) NOT NULL , age TINYINT UNSIGNED NOT NULL , sex TINYINT NOT NULL , PRIMARY KEY (id(10)) ) ENGINE = InnoDB CHARACTER SET utf8;
表結(jié)構(gòu)和數(shù)據(jù)展示如下：
注：
balance 是指余額
province 是指省份
基礎(chǔ)查詢
注：”*” 是一種正則表達(dá)式的寫法，表示匹配所有，上面的查詢語句和下面的是等價(jià)：
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)
指定字段查詢
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)
查詢單個字段不重復(fù)記錄 distinct
mysql> select distinct age deptno from money; +--------+ | deptno | +--------+ | 29 | | 40 | | 27 | | 43 | | 63 | | 30 | | 36 | | 44 | +--------+ 8 rows in set (0.00 sec)
條件查詢 where
? ?
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后可接的條件
比較運(yùn)算符?結(jié)果集中將符合條件的記錄列出來。上面的例子中，where 后面的添加的是一個字段的 ‘=’。
除此之外，還可以使用>、<、>=、<=、!=等比較運(yùn)算符；
邏輯運(yùn)算符
多個條件還可以使用 or 、 and 等邏輯運(yùn)算符進(jìn)行多條件聯(lián)合查詢
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)
結(jié)果集排序
?
在 select 出來之后的結(jié)果集中排序使用 order by ，其中 desc 和 asc 是排序順序中的關(guān)鍵字。desc 表示按照字段進(jìn)行降序排列，asc 表示升序排列，如果不寫關(guān)鍵字默認(rèn)升序排列。
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 后面可以跟多個不同的字段排序，并且排序字段的不同結(jié)果集的順序也不同，如果排序字段的值一樣，則值相同的字段按照第二個排序字段進(jìn)行排序。
? ?
* 注：如果第一個字段已經(jīng)將結(jié)果給排好。第二個字段排序字段不生效。本例中，第二個字段無效。*
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)
結(jié)果集限制
對于查詢或者排序后的結(jié)果集，如果希望只顯示一部分而不是全部，使用 limit 關(guān)鍵字結(jié)果集數(shù)量限制。
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)
限制結(jié)果集并排序
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)
結(jié)果集區(qū)間選擇
假設(shè)我從第0條開始取了3條記錄。又想再從第3條開始取3條記錄。再想從第6條開始取4條記錄怎么辦？
這時候就需要使用到結(jié)果集區(qū)間選擇。
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)計(jì)類函數(shù)使用
1.??? 如果我們想知道總用戶數(shù)怎么辦？
2.??? 查詢誰是數(shù)據(jù)表里的首富怎么辦？
3.??? 如果我們想知道用戶的平均金額怎么辦？
4.??? 如果我們想知道所有用戶的總金額怎么辦？
統(tǒng)計(jì)類函數(shù)最常用的我們有四個：
? ?
mysql> select count(id) from money; +-----------+ | count(id) | +-----------+ | 12 | +-----------+ 1 row in set (0.00 sec)
你還可以給字段取別名喲！使用as關(guān)鍵字。
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
我們拿金額表里面的省份進(jìn)行分組數(shù)據(jù)，分組數(shù)據(jù)后你會發(fā)現(xiàn)。有相同的省份會去掉。即，一個省份為一個組。
? ?
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)計(jì)分組（分類）各總數(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)計(jì)省份數(shù)量后再進(jìn)行分組顯示
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)
在分組基礎(chǔ)上進(jìn)行統(tǒng)計(jì)
with rollup用的很少。這個知識點(diǎn)設(shè)置為了解級別。
它的主要功能是對于分組的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)后，再進(jìn)行一次總數(shù)統(tǒng)計(jì)。
在上面的基礎(chǔ)上統(tǒng)計(jì)總數(shù)，下例結(jié)果中，最后多了一個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)
結(jié)果再過濾having
having子句與where有相似之處但也有區(qū)別,都是設(shè)定條件的語句。
having 是篩選組 而where是篩選記錄。
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)在將語句進(jìn)行整合后，配合使用一次。整體的SQL語句配合使用的語法結(jié)構(gòu)如下：
SELECT?
[字段1 [as 別名1],[函數(shù)(字段2) ,]......字段n]?
FROM 表名
[WHERE where條件]
[GROUP BY 字段]
[HAVING where_contition]
[order 條件]
[limit 條件]
注：上面的語句中可以[] 代表可選。
最終的語法總結(jié)如下：
我們進(jìn)行一次整體的給合使用，查詢money表字段：id,username,balance,province 要求id>1 余額大于50，使用地區(qū)進(jìn)行分組。我們使用用戶id進(jìn)行降序，要求只準(zhǔn)顯示3條。
最后將SQL語句寫成，查詢出來的結(jié)果如下：
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)
