Oracle基本操作

SQL:

​ DDL:数据定义语言

​ DML:数据操纵语言

​ DCL:数据控制语言

​ DQL:数据查询语言

创建表空间、用户及授权操作

创建表空间

create tablespace demo
datafile '/home/oracle/demospace.dbf'
-- 设置表空间大小
size 100m
-- 是否自动增大表空间
autoxtend on;
-- 每次增大多少
next 10m;

创建用户

create user guest
-- 设置用户密码
identified by 123456
-- 设置用户默认表空间
default tablespace demo;

给用户授权

权限名称对应权限
DBA拥有最高的权限
RESOURCE可以创建实体,不可以创建数据库结构
CONNECT只可以登录,不可以创建实体,不可以创建数据库
-- 授予dba权限给用户
grant dba to guest

数据类型和建表

数据类型

字符类型

名称对应类型
char固定长度字符
varchar2可变字符长度

varchar2数据类型长度问题

当字符集为UTF8,可存1333个汉字或4000个数字、字母。

当字符集为GBK2312,可存2000个汉字或4000个数字、字母。

数字类型

名称对应类型
number(总长度,小数长度)数字类型,小数长度不能大于等于总长度
integer整数类型 小的整数
real实数类型(63)精度更高
float浮点数(38)双精度

日期

名称对应类型
date年月日时分秒
timestamp时间戳

大对象

名称对应类型
LONG超长字符串 最大长度2G
BLOB二进制数据 最大长度4G
CLOB字符数据 最长4G

创建表

create table tb1(
	name varchar2(20),
	gender char(1),
	age number(3)
)

通过子查询创建表

create table tb2 as select name,gender from tb1;

数据的增、删、改

insert into tb1 values ('xiaoming','M',18);
insert into tb1 (name,gender) values ('xiaohong','F');

delete from tb1 where name='xiaoming';
-- 清空表数据
truncate table tb1;

update tb1 set age=21 where name='xiaohong';

修改表结构

添加字段

alter table tb1 add addr varchar(30);

修改列

-- 修改列数据类型
alter table tb1 modify gender varchar2(1);
-- 修改列名字
alter table tb1 rename column gender to sex;

删除列

alter table tb1 drop column sex;

修改表名

rename tb1 to tb3;

Oracle查询语句

基本查询

select * 
from tb1
[{left|right|inner} join table [as alias] on ...]
[group by column ]
[having ]
[order by column]

虚表查询

select 1+1 from dual;

空值处理

空值

空值是无效的值,未知的值,不是空格或者0;

因此 null != null ; 且任何包含null的表达式结果都为null;

判断是否为空,不能用 == 或者 != ;要用 'is null'、'is not null';

select * from tb1 where age is null;

nvl

遇到null时,可以使用函数表达式 nvl(exp1,exp2)解决

-- 假设tb1中xiaohong的age为null
-- 讲表中age为null的改为0
update tb1 set age=nvl(age,0) 

字符串

拼接方式1
 #  使用 ||  (Oracle特殊的连接符)
select ename,'income:'||(sal*12+nvl(comm,0)) from emp;

拼接方式2
# 使用concat(str1, str2)
select concat('姓名:', ename) from emp;

条件表达式

条件查询 WHERE

  • 范围

    select field1 from tb_name where 1 < field2 and field2 < 3;
    
    
  • 固定值

    select field1 from tb_name where field2 = 1;
    
    
  • select field1 from tb_name where field2 = 1 or field3 = 1; 
    
    

模糊查询 LIKE

查询名字中带有君的 
select * from students where name like '%君%';
查找名字叫张x的学生 
select * from students where name like '张_';
查找名字叫张xx的同学 
select * from students where name like '张__';
查找姓张的
select * from students where name like '%张_%';

排序查询 ORDER BY

#desc降序 asc升序
select * from tb_name order by field1 desc;

分组查询 GROUP BY

#where 在分组之前进行过滤,不能使用聚合函数_
#having 在分组之后进行过滤,可以使用聚合函数,having中用到的列,必须在前面使用过才可以用
select field1 from tb_name group by field2;

聚合函数

  • count:统计符合条件的记录的个数。
  • sum:返回对应列数据的总和。
  • max/min/avg:返回数据满足条件的列最大值、最小值、平均值。

条件查询

case表达式

/*
	以oracle示例表空间为例
	求员工名字,薪资,等级(以中文显示)
	*/
select
  e.ename,
  e.sal,
  case  s.grade
    when 1 then 'yiji'
    when 2 then 'erji'
    when 3 then 'sanji'
    when 4 then 'siji'
    when 5 then 'wuji'
   end g
from emp e join salgrade s
on e.sal >= s.losal and e.sal< s.hisal;

子查询

/*
	以oracle示例表空间为例
	*/
/*查询最高工资的员工信息*/
select * from emp where sal= (select max(sal) from emp)

/*查询比WARD的工资高,同时和SCOTT职位相同的员工信息*/
select * from emp
where sal>(select sal from emp where ename='WARD') 
and job=(select job from emp where ename='SCOTT');

/*查询每个部门薪资最低和所在部门信息*/
select
       min(e.sal),
       d.deptno,min(d.dname),min(d.loc)
from emp e join dept d on e.deptno=d.deptno
group by d.deptno

/*查询没有下属的员工 可否用exists写*/
select * from emp where empno not in
(
select 
    e2.empno
from emp e1 join emp e2 on e1.mgr=e2.empno
);

