程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> Vertica數據庫sql操作備忘

Vertica數據庫sql操作備忘

編輯:關於SqlServer

      刪除主鍵(Vertica數據庫的主鍵值並不是唯一的):
    SELECT ANALYZE_CONSTRAINTS('fb_s.c_log');

    找到key名,再:

    ALTER TABLE fb_s.c_log DROP CONSTRAINT C_PRIMARY;

    SELECT ANALYZE_CONSTRAINTS('fb_s.user_info');

    ALTER TABLE fb_s.user_info DROP CONSTRAINT C_PRIMARY;

    建用戶和SCHEMA :

    CREATE user fb_s_sql IDENTIFIED BY 'password';
    CREATE SCHEMA fb_s_sql;

    給權限:
    GRANT ALL ON SCHEMA fb_s_sql TO fb_s_sql;
    GRANT ALL ON SCHEMA fb_s TO fb_s_sql;

    GRANT ALL ON TABLE fb_s_sql.sqllog TO fb_s_sql;

    建表:

    CREATE TABLE fb_s.c_log (
    uid int NOT NULL,
    cash int,
    gold int,
    level int,
    rtime datetime,
    tid varchar(20),
    act varchar(50),
    item varchar(500),
    value int,
    value2 int,
    time datetime
    );

    CREATE TABLE fb_s.new_c_log (
    uid integer PRIMARY KEY NOT NULL,
    cash integer,
    gold integer,
    level integer,
    rtime datetime,
    tid varchar(20),
    act varchar(50),
    item varchar(500),
    value integer,
    value2 integer,
    time datetime NOT NULL
    )
    PARTITION BY EXTRACT(year FROM time)*100 + EXTRACT(month FROM time);

    後一個是按time字段分區

    增加及修改字段:

    ALTER TABLE fb_s.c_logADD COLUMN value2 integer default 0;
    ALTER TABLE fb_s.c_log ALTER COLUMN duration SET DEFAULT 0;
    ALTER TABLE fb_s.c_log ALTER COLUMN mesg SET DEFAULT '';


    兩表之間導數據:
    insert into fb_s.c_log (uid,cash,gold,level,rtime,tid,act,item,value,value2,time)
    (select * from fb_s.c_logbak);

    兩庫之間導數據:

    在源庫導出:

    vsql -d topcity -U dbadmin -w password -F ',' -At -o fs_user_info.csv -c "SELECT * FROM fb_s.user_info;" &
    vsql -d topcity -U dbadmin -w password -F ',' -At -o fs_c_log.csv -c "SELECT * FROM fb_s.c_log;" &

    目的庫導入:
    COPY fb_s.user_info FROM '/opt/fs_user_info.csv' EXCEPTIONS '/tmp/exp.log' DELIMITER ',';

    COPY fb_s.c_log FROM '/opt/fs_c_log.csv' EXCEPTIONS '/tmp/exp.log' DELIMITER ',';

    PS:

    是不是感覺本大神無所不會,想崇拜本神的email到 [email protected].

    幫我在HP明日之星投上一票哦親!

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved