表连接,子查询,exists

表连接的本质

数据库连接的本质：引用。

create database xuanke;

create table stu(sid int,sname varchar(20),sphonum char(11));
create table tea(tid int,tname varchar(20),tphonum char); 
create table course(cid int,cname varchar(20)); 
create table xuanke1(sid int,tid int,cid int,xuefen int

insert into stu values(1,'李三','110');
insert into stu values(2,'王冲','120');
insert into stu values(3,'安徽','130');
insert into tea values(1113,'刘老师','1111');
insert into tea values(1114,'王老师','2222');
insert into course values(1,'linux');
insert into course values(2,'mysql');
insert into course values(3,'hadoop');

insert into xuanke1 values(1,1113,2,2);
insert into xuanke1 values(1,1114,2,4);
insert into xuanke1 values(1,1113,3,6);
insert into xuanke1 values(2,1113,2,2);
insert into xuanke1 values(2,1114,1,2);
insert into xuanke1 values(2,1113,3,2);

要求：
查找选择了哪些课程，课程名字，学分，老师。

select s.sname,t.tname,c.cname,x.xuefen
from stu s,tea t,course c,xuanke1 x  --》设置别名
where s.sid=x.sid AND t.tid=x.tid AND c.cid=x.cid
group by ...having...
order by...

1.首先确认我们需要的数据来自于哪些表
2.看这些表中有什么关联关系
3.填写select语句

主键、外键

主键：

列
- 这个列里面的每一个值唯一的代表着这一行数据。
- 值唯一，不能有重复，不能为空
- 例：学生表的学号列。
外键：
- 列
- 这个列的所有数据来自于主表对应的主键。
- eg：选课表里的学号列，是引用的学生表的主键列学生列。

表连接的多种写法

①

select s.sname,t.tname,c.cname,x.xuefen
from stu s,tea t,course c,xuanke1 x  --》设置别名
where s.sid=x.sid AND t.tid=x.tid AND c.cid=x.cid
group by ...having...
order by...

②

select s.name,t.name,c.name,x.xuefen
from stu s
join xuanke x  --》表名
on s.sid=x.xid  --》条件
join tea t
on x.tid=t.tid
join course c
on c.cid=x.cid

③

join xuanke x  --》表名
using(sid)  --》关联列的两列的名字必须一样！

④

natural join xuanke x  --》表名

外连接

例：将所有学生以及他们的选课信息都列出来。
所有学生信息、他们对应的选课信息

左外连接

“所有”两字落在主表上。 (主表里有的，外表不一定有–》例如：王五，没有选课）

select s.name,x.xuefen
from stu s
left join xuanke x
on s.sid=x.sid    （会显示空值）

这里写图片描述

右外连接

将所有的选课信息以及对应的学生列出来
（选课的学号列一定来自学生信息的学号列）
（假设2号学生辍学了，但他曾经选过课）
（即：选课信息里有的，但是学生信息里没有）
right join
on

笛卡尔连接

select s.name,t.name,c.name,x.xuefen
from stu s,tea t,course c,xuanke x;

注意：要避免笛卡尔连接！！因为会产生大量无效信息！
这里写图片描述

子查询

无关子查询

子查询：select里面还有select （- _ -）

标量子查询（单行单列）

例1：
1.求1号球队的队长的编号

select name from PLAYERS where PLAYERNO=（select PLAYERNO from TEAMS where TEAMNO=1）

例2：对于编号小于60的球员，得到他们加入俱乐部的年份和104号球员加入俱乐部的年份之间的差值。
==》

select PLAYERNO，name，joined - （select joined from PLAYERS where PLAYERS=104） from PLAYERS where PLAYERSNO<60

[一个列减去一个值，返回的还是单行单列。]
例3：得到27和44号球员的生日。

select （select birth_date from players where playerno=27）；
select （select birth_date from players where playerno=44）；

例4：查询生日小于联盟队员编号为9999的球员生日的球员的编号和姓名。

select playerno，NAME
from players
where birth_date<（
select birth_date from players where leagueno='9999'）；

行子查询（多列单行）

查询和104号球员性别相同并且住在同一城市的球员的编号。

select playerno
from players
where（sex，town）=（select sex，town from players where playerno=104）
and playerno<>104；

列子查询（单列多行）

例：查询至少参加了一场比赛的球员的编号和姓名。

select playerno，NAME
from players
where playerno IN（select playerno from matches）；

例2：查询那些最老的球员的编号、姓名和生日。最老的球员指的是出生日期小于等于所有其它球员的球员

SELECT  playerno,NAME,birth_date
FROM  players
WHERE  birth_date  <=ALL  ( 
SELECT  birth_date
FROM  players);

PS：
<=all（）小于最小的。

any（）大于最小的。

表子查询（多行多列）

例：得到编号小于10的男性球员的编号。

SELECT playerno
FROM ( SELECT playerno,sex
          FROM players
          WHERE playerno < 10 ) AS player10
WHERE sex='M';

例2：得到编号大于10 而小于100，并且加入俱乐部的年份大于1980的男性球员的编号。
第4层子查询得到编号大于10的所有球员的编号、性别和加入时间
第3层子查询从上面得到编号小于10 的所有球员的编号、性别和加入时间
第2层子查询从上面得到年份大于1980所有球员的编号、性别
最外层子查询从上面得到男性球员的编号

SELECT playerno
  FROM
  (SELECT  playerno, sex
     FROM
       (SELECT playerno, sex, joined
          FROM
            (SELECT playerno, sex, joined
               FROM players
      WHERE playerno > 10) great10
    WHERE playerno < 100) less100
  WHERE joined > 1980) joined1980
WHERE sex = ‘M’ ;

{对子查询来说，先执行最最里面的子查询。}

LPeng的自留地