SQL Server、 My SQL、PG Sql、Oracle、 Access 不同数据库sql差异
MS SQL Server | My SQL | PG SQL | Oracle | Access | |
| 自增 | identity(1,1) | auto_increment ALTER TABLE 'tableName' AUTO_INCREMENT=100 | |||
| 日期 | getdate(),getutcdate() | now(),utc_timestamp() | |||
| 执行储存过程 | exec(ute) | call | |||
| 存储过程跳出 | return | 不能使用return | |||
| 字符串相加 | '1'+'2' | concat('1','2') | || 或 concat | ||
| dual表 | x | select 1 (from dual where 1>0) | select 1 from dual | x | |
| update行数据 | 同时 | 更新依次从左到右 | 同时 | ||
| 通配符 | % 任意长度，_ 单个字符 | % 任意长度，_ 单个字符 | % 任意长度，_ 单个字符 | * 任意长度，? 单个字符 | |
| 取模 | 7%6 | 7%6 | MOD(7,6) | 7 MOD 6 | |
| 星期几 | DATEPART(DW,date) | DAYOFWEEK(data) | TO_CHAR(date,'D') | DatePart('w',date) | |
| 自然对数 | LOG | LN | LN | LOG | |
| True | 1 | 1 | 1 | -1 | |
| 唯一码 | NewID() | UUID() | SYS_GUID() | ||
| 后n行 | 借助top | offset n | 借助rownum | ||
| 更改表名 | EXEC sp_rename 'table1', 'table2'; | rename table table1 to table2; | alter table table1 rename to table2 | ||
| 更改字段名 | EXEC sp_rename N'dbo.test_tab.val', N'val2', 'COLUMN' | alter table t change physics physisc char(10); | |||
| 使用关键字 | [type] | "type" | |||
| 默认时间 | datetime getDate() | TIMESTAMP CURRENT_TIMESTAMP() | |||
| between and范围 | 0<=x<=1 | 0<=x<=1 | |||
| 参数符号 | @id | ?id | :id | ? | |
| 列信息 | SELECT c.name AS ColumnName , ISNULL(TYPE_NAME(c.system_type_id), t.name) AS DataType , COLUMNPROPERTY(c.object_id, c.name, 'charmaxlen') AS DataLength , c.is_nullable AS IsNullable , g.[value] AS Description FROM sys.columns c INNER JOIN sys.objects o ON c.object_id = o.object_id AND o.type = 'U' LEFT JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT JOIN sys.extended_properties g ON c.object_id = g.major_id AND c.column_id = g.minor_id WHERE o.name = 'a'; | show full columns in {0}; | select distinct column_name, format_type(atttypid, atttypmod) dataType, is_nullable, description, (pg_attribute.attnum =Any(pg_constraint.conkey)) isPrimaryKey, col.ordinal_position from pg_class c inner join information_schema.columns col on c.relname=col.table_name left join pg_description d on c.oid=d.objoid and col.ordinal_position =d.objsubid left join pg_constraint on pg_constraint.conrelid = c.oid and pg_constraint.contype='p' left join pg_attribute on pg_attribute.attrelid = c.oid and pg_attribute.attname=col.column_name where c.relname='{0}' order by ordinal_position; | ||
| 修改列默认值 | ALTER TABLE dbo.T_Product ADD CONSTRAINT DF_T_Product_ProductSort DEFAULT 0 FOR ProductSort | ||||
| 增加列 | alter table tb add column `t` varchar(20) after `a` | ||||
| 查询表是否存在 | --如果是实表可以用 if exists (select * from sysobjects where id = object_id(N'[dbo].[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[表名] --如果是临时表可以用 if object_id('tempdb..##temp') is not null drop table ##temp | ||||
| 删除索引 | DROP INDEX if exists index_name ON table_name | ALTER TABLE table_name DROP INDEX index_name | DROP INDEX index_name | DROP INDEX index_name ON table_name | |
| 复制表结构 | select * into newTable from table where 1=0 | create table newTable like table | |||
| 库存在 | if exists(select * from master..sysdatabases where name=N'库名') | ||||
| 存储过程是否存在 | if exists(select 1 from sysobjects where 所有者.存储过程名') and xtype='P')
print '存在' else print '不存在' | ||||
| 视图存在 | --SQL Server 2000
IF EXISTS (SELECT * FROM sysviews WHERE object_id = '[dbo].[视图名]' --SQL Server 2005 IF EXISTS (SELECT * FROM sys.views WHERE object_id = '[dbo].[视图名]' | ||||
| 函数名是否存在 | if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[函数名]') and xtype in (N'FN', N'IF', N'TF')) | ||||
| 修改表空间 | ALTER SCHEMA NewName TRANSFER dbo.table |
