oracle数据库笔记（包括增删查改，索引，plsql，游标，存储函数和存储过程，包）

oracle数据库笔记

注释

1、单行注释：用--

2、多行注释：用/**/

用户的身份

1、管理员用户

管理员用户可以访问数据库中的任何数据，不需要权限，也可以创建用户、修改用户密码、赋予用户权限等行为。

2、普通用户

普通用户被管理员创建，所有访问权限来自于管理员的赋予。创建的用户都是普通用户 初始状态不具备任何权限。

如果想要用户可以正常进行登录，需要我们提供对应的权限。

**- connect 权限 连接权限 (允许用户进行登录)

• resource 权限 资源权限(允许用户创建属于自己的表)
  
• dba 权限 管理员权限 (允许用户以管理员身份进行登录) 不要赋予该权限
  
• 权限撤回 revoke
  
• 用户状态 lock unlock 用户一般情况下，在创建的时候，默认都是解锁的，可以在创建用户的时候，定义用户的状态为锁定状态，锁定状态的用户，就算具有权限，也无法登录数据库
  
• 可以使用alter关键字对指定用户进行解锁
  **
  
  

用户、表的创建

用户创建、添加权限、删除修改权限的例子

-- 查询scott用户下的emp表的所有记录
select * from scott.emp;
-- 创建普通用户  
-- 创建一个叫做mqq的用户 定义密码为a12345   
-- 创建的用户都是普通用户 初始状态不具备任何权限 
create user mqq identified by a12345;  
--连接权限
grant connect to mqq;
--资源权限
grant resource to mqq;
--  也可以合并写为一条 
grant connect,resource to mqq; 
-- 撤回权限
revoke connect from mqq;
revoke resource from mqq;
-- 也可以合并写为 
revoke connect,resource from mqq;
-- 锁定用户
create user mqq identified by a12345 account lock;
-- 使用alter关键字对指定用户进行解锁
alter user mqq account unlock;

创建表

create table 表名称(
表字段 字段类型，
表字段 字段类型
);

基本字段类型有：

number（n） – 最多可以容纳8位长度的数据

number（n，m） – 限制字段最多能够容纳n位数字 其中小数部分m位 整数部分n-m位

char(n)： 定长 最大不能超过2000字节

• 存储的是字符串类型 代表的是该字段所能容纳的数据的
  
• 最大字节大小 字符集 英文字符 一个字符占据一个字节
  
• 中文字符 根据所使用的编码不同 一个中文字符可能占据2 3 甚至是4个字节
  
• 当前oracle 所使用的字符集 一个汉字占据三个字节
  

**varchar2(n) ** 不定长 最大不能超过4000字节

char和varchar2之间的区别：

• char 定长类型，如果规定20个字节，但只使用了三个字节，则oracle会使用空格自动补足剩下的17个字节
  
• varchar2 类型后面的数字只是定义了字段能够存储的最大值。但是，实际在存储时候所占据的空间大小以存储的数据为准
  
• varchar2 节约了空间 但是牺牲了效率 时间换空间
  
• char类型 就牺牲了空间 但是提高了效率 空间换时间
  
• 阿里的要求 字段值几乎相等的情况下 必须使用char类型
  

date 时间类型数据，值如果为sysdate（oracle 的一个自带函数），代表当前的时间。

to_ date 将字符串转换成指定格式的日期 to_ date(‘2018-09-01 06:06:06’,‘YYYY-MM-DD HH24:MI:SS’)
timestamp 精确日期类型 可以精确到秒后6位 甚至包含时区
clob 存储大文本 或者是xml配置文件
blob 存储二进制对象 图片 音频 视频等等
现在的开发中 blob 和 clob使用比较少 因为数据库存储大数据量的数据 性能消耗过大
一般来讲需要存储的资源 会以公网url的方式 存储目标资源的公网路径 进行存储

插播一个硬盘和内存

• 所有的数据都是在硬盘中进行读和写入的
  
• 但是在对数据进行操作 或者是运行程序的时候 需要将数据从硬盘提取到内存中进行读取
  
• 你想要将数据提取到内存中 就必须向内存申请空间
  

约束

• primary key -主键约束，值不可以为空，不可以出现重复值，一张表中只能有一个字段建立主键约束
  
• alter table 表名 add constraint 约束名称 check（检查约束语句）-检查约束
  
• alter table 表名 modify(字段名 not null);-非空约束 限制该字段字段值不可以为空
  
• alter table 表名 modify(字段名 default ‘默认值’);-默认约束 插入数据时，如果没有给添加了默认约束的字段提供值，则约束会给其提供默认值
  
