当前位置: 首页 > news >正文

板凳-------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)
http://www.lryc.cn/news/588558.html

相关文章:

  • 06【C++ 初阶】类和对象(上篇) --- 初步理解/使用类
  • ThreadLocal内部结构深度解析
  • 《大数据技术原理与应用》实验报告三 熟悉HBase常用操作
  • 每天一个前端小知识 Day 31 - 前端国际化(i18n)与本地化(l10n)实战方案
  • html js express 连接数据库mysql
  • Java:继承和多态(必会知识点整理)
  • 为什么资深C++开发者大部分选vector?揭秘背后的硬核性能真相!
  • 9.服务容错:构建高可用微服务的核心防御
  • #Paper Reading# Apple Intelligence Foundation Language Models
  • 微服务初步入门
  • 量子计算新突破!阿里“太章3.0”实现512量子比特模拟(2025中国量子算力巅峰)
  • 【算法训练营Day12】二叉树part2
  • 《大数据技术原理与应用》实验报告二 熟悉常用的HDFS操作
  • 【小白量化智能体】应用5:编写通达信股票交易指标及生成QMT自动交易Python策略程序
  • UDP协议的端口161怎么检测连通性
  • 【PY32】如何使用 J-Link 和 MDK 开发调试 PY32 MCU
  • 【STM32】什么在使能寄存器或外设之前必须先打开时钟?
  • java基础-1 : 运算符
  • 使用dify生成测试用例
  • 13.计算 Python 字符串的字节大小
  • HTML 文本格式化标签
  • 工业新引擎:预测性维护在工业场景中的实战应用(流程制造业为例)
  • 具身智能零碎知识点(五):VAE中对使用KL散度的理解
  • JJ20 Final Lap演唱会纪念票根生成工具
  • HashMap的长度为什么要是2的n次幂以及HashMap的继承关系(元码解析)
  • C语言:20250714笔记
  • 文本预处理(四)
  • AI驱动编程范式革命:传统开发与智能开发的全维度对比分析
  • 【DataWhale】快乐学习大模型 | 202507,Task01笔记
  • js的局部变量和全局变量