**********************************************************
MYSQL存储过程:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`` PROCEDURE `TableName_MethodName`(
_Parameter MYSQLPARAMETERTYPE
)
BEGIN
MYSQL_SENTENCE;
END$$
**********************************************************
MYSQL事物:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`` PROCEDURE `TableName_MethodName`(
_Parameter MYSQLPARAMETERTYPE
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
SAVEPOINT SP;
MYSQL_SENTENCE;
IF @@ERROR_COUNT <> 0 THEN
ROLLBACK TO SAVEPOINT SP;
……;
ELSE
……;
COMMIT;
END IF;
END$$
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`` PROCEDURE `TableName_MethodName`(
_Parameter MYSQLPARAMETERTYPE
)
BEGIN
DECLARE EXIT HANDLER FOR SQLWARNING,SQLEXCEPTION
BEGIN
ROLLBACK;
SET _PTransactionId = 0;
END;
START TRANSACTION;
MYSQL_SENTENCE;
COMMIT;
END$$
后者的执行效率比前者高。
*********************************************************
MYSQL 函数:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `Func_FunctionName`(`_Parameter` ParameterType) RETURNS ReturnType CHARSET utf8
BEGIN
SET @ReturnVar = ParameterTypeDefaultValue;
MYSQLSENTENCE;
RETURN @ReturnVar;
END$$
*******************************************************
MYSQL 执行一个字符串语句@SqlStr='select……':
PREPARE StmtTotalCount FROM @SqlStr;
EXECUTE StmtTotalCount;
DEALLOCATE PREPARE StmtTotalCount;
*******************************************************
MYSQL连接字符串(mysql中不能用+连接字符串,可以用mysql的方法连接):
SET @SqlStr = CONCAT(@SqlStr, ' AND UserId > 0');
*******************************************************
MYSQL IF ELSE:
IF conditions THEN
……
[ELSE]
……
[ELSE]
……
END IF;
*******************************************************
MYSQL WHILE:
WHILE CONDITIONS DO
……
END WHILE;
------------------------------------------------------------------------------------
eg.
依次取出最后一个EmployeeId进行操作
SET _EmployeeIds ='1,2,3,4,5,6,7,0,9,12,51,77'
WHILE _EmployeeIds != '' DO
SET @EmployeeId = SUBSTRING_INDEX(_EmployeeIds,',',-1);
INSERT INTO Member
(
EmployeeId
)
VALUES
(
@EmployeeId
);
SET @NUM = 0;
SET @STR = _EmployeeIds;
WHILE INSTR(@STR,SUBSTRING_INDEX( _EmployeeIds, ',', -1 )) != 0 DO
SET @NUM =@NUM+INSTR(@STR,SUBSTRING_INDEX( _EmployeeIds, ',', -1 ));
SET @STR = SUBSTRING(@STR,INSTR(@STR,SUBSTRING_INDEX( _EmployeeIds, ',', -1 ))+1);
END WHILE;
SET _EmployeeIds=LEFT( _EmployeeIds, @NUM -2);
END WHILE;
*******************************************************
MYSQL SELECT INTO:
mysql的table不支持select * from oldTable into newTable
但可以这么写:CREATE TABLE newTable (SELECT * FROM oldTable)
SET @COLVALUE = DEFAULTVALUE;
SELECT COL FROM TABLENAME INTO @COLVALUE;
将某个字段或者字段处理后的值放到自定义变量 @COLVALUE中。注意,@COLVALUE要赋初始值,否则会出现NULL值的情况。
*****************************************************
MYSQL中判断是否为NULL值用:col IS NULL 或者 col IS NOT NULL。
*****************************************************
MYSQL中
条件里用“=”而不是“==” 来表示是否等于。
“>=”,“<=”,“>”,“<”,“<>”分别表示“大于等于”,“小于等于”,“大于”,“小于”,“不等于”。
*****************************************************