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.
  •