联接

1.联接的结果是每个集合的笛卡尔积

   表联接的基本原理就是采用笛卡尔乘积。笛卡尔乘积是指将两张表的所有数据相连，最后联接的结果数为两张表数量的乘积

将多个表联接在一起和之前组合查询的UNION完全不同，UNION是将多个查询结果组合在一起，称之为纵向联接

  表联接采用的是笛卡尔乘积，称之为横向联接

2. 使用表联接

在数据库中将多表相连需要使用JOIN关键字

SELECT 列1,列2 FROM 表1 JOIN 表2

使用ON关键字来确定其是否匹配   

SELECT 列1,列2 FROM 表1 JOIN 表2   ON 表1.列=表2.列

3.查询所有学生信息及所在的班级名称

SELECT 学号,姓名,科目,c.班级名称 FROM t_student s JOIN t_class c   ON s.班级编号=c.班级编号

4.表联接分类

                       内联：表示左右两个表数据符合才输出内容

内联接的标准语法是INNER JOIN，INNER可以省略：

SELECT 学号,姓名,科目,c.班级名称 FROM t_student s INNER JOIN t_class c  ON s.班级编号=c.班级编号

简单方式：   SELECT 学号,姓名,科目,c.班级名称 FROM t_student s , t_class c   WHERE s.班级编号=c.班级编号

                       外联接：外联接是指不管有没有匹配，被定义了外联接的表数据都要出现在结果中

外联接分类

左外联接：左边那个表全部显示，不管是否与右边匹配

用法：LEFT OUTER JOIN 或 LEFT JOIN

右外联接：右边表全部显示，左边只显示能与右边表所匹配的

用法：RIGHT OUTER JOIN 或 RIGHT JOIN

全外联接：左右都显示匹配的和不匹配的

用法：FULL OUTER JOIN 或 FULL JOIN

注意：MYSQL不支持全外联接

                       自联接

自联接其实就是内联接或外联接的一种特例，同样可以使用INNER JOIN 或 OUTER JOIN。

CREATE TABLE t_emp(
员工编号 INT,
员工姓名 VARCHAR(50),
领导编号 INT)

SELECT e.员工姓名,l.员工姓名 AS 领导姓名 FROM t_emp e JOIN t_emp l ON e.领导编号=l.员工编号

练习

CREATE TABLE album(
   ASIN CHAR(10) NOT NULL PRIMARY KEY, 专辑编号
   title VARCHAR(50), 专辑标题
   artist VARCHAR(50), 专辑作者
   price DECIMAL(10,2),专辑售价
   rdate DATETIME,        发行日期
   label VARCHAR(50), 唱片公司
   rank INTEGER             专辑排名
);


CREATE TABLE track(
   album CHAR(10) NOT NULL , 专辑编号
 dsk INTEGER NOT NULL,碟片编号
  posn INTEGER NOT NULL,在歌曲碟片中的位置号
  song VARCHAR(255),  歌曲名
PRIMARY KEY(album,dsk,posn),    主键
  FOREIGN KEY (album) REFERENCES album(ASIN) 外键指向album中的asin
);


CREATE TABLE style(
   album CHAR(10) NOT NULL
  ,style VARCHAR(100) NOT NULL
  ,PRIMARY KEY (album,style)
  ,FOREIGN KEY (album) REFERENCES album(ASIN)
);

/*1.找出包含song（歌曲）'Alison' 的专辑title（名称）和artist（作者）*/
SELECT title,artist FROM album JOIN track ON track.album=album.asin  WHERE song='Alison';


/*2.哪个artist（作者）录制了song（歌曲）'Exodus'*/
SELECT artist FROM album  JOIN track ON track.album=album.asin WHERE song='exodus';


/*3.列出track(曲目录)中所有属于'Blur' album 的歌曲song*/
SELECT song FROM track t JOIN album a ON a.asin=t.album WHERE a.title='Blur';   #


/*4.显示每张ablum的title和包含的track曲目数*/
SELECT title ,COUNT(song)FROM album JOIN track ON album.asin=track.album GROUP BY album.asin; 


/*5.显示每张专辑ablum 的title名称 以及曲目名称中包含'Heart'的曲目总数（如果专辑中如果没有满足条件的曲目，则不显示该专辑）*/
SELECT title ,COUNT(song)FROM album RIGHT JOIN track ON album.asin=track.album WHERE song LIKE '%Hert%'; #


/*6.'title track'(主打曲目)就是song歌名 和title 专辑名 的相同曲目，请找出所有的title track（主打曲目）*/
SELECT album FROM album JOIN track ON album.asin=track.album WHERE album.title=track.song;


/*7.同名专辑就是专辑名和artist作者名相同的专辑（列如：乐队'Blur'的专辑'Blur'就是同名专辑）请列举同名专辑*/
SELECT DISTINCT title FROM album JOIN track ON album.asin=track.album WHERE artist=title;


/*8.找出在两张以上专辑中出现过的歌曲，同时包含出现的次数*/
SELECT song ,COUNT(song)FROM track JOIN album ON album.asin=track.album GROUP BY song HAVING COUNT(title)>2;


/*9.一张无物所超值的专辑中每首歌曲定价低于50 pence（便士）的专辑，请找出这些专辑，并显示专辑标题、专辑价格和曲目总数*/   1英镑=100便士
SELECT title,price/count(*) as num, price,COUNT(song)FROM album JOIN track ON album.asin=track.album  GROUP BY title having num<0.5;


/*10.Wagner's Ring cycle 的专辑有173 track （曲目），Bing Crosby 的专辑包含101tracks（曲目）请根据专辑中包含曲目的多少，从多到少依次列出这些专辑的名称和包含的曲目*/
select title,count(song)from album join track on album.asin=track.album where title='Wagner's Ring cycle'or title='Bing Crosby'group by title order by count(song) desc;