Oracle最全基础入门笔记(简单清晰，没有之一)

前言：我这里还整理了一些oracle的sql语句练习的题目，大家看完笔记后可以适当做一下，自己掌握了多少ξ( ✿＞◡❛)
下面是链接
oracle SQL语句基础练习题

一、常见的数据库：

mysql 　　sqlServer　　oracle　　db2　　sybase
今天我要分享的是 oracle数据库

二、Oracle的一个认知：

oracle是一家公司的名字(甲骨文公司),世界上屈指可数的，是国际上非常大的巨型IT公司，是全球最大的企业级软件公司，总部位于美国加利福尼亚州的红木滩。1989年正式进入中国市场。2013年，甲骨文已超越IBM，成为继Microsoft后成为全球第二大软件公司，最有名的产品是DB，database，RDBMS，关系型数据库系统。世界第一大数据库提供商，上海研发中心，五角场，离复旦大学近。主营oracle数据库软件，全世界第二大ERP提供商，大型企业级内部管理系统，包括人力资源管理等等。Oracle是Oracle公司第一个也是最成功的一个产品，发展经历过一个漫长的过程，到1997年6月，Oracle第八版发布。Oracle支持面向对象的开发以及新的多媒体应用，这个版本也成为支持Internet、网络计算等奠基定了基础。同时这一版本开始具有同时处理大量用户和海量数据的特性。1998年9月，oracle公司正式发布oracle 8i。"i"代表Internet，这一版本中添加了大量为支持Internet而设计的特性，这一版本为数据库用户提供了全方位的Java支持，Oracle 8i成为第一个完全整个了本地Java运行时环境的数据库。然后有9i，10g，"g"代表”grid“，网格。这一版本最大的特性就是加入了网格计算功能。然后是11g，现在发展到12c，c代表cloud，也就是现在炒的非常火的云概念。

三、Oracle简介

1. Oracle 服务
不推荐开机启动。设置为手动启动。
OracleService[Orcl] oracle服务实例
Oracleora…Listener oracle监听服务
2.表空间
数据库最大逻辑单位。
创建表空间：
create tablespace 表空间名
datafile ‘数据库文件路径’ [size 文件大小 K|M] [autoextend on|off],
‘数据库文件路径’ [size 文件大小 K|M] [autoextend on|off],
…
示例：
create tablespace tp_xz
datafile ‘D:\xz1.dbf’ size 10M,
‘D:\xz2.dbf’ size 30M autoextend on;

查看表空间数据文件：
select *
from dba_data_files
where tablespace_name=‘表空间名’;

删除表空间：
drop tablespace 表空间名; --只删除表空间
drop tablespace 表空间名
including contents and datafiles cascade constraints;
–删除表空间,同时包含内容，数据文件，对象，约束相关级联删除。
示例：

 drop tablespace tp_xz
      including contents and datafiles cascade constraints;

3.常用命令
show 用户名 – 显示当前用户
conn[ect] 用户名/密码 – 切换用户
password --更改当前用户密码
alter user 用户名 identified by 密码 --更改指定用户的密码

4.默认用户
sys : 超级管理员，拥有所有权限。默认密码：change_on_install
必须加上： as sysdba
如： conn sys/密码 as sysdba
System:系统管理员,除了create database 权限。默认密码：manager
scott :示例用户。 默认密码：tiger

5.用户的相关内容
创建用户语法：
create user 用户名
identified by 密码
[default tablespace 默认表空间名]
[temporary tablespace 临时表空间名]
注意：新创建用户是没有任何权限。

示例：
 create user xz
 identified by 123
 default  tablespace tp_xz
 temporary tablespace temp;
 
 对象: 表，视图，索引，同义词 ...
 角色：一组权限的集合。简化权限的操作。
    connect  : 临时用户
    resource : 正式用户
    dba      : 管理员
    
 权限分类： 系统权限 和  对象权限
 系统权限：对数据库操作的权限。 （连接，创建表，视图，索引...）
       赋予系统权限语法：
       grant  系统权限  to  用户名  [with admin option] ;
       grant  系统权限  to  public; --赋予所有用户权限

       示例：
           grant create session to xxz; --创建会话权限
           grant create table to xxz;
           grant create view to xxz;
           grant connect,resource to xxz;

     ss  撤销系统权限语法：
           revoke 系统权限   from  用户名;
           示例：
           revoke create table  from  xxz;

   对象权限：对数据库对象操作的权限。（增加，删除，修改，查询）
      赋予权限语法：
           grant  对象权限 on 对象 to 用户名;

         示例：
           grant select on scott.emp to xxz;
           grant insert on scott.emp to xxz;
           grant update on scott.emp to xxz;
           grant update(ename) on scott.emp to xxz;
           grant delete on scott.emp to xxz;
           grant all on scott.emp to xxz;

     撤销语法：
     revoke  对象权限 on 对象 from 用户名;
     删除用户
     drop user  用户名;   --删除用户
     drop user  用户名  cascade; --删除用户，同时删除用户下所有的对象
     锁定|解锁 用户
     alter user  用户名  account lock|unlock;

