一.常用語法
1.單表查詢
SELECT...FROM...
image.png
查詢一列
image.png
代碼
SELECT prod_id FROM milk_tea;
SELECT prod_name FROM milk_tea;
SELECT net_w FROM milk_tea;
SELECT pro_date FROM milk_tea;
SELECT valid_month FROM milk_tea;
SELECT in_price FROM milk_tea;
SELECT sale_price FROM milk_tea;
2.查詢兩列
image.png
代碼
SELECT prod_name, sale_price FROM milk_tea;
3.查詢所有列
image.png
SELECT prod_name, sale_price, prod_id, net_w, pro_date, valid_month, in_price FROM milk_tea;
或者使用 * 代替所有列
image.png
SELECT * FROM milk_tea;
若想知道該表的列名，可使用 “【表】.” 的方法
image.png
image.png
二.結(jié)果展示
image.png
1.SELECT...AS...FROM...
展示時為 列 起 別名
SELECT prod_name, sale_price, prod_id, net_w AS net_weight, pro_date, valid_month AS 保質(zhì)期, in_price FROM milk_tea;
image.png
2.SELEC...FROM...AS...
展示時為 表 起 別名
SELECT m.valid_month FROM milk_tea AS m;
image.png
3.常數(shù)添加
SELECT prod_name, sale_price, prod_id, net_w, pro_date, valid_month, in_price, 0.9 AS discount FROM milk_tea;
image.png
添加漢字
SELECT prod_name, sale_price, prod_id, net_w, pro_date, valid_month, in_price, '零食' AS class FROM milk_tea;
image.png
4.四則運算
SELECT m.prod_name, m.sale_price, m.in_price, m.sale_price - m.in_price AS profit FROM milk_tea AS m;
image.png
SELECT m.prod_name, m.sale_price, m.in_price, m.sale_price * 0.9 AS new_price FROM milk_tea AS m;
image.png
三.今日所有查詢代碼
SELECT prod_id FROM milk_tea;
SELECT prod_name FROM milk_tea;
SELECT net_w FROM milk_tea;
SELECT pro_date FROM milk_tea;
SELECT valid_month FROM milk_tea;
SELECT in_price FROM milk_tea;
SELECT sale_price FROM milk_tea;
SELECT prod_name, sale_price FROM milk_tea;
SELECT prod_name, sale_price, prod_id, net_w, pro_date, valid_month, in_price FROM milk_tea;
SELECT * FROM milk_tea;
SELECT milk_tea.valid_month FROM milk_tea;
SELECT prod_name, sale_price, prod_id, net_w AS net_weight, pro_date, valid_month AS 保質(zhì)期, in_price FROM milk_tea;
SELECT m.valid_month FROM milk_tea AS m;
SELECT prod_name, sale_price, prod_id, net_w, pro_date, valid_month, in_price, 0.9 AS discount FROM milk_tea;
SELECT prod_name, sale_price, prod_id, net_w, pro_date, valid_month, in_price, '零食' AS class FROM milk_tea;
SELECT m.prod_name, m.sale_price, m.in_price, m.sale_price - m.in_price AS profit FROM milk_tea AS m;
SELECT m.prod_name, m.sale_price, m.in_price, m.sale_price * 0.9 AS new_price FROM milk_tea AS m;
四.課后作業(yè)
Q1: 從表 milk_tea 中查詢出商品名稱（prod_name）、采購價（in_price）、銷售價
（sale_price）三個字段，同時增加以下幾列并為其命名：
“9 折”（discount1），9 折后的銷售價（new_sale1），9 折后的銷售利潤（new_profit1），
“110%”（discount2），上浮 10%后的銷售價（new_sale2），上浮 10%后的銷售利潤
（new_profit2）。
SELECT m.prod_name, m.sale_price, m.in_price, 0.9 AS dicount1, 0.9 * m.sale_price AS new_sale1, 0.9 * m.sale_price - in_price AS new_profit1, 1.1 AS discount2, 1.1 * sale_price AS new_sale2, 1.1 * sale_price - in_price AS new_profit2 FROM milk_tea AS m;
image.png
image.png
