----------------------------建表，與插入數據
--創建職務表
CREATE TABLE POSITION(
? POSITIONID VARCHAR2(2),
? POSITIONNAME VARCHAR2(20),
? CONSTRAINTS POS_ID_PK PRIMARY KEY (POSITIONID)
);
--創建工資級別表
CREATE TABLE EMPLEVEL(
? LEVELNO VARCHAR2(2),
? LOWSALARY NUMBER(7,2),
? HIGHSALARY NUMBER(7,2),
? CONSTRAINTS EMP_NO_PK PRIMARY KEY (LEVELNO)
);

--創建學位表
CREATE TABLE QUAFICATION(
??? QUALID CHAR(1),
??? QUALNAME VARCHAR2(10),
??? CONSTRAINTS QUA_ID_PK PRIMARY KEY (QUALID)
);

--創建部門表
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),
? EMPLOYEENAME 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_DEPTID_FK FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT(DEPTID),
? CONSTRAINTS EMP_PID_FK FOREIGN KEY (POSITIONID) REFERENCES POSITION(POSITIONID),
? CONSTRAINTS EMP_QUALID_FK FOREIGN KEY (QUALID) REFERENCES QUAFICATION(QUALID)
);

---------添加 員工表EMPLOYEE 外鍵,參照原表
ALTER TABLE EMPLOYEE
?ADD CONSTRAINTS EMP_MID_FK FOREIGN KEY (MANAGERID) REFERENCES EMPLOYEE(EMPLOYEEID);

---------添加 部門表DEPARTMENT 外鍵,參照 EMPLOYEE 表
ALTER TABLE DEPARTMENT
?ADD CONSTRAINTS DEP_MAN_FK FOREIGN KEY (MANAGERID) REFERENCES EMPLOYEE(EMPLOYEEID);

------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('0',1000,2000);
INSERT INTO EMPLEVEL VALUES('1',2000,3000);

----select * from EMPLEVEL;

---INSERT QUAFICATION
INSERT INTO QUAFICATION VALUES('1','學士');
INSERT INTO QUAFICATION VALUES('2','博士');

------select * from QUAFICATION;

-----INSERT DEPARTMENT
INSERT INTO DEPARTMENT VALUES('01','企化部','組長辦公室',NULL);
INSERT INTO DEPARTMENT VALUES('02','管理部','經理辦公室',NULL);

----select * from DEPARTMENT;

----INSERT EMPLOYEE
INSERT INTO EMPLOYEE VALUES(01,'小張',TO_DATE('2009-09-01','YYYY-MM-DD'),1500,600,NULL,NULL,NULL,NULL);

----與下面的記錄相同,EMPLOYEEID,SALARY
INSERT INTO EMPLOYEE VALUES(02,'小李',TO_DATE('2009-09-02','YYYY-MM-DD'),1600,300,NULL,NULL,NULL,NULL);

-----與上面的記錄相同
INSERT INTO EMPLOYEE VALUES(03,'小李',TO_DATE('2009-09-02','YYYY-MM-DD'),1600,400,NULL,NULL,NULL,NULL);

INSERT INTO EMPLOYEE VALUES(04,'小勇',TO_DATE('2009-09-03','YYYY-MM-DD'),2100,500,NULL,NULL,NULL,NULL);

INSERT INTO EMPLOYEE VALUES(05,'小勇',TO_DATE('2009-09-03','YYYY-MM-DD'),null,500,NULL,NULL,NULL,NULL);

INSERT INTO EMPLOYEE VALUES(06,'小剛',null,2100,500,NULL,NULL,NULL,NULL);

INSERT INTO EMPLOYEE VALUES(07,'小華',null,2100,500,NULL,NULL,NULL,NULL);
---select * from EMPLOYEE;

