苏州网站建设系统哪家好,东莞做网站 南城信科,彩神app官方网站开发,自己注册了个域名想做一个网站文章目录 学习视频配置环境第一天内连接 外连接第二天第三天 学习视频
学习视频
配置环境
四张表 配置四张表的sql语句
#创建发据库
create database frogdata charsetutf8#xff1b;use frogdata;# 学生表 Student
create table Student(
SId varchar(10),
Sname var… 文章目录 学习视频配置环境第一天内连接 外连接第二天第三天 学习视频
学习视频
配置环境
四张表 配置四张表的sql语句
#创建发据库
create database frogdata charsetutf8use frogdata;# 学生表 Student
create table Student(
SId varchar(10),
Sname varchar(10),
Sage datetime,
Ssex varchar(10));# 教师表 Teacher
create table Teacher(
TId varchar(10),
Tname varchar(10));# 科目表 Course
create table Course(
CID varchar(10),
Cname nvarchar(10),
TId varchar(10)
);# 成绩表 SC
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1));# 学生表
insert into Student values(01,赵雷,1990-01-01,男);
insert into Student values(02,钱电,1990-12-21,男);
insert into Student values(03,孙风,1990-05-20,男);
insert into Student values(04 ,李云,1990-08-06,男);
insert into Student values(05,周梅,1991-12-01,女);
insert into Student values(06,吴兰,1992-03-01,女);
insert into Student values(07,郑竹,1999-07-01,女);
insert into Student values(09,张三,2017-12-20,女);
insert into Student values(10,李四,2017-12-25,女);
insert into Student values(11,李四,2017-12-30,女);
insert into Student values(12,赵六,2017-01-01,女);
insert into Student values(13,孙七,2018-01-01,女);# 教师表
insert into Teacher values(01,张三);
insert into Teacher values(02,李四);
insert into Teacher values(03,王五);# 科目表Course
insert into Course values(01,语文,02);
insert into Course values(02,数学,01);
insert into Course values(03,英语,03);# 成绩表SC
insert into SC values(01, 01, 80),(01, 02, 90),(01, 03, 99),(02, 01, 70),(02, 02, 60),(02, 03, 80),(03, 01, 80),(03, 02, 80),(03, 03, 80),(04, 01, 50),(04, 02, 30),(04, 03, 20),(05, 01, 76),(05, 02, 87),(06, 01, 31),(06, 03, 34),(07, 02, 89),(07, 03, 98);
配置好的环境
第一天
from 后面紧接的基础表会放在开头on 后面放与基础表的条件
1-1 查询01课程比02课程成绩高的学生信息和课程分数
# 第一步:通过Sid主键连接学生表和成绩表
select* from Student a inner join SC b on a.SId b.SId;# 第二步:进行同一学生不同成绩比较,利用Sid相同,Cid不同进行关联
select* from Student a
inner join SC b on a.SId b.SId inner join SC c on a.SId c.SId
and b.CId 01 and c.CId 02;# 最后:回到题目,利用where比较分数即可
select* from Student a
inner join SC b on a.SId b.SId inner join SC c on a.SId c.SId
and b.CId 01 and c.CId 02
where b.score c.score;1-2 查询存在 01 课程但可能不存在 02 课程的情况(不存在的时候显示为null)
# 第一步:实现1名学生的2门课程在同一行,左边的Cid等于 01 课程, 右边Cid等于 02 课程
select * from SC a
inner join SC b on a.SId b.SId
where a.cid 01 and b.CId 02;# 最后:需要用到left JOIN,实现1个学生两个课程在同一行,左边 01 课程,右边 02 课程
# and b.CId 02; 表示 a 这个表只显示01课程若无则显示null
# where a.CId 01; 这是一个显示条件若无就不显示
select * from SC a
left join SC b on a.SId b.SId
and a.CId 01and b.CId 02
where a.CId 01;1-3 查询不存在 01 课程但存在 02 课程的情况
# 第一步:筛选出存在 01 课程的学生
select sid from SC where cid 01;
# 最后:使用not in 筛选出不存在 01 课程,但是存在 02 课程的学生
select* from SC
where sid not in(select sid from SC where cid 01) and cid 02;补充知识点:
内连接 外连接
从网上偷的图: sql执行顺序
from-on-join-where-group by-having聚合函数-select-order by-limiton的优先级高于join那就说明在联表前会对表提前进行过滤形成一张更小的临时表然后再进行join联表接着就对连表的结果进行where过滤在使用内连接的情况下on和where差别不大但是使用外连接的时候就会有差距了那么来看看join是怎么执行的我们的优化器首先会选中一个表作为驱动表然后我们的执行器从存储引擎中取出这个表中的所有数据这个表中的每一行数据会去另一个表进行连表操作如果我们能提前用on条件去缩小我们的这张驱动表这样连表的速度就会更快。
第二天
2-1 查询平均成绩大于等于60分的同学的学生编号学生姓名平均成绩。 提示要求查询平均成绩大于等于60分的同学信息首先确定是在成绩表里面找找到了这样的同学后是不是用sid去学生信息表里面关联就可以得到学生的姓名信息在这里的关键就是找sid。 select a.sid,a.Sname,b.avg_score
from student as a
inner join
(select sid,avg(score) as avg_score from SC
group by SId having avg_score60) as b
on a.sid b.sid;子语句可以查询成绩表中平均成绩大于60的sid
知识点补充
group by 的用法
select 聚合函数列要求出现在group by的后面
from 表
where 筛选条件
group by 分组的列表
order by 子句注意除了出现在group by后面的字段如果要在select后查询其他字段必须用聚合函数进行聚合 特点分组查询中的筛选条件分为两类 where对分组前的表进行筛选所以放在group by前面having对分组后的表进行筛选所以放在group by后面 因为执行顺序from-on-join-where-group by-having聚合函数-select-order by-limit
3-1、查询在sc表存在成绩的学生信息 提示成绩表肯定都是有学生的所以用左关联就可以得到学生的信息。 select b.*from (
select SId
from sc
group by SId) as aleft join student as b
on a.SId b.SId子语句可以查询所有成绩表中的SId结果作为主表。
4-1查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩没成绩的显示为null 解法一子查询
select a.SId,a.sname,b.counts,b.sums
from student as a
left join
(SELECT SId,count(CId) as counts,sum(score) as sums
from sc
group by SId) as b
on a.SId b.SId;子语句使用聚合函数算出选课总数、所有课程的总成绩根据SId分组
解法二
select a.SId,a.sname,count(b.CId) as counts,sum(b.score) as sums
from student as a
left join sc as b
on a.SId b.SId
group by a.SId;除了出现在group by后面的字段如果要在select后查询其他字段必须用聚合函数进行聚合。所以这个代码运行错误但是如果修改一下mysql的配置还是可以使用的。我这里没有修改如果以后有需要再修改。 第三天
5-1 查询所有李姓老师的数量
select count(a.Tname) as nums
from teacher as a
where a.Tname LIKE 李%;like模糊查找‘李%’李后面可以加的文字不限定个数 6-1 查询学过【张三】老师授课的同学的信息
select
b.*
from( select SId from sc
inner join course on sc.CId course.CId
inner join teacher on course.TId teacher.TId
where teacher.Tname 张三) as ainner join student as b
on a.SId b.Sid;子句主要作用是从sc表中找出张三学生的SId然后通过SId关联到学生表找到学生信息 7-1查询没有学全所有课程的同学的信息
select b.*
from(select sc.SId from sc
group by sc.SId
having count(sc.CId) (select count(CId)from course )) AS ainner join student as b
on a.SId b.SId;count(sc.CId) (select count(CId)from course )课程总数大于学生选择数子句主要是找出没有学全所有课程的SId 8-1查询至少一门课与学号为01的同学所学相同的同学的信息
关键点先查询到01同学所学的课程CIdIN DISTINCT 也可以用group by 代替DISTINCT
select distinct a.*
from student a
inner join sc b on a.SId b.SIdwhere b.CId IN (select sc.CId from sc where sc.SId 01);子句主要是查询01学生所学的所有课程 9-1查询和01号同学学习课程完全相同的其他同学信息
关键点1、要没有学习01号同学以外的其他课程 101号同学学习了哪些课程2找到学习1号同学以外课程的同学SELECT cid from sc where sid ‘01’ 01号同学学习课程SELECT sid FROM sc WHERE cid NOT IN (SELECT cid from sc where sid ‘01’ )筛选出与01课程同学所学不一样的同学的信息。两次用NOT IN 第一次筛选出与01课程同学所学不一样的同学的信息第二次把这些不一样的同学筛选掉留下一样的然后再用group by 保证课程个数一样
2、课程的数量要保持一致
select a.*
from student a
inner join sc b
on a.SId b.SId
where b.SId not in (select sc.SId from sc where sc.CId not in
(select CId from sc where SId 01)) and b.SId ! 01group by a.SId
having COUNT(*) (select COUNT(*) from sc where SId 01);10-1查询没有学过“张三”老师讲授任意一门课程的学生姓名
select student.* from student
where student.SId not in
(
select c.Sid from teacher as a
inner join course as b on a.TId b.TId
inner join sc as c on b.CId c.CId
where a.Tname 张三
);子句是查询张三老师教的学生Sidinner join course as b on a.TId b.TId找出与老师相匹配的课程 形成一个表inner join sc as c on b.CId c.CId再刚刚形成表的基础上找到相应的学生
