注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Bioinformatics home

 
 
 

日志

 
 

MYSQL/MSSQL/ORACLE数据库脚本代码  

2010-05-12 21:48:08|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

/******************************************************************************/
/*
主流数据库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;
/

  评论这张
 
阅读(654)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017