create database message--创建信息数据库

use message
go

--1.	班级信息表Class
--字段名称	字段类型	备注
--班级编号 （ClassId）	整型int	主键primary key，自增
--班级名称（ClassName）	字符串nvarchar(20)	唯一unique，非空not null



create table Class
(
ClassId int identity primary key ,
ClassName nvarchar(20) unique not null
);
go
----------------
--1）	用insert into语句一次性为班级表添加10条记录，班级名称自定
set identity_insert Class on

insert into Class(ClassName)
values('软件1班'),
      ('软件2班'),
	  ('软件3班'),
	  ('软件4班'),
	  ('软件5班'),
	  ('软件6班'),
	  ('软件7班'),
	  ('软件8班'),
	  ('软件9班'),
	  ('软件10班')

	  select *from Class

---------------
--2）	用update语句修改编号为1的班级名称  
update Class set ClassName='软件11班' where Classid=1
---------------
--3）	用delete语句删除班级编号为10的记录 
delete from Class where ClassId=10

---------------------------------------------------------------
--2.	学生信息表Student

--字段名称	字段类型	备注
--学号 （StuId）	整型int	主键，自增idtentity(1,1)
--班级编号（ClassId）	整型int	外键foreign key ，关联references班级表的班级编号
--姓名（StuName）	字符串nvarchar(20)	非空not null
--性别（StuSex）	字符串nvarchar(1)	默认为’男’,只能填写男或女
--Check( StuSex in(‘男’,’女’))
--出生日期（StuBirthday）	日期（date）	
--手机号（StuPhone）	字符串nvarchar(11)	唯一，允许为空
--地址（StuAddress）	字符串nvarchar(200)	　允许为空
--创建时间（CreateDate）	日期（datetime）	默认为当前系统时间（getdate()）
create table Student
(
StuId int identity(1,1) primary key,
ClassId int foreign key references Class(ClassId),
StuName nvarchar(20) not null,
StuSex nvarchar(1) check(StuSex in('男','女')) default('男'),
StuBirthday date,
StuPhone nvarchar(11) unique null,
StuAddress nvarchar(200) null,
CreateDate datetime 
);
go

--1）	用insert into语句为学生信息表添加20条记录，使每个班级都有学生信息
set identity_insert Student on

insert into Student (StuId,ClassId,StuName,StuSex,StuBirthday,StuPhone,StuAddress,CreateDate)
values(1,1,'小明','男','2001-1-08','11111111111','啊吧啊吧',getdate()),
       (2,1,'小明','男','2001-1-08','11111111112','啊吧啊吧',getdate()),
	   (3,2,'小明','男','2001-1-08','11111111113','啊吧啊吧',getdate()),
	   (4,2,'小明','男','2001-1-08','11111111114','啊吧啊吧',getdate()),
	   (5,3,'小明','男','2001-1-08','11111111115','啊吧啊吧',getdate()),
	   (6,3,'小明','男','2001-1-08','11111111116','啊吧啊吧',getdate()),
	   (7,4,'小明','男','2001-1-08','11111111117','啊吧啊吧',getdate()),
	   (8,4,'小明','男','2001-1-08','11111111118','啊吧啊吧',getdate()),
	   (9,5,'小明','男','2001-1-08','11111111119','啊吧啊吧',getdate()),
	   (10,5,'小明','男','2001-1-08','11111111120','啊吧啊吧',getdate()),
	   (11,6,'小明','男','2001-1-08','11111111121','啊吧啊吧',getdate()),
	   (12,6,'小明','男','2001-1-08','11111111122','啊吧啊吧',getdate()),
	   (13,7,'小明','男','2001-1-08','11111111123','啊吧啊吧',getdate()),
	   (14,7,'小明','男','2001-1-08','11111111124','啊吧啊吧',getdate()),
	   (15,8,'小明','男','2001-1-08','11111111125','啊吧啊吧',getdate()),
	   (16,8,'小明','男','2001-1-08','11111111126','啊吧啊吧',getdate()),
	   (17,9,'小明','男','2001-1-08','11111111127','啊吧啊吧',getdate()),
	   (18,9,'小明','男','2001-1-08','11111111128','啊吧啊吧',getdate()),
	   (19,10,'小明','男','2001-1-08','11111111129','啊吧啊吧',getdate()),
	   (20,10,'小明','男','2001-1-08','11111111130','啊吧啊吧',getdate())

