Oracle基础知识

小型数据库：access/foxbase

中型数据库：MySql/Sql server/

大型数据库：Oracle(DBA认证就是数据库管理员)/Sybase/db2—相对来说使用较多

Oracle三大认证：Oca初级认证Ocp专家认证Ocm大师认证

Oracle权限大概有140几种

关系数据库的查询可分为两类：1、关系数据语言2、关系演算语言

五中关系代数：1)、并（union）2）、差(diff) 3)、笛卡尔积(Cartesianproduct)   4)、投影(projection)  5)、选择(selection)

数据库中子查询又叫做嵌套查询

数据库的字句是从左到右执行的

在Oracle中子查询就相当于子查询，必须指定别名，使用二分机制，速度快

如果想要创需要建视图超级管理员给各个同户名赋权，执行与查询一样简单，如：select rownum,a1.ename  from myemp a1;

赋权的操作，先登录超级用户，为子用户赋权，为：

grant create any view to scott;

 

 

赋予对象权限的句子：grant 权限的名字 on 表的名字 to 用户名

如：grant select,update on student to scott;

可以赋给其他的用户的权限：grant all on student to scott with grantoption;

删除myemp用户的全部的权限：revoke  all  onstudent  from myemp;

如果遇到表空间不足的问题：

alter user 用户名 quota unlimited on 表空间的名字;

Oracle中备份分为逻辑备份物理备份，在索引中存在于逻辑和物理上独立于已有表的结构

在Oracle运算符级别最高的是算术逻辑符最低的是逻辑运算符

同义词的概念为对象的一种别名，简化Sql的语句，分为私有同义词(synonym)与共有同义词(publicsynonm),创建语句为：createsynonymmyemp from scott.emp;--私有同义词；修改同义词：replace synonym  myemp  fromscott.emp;删除同义词：drop (or public) synonym from  myemp;

索引的出现提高了读取的速度，同时减少了操作磁盘I/O的的次数

复原误删除的信息：flashback table “” to before drop;

 

 

 

 

 

 

 

 

Oracle11g：g:代表网格

Oracle是用片来储存的，片由数据块组成的

Oracle中的临时段即使临时的工作空间

Oracle中数据库是由表空间~》段~》区(块)~》数据块~》模式(用户模式)

Oracle分为了三个进程，分别是：服务进程，用户进程，后台进程

常用的数据类型：varchar/varchar2(节省空间)/char(相对来说较快)/long/number(p,s)/date/clob(大数据)/raw/long raw/bfile/blob(二进制文件)/

在Oracle中多表的查询存在笛卡尔积的关系

在Sql和Oracle中都可以进行多个修改

Oracle备份数据的公式：exp  userid=system/orclfile=d:aa.dmp full=y  inctype=complete(可缓冲下次执行快速，可选)  buffer=设置的大小

在数据字典中只能使用dba_***,user_***,all_***

如：在scott用户下查看所有的表：selecttable_name from user_tables;

查看赋权与自己表中的表的信息：select table_name from all_tables;

代dba的需要使用管理员才能使用：conn  system/orcl;

查看结构： desc  dba_users;

查看信息;select username,password from dba_users;

查看授权的信息：desc dba_rocl_privs;

查看scott的权限：select* from dba_role_privs where GRANTEE='scott';

查看系统的权限：select * from system_privilege_map order by name;

查看所有的较色：select * from system_privilege_map order by name;

查看表的权限：select * from table_privilege_map order by name;

分页技术：

Select * from (Selecta1.*,rownum rn from (Select * from emp) a1 where rownum<=10) where rn>=4;

查找表中的表名：select tname  fromtab;or selecttable_name from user_table;

 

 

 

 

 

 

在所有的查询时注意条件的大小写

1、查看当前的用户：show  user

2、查看数据字典：select  *  from  tab;

3、清除当前的屏幕：host  cls;或者CLEAR

4、在其别名时可以用空格隔开与Sql不一样，如：select  name as     “姓  名” from  emp;

5、去除重复行(distinct)：如：select  distinct name from  emp;

Distinct:可用于后面的所有的列：如：select distinct name,sex  from emp;

6、伪表的使用：select  concat(‘Hello’,’Word’)  from dual;

7、等待测试链接网页形式：http://localhost:1521/isqlplus

8、把信息保存到指定的路径：开始语句：spool 保存路径：文件名字.类型    结束语句：spool  off 

9、回退可以直接使用：rollback

10、WHERE执行的顺序为从右往左判断

11、orger  by  升序排序 按照第一个执行，如果相同则执行第二个，以此类推( 升序为asc或降序为desc)

12、降序排列：select  *  from emp order  by  deptno desc;

升序排列：select *  from emp  order by deptno desc;

13、截取字符串：如：select  substr(‘HelloWord’,开始的值,终止的值) from dual;

 意思就是说：坐标从一开始，不包括+第三个。

14、查找长度：select  length(‘HelloWord’)from   dual;

15、获取系统的时间：select  sysdate  from dual;

16、左右填充：如：select  lpad(‘123’,5,’*’) 左, rpad(‘456’,5,’*’) 右from dual;

17、数字函数：四舍五入--round(45.88，2)  截--trunk(45.88,2) mod(求余)

18、查找最后一月的天数：select last_day(sysdate) from dual;

19、退出程序：exit

20、注释的方法：/** **/

21、设置分页：set pagesize  5;

22、创建用户：create user  xiaozhang identified m123;

删除用户：drop user xiaozhangcascade;赋权限：grant connectto myemp;(连接数据库的权限) grant resourceto myemp;(查询的权限)

修改用户：alter user myemp identified by myempp;

