【笔记】PostgreSQL数据库命令整理及使用样例

秒速五厘米 2022-04-06 03:58 292阅读 0赞

### 文章目录 ###

*   *   *  前言
         *  数据库 表格操作 （创建、切换、查看）
         *  数据操作（增删改查）
         *  条件查询 / 复杂查询
         *  连接

![在这里插入图片描述][20181208144607231.png]

### 前言 ###

> 自从MySQL被Oracle收购之后，Postgresql逐渐成为主流关系型数据库。  
> 查看本笔记需要有一定的MySQL基础。  
> PS： 数据宝贵，文档中涉及到删除的内容，建议进行逻辑删除。表格添加is\_delete字段。

### 数据库 表格操作 （创建、切换、查看） ###

# 连接postgres
    # 建议 方法一
    su - postgres
    
    # 非建议 方法二
    su root  # 有些酌情切换权限
    su postgres
    
    # 进入数据库
    psql
    # 或者
    psql -d databasename
    
    # 退出
    \q
    
    # 退出bash
    exit
    
    # 创建数据库
    CREATE DATABASE database_name;
    create database testdb;  # 示例
    
    # 查看数据库
    \l
    
    # 删除数据库
    DROP DATABASE database_name;
    drop database testdb;  # 示例
    
    # 切换数据库
    \c [database_name]
    \c ×××_db;  # 示例
    
    # 查看当前数据库表格
    \dt
    
    # 切换用户
    \c - [user_name]
    \c - ×××_user;  # 示例
    
    # 创建表格
    CREATE TABLE table_name(  
       column1 datatype,  
       column2 datatype,  
       column3 datatype,  
       .....  
       columnN datatype,  
       PRIMARY KEY( one or more columns )  
    );
    
    CREATE TABLE public.student
    (
      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 '这是一个学生信息表';  # 示例 学生表
    
    # 删除表格
    DROP TABLE table_name;
    drop table student2;
    
    模式(也叫架构)是指定的表集合。
    模式还可以包含视图，索引，序列，数据类型，运算符和函数。
    创建模式在PostgreSQL中，CREATE SCHEMA语句用于创建模式。
    模式不能嵌套。
    
    使用架构的优点：
    模式有助于多用户使用一个数据库，而不会互相干扰。
    它将数据库对象组织成逻辑组，使其更易于管理。
    可以将第三方模式放入单独的模式中，以避免与其他对象的名称相冲突。
    
    语法：
    # 创建模式
    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 TABLE_NAME (column1, column2, column3,...columnN)  
    VALUES (value1, value2, value3,...valueN);
    
    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);  # 示例
    
    # 查询数据
    SELECT "column1", "column2".."column" FROM "table_name";
    SELECT * FROM "table_name";
    SELECT id,name FROM EMPLOYEES;  # 示例
    SELECT ID, NAME, AGE, SALARY  FROM EMPLOYEES;  # 示例
    
    # 改
    # 更新数据
    UPDATE table_name  
    SET column1 = value1, column2 = value2...., columnN = valueN  
    WHERE [condition];
    
    # 删
    # 逻辑删除 更新 is_delete字段。没有的话，添加字段
    update table_name set is_delete = 1 where [condition];
    # 不建议的删除方法
    DELETE FROM table_name WHERE [condition];
    DELETE FROM EMPLOYEES WHERE ID = 1;  # 示例

### 条件查询 / 复杂查询 ###