alter table Student--删除Student的外键约束
drop constraint UQ__Student__2D85FC635B95E011

	   select * from Student
	
--2）	为学生信息表新增“创建时间”字段,数据类型和约束如上表中的信息
       alter table Student
	   add constraint CreateDate default(getDate()) for CreateDate 

--3）	用update语句修改所有学生记录的“创建时间”字段，值为当前时间
    update Student  set CreateDate=getDate() 

--4）	用delete语句删除某个班级的学生信息，删除条件为班级编号
     delete from Student where StuId=1


	 --------------------------
--	 3.	课程信息表Course
--字段名称	字段类型	备注
--课程编号 （CourseId）	整型 int	主键，自增
--课程名称（CourseName）	字符串nvarchar(50)	唯一，非空
--学分（CourseCredit）	整型 int	非空，默认default为1，取值（1~5）
--课程类型(CourseCredit)	字符串nvarchar(10）	取值为“专业课”或者“公共课”
create table Course
(
CourseId int identity primary key,
CourseName nvarchar(50) not null unique,
CourseCredit int not null check(CourseCredit>=1 and courseCredit<=5) default('1'),
CourseType nvarchar(10) check(CourseType='专业课' or CourseType='公共课')
);
go

--1）	用insert into语句为课程信息表添加6条记录：要求只设置课程名称字段的值，学分字段不设置
set identity_insert Course on

       insert into Course(CourseId,CourseName,CourseCredit,CourseType)
	   values(1,'啊吧啊吧',2,'专业课'),
	         (2,'啊吧啊吧',3,'专业课'),
	         (3,'啊吧啊吧',2,'专业课'),
	         (4,'啊吧啊吧',3,'专业课'),
	         (5,'啊吧啊吧',4,'专业课'),
	         (6,'啊吧啊吧',2,'专业课')

			 
--2）	用select查询语句查看添加的记录结果
        select * from Course

--3）	用update语句修改某门课程的学分信息，根据课程名称筛选修改条件
        update Course set CourseCredit=CourseCredit+1 where CourseId=1

-----------------------------------------
--	成绩表Score
--字段名称	字段类型	备注
--成绩编号（ScoreId）	整型int	主键，自增
--学号（StuId）	整型int	外键，关联学生表的学号
--课程编号 （CourseId）	整型int	外键，关联课程信息表的课程编号
--成绩（Score）	小数decimal（5,2）	唯一，非空

create table Score 
(
ScoreId int identity primary key ,
StuId int ,
CourseId int references Course(CourseId),
Score decimal(5,2) unique not null
);
go

set identity_insert Score on 


    insert into Score(ScoreId,StuId,CourseId,Score)
	values (1,1,1,1),
	       (2,2,2,2),
		   (3,3,3,3),
		   (4,4,4,4),
		   (1,1,1,5),
	       (2,2,2,6),
		   (3,3,3,7),
		   (4,4,4,8),
		   (1,1,1,9),
	       (2,2,2,20),
		   (3,3,3,31),
		   (4,4,4,45),
		   (1,1,1,12),
	       (2,2,2,26),
		   (3,3,3,38),
		   (4,4,4,49),
		   (1,1,1,16),
	       (2,2,2,24),
		   (3,3,3,39),
		   (4,4,4,47)
		   

--2）	用update语句修改某门课程的学分信息，根据课程名称筛选修改条件
       update Course set CourseCredit=CourseCredit+1 where CourseName='啊吧啊吧'

--3）	用delete语句删除学号为1的成绩信息
       delete  from Score where StuId=1  

--4）	用delete语句删除课程编号为1的成绩信息
       delete from Course where CourseId=1

--5）	为成绩字段创建约束，值为0~100之间，默认值为0
       alter table Score
	   add constraint DF_Score_Score default('0')

	   alter table Score
	   add constraint CK_Score_Score check(Score>=0 and Score<=100)