Oracle-2-1基本SQL语句单行、通用和多行函数伪列.ppt

《Oracle-2-1基本SQL语句单行、通用和多行函数伪列.ppt》由会员分享，可在线阅读，更多相关《Oracle-2-1基本SQL语句单行、通用和多行函数伪列.ppt（29页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQLSQL语言语言SQLSQL语言语言pSQL全称是“结构化查询语言(Structured Query Language)”SQL是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。随着关系数据库系统和SQL语言应用的日益广泛，SQL语言的标准化工作也在紧张革进行着，十多年来已制订了多个SQL标准；p (1)1982年，美国国家标准化局（AMERICAN NATIONAL STANDARD INSTITUTE，简称ANSI）开始制定SQL标准；p (2)1986年，美国国家标准化协会公布了SQL语言

2、的第一个标准SQL86；p (3)1987年，国际标准化组织（ISO）通过了SQL86标准；p (4)1989年，国际标准化组织（ISO）对SQL86进行了补充，推出了SQL89标准；p (5)1992年，ISO又推出了SQL92标准，也称为SQL2；p (6)SQL99（也称为SQL3），增加了面向对象的功能。SQL语言包含4个部分：数据定义语言(DDL,Data Definition Language)，例如：CREATE、DROP、ALTER语句。数据操作语言(DML,Data Manipulation Language)，例如：INSERT、UPDATE、DELETE语句。数据查询语言

3、(DQL,Data Query Language SELECT)，例如：SELECT语句。数据控制语言(DCL,Data Control Language)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。书写SQL应遵循以下简单原则1.SQL语句不区分大小写2.SQL语句可以写成一行或多行3.关键字不能简写或分割于多行4.子句通常置于单独行这样更具可读性并便于编辑5.Tab和缩进的使用可以提高程序的可读性6.关键字最好大写，其他词诸如表名、列名使用小写SelectSelect语句语句Select语句功能强大，语法也比较复杂。完整的select语句由6个子句构成，分别为：

4、Select指定要获取表中哪些列数据From指定数据来自哪个（些）表Where指定获取哪些行数据Group by用于对表中数据进行分组统计Having 指定分组统计条件（跟group by一起使用！）Order by指定排序字段其中select和from子句是必选项，在select语句中同时包含where、group by、having、order by等多个子句，这些子句使用时是有一定顺序的，order by 必须是最后一条子句。算术操作符逻辑操作符比较操作符 连接操作符SQL操作符算术操作符p加(+)、减(-)、乘(*)、除(/)(1)SELECT sal,sal*1.1,ename FR

5、OM emp;(2)SELECT comm+200 FROM emp;SQL操作符SQL操作符 逻辑操作符 说明 x and y x和y全部为真 x or y x和y有一个为真 not x x的相反值 逻辑运算SELECT*FROM dept WHERE 1=1;SELECT*FROM dept WHERE 1=2;SELECT empno,ename,sal,job FROM emp WHERE sal=3000 AND JOB!=MANAGER;SELECT ename,sal,job FROM emp WHERE sal=3000 OR job=MANAGER;比较运算符（示例）p条件选

6、择(1)SELECT*FROM emp WHERE sal=5000;(2)SELECT*FROM emp WHERE job MANAGER;p复合条件(1)SELECT*FROM emp WHERE sal BETWEEN 3000 AND 6000;(2)SELECT*FROM emp WHERE job IN(MANAGER,CLERK);(3)SELECT*FROM emp WHERE job NOT IN(MANAGER);p 匹配空值（NULL）SELECT*FROM emp WHERE comm IS NOT NULL;NULL要注意的问题：SELECT*FROM emp WH

