day 35 作业

作业

# 创建班级表
creat table class (
cid int unsigned not null auto_increment primary key,
caption varchar(50) not null default ''
) charset=utf8;
  
insert into class (caption) values ('三年二班');
insert into class (caption) values ('一年三班');
insert into class (caption) values ('三年一班');

# 创建学生表
create table student(
sid int unsigned not null auto_increment primary key,
sname varchar(50) not null default '',
gender enum('male','female'),
class_id int unsigned not null default 1,
constraint fk_class_student foreign key (class_id) references class(cid)
) charset=utf8;

insert into student (sname,gender,class_id) values ('张三','male',1);
insert into student (sname,gender,class_id) values ('李四','female',2);
insert into student (sname,gender,class_id) values ('王二','male',3);
insert into student (sname,gender,class_id) values ('赵大','female',2);
insert into student (sname,gender,class_id) values ('钱五','male',3);
insert into student (sname,gender,class_id) values ('周八','male',2);

# 创建老师表
create table teacher(
tid int unsigned not null auto_increment primary key,
tname varchar(50) not null default ''
) charset=utf8;

insert into teacher (tname) values ('李老师');
insert into teacher (tname) values ('叶平');
insert into teacher (tname) values ('张老师');
insert into teacher (tname) values ('王老师');

# 创建课程表
create table course(
cid int unsigned not null auto_increment primary key,
cname varchar(50) not null default '',
teacher_id int unsigned not null default 1,
constraint fk_teacher_course foreign key (teacher_id) references teacher(tid)
) charset=utf8;

insert into course (cname,teacher_id) values ('生物',1);
insert into course (cname,teacher_id) values ('物理',2);
insert into course (cname,teacher_id) values ('体育',1);
insert into course (cname,teacher_id) values ('化学',3);

# 创建成绩表
create table score(
sid int unsigned not null auto_increment primary key,
student_id int unsigned not null default 1,
course_id int unsigned not null default 1,
number decimal(4,1) not null default 0,
constraint fk_student_score foreign key (student_id) references student(sid),
constraint fk_course_score foreign key (course_id) references course(cid)
) charset=utf8;

insert into score (student_id,course_id,number) values (1,1,50);
insert into score (student_id,course_id,number) values (1,2,70);
insert into score (student_id,course_id,number) values (1,3,90);
insert into score (student_id,course_id,number) values (1,4,100);

insert into score (student_id,course_id,number) values (2,1,45);
insert into score (student_id,course_id,number) values (2,2,59);
insert into score (student_id,course_id,number) values (2,3,98);
insert into score (student_id,course_id,number) values (2,4,70);

insert into score (student_id,course_id,number) values (3,1,57);
insert into score (student_id,course_id,number) values (3,2,70);
insert into score (student_id,course_id,number) values (3,3,60);
insert into score (student_id,course_id,number) values (3,4,40);

insert into score (student_id,course_id,number) values (4,1,78);
insert into score (student_id,course_id,number) values (4,2,46);
insert into score (student_id,course_id,number) values (4,3,94);
insert into score (student_id,course_id,number) values (4,4,24);

insert into score (student_id,course_id,number) values (5,1,24);
insert into score (student_id,course_id,number) values (5,2,98);
insert into score (student_id,course_id,number) values (5,3,43);
insert into score (student_id,course_id,number) values (5,4,36);

insert into score (student_id,course_id,number) values (6,1,66);
insert into score (student_id,course_id,number) values (6,2,78);
insert into score (student_id,course_id,number) values (6,3,32);
insert into score (student_id,course_id,number) values (6,4,58);

-- 1. 查询所有大于60分的学生的姓名和学号  (DISTINCT: 去重)
mysql> select distinct student.sid,student.sname from student left join score on student.sid=student_id where number >60;
+-----+-------+
| sid | sname |
+-----+-------+
|   1 | 张三  |
|   2 | 李四  |
|   3 | 王二  |
|   4 | 赵大  |
|   5 | 钱五  |
|   6 | 周八  |
+-----+-------+
6 rows in set (0.00 sec)

-- 2.查询每个老师教授的课程数量 和 老师信息
mysql> select teacher.tid,teacher.tname,count(teacher_id) as count from teacher left join course on teacher.tid=course.teacher_id group by teacher_id;
+-----+--------+-------+
| tid | tname  | count |
+-----+--------+-------+
|   4 | 王老师 |     0 |
|   1 | 李老师 |     2 |
|   2 | 叶平   |     1 |
|   3 | 张老师 |     1 |
+-----+--------+-------+
4 rows in set (0.00 sec)


