基础查询与排序

1.SELECT 语句

1)一般查询
SELECT line_name from TABLE_name
2)条件查询
SELECT line_name from tabel_name WHERE CONDITIONS
3)法则
* 代表全部
汉语名称用 双引号 (' ') 括起来
DISTINCT 可以删除重复行
4)注释： -- ，/* */

-- 二者结果不同
-- 显示 pro_name, pro_type 
SELECT pro_name, pro_type FROM product WHERE pro_type = '衣服';
-- 只显示 pro_name 
SELECT pro_name FROM product WHERE pro_type = '衣服';

-- 查询所有
SELECT * FROM productins;
-- 设定别名
SELECT pro_id AS id,
pro_name AS name,
purchasse_price AS "进货价格"
FROM product;

-- 删除同类型的物品
SELECT DISTINCT pro_type FROM product;

USE mydb1;
-- 2. SELECT 使用表达式查询
SELECT pro_name, sale_price, sale_price * 2 AS "sale_price x2" FROM product;

-- WHERE 使用表达式
SELECT pro_name, sale_price, purchase_price FROM product WHERE sale_price - purchase_price >= 500;

/*
-- 修改列名
ALTER TABLE product CHANGE purchasse_price purchase_price INTEGER;

*/

/* 
eg ：对字符串使用不等号
首先创建chars并插入数据
选取出大于‘2’的SELECT语句
*/

CREATE TABLE chars (
	charas CHAR(3) NOT NULL,
	PRIMARY KEY (charas)
);

-- 选取出大于'2'的数据的SELECT语句('2'为字符串)
SELECT charas FROM chars WHERE charas > '2';
-- 选取NULL的记录
SELECT pro_name, purchase_price FROM product
WHERE purchase_price IS NULL;
-- 选取不为NULL的记录
SELECT pro_name, purchase_price FROM product
WHERE purchase_price IS NOT NULL;

2.算术运算

1. 加 减 乘 除

2. 比较运算
   注意 ，<> 不等于

3. SELECT 中可以使用表达式 或者常数

3.逻辑运算

1. NOT ，不能单独使用，必须和其他查询条件组合使用
   选取出销售单价大于等于1000日元的记录,
   SELECT pro_name, pro_type, sale_price FROM product WHERE
   sale_price > 1000;
2. AND 和 OR
   AND ， 交集
   OR，或者，取并集
   AND 的优先级高于 OR

3）真值表
TRUE OR FALSE
NOT 将假换位真， 将真换位假
含NULL 的这真值表不确定，既不为真也不为假
第三种值 — 不确定， UNKNOWN

3. 逻辑运算

-- 选取出销售单价大于等于1000日元的记录
SELECT pro_name, pro_type, sale_price FROM product WHERE
sale_price > 1000;
-- 添加 NOT
SELECT pro_name, pro_type, sale_price FROM product WHERE
NOT sale_price > 1000;

SELECT * from product;

-- AND 和 OR
-- AND 并列的条件必须都满足时才会查找
-- OR 并列的条件，有一个满足时就可以查找
SELECT pro_name, pro_type, regist_date
FROM product WHERE pro_type = '办公用品'
AND regist_date = '2020-01-09'
OR regist_date = '2020-03-20';

-- 添加（） 改变 OR 的优先级
SELECT pro_name, pro_type, regist_date
FROM product WHERE pro_type = '办公用品'
AND (regist_date = '2020-01-09'
OR regist_date = '2020-03-20');

4.聚合查询
1）聚合函数
COUNT : 记录行数
SUM: 求和
AVG：平均
MAX：最大值
MIN： 最小值
DISTINCT 可以去除重复数据

2）法则
	COUNT(*) 会得到包含NULL 的数据行, 返回的是行数
	COUNT(<列名>) 会得到NULL 之外的数据行， 返回的是行数
	聚合函数会将 NULL 排除在外， COUNT(*) 除外
	MIN， MAX 适合所有类型数据， 返回查询到的数据
	SUM， ACG 只适合于数值类型的列， 返回查询到的数据
	
	计算种类时， 可以在COUNT 函数参数中使用DISTINCT
	聚合函数 的参数中使用 DISTINCT 可以去除重复数据

-- 计算全部数据的行数（包含NULL）
SELECT COUNT(*) FROM product;
-- 计算NULL以外数据的行数
SELECT COUNT(purchase_price) FROM product;

-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price)
FROM product;

-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
FROM product;
-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
FROM product;

-- 使用聚合函数删除重复数
SELECT COUNT(DISTINCT pro_type) 
FROM product;
-- 是否使用DISTINCT时的动作差异（SUM函数）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
FROM product;

