Orcale个人总结

Orcale个人总结

个人代码

-- ORACLE 提供虚拟表给我们进行函数等测试
select 5+6 from dual -- dual 虚拟表

--使用number取代int  使用varchar2 取代 varchar

--数据库的约束有两种 四组合
        第一种:启用验证        ENABLE(启用) DISABLE(不启用)
        第二种:验证状态        VALIDATE(验证) NOVALIDATE(不验证)
        组合为下:
        1:默认的 RENABLE+VALIDATE 对于插入数据的时候进行验证,并对表中已经存在的数据进行验证
        2:ENABLE+NOVALIDATE        对于插入的数据进行验证,但是个已经存在的数据不验证
        3:DISABLE+VALIDATE        对于插入的数据不验证,但是对于表中存在的数据验证
        4:DISABLE+NOVALIDATE       对于插入的数据不验证,对于表中已存在的数据也不验证
        create table LXF(
       id int,
       name varchar2(20),
       sex varchar2(5),
       age number,
       constraint user_c_sex check(sex in('男','女')),
       constraint user_c_age check(age between 16 and 20));
        
ALTER TABLE LXF modify constraint user_c_sex DISABLE NOVALIDATE
insert into LXF values(1,'催化','母',20)
ALTER TABLE LXF modify constraint user_c_sex ENABLE NOVALIDATE

--ORACLE 没有limit 关键字  也就代表不能使用limit 来完成分页
--ROWNUM 伪列 oracle 只能通过伪列来完成分页,由于 伪列的数据是一条一条产生的,所以在使用ruwnum的当前sql中不能使用>对其进行比较
SELECT ROWNUM R,A FROM(
SELECT AVG(SAL) A FROM EMP GROUP BY DEPTNO ORDER BY A DESC) WHERE ROWNUM<3

--求出 第6到第10个员工:由于伪列一条一条产生,所以必须在内部sql先产生所有需要的伪列,然后再外层sql 进行筛选
SELECT * FROM(
SELECT ROWNUM R,E.* FROM EMP E WHERE ROWNUM<11) WHERE R>5

--求出每个人在部门内的工资排名,并显示所有信息
--partition by 指定根据谁去进行分组
--order by 对前面分组后的结果集进行排序
--使用rank 可以将所有的数据按照小组进行划分,然后进行组内排序(名次排序,有可能有并列的)
select rank() over(partition by deptno order by sal desc) empno,ename,sal,deptno from emp
--row_number使用与rank相同,但是取的是行号,不会有并列的说法
select row_number() over(partition by deptno order by sal desc) empno,ename,sal,deptno from emp

--oracle中没有主键自增长
--oracle中有序列 sequence,可以代替主键自增长
sequence 里面的属性
        1:      increment       每次自增的长度
        2:      start with      表示序列的值是从几开始的
        3:      maxvalue        表示序列的最大值是多少
        4:      minvalue        表示序列的最小值是多少
        5:      cycle /nocycle  表示序列是否循环
        6:      cache /nocache  表示缓存的个数
        序列有两个取值方式
                1:      nextval         代表下一个值
                2:      currval         代表当前值
                
                
                
                

/* ORACLE 常用函数：
1：单行函数 ：每一个函数只输入一行结果，返回一个结果
        1：字符函数
               1: ASCII('A') 返回对应ASCII码表中的位置
               2：concat('HELLO','oracle') 字符串拼接   或'HELLO'||'ORACLE'
               3：LENGTH('HE LLO')  返回字符串长度
               4：instr('hello oracle','or')  返回or 在 hello oracle 中出现的位置，索引从1开始
               5： LOWER('HELLO') 返回小写字符串
               6： UPPER('hello') 返回大写字符串
               7：LTRIM('QWHELLO','QW') 截取字符串QWHELLO 中的QW，需注意只能截取以QW开头的字符串
               8：RTRIM('HELLOZX','ZX' 截取字符串HELLOZX 中的ZX，需注意只能截取以ZX结尾的字符串
               9:TRIM('-' FROM '-HELLO-')截取字符串-HELLO-'中前后的-，只能截取前后
                   注意：上面的三个函数比较类似，如果不填入第二个参数的话，默认是截取空格
               10：REPLACE('ABCG','G','DEF') 字符串替换，把字符串ABCG中的G换成DEF，
                   注意：如果ABCG中有多个G 则会多次替换
               11：SUBSTR('abcde',2,6) 从字符串abcde中的第二位开始显示，显示6位
        2：数字函数
                1：ABS(X) 显示X的绝对值
                2：ACOS（X）显示X的反余弦
                3：COS（X）显示X的余弦
                4：CEIL（X）大于或等于X的最小值
                5：FLOOR(X) 小于或等于X的最大值
                6：LOG（X,Y）X为低Y的对数
                7：MOD（X，Y）  类似与取于，X除以Y之后的余数
                8：POWER(X,Y) X的Y次幂
                9：ROUND(X,[Y]) 把数字X在Y的位置上四舍五入
                10：SQRT（X） X的平方根
                11：TRUNC(X，[Y]) 数字X在Y的位置上截断
        3：转换函数
                1:TO_CHAR(SYSDATE,'yyyy"年"mm"月"dd"日" HH24:MI:SS') 
                  TO_CHAR(SYSDATE,'yyyy-mm-dd HH24:MI:SS')
                2:TO_DATE('1990-12-12 15:24:36','yyyy-mm-dd HH24:MI:SS')
        4：日期函数
                1：ADD_MONTHS(SYSDATE,5)在SYSDATE上增加5月
                2：LAST_DAY(SYSDATE) 返回当前月份的最后一天
                3：months_between(ADD_MONTHS(SYSDATE,5),SYSDATE) 返回两个日期相差的月份
                4：NEXT_DAY(SYSDATE,1) 返回下个星期的星期几，注意国外日期
        5:其他单行函数
                1：NVL(COMM,100)  把comm中的空值替换掉
                2：NVL2(COMM,comm*2，200) 升级版，如果comm有值则值*2，如果没值则变成200
2：聚合函数：可以对多行进行操作，返回一个结果集例如 sum（）
                1:avg()
                2:sum()
                3:min()
                4:max()
                5:count()
*/

CREATE SEQUENCE S1
INCREMENT BY 3
START WITH 6
MAXVALUE 30
MINVALUE 3
CYCLE
NOCACHE

INSERT INTO LXF VALUES(S1.NEXTVAL,'123','男',16);
INSERT INTO LXF VALUES(S1.CURRVAL,'123','男',16);

ALTER SEQUENCE S1 INCREMENT BY 1

DROP SEQUENCE S1

--同义词 : 就是给表起一个别名用于简化查询
--如果有public修饰 在所有用户中都能访问,没有public 则只能在当前用户使用
CREATE PUBLIC SYNONYM s2 FOR SALGRADE

SELECT * FROM s2
INSERT INTO S VALUES(6,66,666)

DROP PUBLIC SYNONYM S2


VIEW 视图: 就是将我们查询出来的结果集进行封装,然后方便下次使用,简化查询
            视图本身并不保存任何数据,只是存储了查询的sql语局而已
            简单视图: 封装的是单表查询的sql,由于里面只封装一张表,所以针对视图进行CRUD会直接作用到表中
            复杂视图: 封装的是多表查询的sql,由于封装了多张表所以无法进行DML操作
            
CREATE VIEW V1 AS SELECT * FROM EMP WHERE SAL>3000

SELECT * FROM V1

CREATE VIEW V2 AS SELECT E.*,dname,loc FROM EMP E,dept D WHERE E.DEPTNO=D.DEPTNO AND E.SAL>2000

SELECT * FROM V2

DROP VIEW V2

--nvl  看comm是否为null 如果是null 则变为0
select sal+nvl(comm,0) from emp;
--nvl2 看comm 是否有值 如果有comm+500 如果没有 则变为1000
select sal+nvl2(comm,comm+500,1000) from emp

索引:     相当于图书的目录,可以有效的提高查询效率创建索引也需要消耗系统的一些性能所以并不是索引越多越好,并且索引虽然高了查询但是一般都会
           牺牲增删改的性能

CREATE INDEX N ON LXF(name) 

