都什么年代了你还不学习postgreSQL(入门篇)(下)

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 都什么年代了你还不学习postgreSQL(入门篇)(下)

3.常用数据类型

创建表格时每列都必须使用数据类型。PotgreSQL中主要有三类数据类型:


数值数据类型

字符串数据类型

日期/时间数据类型

1.数值数据类型

smallint(小范围)

integer(常用的整数)

bigint(大范围整数)

decimal(用户指定精度,精确)

numeric(用户指定的精度,精确)

real(可变精度,不精确)

double(可变精度,不精确)

PostgreSQL 使用序列来标识字段的自增长,数据类型有 smallserial、serial 和 bigserial 。这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。


SMALLSERIAL、SERIAL 和 BIGSERIAL 范围:

image.png

2.字符串字符串类型包括

●char(size),character(size):固定长度字符串,size 规定了需存储的字符数,由右边的空格补齐;

●varchar(size),character varying(size):可变长度字符串,size 规定了需存储的字符数;

●text:可变长度字符串。


3.日期/时间

表示日期或时间的数据类型有:

●timestamp:日期和时间;

●date:日期,无时间;

●time:时间;

其他数据类型类型还有布尔值 boolean (true 或 false),货币数额 money 和 几何数据等。


4.数据库表操作

#创建表
CREATE TABLE COMPANY(
   ID  SERIAL PRIMARY KEY,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
#插入数据
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );
mydb=# select * from company;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  1 | Paul  |  32 | California                                         |  20000
  2 | Allen |  25 | Texas                                              |  15000
(2 行记录)

5.Schema


PostgreSQL 模式(SCHEMA)可以看着是一个表的集合。


一个模式可以包含视图、索引、数据类型、函数和操作符等。


相同的对象名称可以被用于不同的模式中而不会出现冲突,例如 schema1 和 myschema 都可以包含名为 mytable 的表。


使用模式的优势:


允许多个用户使用一个数据库并且不会互相干扰。

将数据库对象组织成逻辑组以便更容易管理。

第三方应用的对象可以放在独立的模式中,这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录,但是模式不能嵌套。

#创建schema: 
create schema myschema;
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)
);
#删除schema: 
drop schema myschema;
#删除一个模式以及其中包含的所有对象:
DROP SCHEMA myschema CASCADE;

6.如何备份postgresql数据库

如果您在生产环境中使用PostgreSQL,请务必采取预防措施以确保用户的数据不会丢失。

1.单数据库备份与还原

备份格式有几种选择:

*.bak:压缩二进制格式

*.sql:明文转储

*.tar:tar 需要使用pg_restore还原

PostgreSQL提供了pg_dump实用程序来简化备份单个数据库的过程。 必须以对要备份的数据库具有读取权限的用户身份运行此命令。

#以postgres用户身份登录:
sudo su - postgres
#通过运行以下命令将数据库的内容转储到文件中。替换dbname为要备份的数据库的名称。
pg_dump dbname > dbname.bak

生成的备份文件dbname.bak可以使用scp传输到另一台主机,也可以存储在本地以供以后使用。

要演示恢复丢失的数据,请删除示例数据库并在其位置创建一个空数据库:

#使用`psql`恢复数据库
 psql test < dbname.bak

注意:默认情况下,PostgreSQL将忽略备份过程中发生的任何错误。这可能导致备份不完整。要防止这种情况,您可以使用-1选项运行pg_dump命令。 这会将整个备份过程视为单个事务,这将在发生错误时阻止部分备份。


2.多数据库备份与还原

由于pg_dump一次只创建一个数据库的备份,因此它不会存储有关数据库角色或其他群集范围配置的信息。 要存储此信息并同时备份所有数据库,可以使用pg_dumpall

#创建备份文件:
pg_dumpall > pg_backup.bak

还原

#从备份还原所有数据库:
psql -f pg_backup.bak postgres

例子:

#备份数据库
$ pg_dump -U postgres -f /tmp/postgres.sql postgres (导出postgres数据库保存为postgres.sql)
$ pg_dump -U postgres -f /tmp/postgres.sql -t test postgres (导出postgres数据库中表test的数据)
$ pg_dump -U postgres -F t -f /tmp/postgres.tar postgres (导出postgres数据库以tar形式压缩保存为postgres.tar)
#恢复数据库
$ psql -U postgres -f /tmp/postgres.sql bk01 (恢复postgres.sql数据到bk01数据库)
#pg_restore --  从pg_dump创建的备份文件中恢复PostgreSQL数据库,用于恢复由pg_dump 转储的任何非纯文本格式中的PostgreSQL数据库。
$ pg_restore -U postgres -d bk01 /tmp/postgres.tar  (恢复postgres.tar数据到bk01数据库)

7.用户操作

#创建用户并设置密码
CREATE USER 'username' WITH PASSWORD 'password';
CREATE USER test WITH PASSWORD 'test';
#修改用户密码
$ ALTER USER 'username' WITH PASSWORD 'password';
#数据库授权,赋予指定账户指定数据库所有权限
$ GRANT ALL PRIVILEGES ON DATABASE 'dbname' TO 'username';
#将数据库 mydb 权限授权于 test
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;
#但此时用户还是没有读写权限,需要继续授权表
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xxx;
#注意,该sql语句必须在所要操作的数据库里执行
#移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb from test
#删除用户
drop user test
# 查看用户
\du

