DAY3—单表查询
(一)从一个数据表中,查询出一列或多列数据:
单列查询:SELECT [] FROM [];
多列查询:SELECT [],[],[] FROM [];
所有列查询:SELECT * FROM [];
查询的输出结果会和原表顺序保持一致。
(二)修饰查询结果
不影响原表,只改变查询展示的结果。
1.改变列名
SELECT [] AS [] FROM [];
例如:
SELECT prod_id,prod_name,net_w AS net_weight,pro_date,valid_month AS 保质期,in_price,sale_price FROM milk_tea;
2.改变表名
SELECT m.pro_date,m.sale_price,m.net_w FROM milk_tea AS m;
3.常数添加
4.添加四则运算列
操作:
“表名+ . ” ,会显示所有列。
(三)字符拼接
1.SELECT CONCAT
组合多列信息合成一列
SELECT m.*, CONCAT(prod_name,net_w) AS '产品信息' FROM milk_tea AS m;
对列的信息进行补充
SELECT m.*, CONCAT(m.prod_name,'是',m.net_w, '是',sale_price) FROM milk_tea AS m;
2.SELECT CONCAT_WE
CONCAT_WS(separator,str1,str2,...)
每一列之间加上相同的字符
SELECT m.*, CONCAT_WS('是', m.prod_name, m.net_w, m.sale_price) AS '产品信息' FROM milk_tea AS m; SELECT m.*, CONCAT_WS(' + ', m.prod_name, m.net_w, m.sale_price) AS '产品信息' FROM milk_tea AS m;
3.SELECT DISTINCT
查询结果去重
SELECT DISTINCT m.valid_month FROM milk_tea AS m;
SELECT DISTINCT m.sale_price FROM milk_tea AS m;
(四)SELECT FROM ...ORDER BY
SELECT m.* FROM milk_tea AS m ORDER BY CONVERT(prod_name USING gbk);
CONVERT(prod_name USING gbk) #将utf-8的中文转为gbk编码
DESC 倒序排序,只对紧跟的对象起作用。