oracle学习(二)

lshboo

　　Startup open; 启动数据库实例
　　select name from v$database;

　　增加数据库用户：createuser test11>　　用户授权: grantconnect,resource,dba to test11; grant sysdba to test11;
　　commit;
　　更改数据库用户的密码：(将sys与system的密码改为test.)
　　alter user sys indentified by test;
　　alter user system indentified by test;
　　alter user scott account unlock
　　子查询：
　　-----查询工资比scott高的员工信息
　　Select*
　　From emp
　　Where sal > (select sal
　　fromemp
　　where ename = ‘scott’)
　　子查询所要解决的问题是：问题不能一步解决
　　子查询类型：单行子查询(只返回单行记录)和多行子查询
　　子查询在主查询前一次执行完成，子查询的结果被主查询使用
　　--注意的问题
　　1、子查询必须在()中,采用一个合理的书写风格，可以在主查询的where、select from 、having后面放置子查询。Select 后面只能是单行子查询
　　2、group by后面不可以放置子查询
　　3、强调from后面放置子查询，这种情况用的最多最重要。
　　4、主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用即可
　　5、一般不在子查询中使用order by。只在一种情况下使用order by ：在top-N问题分析中，必须要使用order by。
　　6、一般先执行子查询，但是相关子查询除外。
　　7、单行子查询只能使用单行操作符，多行子查询只能使用多行操作符。
　　8、如果子查询返回的结果中含有null，则不可以使用not in （子查询），
　　Select ename,sal,(select job from emp whereempno = 7893) from emp
　　Select * from (select ename,sal from emp)
　　SQl优化：如果子查询和多表查询都可以解决问题，理论上尽量使用多表查询
　　多行子查询：
　　In：等于列表中的任何一个
　　Any：和子查询返回的任意一个值比较
　　//查询比30号部门任意一个员工高的员工信息
　　Select* from emp where sal any(select sal
　　from emp where deptno=30)
　　All：和子查询返回的所有值比较
　　查询不是老板的员工信息not in (...,null)问题
　　Select * from emp where empno notin (select mgr from emp
　　Wheremgr is not null)
　　如果结果集中含有null，不可以使用not in，但是可以使用in，因为所有金额null值比较的值都会为null，not in 等同于all ,in == any
　　A not in(10,20,null)==A!=10and a!=20 and a!=null
　　A =20,A in (20,10,null)==a=20 or a=10 or a=null
　　集合运算
　　--查询 10和20号部门的员工
　　交集：intersect
　　差集：minus
　　并集：union、union all
　　Select *from emp where deptno = 10
　　Union
　　Select *from emp where deptno = 20
　　1、参与运算的各个集合必须列数相同并且类型一致
　　2、采用第一个集合的表头作为最后的表头
　　3、如果要排序必须在每个集合后使用相同的order by
　　4、可以使用括号
　　Selectdeptno,job,sum(sal) from emp group by rollup(deptno,job)
　　Select deptno, job,sum(sal) from emp group by deptno,job
　　Union
　　Selectdeptno,to_char(null),sum(sal) from emp group by deptno
　　Union
　　Selectto_number(null),to_char(null),sum(sal) from emp;
　　/---------------------------------------------------------------/
　　DML语句：（dml就是增删改查，在sql中没有人叫crud）(可以rollback)
　　DDL语句：Create/alter/drop/truncate/table
　　Create/dropview
　　Create/dropindex(sequence)(不可以rollback)
　　DCL语句：commit/rollback
　　隐式插入null值：执行insert语句，有些咧没有指定，则默认为其默认值或者null
　　显示插入null值values(null)
　　--地址符 & ,类似于PrepareStatement，可以防SQl注入等问题
　　Insert into emp(empno,sal) values(‘&empno’,&sal)---输入 --/
　　地址符可以用在任意的DML语句中
　　Select &condi from emp---sal
　　数据的批量拷贝插入
　　Create table emp2 as select * from emp where1=2;
　　一次性奖emp中所有20号部门的员工插入到emp2表中
　　Insert into emp2 select * from emp wheredeptno = 20;
　　Updatetable set column = value,[..] where condition
　　需要注意更新中的数据完整性错误，也即是约束问题
　　Delete[from] table [where condition] / Truncate table
　　Delete和truncate的区别:
　　1、delete是DML语句，truncate是DDL语句，delete可以rollback
　　2、truncate先摧毁表在重建，delete逐条删除
　　3、从理论上讲truncate性能比delete，但实际上在oracle中delete性能比truncate性能好。
　　4、理论上讲Delete逐条删除会产生碎片，truncate不会
　　5、Delete不会释放空间，但是truncate会
　　Oracle数据库事务：原子性、一致性、隔离性、
　　1、事务的起始标志：DML语句
　　2、事务的结束标志：显示提交commit、rollback
　　隐式提交 DDL语句（insert-update delete这些除了自身的基本功能外，还含有隐式的提交功能）
　　正常退出 exit
　　事务的控制：
　　Commit – delete – 保存点A – insert – update – 保存点b – insert(出现错误)
　　Insertinto ...
　　SavepointA
　　Insertinto ...(错误)
　　Rollbackto savepoint A
　　Sql99事务的隔离级别:
　　Read uncommited 、read commited 、repeatable read、serializable
　　Oracle支持的sql 99中的2个隔离级别：read commited(默认)、serializable，oracle一共有3个隔离级别，还有一个read only
　　DDL语句创建和管理表：
　　常见的数据库对象：
　　表：基本的数据存储集合，由行和列组成
　　视图：从表中抽出的逻辑上相关的数据集合
　　序列：提供有规律的数值
　　索引：提高查询的效率
　　同义词：给对象起别名
　　表名和列名必须在1-30个字符之间，oracle默认的存储时为大写，数据库名称必须是1-8位和
　　Create table：必须有权限、存储空间 // 使用子查询创建表，如果子查询中有表达式，就必须给表达式去个别名
　　Create table emp2
　　As
　　Select empno,ename,sal*12 ‘年薪’ from emp where empno > 5000;
　　Varchar2(size)可变长字符数据
　　Char(size)定长字符数据
　　Number(p,s)可变长数据类型
　　Data日期类型
　　Long可变长字符数据，最大可达到2G
　　CLob字符数据，最大可达4G
　　Raw and long raw 原始的二进制数据
　　Blob二进制数据，最大可达4G
　　Bfile存储外部文件的二进制数据，最大可达4G
　　Rowid行地址
　　上述数据类型除了rowid其余的都是作用在列上面的数据类型
　　Select rowid ,empno from emp;//rowid就相当于一个指针
　　索引就相当于rowid
　　修改表：追加新列、修改列、删除列、重命名列
　　Alter table emp2 add photoUrl blob;
　　Alter table emp2 modify ename varchar2(40);
　　Alter table emp2 drop column photoUrl;
　　Alter table emp2 rname column photoUrl tophoto;
　　Droptable emp2;//并没有实际的删除表
　　Oracle的回收站机制：
　　查看回收站：showrecyclebin
　　清空回收站：purge recyclebin
　　Droptable emp2 purge//删除表，不经过回收站，彻底的删除
　　并不是所有的用户都有回收站(管理员没有回收站，回收站只针对普通的用户)
　　约束：
　　约束是表一级的限制
　　如果存在依赖关系，约束可以防止错误的删除数据
　　类型：
　　Not null 、unique、primary key、foreign key、check
　　Sex varchar2(4) check(sex in (‘男’,’女’))
　　Sal number check (sal > 0)
　　Frorign key:在字表中，定义一个表级的约束
　　References：指定表和附表中的列
　　On delete cascade：当删除父表时，级联删除子表记录（很危险，和他相关的有n张，但是只想删一张）
　　On delete set null：将字表的相关依赖记录的外建设置为null（常用）
　　Sal number(2) constraintemp_sal_min check(sal > 0)
　　Create table person(
　　Pidvarchar2(18) constraint person_Key primary key,
　　Pnamevarchar2(4) constraint person_name nut null,
　　Sexvarchar2(4)constraint person_sex check(sex in (‘男’,’女’))
　　Emailvarchar2(30) constraint person_email unique
　　Deptnonumber constraint peron_pk reference dept(deptno) on delete cascade
　　)
　　视图：(需要被授权grant create view to scott)
　　Create or replace view empView1(如果存在就替换)
　　As
　　Select empno,ename,sal,sal*12 annl fromemp;
　　With read only （屏蔽DML操作）
　　Select * from enpView1
　　优点：简化查询、限制数据的访问(银行用的最多)、同样的数据有不同的显示形式、
　　视图不能提高性能只能简化查询。
　　不建议通过视图对表进行修改，视图只能创建(create)和替换(replace)
　　Create view view1
　　As
　　Select * from emp where deptno =10
　　With check option
　　Insert into view1 values(...,10);
　　Insert into view1 values(...,20);
　　创建视图之前，写sql语句该怎么写就怎么写，
　　序列sequence：auto_increment(mysql)，这两个功能一样
　　序列就是一个数组，如果在创建的时候不指定任何的参数，内存：[1,2,...20][21.. 40]
　　序列在内存中，如果停电(目前在4)，恢复电后从21开始，这就造成序列不连续
　　什么是序列：
　　CreateSwquence sequence
　　Incrementby n,
　　Startwith n
　　Maxvaluen | nomaxvalue(min)
　　Cycle| nocycle
　　Cachen | nocache
　　序列是一个共有对象，A可用、b可用，这样也造成不连续
　　序列的属性：Nextval、currval，nextval应该在currval之前指定
　　Create sequence myseq;
　　Create table testSq(tid number,tnamevarchar2(20));
　　Select myseq.currval from dual;//error
　　Select myseq.nextval from dual;
　　Insert into testsq valus(myseq.nextval,’aa’)
　　回滚之前的操作也会造成序列的不连续
　　Alter|drop sequence mysq .... 修改序列只会影响以后的值
　　索引：
　　通过指针加速oracle服务器的速度
　　Create|drop index myindex on emp()
　　条件：
　　首先必须是大表
　　列数据值分布的特别散
　　列经常在where字句或链接条件中出现
　　表经常被访问而且数据量很大，访问的数量占据数据总量的2%到4%
　　同义词：
　　Conn hr/hrshow user
　　PLSQL语法：过程语言的sql语句，是对sql语言的过程化扩展，指在sql命令中增加了过程处理语句(分支、循环等)，使得sql语言具有过程处理能力，plsql是执行最快的。
　　Declare变量的说明
　　begin
　　dbms_output.put_line(‘helloworld’);Syso.println()
　　end ;
　　在默认情况下oracle输出开关是关闭的，set serveroutput on
　　/ 执行上一条sql
　　Descdbms_output
　　Declare说明部分（变量说明、光标声明）
　　Begin
　　语句序列
　　Exception
　　例外处理语句
　　End
　　说明变量：char、varchar2、date、number、boolean、long
　　Marriedboolean:=true
　　Psalnumber(7,2)
　　My_nameemp.ename%type;引用类型，emp表中列ename的类型
　　Emp_recemp%rowtype 记录性变量
　　--引用变量：打印7839的姓名和薪水
　　Declare
　　penameemp.ename%type;
　　psalemp.sal%type;
　　Begin
　　Selectename,sal into pename,psal from emp where empno=7839;
　　Dbms_output.put_line(pename||’薪水是’||psal)
　　End;
　　Emp_rec Emp%rowtype;代表一行的类型，如果有多列，引用Emp_rec.ename
　　Declare
　　Emp_rec Emp%rowtype;
　　Bigin
　　Select* intoemp_rec from emp where empno=7839
　　Dbms_output.put_line(emp_rec.ename||’的薪水是’emp_rec.sal)
　　End;
　　If语句：
　　If 条件 then 语句1;语句2;end if;
　　If 条件 then 语句1;else 语句2;end if;
　　If 条件 then 语句1;else if 语句2; else 语句3;end if;
　　判断用户输入的数字
　　Accept(sqlplus命令, 接收键盘输入) num prompt ‘请输入一个数字’
　　按职工的职称张工资，总裁涨1000，经理800
　　Declarepjob varchar2(20);
　　Bigin
　　Selectjob into pjob from emp where empno = 7839;
　　Ifpjob=’ceo’ then update emp set sal=sal+1000 where enopno=7839
　　Elseif ..
　　End;
　　While
　　-while
　　While total 10;
　　dbms_output.put_line(pnumber);
　　pnumber := pnumber + 1;
　　endloop;
　　end;
　　declare
　　光标：用于存储一个查询返回的结果集
　　Cursor 光标名[（参数类型）]Is select 语句
　　在使用一个光标之前要打开 open XX，就相当于执行一条sql语句
　　取一行光标的值：fetch c1 into pjob，fetch作用是取出数据并且将光标移到下一项、行.光标打开后可以不关闭,但是oracle默认不关闭的最大光数300(show parameters cursor)
　　光标的三个属性：
　　Isopen 是否被打开true、false
　　Rowcount 行数
　　Notfound是否有值
　　Declare
　　Cursorcemp is select ename,sal from emp;
　　Penameemp.ename%type;
　　Psalemp.sal%type;
　　Begin
　　Opencemp;
　　Loop
　　Fetchcemp into pename,psal;
　　Exitwhen cemp%notfound;//光标中没有值就退出
　　Dbms_output.put_line(pename||’薪水是’||psal);
　　Endloop;
　　Closecemp;
　　End;
　　按职工的职称张工资，总裁涨1000，经理800
　　Declare
　　Cursorcemp is select empno,type,sal,from emp;
　　pempnonumber;
　　ptypevarchar2(5);
　　psalnumber;
　　Begin
　　Opencemp;
　　Loop
　　Fetchcemp into pepno,ptype,psal;
　　Eixtwhen cemp%notfound;
　　Ifptype = ’总裁’ then update emp set sal = psal+1000 where empno = pempno;
　　Elseifptype = ’经理’ then update emp set sal = psal+800 where empno = pempno;
　　Elseupdate emp set sal = psal+400
　　Endloop;
　　Closecemp;
　　Commit;(原因是隔离级别(默认读已提交)，不提交在另一个命令行看不到结果)
　　End;
　　带参数的光标：
　　查询某个部门的员工姓名(要带部门参数)
　　Declare
　　Cursorcemp(pdno number) is select ename from emp where deptno = pdno;
　　Opencemp(10);//10号部门
　　。。。。。。。。。省略
　　例外是用来增强程序的健壮性和容错性
　　系统例外：(oracle的命名规范,_、大写)
　　Not_data_found(没有找到数据)
　　Too_many_rows(select …into语句匹配多行)
　　Zero_divide(被零除)
　　Value_error(算数或转换错误)
　　Time_on_resource(在等待资源时发生超时(分布式))
　　declare n number;
　　begin
　　n := 1/0;
　　exception
　　when Zero_divide then dbms_output.put_line('0不能做被除数');
　　when value_error then dbms_output.put_line('转换异常');
　　when others then dbms_output.put_line('其他的异常');
　　end;
　　用户自定义的例外
　　这些例外需要用户处理，用户最好不要抛给数据库去处理when others
　　Declareno_data exception;//没有数据例外
　　Fetchc1 into job;
　　Ifc1%notfound then raise no_data;
　　Endif;
　　...
　　Exception
　　Whenno_data then insert into log values(‘..’);
　　End