# order by
    # 在一列或多列的基础上进行(升序/降序)排序。
    SELECT column-list  
    FROM table_name  
    [WHERE condition]  
    [ORDER BY column1, column2, .. columnN] [ASC | DESC];
    # 升序 asc
    # 降序 desc
    SELECT * FROM EMPLOYEES ORDER BY name DESC;
    
    
    # group by
    # 将具有相同数据的表中的这些行分组在一起 配合select一起使用
    SELECT column-list  
    FROM table_name  
    WHERE [conditions ]  
    GROUP BY column1, column2....columnN  
    ORDER BY column1, column2....columnN
    
    SELECT NAME, SUM(SALARY) FROM EMPLOYEES GROUP BY NAME;  # 示例 可以消除重复
    消除重复冗余示例：https://www.yiibai.com/postgresql/postgresql-group-by-clause.html
    
    
    # having
    # HAVING子句与GROUP BY子句组合使用，用于选择函数结果满足某些条件的特定行
    SELECT column1, column2  
    FROM table1, table2  
    WHERE [ conditions ]  
    GROUP BY column1, column2  
    HAVING [ conditions ]  
    ORDER BY column1, column2
    
    # 示例 显示名称(name)数量小于2的记录
    SELECT NAME FROM EMPLOYEES GROUP BY NAME HAVING COUNT (NAME) < 2; 
    # 示例 查询表“EMPLOYEES”中name字段值计数大于1的名称
    SELECT NAME,COUNT (NAME) FROM EMPLOYEES GROUP BY NAME HAVING COUNT (NAME) > 1;
    
    # 条件
    # 条件用于从数据库获取更具体的结果。 它们通常与WHERE子句一起使用。 具有子句的条件就像双层过滤器。
    # AND
    SELECT column1, column2, ..... columnN    
    FROM table_name    
    WHERE [search_condition]    
    AND [search_condition];
    # 示例 所有ID小于4并且薪水大于120000的员工数据信息
    SELECT * FROM EMPLOYEES WHERE SALARY > 120000 AND ID <= 4;
    
    # OR
    # OR条件与WHERE子句一起使用，以从表中的一列或多列列中选择唯一数据
    SELECT column1, column2, ..... columnN    
    FROM table_name    
    WHERE [search_condition]    
    OR [search_condition];
    # 示例 查询名字是Minsu或者地址为Noida员工信息
    SELECT *FROM  EMPLOYEES WHERE NAME = 'Minsu' OR ADDRESS = 'Noida';
    
    # AND & OR
    # AND＆OR条件在仅一个查询中提供了AND和OR条件的优点
    SELECT column1, column2, ..... columnN    
    FROM table_name    
    WHERE [search_condition]  AND [search_condition]     
    OR [search_condition];
    # 示例 查询名字的值为Minsu和地址的值为’Delhi‘，或者ID值大于等8的记录信息
    SELECT *FROM EMPLOYEES WHERE (NAME = 'Minsu' AND ADDRESS = 'Delhi') OR (ID>= 8);
    
    # NOT
    # NOT条件与WHERE子句一起使用以否定查询中的条件
    SELECT column1, column2, ..... columnN    
    FROM table_name    
    WHERE [search_condition] NOT [condition];
    # 示例 查询那些地址不为NULL的记录信息
    SELECT * FROM EMPLOYEES WHERE address IS NOT NULL ;
    # 示例 查询那些年龄不是21和24的所有记录
    SELECT * FROM EMPLOYEES WHERE age NOT IN(21,24) ;
    
    # LIKE
    # LIKE条件与WHERE子句一起用于从指定条件满足LIKE条件的表中获取数据
    SELECT column1, column2, ..... columnN    
    FROM table_name    
    WHERE [search_condition] LIKE [condition];
    # 示例 查询名字以Ma开头的数据记录
    SELECT * FROM EMPLOYEES WHERE NAME LIKE 'Ma%';
    # 示例 查询名字以su结尾的数据记录，如下查询语句：SELECT * 
    FROM EMPLOYEES WHERE NAME LIKE '%su';
    # 示例 查询地址中含有大道的数据记录，如下查询语句：SELECT * 
    FROM EMPLOYEES WHERE address LIKE '%大道%';
    
    # IN
    # IN条件与WHERE子句一起使用，从指定条件满足IN条件的表中获取数据
    SELECT column1, column2, ..... columnN    
    FROM table_name    
    WHERE [search_condition] IN [condition];
    # 示例 查询employee表中那些年龄为19，21的员工信息
    SELECT * FROM EMPLOYEES WHERE AGE IN (19, 21);
    
    # NOT IN
    # NOT IN条件与WHERE子句一起使用，以从指定条件否定IN条件的表中获取数据。
    SELECT column1, column2, ..... columnN    
    FROM table_name    
    WHERE [search_condition] NOT IN [condition];
    # 示例 查询那些年龄不是19，25的数据
    SELECT * FROM EMPLOYEES WHERE AGE NOT IN (19, 25);
    # 示例 查询那些名字不是Minsu，Maxsu的数据信息
    SELECT * FROM EMPLOYEES WHERE name NOT IN ('Maxsu', 'Minsu');
    
    # BETWEEN
    # BETWEEN条件与WHERE子句一起使用，以从两个指定条件之间的表中获取数据。
    SELECT column1, column2, ..... columnN    
    FROM table_name    
    WHERE [search_condition] BETWEEN [condition];
    # 示例 查询employees表中年龄在24~27之间(含24，27)的数据
    SELECT * FROM EMPLOYEES WHERE AGE BETWEEN 24 AND 27;

