Oracle-基础常用语句

作者: __Y_Q | 来源:发表于2020-01-07 20:09 被阅读0次

被公司派来做后台开发, 完全没用过 Oracle 数据库, 研究了几天, 总结一下, 这个文章应该会经常更新吧. 会把一些常用的,通用的都记录下来, 方便自己以后查找.

1. 删除

删除表中全部记录

delete table  表名

删除表结果

drop table 表名

先删除表再创建表,效果等同于先先出表中全部记录,但是在数据量大的情况下, 尤其是表中有索引的情况下, 这个方式效率会很高.(因为索引会提高查询效率,但是会影响增删改的效率)

truncate table 表名

2. 序列

序列默认从 1 开始, 依次递增, 主要用来给主键赋值使用.
序列不真的属于任何一张表, 但是可以逻辑和表做绑定.
dual, 虚表, 只是为了补全语法, 没有任何意义
创建序列语法
CREATE SEQUENCE 序列名
INCREMENT BY n (可选参数,n表示递增几)
START WITH n (可选参数, n 表示从多少开始)
{ MAXVALUE n/ MINVALUE n | NOMAXVALUE } (可选参数,最大值,最小值...)
{ cache n | NOCACHE } 缓存

创建一个序列

create sequence 序列名;

查看当前序列编号是多少

select 序列名.currval from dual;

序列自增

select 序列名.nextval from dual;

把序列当做主键插入(假如列 1,是主键列)

insert into 表名 (列 1, 列 2) values (序列名.nextval, 列2值);
commit;

3. 基本函数

字符函数

转大小写

select upper('yes') from dual; --YES
select lower('YES') from dual; --yes

数值函数
- 四舍五入,并保留 1 位小数
```
select round(26.15,1) from dual;
```
- 保留 1 位小数,直接截取
```
select trunc(56.16,1) from dual;
```
- 求余
```
select mod(10,3) from dual;
```
- 上面三个的运行结果分别为
```
26.2
56.1
1
```
日期函数
就以经典的 emp 表(员工) 和 dempt 表(部门) 这两个表为例.
- 查询出员工表中所有员工入职距离现在几天
```
select sysdate - e.hiredate from emp e;
```
- 算出明天此刻
```
select sysdate + 1 from dual;
```
- 查询出员工表中所有员工入职距现在几个月
```
select months_between(SYSDATE , e.hiredate ) from emp e;  
```
- 查询出员工表中所有员工入职距现在几年
```
SELECT MONTHS_BETWEEN(SYSDATE , e.hiredate ) / 12 from emp e; 
```
- 查询出员工表中所有员工入职距现在几周
```
SELECT ROUND((SYSDATE - e.hiredate)/7) from emp e;
```

转换函数

当前日期转换成字符串,12小时制,日期和时间两位,不足补 0

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh:mi:ss' ) from dual;

当前日期转换成字符串,12小时制,日期不足两位,就显示一位

SELECT TO_CHAR(SYSDATE,'fm yyyy-mm-dd hh:mi:ss' ) from dual;

当前日期转换成字符串,24小时制,日期和时间两位,不足补 0

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss' ) from dual;

上面三个的运行结果

2020-01-08 09:22:15
2020-1-8 9:22:15
2020-01-08 09:22:15  (因为时间问题,这个 24 小时制的可能看着不是很明显)

字符串转日期

SELECT to_date('2020-01-4','fm yyyy-MM-dd hh24:mi:ss' ) from dual;

运行结果

2020-01-04 00:00:00

4. 聚合函数

count(1), sum, max, min, avg 都可以使用.

分组查询
(分组查询出现在 group by 后面的原始列, 才能出现在 select 后面. 如果没有出现在 group by 后面的列, 想在 select 后出现, 必须加上聚合函数)
where 是过滤分组前的数据, having 是过滤分组后的数据

表现形式:where必须在 GROUP BY 之前, HAVING 是在 GROUP BY 之后.

查询出每个部门的平均工资.

SELECT e.deptno,avg(e.sal)  from emp e GROUP BY e.deptno

