ubuntu安装pgsql

ubuntu安装postgresSQL

官网地址： https://www.postgresql.org/download/

1.安装

# 添加源
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# 安装数字签名
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 更新源
sudo apt-get update
# 安装
sudo apt-get -y install postgresql
1
2
3
4
5
6
7
8

2.登录

刚安装的数据库还没有登录密码，可以切换postgres账号直接登录

# 切换postgres
yantao@ubuntu20:~$ sudo -i -u postgres
# 执行
postgres@ubuntu20:~$ psql
psql (16.0 (Ubuntu 16.0-1.pgdg20.04+1))
输入 "help" 来获取帮助信息.

postgres=# 
1
2
3
4
5
6
7
8

3.账号

1.修改密码

postgres=# ALTER USER postgres WITH PASSWORD 'MyNewPass4!';
ALTER ROLE
postgres=# 
# 退出程序
postgres=# \q
1
2
3
4
5

2.使用密码登录

yantao@ubuntu20:~$ psql -U postgres -hlocalhost -p5432 -dpostgres
用户 postgres 的口令：
psql (16.0 (Ubuntu 16.0-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: 关闭)
输入 "help" 来获取帮助信息.

postgres=# 
1
2
3
4
5
6
7

第二步的方式还是可以使用的。

3.创建新的账号

# 如果创建普通账号就不需要写 SUPERUSER
postgres=# CREATE USER admin WITH PASSWORD '123456' SUPERUSER;
CREATE ROLE
1
2
3

4.删除账号

DROP USER admin;
1

3.库

1.创建库

CREATE DATABASE mydatabase;
1

2.查看数据库

postgres-# \l
                                                           数据库列表
    名称    |  拥有者  | 字元编码 | Locale Provider |  校对规则   |    Ctype    | ICU Locale | ICU Rules |       存取权限        
------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 mydatabase | admin    | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | 
 postgres   | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | 
 template0  | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | =c/postgres          +
            |          |          |                 |             |             |            |           | postgres=CTc/postgres
 template1  | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | =c/postgres          +
            |          |          |                 |             |             |            |           | postgres=CTc/postgres
(4 行记录)


1
2
3
4
5
6
7
8
9
10
11
12
13

3.选择库

postgres-# \c mydatabase;
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: 关闭)
您现在已经连接到数据库 "mydatabase",用户 "admin".

1
2
3
4

4.删除库

mydatabase=# DROP DATABASE mydatabase;
错误:  无法删除当前使用的数据库
mydatabase=# \c postgres;
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: 关闭)
您现在已经连接到数据库 "postgres",用户 "admin".
postgres=# DROP DATABASE mydatabase;
DROP DATABASE

1
2
3
4
5
6
7
8

5.表

1.创建表

CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);
1
2
3
4
5

2.列表

mydatabase=# \d
                  关联列表
 架构模式 |      名称      |  类型  | 拥有者 
----------+----------------+--------+--------
 public   | mytable        | 数据表 | admin
 public   | mytable_id_seq | 序列数 | admin
(2 行记录)

1
2
3
4
5
6
7
8

3.查看表格信息

mydatabase=# \d mytable
                                  数据表 "public.mytable"
 栏位  |          类型          | 校对规则 |  可空的  |                预设                 
-------+------------------------+----------+----------+-------------------------------------
 id    | integer                |          | not null | nextval('mytable_id_seq'::regclass)
 name  | character varying(100) |          |          | 
 email | character varying(100) |          |          | 
索引：
    "mytable_pkey" PRIMARY KEY, btree (id)

1
2
3
4
5
6
7
8
9
10

4.删除表

mydatabase=# DROP TABLE mytable;
DROP TABLE
mydatabase=# \d
没有找到任何关系.
1
2
3
4

6.CURD

1.增

mydatabase=# INSERT INTO mytable (name,email) VALUES ('yantao','yantao@qq.com');
INSERT 0 1
mydatabase=# INSERT INTO mytable (name,email) VALUES ('yantao','yantao@qq.com');
INSERT 0 1
1
2
3
4

2.删

mydatabase=# DELETE FROM mytable WHERE ID = 1;
DELETE 1
mydatabase=# DELETE FROM mytable WHERE ID = 1;
DELETE 0
1
2
3
4

3.改

mydatabase=# UPDATE mytable SET name = 'Bob', email = 'Bob@qq.com' WHERE id = 7;
UPDATE 1
1
2

4.查

mydatabase=# SELECT * FROM mytable;
 id |  name  |     email     
----+--------+---------------
  1 | yantao | yantao@qq.com
  2 | yantao | yantao@qq.com
  3 | yantao | yantao@qq.com
(3 行记录)
1
2
3
4
5
6
7

7.jsonb

1.建表

CREATE TABLE student (
    id SERIAL PRIMARY KEY,
	name VARCHAR(100),
    data jsonb 
);
1
2
3
4
5

2.插入数据

mydatabase=# INSERT INTO student (name, data) VALUES ('John Doe', '{"home": "北京", "age": "18"}');
INSERT 0 1
1
2

3.查询

mydatabase=# SELECT * FROM student;
 id |   name   |             data              
----+----------+-------------------------------
  1 | John Doe | {"age": "18", "home": "北京"}
(1 行记录)
1
2
3
4
5