SQL语法基础-查询操作

一、select语句简介

如果某一列设置为主键，那么这一列的的值具有两个特点：

• ①不允许为空（NULL）；
• ②具有唯一性。

--测试验证
INSERT INTO pets (pet_id, client_id, name, species, breed, dob) VALUES (NULL, 8, '雪球', '猫', '英国短毛猫', TO_DATE('2018-11-23', 'YYYY-MM-DD'));
INSERT INTO pets (pet_id, client_id, name, species, breed, dob) VALUES (8, 8, '雪球', '猫', '英国短毛猫', TO_DATE('2018-11-23', 'YYYY-MM-DD'));

1、select语句

在SQL中，我们可以使用select语句来对一个表进行查询操作。 select语句由“select子句”和“from子句”这两个部分组成。
语法：select 列名 from 表名;

select语句是对“查询语句”的统称，它是由“子句”组合而成的。所谓的“子句”，它是作为语句的一部分，而不能单独地使用。
对于select语句来说，它包含的子句主要有几种，如下表所示。

如果想要查询所有的列，可以使用“*”这个符号来代替所有的列，下面两种方式是等价的。

-- 方式1
select * from pets;

-- 方式2
select pet_id, client_id, name, species, breed, dob from pets;

推荐方式2，原因有以下两点：

• 使用“*”，无法指定列的显示顺序选择结构
• 使用“*”，查询的性能速度会变慢

2、特殊列名

在Oracle中，对于特殊列名（比如包含空格或关键字），我们只能使用双引号括起来，而不能使用单引号或反引号。

select "first name" 名字 
from clients;

二、指定列名和使用别名：as

在使用SQL查询数据时，我们可以使用as这个关键字来给一个列名起一个别名。别名的作用是：增强代码的可读性，以及查询结果的可读性。实际应用过程中，as往往是省略掉的。
语法：select 列名 (as) 别名 from 表名;
在使用as关键字起一个别名时，如果别名中包含了保留字或者特殊字符，比如括号、空格、加号（+）、减号（-）等，那么该别名必须使用英文引号括起来。

select name as "(宠物)名称"
from pets;

在Oracle数据库中，列名和别名的处理方式在使用特殊符号（如空格、加号、减号等）时基本相同，都需要使用双引号（"）将含有特殊符号的标识符括起来。这样做的原因是Oracle默认会将未加引号的标识符转换为大写，而加了双引号的标识符会保留其字面值，包括任何特殊字符和大小写。

• 如使用双引号括起来的标识符将变为区分大小写。Oracle默认是不区分大小写的，但如果列名或别名在双引号内，必须在引用时保持大小写的一致性。
• 一般来说，除非必须，否则避免在列名或别名中使用特殊字符和空格，因为这可能会导致查询编写上的混淆和错误。
• 使用双引号创建的列名或别名在日后的SQL查询中必须始终使用双引号和正确的大小写来引用）。

 select "first name" as "名 字"
from clients;

三、数据过滤：where

在SQL中，我们可以使用where子句来指定查询的条件。其中，where子句都是配合select子句来一起使用的。
语法：select 列名 from 表名 where 条件;

select name, species, breed, dob
from pets
where species='猫';

对于where子句来说，它一般都是需要结合运算符来使用的，主要包括以下3种:

1. 比较运算符。
2. 逻辑运算符。
3. 其他运算符。

1、比较运算符

在where子句中，我们可以使用比较运算符来指定查询的条件，常用的比较运算符如下表所示。

对于SQL中的运算符，我们需要清楚以下两点:

• 对于“等于”来说，SQL使用的是“=”而不是“==”，这一点和其他编程语言不同。
• 对于“不等于”来说，SQL有两种表示方式：“!=”和“<>”。

当比较运算符用于日期时间类型数据时，我们可以知道以下3点:

1. 小于某个日期时间，指的是在该日期时间之前。
2. 大于某个日期时间，指的是在该日期时间之后。
3. 等于某个日期时间，指的是处于该日期时间。

select name, species, breed, dob
from pets
where dob>TO_DATE('2019-01-01', 'YYYY-MM-DD');

select name, species, breed, dob
from pets
where dob=TO_DATE('2019-07-22', 'YYYY-MM-DD');
……

2、逻辑运算符

在where子句中，如果需要同时指定多个查询条件，就需要使用逻辑运算符了。常见的逻辑运算符如下表所示。

select name, species, breed, dob
from pets
where species='猫'
and dob>TO_DATE('2019-07-22', 'YYYY-MM-DD');

select name, species, breed, dob
from pets
where species='猫'
or species='狗';

select * from pets where NOT species='狗';
select * from pets where species<>'狗';

select * from pets where NOT species<>'狗';
……

3、其他运算符

3.1.范围运算符

用于指定一个范围内的值，包括：
BETWEEN … AND …：值在两个指定值之间
NOT BETWEEN … AND …：值不在两个指定值之间

SELECT * 
FROM pets 
WHERE dob BETWEEN TO_DATE('2018-01-01', 'YYYY-MM-DD') AND TO_DATE('2019-01-01', 'YYYY-MM-DD');

