Oracle总结

oracle数据库的安装下载：

https://blog.csdn.net/west_xia_616/article/details/79791139

 

访问数据库的原理：

 

 

oracle 中的sqlplus

显示当前用户

SQL> show user



SQL> --员工表的结构

SQL> desc emp



SQL> --清屏

SQL> host cls

                                                                                                                                       

SQL> -显示被查询表的行宽

SQL> show linesize

 --设置行宽

SQL> set linesize 120

SQL> --设置列宽

SQL> col ename for a8   a表示字符串   8个字符

SQL> col sal for 9999    数字       用 9 来表示一位数字

SQL> /     表示执行上一条SQL语句或是SQLplus语句

  

修改上一条语句

方式二：

SQL> ed  把上一条语句加入系统默认的编辑器中  （常用于修改上一条语句）

已写入 file afiedt.buf   写完之后直接退出  语句就会过来？？（语句最后不要加分号）

 

 

oralce中序列 索引 函数  视图  存储过程  

序列：

定义: 是oacle提供的用于产生一系列唯一数字的数据库对象。

序列有什么作用？

1.  自动提供唯一的数值

2.  共享对象

3.  主要用于提供主键值

4.  将序列值装入内存可以提高访问效率

实例应用

//实现id的自动递增

//第一步

create table cdpt(

id number(6),

name varchar2(30),

constraint pk_id primary key(id)

);



Create sequence seq_cdpt

Increment by 1

Start with 1

Maxvalue 999999

Minvalue 1

Nocycle

nocache



insert into cdpt values(seq_cdpt.nextval,’feffefe’);

commit;

select * from cdpt;

 

索引：（目录）

索引可以大大的提高查询的效率  我们只需要知道如何创建索引就可以了Oracle自己来维护这个索引（原理可以理解成所对应的列的地址（rowid）重排到一张表中，以后查询的话直接查询这张表）

Create index deptno_index on emp(deptno)

把什么列所谓索引：（个人理解）

1.不一定是唯一值的列

2.经常使用的

所以我们在操作oracle的时候  除了需要建表导入数据 同时也需要创建序列或是索引

视图（只针对查询）

把复杂的查询语句弄成视图  这样以后就不需要在输入复杂的查询语句了

另外，基于安全考虑，可以针对特定的视图赋给用户权限，而不是给用户赋给一个或几个表的权限，这样可以避免误操作更新数据的不安全因素。

存储过程：（sql语句  可以输入参数 ）

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某用户才具有对指定存储过程的使用权。

 

 

mysql 和oracle数据库差异

数据类型

1。NUMBER int / DECIMAL DECIMAL就是NUMBER(10,2) (10表示最大位数，小数位数)这样的结构INT就是是NUMBER(10)，表示整型； MYSQL有很多类int型，tinyint mediumint bigint等，不同的int宽度不一样

2 Varchar2（n） varchar(n)

3 Date DATATIME 日期字段的处理

MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为 SYSDATE, 精确到秒，或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)年-月-日 24小时:分钟:秒的格式YYYY-MM-DD HH24:MI:SS TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.日期型字段转换成字符串函数TO_CHAR(‘2001-08-01’,’YYYY-MM-DD HH24:MI:SS’)

日期字段的数学运算公式有很大的不同。MYSQL找到离当前时间7天用 DATE_FIELD_NAME ＞ SUBDATE（NOW（），INTERVAL 7 DAY）ORACLE找到离当前时间7天用 DATE_FIELD_NAME ＞SYSDATE - 7;

