老师oracle讲义第三天

-----------------------函数补充------------------------

查看当前oracle自动提交状态
show autocommit ;
修改自动提交状态(这是一个会话级的环境变量)
set autocommit on ;
set autocommit off ;

手动提交
commit;
回滚 (autocommit off)
rollback;

----------------------------------------
char 定长字符串 最大2000字节 GBK编码 1000个汉字 可以不指定长度 默认为1
char(10) 等价于 char(10 byte)
也可以这样定义 char(10 char)
varchar2变长字符串 最大4000字节 GBK编码 2000个汉字 必须指定长度
varchar2(100) 等价于 varchar2(100 byte)
也可以这样定义 varchar2(100 char)

----------------------------------------------------
日期常用函数
last_day(date)返回日期date所在月的最后一天
select last_day(sysdate) from dual ;
add_months(date,i)返回日期date加上i个月后的日期值
i取正值整数 小数：截取整数后再运算 负数：减去i个月的日期值

months_between(date , date_) 返回两个日期间隔多少个月 结果可能是负数和小数（可以用ceil函数直接进位）

next_day(date , 周几) 返回date日期数据的下一个周几的日期 ，例如 4 即下一个周四的日期
Sunday （周日）Monday（周一）Tuesday（周二）Wednesday（周三）Thursday（周四）Friday （周五）Saturday（周六）
January一月； February二月； March三月； April 四月； May 五月；June 六月；July七月；August 八月；September 九月；October 十月；November 十一月；December搜索十二月

least(1,3,55,100,2,24)

greatest(1,88,2,33,55)
也被称作比较函数，参数类型必须一致
第二个参数类型会被转向第一参数类型 不能转换就报错

extract(year/month/day from date) 从参数datetime中提取参数date指定的数据类型，例如提取年、月、日
例如：select extract(year from sysdate) from dual ;

 

null和任何值进行运算,返回的都是null
select * from tablename where column1 = null ; //对于null 不能这样进行比较
select * from tablename where column1 is null ;
select * from tablename where column1 is not null ;
空值函数
nvl(col_null , col_not_null) 将null值转变成非null值处理 请保持两个参数的类型一致
例如：select sal , comm , sal + nvl(comm , 0 ) as 'salary' from emp ;

nvl2(col_null , col_is_null , col_is_not_null) 将null值转变成实际值 作用有点像三目表达式
如果是null 转成col_is_null ，如果不是null，转成col_is_not_null
select sal , comm , sal+nvl2( comm , 0 , 1000 ) as 'salary' from emp ;

--------------------------------------------------------------------------------

基本查询语句
any 和 all
不能单独使用 需要与比较符号配合使用
>any 大于最小的
select * from emp where sal >any (1000,2000,3000) ;
<any 小于最大的
>all 大于最大的
<all 小于最小的

distinct 过滤重复关键字

聚合函数
即 数据统计
max(col)
min(col)
avg(col)
sum(col)
count(col/*)
当使用count(column) 时,该字段含null值,计数器会将该null值的这一列忽略,所以会造成统计数据有误差
其中的空值可以使用nvl 和 nvl2函数处理
select count(nvl(sal,0)) from emp ;

select *
from tablename
where id = 1
order by sal ;

查询语句执行顺序
from子句
执行顺序从后往前，从右到左，故数据量较少的表尽量放在后面
select t1.col , t2.col from t1 , t2 where t1.id = t2.id and t1.sal > 1000;
where 子句
执行顺序为自上而下，从右到左，故将能过滤掉最大数量记录的条件写在where子句的最右
group 子句
执行顺序从左往右分组，故最好将group by 前使用where将不需要的记录在group by 之前过滤掉
having 子句
消耗资源 尽量避免使用，having会在检索出所有记录之后才对结果集进行过滤，需要排序等操作。

group by 子句 分组
当希望得到每个部门的平均薪水，而不是整个机构的平均薪水
把整个数据表按部门分成一个个小组，每个小组中包含一行或多行数据，在每个小组中在使用分组函数进行计算，每组返回一个结果
划分的小组由多少，最终的结果集行数就有多少

having 子句
having子句用来对分组后的结果进一步限制，比如按部门分组后，得到每个部门的最高薪水，可以继续限制输出结果。
必须跟在group by 后面，不能单独存在


select子句
少用*号，尽量取字段名称，oracle在解析的过程中，通过查询数据字典将*号依次转换成所有的列名，消耗时间
order by 子句
执行顺序从左到右，消耗资源
order by 子句 对数据按一定规则进行排序操作 必须是查询语句的最后一个子句
ASC(ascending )升序（默认） DESC(descending )降序
null值视作最大
多列排序：
首先按照第一列进行排序，如果第一列数据相同，再以第二列排序，以此类推
例如：select ename , deptno , sal from emp order by deptno asc , sal desc ;