Oracle入门到实战

╰半橙微兮° 2023-05-30 09:20 23阅读 0赞

### 文章目录 ###

*  一、oracle基础语句
 *  二、oralce表查询关键字
 *   *  2.1 使用逻辑操作符号
     *  2.2 使用order by字句
     *  2.3 取别名
     *  2.4 聚合函数
     *  2.5 group by 和 having
     *  2.6 多表查询
     *  2.7 单行子查询
     *  2.8 多行子查询
     *  2.9 多列子查询
     *  2.10 用查询的结果创建新表
     *  2.11 union
     *  2.12 exists
 *  三、oracle常用函数
 *  四、oracle常用结构
 *   *  4.1 dual 表的介绍
     *  4.2 view 视图
     *  4.3 sequence 序列
     *  4.4 index 索引
     *  4.5 cursor 游标
     *  4.6 触发器
     *  4.7 序列+触发器模拟主键自增
     *  4.8 创建用户
     *  4.9 数据库数据导入、导出

# 一、oracle基础语句 #

**新建表**:

CREATE TABLE USERS(
        ID NUMBER(4) PRIMARY KEY,
        NAME VARCHAR(10),
        SEX CHAR(2),
        ADDR VARCHAR2(20),
        BIRTHDAY DATE,
        SAL NUMBER(8,2)
    );

**查看表的结构**：`DESC USERS;`

**增**：`insert into users(id,name,sex,ADDR,BIRTHDAY,sal)values(1,'Jack','1','USA.Los Angeles',to_date('20140214','yyyymmdd'),1000);`

**删**：`delete users where users.name='Bob';`

**改**：`update users set users.name='Jackie' where users.name='Jack';`

**查**：`select * from users;`

# 二、oralce表查询关键字 #

## 2.1 使用逻辑操作符号 ##

select * from emp where (sal <> 500 or job = 'MANAGER') 
    and ename like 'J%';

## 2.2 使用order by字句 ##

select * from emp order by sal desc

## 2.3 取别名 ##

select e.empno "部门编号",mgr m from emp e;

注意：中文需加双引号

## 2.4 聚合函数 ##

---求工资最高的人
    select ename,sal from emp where sal = (select max(sal) from emp);

## 2.5 group by 和 having ##

select  avg(sal), max(sal), deptno from emp 
    group by deptno having avg(sal)< 2000;

**使用关键字的顺序：group by->having->order by**

## 2.6 多表查询 ##

笛卡尔积：多表查询的条件是至少不能少于表的个数`N-1`才能排除笛卡尔集（如果有`N`张表联合查询，必须得有`N-1`个条件，才能避免笛卡尔集合）

---显示各个员工的姓名，工资及工资的级别
    select e.ename,e.sal,s.grade from emp e,salgrade s 
    where e.sal between s.losal and s.hisal;
    
    ---查询员工名和上级名
    select worker.ename,boss.ename from emp worker,emp boss 
    where worker.mgr=boss.empno;

## 2.7 单行子查询 ##

---查询和smith同部门的员工
    select * from emp
    where deptno=(select e.deptno from emp e where e.ename='SMITH')

## 2.8 多行子查询 ##

---查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
    select e2.ename,e2.job,e2.sal,e2.deptno from emp e2 
    where e2.job in
    (select distinct e.job from emp e where e.deptno=10)

## 2.9 多列子查询 ##

---查询与SMITH 的部门和岗位完全相同的所有雇员
    SELECT * FROM emp 
    WHERE (deptno, job) = (
    SELECT deptno, job FROM emp WHERE ename = 'SMITH');

## 2.10 用查询的结果创建新表 ##

CREATE TABLE user (id, name, sal, job, deptno) 
    as SELECT empno, ename, sal, job, deptno FROM emp;

## 2.11 union ##

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>union</td> 
   <td>该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行</td> 
  </tr> 
  <tr> 
   <td>union all</td> 
   <td>该操作符与union相似，但是它不会取消重复行，而且不会排序</td> 
  </tr> 
  <tr> 
   <td>intersect</td> 
   <td>使用该操作符用于取得两个结果集的交集</td> 
  </tr> 
  <tr> 
   <td>minus</td> 
   <td>使用该操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据</td> 
  </tr> 
 </tbody> 
</table>

---使用union关键字
    SELECT ename, sal, job FROM emp WHERE sal >2500
    UNION
    SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

## 2.12 exists ##

--查询table1中存在的数据在table2中不存在的数据
    select * from table1 t1 
    where not exists (select bh from table2 t2 where t1.bh=t2.bh);
    
    ---查询table1、table2中都存在的数据
    select * from table1 t1 
    where exists (select bhfrom table2 t2 where t1.bh=t2.bh)

# 三、oracle常用函数 #

