PostgreSQL中的SQL语句示例

• 创建数据表

-- 组织表
create table if not exists organization(
    id uuid not null,   -- uuid
    name character(50) not null,
    create_time timestamp with time zone not null,
    update_time timestamp with time zone not null,
    forbidden boolean not null default false,   -- 默认值
    comment character(256),
    constraint organization_pkey primary key (id),  -- 主键
    constraint organization_name_key unique (name)  -- 唯一值
);

-- 部门表
create table if not exists department(
    id uuid not null,
    name character(50) not null,
    org_id uuid not null,
    parent_dep_id uuid,
    create_time timestamp with time zone not null,
    update_time timestamp with time zone not null,
    forbidden boolean not null default false,
    comment character(256),
    constraint department_pkey primary key (id),
    constraint department_org_id_fkey foreign key (org_id) references organization (id),    -- 外键
    constraint department_parent_dep_id_fkey foreign key (parent_dep_id) references department (id),
    constraint department_name_key unique (name)
);

-- 角色表
create table if not exists role(
    id uuid not null,
    name character(50) not null,
    comment character(256),
    constraint role_pkey primary key (id),
    constraint role_name_key unique (name)
);

-- 用户表
create table if not exists users(
    id uuid not null,
    name character(50) not null,
    password character(50) not null,
    org_id uuid,
    dep_id uuid,
    role_id uuid not null,
    create_time timestamp with time zone not null,
    update_time timestamp with time zone not null,
    forbidden boolean not null default false,
    comment character(256),
    constraint users_pkey primary key (id),
    constraint users_org_id_fkey foreign key (org_id) references organization (id),
    constraint users_dep_id_fkey foreign key (dep_id) references department (id),
    constraint users_role_id_fkey foreign key (role_id) references role (id),
    constraint users_name_key unique (name)
);

• 插入记录

-- 插入角色
insert into role(id, name, comment) values('A08D038B-B3E6-31CD-A593-AB4A49C74CA2','super_admin','超级管理员');

-- 插入用户
insert into users(id, name, password, role_id, create_time, update_time, forbidden, comment) 
    values('4DE8FEC4-6C13-2EAF-06EE-1B1C4FEF0095', 'admin', 'admin', 'A08D038B-B3E6-31CD-A593-AB4A49C74CA2', now(), now(), false, '超级管理员'); -- now()：获取当前时间

• 删除数据表

drop table if exists users;
drop table if exists role;
drop table if exists department;
drop table if exists organization;