表名和字段
- 1.学生表
- Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
- 2.课程表
- Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
- 3.教师表
- Teacher(t_id,t_name) –教师编号,教师姓名
- 4.成绩表
- Score(s_id,c_id,s_score) –学生编号,课程编号,分数
建库建表:
创建数据库:
drop database if exists stu;
create database stu;
创建学生表:
create table student(
s_id int(10) not null auto_increment comment '学生编号',
s_name varchar(10) not null comment '学生姓名',
s_birth varchar(20) not null comment '出生日期',
s_sex varchar(2) not null comment '性别',
primary key(s_id)
) engine=innodb default charset=utf8;
创建课程表:
create table student(
s_id int(10) not null auto_increment comment '学生编号',
s_name varchar(10) not null comment '学生姓名',
s_birth varchar(20) not null comment '出生日期',
s_sex varchar(2) not null comment '性别',
primary key(s_id)
) engine=innodb default charset=utf8;
创建课程表:
create table course(
c_id int(10) not null auto_increment comment '课程编号',
c_name varchar(10) not null comment '课程名称',
t_id int(10) not null comment '任课老师',
primary key(c_id)
) engine=innodb default charset=utf8;
创建教师表:
create table teacher(
t_id int(10) not null auto_increment comment '教师编号',
t_name varchar(10) not null comment '教师姓名',
primary key(t_id)
) engine=innodb default charset=utf8;
成绩表:
create table score(
s_id int(10) not null auto_increment comment '成绩编号',
c_id int(10) not null comment '课程编号',
s_score int(3) not null comment '学生分数',
primary key(s_id,c_id)
) engine=innodb default charset=utf8;
测试数据:
插入学生表测试数据 (学生编号,学生姓名,出生日期,性别):
insert into Student values(1, '赵雷' , '1990-01-01' , '男');
insert into Student values(2, '钱电' , '1990-12-21' , '男');
insert into Student values(3, '孙风' , '1990-05-20' , '男');
insert into Student values(4, '李云' , '1990-08-06' , '男');
insert into Student values(5, '周梅' , '1991-12-01' , '女');
insert into Student values(6, '吴兰' , '1992-03-01' , '女');
insert into Student values(7, '郑竹' , '1989-07-01' , '女');
insert into Student values(8, '王菊' , '1990-01-20' , '女');
课程表测试数据 (课程编号,课程名,任课教师编号):
insert into Course values(1, '语文' , 2);
insert into Course values(2, '数学' , 1);
insert into Course values(3, '英语' , 3);
教师表测试数据 (教师编号,教师名):
insert into Teacher values(1 , '张三');
insert into Teacher values(2 , '李四');
insert into Teacher values(3 , '王五');
成绩表测试数据 (成绩编号,课程编号,成绩)
insert into Score values(1 , 1 , 80);
insert into Score values(1 , 2 , 90);
insert into Score values(1 , 3 , 99);
insert into Score values(2 , 1 , 70);
insert into Score values(2 , 2 , 60);
insert into Score values(2 , 3 , 80);
insert into Score values(3 , 1 , 80);
insert into Score values(3 , 2 , 80);
insert into Score values(3 , 3 , 80);
insert into Score values(4 , 1 , 50);
insert into Score values(4 , 2 , 30);
insert into Score values(4 , 3 , 20);
insert into Score values(5 , 1 , 76);
insert into Score values(5 , 2 , 87);
insert into Score values(6 , 1 , 31);
insert into Score values(6 , 3 , 34);
insert into Score values(7 , 2 , 89);
insert into Score values(7 , 3 , 98);
练习题、sql语句、执行结果:
--1.查询语文成绩比数学成绩高的学生的信息以及课程分数
select a.* , b.s_score as chinese , c.s_score as math from
student a
join score b on b.c_id = 1 and a.s_id = b.s_id
left join score c on c.c_id = 2 and a.s_id = c.s_id where b.s_score > c.s_score;
运行结果:
--2.查询语文成绩比数学成绩低的学生的信息以及课程分数
select a.* , b.s_score as chinese , c.s_score as math from
student a
join score b on b.c_id = 1 and a.s_id = b.s_id
left join score c on c.c_id = 2 and a.s_id = c.s_id
where b.s_score < c.s_score;
运行结果:
--3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select b.s_id , b.s_name,round(avg(a.s_score),1) as avg_score
from student b
join score a on b.s_id = a.s_id
group by b.s_id,b.s_name having round(avg(a.s_score),1)>=60;
运行结果:
--4.查询平均成绩小于60分的同学编号和学生姓名和平均成绩(包括有成绩和没有成绩的)
select b.s_id,b.s_name,round(avg(a.s_score)) as avg_score
from student b
left join score a on b.s_id = a.s_id
group by b.s_id,b.s_name having round(avg(a.s_score),2)<60
union
select a.s_id,a.s_name,0 as avg_score
from student a
where a.s_id not in (select distinct s_id from score);
运行结果:
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score
from student a
left join score b on a.s_id = b.s_id
group by a.s_id,a.s_name;
运行结果: