Mysql数据库单表查询2

SELECT * FROM orderitems;
SELECT * FROM fruits;
#先执行from,再执行where,然后执行select
SELECT f_price FROM fruits WHERE f_name='apple';

#查询订单编号,订单中的第几样水果,水果名称
SELECT o_num,o_item,f_name,quantity,item_price FROM fruits,orderitems 
  where fruits.f_id=orderitems.f_id;
 
#查询订单总价大于100的定义编号和总价.注意通过分组再筛选数据不能使用where应该使用having
#SELECT o_num,SUM(quantity*item_price) as 总价 FROM orderitems 
#   WHERE SUM(quantity*item_price)>100 GROUP BY o_num;
SELECT o_num,SUM(quantity*item_price) as 总价 FROM orderitems 
    GROUP BY o_num HAVING SUM(quantity*item_price)>100;
        
#查询水果表前4条记录
SELECT * FROM fruits LIMIT 4;

#查询价格最便宜的4种水果
SELECT * FROM fruits ORDER BY f_price LIMIT 4;

#查询价格排在第3和第4的水果
SELECT * FROM fruits ORDER BY f_price LIMIT 2,2;

SELECT COUNT(f_price) FROM fruits;
SELECT * FROM fruits;
SELECT SUM(f_price) FROM fruits;

#统计顾客的数量
SELECT COUNT(*) 顾客数量 FROM customers;

SELECT *FROM customers;
#统计留下email的顾客数量
SELECT COUNT(c_email) FROM customers;

SELECT o_num,COUNT(*) 水果种类 FROM orderitems GROUP BY o_num;

#查询30005订单一共购买的水果总量
SELECT SUM(quantity) 水果总量  FROM orderitems WHERE o_num=30005;

#查询s_id=103的供应商的水果价格的平均值
SELECT AVG(f_price) 均价 FROM fruits WHERE s_id=103;

#查询s_id=103的供应商的水果价格最高价格
SELECT MAX(f_price) 均价 FROM fruits WHERE s_id=103;

#在fruits表查询每一个供应商的水果平均值
SELECT s_id,AVG(f_price) 均价 FROM fruits GROUP BY s_id;

#查询水果平均价格最高的供应商
SELECT AVG(f_price) FROM fruits GROUP BY s_id ORDER BY AVG(f_price) DESC LIMIT 1;

#查询不同供应商提供的价格最高的水果价格
SELECT s_id,f_name,MAX(f_price) FROM fruits GROUP BY s_id;

#MAX,MIN可以用于数字和字符串
#查询f_name的最大值
SELECT MAX(f_name) from fruits;

#注意,AVG和SUM只用于数字
#COUNT可用于任意类型,会忽略NULL

#把所有的水果名连结
SELECT SUM(f_name) FROM fruits;#错误的
SELECT CONCAT('张三','李四','王五');#连结
SELECT SUM('张三','李四','王五');#错误的

#在orderitems表,统计不同订单号订购的水果总量
SELECT o_num,SUM(quantity) 水果总量 FROM orderitems GROUP BY o_num;

SELECT * FROM orderitems WHERE o_num=30001;