1、分組后查找最大或最小值
2、根據(jù)“日期-時(shí)間”分組
3、“分組計(jì)算” 和 “全局計(jì)算” 同時(shí)存在查詢中
4、刪除一行數(shù)據(jù)，sequence 列會(huì)重新生成嗎？
5、sequence 列指定值插入，不是我認(rèn)為的不能指定值哦！
6、刪除最大 sequence 行，對(duì)以后插入新行 sequence 值的影響
7、獲取序列的值
8、設(shè)定序列的初始值
9、創(chuàng)建多重序列
10、使用序列產(chǎn)生計(jì)數(shù)器

______________________________________________________________

1、分組后查找最大或最小值

（Recipe 8.14. Finding Smallest or Largest Summary Values）

表和數(shù)據(jù)：

mysql> select * from driver_log; +--------+-------+------------+-------+ | rec_id | name | trav_date | miles | +--------+-------+------------+-------+ | 1 | Ben | 2006-08-30 | 152 | | 2 | Suzi | 2006-08-29 | 391 | | 3 | Henry | 2006-08-29 | 300 | | 4 | Henry | 2006-08-27 | 96 | | 5 | Ben | 2006-08-29 | 131 | | 6 | Henry | 2006-08-26 | 115 | | 7 | Suzi | 2006-09-02 | 502 | | 8 | Henry | 2006-09-01 | 197 | | 9 | Ben | 2006-09-02 | 79 | | 10 | Henry | 2006-08-30 | 203 | +--------+-------+------------+-------+

MIN()，MAX() 的比較范圍是指定的范圍（如：全表，各組內(nèi)，where指定范圍），但卻不能比較各組值的大小！

全表：

mysql> select min(miles), max(miles) from driver_log; +------------+------------+ | min(miles) | max(miles) | +------------+------------+ | 79 | 502 | +------------+------------+

各組內(nèi)：

mysql> select name, min(miles) ,max(miles) from driver_log-> group by name; +-------+------------+------------+ | name | min(miles) | max(miles) | +-------+------------+------------+ | Ben | 79 | 152 | | Henry | 96 | 300 | | Suzi | 391 | 502 | +-------+------------+------------+

where指定范圍：

mysql> select min(miles), max(miles) from driver_log-> where trav_date < '2006-09-01'; +------------+------------+ | min(miles) | max(miles) | +------------+------------+ | 96 | 391 | +------------+------------+

替代辦法：對(duì)分組后的值排序，取出第一個(gè)，即為最大或最小（有缺陷哦~~~如果最大或最小存在多個(gè)就不對(duì)了）

mysql> select name , sum(miles) as total_miles-> from driver_log-> group by name-> order by total_miles desc; +-------+-------------+ | name | total_miles | +-------+-------------+ | Henry | 911 | | Suzi | 893 | | Ben | 362 | +-------+-------------+取各組最大值：
mysql> select name , sum(miles) as total_miles-> from driver_log-> group by name-> order by total_miles desc limit 1; +-------+-------------+ | name | total_miles | +-------+-------------+ | Henry | 911 | +-------+-------------+當(dāng)最大、最小值存在重復(fù)時(shí)的處理辦法：

查詢數(shù)據(jù)如下

mysql> select left(name,1) as letter, count(*) as count-> from states-> group by letter-> order by count desc; +--------+-------+ | letter | count | +--------+-------+ | M | 8 | | N | 8 | | I | 4 | | A | 4 | | W | 4 | | O | 3 | | C | 3 | | K | 2 | | S | 2 | | T | 2 | | V | 2 | | L | 1 | | P | 1 | | R | 1 | | U | 1 | | D | 1 | | F | 1 | | G | 1 | | H | 1 | +--------+-------+取出各組最大的記錄

方式1：

mysql> set @max = (select count(*) from states-> group by left(name,1) order by count(*) desc limit 1);mysql> select left(name,1) as letter, count(*) as count from states-> group by letter having count = @max; +--------+-------+ | letter | count | +--------+-------+ | M | 8 | | N | 8 | +--------+-------+方式2：

mysql> select left(name,1) as letter ,count(*) as count from states-> group by letter having count =-> ( select count(*) from states-> group by left(name,1) order by count(*) desc limit 1); +--------+-------+ | letter | count | +--------+-------+ | M | 8 | | N | 8 | +--------+-------+

2、根據(jù)“日期-時(shí)間”分組

（Recipe 8.15. Date-Based Summaries）

示例--根據(jù) { year-month }分組

