oracle基础学习第一天

--创建表空间
create tablespace itheima
datafile 'c:\itheima.dbf'
size 100m
autoextend on
next 10m;
--删除表空间
drop tablespace itheima;

--创建用户
create user itheima
identified by itheima
default tablespace itheima;

--给用户授权
--oracle数据库中常用角色
connect--连接角色，基本角色
resource--开发者角色
dba--超级管理员角色
--给itheima用户授予dba角色
grant dba to itheima;

---切换到itheima用户下

---创建一个person表
create table person(
       pid number(20),
       pname varchar2(10)
);

---修改表结构
---添加一列
alter table person add (gender number(1));
---修改列类型
alter table person modify gender char(1);
---修改列名称
alter table person rename column gender to sex;
---删除一列
alter table person drop column sex;

---查询表中记录
select * from person;
----添加一条记录
insert into person (pid, pname) values (1, '小明');
commit;
----修改一条记录
update person set pname = '小马' where pid = 1;
commit;

----三个删除
--删除表中全部记录
delete from person;
--删除表结构
drop table person;
--先删除表，再次创建表。效果等同于删除表中全部记录。
--在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。
--索引可以提供查询效率，但是会影响增删改效率。
truncate table person;


----序列不真的属于任何一张表，但是可以逻辑和表做绑定。
----序列：默认从1开始，依次递增，主要用来给主键赋值使用。
----dual：虚表，只是为了补全语法，没有任何意义。
create sequence s_person;
select s_person.nextval from dual;
----添加一条记录
insert into person (pid, pname) values (s_person.nextval, '小明');
commit;
select * from person;

----scott用户，密码tiger。
--解锁scott用户
alter user scott account unlock;
--解锁scott用户的密码【此句也可以用来重置密码】
alter user scott identified by tiger;
--切换到scott用户下

--单行函数：作用于一行，返回一个值。
---字符函数
select upper('yes') from dual;--YES
select lower('YES') from dual;--yes
----数值函数
select round(56.16, -2) from dual;---四舍五入，后面的参数表示保留的位数
select trunc(56.16, -1) from dual;---直接截取，不在看后面位数的数字是否大于5.
select mod(10, 3) from dual;---求余数


-- 根据 API 学习：首字母大写函数initcap、字符串链接函数concat、
-- 字符串截取函数、字符串替换函数、获取字符串长度函数length等。

-- 获取字符的ascii码
select chr(65),chr(97) from dual;

-- 连接字符串
select concat('oracle' , '11g') from dual;

--首字母大写函数
select initcap('oracle universal installer') name from dual;

--查询一个字符在字符串中出现的位置
select instr(str, dr , i , j) from dual;
--查找dr在str中出现的次数，从i个位置开始查询，左边第一个位置默认为1，
-- i，j默认为1，j表示出现了几次

select instr('yessey' , 's' , 1 , 2) from dual;


-- 字符长度
select length('dailingfei') from dual;

-- 字符串替换函数
select replace('big_blue' , 'blue' , 'yellow') from dual;
 
-- 去掉‘Moisossoppo’最左边的字符，当‘Mois’中含有该字符是
select ltrim('Moisossoppo' , 'Mois') from dual;

-- 字符截取
select substr('Message' , 1 , 2) from dual;


----日期函数
-- 用的是scott用户的emp表
select * from emp e;
----查询出emp表中所有员工入职距离现在几天。
select sysdate - e.hiredate from emp e;
----算出明天此刻
select sysdate+1 from dual;
----查询出emp表中所有员工入职距离现在几月。
select round(months_between(sysdate,e.hiredate)) from emp e;
----查询出emp表中所有员工入职距离现在几年。
select round(months_between(sysdate,e.hiredate)/12) from emp e;
----查询出emp表中所有员工入职距离现在几周。
select round((sysdate-e.hiredate)/7) from emp e;

----转换函数
---日期转字符串
-- fm的作用是去除0开头
-- hh后面24的作用是24小时制
select to_char(sysdate , 'fm yyyy-mm-dd hh24:mi:ss') from dual;
--字符串转日期
select to_date('1998-5-18 19:35' , 'fm yyyy-mm-dd hh24:mi:ss') from dual;

----通用函数
---算出emp表中所有员工的年薪
----奖金里面有null值，如果null值和任意数字做算术运算，结果都是null。
select e.sal*12 + nvl(e.comm , 0 ) from emp e;

