MySQL Cookbook 学习笔记-02
来源：互联网 发布：网络机房装修招聘 编辑：程序博客网 时间：2024/04/30 22:04
1、分组后查找最大或最小值
2、根据“日期-时间”分组
3、“分组计算” 和 “全局计算” 同时存在查询中
4、删除一行数据，sequence 列会重新生成吗？
5、sequence 列指定值插入，不是我认为的不能指定值哦！
6、删除最大 sequence 行，对以后插入新行 sequence 值的影响
7、获取序列的值
8、设定序列的初始值
9、创建多重序列
10、使用序列产生计数器
______________________________________________________________
1、分组后查找最大或最小值
（Recipe 8.14. Finding Smallest or Largest Summary Values）
表和数据：
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() 的比较范围是指定的范围（如：全表，各组内，where指定范围），但却不能比较各组值的大小！
全表：
mysql> select min(miles), max(miles) from driver_log;+------------+------------+| min(miles) | max(miles) |+------------+------------+| 79 | 502 |+------------+------------+
各组内：
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 |+------------+------------+
替代办法：对分组后的值排序，取出第一个，即为最大或最小（有缺陷哦~~~如果最大或最小存在多个就不对了）
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 |+-------+-------------+当最大、最小值存在重复时的处理办法：
查询数据如下
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、根据“日期-时间”分组
（Recipe 8.15. Date-Based Summaries）
示例--根据 { 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 |+-------------+---------------+-------------+示例--根据 { 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、“分组计算” 和 “全局计算” 同时存在查询中
（Recipe 8.16. Working with Per-Group and Overall Summary Values Simultaneously）
示例--将两个查询分开
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）
( 前两个查询用来演示 )
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、删除一行数据，sequence 列会重新生成吗？
答案：不会！
演示如下：
+----+-------------------+------------+------------+| 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;结果：
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 列指定值插入，不是我认为的不能指定值哦！
a、id 重复，报错
b、id 不重复，可以插入！
报错演示：
mysql> insert into insect (id, name, date, origin) values (1,'test','2011-08-25','test');ERROR 1062 (23000): Duplicate entry '1' for key 1id 不重复演示——插入比当前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 不重复演示——补上被删除的行（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 行，对以后插入新行 sequence 值的影响
（Recipe 11.3. The Effect of Row Deletions on Sequence Generation）
a、DBD engine，下一个序列值是当前最大序列值加1（例如删掉 id 为20，下次插入即为9）
b、MyISAM or InnoDB engine，永远不重复（例如删掉 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() 能保证在多用户多线程操作下获取的序列值是正确的（即，当前connection 最后插入行的序列值），因为它和底层的connection 关联。Client-side:
//StatementStatement 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 ();//PreparedStatementPreparedStatement 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 ();推荐策略：如果后面需要序列值，则应在插入数据后即获取序列值保存着，以备后用！8、设定序列的初始值
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、创建多重序列
（Recipe 11.11. Using an AUTO_INCREMENT Column to Create Multiple Sequences）
注意：只适合 engine 为 MyISAM 和 BDB
创建时注意指定表的 engine，因为我的默认为 InnoDB
DROP TABLE IF EXISTS bug;CREATE TABLE bug( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(30) NOT NULL, # type of bug date DATE NOT NULL, # date collected origin VARCHAR(30) NOT NULL, # where collected PRIMARY 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 列必须是定义索引的最后一个（如：PRIMARY KEY (name, id)），主键是强制索引
b、PRIMARY KEY 不允许包含 NULL 列，如果非 ATUO_INCREMENT 列允许NULL，则应该用 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 |+----------+--------+---------+------------+或些一个存储过程，调用存储过程
CREATE FUNCTION houseware_id(category VARCHAR(3), serial INT UNSIGNED, country VARCHAR(2))RETURNS VARCHAR(10) DETERMINISTICRETURN 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、使用序列产生计数器
（Recipe 11.14. Using Sequence Generators as Counters）
CREATE TABLE booksales( title VARCHAR(60) NOT NULL, # book title copies INT UNSIGNED NOT NULL, # number of copies sold PRIMARY KEY (title));a、一般的做法：初始化设置为0，以后更新时加 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执行了更新，这样用户A 查询的数据就错误了！
修改：使用LAST_INSERT_ID(expr) 函数，保证数据一致性
INSERT INTO booksales (title,copies) VALUES('The Greater Trumps',LAST_INSERT_ID(1)) ON DUPLICATE KEY UPDATE copies = LAST_INSERT_ID(copies+1);这样每次都执行该语句，第一次是插入操作，以后是更新操作。获取插入/更新后的值：
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 ();- MySQL Cookbook 学习笔记-02
- MySQL CookBook 学习笔记-01
- MySQL Cookbook 学习笔记-03
- MySQL Cookbook 学习笔记-04
- MySQL CookBook 学习笔记-01
- cookbook学习笔记一
- Sql cookbook学习笔记
- Sql cookbook学习笔记
- 《Apache Cookbook》学习笔记
- R COOKBOOK 学习笔记
- opencv3 cookbook 学习笔记
- TensorFlow cookbook学习笔记
- MySQL CookBook 学习笔记-07排序查询结果
- MySQL CookBook 学习笔记-12使用多重表
- NHibernate Cookbook 学习笔记 5
- NHibernate Cookbook 学习笔记 6
- 《iOS5 programming cookbook》学习笔记
- Python cookbook 学习笔记一
- OpenGL学习笔记：像素
- Mysql 学习-1
- OpenGL学习笔记：显示列表
- OpenGL学习笔记：测试
- OpenGL学习笔记：动画基本概念
- MySQL Cookbook 学习笔记-02
- CXF与Spring集成，方便的调用WebService
- sudo命令使用详解
- MSDN]完成端口 CreateIoCompletionPort
- MapXtreme for java 破解方法
- 常用链接
- 交流
- 解题笔记（13）——几个链表的问题
- 同属开源Linux 移动市场MeeGo独到之秘
