板凳-------Mysql cookbook学习 (十一--------10)
13.6 生成随机数 7/13/2025 10:42:00 PM
mysql> select rand(), rand(), rand();
+--------------------+--------------------+--------------------+
| rand() | rand() | rand() |
+--------------------+--------------------+--------------------+
| 0.6048015268564239 | 0.9033102261864675 | 0.7021465810967111 |
+--------------------+--------------------+--------------------+
1 row in set (0.01 sec)mysql> SHOW COLUMNS FROM t;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(64) | YES | | NULL | |
| value | int | YES | | NULL | |
| date | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)mysql> SELECT name, value, date, RAND(), RAND(10), RAND(20) FROM t;
+-------+-------+------------+---------------------+---------------------+---------------------+
| name | value | date | RAND() | RAND(10) | RAND(20) |
+-------+-------+------------+---------------------+---------------------+---------------------+
| name1 | 38 | 1999-01-01 | 0.8008022576577982 | 0.6570515219653505 | 0.15888261251047497 |
| name2 | 40 | 2000-12-31 | 0.8975715757325027 | 0.12820613023657923 | 0.6355305003333189 |
| name3 | 42 | 2013-02-28 | 0.08545113642276371 | 0.6698761160204896 | 0.7010046948688149 |
| name4 | 44 | 2018-01-02 | 0.7345409856499554 | 0.9647622201263553 | 0.5984320040777623 |
+-------+-------+------------+---------------------+---------------------+---------------------+
4 rows in set (0.00 sec)
13.7 随机化行集合
mysql> select name from t order by rand();
+-------+
| name |
+-------+
| name4 |
| name3 |
| name1 |
| name2 |
+-------+
4 rows in set (0.00 sec)mysql> select name from t order by rand();
+-------+
| name |
+-------+
| name2 |
| name3 |
| name4 |
| name1 |
+-------+
4 rows in set (0.00 sec)
13.8 从行集合中随机选择条目
mysql> select n from die order by rand() limit 1;
+---+
| n |
+---+
| 4 |
+---+
1 row in set (0.02 sec)mysql> select n from die order by rand() limit 1;
+---+
| n |
+---+
| 6 |
+---+
1 row in set (0.00 sec)mysql> select n from die order by rand() limit 1;
+---+
| n |
+---+
| 3 |
+---+
1 row in set (0.00 sec)mysql> select n from die order by rand() limit 1;
+---+
| n |
+---+
| 5 |
+---+
1 row in set (0.00 sec)
13.9 分配等级
mysql> SELECT value FROM t ORDER BY value DESC;
+-------+
| value |
+-------+
| 44 |
| 42 |
| 40 |
| 38 |
+-------+
4 rows in set (0.00 sec)1. 在 MySQL 中,以下常见关键字需要特别注意(建议用反引号包裹):
o rank
o order
o group
o desc
o table
o index 等rank 是 MySQL 的保留关键字(用于窗口函数中的排名操作),直接作为列别名会导致语法错误。解决方法是用反引号 ` 将 rank 包裹起来,或者使用其他非保留关键字作为列名。
mysql> SELECT @rownum := @rownum + 1 AS `rank`, value-> FROM t-> ORDER BY value DESC;
+------+-------+
| rank | value |
+------+-------+
| 1 | 44 |
| 2 | 42 |
| 3 | 40 |
| 4 | 38 |
+------+-------+
4 rows in set, 1 warning (0.00 sec)mysql> set @rank = 0, @prev_val = null;
Query OK, 0 rows affected (0.00 sec)mysql> select @rank := if(@prev_val = value, @rank, @rank+1) as `rank`,-> @prev_val := value as value-> from t order by value desc;
+------+-------+
| rank | value |
+------+-------+
| 0 | 44 |
| 1 | 42 |
| 2 | 40 |
| 3 | 38 |
+------+-------+
4 rows in set, 2 warnings (0.00 sec)mysql> SELECT-> @rownum := @rownum + 1 AS `row`,-> @rank := IF(@prev_val != value, @rownum, @rank) AS `rank`,-> @prev_val := value AS `value`-> FROM t-> ORDER BY value DESC;
+------+------+-------+
| row | rank | value |
+------+------+-------+
| 1 | 0 | 44 |
| 2 | 2 | 42 |
| 3 | 3 | 40 |
| 4 | 4 | 38 |
+------+------+-------+
4 rows in set, 3 warnings (0.00 sec)如果你使用MySQL 8.0及以上版本,推荐使用窗口函数:
mysql> SELECT-> ROW_NUMBER() OVER (ORDER BY value DESC) AS row_num,-> DENSE_RANK() OVER (ORDER BY value DESC) AS ranking,-> value AS score_value-> FROM t;
+---------+---------+-------------+
| row_num | ranking | score_value |
+---------+---------+-------------+
| 1 | 1 | 44 |
| 2 | 2 | 42 |
| 3 | 3 | 40 |
| 4 | 4 | 38 |
+---------+---------+-------------+
4 rows in set (0.01 sec)mysql> select name, wins from al_winner order by wins desc, name;
+----------------+------+
| name | wins |
+----------------+------+
| Mulder, Mark | 21 |
| Clemens, Roger | 20 |
| Moyer, Jamie | 20 |
| Garcia, Freddy | 18 |
| Hudson, Tim | 18 |
| Abbott, Paul | 17 |
| Mays, Joe | 17 |
| Mussina, Mike | 17 |
| Sabathia, C.C. | 17 |
| Zito, Barry | 17 |
| Buehrle, Mark | 16 |
| Milton, Eric | 15 |
| Pettitte, Andy | 15 |
| Radke, Brad | 15 |
| Sele, Aaron | 15 |
+----------------+------+
15 rows in set (0.01 sec)mysql> set @rownum = 0, @rank = 0, @prev_val = null;
Query OK, 0 rows affected (0.00 sec)mysql> select @rownum := @rownum + 1 as `row`,-> @rank := if(@prev_val != wins, @rownum, @rank) as `rank`,-> name,-> @prev_val := wins as wins-> from al_winner order by wins desc;
+------+------+----------------+------+
| row | rank | name | wins |
+------+------+----------------+------+
| 1 | 1 | Mulder, Mark | 21 |
| 2 | 2 | Clemens, Roger | 20 |
| 3 | 2 | Moyer, Jamie | 20 |
| 4 | 4 | Garcia, Freddy | 18 |
| 5 | 4 | Hudson, Tim | 18 |
| 6 | 6 | Abbott, Paul | 17 |
| 7 | 6 | Mays, Joe | 17 |
| 8 | 6 | Mussina, Mike | 17 |
| 9 | 6 | Sabathia, C.C. | 17 |
| 10 | 6 | Zito, Barry | 17 |
| 11 | 11 | Buehrle, Mark | 16 |
| 12 | 12 | Milton, Eric | 15 |
| 13 | 12 | Pettitte, Andy | 15 |
| 14 | 12 | Radke, Brad | 15 |
| 15 | 12 | Sele, Aaron | 15 |
+------+------+----------------+------+
15 rows in set, 3 warnings (0.00 sec)
第14章:处理重复项
14.0 引言
14.1 防止在表中发生重复
mysql> describe person;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| tax_id | int unsigned | NO | PRI | NULL | |
| last_name | char(20) | NO | | NULL | |
| first_name | char(20) | NO | | NULL | |
| address | char(40) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
1. 插入数据时需要确保:
INSERT INTO person (tax_id, last_name, first_name, address)
VALUES (123456, 'Smith', 'John', '123 Main St');mysql> select * from person;
+--------+-----------+------------+-------------+
| tax_id | last_name | first_name | address |
+--------+-----------+------------+-------------+
| 123456 | Smith | John | 123 Main St |
+--------+-----------+------------+-------------+
1 row in set (0.00 sec)mysql> INSERT INTO person (tax_id, last_name, first_name, address)-> VALUES (123456, 'Smith', 'John', '123 Main St');
ERROR 1062 (23000): Duplicate entry '123456' for key 'person.PRIMARY'
mysql> INSERT ignore INTO person (tax_id, last_name, first_name, address)-> VALUES (123456, 'Smith', 'John', '123 Main St');
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> replace into person (tax_id, last_name, first_name, address)-> VALUES (123456, 'Smith', 'John', '123 Main St');
Query OK, 1 row affected (0.01 sec)mysql> select * from person;
+--------+-----------+------------+-------------+
| tax_id | last_name | first_name | address |
+--------+-----------+------------+-------------+
| 123456 | Smith | John | 123 Main St |
+--------+-----------+------------+-------------+
1 row in set (0.00 sec)
14.2 处理向表中装载行时出现的重复错误
mysql> select * from poll_vote;
Empty set (0.01 sec)mysql> describe poll_vote;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| poll_id | int unsigned | NO | PRI | NULL | auto_increment |
| candidate_id | int unsigned | NO | PRI | NULL | |
| vote_count | int unsigned | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)mysql> insert into poll_vote (poll_id, candidate_id,vote_count) values(14, 2, 1)-> on duplicate key update vote_count = vote_count + 1;
Query OK, 1 row affected (0.01 sec)mysql> select * from poll_vote;
+---------+--------------+------------+
| poll_id | candidate_id | vote_count |
+---------+--------------+------------+
| 14 | 2 | 1 |
+---------+--------------+------------+
1 row in set (0.00 sec)mysql> insert into poll_vote (poll_id, candidate_id,vote_count) values(14, 2, 1)-> on duplicate key update vote_count = vote_count + 1;
Query OK, 2 rows affected (0.01 sec)mysql> select * from poll_vote;
+---------+--------------+------------+
| poll_id | candidate_id | vote_count |
+---------+--------------+------------+
| 14 | 2 | 2 |
+---------+--------------+------------+
1 row in set (0.00 sec)
14.3 计数和识别重复项
mysql> select * from catalog_list;
+-----------+-------------+--------------------------+
| last_name | first_name | street |
+-----------+-------------+--------------------------+
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| Baxter | Wallace | 57 3rd Ave. |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| BAXTER | WALLACE | 57 3rd Ave. |
| Brown | Bartholomew | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| Baxter | Wallace | 57 3rd Ave. |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| BAXTER | WALLACE | 57 3rd Ave. |
| Brown | Bartholomew | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
+-----------+-------------+--------------------------+
16 rows in set (0.01 sec)mysql> select count(*) as `rows` from catalog_list;
+------+
| rows |
+------+
| 16 |
+------+
1 row in set (0.00 sec)mysql> select count(distinct last_name, first_name) as 'distinct names'-> from catalog_list;
+----------------+
| distinct names |
+----------------+
| 5 |
+----------------+
1 row in set (0.00 sec)mysql> select count(*) - count(distinct last_name, first_name) as 'distinct names'-> from catalog_list;
+----------------+
| distinct names |
+----------------+
| 11 |
+----------------+
1 row in set (0.00 sec)mysql> SELECT-> COUNT(DISTINCT last_name, first_name) / COUNT(*) AS 'unique',-> 1 - (COUNT(DISTINCT last_name, first_name) / COUNT(*)) AS 'nonunique'-> FROM catalog_list;
+--------+-----------+
| unique | nonunique |
+--------+-----------+
| 0.3125 | 0.6875 |
+--------+-----------+
1 row in set (0.00 sec)mysql> SELECT-> COUNT(DISTINCT CONCAT(last_name, '|', first_name)) / COUNT(*) AS 'unique',-> 1 - (COUNT(DISTINCT CONCAT(last_name, '|', first_name)) / COUNT(*)) AS 'nonunique'-> FROM catalog_list;
+--------+-----------+
| unique | nonunique |
+--------+-----------+
| 0.3125 | 0.6875 |
+--------+-----------+
1 row in set (0.00 sec)mysql> SELECT-> ROUND(COUNT(DISTINCT last_name, first_name) / COUNT(*) * 100, 2) AS 'unique_percent',-> ROUND((1 - COUNT(DISTINCT last_name, first_name) / COUNT(*)) * 100, 2) AS 'duplicate_percent'-> FROM catalog_list;
+----------------+-------------------+
| unique_percent | duplicate_percent |
+----------------+-------------------+
| 31.25 | 68.75 |
+----------------+-------------------+
1 row in set (0.00 sec)mysql> SELECT-> COUNT(DISTINCT full_name) / COUNT(*) AS 'unique',-> 1 - (COUNT(DISTINCT full_name) / COUNT(*)) AS 'nonunique'-> FROM (-> SELECT CONCAT(last_name, '|', first_name) AS full_name-> FROM catalog_list-> ) AS t;
+--------+-----------+
| unique | nonunique |
+--------+-----------+
| 0.3125 | 0.6875 |
+--------+-----------+
1 row in set (0.00 sec)mysql> select count(*) as repetitions, last_name, first_name-> from catalog_list-> group by last_name, first_name-> having repetitions > 1;
+-------------+-----------+-------------+
| repetitions | last_name | first_name |
+-------------+-----------+-------------+
| 2 | Isaacson | Jim |
| 6 | Baxter | Wallace |
| 2 | McTavish | Taylor |
| 4 | Pinter | Marlene |
| 2 | Brown | Bartholomew |
+-------------+-----------+-------------+
5 rows in set (0.00 sec)mysql> create table tmpp6-> select count(*) as count, last_name, first_name from catalog_list-> group by last_name, first_name having count > 1;
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql> select catalog_list.*-> from tmpp6 inner join catalog_list using(last_name, first_name)-> order by last_name, first_name;
+-----------+-------------+--------------------------+
| last_name | first_name | street |
+-----------+-------------+--------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| BAXTER | WALLACE | 57 3rd Ave. |
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
| Baxter | Wallace | 57 3rd Ave. |
| BAXTER | WALLACE | 57 3rd Ave. |
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
| Brown | Bartholomew | 432 River Run |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| Pinter | Marlene | 9 Sunset Trail |
| Pinter | Marlene | 9 Sunset Trail |
| Pinter | Marlene | 9 Sunset Trail |
+-----------+-------------+--------------------------+
16 rows in set (0.00 sec)