mysql> select concat(year(trav_date), '_' , month(trav_date)) as month_group,-> count(*) as month_drivers,-> sum(miles) as month_miles-> from driver_log group by year(trav_date), month(trav_date); +-------------+---------------+-------------+ | month_group | month_drivers | month_miles | +-------------+---------------+-------------+ | 2006_8 | 7 | 1388 | | 2006_9 | 3 | 778 | +-------------+---------------+-------------+示例--根據(jù) { year-week }分組

mysql> select yearweek(trav_date) as week_group,-> count(*) as month_drivers,-> sum(miles) as month_miles-> from driver_log group by week_group; +------------+---------------+-------------+ | week_group | month_drivers | month_miles | +------------+---------------+-------------+ | 200634 | 1 | 115 | | 200635 | 9 | 2051 | +------------+---------------+-------------+

3、“分組計(jì)算” 和 “全局計(jì)算” 同時(shí)存在查詢中

（Recipe 8.16. Working with Per-Group and Overall Summary Values Simultaneously）

示例--將兩個(gè)查詢分開

mysql> select @total := sum(miles) as total_miles from driver_log; +-------------+ | total_miles | +-------------+ | 2166 | +-------------+mysql> select name,-> sum(miles) as 'miles/driver',-> (sum(miles)*100)/@total as 'percent of total miles'-> from driver_log group by name; +-------+--------------+------------------------+ | name | miles/driver | percent of total miles | +-------+--------------+------------------------+ | Ben | 362 | 16.7128 | | Henry | 911 | 42.0591 | | Suzi | 893 | 41.2281 | +-------+--------------+------------------------+示例--合并

mysql> select name,-> sum(miles) as 'miles/driver',-> (sum(miles)*100)/(select sum(miles) from driver_log) as 'total miles'-> from driver_log group by name; +-------+--------------+-------------+ | name | miles/driver | total miles | +-------+--------------+-------------+ | Ben | 362 | 16.7128 | | Henry | 911 | 42.0591 | | Suzi | 893 | 41.2281 | +-------+--------------+-------------+示例--帶條件判斷的（having xxx）

( 前兩個(gè)查詢用來演示 )

mysql> select avg(miles) from driver_log; +------------+ | avg(miles) | +------------+ | 216.6000 | +------------+mysql> select name, avg(miles) as driver_avg from driver_log-> group by name; +-------+------------+ | name | driver_avg | +-------+------------+ | Ben | 120.6667 | | Henry | 182.2000 | | Suzi | 446.5000 | +-------+------------+mysql> select name, avg(miles) as driver_avg from driver_log-> group by name-> having driver_avg < (select avg(miles) from driver_log); +-------+------------+ | name | driver_avg | +-------+------------+ | Ben | 120.6667 | | Henry | 182.2000 | +-------+------------+

4、刪除一行數(shù)據(jù)，sequence 列會(huì)重新生成嗎？

答案：不會(huì)！

演示如下：

+----+-------------------+------------+------------+ | id | name | date | origin | +----+-------------------+------------+------------+ | 1 | housefly | 2006-09-10 | kitchen | | 2 | millipede | 2006-09-10 | driveway | | 3 | grasshopper | 2006-09-10 | front yard | | 4 | stink bug | 2006-09-10 | front yard | | 5 | cabbage butterfly | 2006-09-10 | garden | | 6 | ant | 2006-09-10 | back yard | | 7 | ant | 2006-09-10 | back yard | | 8 | millbug | 2006-09-10 | under rock | +----+-------------------+------------+------------+刪除 id 為2的行：

mysql> delete from insect where id=2;結(jié)果：

mysql> select * from insect; +----+-------------------+------------+------------+ | id | name | date | origin | +----+-------------------+------------+------------+ | 1 | housefly | 2006-09-10 | kitchen | | 3 | grasshopper | 2006-09-10 | front yard | | 4 | stink bug | 2006-09-10 | front yard | | 5 | cabbage butterfly | 2006-09-10 | garden | | 6 | ant | 2006-09-10 | back yard | | 7 | ant | 2006-09-10 | back yard | | 8 | millbug | 2006-09-10 | under rock | +----+-------------------+------------+------------+

5、sequence 列指定值插入，不是我認(rèn)為的不能指定值哦！

a、id 重復(fù)，報(bào)錯(cuò)

b、id 不重復(fù)，可以插入！

報(bào)錯(cuò)演示：

mysql> insert into insect (id, name, date, origin) values (1,'test','2011-08-25','test'); ERROR 1062 (23000): Duplicate entry '1' for key 1id 不重復(fù)演示——插入比當(dāng)前sequence 值最大還大的值

