Oracle下SQL基本操作(三)(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','刚果',TO_DATE('2004-9-6','YYYY-MM-DD'),
2700,1200,NULL,NULL,NULL,NULL);
INSERT INTO EMPLOYEE VALUES('08','AA',TO_DATE('2004-9-6','YYYY-MM-DD'),
2700,1200,NULL,NULL,NULL,NULL);
INSERT INTO EMPLOYEE VALUES('09','BB',TO_DATE('2004-9-6','YYYY-MM-DD'),
2700,1200,NULL,NULL,NULL,NULL);
INSERT INTO EMPLOYEE VALUES('10','aa',TO_DATE('2004-9-6','YYYY-MM-DD'),
2700,1200,NULL,NULL,NULL,NULL);
 
----SELECT * FROM EMPLOYEE;
---DELETE以下为删除元组，也就是一组
DELETE FROM EMPLOYEE EMP WHERE EMP.EMPLOYEEID = '02';
---UPDATE EMPLOYEE
UPDATE EMPLOYEE SET MANAGERID = '01';--更改一列上所有的数据
UPDATE EMPLOYEE SET COMMISSION = 850 WHERE EMPLOYEEID = '02';
--DELETE POSTION
DELETE POSITION;--删除所有的记录
DELETE POSITION WHERE POSITIONNAME='程序员';
---SELECT EMPLOYEE
SELECT * FROM EMPLOYEE;
SELECT EMP.EMPLOYEEID ID,EMP.NAME 姓名   FROM EMPLOYEE EMP;--指定别名
SELECT  EMP.EMPLOYEEID ,EMP.NAME  FROM EMPLOYEE EMP WHERE EMP.NAME = '张五';
---DISTINCT 要在SELECT后
--可以看到张五只有一条记录了
SELECT DISTINCT EMP.NAME  姓名, EMP.SALARY 薪水  FROM EMPLOYEE EMP;