oracle数据库（四）查询和单行多行函数

1查询

1.1范围查询： 数字/日期

between 小 and 大 （>=小 and <=大）

1.2模糊查询：like配合通配符使用：_ 表示 一个字符， % 表示任意个字符

1.2.1 姓名中第二个字母是M的员工信息：select *from emp where ename like ‘_M%’ ;

1.2.2姓名中包含M的员工信息：select *from emp where ename like ‘%M%’ ;

1.2.3姓名长度>6的员工信息： >6 >=7 select *from emp where ename like ‘_______%’ ;

1.2.4姓名中包含下划线的 zhang_san select *from emp where ename like ‘%_%’ escape ‘’ ;

not in 不能出现null：如果出现了null，结果为null（这是语法规定）

1.3 排序查询：order by 字段名|表达式|序号

1.3.1 select *from emp order by sal desc ;降序

1.3.2 select *from emp order by sal asc ;默认升序

1.3.3 select *from emp order by sal+10000 desc ;null默认是最大值 ，如果要让null在最后用 nulls last 例如：select empno,enmae,sal from emp order by sal desc nulls last;

2 函数—单行函数：一次操作一行 （select lower(job) from emp）。多行函数：一次操作多行（select count（*） from emp）

2.1单行函数： 字符函数 数值函数 日期函数 转换函数 通用函数

2.1.1字符函数 lower（全小写） upper（全大写） initcap（首字母大写）substr(str,begin,len) ：从1开始数length字符数 / lengthb 字节数 insrt(str,substr) lpad/ ：填充 trim：去掉任意字符 replace：替换

2.1.2数值函数 round(数字,n位数):四舍五入 ，保留n位小数 trunc(数字,n位数):舍尾，保留n位小数。 mod() 求余数

trunc(数字,n位数):舍尾，保留n位小数

2.1.3日期函数 sysdate：当前时间 to_char(日期,格式)，日期±数字 （默认是天），日期 - 日期 ，months_between(日期1,日期2) ： 日期1-日期2，当月最大是第几天 last_dayadd_months(日期,月数)，下一个星期n是哪一天next_day，round，trunc

2.1.4转换函数 隐式转换: 字符-数字 数字-字符 字符-日期日期-字符 显式转换：字符->字数select to_number(‘￥123,456.7’, ‘L999,999.9’) from dual ;字数->字符 字符-日期 日期-字符

2.1.5通用函数 a. nvl/nvl2b. nullif(a,b) :a=b,null ,否则返回c coalesce :从左往后 找到第一个不为null的值d.条件判断函数decode(字段,条件1，返回值1，条件2，返回2，…,最后表达式)，case when … then when … then else end ;

2.2多行函数（组函数、 聚合函数）求有几个部门

2.2.1count() 自动排除null max/min/avg/sum

2.2.2分组：各个部门总工资： 对部门分组 dept 10 20 30 40 select deptno,avg(sal) from emp 【group by deptno】 ;select deptno,ename , avg(sal) from emp group by deptno ;（错）

10 8000
20 6000
30 5000
40 9000

分组查询时，不在组函数(多行函数)中的列，必须在group by中。

先根据部门分组 ，再根据job分组

–各个部门中各个工作 的平均工资
select deptno,job,avg(sal) from emp group by deptno ,job ;

2.2.3对行筛选用where 对组进行筛选用Having 可以在Having使用多行函数count min avg 但是 不能在where中使用多行函数

3查询语句的各个注意事项

3.1 对查询出的结果集去重：distinct select distinct deptno from emp

3.2连接符： java: “hello”+“world”->“helloworld” oracle: concat ||

3.3dual：oracle提供的 学习时使用 临时表：单行单列select ‘hello’||‘world’ from dual;

3.4修改oracle默认的日期格式 默认：DD-MON-RR修改：alter session set NLS_DATE_FORMAT = ‘yyyy-mm-dd’ ; alter session set NLS_DATE_FORMAT = ‘DD-MON-RR’ ;