Oracle下SQL基本操作(三)(3)

---||串联 将多列作为一列
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