Oracle查询和表管理总结

Oracle函数以及查询总结

Oracle函数

基本格式：
select 函数名 from dual;

列举部分函数:

1.数字函数    round(number,小数位数正负都行) 四舍五入    如： round(125.315) would return 125     trunc(number,小数位数) 截断     如： trunc(-125.815, 2) would return -125.81     如： trunc(125.815, -1) would return 120 2.字符函数    substr(a,b) 从a中，第b为开始    substr(a,b,c) 从a中，第b开始，取c个    length(a) 字符长度    lengthb(a) 字节长度    instr(a,b) 在a中查询b的位置    lpad(a,n,c) 左填充    rpad(a,n,c) 右填充    trim('H' from 'HsssH') 去掉前后指定的字符    replace('hello','l','*') 替换3.日期函数    sysdate : 系统时间（格式），格式化显示时间，to_char(sysdate,'格式') 格式：yyyy-mm-dd hh24:mi:ss    systimestamp: 系统时间戳 ：精度更高，格式化显示时间，to_char(sysdate,'格式') 格式：yyyy-mm-dd  hh24:mi:ss:ff    日期的运算： 加减（整数天），sysdate-1            如： 计算员工工龄             sysdate-hiredate 天             months_between(sysdate,hiredate) 实际的月             add_months 加月 add_months(sysdate,n) n可正负             last_day(sysdate) 月最后一天              next_day  下一个星期 如： next_day(sysdate,'星期一') 下个星期一的日期             round(number,小数位数正负都行) 四舍五入 针对日期函数             round(to_date ('22-AUG-03'),'YEAR') would return '01-JAN-04'              trunc(number,小数位数) 截断 针对日期函数                trunc(to_date('22-AUG-03'), 'YEAR') would return '01-JAN-03' 4.转换函数    隐式函数：  deptno='10' deptno=10 hiredate='yyyy-mm-dd'    number--> character  to_char(sal,'L9,999.99') 人民币格式字符串    character--->number  to_number('99.99')    date---->character  to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天"day')    character-->date        to_date('2016-11-23','yyyy-mm-dd')5. 聚组函数    avg  求平均值        select avg(sal) from emp; 所有员工平均工资    sum  求和        select sum(sal) from emp; 所有员工工资之和    count 求行数        select count(1) from emp; 所有员工的数量    min  求最小值        select min(sal) from emp; 最少工资多少    max  求最大值        select max(sal) from emp; 最大工资多少6.其他函数    nvl(e1,e2)    nvl2(e1,e2,e3) e1为null 返回e3 否则返回e2    nullif(a,b) 当a=b的时候 返回null  否则返回a    coalesce(e1,e2,e3,,,) 从左到右 找第一个不为null的值    decode函数        语法：            decode(exp,ser1,res1,ser2,res2,,,,defualt);        案例：            decode(job,'manager',sal+1000,,,,,sal+100);        decode函数的原理是case语句：            语法：                case  exp                    when  com_epr then return_exp                    when  com_epr then return_exp                    when  com_epr then return_exp                    else  return_exp                end;            案例：                select  sal 涨前,                  case job                         when 'manager' then sal + 100                        when ....                        else sal+300                  end  涨后7.行转列函数    wm_concat函数  如：    select deptno ,wm_concat(ename) names,wm_concat(sal) sals from emp group by deptno;    col sals for a50; 列宽    col names for a50;  

实践:

dual 单行单列,主要用于函数查询
验证: select * from dual;

隐式转换函数
alter session set NLS_DATE_FORMAT = ‘yyyy-mm-dd’;
select * from emp where hiredate = ‘1981-02-20’;–把字符串转成日期

nvl(col,val) –如果col列为null,返回val值
select nvl(comm,0) from emp;
select sal+comm from emp;–null无穷大,任何值和null运算都是null
select sal+nvl(comm,0) from emp;
nvl2(e1,e2,e3)

Oracle查询

分组查询

按列或者函数分组，分组后select后面只能跟分组之后的列，或聚合函数，或子查询

统计每年入职的员工人数

select to_char(hiredate,’yyyy’) y, count(1) from emp group by to_char(hiredate,’yyyy’);

多表查询

取的信息存在于多表中,多表查询或者子查询

内连接(左内连接,右内连接)

外连接(左外连接,右外连接,全外连接)

左外连接:数据以左表为准,右边没有的记录用null填充

右外连接:数据以右表为准,左边没有的记录用null填充

全外连接:所有记录都会显示,没有的记录用null填充

