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

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

文章目录

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

在这里插入图片描述

前言

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

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

  1. # 连接postgres
  2. # 建议 方法一
  3. su - postgres
  5. # 非建议 方法二
  6. su root  # 有些酌情切换权限
  7. su postgres
  9. # 进入数据库
  10. psql
  11. # 或者
  12. psql -d databasename
  14. # 退出
  15. \q
  17. # 退出bash
  18. exit
  20. # 创建数据库
  21. CREATE DATABASE database_name;
  22. create database testdb;  # 示例
  24. # 查看数据库
  25. \l
  27. # 删除数据库
  28. DROP DATABASE database_name;
  29. drop database testdb;  # 示例
  31. # 切换数据库
  32. \c [database_name]
  33. \c ×××_db;  # 示例
  35. # 查看当前数据库表格
  36. \dt
  38. # 切换用户
  39. \c - [user_name]
  40. \c - ×××_user;  # 示例
  42. # 创建表格
  43. CREATE TABLE table_name(  
  44.    column1 datatype,  
  45.    column2 datatype,  
  46.    column3 datatype,  
  47.    .....  
  48.    columnN datatype,  
  49.    PRIMARY KEY( one or more columns )  
  50. );
  52. CREATE TABLE public.student
  53. (
  54.   id integer NOT NULL,
  55.   name character(100),
  56.   subjects character(1),
  57.   CONSTRAINT student2_pkey PRIMARY KEY (id)
  58. )
  59. WITH (
  60.   OIDS=FALSE
  61. );
  62. ALTER TABLE public.student2
  63.   OWNER TO postgres;
  64. COMMENT ON TABLE public.student2
  65.   IS '这是一个学生信息表';  # 示例 学生表
  67. # 删除表格
  68. DROP TABLE table_name;
  69. drop table student2;
  71. 模式(也叫架构)是指定的表集合。
  72. 模式还可以包含视图，索引，序列，数据类型，运算符和函数。
  73. 创建模式在PostgreSQL中，CREATE SCHEMA语句用于创建模式。
  74. 模式不能嵌套。
  76. 使用架构的优点：
  77. 模式有助于多用户使用一个数据库，而不会互相干扰。
  78. 它将数据库对象组织成逻辑组，使其更易于管理。
  79. 可以将第三方模式放入单独的模式中，以避免与其他对象的名称相冲突。
  81. 语法：
  82. # 创建模式
  83. CREATE SCHEMA schema_name;
  84. CREATE SCHEMA myschema;  # 示例
  86. # 在模式中创建表
  87. -- Table: myschema.tb_test
  89. -- DROP TABLE myschema.tb_test;
  91. CREATE TABLE myschema.tb_test
  92. (
  93.   id integer,
  94.   name character(254)
  95. )
  96. WITH (
  97.   OIDS=FALSE
  98. );
  99. ALTER TABLE myschema.tb_test
  100.   OWNER TO postgres;

数据操作（增删改查）

  1. # 增
  2. # 插入数据
  3. INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  
  4. VALUES (value1, value2, value3,...valueN);
  6. INSERT INTO EMPLOYEES(  ID, NAME, AGE, ADDRESS, SALARY)  
  7. VALUES 
  8. (1, 'Maxsu', 25, '海口市人民大道2880号', 109990.00 ), 
  9. (2, 'minsu', 25, '广州中山大道 ', 125000.00 ), 
  10. (3, '李洋', 21, '北京市朝阳区', 185000.00),   
  11. (4, 'Manisha', 24, 'Mumbai', 65000.00), 
  12. (5, 'Larry', 21, 'Paris', 85000.00);  # 示例
  14. # 查询数据
  15. SELECT "column1", "column2".."column" FROM "table_name";
  16. SELECT * FROM "table_name";
  17. SELECT id,name FROM EMPLOYEES;  # 示例
  18. SELECT ID, NAME, AGE, SALARY  FROM EMPLOYEES;  # 示例
  20. # 改
  21. # 更新数据
  22. UPDATE table_name  
  23. SET column1 = value1, column2 = value2...., columnN = valueN  
  24. WHERE [condition];
  26. # 删
  27. # 逻辑删除 更新 is_delete字段。没有的话，添加字段
  28. update table_name set is_delete = 1 where [condition];
  29. # 不建议的删除方法
  30. DELETE FROM table_name WHERE [condition];
  31. DELETE FROM EMPLOYEES WHERE ID = 1;  # 示例

