Oracle

oracle11g  oracle12c

oracle数据库服务器由二部份组成：实例（看不见），数据库（*.DBF文件中）。

只能通过实例去操作数据库！！！

\oracleDB\oradata\目录下，有多少个文件夹，就有多少个数据库；存储的所有数据库，最终都会存放在对应库的*.DBF文件中，以二进制压缩形式存放

安装oracle时，已经创建好了一个数据库，默认名叫orcl

dual：哑表或者伪表，利用它设置序列、调用函数。

oracle中综合语法：
1）select子句-----必须
2）from子句-------必须，不知写什么表了，就写dual
3）where子句------可选
4）group by子句---可选
5）having子句-----可选
6）order by 子句--可选，如果出现列名，别名，表达式，字段

---

SQL语句

SQL92/【99】：访问任何关系型数据库的标准

SQL语句：

1.在任何的关系型数据库中都可以直接执行；2.关健字不能简写；3.提倡大写；4.能够对表数据进行增删改查操作；5.以分号结束；6.通常称做语句。

oracleSQL语言：只访问Oracle数据库服务器的专用语言

---

连接工具

sqlplus：oracle11g自带的一个客户端黑屏界面工具，可以连接到某个数据库的实例

SQLPLUS命令：是SQLPLUS工具中特有的语句：

1.在该工具中执行的命令叫SQLPLUS命令；2.关健字可以简写；3.提倡大写；4.不能够对表数据进行增删改查操作，只能完成显示格式控制；5.可以不用分号结束；6.通常称做命令。

sqldeveloper：oracle11g自带的一个客户端彩屏界面工具，可以连接到某个数据库的实例

第三方的客户端工具

---

使用

---

超级管理员身份：超级用户名sys，角色dba

解锁普通用户scott / hr：

解锁用户：alter user scott/hr account unlock;
设置密码tiger：alter user scott/hr identified by tiger; 

超级管管理员角色进入：sqlplus / as sysdba

普通用户进入：sqlplus scott/tiger

修改新密码password

---

use 数据库名

查询当前用户是谁：show user;

查询用户下的所有对象：select * from tab;

设置显示的列宽

字符型varchar2、日期型date：column 字段 format a12;    -- 设置字段显示12个宽度位

数值型number：column 字段 format 9999;   -- 设置字段显示4个数字位宽

设置显示的高度：set pagesize 80;   -- 页显示80条记录

执行最近一次的SQL语句：/

查询emp表的结构：desc emp;

指定别名： select 字段1 as "别名" from 表;   --AS大小写都可且可以省略AS，别名用双引号，不加双引号的别名不能有空格；加了双引号的别名可以有空格

在sqlplus客户端工具中，是不显示null这个值，使用NVL()函数：NVL(a,b)  --如果a是NULL，用b替代

使用字符串连接符号：select 'hello' || ' world' "结果" from dual;  --输出hello world，单引号是字符串型或日期型

系统当前时间：select sysdate from dual;

保存SQL语句到硬盘：

spoole:/oracle-1.sql;

sql语句；

spool off;

将硬盘文件e:/oracle-1.sql，读到orcl实例中并执行：

@ e:/oracle-1.sql;

---

函数：

单行函数：一个参数输入，一个结果输出

lower()，upper()，concat()，substr()，length/lengthb()，instr/lpad/rpad()，trim/replace()，round/trunc/mod()，months_between()，add_months()，next_day()

多行/分组 函数：多个参数输入，一个结果输出

count/sum/avg/max/min

NVL2(a,b,c)通用函数，如果a不为NULL，取b值，否则取c值

NULLIF(a,b)通用函数，在类型一致的情况下，如果a与b相同，返回NULL，否则返回a

decode()函数：decode(字段,条件1,表达式1,条件2,表达式2,...表达式n)

等价于sql99的case ···

case 字段 
     when 条件1 then 表达式1
     when 条件2 then 表达式2
     else 表达式n
end 

---

多表查询：

理论基础：笛卡尔集表=列数之和，行数之积

1.内连接【只能查询出符合条件的记录】：等值连接（只能使用=号）；非等值连接（不能使用=号，其它符号可以，例如：>=，<=，<>，betwen and等）

