1030作业
// 创建数据库
create database zuoye charset utf8;
// 选择数据库
use zuoye;
// 创建班级表
create table class(
cid int auto_increment primary key,
caption varchar(32) not null default ''
)charset utf8;
//添加数据
insert into class(caption) values ('三年二班'),('一年三班'),('三年一班');
// 创建学生表
create table student(
sid int auto_increment primary key,
sname varchar(32) not null default '' ,
gender varchar(32) not null default '',
class_id int not null default 0,
constraint stu foreign key (class_id) references class(cid)
)charset utf8;
// 添加数据
insert into student(sname,gender,class_id) values ('钢蛋','女',1),('铁锤','女',1),('山炮','男',2);
//创建老师表
create table teacher(
tid int auto_increment primary key,
tname varchar(32) not null default ''
)charset utf8;
// 添加数据
insert into teacher(tname) values ('波多'),('苍井空'),('饭岛爱');
// 创建课程
create table course(
cid int auto_increment primary key,
cname varchar(32) not null default '',
teach_id int not null default 0,
constraint cou foreign key(teach_id) references teacher(tid)
)charset utf8;
// 添加数据
insert into course(cname,teach_id) values ('生物',1),('体育',1),('物理',2);
// 创建成绩表
create table score(
sid int auto_increment primary key,
student_id int not null default 0,
course_id int not null default 0,
number int not null default 0,
constraint sd foreign key(student_id) references student(sid),
constraint cd foreign key(course_id) references course(cid)
)charset utf8;
// 添加数据
insert into score (student_id,course_id,number) values (1,1,60),(1,2,59),(2,2,100);
1.查询所有大于60分的学生的姓名和学号
select student.sname,score.sid from score left join student on student_id = student.sid where number >60;
mysql> select student.sid,student.sname from student left join score on student_id=student.sid where number >60;
/*
+-----+-------+
| sid | sname |
+-----+-------+
| 2 | 铁锤 |
+-----+-------+
1 row in set (0.00 sec)
2.查询每个老师教授的课程数量 和 老师信息
select tid , tname , cname from course left join teacher on teach_id = tid;
/*
+------+--------+-------+
| tid | tname | cname |
+------+--------+-------+
| 1 | 波多 | 生物 |
| 1 | 波多 | 体育 |
| 2 | 苍井空 | 物理 |
+------+--------+-------+
3 rows in set (0.01 sec)
3.查询学生的信息以及学生所在的班级信息
select sname,gender,caption from class left join student on cid = sid;
/*
+-------+--------+----------+
| sname | gender | caption |
+-------+--------+----------+
| 钢蛋 | 女 | 三年二班 |
| 铁锤 | 女 | 一年三班 |
| 山炮 | 男 | 三年一班 |
+-------+--------+----------+
4.学生中男生的个数和女生的个数
select gender,count(*) from student group by gender;
/*
+--------+------------+
| gender | count(sid) |
+--------+------------+
| 女 | 2 |
| 男 | 1 |
+--------+------------+
2 rows in set (0.00 sec)
5.获取所有学习'生物'的学生的学号和成绩；姓名
select student.sid,student.name,score.number from student left join score on student.sid=score.sid;
6.查询平均成绩大于60分的同学的学号与平均成绩
select student.sid,avg(number) from score group by student.sid having avg(number)>60;
7.查询姓李的老师的个数
select count(tname) from teacher where teacher.tname like '李%';
8.查询课程成绩小于60分的同学的学号,姓名
select sid,sname from student left join score on srudent.sid = score.student_id where number<60.
9.删除学习李平老师课的score表记录
delete from score where coure_id in (select cid from teacher left join course on tid=teach_id where tname = '李平');
// 获取李平老师教授课程的cid,然后判断score表中course_id是否在这些cid中,最后delete
10.查询各科成绩的最高分和最低分:以如下形式显示:课程ID,最高分,最低分;
select course_id,max(num),min(num) from score group by course_id;
11.查询每门课程被选修的学生数
select course_id,count(student_id) from score group by course_id;
12.查询只选修了一门课程的全部学生的学号和姓名
// 以学生分组,计算每个学生的选择课程数量,得到只选择一门课程的学生的姓名学号
select sid,sname,count(course_id) from student left join score on sid=student_id group by student_id having count(course_id)=1;
13.查询姓'张'的学生名单
select * from student where sname like '张%';
14.查询同名同姓的学生名单,并统计人数
select * from student group by sname;
15.查询每门功课的平均成绩,结果按平均成绩升序排列,相同时,按课程号降序排列.
// 以课程进行分组,获得每门课程的平均成绩,order by 进行排序
select course_id,avg(num) from score group by course_id order by avg(num)asc,course_id desc;
16.查询平均成绩大于85的所有学生的学号,姓名和平均成绩
// 按学生分组获得学生的平均成绩
select sid,sname,avg(num) from
student left join score
on sid=student_id
group by student_id having avg(num)>80;
17.查询课程名称为数学,且分数低于60 分的学生姓名和分数
select sname,number from
score left join student on sid=student_id
left join course on cid=course_id
where cname='数学' and number <60;
18.查询课程编号为3,且课程成绩在80分以上的学生的学号和姓名;
select sid,sname from
student left join score on sid = student_id
where course_id=3 and number>80;
19.查询各个课程即相应的选修人数
//以课程分组并计数每组人数
select course_id,count(student_id) from score group by course_id;
20.查询至少选修两门课程的学生学号
以学生分组,获得选课数量,获得大于等于2的学生学号
select student_id,count(course_id) from score group by student_id having count(course_id)>=2;
21.查询两门以上不及格课程的同学的学号及平均成绩
// 成绩小于60分的同学以学生分组,筛选出课程数量大于等于2的学号
select student_id from score where number<60 group by student_id having count(course_id)>=2;
22.查询'4'课程分数小于60,按分数降序排列的同学学号
// 当课程为4时,以学生分组,查询分数小于60的学生,按照分数降序排序得到学号
select sid from score where course_id=4 number<60 order by number desc;
23.删除"2"同学的"1"课程成绩
// 获得同学2的课程成绩,并删除
delete from score where student_id=2 and course_id = 1;