查询出平均工资高于 2000 的部门信息,所有条件都不能使用别名来判断.

SELECT e.deptno,avg(e.sal)  from emp e GROUP BY e.deptno HAVING avg(e.sal) > 2000; 
SELECT e.deptno,avg(e.sal) s from emp e GROUP BY e.deptno HAVING s > 2000;  ---这样不行

查询出每个部门工资高于 900 的员工的平均工资

SELECT e.deptno,avg(e.sal)  from emp e where e.sal > 900 GROUP BY e.deptno

查询出每个部门工资高于 800 的员工的平均工资,然后再查询出平均工资高于 2000 的部门

SELECT e.deptno,avg(e.sal)  from emp e where e.sal > 800 GROUP BY e.deptno HAVING avg(e.sal) > 2000

5. 非空处理

算出 emp表中所有员工的年薪, null 值和任意数字做算术运算, 结果都是 NULL

--如果 e.comm 不为 null,就使用 e.comm 值,为 null 就为 0
SELECT c.sal * 12 + nvl(e.comm , 0) from emp e;

6. 条件表达式

表达式的通用写法, oracle 和 mysql 都可以使用.
给员工表中的员工起中文名字

select e.ename, 
  case e.ename
      when 'SMITH'
            then '张三'
      when 'ALLEN' 
            then '李四'
      when 'WARD' 
            then '王二`
      else '麻子'
            end
from emp e;

判断收入

select e.sal, 
    when e.sal > 3000
        then '高收入'
    when e.sal > 1500
        then '中等收入'
    else '低收入'
        end
from emp e;

7. 多表查询

等值连接,等同于内连接

 select * from emp e, dept d where e.deptno = d.deptno

内连接

SELECT * from emp e inner join dept d on e.deptno = d.deptno

外连接

SELECT * from emp e right join dept d on e.deptno = d.deptno

查询出所有员工信息及员工所属部门

SELECT * from emp e left join dept d on e.deptno = d.deptno

直连接(站在不同的角度,把一张表看成多张表)
查询所有的员工姓名, 员工领导姓名 (e1 为员工, e2 为领导)

select e1.ename, e2.ename  
from emp e1, emp e2
where e1.mgr = e1.empno

查询所有的员工姓名,员工所属部门名称, 员工领导的姓名,员工所属领导的所属部门名称.

select e1.ename, d1.dname,  e2.ename, d2.dname
from emp e1, emp e2, dept d1, deptd2
where e1.mgr = e1.empno
and e1.deptno = d1.deptno
and e2.deptno = d2.deptno

8. 子查询

查询出工资和 xx 一样的员工信息

select * from emp where sal in (select sal from emp where ename = 'xxx')

子查询返回一个集合-查询出工资和 2 号部门任意员工一样的员工信息

---先查出 2 号部门所有人工资的一个集合
--- select sal from emp where deptno = 2
select * from emp where sal in (select sal from emp where deptno = 2)

子查询返回一张表(多行记录)-查询出每个部门最低工资和最低工资员工姓名和该员工所在部门的的名字

---先查出每个部门的最低工资,包含部门 id 和最低工资
---select deptno,min(sal) msal from emp group by deptno
select 
      t.deptno, t.msal,
      e.ename,
      d.dname
from  
      (select deptno,min(sal) msal from emp group by deptno) t ,
      emp e,
      dept d
where
      t.deptno = e.deptno  ---确保在同一部门
and 
      t.msal = e.sal       ---同一个部门下的最低工资
and 
      d.deptno = e.deptno  ---建立关系

9. 分页查询

Orcale 分页查询,基本都是固定的格式.
ROWNUM: 行号, 当我们做 select 操作的时候, 每查询出一条记录, 就会在该行加上一个行号, 行号从 1 开始, 依次递增,

对员工表倒序排序后,进行分页,每页五条记录,查询第二页
- 先排序,再分页.
- 排序操作会影响 ROWNUM 的顺序, 所以需要先排序再加 ROWNUM.

