postgreSQL 常用命令 二

• 本次测试基与PostgreSQL 10.x版本

• 创建用户

[postgres@rtm2 data]$ /opt/pgsql-10/bin/createuser rentaomin
[postgres@rtm2 data]$

• 登陆psql查询创建的用户

postgres=# du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rentaomin |                                                            | {}

postgres=#

• 创建数据库

[postgres@rtm2 data]$ /opt/pgsql-10/bin/createdb rmttest;
[postgres@rtm2 data]$

• 查询创建的数据库

[postgres@rtm2 data]$ psql
psql (10.3)
Type "help" for help.

postgres=# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 rmttest   | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=#

• PostgreSQL 创建数据库名称第一个字符必须为 字母

postgres=# create database 12rt;
ERROR:  syntax error at or near "12"
LINE 1: create database 12rt;
                        ^
postgres=#

• 数据库名称不能超过 63byte ，数据名称中间包含特殊字符,对于长于63字节的会 自动删除 多出的字节保留剩下的作为数据库名称

postgres=# create database qw12dd;
CREATE DATABASE
postgres=# create database rentaominrentaominrentaominrentamdsfsdfsdfsdfsdfsfsdfsdf;
CREATE DATABASE
postgres=# create database rentaominrentaominrentaominrentamdsfsdfsdfsdfsdfsfsdfsdfsdfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffwewerwerwerwerewrewrwer;
NOTICE:  identifier "rentaominrentaominrentaominrentamdsfsdfsdfsdfsdfsfsdfsdfsdfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffwewerwerwerwerewrewrwer" will be truncated to "rentaominrentaominrentaominrentamdsfsdfsdfsdfsdfsfsdfsdfsdfffff"
CREATE DATABASE
postgres=# l
                                                             List of databases
                              Name                               |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------------------------------------------------------------+----------+----------+-------------+-------------+-----------------------
 postgres                                                        | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 qw12dd                                                          | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 rentaominrentaominrentaominrentamdsfsdfsdfsdfsdfsfsdfsdf        | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 rentaominrentaominrentaominrentamdsfsdfsdfsdfsdfsfsdfsdfsdfffff | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 template0                                                       | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
                                                                 |          |          |             |             | postgres=CTc/postgres
 template1                                                       | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
                                                                 |          |          |             |             | postgres=CTc/postgres
(6 rows)

postgres=#

• 查看系统版本

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# ^C
postgres=#

• psql命令行 CREATE TABLE,跨行时直接按 ENTER 键或者 表示换行

test=# create table s (
test(# id serial,
test(# age int ,
test(# date timestamp default now()
test(# );
CREATE TABLE
test=# d+ s
                                                          Table "public.s"
 Column |            Type             | Collation | Nullable |            Default            | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+-------------------------------+---------+--------------+-------------
 id     | integer                     |           | not null | nextval('s_id_seq'::regclass) | plain   |              |
 age    | integer                     |           |          |                               | plain   |              |
 date   | timestamp without time zone |           |          | now()                         | plain   |              |

test=#

• psql 中的 real 数据类型，用于存储单精度的浮点数（32位浮点数）

• psql 除支持标准的sql数据类型外，还可以自定义任意数量的数据类型，类型名称不能为关键字，除非要求支持sql标准的特殊情况。

• 数据类型为 point

test=# create table s (
test(# id serial,
test(# age int ,
test(# date timestamp default now()
test(# );
CREATE TABLE

• 插入数据，坐标必须用 单括号 包起来

test=# insert into cities values ('北京','(-192.0,53.0)');
INSERT 0 1
test=# select * from cities ;
 name | location
------+-----------
 北京 | (-192,53)
(1 row)

test=#

• character varying(80) 数据类型与varchar(80) 相同，postgreSQL 默认创建的表名为 小写

• 外键约束

test=# create table city (
test(# cityName varchar(80) primary key,
test(# location point
test(# );
CREATE TABLE


test=# create table weather (
city varchar(80) references city (cityName),
temp_lo int ,
temp_hi int  ,  -- hight temratory
prcp real ,
date date
);
CREATE TABLE

• 若直接向 weather 插入数据,则会报错

test=# insert into weather values('湖南',13,34,0.12345678,'2018-05-20');
ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(湖南) is not present in table "city".

• 必须先 city 表插入数据，才能向 weather 插入数据

test=# insert into city values ('湖南','(-192.0,45)');
INSERT 0 1
test=# select * from city;
 cityname | location
----------+-----------
 湖南     | (-192,45)
(1 row)

test=# insert into weather values('湖南',13,34,0.12345678,'2018-05-20');
INSERT 0 1
test=# select * from weather ;
 city | temp_lo | temp_hi |   prcp   |    date
------+---------+---------+----------+------------
 湖南 |      13 |      34 | 0.123457 | 2018-05-20
(1 row)

test=#

• 注意 ，此处在插入的数据类型为real的数据实际为0.12345678，但是在表中实际的数据为0.1234567,是由于 real为单精度浮点数类型（32位浮点数）

• 显示声明事务块

BEGIN; UPDATE accounts SET balance = balance - 100.00    WHERE name = 'Alice'; -- etc etc COMMIT;

• 同时删除多张表

test=# d+
                          List of relations
 Schema |   Name    |   Type   |  Owner   |    Size    | Description
--------+-----------+----------+----------+------------+-------------
 public | cities    | table    | postgres | 8192 bytes |
 public | city      | table    | postgres | 8192 bytes |
 public | p         | table    | postgres | 16 kB      |
 public | p_id_seq  | sequence | postgres | 8192 bytes |
 public | s         | table    | postgres | 0 bytes    |
 public | s_id_seq  | sequence | postgres | 8192 bytes |
 public | t         | table    | postgres | 8192 bytes |
 public | t2        | table    | postgres | 0 bytes    |
 public | t2_id_seq | sequence | postgres | 8192 bytes |
 public | t_id_seq  | sequence | postgres | 8192 bytes |
 public | userinfo  | view     | postgres | 0 bytes    |
 public | weather   | table    | postgres | 8192 bytes |
(12 rows)

test=# drop table cities, t2, t;
DROP TABLE

• 创建继承表，PostgreSQL 中表可以 继承多张表

test=# create table person (id serial ,name varchar(80),age int );
CREATE TABLE
test=# create table sex (state char(1)) inherits (person);
CREATE TABLE


test=# insert into sex (name,age,state) values ('张三',22,1);
INSERT 0 1
test=# insert into sex (name,age,state) values ('李四',23,0);
INSERT 0 1


test=# insert into sex (name,age,state) values ('成杰',23,0);
INSERT 0 1
test=# insert into sex (name,age,state) values ('李文杰',23,0);
INSERT 0 1
test=# insert into sex (name,age,state) values ('王麻子',25,1);
INSERT 0 1

• 查询表,其中 Only支持SELECT,UPDATE,DELETE;

test=# select * from sex ;
 id |  name  | age | state
----+--------+-----+-------
  5 | 成杰   |  23 | 0
  6 | 李文杰 |  23 | 0
  7 | 王麻子 |  25 | 1
(3 rows)

test=# select * from person;
 id |  name  | age
----+--------+-----
  1 | 张三   |  22
  2 | 李四   |  23
  5 | 成杰   |  23
  6 | 李文杰 |  23
  7 | 王麻子 |  25
(5 rows)

test=# select * from only person;
 id | name | age
----+------+-----
  1 | 张三 |  22
  2 | 李四 |  23
(2 rows)

test=#

• zlib 包用于支持 pg_dump and pg_restore.