day35作业

分析：班级表--学生表，一对多

班级表 class：

cid caption

学生表 student：

sid sname gender class_id

# 创建班级表 class：
create table class(
	cid int auto_increment primary key,
    caption varchar(32) not null default ''
)charset=utf8;


# 插入数据
insert into class (caption) values ("110班"),("119班"),("120班");


# 创建学生表 student：
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 1,
    constraint student_class_key foreign key(class_id) references class(cid)
)charset=utf8;

# 插入数据
insert into student (sname,gender,class_id) values ("张三","男",1),("李四","男",1),("翠花","女",2),("小红","女",3);




# 查看数据
mysql> select * from class;
+-----+---------+
| cid | caption |
+-----+---------+
|   1 | 110班   |
|   2 | 119班   |
|   3 | 120班   |
+-----+---------+
3 rows in set (0.00 sec)

mysql> select * from student;
+-----+--------+--------+----------+
| sid | sname  | gender | class_id |
+-----+--------+--------+----------+
|   1 | 张三   | 男     |        1 |
|   2 | 李四   | 男     |        1 |
|   3 | 翠花   | 女     |        2 |
|   4 | 小红   | 女     |        3 |
+-----+--------+--------+----------+
4 rows in set (0.00 sec)

分析：老师表--课程表，一对多

老师表 teacher：

tid tname

课程表 course：

cid cname teacher_id

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

# 插入数据
insert into teacher (tname) values ("nick"),("tank"),("echo");


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


# 插入数据
insert into course (cname,teacher_id) values ("python",1),("linux",2),("go",3),("shell",1);



# 查看数据
mysql> select * from teacher;
+-----+-------+
| tid | tname |
+-----+-------+
|   1 | nick  |
|   2 | tank  |
|   3 | echo  |
+-----+-------+
3 rows in set (0.00 sec)

mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | python |          1 |
|   2 | linux  |          2 |
|   3 | go     |          3 |
|   4 | shell  |          1 |
+-----+--------+------------+
4 rows in set (0.00 sec)

分析：成绩表--课程表--学生表，多对多

学生表 student：

sid sname gender class_id

课程表 course：

cid cname teacher_id

成绩表 score：

sid student_id course_id number

# 创建成绩表 score：
create table score(
	sid int auto_increment primary key,
    student_id int not null default 1,
    course_id int not null default 1,
    number int not null default 0,
    constraint score_student_key foreign key (student_id) references student(sid),
    constraint score_course_key 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),(3,2,94);


# 查询数据
mysql> select * from score;
+-----+------------+-----------+--------+
| sid | student_id | course_id | number |
+-----+------------+-----------+--------+
|   1 |          1 |         1 |     60 |
|   2 |          1 |         2 |     59 |
|   3 |          2 |         2 |    100 |
|   4 |          3 |         2 |     94 |
+-----+------------+-----------+--------+
4 rows in set (0.00 sec)

查询所有大于60分的学生的姓名和学号 (DISTINCT: 去重)

select student.sid,number,sname from score 
	left join student on student_id=student.sid 
	where number > 60;

-- 2.查询每个老师教授的课程数量 和 老师信息

select tid,tname,cname from course 
	left join teacher on teacher_id=teacher.tid; 

-- 3. 查询学生的信息以及学生所在的班级信息

select sid,sname,gender,caption from student 
	left join class on class_id=class.cid;

-- 4、学生中男生的个数和女生的个数

select gender,count(*) from student group by gender;

-- 5、获取所有学习'生物'的学生的学号和成绩；姓名

select student.sid,sname,number from score 
	left join student on student_id=student.sid;

-- 6、查询平均成绩大于60分的同学的学号和平均成绩；

select student.sid,avg(number) from score 
	left join student on student_id=student.sid 
	group by sname 
	having avg(number) > 60;

-- 7、查询姓“李”的老师的个数；

select count(*) from teacher where tname like "李%";

-- 8、查询课程成绩小于60分的同学的学号、姓名；

select student.sid,sname,number from score 
	left join course on course_id=course.cid 
	left join student on student_id=student.sid 
	where number < 60;

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

delete from score
	where course_id in 
	(select course.cid from score left join course on course_id=course.id where tame="叶平" )	# 不一定正确

-- 10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

select course.cid,max(number),min(number) from score 	
    left join course on course_id=course.cid 
	group by cname;

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

select cname,count(sname) from score 
	left join course on course_id=course.cid 
	left join student on student_id=student.sid 
	group by cname;

-- 12.查询姓“张”的学生名单；

select * from student where sname like "张%";

-- 13.查询每门课程的平均成绩，
结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select cid,cname,avg(number) from score 
	left join course on course_id=course.cid 
	group by cname 
	order by avg(number) asc;

-- 14.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select student.sid,sname, avg(number) from score 
	left join student on student_id=student.sid 
	group by sname 
	having avg(number) > 85;

-- 15.查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；

select * from score left join student on student_id=student.sid 
	left join course on course_id=course.cid 
	where number > 90 and course.cid=3;

-- 16.查询各个课程及相应的选修人数

select cname,count(cname) from score 
	left join student on student_id=student.sid 
	left join course on course_id=course.cid 
	group by cname;

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

select student.sid from score 
	left join course on course_id=course.cid 
	left join student on student_id=student.sid 
	where course.cid=4 and number < 60 
	order by number desc;

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

delete from score 
	where student_id=2 and course_id=1;