PostgreSQL和MSSQL的区别
| 序号 | 不同点 | MS | PG | 说明 |
| 1 | delete |
可以省略from 联表删除：delete ta from ta a inner join tb b on |
不可省略from 联表删除：delete from ta a using tb b where |
|
| 2 | insert | 可以省略into | 不可以省略into | |
| 3 | 行数限制 | select top 10 * from | select * from table limit 10 | |
| 4 | 分页 | 使用top或row_number |
使用limit+offset 或row_number 被 |
详见表格下方 |
| 5 | 自增主键插入 |
insert students values('张三') 或insert into students (name)values('张三') |
insert into students values(default,'张三') 或insert into students (name)values('张三') |
|
| 6 | 创建表 |
[dbo].[]、nvarchar(n)、varchar(n)、自增主键 |
不带[],字符串类型统一用text、自增主键 |
详见表格下方 |
| 7 | 递归查询 |
with dep as ( |
with recursive as ... 不递归的with语句不用带recursive |
|
| 8 | 存储过程 |
|
语法相对严格 | 修改、调用、声明变量、打印结果均有不同，详见表格下方 |
| 9 | 函数 | 详见表格下方 | ||
| 10 | 索引 | CREATE NONCLUSTERED INDEX Students_Index_name ON Students(name asc)--分非聚集和聚集 | CREATE INDEX Students_Index_name ON Students(name asc)--INDEX前可以带UNIQUE | |
| 11 | 临时表 |
SELECT * INTO #table1 FROM Department --会话级，不跨连接 ##table --跨连接 |
SELECT * INTO temp table1 FROM Department --会话级，不跨连接 SELECT * INTO UNLOGGED table1 FROM Department --不预写日志的物理表，跨连接，相对于普通物理较快，但中断数据库服务会丢失数据 |
|
| 12 | 常用函数 |
GETDATE() LEN DATALENGTH SUBSTRING（‘names’,2,3） 输出 ame 'a'+'b' 输出 ab select stuff((select ','+username from Sys_Users for xml path('')),1,1,'') CHARINDEX('aa','weaac') 输出 3，没找到输出0 datediff(MINUTE, t1, t2) 计算t2-t1的分钟总数 @@ROWCOUNT 获取影响条数 select @@identity --返回自增主键 ISNUMERIC(DptNo)=1 |
current_date、current_time、current_timestamp length array_length(数组,开始位置) 开始位置基于1 SUBSTRING('names' from 2 for 3) 输出 ame 'a'||'b' 输出 ab select string_agg(username,',') as txt from sys_users
position('aa' in 'weaac') 输出 3，没找到输出0 EXTRACT(epoch FROM(t2-t1))/60 分钟总数=总秒数/60 get diagnostics affectCount=row_count;--affectCount事先声明 returning 主键列名 --返回自增主键 DptNo~'^([0-9]+[.]?[0-9]*|[.][0-9]+)$' |
|
| 13 | 大小写 |
不区分 |
区分 |
|
| 14 | bool |
可以用0和1填值 |
用false和true填值 |
|
| 15 | 默认值 |
ISNULL(col1,0) 如果col1为空返回0否则返回col1 |
COALESCE(col1,0) 可以有多个参数，返回第一个不为空的值，都为空则返回空 |
分页查询
1.MSSQL
1>使用top
string.Format(@"SELECT TOP {6} * FROM {3} WHERE {0} NOT IN ( SELECT TOP {6}*({5}-1) {0} FROM {3} WHERE 1=1 {4} ORDER BY {1} {2} )
WHERE 1=1 {4} ORDER BY {1} {2} ", key, orderField, orderType, name, where, page, pageSize)
2>使用row_number
string.Format(@"SELECT * INTO #T FROM (SELECT {0}, ROW_NUMBER() over(order by {1} {2}) nr FROM {3} where 1=1 {4}) A
WHERE nr between (({5} - 1) * {6} + 1) and ({5} * {6}) select V.* from #T INNER JOIN {3} V ON #T.{0}=V.{0} ORDER BY V.{1} {2}", key, orderField, orderType, name, where, page, pageSize)
2.PGSQL
select * from students where 1=1 order by id limit 10 offset 10*3 ;--3是pageindex，基于0，10是pagesize
创建表
1.MSSQL
CREATE TABLE [dbo].[Department]( [DptID] [int] IDENTITY(1,1) NOT NULL, [DptParentID] [int] NOT NULL, [DptParentNo] [varchar](30) NULL, [DptParentName] [nvarchar](200) NOT NULL, [DptNo] [varchar](30) NOT NULL, [DptName] [nvarchar](200) NOT NULL CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ( [DptID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
2.PGSQL
CREATE TABLE Department( DptID serial primary key, DptParentID int NOT NULL, DptParentNo text NULL, DptParentName text NOT NULL, DptNo text NOT NULL, DptName text NOT NULL)
存储过程
1.MSSQL
--修改用ALTER代替CREATE;如果返回值是整型，可以用DECLARE+RETURN代替OUTPUT CREATE PROC P_GetMaxNo(@param1 int, @param2 nvarchar(20) OUTPUT) AS BEGIN --DECLARE @maxNo INT=0; IF((SELECT COUNT(1) FROM Department)>0) SELECT @param2=MAX(DptNo) FROM Department; ELSE SET @param2='0'; --RETURN @maxNo --PRINT @param2 END
--调用
declare @param1 int=0,@param2 nvarchar(10) execute P_GetMaxNo @param1,@param2 OUTPUT
2.PGSQL
--RETURN 用于提前结束 CREATE OR REPLACE PROCEDURE P_GetMaxNo(inout param1 int,inout param2 text) language plpgsql AS $$ BEGIN IF EXISTS(SELECT * FROM Department) THEN param2=(SELECT MAX(DptNo) FROM Department);
--elseif 条件 then ...; ELSE param2='0';--分号不能省 END IF;--不能省略 END; $$; --调用 DO $$ DECLARE param1 int; DECLARE param2 text;--多个参数必须分开声明 BEGIN CALL P_GetMaxNo(param1,param2); RAISE NOTICE 'param2=%', param2; END $$;
--或者这样调用 CALL public.p_getmaxno(1, 'a')
函数
1.MSSQL
CREATE FUNCTION Fun_Test(@param1 int,@param2 nvarchar(20)) RETURNS INT AS BEGIN DECLARE @i INT=0; SET @i = @param1*DATALENGTH(@param2) RETURN @i; END --调用 SELECT DBO.Fun_Test(2,'test');
2.PGSQL
CREATE OR REPLACE FUNCTION Fun_Test(param1 int,param2 text) returns setof int language plpgsql AS $$ DECLARE i int=0; BEGIN i=param1 * length(param2); return next i; return; END $$; --调用 select Fun_Test(2,'test');
