代码:
SQL脚本(直接在SQL Server查询分析器中执行即可):
create database Student
Go
use Student
GO
CREATE TABLE [dbo].[users](
[id] [int] NOT NULL,
[username] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[password] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[type] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[courses](
[cid] [int] NOT NULL,
[title] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_courses] PRIMARY KEY CLUSTERED
(
[cid] ASC
)
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[selections](
[sid] [int] NOT NULL,
[cid] [int] NOT NULL,
CONSTRAINT [PK_selection] PRIMARY KEY CLUSTERED
(
[sid] ASC,
[cid] ASC
)
) ON [PRIMARY]
Go
CREATE VIEW [dbo].[administrators]
AS
SELECT id AS aid, username, password, type
FROM dbo.users
WHERE (type = 'a')
Go
CREATE VIEW [dbo].[students]
AS
SELECT id AS sid, username, password, type
FROM dbo.users
WHERE (type = 's')
Go
insert users (id,username,password,type) values ( 1,'黎明','111111','s')
insert users (id,username,password,type) values ( 2,'胡哥','abcd','s')
insert users (id,username,password,type) values ( 3,'Tom','tom','s')
insert users (id,username,password,type) values ( 1000,'admin','888888','a')
insert courses (cid,title) values ( 101,'JavaEE')
insert courses (cid,title) values ( 102,'信息检索')
insert courses (cid,title) values ( 103,'数据库系统原理')
insert courses (cid,title) values ( 105,'J2ME')
insert courses (cid,title) values ( 106,'Math ')
insert selections (sid,cid) values ( 1,101)
insert selections (sid,cid) values ( 1,103)
insert selections (sid,cid) values ( 2,102)
insert selections (sid,cid) values ( 3,103)
insert selections (sid,cid) values ( 3,105)
GO