DROP INDEX N
B树索引(默认的)
位图索引
单列索引
复合索引
函数索引

alter index n coalesce --清楚掉索引碎片  合并索引(合并的是之前索引残留的碎片)
--索引
select * from ALL_INDEXES WHERE TABLE_NAME='LXF'

CREATE INDEX N ON LXF(nvl(age,100)) 
--唯一索引
CREATE UNIQUE INDEX N2 ON LXF(nvl(age,100)) 

select nvl(age,100) from lxf

select ASCII('飞') from dual
-- 交 交集  上面的结果集与下面的结果集有哪些重复,则显示哪些
SELECT * FROM DEPT WHERE DEPTNO=10
INTERSECT
SELECT * FROM DEPT
--差 上面届国际减掉下面结果集返回剩下额内容
SELECT * FROM DEPT 
MINUS
SELECT * FROM DEPT WHERE DEPTNO=20
--并  上面结果集
SELECT * FROM DEPT 
UNION ALL
SELECT * FROM DEPT WHERE DEPTNO=20

--SQL 优化
1:      尽量避免盘扫描,在where和order by 使用的列上创建索引
2:      在where中对列进行null判断,会让数据库放弃索引,直接使用全盘扫描
3:      尽量避免使用<>与!=,这样会让数据库放弃索引,直接使用全盘扫描
SELECT * FROM DEPT WHERE DEPTNO<20
UNION ALL
SELECT * FROM DEPT WHERE DEPTNO>20
4:      尽量使用or来连接条件,这样会让数据库放弃索引,直接使用全盘扫描
SELECT * FROM DEPT WHERE DEPTNO=30
UNION ALL
SELECT * FROM DEPT WHERE DEPTNO=40
5:      尽量避免使用in与not in,这样会让数据库放弃索引,直接使用全盘扫描
6:      模糊查询的时候尽量避免在前面使用通配符%  like '%z',这样会让数据库放弃索引,直接使用全盘扫描
7:      尽量不要在where后面对字段进行表达式操作
        select * from emp where sal/5>1000;  错误写法
        select * from emp where sal>5*1000;  正确写法
8:      尽量避免在where字句中对列进行函数操作,这样会让数据库放弃索引,直接使用全盘扫描
9:      如果使用的索引为复合索引的话,必须使用索引的第一个字段作为条件,否则会让数据库放弃索引,直接使用全盘扫描
10:     索引并不是越多越好,一张表的索引尽量不要超过5~6个
11:     尽量使用varchar2|varchar 来代替char
12:     对于大型数据尽量避免使用游标,因为游标对的效率较差

--PL/SQL 是一门高级语言,专门用于在各种环境下对oracle数据库进行各种操作,这门语言集成与数据库,所以可以对数据进行快速有效的处理

--/
BEGIN
dbms_output.put_line('HELLO JAVA');
END;
/

--变量,分支,循环,语法

--变量

--/
DECLARE
i number default 8;
BEGIN
i:=mod(i,5);                    --让i 等于 i 取余5
dbms_output.put_line(i);
END;
/
--如果刘大于王输出刘大
--/
DECLARE
lage number default 56;
wage number :=65;
BEGIN
if lage>wage then
dbms_output.put_line('刘大');
END if;
END;
/

--如果刘大于王输出刘大,如果王大于刘输出王大,否则输出刘等于王
--/
DECLARE
lage number default 56;
wage number :=56;
BEGIN
if lage>wage then
dbms_output.put_line('刘大');
else if wage>lage then
dbms_output.put_line('王大');
else
dbms_output.put_line('刘等于王');
END if;
END if;
END;
/

--while循环
--/
DECLARE
i int :=0;
begin
while i<=10 loop
dbms_output.put_line(i);
i:=i+1;
END loop;
END;
/

--for 循环
--/
begin
for i in 1..100 loop
dbms_output.put_line(i);
end loop;
end;
/

--/
begin
for i in reverse 1..100 loop
dbms_output.put_line(i);
end loop;
end;
/

--loop 循环
--/
declare
i int :=1;
begin
loop
dbms_output.put_line(i);
i :=i+1;
exit when i>50;
end loop;
end;
/