/*查询薪资大于 本部门平均工资 的员工信息*/
select
e1.*
from emp e1 join 
(
   select deptno,avg(sal) avgsal from emp group by deptno 
) e2 on e1.deptno=e2.deptno
where e1.sal>e2.avgsal

多表查询

笛卡尔积

/*emp有14条记录,dept有4条,笛卡尔积查询56条*/
select * from emp,dept;
/*查询员工及其部门信息
根据实际需要过滤出,有意义的14员工信息及员工对应的部门信息*/
select * from emp,dept
where emp.deptno = dept.deptno;

内连查询

/*查询雇员的编号,姓名,部门的编号和名称,地址*/
select emp.empno,emp.ename,dept.deptno,dept.dname,dept.loc from emp
join dept on emp.deptno = dept.deptno;
/*查询雇员编号,姓名,工作,薪资,薪资等级,losal,hisal*/
select 
  e.empno, e.ename, e.job,e.sal,
  s.grade, s.losal, s.hisal
from emp e
inner join salgrade s on e.sal between s.losal and s.hisal;

外连接

左外连接

/* 查询部门及其员工信息*/
select 
       e.*,
       d.*
from emp e
left join dept d on e.deptno = d.deptno;

右外连接

/* 查询部门及其员工信息*/
select 
       e.*,
       d.*
from emp e
right join dept d on e.deptno = d.deptno;

全连接

select 
       e.*,d.*
from emp e full join dept d on e.deptno = d.deptno;

练习:

/*查询每个雇员信息,及上级领导信息自身连接*/


集合运算

集合运算两集合查询的字段量,字段类型,顺序必须一致

并集 通过union/ union all

/*统计薪资大于1600, 或者部门号为10的雇员信息*/
select * from emp where sal>1600
union
select * from emp where deptno = 10;

交集 通过intersect

/*工资大于1600, 并且是10号部门下的员工*/
select * from emp where sal>1600
intersect
select * from emp where deptno = 10;

差集 minus

/*返回属于第一个集合,但不属于第二个集合的记录*/
select * from emp where sal>1600
minus
select * from emp where deptno = 10;

分页查询

rownum 只能 必须从1开始

/*显示前三条记录*/
select rownum,e.* from emp e where rownum<=3;
/*显示薪资top3*/
select rownum,t.ename, t.sal from 
(
   select ename,sal from emp order by sal desc
) t where rownum<=3

rownum : 分页查询 
/*查询第6 - 第10 记录*/
select t.empno, t.ename,t.sal, t.comm from 
(
select rownum r,e.* from emp e
) t where t.r between 6 and 10;

索引

索引

create table testTable (
       name varchar2(30),
       address varchar2(30)
);
/*插入500百万数据*/
declare 
begin
  for i in 1..5000000 loop
    insert into testTable values ('jack'||i,'addr'||i);
  end loop;
  commit;
end;

/*没有添加索引*/
select * from testTable where name='jack4000000';
/*为name字段添加索引*/
create index index_name on testTable(name);
select * from testTable where name='jack4000000';

select * from testTable where name='jack4000000' and address='addr4000000';
/*复合索引*/
create index index_name_addr on testTable(name,address);
select * from testTable where name='jack4000000' and address='addr4000000';


序列、视图、存储过程

序列

​ 主要是用来实现ID自增长

​ 语法:

create sequence 序列的名称
start with 从几开始
increment by 每次增长多少
maxvalue 最大值 | nomaxvalue
minvalue 最小值 | nominvalue
cycle | nocycle  是否循环
cache 缓存的数量3 | nocache 

​ 测试:

create sequence seq_test;
select test_seq.nextval from dual;
select test_seq.currval from dual;

视图

视图是一个虚拟表,并不在数据库中直接存储视图的数据值,视图的数据来自对基本表的查询。
	1.增强可读性,能够封装复杂的语句
    2.屏蔽表中的细节

语法:

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

创建视图

create or replace view view_without_sal as select empno, ename, job from emp;

存储过程

语法

create [or replace] procedure 存储过程名[(参数名 in/out 数据类型)]
as
begin
	程序体;
end;

代码

create or replace procedure proc_update_sal(vempno in number, vnum in number)
as
       vsal number;
begin
       update emp set sal=vsal+vnum where empno = vempno;
       commit;
end;

调用

call proc_update_sal(7934,100);

删除

drop procedure proc_update_sal;

plsql

​ plsql procedure language 过程语言, 可以编写分支,循环逻辑

​ 语法:

 declare
	变量名 变量类型;
	变量名 变量类型 := 初始值;
	  vsal emp.sal%type;  --引用型的变量          
 begin
	--业务逻辑
 end;

代码: 输出jack

declare
  i varchar2(10) := 'jack';
begin
  dbms_output.put_line(i);
end;

代码分支:

declare
  age number := 40;
begin
     if age <18 then
        dbms_output.put_line( '<18');
     elsif age<30 then
        dbms_output.put_line('<30');
     elsif age<50 then
        dbms_output.put_line('<50');
     else 
        dbms_output.put_line('>=50');
     end if;
end;

循环:

declare
  i number := 1;
begin
  while i<=100 loop
        dbms_output.put_line(i);
        i := i+1;
  end loop;
end;

declare
  i number := 1;
begin
  for i in 1..100 loop
      dbms_output.put_line(i);
  end loop;
end;