商品查询用mysql还是ES sql查询商品名称和销量

1.查询语句

• 从表里选出自己需要的字段对应的数据
  `
  SELECT <列名1（字段名）,列名2，列名3> FROM <表名 >
  select x1，x2, x3 from table1 where <条件表达式>

#案例 选出类型是衣服的产品名和产品类型
SELECT product_name,product_type
FROM product
WHERE product_type=‘衣服’
`

2.注意事项

• 选择表全部字段用 * 代替全部列
• sql语句中可以使用换行符，但不能有空行
• 汉字别名要用双引号
• select distinct 可以去重
• sql的两种注释：单行用 - - 多行用/* */

-- 想要查询出全部列时，可以使用代表所有列的星号（*）。
SELECT *
  FROM <表名>；
-- SQL语句可以使用AS关键字为列设定别名（用中文时需要双引号（“”））。
SELECT product_id     As id,
       product_name   As name,
       purchase_price AS "进货单价"
  FROM product;
-- 使用DISTINCT删除product_type列中重复的数据
SELECT DISTINCT product_type
  FROM product;

3.算术运算符和比较运算符

算术运算符

加减乘除 + - * /

select 2+2 from table;
select sum(abc)/sum(dce) from table;
select sum(abc)*4 from table

比较运算符

• 放在where条件和case when 判断中，条件表达式里的

运算符	含义
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于

注意事项

• select 中可以用常数和表达式
• 字符串类型的数据原则上按照字典顺序排序，不能跟数字大小顺序混淆
• 对于NULL值的判断用 IS NULL 或者 IS NOT NULL
• 不能用 算术运算符处理NULL的表达式，返回是NULL

4.逻辑运算符（not，and ,or）

• not 表示非，否定，不是。可以用于等值判断 not xxx，不能单独使用，必须与其他查询条件组合使用

#选出销售单价大于等于1000的记录
SELECT product_name, product_type, sale_price
  FROM product
 WHERE sale_price >= 1000;
#不大于等于1000的结果（小于）
SELECT product_name, product_type, sale_price
  FROM product
 WHERE NOT sale_price >= 1000;
 与直接用‘<’等价，但是不太好读，不建议滥用

-AND： 两者都有的部分，取交集

• OR: 满足一个都行，两者并集

5.优先级

• 很重要
• 为了避免冲突，使用括号能更好的区分

6.真值表

• 逻辑运算符 and or not 的结果返回的是真假值（TRUE）(FALSE)
• 单个逻辑情况



• 多个逻辑的体现（括号的作用体现）



• NULL值 （几乎没遇到过）
• 短路运算，不进行下一个逻辑预算符判断，盲盒中无法判断第二个逻辑运算符返回的结果

7.习题

/*1.编写一条SQL语句，从 product(商品) 表中选取出“登记日期(regist_date)在2009年4月28日之后”的商品，查询结果要包含 product name 和 regist_date 两列。
*/

SELECT * from product;
SELECT product_name,regist_date from product where regist_date>'2009-04-28';

/*

请说出对product 表执行如下3条SELECT语句时的返回结果。
*/

-- 1.
-- SELECT *
--   FROM product
--  WHERE purchase_price = NULL;
--  找出价格为空的产品所有信息

-------------
-- 2.
-- SELECT *
--   FROM product
--  WHERE purchase_price <> NULL;
--  查询价格不为空的所有产品信息
-------------------
-- 3.
 SELECT *
  FROM product
 WHERE product_name > NULL;
 
-- > null  这个结果是无法判读的，判读结果返回除了 True False 之外的第三值 NULL,含null的表达式返回null ，对null值的判断要用 is null 或 is not null处理

SELECT product_name,sale_price,purchase_price from product 
where sale_price-purchase_price>=500;

/*
请写出一条SELECT语句，从 product 表中选取出满足“销售单价打九折之后利润高于 100 日元的办公用品和厨房用具”条件的记录。查询结果要包括 product_name列、product_type 列以及销售单价打九折之后的利润（别名设定为 profit）。

提示：销售单价打九折，可以通过 sale_price 列的值乘以0.9获得，利润可以通过该值减去 purchase_price 列的值获得。
*/

SELECT product_name,product_type,sale_price*0.9-purchase_price as profit from product
where product_type in ('办公用品','厨房用具') and (sale_price*0.9-purchase_price)>=100

8.表进行聚合查询

聚合函数

