postgresql

基础

sudo apt install postgresql postgresql-contrib
sudo -i -u postgres
psql
sudo -u postgres psql
createuser --interactive
createdb [<databasename>] default name is the username
dropdb <databasename>
psql <databasename>

内置函数

version

SELECT version(), current_date, 2+2;

2. The SQL Language

Introduction

cd ..../src/tutorial
make
cd ..../toturial
psql -s mydb
mydb=> \i basics.sql

2.3 创建表 

CREATE TABLE weather (
    city    varchar(80),
    temp_lo int,  -- 后面是注释
    temp_hi int,
    prcp    real,
    date    date
)

2.4 插入数据

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');  /* 一定要用单引号 */
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

COPY weather FROM '/home/user/weather.txt';

2.5 查询表

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

2.7 Aggregate Functions 聚合数据

SELECT max(temp_lo) FROM weather;
SELECT city FROM weather WHERE temp_lo = max(temp_lo);     WRONG, where里面不能用聚合属性
SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

2.8 更新表

UPDATE weather
  SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
  WHERE date>'1994-11-28';

3.2 Advanced Features -- Views

CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, location FROM weather, cities WHERE city = name;
SELECT * FROM myview;

3.3 外键

CREATE TABLE cities (
    name varchar(80) primary key,
    location point
);
CREATE TABLE weather (
    city varchar(80) reference cities(name),
);

The SQL Language

数据定义 Data

约束

check约束

create table weather ( temp_lo int CHECK (temp_lo > 0) );

5.7 Privileges 权限 

更改表的权限

ALTER TABLE <table_name> OWNER TO <role_name>;  /* 变更表，数据库的owner*/
GRANT SELECT ON <table_name> TO <role_name>;  /* 允许某个用户查询某个表 */
REVOKE ALL ON role2_s_table FROM wangx;  /* 不允许wangx操作role2_s_table */
REVOKE DELETE ON <table_name> TO <role_name>;  /* 删除用户的DELETE权限 */

查看表的权限 r是select权限 a是insert权限 w是update权限 d是delete权限

\dp role2_s_table;
wangx=# \dp role2_s_table;
                                   存取权限 架构模式 |     名称      |  类型  |         存取权限          | 列特权 | 策略
----------+---------------+--------+---------------------------+--------+------
 public   | role2_s_table | 数据表 | postgres=arwdDxt/postgres+|        |
          |               |        | noper=r/postgres          |        |

数据类型

Numeric Types
1. Integer Types

point 位置坐标

CREATE TABLE cities (
    name            varchar(80),
    location        point
);
INSERT INTO cities VALUES (
    'San Francisco', '(-194.0, 53.0)');

Sever Administration 服务器管理

查看数据库

SELECT
    d.datname AS 数据库名,
    pg_get_userbyid(d.datdba) AS 数据库拥有者,
    t.spcname AS tablespace名称,
    pg_tablespace_location(t.oid) AS tablespace路径
FROM pg_database d
LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid
ORDER BY d.datname;

查看当前连接数:

select datname from pg_stat_activity;

查看各个表的尺寸

select
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
  pg_total_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 3 desc;

basic

修改用户密码

ALTER USER user_name WITH PASSWORD 'new_password';

允许用户查询某个数据表

GRANT CONNECT ON DATABASE <db> TO <role>;
GRANT USAGE ON SCHEMA public TO <role>;
GRANT SELECT ON ALL TABLES IN SCHEMA public to <role>;

Database Roles

CREATE ROLE <name>;
DROP ROLE <name>;
SELECT rolname FROM pg_roles;

Back Up and Restore

数据库导出

pg_dump \
    --host localhost \
    --no-owner \
    --if-exists \
    --no-acl \
    --schema-only \
    --table some \
    --exclude-schema=\
    -f <outputfie>.sql
    <dbname>

升级

sudo pacman -S postgresql-old-upgrade
pg_upgrade  -b /opt/pgsql-17/bin/ -B /usr/bin/ -d /var/lib/postgres/data17 -D /var/lib/postgres/data
j

PostgreSQL Administration 

