第四章 子查询 ② 代码

代码仅供参考

Mr.Yang

1.课前测

#创建班级表
create table classInfo
(
	classId int primary key auto_increment,
	className varchar(20)
)

select * from classInfo;

insert into classInfo
(className)
values
('AAA01'),
('AAA02')

#创建学生表
create table studentInfo
(
	studentId int primary key auto_increment,
	name varchar(20) not null,
	sex char(1) not null,
	birthday date,
	province varchar(20) default '河南',
	classId int,
	foreign key (classId)
	references classInfo(classId)
)

select * from studentInfo;

insert into studentInfo
(name,sex,birthday,province,classId)
values
('张三','男','2002-01-01','湖北',1),
('李四','女','2003-01-05','河南',2),
('王五','男','2010-03-01','湖北',1),
('赵六','男','2009-01-08','河南',2),
('孙琪','女','2001-09-01','湖北',1)

#创建课程表
create table courseInfo
(
	courseId int primary key auto_increment,
	courseName varchar(20) not null
)

select * from courseInfo;

insert into courseInfo
(courseName)
values
('html'),
('java'),
('sql')

#创建成绩表
create table examInfo
(
	examId int primary key auto_increment,
	studentId int not null,
	courseId int not null,
	score int,
	foreign key (studentId)
	REFERENCES studentInfo(studentId), #外键对应的类型必须一致
	foreign key (courseId)
	REFERENCES courseInfo(courseId)
)

select * from examInfo;
select * from studentInfo;

insert into examInfo
(studentId,courseId,score)
VALUES
(1,1,90),
(1,2,70),
(1,3,65),
(2,1,88),
(2,2,67),
(2,3,55),
(3,1,69),
(3,3,45)

select * from classInfo;
select * from studentinfo;
select * from examInfo;
select * from courseInfo;

#四表关联，查询班级名字，学生姓名，课程名字，分数
SELECT
	c.className,s.name,ci.courseName,e.score
from classInfo c
join studentInfo s
on c.classId = s.classId
join examinfo e 
on s.studentId = e.studentId
join courseInfo ci
on e.courseId = ci.courseId
and c.className='AAA01'    #关联条件过滤
#where c.className='AAA01' #where过滤


 #查询成绩，从高到低排序，显示姓名和总分
SELECT
	s.name,sum(e.score) 总分
from studentInfo s
join examInfo e
on s.studentId = e.studentId
group by s.name
order by 总分 asc

#查询没有参加考试的学生
#查询所有学生(外连接)的成绩
#方式一:
select 
	s.name
from studentInfo s
left join examinfo e
on s.studentId = e.studentId
where e.score is null

#分组汇总的写法:查找考试门数是0的学生
select 
	s.name,count(e.score) 考试门数
from studentInfo s
left join examinfo e
on s.studentId = e.studentId
group by s.name
having 考试门数=0

#查询平均分最低的课程
SELECT
	c.courseName,avg(e.score) 平均分
from courseInfo c
join examInfo e
on c.courseId = e.courseId
group by c.courseName
order by 平均分
LIMIT 1

2.自关联 【xyj】

#自关联(树形菜单，员工（上下级关系）)
#西游取经团(公司人事结构:职员，领导)
create table xyj
(
	id int primary key, #人员编号
	name varchar(20), #人员姓名
	pid int           #上级领导编号
)

select * from xyj;

insert into xyj
(id,name,pid)
VALUES
(1,'如来',null),
(2,'菩萨',1),
(3,'唐僧',2),
(4,'孙悟空',3),
(5,'猪八戒',3),
(6,'沙和尚',3)

#请查询所有员工自己的名字和上级的名字
select * from xyj

select x.name 自己, 
		y.name 领导
from xyj x #写在前边的表称为左表
left join xyj y #写在后边的表称为右表
on y.id=x.pid; #有点迷，需要多看课件概念。 #以左为尊的意思...
#参考【第三章 模糊查询与分组查询 ① 笔记 3.3.3 外连接】链接在代码后！

第三章 模糊查询与分组查询 ① 笔记 3.3.3 外连接；其中，核心相关知识点如下图：

