KingbaseES数据库运维案例---SCOTT用户及对象创建

案例说明：
生产用户从Oracle环境迁移到KingbaseES数据库后，需要使用Oracle下scott用户的应用测试环境，本案例借助Oracle创建scott用户应用环境的脚本，创建KingbaseES下的应用测试环境。适用版本： KingbaseES V8R3/R6

SCOTT用户有四张数据表：1)部门信息表：dept

2)雇员信息表：emp

3)工资等级表：salgrade

4)工资表：bonus

一、创建scott用户和同名schema(可以将schema在test库下创建)

create user scott with password 'tiger';
create schema scott AUTHORIZATION scott;

在test库创建scott同名schema：

二、scott用户登录数据库创建对象：[kingbase@node101 ~]$ ./ksql -U scott -W tiger test

1)创建dept表

CREATE TABLE SCOTT.DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME varchar(14) ,
    LOC varchar(13) ) ;

2)创建emp表

CREATE TABLE SCOTT.EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME varchar(10),
    JOB varchar(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

3)在dept和emp表插入数据

INSERT INTO SCOTT.DEPT VALUES
    (10,'ACCOUNTING','NEW YORK');
INSERT INTO SCOTT.DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO SCOTT.DEPT VALUES
    (30,'SALES','CHICAGO');
INSERT INTO SCOTT.DEPT VALUES
    (40,'OPERATIONS','BOSTON');

INSERT INTO SCOTT.EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO SCOTT.EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO SCOTT.EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO SCOTT.EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO SCOTT.EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO SCOTT.EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO SCOTT.EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO SCOTT.EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-06-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO SCOTT.EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO SCOTT.EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO SCOTT.EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-06-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO SCOTT.EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO SCOTT.EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO SCOTT.EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

4)创建BONUS表

CREATE TABLE SCOTT.BONUS
    (
    ENAME varchar(10)    ,
    JOB varchar(9)  ,
    SAL NUMBER,
    COMM NUMBER
    ) ;

5)创建SALGRADE表和插入数据

CREATE TABLE SCOTT.SALGRADE
      ( GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER );
INSERT INTO SCOTT.SALGRADE VALUES (1,700,1200);
INSERT INTO SCOTT.SALGRADE VALUES (2,1201,1400);
INSERT INTO SCOTT.SALGRADE VALUES (3,1401,2000);
INSERT INTO SCOTT.SALGRADE VALUES (4,2001,3000);
INSERT INTO SCOTT.SALGRADE VALUES (5,3001,9999);

6)创建完成

test=> \d+
                                 List of relations
 Schema |             Name              | Type  | Owner  |    Size    | Description
--------+-------------------------------+-------+--------+------------+-------------
 scott  | BONUS                         | table | scott  | 8192 bytes |
 scott  | DEPT                          | table | scott  | 8192 bytes |
 scott  | EMP                           | table | scott  | 8192 bytes |
 scott  | SALGRADE                      | table | scott  | 16 kB      |