pgsql的sql实例（持续更新）

最新推荐文章于 2024-05-25 01:12:20 发布

RayBreslin

最新推荐文章于 2024-05-25 01:12:20 发布

阅读量1.9k

点赞数

分类专栏： postgresql 数据库文章标签： pgsql sql 函数存储过程触发器

本文链接：https://blog.csdn.net/u010886217/article/details/98120264

版权

postgresql 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

一、目的

pgsql常用的sql以及对应的实例。

二、具体实例

1.pgsql总结：函数
（1）连接字符：concat或者||
实例：

-》SELECT concat(student_de.id,student_de.NAME) hebing from student_de 
INNER  join studentbak on student_de."id"=studentbak."id"
-》SELECT student_de.id||student_de."name" as hebing from student_de 
INNER  join studentbak on student_de."id"=studentbak."id"

（2）剪切字符：substr
实例：

SELECT substr(student_de.NAME,2,1) hebing from student_de 
INNER  join studentbak on student_de."id"=studentbak."id"

总结：substr(字段,n1,n2)，从n1个字符开始，往后延n2位

（3）判断情况case when

case 
when ... then ...
when ... then ...
else ...
end

实例：

    case
    when ppt.filetype='1' THEN
    NULL
    when ppt.filetype='0' THEN
    end

（4）判断是否包含数组中position
实例：

position(lower(ppt.filetype) IN 'doc,docx,xls')>0

总结：position(lower(ppt.filetype) IN 'doc,docx,xls')判断lower(ppt.filetype)是在后续字符串的起始位置。

（5）取字段的lower、upper

（6）替换replace

题目描述：查找字符串'10,A,B' 中逗号','出现的次数cnt。
解答：
select (length('10,A,B')-length(replace('10,A,B',',','')))/length(',') cnt

（6）聚合连接函数group_concat

目描述
按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
输出格式:
dept_no	employees
d001	10001,10002
d002	10006
d003	10005
d004	10003,10004

解答：

select de.dept_no,group_concat(de.emp_no) employees
from dept_emp de
group by de.dept_no

2.创建视图

实例：

create view aaa as
select 
    ppt.id,
    ppt.name,
    (
    case
    when ppt.filetype='1' THEN
    NULL
    when ppt.filetype='0' THEN
    end
    )
from upfile up
    left join organization org on up.id=org.id
where
    up.isuse=true

3.存储过程【额外过程】
数据库存储过程

CREATE OR REPLACE FUNCTION 函数名(参数1，[整型 int4, 整型数组 _int4, ...])  
RETURNS 返回值类型 AS  
$BODY$  
DECLARE  
变量声明  
BEGIN  
函数体  
END;  
$BODY$  
LANGUAGE ‘plpgsql’ VOLATILE

实例：

create or replace function test_s1 (input1 integer)
RETURNS integer as 
$body$
	declare
		v_1 INTEGER :=2;
		v_2 INTEGER :=input1;
	begin
		v_1=v_1+v_2;
		return v_1;
	end;
$body$
LANGUAGE plpgsql
调用：
SELECT test_s1(4)
结果：
6

（1）if 条件

IF ... THEN
IF ... THEN ... ELSE ... END IF

实例：

create or replace FUNCTION test() RETURNS INTEGER as
$body$
    declare
        a INTEGER=2;
        b INTEGER=5;
        c INTEGER;
    BEGIN
        if(b>5) then
            c=a+b;
        else
            c=a-b;
        end if;

        RETURN c;
    end;
$body$
LANGUAGE plpgsql;

（2）循环：LOOP，EXIT，CONTINUE，WHILE，和 FOR 语句，可以控制PL/pgSQL 函数重复一系列命令。
-》LOOP

[ <<label>> ]
LOOP
    statements
    EXIT [ label ] [ WHEN boolean-expression ];
END LOOP [ label ];

*LOOP定义一个无条件的循环，无限循环，直到由EXIT或RETURN语句终止。*可选的label可以由EXIT和CONTINUE语句使用，用于在嵌套循环中声明应该应用于哪一层循环。
*如果声明了WHEN，循环退出只有在boolean-expression为真的时候才发生，否则控制会落到EXIT后面的语句上。
实例：

CREATE OR REPLACE FUNCTION loop()
RETURNS void
AS $body_ofloop$
DECLARE
n numeric := 0;
BEGIN
  LOOP
    n := n + 1;
    RAISE NOTICE 'n 的当前值为: %',n;
    EXIT WHEN n > 10;
  END LOOP;
END;
$body_ofloop$ 
LANGUAGE PLPGSQL;

SELECT loop()

（3）while

WHILE boolean-expression LOOP
    statements;
END LOOP;

实例：

