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.
本文来自博客园，作者：热爱技术的小牛，转载请注明原文链接：https://www.cnblogs.com/my-blogs-for-everone/p/10226569.html
