编程修炼之sql(RDMS)---Oracle，sqlserver，mysql数据库类型差异与部分语句差异

最新推荐文章于 2024-08-13 16:00:46 发布

夜里慢慢行456

最新推荐文章于 2024-08-13 16:00:46 发布

阅读量250

点赞数

分类专栏： java 其他

本文链接：https://blog.csdn.net/u013257767/article/details/107844090

版权

java 同时被 2 个专栏收录

100 篇文章 2 订阅

订阅专栏

其他

29 篇文章 1 订阅

订阅专栏

文章目录

数据类型
DDL
DML
分页简谈

数据类型

Oracle 数据类型	SQL Server 数据类型	Mysql数据类型	是否备用
BFILE	VARBINARY(MAX)		是
BLOB （4GB）	VARBINARY(MAX)BLOB, LONGBLOB, MEDIUMBLOB		是
CHAR([1-2000])	CHAR([1-2000])	CHAR	是
CLOB （4GB）	VARCHAR(MAX)	TEXT, LONGTEXT, MEDIUMTEXT 用来保存255 到65535个字符	是
DATE	DATETIME	DATE,DATETIME, TIME, TIMESTAMP	是
FLOAT	FLOAT	REAL,DECIMAL, DOUBLE, DOUBLE PRECISION	否
FLOAT([1-53])	FLOAT([1-53])	REAL,DECIMAL, DOUBLE, DOUBLE PRECISION	否
FLOAT([54-126])	FLOAT	REAL,DECIMAL, DOUBLE, DOUBLE PRECISION	否
INT	NUMERIC(38)	INT - 2147483647 到2147483648	是
INTERVAL	DATETIME		是
LONG	VARCHAR(MAX)		是
LONG RAW	IMAGE		是
NCHAR([1-1000])	NCHAR([1-1000])		否
NCLOB	NVARCHAR(MAX)		是
NUMBER	FLOATINT, INTEGER, NUMERIC, YEAR, MEDIUMINT	是
NUMBER([1-38])	NUMERIC([1-38])	SMALLINT, TINYINT, BIGINT	否
NUMBER([0-38],[1-38])	NUMERIC([0-38],[1-38])		是
NVARCHAR2([1-2000])	NVARCHAR([1-2000])	SET, VARCHAR, ENUM	否
RAW([1-2000])	VARBINARY([1-2000])	BLOB,BIT, TINYBLOB, LONGTEXT, LONGBLOB, MEDIUMBLOB, MEDIUMTEXT	否
REAL	FLOAT		否
ROWID	CHAR(18)		否
TIMESTAMP	DATETIME	timestamp类型为4个字节，记录的是从1970-01-01 00:00:00开始计算的秒数。故只能记录至2038-01-19 11:14:07这个时间点	是
UROWID	CHAR(18)		否
VARCHAR2([1-4000])	VARCHAR([1-4000])	TEXT, TINYTEXT	是

DDL

操作	mysql	sql server	oracal
create
drop
trucate
alter			alter table monthly_indicator rename to mi;

DML

操作	mysql	sql server	oracal
update
insert
select	模糊匹配与特殊字符 lower(CONCAT(CONCAT(’%’,？),’%’)) ESCAPE ‘/’ param.replace("", “/”)	lower(ETLPROCESS_CODE) LIKE lower(:ETLPROCESS_CODE) 参数： ‘%’ + etlProcessModel.getCode().replace("", "[]") + ‘%’	表进行别名时，可以省略 as 模糊匹配与特殊字符 lower(CONCAT(CONCAT(’%’,？),’%’)) ESCAPE ‘/’ param.replace("", "/")
分页查询	limit (pageIndex-1),pageSize	三重循环；利用max（主键）;利用row_number关键字，offset/fetch next关键字	Oracle数据库没有limit函数，限制查询结果行数，采用伪列rownum进行
时间类型(字符串输入格式比较)	DATE_FORMAT(T1.ETLPROCESS_PUBLISHTIME,’%Y-%m-%d’) >= :STARTDATE	ETLPROCESS_PUBLISHTIME >= :STARTDATE	TO_CHAR(T1.ETLPROCESS_PUBLISHTIME,‘yyyy-MM-dd HH24:mi:ss’)

