1
2
3
4
5
6
7
8
9
10
11
12
select first_name, last_name  from employees;
-- 别名
select first_name as "名", last_name  "姓" from employees;
-- 查询所有字段
select * from employees;
-- 对查询结果去重
select distinct department_id,job_id from employees;
-- 单行注释
/**
  多行注释
 */

1
2
3
4
5
6
-- 比较运算符 = != <> > >= < <=
select * from employees where employee_id = 100;
select * from employees where employee_id != 100;
select * from employees where salary > 10000;
-- 闭区间查询, 包括区间端点
select * from employees where salary between 9000 and 12000;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查询以S开头的first_name, %匹配多个
select first_name from employees where first_name like 'S%';
-- 查询以s结尾的first_name, %匹配多个
select first_name from employees where first_name like '%s';
-- 包含s的first_name
select first_name from employees where first_name like '%s%';
-- 模糊匹配一个字符_, 查询首字母任意,后面字符为uis
select first_name from employees where first_name like '_uis';
-- 转义字符, 如果要匹配结果中有%或_就需要转义, 默认使用反斜杠\为转义符号标识
select first_name from employees where first_name like '%25\%%';
-- 匹配不区分大小写
select first_name from employees where first_name ILIKE '%s%';
-- 反向匹配
select first_name from employees where first_name not ILIKE '%s%';

1
2
3
4
5
6
7
8
9
-- 为空
select * from employees where manager_id isnull;
-- 非空
select * from employees where manager_id notnull;

-- is distinct from null 是等值判断, 但是支持空值判断, 1 = null, 1 !=null这些没有用的
-- 1是否和null不同
select 1 is distinct from null;
select 1 is not distinct from null;

1
2
3
4
5
6
7
8
9
10
11
12
-- 复杂查询条件
select * from employees where first_name = 'Steven' and last_name = 'King';
select * from employees where first_name = 'Steven' or last_name = 'King';
-- 在pg中, and or使用的是短路运算, 只要前面的表达式满足整体表达式的结果, 后面的表达式就不再进行计算了
select 1=1 or 1/0=1; -- 不会报错, 因为后面的短路了

select * from employees where (salary = 10000 or salary = 12000) and department_id = 80;
-- not: not between not in, 
select * from employees where not first_name = 'Steven';
-- 优先级 not > and > or
-- 练习
select * from employees where hire_date > '2008-01-01' and salary < 5000 and commission_pct is null;

1
2
3
4
5
6
7
8
9
10
11
12
-- 字符串默认按照字典顺序排列
select employee_id,first_name,last_name,hire_date,salary from employees order by first_name;
-- 日期默认按照从早到晚排
select employee_id,first_name,last_name,hire_date,salary from employees order by hire_date;
-- 降序 desc
select employee_id,first_name,last_name,hire_date,salary from employees order by salary desc;
-- 如果排序的两个字段一样(first_name), 那么按下一个字段排序(last_name)
select employee_id,first_name,last_name,hire_date,salary from employees order by first_name, last_name;
-- null值排序的时候当做一个最大的值(降序,在最后面, 升序在最前面)
select employee_id,manager_id,first_name,last_name,hire_date,salary from employees order by manager_id;
-- 强制指定让null排在最前面, nulls first 或者nulls last
select employee_id,manager_id,first_name,last_name,hire_date,salary from employees order by manager_id nulls first;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--获取前10条记录
-- 方式一
select first_name,last_name,salary from employees order by salary fetch first 10 rows only;
-- 方式二
select first_name,last_name,salary from employees order by salary limit 10;
-- with ties 额外返回数据相同的值(salary) limit达不到这种效果
select first_name,last_name,salary from employees order by salary fetch first 10 rows with ties;
-- 分页查询 limit/fetch pageSize offset pageSize*(pageNum-1)
-- 方式1
select first_name,last_name,salary from employees order by salary fetch first 10 rows only offset 0;
--方式2
select first_name,last_name,salary from employees order by salary limit 10 offset 10;

-- 练习:查找月薪第二高的所有员工
select first_name, last_name, salary
from employees
where salary != (select max(salary) from employees)
order by salary desc
fetch first 1 rows with ties;

1
2
3
4
5
6
7
8
-- 数据汇总: 统计条目数, 总和, 平均数, 最大值, 最小值, 只对数值有效
select count(*),sum(salary),avg(salary),max(salary),min(salary) from employees;
-- 聚合函数忽略null的字段
select count(*),count(manager_id) from employees;
-- 统计时去重, 不含null值
select count(distinct manager_id) from employees;
-- string 聚合, "a" + "b" -> "a$$b", 相当于java中reduce函数
select string_agg(first_name, '$$') from employees;