7、ERE empno NOT IN(SELECT mgr FROM emp WHERE mgr IS NOT NULL);SELECT*FROM emp WHERE empno NOT IN(SELECT mgr FROM emp);比较运算符（示例）pany的用法:any大于最小SELECT*FROM emp WHERE sal=ANY(3000,1500,2000,1200);/sal等于其中任何一个SELECT*FROM emp WHERE salANY(3000,1500,2000,1200);SELECT*FROM emp WHERE salANY(SELECT sal FROM em

8、p WHERE job=SALESMAN);p all的用法:all：大于所有，即大于最大SELECT*FROM emp WHERE sal=ALL(3000,1500,2000,1200);select empno,ename,sal,job from emp where salall(select sal from emp where job=SALESMAN);p模糊查询:_匹配1个字符、%匹配0到多个字符(1)SELECT ename FROM emp WHERE ename LIKE _H;(2)SELECT ename FROM emp WHERE ename LIKE A_;(3

9、)SELECT ename FROM emp WHERE ename LIKE A%;(4)SELECT ename FROM emp WHERE ename LIKE%A%;比较运算符比较操作符 说明 等于 或!=不等于 大于 =大于等于 ANY 任何一个值比较 ALL 所有的值比较 IN(NOT IN)匹配值列表 BETWEEN(NOT BETWEEN)匹配值范围 IS NULL(IS NOT NULL)匹配空值 LIKE(NOT LIKE)匹配字符串SQL操作符排序查询ORDER BY lOrder by指定排序字段(1)SELECT ename,sal FROM emp ORDER B

10、Y sal ASC;(2)SELECT ename,sal FROM emp ORDER BY sal DESC;(3)SELECT ename,empno,sal,deptno FROM emp ORDER BY deptno ASC,sal DESC,empno;分组查询lGroup by 用于对表中数据进行分组统计lHaving 指定分组统计条件（跟group by一起使用！）(1)SELECT job FROM emp GROUP BY job;(2)SELECT mgr,COUNT(mgr)FROM emp WHERE mgr IS NOT NULL GROUP BY mgr;(3)

11、SELECT deptno,COUNT(*)FROM emp GROUP BY deptno;(4)SELECT deptno,COUNT(1)FROM emp GROUP BY deptno HAVING COUNT(1)3;SQL操作符连接操作符连接操作符 说明说明|合并字符串连接操作符和别名问题：(1)SELECT empno|ename FROM emp;(2)SELECT empno|-|ename AS 姓名和编号 FROM emp;(3)SELECT empno|-|ename AS 姓名-编号 FROM emp;(4)SELECT empno|-|ename 编号 姓名 FRO

12、M emp;注意：引用标识符(Quoted Identifiers)：区分字符的大小写。用双引号(table)将标识符包围起来以后，就可以使用它们包含一些非标准的字符及空格，甚至还可以包含保留字其它情况是绝不允许包含保留字的。我们强烈反对使用这些非标准的标识符。取消重复DISTINCTpSELECT sal FROM emp ORDER BY sal DESC;pSELECT DISTINCT sal FROM emp ORDER BY sal DESC;pSELECT mgr FROM emp ORDER BY mgr;pSELECT DISTINCT mgr FROM emp ORDER

13、BY mgr;pSELECT job,sal,deptno FROM emp ORDER BY deptno,sal,job;pSELECT DISTINCT job,sal,deptno FROM emp ORDER BY deptno,sal,job;了解DUAL表pDual是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中。p注：在Oracle中select后必须要有from1.查看当前连接用户SELECT user FROM dual;2.查看系统日期SELECT SYSDATE FROM dual;3.当作计算器用SELECT 1+5*2 F

14、ROM dual;SQL函数p单行函数只对单行数据进行操作，并且每行只返回一个结果p多行函数对多数据行的群组进行操作，并且每组只返回一个结果单行函数p字符函数:分为大小写转换函数和字符处理函数。大小写转换函数包括：LOWER(str)将字符串转换成小写UPPER(str)将字符串转换成大写INITCAP(str)将字符串第一个字母转换成大写,后面的字母转成小写字符处理函数包括：CONCAT(str1,str2)连接两个字符串SUBSTR(str,m,n)取出字符串中从m位开始的n个字符的子串 m位为负数时从右数起LENGTH(str)求字符串长度INSTR(str1,str2,m,n)在str

