ORACLE部分自学笔记整理

最新推荐文章于 2022-10-28 12:47:54 发布

RuobaiMEN

最新推荐文章于 2022-10-28 12:47:54 发布

阅读量266

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/RuobaiMEN/article/details/83422266

版权

Oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

--在命令行解锁账户
--使用下面的语句解锁scott:

alter user scott account unlock;

--解锁之后可能会要求你该密码 tiger：

alter user scott identified by tiger;

create table employee(
id number(4),
           name varchar2(20) not null,
           gender char(1) default 'M',
           birth date,
           salary number(6,2),
           job varchar(30),
           deptno number(2)
           );

           rename employee to myemp;

           alter table myemp add (t_table_id number(20));

           alter table myemp modify (t_table_id char(10));

           alter table myemp drop (t_table_id);

           alter table myemp add(birtheate date default sysdate);

--DML 语句，对表中的数据进行的操作
--伴随事物控制
INSERT INTO myemp
(id, NAME, salary, deptno)
VALUES
(2, 'lishazi', 100, 10);

SELECT *
FROM myemp
FOR UPDATE;

COMMIT;

--插入日期格式，比较另类
INSERT INTO myemp
(id, NAME, salary, deptno, birtheate)
VALUES
(3, 'cao', 100, 10, to_date('2009-09-01', 'YYYY-MM-DD'));


-- 修改记录内容
UPDATE myemp
SET NAME = 'ni'
WHERE NAME = 'cao';

-- 查询出来并修改记录内容
SELECT *
FROM myemp
FOR UPDATE;

--删除表中的数据，如果不加条件，是删除整个表
DELETE FROM myemp;

DELETE FROM myemp
WHERE NAME = 'zoushazi';

truncate myemp; --清空表数据，不受事物控制，不可回滚

           ---day 03 数据类型
           --1，字符串类型， char varchar varchar2 long（此类型长度2GB，但是有很多限制，不可作为逐主键，不能建索引，不可用于查询条件，一般不建议使用）
           --一般使用 clob 存储大字符串可存储4GB 无条件限制



           --select子句中使用表达式
           --如，查询工人的年薪
SELECT NAME, salary * 12
FROM myemp

-- --字符串函数
concat() --函数，用来连接两个字符串

SELECT concat(NAME, salary * 12)
FROM myemp

SELECT concat(concat(NAME, ','), salary)
FROM myemp;

|| --字符串拼接符号

SELECT id || ',' || NAME || salary
FROM myemp;

length() --返回变长字符串长度

SELECT NAME, length(NAME), length(salary)
FROM myemp;

upper() --全大写
lower() --全小写
initcap() --首字母大写

SELECT upper(NAME), lower(NAME), initcap(NAME)
FROM myemp;

SELECT upper('woshinibaba')
FROM myemp;
--伪表 dual -就是为了查跟表无关系的数据，向上面的语句一样，必须加表名才可以查询，所以就有了‘伪表’
SELECT lower('woshinidie'), initcap('cao')
FROM dual;

SELECT concat(upper('asdfa'), initcap('dsfa'))
FROM dual;
SELECT lower('AFASDF') || '122' || upper('asdf') || initcap('1cao')
FROM dual;

UPDATE myemp
SET salary = 6000.2
WHERE NAME = 'ni';

