PostgreSQL数据库基础

一、PostgreSQL概述

二、PostgreSQL 登录及常用命令

2.1 psql 命令

要在命令行登录 PostgreSQL，可以使用 psql 命令行工具，psql 支持两种登陆方式：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


# 方法1：

psql postgresql://${username}:${password}@${host}:${pord}/${dbname}

# postgresql：  协议名称
# username：    用户名
# password：    密码
# host：        ip/host
# port：        端口
# dbname：      需要访问的数据库名称

# 方法2：
psql -h127.0.0.1 -p5434 -U postgres -dpostgres -W

# -h 数据库IP
# -p 数据库端口
# -U 用户名
# -d 需要访问的数据库名称
# -W 提示输入密码

# 如果想用 psql 直接连接数据库，需要通过设置postgres用户的环境变量来实现：

export PGHOME=/usr/pgsql-13
export PGUSER=postgres
export PGPORT=5434
export PGHOST=localhost
export PATH=$PGHOME/bin:$PATH:$HOME/bin

导入SQL脚本

1

psql -U postgres -d database -f sqlScript.sql

将 sqlScript.sql 导入到名为 database 的数据库中。

2.2 PostgresSQL 控制台命令

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


dbname=> \h                     # 查看SQL命令的解释，比如\h select。
dbname=> \?                     # 查看psql命令列表。
dbname=> \l                     # 列出所有数据库。
dbname=> \c [database_name]     # 连接数据库。
                                # \c 可选参数 dbname [ username ] [ host ] [ port ] 
                                # eg:
                                # \c postgres
                                # \c postgres username localhost 5432
dbname=> \d                     # 列出当前数据库的所有表格。
dbname=> \d [table_name]        # 列出某一张表格的结构。
dbname=> \sf func_name          # 列出函数定义语句。
dbname=> \du                    # 列出所有用户。
dbname=> \e                     # 打开文本编辑器。
dbname=> \conninfo              # 列出当前数据库和连接的信息。
dbname=> \dn                    # 展示当前数据库下所有schema信息
dbname=> \q                     # 断开数据库连接
dbname=> quit                   # 断开数据库连接

2.3 数据库常用 SQL 语句

数据库创建

在PostgreSQL中，可以使用 CREATE DATABASE 语句来创建一个新的数据库：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


-- 创建数据库，
CREATE DATABASE dbname;

-- 创建数据库 并指定数据库的 owner
CREATE DATABASE dbname OWNER username;

-- 创建数据库 并设置数据库的其他参数，例如字符编码或者最大连接数
CREATE DATABASE dbname
WITH 
    OWNER = username
    ENCODING = 'UTF8'               -- 设置数据库的编码为UTF-8
    TABLESPACE = tablespaceName     -- 指定表空间
    LC_COLLATE = 'en_US.utf8'       -- 设置本地排序规则
    LC_CTYPE = 'en_US.utf8'
    CONNECTION LIMIT = 100;         -- 限制连接数为100

PostgreSQL 还提供了 createdb 命令行工具用于创建一个新的 PostgreSQL 数据库。该工具实际上是在后台使用 PostgreSQL 的 CREATE DATABASE SQL 命令来创建数据库的。使用 createdb 工具可以让数据库管理员在命令行环境中快速地创建数据库，无需直接进入 SQL 命令行接口。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37


root@8135fedb9707:/# which createdb
/usr/bin/createdb
root@8135fedb9707:/# createdb --help
createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
      --icu-locale=LOCALE      ICU locale setting for the database
      --icu-rules=RULES        ICU rules setting for the database
      --locale-provider={libc|icu}
                               locale provider for the database's default collation
  -O, --owner=OWNER            database user to own the new database
  -S, --strategy=STRATEGY      database creation strategy wal_log or file_copy
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database

By default, a database with the same name as the current user is created.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

表操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


-- 创建新表 
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);

-- 插入数据 
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');

-- 查询记录 
SELECT * FROM user_tbl;

-- 更新数据 
UPDATE user_tbl set name = '李四' WHERE name = '张三';

-- 删除记录 
DELETE FROM user_tbl WHERE name = '李四' ;

-- 添加栏位(列) 
ALTER TABLE user_tbl ADD email VARCHAR(40);

-- 更新结构 
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

-- 更名栏位 
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

-- 删除栏位 
ALTER TABLE user_tbl DROP COLUMN email;

-- 表格更名 
ALTER TABLE user_tbl RENAME TO backup_tbl;

-- 删除表格 
DROP TABLE IF EXISTS backup_tbl;

-- 修改数据库的名字
ALTER DATABASE oldName RENAME to newName

函数

1
2


-- 查询自定义函数
select prosrc from pg_proc where proname='func_name'；

三、PostgreSQL 数据类型简介

PGSQL支持的类型特别丰富，大多数的类型和MySQL都有对应的关系

名称	说明	对比MySQL
布尔类型	boolean，标准的布尔类型，只能存储true，false	MySQL中虽然没有对应的boolean，但是有替换的类型，数值的tinyint类型，和PGSQL的boolean都是占1个字节。
整型	smallint（2字节），integer（4字节），bigint（8字节）	跟MySQL没啥区别。
浮点型	decimal，numeric（和decimal一样一样的，精准浮点型），real（float），double precision（double），money（货币类型）	和MySQL基本也没区别，MySQL支持float，double，decimal。MySQL没有这个货币类型。
字符串类型	varchar(n)（character varying），char(n)（character），text	这里和MySQL基本没区别。 PGSQL存储的varchar类型，可以存储一个G。MySQL好像存储64kb（应该是）。
日期类型	date（年月日），time（时分秒），timestamp（年月日时分秒）（time和timestamp可以设置时区）	没啥说的，和MySQL基本没区别。 MySQL有个datetime。
二进制类型	bytea-存储二进制类型	MySQL也支持，MySQL中是blob
位图类型	bit(n)（定长位图），bit varying(n)（可变长度位图）	就是存储0，1。MySQL也有，只是这个类型用的不多。
枚举类型	enum，跟Java的enum一样	MySQL也支持。
几何类型	点，直线，线段，圆…………	MySQL没有，但是一般开发也用不到
数组类型	在类型后，追加[]，代表存储数组	MySQL没有
JSON类型	json（存储JSON数据的文本），jsonb（存储JSON二进制）	可以存储JSON，MySQL8.x也支持
ip类型	cidr（存储ip地址）	MySQL也不支持~
等等	http://www.postgres.cn/docs/12/datatype.html