连接练习

练习

#需要的一些数据
#学生表
CREATE TABLE Student(SId VARCHAR(10),Sname VARCHAR(10),Sage DATETIME,Ssex VARCHAR(10));
INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES('03' , '孙风' , '1990-12-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-12-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-01-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-01-01' , '女');
INSERT INTO Student VALUES('09' , '张三' , '2017-12-20' , '女');
INSERT INTO Student VALUES('10' , '李四' , '2017-12-25' , '女');
INSERT INTO Student VALUES('11' , '李四' , '2012-06-06' , '女');
INSERT INTO Student VALUES('12' , '赵六' , '2013-06-13' , '女');
INSERT INTO Student VALUES('13' , '孙七' , '2014-06-01' , '女');
#科目表
CREATE TABLE Course(CId VARCHAR(10),Cname NVARCHAR(10),TId VARCHAR(10));
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');
#教师表
CREATE TABLE Teacher(TId VARCHAR(10),Tname VARCHAR(10));
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');
#成绩表
CREATE TABLE SC(SId VARCHAR(10),CId VARCHAR(10),score DECIMAL(18,1));
INSERT INTO SC VALUES('01' , '01' , 80);
INSERT INTO SC VALUES('01' , '02' , 90);
INSERT INTO SC VALUES('01' , '03' , 99);
INSERT INTO SC VALUES('02' , '01' , 70);
INSERT INTO SC VALUES('02' , '02' , 60);
INSERT INTO SC VALUES('02' , '03' , 80);
INSERT INTO SC VALUES('03' , '01' , 80);
INSERT INTO SC VALUES('03' , '02' , 80);
INSERT INTO SC VALUES('03' , '03' , 80);
INSERT INTO SC VALUES('04' , '01' , 50);
INSERT INTO SC VALUES('04' , '02' , 30);
INSERT INTO SC VALUES('04' , '03' , 20);
INSERT INTO SC VALUES('05' , '01' , 76);
INSERT INTO SC VALUES('05' , '02' , 87);
INSERT INTO SC VALUES('06' , '01' , 31);
INSERT INTO SC VALUES('06' , '03' , 34);
INSERT INTO SC VALUES('07' , '02' , 89);
INSERT INTO SC VALUES('07' , '03' , 98);

• 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

明确需要查询的是sc表的信息。先查询课程01，

SELECT * FROM  SC WHERE SC.CId="01";

其实就是通过SC表得到2张子表，笛卡儿积的方法合并两张表，然后通过WHERE条件进行筛选

SELECT * FROM (SELECT * FROM sc WHERE sc.`CId`=01)AS biao01,(SELECT * FROM sc WHERE sc.`CId`=02)AS biao02 WHERE biao01.score > biao02.score AND biao01.sid = biao02.sid

• 查询同时存在" 01 “课程和” 02 "课程的情况

  首先也是要明确查询的表是成绩表（SC）的信息。

  先通过两张子表得到课程01和课程02的成绩信息，然后笛卡儿积合并两张表，最后WHERE金星筛选

  SELECT * FROM (SELECT * FROM sc WHERE sc.`CId`= 01)AS biao01,(SELECT * FROM sc WHERE sc.`CId`=02)AS biao02 WHERE biao01.sid = biao02.sid
  

• 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

  此处要用左外连接

  （左外连接：

  语法： select 字段列表 from 表 1 left [outer] join 表2 on 条件 ;

  查询的是左表所有数据以及交集的部分）

SELECT * FROM (SELECT * FROM sc WHERE sc.`CId` = 01)AS biao01 LEFT JOIN (SELECT * FROM sc WHERE sc.`CId` = 02)AS biao02 ON biao01.sid = biao02.sid

• 查询不存在" 01 “课程但存在” 02 "课程的情况

  使用左链接的方法，然后排除掉为NULL的情况

   SELECT *  FROM (SELECT * FROM  SC WHERE SC.CId="02") AS biao01 LEFT JOIN (SELECT * FROM  SC WHERE SC.CId="01") AS biao02 ON biao01.SId = biao02.SId;
  

   SELECT *  FROM (SELECT * FROM  SC WHERE SC.CId="02") AS biao01 LEFT JOIN (SELECT * FROM  SC WHERE SC.CId="01") AS biao02 ON biao01.SId = biao02.SId WHERE biao02.SId IS NULL;