准备表:
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 ;