Oracle转MySql常用
时间:2022-03-14 01:05
int int number(6,2) decimal(6,2) date (sysdate) timestamp (now()) varchar(20) 2.常用函数 3.分页 从第10条记录开始检索20条记录 以下语句是先排序后的分页(很好) order只能这么实现(没有mysql方便) 4.自定义序列函数(来源 ) 本文出自 “” 博客,请务必保留此出处varchar2(20) /* DATE_FORMAT(date,format) 根据format字符串格式化date值 */
select date_format(‘2013-09-21 14:01:20‘,‘%Y-%m-%d %H:%i:%s‘) from dual;
select case 2 when 1 then 1 when 2 then 2 else 0 end from dual;
select IFNULL(null,0) from dual;
select * from sys_option limit 10,20;
select * from table [查询条件] order by id limit ?,?;
SELECT * FROM test_yutong ORDER BY ID DESC LIMIT 0,1;
SELECT * FROM(SELECT * FROM TABLE_A ORDER BY ID DESC)B WHERE ROWNUM=1;
/* CREATE TABLE Sequence */
DROP TABLE IF EXISTS Sequence;
CREATE TABLE Sequence
(
name VARCHAR(32),
maxIndex INTEGER,
CONSTRAINT cst_Sequence_pk PRIMARY KEY (name, maxIndex)
);
/* FUNCTION nextval */
SET GLOBAL log_bin_trust_function_creators = 1;
DROP FUNCTION IF EXISTS nextval;
DELIMITER //
CREATE FUNCTION nextval(sequenceName VARCHAR(32))
RETURNS INTEGER
NOT DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE iMaxIndex INTEGER;
UPDATE Sequence SET maxIndex = maxIndex + 1 WHERE name = sequenceName;
SELECT maxIndex INTO iMaxIndex
FROM Sequence WHERE name = sequenceName;
RETURN iMaxIndex;
END
//
DELIMITER ;
/* 测试例子 */
INSERT INTO Sequence(NAME,maxIndex)VALUES(‘CK10_GHDJ‘,1);
SELECT nextval(‘CK10_GHDJ‘)FROM DUAL;
SELECT * FROM Sequence;