模糊查询
SQL 通配符
在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。
SQL 通配符必须与 LIKE 运算符一起使用。
在 SQL 中，可使用以下通配符：

在where子句中，通配符可与like条件一起使用。在Oracle中：
%(百分号)：用来表示任意数量的字符，或者可能根本没有字符。
_(下划线)：表示确切的未知字符。
?(问号)：用来表示确切的未知字符。
#(井号)：用来表示确切的阿拉伯数字，0到9。
[a-d](方括号)：用来表示字符范围，在这里是从a到d。

单引号(’)：在Oracle中，应该只使用单引号将文本和字符和日期括起来，不能使用引号（包括单双引号）将数字括起来。

双引号(")：在Oracle中，单双引号含义不同。双引号被用来将包含特定字符或者空格的列别名括起来。双引号还被用来将文本放入日期格式。

撇号(’)：在Oracle中，撇号也可以写成彼此相邻的两个单引号。为了在供应商名字中间查找所有带撇号的供应商名字，可以这样编写代码：
select * from l_suppliers where supplier_name like ‘%’’%’

&符号：在Oracle中,&符号常用来指出一个变量。例如，&fox是一个变量，稍微有点不同的一种&& fox。每当&fox出现在Oracle脚本中时，都会要求您为它提供一个值。而使用&&fox，您只需要在& &fox第一次出现时为它提供变量值。如果想将&符号作为普通的符号使用，则应该关闭这个特性。要想关闭这个特性，可以运行以下的命令： set define off ，这是一个SQLplus命令，不是一个SQL命令。SQLplus设置了SQL在Oracle中运行的环境。

 双竖线（||）：Oracle使用双竖线表示字符串连接函数。
 星号(*)：select *意味着选择所有的列，count（*）意味着计算所有的行，表示通配符时，表示0个或任意多个字符。
 正斜杠(/)：在Oracle中，用来终止SQL语句。更准确的说，是表示了“运行现在位于缓冲区的SQL代码”。正斜杠也用作分隔项。
 多行注释：/*......*/。
 不等于：有多种表达方式：!=、^=、<>、not xxx=yyy、not(xxx=yyy)

Oracle、MySQL、SQL Server数据库的数据类型的差异

分页简谈

上面谈到分页，一般的小数据量分页查询可能遇到的极端情况较小，但是如果说我们用limit offset这种结构来进行分页，在查询到的数据量大于数据库服务器内存上限时会出问题。
那么基于索引的查询：

select * from table_name where id>9000000 limit 10;

但是这种设计的前提时有id自增的聚簇索引，数据库能有效地利用索引提高查询效率。

操作	mysql	sql server
查询数据库中的所有数据库名	show databases;	SELECT Name FROM Master…SysDatabases ORDER BY Name
查询某个数据库中所有的表名	SHOW TABLES;	SELECT Name FROM SysObjects Where XType=‘U’ ORDER BY Name
查询表结构信息	desc tableName;	SELECT (case when a.colorder=1 then d.name else null end) 表名,a.colorder 字段序号,a.name 字段名,(case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity’)=1 then ‘√’else ‘’ end) 标识,(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = ‘PK’))>0 then ‘√’ else ‘’ end) 主键,b.name 类型,a.length 占用字节数,COLUMNPROPERTY(a.id,a.name,‘PRECISION’) as 长度,isnull(COLUMNPROPERTY(a.id,a.name,‘Scale’),0) as 小数位数,(case when a.isnullable=1 then ‘√’else ‘’ end) 允许空,isnull(e.text,’’) 默认值,isnull(g.[value], ’ ') AS [说明] FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype=‘U’ and d.name<>‘dtproperties’ left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id left join sys.extended_properties f on d.id=f.class and f.minor_id=0 where b.name is not null order by a.id,a.colorder