MySQL中的特别用法

摩森特沃 2021年12月09日 455次浏览

声明：本文多处直接引用了文末的参考链接中的内容，如有侵权，请联系删除

带条件的插入语句

-- WHERE 条件 成立就插入
INSERT INTO 表名(字段1,字段2)  SELECT 字段1的值,字段2的值 
FROM  DUAL  WHERE EXISTS(SELECT 1 FROM 表名 WHERE 条件)

-- WHERE 条件 不成立就插入
INSERT INTO 表名(字段1,字段2)  SELECT 字段1的值,字段2的值 
FROM  DUAL  WHERE NOT EXISTS(SELECT 1 FROM 表名 WHERE 条件)

连接查询中的on和where

  • 执行顺序：先执行on and条件，再执行where条件

首先根据on and条件过滤出满足条件的右侧记录，然后根据关联字段，左侧表再与过滤出的右侧表记录进行连接

  • 满足字段相等则返回左侧表和右侧表字段信息
  • 若不满足，则返回左侧表字段信息，右侧表字段则显示null

on后面的条件对左侧表的过滤起作用，where条件则对连接后的临时表数据进行筛选(即对左侧表和右侧表都有过滤作用)

查询表中某个字段最大值对应的记录

使用in条件查询的方式

-- 查询部门年龄最大的员工
select * from tb_join_user where (dept_id, age) in 
(select dept_id, Max(age) from tb_join_user group by dept_id);

使用row_number窗口函数的方式

-- 查询部门年龄最大的员工
select * from (
select *, row_number() over(partition by dept_id order by age desc) row_num from tb_join_user
) temp where temp.row_num = 1;

其他窗口函数方法

  • rank()：排名，用法与row_number()相同，row_number()是为每行数据分配一个序号，rank()则是按照分区进行排名，注意：相同的值排名会相同，且会跳过其后相同项的序号个数，如1,2,2,4,5
  • dense_rank()：排名，用法与row_number()相同，row_number()是为每行数据分配一个序号，dense_rank()则是按照分区进行排名，如1,2,2,3,4

随机数生成语句

-- 随机 0-1
select floor(rand() * 2);
 
-- 随机布尔值
select if(floor(rand() * 2) = 1 , '是' , '否');
 
-- 随机2位小数
select convert(floor(rand() * 70 + 30) / 100, decimal(10,2));
 
-- 随机N位字符串
select substr(md5(rand()),1,6);

-- 随机uuid字符串
select uuid() as uuid;
-- 去掉uuid中的“-”
select replace(uuid(),"-","") as uuid;

-- 随机座机号码
select concat(rpad(CEIL(RAND()*1000), 3, '0'), '-',rpad(CEIL(RAND()*1000), 3, '0'), '-',rpad(CEIL(RAND()*10000), 3, '0'))

-- 随机手机号码
select UNIX_TIMESTAMP()*10+floor(rand()*4000000000)

-- 随机邮箱地址
select concat(char(if(floor(rand()*2)=0,65+floor(rand()*26),48+floor(rand()*9)),
if(floor(rand()*2)=0,65+floor(rand()*26),48+floor(rand()*9)),if(floor(rand()*2)=0,65+floor(rand()*26),
48+floor(rand()*9)),if(floor(rand()*2)=0,65+floor(rand()*26),48+floor(rand()*9)),
if(floor(rand()*2)=0,65+floor(rand()*26),48+floor(rand()*9)),if(floor(rand()*2)=0,65+floor(rand()*26),
48+floor(rand()*9))),internal_id, '@', char(if(floor(rand()*2)=0,65+floor(rand()*26),
48+floor(rand()*9)),if(floor(rand()*2)=0,65+floor(rand()*26),48+floor(rand()*9)),
if(floor(rand()*2)=0,65+floor(rand()*26),48+floor(rand()*9)),if(floor(rand()*2)=0,65+floor(rand()*26),
48+floor(rand()*9)),if(floor(rand()*2)=0,65+floor(rand()*26),48+floor(rand()*9)),
if(floor(rand()*2)=0,65+floor(rand()*26),48+floor(rand()*9))), internal_id, '.com');
 
-- 随机姓名 可根据需要增加/减少样本
set @SURNAME = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶
程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';
 
set @NAME = '丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲
嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭
昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊
煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇
蝶觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';

-- 生成3个字的随机名字，length(@surname)/3 是因为中文字符占用3个长度
select concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), 
substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));

数据大小查询语句

通过MySQL的 information_schema 数据库，可查询数据库中每个表占用的空间、表记录的行数；该库中有一个 TABLES 表，这个表主要字段分别是：

  • TABLE_SCHEMA : 数据库名
  • TABLE_NAME：表名
  • ENGINE：所使用的存储引擎
  • TABLES_ROWS：记录数
  • DATA_LENGTH：数据大小
  • INDEX_LENGTH：索引大小

其他字段请参考MySQL的手册，查看一个表占用空间的大小，那就相当于是数据大小 + 索引大小。

-- 查看所有库的大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  
from information_schema.TABLES;

-- 查看指定库的大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  
from information_schema.TABLES where table_schema='sqltest';

-- 查看指定库的指定表的大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  
from information_schema.TABLES where table_schema='sqltest' and table_name='tb_user';

-- 查看指定库的索引大小
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' 
FROM information_schema.TABLES  WHERE table_schema = 'sqltest';

-- 查看指定库的指定表的索引大小
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' 
FROM information_schema.TABLES  WHERE table_schema = 'sqltest' and table_name='tb_user'; 

-- 综合：查看一个库的情况
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', 
CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', 
CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', 
CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', 
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'
FROM information_schema.TABLES WHERE table_schema LIKE 'sqltest';

-- 综合：查看指定库的指定表的情况
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', 
CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', 
CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', 
CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', 
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'
FROM information_schema.TABLES WHERE table_schema = 'sqltest' and table_name='tb_user';

使用存储过程生成大数据量表

建表语句

CREATE TABLE `tb_user` (
         `id` BIGINT (20),
         `username` VARCHAR (200),
         `password` VARCHAR (200),
	 `realname` VARCHAR (200),
         `birth` DATETIME ,
         `sex` VARCHAR (10),
         `email` VARCHAR (200),
         `mobile` VARCHAR (200),
         `create_date` DATETIME ,
         `update_date` DATETIME
) ENGINE=INNODB;

存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `generateRecord`(IN recordCount int)
BEGIN
	DECLARE i INT;
	DECLARE a VARCHAR(64);
	SET i = 0;
	SET a = '';
	SET @s = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶
程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';
	SET @n = '丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听
和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋
旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊
煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶
觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';

	WHILE i >= 0 && i <= recordCount DO
	  SET a = replace(uuid(), "-", "");
		SET i = i + 1;
		INSERT INTO sqltest.`tb_user`
			(`id`, `username`, `password`, `realname`, 
			`birth`, `sex`, `email`, `mobile`, `create_date`, `update_date`) VALUES
			(i, a, '123456', concat(substr(@s,floor(rand()*length(@s)/3+1),1), 
substr(@n,floor(rand()*length(@n)/3+1),1), substr(@n,floor(rand()*length(@n)/3+1),1)),
			NOW(), if(floor(rand() * 2) = 1 , 'female' , 'male'), concat(a, '@qq.com'), 
CONCAT(UNIX_TIMESTAMP()*10+floor(rand()*4000000000), ''), NOW(), NOW());
	END WHILE;
     
	END

引用参考

上一篇： 解决"已损坏，无法打开。 您应该将它移到废纸篓"