SELECT * from (
    SELECT ROWNUM rn,e.* FROM (
        SELECT * from emp  ORDER BY sal desc
    ) e where ROWNUM < 11 
) where rn > 5

10. 索引

就是在表的列上构建一个二叉树, 达到大幅度提高查询效率的目的,但是索引会影响到增删改的效率.
索引分为:单列索引, 复合索引

为员工表的 ename 列创建一个单列索引,

create index idx_ename on emp(ename);

单列索引的触发规则,条件必须是索引列中的原始值.
例: select * from emp where ename = '张三'

为员工表的名字和工作创建一个复合索引

CREATE INDEX idx_enamejob on emp(ename,job);

复合索引触发规则
复合索引中第一列为优先检索列, 如果要触发复合索引, 必须要包含有优先检索列中的原始值
例:
SELECT * from emp where ename = 'scott' ---查询的既是单列索引又是多列索引的, 这条语句触发单列索引
SELECT * from emp where ename = 'scott' or job='xxx' ---不触发索引.

11. 游标

游标可以存放多个对象, 多行记录, 类似 java 中的集合

输出 emp 表中所有员工的姓名

DECLARE
    cursor c1 is SELECT * from ZYQ_PERSON; ---创建游标并且设置值
    personrow ZYQ_PERSON%rowtype;  ---创建记录型变量
BEGIN
    open c1;  ----打开游标
            loop  ---循环
                FETCH c1 into personrow;  ---从游标中取出一行放入变量
                exit when c1%notfond;  --- 如果没有记录了就退出游标
                dbms_output.put_line(personrow.pname); ---打印输出
            END loop;
    close c1;
END;

带参数的游标-给指定部门员工涨工资

declare
        ---定义一个带参数的游标,查询出所有的empno,
        ---参数类型为 emp 表中 deptno 字段的类型. 参数名称为 dno
        ---根据游标内参数部门 ID 查询出所有这个部门下的员工编号.
        ---把所有员工编号赋值给游标 c2
        cursor c2(dno emp.deptno%type ) is SELECT empno from emp where deptno = dno;        
        eno emp.empno%type; ---定义一个 emp.empno 类型的变量 eno
BEGIN
        open c2(10);  ---打开游标的时候就要赋值
            loop  
                 FETCH c2 into en; ----把游标内的一行员工编号赋值给 eno,
                 exit when c2%notfount;  ---当游标内找不到的时候退出
                 ----通过en 员工编号来进行更新
                 UPDATE emp set sal = sal + 100 WHERE empno = en; 
                 commit;
            end loop;
        close c2;  ---关闭游标
END;

12. pl / sql

pl / sql 是对 sql 语言的扩展, 使得 sql 语言具有过程化变成的特性.
面向过程,比一般化的编程语言更加灵活高效.
主要用来编写存储过程和存储函数等.

声明方法, 定义变量, 变量赋值

DECLARE
        i number(2) := 10; ---赋值操作,声明一个变量 i
        s VARCHAR2(10) := '张三';
        ena ZYQ_PERSON.pname%type; ---变量类型为表中的某个字段类型. 引用型变量
        person_row ZYQ_PERSON%rowtype; ---记录型变量.
BEGIN
        dbms_output.put_line(i);
        dbms_output.put_line(s);
        SELECT pname into ena from ZYQ_PERSON where pid = 6; ---通过查询来赋值  into 关键字.
        dbms_output.put_line(ena);
        SELECT * into person_row from ZYQ_PERSON where pid = 6; ---记录一行数据超
        dbms_output.put_line(person_row.pname || '的 ID 是: ' || person_row.pid ); --- ||为连接符
END;

pl / sql中的 if 判断

DECLARE
        i number(3) := &ii; ---获取输入的一个值
BEGIN
        if i < 18 then
              dbms_output.put_line('未成年');
        ELSIF i < 40 then
          dbms_output.put_line('中年人');
        else
          dbms_output.put_line('老年人');
        end if;
END;

pl / sql 的 loop 循环1. 输出 1-10

DECLARE
    i number(2):=1;
