oracle 5-1 字符函数
--upper函数
- select * from emp job = upper('salesman');
--lower函数
select *from emp where lower(job) = 'clerk';
--initcap函数（首字母大写，其余小写）
select empno,initcap(ename) from emp;
以上，当函数内为空，返回值也为空
--字符函数
concat('hello','oracle')返回结果hellooracle
~~select ename||':'||sal from emp;
等价于 select concat(concat(ename,':'),sal);
substr('hello',1,3)从1位开始保留3位，即截取
substr('hello',0,3)从0位也是第一位开始，Oracle中0位不是很严格，似乎全从1位
substr('hello',-1,3)从尾部-1位截取3位，即str
~~select * from emp where substr(job,1,4)='SALE';
length('o r acle')计算字符串长度，共8位，因为空格也算。
~~select * from emp where length(ename)=6;
instr('hello oracle','oracle')结果：7 从第一位h起搜索oracle，得到结果7.
~~select instr('hello oracle','oracle')from dual;dual是系统提供的虚拟表
instr('hello oracle hello oracle','oracle',1,2)结果20. 从第一位，此处由1指定搜索位置，2为搜索子串的第二次出现次数。即从一位开始搜索字串第二次出现的位置。
rpad('hello',10,'#')设定填充后总长度为10，从hello右端开始填充5个#。
lpad('hello',10,'#')///从左侧填充。
~~select rpad(job,9,'*') from emp;
replace('hello oracle','oracle','world')结果：hello word 提换字符Oracle，用Word替换掉Oracle.
~~select replace('hello oracle','oracle','world') from dual;//dual为系统虚拟表
5-2数值函数
Round(25.328)结果25
Round(25.328,2)结果25.33
Round(25.328,-1)结果30
~~select round(sal/30,2) from emp;
Trunc(25.328)截取数字 结果25
Trunc(n,[m])小数点后的m位
Trunc(25.328,2)截取数字 结果25.32
Trunc(25.328,-1)截取数字 结果20
~~select trunc(sal/30,2) from emp;
Mod(25,6)求模（余数） 结果 1
Mod(25,0)用零，则返回结果 25
~~select * from emp where mod(empno,2)=0;搜索员工号位偶数的所有信息
5-3日期函数
sysdate
~~select sysdate-1 昨天,sysdate 今天,sysdate+1 明天 from dual;显示一个表
Months_between(d1,d2)返回的d1和d2相差的月数，d1大于d2结果为正，否则为负数
~~select months_between(sysdate,hiredate) from emp;
~~select round(months_between(sysdate,hiredate)/12) from emp;求员工入职年数
add_months(m,n)
~~select ename,add_months(hiredate,30*12) from emp;求员工入职后30年的日期时间
next_day(d,char)返回d日期后第一个char的时间
~~select sysdate,next_day(sysdate,'星期一') from dual;返回当前时间后下一个周一对应的日期
此处只能用monday和星期一两种格式且必须与日期语言匹配
Last_day(d)返回特定日期所在月份的最后一天
~~select sysdate,last_day(sysdate) from dual;
~~select empno,ename,last_day(hiredate) as 入职月份最后一天 from emp;查员工入职月份的最后一天
~~select empno,ename,hiredate from emp where hiredate=last_day(hiredate);查每月最后一天入职的员工
Round(d[,fmt])返回日期四舍五入结果 d指定日期 fmt指定四舍五入方式 fmt为'YEAR'则7月1日为分界线，为'MONTH',则16日为分界线
~~系统日期为20-7月-17 round(sysdate,'YEAR') 结果01-1月-18
~~ round(sysdate,'MONTH')结果 1-8月-17
Trunc(d[,fmt])截断日期，fmt为'YEAR则结果为年度1月1日，fmt为'MONTH'则结果为本月1日
5-4转换函数
oracle隐式数据转换
VARCHAR2或char可以转换为NUMBER或DATE
NUMBER或DATE可以转换为VARCHAR2
可见不能方便转换的是char类型
显示转换
To_char(d,[,fmt[,'nlsparams']])日期转换，d为指定日期，fmt为指定日期格式（默认日期格式为：'D''D-MON-RR'），nlsparams为指定日期语言（格式：’NLS_DATE_LANGUAGE=AMERICAN‘）
~~select to_char(hiredate,'DD-MON-RR','NLS_DATE_LANGUAGE=AMERICAN') From emp;DD-MM-RR可以变换年和日的顺序，也可以在其内加字符’RR“年”MON“月”DD“日”‘
~~select to_char(hiredate,'YYYY"年"MM"月"DD"日"') from emp;格式中追加字符串，要用双引号括起来。RR和YY为世纪不同
TO_char(n[,fmt])n用于指定数值，fmt用于指定数字格式的模型，如：9 显示数字且忽略前导0 0 显示数字，位数不足，用o补齐 . 在指定位置显示小数点 ，在指定位置显示逗号 $ 在数字前加美元符号 L在数字前加本地货币符号
~~select sal,to_char(sal,'L0,000,000.00') from emp;
~~select sal,to_char(sal,'$99,999,999.99') FROM EMP;注意是忽略前导零，数字后的不忽略，如整数的小数点后的零位。
TO_DATE(char[,fmt[,'nlsparams']])用于将字符串换成日期类型的数据，char为匹配日期数据的字符串，fmt用于指定日期格式模型，’nlsparams'用于指定日期语言。
~~select ename,hiredate from emp where hiredate >to_date('1981-12-31','YYYY-MM-DD'); 必须有'YYYY-MM-DD' 进行格式转换，即声明一个格式，我要用
TO_number(n[,fmt])将包含数字的字符串转换成数值类型，n是包含数字的字符串，fmt用于指定数字格式模型
~~select ename,sal from emp where sal>to_number('￥2000','L99999');必须用格式转换，否则得用默认格式
5-5通用函数
NVL(expr1,expr2)用于处理NULL，如果expr1非空则返回expr1，空则返回expr2
~~select ename,sal,comm,sal+nvl(comm,0) from emp;
NVL2(expr1,expr2,expr3)如果expr1不是null，则返回expr2，是null则返回expr3.
~~select ename,sal,comm,nvl2(comm,comm+sal,sal) from emp;
NULLIF(expr1,expr2)该函数用于比较expr1和expr2，相等则返回空，不等则返回expr1.
~~select empno,ename,hiredate,nullif(hiredate,trunc(sysdate,'month')) as 去除入职首日员工from emp;
Coalesce(expr1[,expr2][,...]) 返回表达式列表中中第一个not null表达式的结果
~~select ename,sal,comm,coalesce(sal+comm,sal) from emp;
5-6条件表达式
CASE expr when comparison_expr1 then return_expr1
[when comparison_expr2 then return_expr2
.....
when comparison_exprn then return_exprn
else else_expr]
end
~~select empno,ename,
case job
when 'clerk' then '办事员'
when 'salesmen' then '销售'
when 'manger' then '经理'
when 'analyst' then '分析员'
else '总裁'
end
as 职位
from emp;将英文职位替换为中文职位
~~select empno,ename,sal,case when sal<2000 then '低'
when sal<5000 then '中'
else '高'
end使用取键判断，且没有case后的表达式低中高顺序依然不可颠倒，因为是依次判断
from emp；
decode(col|expression,search1,result1,
[search2,result2,...]
[,default])
~~select empno,enmae,job,decode(job,'clerk','办事员','salesman,'销售员',manger,'经理','总裁') from emp;decode和case差不多，但不可用于条件判断。
5章皆为单行函数
select ename，empno，to_char(next_day(add_months(hiredate,3),'星期一'),'YYYY-MM-DD') as 员工入职三个月后第一个周一的时间 from emp;
select empno,ename,sal,to_char(round(sal/30,2),'l9,999,999,99) from rmp//员工日工资转换
第六章 数据分组函数
在关系数据库中，使用数据分组可以取得表数据的汇总信息。数据分组是通过分组函数、group by 以及having等子句共同实现的。
分组函数会自动忽略空值，但nvl函数会使分组函数无法忽略空值
SELECT [COLUMN,] group funciton(column),…
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column]; 语句结构
avg() 求平均值
~~select avg(sal) from emp;
sum() 求和
~~select sum(sal) from emp；
min() 求最小值
~~select min(sal) from emp;
max() 求最大值；
~~select max(sal) from emp;
count() 计数
~~select count(*) from emp;
select count(empno) from emp;
select count(distinct deptno) from emp;///去除重复项，count会算上重复项
wm_concat行转列（让查询结果行转列） 该函数可以把列值以逗号分隔起来，并显示成一行。
~~select wm_concat(ename) from emp;所有结果在一个字符段里
~~select sum(sal)/count(*) 方法一,sum(sal)/count(sal) 方法二,avg(sal) 方法三 from emp;求员工平均工资
~~select sum(comm)/count(*) 方法一,sum(comm)/count(comm) 方法二,avg(comm) 方法三 from emp;求员工补助 方法一中count（*） 会计算含空值的行
~~select count(*),count(nvl(comm,o)) from emp; 这时用nvl可以计算含null值得行
6-2分组数据
group by 将表中得数据分成若干组
~~select deptno,avg(sal) from emp group by deptno;用部门分组得到部门平均工资
~~select deptno,job,avg(sal) from emp group by deptno,job order by deptno;
所有未包含在组函数的参数，都应该在group by里这是oracle的规定。此处若不用分组，则会报错。
~~select avg(sal) from emp group by deptno;求部门平均工资
~~select empno,wm_concat(ename) from emp group by deptno;求部门编号及员工姓名以逗号分隔在一行中
6-3having
~~select deptno,avg(sal) from emp group by deptno having avg(sal)>2500; ///在group中要对结果进行过滤，求平均工资大于2500的部门，此处不能用where条件，会报错。因为不能在where子句中使用组函数avg sum等，但可以在having中使用组函数。
~~select max(avg(sal)) from emp group by deptno;
7-1多表查询-笛卡尔集
多表获取数据
~~select empno,ename,员工表.deptno,部门表.deptno,dname from 部门表,员工表 显示两部门的笛卡尔集
~~select empno,ename,员工表.deptno,部门表.deptno,dname from 部门表,员工表 where 部门表.deptno=员工表.deptno;对两表笛卡尔集进行筛选，得到部门名不错误的员工，不筛选的话，部门名会不一致。
7-2等值连接和不等值连接
~~select e.empno,e.ename,e.job,d.name,d.deptno from emp e,dept d where e.deptno = d.deptno and e.deptno=10;多表查询笛卡尔集，并用表的别名 e和d 且条件设置deptno相等和 e的deptno为10
~~select e.empno,e.e name,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal; 查询员工号，姓名，工资及其等级。 并使用不等值查询，e.sal between s.losal and s.hisal;
7-3外连接
外连接是标准连接的扩展，它不仅会返回满足连接条件的所有记录，而且会返回不满足连接条件的部分记录
~~select d.deptno,d.dname,count(e.empno) from dept d,emp e where d.deptno=e.deptno(+) group by d.deptno,d.dname;计算部门人数，显示部门号，部门名，和员工人数。此处d.deptno=e.deptno(+)采用外连接查询，显示e.deptno的满足条件部分，显示d.deptno所有部分。
7-4自连接
自连接实质是将同一张表看成是多张表。
~~select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;显示员工机器上级的姓名，用同一表的两个别名的manger号和雇员号相等为条件，进行搜索。
7-8SQL1999连接查询
支持其他数据库查询
SELECT table1.column_name,table2.column_name FROM table1 [CROSS JOIN table2]|[NATURAL JOIN table2]|[JOIN table2 USING (column_name)]|[JOIN table2 ON (table1.column_name=table2.column_name)]|[LEFT|RIGHT|FULL JOIN table2 ON (table1.column_name=table2.column_name)];
交叉连接 CROSS JOIN 产生笛卡尔集
自然连接 NATURAL JOIN 特殊的等价连接，将表中具有相同名称的列自动进行记录匹配。自然连接不必指定任何同等连接条件。
内连接（只返回满足条件的数据） JOIN table2 USING(column_name)指定共用列名，列数据一致进行匹配 JOIN table2 ON (table1.column_name=table2.column_name) JOIN 指定连接表 ON指定连接条件
左|右（外）连接：返回左|右边全部记录，反之符合条件的记录
完全（外）连接：返回满足条件的数据，以及不满足连接条件的左右表其他数据
7-9SET运算符
set集合操作符专门用于合并多条SELECT语句结果，列明，数量和类型需要对应包括：UNION/UNION ALL （1、2、3和1、2、2、3的区别，重复数据不去除）并集 INTERSECT 交集 MINUS（返回前者减去后者重复项的数据，不重复，则返回前者之全部） 差集
~~create table emp01 as select * from emp where deptno in(10,20);创建一个表，并复制emp的deptno10到20号的所有数据给它。
create table emp02 as select *from emp where deptno in(20,30);
select deptno,empno,ename from emp01 union select deptno,empno,ename from emp02;显示所有数据，去除重复数据，且一第一列为准升序排序10，20，30
select deptno,empno,ename from emp01 union all select deptno,empno,ename from emp02;//显示所有数据，不去出重复行，不排序10，20，20，30 排序可以使用ORDER BY 1 一为列位置，也可以使用列名称。
...intersect..，显示重复数据，且排序20
..minus...。显示第一个结果集中存在，第二个结果集不存在的数据，且排序10
8-1子查询
子查询是指嵌入在其他SQL语句中的SELECT语句，也称为嵌套查询
~~create table empnew as select *from emp;将emp中检索的数据赋值给empnew
~~select *from emp where job = (select job from emp where ename = 'SMITH');在where子句中使用子查询，搜索job为smith job的所有信息
~~select deptno,dname,loc,(select count(empno) from emp where emp.deptno = dept.deptno) cnt from dept;在SELECT子句中使用子查询，查询每个部门的编号、名称、位置、部门人数
~~select deptno,avg(sal) from emp group by deptno having avg(sal) >(select max(sal) from emp where deptno=40);在HAVING子句中使用子查询，查询员工的部门编号，条件是平均工资大于30号部门的最高工资。
~~select ename,job,sal from emp,(select deptno,avg(sal) avgsal from emp group by deptno) dept where emp.deptno = dept.deptno and sal> avgsal;在FROM子句中使用子查询，查询显示高于平均工资的雇员信息
8-2子查询与主查询关系
~~select * from emp where job =(select job from emp where empno =7521) and sal>(select sal from emp where empno = 8934);子查询多个使用
**一般先执行子查询，但当子查询需要引用主查询的表列时，Oracle会执行相关子查询。相关子查询是先执行主查询，再执行子查询。**
8-3单行子查询
单行子查询只返回一行数据。使用单行比较操作符 = >= <= < > <>
~~select ename,sal,deptno from emp where deptno = (select deptno from emp where ename = 'JAMES') and ename <> 'JAMES';查询并显示和JAMES同部门的其他员工信息
~~select ename,job,sal from emp where sal = (select max(sal) from emp group by deptno);非法使用单行子查询，因为其返回了多行数据。
8-4多行子查询
多行子查询是指返回多行数据的子查询语句。使用多行比较操作符 in等于列表中的任何一个 all和子查询返回的所有值进行比较 any和子查询返回的任一值进行比较
~~select * from emp where deptno in (select deptno from dept where loc = 'NEW YEAR' OR 'CHIAGO');查询工作地点再纽约和芝加哥部门的所有信息
~~select ename, sal,deptno from emp where sal > all(select sal from emp where deptno = 30);///查询高于30号部门所有员工工资的员工名、工资、部门名，和单行操作符 > 结合使用。等价于下列单行子查询
~~select ename,sal,deptno from emp where sal> (select max(sal) from emp where deptno =30);
~~select ename,sal,deptno from emp where sal > any (select sal from emp where deptno =10);查询高于10号部门任意一个员工工资的员工名、工资和部门号，须和单行比较操作符连用 > 。等价于下列单行子查询
~~select ename，sal,deptno from emp where sal >(select min(sal) from emp where deptno =10);
8-5 子查询注意的问题
不可以在group by 子句中使用子查询
在TOP-N分析问题中，须对子查询排序
~~select rownum ,empno,ename from empnew where rownum <=5;用系统自带的rownum来控制显示行的数量为5；
~~select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<=5;显示员工中工资u最高的五位员工。先在子查询中根据sal进行降序排序，后在主查询中进行rownum限制。（对结果先排序，再取）
单行子查询和多行子查询注意空值问题。’
单行子查询空值问题
~~select ename,job from emp where job = (select job from emp where ename = 'RUBY');ruby不存在，子查询返回空值，而条件判断是用 is null 和is not null 判断的。所以主查询不会有任何结果
多行子查询空值问题
~~select * from emp where empno not in(select mgr from emp); mgr中有null，null的处理只能用is null 和is not null 判断，所以where 条件false了，主查询不返回任何值。可以这样处理
~~select * from emp where emp not in (select mgr from emp where mgr is not null);
9-1 视图
CREATE [OR REPLACE] VIEW view [(alias[,alias]…)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY]
简单视图
~~CREATE VIEW emp_view AS SELECT empno,ename,sal,FROM emp;建立用于查询员工号、姓名、工资的视图，即虚拟的表（需要有权限 GRANT create view to scott）
~~select * from emp_view;查询视图，默认列名和as select 后的列名一致，也可以这样指定
~~create view emp_view2(员工号,姓名,工资) as select empno,ename,sal from emp;
连接视图
~~CREATE VIEW dept_emp_view AS SELECT d.deptno,d.dname,e.empno,e.ename,e.job FROM dept d,emp e WHERE dept d,emp e WHERE d.deptno = e.deptno AND d.deptno =10;建立用于获得部门号为10的部门号、部门名称及员工信息
只读视图
~~CREATE VIEW emp_view AS SELECT * FROM emp WHERE deptno = 10 WITH READ ONLY;
9-2Oracle视图查询与修改
视图上的DML操作，注意针对视图的更新操作（insert，update，delete）实际改变的是基表的数据
~~select * from empnew_view;查询视图
~~insert into empnew_view(empno,ename,sal) values(8888,'LAYNA',6666);添加数据
~~update empnew_view set sal = sal+100 where empno =8888;修改数据
~~delete from empnew_view where empno=8888;删除数据
在创建视图时定义check约束
WITH CHECK OPTION 选项用于在视图上定义CHECK约束
~~CREATE VIEW empnew_view AS SELECT * FROM empnew WHERE deptno = 20 WITH CHECK OPTION CONSTRAINT chk_view;deptno=20,这一条件须满足，添加和更新数据时
修改视图
~~CREATE OR REPLACE VIEW emp_view AS SELECT * FROM emp WHERE JOB = 'SALESMAN';修改视图
删除视图
~~drop view emp_view;删除视图，但不会删除基表的数据
复杂视图
指包含函数、表达式或者分组数据的视图，它主要用于执行查询操作（当定义复杂视图时，必须要为函数或表达式定义列别名）
~~CREATE VIEW job_view(job,avgsal,sumsal,maxsal,minsal) AS SELECT job,avg(sal),sum(sal),max(sal),min(sal) FROM emp GROUP BY job; 创建用于获得每个岗位平均工资、工资总和、最高工资和最低工资的视图。也可以采用这种列的别名方式。
~~create view job_view as select job job,avg(sal) avgsal,sum(sal) sumsal,max(sal) maxsal,min(sal) minsal from emp group by job with read only;另一种列名方式，添加只读可避免以下DML操作错误
复杂视图包含以下元素之一不能执行delete操作：
group by子句
分组函数
distinct关键字
rownum伪列
视图中包含以下元素之一不能执行update操作：
group by子句
分组函数
distinct关键字
rownum伪列
使用表达式定义的列
视图中包含以下元素之一不能执行insert操作：
group by 子句
分组函数
distinct关键字
rownum伪列
使用表达式定义的列
视图上没有包含基表的not null列
10-1序列
序列是一种用于自动生成唯一数字的数据库对象。主要用于提供主键值
CREATE SEQUENCE sequence //序列名
[INCREMENT BY n] //每次增长的数值（步长）
[START WITH n] //从哪个值开始（初始值）
[{MAXVALUE n}|NOMAXVALUE}]//序列最小值
[{MINVALUE n}|NOMINVALUE}]//序列最大值
[{CYCLE|NOCYCLE}]///是否循环
[{CACHE n|NOCACHE}]//是否缓存
~~create sequence deptno_seq start with 50 increment by 10 maxvalue 70 cache 3;///创建序列
使用序列时，必须通过伪列NEXTVAL和CURRVAL来引用序列。NEXTVAL用于返回下一个序列值自50起，即60 deptno_seq.nextval CURRVAL用于返回当前序列值 deptno_seq.currval
~~insert into deptnew2(deptno,dname,loc)values(deptno_seq.nextval,'text_dname','text_loc');//第一次使用序列，须用.next_val
~~select deptno_seq.currval from dual;///查询当前序列值
SELECT sequence_name,min_value,max_value,increment_by,cycle_flag,cache_size,last_number FROM USER_SEQUENCES;///查询数据字典视图 USER_SEQUENCES 获取序列定义信息，如果指定不包括cache，则last_number显示的是序列中下一个值，如果包括cache选项，则显示的是cache缓存最后一个序列的下一个值
ALTER SEQUENCE sequence [INCREMENT BY n]//每次增长的数值
[{MAXVALUE n|NOMAXVALUE}]
[{MINVALUE n|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}]
DROP SEQUENCE sequence_name;///删除序列
10-2索引
索引是为了加速对表中数据行的检索而创建的一种存储结构，不索引则扫描全表数据
CREATE INDEX index_name on table (column[,column]...);///在一个列多个列创建索引
~~create index idx_ename on emp(ename);单列索引
~~create index idx_deptno_job on emp(deptno,job);///复合索引
~~create unique index idx_dname on dept(danme);//唯一索引，索引列的值不能重复
什么时候应当创建索引：where子句经常引用的表列上；为了提高多表连接的性能，应该在连接列上建立索引；经常排序的列上创建索引，可以加快数据排序速度。
什么时候不能创建索引：表很小；列不经常作为连接条件或出现在where子句中；表经常更新
可以使用数据字典视图 USER_INDEXES和USER_IND_COLUMNS查看索引信息。
SELECT uic.index_name,uic.column_name,uic.column_position,ui.uniqueness FROM user_index ui,user_ind_columns uic WHERE uic.index_name=ui.index_name AND ui.table_name = 'EMP';查询索引
DROP INDEX index_name;///删除索引
10-3 同义词
同义词是数据库方案对象的一个别名
方案对象：表、索引、视图、触发器、序列、同义词、存储过程等。 方案名和用户名一致
非方案对象：表空间、用户、角色等
比如：stu是表student_info的同义词。
同义词作用：一：简化对象访问。二:提供对象访问安全性。多用户协同开发中，可以屏蔽对象的名字及其持有者。
创建同义词
CREATE PUBLIC SYNONYM synonym for [schema.]object;///创建公共同义词
CREATE SYNONYM synonym FOR [schema.]object;///创建私有同义词 //schema是方案名
查讯同义词
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
