PostgreSQL

psql -h 192.168.1.3 -U user -d mydb -a -f run.sql

CREATE TABLE mytable(...);
CREATE TABLE public.mytable(...);

SELECT * FROM pg_catalog.pg_user;

-- 为当前用户创建 myschema
CREATE SCHEMA myschema;

-- 创建一个与用户 dbuser 同名的 schema
CREATE SCHEMA AUTHORIZATION dbuser;

-- 为用户 dbuser 创建名为 test 的 schema, 如果该 schema 已经存在，则不做操作
CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION dbuser;

-- 删除一个空 Schema（不包含任何对象）
DROP SCHEMA myschema;

-- 删除 myschema 和其包含的所有对象
DROP SCHEMA myschema CASCADE;

-- USER
-- 创建用户 dbuser，该用户有创建数据库和用户的权限
CREATE USER dbuser WITH CREATEDB CREATEROLE PASSWORD 'test';

-- 删除用户 dbuser
DROP USER dbuser;

-- 修改用户名
ALTER USER dbuser RENAME TO testuser;

-- 修改用户 dbuser 的密码
ALTER USER dbuser WITH PASSWORD '123';

-- 给用户添加 SUPERUSER 权限
ALTER USER dbuser WITH SUPERUSER;

-- 撤销用户SUPERUSER CREATEUSER的权限
ALTER USER dbuser WITH NOSUPERUSER NOCREATEDB;

-- DATABASE
-- 创建数据库 mydb
CREATE DATABASE mydb OWNER dbuser;

-- 修改数据库名
ALTER DATABASE mydb RENAME TO testdb;

-- 将数据库拥有者改为 testuser
ALTER DATABASE mydb OWNER TO testuser;

-- DROP DATABASE
DROP DATABASE mydb;

-- GRANT
-- 授权 dbuser 可以对 myschema 中的所有表执行 SELECT INSERT 和 UPDATE 操作
GRANT SELECT INSERT UPDATE ON myschema TO dbuser;

-- 授权所有用户对 catalog 表有 SELECT 权限
GRANT SELECT ON catalog TO PUBLIC;

GRANT ALL PRIVILEGES ON DATABASE mydb TO dbuser;

-- CREATE TABLE
CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

CREATE TABLE distributors (
     did    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     name   varchar(40) NOT NULL CHECK (name <> '')
);

-- Copy a table from an existing table including both table structure and data
CREATE TABLE copied AS TABLE existing_table;

-- Copy a table structure without data
CREATE TABLE copied AS TABLE existing_table WITH NO DATA;

-- Copy a table with partial data from an existing table
CREATE TABLE copied AS SELECT * FROM existing_table WHERE conditions;

-- Rename an existing column
ALTER TABLE distributors RENAME COLUMN address TO city;

-- change the types of two existing columns in one operation
ALTER TABLE distributors
  ALTER COLUMN email TYPE varchar(50),
  ALTER COLUMN city TYPE varchar(30);

-- DROP TABLE
DROP TABLE films, distributors;

-- INSERT
INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

-- 没有 len 字段，该字段会填入默认值
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

-- 插入多条记录，第二条记录的 date_prod 字段使用 DEFAULT 子句生成默认值
INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

-- Insert data from another table
INSERT INTO mytable SELECT * FROM anthoer_tabel WHERE conditions;

-- SELECT by order and display top 5 record
SELECT * FROM films ORDER BY date_prod DESC LIMIT 5;

-- 获取近10年出品的电影
SELECT * FROM films WHERE date_prod > (now() - interval '10 years');

SELECT * FROM json_table WHERE json_data::TEXT LIKE '%String%' ORDER BY created LIMIT 10;

value in (value1, value2, ...)

value in (SELECT columns FROM table_name)

select id, name, age from table_name where id in ('001', '002', '003');

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

DELETE FROM films WHERE title='Yojimbo';

-- 获取当前完整时间，下面两条命令等效
select now();
select current_timestamp;

-- 获取当前日期
select current_date;

-- 获取当前时间
select current_time;

-- 时间计算，years, months, weeks, days, hours, minutes, seconds
-- 两天后的时间
select now() + interval '2 days';

-- 截取部分时间
select extract(year from now());
select date_part(day, now());

-- copies a table to the client using the vertical bar (|) as the field delimite
COPY country TO STDOUT (DELIMITER '|');

-- copy data from a file into the country table
COPY country FROM '/usr1/proj/bray/sql/country_data';

-- copy into a file just the countries whose names start with 'A'
COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

-- copy into a compressed file, you can pipe the output through an external compression program
COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';

-- export to a csv file with header
COPY country TO '/tmp/table.csv' (FORMAT csv, HEADER);

-- import the csv file into a table with header
COPY country (country, text, currency) FROM '/var/lib/pgsql/data.csv' (DELIMITER ',', FORMAT csv, header);

select version();

CREATE USER replica WITH REPLICATION LOGIN PASSWORD 'P@ssw0rd!';

show config_file;
show hba_file;

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

select pg_is_in_recovery();

show archive_modes

-- Create restore point
select pg_create_restore_point('first_point');