postgresql简单使用

在pg里面有schema的概念。这里我们可以简单理解下：database（仓库）–> schema（房间）–>对象（表、视图、触发器）

安装

docker exec -it greenplum psql -h 127.0.0.1 -p 5432：5432 -U postgres

• 拉取镜像并运行：docker run -it --name greenplum -p 5432:5432 -d kevinmtrowbridge/greenplumdb_singlenode
• 进入到容器内：docker exec -it greenplum /bin/bash
• su - gpadmin
• 登陆：psql -h localhost -p 5432 -U gpadmin template1
• 切换到 postgres库：\c postgres
• 创建用户：CREATE USER astilt PASSWORD 'astilt';
• 授权：GRANT ALL PRIVILEGES ON DATABASE postgres TO astilt;
• 客户端navicat登陆吧，username：astilt，pwd：astilt

配置允许远程客户端连接

• 使用find [pg的安装目录] -name pg_hba.conf
• 最后一行加：host all all 0.0.0.0/0 md5
• 切换到gpadmin，执行gpstop -u 重新加载配置文件

mysql对比

操作

操作	MySQL	GP
切换数据库	USE {dbname}	\c {dbname}
查看当前库/用户	无	\c
查看所有数据库	SHOW DATABASE	\l
切换Schema	无	SET search_path={schemaname}
查看Schema	无	\dn
查看所用用户	select * from mysql.user	\du
查看所有表	SHOW TABLES	\d
查看表结构	DESC {tablename}	\d {tablename}
按列展示结果	\G（跟在SQL语句后）	\x（单独运行，再次运行切回按行）
查看运行时间	默认展示	\timing
查看客户端连接	SHOW PROCESSLIST	SELECT * FROM pg_stat_activity
客户端工具	mysql -h{hostname} -P{port} -u{user} -p{pwd} {dbname}	PGPASSWORD={pwd} psql -h{hostname} -p{port} -U{user} -d {dbname}

基本语法

自增长

• Mysql

CREATE TABLE `mysql_test` (
  `id` bigint  NOT NULL AUTO_INCREMENT, 
  `name` varchar(32) NOT NULL,
  PRIMARY KEY(`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8

• pg

  使用的 Sequence

// 1
CREATE TABLE gp_test (
  id SERIAL NOT NULL, 
  name varchar(32) NOT NULL ,
  PRIMARY KEY(id)
  ) 
  // 2
 CREATE SEQUENCE users_id_seq INCREMENT BY 2 MINVALUE  10000 MAXVALUE 99999;
 CREATE TABLE gp_test (
  id bigint NOT NULL DEFAULT NEXTVAL ('users_id_seq'),
  name varchar(32) NOT NULL ,
  PRIMARY KEY(id)
  )   

字段类型

MySQL	GP
TINYINT	SMALLINT
MEDIUMINT	INTEGER
TINYINT UNSIGNED	SMALLINT check({columnname} >= 0)
FLOAT	REAL
DATETIME	TIMESTAMP
LONGTEXT	TEXT
MEDIUMTEXT	TEXT
BLOB	BYTEA

PG 常用创建

• 用户/角色

CREATE ROLE custom PASSWORD 'custom'; (创建后不能登陆)
CREATE USER custom PASSWORD 'custom';（创建后能登陆）
ALTER ROLE custom LOGIN;（新增login权限，可以登陆了）
GRANT ALL PRIVILEGES ON DATABASE hf TO custom;（将hf数据库的所有权限都赋予custom用户）
// 登陆
psql -U custom -d hf -h 192.168.90.112 -p 5432
// 修改密码
\password [USERNAME]

• schema

// 创建一个新模式ord，对应于登录用户custom：
CREATE SCHEMA ord; 
ALTER SCHEMA ord OWNER TO custom;
// 再次创建一张test表，这次这张表要指明模式ord
CREATE TABLE ord.test (id integer not null);

• database

// 数据库所有者是当前创建数据库的角色，默认的表空间是系统的默认表空间pg_default。在`PostgreSQL`中，数据的创建是通过**克隆**数据库模板来实现的，这与SQL SERVER是同样的机制。由于`CREATE DATABASE dbname`并没有指明数据库模板，所以系统将默认克隆`template1`数据库，得到新的数据库`dbname`，template1`数据库的默认表空间是`pg_default
CREATE DATABASE hf;
drop databasem hf;

• 表空间

CREATE TABLESPACE ts OWNER custom LOCATION '/tmp/data/tsmars';
// 指定模版和表空间创建数据库
CREATE DATABASE hf TEMPLATE template1 TABLESPACE ts;
ALTER DATABASE hf OWNER TO custom;

• 表

// 新建
create table ([字段名1] [类型1] <references 关联表名(关联的字段名)>;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;); 
// 删除
drop table [表名]; 
// 重命名一个表
alter table [表名A] rename to [表名B]; 
// 在已有的表里添加字段
alter table [表名] add column [字段名] [类型]; 
//删除表中的字段
alter table [表名] drop column [字段名]; 
//重命名一个字段
alter table [表名] rename column [字段名A] to [字段名B]; 
//给一个字段设置缺省值
alter table [表名] alter column [字段名] set default [新的默认值];
//去除缺省值
alter table [表名] alter column [字段名] drop default; 
//表中插入数据
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......); 
//修改表中的某行某列的数据
update [表名] set [目标字段名]=[目标值] where [该行特征]; 
//删除表中某行数据
delete from [表名] where [该行特征]; 

日期和时间间字段

• date:日期字段，格式:2016-04-15

• timestamp:时间字段，格式:2016-04-15 20:00:00

• 获取当时时间函数

  • select now();
  • select current_timestamp;
  • select CURRENT_TIME;
  • select LOCALTIME;
  • select LOCALTIMESTAMP;

• 获取当天日期

  • select now()+interval ‘2 day’;

• 时间截取

  • select extract(year from now());

• 时间转换:

  • select timestamp ‘2012-05-12 18:54:54’;
  • select date ‘2012-05-12 18:54:54’;