第十一天笔记

1.1 SQL查询的基本原理
第一、单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。
第二、两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
第三、多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
理解SQL查询的过程是进行SQL优化的理论依据。

1.2 ON后面的条件（ON条件）和WHERE条件的区别
ON条件：
是过滤两个链接表笛卡尔积形成中间表的约束条件。
WHERE条件：
在有ON条件的SELECT语句中是过滤中间表的约束条件。
在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。
在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
从这里可以看出，将WHERE条件移入ON后面是不恰当的。推荐的做法是：ON只进行连接操作，WHERE只过滤中间表的记录。

连接查询
连接运算符是用来实现多表联合查询的一种重要方式，主要分为三种：内连接、外连接、交叉连接。


1） 内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2

2）左连接
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null

3） 右连接
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4

4） 完全连接
select a.*,b.* from a full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 　　 3 34 4
3 王武 null


总汇；
show databases;
show tables;
select *  from grade;
select * from user;    #运行这句会报错，user表不存在，只能操作当前库里面存在的表


create table aa(id int(1) PRIMARY key,name char(20));
create table cc(s_id int(1) PRIMARY key,score char(20));

insert into aa(id,name)values(1001,'zhangsan'),(1002,'lisi');
insert into cc(s_id,score)values(1001,'99');


select * from aa;
select * from cc;

1、内连接 inner join 对于2个表当中有字段的值相同就可以通过内连接进行连接
select * from aa inner join cc on aa.id=cc.s_id;

2、左连接 left join （以left join 左边的表为主，aa表的数据全部显示，cc表显示符合条件的数据，对于不符合条件的数据显示null）
select * from aa left join cc on aa.id=cc.s_id;

3、右连接 right join（以right join右边的表为主，cc表的数据全部显示，aa表显示符合条件的数据，对不符合条件的数据显示null）
select * from aa right join cc on aa.id=cc.s_id;

4、基本连接（对于2个表当中有字段的值相同就可以通过基本连接进行连接）
select * from aa,cc where aa.id=cc.s_id;

5、硬连接/机械拼接，硬连接要满足的条件是两个表字段数目需要一致
select * from aa union select * from cc;

6、临时表方法
求出张三的成绩；
select * from aa,cc where aa.id=cc.s_id; #这个sql语句查出来就是一个临时表，取别名为t
select score from (select * from aa,cc where aa.id=cc.s_id)t where t.name='zhangsan';

7、嵌套 = 方法
先求张三的id；
select id from aa where name='zhangsan'; #求张三的id，=符合后面只能有一个值
select score from cc where s_id=(select id from aa where name='zhangsan');

8、嵌套 in 方法
select id from aa where name='zhangsan'; # in 后面是一个集合，集合表示一个或多个值
select score from cc where s_id in (select id from aa where name='zhangsan');

9、基本连接方法

select score from aa,cc where aa.id = cc.s_id and name='zhangsan';

求出谁没有参加考试？
一、基本连接+嵌套方法
select id from  aa,cc where aa.id=cc.s_id; #参加考试的id
select name from aa where id not in (select id from  aa,cc where aa.id=cc.s_id);

二、左连接+临时表
select * from aa left join cc on aa.id=cc.s_id; #作为临时表t
select name from (select * from aa left join cc on aa.id=cc.s_id)t where t.score is null;

三、嵌套in方法；这个比第一条更佳一些，因为第一个基本连接只查询公共数据1001，不能判断是否有参加考试。
select s_id from cc where score is not null; #查询分数不为空的id即表示参加考试了的id
select name from aa where id not in (select s_id from cc where score is not null);

四、右连接
select * from cc right join aa on aa.id=cc.s_id;
select name from(select * from cc right join aa on aa.id=cc.s_id)t where t.score is null;
总结
1，内。外连接区别：
内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。也就是，返回2个表中完全符合条件的记录，结果集中每个表的字段内容均来自各自的表。
外连接：连接结果不仅包含符合连接条件的行，同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。也就是，返回2个表中完全符合条件的记录，再加上2个表中各自的记录，结果集中的字段只有一个表中有记录时，另一个表中的字段均使用空值null填写

6666