建表SQL

最新推荐文章于 2024-03-22 09:48:53 发布

zhaohuihua

最新推荐文章于 2024-03-22 09:48:53 发布

阅读量476

点赞数

分类专栏： DB 文章标签： oracle SQL create table

本文链接：https://blog.csdn.net/zhaohuihua/article/details/19686193

版权

DB 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

create table t_clicklog
-- 用户点击日志表
---- author: zhaohuihua
(
    id              number(20) not null,
    time            date default sysdate not null,
    modulepath      varchar2(500) not null,
    sessioncode     varchar2(50) not null,
    usercode        varchar2(50),
    userip          varchar2(50) not null,
    operatetype     number(3) not null,
    resourcetype    number(3) not null,
    resourcecode    varchar2(50),
    resourcename    varchar2(500),
    singername      varchar2(500),
    area            varchar2(100),
    city            varchar2(100),
    description     varchar2(500),
    partid          number(3) not null
)
partition by list (partid)
(
    partition clicklog_jan values (1),
    partition clicklog_feb values (2),
    partition clicklog_mar values (3),
    partition clicklog_apr values (4),
    partition clicklog_may values (5),
    partition clicklog_jun values (6),
    partition clicklog_jul values (7),
    partition clicklog_aug values (8),
    partition clicklog_sep values (9),
    partition clicklog_oct values (10),
    partition clicklog_nov values (11),
    partition clicklog_dec values (12)
);

// alter table t_clicklog add constraint unq_ahclicklog_id    unique(id);

create index idx_ahclicklog_time         on t_clicklog(time) local;
create index idx_ahclicklog_modulepath   on t_clicklog(modulepath) local;
create index idx_ahclicklog_sessioncode  on t_clicklog(sessioncode) local;
create index idx_ahclicklog_usercode     on t_clicklog(usercode) local;
create index idx_ahclicklog_operatetype  on t_clicklog(operatetype) local;
create index idx_ahclicklog_resourcetype on t_clicklog(resourcetype) local;
create index idx_ahclicklog_resourcecode on t_clicklog(resourcecode) local;
create index idx_ahclicklog_resourcename on t_clicklog(resourcename) local;
create index idx_ahclicklog_singername   on t_clicklog(singername) local;
create index idx_ahclicklog_area         on t_clicklog(area) local;
create index idx_ahclicklog_city         on t_clicklog(city) local;

comment on table  t_clicklog              is '用户点击日志表(SP140)';

comment on column t_clicklog.id           is '日志ID';
comment on column t_clicklog.time         is '日志时间';
comment on column t_clicklog.modulepath   is '模块路径';
comment on column t_clicklog.sessioncode  is
    '用户会话编码,用户访问网站的会话编码';
comment on column t_clicklog.usercode     is '用户号码,用户登录网站的号码';
comment on column t_clicklog.userip       is '用户IP地址';
comment on column t_clicklog.operatetype  is
    '操作类型:1.点击浏览|2.播放|3.下载|4.赠送|5.设置|6.搜索';
comment on column t_clicklog.resourcetype is
    '资源类型:1.音乐|5.专辑|7.资讯|8.歌手|51.操作|52.导航|53.链接';
comment on column t_clicklog.resourcecode is '资源编码';
comment on column t_clicklog.resourcename is '资源名称';
comment on column t_clicklog.singername   is '歌手名称';
comment on column t_clicklog.area         is '地区(根据IP获取到的)';
comment on column t_clicklog.city         is '城市(根据IP获取到的)';
comment on column t_clicklog.description  is '描述';
comment on column t_clicklog.partid       is '分区ID';