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]$ psqlpsql (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 DATABASEpostgres=# create database rentaominrentaominrentaominrentamdsfsdfsdfsdfsdfsfsdfsdf;CREATE DATABASEpostgres=# create database rentaominrentaominrentaominrentamdsfsdfsdfsdfsdfsfsdfsdfsdfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffwewerwerwerwerewrewrwer;NOTICE:  identifier "rentaominrentaominrentaominrentamdsfsdfsdfsdfsdfsfsdfsdfsdfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffwewerwerwerwerewrewrwer" will be truncated to "rentaominrentaominrentaominrentamdsfsdfsdfsdfsdfsfsdfsdfsdfffff"CREATE DATABASEpostgres=# \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=# ^Cpostgres=#

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

test=# create table s (test(# id serial,test(# age int ,test(# date timestamp default now()test(# );CREATE TABLEtest=# \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 1test=# 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 pointtest(# );CREATE TABLEtest=# create table weather (city varchar(80) references city (cityName),temp_lo int ,temp_hi int  ,  -- hight temratoryprcp 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 1test=# select * from city; cityname | location----------+----------- 湖南     | (-192,45)(1 row)test=# insert into weather values('湖南',13,34,0.12345678,'2018-05-20');INSERT 0 1test=# 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 TABLEtest=# create table sex (state char(1)) inherits (person);CREATE TABLEtest=# insert into sex (name,age,state) values ('张三',22,1);INSERT 0 1test=# insert into sex (name,age,state) values ('李四',23,0);INSERT 0 1test=# insert into sex (name,age,state) values ('成杰',23,0);INSERT 0 1test=# insert into sex (name,age,state) values ('李文杰',23,0);INSERT 0 1test=# 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.