四、Sql命令

1：sql命令类别
数据定义语言（DDL）：create ,alter,drop,truncate
数据操作语言（DML）：select，insert,update,delete
事务控制语言 (TCL) ：commit,rollback,savepoint
数据控制语言 (DCL) ：grant,revoke

2: 数据类型
数值类型：number 位数 38位
number : 代表38位的整数
number(3): 代表3位的整数 999
number(5,2):代表总位数5位，其中小数位2位。 999.99

  字符类型：
    char      固定长度字符    1-2000个字节
    varchar2  可变长度字符    1-4000个字节
    long      2GB （不推荐）--->CLOB
    
  日期类型：
     date:   日期+时间   ，精确到秒
     timestamp: 日期+时间+时区 ,秒精确到小数点后6位

  LOB：大对象数据，大小4GB
    CLOB：字符数据    （小说，新闻稿...）
    BLOB：二进制数据  （图片，音频，视频...）
    Bfile: 二进制文件  （外部）

  伪列：不存在于表中，但是可以进行查询。
     rowId: 存储数据行的地址，通过rowid快速找到对应行。
            不能增加，删除，修改。
     rownum: 返回结果集的行号。始终从1开始。
             作为条件不能与= >  >=

3:创建表
create table 表名(
列名 数据类型 约束表达式,
列名 数据类型 约束表达式,
…
);

示例：学生表
   学号   number   
   姓名   varchar2
   性别   char    只能为男或女,默认男
   出生日期  date

4: 修改表结构
添加列：
alter table 表名 add(列名 数据类型 约束类型);
示例：
alter table student add(codeId varchar2(18));
修改列：
alter table 表名 modify(列名 数据类型 约束类型);
删除列：
alter table 表名 drop column 列名;

5: 追加约束
约束名命名规范：
主键： PK_表名
唯一键：UK_表名_列名
外键： FK_表名_列名
check: CK_表名_列名
alter table 表名 add constraint 约束名 约束类型(列名);
示例：
追加主键
alter table student
add constraint PK_student primary key(stuNo);
追加外键：
alter table student
add constraint FK_表名_列名 foreign key(列名)
references 表(主键列) ;

删除约束：
   alter table student drop constraint 约束名;

6: 删除表
drop table 表名 [purge]; --删除表，放入回收站
select * from recyclebin; --查看回收站
flashback table 表名 to before drop [rename to 新表名];将表从回收站还原
purge table 表名; --清空回收站指定的表
purge recyclebin; – 清空回收站

7:利用现有表创建表
create table 表名
as
select 查询语句;

8:插入表的数据来自于另一张表 （批量）
insert into 表名
select 查询语句;

9:事务
将多条sql作为一个整体去执行，要么一起成功，要么一起失败。
特性: 原子性，一致性，持久性，隔离性
默认DML语言，不自动提交或回滚。
以下情况事务会完成：
1：commit ,rollback
2：遇到DDL，DCL语言
3：关闭会话，出现异常终止
事务关键字：
commit 提交
rollback 回滚 rollback to 回滚点
savepoint 设置保存点，用于回滚

10：集合操作符
将两个查询结果组合成一个结果返回
union : 返回两张表所有的行，去掉重复的行。
union all:返回两张表所有的行，不去掉重复的行。
intersect :返回两张表相同的行
minus :返回第一张表的行，去掉第二张表重复行。

11: 连接操作符 ||
示例：
select ‘姓名:’||ename||’,职位：’||job
from myemp;

五、sql函数

1：sql 函数
单行函数：每一行返回一个结果
1:字符串函数
initcap():首字母转大写
lower():转小写
upper():转大写
length():获取字符的长度
instr():查找字符出现的位置。从1开始，没有找到返回0
substr(str,start,len):截取子字符串。start:起始位置为负数，代表从后面开始截取
replace():替换字符
translate(str,from,to):将字符串from中的字符替换成to中对应位置的字符。