SELECT * 
FROM pets 
WHERE dob NOT BETWEEN TO_DATE('2018-01-01', 'YYYY-MM-DD') AND TO_DATE('2019-01-01', 'YYYY-MM-DD');

3.2. 集合运算符

用于指定值是否属于一个集合，包括：
IN (list)：值属于指定列表中的任一值
NOT IN (list)：值不属于指定列表中的任一值

SELECT * 
FROM pets 
WHERE breed IN ('金毛寻回犬', '拉布拉多', '暹罗猫');

SELECT * 
FROM pets 
WHERE breed NOT IN ('金毛寻回犬', '拉布拉多', '暹罗猫');

3.3. 模式匹配运算符

用于基于模式（通常是字符串）的搜索，包括：
LIKE：值匹配一个指定模式（%表示任意字符序列，_表示任意单个字符）
NOT LIKE：值不匹配指定模式

SELECT * FROM pets WHERE name LIKE '小%';

SELECT * FROM pets WHERE name NOT LIKE '%小%';

3.4. 空值检查运算符

用于检查值是否为NULL：
IS NULL：值是空值
IS NOT NULL：值非空

SELECT * FROM pets WHERE breed IS NULL;

SELECT * FROM pets WHERE breed IS NOT NULL;

4、运算符优先级

在SQL中，逻辑运算符也是有着优先级的。规则很简单：优先级高的先运算，优先级低的后运算。优先级相同的，从左到右进行运算。
对于运算符优先级，我们只需要清楚以下两个规则:

• 对于算术运算来说，“乘除”比“加减”优先级要高。
• 对于逻辑运算来说，非（not）＞与（and）＞或（or）。

以下是Oracle中运算符的优先级顺序，从最高到最低：
    算术运算符
        一元运算符（正号和负号）
        乘法(*), 除法(/)
        加法(+), 减法(-)
    连接运算符
        连接运算符(||)
    比较运算符
        所有的比较运算符（=，!=，<>，>，>=，<，<=）
    IS [NOT] NULL, LIKE, [NOT] IN
        这些运算符用于比较和测试特定的条件。
    [NOT] BETWEEN
        用于比较一个值是否在两个值之间。
    NOT
        逻辑否定运算符。
    AND
        逻辑与运算符。
    OR
        逻辑或运算符。

四、排序子句：order by

在SQL中，我们可以使用order by子句来对单列排序或多列排序进行大小排序。其中，order子句是作为select语句的一部分来使用的。
语法：select 列名 from 表名 order by 列名 asc或desc;

如果你想要使用升序排列，那么后面的asc关键字是可以省略的，下面两种方式是等价的。（升序ASC和降序DESC）

-- 不省略asc
select name, species
from pets
order by species asc;

-- 省略asc
select name, species
from pets
order by species;

当数据库和客户端正确支持中文字符集，例如使用AL32UTF8字符集。可以在具体的ORDER BY子句中使用NLSSORT函数来指定排序规则。，此时会按照中文拼音的顺序来进行排序。
语法： order by NLSSORT(列名 'NLS_SORT=CHINESE_PINYIN');

SELECT * FROM nls_database_parameters WHERE parameter IN ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_SORT');

五、限制行数：rownum

对于oracle来说，rownum为查询结果的每行分配一个唯一的序号，这些序号是从1开始的。你可以使用rownum在WHERE子句中设置条件，以返回前N行。我们可以使用rownum这个关键字来获取前n行数据。需要注意的是， MySQL使用的是limit关键字，而SQL Server使用的是top关键字。
语法： select 列名 from 表名 where rownum <= N;

select name, species
from pets
where rownum<=5;

对于Oracle 12c及以后版本引入了FETCH FIRST子句，这提供了一种更直观的方式来限制查询结果返回的行数。使用FETCH FIRST子句，你可以指定返回结果集中的前N行，或者返回结果集的一定百分比的行。
语法：
SELECT 列名 FROM 表名
ORDER BY 列名
FETCH FIRST N ROWS ONLY;

或者在Oracle 12c及之后的版本中，可以使用
SELECT * FROM pets 
FETCH FIRST 5 ROWS ONLY;

六、去重处理：distinct

在SQL中，我们可以使用distinct关键字来实现数据的去重。所谓的数据去重，指的是查询结果中如果包含多个重复行，结果只会保留其中一行。
语法：select distinct 字段列表 from 表名;

注意：distinct关键字用于select子句中，它总是紧跟在select关键字之后，并且放在第一个列名之前。此外，distinct作用于整个字段列表的所有列，而不是单独某一列。

SELECT DISTINCT species 
FROM pets;

七、算术运算

对于select语句来说，我们可以在select子句中使用算术运算。对于SQL常用的算术运算符有4个，如下表所示。

八、聚合函数

聚合函数，也可以叫做“统计函数”。所谓的聚合函数，指的是对一列值进行计算，然后最终会返回单个值。所以聚合函数还被叫做“组函数”。聚合函数通常在SELECT语句的SELECT列表中使用，经常与GROUP BY子句结合使用来汇总数据
聚合函数一般用于select子句，而不能用于where子句。

1、求和：sum()

