use master
go
create database Test
go
use Test
go
--项目表
create table Project
(
ProjectID int identity(1,1)primary key,--项目编号
ProjectName char(100)--项目名称
)
go
--任务表
create table Task
(
TaskID int identity(1,1)primary key,--任务编号
TaskPrioriry int check(TaskPrioriry in(1,2,3)),--任务优先级
Content varchar(200),--任务内容
AddTime datetime default getdate(),--添加时间
TaskState int check(TaskState=1 or TaskState=2), --任务状态
FinishedTime datetime default null,--完成时间
ProjectID int references Project(ProjectID)--所属项目编号
)
go
insert into Project values('考勤管理平台')
insert into Project values('申请单管理系统')
insert into Project values('会员管理系统')
insert into Task values(1,'进行数据库设计','2019-12-04',2,'2019-12-05',1)
insert into Task values(2,'实现审批申请单','2019-12-04',1,null,2)
insert into Task values(3,'测试整个系统','2019-12-04',1,null,3)
insert into Task values(2,'实现登录','2019-12-04',2,'2019-12-05',1)
select * from Task
select * from Project
select * from Project left join Task on Project.ProjectID=Task.ProjectID
SELECT Task.TaskID, Task.TaskPrioriry, Task.[Content], Task.AddTime, Task.TaskState, Task.FinishedTime, Task.ProjectID ,Project.ProjectName FROM Task INNER JOIN Project ON Task.ProjectID = Project.ProjectID
select TaskID,ProjectName,TaskPrioriry,Content,AddTime,TaskState,FinishedTime from Project,Task where Project.ProjectID=Task.ProjectID
select Task.*,ProjectName from Task,Project where Task.ProjectID=Project.ProjectID
insert into Task values(2,'.\r\n12.0',getdate(),2)
delete from Task where TaskID=6
delete from Task where TaskID=5
update Task set TaskState=2,FinishedTime=getdate() where TaskID=7
