本文共 10655 字,大约阅读时间需要 35 分钟。
一、目的
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以及连接出的结果employeesCREATE 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 employeesd001 10001,10002d002 10006d003 10005d004 10003,10004
解答:
select de.dept_no,group_concat(de.emp_no) employeesfrom dept_emp degroup by de.dept_no2.创建视图
实例:
create view aaa asselect 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.idwhere 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 ... THENIF ... 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[ <
*LOOP定义一个无条件的循环,无限循环,直到由EXIT或RETURN语句终止。*可选的label可以由EXIT和CONTINUE语句使用,用于在嵌套循环中声明应该应用于哪一层循环。
*如果声明了WHEN,循环退出只有在boolean-expression为真的时候才发生, 否则控制会落到EXIT后面的语句上。 实例:CREATE OR REPLACE FUNCTION loop()RETURNS voidAS $body_ofloop$DECLAREn 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 voidAS $$DECLAREn 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_testbegin insert into audit values (NEW.ID,NEW.NAME);end;
总结:
(1)触发器出发时间:事务发生after(之后)/before(之前)时触发 (2)触发具体操作:insert/delete/update (3)触发器具体操作:begin-end之间出发动作 (4)触发前后基本标识: 更新后的表字段:NEW ,更新前的表字段 :OLD5.区别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_update1 PENELOPE GUINESS 2006-02-15 12:34:332 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 asselect 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 actorvalues(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) growthfrom employees e,salaries s1,salaries s2where e.emp_no=s1.emp_no and e.emp_no=s2.emp_no and s1.from_date=e.hire_dateand 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 rankfrom salaries s1,salaries s2 where s1.to_date='9999-01-01' and s2.to_date='9999-01-01' and s1.salary<=s2.salarygroup 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.salaryfrom employees e,salaries s,dept_emp dewhere 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 电影idtitle 电影名称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 电影分类idname 电影分类名称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 电影idcategory_id 电影分类idlast_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_idand f.description like '%robot%'and c.category_id=cc.category_id总结:1."该分类对应电影数量>=5部",指的是所有电影的分类的个数;2.模糊查询:f.description like '%robot%',要加上''!
5.嵌套查询
题目描述对于employees表中,给出奇数行的first_nameCREATE 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_nameGeorgiChirstianAnneke
结果:
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://blog.csdn.net/u010886217/article/details/98120264 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!