postgresql入门笔记(详细)

最新推荐文章于 2024-06-22 16:47:00 发布

Hacker_wtm

最新推荐文章于 2024-06-22 16:47:00 发布

阅读量376

点赞数 1

分类专栏：数据库后端文章标签： postgresql psql

本文链接：https://blog.csdn.net/qq_31600613/article/details/88978654

版权

数据库同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

后端

2 篇文章 0 订阅

订阅专栏

简介：postgresql数据库与mysql在操作数据方面很多都是一样的，只是在少数个别出有不同，只要你熟练使用mysql数据库，postgresql对你来说就是小菜一碟。
1）

切换数据库

 \c dbname username serverIP port
 数据库必选，其他参数可选

查看帮助

 help
 注意：psql的命令多数是以 \ 开始的

显示所有的数据库：

 mysql: show databases
 psql: \l或\list

数据库切换

 mysql: use dbname
 psql: \c dbname

数据库下的所有表

 mysql: show tables  
 psql: \d

表所有字段

 mysql: show columns from table name
 psql: \d tablename

表描述

 mysql: describe tablename
 psql: \d+ tablename

退出

 mysql: quit 或者\q
 psql:\q

二）

数据类型：

      特殊类型：json,jsonb (后面会讲解)    键值对的键必须使用双引号
      数值数据类型：smallint，integer，bigint，decimal，numeric，real,double，serial，bigserial。
	  字符串数据类型：char(size)，character(size)，varchar(size)，character，varying(size)，text
	  日期/时间数据类型：timestamp [ (p) ] [不带时区 ]，timestamp [ (p) ]带时区，date，time [ (p) ] [ 不带时区 ]，time [ (p) ] 带时区，interval [ fields ] [ (p) ]
	  其他：boolean，money，point，line，lseg，box，path，polygon，circle

3）

数据库（表）

 创建数据库：CREATE DATABASE database_name;
 删除数据库：drop database database_name
 
 创建表：
 CREATE TABLE table_name(  
   column1 datatype,  
   column2 datatype,  
   column3 datatype,  
   .....  
   columnN datatype,  
   PRIMARY KEY( one or more columns )  
);
 删除表：drop table table_name

 创建模式：模式(也叫架构)是指定的表集合。 它还可以包含视图，索引，序列，数据类型，运算符和函数。
  CREATE SCHEMA myschema
  删除制定架构的表
  DROP TABLE myschema.tb_test

4）

查询插入更新删除数据，`(与mysql操作一致)`

插入：
   INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  
VALUES (value1, value2, value3,...valueN);

更新：
   UPDATE table_name  SET column1 = value1, column2 = value2...., columnN = valueN  WHERE [condition];

删除：
   DELETE FROM table_name  WHERE [condition];

查询：
   SELECT "column1", "column2".."column" FROM "table_name";
....

4）

用户操作

 创建数据库用户: create user user1 with password '123456';
 删除数据库用户: drop user user1

5）

json,jsonb 详解

 区别在于效率,json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。
 而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。
 使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对
 1）json存储快，使用慢； 存的时候不做处理，使用时再解析
 2）jsonb存储稍慢，存储时就做了解析，使用时速度较快
 3）两者的部分函数很相似，稍有区别

 json和jsonb共同操作符：
 
  ->,->>,#>,#>>区别不大，只是返回的数据类型不同，->,#>返回json,而->>,#>>返回文本text

  -> // 右边传入整数（针对纯数组）
  例： select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 // 输出 {"c":"baz"}

  -> // 右边传入键值（针对关联数组）
  例： select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->'a' // 输出 {"b":"foo"}

  ->> // 右边传入整数（针对纯数组）
  例： select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->>2 // 输出 {"c":"baz"}

  ->> // 右边传入键值（针对关联数组）
  例： select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->>'a' // 输出 {"b":"foo"}

  #> // 获取json子对象，传入数组，返回json
  例： select '{"a": {"b":{"c": "foo"}}}'::json#> '{a,b}' // 输出 {"c": "foo"}

  #>> // 获取json子对象并转换为文本
  例： select '{"a": {"b":{"c": "foo"}}}'::json#>> '{a,b}' // 输出 {"c": "foo"}