3.mysql函数

#函数
#查询姓名和性别，要求显示为 张三(男) 李四(女)
#concat 字符串拼接
select name,sex ,concat(name,'(',sex,')')
from studentInfo;

#length 返回字符的字节数
select length('abc')
select length('张三')
#char_length():返回字符长度
select char_length('张三')

#查询名字是三个字的学生
select * from studentinfo
where char_length(name)=3

#日期函数
#返回系统日期函数
select now() #返回当前的系统日期
select year(now()) #返回当前日期的年份
select month(now()) #返回月
select date(now()) #返回年月日
#求两个日期差了多少年，月，天
select TIMESTAMPDIFF(month,'2019-01-01',now())

select name 大于18的学生姓名
from studentinfo
where timestampdiff(year,birthday,now())>18;

#查询年龄大于18岁的学生？？？？？
select * from studentinfo;

#一年后的今天是哪一天
select DATE_ADD(now(),INTERVAL 1 year)

#空值处理
#对于null的判断要用 is null 或者 is not NULL

update studentinfo #首先设置studentinfo表的设计表，将sex的not null属性取消掉;然后更新表中数据sex为null
set sex=null
where studentId = 2

select * from studentInfo
where sex is null

select * from studentInfo
where sex is not null

#如果性别没有数据，请显示"未知"
#ifnull(列,'替换值'):如果列值为空，则替换为另外一个值
select name,ifnull(sex,'未知'),birthday 
from studentInfo

4.子查询

#子查询:简单子查询，相关子查询

#简单子查询:可以独立运行,就是一个简单的嵌套语句
#查询班级是AAA01的学生
SELECT
	*
from studentInfo s
join classInfo c
on s.classId = c.classId
where c.className = 'aaa01'

#简单子查询
SELECT
	*
from studentInfo 
where classId  = (select classId from classInfo where className = 'aaa01')

#与王五和李四，在同一个班级的学生
select * from studentinfo
where classId  in (
	select classId from studentinfo where name='王五' or name='李四'
)

5.简单子查询 相关子查询 【myexam】

#子查询:简单子查询，相关子查询

#简单子查询:可以独立运行,就是一个简单的嵌套语句
#查询班级是AAA01的学生
SELECT
	*
from studentInfo s
join classInfo c
on s.classId = c.classId
where c.className = 'aaa01'

#简单子查询
SELECT
	*
from studentInfo 
where classId  = (select classId from classInfo where className = 'aaa01')

#与王五和李四，在同一个班级的学生
select * from studentinfo
where classId  in (
	select classId from studentinfo where name='王五' or name='李四'
)

#相关子查询: 内外相关，用外部查询的内容作为内部查询的输入条件，
#            内部查询完了，再将查询结果返回给外部。
#            相关子查询不能独立运行，需要依赖于外部查询

#内连接查询
select
	s.name,c.courseName,e.score
from studentInfo s
join examInfo e
on s.studentId = e.studentId
join courseInfo c
on e.courseId = c.courseId

#相关子查询实现
select 
	(select name from studentInfo where studentId =e.studentId ),
	(select courseName from courseInfo where courseId = e.courseId),
	score
from examinfo e


#查询每门课考试最高分的学生信息
#1. 查询每门课的最高分
#2. 通过每门课的最高分关联学生信息

#查询科目最高分
select 
	courseId , max(score) 最高分
from examInfo 
group by courseId

#查询学生信息，关联这个最高分
select
	s.name,e.courseId,e.score
from studentInfo s
join examinfo e
on s.studentId =e.studentId
join (
	select 
	courseId , max(score) max_score
	from examInfo 
	group by courseId
) as c
on e.courseId = c.courseId and e.score = c.max_score

#查询参加了某门课程（html）考试的学生信息 ??
##查询与张三在同一个班级的学生信息 ??

#查询参加了某门课程（java）考试的学生信息 ??
select * from courseinfo;
select * from studentinfo;
select * from examinfo;

select 
	* 
from studentinfo s
join examinfo e on s.studentId=e.studentId
where e.courseId=(select courseId from courseinfo where courseName='java');

