Mysql 查询

准备表：

        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);

查询：

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 ;