ORACLE/MYSQL/MSSQL语法的不同之处

最近在研究Oracle/MySQL/MSSQL三种数据库的数据增删改。以此记录一些使用感受。

转义符

每种数据库都有自己的关键字，比如view。当使用关键字作为表名时，需要使用转义符对关键字进行处理后才能创建对应的表，列名同理。

• MSSQL中的转义符为[]
• Oracle中的转义符为"
• MySQL中的转义符为```

大小写

MSSQL和MySQL默认对大小写不敏感，所以在写这两个数据库的查询语句时，表名和列名不论大小写都支持。

Oracle是大小写敏感的，默认情况下，表名和列名都是大写。但是一旦用转义符"引用表名或列名，此时就要注意表名和列名的大小写了。

临时表

MSSQL

MSSQL中的临时表分为两种，#开头的为局部临时表，##开头的为全局临时表。它不存储在当前数据库内，而是存储在系统数据库tempdb里。

局部临时表只在当前会话中创建和访问，当退出当前会话后，局部临时表就不存在了。

全局临时表一旦创建后，任何用户都可以连接访问。当创建这个临时表会话的用户中断连接后，且没有其他用户访问这个临时表，全局临时表才会被删除。

一般情况下我们会先判断临时表是否存在，如果已存在则清空，如果不存在则创建。

MSSQL语法

IF OBJECT_ID(N'tempdb.dbo.#temp_table') IS NOT NULL
	BEGIN
		TRUNCATE TABLE #temp_table;
	END
ELSE
	BEGIN
		SELECT * INTO #temp_table FROM source_table;
	END

Oracle

Oracle中的临时表有点不同，一旦创建后它的表结构会保留，不会随着用户连接断开而自动销毁。只是它存在的空间为临时的表空间，而非用户的表空间。Oracle临时表支持会话以及事务级别，只对当前的会话或事务可见。每个会话只能查看和修改自己的数据。

• ON COMMIT DELETE ROWS

这是临时表的默认参数，表示临时表中的数据仅在事务过程中有效。当事务提交后，临时表的数据将被自动TRUNCATE。

• ON COMMIT PRESERVER ROWS

临时表的内容可以跨事务存在，只有当会话结束时，临时表的数据才会被丢弃。

以上两种临时表，在事务或会话结束后，它的结构和元数据都还存储在用户的数据字典中。所以，一旦使用完临时表，最好立即删除，否则数据库残留很多临时表的表结构和元数据。

Oracle创建临时表的语法

CREATE  GLOBAL TEMPORARY TABLE temp_table (...) ON COMMIT DELETE/PRESERVE ROWS

需要注意的是，如果使用

SELECT * INTO temp_table FROM source_table;

来创建临时表的话，如果列为字符串类型，默认情况下，临时表对应的列的长度只有2。

MySQL

MySQL中的临时表和MSSQL的临时表功能的类似，只是创建临时表的语法不同

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table(...);

Insert语法

三个数据库都可以通用

INSERT INTO target_table(target_col1, target_col2, ...) SELECT (source_col1, source_col2, ...) FROM source_table;

Update语法

MSSQL

UPDATE target_table SET ... FROM source_table WHERE condition;

Oracle

UPDATE target_table SET (target_col1, target_col2, ...) = (SELECT source_col1, source_col2, ... FROM source_table WHERE condition);

MySQL

UPDATE target_table INNER JOIN source_table ON condition SET ...;

DELETE语法

三个数据库都可以通用

DELETE FROM target_table WHERE EXISTS (SELECT * FROM source_table WHERE condition);

分页

MSSQL使用TOP关键字进行条数限制查询。语法为

SELECT TOP 1000 * FROM table;

Oracle使用ROWNUM关键字进行条数限制查询。语法为

SELECT * FROM table WHERE ROWNUM <= 1000;

MySQL使用LIMIT以及OFFSET关键字进行条数限制查询。语法为

SELECT * FROM table LIMIT 100 OFFSET 100