pgsql的sql实例(持续更新)
发布日期:2021-07-30 03:25:55 浏览次数:2 分类:技术文章

本文共 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_no

2.创建视图

实例:

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 ,更新前的表字段 :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_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 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:解决问题:xshell6评估已过期
下一篇:(转)npm安装与使用

发表评论

最新留言

初次前来,多多关照!
[***.217.46.12]2024年03月05日 20时49分26秒

关于作者

    喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!

推荐文章

java list详解_java集合List解析 2019-04-21
java坐标代码_java实现计算地理坐标之间的距离 2019-04-21
kettle调用java程序_Kettle ETL调用 java代码来进行数据库的增删改查 2019-04-21
mysql 取两个时间差 php_在php和MySql中计算时间差的方法详解 2019-04-21
mysql 重启数据库实例_mysql 单机多实例重启数据库服务 2019-04-21
collator java_Java Collator getInstance(Locale)用法及代码示例 2019-04-21
dtc mysql_DTCC归来-高可用可扩展数据库架构探讨 2019-04-21
java怎样将日期本土化_Java中的日期操作 2019-04-21
java生产者消费者模型到精通_java生产者消费者模型 2019-04-21
java 执行 awk_3.1 biostar lesson3 linux学习日记;java版本;awk 2019-04-21
java二叉树求权值_百度笔试题目:二叉树路径权值和【转】 2019-04-21
欧亚马 java折叠车_如何选择欧亚马折叠车? 2019-04-21
python函数代码块以什么开头_Python初体验-开篇 代码全析 2019-04-21
java闹钟程序设计_JAVA课程设计_闹钟的设计与实现项目-报告_附源代码.doc 2019-04-21
java中的无效的列类型_java.sql.SQLException: 无效的列类型: 1111 2019-04-21
php rewrite url_PHP_URL Rewrite的设置方法,URL Rewrite需要服务器的支持! - phpStudy 2019-04-21
php读取大文件某行内容,PHP读取和修改大文件的某行内容_PHP教程 2019-04-21
打印php错误日志,php怎样打印错误日志 2019-04-21
Calendar导入java,Java程序使用Calendar.add()方法将分钟添加到当前时间 2019-04-21
mysql中用户线程作用,mysql用户线程的建立与用户线程的状态源码解析 2019-04-21