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,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,constraint role_pkey primary key (id),constraint role_name_key unique (name) );

-- 用户表
create table if not exists users( id uuid not null,password character(50) not null,org_id uuid,dep_id uuid,role_id uuid not null,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,password,role_id,create_time,update_time,forbidden,comment) values('4DE8FEC4-6C13-2EAF-06EE-1B1C4FEF0095','admin','A08D038B-B3E6-31CD-A593-AB4A49C74CA2',now(),false,'超级管理员'); -- now()：获取当前时间

• 删除数据表

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