sql语句小练习二
时间:2022-03-14 03:50
1、创建一个数据库StudentManage, 初始化大小10M,不足以1M每次增长 create database StudentManage on ( name = ‘StudentManage_data‘, filename = ‘D:\StudentManage_data.mdf‘, size = 10mb, maxsize = 100mb, filegrowth = 15% ) log on ( name = ‘StudentManage_log‘, filename = ‘D:\StudentManage_log.ldf‘, size = 2mb, filegrowth = 1mb ) 2、其中表结构包括 学生信息表:StudentInfo ( 学生ID(主键), 所在班级ID(外键约束), 学生姓名(非空约束), 性别(检查约束(男,女)), 年龄(检查约束(6-30)), 身份证号(检查约束), 家庭地址(默认值‘地址不详’)) create table StudentInfo ( StuId varchar(10) primary key, ClassId varchar(8) foreign key references ClassInfo(ClassId), StuName varchar(6) not null, Sex varchar(4) default ‘男‘ check(Sex=‘男‘or Sex=‘女‘), Age smallint check(Age>0 and Age<36), IdNum char(18) check(IdNum like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9,X]‘), StuAddress nvarchar(50) default ‘地址不详‘, ) 教师信息表:TeacherInfo (教师ID(主键), 教师姓名(非空约束), 性别(检查约束(男,女)), 年龄(检查约束(25-60)), 身份证号(检查约束), 教学方向 家庭地址(默认值‘地址不详’)) create table TeacherInfo ( TecId varchar(10) primary key, TecName varchar(8) not null, Sex varchar(4) default ‘男‘ check(Sex=‘男‘ or Sex=‘女‘), Age smallint check(Age>25 and Age<60), IdNum char(18) check(IdNum like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9,X]‘), TecWhat nvarchar(8) not null, TecAddress nvarchar(50) default ‘地址不详‘, ) 班级信息表:ClassInfo (班级ID(主键), 班级名称(非空约束), 班级人数(15-60), 入校时间, 毕业时间 ) use StudentManage create table ClassInfo ( ClassId varchar(8) primary key, ClassName varchar(8) not null, CstuNum int, InDate date, GraduDate date ) 课程信息表:CourseInfo (课程ID(主键), 课程名称(非空约束), ) create table CourseInfo ( CourId varchar(8) primary key, CourName varchar(8) not null ) 考分信息表:Scores (ID(标识列,(1, 1)), 学生ID(外键约束), 教师ID(外键约束), 考试批次,--第一次考还是补考? 分数(0-100) ) create table ScoresInfo ( ScoreId int primary key identity(1,1), StuId varchar(10) foreign key references StudentInfo(StuId), TecId varchar(10) foreign key references TeacherInfo(TecId), PiCi varchar(8), Score smallint check(Score>=0 and Score <=100) ) 3、以上的每个表至少加入3条以上的记录 use StudentManage --向班级表插入三条数据 insert into ClassInfo values(‘101‘,‘计算机‘,50,‘2010-09-09‘,‘2014-10-10‘) insert into ClassInfo values(‘102‘,‘嵌入式‘,50,‘2010-09-09‘,‘2014-10-10‘) insert into ClassInfo values(‘103‘,‘网络安全‘,50,‘2010-09-09‘,‘2014-10-10‘) --向学生信息表里插入数据 insert into StudentInfo values(‘144712190‘,‘101‘,‘武索普‘,‘男‘,18,‘411481199012102908‘,default) insert into StudentInfo values(‘144712221‘,‘102‘,‘路飞‘,‘男‘,18,‘211481199012102593‘,default) insert into StudentInfo values(‘144712321‘,‘103‘,‘罗宾‘,‘女‘,18,‘511481199012102908‘,default) --向教师信息表里插入数据 insert into TeacherInfo values(‘541001‘,‘白胡子‘,‘男‘,50,‘123456789098765432‘,‘震震大地‘,default) insert into TeacherInfo values(‘541002‘,‘罗利‘,‘男‘,50,‘323456789098765432‘,‘催眠掌‘,default) insert into TeacherInfo values(‘541003‘,‘帝喾克‘,‘女‘,26,‘523456789098765432‘,‘媚眼‘,default) --向课程信息表里插入数据 insert into CourseInfo values(‘101‘,‘气武镜‘) insert into CourseInfo values(‘102‘,‘玄武镜‘) insert into CourseInfo values(‘103‘,‘灵武镜‘) insert into CourseInfo values(‘104‘,‘天武镜‘) --向成绩表里插入数据 insert into ScoresInfo values(‘144712190‘,‘541001‘,‘第一批‘,100) insert into ScoresInfo values(‘144712221‘,‘541002‘,‘第一批‘,100) insert into ScoresInfo values(‘144712321‘,‘541003‘,‘第一批‘,100)