Customers表格:

Orders表格:

select * from customers as c left join orders as o on c.id=o.customer_id;

select * from orders as o right join customer as c on c.id=o.customer_id;

select * from students where id in（select student_id from teacher_student
where t_id=1）;

select * from score as a where a.score>(select avg(b.score) from score as b
where a.cou_id=b.cou_id);

1、count()：求满足列的条件的总的行数。

select count(id) from class one;

2、sum()：求总和。

select sum（math）/count(*) from student ;

3、avg()：求平均数。

select avg(math) from student;

4、max()\min():求最大值，最小值。

select max（math），min（math） from student；

group by ：分组
GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。

create table t_order(id int primary key,product varchar(20),price float(8,2));
insert into t_order values(1,‘xiaomi’, 1000);
insert into t_order values(2,‘xiaomi’,1100);
insert into t_order values(3,‘huawei’,2200);
insert into t_order values(4,‘apple’,8200);

(1)对订单中每一类商品进行归类。
select product sum(price) from t_order group by product;
(2)查询商品总价格大于3000的商品。
select product ，sum（price） from t_order group by product having
sum(price)>3000;

having \ where 的区别
①、where和having都是用来做条件限定的,
②、WHERE是在分组(group by)前进行条件过滤，
③、HAVING子句是在分组(group by)后进行条件过滤，
④、WHERE子句中不能使用聚合函数,HAVING子句可以使用聚合函数。
⑤、HAVING子句用来对分组后的结果再进行条件过滤
having sum(price)>2000 相当于 拿着 列名为sum(price)去查询。