day71_oracle01笔记

==========oracle01笔记

==============

一、oracle的体系结构===【5个概念】

--1.数据库 database  
  Oracle数据库 只有一个database  名字orcl

--2.实例 由一系列的进程和内存结构组成，
--实例可以有多个，但是在使用时99.9%都使用一个实例

--3.数据文件  .dbf
   存放数据的文件

--4.表空间###
   就是数据文件的逻辑映射

--5.用户###
   用来操作表空间的
   
   
二 、首先是system系统用户，执行如下代码可以【切换用户】。 
---------Scott用户默认密码是tiger【重置密码】
使用sql重置Scott的密码
alter user scott identified by tiger

---###---select * from scott.emp; --访问scott用户的emp表

-----------------------以下使用scott用户---------------------------
select * from dept;--  部门
select * from emp; --员工
select * from salgrade;--工资等级

三、查询
--1.简单查询

1、别名的使用---乱码
select empno as "员工编号"  ,ename as 员工姓名 , job  "职位" ,mgr 领导  from emp

select empno as "员工编号"   from emp
解决乱码
 中文乱码问题解决
/* 
1.查看服务器端编码
select userenv('language') from dual;
我实际查到的结果为:AMERICAN_AMERICA.ZHS16GBK
2.执行语句 select * from V$NLS_PARAMETERS 
查看第一行中PARAMETER项中为NLS_LANGUAGE 对应的VALUE项中是否和第一步得到的值一样。
如果不是，需要设置环境变量.
否则PLSQL客户端使用的编码和服务器端编码不一致,插入中文时就会出现乱码.
3.设置环境变量
计算机->属性->高级系统设置->环境变量->新建
设置变量名:NLS_LANG,变量值:第1步查到的值， 我的是	AMERICAN_AMERICA.ZHS16GBK
4.重新启动PLSQL,插入数据正常
*/

2、四则运算 
select sal , sal*12 from emp;

3、连接符   ||

--dual伪表  目的：【就是为了配置查询语句】---了解
"员工编号是XXX，姓名是XXX，职位是XXX"
select '员工编号是XXX'||'姓名是XXX'||'职位是XXX' info from dual

select '员工编号是'||empno||'姓名是'||ename||'职位是'||job  from emp;

4、去重 distinct

--2.条件查询  where

1、查询工资大于1500并且小于3000的员工
select * from emp where  sal>1500 and sal<3000
2、between ..and   --包含临界值
select * from emp where  sal>=1500 and sal<=3000;
select * from emp where  sal between 1500 and  3000;
3、null 不是空字符串也不是0，是未知类型的数据并且 null和null不相等,
查询奖金为空的员工
select * from emp where comm is null
查询奖金不为空的员工
select * from emp where comm is not null
4、not
查询工资不大于1500
select * from emp where not(sal>1500)
5、 in
查询员工编号是7788,7369,7566
select * from emp where empno in(7788,7369,7566)
查询员工姓名是  SMITH  JONES  SCOTT
select * from emp where ename in('SMITH',  'JONES',  'SCOTT')

--3.模糊查询
like  %  _
注意：Oracle中是大小写敏感

查询员工姓名中第二个字母是M的
select * from emp  where ename like '_M%'

select * from emp  where ename like '%M%'

查询员工姓名中带_的
select * from emp  where ename like '%1_%' escape '1'
select * from emp  where ename like '%d_%' escape 'd'
select * from emp  where ename like '%@_%' escape '@'
select * from emp  where ename like '%#_%' escape '#'
select * from emp  where ename like '%__%' escape '_'
错误的
select * from emp  where ename like '%%_%' escape '%'
select * from emp  where ename like '%&_%' escape '&'

---4.排序
order by    desc   asc
按照奖金从高到低排序

select * from emp order by comm desc nulls last
按照奖金从低到高排序
select * from emp order by comm  nulls first



四、函数

--1.单行函数
1.1字符串类型
 lower--转小写
 select lower('ORACLE') from dual
 select ename,lower(ename) from emp
 upper--转大小
 
 initcap--首字母大写
 
 substr--截取
 
 oracle截取时起始位置写0和1是一样的
 select substr('qwertyui',0,4) from dual;--qwer
 select substr('qwertyui',1,4) from dual;--qwer
  select substr('qwertyui',2,4) from dual--wert
 
 length--长度
  select length('qwertyui') from dual--8
 replace--替换
  select replace('qwertyui','qw','aa') from dual-- aaertyui
 
 concat--连接两个字符串函数  ===推荐使用||
 select concat('aaa' ,'bbb') from dual
  select concat(concat(concat('aaa' ,'bbb'),'ccc' ),'ddd')  from dual

