oracle初级（续）

　　　　　　　　　　　　　　　　　　　　　　　　　　有志者、事竟成，破釜沉舟，百二秦关终属楚；
　　　　　　　　　　　　　　　　　　　　　　　　　　苦心人、天不负，卧薪尝胆，三千越甲可吞吴。

oracle基本简单的用法，之前的笔记稍作整理一下，希望对各位有用，如有问题可在下方留言，所有SQL都是经过oracle环境测试的，可复制可粘贴。

select * from emp order by deptno asc ;

select * from dept;
--oracle 拼接字符串
--oracle中字符串需要在英文或其他文字两端用单引号包含
select empno, ename, '职位是' || job, sal from emp;

--列名的别名起别名利用空格
select empno, ename, '职位是' || job haha, sal from emp;
--如果使用双引号声明别名，则可以区分大小写
select empno, ename, '职位是' || job "haha", sal from emp;
--如果想在别名中显示空格，一定要有双引号

--–关键字AS
select empno, ename, '职位是' || job as "ha ha", sal from emp;
--–注意：推荐使用as声明别名，容易区分表达式与别名的界限

--  去掉重复行
select distinct deptno from emp;
-注意：distinct只能放在select与列名之间的位置
-- select distinct 列1，列2，..列n
--select和from 书写顺序 select…from
执行顺序 from…select

--不等于
select * from emp where deptno != 20;
select * from emp where deptno <>20;--推荐这种写法

--查询条件中的 与关系
问题一：
查询在10号部门工作，并且月薪高于2000，
并且职位是MANAGER的员工信息

select * from emp where deptno = 10 and sal>2000 and job = 'MANAGER';
--或关系or
问题一：
查询在10号部门工作或者工资高于2000的员工

select * from emp where deptno =10 or sal>2000;
--如果and与or一起使用
–and的优先级要比or高（重要）
如果要想修改优先级，加括号查询在10号部门工作或者工资高于2000,并且职位是MANAGER的员工


select * from emp where (deptno = 10 or sal > 2000) and job = 'MANAGER';

-- 特殊的比较运算符

--beetween ... and

--beetween 上限 and 下限

查询emp表中，20号部门月薪在1000~2000之间的员工信息

select * from emp where (sal between 1000 and 2000) and deptno = 20;

–between and 使用范围 数值和日期
–数据库的常见数据类型：
–1）文字
–2）数值
–3）日期/时间

select * from emp where hiredate between '1-1月-1982' and '31-12月-1982';

--between and 也可以比较字符串，比较顺序是按照字典的顺序比较


select * from emp where ename between 'A' and 'C';

--上下限反着写 
select * from emp where sal between 2000 and 1000;
-- 语句不报错，但是逻辑关系没有，没有结果
--关键字in 格式 列 in（值1，值2，值3…值n）列的取值是值1或值2或值3…或值n
select ename, mgr from emp where mgr in(7902, 7698, 7788);
--like… 像…（重点）
模糊查询
通配字符
% :0­n个任意字符
_:1一个任意字符
--查询所有员工首字母为B的员工信息
select * from emp where ename like 'B%';
--–练习：查询倒数第二个字符是R的员工信息

--like只能用于文字类型的模糊查询匹配。
select * from emp where ename like '%R_';

--is null
用于筛选空值
因为=null的逻辑关系是null，不能筛选出空值
查询没有奖金的人


select * from emp where comm = 0 or comm is null;

--NOT
逻辑非关系,表示取反

--not between and

查询月薪不在1000~2000之间的员工信息

select * from emp where sal not between 1000 and 2000;
列名在not前后结果一致，保证列名在between前即可
--not in

查询其上司的员工编号不是7902,7698,7788的员工的信息

select * from emp where mgr not in(7902, 7698, 7788);

--not like 

查询所有不姓张的员工

select * from emp
where ename not like '张%';
--is not null
select * from emp
where comm is not null;


排序
–order by
–升序 由小到大
–数值：由小到大
–日期：由早到晚
–文字：字典顺序


查询所有10号部门的员工信息，按照月薪升序进行排序


select * from emp where deptno = 10 order by sal ;

有的环境下不加升序排列，环境默认的是升序，
oracle默认的是升序

