SQLServer、MySQL、Oracle语法差异小集锦

发布日期:2018-02-09    浏览次数:539

一、差异集锦

  在建表的时候,只有自增的语法不同。

  下面给出3种数据库通用的建表与初始化测试语句:

CREATE TABLE Country(
   Id int PRIMARY KEY,
   Name varchar(20)
);

CREATE TABLE Person (  
  Id int PRIMARY KEY,  
  CountryId int,
  Name varchar(20) NOT NULL UNIQUE,  
  Sex int DEFAULT 0,
  CONSTRAINT FK_CID_PID FOREIGN KEY (CountryId) REFERENCES Country(Id)
);

  插入测试数据:

INSERT INTO Country VALUES(1, '蜀国');
INSERT INTO Country VALUES(2, '魏国');
INSERT INTO Country VALUES(3, '吴国');

INSERT INTO Person VALUES(1,1,'刘备',1);
INSERT INTO Person VALUES(2,1,'关羽',1);
INSERT INTO Person VALUES(3,1,'张飞',1);
INSERT INTO Person VALUES(4,2,'曹操',1);
INSERT INTO Person VALUES(5,2,'郭嘉',1);
INSERT INTO Person VALUES(6,2,'典韦',1);
INSERT INTO Person VALUES(7,3,'孙权',1);
INSERT INTO Person VALUES(8,3,'周瑜',1);
INSERT INTO Person VALUES(9,3,'大乔',2);

 

  SQLServer MySQL Oracle
自增 identity(1,1)  AUTO_INCREMENT   SEQUENCE/触发器
自增行插入 INSERT INTO Person VALUES(NULL,1,'魏延',1) INSERT INTO Person VALUES(1,'魏延',1) INSERT INTO Person VALUES(SEQID.NEXTVAL,1,'魏延',1)
取前几条 SELECT TOP 5 * FROM Person  SELECT * FROM Person LIMIT 0,5 SELECT * FROM Person WHERE ROWNUM < 5
表别名 SELECT * FROM Person AS P INNER JOIN Country AS C ON P.CountryId = C.Id SELECT * FROM Person AS P INNER JOIN Country AS C ON P.CountryId = C.Id SELECT * FROM Person P INNER JOIN Country C ON P.CountryId = C.Id
字段名大小写  与SELECT相同 与SELECT相同 Oracle中,字段名都是大写的,即使SELECT语句中是小写,得到的结果也是大写。
获取当前时间&查询临时值  SELECT now() AS NOWDATE SELECT getdate() AS NOWDATE  SELECT sysdate AS NOWDATE FROM Dual--需加Dual虚表
       
       
       
       

  

本文网址:https://www.wyxxw.cn/blog-detail-17-18-287.html

返回列表

非特殊说明,本文版权归原作者所有,转载请注明出处

提示:本站所有资源仅供学习与参考,请勿用于商业用途。图片来自互联网~如侵犯您的权益,请联系QQ:1067507709.

提示:转载请注明来自:http://www.cnblogs.com/kissdodog/p/3938035.html 。 转载人:momo