BiuBiu

# PostgreSQL

# 入门介绍

简介：

PostgreSQL是一种关系型数据库管理系统 （RDBMS）用它的原话来说就是 The World's Most Advanced Open Source Relational Database, 官方网站文档：https://www.postgresql.org/docs/ (opens new window)

常用数据类型

项目 类型
数值型 smallint/int2、integer/int4/int、 real/float4 、numeric(p,s)/decimal(p,s)、serial/serial4、bigserial
字符类型 varchar(n)、char(n)、 text
日期和时间型 timestamp、timestamp with time zone、date、time、interval
几何类型 point
其他 --

SQL

  1. 建库删库

    createdb mydb
dropdb mydb

-- 命令行访问数据库
psql mydb

SELECT version();
SELECT current_date;
    1
    2
    3
    4
    5
    6
    7
    8

  2. 建表删表

    CREATE TABLE "weather" (
    id              SERIAL NOT NULL, -- 自增ID
    city            varchar(80),
    temp_lo         int,             -- 最低温度
    temp_hi         int,             -- 最高温度
    prcp            real,            -- 湿度
    date            date
);
DROP TABLE tablename;
    1
    2
    3
    4
    5
    6
    7
    8
    9

  3. 表字段增删改

    -- 新增字段
ALTER TABLE my_table ADD COLUMN remark VARCHAR(200);

-- 删除表字段
ALTER TABLE my_table DROP COLUMN remark;

-- 修改列类型和长度
ALTER TABLE my_table ALTER COLUMN remark TYPE VARCHAR(500);

-- 修改列名
ALTER TABLE my_table RENAME COLUMN remark TO remark2;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11

  4. 添加注释

    -- 修改表注释
COMMENT ON TABLE my_table IS '表注释';

-- 修改字段注释
COMMENT ON COLUMN my_table.name IS '字段注释名称';
    1
    2
    3
    4
    5

  5. 创建索引

    -- 建普通索引(索引名通常为 idx_表名_字段名)
CREATE INDEX IF NOT EXISTS idx_my_table_age ON my_table USING btree (age);

-- 建唯一索引
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_unique_my_table_id ON my_table (id);

-- 组合索引
CREATE INDEX IF NOT EXISTS index_name ON table_name (column1_name, column2_name);

-- 删除索引
DROP INDEX IF EXISTS idx_unique_my_table_id;

-- 新增主键
ALTER TABLE my_table ADD CONSTRAINT custom_name_of_my_table_id PRIMARY KEY (id);
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14

  6. 创建序列

    CREATE SEQUENCE IF NOT EXISTS id_no START 10000;
    1

  7. 赋予用户表权限

    -- 赋予用户表权限
ALTER TABLE my_table OWNER TO user_name;

-- 赋予用户所有表权限
GRANT ALL ON my_table TO user_name;

-- 赋予用户表的增删改查权限
GRANT INSERT,UPDATE,DELETE,SELECT ON my_table TO user_name;

-- 将此表的SELECT权限赋给所有用户
GRANT SELECT ON my_table TO PUBLIC;

-- 赋予demo_role所有表的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to user_name;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14

  8. 视图

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

SELECT * FROM myview;
    1
    2
    3
    4
    5
    6

  9. 事务

    在PostgreSQL中，开启一个事务需要将SQL命令用BEGIN和COMMIT命令包围起来，一组被BEGIN和COMMIT包围的语句也被称为一个事务块。

    BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;

SAVEPOINT my_savepoint;
ROLLBACK TO my_savepoint;
    1
    2
    3
    4
    5
    6
    7
    8

  10. 窗口函数 SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

← SQL Server →