--update EMPLOYEE
UPDATE? EMPLOYEE SET COMMISSION = 300;
UPDATE EMPLOYEE SET COMMISSION = 200 WHERE EMPLOYEEID = 1;
UPDATE EMPLOYEE SET COMMISSION = 400 WHERE EMPLOYEEID = 3;
UPDATE EMPLOYEE SET COMMISSION = 500 WHERE EMPLOYEEID = 4;
UPDATE EMPLOYEE SET COMMISSION = 200 WHERE EMPLOYEEID = 2;
UPDATE EMPLOYEE SET EMPLOYEENAME = '小胖' WHERE EMPLOYEEID = 4;
UPDATE EMPLOYEE SET EMPLOYEENAME = '張小華' WHERE EMPLOYEEID = 1;
UPDATE EMPLOYEE SET EMPLOYEENAME = '李小華' WHERE EMPLOYEEID = 2;
UPDATE EMPLOYEE SET EMPLOYEENAME = '大小華華' WHERE EMPLOYEEID = 3;
UPDATE EMPLOYEE SET COMMISSION = 1000 WHERE EMPLOYEEID = 6;

---DELETE? POSITION
DELETE POSITION ;
DELETE POSITION WHERE POSITIONNAME = '組長';
DELETE POSITION WHERE POSITIONID = '02';
---select * from POSITION;

--SELECT EMPLOYEE
SELECT * FROM EMPLOYEE;
SELECT EMP.EMPLOYEEID,EMP.EMPLOYEENAME? FROM EMPLOYEE EMP;
SELECT EMP.EMPLOYEEID ID,EMP.EMPLOYEENAME 姓名? FROM EMPLOYEE EMP;
SELECT EMP.EMPLOYEEID ID,EMP.EMPLOYEENAME 姓名? FROM EMPLOYEE EMP WHERE EMP.SALARY = 1500;

---DISTINCT
SELECT? DISTINCT EMP.EMPLOYEENAME,EMP.SALARY FROM EMPLOYEE EMP;

----||串聯
SELECT EMP.EMPLOYEENAME||'的月薪是'||EMP.SALARY INFO FROM EMPLOYEE EMP;

---算術表達式
SELECT EMP.EMPLOYEEID,EMP.SALARY *12 年薪? FROM EMPLOYEE EMP;

?

---select * from EMPLOYEE;

---where子句 >
SELECT?? EMP.EMPLOYEEID,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY > 1600;

---BETWTEEIN AND
SELECT EMP.EMPLOYEEID, EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY BETWEEN 1500 AND 1660;

---IN
SELECT EMP.EMPLOYEENAME,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY IN(1399,1600);

---NOT IN
SELECT? EMP.EMPLOYEENAME,EMP.SALARY?? FROM EMPLOYEE EMP WHERE SALARY NOT IN(1600,3000);

---IS NULL
SELECT? EMP.EMPLOYEENAME, EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY IS NULL;

-----IS NOT NULL
SELECT EMP.EMPLOYEENAME, EMP.HIREDATE? FROM EMPLOYEE EMP WHERE EMP.HIREDATE IS NOT NULL;

---LIKE
SELECT??? EMP.EMPLOYEENAME???? FROM EMPLOYEE EMP? WHERE EMP.EMPLOYEENAME LIKE '小%';
SELECT??? EMP.EMPLOYEENAME???? FROM EMPLOYEE EMP? WHERE EMP.EMPLOYEENAME LIKE '%小%';
SELECT??? EMP.EMPLOYEENAME???? FROM EMPLOYEE EMP? WHERE EMP.EMPLOYEENAME LIKE '_小%';
SELECT??? EMP.EMPLOYEENAME???? FROM EMPLOYEE EMP? WHERE EMP.EMPLOYEENAME LIKE '_小_';

---NOT LIKE
SELECT? EMP.EMPLOYEENAME? FROM? EMPLOYEE EMP WHERE EMP.EMPLOYEENAME NOT LIKE '大%';

---AND
SELECT? EMP.EMPLOYEENAME,EMP.SALARY FROM EMPLOYEE EMP WHERE? EMP.SALARY > 1600 AND EMP.COMMISSION > 900;

---ORDER BY
SELECT EMP.EMPLOYEEID,EMP.EMPLOYEENAME,EMP.COMMISSION FROM EMPLOYEE EMP
ORDER BY EMP.COMMISSION;

SELECT? EMP.EMPLOYEEID,EMP.EMPLOYEENAME,EMP.SALARY,EMP.COMMISSION? FROM EMPLOYEE EMP
ORDER BY EMP.SALARY,EMP.COMMISSION DESC;

?

轉載于:https://blog.51cto.com/baiyan425/618040

總結

以上是生活随笔為你收集整理的Oracle下SQL基本操作(二)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。