注意:

pg_hba.conf配置中的第一项设置的意思是:本地用户通过unix socket登陆时,使用peer方式认证。

# "local" is for Unix domain socket connections only
local   all             all                                     peer

peer是用PostgreSQL所在的操作系统上的用户登陆。


peer方式中,client必须和PostgreSQL在同一台机器上。只要当前系统用户和要登陆到PostgreSQL的用户名相同,就可以登陆。


在刚部署PostgreSQL之后,切换到系统的postgres用户后,直接执行psql就能进入PostgreSQL就是这个原因(当前系统用户为名postgre,PostgreSQL中的用户名也是postgre)。


8.PostgreSQL 角色管理

在PostgreSQL 里没有区分用户和角色的概念,“CREATE USER” 为 “CREATE ROLE” 的别名,这两个命令几乎是完全相同的,唯一的区别是"CREATE USER" 命令创建的用户默认带有LOGIN属性,而"CREATE ROLE" 命令创建的用户默认不带LOGIN属性


例子:创建david角色和sandy用户

postgres=# CREATE ROLE david;  //默认不带LOGIN属性
CREATE ROLE
postgres=# CREATE USER sandy;  //默认具有LOGIN属性
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 david     | Cannot login                                   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}
postgres=# 
postgres=# SELECT rolname from pg_roles ;
 rolname  
----------
 postgres
 david
 sandy
(3 rows)
postgres=# SELECT usename from pg_user;         //角色david 创建时没有分配login权限,所以没有创建用户
 usename  
----------
 postgres
 sandy
(2 rows)
postgres=#

1.角色属性image.png2.创建用户时赋予角色属性

如果要在创建角色时就赋予角色一些属性,可以使用下面的方法。

首先切换到postgres 用户。

创建角色bella 并赋予其CREATEDB 的权限。

postgres=# CREATE ROLE bella CREATEDB ;
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB, Cannot login                        | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}
postgres=#

创建角色renee 并赋予其创建数据库及带有密码登录的属性。

postgres=# CREATE ROLE renee CREATEDB PASSWORD 'abc123' LOGIN;
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB, Cannot login                        | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create DB                                      | {}
 sandy     |                                                | {}
postgres=#

测试renee 角色

postgres@CS-DEV:~> psql -U renee -d postgres
psql (9.1.0)
Type "help" for help.
postgres=>

3.给已存在用户赋予各种权限

赋予登录权限

postgres=# ALTER ROLE bella WITH LOGIN;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create DB                                      | {}
 sandy     |                                                | {}
postgres=#

赋予renee 创建角色的权限

postgres=# ALTER ROLE renee WITH CREATEROLE;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}
postgres=#

四,控制台常用命令

1.命令1

\password命令(设置密码)
\q命令(退出)
\h:查看SQL命令的解释,比如\h select。
\?:查看psql命令列表。
\l:列出所有数据库。
\c [database_name]:连接其他数据库。
\d:列出当前数据库的所有表格。
\di:查看索引
\d [table_name]:列出某一张表格的结构。
\du:列出所有用户。
\copyright     显示 PostgreSQL 的使用和发行条款
\encoding [字元编码名称]
                 显示或设定用户端字元编码
\prompt [文本] 名称
                 提示用户设定内部变数

2.操作命令

创建数据库: 
create database [数据库名]; 
删除数据库: 
drop database [数据库名];  
*重命名一个表: 
alter table [表名A] rename to [表名B]; 
*删除一个表: 
drop table [表名]; 
*在已有的表里添加字段: 
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 [该行特征]; 
delete from [表名];--删空整个表 
创建表: 
create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;); 
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
7月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL从入门到精通 - 第40讲:数据库不完全恢复
PostgreSQL从入门到精通 - 第40讲:数据库不完全恢复
215 1
|
7月前
|
存储 SQL 人工智能
01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)(下)
01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)
|
4月前
|
SQL 存储 关系型数据库
新手如何入门学习PostgreSQL?
新手如何入门学习PostgreSQL?
|
4月前
|
SQL 存储 关系型数据库
PostgreSQL核心之SQL基础学习
PostgreSQL核心之SQL基础学习
50 3
|
5月前
|
SQL 存储 关系型数据库
关系型数据库PostgreSQL学习
【7月更文挑战第4天】
458 2
|
6月前
|
存储 关系型数据库 数据库
经验大分享:PostgreSQL学习之【用户权限管理】说明
经验大分享:PostgreSQL学习之【用户权限管理】说明
104 0
|
7月前
|
关系型数据库 数据库 PostgreSQL
|
7月前
|
存储 SQL 关系型数据库
01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)(上)
01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)
|
7月前
|
Oracle 关系型数据库 数据库
PostgreSQL从入门到精通教程 - 第42讲:pg_rman部署与使用
PostgreSQL从入门到精通教程 - 第42讲:pg_rman部署与使用
178 1
|
7月前
|
SQL 监控 关系型数据库
postgresql|数据库|插件学习(二)---postgresql-12的外置插件pg_profile的启用和使用
postgresql|数据库|插件学习(二)---postgresql-12的外置插件pg_profile的启用和使用
225 0