Mysql 子查询,隐式连接,显式连接,左右外连接 案例

所有查询语句均为手打 ,所以可能我写的有些子查询办法有些蠢,不过我觉得首先能真的用明白子查询语句才是第一步;

每个需求的下的最后一条select语句为该需求最终子查询语句;

这些都能敲出来我觉得子查询第一步是学扎实了;

-- 第一大题:
create database mydb4;
use mydb4;
create table A(
  A_ID int primary key auto_increment,
  A_NAME varchar(20) not null
);
insert into A values(1,'苹果');
insert into A values(2,'橘子');
insert into A values(3,'香蕉');

create table B( 
   A_ID int primary key auto_increment,
   B_PRICE double
);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);
-- 需求：查询出每个水果的价格：
-- 显示内连接:
select I.`A_ID` ,O.`B_PRICE` from a I inner join b O on I.A_ID = O.A_ID;
-- 隐式内连接:
select n.`A_NAME`,o.`B_PRICE` from a n,b o where n.`A_ID` = o.`A_ID`;
-- 外连接查询:
-- 左外连接:
select c.`A_NAME` 水果名,m.`B_PRICE` 价格 from a c left outer join b m on c.`A_ID` = m.`A_ID`
-- 右外连接:
select a.`A_NAME` 水果名, m.`B_PRICE` 价格 from a  right outer join b m on a.`A_ID` = m.`A_ID`

-- 子查询：把一个sql的查询结果作为另外一个查询的参数存在
-- 第一步: 在b表中查询最高的价格, 第二步:查询与最高价格相对应的b表id
-- 第三步: 在a表中查询出与b表中刚查询出的id相等的id
select a.`A_NAME` from a where a.`A_ID` =(select b.`A_ID` from b where b.`B_PRICE` = (select MAX(b.`B_PRICE`)from b));

-- -------------------------------------------------------------------------------------
-- 第二大题

create table teacher (
  id int(11) not null primary key auto_increment,
  name varchar(20) not null unique
 );
create table student (
  id int(11) not null primary key auto_increment,
  name varchar(20) not null unique,
  city varchar(40) not null,
  age int 
) ;
create table course(
  id int(11) not null primary key auto_increment,
  name varchar(20) not null unique,
  teacher_id int(11) not null,
  foreign key (teacher_id) references teacher (id)
);

create table studentcourse (
   student_id int not null,
   course_id int not null,
   score double not null,
   foreign key (student_id) references student (id),
   foreign key (course_id) references course (id)
);

insert into teacher values(null,'关羽');
insert into teacher values(null,'张飞');
insert into teacher values(null,'赵云');

insert into student values(null,'小王','北京',20);
insert into student values(null,'小李','上海',18);
insert into student values(null,'小周','北京',22);
insert into student values(null,'小刘','北京',21);
insert into student values(null,'小张','上海',22);
insert into student values(null,'小赵','北京',17);
insert into student values(null,'小蒋','上海',23);
insert into student values(null,'小韩','北京',25);
insert into student values(null,'小魏','上海',18);
insert into student values(null,'小明','广州',20);

insert into course values(null,'语文',1);
insert into course values(null,'数学',1);
insert into course values(null,'生物',2);
insert into course values(null,'化学',2);
insert into course values(null,'物理',2);
insert into course values(null,'英语',3);

insert into studentcourse values(1,1,80);
insert into studentcourse values(1,2,90);
insert into studentcourse values(1,3,85);
insert into studentcourse values(1,4,78);
insert into studentcourse values(2,2,53);
insert into studentcourse values(2,3,77);
insert into studentcourse values(2,5,80);
insert into studentcourse values(3,1,71);
insert into studentcourse values(3,2,70);
insert into studentcourse values(3,4,80);
insert into studentcourse values(3,5,65);
insert into studentcourse values(3,6,75);
insert into studentcourse values(4,2,90);
insert into studentcourse values(4,3,80);
insert into studentcourse values(4,4,70);
insert into studentcourse values(4,6,95);
insert into studentcourse values(5,1,60);
insert into studentcourse values(5,2,70);
insert into studentcourse values(5,5,80);
insert into studentcourse values(5,6,69);
insert into studentcourse values(6,1,76);
insert into studentcourse values(6,2,88);
insert into studentcourse values(6,3,87);
insert into studentcourse values(7,4,80);
insert into studentcourse values(8,2,71);
insert into studentcourse values(8,3,58);
insert into studentcourse values(8,5,68);
insert into studentcourse values(9,2,88);
insert into studentcourse values(10,1,77);
insert into studentcourse values(10,2,76);
insert into studentcourse values(10,3,80);
insert into studentcourse values(10,4,85);
insert into studentcourse values(10,5,83);



-- 需求：查询不及格的学生(使用in完成)
select `student_id` from `studentcourse` where `score` < 60;
select  * from student where `id` in(select `student_id` from `studentcourse` where `score` < 60);

-- 使用exists 完成:
select * from student where exists (
	select `student_id` from `studentcourse` 
		where `score` < 60 and `studentcourse`.`student_id` = `student`.`id` );

-- 需求：查询获得最高分的学生信息：

select `student_id` from `studentcourse`
	where score = (select MAX(score) from `studentcourse`);
	
select * from `student` where `id` = (select `student_id` from `studentcourse`
	where score in (select MAX(score) from `studentcourse`));