2：数值函数
ceil():向上取整
floor():向下取整
round():四舍五入
trunc():截断
mod():求余

3：日期函数
sysdate :返回系统当前日期
add_months():对月份进行加减操作
months_between():两个日期之间月份的差值
last_day():本月的最后一天
next_day()：返回下周对应的日期
extract(year|month|day from d1)：提取日期中指定的数据

4：转换函数
to_char():
to_date():
to_number():

5:其他函数
nvl(expr1,expr2):如果expr1为null,返回expr2。否则expr1。
注意：expr1，expr2必须类型一致
NVL2(expr1,expr2, expr3):如果expr1不为null,返回expr2。
如果expr1为null，返回expr3。
nullif(expr1, expr2):expr1和expr2相等返回NULL，不相等返回expr1
decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值):等价于switch…case

多行函数：多行操作返回一个函数
6：聚组函数
max() min() avg() count() sum()

7:分析函数
按照指定排序规则，添加行号
row_number() over(排序规则): 序号连续，不重复
rank() over(排序规则): 可以重复，可能会间断
dense_rank() over(排序规则):连续，可以重复

六、数据库对象

注意：oracle沒有标识列，自动增长。
1：序列
作用：产生唯一，连续的序号。
创建序列语法：
create sequence 序列名
start with number --起始值，默认从1开始
increment by number --步长 ，默认为 1
maxvalue number |nomaxvalue --最大值
minvalue number |nominvalue --最小值
cycle|nocycle --是否循环
cache number|nocache --是否缓存，默认20
修改序列：
alter sequence 序列名
increment by number --步长 ，默认为 1
maxvalue number |nomaxvalue --最大值
minvalue number |nominvalue --最小值
cycle|nocycle --是否循环
cache number|nocache --是否缓存，默认20
访问序列：
序列名.nextval
序列名.currval
删除序列：
drop sequence 序列名;

2: 同义词
给当前对象取别名
分类：
私有同义词：只能在当前模式下使用，不可以与当前模式下的对象同名。
语法：
create synonym 同义词 for 用户名.对象名
示例：
create synonym sy_emp for scott.emp;

 公有同义词：所有用户都可以访问
    create public synonym 同义词 for 用户名.对象名
  示例：
    create public synonym  sy_emp for scott.emp;

 删除同义词：
   drop [public] synonym 同义词;

注意：需要当前用户赋予创建|删除同义词权限。

3：视图
是一张虚拟表，不存储数据，存储select语句的结构。
作用：
1：保证基表数据的安全
2：存储复杂的业务功能sql语句

语法：
create [or replace] view 视图名
[(列别名)]
as
select语句
[with check option 列名]
[with read only];
查看：
select * from 视图名;

删除：
drop view 视图名;

4: 索引
优点：提高查询效率
缺点：占用空间，影响增加，删除，修改效率
分类：
B树索引：（标准索引）高基数的列（很多不同值的列，分布均匀）
create index 索引名 on 表(列);
唯一索引：列值唯一
create unique index 索引名 on 表(列);
组合索引：多列组合的索引 ,经常使用的放前面。
create index 索引名 on 表(列,列);
反向键索引：( B树索引的分支)，适用于自动增长的列
create index 索引名 on 表(列) reverse;
位图索引：低基数的列（列值有很重复的值）
create bitmap index 索引名 on 表(列);
函数索引：
create index 索引名 on 表(函数(列));

 删除索引:
    create index 索引名;

 适用于添加索引情况：数据量大，经常使用的列
 不适用于添加索引情况：数据量少,不使用的列

七、PL/SQL

1：PL/SQL 过程语言和结构化查询语言组合的编程语言。是SQL扩展.
PL/SQL块结构语言,将一组sql语言放入一个结构块中。
PL/SQL中可以放DML语言，事务，函数，控制语言…
不能直接放DDL语言，但是动态sql。

2：PL/SQL 语法 组成：声明部分，执行部分，异常处理部分
declare
–声明部分，变量，常量，游标，异常类型
begin
–执行部分
exception
–异常处理部分
end;

3：声明变量和常量
变量名 数据类型[(长度)] [:=值]; – name varchar2(10);
变量名 contant 数据类型[(长度)] :=值; --contant:常量

