数据库课程设计sql server 家教管理系统代码
时间: 2023-08-07 20:49:21 浏览: 184
以下是一个使用 SQL Server 数据库的家教管理系统的代码示例:
1. 用户表(User)的创建
CREATE TABLE [dbo].[User](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Gender] [nvarchar](10) NOT NULL,
[Age] [int] NOT NULL,
[PhoneNumber] [nvarchar](50) NOT NULL,
[UserRole] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
2. 需求表(Demand)的创建
CREATE TABLE [dbo].[Demand](
[DemandID] [int] IDENTITY(1,1) NOT NULL,
[Subject] [nvarchar](50) NOT NULL,
[TeachingMethod] [nvarchar](50) NOT NULL,
[TeachingTime] [nvarchar](50) NOT NULL,
[TeachingLocation] [nvarchar](50) NOT NULL,
[Salary] [float] NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_Demand] PRIMARY KEY CLUSTERED
(
[DemandID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
3. 教师表(Teacher)的创建
CREATE TABLE [dbo].[Teacher](
[TeacherID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Gender] [nvarchar](10) NOT NULL,
[Age] [int] NOT NULL,
[PhoneNumber] [nvarchar](50) NOT NULL,
[TeachingSubject] [nvarchar](50) NOT NULL,
[TeachingExperience] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED
(
[TeacherID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
4. 匹配表(Match)的创建
CREATE TABLE [dbo].[Match](
[MatchID] [int] IDENTITY(1,1) NOT NULL,
[DemandID] [int] NOT NULL,
[TeacherID] [int] NOT NULL,
[MatchStatus] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Match] PRIMARY KEY CLUSTERED
(
[MatchID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
5. 插入数据
INSERT INTO [dbo].[User] ([UserName], [Password], [Name], [Gender], [Age], [PhoneNumber], [UserRole])
VALUES ('john', '123456', 'John', 'Male', 30, '13812345678', 'Parent');
INSERT INTO [dbo].[Teacher] ([Name], [Gender], [Age], [PhoneNumber], [TeachingSubject], [TeachingExperience])
VALUES ('Tom', 'Male', 35, '13987654321', 'Math', '10 years');
INSERT INTO [dbo].[Demand] ([Subject], [TeachingMethod], [TeachingTime], [TeachingLocation], [Salary], [UserID])
VALUES ('English', 'Online', 'Weekend Morning', 'Beijing', 150, 1);
INSERT INTO [dbo].[Match] ([DemandID], [TeacherID], [MatchStatus])
VALUES (1, 1, 'Pending');
6. 查询数据
-- 查询所有需求信息
SELECT * FROM [dbo].[Demand];
-- 查询所有教师信息
SELECT * FROM [dbo].[Teacher];
-- 查询匹配信息
SELECT [Match].[MatchID], [Demand].[Subject], [Teacher].[Name], [Match].[MatchStatus]
FROM [dbo].[Match]
INNER JOIN [dbo].[Demand] ON [Match].[DemandID] = [Demand].[DemandID]
INNER JOIN [dbo].[Teacher] ON [Match].[TeacherID] = [Teacher].[TeacherID];
以上是一个简单的使用 SQL Server 数据库实现的家教管理系统代码示例,仅供参考。具体实现还需要根据实际需求进行调整和改进。