MySQL学习笔记(二)—查询

一、多表连接查询

新建两张表t_user、t_order。

          

1.内连接

     返回满足条件的所有记录。

(1)显式内连接

     使用inner join关键字，在on子句中设定连接条件。

SELECT
	u.id,
	u.name,
	u.address,
	o.orderno
FROM
	t_user u
INNER JOIN t_order o ON u.id = o.userid;

结果:

      

(2)隐式内连接

     不包含inner join和on关键字，在where子句中设定连接条件。

SELECT
	u.id,
	u.name,
	u.address,
	o.orderno
FROM
	t_user u,
	t_order o
WHERE
	u.id = o.userid;

结果:

2.外连接

(1)左外连接

     A:返回满足连接条件的所有记录，同时也返回左表中剩余的其他记录。

     B:使用left outer join关键字，在on子句中设定连接条件，可加where子句。

SELECT
	u.id,
	u.name,
	u.address,
	o.orderno
FROM
	t_user u
LEFT OUTER JOIN t_order o ON u.id = o.userid;

结果:

    

(2)右外连接

     A:返回满足连接条件的所有记录，同时也返回右表中剩余的其他记录。

     B:使用right outer join关键字，在on子句中设定连接条件，可加where子句。

SELECT
	u.id,
	u.name,
	u.address,
	o.orderno
FROM
	t_user u
RIGHT OUTER JOIN t_order o ON u.id = o.userid;

结果:

3.SQL查询的基本原理

(1)单表查询

     根据where条件过滤表中的记录，形成中间表(这个中间表对用户是不可见的)。然后根据select的选择列选择相应的列进行返回最终结果。

(2)两表连接查询

     对两表求积(笛卡尔积)并用on条件和连接类型进行过滤形成中间表；然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。

(3)多表连接查询

     先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。
理解sql查询的过程是进行sql优化的理论依据。

(4)on条件和where条件的区别

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

二、子查询

     子查询也叫嵌套查询，是指在select子句或者where子句中又嵌入select查询语句。

1.where型子查询

把内层查询结果当作外层查询的比较条件。

#不用order by 来查询最新的商品
select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);
                
#取出每个栏目下最新的产品(goods_id唯一)
select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

2.from子查询

把内层的查询结果供外层再次查询。

###用子查询查出挂科两门及以上的同学的平均成绩###
#先查出哪些同学挂科两门以上
select name,count(*) as gk from stu where score < 60 having gk >=2;
#以上查询结果，我们只要名字就可以了，所以再取一次名字
select name from (select name,count(*) as gk from stu having gk >=2) as t;
#找出这些同学了，那么再计算他们的平均分
select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;