2.基础查询与排序

最新推荐文章于 2024-07-24 14:50:40 发布

AlexChoyZX

最新推荐文章于 2024-07-24 14:50:40 发布

阅读量643

点赞数 11

分类专栏： SQL 文章标签： sql 数据库

本文链接：https://blog.csdn.net/AlexZChoy/article/details/138476157

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1. SELECT语句基础

匹配查询（query）

SELECT <列名>, ……
  FROM <表名>
 WHERE <条件表达式>;

-- 用来选取product type列为衣服的记录的SELECT语句
SELECT product_name, product_type
  FROM product
 WHERE product_type = '衣服';
-- 也可以选取出不是查询条件的列（条件列与输出列不同）
SELECT product_name
  FROM product
 WHERE product_type = '衣服';

星号（*）代表全部列的意思。
SQL中可以随意使用换行符，不影响语句执行（但不可插入空行）。
设定汉语别名时需要使用双引号（"）括起来。
在SELECT语句中使用DISTINCT可以删除重复行。

-- 想要查询出全部列时，可以使用代表所有列的星号（*）。
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;

2. 运算符

<>：和 ~ 不相等
SELECT子句中可以使用常数或者表达式。
字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。
希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。
希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

-- SQL语句中也可以使用运算表达式
SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
  FROM product;
  
-- WHERE子句的条件表达式中也可以使用计算表达式
SELECT product_name, sale_price, purchase_price
  FROM product
 WHERE sale_price - purchase_price >= 500;
 
/* 对字符串使用不等号
首先创建chars并插入数据
选取出大于‘2’的SELECT语句*/
-- DDL：创建表
CREATE TABLE chars
（chr CHAR（3）NOT NULL, 
PRIMARY KEY（chr））;
-- 选取出大于'2'的数据的SELECT语句('2'为字符串)
SELECT chr
  FROM chars
 WHERE chr > '2';
 
-- 选取NULL的记录
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price IS NULL;
 
-- 选取不为NULL的记录
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price IS NOT NULL;

想要表示“不是……”时，除了前文的<>运算符外，还有NOT。
NOT不能单独使用，必须和其他查询条件组合起来使用。

-- 选取出销售单价大于等于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;

通过括号优先处理

-- 通过使用括号让OR运算符先于AND运算符执行
SELECT product_name, product_type, regist_date
  FROM product
 WHERE product_type = '办公用品'
   AND ( regist_date = '2009-09-11'
        OR regist_date = '2009-09-20');

AND 运算符两侧的真值都为真时返回真，除此之外都返回假。
OR 运算符两侧的真值只要有一个不为假就返回真，只有当其两侧的真值都为假时才返回假。
NOT运算符只是单纯的将真转换为假，将假转换为真。
NULL的真值结果既不为真，也不为假，这时真值是除真假之外的第三种值——不确定（UNKNOWN）。

3. 聚合查询

SUM：合计值
AVG：平均值
MAX：最大值，包括文本类型和数字类型
MIN：最小值，包括文本类型和数字类型
COUNT：记录条数（行数）
- COUNT 聚合函数运算结果与参数有关，COUNT(*) / COUNT(1) 得到包含 NULL 值的所有行，COUNT(<列名>) 得到不包含 NULL 值的所有行。
- 聚合函数不处理包含 NULL 值的行，但是 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;

4. 对表进行分组

GROUP BY：将现有的数据按照某列来汇总统计
在 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

按照商品种类对表进行切分

聚合键中包含NULL时，会将NULL作为一组特殊数据进行聚合运算

SELECT purchase_price, COUNT(*)
  FROM product
 GROUP BY purchase_price;

GROUP BY的子句书写顺序有严格要求（其中前三项用于筛选数据，GROUP BY对筛选出的数据进行处理）：
1. SELECT ➡️
2. FROM ➡️
3. WHERE ➡️
4. GROUP BY

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

在使用聚合函数及GROUP BY子句时，经常出现的错误有：
1. 在聚合函数的SELECT子句中写了聚合键以外的列
  使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
2. 在GROUP BY子句中使用列的别名
  SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
3. 在WHERE中使用聚合函数
  聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。如果想指定条件，可以在SELECT，HAVING以及ORDER BY子句中使用聚合函数。

5. 为聚合结果指定条件

HAVING 子句必须与 GROUP BY 子句配合使用，且限定的是分组聚合结果，WHERE 子句是限定数据行（包括分组列），二者各司其职，不要混淆
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 = '圆珠笔';

6. 对查询结果进行排序

ASC 表示升序排列，DESC 表示降序排列，默认为升序

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;

多列排序：在 ORDER BY 子句中依次书写排序列 + 排序参数

-- 多个排序键
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price, product_id;

当排序列存在 NULL 值时，NULL 结果会展示在查询结果的开头或者末尾
在MySQL中，NULL 值被认为比任何非NULL 值低

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

如果想指定存在 NULL 的行出现在首行或者末行，需要特殊处理。
既然排序时，NULL 的值比非NULL 值低（可以理解为 0 或者 -∞），那么我们在排序时就要对这个默认情况进行特殊处理以达到想要的效果。

-- 使用如下代码构建示例表
CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    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');

对于数字或者日期类型，可以在排序字段前添加一个负号（minus）来得到反向排序：（-1、-2、-3…-∞）。即，将 NULL 值排在末行，同时将所有非NULL 值按升序排列。

SELECT * FROM user 
 ORDER BY -date_login DESC;

对于字符型或者字符型数字，此方法不一定能得到期望的排序结果，可以使用 IS NULL

-- ISNULL( ) 函数等同于使用 IS NULL 比较运算符
/*先使用 ISNULL(name) 字段进行升序排列，
而只有当 name 列值为 NULL 时，ISNULL(name) 才为真，所以其排到末行，
而 name ASC 则实现了 非NULL 值升序排列。*/

-- IS NULL
SELECT * FROM user 
 ORDER BY name IS NULL ASC,name ASC;
 
-- ISNULL()
SELECT * FROM user 
 ORDER BY ISNULL(name) ASC,name ASC;

还可以使用 COALESCE 函数实现需求

SELECT * FROM user 
 ORDER BY COALESCE(name, 'zzzzz') ASC;

GROUP BY 子句中不能使用SELECT 子句中定义的别名，但是在 ORDER BY 子句中却可以使用别名
这是因为 SQL 在使用 HAVING 子句时 SELECT 语句的执行顺序为：
FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY
其中 SELECT 的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。
当在 ORDER BY 子句中使用别名时，已经知道了 SELECT 子句设置的别名，但是在 GROUP BY 子句执行时还不知道别名的存在，所以在 ORDER BY 子句中可以使用别名，但是在GROUP BY中不能使用别名。