15、1字符串中从第m位开始搜索str2字符串第n次出现的位置 m和n默认值为1RPAD和LPAD(粘贴字符)RPAD 在列的右边粘贴字符 LPAD 在列的左边粘贴字符SELECT lpad(gao,6,*)FROM dual;SELECT RPAD(ename,10,*)FROM emp;SELECT LPAD(RPAD(ename,8,*),10,$)name FROM emp;replace(str,seartch_string,replace_string)将str中查找seartch_string并且将其替换为replace_string TRIM单行函数p数值函数ROUND(n,m)将n

16、四舍五入,保留小数点m位Select round(11.1234,2)from dual;TRUNC(n,m)将n保留小数点m位，其余的小数直接截掉MOD(m,n)m除以n的余数，如n0，则返回mSelect mod(7,5)from dual;p日期函数TRUNC(date,t)如t为hh将保留小时截断分和秒select to_char(trunc(sysdate,hh),yyyy.mm.dd hh24:mi:ss)from dual;MONTHS_BETWEEN(date1,date2)返回的月份ADD_MONTHS(date,n)增加或减去月份（date+ndate1-date2)NEX

17、T_DAY(date,day)计算给出的日期date下一个星期day的日期select next_day(sysdate,星期二)from dual;算下星期二的日期select next_day(sysdate,2)from dual;计算下星期第二天的日期LAST_DAY(date)本月的最后一天Select last_day(sysdate)from dual;SELECT EXTRACT(MONTH FROM SYSDATE)FROM DUAL;单行函数p隐式转换:表达式中oracle自动完成转换VARCHAR2 or CHAR-NUMBERselect*from emp where

18、123=123;VARCHAR2 or CHAR-DATESELECT ename,hiredate FROM emp WHERE hiredate 03-12月-81;单行函数p数据类型的显式转换TO_CHAR(date,format)select to_char(sysdate,yyyy/mm/dd hh24:mi:ss)from dual;TO_NUMBER(str)将给出的数字字符转换为数字select to_number(1999)year from dual;TO_DATE(string,format)将字符串转化为ORACLE中的一个日期select to_date(188803

19、12,yyyymmdd)from dual;（SELECT DATE1999-1-2 FROM dual;-这种写法只能是年月日的形式）单行函数p通用函数NVL(v,value)如果v为null返回value的值,否则返回v的值select avg(nvl(comm,0)from emp;NVL2(v,v1,v2)如果v不为null则返回v1的值，如果v为null则返回v2的值select comm,nvl2(comm,comm*1.01,0)com from emp;COALESCE(expr1,expr2,expr3,exprN)若expr1值不为null,则返回expr1的值,若expr

20、1值为null则计算expr2值是否为null,若不为null则返expr2值,若为null则再计算expr3select ename,coalesce(sal+comm,sal,0)sl,sal,comm from emp;DECODE(value,if1,then1,if2,then2,if3,then3,.,else)Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果

21、value结果不等于给出的任何配对时，Decode 结果就返回else。SELECT ename,job,sal,DECODE(job,MANAGER,sal*0.5,SALESMAN,sal*0.1,sal*0)add_sal FROM emp;练习多行函数多行函数p思考:p用GROUP BY 进行数据分组(出现在select后的字段必须在group by 后出现)1.求每个部门的平均薪水2.求各部门相同工种员工的平均工资pHAVING子句的使用1.求员工人数多于4人的部门内员工的薪水和2.求各部门CLERK工种员工的平均工资,并以平均工资降序排列p(注：组函数的嵌套与单行函数不同，最多只能嵌套两层)补充：伪列补充：伪列(rowid(rowid和和rownum)rownum)pOracle 中伪列就像一个表列，但是它并没有存储在表中。伪列可以从表中查询，但不能插入、更新和删除它们的值pROWID返回表中行的存储地址，该地址可以唯一地标识数据库中的一行pROWNUM返回行在结果集中行的顺序号，可以用它限制返回的行数SELECT rowid,dept.*,rownum FROM dept;1.按工资降序排列2.显示工资降序排列后的前五条记录