postgreSQL查漏补缺

# 我遇到的面试题

说说 union和union all的区别

union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高

# 概念

常用的数据类型有：

整数
浮点
日期/时间
字符串
二进制

数据库系统有3个组成部分

数据库
数据库管理系统
数据库应用系统

# SQL

SQL是对数据库进行查询和修改的语言

4个部分

DDL: DROP/CREATE/ALTER
DML:INSERT/UPDATE/DELETE
DQL:SELECT
DCL:GRANT/REVOKE...

# 常用的数据库访问技术

目前流行的数据库访问技术

ODBC: Open Database Connectivity
JDBC
ADO.NET
PDO

# PostgreSQL介绍

# 发展历程

# 主要特点

免费
速度快
平台可移植性
丰富的接口
面向对象特性
安全性
配置的开源软件很多

# psql语法

# 空值排序


-- 相当于 order by emanager asc nulls last
select * from emp_test order by emanager;

-- 默认
select * from emp_test order by emanager asc nulls last;

-- nulls first: 空值排在前面
select * from emp_test order by emanager asc nulls first;

# exists

where exists xxxxx

exists 是真，则执行前面的查询

select exists(select * from (select 'true' where 1=1) as b);
select exists(select * from (select 'true' where 1=1) as b);

# some、any、all 查询

一. ANY/SOME

WHERE expression operator ANY (subquery) WHERE expression operator SOME (subquery)

其实ANY和SOME在这里是同等效的，子查询的结果集只能是一个字段，左边表达式使用operator对结果集的每一行进行一次比较运算，如果有一个运算结果是'TRUE'，则表达式结果为'TRUE'，如果比较结果全部是'FALSE'表达式结果才是'FALSE'。

> ANY 大于子查询结果中的某个值

< ANY 小于子查询结果中的某个值

>= ANY 大于或等于子查询结果中的某个值

<= ANY 小于或等于子查询结果中的某个值

= ANY 等于子查询结果中的某个值，相当于IN

！= ANY 不等于子查询结果中的某个值

示例1.查询tbl_insert表,条件是字段a大于表tbl_test字段f某一行中的值

test=# select * from tbl_insert where a > any(select f from tbl_test);
 a | b |   c   
---+---+-------
 2 | 2 | 22
 3 | 3 | 33
 4 | 4 | 44
 5 | 5 | 51
 6 | 6 | 1
 6 | 6 | 61
 6 | 6 | 661
 7 | 7 | 3%1
 8 | 8 | 3%_1
 8 | 8 | 3_%_1
 7 | 7 | abc
 7 | 7 | ABc
 7 | 7 | aBC
(13 rows)

二. ALL

WHERE expression operator ALL(subquery)

同样子查询中仍只能返回一个字段，与子查询结果集每一行进行比较结果全部是'TRUE'表达式结果才是'TRUE'，否则为'FALSE'。

> ALL 大于子查询结果中的所有值

< ALL 小于子查询结果中的所有值

>= ALL 大于或等于子查询结果中的所有值

<= ALL 小于或等于子查询结果中的所有值

= ALL 等于子查询结果中所有值（除非子查询的结果全部相等，所以实际上没什么意义）

！= ALL 不等于子查询结果中的任何一个值，相当于NOT IN

示例1.查询tbl_insert表中a最大的行

test=# select * from tbl_insert where a = (select max(a) from tbl_insert);
 a | b |   c   
---+---+-------
 8 | 8 | 3%_1
 8 | 8 | 3_%_1
(2 rows)

test=# select * from tbl_insert where a >= all(select a from tbl_insert);
 a | b |   c   
---+---+-------
 8 | 8 | 3%_1
 8 | 8 | 3_%_1
(2 rows)

示例2.查询tbl_insert表中a最小的行

test=# select * from tbl_insert where a <= all(select a from tbl_insert);
 a | b | c  
---+---+----
 1 | 1 | 11
(1 row)

test=# select * from tbl_insert where a = (select min(a) from tbl_insert);
 a | b | c  
---+---+----
 1 | 1 | 11
(1 row)

# 左外连接

left outer join 怎么理解？左外连接就是做连接。只是相对inner join 而言

