SCOTT/TIGER demonstration schema (demobld.sql)

来源：互联网发布：苹果手机赌博软件编辑：程序博客网时间：2024/05/16 17:39

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 表示删除与此主键关联的外键）