student.sql

来自「这是个学生信息管理系统」· SQL 代码 · 共 91 行

use master
go

if exists (select * from dbo.sysdatabases where name = 'Student') 
drop database Student
GO

create database Student
go
use Student
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[user_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[user_Info]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[student_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[student_Info]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[result_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[result_Info]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gradecourse_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[gradecourse_Info]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[course_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[course_Info]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[class_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[class_Info]
GO

CREATE TABLE [dbo].[user_Info] (
	[user_ID] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[user_PWD] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[user_Des] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[student_Info] (
	[student_ID] [int] NOT NULL ,
	[student_Name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[student_Sex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[born_Date] [datetime] NULL ,
	[class_NO] [int] NULL ,
	[tele_Number] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[ru_Date] [datetime] NULL ,
	[address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[comment] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[result_Info] (
	[exam_No] [char] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[student_ID] [int] NOT NULL ,
	[student_Name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[class_No] [int] NULL ,
	[course_Name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[result] [float] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[gradecourse_Info] (
	[grade] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[course_Name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[course_Info] (
	[course_No] [int] NOT NULL ,
	[course_Name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[course_Type] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
	[course_Des] [char] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[class_Info] (
	[class_No] [int] NOT NULL ,
	[grade] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[director] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[classroom_No] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

INSERT 
INTO USER_INFO
VALUES ('adm',1,1)