第八章 表联接

联接的表之间必须要有相关列，一般是外键和它对应的主键相联接。

一、内联

内联就是只有 相关列的值相等是才进行联接，并显示出来，不相等的不显示；

内联接有相等联接，非相等联接；

2种写法：

select t_1.列2,t_2.列2     from    t_1    inner    join    t_2     on     t_1.列1=t_2.列1 ;on 后面是联接条件，后面还可以再跟where 选择条件

select t_1.列2,t_2.列2     from    t_1             ,              t_2  where  t_1.列1=t_2.列1 ;  where后面还可以跟上and 选择条件

二、外联

外联 ：因为某张表中的一些行与其他表并不匹配，但是我们又需要这些行显示出来，保证数据的完整。

需要保证这张表中所有行都输出，就用外联接。

外联有 left   join    on左联，right   join    on右联，full    join    on全联：

 

列子：

USE test;

CREATE TABLE student(
    id INT PRIMARY KEY,
    NAME VARCHAR(30),
    class VARCHAR(30),
    fk INT
);

CREATE TABLE chengji(
    id INT PRIMARY KEY,
    chengji INT
);

SELECT * FROM student;
INSERT INTO student(id,NAME,class,fk) VALUES (1,'李三','一班',2),
					     (2,'王五','一班',1),
					     (3,'ck','三班',1),
					     (4,'jk','三班',3),
					     (5,'ak','三班',3),
					     (6,'张三','三班',4),
					     (7,'cf','二班',4);

INSERT INTO chengji(id,chengji) VALUES (1,70),(2,50),(3,80),(4,60);


-- 1 查询每个班的人数
SELECT class,COUNT(*)AS 人数 FROM student GROUP BY class;

-- 2 查询每个班的平均分数
SELECT class,AVG(chengji) AS 平均分数 FROM student LEFT JOIN chengji ON student.fk=chengji.id GROUP BY class;

-- 3 统计每个班姓C 的人
SELECT class,COUNT(NAME) AS 姓C的人数 FROM student WHERE NAME LIKE 'c%' GROUP BY class;

-- 4 查询二班学生的分数大于一班的最低分的人数有多少
SELECT COUNT(chengji)AS 二班分数高于一班最低分多的人数 FROM student LEFT JOIN chengji ON student.fk=chengji.id 
	WHERE class='二班'AND chengji>(SELECT MIN(chengji) FROM student LEFT JOIN chengji ON student.fk=chengji.id WHERE class='一班');

-- 6 查询同AK 同班级的学生信息
SELECT student.id,NAME,class,chengji FROM student LEFT JOIN chengji ON student.fk=chengji.id WHERE class=
	(SELECT class FROM student WHERE NAME='ak') AND NAME<>'ak';

-- 7 查询与张三有相同分数的学生信息
SELECT student.id,NAME,class,chengji FROM student LEFT JOIN chengji ON student.fk=chengji.id 
	WHERE chengji=(SELECT chengji FROM student LEFT JOIN chengji ON student.fk=chengji.id WHERE NAME='张三')AND NAME<>'张三';

-- 8 统计一班的及格人数比二班的及格人数多多少？ 及格分数为60
SELECT (SELECT COUNT(NAME) FROM student LEFT JOIN chengji ON student.fk=chengji.id WHERE class='一班'AND chengji>=60)
	-(SELECT COUNT(NAME) FROM student LEFT JOIN chengji ON student.fk=chengji.id WHERE class='二班'AND chengji>=60)
	AS 一班比二班及格的多;