SQL语言学习——连接查询

Marbles

第三部分——连接查询


1. 等值与非等值连接查询
2. 自身连接
3. 外连接
4. 多表连接

1.等值连接 连接运算符为 =
      
      连接执行过程
         （1）嵌套循环法（nested-loop）类似两重循环左表指针每向下移一次，右表便从头开始遍历一次
         （2）排序合并法（sort-merge）两表各自排序其次，右表指针不返回
         （3）索引连接（index-join）根据连接字段建立索引，直接找到满足条件的元组


      特殊的等值连接——自然连接（去除重复的列）
      明确写出select后面是哪个表的
      例如：select student.sno,sname,ssex,sdept,cno,gradefrom student,scwhere student.sno=sc.sno


2.自身连接（需要给表起别名来区别，列名使用别名前缀）

例：查询每一门课的间接先修课（先修课的先修课）
select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno


3. 外连接    自然连接会抛弃掉悬浮元组（不满足连接条件的元组）
    而外连接会将悬浮元组放在自然连接的下面，其余列值补null

（1）左外连接，列出左边所有元组 from student left join sc on (student.sno=sc.sno)
（2）右外连接，列出右边所有元组
（3）全连接，from A outer join B on A.key=B.key


4. 多表连接（where或者from）


例：查询每个学生的学号、姓名、选修的课程名及成绩
    select student.sno,sname,cname,grade
    from student,sc,course
    where student.sno=sc.snoand sc.cno=course.cno
    或者
    select student.sno,sname,cname,grade
    from student join sc on student.sno=sc.sno
                 join course on sc.cno=course.cno

练习：


1.为了方便题目练习，重新建表和插入数据如下：（和第一部分表的差别是新增birthdate列，course表中新增cpno）

[SQL] 纯文本查看 复制代码

create table student (
  sno char(7) primary key,
  sname char(10) not null,
  sex char(2) check(sex in('男','女')), 
  sage int,
  dept char(20),
  birthdate date )
create table course (
  cno char(10) primary key,
  cname char(20)        not null,
  credit int check(Credit>0),
  cpno char(10),
  semester int)
create table sc (
  sno char(7),
  cno char(10),
  grade int check(grade>=0 and grade<=100),
  primary key (sno, cno),
  foreign key (sno) references student(sno),
  foreign key (cno) references course(cno) )
insert into student(sno,sname,sex,sage,dept,birthdate)
values('0811101','李勇','男', 21,'计算机系','2000/5/6'),
('0811102','刘晨','男', 20,'计算机系','2001/8/8'),
('0811103','王敏','女', 21,'计算机系','2000/3/18'),
('0811104','张小红','女', 19,'计算机系','2002/1/10'),
('0821101','张立','男',21,'信息管理系','2000/10/12'),
('0821102','吴宾','女',20,'信息管理系','2001/3/20'),
('0821103','张海','男',20,'信息管理系','2001/6/3'),
('0831101','钱小平','女',21,'通信工程系','2000/11/9'),
('0831102','王大力','男',21,'通信工程系','2000/5/6'),
('0831103','张姗姗','女',20,'通信工程系','2001/2/26')
insert into course(cno,cname,credit,cpno,semester)
values('c001','高等数学',4,null,1),
('c002','大学英语',3,null,1),
('c003','大学英语',3,'c002',2),
('c004','计算机文化学',2,'c001',2),
('c005','java',2,'c004',3),
('c006','数据库基础',4,'c007',5),
('c007','数据结构',4,'c001',4),
('c008','计算机网络',4,'c004',4)
insert into sc (sno,cno,grade)values('0811101','c001',96),
('0811101','c002',80),('0811101','c003',84),
('0811101','c005',62),('0811102','c001',92),
('0811102','c002',90),('0811102','c004',84),
('0821102','c001',76),('0821102','c004',85),
('0821102','c005',73),('0821102','c007',null),
('0821103','c001',50),('0821103','c004',80),
('0831101','c001',50),('0831101','c004',80),
('0831102','c007',null),('0831103','c004',78),
('0831103','c005',65),('0831103','c007',null)

2.练习如下：

（注：每个练习后的
                         select * from tn
                         drop table tn     这两条语句，是为了方便检查语句是否正确，不属于题干操作）

（1）查询“计算机系”学生的修课情况，要求列出学生的名字、所修课的课程号和成绩，查询结果存入新表t1。

[Asm] 纯文本查看 复制代码

select sname,cno,grade into t1 from student,sc
where student.sno=sc.sno and dept='计算机系'

select * from t1
drop table t1

（2）查询“信息管理系”选修了“计算机文化学”课程的学生姓名和成绩，查询结果存入新表t2。

