本文主要是介绍编程修炼之sql(RDMS)---Oracle，sqlserver，mysql数据库类型差异与部分语句差异，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
数据类型
| 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 | oracal | postgresql |
|---|---|---|---|---|
| 查询数据库中的所有数据库名 | 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 |
这篇关于编程修炼之sql(RDMS)---Oracle，sqlserver，mysql数据库类型差异与部分语句差异的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