*  `lower(char)`：将字符串转换为小写的格式
 *  `upper(char)`：将字符串转换为大写的格式
 *  `length(char)`：返回字符串的长度
 *  `substr(char,m,n)`：截取字符串的子串，`n`代表取几个字符
 *  `replace(char1,search_string,repacle_string)`：替换
 *  `instr(C1,C2,I,J)`：`C1`\->被搜索的字符串 `C2`\->希望搜索的字符串 `I`\->搜索的开始位置，默认为1 `J`\->出现的位置，默认为1；若存在则返回索引，若不存在则返回<1
 *  `to_char()`函数：可以将时间转换为字符串
 *  `to_date()`函数：可以将字符串类型转换为`date`类型

# 四、oracle常用结构 #

## 4.1 dual 表的介绍 ##

`dual`是一个虚拟表，用来构成`select`的语法规则，`oracle`保证`dual`里面永远只有一条记录，可以用它来做很多事情。其作用如下：

1.  查看当前用户，可以执行下面语句：`select user from dual`
2.  用来调用系统函数
3.  得到序列的下一个值或当前值，用下面语句

select your_sequence.nextval from dual;--获取序列的your_sequence的下一个值
    
    select your_sequence.currval from dual;--获取序列your_sequence的当前值

1.  可以用于计算

## 4.2 view 视图 ##

视图是查询结果的一个封装，视图中的所有数据都来自它查询的表，视图本身不存储任何数据。视图能封装复杂的查询结果

创建视图语法:

create [or replace] view 视图名 as 查询语句 [with read only]

## 4.3 sequence 序列 ##

`Oracle`的`sequence`类似`MySQL`的`auto_increment`，`id`自动增长。

创建`sequence`语法：

create sequence 序列名 
    [start with num] [increment by increment] 
    [maxvalue num|nomaxvalue] [minvalue num| nominvalue] 
    [cycle| nocycle] [cache num | nocache]

语法解析：

*  `start with`：从某一个整数开始，升序、降序默认值均为1
 *  `increment by`：增长数，升序、降序默认值均为1
 *  `maxvalue`：最大值
 *  `nomaxvalue`:最大值的默认选项，升序的最大值是10^27，降序默认值是1
 *  `minvalue`：指最小值
 *  `nominvalue`:最小值默认值选项，升序默认值是 1 ，降序默认值是 10的26 次方
 *  `cycle`：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最小值后，从最大值重新开始
 *  `nocycle`：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认`NOCYCLE`
 *  `cache`：使用`CACHE`选项时，该序列会根据序列规则 预生成一组序列号 。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列 号的效率。 `Oracle`默认会生产 20 个序列号
 *  `nocache`：不预先在内存中生成序列号
 *  `cache num`：缓存数

序列创建之后，可以通过序列队形的`currval`和`nextval`两个"伪列"，分别访问该序列的当前值和下一个值，`currval`必须在`nextval`调用之后才能使用。

使用`alter sequence`可以修改序列（`alter sequence seq1 cache 10`），在修改序列时有如下限制：

1.  不能修改序列的初始值
2.  最小值不能大于当前值
3.  最大值不能小于当前值

删除序列对象：`drop sequence seq2;`

## 4.4 index 索引 ##

索引相当于一本书的目录，能过提供检索的速度，如果某一列需要经常作为查询条件，则有必要为其创建索引，能显著提供效率。`Oracle`数据库会为表的主键和包含唯一约束的列自动创建索引。

CREATE [UNIQUE] INDEX index_name 
    ON table_name(column_name[,column_name…])

语法解析：

1.  `unique`指定索引列上的值必须是唯一的，称为唯一索引，否则就是普通索引
2.  `index_name`：指定索引名
3.  `table_name`：指定要为哪个表创建索引
4.  `column_name`：指定要对哪个列创建索引。我们可以对多列创建索引，这种索引称为组合索引

**索引测试:**

---创建表
    create table person(
    	pid number(32) primary key, 
    	pname varchar2(16) not null,
    	paddress varchar2(16) not null
    );
    	
    ---使用 PLSql 语法插入 500万条数据。plsql 是 Oracle 对原生sql的封装，
     declare 
     begin
       for i in 1..5000000 loop
         insert into person values(i,'姓名'||i,'地址'||i);
         end loop;
         commit;
     end;
        
    ---在没有使用索引的情况下，查询 panme='姓名4000000' 的用户 。耗时：3~5秒
    select * from person where pname = '姓名4000000';
    
    --实际中建表后就应该设置索引，这里已经有500万条数据后再创建索引花了56秒
    create index index_pname on person(pname);--为 pname 列创建索引
    
    ---使用索引的情况下，查询 panme='姓名4000000' 的用户 。耗时：0.01秒
    select * from person where pname = '姓名4000000';
    
    ---在没有复合索引的情况查询 
    ---panme='姓名4000000' 且 paddress='地址4000000' 的用户，耗时：0.032
    select * from person 
    where pname = '姓名4000000' and paddress = '地址4000000';
    
    ---为 panme 、paddress 创建复合索引后再次查询。耗时：0.25
    ---与没建复合索引区别不是很明显
    create index index_pname_paddress on person(pname,paddress);
    
    --花了 46秒
    select * from person 
    where pname = '姓名4000000' and paddress = '地址3500000';

