PostgreSQL数据库学习笔记

基本数据类型

在开始学语法之前，还是先要看一下他的基本数据类型都有哪些：

• 数值数据类型
• 字符串数据类型
• 日期/时间数据类型

数值数据类型

字符串数据类型

日期/时间数据类型

一些其他数据类型

布尔类型：

货币类型：

几何类型：

几何数据类型表示二维空间对象。最根本的类型：点 - 形成所有其他类型的基础。

重点用在postgis上的数据。

语法篇

创建数据库

CREATE DATABASE database_name;

删除数据库

DROP DATABASE database_name

创建表

CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,
  ... ...
    columnN datatype,
  PRIMARY KEY(one or more columns)
)

-- eg:
CREATE TABLE public.student2
(
  id integer NOT NULL,
  name character(100),
  subjects character(1),
  CONSTRAINT student2_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.student2
  OWNER TO postgres;
COMMENT ON TABLE public.student2
  IS '这是一个学生信息表2';

删除表

DROP TABLE student2;

创建/删除模式. 表

模式(也叫架构)是指定的表集合。 它还可以包含视图，索引，序列，数据类型，运算符和函数。

CREATE SCHEMA schema_name;


CREATE SCHEMA myschema;

-- Table: myschema.tb_test
-- DROP TABLE myschema.tb_test;
CREATE TABLE myschema.tb_test
(
  id integer,
  name character(254)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE myschema.tb_test
  OWNER TO postgres;

执行语句

插入数据

INSERT INTO EMPLOYEES(  ID, NAME, AGE, ADDRESS, SALARY)  
VALUES
 (1, 'Maxsu', 25, '海口市人民大道2880号', 109990.00 ), 
(2, 'minsu', 25, '广州中山大道 ', 125000.00 ), 
(3, '李洋', 21, '北京市朝阳区', 185000.00),   
(4, 'Manisha', 24, 'Mumbai', 65000.00), 
(5, 'Larry', 21, 'Paris', 85000.00);

删除数据

DELETE FROM EMPLOYEES  
WHERE ID = 1;

修改数据

update employee
    set age=29, salary=1000000
    where id=1;

查询数据

select id, age from employee;

ORDER BY

-- ASC升序(默认为升序)  DESC为降序
SELECT *   
FROM EMPLOYEES 
ORDER BY name DESC;

GROUP BY

-- group by 除冗余 name相同时，sum(salary)为相同name的sum

SELECT NAME, SUM(SALARY)   
FROM EMPLOYEES   
GROUP BY NAME;

HAVING

一般与group by连用，用于选择函数结果满足某些条件的特定行

select name, count(name) from employees
group by name having count(name) > 1;

条件查询

• and 条件

  SELECT *  
  FROM EMPLOYEES  
  WHERE SALARY > 120000  
  AND ID <= 4;
  

• or条件

  SELECT *  
  FROM  EMPLOYEES 
  WHERE NAME = 'Minsu'  
  OR ADDRESS = 'Noida';
  

• and & or条件

  SELECT *  
  FROM EMPLOYEES  
  WHERE (NAME = 'Minsu' AND ADDRESS = 'Delhi')  
  OR (ID>= 8);
  

• not条件

  SELECT *  
  FROM EMPLOYEES  
  WHERE address IS NOT NULL;
  

• like条件

  -- 查 name以Ma开头
  SELECT *   
  FROM EMPLOYEES   
  WHERE NAME LIKE 'Ma%';
  
  -- 查 name以su结尾
  select * 
  from employees
  where name like '%su';
  
  -- 查 address中有'大道'记录
  select * 
  from employees
  where addtress like '%大道%';
  

• in条件

  -- 查询employee表中那些年龄 为19，21的员工信息，执行以下查询：
  select * 
  from employees
  where age in (19,21);
  

• not in 条件

  -- 查询employee表中那些年龄 不是 19，21的员工信息，执行以下查询：
  select * 
  from employees
  where age not in (19,21);
  

• between条件

  -- 查询employees表中年龄在24~27之间(含24，27)的数据信息，执行以下查询：
  select * 
  from employees
  where age between 24 and 27;
  

连接(画圈内外左右连接)

<span style="color:red">与mysql同理</span>

高级语法

视图

视图其实就是一张伪表，它不是物理表，而是可以用来普通表选择查询。

它可以包含一个或者多个表的所选行。

-- 我们把员工信息表中 name，id，salary抽出来，作为一个视图
create view current_employees as 
select name, id, salary 
from employees;

函数(存储过程)

PostgreSQL函数也称为PostgreSQL存储过程。PostgreSQL函数是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明、分配、循环、控制流程等)。有助于通常在数据库中单个函数中进行多次查询和往返操作的操作。

-- 语法
CREATE [OR REPLACE] FUNCTION function_name (arguments)   
RETURNS return_datatype AS $variable_name$  
  DECLARE  
    declaration;  
    [...]  
  BEGIN  
    < function_body >  
    [...]  
    RETURN { variable_name | value }  
  END; LANGUAGE plpgsql;
  
-- function_name：指定函数的名称。
-- [OR REPLACE]：是可选的，它允许您修改/替换现有函数。
-- RETURN：它指定要从函数返回的数据类型。它可以是基础，复合或域类型，或者也可以引用表列的类型。
-- function_body：function_body包含可执行部分。
-- plpgsql：它指定实现该函数的语言的名称。

eg：

-- 创建函数
create or replace function totalrecords () 
returns integer as $total$ 
declare 
    total integer;
begin
    select count(*) into from employees;
    return total;
end;
$total$ language plpgsql;

-- 执行函数
select totalrecords();

学习链接：https://www.yiibai.com/postgresql/postgresql-trigger.html