今天复习数据库的sql，想着需要总结下各种数据库的sql的不同点，供总结查阅，供别人参考！ 返回的记录的数目 1. sql server 的语法： select top number|percent column_name(s) from table_name 2. mysql的语法： select column_name(s) from table_name li
今天复习数据库的sql，想着需要总结下各种数据库的sql的不同点，供总结查阅，供别人参考！
返回的记录的数目1. sql server 的语法：
select top number|percent column_name(s)
from table_name


2. mysql的语法：
select column_name(s)

from table_name


limit number
3. oracle的语法：
select column_name(s)

from table_name


where rownum 注意:oracle rownum不支持>=
· sql unique 约束1. mysql:
create table persons





(





id_p int not null,

lastname varchar(255) not null,





firstname varchar(255),





address varchar(255),





city varchar(255),


unique(id_p)
2. sql server / oracle / ms access:
create table persons




(





id_p int not null unique,
lastname varchar(255) not null,





firstname varchar(255),





address varchar(255),





city varchar(255)


)




3. mysql / sql server / oracle / ms access:
create table persons




(





id_p int not null,

lastname varchar(255) not null,





firstname varchar(255),





address varchar(255),





city varchar(255),


constraint uc_personid unique (id_p,lastname)
)




· sql unique constraint on alter tablemysql / sql server / oracle / ms access:
alter table persons



add unique (p_id)
或

alter table persons



add constraint uc_personid unique (p_id,lastname)
撤销 unique 约束1. mysql:
alter table persons



drop index uc_personid
2. sql server / oracle / ms access:
alter table persons



drop constraint uc_personid
这里只提供unique约束，primary key约束、foreign key约束、check约束类似。
· sql auto increment 字段
1. mysql:
create table persons




(





p_id int not null auto_increment,
lastname varchar(255) not null,





firstname varchar(255),





address varchar(255),





city varchar(255),


primary key (p_id)
)




2. sql server:
create table persons




(





p_id int primary key identity,
lastname varchar(255) not null,





firstname varchar(255),





address varchar(255),





city varchar(255)


)




3.access:
create table persons




(





p_id int primary key autoincrement,
lastname varchar(255) not null,





firstname varchar(255),





address varchar(255),





city varchar(255)


)




4. oracle:
create sequence seq_person
minvalue 1
start with 1
increment by 1
cache 10








insert into persons (p_id,firstname,lastname)
values (seq_person.nextval,'lars','monsen')
null 函数1. sql server/ ms access
select productname,unitprice*(unitsinstock+isnull(unitsonorder,0))
from products



2. oracle
select productname,unitprice*(unitsinstock+nvl(unitsonorder,0))
from products



3.mysql
select productname,unitprice*(unitsinstock+ifnull(unitsonorder,0))
from products







或





select productname,unitprice*(unitsinstock+coalesce(unitsonorder,0))
from products



作者: dxx23