oracle学习笔记

--创建表空间
create tablespace 表空名;   --表空间名字;
datafile 'e/..'             --路径
size 100m                   --空间大小
autoextend on next 10m;     --自动扩展开每次10m
--删除表空间
drop tablespace 表空名;

--创建用户
create user 用户名             --创建用户
identified by 密码            --设置密码
default tablespace 表空名;     --出生表空间

connect            --连接
resource           --开发者
dba                --超级管理员
grant dba to 用户名;  --给用户授权权限

--创建表
create table 表名(
       字段1 number(20),
       字段2 varchar2(10)
);
--添加一列
alter table 表名 add 字段 number(1);
--修改列类型
alter table 表名 modify 字段 char(1);
--修改列名称
alter table 表名 rename column 字段 to 新字段名;
--删除列
alter table 表名 drop column 字段;

--添加一条记录
insert into 表名(字段1，字段2) value(1,2); commit ;--提交事务
--修改一条记录
update 表名 set 字段='' where ID= ;commit;
--删除表中全部记录
delete from 表名;
--删除表结构
drop table 表名;
--先删除表再创建表，效果等同于删除表中全部记录
--适用于数据量大，带有索引的情况，效率较高 索引提高查询效率，影响增删改效率 
truncate table 表名;

--序列主要用于主键赋值，默认从1开始依次递增
--创建序列
create sequence s_表名; --s代表此表序列  但序列不属于任何表，只作为逻辑绑定
select s_表名 from dual; --dual为虚表 只为补全语法，无意义
s_表名.currval --序列当前值
s_表名.nextval --序列下一个值
insert into 表名(ID，name) value(s_表名.nextval,2); commit;

--scott用户解锁 密码为tiger
alter user scott account unlock;               --解锁用户
alter user scott identified by scott;             --重置密码

--单行函数，作用于一行，返回一个值
--字符函数
select upper('yes') from dual;                    --小写变大写
select lower('YES') from dual;                    --大写变小写
--数值函数
--四舍五入 ,号后-1指往前保留一位结果为30。  1为保留小数点后一位结果为26.2
select round(26.18)from dual;  select round(26.18,1)from dual; 
--直接切去小数点后位数 逗号后为1结果为26.1
select trunc(26.18)from dual;  select trunc(26.18,1)from dual; 
select mod(10,3)from dual;     --求余数 
--日期函数
select sysdate from dual; --查询当前日期
select sysdate-e.date from emp e; --假如date为e表入职时间，求入职时间到现在有多少天
select sysdate+1 from dual;    --算出明天时间
select months_between(sysdate,e.date) from emp e;        ----假如date为e表入职时间，求入职时间到现在有多少月
select months_between(sysdate,e.date)/12 from emp e;        ----假如date为e表入职时间，求入职时间到现在有多少年
select (sysdate-e.date)/7 from emp e; --假如date为e表入职时间，求入职时间到现在有多少周
--转换函数
--日期转字符串
--时间格式为2020-08-06...如不想加前面的0前面加fm。 hh后面24为24小时计时法
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss')from dual;
--字符串转日期   时间为具体日期
select to_date('2020-08-06 16:39:50','fm yyyy-mm-dd hh24:mi:ss')from dual;
--算出工资表中所有员工年薪
select e.xinzi*12+e.jiangjin from emp e; --错误
--因为奖金可能为null 所以使用val函数如括号内第一个为null，则取第二个值0，如不为空则取第一个值 
select e.xinzi*12+nvl(e.jiangjin,0) from emp e;     --正确

--条件表达式
--给emp表中员工起中文名
select e.name
       case e.name                  --因为要给name起别名所以这里写name列
         when 'asd'then '小明'
           when 'qwe'then '小黑'      --当这些名字存在时产生后面的别名结果
             when 'zxc'then '小红'
               else '无名'        --当不满足上面when的名字时都是‘无名’ 如果注释else除when后发生改变其他不改变
                 end
 from emp e;
