oracle学习笔记（1）

1、sql*plus常用命令
1）disc[onnect]  用来断开与当前数据库的连接
2）passw[ord]  用来修改用户的密码，如果想修改其它用户的密码，需要用sys/system登陆
3）show urser  显示当前用户名
4）exit 断开连接，同时退出
5）&  可以替代变量，而该变量在执行时，需要用户输入。
   select * from emp where job="&job"
6) eidt  可以编辑指定的sql脚本
   edit d:\a.sql
7) spool 将slq*plus 屏幕上的内容输出到指定的文件中去。
   spool d:\b.sql 并输入 spool off    
8) 设置行的大小默认80：set linesize
9) 连接命令：conn system/netjava
10）运行脚本start和&：start d:\a.sql或者& d:\a.sql
11) set timing on 显示操作所耗时间
2、用户管理
1) 创建用户（只有sys和System具有权限） create user champion identified by netjava;
2）修改密码  alter user champion  identified by "champion"

3) 删除用户 drop user champion
4) 赋予用户权限grant(角色：connect、dab、resource等,对象权限：select、update、delete、insert等)
   系统权限：grant connect to champion
   对象权限：grant select on emp to champion
             grant all on emp to champion
5）收回权限 revoke select on emp form champion
6) 权限传递
   对象权限：grant select on emp to champion with grant option
   系统权限：grant connect to champion with admin option 
7) 账户锁定： profile 管理用户口令
   创建profile 文件：create profile lock_account(可以改) limit failed_login_attempts 3（可以改） password_lock_time 2（可以改）;
   给用户配置文件：alert user champion profile lock_account;
8）账户解锁：alert user champion account unlock;
9) 终止口令：定期要用户修改密码
   创建profile文件：create profile myprofile limit password_life_time 10 password_grance_time 2
   给用户配置文件：alert user champion profile myporfile
10)口令历史：密码在定期不可以重复使用
   创建profile文件：create profile myprofile limit password_life_time 10 password_grance_time 2 password_reuse 10
   给用户配置文件：alert user champion profile myporfile   
11)删除profile
   drop profile myprofile [cascade]
3、数据类型
1）字符型
   （1）char 定长 最大2000字符 如：char(10)，若存放“冠军”，则前四个字符放“冠军”，
        后添6个空格,查询时间快，因为它先比较长度是否相等
   （2）varchar2(20)  变长 最大4000字符，可以节省空间
   （3）clob 字符型大对象，最大4G
2) 数字类型
   number 范围-10的-38次方至10的38次方，可以表示小数，也可以表示整数
   number（5，2）表示一个小数有5位有效数字，2位小数，范围 -999.99-999.99
   number(5) 表示一个五位整数，范围-99999-99999
3）日期类型
   （1）date 包含年月日时分秒  默认格式：‘DD-MON-YY’ ‘16-8月-89’
        修改默认的格式 alter session set nls_date_format='yyyy-mm-dd'
   （2）timestamp 对date的拓展，精度高一点，微秒级
4）图片类型
   blob 二进制数据 可以存放图/声音 4G
4、表的操作
   1）写日志与数据恢复
      设置回滚点：savepoint a
      回滚到a点： rollback to a;
   2) truncate table student;删除表中的所有记录，表结构还在，不写日志，
      无法找回删除的数据，删除速度很快。
   3）简单查询
     （1）select count(*) from student 查看表中的记录数
     （2）select distinct name from student 查询表中不重复的名字
     （3）select sal*12+nvl(comm,0)*12 from emp 如果comm为null,则用0来替代
      (4) select ename  from emp where ename like 'S%' 查找名字以S开头的员工
           % ：表示任意0到多个字符，_:表示单个字符
     （5）select empno , ename from emp where empno in(200,300,400) 查找职工号为200，300，400的员工
     （6）select ename from emp where sal is null;
      (7) select * from emp order by sal; 按工资从低到高排序
      (8) select * from emp order by sal desc ;从高到低排序
      (9) select * from emp order by deptno,sal desc;
      (10)select ename , sal*12 '年薪' from emp order by '年薪';
   4) 复杂查询
     （1）常用函数：max,min，avg,sum,count
     （2) select ename , sal from emp where sal=(select max(sal) from emp);
          查询工资最高的员工姓名和工资
     （3）group by 和 having 字句
          查询每一个部门的平均工资和最高工资
          select avg(sal), max(sal),deptno from emp group by deptno;
          查询部门平均工资在2000以上的部门
          select avg(sal),max(sal), deptno from emp group by deptno having avg(sal)>2000;
     （4）分组函数只能出现在选择列表（即select 后面的）、having、order by 句子中
   5) 多表查询
      (1) select e.ename , e.sal ,d.dname from emp e, dept d where e.deptno=d.deptno and e.ename='SMITH';
      (2) select e.ename,e.sal,s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;  
      (3) 一张表查询select worker.ename , boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='FORD';
   6）子查询（嵌套查询）
      数据库在执行sql时，是从右到左
     （1）单行子查询是指只返回一行数据的子查询语句
          select e.ename  from emp e where e.deptno=(select deptno from emp where ename
     （2）多行子查询
          select * from emp where job in (select distinct job from emp e where e.deptno=10 )
          select * from emp where (deptno ,job)=(select deptno ,job from emp where ename='SMITH');
     （3）all关键字
          select * from emp  where sal >= all(select sal from emp where deptno=30);
     （4）any 关键字
          select e.ename , e.sal , e.deptno from emp e where e.sal > any ( select sal from emp where deptno = 10);
     （5）在from语句时将查询出来的数据当作一个表来看待，需要指定别名
          select * from emp e, (select deptno,avg(sal) mysal from emp group by deptno) e1 where e.deptno=e1.deptno and e.sal> e1.mysal;
  7）oracle分页,分页比较复杂，采用的是嵌套查询方式，二分法，速度比较快，但是语句比较复杂
      (1) rownum分页 (select * from emp)
      (2) 显示rownum，oracle 自动分配的
          select e.*,rownum rn from (select * from emp) e;
     （3）显示前5条数据，注意的是这里的条件rownum<5不可以写成rn<5,否则没有数据
          select e.* ,rownum rn from emp e where rownum<5;
          如果写>5成：select e.* ,rownum rn from emp e where rownum>5;
          将没有数据，要想用>条件，则必须再嵌套一个子查询
      (4) 显示第6到第10调数据，注意，最外层的条件rn>=6不可以写成rownum>=6，否则也没有数据
          select e2.*from (select e.* ,rownum rn from emp e where rownum<=10) e2 where rn>=6;
     （5）对于需要查询指定的字段时或者排序、分组等，只需要改变最里面的查询条件
          查询员工名字和薪水
          select e2.*from (select e.ename, e.sal ,rownum rn from emp e where rownum<=10) e2 where rn>=6;
   8）用查询结果创建新表
      create table mytable(id,name,sal) as select e.empno,e.ename,e.sal from emp e;       
   9）合并查询（union,union all,intersect,minus）
     （1）union 用于取得两个结果集的并集。
          select ename ,sal, job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER'
     （2）union all 用于把所有查询到的记录联合起来
          select ename ,sal, job from emp where sal>2500 union all select ename,sal,job from emp where job='MANAGER'
      (3) intersect 用于取得两个结果集的交集
          select ename ,sal, job from emp where sal>2500 intersect select ename,sal,job from emp where job='MANAGER'
     （4）minus 用于取得两个结果集的差集，只显示在第一个集合但是不在第二个集合的数据
          select ename ,sal, job from emp where sal>2500 minus select ename,sal,job from emp where job='MANAGER'