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;
---||串联 将多列作为一列
SELECT EMP.NAME || ' 的月薪是 ' || EMP.SALARY INFO FROM EMPLOYEE EMP;
---算术表达式
SELECT EMP.NAME, EMP.SALARY * 12 年薪 FROM EMPLOYEE EMP;
---WHERE 子句
---->
SELECT EMP.NAME,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY > 1600;
---BETWEEN AND
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY BETWEEN 1600 AND 2500;
----NOT BETWEEN AND
SELECT EMP.NAME,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY NOT BETWEEN 1600 AND 2500;
----IN
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY IN(1600,2001,2004);
---NOT IN
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY FROM EMPLOYEE EMP WHERE EMP.SALARY NOT IN(1600,2003);
----IS NULL
UPDATE EMPLOYEE SET MANAGERID = NULL WHERE EMPLOYEE.EMPLOYEEID = '01';
SELECT * FROM EMPLOYEE EMP WHERE EMP.MANAGERID IS NULL;
--- IS NOT NULL
SELECT * FROM EMPLOYEE EMP WHERE EMP.MANAGERID IS NOT NULL;
---LIKE
--SELECT * FROM EMPLOYEE;
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME LIKE '%刚';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME LIKE '_刚';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME LIKE '%刚%';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME LIKE '_刚_';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME LIKE '%刚_';
---NOT LIKE
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME NOT LIKE '%刚%';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME NOT LIKE '%刚';
SELECT * FROM EMPLOYEE EMP WHERE EMP.NAME NOT LIKE '%刚_';
---AND
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY,EMP.COMMISSION FROM EMPLOYEE EMP WHERE EMP.SALARY >1600 AND EMP.COMMISSION >=1500;
---OR
SELECT FROM EMPLOYEE EMP WHERE EMP.EMPLOYEEID = '02'OR EMP.NAME = '刚果';
---ORDER BY
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY,EMP.COMMISSION FROM EMPLOYEE EMP
ORDER BY SALARY;
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY,EMP.COMMISSION FROM EMPLOYEE EMP
ORDER BY EMP.SALARY ,EMP.COMMISSION DESC;
---CASE 子句
--SELECT * FROM EMPLOYEE;
SELECT EMP.EMPLOYEEID,EMP.NAME,EMP.SALARY, CASE WHEN EMP.EMPLOYEEID = '02' THEN EMP.SALARY + 400
WHEN EMP.EMPLOYEEID = '03' THEN EMP.SALARY + 7
ELSE EMP.SALARY
END 加薪后
FROM EMPLOYEE EMP
UPDATE EMPLOYEE EMP SET EMP.SALARY = CASE WHEN EMP.EMPLOYEEID = '02' THEN EMP.SALARY + 400
WHEN EMP.EMPLOYEEID = '03' THEN EMP.SALARY + 7
|