postgresql基本操作

连接数据库:

psql -h 127.0.0.1 -U postgres -d postgres -p 5432

创建用户:

CREATE USER sec WITH PASSWORD 'sec';

创建数据库:

方法1:使用createdb命令(类似psql),使用这个方法你必须得保证-U参数后面的用户拥有创建数据库的权限,否则会报错!

createdb -h localhost -p5432 -U postgres testdb1

方法2:使用SQL命令create database

CREATE DATABASE testdb2 WITH ENCODING='UTF8' OWNER=sec CONNECTION LIMIT=25

上面创建的数据库设置了数据库允许的最大连接数为25！

查看已有数据库:

方法1:使用l命令查看数据库，在psql命令模式下面

方法2:使用SQL语句

SELECT datname FROM pg_database WHERE datistemplate = false;

pg_database是系统内建表,里面存放了数据库的基本信息:数据库名,编码,所属者id,模版等.

创建schema:

schema是数据库里面一个非常重要的概念,它包含了:索引,视图,序列,数据类型,运算和函数.

schema可以说是组成数据库对象的逻辑集合;

CREATE SCHEMA employee;

创建schema并且指定用户:

CREATE SCHEMA university AUTHORIZATION sec;

注意:一般情况下,当用户自己不创建schema的时候,数据库对象会默认存储在public schema下面,如果是指定schema的用户必须保存改用户是超级用户或者是拥有create权限.因为创建了schema你就会创建数据库对象等并且去使用创建的这个schema。

查看已经存在的schema:

使用psql命令dn

查看当前的schema：

SELECT current_schema();

当创建的数据库与schema比较多的时候,我们可以指定模式(schema)的查询路径来提高性能:

ALTER DATABASE testdb2 SET search_path TO employee,university,public,pg_catalog;

注意：不管是否指定schema查询路径,pg_catalog模式是一定会查询的.

创建用户:

前面虽然演示了使用create user来创建用户,但是没有仔细说:

创建用户的方法有两种:

方法1：使用sql语句:create user

CREATE USER sec01 WITH PASSWORD 'r00t';

方法2:使用createuser命令,与createdb命令类似:

createuser sec03 -p5432 -hlocalhost -P -e

查看创建的用户:

方法1:使用psql命令du

方法2:使用SQL语句

SELECT usename FROM pg_user;

复杂点的语句(显示友好一点):

SELECT u.usename AS "user name",CASE WHEN u.usecreatedb AND u.usesuper THEN CAST('super user' as pg_catalog.text) when u.usecreatedb then cast('create databse' as pg_catalog.text) else cast('' as pg_catalog.text ) END AS attribute FROM pg_catalog.pg_user u;

创建用户组:

CREATE GROUP dept;

指定用户到dept组:

ALTER GROUP dept ADD USER sec,sec03;

创建时指定用户到该组:

CREATE GROUP admin WITH USER sec01;

注:一个用户可以同时属于多个用户组.

针对不同的用户组可以赋予不同的权限,用户组支持集群,可以跨服务器.

查看已经存在的用户群组:

方法1:使用dg命令

方法2:使用SQL语句

SELECT * FROM pg_group;

删除数据库

方法1:使用dropdb命令

dropdb db1 -h localhost

方法2:使用SQL

DROP DATABASE db2;

注:如果数据库正在被使用中是不能够删除的;

查看当前正在使用的数据库:

SELECT datname,usename,pid FROM pg_stat_activity;

关闭正在连接数据库的进程:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='db2';

注:如果你知道运行数据库的进程id,可以使用

select pg_terminate_backend(pid_number);

创建和删除表空间:

注:表的数据是存储在硬盘上面的,只有查询出来的数据才会存放在内存里面.

首先需要在data目录下面创建一个目录(我的:/opt/PostgreSQL/9.4/data):

bash-4.2$ mkdir -p dbs

创建表空间(用户必须为superuser):

CREATE TABLESPACE data_tbs OWNER sec01 LOCATION '/opt/PostgreSQL/9.4/data/dbs';

注:一个表空间独占一个文件夹,例如dbs里面只能创建一个表空间,还有不能使用pg_打头的名字,这个是系统保留的。

查看表空间:

SELECT * FROM pg_tablespace;

注:pg_default:是存储用户数据.

pg_global是存储系统数据

删除表空间:

DROP TABLESPACE data_tbs;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!