• SUM(x)：求x列的汇总合
• AVG(x)：求x列的平均值
• MAX(x)：求X列的最大值，包括文本类型和数字类型
• MIN(x)：求X列的最小值，包括文本类型和数字类型
• COUNT(x)：计算x列的不为null的行数，即字段对应值出现的次数，不包括null
• COUNT(NULL)=0
• COUNT(1)：计算表中的记录数，包括null，跟count()作用一致，只不过底层优化不同，count(1)是数索引，count()会自动优化指定某一列，
• COUNT(*)：计算表中的记录条数，包括null
• 关于count（）结果问题：count(1)=count(*)=count(2)>=count(列名）

• 原因：count(1)这种相当于每行填充一个1，统计有多少个1，count(2)填充多少个2，所以结果相同，count(列名）最小是因为这个列中可能有空值（null）所以行数小于等于总行数

• 关于count（）执行效率的问题：
• count(*)≈count(主键)=count(1)>=count(其他索引)>count(字段)
• 原因，计数时候扫描自动优化≈扫描主键=扫描主键>=扫描其他索引>扫描全表

-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price) 
  FROM product;
-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
  FROM product;
-- 计算销售单价的最大值和最小值
SELECT MAX(sale_price), MIN(sale_price)
  FROM product;
-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
  FROM product;
-- 计算全部数据的行数（包含 NULL 所在行）
SELECT COUNT(*)
  FROM product;
-- 计算 NULL 以外数据的行数
SELECT COUNT(purchase_price)
  FROM product;
  #使用distinct计算去重值
  SELECT COUNT(DISTINCT product_type)
  FROM product;

聚合函数运用法则

分组聚合（group by）

SELECT <列名1>,<列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type;
 -- 不含GROUP BY
SELECT product_type, COUNT(*)
  FROM product

• 书写顺序：select from where group by

SELECT purchase_price, COUNT(*)
  FROM product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;

注意点

• select 除了聚合函数里的字段，其他字段需要在group by 中指定
• group by 不能使用别名，因为select在group by之后执行
• where 后面不能跟聚合函数

聚合结果筛选

• 相对聚合结果筛选，用HAVING

HAVING用法

• HAVING子句用于对分组进行过滤，可以使用常数、聚合函数和GROUP BY中指定的列名（聚合键）。

-- 常数
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;

-- 错误形式（因为product_name不包含在GROUP BY聚合键中）
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING product_name = '圆珠笔';

9.查询结果排序

ORDER BY

• SQL 语句执行结果默认随机排列，想要按照顺序排序，需使用 ORDER BY 子句。
• order by可以使用别名

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基准列1> [ASC, DESC], <排序基准列2> [ASC, DESC], ……
 -- 降序排列
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price DESC;
-- 多个排序键
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price, product_id;
 -- 当用于排序的列名中含有NULL时，NULL会在开头或末尾进行汇总。
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY purchase_price;

order by中null值的位置安排

• 在MySQL中，NULL 值被认为比任何 非NULL 值低，因此，当顺序为 ASC（升序）时，NULL 值出现在第一位，而当顺序为 DESC（降序）时，则排序在最后。
• 如果想指定存在 NULL 的行出现在首行或者末行，需要特殊处理。

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(5),
    date_login DATE,
    PRIMARY KEY (id)
);

INSERT INTO user(name, date_login) VALUES
(NULL,    '2017-03-12'), 
('john',   NULL), 
('david', '2016-12-24'), 
('zayne', '2017-03-02');

• 根据上表的数据，进行测试

1.null排到末尾，其他位置升序（理解为把null对应值相对变大）关键字is null 或 isnull() 或 coalesce(null,最大值) asc

• 对于数字或者日期类型，可以在排序字段前添加一个负号（minus）来得到反向排序。（-1、-2、-3…-∞）
• 既然排序时，NULL 的值比 非NULL 值低（可以理解为 0 或者 -∞），那么我们在排序时就要对这个默认情况进行特殊处理以达到想要的效果。

理解null为负无穷，将 NULL 值排在末行，同时将所有 非NULL 值按升序排列。加一个减号 降序，负负得正，结果升序，null变成正无穷

• 对于字符型或者字符型数字，此方法不一定能得到期望的排序结果，可以使用 IS NULL 比较运算符。另外 ISNULL( ) 函数等同于使用 IS NULL 比较运算符。

#字符型或者字符型数字

select * from user ORDER BY name is null,name asc

select * from user ORDER BY isnull(name) ,name desc
#这两种方式 null永远排在最后一位，不管正序倒序

#方案三，用coalesce函数实现
select * from user order by coalesce(name,'zzzz') asc
#coalesce 函数 取第一个非空值，当name为空，zzzz最大

2.null排在第一位置，其他字段倒序（理解为把null相对值变大），其他关键字 is not null 或 ！isnull() 或coalesce(null,最大值) desc

select * from user ORDER BY name is not null,name desc;

select * from user ORDER BY !isnull(name) ,name desc;

select * from user ORDER BY COALESCE(name,'zzzzz') desc

语句的执行顺序

• FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY

10.练习题

一、请指出下述SELECT语句中所有的语法错误。
SELECT product_id, SUM（product_name）
--本SELECT语句中存在错误。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';
--错误点
1.group by 字段应该跟select 中字段一致
2.sql顺序应该是 select from  where group by
正确：
select product_id,sum(product_name) from product where regist_date>'2009-09-01'
group by product_id

二、请编写一条SELECT语句，求出销售单价（ sale_price 列）合计值大于进货单价（ purchase_price 列）合计值1.5倍的商品种类。执行结果如下所示。
product_type | sum  | sum 
-------------+------+------
衣服         | 5000 | 3300
办公用品      |  600 | 320


select product_type,sum(sale_price)as sum,
sum(purchase_price) as `sum`
from product
group by product_type
having sum(sale_price)>sum(purchase_price)*1.5

三、此前我们曾经使用SELECT语句选取出了product（商品）表中的全部记录。当时我们使用了 ORDER BY 子句来指定排列顺序，但现在已经无法记起当时如何指定的了。请根据下列执行结果，思考 ORDER BY 子句的内容。

select * from product
ORDER BY !isnull(regist_date),regist_date desc;

select * from product
ORDER BY regist_date is not null, regist_date desc;

select * from product
ORDER BY COALESCE(regist_date,'zzzz') desc;