PostgreSQL SQL操作简单入门

1.角色

查看角色
postgres-# \du
角色列表
角色名称 | 属性 | 成员属于
----------±-------------------------------------------±---------
postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
创建角色
postgres=# CREATE ROLE test_role WITH LOGIN CREATEDB PASSWORD ‘test’;
CREATE ROLE
postgres=# \du
角色列表
角色名称 | 属性 | 成员属于
-----------±-------------------------------------------±---------
postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
test_role | 建立 DB | {}
2.修改用户密码
postgres=# alter role postgres with password ‘postgres’;
ALTER ROLE

2.数据库

查询
postgres-# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------±---------±---------±---------------------------±---------------------------±----------------------
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行记录)

创建
postgres=> CREATE DATABASE testdb OWNER test_role;
CREATE DATABASE
postgres=> \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------±----------±---------±---------------------------±---------------------------±----------------------
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | test_role | UTF8 | English_United States.1252 | English_United States.1252 |
(4 行记录)

删除

postgres=> DROP DATABASE IF EXISTS testdb;
DROP DATABASE
postgres=> \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------±---------±---------±---------------------------±---------------------------±----------------------
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行记录)

3.表

下面的练习使用training数据库登录
Server [localhost]:
Database [postgres]: training
Port [5432]:
Username [postgres]: test_role
用户 test_role 的口令：

training=> \dt
Did not find any relations.
training=> CREATE TABLE STUDENT (
training(> SNO VARCHAR(20) PRIMARY KEY,
training(> SNAME VARCHAR(20),
training(> AGE INT,
training(> SEX CHAR(2) CHECK(SEX IN(‘男’,‘女’)),
training(> CREATED_AT TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
training(> );
CREATE TABLE
training=> \dt
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------±--------±-------±----------
public | student | 数据表 | test_role
(1 行记录)
查看表结构
training=> \d student
数据表 “public.student”
栏位 | 类型 | Collation | Nullable | Default
------------±----------------------------±----------±---------±--------
sno | character varying(20) | | not null |
sname | character varying(20) | | |
age | integer | | |
sex | character(2) | | |
created_at | timestamp without time zone | | | now()
索引：
“student_pkey” PRIMARY KEY, btree (sno)
检查约束限制
“student_sex_check” CHECK (sex = ANY (ARRAY[‘男’::bpchar, ‘女’::bpchar]))
training=> INSERT INTO STUDENT VALUES(‘1’,‘李强’,23,‘男’);
INSERT 0 1
training=> SELECT * FROM student;
sno | sname | age | sex | created_at
-----±------±----±----±---------------------------
1 | 李强 | 23 | 男 | 2019-05-25 07:22:33.415539
(1 行记录)

training=> INSERT INTO STUDENT(SNO, SNAME, AGE,SEX) VALUES (‘2’,‘刘丽’,22,‘女’);
INSERT 0 1
training=> INSERT INTO STUDENT VALUES(‘5’,‘张友’,22,‘男’);
INSERT 0 1
training=> SELECT * FROM student;
sno | sname | age | sex | created_at
-----±------±----±----±---------------------------
1 | 李强 | 23 | 男 | 2019-05-25 07:22:33.415539
2 | 刘丽 | 22 | 女 | 2019-05-25 07:22:58.713986
5 | 张友 | 22 | 男 | 2019-05-25 07:23:07.742502
(3 行记录)
表中数据导出到文件
training=> \copy (SELECT * FROM student) TO ‘d:/student.csv’ WITH CSV HEADER;
COPY 3
d:/student.csv文件的内容如下：
sno,sname,age,sex,created_at
1,李强,23,男 ,2019-05-25 07:22:33.415539
2,刘丽,22,女 ,2019-05-25 07:22:58.713986
5,张友,22,男 ,2019-05-25 07:23:07.742502
表增加列
training=> ALTER TABLE student ADD COLUMN C1 INT;
ALTER TABLE
training=> \d student;
数据表 “public.student”
栏位 | 类型 | Collation | Nullable | Default
------------±----------------------------±----------±---------±--------
sno | character varying(20) | | not null |
sname | character varying(20) | | |
age | integer | | |
sex | character(2) | | |
created_at | timestamp without time zone | | | now()
c1 | integer | | |
索引：
“student_pkey” PRIMARY KEY, btree (sno)
检查约束限制
“student_sex_check” CHECK (sex = ANY (ARRAY[‘男’::bpchar, ‘女’::bpchar]))
表删除列
training=> ALTER TABLE STUDENT DROP COLUMN C1;
training=> \d student;
数据表 “public.student”
栏位 | 类型 | Collation | Nullable | Default
------------±----------------------------±----------±---------±--------
sno | character varying(20) | | not null |
sname | character varying(20) | | |
age | integer | | |
sex | character(2) | | |
created_at | timestamp without time zone | | | now()
索引：
“student_pkey” PRIMARY KEY, btree (sno)
检查约束限制
“student_sex_check” CHECK (sex = ANY (ARRAY[‘男’::bpchar, ‘女’::bpchar]))
JSON数据格式使用
CREATE TABLE test (
j JSON,
ja JSON[]
);

INSERT INTO test(ja) VALUES (
array[
‘{“name”:“alex”, “age”:20}’::json,
‘{“name”:“peter”, “age”:24}’::json
]
);
INSERT INTO test(j,ja) VALUES (
‘{“key”:“worker”, “company”:“pc”}’::json,
array[
‘{“name”:“alex”, “age”:20}’::json,
‘{“name”:“peter”, “age”:24}’::json
]
);

training=> delete from test where j is null;

training=> select j->>'key' as j_key from test;
 j_key
--------
 worker
(1 行记录)

从JSON提取数据

training=> SELECT '{"arr":[2,4,6,8]}'::json -> 'arr' -> 2 as new;
 new
-----
 6
(1 行记录)


training=> SELECT '{"arr":[2,4,6,8]}'::json #> ARRAY['arr','2'] as newcolumn;
 newcolumn
-----------
 6
(1 行记录)

4.FAQ

1.当命令输错了的时候，如何回到正常输入场景？
postgres-> \r
查询缓存区重置(已清空)。
postgres=>