##查询与张三在同一个班级的学生信息 ??
select * from studentinfo;

select 
	*
from studentinfo s
where classId=(select classId from studentinfo where name='张三');

create table myexam
(
	examId int primary key auto_increment,
	name varchar(20), #学生姓名
	sex char(1), #性别
  score int    #分数
)

insert into myexam
(name,sex,score)
VALUES
('张三','男',89),
('李四','女',98),
('王五','男',77),
('赵六','男',89),
('孙琪','女',68),
('郭靖','女',60),
('黄蓉','男',89),
('欧阳锋','男',43),
('欧阳克','男',34)

select * from myexam

#请查询比所有男生成绩好的女生
#1.查询男生最高分
#2.女生比最高分高的
select * from myexam
where sex = '女' and score>
(
	select max(score) from myexam where sex='男'
)

#all子查询
select * from myexam
where sex = '女' and score>all(select score from myexam where sex='男')


#请查询比某些男生成绩好的女生
#1.查询男生最低分
select min(score) from myexam where sex='男'
#2.查询女生比男生最低分高的记录
select * from myexam
where sex='女' and score>
(
	select min(score) from myexam where sex='男'
)

#any/some
select * from myexam
where sex='女' and score>some(select score from myexam where sex='男')


#EXISTS: 存在查询结果则返回true
#not EXISTS:不存在查询结果返回ture
#一般用在相关子查询中

#查询没有参加java考试的学生信息
select * from studentinfo s
where not exists (
	select * from examInfo where studentId = s.studentId
	and courseId = (select courseId from courseInfo where courseName='java')
)

select * from studentInfo s
where 
(
  select count(*) from examInfo where studentId = s.studentId
	and courseId = (select courseId from courseInfo where courseName='java')
)=0


#查询参加了html考试的学生信息?
#exists (...)
select * from studentinfo s
where EXISTS (
	select * from examInfo 
	where courseId = (select courseId from courseinfo where courseName='html' )
  and studentid = s.studentId
)

#查询参加两门考试的学生?
#count(*) =2
select * from studentinfo s
where (select count(*) from examInfo where s.studentId = studentId)=2

Mr.Wang

1.keqiance

#创建班级表
create table classInfo
(
	classId int primary key auto_increment,
	className varchar(20)
);

select * from classInfo;

insert into classInfo
(className)
values
('AAA01'),
('AAA02');

#创建学生表
create table studentInfo
(
	studentId int primary key auto_increment,
	name varchar(20) not null,
	sex char(1) not null,
	birthday date,
	province varchar(20) default '河南',
	classId int,
	foreign key (classId)
	references classInfo(classId)
);

select * from studentInfo;

insert into studentInfo
(name,sex,birthday,province,classId)
values
('张三','男','2002-01-01','湖北',1),
('李四','女','2003-01-05','河南',2),
('王五','男','2010-03-01','湖北',1),
('赵六','男','2009-01-08','河南',2),
('孙琪','女','2001-09-01','湖北',1);

#创建课程表
create table courseInfo
(
	courseId int primary key auto_increment,
	courseName varchar(20) not null
);

select * from courseInfo;

insert into courseInfo
(courseName)
values
('html'),
('java'),
('sql');

#创建成绩表
create table examInfo
(
	examId int primary key auto_increment,
	studentId int not null,
	courseId int not null,
	score int,
	foreign key (studentId)
	REFERENCES studentInfo(studentId), #外键对应的类型必须一致
	foreign key (courseId)
	REFERENCES courseInfo(courseId)
);

select * from examInfo;
select * from studentInfo;

insert into examInfo
(studentId,courseId,score)
VALUES
(1,1,90),
(1,2,70),
(1,3,65),
(2,1,88),
(2,2,67),
(2,3,55),
(3,1,69),
(3,3,45);

-- 1.多表查询，查询班级,学生，课程，成绩。显示班级名字,学生名字，科目名字和分数
-- 2.多表分组查询，查询成绩表，统计每个学生的成绩总分并从高到低排序。要显示学生姓名
-- 3.多表分组查询，查询成绩表，统计没有参加考试的学生。要求显示学生姓名
-- 4.多表分组查询，查询成绩表，统计每门课程的平均分,并查询平均分最低的课程