2.外连接查询【既能查询出符合条件的记录，也能根据一方强行将另一个方查询出来】

(+) 哪张表少加在那张

左外连接【左表数据全部保留】：
select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数"
from dept,emp
where dept.deptno = emp.deptno(+) 
group by dept.deptno,dept.dname;

3.自连接【将一张表，通过别名看作两张表内/外连接】

---

集合查询：

并集运算

union：二个集合中，如果都有相同的，取其一

union all：二个集合中，如果都有相同的，都取

交集运算[intersect]

差集运算[minus]

使用set time/timing on，打开时间的开关set time on;set time off;

使用set tim/timing off，关闭时间的开关set timing on;set timint off;

集合查询的细节：

1）集合操作时，必须确保集合列数是相等
2）集合操作时，必须确保集合列类型对应相同
3）A union B union C = C union B union A
4）当多个集合操作时，结果的列名由第一个集合列名决定

---

oracle分页 ：rownum

1）rownum是oracle专用的关健字
2）rownum与表在一起，表亡它亡,表在它在 
3）rownum在默认情况下，从表中是查不出来的
4）只有在select子句中，明确写出rownum才能显示出来
5）rownum是number类型，且唯一连续
6）rownum最小值是1，最大值与你的记录条数相同
7）rownum也能参与关系运算
   * rownum = 1    有值
   * rownum < 5    有值
   * rownum <=5    有值
   * rownum > 2    无值    
   * rownum >=2    无值
   * rownum <>2    有值 与  rownum < 2 相同
   * rownum = 2    无值
8）基于rownum的特性，我们通常rownum只用于<或<=关系运算 。用别名代替做>运算

---

oracle中三大类型

字符串varchar2，数字number，日期date

隐式数据类型转换

（1）varchar2变长/char定长  -->   number。     例如：'123'->123
（2）varchar2/char  -->   date。     例如：'25-4月-15'->'25-4月-15'
（3）number   --->   varchar2/char。     例如：123->'123'
（4）date   ---->   varchar2/char。     例如：'25-4月-15'->'25-4月-15'

隐式转换注意：
1）=号二边的类型是否相同
2）如果=号二边的类型不同，尝试的去做转换
3）在转换时，要确保合法合理，否则转换会失败，例如：12月不会有32天，一年中不会有13月

显式转换：

将数字/日期转成字符串：to_char(数值/日期,'格式yyyy mm dd $ L "常量"' )函数

将字符串转成日期：to_date('字符串','格式')函数

将字符串转成数字：to_number('字符串')函数

---

创建表和约束

使用oracleSQL，创建用户表users(id整型/name字符串/birthday日期/sal整型，默认今天)
create table users(
  id number(5) primary key,
  name varchar2(8) not null unique,
  sal number(6,2) not null,
  birthday date default sysdate
);

进入回收站
drop table users;

查询回收站中的对象
show recyclebin;

闪回，即将回收站还原
flashback table 表名 to before drop;
flashback table 表名 to before drop rename to  新表名;

彻底删除users表
drop table users purge;

清空回收站
purge recyclebin;

（1）number(5)：5表示最多存99999
（2）number(6,2)：6表示小数+整数不多于6位。2表示最多显示2位小数，采用四舍五入，不足位数补0，同时要设置col ... for ...
（3）varchar2(8)：8表示字节
（4）date：默认格式为：'27-月-15'
（5）CLOB【Character Large OBject】：大文本对象，即超过65565字节的数据对象，最多存储4G
（6）BLOB【Binary Large OBject】：大二进制对象，即图片，音频，视频，最多存储4G

---

修改表

为emp表增加image列，alter table 表名 add 列名 类型(宽度) 
alter table emp
add image blob;

修改ename列的长度为20个字节，alter table 表名 modify 列名 类型(宽度) 
alter table emp
modify ename varchar2(20);

删除image列，alter table 表名 drop column 列名
alter table emp
drop column image;

重名列名ename为username，alter table 表名 rename column 原列名 to 新列名
alter table emp
rename column ename to username;

将emp表重命名emps，rename 原表名 to 新表名
rename emp to emps;

注意：修改表时，不会影响表中原有的数据

修改表不可回滚