SCOTT/TIGER demonstration schema (demobld.sql)

最新推荐文章于 2019-05-28 13:47:11 发布

原创最新推荐文章于 2019-05-28 13:47:11 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

expert 专栏收录该内容

3 篇文章

订阅专栏

10g及以上版本没有$ORACLE_HOME\sqlplus\demo，就更没有demobld.sql 但是有\u01\app\product\11.2.0\dbhome_1\RDBMS\ADMIN\scott.sql可以用来参考。

搭建环境我用自己建立的用户，然后执行编辑好的脚本。

1.用户。

创建表空间 create tablespace herbert datafile 'D:\oradata\herbert\herbert01.dbf' 2048m

创建用户create user herbert identified by herbert;

grant dba to herbert;

alter user herbert default tablespace herbert ;

2.表与约束。

修改日期格式。

alter session set nls_date_language='american';

建表插入数据

CREATE TABLE DEPT
       (DEPTNO NUMBER(2) ,
  DNAME VARCHAR2(14) ,
  LOC VARCHAR2(13) ) ;

CREATE TABLE EMP
       (EMPNO NUMBER(4) ,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) ;
INSERT 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');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

修改约束

alter table emp add constraint emp_pk primary key (empno);
alter table dept add constraint dept_pk primary key(deptno);
alter table emp add constraint emp_fk_dept foreign key(deptno) references dept;
alter table emp add constraint emp_fk_emp foreign key(mgr) references emp;

注：

1.插入数据的客户端字符集是美国的，默认是中文的所以需要改一下，否则报错。

2.约束没弄好可以删掉重来：alter table emp drop constraint primary key cascade(cascade 表示删除与此主键关联的外键）