• alter table 表名 add constraint 约束名称 unique(字段名); -唯一约束 限制字段中插入的值不可以重复(主键约束可以理解为是一种特殊的唯一约束) 该字段的值不可重复
• alter table 需要建立约束的表名 add constraint 约束名称 foreign key(需要建立约束的表的字段) references 被关联的表(被关联的表的字段);（alter table 子表表名 add constraint 外键约束的名称 foreign key(字段名) references 父表名称(字段名)） 被关联字段必须是唯一的（1. 子表在插入数据的时候 必须在父表中拥有关联记录 2. 父表的数据 如果在子表中有关联数据存在 父表数据无法删除 3.被建立外键约束的父表 是不可以被直接删除的）

建立约束例子

-- 检查约束 限制性别的字段值只能是男或者女
alter table student add constraint ch_stu_gen check(gender = '男' or gender='女');
-- 检查约束 限制学生表中age的范围在18 ~25 
alter table student add constraint ch_stu_age check(age between 18 and 25);
-- 非空约束 限制姓名字段值不可以为空
alter table student modify(sname not null);
-- 默认约束 
alter table student modify(addr default '南京');
-- 唯一约束 地址不可重复
alter table student add constraint un_stu_addr unique(addr);

插入/修改/删除数据

插入：

• 插入全部字段值
  insert into 表名 values(字段1，字段2，…);括号中需要有所有字段的值
• 插入部分指定字段值
  insert into 表名(指定字段1，指定字段2) values(指定字段值1，指定字段值2);

修改：

• 修改一个字段
  update 表名 set 要修改字段名 = ‘要修改字段的值’ where 条件字段=‘条件字段值’;
• 修改几个字段
  update 表名 set 要修改字段名1 = ‘要修改字段名1’ ,要修改字段名2 = 要修改字段值2 where 条件字段= ‘条件字段值’;

删除：

• 删除数据
  delete from student where 删除条件字段=‘删除条件字段值’;

数据库语句的分类

• 数据定义语言 DDL create alter drop turncate(清空表数据)
• 数据操纵语言 DML insert update delete
• 数据查询语言 DQL select order by where group by having join 等和查询相关的数据库关键字
• 事务控制语言 TCL commit rollback savepoint
• 数据控制语言 DCL grant revoke
• 冷知识 oracle单表最多支持一亿条数据 mysql 单表最多只支持500W条

数据添加的本质是硬盘读写，大量的硬盘读写会导致硬盘性能下降，数据库为了避免这样的情况，所以有了日志文件，并且引入了session会话。

oracle是支持多个用户同时登录的数据库，每当发生了用户登录的行为，则我们的数据库都会为该用户分配一个session会话，每一个用户都有一个自己的日志文件，创建表create用户属于DDL语句，而用户所执行的所有DDL语句都会被立刻提交到数据库。但是所有的DML语句并不会被立刻提交到数据库，而是会存放在于会话锁绑定的一个日志文件中，如果你不提交sql语句到数据库，则管理员和其他用户都无法看到你没提交到数据库的执行的sql语句得到的数据。

truncate删除的数据是不可以恢复的 DDL操作 DDL操作是直接提交数据库的。

将结果提交到数据库中，必须执行指令commit;，不然只能在当前用户的会话中查看到。

savepoint 叫做存档点 对当前日志文件中的数据操作进行存档 savepoint A;

rollback 回滚 读档 可以将日志文件中的数据状态 回滚到指定存档点的时候 rollback to A;

• 1.日志文件中的数据 只有在你使用commit或者切换用户登录的情况下 才会提交
• 2.commit 会提交并清空当前用户的日志文件中的所有数据 包括存档点
• 3.在数据没有被提交的情况下 使用rollback to 保存点的名称 可以使日志文件的数据恢复到定义保存点时候的状态 如果直接使用rollback 没有指定保存点 数据会默认回滚到上一次commit的时候

向公司的数据库插入100w条数据 这个是业务
我需要准备100w条insert语句 负责数据库插入，这100w条insert语句 就可以看住是一个整体，这个整体叫做事务。

• 事务的四大特性 原子性 一致性 持久性和隔离性
• 原子性 所有的事务中的dml语句 要么全部提交 要么全部不提交
• 持久性 确保所有数据都能够长期的存储在数据库中执性的sql语句需要及时被commit
• 隔离性 在A事务操作数据的时候 B事务 不可以操作数据 如果B想要操作 只能在A操作之前操作 或者在A操作完成之后进行操作 目的是避免多个事务同时操作一个数据 所产生的错误数据(脏数据)

