oracle 创建emp表，dept表

「爱情、让人受尽委屈。」 2023-10-07 17:49 40阅读 0赞

废话不多说 直接上SQL

/*创建empz表*/
    CREATE TABLE EMP
    (EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7, 2),
    COMM NUMBER(7, 2),
    DEPTNO NUMBER(2)
    );
    
    /*emp表添加数据*/
    INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,
    '17-12月-1980', 800, NULL, 20);
    INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,
    '20-2月-1981', 1600, 300, 30);
    INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,
    '22-2月-1981',  1250, 500, 30);
    INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,
    '2-4月-1981', 2975, NULL, 20);
    INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,
    '28-9月-1981',  1250, 1400, 30);
    INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,
    '1-5月-1981', 2850, NULL, 30);
    INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,
    '9-6月-1981',  2450, NULL, 10);
    INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,
    '09-12月-1982',  3000, NULL, 20);
    INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,
    '17-11月-1981',5000, NULL, 10);
    INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,
    '8-9月-1981',  1500, 0, 30);
    INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,
    '12-1月-1983',  1100, NULL, 20);
    INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,
    '3-12月-1981', 950, NULL, 30);
    INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,
    '3-12月-1981', 3000, NULL, 20);
    INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,
    '23-1月-1982', 1300, NULL, 10);
    
    /*创建dept表*/
    CREATE TABLE DEPT
    (DEPTNO NUMBER(2),
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13)
    );
    
    /*dept表添加数据*/
    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 ');
    
    /*添加约束条件*/
    alter table emp add constraint emp_pk primary key(empno);
    alter table dept add constraint dept_pk primary key(deptno);
    alter table dept add constraint emp_fk_dept foreign key(deptno) references dept;
    alter table emp add constraint emp_fk_emp foreign key(mgr) references emp;