select c.className 班级名字,
				s.name 学生名字,
				m.courseName 科目名字,
        score 分数
from classInfo c
join studentInfo s on c.classId=s.classId
join examInfo e on s.studentId=e.studentId
join courseInfo m on m.courseId=e.courseId ;

-- ⒉多表分组查询，查询成绩表，统计每个学生的成绩总分并从高到低排序。要显示学生姓名
select s.name 学生名字,
				sum(e.score) 总成绩
from classInfo c
join studentInfo s on c.classId=s.classId
join examInfo e on s.studentId=e.studentId
join courseInfo m on m.courseId=e.courseId 
group by s.name
order by sum(e.score) desc ;


-- 3.多表分组查询，查询成绩表，统计没有参加考试的学生。要求显示学生姓名
select s.name 学生名字,
				e.score 课程成绩
from studentInfo s
#join studentInfo s on c.classId=s.classId
left join examInfo e on s.studentId=e.studentId
#join courseInfo m on m.courseId=e.courseId 
where e.score is null;


-- 4.多表分组查询，查询成绩表，统计每门课程的平均分,并查询平均分最低的课程
select m.courseName 科目名字,
        avg(e.score) 平均分数
from classInfo c
join studentInfo s on c.classId=s.classId
join examInfo e on s.studentId=e.studentId
join courseInfo m on m.courseId=e.courseId
group by m.courseName
order by avg(e.score);

2.text1ziguanlian

#自关联(树形菜单，员工（上下级关系）)
#西游取经团(公司人事结构:职员，领导)
create table xyj
(
	id int primary key, #人员编号
	name varchar(20), #人员姓名
	pid int           #上级领导编号
)

select * from xyj;

insert into xyj
(id,name,pid)
VALUES
(1,'如来',null),
(2,'菩萨',1),
(3,'唐僧',2),
(4,'孙悟空',3),
(5,'猪八戒',3),
(6,'沙和尚',3)

#请查询所有员工自己的名字和上级的名字
select * from xyj

select x.name 自己, 
		y.name 领导
from xyj x #写在前边的表称为左表
left join xyj y #写在后边的表称为右表
on y.id=x.pid; #有点迷了 #以左为尊。。。

3.test2hanshu

#concat 字符串拼接
select name 姓名,
				sex 性别,
				concat(name,'(',sex,')') 姓名性别
from studentinfo;

#length 返回字符的字节数
select length('abc'); #一个字母一个字节
select length('张三'); #6 一个汉字三个字节
#char_length 返回字符长度
select char_length('abc'); #3 一个字母一个字符长度
select char_length('张三'); #2 一个汉字一个字符长度

#查询名字是三个字的学生
select 
	* 
from studentinfo
where char_length(name)=3;

#日期函数 
#返回系统日期函数
select now(); 
select year(now());
select month(now());
select date(now());
#求两个日期查了多少年月日 (今天2020.7.19)
select timestampdiff(year,'2019-01-01',now()); #3 
select timestampdiff(month,'2019-01-01',now()); #42
select timestampdiff(day,'2019-01-01',now()); #1295

#查询年龄大于18岁的学生
select * from studentinfo;

select name 大于18的学生姓名
from studentinfo
where timestampdiff(year,birthday,now())>18;

#一年后的今天是哪一天
select date_add(now(),interval 1 year); #2023-07-19 17:41:03 #interval 间隔

#空值处理
#对于null的判断要用 is null 或者 is not null
select * from studentinfo;

update studentinfo #首先设置studentinfo表的设计表，将sex的not null属性取消掉;然后更新表中数据sex为null
	set sex=null
where name='李四'; 

select * from studentinfo
where sex is null;

select * from studentinfo
where sex is not null;

#如果性别没有数据，请显示“未知”
#ifnull(列，'替换值')：如果列值为空，则替换为另一个值
select name,ifnull(sex,'未知'),birthday
from studentinfo;

4.test3zichaxun

#查询班级是AAA01的学生
select
	*