相关函数： 
  json_each 和 jsonb_each ， json_array_elements 和 jsonb_array_elements 。
  json_object_keys  // 返回json的键（多层只返回第一层），该函数不能用于纯数组.
  json_array_elements  // 提取转换纯数组元素
  json_extract_path   // 返回JSON值所指向的某个键元素（相当于 #> 操作符），该函数不能直接操作纯数组。
  array_to_json //把数组json转换为数组 ,参数可以直接写表名称
  row_to_json  把一行数据按Json字符串形式返回
  to_json  就是字符串，但是要加类型 'Fred said "Hi"'::text
  json_array_length 获取数组的长度
  json_each  遍历json数据
  json_each_text  遍历json数据,值类型是text
  json_populate_recordset  将json数据转化为表


jsonb额外操作符:
  ~~操作符	右操作数类型	    描述	     例子~~ 
  @>	jsonb	左边的 JSON 值是否包含顶层右边JSON路径/值项?	'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
  <@	jsonb	左边的JSON路径/值是否包含在顶层右边JSON值中？	'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
  ?	    text	字符串是否作为顶层键值存在于JSON值中？	'{"a":1, "b":2}'::jsonb ? 'b'
  ?|	text[]	这些数组字符串中的任何一个是否作为顶层键值存在？	'{"a":1, "b":2, "c":3}'::jsonb ?|array['b',c']
  ?&	text[]	这些数组字符串是否作为顶层键值存在？	'["a", "b"]'::jsonb ?& array['a', 'b']
  ||	jsonb	连接两个jsonb值到新的jsonb值	'["a", "b"]'::jsonb|| '["c", "d"]'::jsonb
  -	    text	从左操作数中删除键/值对或字符串元素。基于键值匹配键/值对。	'{"a": "b"}'::jsonb - 'a'
  -     integer	删除指定索引的数组元素（负整数结尾）。如果顶层容器不是一个数组，那么抛出错误。	'["a", "b"]'::jsonb - 1
  #-	text[]	删除指定路径的域或元素（JSON数组，负整数结尾）	'["a", {"b":1}]'::jsonb #- '{1,b}'



[官网的json,jsonb可能比较详细](https://www.postgresql.org/docs/11/functions-json.html)(需要英语水平)，如下：
 json，jsonb 都有
  ~~操作符	右操作数类型	    描述	     例子~~ 
	->	int	Get JSON array element (indexed from zero, negative integers count from the end)	'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2	{"c":"baz"}
	->	text	Get JSON object field by key	'{"a": {"b":"foo"}}'::json->'a'	{"b":"foo"}
	->>	int	Get JSON array element as text	'[1,2,3]'::json->>2	3
	->>	text	Get JSON object field as text	'{"a":1,"b":2}'::json->>'b'	2
	#>	text[]	Get JSON object at specified path	'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'	{"c": "foo"}
	#>>	text[]	Get JSON object at specified path as text	'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'	3


 jsonb:特有的
  ~~操作符	右操作数类型	    描述	     例子~~ 
   @>	jsonb	Does the left JSON value contain the right JSON path/value entries at the top level?	'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
   <@	jsonb	Are the left JSON path/value entries contained at the top level within the right JSON value?	'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
   ?	text	Does the string exist as a top-level key within the JSON value?	'{"a":1, "b":2}'::jsonb ? 'b'
   ?|	text[]	Do any of these array strings exist as top-level keys?	'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
   ?&	text[]	Do all of these array strings exist as top-level keys?	'["a", "b"]'::jsonb ?& array['a', 'b']
   ||	jsonb	Concatenate two jsonb values into a new jsonb value	'["a", "b"]'::jsonb || '["c", "d"]'::jsonb
   -	text	Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value.	'{"a": "b"}'::jsonb - 'a'
   -	text[]	Delete multiple key/value pairs or string elements from left operand. Key/value pairs are matched based on their key value.	'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
   -	integer	Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array.	'["a", "b"]'::jsonb - 1
   #-	text[]	Delete the field or element with specified path (for JSON arrays, negative integers count from the end)	'["a", {"b":1}]'::jsonb #- '{1,b}'
    ```