天河做网站系统,wordpress recently_edited,北京seo顾问外包,jn建站系统数据库
创建数据库
PostgreSQL 创建数据库可以用以下三种方式#xff1a;
1、使用 CREATE DATABASE SQL 语句来创建。2、使用 createdb 命令来创建。3、使用 pgAdmin 工具。
CREATE DATABASE 创建数据库
CREATE DATABASE 命令需要在 PostgreSQL 命令窗口来执行#xff0…数据库
创建数据库
PostgreSQL 创建数据库可以用以下三种方式
1、使用 CREATE DATABASE SQL 语句来创建。2、使用 createdb 命令来创建。3、使用 pgAdmin 工具。
CREATE DATABASE 创建数据库
CREATE DATABASE 命令需要在 PostgreSQL 命令窗口来执行语法格式如下
postgres# \h create database;
Command: CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name[ [ WITH ] [ OWNER [] user_name ][ TEMPLATE [] template ][ ENCODING [] encoding ][ LOCALE [] locale ][ LC_COLLATE [] lc_collate ][ LC_CTYPE [] lc_ctype ][ TABLESPACE [] tablespace_name ][ ALLOW_CONNECTIONS [] allowconn ][ CONNECTION LIMIT [] connlimit ][ IS_TEMPLATE [] istemplate ] ]URL: https://www.postgresql.org/docs/13/sql-createdatabase.html例如我们创建一个 lhrpgdb 的数据库
postgres# CREATE DATABASE lhrpgdb;CREATE DATABASE db1WITHOWNER lhrENCODING UTF8TABLESPACE ts_test1CONNECTION LIMIT -1;
createdb 命令创建数据库
createdb 是一个 SQL 命令 CREATE DATABASE 的封装。
createdb 命令语法格式如下
createdb [option...] [dbname [description]]参数说明
dbname要创建的数据库名。
description关于新创建的数据库相关的说明。
options参数可选项可以是以下值
序号选项 描述1-D tablespace指定数据库默认表空间。2-e将 createdb 生成的命令发送到服务端。3-E encoding指定数据库的编码。4-l locale指定数据库的语言环境。5-T template指定创建此数据库的模板。6–help显示 createdb 命令的帮助信息。7-h host指定服务器的主机名。8-p port指定服务器监听的端口或者 socket 文件。9-U username连接数据库的用户名。10-w忽略输入密码。11-W连接时强制要求输入密码。
接下来我们打开一个命令窗口进入到 PostgreSQL 的安装目录并进入到 bin 目录createdb 命令位于 PostgreSQL安装目录/bin 下执行创建数据库的命令
$ cd /Library/PostgreSQL/11/bin/
$ createdb -h localhost -p 5432 -U postgres lhrpgdb
password ******以上命令我们使用了超级用户 postgres 登录到主机地址为 localhost端口号为 5432 的 PostgreSQL 数据库中并创建 lhrpgdb 数据库。
pgAdmin 工具创建数据库
pgAdmin 工具提供了完整操作数据库的功能 选择数据库
上一章节我们讲了如何创建数据库接下来我们来讨论如何去选择我们创建的数据库。
数据库的命令窗口
PostgreSQL 命令窗口中我们可以命令提示符后面输入 SQL 语句
postgres#使用 \l 用于查看已经存在的数据库
postgres# \lList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges
----------------------------------------------------------------------postgres | postgres | UTF8 | C | C | lhrpgdb | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | c/postgres | | | | | postgresCTc/postgrestemplate1 | postgres | UTF8 | C | C | c/postgres | | | | | postgresCTc/postgres
(4 rows)接下来我们可以使用 \c 数据库名 来进入数据库
postgres# \c lhrpgdb
You are now connected to database lhrpgdb as user postgres.
lhrpgdb#
lhrpgdb# select current_user,current_database(),pg_backend_pid();current_user | current_database | pg_backend_pid
------------------------------------------------postgres | lhrpgdb | 2715
(1 row)系统命令行窗口
在系统的命令行查看我么可以在连接数据库后面添加数据库名来选择数据库
$ psql -h localhost -p 5432 -U postgress lhrpgdb
Password for user postgress: ****
psql (11.3)
Type help for help.
You are now connected to database lhrpgdb as user postgres.
lhrpgdb# pgAdmin 工具
pgAdmin 工具更简单了直接点击数据库选择就好了还可以查看一些数据库额外的信息 删除数据库
PostgreSQL 删除数据库可以用以下三种方式
1、使用 DROP DATABASE SQL 语句来删除。2、使用 dropdb 命令来删除。3、使用 pgAdmin 工具。
**注意**删除数据库要谨慎操作一旦删除所有信息都会消失。
DROP DATABASE 删除数据库
DROP DATABASE 会删除数据库的系统目录项并且删除包含数据的文件目录。
DROP DATABASE 只能由超级管理员或数据库拥有者执行。
DROP DATABASE 命令需要在 PostgreSQL 命令窗口来执行语法格式如下
DROP DATABASE [ IF EXISTS ] name参数说明
IF EXISTS如果数据库不存在则发出提示信息而不是错误信息。name要删除的数据库的名称。
例如我们删除一个 lhrpgdb 的数据库
postgres# DROP DATABASE lhrpgdb;dropdb 命令删除数据库
dropdb 是 DROP DATABASE 的包装器。
dropdb 用于删除 PostgreSQL 数据库。
dropdb 命令只能由超级管理员或数据库拥有者执行。
dropdb 命令语法格式如下
dropdb [connection-option...] [option...] dbname参数说明
dbname要删除的数据库名。
options参数可选项可以是以下值
序号选项 描述1-e显示 dropdb 生成的命令并发送到数据库服务器。2-i在做删除的工作之前发出一个验证提示。3-V打印 dropdb 版本并退出。4–if-exists如果数据库不存在则发出提示信息而不是错误信息。5–help显示有关 dropdb 命令的帮助信息。6-h host指定运行服务器的主机名。7-p port指定服务器监听的端口或者 socket 文件。8-U username连接数据库的用户名。9-w连接数据库的用户名。10-W连接时强制要求输入密码。11–maintenance-dbdbname删除数据库时指定连接的数据库默认为 postgres如果它不存在则使用 template1。
接下来我们打开一个命令窗口进入到 PostgreSQL 的安装目录并进入到 bin 目录dropdb 名位于 PostgreSQL安装目录/bin 下执行删除数据库的命令
$ cd /Library/PostgreSQL/11/bin/
$ dropdb -h localhost -p 5432 -U postgres lhrpgdb
password ******以上命令我们使用了超级用户 postgres 登录到主机地址为 localhost端口号为 5432 的 PostgreSQL 数据库中并删除 lhrpgdb 数据库。
pgAdmin 工具删除据库
pgAdmin 工具提供了完整操作数据库的功能 查询 -- \lSELECT d.datname as Name,pg_catalog.pg_get_userbyid(d.datdba) as Owner,pg_catalog.pg_encoding_to_char(d.encoding) as Encoding,d.datcollate as Collate,d.datctype as Ctype,pg_catalog.array_to_string(d.datacl, E\n) AS Access privileges,CASE WHEN pg_catalog.has_database_privilege(d.datname, CONNECT)THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))ELSE No AccessEND as Size,t.spcname as Tablespace,pg_catalog.shobj_description(d.oid, pg_database) as Description
FROM pg_catalog.pg_database dJOIN pg_catalog.pg_tablespace t on d.dattablespace t.oid
ORDER BY 1;-- 查看各数据库数据创建时间
SELECTdatname,(pg_stat_file (format ( %s/%s/PG_VERSION, CASE WHEN spcname pg_default THEN base ELSEpg_tblspc/ || t2.oid || /PG_11_201804061/ END, t1.oid ))).*
FROMpg_database t1,pg_tablespace t2
WHEREt1.dattablespace t2.oid;
表空间
用户必须有表空间所在目录访问权限所以在创建表空间之前需要在对应分区下创建相应的目录并为其分配权限。
PostgreSQL中的表空间允许在文件系统中定义用来存放表示数据库对象的文件的位置。在PostgreSQL中表空间实际上就是给表指定一个存储目录能合理利用磁盘性能和空间,制定最优的物理存储方式来管理数据库表和索引。
在DB2和Oracle数据库中一个表空间只属于一个数据库使用而一个数据库可以拥有多个表空间。属于一对多的关系。 在PostgreSQL集群中一个表空间可以让多个数据库使用而一个数据库可以使用多个表空间。属于多对多的关系。用户下面拥有表拥有模式。模式下面拥有表空间。
initdb()后马上创建pg_default和pg_global表空间建表时如果没有指定特定的表空间表默认被存在pg_default表空间中。用于管理整个数据库集群的表默认被存储在pg_global表空间中。pg_default表空间的物理位置为$PGDATA\base目录。pg_global表空间的物理位置为$PGDATA\global目录。一个表空间可以被多个数据库同时使用。此时每一个数据库都会在表空间路径下创建为一个新的子路径。创建一个用户表空间会在$PGDATA\pg_tblspc目录下面创建一个软连接连接到表空间制定的目录位置。
目录结构可以用大致下面图示意 创建表空间
mkdir -p /postgresql/pgdata/ts_test1
mkdir -p /postgresql/pgdata/ts_test2psql
\h create tablespace
create tablespace ts_test1 location /postgresql/pgdata/ts_test1;
create tablespace ts_test2 location /postgresql/pgdata/ts_test2;
\db
create table lhrtest (id int) tablespace ts_test1;
\d lhrtest
alter table lhrtest set tablespace ts_test2;select pg_relation_filepath(lhrtest);执行过程
[pgsqllhrpg pgdata]$ mkdir -p /postgresql/pgdata/ts_test1
[pgsqllhrpg pgdata]$ mkdir -p /postgresql/pgdata/ts_test2
[pgsqllhrpg pgdata]$ psql
psql (13.2)
Type help for help.postgres# \h create tablespace;
Command: CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name[ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]LOCATION directory[ WITH ( tablespace_option value [, ... ] ) ]URL: https://www.postgresql.org/docs/13/sql-createtablespace.htmlpostgres# \dbList of tablespacesName | Owner | Location | Access privileges | Options | Size | Description
---------------------------------------------------------------------------------pg_default | postgres | | | | 295 MB | pg_global | postgres | | | | 559 kB |
(2 rows)postgres#
postgres# create tablespace ts_test1 location /postgresql/pgdata/ts_test1;
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
postgres# create tablespace ts_test2 location /postgresql/pgdata/ts_test2;
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
postgres# \dbList of tablespacesName | Owner | Location
---------------------------------------------------pg_default | postgres | pg_global | postgres | ts_test1 | postgres | /postgresql/pgdata/ts_test1ts_test2 | postgres | /postgresql/pgdata/ts_test2
(4 rows)postgres#
postgres# create table lhrtest (id int) tablespace ts_test1;
CREATE TABLE
postgres# \d lhrtestTable public.lhrtestColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------------------------------------------------------------------id | integer | | | | plain | |
Tablespace: ts_test1
Access method: heappostgres#
postgres# alter table lhrtest set tablespace ts_test2;
ALTER TABLE
postgres# \d lhrtestTable public.lhrtestColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------------------------------------------------------------------id | integer | | | | plain | |
Tablespace: ts_test2
Access method: heappostgres# \dList of relationsSchema | Name | Type | Owner
----------------------------------public | lhrtest | table | postgrespublic | sbtest | table | lhr2
(2 rows)[pgsqllhrpg pgdata]$ ll pg_tblspc/
total 0
lrwxrwxrwx 1 pgsql pgsql 27 May 28 09:13 16534 - /postgresql/pgdata/ts_test1
lrwxrwxrwx 1 pgsql pgsql 27 May 28 09:21 16535 - /postgresql/pgdata/ts_test2
删除表空间
查询
SELECT oid,spcname AS Name,pg_catalog.pg_get_userbyid(spcowner) AS Owner,pg_catalog.pg_tablespace_location(oid) AS Location,pg_catalog.array_to_string(spcacl, E\n) AS Access privileges,spcoptions AS Options,pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS Size,pg_catalog.shobj_description(oid, pg_tablespace) AS Description
FROM pg_catalog.pg_tablespace
ORDER BY 1;\db用户和角色
用户和角色在PostgreSQL中是一个概念。 但是 CREATE ROLE创建的用户默认不带LOGIN属性而CREATE USER创建的用户默认带有LOGIN属性。如果给role授权login则等同user。
创建用户 create user lhr with password lhr;
GRANT ALL PRIVILEGES ON DATABASE postgres to lhr;CREATE USER lhr2 WITH
LOGIN SUPERUSER CREATEDB CREATEROLE
INHERIT REPLICATION
CONNECTION LIMIT -1
PASSWORD lhr;CREATE ROLE username WITH LOGIN password l;
ALTER ROLE username WITH NOLOGIN;
ALTER ROLE username WITH login;查:
postgres# \duList of rolesRole name | Attributes | Member of | Description
-----------------------------------------------------------------------------------------------lhr | | {} |lhr2 | Superuser, Create role, Create DB, Replication | {} |postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |postgres# select * from pg_user;usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
-------------------------------------------------------------------------------------------------postgres | 10 | t | t | t | t | ******** | |lhr | 16540 | f | f | f | f | ******** | |lhr2 | 16541 | t | t | t | f | ******** | |
(3 rows)删除用户
-- ERROR: role lhr cannot be dropped because some objects depend on it
drop owned by lhr cascade;-- 若有数据库那么还需要删掉数据库
drop user lhr;执行
sbtest# drop user lhr ;
ERROR: role lhr cannot be dropped because some objects depend on it
DETAIL: privileges for database postgres
sbtest# drop owned by lhr cascade;
DROP OWNED
sbtest# drop user lhr ;
DROP ROLE查询
\du
select * from pg_user;模式(schema)
模式(schema):我们在pg数据库中创建的任何对象(表,索引,视图等)都会在一个模式下被创建。
当创建对象时,如果未指定模式,这些对象将会在默认的模式下被创建.这个模式叫做public。public模式代表所有人的意思。 一个例外情况是另一个模式首先出现在search_path中。
PostgreSQL 模式SCHEMA可以看着是一个表的集合。
一个模式可以包含视图、索引、数据类型、函数和操作符等。
相同的对象名称可以被用于不同的模式中而不会出现冲突例如 schema1 和 myschema 都可以包含名为 mytable 的表。
使用模式的优势
允许多个用户使用一个数据库并且不会互相干扰。将数据库对象组织成逻辑组以便更容易管理。第三方应用的对象可以放在独立的模式中这样它们就不会与其他对象的名称发生冲突。
模式类似于操作系统层的目录但是模式不能嵌套。
创建SCHEMA
我们可以使用 CREATE SCHEMA 语句来创建模式语法格式如下
postgres# \h create schema;
Command: CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specificationwhere role_specification can be:user_name| CURRENT_USER| SESSION_USERURL: https://www.postgresql.org/docs/13/sql-createschema.html接下来我们连接到 lhrpgdb 来创建模式 myschema
lhrpgdb# create schema myschema;
CREATE SCHEMAlhrpgdb# \dnList of schemasName | Owner
--------------------lhr | postgresmyschema | postgrespublic | postgres
(3 rows)lhrpgdb# \dnSList of schemasName | Owner
------------------------------information_schema | postgreslhr | postgresmyschema | postgrespg_catalog | postgrespg_temp_1 | postgrespg_toast | postgrespg_toast_temp_1 | postgrespublic | postgres
(8 rows)lhrpgdb# \dnSList of schemasName | Owner | Access privileges | Description
--------------------------------------------------------------------------------------information_schema | postgres | postgresUC/postgres|| | U/postgres |lhr | postgres | |myschema | postgres | |pg_catalog | postgres | postgresUC/postgres| system catalog schema| | U/postgres |pg_temp_1 | postgres | |pg_toast | postgres | | reserved schema for TOAST tablespg_toast_temp_1 | postgres | |public | postgres | postgresUC/postgres| standard public schema| | UC/postgres |
(8 rows)输出结果 “CREATE SCHEMA” 就代表模式创建成功。
接下来我们再创建一个表格
lhrpgdb# create table myschema.company(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY (ID)
);以上命令创建了一个空的表格我们使用以下 SQL 来查看表格是否创建
lhrpgdb# select * from myschema.company;id | name | age | address | salary
--------------------------------
(0 rows)删除模式
删除一个为空的模式其中的所有对象已经被删除
DROP SCHEMA myschema;删除一个模式以及其中包含的所有对象
DROP SCHEMA myschema CASCADE;查询
select * from pg_catalog.pg_namespace;SELECT n.nspname AS Name,pg_catalog.pg_get_userbyid(n.nspowner) AS Owner,pg_catalog.array_to_string(n.nspacl, E\n) AS Access privileges,pg_catalog.obj_description(n.oid, pg_namespace) AS Description
FROM pg_catalog.pg_namespace n
ORDER BY 1;
表格我们使用以下 SQL 来查看表格是否创建
lhrpgdb# select * from myschema.company;id | name | age | address | salary
--------------------------------
(0 rows)删除模式
删除一个为空的模式其中的所有对象已经被删除
DROP SCHEMA myschema;删除一个模式以及其中包含的所有对象
DROP SCHEMA myschema CASCADE;查询
select * from pg_catalog.pg_namespace;SELECT n.nspname AS Name,pg_catalog.pg_get_userbyid(n.nspowner) AS Owner,pg_catalog.array_to_string(n.nspacl, E\n) AS Access privileges,pg_catalog.obj_description(n.oid, pg_namespace) AS Description
FROM pg_catalog.pg_namespace n
ORDER BY 1;
