Mysql常用命令和方法

 -- 時間
 SELECT CONCAT(DATE_FORMAT(NOW(),'%Y/%m/%d'),'|',DATE_FORMAT(NOW(),'%H:%i:%s'));
-- 毫秒 SELECT REPLACE(REPLACE(REPLACE(REPLACE(CURRENT_TIMESTAMP(3),'-',''),'.',''),':',""),' ','')
SELECT CURRENT_TIMESTAMP(3)
-- DATEDIFF
SELECT CASE WHEN DATEDIFF('2019/01/01',NOW())>3 THEN '1' ELSE 'w' END
-- DATE_ADD
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY);
-- SUBSTRING
SELECT SUBSTRING('08:02:12',1,5)
-- REPLACE
SELECT REPLACE('aasd&lt;fasdf','&lt;','<') 
-- 獲取表字段
SELECT GROUP_CONCAT(column_name) FROM INFORMATION_SCHEMA.columns WHERE table_schema='lxpdc' AND table_name='lrwf03h'
-- 查詢數據庫表
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='lxpdc' ORDER BY table_name
-- 查詢 執行計劃是否開啟 配置項 event_scheduler=1 sql調整 SET GLOBAL event_scheduler =1; 
SHOW VARIABLES LIKE 'event_scheduler';
-- 查詢 從數據庫同步情況 root權限
SHOW  SLAVE STATUS 
-- 從數據同步配置
CHANGE MASTER TO MASTER_HOST='ip地址', 
MASTER_USER='lrmis',MASTER_PASSWORD='ooooo',
MASTER_LOG_FILE='binlog.000008',
MASTER_LOG_POS=45815714;
-- 從數據庫忽略 當前同步異常
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;
-- 查詢 主數據庫是否配置同步情況
SHOW MASTER STATUS
-- 簡單的存儲過程
CREATE DEFINER=`lrmis`@`%` PROCEDURE `AutoCancelWFsale`()
BEGIN
-- 自動註銷離職人員
UPDATE lrwf07h a
JOIN lrpa65h_cancel b ON a.wf_sale=b.pa_lxno AND b.pa_oudat<>''
SET a.wf_cancel= 'Y';
END$$

DELIMITER ;
-- 簡單的事件
DELIMITER $$
-- 2019-06-11起,每晚凌晨3點自動執行註銷人員
CREATE DEFINER=`lrmis`@`%` EVENT `event_AutoCancelWFsale` 
ON SCHEDULE EVERY 1 DAY STARTS '2019-06-11 03:00:00' ON COMPLETION PRESERVE ENABLE DO CALL AutoCancelWFsale()$$

DELIMITER ;
-- 查看事件
SHOW EVENTS; 
-- 將事件設置可用狀態
ALTER EVENT event_AutoCancelWFsale ON COMPLETION PRESERVE ENABLE; 
-- 獲取批量修改表的字段類型的sql uid修改成子增長性
SELECT 
  CONCAT(
    'alter table ',
    table_name,
    ' CHANGE uid ',
    column_name,
    ' BIGINT(28) NOT NULL AUTO_INCREMENT;'
  ) AS col
FROM
  INFORMATION_SCHEMA.columns a 
WHERE TABLE_SCHEMA = 'lxtest' -- 代表所在的数据库
AND COLUMN_NAME='uid'
  AND data_type IN ('decimal') -- 批量修改的类型
  /*
  SELECT 
   CONCAT(
    'alter table ',
    table_name,
    ' CHANGE ',
    column_name,' ',
    column_name,
    ' decimal(',numeric_precision,',',numeric_scale,') NOT NULL default 0;'
  ) AS col
  -- select *
FROM
  INFORMATION_SCHEMA.columns a 
WHERE TABLE_SCHEMA = 'lxtest' -- 代表所在的数据库
  -- AND data_type IN ('varchar') -- 批量修改的类型
  AND data_type NOT IN ('bigint','longblob','longtext')
  AND column_default IS NULL AND is_nullable="NO"
  
  SELECT 
   CONCAT(
    'alter table ',
    table_name,
    ' CHANGE ',
    column_name,' ',
    column_name,
    ' varchar(',character_maximum_length,') NOT NULL default \'\';'
  ) AS col
  -- select *
FROM
  INFORMATION_SCHEMA.columns a 
WHERE TABLE_SCHEMA = 'lxtest' -- 代表所在的数据库
  AND data_type IN ('varchar') -- 批量修改的类型
  AND column_default IS NULL AND is_nullable="NO"
  */
  --