mysql> insert into insect (id, name, date, origin) values (20,'test','2011-08-25','test'); Query OK, 1 row affected (0.03 sec)mysql> select * from insect; +----+-------------------+------------+------------+ | id | name | date | origin | +----+-------------------+------------+------------+ | 1 | housefly | 2006-09-10 | kitchen | | 3 | grasshopper | 2006-09-10 | front yard | | 4 | stink bug | 2006-09-10 | front yard | | 5 | cabbage butterfly | 2006-09-10 | garden | | 6 | ant | 2006-09-10 | back yard | | 7 | ant | 2006-09-10 | back yard | | 8 | millbug | 2006-09-10 | under rock | | 20 | test | 2011-08-25 | test | +----+-------------------+------------+------------+id 不重復(fù)演示——補(bǔ)上被刪除的行（id 為2）

mysql> insert into insect (id, name, date, origin) values (2,'test','2011-08-25','test'); Query OK, 1 row affected (0.01 sec)mysql> select * from insect; +----+-------------------+------------+------------+ | id | name | date | origin | +----+-------------------+------------+------------+ | 1 | housefly | 2006-09-10 | kitchen | | 2 | test | 2011-08-25 | test | | 3 | grasshopper | 2006-09-10 | front yard | | 4 | stink bug | 2006-09-10 | front yard | | 5 | cabbage butterfly | 2006-09-10 | garden | | 6 | ant | 2006-09-10 | back yard | | 7 | ant | 2006-09-10 | back yard | | 8 | millbug | 2006-09-10 | under rock | | 20 | test | 2011-08-25 | test | +----+-------------------+------------+------------+

6、刪除最大 sequence 行，對(duì)以后插入新行 sequence 值的影響

（Recipe 11.3. The Effect of Row Deletions on Sequence Generation）

a、DBD engine，下一個(gè)序列值是當(dāng)前最大序列值加1（例如刪掉 id 為20，下次插入即為9）

b、MyISAM or InnoDB ?engine，永遠(yuǎn)不重復(fù)（例如刪掉 id 為20，下次插入即為21）

查看表的 engine——示例

mysql> select engine from information_schema.tables-> where table_schema = 'cookbook' and table_name = 'insect'; +--------+ | engine | +--------+ | InnoDB | +--------+

7、獲取序列的值

（Recipe 11.4. Retrieving Sequence Values）

Server-side—— select last_insert_id()示例

mysql> insert into insect(name,date,origin)-> values('testLastInsert', '2011-08-25', 'testLastInsert'); Query OK, 1 row affected (0.03 sec)mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 21 | +------------------+ 1 row in set (0.00 sec)mysql> select * from insect where id=last_insert_id(); +----+----------------+------------+----------------+ | id | name | date | origin | +----+----------------+------------+----------------+ | 21 | testLastInsert | 2011-08-25 | testLastInsert | +----+----------------+------------+----------------+LAST_INSERT_ID() 能保證在多用戶多線程操作下獲取的序列值是正確的（即，當(dāng)前connection 最后插入行的序列值），因?yàn)樗偷讓拥腸onnection 關(guān)聯(lián)。

Client-side:

//Statement Statement s = conn.createStatement (); s.executeUpdate ("INSERT INTO insect (name,date,origin) VALUES('moth','2006-09-14','windowsill')"); long seq = ((com.mysql.jdbc.Statement) s).getLastInsertID (); s.close ();//PreparedStatement PreparedStatement s = conn.prepareStatement ("INSERT INTO insect (name,date,origin) VALUES('moth','2006-09-14','windowsill')"); s.executeUpdate (); long seq = ((com.mysql.jdbc.PreparedStatement) s).getLastInsertID (); s.close ();推薦策略：如果后面需要序列值，則應(yīng)在插入數(shù)據(jù)后即獲取序列值保存著，以備后用！

8、設(shè)定序列的初始值

For MyISAM or InnoDB tables

mysql> CREATE TABLE t-> (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id))-> AUTO_INCREMENT = 100; mysql> INSERT INTO t (id) VALUES(NULL); mysql> INSERT INTO t (id) VALUES(NULL); mysql> INSERT INTO t (id) VALUES(NULL); mysql> SELECT id FROM t ORDER BY id; +-----+ | id | +-----+ | 100 | | 101 | | 102 | +-----+或

