板凳-------Mysql cookbook学习 （十--7）
第8章：生成摘要
8.0 引言
mysql> select * from states order by name;
+----------------+--------+------------+----------+
| name | abbrev | statehood | pop |
+----------------+--------+------------+----------+
| Alabama | AL | 1819-12-14 | 5039877 |
| Alaska | AK | 1959-01-03 | 732673 |
| Arizona | AZ | 1912-02-14 | 7276316 |
| Arkansas | AR | 1836-06-15 | 3025891 |
| California | CA | 1850-09-09 | 39237836 |
| Colorado | CO | 1876-08-01 | 5812069 |
| Connecticut | CT | 1788-01-09 | 3605597 |
| Delaware | DE | 1787-12-07 | 1003384 |
| Florida | FL | 1845-03-03 | 21781128 |
| Georgia | GA | 1788-01-02 | 10799566 |
| Hawaii | HI | 1959-08-21 | 1441553 |
| Idaho | ID | 1890-07-03 | 1900923 |
| Illinois | IL | 1818-12-03 | 12671469 |
| Indiana | IN | 1816-12-11 | 6805985 |
| Iowa | IA | 1846-12-28 | 3193079 |
| Kansas | KS | 1861-01-29 | 2934582 |
| Kentucky | KY | 1792-06-01 | 4509394 |
| Louisiana | LA | 1812-04-30 | 4624047 |
| Maine | ME | 1820-03-15 | 1372247 |
| Maryland | MD | 1788-04-28 | 6165129 |
| Massachusetts | MA | 1788-02-06 | 6984723 |
| Michigan | MI | 1837-01-26 | 10050811 |
| Minnesota | MN | 1858-05-11 | 5707390 |
| Mississippi | MS | 1817-12-10 | 2949965 |
| Missouri | MO | 1821-08-10 | 6168187 |
| Montana | MT | 1889-11-08 | 1104271 |
| Nebraska | NE | 1867-03-01 | 1963692 |
| Nevada | NV | 1864-10-31 | 3143991 |
| New Hampshire | NH | 1788-06-21 | 1388992 |
| New Jersey | NJ | 1787-12-18 | 9267130 |
| New Mexico | NM | 1912-01-06 | 2115877 |
| New York | NY | 1788-07-26 | 19835913 |
| North Carolina | NC | 1789-11-21 | 10551162 |
| North Dakota | ND | 1889-11-02 | 774948 |
| Ohio | OH | 1803-03-01 | 11780017 |
| Oklahoma | OK | 1907-11-16 | 3986639 |
| Oregon | OR | 1859-02-14 | 4246155 |
| Pennsylvania | PA | 1787-12-12 | 12964056 |
| Rhode Island | RI | 1790-05-29 | 1095610 |
| South Carolina | SC | 1788-05-23 | 5190705 |
| South Dakota | SD | 1889-11-02 | 895376 |
| Tennessee | TN | 1796-06-01 | 6975218 |
| Texas | TX | 1845-12-29 | 29527941 |
| Utah | UT | 1896-01-04 | 3337975 |
| Vermont | VT | 1791-03-04 | 645570 |
| Virginia | VA | 1788-06-25 | 8642274 |
| Washington | WA | 1889-11-11 | 7738692 |
| West Virginia | WV | 1863-06-20 | 1782959 |
| Wisconsin | WI | 1848-05-29 | 5895908 |
| Wyoming | WY | 1890-07-10 | 578803 |
+----------------+--------+------------+----------+
50 rows in set (0.02 sec)
8.1 使用count函数生成摘要
mysql> select count(*) from driver_log;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.01 sec)mysql> select count(*) from states;
+----------+
| count(*) |
+----------+
| 50 |
+----------+
1 row in set (0.00 sec)mysql> select table_rows from information_schema.tables-> where table_schema='cookbook' and table_name = 'states';
+------------+
| TABLE_ROWS |
+------------+
| 50 |
+------------+
1 row in set (0.05 sec)mysql> select count(*) from driver_log where miles > 200;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)mysql> select count(*) from driver_log where name = 'Suzi';
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)mysql> select count(*) from states where statehood < '1900-01-01';
+----------+
| count(*) |
+----------+
| 45 |
+----------+
1 row in set (0.01 sec)mysql> select count(*) from states where statehood < '1900-01-01';
+----------+
| count(*) |
+----------+
| 45 |
+----------+
1 row in set (0.00 sec)mysql> select count(*) from states-> where statehood between '1800-01-01' and '1899-12-31';
+----------+
| count(*) |
+----------+
| 29 |
+----------+
1 row in set (0.00 sec)mysql> select-> count(if(dayofweek(trav_date)=7, 1, null)) as 'Saturday tripe',-> count(if(dayofweek(trav_date)=1, 1, null)) as 'Sunday trips'-> from driver_log;
+----------------+--------------+
| Saturday tripe | Sunday trips |
+----------------+--------------+
| 3 | 1 |
+----------------+--------------+
1 row in set (0.00 sec)mysql> select-> count(if(dayofweek(trav_date) in (1, 7), 1, null)) as 'weekend trips',-> count(if(dayofweek(trav_date) in (1, 7), null, 1)) as 'weekday trips'-> from driver_log;
+---------------+---------------+
| weekend trips | weekday trips |
+---------------+---------------+
| 4 | 6 |
+---------------+---------------+
1 row in set (0.00 sec)mysql> create view trip_summary_view as-> select-> count(if(dayofweek(trav_date) in(1, 7), 1, null)) as 'weekend trips',-> count(if(dayofweek(trav_date) in(1, 7), null, 1)) as 'weekday trips'-> from driver_log;
Query OK, 0 rows affected (0.03 sec)mysql> select * from trip_summary_view;
+---------------+---------------+
| weekend trips | weekday trips |
+---------------+---------------+
| 4 | 6 |
+---------------+---------------+
1 row in set (0.01 sec)
8.2 使用min()和max()函数生成摘要
mysql> select-> min(t) as earliest, max(t) as latest,-> min(size) as smallest, max(size) as largest-> from mail;
+---------------------+---------------------+----------+---------+
| earliest | latest | smallest | largest |
+---------------------+---------------------+----------+---------+
| 2006-05-11 10:15:08 | 2006-05-19 22:21:51 | 271 | 2394482 |
+---------------------+---------------------+----------+---------+
1 row in set (0.01 sec)mysql> select-> min(miles) as shortest, max(miles) as longest-> from driver_log;
+----------+---------+
| shortest | longest |
+----------+---------+
| 79 | 502 |
+----------+---------+
1 row in set (0.00 sec)mysql> select-> min(pop) as 'fewest people', max(pop) as 'most people'-> from states;
+---------------+-------------+
| fewest people | most people |
+---------------+-------------+
| 578803 | 39237836 |
+---------------+-------------+
1 row in set (0.00 sec)mysql> select min(name), max(name) from states;
+-----------+-----------+
| min(name) | max(name) |
+-----------+-----------+
| Alabama | Wyoming |
+-----------+-----------+
1 row in set (0.00 sec)mysql> select-> min(char_length(name)) as shortest,-> max(char_length(name)) as longest-> from states;
+----------+---------+
| shortest | longest |
+----------+---------+
| 4 | 14 |
+----------+---------+
1 row in set (0.00 sec)
8.3 使用sum()和avg()函数生成摘要
mysql> select-> sum(size) as 'total traffic',-> avg(size) as 'average message size'-> from mail;
+---------------+----------------------+
| total traffic | average message size |
+---------------+----------------------+
| 3798185 | 237386.5625 |
+---------------+----------------------+
1 row in set (0.00 sec)mysql> select-> sum(miles) as 'total miles',-> avg(miles) as 'average_mile/day'-> from driver_log;
+-------------+------------------+
| total miles | average_mile/day |
+-------------+------------------+
| 2166 | 216.6000 |
+-------------+------------------+
1 row in set (0.00 sec)mysql> select sum(pop) from states;
+-----------+
| sum(pop) |
+-----------+
| 331223695 |
+-----------+
1 row in set (0.00 sec)mysql> select t1 from time_val;
+----------+
| t1 |
+----------+
| 15:00:00 |
| 05:01:30 |
| 12:30:20 |
+----------+
3 rows in set (0.01 sec)mysql> select sum(time_to_sec(t1)) as 'total seconds',-> sec_to_time(sum(time_to_sec(t1))) as 'total time'-> from time_val;
+---------------+------------+
| total seconds | total time |
+---------------+------------+
| 117110 | 32:31:50 |
+---------------+------------+
1 row in set (0.01 sec)
8.4 使用distinct函数消除重复
mysql> select distinct name from driver_log order by name;
+-------+
| name |
+-------+
| Ben |
| Henry |
| Suzi |
+-------+
3 rows in set (0.00 sec)mysql> select name from driver_log;
+-------+
| name |
+-------+
| Ben |
| Suzi |
| Henry |
| Henry |
| Ben |
| Henry |
| Suzi |
| Henry |
| Ben |
| Henry |
+-------+
10 rows in set (0.00 sec)mysql> select count(distinct name) from driver_log;
+----------------------+
| count(distinct name) |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.00 sec)mysql> select distinct srcuser, dstuser from mail-> order by srcuser, dstuser;
+---------+---------+
| srcuser | dstuser |
+---------+---------+
| barb | barb |
| barb | tricia |
| gene | barb |
| gene | gene |
| gene | tricia |
| phil | barb |
| phil | phil |
| phil | tricia |
| tricia | gene |
| tricia | phil |
+---------+---------+
10 rows in set (0.00 sec)mysql> select count(distinct srcuser, dstuser) from mail;
+----------------------------------+
| count(distinct srcuser, dstuser) |
+----------------------------------+
| 10 |
+----------------------------------+
1 row in set (0.00 sec)mysql> select count(distinct hour(t)) from mail;
+-------------------------+
| count(distinct hour(t)) |
+-------------------------+
| 12 |
+-------------------------+
1 row in set (0.00 sec)mysql> select distinct hour(t) as hour from mail order by hour;
+------+
| hour |
+------+
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 17 |
| 22 |
| 23 |
+------+
12 rows in set (0.00 sec)
8.5 查找数值相关的最大值和最小值
mysql> select max(pop) from states;
+----------+
| max(pop) |
+----------+
| 39237836 |
+----------+
1 row in set (0.00 sec)mysql> select max(pop), name from states where pop = max(pop);
ERROR 1111 (HY000): Invalid use of group function
mysql> set @max=(select max(pop) from states);
Query OK, 0 rows affected (0.01 sec)mysql> select pop as 'highest population', name from states where pop = @max;
+--------------------+------------+
| highest population | name |
+--------------------+------------+
| 39237836 | California |
+--------------------+------------+
1 row in set (0.00 sec)mysql> select pop as 'highest population', name from states-> where pop = (select max(pop) from states);
+--------------------+------------+
| highest population | name |
+--------------------+------------+
| 39237836 | California |
+--------------------+------------+
1 row in set (0.00 sec)mysql> select min(char_length(vtext)) from kjv;
+-------------------------+
| min(char_length(vtext)) |
+-------------------------+
| 11 |
+-------------------------+
1 row in set (0.11 sec)mysql> select bname, cnum, vnum, vtext from kjv-> where char_length(vtext) = (select min(char_length(vtext)) from kjv);
+-------+------+------+-------------+
| bname | cnum | vnum | vtext |
+-------+------+------+-------------+
| John | 11 | 35 | Jesus wept. |
+-------+------+------+-------------+
1 row in set (0.09 sec)mysql> select * from t;
+------+
| val |
+------+
| 3 |
| 100 |
| NULL |
| NULL |
| 9 |
+------+
5 rows in set (0.00 sec)mysql> drop table if exists ts;
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> create table ts select max(pop) as maxpop from states;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> select states.* from states inner join ts on states.pop = ts.maxpop;
+------------+--------+------------+----------+
| name | abbrev | statehood | pop |
+------------+--------+------------+----------+
| California | CA | 1850-09-09 | 39237836 |
+------------+--------+------------+----------+
1 row in set (0.01 sec)
8.6 控制min()函数和max()函数的字符串大小写区分
mysql> use employees;
Database changedmysql> show create table employees;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (`emp_no` int NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` enum('M','F') NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> SELECT-> MAX(last_name COLLATE utf8mb4_bin) AS max_last_name,-> MIN(last_name COLLATE utf8mb4_bin) AS min_last_name-> FROM employees;
+---------------+---------------+
| max_last_name | min_last_name |
+---------------+---------------+
| dAstous | Aamodt |
+---------------+---------------+
1 row in set (0.31 sec)mysql> CREATE TABLE tb1 (-> col_a INT(11) DEFAULT NULL,-> col_b CHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,-> col_c CHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,-> col_d CHAR(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,-> KEY col_a (col_a),-> KEY col_b (col_b)-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected, 1 warning (0.02 sec)mysql> INSERT INTO tb1 (col_a, col_b, col_c, col_d) VALUES-> (1, 'Apple', 'Red', 'FRUIT'),-> (2, 'Banana', 'Yellow', 'FRUIT'),-> (3, 'Dog', 'Brown', 'ANIMAL'),-> (4, 'Sky', 'Blue', 'NATURE'),-> (5, 'Sun', 'Yellow', 'NATURE'),-> (6, 'Coffee', 'Black', 'DRINK'),-> (7, 'Snow', 'White', 'WEATHER'),-> (8, 'Cat', 'Gray', 'ANIMAL'),-> (9, 'Rose', 'Pink', 'FLOWER'),-> (10, 'Book', 'Green', 'OBJECT');
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0mysql> select * from tbl;
ERROR 1146 (42S02): Table 'employees.tbl' doesn't exist
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| data_quality_monitor |
| data_quality_results |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| news |
| salaries |
| t |
| tb1 |
| test |
| titles |
+----------------------+
14 rows in set (0.00 sec)mysql> SELECT * FROM tb1 ORDER BY col_a;
+-------+--------+--------+---------+
| col_a | col_b | col_c | col_d |
+-------+--------+--------+---------+
| 1 | Apple | Red | FRUIT |
| 2 | Banana | Yellow | FRUIT |
| 3 | Dog | Brown | ANIMAL |
| 4 | Sky | Blue | NATURE |
| 5 | Sun | Yellow | NATURE |
| 6 | Coffee | Black | DRINK |
| 7 | Snow | White | WEATHER |
| 8 | Cat | Gray | ANIMAL |
| 9 | Rose | Pink | FLOWER |
| 10 | Book | Green | OBJECT |
+-------+--------+--------+---------+
10 rows in set (0.00 sec)mysql> -- 不区分大小写的查询（col_b列）
mysql> SELECT * FROM tb1 WHERE col_b = 'apple';
+-------+-------+-------+-------+
| col_a | col_b | col_c | col_d |
+-------+-------+-------+-------+
| 1 | Apple | Red | FRUIT |
+-------+-------+-------+-------+
1 row in set (0.00 sec)mysql> use cookbook;
Database changed
mysql> DROP TABLE IF EXISTS tb1;
Query OK, 0 rows affected, 1 warning (0.02 sec)mysql> CREATE TABLE tb1 (-> col_a INT DEFAULT NULL,-> col_b CHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci DEFAULT NULL,-> col_c CHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci DEFAULT NULL,-> col_d CHAR(10) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,-> KEY (col_a),-> KEY (col_b)-> ) ENGINE=MyISAM DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO tb1 (col_a, col_b, col_c, col_d) VALUES-> (1, 'Apple', 'Red', 'FRUIT'),-> (2, 'Banana', 'Yellow', 'FRUIT'),-> (3, 'Dog', 'Brown', 'ANIMAL'),-> (4, 'Sky', 'Blue', 'NATURE'),-> (5, 'Sun', 'Yellow', 'NATURE'),-> (6, 'Coffee', 'Black', 'DRINK'),-> (7, 'Snow', 'White', 'WEATHER'),-> (8, 'Cat', 'Gray', 'ANIMAL'),-> (9, 'Rose', 'Pink', 'FLOWER'),-> (10, 'Book', 'Green', 'OBJECT');
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0mysql> show create table tbl;
ERROR 1146 (42S02): Table 'cookbook.tbl' doesn't exist
mysql> SELECT * FROM tb1 ORDER BY col_a;
+-------+--------+--------+---------+
| col_a | col_b | col_c | col_d |
+-------+--------+--------+---------+
| 1 | Apple | Red | FRUIT |
| 2 | Banana | Yellow | FRUIT |
| 3 | Dog | Brown | ANIMAL |
| 4 | Sky | Blue | NATURE |
| 5 | Sun | Yellow | NATURE |
| 6 | Coffee | Black | DRINK |
| 7 | Snow | White | WEATHER |
| 8 | Cat | Gray | ANIMAL |
| 9 | Rose | Pink | FLOWER |
| 10 | Book | Green | OBJECT |
+-------+--------+--------+---------+
10 rows in set (0.00 sec)mysql> SELECT-> MIN(col_b COLLATE gbk_chinese_ci) AS min_val,-> MAX(col_b COLLATE gbk_chinese_ci) AS max_val-> FROM tb1;
+---------+---------+
| min_val | max_val |
+---------+---------+
| Apple | Sun |
+---------+---------+
1 row in set (0.00 sec)mysql> SELECT-> MIN(col_b COLLATE gbk_bin) AS min_val_bin,-> MAX(col_b COLLATE gbk_bin) AS max_val_bin-> FROM tb1;
+-------------+-------------+
| min_val_bin | max_val_bin |
+-------------+-------------+
| Apple | Sun |
+-------------+-------------+
1 row in set (0.00 sec)mysql> INSERT INTO tb1 VALUES-> (11, 'apple', 'red', 'fruit'),-> (12, 'book', 'blue', 'object');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> SELECT * FROM tb1 WHERE col_b = 'Apple';
+-------+-------+-------+-------+
| col_a | col_b | col_c | col_d |
+-------+-------+-------+-------+
| 1 | Apple | Red | FRUIT |
| 11 | apple | red | fruit |
+-------+-------+-------+-------+
2 rows in set (0.00 sec)mysql> SELECT * FROM tb1 WHERE col_d = 'FRUIT' COLLATE gbk_bin;
+-------+--------+--------+-------+
| col_a | col_b | col_c | col_d |
+-------+--------+--------+-------+
| 1 | Apple | Red | FRUIT |
| 2 | Banana | Yellow | FRUIT |
+-------+--------+--------+-------+
2 rows in set (0.00 sec)mysql> EXPLAIN SELECT * FROM tb1 WHERE col_b = 'Apple';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb1 | NULL | ref | col_b | col_b | 21 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> EXPLAIN SELECT * FROM tb1 WHERE col_a = 5;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb1 | NULL | ref | col_a | col_a | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> SELECT col_a,-> CONVERT(col_b USING utf8mb4) AS col_b_utf8,-> col_d-> FROM tb1-> WHERE col_a < 5;
+-------+------------+--------+
| col_a | col_b_utf8 | col_d |
+-------+------------+--------+
| 1 | Apple | FRUIT |
| 2 | Banana | FRUIT |
| 3 | Dog | ANIMAL |
| 4 | Sky | NATURE |
+-------+------------+--------+
4 rows in set (0.00 sec)mysql> -- 测试索引边界
mysql> INSERT INTO tb1 VALUES-> (0, '0_test', 'color', 'TYPE'),-> (2147483647, 'z_test', 'color', 'TYPE'); -- INT最大值
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql>
mysql> -- 验证索引扫描
mysql> EXPLAIN SELECT * FROM tb1 WHERE col_a BETWEEN 1 AND 10;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb1 | NULL | range | col_a | col_a | 5 | NULL | 9 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)mysql> -- 混合排序规则查询
mysql> SELECT col_a, col_b-> FROM tb1-> WHERE col_b = 'apple' COLLATE gbk_bin; -- 强制区分大小写
+-------+-------+
| col_a | col_b |
+-------+-------+
| 11 | apple |
+-------+-------+
1 row in set (0.00 sec)mysql>
mysql> -- 与默认排序规则对比
mysql> SELECT col_a, col_b-> FROM tb1-> WHERE col_b = 'apple'; -- 不区分大小写
+-------+-------+
| col_a | col_b |
+-------+-------+
| 1 | Apple |
| 11 | apple |
+-------+-------+
2 rows in set (0.00 sec)mysql> -- 创建大容量测试数据（约10万行）
mysql> INSERT INTO tb1-> SELECT-> n+10,-> CONCAT('item', n),-> 'color',-> CASE WHEN n%2=0 THEN 'TYPE_A' ELSE 'TYPE_B' END-> FROM (-> SELECT a.N + b.N*10 + c.N*100 + d.N*1000 + e.N*10000 AS n-> FROM (SELECT 0 AS N UNION SELECT 1 UNION...SELECT 9) a,-> (SELECT 0 AS N UNION...SELECT 9) b,-> (SELECT 0 AS N UNION...SELECT 9) c,-> (SELECT 0 AS N UNION...SELECT 9) d,-> (SELECT 0 AS N UNION...SELECT 9) e-> LIMIT 100000-> ) t;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '...SELECT 9) a,(SELECT 0 AS N UNION...SELECT 9) b,(SELECT 0 ' at line 9
mysql>
mysql> -- 索引效率测试
mysql> ANALYZE TABLE tb1;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| cookbook.tb1 | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.01 sec)mysql> EXPLAIN SELECT * FROM tb1 WHERE col_b LIKE 'item1%';
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb1 | NULL | range | col_b | col_b | 21 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)生产环境建议
索引优化ALTER TABLE tb1 MODIFY col_b CHAR(10) CHARACTER SET gbk COLLATE gbk_bin;
-- 重建索引以提高区分大小写查询性能
ALTER TABLE tb1 DROP INDEX col_b, ADD INDEX (col_b);
字符集统一-- 建议全表使用utf8mb4（如果系统支持）
ALTER TABLE tb1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;
监控建议-- 定期检查索引使用情况
SELECT index_name, rows_read
FROM information_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'cookbook' AND object_name = 'tb1';mysql> -- 查看表结构确认列名
mysql> DESCRIBE tb1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| col_a | int | YES | MUL | NULL | |
| col_b | char(10) | YES | MUL | NULL | |
| col_c | char(10) | YES | | NULL | |
| col_d | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)mysql>
mysql> -- 正确的最小/最大值查询（区分大小写）
mysql> SELECT-> MIN(col_b COLLATE gbk_bin) AS min_value,-> MAX(col_b COLLATE gbk_bin) AS max_value-> FROM tb1;
+-----------+-----------+
| min_value | max_value |
+-----------+-----------+
| 0_test | z_test |
+-----------+-----------+
1 row in set (0.00 sec)mysql>
mysql> -- 不区分大小写的查询
mysql> SELECT-> MIN(col_b) AS min_value_ci, -- 默认使用gbk_chinese_ci-> MAX(col_b) AS max_value_ci-> FROM tb1;
+--------------+--------------+
| min_value_ci | max_value_ci |
+--------------+--------------+
| 0_test | z_test |
+--------------+--------------+
1 row in set (0.00 sec)
8.7 将摘要划分为子群
mysql> select count(*) from mail;
+----------+
| count(*) |
+----------+
| 16 |
+----------+
1 row in set (0.00 sec)mysql> select srcuser, count(*) from mail-> group by srcuser;
+---------+----------+
| srcuser | count(*) |
+---------+----------+
| barb | 3 |
| tricia | 2 |
| phil | 5 |
| gene | 6 |
+---------+----------+
4 rows in set (0.00 sec)mysql> select srcuser,-> sum(size) as 'total bytes',-> avg(size) as ' bytes per message'-> from mail group by srcuser;
+---------+-------------+-------------------+
| srcuser | total bytes | bytes per message |
+---------+-------------+-------------------+
| barb | 156696 | 52232.0000 |
| tricia | 2589407 | 1294703.5000 |
| phil | 18974 | 3794.8000 |
| gene | 1033108 | 172184.6667 |
+---------+-------------+-------------------+
4 rows in set, 1 warning (0.00 sec)mysql> select srcuser, srchost ,count(srcuser) from mail-> group by srcuser, srchost;
+---------+---------+----------------+
| srcuser | srchost | count(srcuser) |
+---------+---------+----------------+
| barb | saturn | 2 |
| tricia | mars | 1 |
| phil | mars | 3 |
| gene | venus | 2 |
| barb | venus | 1 |
| tricia | saturn | 1 |
| gene | mars | 2 |
| phil | venus | 2 |
| gene | saturn | 2 |
+---------+---------+----------------+
9 rows in set (0.00 sec)mysql> select srcuser, max(size), max(t) from mail group by srcuser;
+---------+-----------+---------------------+
| srcuser | max(size) | max(t) |
+---------+-----------+---------------------+
| barb | 98151 | 2006-05-14 14:42:21 |
| tricia | 2394482 | 2006-05-14 17:03:01 |
| phil | 10294 | 2006-05-17 12:49:23 |
| gene | 998532 | 2006-05-19 22:21:51 |
+---------+-----------+---------------------+
4 rows in set (0.00 sec)mysql> select srcuser, dstuser, max(size) from mail group by srcuser,dstuser;
+---------+---------+-----------+
| srcuser | dstuser | max(size) |
+---------+---------+-----------+
| barb | tricia | 58274 |
| tricia | gene | 194925 |
| phil | phil | 1048 |
| gene | barb | 2291 |
| phil | tricia | 5781 |
| barb | barb | 98151 |
| tricia | phil | 2394482 |
| gene | gene | 23992 |
| gene | tricia | 998532 |
| phil | barb | 10294 |
+---------+---------+-----------+
10 rows in set (0.00 sec)mysql> select name, max(miles) as 'longest trip'-> from driver_log group by name;
+-------+--------------+
| name | longest trip |
+-------+--------------+
| Ben | 152 |
| Suzi | 502 |
| Henry | 300 |
+-------+--------------+
3 rows in set (0.00 sec)mysql> SELECT name, trav_date, miles AS 'longest trip'-> FROM (-> SELECT-> name,-> trav_date,-> miles,-> RANK() OVER (PARTITION BY name ORDER BY miles DESC) AS rnk-> FROM driver_log-> ) t-> WHERE rnk = 1;
+-------+------------+--------------+
| name | trav_date | longest trip |
+-------+------------+--------------+
| Ben | 2014-07-30 | 152 |
| Henry | 2014-07-29 | 300 |
| Suzi | 2014-08-02 | 502 |
+-------+------------+--------------+
3 rows in set (0.00 sec)mysql> SELECT d1.name, d1.trav_date, d1.miles AS 'longest trip'-> FROM driver_log d1-> JOIN (-> SELECT name, MAX(miles) AS max_miles-> FROM driver_log-> GROUP BY name-> ) d2 ON d1.name = d2.name AND d1.miles = d2.max_miles;
+-------+------------+--------------+
| name | trav_date | longest trip |
+-------+------------+--------------+
| Ben | 2014-07-30 | 152 |
| Henry | 2014-07-29 | 300 |
| Suzi | 2014-08-02 | 502 |
+-------+------------+--------------+
3 rows in set (0.00 sec)mysql> -- 临时禁用ONLY_FULL_GROUP_BY
mysql> SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> -- 执行原查询
mysql> SELECT name, trav_date, MAX(miles) AS 'longest trip'-> FROM driver_log-> GROUP BY name;
+-------+------------+--------------+
| name | trav_date | longest trip |
+-------+------------+--------------+
| Ben | 2014-07-30 | 152 |
| Suzi | 2014-07-29 | 502 |
| Henry | 2014-07-29 | 300 |
+-------+------------+--------------+
3 rows in set (0.00 sec)mysql>
mysql> -- 恢复原设置
mysql> SET SESSION sql_mode=@@global.sql_mode;
Query OK, 0 rows affected (0.00 sec)
方案对比
方案 优点 缺点 适用场景
子查询 兼容性好，所有MySQL版本支持 SQL稍复杂 所有环境
窗口函数 语法简洁 需要MySQL 8.0+ 新版MySQL
修改sql_mode 简单 违反SQL标准 临时分析mysql> create table ttt-> select name, max(miles) as miles from driver_log group by name;
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select d.name, d.trav_date, d.miles as 'longest trip'-> from driver_log as d inner join ttt using (name, miles) order by name;
+-------+------------+--------------+
| name | trav_date | longest trip |
+-------+------------+--------------+
| Ben | 2014-07-30 | 152 |
| Henry | 2014-07-29 | 300 |
| Suzi | 2014-08-02 | 502 |
+-------+------------+--------------+
3 rows in set (0.00 sec)
8.8 摘要与空值
mysql> select subject, test, score from expt order by subject, test;
+---------+------+-------+
| subject | test | score |
+---------+------+-------+
| Jane | A | 47 |
| Jane | B | 50 |
| Jane | C | NULL |
| Jane | D | NULL |
| Marvin | A | 52 |
| Marvin | B | 45 |
| Marvin | C | 53 |
| Marvin | D | NULL |
+---------+------+-------+
8 rows in set (0.01 sec)mysql> select subject,-> count(score) as n,-> sum(score) as total,-> avg(score) as average,-> min(score) as lowest,-> max(score) as highest-> from expt group by subject;
+---------+---+-------+---------+--------+---------+
| subject | n | total | average | lowest | highest |
+---------+---+-------+---------+--------+---------+
| Jane | 2 | 97 | 48.5000 | 47 | 50 |
| Marvin | 3 | 150 | 50.0000 | 45 | 53 |
+---------+---+-------+---------+--------+---------+
2 rows in set (0.00 sec)