mysql的连接查询

 1.准备表[mysql.sql]
 drop table if exists customers; -- 删除表
 drop table if exists orders ; -- 删除表
 create table customers(id int primary key auto_increment , name varchar(20) , age int);     -- 创建customers表
 create table orders(id int primary key auto_increment , orderno varchar(20) , price float , cid int); -- 创建orders表
 
 -- 插入数据
 insert into customers(name,age) values('tom',12);
 insert into customers(name,age) values('tomas',13);
 insert into customers(name,age) values('tomasLee',14);
 insert into customers(name,age) values('tomason',15);

 -- 插入订单数据
 insert into orders(orderno,price,cid) values('No001',12.25,1);
 insert into orders(orderno,price,cid) values('No002',12.30,1);
 insert into orders(orderno,price,cid) values('No003',12.25,2);
 insert into orders(orderno,price,cid) values('No004',12.25,2);
 insert into orders(orderno,price,cid) values('No005',12.25,2);
 insert into orders(orderno,price,cid) values('No006',12.25,3);
 insert into orders(orderno,price,cid) values('No007',12.25,3);
 insert into orders(orderno,price,cid) values('No008',12.25,3);
 insert into orders(orderno,price,cid) values('No009',12.25,3);
 insert into orders(orderno,price,cid) values('No0010',12.25,NULL);

 2.查询--连接查询
 mysql>-- 笛卡尔积查询,无连接条件查询
 mysql>select a.*,b.* from customers a , orders b ;
 
 mysql>-- 内连接,查询符合条件的记录.
 mysql>select a.*,b.* from customers a , orders b where a.id = b.cid ;

 mysql>-- 左外连接,查询符合条件的记录.
 mysql>select a.*,b.* from customers a left outer join orders b on a.id = b.cid ;

 mysql>-- 右外连接,查询符合条件的记录.
 mysql>select a.*,b.* from customers a right outer join orders b on a.id = b.cid ;

 mysql>-- 全外连接,查询符合条件的记录(mysql不支持全外链接)
 mysql>select a.*,b.* from customers a full outer join orders b on a.id = b.cid ;

 2.查询--分组
            字段列表   表       条件        分组        组内条件       排序         分页
 mysql>select ... from ... where ... group by ... having ...  order by ... limit ..

 mysql>-- 去重查询
 mysql>select distinct price,cid from orders ;

 mysql>-- 条件查询
 mysql>select price,cid from orders where price > 12.27 ;

 mysql>-- 聚集查询
 mysql>select max(price) from orders ;
 mysql>select min(price) from orders ;
 mysql>select avg(price) from orders ;
 mysql>select sum(price) from orders ;
 mysql>select count(id) from orders ;

 mysql>-- 分组查询
 mysql>select max(price) from orders where cid is not null group by cid ;

 
 mysql>-- 分组查询(组内过滤)
 mysql>select cid ,orderno,max(price) as max_price,min(price) from orders where cid is not null group by cid having max_price > 20 ;

 mysql>-- 降序查询
 mysql>select cid ,orderno,max(price) as max_price,min(price) from orders where cid is not null group by cid having max_price > 20 order by max_price desc;

 mysql>-- 模糊查询
 mysql>select  * from customers where name like 'toma%'  
 mysql>select  * from customers where name not like 'toma%'  

 mysql>-- 范围查询
 mysql>select  * from customers where id in (1,2,3)  
 mysql>select  * from customers where id not in (1,2,3)  

 mysql>-- between 1 and 10,闭区间
 mysql>select  * from customers where id between 1 and 3 ;
 mysql>select  * from customers where id >= 1 and id <= 3 ;

 mysql>-- 嵌套子查询(查询没有订单的客户)
 mysql>select  * from customers where id not in (select distinct cid from orders where cid is not null);

 mysql>-- 嵌套子查询(查询订单数量>2的客户)
 mysql>select * from customers where id in (select cid from orders group by cid having count(cid) > 2);
 mysql>select * from customers where id in ( select t.cid from (select cid,count(*) as c from orders group by cid having c > 2) as t);

 mysql>-- 嵌套子查询(查询客户id,客户name,订单数量,最贵订单，最便宜订单，平均订单价格 where 订单数量>2的客户)
 mysql>select a.id,a.name,b.c,b.max,b.min,b.avg
    from customers a,((select cid,count(cid) c , max(price) max ,min(price) min,avg(price) avg from orders group by cid having c > 2) as b)
    where a.id = b.cid ;