sql 创建学生表课程表成绩表

深碍√TFBOYSˉ_ 2022-05-26 01:57 188阅读 0赞

创建  
create database student  
go  
学生表  
create table student  
(  
stu\_name varchar(30) not null,  
stu\_id char primary key (stu\_id),  
stu\_specialty varchar(30) not null,  
stu\_sex char(2) check( stu\_sex in (‘男’,’女’)),  
stu\_age int check (stu\_age>16)  
)  
课程表  
create table studentclass  
(  
stu\_classid varchar(30) not null,  
stu\_id char not null primary key (stu\_id),  
stu\_classname varchar(30) not null,  
stu\_age int check (stu\_age>16),  
stu\_time datetime not null  
)  
成绩表  
create table studentscore  
(  
stu\_id char primary key (stu\_id),  
stu\_classid varchar(30) not null,  
stu\_score int not null  
)  
select \*from student  
插入学生表数据  
insert into student values (‘张三’,’01’,’计算机专业’,’男’,’10’)  
insert into student values (‘张四’,’02’,’计算机专业’,’男’,’11’)  
insert into student values (‘王五’,’03’,’计算机专业’,’男’,’12’)  
插入课程表数据  
insert into studentclass values(‘001’,’java’,’2’,’40’)  
insert into studentclass values(‘001’,’c’,’2’,’41’)  
insert into studentclass values(‘001’,c++’,’2’,’42’)  
插入成绩表数据  
insert into studentscore values(‘01’,’001’,’60’)  
insert into studentscore values(‘01’,’002’,’70’)  
insert into studentscore values(‘01’,’003’,’80’)

select stu\_id,stu\_classid,stu\_score  
from student,studentclass,studentscore  
where (student .stu\_id=studentscore. stu\_id and studentclass.stu\_classid.studentscore.stu\_classid)  
order by stu\_score desc

select avg(stu\_score) 平均分,max(stu\_score) 最高分,min(stu\_score) 最低分  
from studentscore  
where stu\_id=’01’