---创建部门表
CREATE TABLE DEPARTMENT(
DEPTID VARCHAR2(2),
DEPTNAME VARCHAR2(30),
LOCATION VARCHAR2(30),
MANAGERID VARCHAR2(4),
CONSTRAINTS DEP_ID_PK PRIMARY KEY (DEPTID)
);
---创建员工表
CREATE TABLE EMPLOYEE(
EMPLOYEEID VARCHAR2(4),
NAME VARCHAR2(40) NOT NULL,
HIREDATE DATE,
SALARY NUMBER(7,2),
COMMISSION NUMBER(7,2),
MANAGERID VARCHAR2(4),
DEPTID VARCHAR2(2),
POSITIONID VARCHAR2(2),
QUALID CHAR(1),
CONSTRAINTS EMP_ID_PK PRIMARY KEY (EMPLOYEEID),
CONSTRAINTS EMP_DEP_FK FOREIGN KEY(DEPTID) REFERENCES DEPARTMENT(DEPTID),
CONSTRAINTS EMP_POS_FK FOREIGN KEY(POSITIONID) REFERENCES POSITION(POSITIONID),
CONSTRAINTS EMP_QUA_FK FOREIGN KEY(QUALID) REFERENCES QUAFICATION(QUALID)
);
--DROP TABLE EMPLOYEE;
----添加 员工表EMPLOYEE 外键,参照原表
ALTER TABLE EMPLOYEE
ADD CONSTRAINTS EMP_MAN_FK FOREIGN KEY(MANAGERID) REFERENCES EMPLOYEE(EMPLOYEEID);
----------------添加 部门表DEPARTMENT 外键,参照 EMPLOYEE 表
ALTER TABLE DEPARTMENT
ADD CONSTRAINTS DEP_MAN_FK FOREIGN KEY(MANAGERID) REFERENCES EMPLOYEE(EMPLOYEEID);
---SELECT * FROM USER_CONSTRAINTS;查看约束
SELECT * FROM EMPLOYEE FOR UPDATE;
----INSERT POSITION
INSERT INTO POSITION(POSITIONID,POSITIONNAME) VALUES('01','程序员');----指定列
INSERT INTO POSITION VALUES('02','组长');---不指定列，则要给所有的列指定值
INSERT INTO POSITION VALUES('03','经理');
INSERT INTO POSITION VALUES('04','总裁');
----SELECT *FROM POSITION;
----INSERT EMPLEVEL
INSERT INTO EMPLEVEL VALUES('01',1000,2000);
INSERT INTO EMPLEVEL VALUES('02',2001,3000);
INSERT INTO EMPLEVEL VALUES('03',3001,4000);
INSERT INTO EMPLEVEL VALUES('04',401,5000);
----SELECT *FROM EMPLEVEL;
---INSERT QUAFICATION
INSERT INTO QUAFICATION VALUES('1','学士');
INSERT INTO QUAFICATION VALUES('2','博士');
INSERT INTO QUAFICATION VALUES('3','硕士');
----SELECT * FROM QUAFICATION;
----INSERT DEPARTMENT
INSERT INTO DEPARTMENT VALUES('01','教学部','教学部办公室',NULL);
INSERT INTO DEPARTMENT VALUES('02','市场部','市场部办公室',NULL);
INSERT INTO DEPARTMENT VALUES('03','教务部','教务部办公室',NULL);
INSERT INTO DEPARTMENT VALUES('04','资助部','资助部办公室',NULL);
----SELECT * FROM DEPARTMENT;
----INSET EMPLOYEE
INSERT INTO EMPLOYEE VALUES('01','张琴',TO_DATE('2009-10-15','YYYY-MM-DD'),
1500,600,NULL,NULL,NULL,NULL);
-----以下两条记录主要是用于在SELECT时，DISTINCT
----与下面的记录相同,EMPLOYEENAME,SALARY
INSERT INTO EMPLOYEE VALUES('02','张五',TO_DATE('2005-6-5','YYYY-MM-DD'),
1600,800,NULL,NULL,NULL,NULL);
-----与上面的记录相同
INSERT INTO EMPLOYEE VALUES('06','张五',TO_DATE('2004-6-5','YYYY-MM-DD'),
1600,900,NULL,NULL,NULL,NULL);
----以下四条记录主要是为了LIKE
INSERT INTO EMPLOYEE VALUES('03','杨刚',TO_DATE('2003-7-7','YYYY-MM-DD'),
2003,1000,NULL,NULL,NULL,NULL);
INSERT INTO EMPLOYEE VALUES('07','刚',TO_DATE('2000-5-12','YYYY-MM-DD'),
2450,1120,NULL,NULL,NULL,NULL);
INSERT INTO EMPLOYEE VALUES('04','杨刚荣',TO_DATE('2001-9-8','YYYY-MM-DD'),
3000,1500,NULL,NULL,NULL,NULL);
INSERT INTO EMPLOYEE VALUES('05','刚果',