在SQL中，我们可以使用sum()函数来对某一列进行求和。
语法：select sum(列名) from 表名;

SELECT SUM(age) FROM pets;

2、求平均值：avg()

在SQL中，我们可以使用avg()函数来对某一列进行求平均值。其中，avg是“average（平均值）”的缩写。
语法：select avg(列名) from 表名;

SELECT AVG(age) FROM pets;

3、求最值：max()和min()

在SQL中，我们可以使用max()函数来对某一列求最大值，也可以使用min()函数来对某一列求最小值。
语法：select max(列名), min(列名) from 表名;

SELECT MAX(age) FROM pets;
SELECT MIN(age) FROM pets;

4、获取行数：count()

在SQL中，我们可以使用count()函数来获取某一列中有效值的行数是多少。所谓的有效值，指的是非NULL值。
语法：select count(列名) from 表名;

对于count()函数来说，它有以下两种使用方法:

• count(列名)：计算指定列的总行数，会忽略值为NULL的行。
• count( * )：计算数据表的行数，不会忽略值为NULL的行，因为count( * )包含了所有的列。

SELECT COUNT(*) FROM pets;
SELECT COUNT(DISTINCT species) FROM pets;

5、字符串聚合：listagg()

在SQL中，我们可以使用listagg()函数来将文本值从多个行连接为单个文本值。
语法：
select LISTAGG(column, delimiter)
WITHIN GROUP(ORDER BY order_by_column)
[OVER (partition_by_clause)]
from 表名;

column是你想要聚合成字符串的列。
delimiter是用来分隔列值的字符串，比如逗号,或者其他任何字符串。
ORDER BY order_by_column指定在聚合之前对行进行排序的列。这是必须的，因为聚合的结果需要按照特定的顺序排列。
OVER (partition_by_clause)是可选的，用于在使用LISTAGG作为窗口函数时定义分区。这允许你在不同的分区内独立地聚合字符串。

SELECT client_id,
       LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS pet_names
FROM pets
GROUP BY client_id;

注意事项:

• LISTAGG在处理大量数据时可能会因为结果字符串超过数据库限制而导致错误。例如，Oracle的字符串长度限制通常是4000个字节（对于VARCHAR2数据类型）。
• 如果你需要处理超长文本的聚合，可能需要考虑其他策略，比如使用自定义聚合函数或者分段处理数据。

6、深入了解

在SQL中，所有聚合函数都可以使用一个“类型”前缀来进行计算。
语法：函数名(类型 列名)

这里的“类型”的取值有两种：all和distinct，默认值是all。如果是all，则表示计算所有值的和；如果是distinct，则表示计算非重复值的和。

SELECT breed,
       AVG(age) AS average_age
FROM pets
GROUP BY breed; --计算每种宠物品种的平均年龄

SELECT p.pet_id,
       COUNT(DISTINCT br.service_id) AS unique_service_count
FROM boarding_records br
JOIN pets p ON br.pet_id = p.pet_id
GROUP BY p.pet_id; --统计提供给不同宠物的独特服务数量

SELECT service_id,
       COUNT(DISTINCT client_id) AS unique_client_count
FROM boarding_records br
JOIN pets p ON br.pet_id = p.pet_id
GROUP BY service_id;


SELECT service_id,
       COUNT(client_id) AS total_usage_count 
FROM boarding_records br
JOIN pets p ON br.pet_id = p.pet_id
GROUP BY service_id;

7、特别注意

对于聚合函数来说，需要特别注意以下两点:

• 聚合函数一般用于select子句，而不能用于where子句。
• sum()、avg()、max()、min()这4个聚合函数，只适合用于统计数字类型的列。如果指定列的类型不是数字类型，那么就可能会报错。

-- 计算平均价格，过滤掉低于平均价格的服务
SELECT service_name, price
FROM services
WHERE price > (
    SELECT AVG(price)
    FROM services
);

九、分组子句：group by

分组统计，它指的是根据“某些条件”来将数据拆分为若干组，将结果集分组，通常用于结合聚合函数，如计数、求和等
在SQL中，我们可以使用group by子句来根据一列或多列的取值进行分组。
语法：select 列名 from 表名 group by 列名;

SELECT species, AVG(age) 平均岁数
FROM pets 
GROUP BY species;

SELECT breed, COUNT(*) 数量 
FROM pets 
GROUP BY breed;

十、指定条件：having

在SQL中，我们可以使用having子句来给分组指定条件，也就是给group by子句的结果来指定条件。它与WHERE子句的区别在于HAVING可以使用聚合函数。
语法：select 列名 from 表名 where 条件 group by 列名 having 条件;

SELECT breed, COUNT(*) 数量 
FROM pets 
GROUP BY breed 
HAVING COUNT(*) > 2;

十一、子句顺序

一个语句都是由不同子句组合而成的。对于select语句来说，它主要包含7种子句。
对于select语句中的各种子句来说，它需要严格遵循下面的书写顺序。
select → from → where → group by → having → order by → rownum
FETCH子句：（Oracle 12c及更新版本）用于限制结果集中的行数或百分比，是实现分页查询的手段之一。
limit子句：MySQL、postgresql中常用