### 连接 ###

连接类型：

*  内连接(INNER JOIN)
 *  左外连接(LEFT OUTER JOIN)
 *  右外连接(RIGHT OUTER JOIN)
 *  全连接(FULL OUTER JOIN)
 *  跨连接(CROSS JOIN)

# INNER JOIN
    # 内连接也称连接或简单连接。 这是最常见的连接类型。 此连接返回满足连接条件的多个表中的所有行。
    SELECT table1.columns, table2.columns  
    FROM table1  
    INNER JOIN table2  
    ON table1.common_filed = table2.common_field;
    # 连接两个表
    SELECT EMPLOYEES.ID, EMPLOYEES.NAME, DEPARTMENT.DEPT FROM EMPLOYEES   
    INNER JOIN DEPARTMENT ON EMPLOYEES.ID = DEPARTMENT.ID;
    
    # 左外连接
    # 左外连接返回从“ON”条件中指定的左侧表中的所有行，只返回满足条件的另一个表中的行
    SELECT table1.columns, table2.columns  
    FROM table1  
    LEFT OUTER JOIN table2  
    ON table1.common_filed = table2.common_field;
    # 示例
    SELECT EMPLOYEES.ID, EMPLOYEES.NAME, DEPARTMENT.DEPT FROM EMPLOYEES 
    LEFT OUTER JOIN DEPARTMENT ON EMPLOYEES.ID = DEPARTMENT.ID;
    
    # 右外连接
    # 右外连接返回从“ON”条件中指定的右侧表中的所有行，只返回满足条件的另一个表中的行
    SELECT table1.columns, table2.columns  
    FROM table1  
    RIGHT OUTER JOIN table2  
    ON table1.common_filed = table2.common_field;
    # 示例
    SELECT EMPLOYEES.ID, EMPLOYEES.NAME, DEPARTMENT.DEPT FROM EMPLOYEES 
    RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEES.ID = DEPARTMENT.ID;
    
    # 全外连接
    # 全外连接从左表和左表中返回所有行。 它将NULL置于不满足连接条件的位置。
    SELECT table1.columns, table2.columns  
    FROM table1  
    FULL OUTER JOIN table2  
    ON table1.common_filed = table2.common_field;
    # 示例
    SELECT EMPLOYEES.ID, EMPLOYEES.NAME, DEPARTMENT.DEPT FROM EMPLOYEES 
    FULL OUTER JOIN DEPARTMENT ON EMPLOYEES.ID = DEPARTMENT.ID;
    
    # 交叉连接 / 跨连接
    # 跨连接(CROSS JOIN)将第一个表的每一行与第二个表的每一行相匹配。
    # 它也被称为笛卡尔积。 如果table1具有“x”行，而table2具有“y”行，则所得到的表将具有(x * y)行。
    SELECT coloums   
    FROM table1   
    CROSS JOIN table2
    # 示例
    SELECT NAME, DEPT 
    FROM EMPLOYEES  
    CROSS JOIN DEPARTMENT;

样例整理自：[https://www.yiibai.com/postgresql][https_www.yiibai.com_postgresql]

[20181208144607231.png]: /images/20220406/434a1a3c94d9454a8a7af45eb2ba17f7.png
[https_www.yiibai.com_postgresql]: https://www.yiibai.com/postgresql