MYSQL中插入当前时间的几个函数是：NOW()函数以`'YYYY-MM-DD HH:MM:SS'返回当前的日期时间，可以直接存到DATETIME字段中。CURDATE()以’YYYY-MM-DD’的格式返回今天的日期，可以直接存到DATE字段中。CURTIME()以’HH:MM:SS’的格式返回当前的时间，可以直接存到TIME字段中。例：insert into tablename (fieldname) values (now())

4.自动增长的序列 自动增长的数据类型  MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。

5.NULL NULL 空字符的处理

MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。

性能上：

6. Oracle支持大并发，大访问量，是OLTP最好的工具。

 用户管理

7.一个数据库下多个用户（Scott/tiger  hr/hr  选择narmal进行登录） 而mysql下是一个用户多个数据库

scott 是个演示用户，是让你学习ORACLE用的
SYSDBA 不是用户，可以认为是个权限，超级权限
详细点说吧
超级用户分两种 SYSDBA和SYSOPT
SYSOPT 后面3个字母是operator的意思，也就是操作数据库的人，而SYSDBA 则是管理数据库的人
SYSDBA比SYSOPT的权限还要大，而SYS用户就完全是个SYSDBA，但SYSTEM用户默认是SYSOPT，不过它也能以SYSDBA的权限登陆

sql语句：

1. 分页查询：首先是大体一致的，只是分页查询时oracle用的伪列（rownum），mysql用的是limit，
2. MySQL的单引号和双引号都可以用，而oracle只能用单引号。
3. MySQL引号里的字母可以不用区分大小写，而oracle引号里的字母必须区分大小写。
4. 大部分都相同，对MySQL比较熟悉 如果在使用oracle  中如果报错在百度

5.在MySQL中

concat函数（concat–连接字符串 concat函数可以连接一个或者多个字符串，若其中一个为null，则返回null）

在Oracle中：

concat 只能连接两个字符串，|| 可以连接多个

 

SQL中的null（null的总结（5点））

SQL> 1、包含null的表达式都为null

SQL> 2、null永远！=null      用is null表示

3.排序的时候有null时，   null表示最大值

4．SQL> --null值 3、如果集合中含有null，不能使用not in；但可以使用in

SQL> --查询不是老板的员工信息

     select *

  2  from emp

  3* where empno  not in (select mgr from emp)    不可以执行    not in(这里不能有null)

SQL> select *

  2  from emp

  3  where empno not in (select mgr from emp where mgr is not null);   运行成功

其他：

5.--nvl(a,b) 表示a 如果为null，就用b来代替  如果不为null就取本身的值       nvl2

 select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)

 

 

常见的sql关键字

级联操作（在操作主表的数据的时候希望从表中的数据也同步的更新过来）

级联更新、删除

在设置外键的时候加上这个：
on update cascade on delete cascade

给表添加外键的作用是什么？

答： 更多时候表不是独立存在的 表与表之间存在着某种关系  而这种关系的维护就需要用到外键  外键约束的主要作用 ：维护两个表之间数据的一致性。（一对多或是多对多）

内联的作用：

多表查询又叫做多个表之间的关联查询）

内联的前提是表之间有相同的字段  

外键和内联之间的关系

外键主表和从表之间的查询多用到内联

但是没有设置主从表关系的表之间只要有相同的字段也可以进行查询（一对一）

：主要是为了查询多个表中的字段吗 组成一张新的表返回出去

内连接和外连接是查询时表的连接方式，主外键是表之间关系

以查询部门和员工表为例（部门在左边  员工在右边）  他们共同的字段是部门号

部门     员工

1       1

2         2

3         3

4         5

 

内连接:  只显示 123  一对都多的情况下一的一方数据重复

其实select * from A,B where A.Aid=B.Bnameid与Select * from A INNER JOIN B ON A.Aid=B.Bnameid的运行结果是一样的。

左外连接：1234

select * from A Left JOIN B ON A.Aid=B.Bnameid

右外连接：显示1235

select * from A Right JOIN B ON A.Aid=B.Bnameid

注意左还是右是根据join的左边还是右边 而不是等号

all和any的作用：

 select * from student where 班级='01' and age > all (select age from student where 班级='02');

等同于

select * from student where 班级='01' and age > (select max(age) from student where 班级='02');

select * from student where 班级='01' and age > any (select age from student where 班级='02');

：就是说，查询出01班中，年龄大于 02班任意一个 的 同学

注意在sql语句中：name password 都是关键字所以不要取字段名或是表名为这样的  不让容易出错  在其他地方也是一样取名的时候不要取到关键字

distinct 

去掉重复记录（可以作用于多列也可以是单列）

union  

 把两条SQL语句连起来  union（交的地方只加一次）  与union all（交的地方各加一次）

 

 

oracle高级性能优化

什么是SQL的执行计划？？（以查询建立了索引的表为例 是使用查询表  还是索引表）

就是数据库服务器在执行sql语句之前会制定几套执行计划！看那个机会消耗的系统资源少，就是用那套计划！

有关sql的优化

1. SQL语句尽量用大写的；
2. SELECT子句中避免使用 * ： 
3. 用索引提高效率： 
4. 用Where子句替换HAVING子句：
5. 用TRUNCATE替代DELETE： 
6. 用EXISTS替代IN、用NOT EXISTS替代 NOT IN：
7. WHERE子句: 
8. 尽量使用多表查询： 因为子查询最后还是会转成多表查询

如果导入大量的数据

（思路：通过程序  输出输入流来生成sql脚本 在到数据库中导入脚本）

SQL> 1、数据泵（PLSQL程序）

SQL>  dbms_datapump(程序包)

SQL> 2、SQL*Loader  加载工具

 

oracle中的伪列  伪表 及其作用

伪列：可以完成数据库管理，和某些功能的隐藏列 （）

LEVEL 查询数据所对应的层级

  ROWID 记录的唯一标识    索引

  ROWNUM 限制查询结果集的数量   分页查询  top n问题

应用：

ROWNUM是动态的，也就是必须先有查询到的结果集，然后再给这个结果集加上一个列，比如结果集中的第一条数据的ROWNUM的值为1，依次类推SELECT   

SELECT ROWNUM,empno,ename,job FROM emp WHERE ROWNUM > 5 AND ROWNUM <= 10;（没结果）

SELECT * FROM (SELECT ROWNUM nums,emp.* FROM emp) WHERE nums > 5 AND nums <= 10;（有结果）

同ROWNUM伪列不同的是，它是物理存在的，ROWID是一种数据类型，它使用基于64为编码的18个字符来唯一标识一条记录物理位置的一个ID，类似于Java中一个对象的哈希码，都是为了唯一标识对应对象的物理位置，需要注意的是ROWID虽然可以在表中进行查询，但是其值并未存储在表中，所以不支持增删改操

 

oralce常用的函数：

参考这篇博客：https://zhuanlan.zhihu.com/p/87959614