select * from emp where deptno = 10 order by sal asc ;

推荐写法

SQl语句执行顺序
书写顺序:select…from…where…order by
执行顺序:from…where…select…order by
where不能使用别名，因为select中尚未执行，
所以还没有创建出别名，order by可以使用别名，
因为order by在select之后执行，列的别名已经被创建
--多列排序
查询员工信息，按照部门编号排序，月薪降序
select * from emp order by deptno asc, sal desc;

--oracle函数
单行函数、多行函数（分组函数）
单行函数
dual
--系统显示当前日期
select sysdate from dual;

使用的单行函数，对查询结果的每一行记录都有效，
会影响到每一行的记录
dual表为了补全sql语句

--转大写 upper(str) 将str字符串转成大写

select upper('hAHa') as 描述 from dual;

可以为列增加别名

--转小写lower(str) 将str字符串转成小写

select lower('hAHa') from dual;

--首字母转大写，其余字母小写initcap(str)

select initcap('hAHa') from dual;

select initcap('i LiKe GIRL') from dual;

--以单词为单位，每个单词的首字母大写，其余的小写

select * from emp where lower(ename) = lower('ScOTT');
--lower()替换为upper()效果一样

字符截取函数
--substr(str,n1,n2)
str:被截取的字符串
n1：开始截取的位置（以1的位置开始）
n2：截取字符的个数
将str字符串从n1位置起，截取n2个字符
结果：字符型类


select substr('0123456', 3, 3) from dual;

从n1的位置开始，要包含该位置的字符

字符查找函数
--instr(str1,str2,n1,n2)
在str1中寻找str2的字符串，
从n1的位置起，第n2次出现的位置
结果是一个位置，位置是一个数值

select instr('I LIKE GIRL', 'I', 2, 1) from dual;

--字符拼接函数
select 'haha' || 'xixi' || 'hehe' from dual;
–将str1和str2拼接组成一个字符串，功能等同于 ||
--concat(str1,str2)
select concat('haha', 'xixi') from dual;

select concat(concat('haha', 'xixi'),'hehe') from dual;

字符替换函数
--replace(str1,str2,str3)
在str1字符串中，使用str3来替换全部的str2

select replace('ABCDECD', 'CD', 'HAHA') from dual;

字符串的长度函数
--length(str)
返回str字符串的文字的个数

select length('abcde'), length('哈哈') from dual;

字符串补齐函数
lpad(str1,n1,str2)
rpad(str1,n1,str2)
lpad:将str1字符串，使用str2在左侧补充到n1个长度
rpad:将str1字符串，使用str2在右侧补充到n1个长度

select lpad('abc', 6, '哈呵'), rpad('abc', 2, '*')
from dual;

--trunc(n1,n2)
–将n1数字舍弃，精确到小数点后n2位
select trunc(3.1415, 3), trunc(3.14, 0), trunc(6.14, -1)from dual;
--MOD(n1,n2)
求n1除以n2后得到的余数 取余

select mod(5, 3), mod(5, -3), mod(-5, 3), mod(-5, -3)
from dual;
取余运算区分正负值，看被除数 编程也是遵循这样原则

日期函数（重要）
--sysdate（函数）
获取数据库所在服务器的当前系统时间
select sysdate from dual;


--months_between(date1,date2) date1与date2之间的月数差
假设emp表中，所有员工至今未辞职，计算他们现在的司龄
months_between(date1,date2)与between and上下限不是对应的


select ename, hiredate,trunc(months_between(sysdate, hiredate) / 12, 0) as 司龄 from emp;
用于计算年龄，司龄，工龄
这些类似的随着时间流逝而改变的数据


--add_months(date1,d1)
为date1的时间，追加d1个月，结果还是一个日期
设公司的试用期为6个月，计算emp表中员工的转正日期


select ename, hiredate, add_months(hiredate, 6)as 转正日期 from emp;

--last_day(date1)
date1所在月份最后一天的日期（结果的类型还是日期）

select ename, hiredate, last_day(hiredate)
from emp;

两个日期之间的天数(重要)

差值就是天数，没有小数点

--next_day(date1,n1)
返回date1起之后周几的日期
1:周日 2：周一 … 7:周六