4：变量赋值方式
1：方式一
变量名:=值;
2: 方式二
select 列名列表 into 变量列表 from …
注意：
1：列名列表与变量列表 顺序，个数，数据类型一致
2：select into 只能返回一行数据，数据放到变量中。
返回多行或者O行都会报错。

5：数据类型
标量类型：数值，字符，日期 …
属性类型：
%type: 返回与表的列类型或变量类型一致。–emp.ename%type
%rowtype:行的记录类型。 --emp%rowtype
boolean:布尔类型 true,false,null

6: 异常处理
异常:在运行过程中可能出现的错误。导致运行的终止。
异常分类：预定义异常 和 用户定义异常
预定义异常：Oracle定义，隐式引发。
用户定义异常：用户自定义，显式引发 raise
1:预定义异常
no_data_found: 没有找到数据
to_many_rows : select…into 返回多行数据时
others : 没有明确捕获异常，补充异常完整

  2:用户定义异常 raise
    declare
       异常类型  exception;  --声明异常 
    begin
       --执行部分
       --显式引发用户定义异常
       raise 异常类型;
       ...
       exception
         when 异常类型  then
            --异常处理 错误号:-20000~-20999  错误信息：2048个字节
            raise_application_error(错误号,错误信息);
    end;

7：PL/SQL的控制结构
1：条件控制
if 条件表达式 then
–sql语句块
end if;
--------------------------------------------
if 条件表达式 then
–sql语句块
else
–sql语句块
end if;
-----------------------------------
if 条件表达式 then
–sql语句块
elsif 条件表达式 then
–sql语句块
elsif 条件表达式 then
–sql语句块
…
else
–sql语句块
end if;
-------------------------------
case
when 条件表达式 then --sql语句块
when 条件表达式 then --sql语句块
…
else
–sql语句块
end case;
-----------------------------
case 条件参数名
when 值1 then --sql语句块
when 值2 then --sql语句块
…
else
–sql语句块
end case;

2：循环控制
1.LOOP
LOOP
语句;
EXIT WHEN <条件>
END LOOP;

  2.WHILE LOOP
WHILE <条件>
LOOP
	语句;
END LOOP;

  3.FOR   reverse:反向降序 
FOR <循环变量> IN [reverse] 下限..上限
LOOP
	语句;
END LOOP;

8：动态sql
动态sql 在运行时才确定，运行之前不编译，不执行。执行DDL语言
语法
declare
–声明变量
begin
执行动态sql
execute immediate ‘动态sql’
[into 变量列表]
[using (参数列表)];
end;

八、子程序 存储过程和函数

1：子程序
已命名的PL/SQL,编译并存储到数据库中。
分类：
存储过程：执行操作。
函数：执行操作并返回值。

2：存储过程
创建：
create or replace procedure 存储过程名
[(参数列表)]
as|is
局部变量声明
begin
执行部分
exception
异常处理部分
end;

参数类型：（3种）
in : 输入参数(默认) (将值传给存储过程)
out: 输出参数 (存储过程返回的值)
in out：输入/输出参数

执行过程：
1：程序PL/SQL块中
begin
存储过程名(参数列表);
end;
2: 命令行 sql> set serveroutput on;
exec[ute] 存储过程名(参数列表);

赋予执行存储过程的权限：
grant execute on 存储过程 to 用户名;

删除存储过程：
drop procedure 存储过程名;

3：函数
创建函数：
create or replace function 函数名
[(参数列表)]
return 数据类型
as|is
局部变量声明
begin
执行部分
return 值;
exception
异常处理部分
end;

调用函数
方式一
select addFun(10,20) from dual;

方式二
declare
v_sum number;
begin
v_sum:=addFun(10,20);
dbms_output.put_line(‘和为：’||v_sum);
end;

4: 调试
1：赋予 dubug conect session 的权限
grant debug connect session to scott;

2：存储过程–>右键–>添加测试信息

3: 创建一个测试窗体，测试代码复制

4：启动测试（F9）,进行单步调试

九、游标

1: 游标
将表中多行数据存储到游标中(内存).
游标中有一个指针，默认指针位于第一行数据的上方。
通过fetch提取游标中的一行数据，进行处理。(提取一行，处理一行)。

2：游标分类
隐式游标
显式游标
ref游标

3：隐式游标
在pl/sql执行DML语句时，会自动创建的游标。游标名sql
自动创建，自动打开，自动提取，自动关闭。
属性：
%found: 如果有受影响的行数，返回true。
%notfound:如果没有受影响的行数，返回true。
%rowcount: 返回受影响的行数。
%isopen :判断游标是否打开。隐式游标中始终为false。
访问属性： 游标名%属性名

