ORacle语句

 MYSQL/MSSQL/ORACLE数据库脚本代码 收藏
/******************************************************************************/
/*
主流数据库MYSQL/MSSQL/ORACLE测试数据库脚本代码
脚本任务:建立4个表,添加主键,外键，插入数据,建立视图
运行环境1:microsoft sqlserver 2000 查询分析器
运行环境2:mysql5.0 phpMyAdmin网页界面
运行环境3:oracle 9i SQL*PLUS命令行界面
author：chinayaosir
blog:   http://blog.csdn.net/chinayaosir/
QQ:    44633197

声明：严禁其它网站不经过作者chinayaosir同意任意转载

*/
/******************************************************************************/
/*script test ok with microsoft sqlserver 2000 查询分析器 */
/******************************************************************************/
/*DB:MSSQL2000 SCRIPT*/
/*0.drop table list*/
    drop  table Employee;
    drop  table Department;
    drop  table Post;
    drop  table Account;
/*********************************************************/
/*DB:MSSQL2000 SCRIPT*/
/*1.create all table*/
create table Account(
    oid     int  not null,
    username varchar(30) not null,
    password varchar(10)  null,
    invalid    varchar(1)  null          
);
create table bab.Post(
    /*oid     int identity(1,1) not null primary key,*/
    oid     int  not null,
    postName varchar(30) not null
);

create table Department(
    oid     int  not null,
    deptName varchar(30) not null,
    parentid  int  null,
    manager   varchar(30)  null,
    email     varchar(30)  null
);

create table Employee(
    oid     int  not null,
    empName varchar(30) not null,
    postid  int  null,
    deptid    int  null,
    phone   varchar(20)  null,
    birthday varchar(10)  null
);
/*********************************************************/
/*DB:MSSQL2000 SCRIPT*/
/*2.add constraint with primary key */
alter table Account    add constraint Account_pk primary key(oid);
alter table Post    add constraint Post_pk primary key(oid);
alter table Department    add constraint Department_pk primary key(oid);
alter table Employee    add constraint Employee_pk primary key(oid);

/*********************************************************/
/*DB:MSSQL2000 SCRIPT*/
/*3.add constraint with foreign key */
alter table Department    add constraint Department_fk
foreign key(parentid) references Post(oid)
on update cascade on delete cascade;

alter table Employee add constraint Employee_fk1
foreign key(oid) references Account(oid)
on update cascade on delete cascade;

alter table Employee add constraint Employee_fk2
foreign key(postid) references Post(oid);

alter table Employee add constraint Employee_fk3
foreign key(deptid) references Department(oid);
/*********************************************************/
/*DB:MSSQL2000 SCRIPT*/
/*4. inert sample data into tables*/
insert into post(oid,postname) values(1,'office');
insert into post(oid,postname) values(2,'workshop');

insert into Department values(1,'R+D DEPT',1,'zhangshan','zhangshan@126.com');
insert into Department values(2,'SALE DEPT',1,'lishi','lishi@126.com');
insert into Department values(3,'MADE DEPT',2,'wanger','wanger@126.com');

insert into Account values(111,'user01','654123','1');
insert into Account values(112,'user02','963147','1');
insert into Account values(113,'user03','4456','1');

insert into Employee values(111,'smith lee',1,1,'13612345678','1970');
insert into Employee values(112,'ming yang',1,2,'13712345678','1980');
insert into Employee values(113,'san zhang',2,3,'13812345678','1990');
/*********************************************************/
/*DB:MSSQL2000 SCRIPT*/
/*5. create view*/
create view v_alltables
as
select  Employee.oid,
    Employee.empname,
    Account.username,
    Account.password,
    Account.invalid,
    post.postname,
    department.deptname,
    department.manager,
    department.email,
    Employee.phone,
    Employee.birthday
from      Employee,
          Account,
    post,
    department
where    Employee.oid=Account.oid and  
    Employee.postid=post.oid and  
    Employee.deptid=department.oid

/******************************************************************************/
/*script test ok with mysql5.0 phpMyAdmin */
/******************************************************************************/
/*DB:MYSQL SCRIPT*/
/*0.drop table list*/
    drop  table Employee;
    drop  table Department;
    drop  table Post;
    drop  table Account;
/*********************************************************/
/*DB:MYSQL SCRIPT*/
/*1.create all table*/
create table Account(
    oid     int  not null,
    username varchar(30) not null,
    password varchar(10)  null,
    invalid    varchar(1)  null          
);
create table Post(
    /*oid     int identity(1,1) not null primary key,*/
    oid     int  not null,
    postName varchar(30) not null
);

create table Department(
    oid     int  not null,
    deptName varchar(30) not null,
    parentid  int  null,
    manager   varchar(30)  null,
    email     varchar(30)  null
);

create table Employee(
    oid     int  not null,
    empName varchar(30) not null,
    postid  int  null,
    deptid    int  null,
    phone   varchar(20)  null,
    birthday varchar(10)  null
);
/*********************************************************/
/*DB:MYSQL SCRIPT*/
/*2.add constraint with primary key */
alter table Account    add constraint Account_pk primary key(oid);
alter table Post    add constraint Post_pk primary key(oid);
alter table Department    add constraint Department_pk primary key(oid);
alter table Employee    add constraint Employee_pk primary key(oid);