CREATE OR REPLACE FUNCTION while()
RETURNS void
AS $$
DECLARE
n numeric := 0;
BEGIN
    WHILE n < 10 LOOP
    n := n + 1;
    RAISE NOTICE 'n 的当前值为: %',n;
        END LOOP;
END;
$$ LANGUAGE PLPGSQL;

select while()

4.触发器
应用实例：

题目：

构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

解答：

create trigger audit_log after insert on employees_test
begin
    insert into audit values (NEW.ID,NEW.NAME);
end;

总结：
（1）触发器出发时间：事务发生after（之后）/before（之前）时触发
（2）触发具体操作：insert/delete/update
（3）触发器具体操作：begin-end之间出发动作
（4）触发前后基本标识：更新后的表字段:NEW ，更新前的表字段 :OLD

5.区别where/group by/having/

select emp_no,count(*) as t from salaries  group by emp_no having t>15

总结：having的原理是先进行select语句，然后从select出来的字段进行筛选；而where是先筛选数据表中原有的字段，然后在select。所以，这里应当营haveing。

6.索引

（1）创建索引

针对如下表actor结构创建索引：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

解答：
create unique  index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name)

（2）使用强制索引

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
create index idx_emp_no on salaries(emp_no);

解答：SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005

7.添加一列

题目描述
存在actor表，包含如下列信息：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')));
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000 00:00:00'

解答：alter table actor  add column  `create_date` datetime not NULL DEFAULT '0000-00-00 00:00:00'

8.依据其他表，创建新表

题目描述：
对于如下表actor，其对应的数据为:
actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：
列表	类型	是否为NULL	含义
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏

解答：
create table actor_name as
select first_name,last_name from actor

9.插入数据，重复处理方法

对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
actor_id	first_name	last_name	last_update
'3'	'ED'	'CHASE'	'2006-02-15 12:34:33'

解答：
insert or ignore into actor
values(3,'ED','CHASE','2006-02-15 12:34:33')

10.删除数据

删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

解答：

delete from titles_test  where id not in 
(select min(id) from titles_test group by emp_no)

11.修改表名

将titles_test表名修改为titles_2017。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

解答：

alter table titles_test rename to titles_2017

12.外键

题目：在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

解答：

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

三、同表多复合使用

1.题目：查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序。

解答：

select e.emp_no,(s2.salary-s1.salary) growth
from employees e,salaries s1,salaries s2
where e.emp_no=s1.emp_no and e.emp_no=s2.emp_no 
and s1.from_date=e.hire_date
and s2.to_date='9999-01-01'
order by growth asc

2.题目：对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解答：

select s1.emp_no,s1.salary,count(distinct s2.salary) as rank
from salaries s1,salaries s2 
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01' and s1.salary<=s2.salary
group by s1.emp_no 
order by s1.salary desc,s1.emp_no  asc
总结：
（1）按照s1的salary排序：s1.salary desc
（2）去重：distinct

3.题目：获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解答：

select de.dept_no,e.emp_no,s.salary
from employees e,salaries s,dept_emp de
where e.emp_no=s.emp_no and de.emp_no=e.emp_no 
and de.to_date='9999-01-01'
and s.to_date='9999-01-01'
and e.emp_no not in (
    select emp_no from dept_manager where dept_manager.to_date='9999-01-01'
)

总结：
（1）当前员工通过to_date='9999-01-01'判断
（2）如何获得非manager的员工

4.题目

（1）film表
字段	说明
film_id	电影id
title	电影名称
description	电影描述信息

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));

（2）category表
字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));

（3）film_category表
字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

解答：

select c.name,count(f.film_id) 
from film f,film_category fc,category c,(
    select category_id 
    from film_category 
    group by category_id  having count(film_id)>=5
) cc 
where f.film_id =fc.film_id and fc.category_id=c.category_id
and f.description like '%robot%'
and c.category_id=cc.category_id

总结：
1."该分类对应电影数量>=5部"，指的是所有电影的分类的个数；
2.模糊查询：f.description like '%robot%'，要加上''!

5.嵌套查询

题目描述
对于employees表中，给出奇数行的first_name
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
输出格式:

first_name
Georgi
Chirstian
Anneke

结果：

select e1.first_name from employees e1 where
(
    select count(*) from employees e2 where e1.first_name>=e2.first_name
)%2=1

四、参考

1.PostgreSQL存储过程(3)-流程控制语句

https://www.cnblogs.com/lottu/p/7405829.html

RayBreslin

关注

0
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
pgsql的sql实例（持续更新）

一、目的pgsql常用的sql以及对应的实例。二、具体实例1.pgsql总结：函数（1）连接字符：concat或者||实例：-》SELECT concat(student_de.id,student_de.NAME) hebing from student_deINNER join studentbak on student_de."id"=studentbak."id...
复制链接

扫一扫