mysql> CREATE TABLE t-> (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)); mysql> ALTER TABLE t AUTO_INCREMENT = 100; mysql> INSERT INTO t (id) VALUES(NULL); mysql> INSERT INTO t (id) VALUES(NULL); mysql> INSERT INTO t (id) VALUES(NULL); mysql> SELECT id FROM t ORDER BY id; +-----+ | id | +-----+ | 100 | | 101 | | 102 | +-----+To start a sequence at n for storage engines other than MyISAM or InnoDB, you can use a trick: insert a "fake" row with sequence value n -1, and then delete it after inserting one or more "real" rows. The following example illustrates how to start a sequence at 100 for a BDB table:

mysql> CREATE TABLE t-> (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id))-> ENGINE = BDB; mysql> INSERT INTO t (id) VALUES(99); mysql> INSERT INTO t (id) VALUES(NULL); mysql> INSERT INTO t (id) VALUES(NULL); mysql> INSERT INTO t (id) VALUES(NULL); mysql> DELETE FROM t WHERE id = 99; mysql> SELECT * FROM t ORDER BY id; +-----+ | id | +-----+ | 100 | | 101 | | 102 | +-----+

9、創(chuàng)建多重序列

（Recipe 11.11. Using an AUTO_INCREMENT Column to Create Multiple Sequences）

注意：只適合 engine 為 MyISAM 和 BDB

創(chuàng)建時(shí)注意指定表的 engine，因?yàn)槲业哪J(rèn)為 InnoDB