select next_day(sysdate,7) from dual;


转换函数（重点）
--to_number(str)
将str字符串转为数字

select to_number('1234') from dual;

按照特定格式转换
--to_number(str1,fmt) fmt格式也一定要是一个字符串 fmt也叫做格式码
–$ : 美元符号
–, :三位分隔符或者千位分割符 100,222,119.00
–9 ：一位数字
–0 ：一位数字或者前导0(补全前面的位置)（了解）


如果实际数据超过fmt定义的长度，则数值无法显示
实际数据小于fmt定义的长度，数值可以显示


--to_char(n1,fmt)
将数值转换为fmt表示的文字

select to_number('$23,412,123.34','$99,999,999.99')from dual;

select to_char(2236778,'$9,999,999.99') from dual;

–日期转字符 to_char(date1,fmt)
–日期的格式码：
–YYYY 年
–MM 月
–DD 日
–HH24 24进制的小时
–HH12 12进制的小时
–MI 分
–SS 秒
– ­ / 常规日期分割符
–: 常规时间分割符
DATE类型 精确到秒
查询员工姓名和入职日期
入职日期按照”年/月/日 时:分:秒”的格式显示

select ename, to_char(hiredate, 'YYYY/MM/DD HH24:MI:SS')from emp;
YY,RR 也是表示年

--to_date

select to_date('2017-4-7','YYYY-MM-DD') from dual;

查询在1982年期间入职的员工 按照规定格式显示
XXXX­XX­XX
select * from empwhere hiredate between to_date('1982-1-1','YYYY-MM-DD')and to_date('1982-12-31','YYYY-MM-DD');


通用函数
--nvl(expr1,expr2)
当expr1值不是null时，函数结果是expr1
当expr1值是null时，函数的结果是expr2
计算员工月收入，如果员工有奖金，则奖金+500
如果员工没有奖金，则不发奖金

select ename, comm, sal + nvl(comm + 500,0) from emp;
--nvl2(expr1,expr2,expr3)
判断expr1的值是否为null
如果是null使用expr3的值
如果不是null使用expr2的值

select comm, nvl2(comm, 1, 2) from emp;
给员工发节日福利，有奖金的人节日福利是1000,没有奖金的人节日福利是800
select comm, nvl2(comm, 1000, 800) from emp;

多行函数（分组函数）
由多行记录为一组统计出的数据，利用的是分组函数（多行函数）
常见的统计型数据
求和
--sum()
–查询emp表中，所有月薪的总和
select sum(sal) from emp;
求平均
--avg()
求emp中平均司龄
select avg(months_between(sysdate,hiredate) / 12)from emp;
最大值
--max()
–查询emp表中，最高月薪
select max(sal) from emp;
最小值
--min()
–查询emp表中，最低月薪
select min(sal) from emp;
计数
--count()
查询emp表中，有多少个员工
select count(empno) from emp;
select count(distinct deptno) from emp;
去掉重复数据用distinct，在count函数内部
查询每个部门的平均工资
--group by 依据XX分组 不是分组函数
只有使用了分组函数，才能使用group by

select deptno ,avg(sal) from emp group by deptno;

只有在group by中出现的列，才能写在select之后（必须记住）
多次分组，第二次分组是在第一次分组的基础上进行的
查询10号和20号部门的平均工资

select deptno,avg(sal)from emp where deptno in(10,20)group by deptno order by deptno asc;
--书写顺序
--执行顺序 from...where...group by...select...order by...
分组函数是在group by 执行位置进行计算
查询平均工资高于2500的部门id和其平均工资
select deptno, avg(sal)from emp where avg(sal) > 2500  --错误写法group by deptno
-- where 后面不允许使用分组函数
--having
–专门处理分组函数的筛选
–group by… having…

–查询平均工资在2000~2500之间的部门ID和其平均工资
select deptno, avg(sal)from emp group by deptno having avg(sal) between 2000 and 2500;
select deptno, avg(sal)from emp group by deptno having avg(sal) >= 2000 and avg(sal) <= 2500;
select deptno, avg(sal)from emp group by deptno having avg(sal) > 2500;
--执行顺序

--from ...where... group by ... having... select ...order ...by

今天先写这些，未完，待续。。。。