from studentinfo s
join classinfo c on s.classId=c.classId
where c.className='AAA01';

#简单子查询（子有一个值用=）
select 
	*
from studentinfo 
where classId = (select classId from classinfo where className='AAA01');

#与王五和李四同一个班级的学生（子有多个值用in）
select 
	*
from studentinfo
where classId in (select classId from studentinfo where name='王五' or name='李四' );

#相关子查询

# 查询学生姓名、课程名称、成绩

#内连接查询实现 
select 
		s.name,c.courseName,e.score
from studentinfo s
join examinfo e on s.studentId=e.studentId
join courseinfo c on e.courseId=c.courseId;


#相关子查询实现
select 
			#s.name,
			(select name from studentinfo where studentId=e.studentId),
			#c.courseName,
			(select courseName from courseinfo where courseId=e.courseId),
			e.score
from examinfo e; #examinfo关联的有studentId和courseId

#查询每门课考试最高分的学生信息

#查询科目最高分
select * from examinfo;

select courseId,max(score) 最高分
from examinfo
group by courseId;

#查询学生信息，关联这个最高分
select * from courseinfo;
select * from examinfo;

select s.name,e.courseId,e.score
from studentinfo s
join examinfo e on s.studentId=e.studentId
join(
	select courseId,max(score) max_score
	from examinfo
	group by courseId
) as c
on e.courseId=c.courseId and e.score=c.max_score;

#查询参加了某门课程（java）考试的学生信息 ??
select * from courseinfo;
select * from studentinfo;
select * from examinfo;

select 
	* 
from studentinfo s
join examinfo e on s.studentId=e.studentId
where e.courseId=(select courseId from courseinfo where courseName='java');

##查询与张三在同一个班级的学生信息 ??
select * from studentinfo;

select 
	*
from studentinfo s
where classId=(select classId from studentinfo where name='张三');

5.text4existORnotexist

create table myexam
(
	examId int primary key auto_increment,
	name varchar(20), #学生姓名
	sex char(1), #性别
  score int    #分数
);

insert into myexam
(name,sex,score)
VALUES
('张三','男',89),
('李四','女',98),
('王五','男',77),
('赵六','男',89),
('孙琪','女',68),
('郭靖','女',60),
('黄蓉','男',89),
('欧阳锋','男',43),
('欧阳克','男',34);

select * from myexam;

#查询比所有男生成绩好的女生
select
	*
from myexam
where sex='女' and score>
(
select max(score) from myexam where sex='男' #注意这里的max()的使用!!!!这里只需要最大成绩即可
);

#all子查询
select * from myexam
where sex='女' and score>all(select score from myexam where sex='男');

#查询比某些男生成绩好的女生

#男生最低分
select min(score) from myexam where sex='男';

#查询女生比男生最低分高的记录
select * from myexam;

select 
 *
from myexam
where sex='女' and score>(select min(score) from myexam where sex='男');

#any/some 子查询
select 
 *
from myexam
where sex='女' and score>some(select score from myexam where sex='男');
 
select 
 *
from myexam
where sex='女' and score>any(select score from myexam where sex='男');

#exists :存在查询结果则返回true
#not exists : 不存在查询结果返回true

#查询没有参加java考试的学生
#一般用在相关子查询中
select * from studentinfo;
select * from examinfo;

desc studentinfo;
desc examinfo;

select * from studentinfo s
where not exists (
	select * from examinfo where studentId=s.studentId  #不能单独运行
	and courseId=(select courseId from courseinfo where courseName='java') 
);

select * from studentinfo s
where(
	select count(*) from examinfo where studentId=s.studentId  #不能单独运行
	and courseId=(select courseId from courseinfo where courseName='java') 
)=0;

#查询参加了html考试的学生信息
#esist(...)
select * from studentinfo s
where exists (
	select * from examinfo where studentId=s.studentId  #不能单独运行
	and courseId=(select courseId from courseinfo where courseName='html') 
);

#查询参加两门考试的学生
#count(*)=2
select * from courseinfo;

select * from studentinfo s
where(select count(*) from examinfo where studentId=s.studentId)=2;

desc studentinfo;
desc examinfo;

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';