经实测发现，`pname`建了复合索引之后，`select * from person where pname = '姓名4000000';` 速度更慢了!

**结论：**

索引的原理底层使用是平衡二叉树。数据库中索引`index`的概念与目录的概念非常类似。如果某列出现在查询的条件而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据排序，生成独立的索引表。如果该列出现在查询条件中，`oracle`会自动引用该索引，先从索引表中查出符合记录的`rowid`,由于`rowid`是记录的物理地址，因此可以根据`rowid`快速的定位到具体的记录，表中的数据很多时，引用索引带来的查询效率非常可观。

如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该 列创建索引。当从很多行的表中查询少数行时 ，也要考虑创建索引。有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的 10%时，索引就非常有用。

索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。

## 4.5 cursor 游标 ##

游标：用于操作查询的结果集，类似`JDBC`的`ResultSet`。`Oracle`中的游标需要结合`PLSQL`使用。

语法：

cursor 游标名[(参数名 参数类型)] is 查询结果集

## 4.6 触发器 ##

触发器：是一个与表关联的、存储的`PL/SQL`程序，当用户执行了`insert`、`update`、`delete`操作之后，`oracle`自动地执行触发器中定义的语句序列。

**触发器作用**：

1.  数据确认：如员工涨薪后，新工资不能少于之前的工资
2.  安全性检查：如禁止非工作时间插入新员工
3.  做审计，跟踪上所做的数据操作等
4.  数据的备份与同步

**触发器类型:**

*  语句级触发器：在指定的操作语句之前或者之后执行一次，不管这个语句影响了多少行语句
 *  行级触发器：触发语句作用的每一条记录都被触发，在行级触发器中使用`old`和`new`伪记录变量，识别值的状态

**触发器语法:**

---创建触发器
    create [or replace] trigger 触发器名
    before/after
    insert/update/delete [of 列名]
    on 表名
    [for each row [when(条件)]]
    declare
    ...
    begin
    	PLSQL块
    end
    
    ---删除触发器
    drop trigger 触发器名

**触发器案例:**

---新员工入职后，输出 "欢迎加入" 字符串。创建触发器
    create or replace trigger trig_show_hello
    after             ---after 表示操作后触发
    insert on emp
    declare
    begin
    dbms_output.put_line('欢迎加入');
    end;
     
    ---插入员工。插入成功后就会触发上面的 trig_show_hello 触发器
    insert into emp 
    values(9996,'华安','MANAGER','7698',sysdate,9888.87,300,30);

---更新所有员工的薪水，同一加 100，创建触发器，更新完成后给出提示
    create or replace trigger tric_update_sal
    after update on emp
    for each row --表示行级触发器
    declare
    begin
    --- :old 表示操作前的记录行，:new 表操作后的记录行
    dbms_output.put_line('原来工资：'||:old.sal|| ' 现在薪水：'||:new.sal);
    end;
     
    ---更新员工薪水。自动触发上面的 tric_update_sal 触发器
    update emp set sal = sal+100;

## 4.7 序列+触发器模拟主键自增 ##

---模拟 mysql 中主键 id 的自增属性 auto_increment
    ---Oralce 中可以使用 序列 sequence 结合 触发器 trigger 达到同样的效果
     
    ---先建一张表
    create table person2(
    pid number(32) primary key,
    pname varchar2(16)
    );
    
    ---创建一个序列。默认从1开始，每次递增1，没有最大值
    create sequence person2_id_sequ;
     
    ---创建触发器
    create or replace trigger trig_person2_add_pid
    before insert on person2 ---在插入数据前触发，因为需要修改 pid 的 null值
    for each row  ---行级触发器
    declare
    begin
    	---打印语句，可以删除
        dbms_output.put_line('新增员工名称为：'||:new.pname);
        ---正式插入前修改新记录的 pid 字段值
        select person2_id_sequ.nextval into :new.pid from dual;
    end;
     
    --插入用户
    --插入前触发器会自动通过序列修改 pid 的 null 值为具体的数字
    insert into person2 values(null,'华安');
    insert into person2 values(2,'华安');
    
    --此时自己设置 pid 也是无效的
    select * from person2;

## 4.8 创建用户 ##

--创建用户
    create user 用户名 identified by 密码 default tablespace 表空间表;
    
    --赋予dba权限
    grant dba to user

## 4.9 数据库数据导入、导出 ##

**语法**：

--导入
    imp/exp 用户名/密码
    @service_name或oracle_sid 
    full=y file=C:\导出文件名称.dmp ignore=y

`imp`中`fromuser`和 `touser`的用法

*  `fromuser`：把当前的`dmp`文件中的某一个用户下的数据取出
 *  `touser`：把现在`dmp`文件中的数据导入到目标库的指定user下

**实例：**

imp root/1234@192.168.2.100:1521/orcl 
    file=d:/order.dmp fromuser=order touser=root