mysql 学习笔记4

过滤分组

select class_id,count(*) orders from students 
group by class_id
having count(*) >= 2;

使用having 代替where,(where在分组前过滤，having在分组后过滤)

SELECT子句的顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

子查询过滤

select id,name
from students
where class_id in (select class_id
					from students
					where class_id in (1,2));

从内向外操作

内部联结
可使用别名 + INNER JOIN 的方式实现

select s.id,s.class_id,c.name as class_name,s.name
from students s
inner join classes c
on s.class_id = c.id;

高级联结

自联结

SELECT a.cust_id,a.cust_name,a.cust_contact
FROM Customers a,Customers b 
WHERE a.cust_name = b.cust_name
  AND b.cust_contact= "jack"

SELECT cust_id,cust_name,cust_contact
FROM Customers
WHERE cust_name=(SELECT cust_name
                 FROM Customers
                 WHERE cust_contact="jack")

以上两者等价，自联结大多数情况下比子查询速度快，可实际比较两者速度，再选用。

自然联结
所有我们建立的内部联结都是自然联结

外部联结
几种联结方式见mysql学习笔记3；