[SQL] 纯文本查看 复制代码

select sname,grade into t2 from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno and cname='计算机文化学' and dept='信息管理系'

select * from t2
drop table t2

（3）查询所有选修了“Java”课程的学生情况，列出学生姓名和所在系，查询结果存入新表t3。

[SQL] 纯文本查看 复制代码

select sname,dept into t3 from student join sc on student.sno =sc.sno
join course on sc.cno=course.cno
where course.cname='Java'

select * from t3
drop table t3

（4）统计每个系的学生的考试平均成绩，列出系名和平均成绩，查询结果存入新表t4。

[SQL] 纯文本查看 复制代码

select dept '系名',AVG(grade) '平均成绩' into t4 from student join sc on student.sno=sc.sno
group by dept

select * from t4
drop table t4

（5）统计“计算机系”每个学生的选课门数、平均成绩、最高成绩和最低成绩,列出学生学号和以上信息，查询结果存入新表t5。

[SQL] 纯文本查看 复制代码

select student.sno '学生学号',COUNT(sc.cno) '选课门数',AVG(grade) '平均成绩',MAX(grade) '最高成绩',MIN(grade) '最低成绩'
into t5 
from student left join sc on student.sno=sc.sno
group by student.sno

select * from t5
drop table t5

（6）查询与“刘晨”在同一个系学习的学生的姓名和所在的系，查询结果存入新表t6。

[SQL] 纯文本查看 复制代码

select st2.sname '姓名',st2.dept '所在系' into t6 from student st1,student st2
where st1.sname='刘晨' 
and st2.sname<>'刘晨'
and st1.dept=st2.dept 


select * from t6
drop table t6

（7）查询与“数据结构”在同一个学期开设的课程的课程名和开课学期，查询结果存入新表t7。

[SQL] 纯文本查看 复制代码

select c2.cname '课程名',c2.semester '开课学期' into t7 from course c1,course c2
where c1.cname='数据结构'
and c2.cname<>'数据结构'
and c1.semester=c2.semester

select * from t7
drop table t7

（8）查询至少被两个学生选的课程的课程号，查询结果存入新表t8。

[SQL] 纯文本查看 复制代码

select cno into t8 from sc
group by cno
having COUNT(sno)>=2

select * from t8
drop table t8

（9）查询全体学生的选课情况，包括选修了课程的学生和没有选修课程的学生，列出学生学号、姓名、课程号、成绩，查询结果存入新表t9。

[SQL] 纯文本查看 复制代码

select student.sno,sname,cno,grade into t9 from student left join sc on student.sno=sc.sno

select * from t9
drop table t9

（10）查询没人选的课程的课程名，查询结果存入新表t10。

[SQL] 纯文本查看 复制代码

select cname into w10 from course left join sc on course.cno=sc.cno
where sc.cno is null

select * from w10
drop table w10

（11）查询“计算机系”没有选课的学生，列出学生姓名和性别，查询结果存入新表t11。

[SQL] 纯文本查看 复制代码

select sname,sex into t11 from student left join sc on student.sno=sc.sno
where dept='计算机系'
and sc.sno is null

select * from t11
drop table t11

（12）统计“计算机系”每个学生的选课门数，包括没有选课的学生。列出学生学号、选课门数，查询结果存入新表t12。

[SQL] 纯文本查看 复制代码

select student.sno '学号',COUNT(cno) '选课门数' into t12 from student left join sc on student.sno=sc.sno
where dept='计算机系'
group by student.sno

select * from t12
drop table t12

（13）查询“信息管理系”选课门数少于3门的学生的学号和选课门数，包括没有选课的学生。查询结果按选课门数递增排序，查询结果存入新表t13。

[SQL] 纯文本查看 复制代码

select student.sno '学号',COUNT(cno) '选课门数' into t13 from student left join sc on student.sno=sc.sno
where dept='信息管理系'
group by student.sno
having COUNT(cno)<3
order by COUNT(cno)

select * from t13
drop table t13

其他部分链接：
第一部分 建表和插入数据：https://www.52pojie.cn/thread-1619534-1-1.html
第二部分 单表查询：https://www.52pojie.cn/forum.php ... 619534&pid=42268783

欢迎指正~~

ZSY1206

SQL真的蛮好的，值得收藏，下次学习

nx2008

不错不错

nanhai31

赞，学习一下

layuai

编辑的有些语句黏到一块了

nanhai31

select ....into..在mysql中不能用，create table 新表 as select

jiang12

学习学习，感谢分享

fly2006

SQL收藏，备查用！

ptyh

好耶 之前学了一半可以接着学