表结构准备:
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/*==============================================================*/
drop table if exists tb_course;
drop table if exists tb_score;
drop table if exists tb_student;
drop table if exists tb_teacher;
/*==============================================================*/
/* Table: tb_course */
/*==============================================================*/
create table tb_course
(
id int not null auto_increment,
name varchar(20),
description varchar(200),
primary key (id)
);
/*==============================================================*/
/* Table: tb_score */
/*==============================================================*/
create table tb_score
(
id int not null auto_increment,
stu_id int,
course_id int,
teacher_id int,
score int,
primary key (id)
);
/*==============================================================*/
/* Table: tb_student */
/*==============================================================*/
create table tb_student
(
id int not null auto_increment,
name varchar(20),
sex char(6),
stu_num varchar(20),
age int,
primary key (id)
);
/*==============================================================*/
/* Table: tb_teacher */
/*==============================================================*/
create table tb_teacher
(
id int not null auto_increment,
name varchar(20),
sex varchar(6),
primary key (id)
);
alter table tb_score add constraint FK_COURSE_SCORE foreign key (course_id) references tb_course (id) on delete restrict on update restrict;
alter table tb_score add constraint FK_STU_SCORE foreign key (stu_id) references tb_student (id) on delete restrict on update restrict;
alter table tb_score add constraint FK_Teacher_SCORE foreign key (teacher_id) references tb_teacher (id) on delete restrict on update restrict;
下面sql语句可以添加数据
insert into tb_course values
(null, "数学","数学"),
(null, "英语","英语"),
(null, "语文","语文"),
(null, "物理","物理"),
(null, "化学","化学"),
(null, "生物","生物");
insert into tb_teacher VALUES
(null, "石老师", "男"),
(null, "田老师", "女"),
(null, "杨老师", "男"),
(null, "张老师", "女"),
(null, "李老师", "男"),
(null, "马老师", "男");
insert into tb_student VALUES
(null, "刘备","男",1, 50),
(null, "关羽","男",2, 48),
(null, "张飞","男",3, 46),
(null, "貂蝉","女",4, 20),
(null, "杨玉环","女",5,24),
(null, "西施","女",6, 22),
(null, "如花","女",7, 18);
insert into tb_score VALUE
(null, 1, 1, 1 ,80),
(null, 1, 2, 3 ,60),
(null, 2, 1, 3 ,40),
(null, 3, 2, 2 ,70),
(null, 4, 5, 4 ,90),
(null, 3, 1, 1 ,100),
(null, 4, 1, 1 ,89),
(null, 1, 3, 3 ,99),
(null, 1, 4, 3 ,47),
(null, 5, 5, 5 ,90),
(null, 5, 2, 2 ,38),
(null, 6, 1, 4 ,100),
(null, 6, 4, 3 ,89),
(null, 6, 2, 3 ,88),
(null, 6, 4, 1 ,77);
查询李老师所教的课程
select *
from tb_course
where
id in(select
course_id
from tb_score
where teacher_id = (
select
id
from tb_teacher
where name = "李老师"))
SELECT teacher.`name` AS '⽼师',
course.`name`AS '课程'
FROM tb_course course LEFT JOIN tb_score score
ON score.course_id = course.id LEFT JOIN tb_teacher teacher
ON score.teacher_id = teacher.id
WHERE teacher.NAME = '李⽼师' ;
SELECT
tc.name
from tb_course tc ,tb_score ts
where
tc.id = ts.course_id and ts.teacher_id = (
select
ts.teacher_id
from tb_score ts,tb_teacher tt
where ts.teacher_id = tt.id and tt.name="李老师");
SELECT
name
FROM tb_course
WHERE id IN(
SELECT course_id
FROM tb_score sc,tb_teacher te
WHERE sc.teacher_id = te.id AND te.`name` = '李⽼师' );
查询西施选修的课程
select *
from tb_course
where id in(
select
course_id
from tb_score
where stu_id = (
select id
from tb_student
where name = "西施"))
SELECT
DISTINCT tc.name
from tb_course tc ,tb_score ts
where tc.id = ts.course_id and ts.stu_id =(
select ts.stu_id
from tb_score ts ,tb_student tst
where ts.stu_id = tst.id and tst.name="西施" limit 1);
SELECT name
FROM tb_course co, (
SELECT course_id
FROM tb_student st,tb_score sc
WHERE st.id = sc.stu_id AND st.name = '⻄施'
) ci
WHERE co.id = ci.course_id
GROUP BY name;
查询杨玉环所有选修课程的总分
select
sum(score)
from tb_score
where stu_id = (
select id
from tb_student
where name = "杨玉环")
select SUM(score)
from tb_score ,tb_student
where
tb_score.stu_id = tb_student.id
and tb_student.name= "杨玉环";
查询每个学生选修的课程总分(没有选修课程的学生也要显示)
select stu.name, sum(s.score)
from tb_student stu left join tb_score s
on stu.id = s.stu_id
group by s.stu_id;
SELECT student.NAME, SUM( score.score ) AS '总分'
FROM tb_student student
LEFT JOIN tb_score score
ON score.stu_id = student.id
GROUP BY student.id;
select st.name , SUM(sc.score)
from tb_score sc
right JOIN tb_student st
on sc.stu_id = st.id
GROUP BY st.id ;
select tst.name,sum(ts.score)
from tb_student tst
left OUTER join tb_score ts
on tst.id = ts.stu_id
group by tst.name;
SELECT name,SUM(score)
FROM tb_score sc
RIGHT JOIN tb_student st
ON sc.stu_id = st.id
GROUP BY name;
查询选修了数学的所有学生信息
select *
from tb_student
where id in(
select
stu_id
from tb_score
where course_id = (
select id
from tb_course
where name = "数学"))
SELECT
student.*
FROM tb_student student
LEFT JOIN tb_score score
ON score.stu_id = student.id
LEFT JOIN tb_course course
ON course.id = score.course_id
WHERE course.NAME = '数学';
select tst.*
from tb_student tst,tb_score ts
where tst.id=ts.stu_id
and ts.course_id=(
select tc.id
from tb_course tc
where tc.name = "数学");
SELECT st.*
FROM tb_student st, (
SELECT stu_id
FROM tb_score sc, tb_course co
WHERE sc.course_id = co.id AND co.name = '数学') si WHERE st.id = si.stu_id;
查询平均成绩不及格的学生信息
select *
from tb_student
where id = (
select stu_id
from tb_score
group by stu_id
having avg(score) < 60);
select *
from tb_student where id =(
select stu_id
from tb_score
group by stu_id
having AVG(score)<60);
SELECT *
FROM tb_student WHERE id IN(
SELECT stu_id
FROM tb_score
GROUP BY stu_id
HAVING AVG(score) < 60);
查询各科的平均分数
select
c.name,avg(score)
from tb_score s, tb_course c
where c.id = s.course_id
group by s.course_id;
SELECT
course.NAME AS '课程', COUNT( * ) AS '数量', SUM( score ) AS '总分',SUM( score ) / COUNT( * ) AS '平均分'
FROM tb_score score
LEFT JOIN tb_course course
ON score.course_id = course.id
GROUP BY course_id;
select
tc.name ,avg(ts.score)
from tb_course tc ,tb_score ts
where tc.id = ts.course_id
group by ts.course_id;
SELECT
name,AVG(score)
FROM tb_score sc
JOIN tb_course co
ON sc.course_id = co.id
GROUP BY name;
查询没有报李老师课的学生姓名
select
name
from tb_student
where id not in (
select stu_id
from tb_score
where teacher_id in (
select id
from tb_teacher
where name = "李老师")) ;
SELECT *
FROM tb_student
WHERE id NOT IN (
SELECT score.stu_id
FROM tb_score score LEFT JOIN tb_teacher teacher
ON score.teacher_id = teacher.id
WHERE teacher.`name` = '李⽼师' );
select DISTINCT tst.name
from tb_student tst ,tb_score ts
where tst.id= (
select ts.stu_id
from tb_score ts, tb_teacher tt
where ts.teacher_id = tt.id and tt.name!="李老师" limit 1 );
SELECT name
FROM tb_student
WHERE id NOT IN (
SELECT stu_id
FROM tb_score sc,tb_teacher te
WHERE sc.teacher_id = te.id AND te.`name` = '李⽼师' )
查询每科选修的学生个数(显示科目和学生个数)
select
count(s.stu_id) 个数,c.name 姓名
from tb_score s, tb_course c
where c.id = s.course_id
group by s.course_id ;
SELECT
course.`name` AS '课程', COUNT( score.course_id ) AS '学⽣数'
FROM tb_score score LEFT JOIN tb_course course
ON course.id = score.course_id
GROUP BY score.course_id;
select
tc.name,COUNT(ts.stu_id)
from tb_course tc,tb_score ts
where tc.id=ts.course_id
group by tc.name;
SELECT `name`,COUNT(cs.stu_id) FROM tb_course co LEFT JOIN (
select course_id,stu_id
from tb_score
group by course_id,stu_id) cs
ON cs.course_id = co.id
GROUP BY `name`;
查询所有学生信息,包括任课老师,各科成绩,课程名称(没有选修课程的也要显示)
select stu.name,tb.courseName, tb.teacherName,tb.score
from tb_student stu left join (
select stu_id, c.name courseName ,t.name teacherName, s.score
from tb_score s,tb_course c, tb_teacher t where s.teacher_id = t.id and c.id = s.course_id) tb
on stu.id = tb.stu_id
SELECT
student.`name` AS '学⽣', course.`name` AS '科⽬', teacher.`name` AS '教师',score.score AS '成绩'
FROM tb_student student LEFT JOIN tb_score score
ON student.id = score.stu_id LEFT JOIN tb_course course
ON course.id = score.course_id LEFT JOIN tb_teacher teacher
ON teacher.id = score.teacher_id;
select st.* ,sc.score,c.name 选修课程 ,tc.name 任课老师
from tb_student st left join tb_score sc
on st.id = sc.stu_id LEFT JOIN tb_teacher tc
on sc.teacher_id = tc.id LEFT JOIN tb_course c
on c.id = sc.course_id;
SELECT * FROM tb_student st LEFT JOIN (
SELECT stu_id,co.descripition,score,te.`name`
FROM tb_score sc,tb_teacher te,tb_course co WHERE sc.course_id = co.id AND sc.teacher_id = te.id) stc
ON st.id = stc.stu_id;