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
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
用户名: 验证码:点击我更换图片
最新评论 更多>>

推荐热点

  • Table函数使用简介
  • Oracle数据库Constraint约束的常用操作及异常处理
  • Bulk Collect性能分析(zz)
  • export/import的使用
  • OCP043第十五讲 Database Security
  • ORACLE10gr2数据导入MySQL方案
  • oracle 让sys用户可以使用isqlplus
  • 在oracle数据库下使用iSQL*Plus DBA访问数据库
  • Oracle行列转换小结
网站首页 - 友情链接 - 网站地图 - TAG标签 - RSS订阅 - 内容搜索
Copyright © 2008-2015 计算机技术学习交流网. 版权所有

豫ICP备11007008号-1