Oracle下SQL基本操作(二)

----------------------------建表，与插入数据
--创建职务表
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;