ITPub博客

首页 > 数据库 > Oracle > 最常用的2个数据表emp dept

最常用的2个数据表emp dept

原创 Oracle 作者:Michael_DD 时间:2014-02-26 16:44:24 0 删除 编辑
CREATE TABLE EMP (
  EMPNO number(4) NOT NULL,
  ENAME varchar2(10) NOT NULL,
  JOB varchar2(9) NOT NULL,
  MGR number(4),
  HIREDATE DATE NOT NULL,
  SAL number(7,2) NOT NULL,
  COMM number(7,2),
  DEPTNO number(2)
); 

数据库会报告一个ORA-01843的错误:
alter session set nls_date_language='american';
alter session set NLS_DATE_FORMAT='DD-MON-YY';


INSERT INTO emp VALUES ('7369', 'SMITH', 'CLERK', '7902', '17-DEC-90', '13750', '', '20');
INSERT INTO emp VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '20-FEB-89', '19000', '6400', '30');
INSERT INTO emp VALUES ('7521', 'WORD', 'SALESMAN', '7698', '22-FEB-93', '18500', '4250', '30');
INSERT INTO emp VALUES ('7566', 'JONES', 'MANAGER', '7839', '02-APR-89', '36850', '', '20');
INSERT INTO emp VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '28-SEP-97', '15675', '3500', '30');
INSERT INTO emp VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '01-MAY-90', '24000', '', '30');
INSERT INTO emp VALUES ('7782', 'CLARK', 'MANAGER', '7839', '09-JUN-88', '27500', '', '10');
INSERT INTO emp VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '19-APR-87', '19500', '', '20');
INSERT INTO emp VALUES ('7839', 'KING', 'PRESIDENT', '', '17-NOV-83', '82500', '', '10');
INSERT INTO emp VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '08-SEP-92', '18500', '6250', '30');
INSERT INTO emp VALUES ('7876', 'ADAMS', 'CLERK', '7788', '23-MAY-96', '11900', '', '20');
INSERT INTO emp VALUES ('7900', 'JAMES', 'CLERK', '7698', '03-DEC-95', '12500', '', '30');
INSERT INTO emp VALUES ('7902', 'FORD', 'ANALYST', '7566', '03-DEC-91', '21500', '', '20');
INSERT INTO emp VALUES ('7934', 'MILLER', 'CLERK', '7782', '23-JAN-95', '13250', '', '10');
INSERT INTO emp VALUES ('3258', 'GREEN', 'SALESMAN', '4422', '24-JUL-95', '18500', '2750', '50');
INSERT INTO emp VALUES ('4422', 'STEVENS', 'MANAGER', '7839', '14-JAN-94 ', '24750', '', '50');
INSERT INTO emp VALUES ('6548', 'BARENS', 'CLERK', '4422', '16-JAN-95', '11950', '', '50');
INSERT INTO emp VALUES ('7500', 'CAMPBELL', 'ANALYST', '7566', '30-OCT-92 ', '24500', '0', '40');



CREATE TABLE DEPT (
  DEPTNO number(2) NOT NULL,
  DNAME char(10) ,
  LOC char(10)
);

INSERT INTO dept VALUES ('10', 'ACCOUNTING', 'LONDON');
INSERT INTO dept VALUES ('30', 'SALES', 'LIVERPOOL');
INSERT INTO dept VALUES ('40', 'OPERATIONS', 'STAFFORD');
INSERT INTO dept VALUES ('50', 'MARKETING', 'LUTON');
INSERT INTO dept VALUES ('20', 'RESEARCH', 'PERSTON');


大批量生成数据:
INSERT INTO DEPT SELECT *  FROM DEPT;

INSERT INTO EMP SELECT * FROM EMP;


oracle 11g 里面的emp等sample表
执行以下语句即可:

$oracle_home/rdbms/admin/utlsampl.sql


