SQL单表查询及实例

wjhfly

学而不思则罔

1.查询milk_tea 表中的产品名称，销售价格，并重新命名；

SELECT prod_name AS 产品名称, sale_price AS 销售价格 FROM milk_tea;

1.查询milk_tea 表中的产品名称，销售价格，九折销售价格，并备注“打折”；

SELECT m.prod_name, m.sale_price, 0.9*m.sale_price AS discount, "打折" AS 备注 
FROM milk_tea AS m;

3.查询milk_tea 表中的产品名称，销售利润；（销售利润 = 销售价格-进价）

SELECT m.prod_name AS 产品名称, m.in_price AS 进价, m.sale_price AS 售价, 
m.sale_price - m.in_price AS profit 
FROM milk_tea AS m;

4.查询milk_tea 表中详细产品信息(产品名称重量)，售卖价格

SELECT CONCAT(m.prod_name," ", m.net_w) AS 产品明细, m.sale_price AS 售卖价格 
FROM milk_tea AS m;

5.查询milk_tea 表中有多少个产品；

SELECT DISTINCT m.prod_name AS 产品种类 FROM milk_tea AS m;

6.查询milk_tea 表产品名称，售卖价格，并按产品名称升序，产品价格降序展示；

SELECT m.prod_name AS 产品名称, m.sale_price AS 售卖价格 
FROM milk_tea AS m
ORDER BY m.prod_name ASC, m.sale_price DESC;

7.查询milk_tea 表中产品名称为火腿肠，奶茶的所有产品信息；

SELECT * FROM milk_tea AS m WHERE m.prod_name IN ("火腿肠","奶茶");

8.查询milk_tea 表中售卖价格打9折后小于15的产品所有信息；售卖价格为空值时表示售卖价格为0；

SELECT *, 0.9*m.sale_price AS discount 
FROM milk_tea AS m 
WHERE ifnull(0.9*m.sale_price,0) < 15;

8.查询milk_tea 表中售卖价格在4-15元之间的所有产品信息；

SELECT * FROM milk_tea AS m WHERE m.sale_price BETWEEN 4 AND 15;

9.查询milk_tea 表中产品名称带有“糖”或“薯”字的所有产品信息；

SELECT * FROM milk_tea AS m WHERE m.prod_name LIKE "%糖%" OR m.prod_name LIKE "%薯%";

10.查询milk_tea 表中产品名称带有“糖”或“薯”字的所有产品信息且售卖价格大于5小于16；

SELECT * 
FROM milk_tea AS m 
WHERE (m.prod_name LIKE "%糖%" OR m.prod_name LIKE "%薯%") 
AND (m.sale_price >= 5 AND m.sale_price < 16);

11.查询milk_tea 表中除“薯片”“棒棒糖”之外的所有产品信息；

SELECT * FROM milk_tea AS m
WHERE m.prod_name NOT IN ("薯片","棒棒糖");

12.查询milk_tea 表中产品类型数量；

SELECT COUNT(DISTINCT m.prod_name) AS 产品类型数量 FROM milk_tea AS m;

13.查询milk_tea 表中合计售卖价格大于等于10元的每种产品及合计售卖价格，棒棒糖产品除外；

SELECT m.prod_name, sum(m.sale_price) AS 合计售卖价格 
FROM milk_tea AS m
WHERE NOT m.prod_name = "棒棒糖"
GROUP BY  m.prod_name