Mysql常用語句整理

python_lover 2022-12-25 發布于北京

展開全文

把工作常用的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