Oracle下SQL基本操作(三)(3)
来源:未知 责任编辑:责任编辑 发表时间:2013-11-26 22:16 点击:次
---||串联 将多列作为一列
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
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>