SELECT lower('ADFASDFASD')
FROM dual;
SELECT upper('nibaba')
FROM dual;
SELECT initcap('asdfasdfa')
FROM dual;

           --截取字符串，去除字符串中两边的首个指定重复字符 (只能去除单个字符）
           --注意：如果首字母中不包含要去除的字符，则停止
           --trim(c1 from c2) 从C2左右两边去除首个 C1，如果遇到多个C1连续在一起，也连同去掉，直到不再为C1即刻停止去除
           --ltrim（）仅去除从左边开始的一个字符
           -- rtrim() 仅去除从右边开始的一个字符
SELECT TRIM('e' FROM 'ieieeiuueuueeiei')
FROM dual;
SELECT ltrim('ieeeraerseeres', 'e')
FROM dual;
SELECT rtrim('asdefefff')
FROM dual;

SELECT TRIM('b' FROM 'eabsdgbag')
FROM dual;
SELECT ltrim('easeaasdfasdfasda', 'baeras')
FROM dual; --多个要去除的字符，会轮流一个一个找，找到就去除，直到遇到要去除字符组中没有的字符即刻停止


SELECT *
FROM myemp
FOR UPDATE;

INSERT INTO myemp
(id, NAME, salary, birtheate)
VALUES
(4, 'ba', 2015.8, to_date('2018-8-2', 'YYYY-MM-DD'));

--补位函数，用于在字符串char1的左端或右端用char2 补足到N 位，char2 可重复多次
lpad(char1,n,char2) --左补位函数
           rpad(char1,n,char2) --右补位函数
           --例如在myemp表中使用左补位，将sal用$ 补齐6位
SELECT NAME, lpad(salary, 6, '0') AS sal
FROM myemp;

SELECT NAME, rpad(salary, 7, '$') AS salary
FROM myemp;

SELECT lpad(salary, 2, '$')
FROM myemp; --当补足到的位数小于原来长度时，会从末尾截取

SELECT lpad(NAME, 2, '#')
FROM myemp;
           --java 的下标默认从 0 开始，而Oracle的下标默认从 1 开始

           -- 截取n个字符函数
           --substr(char m,n ), 从char 中的m 位开始截取 n个字符，
           --如果 m=0 ，则从首字符开始，如果 m 为负数，则从尾部开始向前数 m个数，继续向后截取
           --如果没有设置n，或者 n 的长度超过了char 的长度，则取值到末尾
           SELECT substr('asdfasdfasdf', 5, 3)
           FROM dual;

SELECT substr(';ladsjfpasdfa', 0, 10)
FROM dual; --但是截取时如果下标是 0 ，也是从 1 开始

SELECT substr('[oajglk;asj', -5, 100)
FROM dual; --继续向后截取，而不是向前

           -- instr(char1,char2, n , m ) 返回子串 char2 在源字符串char1中的位置
           --参数 n ：从第n个字符开始检索
           --参数 m ：第几次出现
           --m n 不写默认都是1 ,查找的是第一次出现的位置


           SELECT instr('adfsoafgaf', 'f', 4, 2) AS ini
           FROM dual;

SELECT instr('oijaslkfjk;asf', 'k')
FROM dual;

           --数值类型
           number(m,n)-- m 为长度，n 为小数位小数位可以从1-38

           -- round(m,n) --四舍五入函数 m为任意数字，n 必须为整数
           -- n 取正数则四舍五入到小数点后m 位
                                   -- m 取0 值则四舍五入到整数位，不写缺省也是默认 0
                                   -- m 取负数，则四舍五入到小数点前 m 位
           select round(15.532) from dual; -- 16
           select round(12.444,0) from dual; -- 12
           select round(12.233,-1) from dual; -- 10
                      select round(12.233,-2) from dual; -- 0

           --trunc(m,n) 截取数字
           select trunc(45.678,2) from dual; --45.67
           select trunc(45.678,0) from dual; --45
select trunc(45.678,-1) from dual; --40

           --mod(m,n) 取余函数, 返回m除以n 后的余数，
           -- n 为0 则直接返回m
       select mod(20,3) from dual;

           select mod(18,0) from dual; --18

           select mod(0,1) from dual;
           --cell(n) 向上取整取大于或等于n的最小整数值
           --floor(n) 向下取整取小于或等于n的最大整数值

           select ceil(45.678) from dual;

           --TEST
                       --1,查看名字只有2 个字母的员工信息
                       select * from myemp where length(name)=2;
                       --2,查看第三个字母是u的员工信息
                       select * from myemp where substr(name,3,1)='u';

       --select ename,sal,deptno from emp where length(ename)=5;



       --oracle 中的日期类型
       -- date() 7 个字节
                   --timestamp() 与DATE（）的区别就是可以保存小数秒，最高精度可以到 NS(纳秒)

       --sysdate 当前系统时间，精确到秒
           select sysdate from dual;

           create table student (id number(4), name char(20),register Date default sysdate);

--systimestamp 时间戳类型的时间，11个字节，精确到毫秒
SELECT systimestamp
FROM dual;

SELECT to_char(systimestamp, 'DD.SS')
FROM dual;



-- 查看名字只有5 个字母的员工的名字，工资部门号？
SELECT NAME, salary, bumen_id
FROM emp
WHERE length(NAME) = 5;
-- 查看第三个字母是A 的员工信息？
SELECT *
FROM myemp
WHERE substr(NAME, 3, 1) = 'u';

SELECT *
FROM myemp
WHERE instr(NAME, 'u') = 3;

           select * from myemp;

           select sysdate from dual ;
           select systimestamp from dual; --时间戳类型的时间

           -- to_date() 函数
           可以将字符串按照给定的日期格式解析为一个DATE类型的值
SELECT to_date('2001-5-9 20:15:59', 'YYYY-MM-DD HH24:MI:SS')--如果有中文或特殊字符，如“年”“月”“日”要用“ 双引号”括起来
FROM dual;
           --日期的计算
           --日期可以与一个数字进行加减法，这相当于加减指定的天数
           --两个日期可以进行减法，差为相差的天数


           --查看每个员工至今入职多少天了？
           select name,sysdate-birtheate from myemp

           select sysdate-to_date('1993-10-29','YYYY-MM-DD') from DUAL; --活了这些天
           select to_date('1993-10-29','YYYY-MM-DD')-sysdate from dual;

           --to_char();可以将date（）按照给定的格式转换为字符串
           select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;


           --日期格式中 RR 和 YY 的区别

SELECT to_date('50-07-12', 'YY-MM-DD')
FROM dual;
           SELECT to_date('50-07-12', 'RR-MM-DD') --RR 是跟世纪有关的
FROM dual;

   --- 日期函数
   -- last_day(date) : 返回给定日期所在月的月底
           select last_day(sysdate) from dual;

       -- add_months(date,i )   对给定日期加上指定的月，若i为负数则是减去

           --查看每个员工入职20 周年的纪念日
              select add_months(birtheate,12*20) from myemp;

               select add_months(sysdate,10*12) from dual;
-- months_between(date1,date2) 两个日期之间相差多少个月
           select months_between(sysdate,systimestamp) from dual;
           select months_between(sysdate,birtheate) from myemp;
           --next_date(date,char):返回date 日期数据的下一个周几，周几是由参数char来决定的，char可以是-1-7之间的数字，
           --1代表周日，7 代表周六
           select next_day(sysdate,2) from dual;
       --最值函数，除了日期之外常用数字也可以比较大小
           --least(a,b,c,d) 最小值注意：日期越晚，越大
           --greatest(a,b,c,d) 最大值
           select least(sysdate,birtheate) from myemp;
           select greatest(sysdate,birtheate) from myemp;

       -- extract() 从指定日期中提取时间分量如日，月，年
       select extract(year from sysdate) from dual;

       select extract(year from birtheate) from myemp;
               select extract(day from birtheate) from myemp;



-- 空置操作 null 在Oracle中不插入的值默认为 NULL
--删除字段值为null的记录
delete from student where gender is null;--判断是null用 is 而不是 =

   --null值得运算与操作
   --null 与任何数字运算结果还是null
   --null与字符串拼接等于什么都没干
   select '123'||null from dual; --123
   --查看工资(工资=基本工资+绩效+奖金)
   select 123+null from dual;
--空值函数
-- nvl(arg1,arg2)
   --当arg1为null，函数返回arg2 的值，若不为null，则返回arge1 本身
   --所以该函数的作用是将null值替换为一个非null值

   --比如
   select 123+nvl(null,2) from dual; -- 125
       select 123+nvl(1,2) from dual; --124

   --查看每个人的绩效情况，即，有绩效的，显示为“有绩效”，绩效为null 的，显示为
   --”没有绩效“
   --使用nvl（）函数无法实现，要使用nvl2（）函数
--nvl2(arg1,arg2,arg3)
       --当arg1不为null，则函数返回arg2
       --当arg1为 null，则函数返回arg3
       --该函数是根据一个值是否为null来返回两个不同的结果
       select nvl2(null,1,2) from dual; --2
       select nvl2(1,2,3) from dual; --2




--别名
--如何让别名含有空格，或者别名里面出现大小写，可以使用双引号括起来“”
       select 12*2 "A a" from dual;

--where 关键字中使用 and 和 or 可以使用（）提高or的优先级

       --使用 like（模糊查询）通配符 % 多个 _单个字符
       select * from myemp where name like 'n%';
       select * from myemp where name like '_o%';

--in 和 not in 判断是否在一个列表中(列表可以为一个子查询)
select * from myemp where gender in ('M','W');
--any 和 all 配合>,< ，<=，>= 一个列表使用（一般列表是一个子查询）
>any(list) :大于列表中最小的
>all(list) :大于列表中最大的
           <any(list) :小于列表中最大的
           <all(list) :小于列表中最小的


--between a and b 判断是否在一个范围内包含a 和 b （注意：必须 a<b ）
       --判断工资在4000-5000
       select * from myemp where salary between 6001 and 5000;


--distinct 去除重复 ,有distinct 修饰的字段前面不要有字段，并且，如果使用
--多个distinct，是组合去重，两个字段加在一起一样的去除掉重复
select distinct job from myemp;
           select distinct job,distinct salary from myemp;

--排序 order by + 指定的字段 +asc 升序(默认)/desc 降序，（注意：order by 必须为select 语句的最后一个子句，必须放在最后边）
--可以按照多个字段排序，且都可以指定排序方式，优先顺序是先按照第一个字段排序，再按照第二个字段排序
-- 注意：如果排序时候有null值，则null被认为是最大值
select * from myemp order by salary desc,id;

--聚合函数用于统计出一个值
--聚合函数又叫多行函数，分组函数
           --聚合函数是对结果集某些字段的值进行统计的
   --MAX ,MIN 求给定字段的最大值与最小值
   --avg ,sum 求平均值和总和
   --count 函数不是对给定的字段值进行统计，而是对给定字段不为 null 的数据的统计
   --注意：实际上所有聚合函数都是忽略null 值统计

select count (*) from myemp; --查看这张表的总共记录数
select count(name) from myemp; --查看有多少有名字的记录

--注意：如果统计的时候出现null值，avg函数会只统计不为null的值，并且除以的也是不为null的个数，所以不能做到真正平均
       --这样就要组合使用 nvl（）函数
       select avg(salary),sum(salary) from myemp; --平均值为3629，未做到平均，因为有 null
           select avg(nvl(salary,0)),sum(salary) from myemp; --平均值2903.32

select * from myemp for update;


--分组 group by 有很多注意事项－－

       --查看每个部门的平均工资
       select avg(salary) from myemp group by deptno;
       --查看每个职位的最高工资
       select max(salary) from myemp group by deptno;
       select * from myemp for update;


--关联查询
       ---查询工资高于3000的员工的名字，工资，部门名以及所在地
       select * from dept;
       select * from myemp;
       alter table myemp rename to emp;
       select * from emp;

       select e.ename,e.sal,d.dname,d.loc
       from emp e,dept d
       where e.deptno=d.deptno and sal>3000;
--内链接
--查看员工名字以及其所在部门的名字

SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON (e.deptno = d.deptno)
WHERE e.ename = 'KING';
       --注意，所有不满足连接条件的记录是不会在关联查询中被查询出来的

--外连接，可以将满足条件的记录查询出来之外，还会将不满足连接条件的记录
--也查询出来
   --外连接分为（left outer join/right outer join/full outer join）
   --1，左外连接：以join左侧表作为驱动表（所有数据都会被查询出来），那么
       --当该表中的某条记录不满足连接条件时来自右侧表中的字段，全部填上null

       --2，右外连接：以join右侧表作为驱动表（所有数据都会被查询出来），那么
       --当该表中的某条记录不满足连接条件时来自左侧表中de

select e.ename,d.dname,d.loc
       from emp e left outer join dept d
       on(e.deptno=d.deptno);
--注意：可用 + 简写左外连接和右外连接，但是全连接不可以这样用
select e.ename,d.dname,d.loc
       from emp e join dept d
       on e.deptno=d.deptno(+);

--自连接
--自连接即：当前表的一条记录可以对应当前表自己的多条记录
       --自连接时为了解决同类型数据但是又存在上下级关系的树状结构数据时使用；

       select e.ename,d.ename
       from emp e,emp d
       where e.mgr=d.empno;

       select * from emp;

--查看每个员工以及其领导的名字
select y.ename,l.ename
       from emp y,emp l
       where y.mgr=l.empno

       select y.ename,y.mgr,l.ename,l.empno
       from emp y join emp l
       on y.mgr = l.empno

--查看 SMITH 的上司在那个城市工作
       select distinct d.dname,d.loc
       from emp y join emp l
       on y.mgr=l.empno
       join dept d
       on l.deptno =d.deptno
       where d.deptno=
       (select f.deptno
       from emp e,emp f
                   where e.mgr=f.empno
                   and e.ename='SMITH')

       select d.dname,d.loc
       from deptno d,emp y,emp l
       where y.mgr=l.empno
       and l.
--oracle 中的行号 -rownum 可将 rownum作为一个虚拟的字段，直接查询
       select rownum,d.deptno,d.dname,d.loc from dept d;

       select rownum,e.ename,m.ename,m.deptno,d.loc
       from emp e join emp m
       on e.mgr=m.empno
       join dept d
       on m.deptno=d.deptno
       where e.ename='SMITH'

       --n张表进行查询，至少出现 n-1个链接条件

       --子查询，嵌套在其他查询语句之中的，
       --查询工资大于CLARK的员工

       select e.ename
       from emp e
       where e.sal>(select M.SAL from emp m where m.ename='CLARK' )
       --查询和CLARK同部门的员工
       select e.ename
       from emp e
       where e.deptno=(select m.deptno from emp m where m.ename='CLARK')

       --子查询可以出现在 DDL和DML中
       -- 在DDL中使用子查询，可以根据子查询的结果集创建一个表用 AS
       create table emp01
   as (
       select e.ename,e.deptno,d.dname,d.loc
               from emp e join dept d
               on e.deptno=d.deptno (+)
       )

       select * from emp01;
       --创建表时若子查询中的字段有别名，如果有运算和函数，必须用别名，则会以别名命名字段名

       create table emp02
   as (
       select e.ename as name,e.sal*12 as yearsal ,e.deptno as deptnum,d.dname,d.loc
               from emp e join dept d
               on e.deptno=d.deptno (+)
       )
       select * from emp02;
       --将CLARK所在部门的所有员工都删除 DML中使用子查询
       delete from emp02 e
       where    e.deptnum =(select deptnum from emp02 m where m.name='CLARK')

--查询工资大于平均工资的员工
       select *
       from emp e
       where e.sal>(select avg(sal) from emp)
       --查询出部门中有SALESMAN但职位不是SALESMAN的员工信息
       --查询出与SALEMAN同部门的其他职位的员工
       select * from dept;
       select * from emp;
       --单行子查询返回多个值不用 = 用 in
       select e.ename,e.deptno,e.job ,d.dname
       from emp e join dept d
       on e.deptno=d.deptno
       where e.deptno in (select distinct e.deptno
       from emp e
       where e.job='SALESMAN')
       and e.job<>'SALESMAN'


       select *
       from emp m
       where m.deptno in
       (select e.deptno
       from emp e
       where e.job='SALESMAN')
       and m.job <>'SALESMAN'

       --查看职位比CLERK和SALESMAN工资都高得员工 !!!!!!!!!!!!!!
       select a.sal
       from
       (select rownum,m.* from emp m
       where m.sal >all
       (select e.sal
       from emp e
       where E.job in ('CLERK','SALESMAN'))
       order by m.sal desc) a

--EXISTS关键字!!!!!!!!!!!!!!
       --exists后面跟一个子查询，当该子查询可以查询出至少一条记录时
       --则exists表达式成立并返回true，返回成立的记录

       --TEST查询有员工的部门
SELECT d.deptno, d.dname
FROM dept d
WHERE EXISTS (SELECT *
FROM emp e
WHERE e.deptno = d.deptno)

       --TEST查询没有员工的部门 not exists
SELECT d.deptno, d.dname
FROM dept d
WHERE NOT EXISTS (SELECT *
FROM emp e
WHERE e.deptno = d.deptno)

       --TEST 查看每个部门的最低薪水是多少？前提是该部门的最低薪水高于30号部门的最低薪水

       /*select m.sal ,d.dname
       from emp m join dept d
       on m.deptno=d.deptno
       where m.sal>(
       select min(e.sal)
       from emp e
       where e.deptno=30)
       order by sal desc*/


SELECT MIN(e.sal) a, e.deptno
FROM emp e
GROUP BY e.deptno
HAVING MIN(e.sal) > (SELECT MIN(e.sal)
FROM emp e
WHERE e.deptno = 30)
   --子查询在FROM 中的使用
   --当一个子查询是多列子查询，通常将该子查询的结果集当作一个表
   --看待，并基于它进行二次查询

   --TEST 查看比自己所在部门平均工资高的员工！！！！！！！！！！！！！
SELECT AVG(sal) avgsal, e.deptno
FROM emp e
GROUP BY e.deptno

SELECT m.ename, m.deptno, m.sal
FROM emp m,
(SELECT AVG(sal) avgsal, e.deptno
FROM emp e
GROUP BY e.deptno) n
WHERE m.deptno = n.deptno
AND m.sal > n.avgsal
ORDER BY m.sal DESC

--子查询在SELECT中出现，子查询可以作为一个字段形式出现
select e.ename,e.sal,e.job,
   (select d.dname from dept d where e.deptno=d.deptno) deptname
   from emp e

----分页查询 rownum
--分页查询时将查询表中数据时分段查询，而不是一次性的将所有数据查询出来
       --有时查询的数据量非常庞大，这会导致系统资源消耗大，响应速度长，数据冗余严重
       --为此当遇到这种情况时一般使用分页查询解决，
       --数据库基本都支持分页，但是不同数据库语法不通，都是各自的方言

       --ORACLE中的分页是基于伪利ROWNUM实现的
       --ROWNUM不存在任何一张表中，但是所有的表都可以查询该字段，该字段的值都会i
       --随着查询自动生成的，方式是：每当可以从表中查询出一条记录，该字段的
       --值几位该条记录的行号，从1开始，逐次递增


       select rownum ,a.*
       from(select *
       from emp e
       order by sal desc) a
       where rownum between 6 and 10
       --查看员工表中的第6-10行记录（要从结果集中再取出记录）
SELECT *
FROM (SELECT rownum rn, empno, ename, sal, job
FROM emp)
WHERE rn BETWEEN 6 AND 10
       --查看工资排序后的第6-10行（注意要先排序，从里面向外巴）！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SELECT *
FROM (SELECT rownum rn, a.*
FROM (SELECT e.ename, e.deptno, e.sal
FROM emp e
ORDER BY sal) a)
WHERE rn BETWEEN 6 AND 10;
       --注意：要点，先排好序，再加行号，再取范围内的内容

       --再注意：如果数据量过于庞大，要先取有意义的部分
       select *
       from( select rownum rn,a.*
       from(select e.ename,e.job,e.sal
       from emp e
       order by sal desc) a
       where rownum<=10)
       where rn>=6
--
--计算区间公式
--pagesize:每页显示的条目数
       --page：页数

       --star: (page-1)*pageSize + 1
       --end: pageSize*page

--数据库中的名字不可以重复

--DECODE 函数，可以实现像 switch case
--最少三个值 DECODE(表达式1，值1，值2，值3，值4，值5)
   --如果表达式1的值是值1，则返回值2，如果是值3则返回值4，其他返回值5,如果没有值5，返回null

   -- TEST根据职位不同，涨工资
   select ename,job,sal,
   decode(job,
                   'MANAGER',sal*1.2,
                   'ANALYST',sal*1.1,
                   'SALESMAN',sal*1.05,
                   sal
                   ) bonus
   from emp;

   --DECODE函数基本语法相似的有 CASE 语句，实现类似于 if -else 的操作
   --写case 必须以 case开始，以end结束，中间用空格，不用逗号
   select ename,job,sal,
   case job when 'MANAGER' then sal*1.2
                   when 'ANALYST' then sal*1.1
                                   when 'SALESMAN' then sal*1.05
                                   else sal end
                   bonus
   from emp;

   --DECODE 在group by 分组中的应用可以将字段值不同的记录看作一组()

   --统计人数，将职位是‘MANAGER','ANALYST' 看作一组，其余职业看作另一组分别统计人数

SELECT COUNT(*) NUM, decode(job, 'MANAGER', 'VIP', 'ANALYST', 'VIP', 'OTHER') JOB1
FROM emp
GROUP BY decode(job, 'MANAGER', 'VIP', 'ANALYST', 'VIP', 'OTHER')

--DECODE 在排序中的灵活应用
   --按字段内容排序，DEPT表中按’POERATIONS'、'ACCOUNTING'、'SALES'
                   --排序而不按照原来的字面顺序排序，注意：null比任何字符都大
   select deptno,dname,loc, decode(
       dname,'OPERATIONS',1,'ACCOUNTING',2,'SALES',3) a
   from dept
   order by decode(dname,'OPERATIONS',1,'ACCOUNTING',2,'SALES',3)

--排序函数 ROW_NUMBER() --组内排序
--同rownum一样也是给结果集编号，但是它可以分组后排序，允许对
   --结果集按照指定的字段分组，在组内再按照指定的字段排序，最终生成组内编号

   --TEST查看每个部门的工资排名？
   select ename,sal,deptno,
   ROW_NUMBER() over(
                   partition by deptno
                   order by sal desc) rank
   from emp;

--Rank 函数，生成组内不连续也不唯一的数字
   --同组内排序字段值一样的记录，生成的数字也一样

   select ename,sal,deptno,
   rank() over(
                   partition by deptno
                   order by sal desc) rank
       from emp;
--Dense_Rank()函数，生成组内不唯一但连续的数字
select ename,sal,deptno,
       dense_rank() over(
                   partition by deptno
                   order by sal desc) rank
   from emp;


--高级分组函数
--创建一张表，插入1000条数据

       create table sales_tab(
       year_id number not null,
       month_id number not null,
       day_id number not null,
       sales_values number(10,2) not null);

       insert into sales_tab
       select trunc(DBMS_RANDOM.value(2010,2012)) as year_id,
       trunc(DBMS_RANDOM.value(1,13)) as month_id,
                   trunc(DBMS_RANDOM.value(1,32)) as day_id,
                   round(DBMS_RANDOM.value(1,100),2) as sales_value
       from dual
       connect by level <=1000;

select * from sales_tab;


--集合操作
--并集 union/union all 全集，有交叉重复的
           --交集 intersect
   --差集   minus
       --TEST 并集
   select ename,job,sal from emp
   where job='MANAGER'
   union
   select ename,job,sal from emp
   where sal>2500;

   --全集有重复的
   select ename,job, sal from emp
   where job='MANAGER'
   union all
   select ename,job,sal from emp
   where sal>2500;
   --TEST 交集（共有部分，即使也是）
   select ename,job,sal from emp
   where job='MANAGER'
   intersect
   select ename,job, sal from emp
   where sal>2500;

   --TEST 差集（只在第一个结果集中存在，第二个结果集中不存在，即是结果集1减去结果集2的结果）

   select ename,job,sal from emp
   where job='MANAGER'
   minus
   select ename,job,sal from emp
   where sal>=2500

--高级分组函数
--rollup 、cube 、grouping sets ，都是在group by 后面使用的

   -- TEST 想要按着，年月日分组查看营业额，并且先显示在一条结果集中
   select s.year_id,s.month_id,s.day_id,s.sales_values
   from sales_tab s
   order by s.year_id,s.month_id,s.day_id

   --rollup():分组原则，参数逐次递减，一直到所有参数都不要，每一种分组都
   --统计一次结果，并且在一个结果集显示。

   --group by rollup (a,b,c)
   --等价于下面的一坨
--group by a,b,c
   --union all
   --group by a,b
   --union all
   --group by a
   --union all
   --全表

   --TEST查看每天，每月，每年以及总共的营业额？
   select year_id,month_id,day_id,
   sum(sales_values)
   from sales_tab
   group by rollup(year_id,month_id,day_id)

   --cube():每种组合分一次组
   --分组次数：2 的参数个数次方

   --group by cube(a,b,c)
--等价于
   --abc
   --ab
   --ac
   --bc
   --a
   --b
   --c
   --全表
   --TEST
   select year_id,month_id,day_id,
   sum(sales_values)
   from sales_tab
   group by cube(year_id,month_id,day_id)
   order by year_id,month_id,day_id

--grouping sets: 每个参数是一种分组方式，然后将这些分组统计并在一个结果集
--显示，仅查看每天与每月营业额？


   select year_id,month_id,day_id,
   sum(sales_values)
   from sales_tab
   group by
   grouping sets(
       (year_id,month_id,day_id), -- 精确到每天，必须把一个分组用括号括起来
       (year_id,month_id) -- 精确到每月
       )

   select s.year_id,s.month_id,s.day_id,
   sum(s.sales_values)
   from sales_tab s
   group by
   grouping sets(
       (year_id,month_id,day_id),
       (year_id,month_id)
       )
   order by s.year_id,s.month_id,s.day_id




--视图
--视图是数据库对象之一
   --所有数据库对象名字不能重复，所以，视图名字一般是以“v_"开头

--视图在sql语句中体现的角色与表相同
   --但是视图并不是一张真是存在的表，而只是对应一个select语句的查询结果集
   --并将其当作表看待而已
   --使用视图的目的是简化SQL语句的复杂度
   --重用子查询，限制数据访问

   --创建视图
   --该视图包含数据为10号部门的员工信息
--注意：创建视图时候如果提示没有权限，使用dba登陆后赋予scott用户权限,命令：grant create view to scott
   create view v_emp_10
   as
   select *
   from emp e
   where e.deptno=10

   --查看视图数据：
   select * from v_emp_10


   --修改视图对应的SQL查询语句
   create or replace view v_emp_10 --创建或替换视图v_emp_10
   as
   select empno id,ename name ,sal salary,deptno
   from emp
   where deptno=10;

   --检查视图结构
   desc v_emp_10;

   --说明：视图对应的子查询中的字段若含有函数或者表达式，
   --那么该字段必须指定别名，当视图对应的子查询中的字段使用了
   --了别名，那么视图中该字段就用别名来命名

   --修改视图
   --由于视图仅对应一个select语句，所以修改视图，就是替换该
   --select 语句而已

   create or replace view v_emp_10
   as
   select empno id,ename name,
   sal salary,deptno dep
   from emp
   where deptno=10


   --视图分为，简单视图，复杂视图，连接视图（基于多个表，也算复杂）
   --视图分为简单视图与复杂视图
   --简单视图：对应子查询中不含油关联查询，查询的字段不包含函数，
               --表达式等，没有分组，没有去重，反之则是复杂视图
       --对视图进行DML操作
       --仅能对简单视图进行DML操作，对视图进行DML操作就是对视图数据来源的基础
       --表进行的操作

   --在视图中插入的记录实际是存在于原始表中的，插入时不可违反原表约束
   insert into v_emp_10 (id,name,salary,dep)
   values(1001,'JACK',2000,10)

   select * from v_emp_10
      select * from emp
--在视图中修改记录的值，会在原始表中进行更改
   --对视图的DML操作就是对基表的操作，那么操作不当可能对基表进行数据污染
   update v_emp_10 set salary=3000 where name='JACK'

   Commit
   --对基表的污染案例
   --因为原视图是只查询10号部门的员工信息，如果插入20号部门的信息，也会插入到
       --基表中，但是视图中查询时看不到的，不可控的，所以污染了基表

   insert into v_emp_10 (id,name,salary,deptno)values
   (1002,'BOSE',3000,20)

   commit
   --更新同样存在更新后对数据不可控的情况
   --如果将视图里面的部门号都改为20号部门，那么，视图中将不可见原来的数据
   update v_emp_10 set deptno=20

   rollback --回滚


   --删除非视图中的数据，不会对基表进行删除，
   --但是删除视图中有的数据，真的会删除基表中的数据
   delete from v_emp_10 where deptno=20

--如何避免出现污染的情况
--创建具有 with check option 约束，可避免基表污染
   --为视图添加检查选项，可以保证对对视图的DML操作后视图对其看见
   --否则不允许进行该DML操作，这样就避免了对基表进行数据污染



   create or replace view v_emp_10
   as
   select e.empno id ,e.ename name,e.sal salary,deptno
   from emp e
   where e.deptno=10
   with check option
   --如果再插入20号部门的员工，会提示报错，如果要将视图中
   --10号部门的员工都改成20号部门，也会报错，违规


   --为视图添加制度选项，那么该视图不允许进行DML操作
   --with read only 只读约束
   create or replace view v_emp_10
   as
   select e.empno id,e.ename name,sal salary,deptno
   from emp e
   where e.deptno=10
   with read only

   --下面操作会报错：无法对只读视图进行DML操作
   update v_emp_10 set deptno=20


   --数据字典：就是几个特殊的表清单

   --user_objects 数据库中所有数据库对象
       --user_views 所有视图
       --user_tables 所有表
       --user_update_columns

   select object_name from user_objects u where
   object_type='VIEW' or u.object_type='TABLE'

   select table_name
   from user_tables; --查看表名在‘所有表’中

   select u.text,u.view_name --查看‘视图表’中的内容
   from user_views u


   --复杂视图，创建一个含有部门工资情况的视图，内容为：部门编号
   --部门名称，部门的最高，最低，平均，以及工资总和信息

   create or replace view v_salary
   as
   select e.deptno,d.dname,max(e.sal) maxsal,min(e.sal) minsal,avg(e.sal) avgsal,sum(e.sal) sumsal
   from emp e,dept d
   where e.deptno=d.deptno
   group by e.deptno,d.dname ---分组时，除了聚合函数的字段，其他出现在select中的字段都要出现在group by中
   with read only

   select * from v_salary

   create or replace view v_dept_sal
   as
   select d.deptno,d.dname,
   min(e.sal) min_sal,
                   max(e.sal) max_sal,
                   avg(e.sal) avg_sal,
                   sum(e.sal) sum_sal
   from emp e join dept d
   on e.deptno=d.deptno
   group by d.deptno,d.dname
   with read only

   select * from v_dept_sal

   --查看谁比自己所在部门平均工资高？

   select e.*
   from emp e,v_dept_sal v
   where e.deptno=v.deptno
   and e.sal>v.avg_sal

   --为什么不能对复杂视图进行DML操作，因为基表中没有视图中的字段

--删除视图
   --删除视图是不会对基表产生影响
           --但是，删除视图中的数据会将基表中的数据也删除掉！！！！！！！！！！！！！！

   drop view v_salary
   select * from v_salary --表或视图不存在

   rollback --删除操作无法回滚



--序列 (注意：系列不会回退，用了就是用了！！！！！！！！！！)
--生成一系列数字

   --序列也是数据库对象之一，作用是生成一系列数字
   --序列常用于为某张表的主键字段提供值使用
   --注意 --序列会在两种情况下断裂，1，多张表使用同一个序列，2，服务器断电，缓存消失
   create sequence seq_emp_id
   start with 1 --从1开始
   increment by 1 --步长值是1，
   --默认缓存是20

   --序列支持两个伪例
   --nextval: 获取序列下一个值
       --若是新创建的序列，那么第一次调用返回的是start with
       --指定的值，以后每次调用都会得到当前序列值加上步长值后的数字
       --nextval会导致序列发生步进，且序列不能回退

       --currval：获取序列当前值，即：最后一次调用nextval后得到的值，currval
       --不会导致步进，但是新创建的序列至少调用一次nextval后才可以使用currval

   --使用序列，没执行查询一次，步进一次
   select seq_emp_id.nextval
   from dual
   --查询序列当前值，不会产生步进
   select seq_emp_id.currval
   from dual

   --TEST 在emp中插入记录，使用序列递增自动改变empno
   --此时的 empno值为序列值的下一个值
           --使用序列为EMP表中插入的数据提供
   insert into emp(empno,ename,sal,job,deptno)
   values
   (seq_emp_id.nextval,'JACK',3000,'CLERK',10)

   select * from
   emp

   --删除序列
   drop sequence seq_emp_id



--索引（注意：索引要使用在数据量大、查询频繁的字段中）
   --数据库内部自己维护，不用关心，索引是给表的某个字段或某些字段加的
           --而不是给整个表加，索引就像是字典的目录以及路标一样，当数据改变，也是需要
           --维护一下索引的

           --给emp表中的ename字段加索引
       create index idx_emp_ename on emp(ename);
   --查询的时候不用关心怎么用，只要创建过这个字段的索引，就会自动显现有时
select * from emp where ename='JACK'
       --索引的统计与应用是数据库自动完成的，只要数据库认为可以使用某个
       --已创建的索引时就会自动应用

   --复合索引
   create index idx_emp_job_sal on emp(job,sal);
   --当做下面查询时，会自动应用该索引(注意：job,sal 的顺序要和索引一致)
   select empno,ename,sal,job from emp
   order by job,sal;

   --基于函数的索引
   create index emp_ename_upper_idx on emp(upper(ename));

   select * from emp where upper(ename)='KING';

   --修改和删除索引
   --修改索引
       --如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间liyonlv

           --重建索引
           alter index index_name rebuild;

           --删除索引当一个表上有不合理的索引，会导致操作性能下降，删除索引的语法

           drop index index_name;
           drop index idx_emp_ename;

--约束
--非空约束，两种创建方式
           create table employees(
           eid number(6),
           name varchar(30) not null, --非空
           salary number(7,2),
           hiredate date constraint employees_hiredate_nn not null --非空必须在一个字段后面，不可单独指定
           );
           --添加/修改表时添加非空约束
           alter table employees modify (eid number(6) not null);
           --取消非空约束修改为可以有空值
           alter table employees modify (eid number(6) null);


           --唯一性约束 unique 同样有两种写法
           --除了null之外不能有重复值
                   --唯一性约束可以保证表中字段的值任何一条记录都不可以初伏，nullchuwai
           create table employees(
           eid number(6) unique,
           name varchar(30) not null, --非空
           salary number(7,2),
           hiredate date constraint employees_hiredate_nn not null, --非空
           constraint employees_email_uk unique(email) --可以单独指定
           );


           --添加唯一性约束(在建表之后添加)
           alter table employees
           add constraint employes_ename_uk unique(name);
           --如果原表中该字段已经存在有重复的值，会报错-找到重复关键字
           --要将其找到清除才能正确添加唯一性约束

--主键约束
--一般每张表的第一个字段是主键，命名为ID

           --在建表时添加主键约束条件
           create table employees2(
           eid number(6) primary key,
           name varchar2(30),
           email varchar2(50),
           salary number(7,2),
           hiredate DATE
           );
           --如果重复插入该行记录，会报错‘违反唯一约束’
           insert into employees2
           (eid,name)
           values
           (2,'JACK');
           --添加主键约束
           alter table employees3
           add constraint employees3_eid_pk primary key(eid);

--外键约束
--oracle中一般不使用，很疼
           --主表的外键必须是附表的主键
           --如果使用外键约束，外键必须参照主键，同时制约主键和外键
           --外键里面的值必须是主键中有的或者是null

--检查约束
--限制字段值的插入，修改
           --如果有两个check 检查约束，必须同时满足

           --添加check检查约束
           alter table employees2
           add constraint employees2_salary_check
           check(salary>2000);
--正常插入数据
       insert into employees2(eid,name,salary)
       values(1236,'donna moble',2500);
select * from employees2
           --修改有约束的记录值为不满足条件
           update employees2 set salary=1300
           where eid=1236 --报错，违反检查约束

RuobaiMEN

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ORACLE部分自学笔记整理

--在命令行解锁账户--使用下面的语句解锁scott: alter user scott account unlock;--解锁之后可能会要求你该密码 tiger： alter user scott identified by tiger; create table employee( id number(4), name varchar...
复制链接

扫一扫