ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 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

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

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






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

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

注册时间：2014-02-22

  • 博文量
    326
  • 访问量
    2671974

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593