自连接

需求:emp查询所有经理信息

select * from emp where empno in (select mgr from emp);
select distinct e1.* from emp e1,(select mgr from emp) e2 where e1.empno=e2.mgr;

子查询

从行列角度子查询主要有三种：

单行子查询

单行子查询常用的操作符号：> < >= <= = <>

查询出高于10部门平均工资的员工信息select * from emp where sal > (select avg(sal) from emp where deptno=10);

多行子查询

常用的操作符号:in (not in) any all exists
注意:子查询中 null not in(null) 统计不出

select * from dept where exists(select 1 from emp where emp.deptno=dept.deptno)-- exists(子查询) 子查询有行数 true 子查询无行数 false

多列子查询

和10号部门同名同工作的员工信息：select * from emp where (ename,job) in (select ename,job from emp where deptno = 10)

子查询的位置

1. select 后面子查询(必须是单行单列子查询)

2. from 后面子查询

3. where 后面子查询

4. having 后面子查询

5. 在DDL中使用子查询

6. insert 后面子查询

rownum: oracle 中的伪列,rownum是动态计算出来的,只能从1开始计算.

rowid用法:去掉重复行应用

delete from t_data ta1 where rowid > (select min(rowid) from t_data ta2 where ta1.t1 = ta2.t1 and ta1.t2 = ta2.t2)select * from t_data;

结果排序

order by的特点：

order by 后面 + 列，表达式，别名，序号（列序号从1开始）如果排序多列，按顺序排（先排第一个列，如果有desc 是针对一个列的，如果每个列都降序，那么每列都加desc）如果排序的列有多个null值，降序排序的时候，null显示在前面，有数据的按降序显示在null的后面如果要显示在null的前面，那么需要在原sql语句的后面加 nulls last (原因是null值最大)   

集合运算

union 并集：两个集合取并集，自动排序，去掉重复行

select ename,sal,deptno from emp where sal > 1000unionselect ename,sal,deptno from emp where deptno = 30;

union all 并集：两个结合取并集，不排序，不去掉重复行

select ename,sal,deptno from emp where sal > 1000union allselect ename,sal,deptno from emp where deptno = 30;

intersect 交集：第一个集合减第二个集合

select ename,sal,deptno from emp where sal > 1000intersectselect ename,sal,deptno from emp where deptno = 30;

minus 差集：第一个集合减第二个集合

select ename,sal,deptno from emp where sal > 1000minusselect ename,sal,deptno from emp where deptno = 30;

特点： 列的个数要一致
不够的列可以用null来代替

表的管理

授权

grant(授权)

revoke(取消授权)

授予scott用户创建视图的权限
grant create view to scott;

视图

视图:就是查询结果的封装,并不真正的存储数据,数据还是存在表中
针对复杂查询的封装

create view v_empdept as select ename,sal,dname,loc from emp,dept,where emp.deptno=dept.deptno;

索引

索引:

作用:提高查询效率

缺点:会反向影响增删改的效率

实际开发:分析查询和增删改对比,数据量大，经常按某列做查询条件，给该列创建索引,如果经常做增删改操作，不建议创建索引

B树索引(有序的二叉树)

针对数据重复不大

给emp表的ename列创建了B树索引

create index emp_name_index on emp(ename);

位图索引

create table t_index (t_id integer,t_sex char(2),t_age integer,t_city varchar2(20));

函数索引

create index in_emp_hiredate on emp(to_char(hiredate,’yyyy-mm-dd’));

序列

pl/sql工具可视化创建

在内存中，回滚 关机 不连续

REATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 2000000 MINVALUE 1 CACHE 20;

同义词

别名

创建同义词

create synonym hh for emp;//hh相当于员工表

表的高级管理

表的五大约束:

1.主键约束

alter table t_index add constraint pk_t_index_pri primary key(t_id);

2.外键约束

alter table t_index add tp_id integer;

alter table t_index add constraint fk_index

3.唯一约束

alter table t_student add constraint unique_stu_name unique(t_name);

4.非空约束

alter table t_student modify t_name not null;

5.检查约束

alter table t_student add constraint ck_sex check(t_sex in (‘男’,’女’));

表的三大范式:(作用:衡量数据库设计是否完善)
1.所有的列都是不可分割的最小单元
2.所有的非主键列都完全依赖主键列
3.非主键列之间不存在传递依赖

设计原则:
1.每张表描述一个实体(对象)
2.给每张表创建代理主键
3.确定数据与数据之间的关系（一对一，一对多，多对多）