Mysql常用语句整理

把工作常用的mysql命令整理一下，省的用的时候在到处找

1.常用命令

1.1 登录 mysql -u root -p

1.2 生成随机数 若在 i<=R<=j 范围内生成随机数 FLOOR(I+RAND()*(j-i+1))

1.3 让主键从0开始 TRUNCATE TABLE TableName

1.4 拼接删除指定数据库表 SELECT CONCAT('DROP TABLE ', table_name,';') FROM information_schema.`TABLES` WHERE table_schema='数据库名';

 

2.存储过程

2.1 使用存储过程添加测试数据

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_data_p`(IN num INT)
BEGIN
DECLARE n INT DEFAULT 1;
WHILE n <= num DO
INSERT INTO TableName(column1,column2)
VALUES(columnvalue1,columnvalue2);
set n=n+1;
end while;
END

2.2 使用存储过程传参查询数据

CREATE DEFINER=`root`@`%` PROCEDURE `Proc_StatisticsVeterans`(in groupstr varchar(50),in groupstrval varchar(50))
BEGIN

   DECLARE sqlstr varchar(2000) DEFAULT '';
     
     set sqlstr = CONCAT(sqlstr,'select a.',groupstr,' as GroupStr');
   set sqlstr = CONCAT(sqlstr,',count(*) as PersonNum,b.cnt as  DocumentPage from TBase_VeteranInfo a left join (select ');
     set sqlstr = CONCAT(sqlstr,'v.',groupstr,',count(*) cnt from TBase_VeteranInfo v inner join TBase_ImgDetailInfo d on v.Id=d.VId where 1=1 ');
     
     IF(groupstrval!=''&&groupstr!='NewJobTime') THEN
         set sqlstr = CONCAT(sqlstr,' And v.', groupstr, ' = ',groupstrval);
     END IF;
     
     /*
     IF(groupstrval!=''&&groupstr ='NewJobTime') THEN
         set sqlstr = CONCAT(sqlstr,' AND DATE(NewJobTime)>=DATE("',startime,'") AND DATE(NewJobTime)<=DATE("',endtime,'")', groupstr, ' = ',groupstrval);
     END IF;
     */
     
     set sqlstr = CONCAT(sqlstr,' group by v.',groupstr);
     set sqlstr = CONCAT(sqlstr,' ) b');
     set sqlstr = CONCAT(sqlstr,' on a.',groupstr,' = ','b.',groupstr);
     set sqlstr = CONCAT(sqlstr,' where 1=1 ');
     
     IF(groupstrval!=''&&groupstr!='NewJobTime') THEN
         set sqlstr = CONCAT(sqlstr,' And a.', groupstr, ' = ',groupstrval);
     END IF;
     
     
     /*
     IF(groupstrval!=''&&groupstr ='NewJobTime') THEN
         set sqlstr = CONCAT(sqlstr,' AND DATE(NewJobTime)>=DATE("',startime,'") AND DATE(NewJobTime)<=DATE("',endtime,'")', groupstr, ' = ',groupstrval);
     END IF;
      */
        
     set sqlstr = CONCAT(sqlstr,' group by a.',groupstr,',b.cnt;');
     
      /*select sqlstr; */
     
   # select var_sql ;
     set @sql = sqlstr;
     # 预处理动态sql语句
     PREPARE stmt  from @sql;    
     # 执行sql
     EXECUTE stmt ;
     # 释放prepare
      deallocate prepare stmt; 

END

 2.3 使用存储过程生成随机email、电话、名字

#email
CREATE DEFINER=`root`@`localhost` FUNCTION `generate_163email`() RETURNS char(20) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE head VARCHAR (100) DEFAULT '000,182,150,136,152,158,183';
  DECLARE content CHAR(10) DEFAULT '0123456789';
  DECLARE phone CHAR(11) DEFAULT SUBSTRING(head, 1+ (FLOOR(1 + (RAND() * 3)) * 4), 3);#定义手机号变量且长度为11
  DECLARE email CHAR(20); #定义邮箱变量且长度为20
  DECLARE i INT DEFAULT 1;
  DECLARE len INT DEFAULT LENGTH(content);
  WHILE
    i < 9 DO SET i = i + 1;
    SET phone = CONCAT(phone, SUBSTRING(content, FLOOR(1 + RAND() * len), 1));
  END WHILE;
    set email = CONCAT(phone,'@163.com');
  RETURN email;
end




#电话
CREATE DEFINER=`root`@`localhost` FUNCTION `generate_phone`() RETURNS char(11) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE head VARCHAR (100) DEFAULT '000,182,150,136,152,158,183';
  DECLARE content CHAR(10) DEFAULT '0123456789';
  DECLARE phone CHAR(11) DEFAULT SUBSTRING(head, 1+ (FLOOR(1 + (RAND() * 3)) * 4), 3);
  DECLARE i INT DEFAULT 1;
  DECLARE len INT DEFAULT LENGTH(content);
  WHILE
    i < 9 DO SET i = i + 1;
    SET phone = CONCAT(
      phone,
      SUBSTRING(content, FLOOR(1 + RAND() * len), 1)
    );
  END WHILE;
  RETURN phone;
end


#名字
CREATE DEFINER=`root`@`localhost` FUNCTION `generate_userName`() RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN -- 开始
  DECLARE xing VARCHAR (2056) DEFAULT '赵钱孙李周郑王冯陈楮卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闽席季麻强贾路娄危江童颜郭梅盛林刁锺徐丘骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁'; -- 定义姓
  DECLARE ming VARCHAR (2056) DEFAULT '嘉懿煜城懿轩烨伟苑博伟泽熠彤鸿煊博涛烨霖烨华煜祺智宸正豪昊然明杰诚立轩立辉峻熙弘文熠彤鸿煊烨霖哲瀚鑫鹏致远俊驰雨泽烨磊晟睿天佑文昊修洁黎昕远航旭尧鸿涛伟祺轩越泽浩宇瑾瑜皓轩擎苍擎宇志泽睿渊楷瑞轩弘文哲瀚雨泽鑫磊梦琪忆之桃慕青问兰尔岚元香初夏沛菡傲珊曼文乐菱痴珊恨玉惜文香寒新柔语蓉海安夜蓉涵柏水桃醉蓝春儿语琴从彤傲晴语兰又菱碧彤元霜怜梦紫寒妙彤曼易南莲紫翠雨寒易烟如萱若南寻真晓亦向珊慕灵以蕊寻雁映易雪柳孤岚笑霜海云凝天沛珊寒云冰旋宛儿绿真盼儿晓霜碧凡夏菡曼香若烟半梦雅绿冰蓝灵槐平安书翠翠风香巧代云梦曼幼翠友巧听寒梦柏醉易访旋亦玉凌萱访卉怀亦笑蓝春翠靖柏夜蕾冰夏梦松书雪乐枫念薇靖雁寻春恨山从寒忆香觅波静曼凡旋以亦念露芷蕾千兰新波代真新蕾雁玉冷卉紫山千琴恨天傲芙盼山怀蝶冰兰山柏翠萱乐丹翠柔谷山之瑶冰露尔珍谷雪乐萱涵菡海莲傲蕾青槐冬儿易梦惜雪宛海之柔夏青亦瑶妙菡春竹修杰伟诚建辉晋鹏天磊绍辉泽洋明轩健柏煊昊强伟宸博超君浩子骞明辉鹏涛炎彬鹤轩越彬风华靖琪明诚高格光华国源宇晗昱涵润翰飞翰海昊乾浩博和安弘博鸿朗华奥华灿嘉慕坚秉建明金鑫锦程瑾瑜鹏经赋景同靖琪君昊俊明季同开济凯安康成乐语力勤良哲理群茂彦敏博明达朋义彭泽鹏举濮存溥心璞瑜浦泽奇邃祥荣轩'; -- 定义名
  DECLARE I_xing INT DEFAULT LENGTH(xing) / 3;    -- 定义姓的下标
  DECLARE I_ming INT DEFAULT LENGTH(ming) / 3;    -- 定义名的下标
  DECLARE return_str VARCHAR (2056) DEFAULT '';   -- 定义默认的返回值
  SET return_str = CONCAT(
    return_str,
    SUBSTRING(xing, FLOOR(1 + RAND() * I_xing), 1)    -- 截取xing集合中的随机一个下标,取一位(字符下标从1开始)
  );
  SET return_str = CONCAT(
    return_str,
    SUBSTRING(ming, FLOOR(1 + RAND() * I_ming), 1)
  );
  IF RAND() > 0.400           -- 如果随机函数值大于0.4.则添加第3个名字
  THEN SET return_str = CONCAT(
    return_str,
    SUBSTRING(ming, FLOOR(1 + RAND() * I_ming), 1)
  );
  END IF;     -- 结束if语句
  RETURN return_str;      -- 返回执行结果
end