mysql示例2-2

-- 01.从Products表中检索一个名为prod_ name的
-- 考察点:检索单列
     select prod_name from Products;
-- 02.从表Products中选择数据prod_ 1d列， prod_ name列，prod_ price列
-- 考察点:检索多列
    select prod_id,prod_name,prod_price from Products ;
-- 03.返回Products表中所有列
-- 考察点:检索所有列
    select * from Products ;
-- 04.检索Products表中所有产品供应商的ID(vend_ id列)
-- 考察点:检索不同的值.主要想看distinct使用,本体在做时注意读题,这里是要所有供应商id应该做去重,不然只有3个供应商出现9行
-- distinct是作用于所有列而不是紧跟其后的列
    SELECT DISTINCT vend_id from Products  ;
-- 05.从Products表中检索prod_ name列， 并以字母顺序排序数据
-- 考察点:排序order by 按照字母顺序拍是升序,直接以prod_name列就行
-- 扩展:在MySQL数据库中使用UTF-8的编码进行排序会出现不按照中文拼音的顺序排序,解决方案是编码重新设定为GBK或者BG2312,使用CONVERT实现临时转化
    select prod_name from Products order by prod_name ASC ;
    select prod_name from Products order by CONVERT(prod_name USING GBK) ASC ;
-- 06.从表Products中选择数据prod_ id列，prod_ name列，prod_ price列， 首先按价格prod_ price, 然后按名称prod_ name以字母顺序排序
-- 考察点:多个列排序
    select prod_id,prod_name,prod_price from Products order by prod_price,prod_name;
-- 07.从表Products中选择数据prod_ id列，prod_ name列，prod_ price列， 以价格prod_ price降序来排序产品(最贵的排在最前面)
-- 考察点:指定排序方向
select prod_id,prod_name,prod_price from Products order by prod_price desc;
-- 08.从Products表中检索两个列， 但不返回所有行，只返回prod_ _price值为3 .49的行
-- 考察点:多列指定条件查询,where语句与条件对比
select prod_id,prod_price from Products where prod_price =3.49;
-- 09.从Products表 ,检索出所有价格小于10美元的产品
/*注意点:这里是查询商品,看是用*还是prod_name,prod_price*/
    select * FROM Products where prod_price <10; /*答案:select prod_name,prod_price from products where prod_price<10;*/
-- 10.从Products表,检索出所有价格小于等于10美元的产品
    select * FROM Products where prod_price <=10; /*答案:select prod_name,prod_price from products where prod_price<=10;*/
-- 11.从Products表 ,检索出所有不是供应商(vend_ id列)DLL01制造的产 品
    select * FROM Products where NOT vend_id = 'DLL01';
    select * FROM Products where vend_id != 'DLL01'; /*答案:select vend_id ,prod_name from products where vend_id != 'DLL01';*/
-- 12.从P roducts表,检索价格在5美元和10美元之间的所有产品
select * FROM Products where prod_price BETWEEN 5 and 10; /*答案:select prod_name,prod_price from products where prod_price between 5 and 10;*/
-- 13.从P roducts表，返回所有没有价格(空prod_ price字段， 不是价格为0)的产品
select * FROM Products where prod_price is null; /*答案:select prod_name,prod_price from Products where prod_price is null*/
-- 14. 从Customers表,所有没有电子邮件地址(空cust_ email字段， 不是邮件为空字符串)的消费者
select * FROM Customers where cust_email is NULL ; /* select cust_name,cust_email from Customers where cust_Email is null;*/
-- 15. 从Products表，检索由供应商DLL01制造且价格小于等于4美元的所有产品的名称和价格
select * FROM Products ;
select * FROM Products where vend_id ='DLL01'and prod_price <=4;
-- 答案:select prod_name,prod_price from Products where vend_id ='DLL01' and prod_price<=4;
-- 16. 从P roducts表,检索由供应商DLL01或BRS01中任何一个制造的所有产品的产品名和价格
select * FROM Products ;
select prod_name ,prod_price FROM Products where vend_id ='DLL01'or vend_id ='BRS01';
-- 答案: select prod_name ,prod_price FROM Products where vend_id ='DLL01'or vend_id ='BRS01';
-- 17.从P roducts表,列出价格为10美元及以上，且由DLL01或BRS01制造的所有产品
select * FROM Products where prod_price >10 and vend_id in('DLL01','BRS01');
select * from Products where prod_price >= 10 and (vend_id = 'DLL01' or vend_id = 'BRS01');
-- 答案: select prod_name from Products where prod_price >= 10 and (vend_id = 'DLL01' or vend_id = 'BRS01');
-- 18. 从P roducts表, 检索由供应商DLL01和BRS01制造的所有产品，按照商品名prod_ name以字母顺序排序数据(用in实现)
select prod_name ,vend_id FROM Products where vend_id in('DLL01','BRS01') order by prod_name ;
/* 答案:select prod_name,vend_id from products where vend_id in('DLL01','BRS01') order by prod_name;
或 select prod_name,vend_id from products where vend_id = 'DLL01' OR VEND_ID = 'BRS01' order by prod_name;*/
-- 19.从Products表 ,列出除DLL01之外的所有供应商制造的产品
select prod_name,vend_id from Products where not vend_id ='DLL01';

