主流数据库常用内容对比

发布时间:2019-09-06编辑:佚名阅读(1591)

1、表名和列名使用时候区分大小写

  • Sqlserver:不区分,和语句保持一致

  • Oracle:设计全部显示为大写,查询结果以大写展示

  • MySql: 设计中将以小写显示,查询以小写展示【lower_case_table_names = 1,】

  • PostgreSql:设计建议小写,查询时候全部按小写 (如果需要区分大小写,需要使用’,如: ‘tableName’)

  • SqlLite:不区分,和语句保持一致

2、视图

  • Sqlserver:支持

  • Oracle:支持

  • MySql:支持

  • PostgreSql:支持

  • SqlLite:支持

3、存储过程

  • Sqlserver:支持

  • Oracle:支持

  • MySql:支持

  • PostgreSql:支持

  • SqlLite:不支持

4、函数

  • Sqlserver:支持

  • Oracle:支持

  • MySql:支持

  • PostgreSql:支持

  • SqlLite:支持

5、分页

Sqlserver:

通用:ROW_NUMBER() OVER()方式

select * from ( 
    select *, ROW_NUMBER() OVER(Order by ArtistId ) AS RowId from ArtistModels 
  ) as b
      where RowId between 10 and 20

备注:---where RowId BETWEEN 当前页数-1*条数 and 页数*条数---

SQL2012以上的版本才支持:offset fetch next方式(:推荐使用 )

select * from ( 
    select *, ROW_NUMBER() OVER(Order by ArtistId ) AS RowId from ArtistModels 
  ) as b
      where RowId between 10 and 20

备注:--order by ArtistId offset 页数 rows fetch next 条数 rows only ----

适应于数据库2012以下的版本:top not in方式

select top 3 * from ArtistModels 
where ArtistId not in (select top 15 ArtistId from ArtistModels)

备注:-where Id not in (select top 条数*页数  ArtistId  from ArtistModels)  

Oracle:

方式一(效率高):

SELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21

方式二: 

SELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
)  
WHERE RN BETWEEN 21 AND 40

MySql:

语法:

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

案例:

select * from orders_history where type=8 order by id limit 10000,10;

PostgreSql:

语法:

select * from persons limit  A  offset  B;

案例:

select * from persons limit 5 offset 0 ;

解释:A就是你需要多少行;B就是查询的起点位置

SqlLite:

案例:

SELECT * FROM Table ORDER BY ID DESC Limit 10,9

解释:limit语义:跳过10行,取9行

6、按日期范围查询

Sqlserver:

select * from table1 where t1>='2017-6-1' and t1<='2017-6-5'

Oracle:

SELECT * FROM TB_NAME t WHERE to_date(,'yyyy-MM-dd HH24:MI:SS')
between to_date('2011-07-01 12:00:00','yyyy-MM-dd HH24:MI:SS') and to_date('20110702 12:00:00','yyyymmdd HH24:MI:SS') ;

MySql:

select fullName,addedTime FROM t_user where addedTime >='2017-1-1 00:00:00'  and addedTime < '2018-1-1 00:00:00';

PostgreSql:

select * from user_info where create_date >= '2015-07-01' and create_date < '2015-08-15';

SqlLite:

select * from table1 where t1>='2017-6-1' and t1<='2017-6-5'

7、模糊查询

Sqlserver:

通配符 含义

1、% 包含零个或更多字符的任意字符串。

2、_(下划线) 任何单个字符。3、

4、[ ] 指定范围(例如 [a-f])或集合(例如 [abcdef])内的任何单个字符。

5、[^] 不在指定范围(例如 [^a - f])或集合(例如 [^abcdef])内的任何单个字符。

6、Like特殊情况:搜索通配符字符   WHERE ColumnA LIKE '%5/%%' ESCAPE '/'

  在 LIKE 子句中,前导和结尾百分号 (%) 解释为通配符,而斜杠 (/) 之后的百分号解释为字符%。

Oracle:

1、%:

SELECT * FROM [user] WHERE uname LIKE ‘%三%’ ;

2、_:单一任何字符(下划线)常用来限制表达式的字符长度语句

SELECT * FROM [user] WHERE uname LIKE ‘三__’;

3、[]:在某一范围内的字符,表示括号内所列字符中的一个(类似正则表达式)

SELECT * FROM [user] WHERE u_name LIKE ‘[张李王]三’ ;

4、[^]: 不在某范围内的字符,用法与[ ]相反

5、在Oracle中提供了instr(strSource,strTarget)函数,比使用’%关键字%’的模式效率高很多。

instr函数也有三种情况:

instr(字段,’关键字’)>0相当于 字段like ‘%关键字%’  ;

instr(字段,’关键字’)=1相当于 字段like ‘关键字%’  ;

instr(字段,’关键字’)=0相当于 字段not like ‘%关键字%’  。

特殊用法: 

select id, namefrom user where instr(‘101914, 104703’, id) > 0;

它等价于 

select id, namefrom user where id = 101914 or id = 104703;

MySql:

1、同Sqlserver和Oracle前4点

2、LOCATE('substr',str,pos)方法

SELECT LOCATE('bar',`foobarbar`);  ###返回4

3、POSITION('substr' IN `field`)方法

SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)

4、INSTR(`str`,'substr')方法

SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0

5、FIND_IN_SET(str1,str2):

SELECT * FROM `person` WHERE FIND_IN_SET('apply',`name`);

PostgreSql:

1、前缀+模糊查询。(可以使用b-tree索引)

select * from tbl where col like 'ab%';

select * from tbl where col ~ '^ab';

2、后缀+模糊查询。(可以使用reverse(col)表达式b-tree索引

select * from tbl where col like '%ab';

select * from tbl where col ~ 'ab$';

写法

select * from tbl where reverse(col) like 'ba%';

select * from tbl where reverse(col) ~ '^ba';

3、前后模糊查询。(可以使用pg_trgm和gin索引)

select * from tbl where col like '%ab%';

select * from tbl where col ~ 'ab';

4、全文检索。(可以使用全文检索类型以及gin或rum索引)

select * from tbl where tsvector_col @@ 'postgres & china | digoal:A' order by ts_rank(tsvector_col, 'postgres & china | digoal:A') limit xx;

5、正则查询。(可以使用pg_trgm和gin索引)

select * from tbl where col ~ '^a[0-9]{1,5}\ +digoal$';

6、相似查询。(可以使用pg_trgm和gin索引)

select * from tbl order by similarity(col, 'postgre') desc limit 10;

7、ADHOC查询,任意字段组合查询

select * from tbl where a=? and b=? or c=? and d=? or e between ? and ? and f in (?);

8、关键字

SELECT * FROM 表名 WHERE 字段 ILIKE regexp_replace(concat('%','关键字','%'),'\\','\\\','g')

SqlLite:

1、同Sqlserver和Oracle前4点

select * from zipcode where (address like'%" & zipcode_key & "%') or (city like'%" & zipcode_key & "%') or (province like'%" & zipcode_key & "%') order by province,city,address

8、字符串连接符

Sqlserver:

1、使用 +

select 'Post'+'gresql'+' good!';

2、使用 CONCAT

SELECT CONCAT('学号:',XNumber,'的综合成绩:',FSalary/(FAge-21))  FROM user

Oracle:

1、使用 ||

select 'Post'||'gresql'||' good!';

2、使用 CONCAT

SELECT CONCAT('学号:',XNumber,'的综合成绩:',FSalary/(FAge-21))  FROM user

MySql:

1、使用 ||,如果是纯数字拼接,不能使用,得出结果为 1||2=3

select 'Post'||'gresql'||' good!';

2、使用 CONCAT

SELECT CONCAT('学号:',XNumber,'的综合成绩:',FSalary/(FAge-21))  FROM user

PostgreSql:

1、使用 ||

select 'Post'||'gresql'||' good!';

2、使用 CONCAT

SELECT CONCAT('学号:',XNumber,'的综合成绩:',FSalary/(FAge-21))  FROM user

SqlLite:

1、使用 ||

SELECT 'I''M '||'Chinese.'

9、主键自增和返回值

Sqlserver:

1、创建:id int identity(1,1),

2、返回值

@@IDENTITY 返回为当前会话的所有作用域中的任何表最后生成的标识值。

SCOPE_IDENTITY 返回为当前会话和当前作用域中的任何表最后生成的标识值

SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是,SCOPE_IDENTITY 只返回插入到当前作用域中的值;@@IDENTITY 不受限于特定的作用域。

Oracle:

1、创建

创建表

Create table t_user(Id number(6),userid varchar2(20),loginpassword varchar2(20),isdisable number(6) );

创建序列

create sequence user_seq increment by 1 start with 1 nomaxvalue nominvalue nocache

创建触发器

create or replace trigger tr_user
before insert on t_user
for each row
begin
select user_seq.nextval into :new.id from dual;
end;

测试

insert into t_user(userid,loginpassword, isdisable) values('ffll','liudddyujj', 0);
insert into t_user(userid,loginpassword, isdisable) values('dddd','zhang', 0) ;
select * from t_user;

2、返回值 select user_seq.CURRVAL from dual

MySql:

1、创建:id int primary key auto_increment
2、返回值:SELECT LAST_INSERT_ID()

PostgreSql:

1、创建:新建字段  并且将字段属性设置为serial
2、新增返回:

insert into point(pointtype,pointx,pointy,pointval)values(1,2,3,4) RETURNING id;

SqlLite:

1、创建:id INTEGER PRIMARY KEY AUTOINCREMENT

2、返回值:

select last_insert_rowid() from person

3、自增自增归零:

DELETE FROM sqlite_sequence WHERE name='TableName';

4、特定字段自增归零:

UPDATE sqlite_sequence SET seq = 0 WHERE name='TableName';

10、变量定义符号

Sqlserver:

1、@

Oracle:

1、:

MySql:

1、@

PostgreSql:

1、: 或 @

SqlLite:

1、@

11、默认时间函数

Sqlserver:

1、创建默认时间: datetime default getdate()

Oracle:

1、创建默认时间:date default sysdate

MySql:

1、创建默认时间: datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;

PostgreSql:

1、创建默认时间:datetime varchar(40) DEFAULT (now())

SqlLite:

1、创建默认时间: datetime default (datetime(current_timestamp,'localtime'))


  关键字:数据库对比


鼓掌

0

正能量

0

0

呵呵

0


评论区