Mysql——小型选课系统

最新推荐文章于 2024-01-28 20:10:33 发布

【你不认识我】

最新推荐文章于 2024-01-28 20:10:33 发布

阅读量266

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/Aa13527916708/article/details/103189184

版权

Mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

creat table class
(
	chaid int not null auto_increment,
	claname char(20),
	term char(20),
	teacher char(20)
)

create table manager
(
	manid int not null auto_increment,
	manname char(20) ,
	role cha(20),
	manpasswd char(20)
)

insert manager
values('student1','学生','123')

creat table sc
(
	scid int not null,
	stuid int,
	claid int,
	grades int
)

creat table sctime
(
	sctimed int,
	claid int,
	sctime char(20),
	location char(20)
)

creat table student
(
	stuid int,
	stuname char(20),
	stuxuehao char(30),
	stupasswd char(30),
	stugrade char(30),
	stumajor char(30),
	stusex char(20),
	stuborn char(20),
	role char(20),
	stuhometown char(20)
)

create database student

USE student

CREATE TABLE dbo.class(
	claid bigint IDENTITY(1,1) NOT NULL,
	claname varchar(30) NULL,
	term varchar(30) NULL,
	teacher varchar(30) NULL,
PRIMARY KEY CLUSTERED 
(
	[claid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[manager](
	[manid] [bigint] IDENTITY(1,1) NOT NULL,
	[manname] [varchar](30) NOT NULL,
	[role] [varchar](30) NULL,
	[manpasswd] [varchar](30) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[manid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sc](
	[scid] [bigint] IDENTITY(1,1) NOT NULL,
	[stuid] [bigint] NULL,
	[claid] [bigint] NULL,
	[grades] [bigint] NULL,
PRIMARY KEY CLUSTERED 
(
	[scid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sctime](
	[sctimeid] [bigint] IDENTITY(1,1) NOT NULL,
	[claid] [bigint] NULL,
	[sctime] [varchar](30) NULL,
	[location] [varchar](30) NULL,
PRIMARY KEY CLUSTERED 
(
	[sctimeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student](
	[stuid] [bigint] IDENTITY(1,1) NOT NULL,
	[stuname] [varchar](30) NULL,
	[stuxuehao] [varchar](30) NOT NULL,
	[stupasswd] [varchar](30) NOT NULL,
	[stugrade] [varchar](30) NULL,
	[stumajor] [varchar](30) NULL,
	[stusex] [varchar](2) NULL,
	[stuborn] [varchar](30) NULL,
	[role] [varchar](30) NULL,
	[stuhometown] [varchar](30) NULL,
PRIMARY KEY CLUSTERED 
(
	[stuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[class] ON 

INSERT [dbo].[class] ([claid], [claname], [term], [teacher]) VALUES (1, N'数据库', N'2019年春季学期', N'test1')
INSERT [dbo].[class] ([claid], [claname], [term], [teacher]) VALUES (2, N'如何把妹', N'2019年春季学期', N'test2')
INSERT [dbo].[class] ([claid], [claname], [term], [teacher]) VALUES (3, N'编程之美', N'2020年春季学期', N'test3')
INSERT [dbo].[class] ([claid], [claname], [term], [teacher]) VALUES (4, N'Github从入门到入狱', N'2020年春季学期', N'test4')
INSERT [dbo].[class] ([claid], [claname], [term], [teacher]) VALUES (5, N'为什么这么帅', N'2020年春季学期', N'test5')
INSERT [dbo].[class] ([claid], [claname], [term], [teacher]) VALUES (6, N'帅的没有脾气', N'2020年春季学期', N'test9')
SET IDENTITY_INSERT [dbo].[class] OFF
SET IDENTITY_INSERT [dbo].[manager] ON 

INSERT [dbo].[manager] ([manid], [manname], [role], [manpasswd]) VALUES (1, N'admin', N'管理员', N'admin')
INSERT [dbo].[manager] ([manid], [manname], [role], [manpasswd]) VALUES (4, N'root1', N'管理员', N'root')
INSERT [dbo].[manager] ([manid], [manname], [role], [manpasswd]) VALUES (5, N'root2', N'管理员', N'root2')
INSERT [dbo].[manager] ([manid], [manname], [role], [manpasswd]) VALUES (6, N'root4', N'管理员', N'root4')
INSERT [dbo].[manager] ([manid], [manname], [role], [manpasswd]) VALUES (7, N'root3', N'管理员', N'root3')
INSERT [dbo].[manager] ([manid], [manname], [role], [manpasswd]) VALUES (8, N'test9', N'管理员', N'test9')
SET IDENTITY_INSERT [dbo].[manager] OFF
SET IDENTITY_INSERT [dbo].[sc] ON 

INSERT [dbo].[sc] ([scid], [stuid], [claid], [grades]) VALUES (8, 1, 1, 90)
INSERT [dbo].[sc] ([scid], [stuid], [claid], [grades]) VALUES (9, 1, 2, 90)
INSERT [dbo].[sc] ([scid], [stuid], [claid], [grades]) VALUES (10, 1, 4, 90)
SET IDENTITY_INSERT [dbo].[sc] OFF
SET IDENTITY_INSERT [dbo].[sctime] ON 

INSERT [dbo].[sctime] ([sctimeid], [claid], [sctime], [location]) VALUES (1, 1, N'星期一第一节', N'A102')
INSERT [dbo].[sctime] ([sctimeid], [claid], [sctime], [location]) VALUES (2, 1, N'星期一第二节', N'A102')
INSERT [dbo].[sctime] ([sctimeid], [claid], [sctime], [location]) VALUES (3, 2, N'星期一第三节', N'A102')
INSERT [dbo].[sctime] ([sctimeid], [claid], [sctime], [location]) VALUES (4, 3, N'星期一第一节', N'A104')
INSERT [dbo].[sctime] ([sctimeid], [claid], [sctime], [location]) VALUES (5, 3, N'星期一第二节', N'A104')
INSERT [dbo].[sctime] ([sctimeid], [claid], [sctime], [location]) VALUES (6, 4, N'星期三第一节', N'A102')
INSERT [dbo].[sctime] ([sctimeid], [claid], [sctime], [location]) VALUES (7, 4, N'星期三第二节', N'A102')
INSERT [dbo].[sctime] ([sctimeid], [claid], [sctime], [location]) VALUES (8, 5, N'星期一第一节', N'A203')
INSERT [dbo].[sctime] ([sctimeid], [claid], [sctime], [location]) VALUES (9, 5, N'星期一第二节', N'A203')
INSERT [dbo].[sctime] ([sctimeid], [claid], [sctime], [location]) VALUES (10, 6, N'星期一第三节', N'A103')
INSERT [dbo].[sctime] ([sctimeid], [claid], [sctime], [location]) VALUES (11, 6, N'星期一第四节', N'A103')
SET IDENTITY_INSERT [dbo].[sctime] OFF
SET IDENTITY_INSERT [dbo].[student] ON 

INSERT [dbo].[student] ([stuid], [stuname], [stuxuehao], [stupasswd], [stugrade], [stumajor], [stusex], [stuborn], [role], [stuhometown]) VALUES (1, N'test2', N'201000830090', N'admin', N'一年级', N'人工智能', N'男', N'2012-11-26', N'学生', N'河南')
INSERT [dbo].[student] ([stuid], [stuname], [stuxuehao], [stupasswd], [stugrade], [stumajor], [stusex], [stuborn], [role], [stuhometown]) VALUES (2, N'test1', N'201000830091', N'201000830091', N'二年级', N'计算机科学与技术', N'男', N'2012-11-26', N'学生', N'济源')
INSERT [dbo].[student] ([stuid], [stuname], [stuxuehao], [stupasswd], [stugrade], [stumajor], [stusex], [stuborn], [role], [stuhometown]) VALUES (3, N'test3', N'201000830092', N'201000830092', N'一年级', N'人工智能', N'男', N'2012-11-26', N'学生', N'山东')
INSERT [dbo].[student] ([stuid], [stuname], [stuxuehao], [stupasswd], [stugrade], [stumajor], [stusex], [stuborn], [role], [stuhometown]) VALUES (4, N'test3', N'201000830093', N'201000830093', N'一年级', N'网络空间安全', N'男', N'2012-11-26', N'学生', N'威海')
INSERT [dbo].[student] ([stuid], [stuname], [stuxuehao], [stupasswd], [stugrade], [stumajor], [stusex], [stuborn], [role], [stuhometown]) VALUES (5, N'test4', N'201000830094', N'201000830094', N'一年级', N'通信工程', N'男', N'2012-11-26', N'学生', N'大龄村')
INSERT [dbo].[student] ([stuid], [stuname], [stuxuehao], [stupasswd], [stugrade], [stumajor], [stusex], [stuborn], [role], [stuhometown]) VALUES (6, N'test5', N'201000830095', N'201000830095', N'一年级', N'计算机科学与技术', N'男', N'2012-11-26', N'学生', N'下冶乡')
SET IDENTITY_INSERT [dbo].[student] OFF
SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[manager] ADD UNIQUE NONCLUSTERED 
(
	[manname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[student] ADD UNIQUE NONCLUSTERED 
(
	[stuxuehao] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[manager] ADD  DEFAULT ('管理员') FOR [role]
GO
ALTER TABLE [dbo].[student] ADD  DEFAULT ('男') FOR [stusex]
GO
ALTER TABLE [dbo].[student] ADD  DEFAULT ('学生') FOR [role]
GO
ALTER TABLE [dbo].[sc]  WITH CHECK ADD  CONSTRAINT [scc_id] FOREIGN KEY([claid])
REFERENCES [dbo].[class] ([claid])
GO
ALTER TABLE [dbo].[sc] CHECK CONSTRAINT [scc_id]
GO
ALTER TABLE [dbo].[sc]  WITH CHECK ADD  CONSTRAINT [ssc_id] FOREIGN KEY([stuid])
REFERENCES [dbo].[student] ([stuid])
GO
ALTER TABLE [dbo].[sc] CHECK CONSTRAINT [ssc_id]
GO
ALTER TABLE [dbo].[sctime]  WITH CHECK ADD  CONSTRAINT [csctime_id] FOREIGN KEY([claid])
REFERENCES [dbo].[class] ([claid])
GO
ALTER TABLE [dbo].[sctime] CHECK CONSTRAINT [csctime_id]
GO
USE [master]
GO
ALTER DATABASE [student] SET  READ_WRITE 
GO