23、查询非空的数据：select *  from  emp where  sname is not  null;

24、修改多个字段：update emp set  ename=’’,id=’’ wherename=’’;

25、删除数据：delete from emp; 只删除数据，但可以用rollback回滚，保存点save  point 进行保存。droptable  emp;删除数据域结构

26、查看字段：dest   emp;

27、设置显示时间 ;set  timing  on ;

28、复制字段 ;insert into  emp(ename,name) select*  from emp ;是2的倍数进行复制

29、修改表名：alter table temp renameto temp1;

30、复杂查询：select avg(sal),sum(DEPTNO),MGR from  emp group by MGR having avg(sal)>20;

31、显示平均工资大于2000的部门号与平均工资

selectavg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;

32、对于(1)group by 和(2)having 和(3)orger by 同时存在的情况，一定记住顺序，

33、按照分组再晋级和分组同时存在的实例：

selectavg(sal),deptno from emp group by deptno having avg(sal)>2000 order byavg(sal);

34、显示薪水，员工名称，级别

selecta1.ename,a1.sal,a2.grade  from empa1,salgrade a2 where a1.sal between a2.losal and a2.hisal ;

36、查询ford的老板的上级

selecta1.ename,a2.job   from  emp a1,emp a2 where a1.empno=a2.mgr anda1.ename='FORD';

37、保存点的使用：

Drop table  emp ;

Savepoint a1;

Drop table empt;

Rollbask to  savepoint a1;

Commit;

38、使用||链接字符串：

Select ename || ‘的工资是’ || sal || ‘元’ from emp;

39、        查看那每一行的地址值：

Select roeid,ename from emp;

40、        rownum伪列的使用：rowid(伪劣的一种)使用伪劣可以快速的查找信息。如：select * from emp whereROWID='AAAMgzAAEAAAAAgAAA';

select rownum,ename from emp;

41、        查找数据的前四行；

Select * from  emp where rownum<5;

42、        查询与SMITH相同的部门与岗位,顺序不许相同

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

43、        如何显示高于自己部门平均工资的员工的信息（*）；

selecta1.empno,a1.empno,a1.job,a2.mysal  fromemp a1,( select DEPTNO,avg(sal) mysal

from emp group  by  DEPTNO ) a2 where  a1.deptno=a2.deptno and a1.sal>a2.mysal;

44、        查询前五条记录：

select  rownum,a1.* from emp a1 where rownum<=5;

45、        查询五行到十行的记录

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

46、        行到十行的记录并且工资为升序

 select  * from (select rownum rn,a1.* from (selectsal from emp order by sal) a1) where rn>=5 and rn<=8;

47、        创建表：

Create table myemp(id,ename,sal) as selectEMPNO,ename,sal fromemp;

48、        切换用户：

Conn  system/orcl

49、union(查找不重复的) union all(查找所有的) intersect(查找相同的)minus(减去)的使用，相对来说速度较快

selectename,sal,job from emp where sal>2000 unionselect ename,sal,job from emp where ename='MANAGR';

50、按照年薪降序、年龄降序列出财务部的员工姓名、年龄、年薪；(ceil向上取整)

select ename,ceil((sysdate-hiredate)/365),sal*12from emp where deptno in (select deptno from dept where dname='财务部') order by sal*12 desc ,hiredateasc;

51、锁的使用（for update waitnum(等待num秒)或 nowait (不等待)）;锁住数据：Select * from emp where empno=7369 for update;

解锁：rollbask; 或 commit;

52、更新小技巧：

Update emp set(sal,job,ename)=(selectsal,job,ename from emp where ename=’scott’ ) where ename=’xiaozhang’;

53、创建表空间：

CREATE TABLESPACEtablespacename

DATAFILE ‘filename’ [SIZE integer [K|M]]

[AUTOEXTEND[OFF|ON]];

54、只读事务：(设置了只读事物，其他的就不能查看了)

Set  transaction read only;

55、在数据库中Having一般用于集合函数的里面（min或max或sum或avg）

56、效率的问题：

有两个简单例子，以说明 “exists(存在)”和“in”的效率问题

1) select* from T1 where exists(select 1 from T2 where T1.a=T2.a) ;

    T1数据量小而T2数据量非常大时，T1<<T2时，1) 的查询效率高。

2) select* from T1 where T1.a in (select T2.a from T2) ;

     T1数据量非常大而T2数据量小时，T1>>T2时，2) 的查询效率高。

exists 用法：

请注意 1）句中的有颜色字体的部分，理解其含义；

其中 “select 1 from T2 where T1.a=T2.a”相当于一个关联表查询，相当于

“select 1from T1,T2     whereT1.a=T2.a”

但是，如果你当当执行 1）句括号里的语句，是会报语法错误的，这也是使用exists需要注意的地方。

“exists（xxx）”就表示括号里的语句能不能查出记录，它要查的记录是否存在。

因此“select 1”这里的 “1”其实是无关紧要的，换成“*”也没问题，它只在乎括号里的数据能不能查找出来，是否存在这样的记录，如果存在，这 1）句的where条件成立。

57、集合操作符包括：intersect(查找相同的部分)/union(或的意思)/union all/minus(第一个表不包括第二个表信息)

58、导出文件cmd下运行exp,导入cmd下运行imp导入

59、on关键字的使用

on 是关联条件,‘当...的时候’的意思
作用类似WHERE.
只有用join 的时候 才用.
有:
inner join ...on 
left outer join ...on 
right outer join ...on
几种情况.
如：
SELECT * 
FROM TABALE1 A
LEFT  OUTER JOIN TABLE2 B ON A.COL1=B.COL2.