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 , |
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 tb add column `t` varchar(20) after `a` | ||||
查询表是否存在 |
--如果是实表可以用 --如果是临时表可以用 |
||||
列是否存在 |
if COL_LENGTH('table name', 'column name') is null |
||||
删除索引 |
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 id=object_id('所有者.存储过程名') 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 |