1030 作业

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;