-- 3. 查询学生的信息以及学生所在的班级信息
mysql> select sid,sname,gender,caption from student left join class on student.class_id=class.cid;
+-----+-------+--------+----------+
| sid | sname | gender | caption  |
+-----+-------+--------+----------+
|   1 | 张三  | male   | 三年二班 |
|   2 | 李四  | female | 一年三班 |
|   4 | 赵大  | female | 一年三班 |
|   6 | 周八  | male   | 一年三班 |
|   3 | 王二  | male   | 三年一班 |
|   5 | 钱五  | male   | 三年一班 |
+-----+-------+--------+----------+
6 rows in set (0.00 sec)

-- 4、学生中男生的个数和女生的个数
mysql> select gender,count(sid) as count from student group by gender;
+--------+-------+
| gender | count |
+--------+-------+
| male   |     4 |
| female |     2 |
+--------+-------+
2 rows in set (0.00 sec)

-- 5、获取所有学习'生物'的学生的学号和成绩；姓名
mysql> select student.sid,student.sname,score.number from student left join score on student.sid=student_id where course_id=1;
+-----+-------+--------+
| sid | sname | number |
+-----+-------+--------+
|   1 | 张三  |   50.0 |
|   2 | 李四  |   45.0 |
|   3 | 王二  |   57.0 |
|   4 | 赵大  |   78.0 |
|   5 | 钱五  |   24.0 |
|   6 | 周八  |   66.0 |
+-----+-------+--------+
6 rows in set (0.00 sec)

-- 6、查询平均成绩大于60分的同学的学号和平均成绩； 
mysql> select student_id,avg(number) from score group by student_id having avg(number) > 60;
+------------+-------------+
| student_id | avg(number) |
+------------+-------------+
|          1 |    77.50000 |
|          2 |    68.00000 |
|          4 |    60.50000 |
+------------+-------------+
3 rows in set (0.00 sec)

-- 7、查询姓“李”的老师的个数；
mysql> select count('李%')as count from teacher where tname like '李%';
+-------+
| count |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

-- 8、查询课程成绩小于60分的同学的学号、姓名；
mysql> select distinct student.sid,student.sname from student left join score on student.sid=student_id where number <60;       
+-----+-------+
| sid | sname |
+-----+-------+
|   1 | 张三  |
|   2 | 李四  |
|   3 | 王二  |
|   4 | 赵大  |
|   5 | 钱五  |
|   6 | 周八  |
+-----+-------+
6 rows in set (0.00 sec)

-- 9. 删除学习“叶平”老师课的SC表记录


-- 10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
mysql> select course.cid,max(number),min(number) from score left join course on course_id=course.cid group by course_id;        
+------+-------------+-------------+
| cid  | max(number) | min(number) |
+------+-------------+-------------+
|    1 |        78.0 |        24.0 |
|    2 |        98.0 |        46.0 |
|    3 |        98.0 |        32.0 |
|    4 |       100.0 |        24.0 |
+------+-------------+-------------+
4 rows in set (0.00 sec)

-- 11.查询每门课程被选修的学生数



-- 12.查询姓“张”的学生名单；
mysql> select sname from student where sname like '张%';
+-------+
| sname |
+-------+
| 张三  |
+-------+
1 row in set (0.00 sec)

-- 13.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
mysql> select course_id,avg(number) as avg from score left join course on course_id=course.cid group by course_id order by avg asc,course_id desc;
+-----------+----------+
| course_id | avg      |
+-----------+----------+
|         1 | 53.33333 |
|         4 | 54.66667 |
|         3 | 69.50000 |
|         2 | 70.16667 |
+-----------+----------+
4 rows in set (0.00 sec)

-- 14.查询平均成绩大于60的所有学生的学号、姓名和平均成绩
mysql> select student.sid,sname,avg(number) as avg from score left join student on student_id=student.sid group by student_id having avg > 60;
+------+-------+----------+
| sid  | sname | avg      |
+------+-------+----------+
|    1 | 张三  | 77.50000 |
|    2 | 李四  | 68.00000 |
|    4 | 赵大  | 60.50000 |
+------+-------+----------+
3 rows in set (0.00 sec)

-- 15.查询课程编号为3且课程成绩在60分以上的学生的学号和姓名；
mysql> select student.sid,student.sname from score left join student on student.sid=student_id where score.course_id=3 and number>60;
+------+-------+
| sid  | sname |
+------+-------+
|    1 | 张三  |
|    2 | 李四  |
|    4 | 赵大  |
+------+-------+
3 rows in set (0.00 sec)

-- 16.查询各个课程及相应的选修人数
mysql> select cname,count(course_id) from score right join course on course_id=course.cid group by course_id;
+-------+------------------+
| cname | count(course_id) |
+-------+------------------+
| 生物  |                6 |
| 物理  |                6 |
| 体育  |                6 |
| 化学  |                6 |
+-------+------------------+
5 rows in set (0.00 sec)

-- 17.查询“4”课程分数小于60，按分数降序排列的同学学号

-- 18.删除学号为“2”的同学的“1”课程的成绩