-- 需求：查询编号2课程比编号1课程最高成绩高学生信息：
select name from `course` where `id` = 1;
select MAX(score) from `studentcourse` where `course_id` = 1;
select `student_id` from `studentcourse`
	where score > (select MAX(score) from `studentcourse` where `course_id` = 1)
		and `course_id` = 2;

select * from student where `id` in(
	select `student_id` from `studentcourse`
	where score > (select MAX(score) from `studentcourse` where `course_id` = 1)
		and `course_id` = 2
);

-- 需求：查询编号2课程比编号1课程最高成绩高学生姓名和成绩 （临时表）

select `student`.`name`, ui.`score` from `student` , (
	select student_id,score from `studentcourse`
	where score > (select MAX(score) from `studentcourse` where `course_id` = 1)
		and `course_id` = 2) as ui
		where `student`.`id` = ui.`student_id`;


-- 查询平均成绩大于70分的同学的学号和平均成绩
select `student_id`,AVG(`score`)from `studentcourse`
	group by `student_id`
		having AVG(`score`) >70;
-- 查询所有同学的学号、姓名、选课数、总成绩
select `student_id`, COUNT(`student_id`) 选课数,SUM(`score`) 总成绩 from `studentcourse`
	group by `student_id`;

select * from (select `student_id`, COUNT(`student_id`) 选课数,SUM(`score`) 总成绩 from `studentcourse`
	group by `student_id`) u ,`student`
	where `student`.`id` = u.`student_id`;

select i.id,i.name 姓名 ,i.选课数,i.总成绩 from (select * from (select `student_id`, COUNT(`student_id`) 选课数,SUM(`score`) 总成绩 from `studentcourse`
	group by `student_id`) u ,`student`
	where `student`.`id` = u.`student_id`) i ;

-- 查询学过赵云老师所教课的同学的学号、姓名

select `id` from `teacher`
	where name = "赵云";
select id from `course`
	where `teacher_id` = (select `id` from `teacher`where name = "赵云");
select `student_id` from `studentcourse`
	where `course_id` = (select id from `course`
	where `teacher_id` = (select `id` from `teacher`
	where name = "赵云"));
	
select `id` 学号,`name` 姓名 from `student`
	where `id` in (select `student_id` from `studentcourse`
	where `course_id` = (select id from `course`
	where `teacher_id` = (select `id` from `teacher`
	where name = "赵云")));

-- 查询没学过关羽老师课的同学的学号、姓名
select `id` from `teacher`
	where name = "关羽";
select id from `course`
	where `teacher_id` = (select `id` from `teacher`where name = "关羽");
select  `student_id`  from `studentcourse`
	where `course_id`  in (select id from `course`
	where `teacher_id` = (select `id` from `teacher`
	where name = "关羽"));

select `id`,`name` from `student`where `id` not in (select  `student_id`  from `studentcourse`
	where `course_id`  in (select id from `course`
	where `teacher_id` = (select `id` from `teacher`
	where name = "关羽")));

-- 查询没有学三门课以上的同学的学号、姓名
select COUNT(`student_id`) 选课数,`student_id` id from `studentcourse`
	group by `student_id`
	having COUNT(`student_id`) <4;

select `student`.`id` 学号 ,`student`.`name` 姓名,选课数  from `student`,(select COUNT(`student_id`) 选课数,`student_id` id from `studentcourse`
	group by `student_id`having COUNT(`student_id`) <4) u
	where `student`.`id` = u.id;

-- 查询各科成绩最高和最低的分
select `course_id`,MAX(`score`) 最高分,MIN(`score`) 最低分 from `studentcourse`
	group by `course_id`;
	
select i.`name` 学科,最高分,最低分 from (select `course_id`,MAX(`score`) 最高分,MIN(`score`) 最低分 from `studentcourse`
	group by `course_id`) p,`course` i
	where p.course_id = i.`id`;

-- 查询学生信息和平均成绩
select `student_id` 学号,AVG(`score`) 平均成绩 from `studentcourse`
	group by `student_id`;
	
select 学号,stu.`name`,平均成绩,stu.`age`,stu.`city` from (select `student_id` 学号,AVG(`score`) 平均成绩 from `studentcourse`
	group by `student_id`
) c ,`student` stu
	where stu.`id` = 学号
	order by 平均成绩 desc;
	
-- 8、查询上海和北京学生数量
select COUNT(*),`city` from `student`
	group by `city`
	having `city` = "上海" or `city` = "北京";
-- 9、查询不及格的学生信息和课程信息
select * from `studentcourse`
	where `score`<60;
select stu.*,c.`name` 不及格课程号,不及格成绩 from (select `student_id` 学号,`course_id` 不及格课程号,`score` 不及格成绩 from `studentcourse`
	where `score`<60) u ,`student` stu ,`course` c
	where 学号 = stu.id and 不及格课程号 = c.`id`;
-- 10、统计每门课程的学生选修人数（超过四人的进行统计）
select `course_id` 课程号, COUNT(`course_id`) 选修人数 from `studentcourse`
	group by `course_id`
	having COUNT(`course_id`)>4;
select `course`.`name` 课程名称,选修人数 from `course` ,(select `course_id` 课程号, COUNT(`course_id`) 选修人数 from `studentcourse`
	group by `course_id`
	having COUNT(`course_id`)>4) p
	where `course`.`id` = 课程号
	order by 选修人数 desc;


	
	

 _不喜欢数据库

_真香!