BEGIN
    while i<11 loop
        dbms_output.put_line(i);
        i:= i+1;
    END loop;
END;

pl / sql 的 loop 循环2. 输出 1-10, 退出循环,常用.

DECLARE
    i number(2) := 1;
BEGIN
    loop
        exit when  i > 10;
        dbms_output.put_line(i);
        i:= i+1;
    end loop;
END;

pl / sql 的 loop 循环2. 输出 1-10, for 循环

DECLARE
BEGIN
    for i in 1..10 loop
    dbms_output.put_line(i);
    END loop;
END;

13. 存储过程

存储过程就是自己提前已经编译好的一段 pl / sql 语言, 放在数据库端, 可以直接被调用. 这段 pl / sql 一般都是固定步骤的业务
存储过程参数分为 in 和 out 两种. 不显示声明 out ,默认就是为 in 类型
in 和 out 参数的区别:
凡是涉及到 into 查询语句赋值或者 := 赋值操作的参数,都必须使用 out 来修饰,其余的都用 in

给指定员工涨 100 - in 类型参数

--- CREATE or replace 创建或者替换, or replace可以省略,不过一般都会加上
--- 关键字 PROCEDURE
--- p1 存储过程的名称
---(eno emp.empno%type)  参数, 没有指定类型,默认为 in 类型
CREATE or replace PROCEDURE p1(eno emp.empno%type)  
is
BEGIN
    update emp set sal= sale + 100 where empno = eno;
    commit;
END;

测试刚才创建的存储过程

DECLARE
BEGIN
    p1(5);
END;

计算年薪 - out 类型参数

---这个存储过程有两个参数
---1. emp.empno 类型的参数 eno, in 类型
---2. number 类型的参数 yearsal, 指定为 out 类型.(这里参数类型不能加上长度)
CREATE or replace PROCEDURE zyq_tout_yearsal(eno emp.empno%type,yearsal out number)
is
    ---声明变量 s 这里可以加上长度
    s number(10);      
    ---声明变量 c ,为 emp.comm奖金字段类型,
    c emp.comm%type; 
BEGIN
    ---查询指定员工编号的 12 个月工资和奖金,分别赋值给 s 和 c.
    ---如果奖金为 null 值就为0,否则就使用奖金字段的值
    SELECT sal*12, nvl(comm,0) into s,c from emp where empno = eno;
    ---给 out 型参数赋值.
    yearsal := s+c;
END;

测试这个存储过程

DECLARE
    ---声明一个为 number 类型的变量,用来接收存储过程 out 参数的值
    yearsal number(10);
BEGIN
    ---调用存储过程,传入 in 类型参数指定员工编号, 传入刚才声明的变量,来接收 out 类型参数的值
    zyq_tout_yearsal(1,yearsal);
    ---输出
    dbms_output.put_line(yearsal);
END;

14. 存储函数

通过存储函数来计算指定员工的年薪

--- 关键字 FUNCTION
--- 传入参数 emp 表中  empno字段类型的 eno
--- return number ,返回值类型为 number 类型, 这里 number 也不能设置长度
CREATE or REPLACE FUNCTION  f_yearsal(eno emp.empno%type) return number 
is
    ---定义一个变量来接收查询出的结果
    s number(10);  
BEGIN
    SELECT sal * 12 + nvl(comm,0) into s from emp where empno = eno;    
    return s;
END;

测试存储函数

DECLARE
    result number(10);  
BEGIN
    ---需要在调用的时候用变量来接收
    s := f_yearsal(5)
    dbms_output.put_line(s);
END;

根据输入的 id,返回字符串包含 ID 和名字

--- return VARCHAR2 定义返回值的类型,
---(id ZYQ_PERSON.PID%type)定义参数类型
CREATE or REPLACE FUNCTION  zyq_f(id ZYQ_PERSON.PID%type) return VARCHAR2
is
    bname VARCHAR2(30); 
    bid  number(10);
    result VARCHAR2(500); ---定义一个变量来接收查询出的结果