1.2数值函数
round--四舍五入
select round(12.456) from dual; --12
select round(12.656) from dual; --13
select round(12.456,2) from dual;--12.46
select round(12.456,-1) from dual;--10
select round(16.456,-1) from dual;--20

trunc--截断
select trunc(12.456) from dual; --12
select trunc(12.956) from dual; --12
select trunc(12.456,2) from dual;--12.45
select trunc(12.456,-1) from dual;--10
select trunc(16.456,-1) from dual;--10

mod--取余
select mod(10,3) from dual;--1
select mod(10,2) from dual;--0

1.3日期类型

日期1-日期2=数值（单位：天）

当前时间-入职时间=工龄
select sysdate from dual
获取员工入职的天数
select  ename,hiredate,sysdate-hiredate from emp
获取员工入职的周数
select  ename,hiredate,round((sysdate-hiredate)/7) from emp
获取员工入职的月数
months_between--两个日期的月数差
select  ename,hiredate,months_between(sysdate,hiredate),(sysdate-hiredate)/30 from emp

add_months--添加月
select  sysdate  from dual
select add_months(sysdate,1) from dual



1.4 转换函数--### 转换函数（重点）

--to_char 数值转成字符串
select 12,to_char(12) from dual

--to_char 日期转成字符串
select * from emp where to_char(hiredate,'yyyy/MM/dd')= '1981/02/20'
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
select to_char(sysdate,'yyyy'),to_char(sysdate,'mm'),to_char(sysdate,'dd'),to_char(sysdate,'day') from dual

--to_number 字符串转数字
select to_number('12') + to_number('34') from dual
select  '12'  + '34'  from dual

--to_date 字符串转成日期
select add_months(to_date('2016-01-31','yyyy-MM-dd'),1) from dual
select * from emp where hiredate=to_date('1981-02-20','yyyy-MM-dd')


1.5 通用函数--了解
--nvl ---######
语法：nvl(数字类型字段名,0)--当 字段为null时，把它当做0处理。
计算 员工年薪   -- null参与运算结果恒为null
select ename,sal,comm,sal*12+comm from emp
       --nvl存在意义：上面 这样写运行结果有问题：comm没有的，导致年薪也为null。
       --### null参与运算结果恒为null
select ename,sal,comm,sal*12+nvl(comm,0) from emp
   --nvl(comm,0)含义：当comm为null时，把它当做0处理。
   
====emp表 字段值 英文理解:
CLERK--职员
SALESMAN--销售员
MANAGER--经理
ANALYST--分析师
PRESIDENT--总裁

--decode  --是【Oracle独有】的---重点
decode(列名,'表中的数据','显示的值','表中的数据','显示的值','表中的数据','显示的值')

select ename,job,
decode(job,      --类似Java：switch case --对某个字段根据取值不同，显示不同的内容
'CLERK','职员',
'SALESMAN','销售员',
'其他'
) from emp

--条件表达式  --任何关系型数据库都支持---
case 列名  when 值  then 显示的值   end ---和decode功能一样：对某个字段根据取值不同，显示不同的内容

select ename,job ,
 case job             
   when 'CLERK' then '职员'
   when 'SALESMAN' then '销售员'
   else '其他'
    end 

 from emp


--2. 多行函数：  聚合函数  组函数
2.1 聚合函数
 sum  avg  count max min

select avg(sal) from emp

select max(sal)- min(sal) from emp

select count(*) from emp

select sum(sal) from emp
 
2.1 组函数：分组
group by
查询每个部门的平均工资
select deptno,max(sal) from emp group by deptno

select deptno,avg(sal) from emp group by deptno
查询部门的平均工资大于2000的部门
select deptno,avg(sal) from emp group by deptno
having avg(sal)>2000 

--where 和having的区别【笔试题】###
where过滤的是表中的物理列（表中存在的列）
where出现在group by前面, having出现在group by的后面



----------------------小结------
五、day01 总结：

Oracle的安装和配置
1、加载xp虚拟机
2、在虚拟机中安装Oracle数据库
3、配置网络 加一个虚拟网卡
4、在本机上测试客户端工具instantclient_12_1远程连接虚拟机
5、安装plsqlDev工具，并且配置

简单查询和条件查询--必须掌握

--常用 重点函数
1.字符串：
replace
length
substr

2.数字
round

3.类型转换--###
to_char--数字、日期转字符串
to_date--字符串转日期。

4.通用函数(常用 )
nvl
解决：--### null参与运算结果恒为null  
select ename,sal,comm,sal*12+comm from emp
       --nvl存在意义：上面 这样写运行结果有问题：comm没有的，导致年薪也为null。    
select ename,sal,comm,sal*12+nvl(comm,0) from emp
语法：nvl(数字类型字段名,0)--###--当 字段为null时，把它当做0处理。

5.  5个基本聚合函数
count  sum max min avg