条件查询 / 复杂查询

  1. # order by
  2. # 在一列或多列的基础上进行(升序/降序)排序。
  3. SELECT column-list  
  4. FROM table_name  
  5. [WHERE condition]  
  6. [ORDER BY column1, column2, .. columnN] [ASC | DESC];
  7. # 升序 asc
  8. # 降序 desc
  9. SELECT * FROM EMPLOYEES ORDER BY name DESC;
  12. # group by
  13. # 将具有相同数据的表中的这些行分组在一起 配合select一起使用
  14. SELECT column-list  
  15. FROM table_name  
  16. WHERE [conditions ]  
  17. GROUP BY column1, column2....columnN  
  18. ORDER BY column1, column2....columnN
  20. SELECT NAME, SUM(SALARY) FROM EMPLOYEES GROUP BY NAME;  # 示例 可以消除重复
  21. 消除重复冗余示例：https://www.yiibai.com/postgresql/postgresql-group-by-clause.html
  24. # having
  25. # HAVING子句与GROUP BY子句组合使用，用于选择函数结果满足某些条件的特定行
  26. SELECT column1, column2  
  27. FROM table1, table2  
  28. WHERE [ conditions ]  
  29. GROUP BY column1, column2  
  30. HAVING [ conditions ]  
  31. ORDER BY column1, column2
  33. # 示例 显示名称(name)数量小于2的记录
  34. SELECT NAME FROM EMPLOYEES GROUP BY NAME HAVING COUNT (NAME) < 2; 
  35. # 示例 查询表“EMPLOYEES”中name字段值计数大于1的名称
  36. SELECT NAME,COUNT (NAME) FROM EMPLOYEES GROUP BY NAME HAVING COUNT (NAME) > 1;
  38. # 条件
  39. # 条件用于从数据库获取更具体的结果。 它们通常与WHERE子句一起使用。 具有子句的条件就像双层过滤器。
  40. # AND
  41. SELECT column1, column2, ..... columnN    
  42. FROM table_name    
  43. WHERE [search_condition]    
  44. AND [search_condition];
  45. # 示例 所有ID小于4并且薪水大于120000的员工数据信息
  46. SELECT * FROM EMPLOYEES WHERE SALARY > 120000 AND ID <= 4;
  48. # OR
  49. # OR条件与WHERE子句一起使用，以从表中的一列或多列列中选择唯一数据
  50. SELECT column1, column2, ..... columnN    
  51. FROM table_name    
  52. WHERE [search_condition]    
  53. OR [search_condition];
  54. # 示例 查询名字是Minsu或者地址为Noida员工信息
  55. SELECT *FROM  EMPLOYEES WHERE NAME = 'Minsu' OR ADDRESS = 'Noida';
  57. # AND & OR
  58. # AND＆OR条件在仅一个查询中提供了AND和OR条件的优点
  59. SELECT column1, column2, ..... columnN    
  60. FROM table_name    
  61. WHERE [search_condition]  AND [search_condition]     
  62. OR [search_condition];
  63. # 示例 查询名字的值为Minsu和地址的值为’Delhi‘，或者ID值大于等8的记录信息
  64. SELECT *FROM EMPLOYEES WHERE (NAME = 'Minsu' AND ADDRESS = 'Delhi') OR (ID>= 8);
  66. # NOT
  67. # NOT条件与WHERE子句一起使用以否定查询中的条件
  68. SELECT column1, column2, ..... columnN    
  69. FROM table_name    
  70. WHERE [search_condition] NOT [condition];
  71. # 示例 查询那些地址不为NULL的记录信息
  72. SELECT * FROM EMPLOYEES WHERE address IS NOT NULL ;
  73. # 示例 查询那些年龄不是21和24的所有记录
  74. SELECT * FROM EMPLOYEES WHERE age NOT IN(21,24) ;
  76. # LIKE
  77. # LIKE条件与WHERE子句一起用于从指定条件满足LIKE条件的表中获取数据
  78. SELECT column1, column2, ..... columnN    
  79. FROM table_name    
  80. WHERE [search_condition] LIKE [condition];
  81. # 示例 查询名字以Ma开头的数据记录
  82. SELECT * FROM EMPLOYEES WHERE NAME LIKE 'Ma%';
  83. # 示例 查询名字以su结尾的数据记录，如下查询语句：SELECT * 
  84. FROM EMPLOYEES WHERE NAME LIKE '%su';
  85. # 示例 查询地址中含有大道的数据记录，如下查询语句：SELECT * 
  86. FROM EMPLOYEES WHERE address LIKE '%大道%';
  88. # IN
  89. # IN条件与WHERE子句一起使用，从指定条件满足IN条件的表中获取数据
  90. SELECT column1, column2, ..... columnN    
  91. FROM table_name    
  92. WHERE [search_condition] IN [condition];
  93. # 示例 查询employee表中那些年龄为19，21的员工信息
  94. SELECT * FROM EMPLOYEES WHERE AGE IN (19, 21);
  96. # NOT IN
  97. # NOT IN条件与WHERE子句一起使用，以从指定条件否定IN条件的表中获取数据。
  98. SELECT column1, column2, ..... columnN    
  99. FROM table_name    
  100. WHERE [search_condition] NOT IN [condition];
  101. # 示例 查询那些年龄不是19，25的数据
  102. SELECT * FROM EMPLOYEES WHERE AGE NOT IN (19, 25);
  103. # 示例 查询那些名字不是Minsu，Maxsu的数据信息
  104. SELECT * FROM EMPLOYEES WHERE name NOT IN ('Maxsu', 'Minsu');
  106. # BETWEEN
  107. # BETWEEN条件与WHERE子句一起使用，以从两个指定条件之间的表中获取数据。
  108. SELECT column1, column2, ..... columnN    
  109. FROM table_name    
  110. WHERE [search_condition] BETWEEN [condition];
  111. # 示例 查询employees表中年龄在24~27之间(含24，27)的数据
  112. 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

发表评论

表情：
评论列表 （有 0 条评论，411人围观）

还没有评论，来说两句吧...

相关阅读