一.常用语法
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
二.结果展示
image.png
1.SELECT...AS...FROM...
展示时为 列 起 别名
SELECT prod_name, sale_price, prod_id, net_w AS net_weight, pro_date, valid_month AS 保质期, in_price FROM milk_tea;
image.png
2.SELEC...FROM...AS...
展示时为 表 起 别名
SELECT m.valid_month FROM milk_tea AS m;
image.png
3.常数添加
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 保质期, 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;
四.课后作业
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
