oracle从入门到精通第二篇（运算符|常用函数|排序|分组）

运算符：

+ - * / mod(x,y)
      select mod(12,5) from dual;

与空判断：

is null：是空
is not null：非空

select name,birthday from student where birthday is null;

多行函数：组函数：聚簇函数：聚组函数

max() 最大值 min() 最小值 avg() 平均值 sum() 求和 count() 求记录数

求最大值

select max(salary) from student;

求最小值

select min(salary) from student;

求平均值

select avg(salary) from student;

求和

select sum(salary) from student;

求记录数

select count(*) from student;
select count(birthday) from student;

练习：

求出emp表中最高工资，最低工资，平均工资

单行函数：

ceil()：返回大于等于x的最小整数

floor()：返回小于等于x的最大整数

 select floor(12.3) from dual;

round()：四舍五入

 select round(12.3) from dual; 
 select round(3.141592653,4) from dual;

trunc()：直接截断

select trunc(12.3) from dual;
select trunc(3.141592653,4) from dual;

sign()：求符号位 正数：1 负数：-1 零返回0

select sign(666) from dual;

abs()：求绝对值

select abs(-666) from dual;

power(a,b)：求a的b次方

select power(2,3) from dual;

sqrt()：求正平方根

select sqrt(16) from dual;

日期函数：

日期可以加减运算（整数） 单位：天
两个日子是没法相加

sqlplus中修改会话日期展示格式：
alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
永久修改方法：详见环境变量

Java：yyyy-MM-dd HH:mm:ss
Oracle：yyyy-mm-dd hh24:mi:ss

yyyy 年 year 年
mm 月 month 带‘月’的月份
ddd 日 年中的日
dd 日 月中的日
d 日 周中的日
hh24 时 24小时制
hh 时 12小时制
mi 分
ss 秒
xff 毫秒
ff3 毫秒保留三位

add_months(日期，整数值)：在某个日子上添加多个月

select add_months(sysdate,3) from dual;

months_between(a1,a2)：两个日子之间有多少个月份
计算公式：a1-a2

select months_between(sysdate,to_date('20200126121212','yyyy-mm-dd hh24:mi:ss')) from dual;

next_day(日期，周中的某天)：查找出下一个周几

select next_day(sysdate,'星期三') from dual;

last_day()：计算当前给定日期所在月份的最后一天

select last_day(sysdate) from dual;

练习：
查询各月倒数第3天入职的员工信息

转换函数：

to_number()：将一个字符类型的数值转换成数值类型

select name,email from student where to_number(email) = 1;

---

to_char()：将数值类型转换成字符类型

select name,salary from student where to_char(salary) = '5400';

常用在货币单位，格式化字符串

select to_char(123456789,'999,999,999,999.99') from dual;

日期转换to_char(日期,‘yyyy-mm-dd’)

select to_char(sysdate,'yyyy-mm-dd') from dual;

to_date(字符类型的日期，日期格式)：

select to_date('20200202202020','yyyy-mm-dd hh24:mi:ss') from dual;

日期区间查询

SELECT NAME
	,
	birthday 
FROM
	student 
WHERE
	birthday BETWEEN to_date ( '20200201', 'yyyy-mm-dd' ) 
	AND to_date ( '20200205', 'yyyy-mm-dd' );

SELECT NAME
	,
	birthday 
FROM
	student 
WHERE
	birthday >= to_date ( '20200201', 'yyyy-mm-dd' ) 
	AND birthday <= to_date ( '20200205', 'yyyy-mm-dd' );

练习：
java给你一个字符型’20200202202020’
year yue ri
2020 02 02

字符函数：

lower()：转换成小写 upper()：转换成大写 initcap()：首字母大写 length()：求长度

select ename,lower(ename),upper(ename),initcap(ename),length(ename) from emp;

substr(a1,a2,a3)：截取字符串 a1：原字符串 a2：从哪个位置开始截取 a3：截取长度 默认截取到最后

select substr('woshizhizhuxia',3) from dual;
select substr('woshizhizhuxia',3,9) from dual;

replace(a1,a2,a3)：替换 a1：原字符串 a2：被替换的字符串 a3：替换的字符串

select replace('woshizhizhuxia','z') from dual;
select replace('woshizhizhuxia','z','*') from dual;

instr(a1,a2,a3,a4)：索引字符串 a1：原字符串 a2：希望找到的字符 a3：从哪开始找 默认是1 a4：第几次出现 默认是1

select instr('woshizhizhuxia','z',5,2) from dual;
select instr('woshizhizhuxia','z',5) from dual;
select instr('woshizhizhuxia','z') from dual;

concat(a1,a2)：拼接字符串

select concat(ename,sal) from emp;

字符串拼接野路子 ||

select '123' || '456' from dual;

练习：
查询手机号，中间四位以‘’代替 例如：1335678
添加手机号：13356785678，18888888888，13867899876

lpad(a1,a2,a3)：左侧补全 rpad(a1,a2,a3)：右侧补全 a1：原字符串 a2：补全到多少位 a3：用什么来补全

select lpad(sal,11,'138') from emp;

trim()：去除两侧空格 trim(a1 from a2)：默认把a2的两侧去除a1

 select trim('a' from 'aaaababaaa') from dual;

ltrim()：左侧去除 rtrim()：右侧去除

select ltrim('         abc') from dual;
select ltrim('         abc',' ') from dual;

通用函数：

nvl(字段，是空展示什么)：空值处理

select ename,comm,nvl(comm,0) from emp;

nvl2(字段，不是空展示什么，是空展示什么)：空值处理二代

select ename,comm,nvl2(comm,comm,0) from emp;

练习：
15查询员工姓名的第2个字母为“M”的员工信息

排序：order by

排序字段：desc 降序 asc 升序 默认升序

select ename,sal from emp order by sal desc;
select ename,deptno,sal from emp order by deptno asc,sal desc;

分组：group by 聚合统计

根据在某一个列上或者多个列上相同的值将他划分为一个组，该表就被分为多个组
如果以字段A分组，那么只能查询字段A，或者用组函数的形式统计其他字段

select deptno,count(ename) from emp group by deptno;
select deptno,ename from emp group by deptno,ename;

having 条件

select deptno,count(ename) from emp group by deptno having count(
ename) >= 5;

distinct：去重

distinct：去重，支持单列多列的去重

select distinct deptno from emp;
select distinct deptno,job from emp;

查询关键字的优先级：

select 列名 – 优先级高于order by
from 表名 – 优先级最高
连表查询
where 条件 – 优先级次高
group by 分组 – 优先级次于where
having 条件 – 优先级一定在group by之后
order by 排序 – 优先级最低

select  deptno from emp 
where job= 'MANAGER' 
group by deptno having count(ename) >=5 
order by deptno asc;