创建学生成绩数据库的简单sql语句

//创建数据库

create database JW;

//学生表

create table student(

sno varchar(20) not null,

sname varchar(20) not null,

ssex varchar(20) not null default'男',

sage varchar(20),

PRIMARY KEY(sno));

//课程表

create table course(

cno varchar(20) not null,

cname varchar(20) not null,

cyear varchar(20) not null,

ctime varchar(20) not null,

PRIMARY KEY(cno));

//成绩表

create table score(

sno varchar(20) not null,

cno varchar(20) not null,

degree decimal(4,1) not null,

PRIMARY KEY(sno,cno),

CONSTRAINT score_sno FOREIGN KEY(sno) REFERENCES student(sno),

CONSTRAINT score_cno FOREIGN KEY(cno) REFERENCES course(cno));

//插入数据

insert into student(sno,sname,ssex,sage)
values('108','吴奇林','男','19');

insert into student(sno,sname,ssex,sage)
values('105','马天伟','男','22');

insert into student(sno,sname,ssex,sage)
values('107','徐宏宇','女','23');

insert into course(cno,cname,cyear,ctime)
values('3-105','计算机导论','2021','春');

insert into course(cno,cname,cyear,ctime)
values('3-245','操作系统','2021','秋');

insert into course(cno,cname,cyear,ctime)
values('1-888','数据电路','2021','秋');

insert into course(cno,cname,cyear,ctime)
values('6-166','云计算与大数据处理','2021','春');

insert into score(sno,cno,degree)
values('105','3-245',75);

insert into score(sno,cno,degree)
values('105','3-105',88);

insert into score(sno,cno,degree)
values('107','3-105',91);

insert into score(sno,cno,degree)
values('108','3-105',78);

insert into score(sno,cno,degree)
values('107','6-166',79);

insert into score(sno,cno,degree)
values('108','6-166',81);

//查询语句

select avg(degree) from score,course,student 
where student.sno=score.sno and 
course.cno=score.cno and
cname='云计算与大数据处理'and
cyear='2021'and
ctime='春';