pgsql的sql实例（持续更新）
⼀、⽬的
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_p_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,
批量更新sql语句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）使⽤强制索引
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论