-- 20.从Products表 ,为了找出所有prod_ name商品名以词Fish起头的产品
SELECT prod_name from Products where prod_name like 'Fish%';
-- 21.从Products表,为 了找出所有prod_ name商品名包含bean bag的产品
SELECT prod_name from Products where prod_name like '%bean bag%';
-- 22.从Products表 ,为了找出所有prod_ name商品名以F开头y结尾的产品
SELECT prod_name from Products where prod_name like 'F%y';
-- 23. 从OrderItems表， 检索订单号(order _num列)20008中的所有物品
select * from OrderItems where order_num =20008;
-- 24.从OrderItems表， 检索订单号(order. num列)20008中的所有物品，汇总每个物品的价格(单价乘以订购数量)
select * from OrderItems where order_num =20008;
select order_num ,order_item,prod_id,quantity*item_price from OrderItems where order_num =20008;
-- 25.从OrderItems表， 检索订单号( order_ num列) 20008中的所有物品，汇总每个物品的价格(单价乘以订购数量,别名显示为expanded_price)
select order_num ,order_item,prod_id,quantity*item_price expanded_price from OrderItems where order_num =20008;
-- 26. 计算P roducts表中所有产品的平均价格(别名显示为avg_ price)
select * from Products ;
select avg(prod_price) as 'avg_ price' from Products ;
-- 27.从Products表， 检索特定供应商(vend_ id)DLL01所提供产品的平均价格(别名显示为avg_ price )
select vend_id,avg(prod_price) as 'avg_ price' from Products ;
-- 28.计算Cus tomers表中顾客的总数(别名显示为num_ cust )
select * from Customers ;
select count(*) from Customers ;
-- 29.计算Cus tomers表中顾客的总数(有电子邮件地址的客户计数，别名显示为num_ .cust )
select count(cust_email) num_cust from Customers ;
-- 30.检索P roducts表中最贵物品的价格(别名显示为max_ price )
select * from Products ;
select MAX(prod_price) max_price from Products;
select prod_name,prod_price max_price from Products order by prod_price desc limit 1;
-- 31. 检索P roducts表中最便宜物品的价格(别名显示为min_ price )
select MIn(prod_price) min_price from Products;
select prod_name,prod_price minprice from Products order by prod_price limit 1;
-- 32. 从OrderItems表， 检索订单( order. num )20005所订购物品的总数( (所有quantity值之和， 别名显示为items_ ordered )
select * from OrderItems ;
select SUM(quantity) items_ordered from OrderItems where order_num=20005;
-- 33.从OderItems表， 检索订单( order_ num ) 20005所订购物品的总订单金额(合计每项物品的item pr ice*quantity,得出总的订单金额
    select SUM(quantity*item_price) sumprice from OrderItems where order_num =20005;
-- 34.检索P roducts表中物品的数目(别名显示为num_ items), 产品价格的最高值(别名显示为price_ max)、最低值(别名显示为price_min)
select * from Products ;
select count(prod_name) num_items ,MAX(prod_price) price_max ,MIN(prod_price) price_min from Products ;