/*********************************************************/
/*DB:MYSQL SCRIPT*/
/*3.add constraint with foreign key */
alter table Department    add constraint Department_fk
foreign key(parentid) references Post(oid)
on update cascade on delete cascade;

alter table Employee add constraint Employee_fk1
foreign key(oid) references Account(oid)
on update cascade on delete cascade;

alter table Employee add constraint Employee_fk2
foreign key(postid) references Post(oid);

alter table Employee add constraint Employee_fk3
foreign key(deptid) references Department(oid);
/*********************************************************/
/*DB:MYSQL SCRIPT*/
/*4. inert sample data into tables*/
insert into post(oid,postname) values(1,'office');
insert into post(oid,postname) values(2,'workshop');

insert into Department values(1,'R+D DEPT',1,'zhangshan','zhangshan@126.com');
insert into Department values(2,'SALE DEPT',1,'lishi','lishi@126.com');
insert into Department values(3,'MADE DEPT',2,'wanger','wanger@126.com');

insert into Account values(111,'user01','654123','1');
insert into Account values(112,'user02','963147','1');
insert into Account values(113,'user03','4456','1');

insert into Employee values(111,'smith lee',1,1,'13612345678','1970');
insert into Employee values(112,'ming yang',1,2,'13712345678','1980');
insert into Employee values(113,'san zhang',2,3,'13812345678','1990');
/*********************************************************/
/*DB:MYSQL SCRIPT*/
/*5. create view*/
drop view v_alltables;

create view v_alltables
as
select  Employee.oid,
    Employee.empname,
    Account.username,
    Account.password,
    Account.invalid,
    post.postname,
    department.deptname,
    department.manager,
    department.email,
    Employee.phone,
    Employee.birthday
from      Employee,
          Account,
    post,
    department
where    Employee.oid=Account.oid and  
    Employee.postid=post.oid and  
    Employee.deptid=department.oid;

/******************************************************************************/
/*script test ok with Oracle 9i sql*plus */
/******************************************************************************/
/*DB:Oracle 9i sql*plus script*/
/*0.drop table list*/
    drop  table bab.Employee;
    drop  table bab.Department;
    drop  table bab.Post;
    drop  table bab.Account;
/
/*********************************************************/
/*DB:Oracle SCRIPT*/
/*1.create all table*/
create table bab.Account(
    oid     int  not null,
    username varchar(30) not null,
    password varchar(10)  null,
    invalid    varchar(1)  null          
);
/
create table bab.Post(
    /*oid     int identity(1,1) not null primary key,*/
    oid     int  not null,
    postName varchar(30) not null
);
/
create table bab.Department(
    oid     int  not null,
    deptName varchar(30) not null,
    parentid  int  null,
    manager   varchar(30)  null,
    email     varchar(30)  null
);
/
create table bab.Employee(
    oid     int  not null,
    empName varchar(30) not null,
    postid  int  null,
    deptid    int  null,
    phone   varchar(20)  null,
    birthday varchar(10)  null
);
/
/*********************************************************/
/*DB:Oracle SCRIPT*/
/*2.add constraint with primary key */
alter table bab.Account    add constraint Account_pk primary key(oid);
alter table bab.Post    add constraint Post_pk primary key(oid);
alter table bab.Department    add constraint Department_pk primary key(oid);
alter table bab.Employee    add constraint Employee_pk primary key(oid);
/
/*********************************************************/
/*DB:Oracle SCRIPT*/
/*3.add constraint with foreign key */
alter table bab.Department    add constraint Department_fk
foreign key(parentid) references bab.Post(oid);

alter table bab.Employee add constraint Employee_fk1
foreign key(oid) references bab.Account(oid);

alter table bab.Employee add constraint Employee_fk2
foreign key(postid) references bab.Post(oid);

alter table bab.Employee add constraint Employee_fk3
foreign key(deptid) references bab.Department(oid);
/
/*********************************************************/
/*DB:Oracle SCRIPT*/
/*4. inert sample data into tables*/
insert into post(oid,postname) values(1,'office');
insert into post(oid,postname) values(2,'workshop');

insert into Department values(1,'R+D DEPT',1,'zhangshan','zhangshan@126.com');
insert into Department values(2,'SALE DEPT',1,'lishi','lishi@126.com');
insert into Department values(3,'MADE DEPT',2,'wanger','wanger@126.com');

insert into Account values(111,'user01','654123','1');
insert into Account values(112,'user02','963147','1');
insert into Account values(113,'user03','4456','1');

insert into Employee values(111,'smith lee',1,1,'13612345678','1970');
insert into Employee values(112,'ming yang',1,2,'13712345678','1980');
insert into Employee values(113,'san zhang',2,3,'13812345678','1990');
/
/*********************************************************/
/*DB:Oracle SCRIPT*/
/*5. create view*/
drop view bab.v_alltables;
/
create view bab.v_alltables
as
select  Employee.oid,
    Employee.empname,
    Account.username,
    Account.password,
    Account.invalid,
    post.postname,
    department.deptname,
    department.manager,
    department.email,
    Employee.phone,
    Employee.birthday
from      Employee,
          Account,
    post,
    department
where    Employee.oid=Account.oid and  
    Employee.postid=post.oid and  
    Employee.deptid=department.oid;
/
/******************************************************************************/

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/chinayaosir/archive/2010/04/09/5467514.aspx