CREATE TABLE employee
(
  id integer primary key,
  name varchar(50),
  dept_id integer
);

insert into employee values (1, '张三', 1);
insert into employee values (2, '李四', 2);
insert into employee values (3, '王五', 3);
insert into employee values (4, '李洋', null);
insert into employee values (5, 'Marry', null);
insert into employee values (6, 'lily', null);
insert into employee values (7, 'Larry', null);


CREATE TABLE department
(
  id integer primary key,
  dept text,
  fac_id integer
);


-- 插入数据
INSERT INTO department VALUES(1,'IT', 1);
INSERT INTO department VALUES(2,'Engineering', 2);
INSERT INTO department VALUES(3,'HR', 7);


-- 这两写法结果相同
select * from employee e left join department d on e.dept_id = d.id;
select * from employee e left outer join department d on e.dept_id = d.id;

# unoin 和unoin all

unoin 执行速度慢。unoin all执行速度快,因为不会去除重复

select id,name from employee where id > 3 union (select id,name from employee where id < 5);

select id,name from employee where id > 3 union all (select id,name from employee where id < 5);

# update returning

更新是返回更新后该行的数据

update dummy_table set age=30 returning age as age_no,name,address;

# view的相关操作

create or replace view vi as select * from dummy_table where age is not NULL;

此时删除v2（视图）中的数据，也会删除dummy_table中的数据。


insert into test_table values(10,'A1','CEO',null);
insert into test_table values(11, 'B1', 'VP', 10);
insert into test_table values(12, 'B2', 'VP', 10);
insert into test_table values(13, 'B3', 'VP', 10);
insert into test_table values(14, 'C1', 'DIRECTOR', 13);
insert into test_table values(15, 'C2', 'DIRECTOR', 13);
insert into test_table values(16, 'D1', 'MANAGER', 15);
insert into test_table values(17 ,'E1', 'ENGINEER', 11);
insert into test_table values(18, 'E2', 'ENGINEER', 11);

# psql常用指令

\c db_name: 连接数据库

\dt: 列出所有表

创建schema create schema schame_name;

show search_path: 现实搜索路径，即schema的搜索路径，默认为public

set search_path='demo',$user,'public'设置搜索路径

\l 列出所有的数据库

\?帮助

\d+ table_name 显示表结构

\dt 列出所有表

# 递归查询

查询出管理的所有人

CREATE TABLE emp_test (
  id int,
  ename varchar(255),
  emanager int
);

INSERT INTO emp_test VALUES (1, 'abc', null);
INSERT INTO emp_test VALUES (2, 'xyz', 1);
INSERT INTO emp_test VALUES (3, 'def', 2);
INSERT INTO emp_test VALUES (4, 'cde', 1);
INSERT INTO emp_test VALUES (5, 'qrs', 2);
INSERT INTO emp_test VALUES (9, 'iop', 3);
INSERT INTO emp_test VALUES (10, 'klm', 4);

-- 查询所有下属
WITH RECURSIVE emp_testnew  AS (
  SELECT id, ename, emanager
  FROM emp_test
  WHERE id = 2
  UNION ALL
  SELECT e.id, e.ename, e.emanager
  FROM emp_test e
  INNER JOIN emp_testnew e1 ON e1.id = e.emanager
)
SELECT *
FROM emp_testnew;

-- 查询所有上级管理人员
WITH RECURSIVE emp_testnew  AS (
  SELECT id, ename, emanager
  FROM emp_test
  WHERE id = 9
  UNION ALL
  SELECT e.id, e.ename, e.emanager
  FROM emp_test e
  INNER JOIN emp_testnew e1 ON e1.emanager = e.id
)
SELECT *
FROM emp_testnew;

# ALTER TABLE

-- 添加列
alter table dummy_table add col1 int;

-- 重命名列
alter table dummy_table  rename col1 to col2;

-- 修改列的类型
alter table dummy_table alter column col2 type char;

-- 删除列
alter table dummy_table drop column col2;

# COMMENT

注释


-- 给表添加注释
Comment on table dummy_table  is 'This was a testing table';

-- 方法添加注释
Comment on function f is 'Don''t update the function please' ;

← docker-compose postgresQL sath89/oracle-xe-11g →