lunzi

/*简单查询*/
select * from customers where age between 18 and 26 order by age desc,name asc;
select * from customers where name in('lunzi','haohao');
select * from customers where name like '__n%'
/*连接查询*/
select * from customers,orders /*交叉连接查询*/

select c.id,o.customer_id,c.name,o.id order_id,order_no 
from orders o inner join customers c  on c.id=o.customer_id/*显示内链接*/

select c.id,o.customer_id,c.name,o.id order_id,order_no 
from orders o , customers c  
where c.id=o.customer_id/*隐式内链接*/

select c.id,o.customer_id,c.name,o.id order_id,order_no 
from  customers c left outer join orders o on c.id=o.customer_id
where o.id>4 and c.id>2 /*左外连接查询*/

select c.id,o.customer_id,c.name,o.id order_id,order_no 
from  customers c right outer join orders o on c.id=o.customer_id /*右外连接查询*/

/*子查询*/
(--1)查询具有三个以上订单的客户
select * from customers c
where 3<=(select count(*) from orders o where c.id=o.customer_id)
--(2)查询名为lunzi的客户的所有订单
select * from orders o
where o.customer_id in (select id from customers where name='lunzi');
--(3)查询没有订单的客户
select * from customers c
where not exists (select * from orders o where c.id=o.customer_id);
或者
select * from customers c
where 0=(select count(*) from orders o where c.id=o.customer_id);
--(4)查询ID为1的客户的姓名、年龄以及它的所有订单的总价格
select name,age,
(select sum(price) from orders where customer_id=2) total_price
from customers 
where id=2
--或者通过左外连接查询来完成相同的功能
select c.id,sum(price) 
from customers c left outer join orders o on c.id=o.customer_id
where c.id=2 
group by c.id

/*联合查询*/
--联合查询能能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。
select * from customers 
where age<25 union select * from customers where age>=24

/*报表查询*/
--报表查询对数据进行分组统计
--(1)按照客户分组，查询每个客户的所有订单的总价格
select (select name from customers where id = c.id) name
,sum(price) total_price
from customers c left outer join orders o on c.id=o.customer_id group by c.id
--(2)按照客户分组，查询每个客户的所有订单的总价格，并且要求订单的总价格大于100
select(select name from customers where id = c.id) name,sum(price) total_price,c.id
from customers c left outer join orders o on c.id=o.customer_id group by c.id having(sum(price)>100);