[toc] 一、 創建圖中的表 二、完成下列需求 1. 查詢所有大於60分的學生的姓名和學號 (DISTINCT: 去重) 2. 查詢每個老師教授的課程數量 和 老師信息 3. 查詢學生的信息以及學生所在的班級信息 4. 學生中男生的個數和女生的個數 5. 獲取所有學習'生物'的學生的學號和成績；姓 ...
目錄
一、 創建圖中的表
# 創建班級表
create table class (
cid int unsigned auto_increment primary key,
caption varchar(255) not null default ''
)charset utf8;
insert into class (caption) values
('三年六班'),('一年二班'),('三年十二班');
select * from class;
# 創建學生表，並外鍵關聯班級表
create table student(
sid int unsigned auto_increment primary key,
sname varchar(255) not null default '',
gender enum ('男','女'),
class_id int unsigned not null default 0,
constraint fk_stu_class foreign key (class_id) references class(cid)
)charset utf8;
insert into student (sname,gender,class_id) values
('宋冬野','女',1),('李志','男',1),('趙雷','男',2);
select * from student;
# 創建老師表
create table teacher(
tid int unsigned auto_increment primary key,
tname varchar(255) not null default ''
)charset utf8;
insert into teacher(tname) values('剝奪'),('蒼空'),('飯島');
select * from teacher;
#創建課程表，並關聯老師表
create table course(
cid int unsigned auto_increment primary key,
cname varchar(255) not null default '',
teacher_id int unsigned not null default 0,
constraint fk_course_tea foreign key (teacher_id)
references teacher(tid)
)charset utf8;
insert into course(cname,teacher_id) values
('生物',1),('體育',1),('物理',2);
select * from course;
# 創建成績表,並關聯學生表和課程表
create table score(
sid int unsigned auto_increment primary key,
student_id int unsigned not null default 0,
course_id int unsigned not null default 0,
number decimal(3,0) not null default 0,
constraint fk_score_stu foreign key (student_id) references student(sid),
constraint fk_score_course 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);
select * from score;二、完成下列需求
查詢所有大於60分的學生的姓名和學號 (DISTINCT: 去重)
select distinct student.sid,student.sname from score left join student on score.student_id = student.sid where score.number > 60;查詢每個老師教授的課程數量 和 老師信息
select tname,count(tname) as course_num from teacher right join course on teacher_id = tid group by tname ;查詢學生的信息以及學生所在的班級信息
select sname,caption from student left join class on cid = class_id;學生中男生的個數和女生的個數
select gender,count(gender) as number from student group by gender;獲取所有學習'生物'的學生的學號和成績；姓名
select student.sid,number,sname from student left join course on cid = class_id left join score on student_id = student.sid where course_id=1;查詢平均成績大於60分的同學的學號和平均成績；
select student_id,avg(number) from score group by student_id having avg(number) > 60;查詢姓“李”的老師的個數；
select count(tname) as num from teacher where tname like'李%';查詢課程成績小於60分的同學的學號、姓名；
select student.sid,sname from score left join student on student_id = student.sid where number < 60;刪除學習“葉平”老師課的SC表記錄
delete from score where course_id in (select cid from course left join teacher on tid = teacher_id where tname = '剝奪');查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；
select course_id,max(number),min(number) from score group by course_id;查詢每門課程被選修的學生數
select cname,count(cname) as student_num from course right join student on class_id=cid group by cname;查詢姓“張”的學生名單；
select sname from student where sname like'張%';查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列
select cname ,avg(number) from score left join course on course_id = cid order by cname asc,cid desc;查詢平均成績大於85的所有學生的學號、姓名和平均成績
select student.sid,sname,avg(number) from score left join student on student_id = student.sid group by sname,student.sid having avg(number) > 85;查詢課程編號為3且課程成績在80分以上的學生的學號和姓名；
select student.sid,sname from score left join student on student_id = student.sid where course_id=3 and number >80;查詢各個課程及相應的選修人數
select cname,count(cname) from course right join student on class_id = cid group by cname;查詢“4”課程分數小於60，按分數降序排列的同學學號
select student.sid from score join student on student_id = student.sid where number <60 order by student.sid desc ;刪除學號為“2”的同學的“1”課程的成績
delete number from score where student_id=2 and course_id = 1;