DROP TABLE IF EXISTS bug;CREATE TABLE bug (id INT UNSIGNED NOT NULL AUTO_INCREMENT,name VARCHAR(30) NOT NULL, # type of bugdate DATE NOT NULL, # date collectedorigin VARCHAR(30) NOT NULL, # where collectedPRIMARY KEY (name, id) )engine=myisam;INSERT INTO bug (name,date,origin) VALUES ('ant','2006-10-07','kitchen'), ('millipede','2006-10-07','basement'), ('beetle','2006-10-07','basement'), ('ant','2006-10-07','front yard'), ('ant','2006-10-07','front yard'), ('honeybee','2006-10-08','back yard'), ('cricket','2006-10-08','garage'), ('beetle','2006-10-08','front yard'), ('termite','2006-10-09','kitchen woodwork'), ('cricket','2006-10-10','basement'), ('termite','2006-10-11','bathroom woodwork'), ('honeybee','2006-10-11','garden'), ('cricket','2006-10-11','garden'), ('ant','2006-10-11','garden') ;限制：

a、AUTO_INCREMENT 列必須是定義索引的最后一個(gè)（如：PRIMARY ?KEY ?(name, id)），主鍵是強(qiáng)制索引

b、PRIMARY KEY 不允許包含 NULL 列，如果非 ATUO_INCREMENT 列允許NULL，則應(yīng)該用 UNIQUE 索引

mysql> SELECT * FROM bug ORDER BY name, id; +----+-----------+------------+-------------------+ | id | name | date | origin | +----+-----------+------------+-------------------+ | 1 | ant | 2006-10-07 | kitchen | | 2 | ant | 2006-10-07 | front yard | | 3 | ant | 2006-10-07 | front yard | | 4 | ant | 2006-10-11 | garden | | 1 | beetle | 2006-10-07 | basement | | 2 | beetle | 2006-10-08 | front yard | | 1 | cricket | 2006-10-08 | garage | | 2 | cricket | 2006-10-10 | basement | | 3 | cricket | 2006-10-11 | garden | | 1 | honeybee | 2006-10-08 | back yard | | 2 | honeybee | 2006-10-11 | garden | | 1 | millipede | 2006-10-07 | basement | | 1 | termite | 2006-10-09 | kitchen woodwork | | 2 | termite | 2006-10-11 | bathroom woodwork | +----+-----------+------------+-------------------+其他示例：

CREATE TABLE housewares (category VARCHAR(3) NOT NULL,serial INT UNSIGNED NOT NULL AUTO_INCREMENT,country VARCHAR(2) NOT NULL,description VARCHAR(255),PRIMARY KEY (category, country, serial) );或

mysql> ALTER TABLE housewares-> ADD category VARCHAR(3) NOT NULL FIRST,-> ADD serial INT UNSIGNED NOT NULL AUTO_INCREMENT AFTER category,-> ADD country VARCHAR(2) NOT NULL AFTER serial,-> ADD PRIMARY KEY (category, country, serial); mysql> UPDATE housewares SET category = LEFT(id,3); mysql> UPDATE housewares SET serial = MID(id,4,5); mysql> UPDATE housewares SET country = RIGHT(id,2); mysql> ALTER TABLE housewares DROP id; mysql> SELECT * FROM housewares; +----------+--------+---------+------------------+ | category | serial | country | description | +----------+--------+---------+------------------+ | DIN | 40672 | US | dining table | | KIT | 372 | UK | garbage disposal | | KIT | 1729 | JP | microwave oven | | BED | 38 | SG | bedside lamp | | BTH | 485 | US | shower stall | | BTH | 415 | JP | lavatory | +----------+--------+---------+------------------+指定排序

mysql> SELECT category, serial, country,-> CONCAT(category,LPAD(serial,5,'0'),country) AS id-> FROM housewares ORDER BY category, country, serial; +----------+--------+---------+------------+ | category | serial | country | id | +----------+--------+---------+------------+ | BED | 38 | SG | BED00038SG | | BTH | 415 | JP | BTH00415JP | | BTH | 485 | US | BTH00485US | | DIN | 40672 | US | DIN40672US | | KIT | 1729 | JP | KIT01729JP | | KIT | 372 | UK | KIT00372UK | +----------+--------+---------+------------+或修改 serial 的寬度為5，然后再直接連接排序

mysql> ALTER TABLE housewares-> MODIFY serial INT(5) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT; mysql> SELECT category, serial, country,-> CONCAT(category,serial,country) AS id-> FROM housewares ORDER BY category, country, serial; +----------+--------+---------+------------+ | category | serial | country | id | +----------+--------+---------+------------+ | BED | 00038 | SG | BED00038SG | | BTH | 00415 | JP | BTH00415JP | | BTH | 00485 | US | BTH00485US | | DIN | 40672 | US | DIN40672US | | KIT | 01729 | JP | KIT01729JP | | KIT | 00372 | UK | KIT00372UK | +----------+--------+---------+------------+或些一個(gè)存儲(chǔ)過程，調(diào)用存儲(chǔ)過程

CREATE FUNCTION houseware_id(category VARCHAR(3),serial INT UNSIGNED,country VARCHAR(2)) RETURNS VARCHAR(10) DETERMINISTIC RETURN CONCAT(category,LPAD(serial,5,'0'),country); mysql> SELECT category, serial, country,-> houseware_id(category,serial,country) AS id-> FROM housewares; +----------+--------+---------+------------+ | category | serial | country | id | +----------+--------+---------+------------+ | BED | 38 | SG | BED00038SG | | BTH | 415 | JP | BTH00415JP | | BTH | 485 | US | BTH00485US | | DIN | 40672 | US | DIN40672US | | KIT | 1729 | JP | KIT01729JP | | KIT | 372 | UK | KIT00372UK | +----------+--------+---------+------------+

10、使用序列產(chǎn)生計(jì)數(shù)器

（Recipe 11.14. Using Sequence Generators as Counters）

CREATE TABLE booksales (title VARCHAR(60) NOT NULL, # book titlecopies INT UNSIGNED NOT NULL, # number of copies soldPRIMARY KEY (title) );a、一般的做法：初始化設(shè)置為0，以后更新時(shí)加 1。

INSERT INTO booksales (title,copies) VALUES('The Greater Trumps',0); UPDATE booksales SET copies = copies+1 WHERE title = 'The Greater Trumps';b、或使用 INSERT ...?ON DUPLICATE KEY UPDATE 語句：

INSERT INTO booksales (title,copies)VALUES('The Greater Trumps',1)ON DUPLICATE KEY UPDATE copies = copies+1; 獲取更新的值：

SELECT copies FROM booksales WHERE title = 'The Greater Trumps';缺陷：

在多用戶多線程的情況下，用戶A 完成更新在取值查詢開始前，用戶B執(zhí)行了更新，這樣用戶A 查詢的數(shù)據(jù)就錯(cuò)誤了！
修改：使用LAST_INSERT_ID(expr) 函數(shù)，保證數(shù)據(jù)一致性

INSERT INTO booksales (title,copies)VALUES('The Greater Trumps',LAST_INSERT_ID(1))ON DUPLICATE KEY UPDATE copies = LAST_INSERT_ID(copies+1); 這樣每次都執(zhí)行該語句，第一次是插入操作，以后是更新操作。

獲取插入/更新后的值：

Server-side:

SELECT LAST_INSERT_ID(); Client-side:

Statement s = conn.createStatement (); s.executeUpdate ("INSERT INTO booksales (title,copies)"+ " VALUES('The Greater Trumps',LAST_INSERT_ID(1))"+ " ON DUPLICATE KEY UPDATE copies = LAST_INSERT_ID(copies+1)"); long count = ((com.mysql.jdbc.Statement) s).getLastInsertID (); s.close ();

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的MySQL Cookbook 学习笔记-02的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。