点击(此处)折叠或打开

  1. Rem Copyright (c) 1990, 2006, Oracle. All rights reserved.
  2. Rem NAME
  3. REM UTLSAMPL.SQL
  4. Rem FUNCTION
  5. Rem NOTES
  6. Rem MODIFIED
  7. Rem lburgess 04/02/06 - lowercase passwords
  8. Rem menash 02/21/01 - remove unnecessary users for security reasons
  9. Rem gwood 03/23/99 - make all dates Y2K compliant
  10. Rem jbellemo 02/27/97 - dont connect as system
  11. Rem akolk 08/06/96 - bug 368261: Adding date formats
  12. Rem glumpkin 10/21/92 - Renamed from SQLBLD.SQL
  13. Rem blinden 07/27/92 - Added primary and foreign keys to EMP and DEPT
  14. Rem rlim 04/29/91 - change char to varchar2
  15. Rem mmoore 04/08/91 - use unlimited tablespace priv
  16. Rem pritto 04/04/91 - change SYSDATE to 13-JUL-87
  17. Rem Mendels 12/07/90 - bug 30123;add to_date calls so language independent
  18. Rem
  19. rem
  20. rem $Header: utlsampl.sql 02-apr-2006.21:13:01 lburgess Exp $ sqlbld.sql
  21. rem
  22. SET TERMOUT OFF
  23. SET ECHO OFF

  24. rem CONGDON Invoked in RDBMS at build time. 29-DEC-1988
  25. rem OATES: Created: 16-Feb-83
  26.  
  27. DROP USER SCOTT CASCADE;
  28. DROP USER ADAMS CASCADE;
  29. DROP USER JONES CASCADE;
  30. DROP USER CLARK CASCADE;
  31. DROP USER BLAKE CASCADE;
  32. GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;
  33. DROP PUBLIC SYNONYM PARTS;

  34. CONNECT SCOTT/tiger
  35. CREATE TABLE DEPT
  36.        (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  37.     DNAME VARCHAR2(14) ,
  38.     LOC VARCHAR2(13) ) ;
  39. CREATE TABLE EMP
  40.        (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  41.     ENAME VARCHAR2(10),
  42.     JOB VARCHAR2(9),
  43.     MGR NUMBER(4),
  44.     HIREDATE DATE,
  45.     SAL NUMBER(7,2),
  46.     COMM NUMBER(7,2),
  47.     DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
  48. INSERT INTO DEPT VALUES
  49.     (10,\'ACCOUNTING\',\'NEW YORK\');
  50. INSERT INTO DEPT VALUES (20,\'RESEARCH\',\'DALLAS\');
  51. INSERT INTO DEPT VALUES
  52.     (30,\'SALES\',\'CHICAGO\');
  53. INSERT INTO DEPT VALUES
  54.     (40,\'OPERATIONS\',\'BOSTON\');
  55. INSERT INTO EMP VALUES
  56. (7369,\'SMITH\',\'CLERK\',7902,to_date(\'17-12-1980\',\'dd-mm-yyyy\'),800,NULL,20);
  57. INSERT INTO EMP VALUES
  58. (7499,\'ALLEN\',\'SALESMAN\',7698,to_date(\'20-2-1981\',\'dd-mm-yyyy\'),1600,300,30);
  59. INSERT INTO EMP VALUES
  60. (7521,\'WARD\',\'SALESMAN\',7698,to_date(\'22-2-1981\',\'dd-mm-yyyy\'),1250,500,30);
  61. INSERT INTO EMP VALUES
  62. (7566,\'JONES\',\'MANAGER\',7839,to_date(\'2-4-1981\',\'dd-mm-yyyy\'),2975,NULL,20);
  63. INSERT INTO EMP VALUES
  64. (7654,\'MARTIN\',\'SALESMAN\',7698,to_date(\'28-9-1981\',\'dd-mm-yyyy\'),1250,1400,30);
  65. INSERT INTO EMP VALUES
  66. (7698,\'BLAKE\',\'MANAGER\',7839,to_date(\'1-5-1981\',\'dd-mm-yyyy\'),2850,NULL,30);
  67. INSERT INTO EMP VALUES
  68. (7782,\'CLARK\',\'MANAGER\',7839,to_date(\'9-6-1981\',\'dd-mm-yyyy\'),2450,NULL,10);
  69. INSERT INTO EMP VALUES
  70. (7788,\'SCOTT\',\'ANALYST\',7566,to_date(\'13-JUL-87\',\'dd-mm-rr\')-85,3000,NULL,20);
  71. INSERT INTO EMP VALUES
  72. (7839,\'KING\',\'PRESIDENT\',NULL,to_date(\'17-11-1981\',\'dd-mm-yyyy\'),5000,NULL,10);
  73. INSERT INTO EMP VALUES
  74. (7844,\'TURNER\',\'SALESMAN\',7698,to_date(\'8-9-1981\',\'dd-mm-yyyy\'),1500,0,30);
  75. INSERT INTO EMP VALUES
  76. (7876,\'ADAMS\',\'CLERK\',7788,to_date(\'13-JUL-87\', \'dd-mm-rr\')-51,1100,NULL,20);
  77. INSERT INTO EMP VALUES
  78. (7900,\'JAMES\',\'CLERK\',7698,to_date(\'3-12-1981\',\'dd-mm-yyyy\'),950,NULL,30);
  79. INSERT INTO EMP VALUES
  80. (7902,\'FORD\',\'ANALYST\',7566,to_date(\'3-12-1981\',\'dd-mm-yyyy\'),3000,NULL,20);
  81. INSERT INTO EMP VALUES
  82. (7934,\'MILLER\',\'CLERK\',7782,to_date(\'23-1-1982\',\'dd-mm-yyyy\'),1300,NULL,10);
  83. CREATE TABLE BONUS
  84.     (
  85.     ENAME VARCHAR2(10) ,
  86.     JOB VARCHAR2(9) ,
  87.     SAL NUMBER,
  88.     COMM NUMBER
  89.     ) ;
  90. CREATE TABLE SALGRADE
  91.       ( GRADE NUMBER,
  92.     LOSAL NUMBER,
  93.     HISAL NUMBER );
  94. INSERT INTO SALGRADE VALUES (1,700,1200);
  95. INSERT INTO SALGRADE VALUES (2,1201,1400);
  96. INSERT INTO SALGRADE VALUES (3,1401,2000);
  97. INSERT INTO SALGRADE VALUES (4,2001,3000);
  98. INSERT INTO SALGRADE VALUES (5,3001,9999);
  99. COMMIT;
  100. EXIT






来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29500582/viewspace-1092353/,如需转载,请注明出处,否则将追究法律责任。

上一篇: linux笔试题
下一篇: 笔记mysql优化
请登录后发表评论 登录
全部评论

注册时间:2014-02-22

  • 博文量
    326
  • 访问量
    2460438