【学习笔记】PostgreSQL进阶查询之排序、分组和条件查询
这一节主要包含如下内容:
- ORDER BY 子句
- GROUP BY 子句
- HAVING 子句
- 条件查询
—AND
—OR
—NOT
—LIKE
—IN
—BETWEEN
一、ORDER BY 子句
说明:
按序查询表
语法:
SELECT column_list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
参数说明:
- column_list:它指定要检索的列或计算。
- table_name:它指定要从中检索记录的表。FROM子句中必须至少有一个表。
- WHERE conditions:可选。 它规定必须满足条件才能检索记录。
- ASC:可选。它通过表达式按升序排序结果集(默认,如果没有修饰符是提供者)。
- DESC:可选。 它通过表达式按顺序对结果集进行排序。
示例1(单列排序):
select * from employees
order by age asc;
结果1:
示例2(多列排序):
select * from employees
order by age, address asc;
结果2:
二、GROUP BY 子句
说明:
用于将具有相同数据的表中的这些行分组在一起(提取出指定列中数据内容完全不同的列)
语法:
SELECT column_list
FROM table_name
WHERE [conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
示例1:
select age from employees
group by age;
结果1:
示例2:
select name,address from employees
group by name,age,address,salary;
结果2:
三、HAVING 子句
说明:
HAVING子句与GROUP BY子句组合使用,用于选择函数结果满足某些条件的特定行
语法:
SELECT column1, column2
FROM table1, table2
HERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
示例(显示name的重复次数小于2次的数据):
select name
from employees
group by name having count(name) < 2;
结果:
三、条件查询
3.1 AND条件
语法:
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition]
AND [search_condition];
示例:
select * from employees
where age > 21
and salary >100000;
结果:
3.2 OR条件
语法:
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition]
OR [search_condition];
示例:
select * from employees
where age = 21
or age = 25;
结果:
3.3 NOT条件
语法:
SELECT column1, column2, ..... columnN
FROM table_name WHERE [search_condition] NOT [condition];
示例:
select * from employees
where age not in (21,23);
结果:
3.4 LIKE条件
说明:
LIKE条件与WHERE子句一起用于从指定条件满足LIKE条件的表中获取数据
语法:
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] LIKE [condition];
示例1:
select * from employees
where name like 'Ma%';
结果1:
示例2:
select * from employees
where address like '%大道%';
结果2:
3.5 IN条件
说明:
IN条件与WHERE子句一起用于从指定条件满足IN条件的表中获取数据(这是个列表,而非范围)
语法:
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] IN [condition];
示例:
select * from employees
where age in (21, 24);
结果:
3.6 BETWEEN 条件
说明:
BETWEEN条件与WHERE子句一起用于从指定条件满足BETWEEN条件的表中获取数据(这是个范围)
语法:
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] BETWEEN [condition];
示例:
select * from employees
where age between 21 and 24;
结果: