PostgreSQL学习记录1-4章(上)

golang  

PostgreSQL学习记录上1-4章

安装及相关的工具使用

  • 安装
    1. 我选择使用的docker安装的.利用docker-compose 编排一键启动服务端和web端(工具)
  • 官网镜像地址和编排代码
    1. hubdocker官网镜像地址: https://hub.docker.com/_/postgres
    2. # Use postgres/example user/password credentials
    3. version: '3.1'
    4. services:
    5. db:
    6. image: postgres
    7. restart: always
    8. environment:
    9. POSTGRES_USER: root # 设置用户名
    10. POSTGRES_PASSWORD: root #设置登录密码
    11. ports:
    12. - 15432:5432 # 服务默认端口是 5432 映射到宿主机的15432端口,可以使用Navicat等三方工具进行连接
    13. adminer:
    14. image: adminer
    15. restart: always
    16. ports:
    17. - 8080:8080 # 一个web操作管理软件,直接登录 localhost:8080即可访问

命令学习

  • 进入postgreSQL容器

    docker exec -it (postgresql_db_1你容器的名称) /bin/bash

  • 创建一个数据库 命令 createdb

    createdb mydb

    1. 如果出现 createdb: command not found
    2. 则说明没有安装好,可将容器和镜像删除再重新构建
    3. createdb: error: database creation failed: ERROR: database "mytest" already exists
    4. 说明该数据库已经创建
  • 删除数据库 命令: dropdb

    dropdf mydb

    1. dropdb: error: database removal failed: ERROR: database "mydb" does not exist
    2. 说明没有该数据库,或者已经被删除
  • 访问数据库 命令: psql

    psql mydb

    1. psql 为交互的终端命令,它可以交互输入,编辑,执行SQL命令
    2. 成功后会出现以下终端.显示版本和欢迎信息------------
    3. root@f5cb6aeb374f:/# psql mydb
    4. psql (13.0 (Debian 13.0-1.pgdg100+1))
    5. Type "help" for help.
    6. mydb=#
    • 查看版本信息
      1. SELECT version();
      2. 终端显示如下:
      3. version
      4. ------------------------------------------------------------------------------------------------------------------
      5. PostgreSQL 13.0 (Debian 13.0-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
      6. (1 row)
    • 查看当前日期
      1. SELECT current_date;
      2. 终端显示如下:
      3. current_date
      4. --------------
      5. 2020-11-03
      6. (1 row)
    • 查看SQL命令的帮助语法:

      帮助语法: \h, 退出psql: \q,从指定文件中读取命令: \i 文件名

    • 创建一个新表
      1. CREATE TABLE weather (
      2. city varchar(80),
      3. temp_lo int, -- 最低温度
      4. temp_hi int, -- 最高温度
      5. prcp real, -- 湿度
      6. date date
      7. );
      8. "--" 该符号是引入注释
    • 删除一个表
      1. DROP TABLE weather;
    • 在表中增加行, insert 语句
      1. INSERT INTO weather VALUES ('上海',18,23,0.25,'2020-10-29');
      2. 终端显示如下:
      3. INSERT 0 1
    • 查询一个表
      1. SELECT * FROM weather;
      2. *号代表搜索所有列
      3. 终端显示如下:
      4. city | temp_lo | temp_hi | prcp | date
      5. ------+---------+---------+------+------------
      6. 上海 | 18 | 23 | 0.25 | 2020-11-03
      7. (1 row)
      8. 可以指定字段和声明别名进行查询
      9. 终端显示如下:
      10. mydb=# select city as 城市,temp_lo as 低温,prcp as 湿度 from weather ;
      11. 城市 | 低温 | 湿度
      12. ------+------+------
      13. 上海 | 18 | 0.25
      14. (1 row)
      15. 同样可以计算评价温差使用任意表达式进行查询
      16. 终端显示如下:
      17. mydb=# select city as 城市,(temp_lo + temp_hi)/2 as 平均温差 from weather ;
      18. 城市 | 平均温差
      19. ------+----------
      20. 上海 | 20
      21. (1 row)
      22. 使用where子句"修饰",返回一个where子句为(真)的表达式结果,语句中可以使用(AND/ORNOT)
      23. 终端显示如下:
      24. mydb=# select * from weather where city like '%上%' and prcp > 0;
      25. city | temp_lo | temp_hi | prcp | date
      26. ------+---------+---------+------+------------
      27. 上海 | 18 | 23 | 0.25 | 2020-11-03
      28. (1 row)
      29. 使用order by 子句进行倒序排序
      30. 终端显示如下:
      31. mydb=# select * from weather order by temp_lo desc;
      32. city | temp_lo | temp_hi | prcp | date
      33. ------+---------+---------+------+------------
      34. 上海 | 18 | 23 | 0.25 | 2020-11-03
      35. 上海 | 17 | 22 | 0.25 | 2020-11-03
      36. 上海 | 16 | 21 | 0.25 | 2020-11-03
      37. 上海 | 15 | 20 | 0.25 | 2020-11-03
      38. (4 rows)
      39. 使用 DISTINCT 进行去重,此时顺序可能已经发生变化,需要使用 order by 来保证获取一致的结果.
      40. 终端显示如下:
      41. mydb=# select DISTINCT city from weather ;
      42. city
      43. ------
      44. 上海
      45. (1 row)
    • 在表之间连接
      1. 同时查两个表
      2. 终端显示如下:
      3. mydb=# select * from weather,cities where city = name;
      4. city | temp_lo | temp_hi | prcp | date | name | location
      5. ------+---------+---------+------+------------+------+-----------
      6. 上海 | 18 | 23 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      7. 上海 | 17 | 22 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      8. 上海 | 16 | 21 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      9. 上海 | 15 | 20 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      10. (4 rows)
      11. 使用 inner join 查询多表关联
      12. 终端显示如下:
      13. mydb=# select * from weather w inner join cities c on (w.city = c.name);
      14. city | temp_lo | temp_hi | prcp | date | name | location
      15. ------+---------+---------+------+------------+------+-----------
      16. 上海 | 18 | 23 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      17. 上海 | 17 | 22 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      18. 上海 | 16 | 21 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      19. 上海 | 15 | 20 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      20. (4 rows)
      21. 使用 left join 左联查询
      22. 终端显示如下:
      23. mydb=# select * from weather w left join cities c on (w.city = c.name);
      24. city | temp_lo | temp_hi | prcp | date | name | location
      25. ------+---------+---------+------+------------+------+-----------
      26. 上海 | 18 | 23 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      27. 上海 | 17 | 22 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      28. 上海 | 16 | 21 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      29. 上海 | 15 | 20 | 0.25 | 2020-11-03 | 上海 | (-194,53)
      30. (4 rows)
    • 聚集函数
      1. count(计数),sum(求和),avg(平均值),max(最大值),min(最小值)
      2. 平均示例
      3. 终端显示如下:
      4. mydb=# select avg(temp_hi) as 平均最高温度 from weather ;
      5. 平均最高温度
      6. ---------------------
      7. 21.5000000000000000
      8. (1 row)
    • 更新
      1. 将日期为2020-11-03的最低和最高温度减2
      2. 终端显示如下:
      3. mydb=# update weather set temp_lo = temp_lo - 2,temp_hi = temp_hi - 2 where date = '2020-11-03';
      4. UPDATE 4
      5. mydb=# select * from weather ;
      6. city | temp_lo | temp_hi | prcp | date
      7. ------+---------+---------+------+------------
      8. 上海 | 16 | 21 | 0.25 | 2020-11-03
      9. 上海 | 15 | 20 | 0.25 | 2020-11-03
      10. 上海 | 14 | 19 | 0.25 | 2020-11-03
      11. 上海 | 13 | 18 | 0.25 | 2020-11-03
      12. (4 rows)
    • 删除
      1. 将最高温度低于20度的都删除掉
      2. 终端显示如下:
      3. mydb=# delete from weather where temp_hi < 20;
      4. DELETE 2
      5. mydb=# select * from weather ;
      6. city | temp_lo | temp_hi | prcp | date
      7. ------+---------+---------+------+------------
      8. 上海 | 16 | 21 | 0.25 | 2020-11-03
      9. 上海 | 15 | 20 | 0.25 | 2020-11-03
      10. (2 rows)
      11. 特别注意: 如果 delete from tablename; 没有条件限制,则系统会直接清除所有行,不会请求确认!
  • 视图

    如果两张表的信息对我都有用,而我每次又不想都用联表查询等方式,这样就可以创建一个视图,并给一个查询的名称,后续使用就可以向普通表一样使用它

    1. 终端显示如下:
    2. mydb=# create view myview as select city,temp_lo,temp_hi,prcp,date,location from weather,cities where city = name;
    3. CREATE VIEW
    4. 查询结果如下:
    5. mydb=# select * from myview;
    6. city | temp_lo | temp_hi | prcp | date | location
    7. ------+---------+---------+------+------------+-----------
    8. 上海 | 16 | 21 | 0.25 | 2020-11-03 | (-194,53)
    9. 上海 | 15 | 20 | 0.25 | 2020-11-03 | (-194,53)
    10. (2 rows)
  • 外键

    外键是为了保证在cities表中必须有数据后才可以插入weather表中数据

    1. 定义外键:
    2. create table cities(
    3. city varchar(80) primary key,
    4. location point
    5. );
    6. create table weather(
    7. city varchar(80) references cities(city),
    8. temp_lo int,
    9. temp_hi int,
    10. prcp real,
    11. date date
    12. );
    13. 该语句说明在weather表中的citycities表中的city关联,如果cities表中不存在指定的城市则不能直接插入数据到weather
    14. 在我本地,我因为有两个表,所以我尝试修改添加外键
    15. ALTER TABLE "public"."weather"
    16. ADD CONSTRAINT "city" FOREIGN KEY ("city") REFERENCES "public"."cities" ("name");
    17. 终端显示如下;
    18. mydb=# ALTER TABLE "public"."weather"
    19. ADD CONSTRAINT "city" FOREIGN KEY ("city") REFERENCES "public"."cities" ("name");
    20. ALTER TABLE
    21. 这里需要注意的是,要把cities中的name设置成主键
    22. 再次进行直接插入一个cities中不存在的城市看看是否报错
    23. 终端显示报错信息如下:
    24. mydb=# insert into weather values ('海南',45,54,0.1,'2020-11-03');
    25. ERROR: insert or update on table "weather" violates foreign key constraint "city"
    26. DETAIL: Key (city)=(海南) is not present in table "cities".
  • 事务
    事务是所有数据库系统的基本概念.事务最重要的是将多个步骤捆绑成一个单一的,要么你全部完成,要么全部不完成的操作.步骤之间的中间状态对于其他并发事务是不可见的,并且如果有某些错误发生导致事务不能完成.则其中任何一个步骤都不会对数据库造成影响
    1. 示例,将城市为上海的所有最低气温增加2度,步骤需要开启一个事务,然后在做事情,做完后统一提交事务
    2. mydb=# BEGIN ;
    3. BEGIN
    4. mydb=*# update weather set temp_lo = temp_lo +2 where city = '上海';
    5. UPDATE 2
    6. mydb=*# COMMIT;
    7. COMMIT
    8. 如果在事务的途中出现错误,或者不想提交使用命令,ROLLBACK,进行取消回滚
    9. 另外还可以在事务中,使用命令 SAVEPOINT,保存点,然后用ROLLBACK TO 保存的点.
  • 窗口函数
    1. 通俗易懂的讲,窗口函数就是对一些数据既要分组,又要排序会有更好的处理.如果使用 group by 则只会有一行数据,
    2. 而窗口函数会保留所有数据的行进行分组排序.
    3. 语法:
    4. <窗口函数> over (partition by <用于分组的列名>
    5. order by <用于排序的列名>)
    6. 专用窗口函数的有哪些?
    7. rank(), dense_rank(), row_number()
    8. rank()的使用表示:对所有数据进行分组排名,如果8人进行排名,有3人名次并列的都在第3名,则最后结果会占用相等名次的行号,然后排名,排名结果为(1,2,3,3,3,6,7,8).
    9. dense_rank()的使用表示:对所有数据进行分组排名,如果有8人进行排名,有3人名次并列的都在第3名,则最后结果不会占用相等名次的行号.排名结果为(1,2,3,3,3,4,5,6)
    10. row_number()的使用表示:对所有数据进行分组排名,如果有8人进行排名,有3人名次并列的都在第3名,则最后结果不会考虑并列的名次,会将3个第三名的依次排名结果为(1,2,3,4,5,6,7,8),其中 3,4,5他们都是第三名.
    11. 聚合窗口函数的有哪些?
    12. sum(),avg(),count(),max(),min()
    13. 聚合窗口函数使用表示: 对前面的数据的sum()是累加值,avg()是累加值的平均值,count()是前面的数量的总数统计,max()是对前面数据的最大记录,min()是对前面的数据的最小的记录
  • 继承
    1. 继承是面向对象数据库中的概念.它展示了数据库设计的新的可能性.
    2. CREATE TABLE cities(
    3. name text,
    4. population real,
    5. altitude int --(in ft)
    6. );
    7. CREATE TABLE capitals(
    8. state char(2)
    9. ) INHERITS (cities);
    10. 一个capitals的行从它的父亲cities继承了所有列(name,populationaltitude)
    11. 可以在表名前使用 ONLY 关键字,只查询某张表,不包含其他的继承关系的表.
    12. SELECT,UPDATE,和DELETE都支持这个ONLY标识

    SQL语言部分

  • 词法结构
    SQL输入由一个命令序列组成.一个命令由一个记号的序列构成,并由一个分号(“;”)终结
    一个记号可以是一个关键词/一个标识符/一个带引号的标识符/一个literal(或常量)或一个特殊字符符号.
  • 标识符和关键词
    SELECT.UPDATE或VALUES记号是关键词的例子,即SQL语言中具有特定意义的词.
    关键词和不被引号修饰的标识符是大小写不敏感的
  • 常量
    在PostgreSQL中有三种隐式类型常量: 字符串,位串和数字.常量也可以被指定显示类型,这可以使得它被更精确的展示以及有效的处理.
    • 字符串常量
      字符串常量是一个由单引号保卫的任意字符序列
    • C风格转义的字符串常量
      “转义”字符串常量,是SQL标准的一个扩展.使用反斜杠在一个特殊字符前表示该字符的字面意思
    • 带有Unicode转义的字符串常量
    • 美元引用的字符串常量
    • 位串常量
    • 数字常量
    • 其他类型的常量
  • 操作符
    1. + - * / < > = ~ ! @ # % ^ & | ` ?
    2. -- /* 不能再一个操作符名的任何地方出现,因为它们被作为一段注释的开始.
    3. 一个多字符操作符名不能以+或-结尾,除非该名称也至少包含这些字符中的一个: ~ ! @ # % ^ & | ~ ?
  • 特殊字符
    • 美元符号($)后面的数字被用来表示在一个函数定义或一个预备语句中的位置参数.
    • 圆括号(())具有他们通常的含义,用来分组表达式并且强制优先级.
    • 方括号([])被用来选择一个数组中的元素.
    • 逗号(,)被用来在某些语法结构中来分隔一个列表的元素
    • 分号(;)结束一个SQL命令
    • 冒号(:)被用来充数组中选择”切片”
    • 星号(*)被用在某些上下文中标记一个表的所有域或者组合值
    • 句点(.)被用在数字常量中,并且被用来分隔模式/表和列名.
  • 注释
    -- 是注释一行
    /* 注释多行 */
  • 操作符优先级
操作符/元素 结合性 描述
. 表/列名分隔符
:: PostgreSQL-风格的类型转换
[ ] 数组元素选择
+ - 一元加,一元减
^ 指数
* / % 乘,除,模
+ - 加,减
(任意其他操作符) 所有其他本地以及用户定义的操作符
BETWEEN IN LIKE ILIKE SIMILAR 范围包含,集合成员关系,字符串匹配
< > == <= >= <> 比较操作符
IS ISNULL NOTNULL IS TRUE,IS FALSE,IS NULL,IS DISTINCT FROM等
NOT 逻辑否定
AND 逻辑合取
OR 逻辑析取
  • 值表达式
    值表达式被用于各种各样的环境中,例如在SELECT命令的目标列中,作为insert或者UPDATE中新列值或者若干命令中的搜索条件.
    一个值表达式是下列之一:
    • 一个常量或者文字值
    • 一个列引用
    • 在一个函数定义提或预备语句中的一个位置参数引用
    • 一个下标表达式
    • 一个域选择表达式
    • 一个操作符调用
    • 一个函数调用
    • 一个聚集表达式
    • 一个窗口函数调用
    • 一个类型转换
    • 一个排序规则表达式
    • 一个标量子查询
    • 一个数组构造器
    • 一个行构造器
    • 另一个在圆括号(用来分组子表达式以及重载优先级)中的值表达式
  • 列引用
    列引用是在SQL语句中对当前表或表别名的列进行引用查询.
  • 位置参数
    一个位置参数引用被用来指示一个由SQL语句外部提供的值.
    1. 创建一个city函数,参数为 name
    2. create function city(text) returns cities as $$ select * from cities where name = $1 $$ language sql;
    3. 删除city函数
    4. drop function city(text);
    5. 使用函数,终端显示结果如下:
    6. mydb=# select city('上海');
    7. city
    8. --------------------
    9. (上海,"(-194,53)")
    10. (1 row)
  • 下标
    如果一个表达式得到了一个数组类型的值,那个可以抽取出该数组值的一个特定元素,或者取出多个元素(切片)
  • 域选择
    如果一个表达式得到一个组合类型(行类型)的值,那么可以抽取该行的指定域
  • 操作符调用
    二元中缀操作符(expression operator expression)
    一元前缀操作符(operator expression)
    一元后缀操作符(expression operator)
  • 函数调用
    一个函数调用的语法是一个函数的名称(可能受限于一个模式名)后面跟上封闭于圆括号中的参数
  • 聚集表达式
    一个聚集表达式表示在由一个查询选择的行上应用一个聚集函数.
    1. 示例: 使用 count()函数, 输入*代表总行数,输入某个指定字段得到指定字段的总行数,去重则得到去重后的总数
    2. 终端示例:
    3. mydb=# select * from weather ;
    4. city | temp_lo | temp_hi | prcp | date
    5. ------+---------+---------+------+------------
    6. 上海 | 18 | 21 | 0.25 | 2020-11-03
    7. 上海 | 17 | 20 | 0.25 | 2020-11-03
    8. (2 rows)
    9. mydb=# select count(city) from weather ;
    10. count
    11. -------
    12. 2
    13. (1 row)
    14. mydb=# select count(distinct city) from weather ;
    15. count
    16. -------
    17. 1
    18. (1 row)
  • 窗口函数调用
    一个窗口函数调用标识在一个查询选择的行的某个部分上应用一个聚集类的函数.
    1. 获取平均值,根据 prcp 分组,按照 temp_hi 正序
    2. mydb=# select avg(temp_lo) over(partition by prcp order by temp_hi) from weather;
    3. avg
    4. ---------------------
    5. 17.0000000000000000
    6. 17.5000000000000000
    7. (2 rows)
  • 类型转换
    一个类型造型值定从一种数据类型到另一种数据类型的转换.PostgreSQL接收两种等价的类型造型语法
    1. CAST(expression as type)
    2. expression::type
    3. CAST语法遵从 SQL,而用::的语法是PostgreSQLd历史用法
    4. 示例将字符串转换成int,终端显示如下:
    5. mydb=# select '123'::int;
    6. int4
    7. ------
    8. 123
    9. (1 row)
    10. mydb=# select CAST('123'as int);
    11. int4
    12. ------
    13. 123
    14. (1 row)
  • 排序规则表达式
    COLLATE子句会重载一个表达式的排序规则.它被追加到它使用的表达式:
    expr COLLATE collation
  • 标量子查询
    一个标量子查询是一种圆括号内的普通SELECT查询,它刚好返回一行一列.
  • 数组构造器
    一个数组构造器是一个能构建一个数组值并且将值用于它的成员元素的表达式.
    查询示例:终端显示如下:
    1. mydb=# select * from arr;
    2. f1 | f2
    3. ---------------+---------------
    4. {{1,2},{3,4}} | {{5,6},{7,8}}
    5. (1 row)
    6. mydb=# select f1[2:] from arr;
    7. f1
    8. ---------
    9. {{3,4}}
    10. (1 row)
  • 行构造器
    一个行构造器是能够构建一个行值(也称作一个组合类型)并用值作为其成员域的表达式.
    一个行构造器由关键词ROW.一个做圆括号.用于行的域值的零个或多个表达式(用逗号分隔)以及最后的一个右圆括号.例如: select row(1,2,5,’this is a test’)
    1. 终端显示如下:
    2. mydb=# select row(t.f1,t.f2,22) from arr t;
    3. row
    4. --------------------------------------
    5. ("{{1,2},{3,4}}","{{5,6},{7,8}}",22)
    6. (1 row)
    7. mydb=# select row(a.*,44) from weather a;
    8. row
    9. ---------------------------------
    10. (上海,18,21,0.25,2020-11-03,44)
    11. (上海,17,20,0.25,2020-11-03,44)
    12. (2 rows)
    13. mydb=# select row(a.f1[:1],a.f2[2:]) from arr a;
    14. row
    15. -----------------------
    16. ("{{1,2}}","{{7,8}}")
    17. (1 row)
  • 表达式计算规则
    子表达式的计算顺序没有被定义.特别地,一个操作符或函数的输入不必安装从左至右或其他任何固定顺序进行计算.
    此外,如果一个表达式的结果可以通过只计算其一部分来计算决定,那么其他子表达式可能完全不需要被计算.
  • 调用函数
    • 使用位置记号
    • 使用命名记号
    • 使用呼和记号


评论 0

发表评论

Top