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

sql | 2018-05-11 21:22:45 | 阅读 298 次 | 评论(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’

文章评论,共0条
游客请输入验证码
最新评论