chuangjianshujk.txt

Oracle 编写的代码 主要是一个项目的数据库设计

create  database oo
CREATE TABLE [Liuyan] (
	[LiuyanID] [int] IDENTITY (1, 4) NOT NULL ,
	[liuyanText] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[getDate] [datetime] NULL ,
	[userName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL 
) 
go
CREATE TABLE [MangerInfo] (
	[MangerName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[MangerPwd] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[MangerName]
	)  ON [PRIMARY] 
) 
go
CREATE TABLE [singer] (
	[singerID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[singerName] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[singerzhshu] [int] NULL ,
	[singerInfomtionText] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[singerID]
	)  ON [PRIMARY] 
) 


go
CREATE TABLE [song] (
	[songID] [char] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[songName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[singerID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[clickAmount] [int] NOT NULL ,
	[songText] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
	[songaddress] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
	[songType] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[songID]
	)  ON [PRIMARY] ,
	 FOREIGN KEY 
	(
		[singerID]
	) REFERENCES [singer] (
		[singerID]
	)
) 
go
CREATE TABLE [special] (
	[specialID] [char] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[specialName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[singerID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[sellAmount] [int] NOT NULL ,
	[specialMap] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[specialID]
	)  ON [PRIMARY] ,
	 FOREIGN KEY 
	(
		[singerID]
	) REFERENCES [singer] (
		[singerID]
	)
)

go
CREATE TABLE [userInfo] (
	[userName] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[userpwd] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[realName] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[sex] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[keyWord] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[userName]
	)  ON [PRIMARY] 
) 

go

create view searchView as
select s.songName,sp.specialName,si.singerName from song s join special sp on s.singerID=sp.singerID join
singer si on si.singerID=sp.singerID
go
create view searchView1 as
select s.songID,sp.specialName,si.singerName, si.singerInfomtionText from song s join special sp on s.singerID=sp.singerID join
singer si on si.singerID=sp.singerID 
go 
 create view searchView10 as
select s.songID,sp.specialID,s.songName,sp.specialName,si.singerName from song s join special sp on s.singerID=sp.singerID join
singer si on si.singerID=sp.singerID
go
create view searchView2 as
select sp.specialID,sp.specialName,si.singerName, si.singerInfomtionText from song s join special sp on s.singerID=sp.singerID join
singer si on si.singerID=sp.singerID  
go
create view searchView3 as
select s.songName,sp.specialName,si.singerName ,si.singerInfomtionText from song s join special sp on s.singerID=sp.singerID join
singer si on si.singerID=sp.singerID 
go 
create view searchView4 as
select sp.specialID,s.songName,sp.specialName,si.singerName ,si.singerInfomtionText from song s join special sp on s.singerID=sp.singerID join
singer si on si.singerID=sp.singerID 
go       
create view searchView5 as
select sp.specialID,s.songID,s.songName,sp.specialName,si.singerName ,si.singerInfomtionText from song s join special sp on s.singerID=sp.singerID join
singer si on si.singerID=sp.singerID