Oracle知识点笔记(二)

(一) 配置控制台

showpagesize;
set pagesize 23;
show linesize ;
set linesize 120;

(二) Oracle权限

A、系统权限：用户对数据库的相关权限。140多种
B、对象权限：用户对其他用户的数据对象访问的权限。20多种

(三) 角色

1、自定义角色：connect , dba , resource

2、预定义角色问题：

        A、希望suwenjun有权限查表emp

        B、希望suwenjun这个用户可以查看scott的表emp

对象权限：select、insert、update、delete、all、create、index

grant select on emp to suwenjun；
grant update on emp to suwenjun；

这样使用

SQL> select * from scott.emp;

那么希望suwenjun这个用户可以去增删改查的的权限交给suwenjun

grant all on emp to suwenjun；

scott希望收回权限

revoke select on emp from suwenjun;

(四) 对权限的维护

希望suwenjun这个用户可以查看scott的表emp，还希望suwenjun把这个权限给别人

grant select on emp to suwenjun with grant option;

如果是系统权限   system 给suwenjun 

grant connect to suwenjun with admin option，

代表suwenjun这个用户可以把这个权限向下传递
如果scott收回对suwenjun的权限，则通过suwenjun这个用户的授权的权限都被收回

 (五)  账户锁定

 

 (六)  Oracle数据类型

1、字符类

  a、char：定长，最大2000字符，查询速度快

  b、varchar2：变长，最大4000字符

  c、clob：字符型大对象，最大4G

2、数字类型

  number：范围：10的38次方，负数-10的38次方

3、日期类型

  date：年月日，时分秒

  timestamp：

4、图片，视频，音频

  blob：二进制数据

(七)  修改表

 1、添加字段

alter table tableA add （classid number（3））;

2、修改字段

alter table tableA modify （xm varchar（34））;

3、删除一个字段

alter table tableA drop column sal;

4、修改字段类型

5、修改表的名字

rename tableA to tableB;

6、删除表

drop table tableA;

(八)  修改日期形式

set session set nls_date_format ='yyyy-mm-dd';

查询空值的列

select * from student where birthday is not null;

(九)  保存点

savepoint aa;
rollback to aa;

(十)  查询
   如何取消重复的行

select distinct deptno ，job from dept;

  打开显示执行时间

select timing on;

  疯狂复制

insert into user（userID，userName） select * from user;

注：Oracle是将内容区分大小写的

判断空值函数 ： nvl
例如：nvl（comm，0）：如果comm为null则用0代替，如果不为空则用comm本身

SQL> select sal*13+nvl(comm,0)*13 as nian from emp;
select ename ，hiredate from emp where hiredate > '1-1月-1982';

SQL> select ename from emp where ename like 'S%';

SQL> select ename from emp where ename like '__O%';

SQL> select empno from emp where empno in (23,3);

SQL> select ename from emp where mgr is null;

select * from emp where (sal>500 or job ='MANAGER' ) and ename like 'J%';
select * from emp order by deptno asc , hiredate desc;
select ename ,sal from emp where sal =(select max(sal) from emp);
select avg(sal),max(sal),deptno,job from emp group by deptno,job;
select avg(sal) ,deptno from emp group by deptno having avg(sal)> 2000;

(十一)  多表查询

select ename , sal , dname from emp ,dept where emp.deptno = dept.deptno;

1、将雇员的姓名和工资，以及工资级别查询出来

select ename，sal，grade from emp tb1，salgrade tb2 where sal between tb2.losal and tb2.hisal;

2、显示员工的上级领导名字:自连接

select worker.ename as worker ,boss.ename as boss from emp worker,emp boss where worker.mgr=boss.empno;

select * from emp where sal > any (select sal from emp where deptno=30);

select * from emp where (deptno,job) = (select deptno,job from emp where ename ='SMITH');

3、复杂语句

SQL> select deptno,avg(sal) mysal from emp group by deptno;