4：显式游标 （处理查询的多行数据的结果）
需要显式声明，手动打开，手动提取数据，手动关闭
步骤：
1：声明显式游标
cursor 游标名 is select 语句;
2：打开游标
open 游标名;
3: 提取数据 （一次提取一行）
fetch 游标名 into 变量列表|行记录类型
4: 关闭游标
close 游标名;

带参数的显示游标：
1：声明显式游标
cursor 游标名(参数列表) is select 语句;
2：打开游标
open 游标名(实参列表);

游标修改数据：
1：声明显式游标
cursor 游标名(参数列表) is select 语句 for update;
2: where 条件
where current of 游标名;

循环简化游标：（不需要打开，提取，关闭）
for 游标一条记录的类型名 in 游标名
loop
end loop;

5: ref 游标（动态游标）
执行时确定的sql语句。
步骤：
1：声明游标的数据类型
type 数据类型名 is ref cursor [return type];
如果没有加return，说明是一个弱类型的引用游标，可以打开任意表。
如果加return，说明是一个强类型的引用游标，只能打开指定类型的表.
如： return emp%rowtype;–只能打开emp的表。
2：声明数据类型变量
游标变量名 数据类型名;
3: 打开游标时确定sql语句
open 游标变量名 for select 语句;

十、触发器

1：触发器
当前触发该事件时，自动调用的特殊的存储过程。
作用：复杂业务功能，日志记录操作。
触发事件：
DDL事件：create,drop,alter
DML事件：delete,update,insert
数据库事件： startup,startdown

 执行DML触发操作
   1：确定触发事件： 
      insert|update|delete  ---触发事件
   2：确定触发的时机
      before|after|instead of  
   3：确定作用的表和视图   
   4：触发器类型
      行级触发： for each row
      语句触发：
   5: 为了保存之前和之后的数据，提供了old和new记录类型的变量
      insert:  new
      update:  old   new 
      delete:  old
      如果old和new放在when后面使用，直接使用
      否则：需要用 :old 和 :new

   6：DML触发器中不能写DDL语句

   7：DML触发器中不能用事务语句commit|rollback

   DML触发器语法：     
   create or replace trigger 触发器名
      before|after|instead of 
      insert|update [of 列名]|delete 
      on  表名|视图名
     [for each row]  --行级触发
     [when [条件] ]
     begin
         PL/SQL
     end;

----删除触发器
drop trigger 触发器名;

----禁用触发器
alter trigger 触发器名 disable;

----启用触发器
alter trigger 触发器名 enable;

十一、程序包 和 备份，恢复

1: 程序包
将一组相关类型，存储过程，函数，游标，变量，常量，异常封装。
便于管理。

2：程序包组成：规范和主体
程序包规范：声明程序包包含公有的对象，变量，没有实现。
程序包主体：对包中声明的对象提供具体的实现。

3: 程序包语句
–程序包规范
create or replace package 程序包名
as
–声明变量|常量
变量名 [constant] 数据类型 :=值;
–声明过程
procedure 过程名 [(参数列表)];
–声明函数
function 函数名 [(参数列表)] return 返回类型;
–声明游标 ,必须加return 指定游标返回的记录类型
cursor 游标名[(参数列表)] return returnType;
end;

--程序包主体
create or replace package  body  程序包名
as
begin
   --过程主体
   procedure 过程名 [(参数列表)]
   as|is
   begin
      --过程执行部分
   end;
   --函数主体
   function 函数名 [(参数列表)] return  返回类型
   as|is
   begin
      --过程执行部分
      return 值;
   end;

   --游标主体
   cursor  游标名[(参数列表)] return returnType
   is  select语句;
end 程序包名;

4: 备份和恢复
导出： exp
exp help=yes; 查看参数
1：交互式
exp 提示导出
2：命令行参数导出
exp userid=scott/tiger tables=(emp,dept) rows=yes file=D:\emp.dmp
exp userid=scott/tiger tablespaces=(users) rows=yes file=D:\emp.dmp
exp userid=scott/tiger owner=(scott) rows=yes file=D:\emp.dmp
exp userid=scott/tiger full=yes rows=yes file=D:\emp.dmp
到这里介绍了~

有什么问题欢迎各位提出来，本人也是刚学…(๑╯ﻌ╰๑)=3