Basic Operation of PostgreSQL - HolmesJJ/CS2102-Database-Systems GitHub Wiki

命令行控制

  • sudo su postgres登录PostgreSQL
  • psql 进入PostgreSQL
  • \l 查看当前数据库
  • \q 退出控制台

数据库操作

-- 创建数据库
CREATE DATABASE dbtest
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

COMMENT ON DATABASE dbtest
    IS 'This is my first database';

ALTER ROLE postgres IN DATABASE dbtest
    SET application_name TO 'DEFAULT';

-- 数据库授权
GRANT CREATE, CONNECT ON DATABASE dbtest TO postgres;
GRANT TEMPORARY ON DATABASE dbtest TO postgres WITH GRANT OPTION;

GRANT TEMPORARY, CONNECT ON DATABASE dbtest TO PUBLIC;

-- 修改数据库属性
ALTER DATABASE dbtest WITH CONNECTION LIMIT = 20;

-- 删除数据库
DROP DATABASE dbtest

表,主键,外键操作

-- 创建transcripts表
CREATE TABLE public.transcripts
(
    id integer NOT NULL,
    module "char",
    score integer,
    CONSTRAINT transcripts_pkey PRIMARY KEY (id)
)

-- 创建students表
CREATE TABLE public.students
(
    id integer NOT NULL,
    name "char",
    module_id integer,
    CONSTRAINT students_pkey PRIMARY KEY (id),
    CONSTRAINT students_transcripts_fkey FOREIGN KEY (module_id) REFERENCES public.transcripts (id)
)

修改数据库属性

-- 修改列属性
ALTER TABLE public.students ADD CONSTRAINT students_id_unique UNIQUE (id);

-- 修改表名
ALTER TABLE students RENAME TO all_students;

-- 添加列
ALTER TABLE all_students ADD COLUMN description text;

-- 添加列和约束类型
ALTER TABLE all_students ADD COLUMN description text NOT NULL;

-- 修改列的类型
ALTER TABLE all_students ALTER COLUMN name TYPE text;

-- 修改列的名
ALTER TABLE all_students RENAME name TO full_name;

-- 修改列的约束类型
ALTER TABLE all_students ALTER COLUMN full_name SET NOT NULL;

-- 移除列
ALTER TABLE all_students DROP description;

-- 移除约束类型
ALTER TABLE all_students ALTER COLUMN description DROP NOT NULL;

-- 添加外键约束
ALTER TABLE all_students ADD CONSTRAINT students_transcripts_fkey 
FOREIGN KEY (module_id) REFERENCES transcripts (id);

-- 删除外键约束
ALTER TABLE all_students DROP CONSTRAINT students_transcripts_fkey;

-- 删除多个表
DROP TABLE IF EXISTS test1, test2;