select a2.ename ,a2.deptno ,a2.sal,a1.mysal from emp a2, (select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno = a1.deptno and a2.sal > a1.mysal;

(十二) Oracle分页

Oracle的分页有三种，以下介绍rownum分页

1、rownum分页

select a1.* ,rownum rn from (select * from emp ) a1;

2、显示rownum（Oracle分配的）

select a1.* ,rownum rn from (select * from emp ) a1 where rownum<=10;

select * from (select a1.* ,rownum rn from (select * from emp ) a1 where rownum<=10) where rn>=6;

    a、如果要指定查询列，那么只需修改最里层的select；

select * from (select a1.* ,rownum rn from (select ename ,deptno from emp ) a1 where rownum<=10) where rn>=6;

    b、排序 

select * from (select a1.* ,rownum rn from (select ename ,deptno ,sal from emp order by sal ) a1 where rownum<=10) where rn>=6;

3、查询表一共有多少条记录

select count(*) from emp;

4、小技巧：用查询结果创建新表
  作用：倒表

create table myemp (id,ename,sal) as select empno ,ename,sal from emp;

(十三) 合并查询
1、union去掉重复的记录

SQL> 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、minus取差集

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

4、取交集

(十四)  只读事务

只允许读操作，儿不允许dml操作
system 管理员：set transaction read only;
普通用户scott：insert into emp values;
那么system 用户：select * from scott.emp ；看不到新增加的记录;

(十五)  sql 函数
 

lower(char)
upper(char)
length(char)
substr(char,m,n):取字符子窜

select lower(ename) from emp;
select upper(ename) from emp;
select * from emp where length(ename)=5;
select substr(ename,1,3) from emp;//从第一个截取，截取3个

1、第一个字母是大写，后面都是小写

1、select upper(substr(ename,1,1)) from emp;
2、select lower(substr(ename,2,length(ename)-1) from emp;
3、合并select upper(substr(ename,1,1)) ||lower(substr(ename,2,length(ename)-1) from emp

2、将'A'替换成'我是'

select replace(ename,'A','我是') from emp ;

3、四舍五入

round(n,[m])
round(n,1):保留一位小数

4、截取数据
  trunc(comm,1):截取到小数点后的第一位
  trunc(comm)：直接截取到整数

5、返回小于或等于n的整数

  floor(comm)

6、返回大于n的整数
  ceil(comm)

7、求余数：
mod(10,2):
在做oracle 测试的时候可以用dual 表，是虚拟的表
select mod(10,2) from dual;

8、一下两个的效果是一样的：

select trunc(sal/30) ,ename from emp;
select floor(sal/30) ,ename from emp;

(十六)  日期函数

默认格式：dd-mon-yy

 1、sysdate ：返回系统时间

select sysdate from dual;

    问题1：查找入职8个月多的员工

select * from emp where sysdate > add_months(hiredate,8);

add_months(hiredate,8):入职时间在加上8个月

    问题2:显示满了10年的员工信息

select * from emp where sysdate >= add_months(hiredate,12*10);

    问题3:显示每个员工加入公司的天数

select sysdate - hiredate '入职天数' ,ename from emp;
select trunc(sysdate - hiredate) '入职天数' ,ename from emp;

    问题4：找出各月倒数第三天受雇的所有员工

    last_day(d)：返回指定日期所在月份的最后一天

select hiredate,last_day(hiredate) from emp;
select hiredate ,ename  from emp where last_day(hiredate) - 2=hiredate;

2、to_date函数

insert into emp values(9998,'小红','MANAGER',7782,to_date('1988-12-12','YYYY-mm-dd'),98.5,55.23,10);
insert into emp values(9999,'小红','MANAGER',7782,to_date('1988/12/12','YYYY/mm/dd'),98.5,55.23,10);

3、转换函数

select * from emp；
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;

    问题1、显示1980年入职的员工

select ename,to_char(hiredate,'yyyy') from emp;
select * from emp where to_char(hiredate,'yyyy')=1980;
select * from emp where to_char(hiredate,'mm')=12;//12月份入职的员工

(十七) 系统函数

sys_context：
terminal
lanuage
db_name
nls_date_format
sesssion_user
current_schema

host
select sys_context('userenv','db_name') from dual;
select sys_context('userenv','lanaugua') from dual;