Oracle入门到实战

数据库 专栏收录该内容
7 篇文章 0 订阅

一、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

说明
union该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行
union all该操作符与union相似,但是它不会取消重复行,而且不会排序
intersect使用该操作符用于取得两个结果集的交集
minus使用该操作符用于取得两个结果集的差集,他只会显示存在第一个集合中,而不存在第二个集合中的数据
---使用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 序列

Oraclesequence类似MySQLauto_incrementid自动增长。

创建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:缓存数

序列创建之后,可以通过序列队形的currvalnextval两个"伪列",分别访问该序列的当前值和下一个值,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 游标

游标:用于操作查询的结果集,类似JDBCResultSetOracle中的游标需要结合PLSQL使用。

语法:

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

4.6 触发器

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

触发器作用

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

触发器类型:

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

触发器语法:

---创建触发器
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

impfromusertouser的用法

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

实例:

imp root/1234@192.168.2.100:1521/orcl 
file=d:/order.dmp fromuser=order touser=root
©️2021 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值