BEGIN
    ---把查询出来的名字和 ID 赋值给上面声明的两个变量
    SELECT pid,pname into bid,bname from ZYQ_PERSON where PID = id;
    ---组合返回值
    result := bname || ' 的 ID 是: ' || bid;
    ---返回返回值
    return result;
END;

测试

DECLARE
    result VARCHAR2(500);
BEGIN
    s := zyq_f(5)
    dbms_output.put_line(s);
END;

使用通用的查询语句调用存储函数 - 根据部门编号,输出一个部门名称

---创建一个存储过程
---返回值为 dept表中 dname 字段的属性
---参数为 dept 表中 deptno 字段的属性
CREATE or REPLACE FUNCTION finddname(dno dept.deptno%type) return dept.dname%type
is 
    ---声明变量
    dna dept.dname%type;
BEGIN
    ---根据传入的部门编号,输出部门名称,并赋值给 dna
    SELECT dname into dna from dept where deptno = dno;
    ---返回 dna
    return dna;
END;

调用

---输出指定部门 ID 下的
select e.ename , finddname(e.deptno) from emp e;

15. 存储函数和存储过程的区别

存储函数和存储过程的参数都不能带长度
存储函数的返回值类型不能带长度
他们的关键字不同, 存储过程是PROCEDURE,存储函数是 FUNCTION
存储函数比存储过程多了两个 return .

本质区别就是: 存储函数有返回值, 存储过程没有.
如果存储过程想实现有返回值的业务,我们就必须使用out 类型的参数, 即便是使用了out 类型的参数,其本质也不是真的有了返回值, 而是在存储过程内部给 out 类型参数赋值, 在执行完毕后,我们直接拿到输出类型参数的值.

16. 触发器

触发器就是一个规则, 在我们做增删改操作的时候, 只要满足这个规则, 就会自动触发, 不需要手动调用.
触发器分为两类,
语句级触发器: 不包含 for each row 关键字的就是语句级触发器.
行级触发器: 包含 for each row 关键字的就是行级触发器.
包含 for each row 关键字是为了使用 :old, :new 关键字获得一行记录

触发语句	:old	:new
insert	所有字段都是 null	将要插入的数据
update	更新以前该行的数据	更新后该行的数据
delete	删除以前该行的数据	所有字段都是 null

语句级触发器, 为某张表的插入语句做一个触发器, 每当执行插入的时候,就会输出一句话.

---TRIGGER 关键字
---AFTER, BEFORE 执行之后触发,还是执行之前触发
CREATE  OR  REPLACE TRIGGER zyq_t1
AFTER
      ---触发器目标是  ZYQ_PERSON 表的插入语句
      INSERT ON ZYQ_PERSON 
declare
begion
      ---触发后要执行的操作
      dbms_output.put_line('刚才执行了添加操作');
end;

测试

INSERT into ZYQ_PERSON VALUES(1,'张三');
commit;

输出

刚才执行了添加操作

行级触发器. 不能给员工降薪, (更新薪资的时候需要判断更新前和更新后的薪资)

---这个需要在执行前进行触发
---包含了 for each row 关键字
CREATE OR REPLACE TRIGGER zyq_t2
before
        UPDATE ON ZYQ_PERSON for each row
declare
        ---如果更新前该行的薪资,大于要更新值的薪资,就抛出异常
        if :old.sal > :new.sal then
              raise_application_error(-20001, '不能给员工降薪');  ---抛出异常  -20001~-20009之间
        end if;
end;

我们还可以使用触发器来实现主键的自增 (在用户插入数据操作之前, 拿到即将插入的数据, 然后查询序列, 给该数据中的主键列赋值)

CREATE OR REPLACE TRIGGER auid
before
      INSERT ON ZYQ_PERSON for each row
declare
begin
      ---在插入操作之前,获取序列的下一个值,赋值给新增数据的主键列
      SELECT S_ZYQ_PERSON.nextval into :new.pid from dual;
end;

测试

---在插入的时候,就不需要为主键赋值了
INSERT into ZYQ_PERSON (pname) VALUES ('a');
commit;