5. 表分组

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

   2)语法规则
   1 SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

   3)常见错误
   select 出现的列名只能是 GROUP BY 子句中指定的列名

   GROUP BY 中使用的别名可以通过 AS 指定，但在 GROUP BY 中不能使用别名， 因为 SELECT 在 GROUP BY 之后执行

   WHERE 中使用 聚合是因为 聚合函数的结果 已经确定，而 WHERE 还处于确定结果集的过程中，所以相互矛盾引发错误

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

 -- 不含GROUP BY
 /*
SELECT pro_type, COUNT(*) 
FROM product;
*/

-- 按进货单价分组
SELECT purchase_price, COUNT(*)
FROM product
GROUP BY purchase_price;

-- WHERE 使用
SELECT purchase_price, COUNT(*)
FROM product
WHERE pro_type = '衣服'
GROUP BY purchase_price;

6.聚合指定条件

1）HAVING 得到特定分组， GROUP BY 后使用 HAVING，用法类似 WHERE

2) 特点
用于对分组进行过滤，可以使用数字，聚合函数和 GROUP BY 中指定的列名

-- 查找 类型 累计为2 的 商品类型
SELECT pro_type, COUNT(*) 
FROM product 
GROUP BY pro_type
HAVING COUNT(*) = 2;

-- 出错，pro_name 不在 GROUP BY 的 条件（聚合建）中 
SELECT pro_type, COUNT(*)
FROM product
GROUP BY pro_type
HAVING pro_name = '圆珠笔';

7.查询结果排序

1）ORDER BY 按特定排列顺序排序
SELECT <列名1>, <列名2>, <列名3>, ……

FROM <表名>
ORDER BY <排序基准列1>, <排序基准列2>
默认为升序， 降序为 DESC

2）ORDER BY 中列名可以使用别名
	有 HAVING 子句的 SELECT 语句的顺序
	FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
	SELECT 的执行顺序在 GROUP BY 之后， 在 ORDER BY 之前；就是说 ORDER BY 在执行前就知道了 SELECT 设置的别名存在，而 GROUP BY 在执行时不知道 别名的存在，故 GROUP BY不可以 使用别名
	
3）ORDER BY 排序中存在NULL时， 指定其出现在首行或者末行
	MySQL 中认为 NULL 比 非NULL值低， 因此，升序时，NULL 出现在第一位， 降序时，NULL排在最后
	@.特殊处理，将NULL 置于首行或者末行
		将NULL排在末行，同时将所有非NULL 值按升序排序
		字符型数据不一定得到期望的结果，可以用 ISNULL() 比较
		也可以用 COALESCE 函数实现
	@.将NULL 值排在首行， 同时将所有非NULL 值按倒序排列
		对于数字或日期类型，可以再排序前面添加一个负号(minus)
		字符型数据不一定得到期望的结果，可以用 ISNULL() 比较
		也可以用 COALESCE 函数实现

-- 降序排列
SELECT pro_id, pro_name, sale_price, purchase_price
FROM product ORDER BY sale_price DESC;

-- 多个排序键
-- 销售单价 升序排列
SELECT pro_id, pro_name, sale_price, purchase_price 
FROM product
ORDER BY sale_price, pro_id;

--当排序的列名中含有NULL时，NULL会在开头末尾进行汇总
SELECT pro_id, pro_name, sale_price, purchase_price
FROM product
ORDER BY purchase_price;

-- 出现 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-02-12'),
('john', NULL),
('david', '2019-09-19'),
('bob', '2020-09-30');
-- 末行情况

-- 使用 - ，反向排序， 加入DESC，使 NULL 位于末行
SELECT *
FROM user
ORDER BY - date_login DESC;

-- 姓名升序排列
-- 加入 ISNULL(name) 使 NULL 置于尾行
SELECT * FROM user
ORDER BY name IS NULL, name ASC;
SELECT * FROM user
ORDER BY ISNULL(name), name ASC;

-- 使用 COALESCE(name,'ZZzz')， 将 NULL 置于 末行
SELECT * FROM user
ORDER BY COALESCE(name,'ZZzz') ASC;

-- 首行情况

-- 排序字段前加 - ，使NULL 置于首行
SELECT * FROM user
ORDER BY - date_login;

-- name IS NOT NULL, 将 NULL 置于 首行
SELECT * FROM user
ORDER BY name IS NOT NULL, name DESC;

-- COALESCE 将NULL 置于首行
SELECT * FROM user
ORDER BY COALESCE(name,'zzz') DESC;