关闭

PostgreSQL中的SQL语句示例

标签: postgresqlsql
317人阅读 评论(0) 收藏 举报
分类:
  • 创建数据表
-- 组织表
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;
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:172745次
    • 积分:2736
    • 等级:
    • 排名:第13138名
    • 原创:99篇
    • 转载:73篇
    • 译文:1篇
    • 评论:20条
    最新评论