Mysql 细节记忆
DELIMITER $$ 和 DELIMITER ;
DROP PROCEDURE IF EXISTS `pro_follow_getBookBeforeExpired`$$
DECLARE p_ServiceCode VARCHAR(50);
SET p_ServiceCode = 'YYGH';
IF p_expiredMinute = 0
THEN
...;
ELSE IF p_expiredMinute > 0
THEN
...;
ELSE
THEN
...;
END IF
;
跨表的WHERE EXISTS 可以用 LEFT JOIN代替
DATE_ADD(pd.`CreationDate`, INTERVAL (p_expiredMinute-p_alertMinute) MINUTE) <= NOW() -- 提前10分钟
行号
SELECT (@rowNO := @rowNo + 1) AS '行号',t.*
FROM `TB_CC_Task` AS t
,(SELECT @rowNO := 0) AS b
SELECT (CASE WHEN t.a = 2 THEN '1' ELSE '2' END) AS 支付类型
CONCAT('str1','str2','str3')
Function 才可以返回标量
全局临时变量
SET @a = 1;
SELECT @a;
修改表自身
UPDATE `xxx`
SET `MemberPassword` = '670b14728ad9902aecba32e22fa4f6bd'
WHERE `ID` IN
(
SELECT C.`ID`
FROM(
SELECT B.*
FROM `TB_MB_HosptalMember` A
JOIN `TB_MB_MemberInfo` B ON A.`MemberID` = B.`ID`
) C
)
触发器:
DELIMITER $$
USE `boshcc_dev`$$
DROP TRIGGER /*!50032 IF EXISTS */ `Trigger_TB_HS_HealthManage_IsUploaded`$$
CREATE
/*!50017 DEFINER = 'root'@'%' */
TRIGGER `Trigger_TB_HS_HealthManage_IsUploaded` BEFORE UPDATE ON `TB_HS_HealthManage`
FOR EACH ROW BEGIN
IF New.`IsUploaded` = OLD.`IsUploaded`
THEN
SET New.`IsUploaded` = FALSE;
END IF;
END;
$$
DELIMITER ;
命令:
查看服务器版本:
cat /proc/version
连接mysql
mysql -uroot -p
然后输入密码
