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
2.登录
刚安装的数据库还没有登录密码,可以切换postgres账号直接登录
# 切换postgres
yantao@ubuntu20:~$ sudo -i -u postgres
# 执行
postgres@ubuntu20:~$ psql
psql (16.0 (Ubuntu 16.0-1.pgdg20.04+1))
输入 "help" 来获取帮助信息.
postgres=#
3.账号
1.修改密码
postgres=# ALTER USER postgres WITH PASSWORD 'MyNewPass4!';
ALTER ROLE
postgres=#
# 退出程序
postgres=# \q
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=#
第二步的方式还是可以使用的。
3.创建新的账号
# 如果创建普通账号就不需要写 SUPERUSER
postgres=# CREATE USER admin WITH PASSWORD '123456' SUPERUSER;
CREATE ROLE
4.删除账号
DROP USER admin;
3.库
1.创建库
CREATE DATABASE mydatabase;
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 行记录)
3.选择库
postgres-# \c mydatabase;
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: 关闭)
您现在已经连接到数据库 "mydatabase",用户 "admin".
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
5.表
1.创建表
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
2.列表
mydatabase=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+----------------+--------+--------
public | mytable | 数据表 | admin
public | mytable_id_seq | 序列数 | admin
(2 行记录)
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)
4.删除表
mydatabase=# DROP TABLE mytable;
DROP TABLE
mydatabase=# \d
没有找到任何关系.
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
2.删
mydatabase=# DELETE FROM mytable WHERE ID = 1;
DELETE 1
mydatabase=# DELETE FROM mytable WHERE ID = 1;
DELETE 0
3.改
mydatabase=# UPDATE mytable SET name = 'Bob', email = 'Bob@qq.com' WHERE id = 7;
UPDATE 1
4.查
mydatabase=# SELECT * FROM mytable;
id | name | email
----+--------+---------------
1 | yantao | yantao@qq.com
2 | yantao | yantao@qq.com
3 | yantao | yantao@qq.com
(3 行记录)
7.jsonb
1.建表
CREATE TABLE student (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
data jsonb
);
2.插入数据
mydatabase=# INSERT INTO student (name, data) VALUES ('John Doe', '{"home": "北京", "age": "18"}');
INSERT 0 1
3.查询
mydatabase=# SELECT * FROM student;
id | name | data
----+----------+-------------------------------
1 | John Doe | {"age": "18", "home": "北京"}
(1 行记录)