字段修改（增/删/重名命/修改字段类型/删除用户/删除表）：

• 增加字段 alter table 表名 add 字段 字段类型;
• 删除字段 alter table 表名 drop column 字段名;
• 重命名字段 alter table 表名 rename column 原字段名 to 修改后的字段名;
• 修改字段的数据类型 原本是字符串类型的数据 如果表里有数据 不可以转为number类型 alter table 表名 modify 字段名 字段类型;
• 用户修改密码 alter user 要修改的用户名 identified by 密码;
• 用户删除 drop user 用户名;-- 表的删除 drop table 表名;

查询语句：

单表简单查询

• • 代表oracle的通配符表示选择表中的所有字段，from 之后代表你数据的来源，所有的查询语句 最少要有select 和 from关键字

• 可使用别名 修改字段在显示时候的效果 在字段名之后空格跟别名

• 字符串连接符号 || 可以将字段的值和指定的字符串进行拼接。例子： select ename||‘的薪水为’||sal 薪资情况 from emp;

• concat 也是做字符串拼接的 但是每一次只能拼接两个

  select concat(concat(concat(‘员工的姓名叫’,ename),‘他的收入为’),sal) 薪资情况 from emp;

• 字段的值可以经过数学运算之后再显示

• where关键字 条件筛选

• 运算符号：and or > < >= <= = 不等于 != <> between and in not

• 空值处理 如果对应的某一个字段没有值 可以用null代表空值，值为0 不等于空值。

• nvl 对字段的空值进行处理 可以指定值为null的字段值 如何显示

    查询所有人的奖金,奖金为空的显示为0 不为空的正常显示
    select ename,nvl(comm,0) from emp;
  

• nvl2 不管你如何查询 改变的只是查询结果的显示 不会改变表中原有的数据

• floor 对向下取证

    comm表示你还需要处理的字段 comm+1000 代表如果字段的值不为空 则+1000显示 100 代表如果字段的值为空 则显示为100 
    查询所有人的奖金 所有奖金为空的人 设置奖金显示为100 所有奖金不为空的人 奖金在原有基础上+1000
    select ename,nvl2(comm,comm+1000,100) 修改显示之后的奖金 from emp;
    查询所有部门编号为20并且sal>1500的所有员工的姓名,部门编号和年收入
    年收入=（sal+comm）*12
    select ename,deptno,(sal+nvl(comm,0))*12 from emp where deptno=20 and sal >1500;
  

• 聚合函数 对多行数据进行统计的函数 如果没有指定数据统计的范围 默认统计所有数据

  • min max avg count sum

  • 最小值 最大值 平均 统计出现次数 求和

      查询emp表中所有员工的最低薪水 最高薪水 平均薪水 薪水总合
      select min(sal) 最低薪水,max(sal) 最高薪水,floor(avg(sal)) 平均薪水,sum(sal) 薪水综合 
    

    from emp;

• count 统计的值指定字段下 非空值的数量

    -- 统计所有奖金不为空的人数
    select count(comm) from emp;
  

• count(*) 代表的是统计记录行数

    -- 统计emp表的员工人数
    select count(*) from emp;
  

• group by 分组

  • 分组会将数据按照你所指定的分组字段进行分类 所有指定字段值相同的数据会被分配到一组 你可以使用我们的聚合函数对每一组的数据进行分可别统计

      -- 查询各部门的平均工资？
      select deptno,avg(sal) from emp group by deptno;
    

  • 可以使用聚合函数分别统计不同组的相关信息

  • 数据被分组之后 select后面只可以出现 被分组的字段本身 和五种聚合函数

  • 如果出现了其他字段查询 会无法执行

  • 数据被分组之后 从行数据 变成了组数据 原本行数据字段不可以被查询

  • 可以同时基于两个字段进行分组 分组的依据 是这两个字段值的组合

      select count(*),deptno,job from emp group by (deptno,job);
    

• 条件筛选 having 用来筛选分组之后的数据（组数据）

• 条件筛选 where 用来筛选分组之前的数据 (行数据)

    -- 查询各个部门中去除JAMES这个员工之外的所有部门的平均工资情况,并且只筛选平均工资大于2000的部门数据
    select deptno,avg(sal) from emp where ename!='JAMES' group by 
    deptno having avg(sal)>2000;
  

• 排序 按照指定的字段的值 对数据进行排序

  • asc 代表升序 asc通常可以省略不写

  • desc 代表降序

      -- 查询所有员工的信息 并且按照工资进行升序排序
      select * from emp order by sal asc;
      select * from emp order by sal desc;
    </