T-001.数据库练习表

>>数据库练习表，先写在T001，后续会陆续用到~~~

Create Sequence tablename1_s;Create Synonym tn1 For tablename1;create table tablename1( GROUP_ID    Number Default tablename1_s.nextval, NUM1 Number, NUM2 Number, NUM3 Number, NUM4 Number, NUM5 Number, NUM6 Number, NUM7 Number, NUM8 Number, NUM9 Number, NUM10 Number, CHAR1 Varchar2(2000), CHAR2 Varchar2(2000), CHAR3 Varchar2(2000), CHAR4 Varchar2(2000), CHAR5 Varchar2(2000), CHAR6 Varchar2(2000), CHAR7 Varchar2(2000), CHAR8 Varchar2(2000), CHAR9 Varchar2(2000), CHAR10 Varchar2(2000), PROCESS_STATUS Varchar2(1), CREATION_DATE     DATE DEFAULT SYSDATE NOT NULL, CREATED_BY        NUMBER DEFAULT -1 NOT NULL, LAST_UPDATED_BY   NUMBER DEFAULT -1 NOT NULL, LAST_UPDATE_DATE  DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_LOGIN NUMBER);Create Sequence tablename2_s;Create Synonym tn2 For tablename2;create table tablename2( GROUP_ID    Number Default tablename2_s.nextval, NUM1 Number, NUM2 Number, NUM3 Number, NUM4 Number, NUM5 Number, NUM6 Number, NUM7 Number, NUM8 Number, NUM9 Number, NUM10 Number, CHAR1 Varchar2(2000), CHAR2 Varchar2(2000), CHAR3 Varchar2(2000), CHAR4 Varchar2(2000), CHAR5 Varchar2(2000), CHAR6 Varchar2(2000), CHAR7 Varchar2(2000), CHAR8 Varchar2(2000), CHAR9 Varchar2(2000), CHAR10 Varchar2(2000), PROCESS_STATUS Varchar2(1), CREATION_DATE     DATE DEFAULT SYSDATE NOT NULL, CREATED_BY        NUMBER DEFAULT -1 NOT NULL, LAST_UPDATED_BY   NUMBER DEFAULT -1 NOT NULL, LAST_UPDATE_DATE  DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_LOGIN NUMBER);Create Sequence tablename3_s;Create Synonym tn3 For tablename3;create table tablename3( GROUP_ID    Number Default tablename3_s.nextval, NUM1 Number, NUM2 Number, NUM3 Number, NUM4 Number, NUM5 Number, NUM6 Number, NUM7 Number, NUM8 Number, NUM9 Number, NUM10 Number, CHAR1 Varchar2(2000), CHAR2 Varchar2(2000), CHAR3 Varchar2(2000), CHAR4 Varchar2(2000), CHAR5 Varchar2(2000), CHAR6 Varchar2(2000), CHAR7 Varchar2(2000), CHAR8 Varchar2(2000), CHAR9 Varchar2(2000), CHAR10 Varchar2(2000), PROCESS_STATUS Varchar2(1), CREATION_DATE     DATE DEFAULT SYSDATE NOT NULL, CREATED_BY        NUMBER DEFAULT -1 NOT NULL, LAST_UPDATED_BY   NUMBER DEFAULT -1 NOT NULL, LAST_UPDATE_DATE  DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_LOGIN NUMBER);

>>ORACLE自带练习表

--创建数据表Create Table dept(deptno Number(2) Constraint pk_dept primary key                 ,dname Varchar2(14)                 ,loc Varchar2(13));                 Create Table emp(empno Number(4) Constraint pk_emp primary key                ,ename Varchar2(10)                ,job Varchar2(9)                ,mgr Number(4)                ,hiredate Date                ,sal Number(7, 2)                ,comm Number(7, 2)                ,deptno Number(2) Constraint fk_deptno references dept);                  Create Table salgrade(grade Number                     ,losal Number                     ,hisal Number);                     --创建序列                   Create Sequence emp_s Start With 8888;                --插入测试数据 —— deptINSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');INSERT INTO dept VALUES (30,'SALES','CHICAGO');INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');--插入测试数据 —— empINSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('1980-12-17','YYYY-MM-DD'),800,NULL,20);INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('1981-2-20','YYYY-MM-DD'),1600,300,30);INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('1981-2-22','YYYY-MM-DD'),1250,500,30);INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('1981-4-2','YYYY-MM-DD'),2975,NULL,20);INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('1981-9-28','YYYY-MM-DD'),1250,1400,30);INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1981-5-1','YYYY-MM-DD'),2850,NULL,30);INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('1981-6-9','YYYY-MM-DD'),2450,NULL,10);INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('1987-07-13','YYYY-MM-DD')-85,3000,NULL,20);INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('1981-11-17','YYYY-MM-DD'),5000,NULL,10);INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('1981-9-8','YYYY-MM-DD'),1500,0,30);INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('1987-07-13','YYYY-MM-DD')-51,1100,NULL,20);INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('1981-12-3','YYYY-MM-DD'),950,NULL,30);INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('1981-12-3','YYYY-MM-DD'),3000,NULL,20);INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('1982-1-23','YYYY-MM-DD'),1300,NULL,10);--插入测试数据 —— salgradeINSERT INTO salgrade VALUES (1,700,1200);INSERT INTO salgrade VALUES (2,1201,1400);INSERT INTO salgrade VALUES (3,1401,2000);INSERT INTO salgrade VALUES (4,2001,3000);INSERT INTO salgrade VALUES (5,3001,9999);--事务提交COMMIT;