1
2
3
4
5
6
7
8
9
10
11
-- 分组
select hire_date, count(*)  from employees group by hire_date;
select extract(year from hire_date), department_id, count(*)  from employees group by hire_date,department_id;
-- 空值会被分为一个组
select commission_pct, count(*) from employees group by commission_pct;
-- 分组查询结果的字段, 必须在group by子句中, 否则会报错
-- having子句对分组结果进行过滤
select department_id, count(*) from employees group by department_id having  count(*) > 10;

-- 练习,查询哪些部门月薪大于10000的员工人数超过两个
select department_id,salary,count(*) count from employees group by department_id, salary having count(*) >= 2 and salary >= 10000;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- 高级分组准备表
create table sales (
  item varchar(10),
  year varchar(4),
  quantity int
);
-- 高级分组准备表数据
insert into sales values ('apple','2018',800);
insert into sales values ('apple','2018',1000);
insert into sales values ('banana','2018',500);
insert into sales values ('banana','2018',600);
insert into sales values ('apple','2019',1200);
insert into sales values ('banana','2019',1800);

-- 高级分组
select * from sales;
-- 统计销量
select sum(quantity) from sales;
-- 统计不同产品的销量
select item, sum(quantity) from sales group by item;
-- 统计不同产品的不同年份的销量
select item, year, sum(quantity) from sales group by item,year;
-- rollup更简单的写法, 可以一次性获取上面所有结果
select item,year,sum(quantity) from sales group by rollup (item, year);
-- coalesce给null值的字段设置一个默认值(相当于mysql的ifnull)
select coalesce(item,'所有产品') as 产品,coalesce(year,'所有年份') as 年份 ,sum(quantity) from sales group by rollup (item, year);
-- cube更多种字段的组合
select coalesce(item,'所有产品') as 产品,coalesce(year,'所有年份') as 年份 ,sum(quantity) from sales group by cube (item, year);
-- 更加通用的grouping sets(rollup和cube是sets的特例)
select coalesce(item, '所有产品') as 产品, coalesce(year, '所有年份') as 年份, sum(quantity)
from sales
group by grouping sets ((item,year),(item),()); --和rollup等价
-- 和cube等价
select coalesce(item, '所有产品') as 产品, coalesce(year, '所有年份') as 年份, sum(quantity)
from sales
group by grouping sets ((item,year),(item),(year),()); --和cube等价
-- grouping 函数, 可以查看结果集是不是聚合结果, 1是聚合结果, 0不是聚合结果
select item,year,sum(quantity),grouping(item), grouping(year) from sales group by rollup (item, year);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 连接查询
select * from employees;
select * from departments;
-- 内连接
select d.department_id, e.first_name, d.department_name
from employees e
join departments d
on e.department_id = d.department_id;
-- 外连接
select d.department_id, e.first_name, d.department_name
from employees e
left join departments d
on e.department_id = d.department_id
-- pg中and是连接条件, 不会影响结果行数, 会有很多null值, 所以过滤条件一定要写在where后面
-- and d.department_name = 'IT'
-- where是过滤条件, 对连接的结果进行过滤, 会影响结果行数
where department_name = 'IT';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 交叉连接(笛卡尔积) 生成一个1-9 * 1-9的乘法表
select concat(t1,'*',t2,'=',t1* t2) from generate_series(1,9) t1
cross join generate_series(1,9) t2;

select *
from employees e
join departments d
-- on d.department_id = e.department_id
-- 两个表的连接字段名相同, 可以使用using关键字简化连接条件
using (department_id);
-- 还能使用自然连接简化: 如果两个表有相同的字段(不推荐, 还是指出连接条件好)
select * from employees e natural join departments d;

-- 自连接, 自己连接自己
select *
from employees e
left join employees m
on e.manager_id = m.employee_id;

-- 多个表的连接
select e.first_name, d.department_name, j.job_title
from employees e
join departments d
on e.department_id = d.department_id
join jobs j
on e.job_id = j.job_id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- 条件表达式
select first_name,last_name,
       case department_id
        when 90 then '管理'
        when 60 then '开发'
        else '其他'
        end as 部门
from employees;
-- 不同部门id的人数
select count(case department_id when 10 then 1 end) dept10_count,
       count(case department_id when 20 then 1 end) dept20_count,
       count(case department_id when 30 then 1 end) dept30_count
from employees;
-- 过滤count(*)
select count(*) filter (where department_id = 10) dept10_count from employees;

-- 用case来针对范围来返回不同的结果
select first_name, last_name,
        case
            when salary < 5000 then '低收入'
            when salary between 5000 and 10000 then '中等收入'
            else '高收入'
        end as 收入
from employees;
-- 两个函数: NULLIF coalesce
-- The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1.
select nullif(2,2);
-- 避免除以0错误
select 1/nullif(1,0);
-- Returns the first of its arguments that is not null. Null is returned only if all arguments are null.
-- It is often used to substitute a default value for null values when data is retrieved for display.
select coalesce(1,2,3);
-- 为null设置默认值
select first_name, coalesce(commission_pct,0) from employees;