---条件表达式
---条件表达式的通用写法，mysql和oracle通用
---给emp表中员工起中文名
select e.ename,
       case e.ename
         when 'SMITH' then '曹贼'
           when 'ALLEN' then '凉冰'
             when 'WARD' then '诸葛孔明'
               when 'JONES' then '韩信'
                 else '无名'
                   end
from emp e;

---判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，
-----其余显示低收入
select e.sal,
       case 
         when e.sal>3000 then '高收入'
           when e.sal>1500 then '中等收入'
             else '低收入'
               end "收入等级"    
from emp e;
----oracle中除了起别名，都用单引号。
----oracle专用条件表达式
select e.ename,
        decode(e.ename,
          'SMITH' , '曹贼',
            'ALLEN' , '凉冰',
              'WARD' , '诸葛孔明',
                'JONES' , '韩信',
                  '无名') "中文名"
                   
from emp e;

--多行函数【聚合函数】：作用于多行，返回一个值。
select count(1) from emp e;
select sum(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;


-- 分组查询中出现在group by 后面的原始列，才能出现在select后面
-- 没有出现在group by后面的原始列，想出现在select后面，可以加聚合函数
select e.deptno, avg(e.sal) from emp e where e.sal > 800 group by e.deptno;

---查询出平均工资高于2000的部门信息
select e.deptno, avg(e.sal) 平均工资
  from emp e
 group by e.deptno
having avg(e.sal) > 2000;

-- where是过滤分组前的数据，having是过滤分组后的数据
-- where必须在group by之前，having是在group by之后
---查询出每个部门工资高于800的员工的平均工资
---然后再查询出平均工资高于2000的部门
select e.deptno , avg(e.sal) 平均工资
from emp e
where e.sal > 800
group by e.deptno
having avg(e.sal) > 2000;

-- 多表查询
-- 笛卡尔积
select * from emp e, dept d;

-- 等值连接
select * from emp e, dept d 
where e.deptno = d.deptno;

-- 内连接
select * from emp e
inner join dept d
on e.deptno = d.deptno;

select * from emp e
inner  join dept d
on e.deptno = d.deptno;

-- 外连接
-- 左外连接
select * from emp e
left outer join dept d
on d.deptno = e.deptno;

-- 右外连接
-- 查询没有人的部门
select d.deptno from emp e
right outer join dept d
on d.deptno = e.deptno where empno is null;

------查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
select e1.ename, d1.dname , e2.ename , d2.dname 
from emp e1 , emp e2 , dept d1 , dept d2
where e1.mgr = e2.empno 
and e1.deptno = d1.deptno
and e2.deptno = d2.deptno;

--查询出每个员工编号，姓名，部门名称，
--工资等级和他的上级领导的姓名，工资等级
select * from salgrade;
select * from emp;
select e1.empno 员工编号,
       e1.ename 员工姓名,
       d1.deptno 员工部门,
       decode(s1.grade,
              1,
              '一级',
              2,
              '二级',
              3,
              '三级',
              4,
              '四级',
              5,
              '五级') 员工工资等级,
       e2.ename 领导姓名,
       d2.deptno,
       decode(s2.grade,
              1,
              '一级',
              2,
              '二级',
              3,
              '三级',
              4,
              '四级',
              5,
              '五级') 领导工资等级
  from emp e1, emp e2, dept d1,dept d2, salgrade s1, salgrade s2
 where e1.mgr = e2.empno
   and e1.deptno = d1.deptno
   and e2.deptno = d2.deptno
   and e1.sal between s1.losal and s1.hisal
   and e2.sal between s2.losal and s2.hisal;

-- 子查询
-- 子查询返回一个值
---1.查询出工资和SCOTT一样的员工信息
select *
  from emp e
 where e.sal in (select sal from emp where ename = 'SCOTT');
-- 子查询返回一个集合
---2.查询出工资和10号部门任意员工一样的员工信息
select * 
  from emp e
 where e.sal in (select sal from emp where deptno = 10);

-- 子查询返回一张表
---查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
---1，先查询出每个部门最低工资
select min(e.sal) from emp e group by deptno;

---2，三表联查，得到最终结果。
select e1.ename 最低工资员工 , e1.sal 工资 , d.dname 部门
from (select min(e.sal), deptno sal from emp e group by deptno) minSal, emp e1 , dept d
where minSal.deptno = d.deptno
and minSal.sal = e1.sal
and e1.deptno = d.deptno;


-- 分页查询
select *
  from (select rownum rn, t.*
          from (select * from emp e order by e.sal desc) t
         where rownum < 11) tt
 where rn > 5;