一、建表语句,并且插入数据
学生表student
create table student(
student_id int(6) primary key auto_increment,
student_name varchar(12) not null,
student_age int(3),
student_sex varchar(4) not null
);
insert into student values(1000,'科比',16,'男'),(1001,'乔丹',17,'男'),(1002,'詹姆斯',18,'男'),(1003,'库里',14,'女'),(1004,'巴特勒',14,'男'),(1005,'乔治',21,'女'),(1006,'伦纳德',21,'男'),(1007,'汤普森',19,'男');
教师表teacher
create table teacher(
teacher_id int(6) primary key auto_increment,
teacher_name varchar(5) not null,
teacher_sex varchar(4) not null,
teacher_age int(3)
);
insert into teacher values(1,'波波','男',30),(2,'斯波','女',31),(3,'科尔','男',33),(4,'里弗斯','女',28),(5,'少帅','女',27),(6,'小红','女',30),(7,'谢霆锋','女',45),(8,'吴京','男',41),(9,'张三','男',28),(10,'李四','男',35);
课程表course
create table course(
course_id int(6) primary key auto_increment,
course_name varchar(10) not null,
teacher_id int(6)
);
insert into course values(301,'高等数学',1),(302,'线性代数',2),(303,'概率论',3),(304,'计算机基础',4),(305,'计算机原理',5),(306,'原件工程',6),(307,'python',7),(308,'java',8),(309,'c',9),(310,'c++',10),(311,'shell',1),(312,'javascript',2),(313,'单片机',3),(311,'NBA',4),(312,'mysql',5),(313,'劳动课',6);
成绩表score
create table score(
student_id int(6),
course_id varchar(10) not null,
student_score int(6)
);
insert into score values(1000,301,98),(1001,302,97),(1002,303,82),(1003,304,88),(1004,305,70),(1005,306,54),(1006,307,62),(1007,308,93),(1008,309,98),(1009,310,89),(1000,311,54),(1001,312,97),(1002,313,99),(1003,314,66),(1004,315,95);
二、简单查询
(1)查询学生表所有数据
select * from student;
(2)查询姓“巴”学生
SELECT * FROM student where student_name LIKE "巴%";
(3)查询男性教师人数
select count(*) as 男性人数 from student where student_sex="男"
(4)查询男性、女性教师人数
select student_sex as 性别,count(*) as 总数 from student group by student_sex;
(5)查询各科成绩最高和最低的分,并以课程ID,课程最高分,课程最低分形式显示。
select score.course_id as 课程id,max(student_score) as 最高分,min(student_score) as 最低分
from score
group by course_id;
三、复杂查询
(6)查询各科成绩最高和最低的分,并以课程ID,课程名称,授课老师,最高分,最低分形式显示。
select course.course_id as 课程id,MAX(student_score) as 最高分,MIN(student_score) as 最低分,course_name,teacher_name
from course,teacher,score
where course.course_id=score.course_id and teacher.teacher_id=course.teacher_id
GROUP BY 课程id;
(7)查询学过“斯波老师”所教的所有课的同学的学号、姓名
1、查询斯波老师所教的课的课程id
select course.course_id
from course,teacher
where course.teacher_id=teacher.teacher_id and teacher_name='斯波';
2、查询选择该课程的学生
select student.student_id,student.student_name,score.course_id
from student,score
where student.student_id=score.student_id and score.course_id=(select course.course_id from course,teacher where course.teacher_id=teacher.teacher_id and teacher_name='斯波');
(8)查询没学过"林春雁"老师所教的课的学生的学号、姓名(重点)
先是3表联查
1、查询学过"波波"老师所教的课的学生id
select score.student_id
from score,teacher,course
where score.course_id=course.course_id and teacher.teacher_id=course.teacher_id
and teacher.teacher_name='波波';
2、查询学过"波波"老师所教的课的学生的学号、姓名
select student.student_id,student.student_name
from student
where student_id in (select score.student_id
from score,teacher,course
where score.course_id=course.course_id and teacher.teacher_id=course.teacher_id
and teacher.teacher_name='波波');
3、查询没有学过"波波"老师所教的课的学生的学号、姓名
select student.student_id,student.student_name
from student
where student_id not in (select score.student_id
from score,teacher,course
where score.course_id=course.course_id and teacher.teacher_id=course.teacher_id
and teacher.teacher_name='波波');
(9)查询学过课程ID为‘301’和‘302’的学生的学号、姓名(重点)
这道题最最最关键的所在,(划重点)就是:把学过编号为“01”课程的同学查询出来,再把学过编号为“02”课程的同学查询出来,然后把两张表合并到一张表中。
错误sql编写:
select student.student_id,student.student_name
from student,score
where student.student_id=score.student_id
and score.course_id=301 and score.course_id=302;
1、查询学过301课程的学生
select student_id
from score
where course_id=301;
2、查询学过302课程的学生
select student_id
from score
where course_id=302;
3、将查询到,学过课程编号为301的同学id,作为表a,和查询到,学过课程编号为302的同学id,作为表b, 通过内连接的方式合并
select *
from (select student_id from score where course_id=301) as a
INNER JOIN (select student_id from score where course_id=302) as b
on a.student_id=b.student_id;
4、将student_id通过student表获取学生信息。因为最终是一组数据,所以用IN关键字
select student.student_id,student.student_name
from student
where student.student_id IN(select a.student_id
from (select student_id from score where course_id=301) as a
INNER JOIN (select student_id from score where course_id=302) as b
on a.student_id=b.student_id);
(10)查询学过学号为1005同学所有课程的同学的学号
1、查询学号为1005同学所学的所有课程
select score.course_id
FROM student,score
where student.student_id=score.student_id and student.student_id=1005;
2、综合查询
select student.student_id,student.student_name,score.course_id
FROM student,score
where student.student_id=score.student_id and score.course_id = (select score.course_id
FROM student,score
where student.student_id=score.student_id and student.student_id=1005) AND student.student_id!=1005;
(11)查询不同老师所教不同课程平均分从高到低显示
SELECT course.teacher_id,course.course_name,AVG(score.student_score)
from course,score
where course.course_id=score.course_id
group by course.teacher_id
ORDER BY AVG(score.student_score) DESC;
(12)查询选修“波波”老师所授课程的学生中成绩最高的学生姓名,课程名及其成绩
1、查询“波波”老师所教的课程
select course.course_id
FROM course,teacher
where course.teacher_id=teacher.teacher_id and teacher.teacher_name='波波';
2、查询学该课程的所有学生
select student.student_name,score.student_score,score.course_id
from score,student
where student.student_id=score.student_id and score.course_id = (select course.course_id
FROM course,teacher
where course.teacher_id=teacher.teacher_id and teacher.teacher_name='波波')
ORDER BY score.student_score
DESC
LIMIT 1;
(13)查询至少有一门课与学号为“1001”的学生所学课程相同的学生的学号和姓名
1、查询学号为“1001”的学生所学课程
select score.course_id
from score
where score.student_id=1001;
2、查询所学该课程的学生
select student.student_id,student.student_name,score.course_id
from student,score
where student.student_id=score.student_id and score.course_id in (select score.course_id
from score
where score.student_id=1001);
(14)查询某门课程成绩小于等于60分的学生的学号、姓名
1、查询成绩小于等于60分的学生
select score.student_score
from score
where score.student_score<=60;
2、查询选择该课程的学生
select student.student_id,student.student_name,score.student_score
from student,score
where student.student_id=score.student_id and score.student_score in(select score.student_score
from score
where score.student_score<=60);
(15)按各科的成绩从高到低排列,以如下形式显示:课程号、课程名、平均成绩。
select course.course_id,course.course_name,AVG(score.student_score)
from course,score
where score.course_id=course.course_id
GROUP BY course.course_id
order by AVG(score.student_score)
desc;