sqlserver、达梦、mysql的差异
持续更新
| 差异项 | sqlserver | 达梦 | mysql |
| 单行注释 | -- | -- | 1、-- ，--后面带个空格 2、# |
| 包裹对象名称，如表、表字段等 | [tableName] | "tableName" | `tableName` |
| 表字段自增 | IDENTITY(1, 1) | IDENTITY(1, 1) | AUTO_INCREMENT |
| 二进制数据类型 | IMAGE | IMAGE、BLOB | BLOB |
| 存储一个汉字需要定义的varchar长度 | varchar(2) | varchar(3) | varchar(1) |
| 判断是否为NUL | isnull(id, 0) | isnull(id, 0) | ifnull(id, 0) |
| 获取当前日期/时间 | select getdate() | 1、select getdate(); 2、select now(); | SELECT NOW(); |
| 类型转换convert，将日期转换为字符串，返回yyyy-mm-dd | select CONVERT(varchar(10), getdate(), 121) | 1、select SUBSTR(CONVERT(varchar(30), getdate()), 1, 10) 2、select SUBSTR(CONVERT(varchar(30), now()), 1, 10) | SELECT CONVERT(NOW(), CHAR(10)) |
| 判断字符串中是否包含某些字符 | charindex('字符', '全部字符串') | 1、instr('全部字符串', '字符') 2、regexp_like('全部字符串', '字符') | INSTR('全部字符串', '字符') |
| 字符串拼接操作符 | + select 's1' + 's2' + 's3' | || SELECT 's1' || 's2' || 's3'; | CONCAT SELECT CONCAT('s1', 's2', 's3'); |
| 求字符串长度 | select len('abc') | 1、select len('abc')； 2、select length('abc'); | SELECT LENGTH('abc'); |
| 执行语句块，变量定义、赋值、运算、查询等 | declare @i int, @j int, @k int set @i = 1 set @j = 2 set @k = @i + @j select @k as k | DECLARE i,j,k INT; begin set i = 1; set j = 2; set k = i + j; select k as k; end; | SET @i = 1; SET @j = 2; SET @k = @i + @j; SELECT @k AS k; |
| 执行/调用存储过程 | exec proc_GetMenu 'manager' | 1、proc_GetMenu('manager'); 2、call proc_GetMenu('manager'); 3、exec proc_GetMenu 'manager'; 4、execute proc_GetMenu 'manager'; | CALL proc_GetMenu('manager'); |
| 执行/调用有out参数的存储过程 | 可以省略out参数 exec proc_GetReportPrintData 1, '', '', 1 | 可以省略out参数 CALL proc_GetReportPrintData(1, '', '', 1); | 不能省略out参数 CALL proc_GetReportPrintData(1, '', '', 1, @outValue); |
| 指定查询返回一条数据 | select top 1 * from employee | select top 1 * from employee | SELECT * FROM employee LIMIT 1 |
| delete | 1、delete Employee where emp_ID = 'userId' 2、delete from Employee where emp_ID = 'userId' | 1、delete Employee where emp_ID = 'userId'; 2、delete from Employee where emp_ID = 'userId'; | DELETE FROM Employee WHERE emp_ID = 'userId'; |
| if…else语句 | declare @age int; set @age = 18; if @age < 18 begin select '小于18' AS msg; end else begin select '已满18' as msg; end; | declare age int; begin set age = 18; if age < 18 begin select '小于18' AS msg; end else begin select '已满18' as msg; end; end; | 不能用于会话，只能用于存储过程、存储函数、触发器中。 |
| 临时表 | create table #tempTable(id int, xm varchar(20)) 表名必须以#开头 | CREATE GLOBAL TEMPORARY TABLE tmpTable(id int, xm varchar(30)); | CREATE TEMPORARY TABLE tmpTable(id INT, xm VARCHAR(10)); |
| 建表主键的关键语句 | CONSTRAINT [PK_name] PRIMARY KEY CLUSTERED ( [ID] ASC ) | NOT CLUSTER PRIMARY KEY("ID") | PRIMARY KEY (`id`) |