一.结果展示
image.png
1.字符拼接
CONCAT(str1,str2,...)
SELECT m.*, CONCAT(prod_name,net_w) AS 产品信息 FROM milk_tea AS m;
image.png
SELECT m.*, CONCAT(m.prod_id,prod_name,net_w) AS 产品信息 FROM milk_tea AS m;
image.png
添加中文汉字和符号
SELECT m.*, CONCAT(prod_name, '（', net_w, '）') AS 产品信息 FROM milk_tea AS m;
image.png
SELECT m.*, CONCAT(m.prod_name, '是', m.net_w, '是', m.sale_price) AS 产品信息 FROM milk_tea AS m;
image.png
2.指定拼接符
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;
image.png
image.png
3.内容去重
DISTINCT
原表
image.png
SELECT DISTINCT m.sale_price FROM milk_tea AS m;
image.png
SELECT DISTINCT m.valid_month FROM milk_tea AS m;
image.png
SELECT DISTINCT m.net_w FROM milk_tea AS m;
image.png
二.结果排序
image.png
ORDER BY
单列排序
默认升序
SELECT m.* FROM milk_tea AS m ORDER BY m.valid_month;
image.png
DESC倒序
SELECT m.* FROM milk_tea AS m ORDER BY m.sale_price DESC;
image.png
ASC升序
SELECT m.* FROM milk_tea AS m ORDER BY m.pro_date ASC;
image.png
汉字排序
SELECT m.* FROM milk_tea AS m ORDER BY CONVERT(prod_name USING gbk);
image.png
多列排序
SELECT m.* FROM milk_tea AS m ORDER BY m.valid_month, m.sale_price;
image.png
SELECT m.* FROM milk_tea AS m ORDER BY m.valid_month DESC, m.sale_price DESC;
image.png
SELECT m.prod_id, m.prod_name FROM milk_tea AS m ORDER BY m.pro_date;
对结果中不显示的列进行排序（m.prod_date）
image.png
DAY4查询代码
SELECT m.*, CONCAT(prod_name,net_w) AS 产品信息 FROM milk_tea AS m;
SELECT m.*, CONCAT(m.prod_id,prod_name,net_w) AS 产品信息 FROM milk_tea AS m;
CONCAT(str1,str2,...)
SELECT m.*, CONCAT(prod_name, '（', net_w, '）') AS 产品信息 FROM milk_tea AS m;
SELECT m.*, CONCAT(m.prod_name, '是', m.net_w, '是', m.sale_price) AS 产品信息 FROM milk_tea AS m;
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;
DISTINCT
SELECT DISTINCT m.sale_price FROM milk_tea AS m;
SELECT DISTINCT m.valid_month FROM milk_tea AS m;
SELECT DISTINCT m.net_w FROM milk_tea AS m;
SELECT m.* FROM milk_tea AS m ORDER BY m.valid_month;
SELECT m.* FROM milk_tea AS m ORDER BY m.sale_price DESC;
SELECT m.* FROM milk_tea AS m ORDER BY m.pro_date ASC;
SELECT m.* FROM milk_tea AS m ORDER BY CONVERT(prod_name USING gbk);
SELECT m.* FROM milk_tea AS m ORDER BY m.valid_month, m.sale_price;
SELECT m.* FROM milk_tea AS m ORDER BY m.valid_month DESC, m.sale_price DESC;
SELECT m.prod_id, m.prod_name FROM milk_tea AS m ORDER BY m.pro_date;
三.课后作业
Q1：对 supplier_info 表中的信息进行拼接，分别达到如下效果。
1、查询所有供应商信息，展示结果要求为“xxx 供货商的地址为 xxxxxxx，联系电话为
xxxxxx。”，举例：“南京第一家供货商的地址为南京市光明大道 001 号，联系电话为
1338888001。”，并将该列命名为“供应商信息”。 2、查询所有供应商信息，展示结果要求为“xxx 供货商 | （地址） |（电话）。”，举
例：“南京第一家供货商 | 南京市光明大道 001 号 | 1338888001”，并将该列命名为“供
应商信息”。（2 种方法）
答：
SELECT CONCAT(s.supplier_name, '的地址为', s.addr, '联系电话为', s.tel) AS 供应商信息 FROM supplier_info AS s;
image.png
SELECT CONCAT_WS(' | ', s.supplier_name, s.addr, s.tel) AS 供应商信息 FROM supplier_info AS s;
image.png
Q2：按要求进行排序操作。
1、对产品信息表中的产品采购价按升序排列
2、对产品信息表中的产品销售价按降序排列
3、对产品信息表中的产品销售价按升序排列，同时对采购价按降序排列
答：
SELECT p.* FROM prod_info AS p ORDER BY cost ASC;
image.png
SELECT p.* FROM prod_info AS p ORDER BY sale_price DESC;
image.png
SELECT p.* FROM prod_info AS p ORDER BY p.sale_price ASC, p.cost DESC;
image.png
