数据库—关系型数据库的差异(持续更新中)

关系型数据库之间的差异

一、MySQL ，SQL Server ，Oracle 常用部分差异

1.保留字作为字段？

在开发中不建议使用SQL语言的保留字作为字段，这样极容易出现混淆或错误。如果有特殊场景需要这样做，你可以这样做:

• MySQL：将关键字用反单引号(`)包围。例：`key`。
• SQL Server：将关键字用中括号([)包围。例：[key]。
• Oracle：将关键字用双引号(")包围。例：“key”。

2.在sql语句中将查询到的字段类型转换成你想要的类型？

在开发中数据库表字段类型先确定好了，这时有场景需要其它类型时可在数据库中转换数据类型：

• MySql：CONVERT(字段, 类型)，CAST(字段 AS 类型)

-- covert()
SELECT CONVERT(data_type(length), column_name) FROM table_name;

-- cast()
SELECT CAST(column_name AS data_type) FROM table_name;

• SQL Server：CONVERT(类型, 字段，格式(可选)) ,这里格式可选,比如时间哪种格式，CAST(字段 AS 类型)

-- convert()
SELECT CONVERT(data_type(length), column_name, style) FROM table_name;

-- cast()
SELECT CAST(字段 AS 类型) FROM table_name;

• Oracle：CAST(字段 AS 类型)，TO_NUMBER(), TO_DATE(), TO_CHAR()等

-- cast()
SELECT CAST(字段 AS 类型) FROM table_name;

// 将字符串转换成日期
SELECT TO_DATE(date_string, 'YYYY-MM-DD') FROM table_name; 

3.拼接字符串？

在开发中也经常遇见查询出字段需要加单位的或者拼接字符串来满足业务需要，方法如下：

• MySQL：CONCAT()，CONCAT_WS()

-- CONCAT()没有NULL值
SELECT CONCAT('Hello', ' ', 'World') AS String; // 结果：'Hello World'

-- CONCAT()有NULL值
SELECT CONCAT('Hello', NULL, 'World') AS String; // 结果：NULL

-- CONCAT_WA()
SELECT CONCAT_WS('-', 'MySQL', NULL, 'is', 'awesome') AS String;  // 结果：'MySQL-is-awesome' 自动忽略NULL

在MySQL 中 CONCAT()可以连接两个或多个字符串，也可以连接可以隐式转换成字符串类型的数据。需要注意参数为NULL时，结果都为NULL，可以将NULL值转换后进行拼接。也可以使用CONCAT_WS()函数来在字符串之间插入分隔符，并且自动忽略NULL值。

• SQL Server：+，CONCAT()

SELECT 'Hello' + ' ' + 'World' AS String;
SELECT CONCAT('SQL Server', NULL, 'is', 'great') AS String;

在SQL Server中，可以使用+操作符或CONCAT()函数来连接字符串。与MySQL的CONCAT()函数类似，SQL Server的CONCAT()函数会自动忽略NULL值，并允许将两个或多个字符串值连接成一个字符串，也可以连接可以隐式转换成字符串类型的数据。

• Oracle：||，CONCAT()

SELECT 'Oracle' || ' ' || 'Database' AS String FROM DUAL;
SELECT CONCAT(CONCAT('Oracle', ' '), 'Database') AS String FROM DUAL;

1.在Oracle中，也可以使用||操作符或CONCAT()函数来连接字符串。然而，Oracle的CONCAT()函数只接受两个参数，也可以连接可以隐式转换成字符串类型的数据，如果你需要连接多个字符串，需要嵌套使用CONCAT()或使用||操作符。
2.在Oracle中 dual 确实是一张表.是一张只有一个字段,一行记录的表。一般,我们称之为’伪表’，因为他不存储主题数据。他的存在,是为了操作上的方便，因为select 都是要有特定对象的。

4.字段自增？

在开发中，表设计主键通常使用自增方式，方便索引查询使用。方法如下：

• MySQL：AUTO_INCREMENT，通常和主键一起使用

CREATE TABLE example (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

在MySQL中，你可以使用AUTO_INCREMENT属性来设置自增字段。这通常与主键一起使用，并且只能有一个自增字段。

• SQL Server：IDENTITY，也通常和主键一起使用

CREATE TABLE example (
    id INT IDENTITY(1,1),
    name VARCHAR(100),
    PRIMARY KEY (id)
);

• Oracle：Identity Columns新特性自增(oracle版本>=12) ，sequence序列 ，trigger触发器

// 方案一：新特性
CREATE TABLE user_info (
	id number(11) generated by default as IDENTITY,// generated by default as IDENTITY 自增功能
	name varchar2(20) ,
  	age number(3)
)

// 方案二：sequence序列  
create sequence seq_test  // 设置自增序列，名称为"seq_test"，名字任意命名
 increment by 1		--每次+1	
 start with 1		--从1开始
 nomaxvalue			--不限最大值
 nominvalue			--不限最小值
 cache 20;			--设置取值缓存数为20
// 创建user_info表
CREATE TABLE user_info (
  id number(11)  DEFAULT seq_test.nextval, // "seq_test"为自增序列名称
  name varchar2(20) ,
  age number(3)
);

// 方案三：使用触发器
// 创建user_info表
CREATE TABLE user_info (
  id number(11) not null,
  name varchar2(20) ,
  age number(3)
);
// 创建触发器，名称为"test_TRIGGER"，名字任意命名
create or replace trigger test_TRIGGER
before insert on user_info	// "user_info"为表名称
for each row
begin
select seq_test.nextval into :new.id from dual;	// 1、"seq_test"为自增序列名称 2、这里的id是你需要自增的序列
end test_TRIGGER;	// "test_TRIGGER"为触发器名称

Oracle数据库不直接支持像MySQL或SQL Server那样的自增字段。但是，你可以通过序列（SEQUENCE）和触发器（TRIGGER）的组合来实现类似的功能。

5.字段类型区别？

不同数据库中字段类型也有差异，主要类型差别如下：

1. 整数类型：

MySQL：tinyint、smallint、mediumint、int/integer、bigint等整数类型。
SQL Server：bit（表示0或1）、tinyint、smallint、int、bigint等。其中，bit类型在SQL Server中特别用于表示布尔值。
Oracle：整数类型包括 NUMBER（可以指定精度和标度，但通常用于小数）、PLS_INTEGER 以及 BINARY_INTEGER(与PLS_INTEGER相同，但在某些上下文中具有特殊含义)Oracle还提供了NUMBER类型用于存储整数和小数，但通常不直接用于表示纯整数类型。

2. 浮点类型：

MySQL：提供了float、double和decimal类型来表示小数。FLOAT是单精度浮点数，DOUBLE是双精度浮点数，而DECIMAL是定点数，用于需要高精度计算的场景。
SQL Server：小数类型包括REAL（单精度浮点数）和FLOAT（双精度浮点数），以及DECIMAL和NUMERIC（两者在SQL Server中几乎相同，都是定点数）。
Oracle：使用NUMBER类型来表示小数，可以指定精度和标度来满足不同的需求。

3. 字符串类型：

MySQL：字符串类型包括char、varchar、text、longtext等。char是固定长度的字符串，varchar是可变长度的字符串，text用于存储大量文本,longtext用于存储更大文本。
SQL Server：字符串类型包括char、varchar、text（但在较新版本中已被nvarchar(MAX)等类型取代）、nchar、nvarchar等。nchar和nvarchar用于存储Unicode字符。
Oracle：提供了char、varchar2、nchar、nvarchar2等字符串类型。varchar2是Oracle中最常用的可变长度字符串类型，而nchar和nvarchar2用于存储Unicode字符。

4. 日期类型：

MySQL：日期和时间类型包括date、time、datetime、timestamp 等。
SQL Server：日期和时间类型包括date、time、datetime、datetime2、smalldatetime 等。
Oracle：使用date类型来存储日期和时间（包括年、月、日、时、分、秒），以及timestamp 类型来存储更高精度的日期和时间值。

6.对数据判空和空值替换？

• MySQL：IS NULL或IS NOT NULL操作符来检查字段是否为空。‌IFNULL()和COALESCE()函数可以用来替换NULL值。

-- is null 和 is not null 用法几种sql一致，以下不多做解释
SELECT * FROM table_name WHERE column_name IS NULL;
SELECT * FROM table_name WHERE column_name IS NOT NULL;

-- 替换空值用法
SELECT IFNULL(column_name, 'replacement_value') FROM table_name; 

SELECT COALESCE(column_name, 'replacement_value') FROM table_name; 

• SQL Server：IS NULL或IS NOT NULL来检查字段是否为空。‌ISNULL()和COALESCE()函数可以用来替换NULL值。

-- ISNULL()
SELECT ISNULL(column_name, 'replacement_value') FROM table_name;

-- COALESCE()
SELECT COALESCE(column_name, 'replacement_value') FROM table_name;

• Oracle：IS NULL或IS NOT NULL来检查字段是否为空。‌NVL()和COALESCE()函数可以用来替换NULL值。‌

-- NVL()
SELECT NVL(column_name, 'replacement_value') FROM table_name;

-- COALESCE()
SELECT COALESCE(column_name, 'replacement_value') FROM table_name;

IFNULL() ,ISNULL(),NVL()都是接受两个参数，对单个值判断，为空则返回自己设定的数据；COALESCE()对多个值判断返回第一个不为null的值

7.对时间格式转换？

• MySQL：DATE_FORMAT()，STR_TO_DATE()将字符串转换成时间格式

-- DATE_FORMAT()
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_date;

-- STR_TO_DATE()
SELECT STR_TO_DATE('2024-08-29', '%Y-%m-%d') AS date;

• SQL Server：CONVERT(),FORMAT()

--CONVERT()
SELECT CONVERT(VARCHAR, GETDATE(), 120) AS formatted_date; -- 使用样式代码120，样式代码多种想使用哪种方式可自行搜索

-- FORMAT()
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS formatted_date;

• Oracle：TO_DATE()将日期转换成时间，TO_CHAR()将时间转换成字符串，TO_TIMESTAMP()将字符串时间转换成时间戳

-- TO_CHAR()
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM DUAL;

-- TO_TIMESTAMP()
SELECT TO_TIMESTAMP('2024-08-29 12:00:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp FROM DUAL;

8.计算时间？

• MySQL：DATE_ADD()、‌DATE_SUB()、‌DATEDIFF()

-- 增加时间 DAY位置参数可变month(月)，year(年)
SELECT DATE_ADD('2023-04-01', INTERVAL 1 DAY) AS added_date;

-- 减少时间
SELECT DATE_SUB('2023-04-01', INTERVAL 1 DAY) AS subtracted_date;

-- 计算时间差
SELECT DATEDIFF('2023-04-05', '2023-04-01') AS difference;

• SQL server：

-- 增加时间 DAY位置也可替换month(月)，year(年)
SELECT DATEADD(DAY, 1, '2023-04-01') AS added_date;

-- 计算时间差
SELECT DATEDIFF(DAY, '2023-04-01', '2023-04-05') AS difference;

• Oracle

-- 增加时间 默认是天数 增加月份：ADD_MONTHS(SYSDATE, 12) 减少月份：ADD_MONTHS(SYSDATE, -1)
SELECT TO_DATE('2023-04-01', 'YYYY-MM-DD') + 1 AS added_date FROM DUAL;

-- 减少时间
SELECT TO_DATE('2023-04-01', 'YYYY-MM-DD') - 1 AS subtracted_date FROM DUAL;

-- 计算时间差
SELECT TO_DATE('2023-04-05', 'YYYY-MM-DD') - TO_DATE('2023-04-01', 'YYYY-MM-DD') AS difference FROM DUAL;