一、目的
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