展示所有table \dt
展示单个表

\d+ users

展示所有数据库 \l
执行sql语句

\i lesson1.sql

展示所有连接

select usename, client_addr from pg_stat_activity;
SHOW max_connections;

权限

允许用户创建表

ALTER USER <username> CREATEDB

Reference

ALTER USER 

CREATE USER <username> WITH PASSWORD '密码';
ALTER USER <username> WITH PASSWORD '新密码';

ALTER TABLE 

ALTER TABLE students ALTER age integer;
ALTER TABLE students DROP age;

CREATE DATABASE

CREATE DATABASE people WITH OWERN 'pg4e';

Create Table 创建表 

添加自增主键

create table students (
    id integer primary key generated by default as identity,
    name char(3)
);
insert into students values (default, '1');  /* 1 */
insert into students values (2, '1');
insert into students values (default, '1');  /* 报错, 因为2存在了 */

PostgreSQL Client Applications - PSQL命令工具

官网

\h查看SQL帮助
\?查看命令相关帮助
\q退出
\dt 查看所有的数据表
\dS+ <tablename> 查看某个数据表
\timing 显示语句执行时间
\r 重置输入的内容

配置

允许远程登录

编辑postgresq.conf

listen_addresses = '*'  # 或者改成IP地址

编辑pg_hba.conf

host    <数据库>     <用户>             <fromIP>/32                 scram-sha-256

刷新登录权限

select pg_reload_conf();  /* 执行这个语句更新登录权限 */

多数据库管理

TableSpaces

-- 利用额外的硬盘创建tablespace空间
CREATE TABLESPACE table_03 LOCATION '/ssd1/postgresql/data';
-- create database table
create database db_02 TABLESPACE=tablespace_02;

海量数据管理经验

大表的孔洞

更新或者删除数据, 可能导致空间留下孔洞, 安装 pgstattuple 可以直接看到孔洞数量. 但是这个查询是全表查询, 速度慢

-- 安装扩展（只需执行一次）
CREATE EXTENSION pgstattuple;

-- 查看表的详细空间使用情况
SELECT * FROM pgstattuple('your_table_name');

-- 查看索引的空洞
SELECT * FROM pgstatindex('your_table_pkey');

使用估算

-- 快速估算，避免全表扫描
SELECT * FROM pgstattuple_approx('large_table');

-- 返回结果包含近似值，适合快速巡检

postgresql

基础

Tutorial 

1. Getting Started 

内置函数

2. The SQL Language

2.3 创建表 

2.4 插入数据

2.5 查询表

2.7 Aggregate Functions 聚合数据

2.8 更新表

3.2 Advanced Features -- Views

3.3 外键

The SQL Language

数据定义 Data

5.7 Privileges 权限 

数据类型

Sever Administration 服务器管理

basic

Database Roles

Back Up and Restore

数据库导出

升级

PostgreSQL Administration 

权限

Reference

ALTER USER 

ALTER TABLE 

CREATE DATABASE

Create Table 创建表 

PostgreSQL Client Applications - PSQL命令工具

配置

多数据库管理

TableSpaces

海量数据管理经验

大表的孔洞

postgresql

基础

Tutorial

1. Getting Started

内置函数

2. The SQL Language

2.3 创建表

2.4 插入数据

2.5 查询表

2.7 Aggregate Functions 聚合数据

2.8 更新表

3.2 Advanced Features -- Views

3.3 外键

The SQL Language

数据定义 Data

5.7 Privileges 权限

数据类型

Sever Administration 服务器管理

basic

Database Roles

Back Up and Restore

数据库导出

升级

PostgreSQL Administration

权限

Reference

ALTER USER

ALTER TABLE

CREATE DATABASE

Create Table 创建表

PostgreSQL Client Applications - PSQL命令工具

配置

多数据库管理

TableSpaces

海量数据管理经验

大表的孔洞

Tutorial 

1. Getting Started 

2.3 创建表 

5.7 Privileges 权限 

PostgreSQL Administration 

ALTER USER 

ALTER TABLE 

Create Table 创建表 