--判断emp表中工资如果高于3000显示高收入，如果高于1500，低于3000显示中收入，如小于1500显示低收入
select e.gongzi
       case                   
         when e.gongzi>3000 then '高收入'
           when e.gongzi>1500 then '中收入' 
               else '低收入'             
                 end
 from emp e;
--oracle专用：给emp表中员工起中文名
select e.name
       decode(e.name,                  
         'asd','小明'
           'qwe','小黑'      
             'zxc','小红'
               '无名') 列名  --列名可加双引号可不加
from emp e;

--多行函数[也叫聚合函数]作用于多行，返回一个值。
select count(1) from emp;     --查询总数量
select avg(gongzi) from emp;  --平均工资
select sum(gongzi) from emp;  --工资总和
Asc升序排列   Desc降序排列
select * from EMP
select e.mgr,avg(e.sal) from emp e group by e.mgr;
--分组查询中，出现在group by后面的原始列，才能出现在select后面
--没有出现在group by后面的列（例如经处理出现的avg【e.gongzi】），想出现在select后面，必须加上聚合函数
--聚合函数特性可以把多行记录变成一个值
--查询每个部门的平均工资
select e.bumen,avg(e.gongzi) from emp e group by e.bumen;
--查询出平均工资高于2000的部门信息
select e.bumen,avg(e.gongzi) from emp e group by e.bumen having avg(e.gongzi)>2000; 
--所有条件不能使用别来判断
--查询每个部门工资高于800的员工的平均工资
select e.bumen，avg(e.sal) from emp e where e.sal>800 group by e.bumen;
--where过滤分组前数据，having过滤分组后数据
--where必须在group by之前，having在group by之后
--查询每个部门工资高于800的员工的平均工资，再查询出平均工资高于2000的部门信息
select e.bumen，avg(e.gongzi) from emp e where e.gongzi>800 group by e.bumen having avg(e.gongzi)>2000; 


--等值连接
select * from emp e,dept d where e.no=d.no;
--内连接
select * from emp e inner join dept d on e.no=d.no;   --等值连接与内连接的结果相同，使用等值较多
--查询所有部门及部门下的员工信息（外连接 外连接包括左和右）
select * from emp e right join dept d on e.no=d.no;
--查询所有员工信息，以及员工所属部门
select * from emp e left join dept d on e.no=d.no;
--oracle专用外连接
select * from emp e,dept d where e.no(+)=d.no;        --(+)为显示d表所有信息 


--查询员工姓名和员工领导姓名
--自连接：站在不同角度把一张表看成多张表
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno; 
--查询员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
select e1.name,d1.name,e1.name,d2.name from emp e1,emp e2,dept d1,dept d2
where e1.mgr=e2.empno and e1.deptno=d1.deptno and e2.deptno=d2.deptno; 
--子查询
--查询工资和xiao一样的员工信息
select * from emp where gz=(select gz from emp where name='xiao' );
--查询工资和10号部门任意员工一样的员工信息
select * from emp where gz in (select gz from emp where deptno=10); 
--查询每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
--先查询每个部门最低工资
select deptno，min(gz) msal from emp group by deptno;
---三标联查，得到最终结果
select * from (select deptno，min(gz) msal from emp group by deptno;) t,emp e,dept d
where t.deptno=e.deptno and t.msal=e.sal and e.deptno=d.deptno;


--oracle中的分页
--rownum行号：当做select操作时，每查询出一条记录，就会在该行加一个行号
--行号必须从1开始，依次递增不能跳着走。
--排序会影响rownum的排序，可以先排序后加行号 使用嵌套查询
select rownum,t.* from (
select rownum，e.* from emp e  order by e.sal desc) t;
--emp工资倒序排列后，每页5条记录，查询第二页
--rownum行号不能写上大于一个正数
select * from (
       select rownum rn,tt.* from(
              select * from emp order by sal desc
       )tt where rownum<11
)where rn>5