Oracle

Oracle

以下用到的表(除了DML模块创建了一些表外)都是安装完Oracle后,自带的一些表

SQL 的分类以及每类分类的常见的操作

DDL : 数据定义语言 create alter drop truncate
DML : 数据操纵语言 insert update delete
DCL : 数据控制语言 安全 授权 grant revoke
DQL : 数据查询语言 select

SQL执行顺序

from .. where ..group by..having .. select..rownum..order by

基本查询

select {列名/*} from 表名 {where 条件} {group by 分组条件} {having 过滤} {order by 排序}

select * from emp;

别名查询

使用as关键字,可以省略

select ename "姓 名",sal 工资 from emp;		--别名中不能有特殊字符或关键字,如果有就需要加双引号

去除重复数据 distinct;

select distinct job from emp;				--单列去除重复

select distinct job,deptno from emp;		--多列去除重复 每一列都一样才算重复

查询中的四则运算

select 1+1;   								--orcal报错,缺少from表名  mysql中是正确的

select 1+1 from dual;						--dual是虚表/伪表 主要用来补齐语法结构

• 查询员工年薪 = 月薪 * 12

  select sal*12 from emp;
  

• 查询员工年薪 + 奖金

  select sal*12 + commn from emp;			--有错误 null值,代表不确定的 不可预知的内容不可以做四则运算
  
  select sal*12 + nav(comm,0) from emp;	--nav函数:如果参数1为null 就返回参数2
  

字符串拼接

• 查询员工姓名

  select '姓名:' || ename from emp;        --||表示拼接
  
  select concat('姓名:',ename) from emp; 	--concat函数:字符串拼接,mysql和orcal中都有
  

条件查询

like : 模糊查询

in(set) : 在某个集合内

between .. and .. : 在某个区间内

is null : 判断为空

is not null : 判断不为空

• 查询每月能够得到奖金的员工信息

  select * from emp where comm is not null
  

• 查询月薪在1500-3000之间的员工信息

  select * from emp where sal >= 1500 and sal <= 3000;
  
  select * from emp where sal between 1500 and 3000;
  

• 查询名字在某个范围内的员工信息

  select * from emp where ename in ('JONES','SCOTT','FORD');
  

• 查询员工姓名第三个字符是o的员工信息

  select * from emp where ename like '___O%';
  

• 查询员工姓名中包含&的员工信息

  select * from emp where ename like '%\%%' escape '\';	 --如果有特殊字符,需要用escape转义
  

排序

• 查询员工信息,按照奖金降序排序

  select * from emp order by comm desc nulls last;
  

• 查询部门编号和工资,按照部门编号升序排序,工资降序排序

  select deptno,sal from emp order by deptno asc,sal desc;
  

函数 必须要有返回值

单行函数: 对某一行的某一个值进行处理 数值函数 字符函数 日期函数 转换函数 通用函数

多行函数: 对某一列的所有行进行处理 max min count sum avg 直接忽略空值

• 统计员工工资总和

  select sum(sal) from emp;
  

• 统计员工个数

  select count(*) from emp;
  

• 统计员工的平均奖金

  select avg(comm) from emp;				--错误,comm有空值
  
  select sum(comm)/count(1) from emp;
  

数值函数

• 四舍五入

  select ceil(45.926) from dual;	--46
  
  select floor(45.926) from dual;	--45
  
  select round(45.926,2)			--45.93
  
  select round(45.926,1)			--45.9
  
  select round(45.926,0)			--46
  
  select round(45.926,-1)			--50
  
  select round(45.926,-2)			--0
  
  select round(65.926,-2)			--100
  

• 截断

  select trunc(45.926,2)			--45.92
  
  select trunc(45.926,1)			--45.9
  
  select trunc(45.926,0)			--45
  
  select trunc(45.926,-1)			--40
  
  select trunc(45.926,-2)			--0
  
  select trunc(65.926,-2)			--0
  

• 求余

  select mod(9,3) from dual;		--0
  
  select mod(9,4) from dual; 		--1
  

字符函数

• 获取字符串子串 ( 不管是0还是1都是从第一个字符开始 )

  select substr('abcdefg',0,3) from dual;		--abc
  
  select substr('abcdefg',1,3) from dual;		--abc
  

• 获取字符串长度

  select length('abcdefg') from dual;			--7
  

• 去除字符串左右两边空格( 中间的空格不会去除 )

  select trim(' hel lo ') from dual;			--hel lo
  

日期函数

• 查询今天的日期

  select sysdate from dual;
  

• 查询三个月后的日期

  select add_months(sysdate,3) from dual;
  

• 查询员工入职的天数

  select ceil(sysdate - hiredate) from emp; 
  

转换函数

• 数值转字符

  select to_char(sal,'$9,999.99') from emp;
  

• 字符转数值

  select 100 + '10' from dual; 				--110,默认转换
  
  select 100 to_number('10') from dual;		--110
  

• 日期转字符

  select to_char(sysdate,'yyyy-mm--dd hh:mi:ss') from dual;
  
  select to_char(sysdate,'d') from dual;		--代表一星期中的第几天
  
  select to_char(sysdate,'dd') from dual;		--代表一个月中的第几天
  
  select to_char(sysdate,'ddd') from dual;	--代表一年中的第几天
  

• 字符转日期

  select to_date('2019-6-25','yyyy-mm-dd') from dual;
  

通用函数

nav(参数1,参数2) : 如果参数1 = null 就返回参数2

nav2(参数1,参数2,参数3) : 如果参数1 = null, 就返回参数3,否则返回参数2

nullif(参数1,参数2) : 如果参数1 = 参数2,就返回null,否则返回参数1

条件表达式

• 方式一 :

  case 字段:
  	when 值1 then 值
  	when 值2 then 值
  	...
  	else
  		默认值
  	end
  

• 方式二 :

  decode(字段,if1,then1,if2,then2,...,else)
  

• 给表中姓名取一个中文名

  select 
  	case ename
  		when 'SMITH' then '刘备小儿'
  		when 'ALLEN' then '诸葛村夫'
  		else
  			'路人'
  		end "中文名"
  from emp;
  
  
  select decode(ename,'SMITH','刘备小儿','ALLEN','诸葛村夫','SCOTT','曹贼','炮灰') from emp;
  

分组表达式

select 分组条件,分组之后的操作 from 表名 group by 分组的条件 having 条件过滤

• 分组统计所有部门的平均工资,找出工资大于2000的部门

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

多表查询

笛卡尔积 : 实际上是两张表的乘积,但是在实际开发中,没有太大意义

select * from 表1,表2

内连接

• 隐式内连接

  • 等值内连接 ： select * from emp e, dept d where e.deptno = d.deptno;
  • 不等值内连接 ： select * from emp e, dept d where e.deptno <> d.deptno;
  • 自连接 ： select * from emp e, emp m where e.mgr = m.empno;

• 显示内连接

  select * from 表1 inner join 表2 on 连接条件   inner 关键字可以省略
  select * from emp e inner join dept d on e.deptno = d.deptno;
  

• 查询员工姓名,部门名称,经理姓名

  select e.ename,d.dname,m.ename 
  from emp e,emp m,dept d 
  where e.mgr = m.empno 
  and e.deptno = d.deptno;
  

• 查询员工姓名,部门名称,经理姓名,经理的部门名称

  select e.emame,d1.dname,m.ename,d2.dname 
  from emp e,emp m,dept d1,dept d2 
  where e.mgr = m.empno 
  and e.deptno = d1.deptno 
  and m.deptno = d2.deptno;
  

• 查询员工姓名,员工工资等级,部门名称,经理姓名,经理工资等级,经理的部门名称,等级显示中文

  select e.ename,				
  	   case s1.grade
  			when 1 then '一级'
  			when 2 then '二级'
  			when 3 then '三级'
  			when 4 then '四级'
  			else
  				'五级'
  			end '等级',
  	   d1.dname,
  	   m.ename,
  	   decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级','五级') 等级,
  	   d2.dname 
  from emp e,emp m,dept d1,dept d2, salgrade s1,salgrade s2
  where e.mgr = m.empno 
  and e.deptno = d1.deptno 
  and m.deptno = d2.deptno 
  and e.sal between s1.losal and s1.hisal 
  and m.sal between s2.losal and s2.hisal;
  

外连接

• 左外连接 : left outer join 左表中所有的记录,如果右表中没有对应记录,就显示空

  select * from emp e left outer join dept d on e.deptno = d.deptno;
  select * from emp e,dept d where e.deptno = d.deptno(+);
  

• 右外连接 : right outer join 右表中所有记录,如果左表中没有对应的记录,就显示空

    select * from emp e right outer join dept d on e.deptno = d.deptno;
    select * from emp e,dept d where e.deptno(+) = d.deptno;
  

Oracle中的外连接 (+):如果没有对应的记录就加空值,例子中的第二条语句

子查询

查询语句中嵌套查询语句

select * from emp where sal = (select max(sal) from emp);

单行子查询

• 查询出比雇员7654的工资高,同时和7788从事相同工作的员工信息

  --1.查询员工7654的工资
  select sal from emp where empno = 7654;
  
  --2.查询7788从事的工作
  select job from emp where empno = 7788;
  
  --3.两个条件进行合并
  select * from emp where sal > (select sal from emp where empno = 7654) 
  and job = (select job from emp where empno = 7788);
  

• 查询每个部门最低工资的员工信息和他所在的部门信息

  --1.查询每个部门的最低工资,分组统计
  select deptno,min(sal) minsal from emp group by deptno;
  
  --2.员工工资等于他所在部门的最低工资
  select * from emp e,(select deptno,min(sal) minsal from emp group by deptno) t 
  where e.deptno = t.deptno and e.sal = t.minsal;
  
  --3.查询部门相关信息
  select * from emp e,(select deptno,min(sal) minsal from emp group by deptno) t,dept d
  where e.deptno = t.deptno and e.sal = t.minsal and e.deptno = d.deptno;
  

多行子查询

• n
• not in
• any
• all
• exists

通常情况下, 数据库中不要出现null 最好的做法加上Not null
null值并不代表不占空间, char(100) null 100个字符

• 查询领导信息

  --1.查询所有经理的编号
  select mgr from emp;
  
  --2.查询结果
  select * from emp where empno in (select mgr from emp);
  

• 查询不是领导的信息

  select * from emp where empno not in (select mgr from emp where mgr is not null);
  

• 查询出比10号部门任意一个员工工资高的员工的信息

  select * from emp where sal >any(select sal from emp where deptno = 10);
  

• 查询出比20号部门所有员工工资高的员工信息

  select * from emp where sal >all(select sal from emp where deptno = 20);
  

exists(查询语句)

数据量大的时候非常高效

• 查询有员工的部门信息

  select * from dept d where exists(select * from emp e where e.deptno = d.deptno);
  

rownum

伪列,系统自动生成的一列,用来表示行号

• 查找员工表中工资最高的前三名(降序排列)

  select * from (select * from emp order by sal desc) where rownum < 4;
  

• 分页查询

  • 查询第6 - 第10条记录

    select * from (select rownum num,emp.* from emp) t where t.num between 6 and 10;
    

rowid

伪列 每行记录所存放的真实物理地址

• 去除表中重复记录,将多余的记录用delete删除

  create table p(
     name varchar2(10)
  );
  
  --重复执行几次
  insert into p values('fan');
  insert into p values('wu');
  insert into p values('gao');
  insert into p values('yang');
  
  delete from p p1 where rowid > (select min(rowid) from p p2 where p1.name = p2.name);
  

• 查询员工工资大于本部门平均工资的员工信息

  --1.分组统计各部门的平均工资
  select deptno,avg(sal) from emp group by deptno;
  
  --2.员工工资大于本部门的平均工资
  select * from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) t 
  where e.deptno = t.deptno and e.sal >t.avgsal; 
  

• 统计所有年份的入职人数

  --1.只显示年
  select to_char(hiredate,'yyyy') from emp;
  
  --2.分组统计
  select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy');
  
  --3.显示结果
  select sum(cc) "TOTAL",
  	   sum(case yy when '1980' then cc end) "1980",
  	   sum(case yy when '1981' then cc end) "1981",
  	   sum(case yy when '1982' then cc end) "1982",
  	   sum(case yy when '1987' then cc end) "1987"
  from (select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')); 
  

集合运算

并集运算

• union : 去除重复的

  select * from emp where sal > 1500 
  union 
  select * from emp where deptno = 20;
  

• union all : 没有去除重复的

  select * from emp where sal > 1500
  union all 
  select * from emp where deptno = 20;
  

交集运算

• intersect

  select * from emp where sal > 1500
  intersect 
  select * from emp where deptno = 20;
  

差集 运算

• minus

  • 1981年入职员工(不包括总裁和经理)

    --1.查询1981年的职员
    select * from emp where to_char(hiredate,'yyyy') = '1981';
    
    --2.查询总裁和经理
    select * from emp where job = 'PRESIDENT' or job = 'MANAGER';
    
    --3.差集运算
    select * from emp where to_char(hiredate,'yyyy') = '1981'
    minus
    select * from emp where job = 'PRESIDENT' or job = 'MANAGER';
    

集合运算注意事项

集合运算中的注意事项:

1. 列的类型要一致
2. 按照顺序写
3. 列的数量要一致,如果不足,用空值填充

DDL管理表

创建表空间

create	tablespace 表空间名
datafile '文件地址'
size 文件大小
autoextend on
next 自动扩展大小;

例:

create tablespace wu
datafile 'C:\'
size 100m
autoextend on next 10m;

删除表空间

drop tablespace wu;

创建用户

create user wu
identified	by wu
default tablespace wu;

授予权限

grant connect to wu;

创建表

create table 表名(
	列名 列的类型 {列的约束},
	列名 列的类型 {列的约束}
);

列的类型:
	varchar(长度)  			在Oracle中目前支持,但不保证以后支持
	varchar2(长度)  			可变字符串
	char(长度)				固定字符串
	date 					日期类型
	number(总长度,小数长度)	数字类型
	timestamp				时间戳,比date类型准确

	Long/CLOG				长字符类型,可达2G,存放小说
	BLOG					二进制数据,可达4G,存放电影

例:

create table test1(
	name1 varchar2(10),
	name2 char(10),
	name3 number(3,2),
	name4 date,
	name5 timestamp
);

用子查询的方式创建表

create table 表名 as 查询语句;	--复制表,只会复制表结构和表中的数据,不会复制表中的约束

修改表

--创建表
create table stu(
    stuid number,
    sname varchar(10)
);


--添加一列
alter table stu add phone varchar(11);

--添加多列
alter table stu add(
    mobile varchar(11),
    sex varchar2(2)
);

--修改列
alter table stu modify sex varchar(4);           --修改列的类型
alter table stu rename column sex to gender;     --修改列名   

--删除列
alter table stu drop column phone;

--修改表名
rename stu to student;

--查询数据
select * from stu;

删除表

drop table student;

表单约束

列的约束 :

1. 主键约束 : primary key
2. 非空约束 : not null
3. 唯一约束 : unique
4. 检查约束 : check(条件) 在mysql是可以写的,但是mysql直接忽略了检查约束
5. 外键约束 : foreign key(外键字段名) references 主表(主键) [on delete cascade] 代表级联删除

级联删除 : 首先去从表中找有没有关联数据,如果从表中有关联数据,先删除从表中的关联数据,在删除主表中数据

例:

create table category(
	cid varchar(10) primary key,
	cname varchar(20) unique
);

create table product(
	pid varchar(10) primary key,
	pname varchar(20) not null,
	cno varchar(10)
);

	--外键约束,级联删除
alter table product add foreign key(cno) references category(cid) on delete cascade;   

insert into category values('1','电脑');
insert into category values('2','手机');

select * from category;

insert into product values('1','MacBook','1');
insert into product values('2','iPhone','2');

select * from product;

	--级联删除 : 首先去从表中找有没有关联数据,如果从表中有关联数据,先删除从表中的关联数据,在删除主表中数据
delete from category where cid = 2;

DML管理表数据

插入数据

insert into 表名 values(所有列对应的值);

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

使用子查询插入数据

insert into 表名 查询语句

更新数据

update 表名 set 列名 = 列的值 where 条件;

删除数据

delete from 表名 where 条件;

delete和truncate的区别:

delete				truncate
 DML				   DDL
 逐条删除			   先删除表在创建表
 支持事务操作			   不支持事务操作

事务

一系列的的操作,要么都成功,要么都失败

四大特性 :

1. 原子性
2. 隔离性
3. 持久性
4. 一致性

隔离级别 :

mysql : READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIAZABLE
oracle : READ COMMITTED,SERIALIAZABLE,READ ONLY

保存点 : savepoint 保存点名称
回滚点 : rollback to 保存点名称

视图

对查询结果的一个封装,视图里边所有的数据都来自于它查询的那张表,视图本身不存储任何数据

1. 能够封装复杂的查询结果
2. 屏蔽表中的细节

语法 :

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

注意:通常不用视图修改数据,视图创建的时候,通常会加上with read only

同义词

create synonym 同义词名 for 一个对象

序列

ID自增长

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

索引

相当于一本书的目录,能够提高查询效率。在数据量比较大的情况下,如果某一列经常被查询,那么就有必要创建索引

create index 索引的名称 on 表名(列)

注意 : 主键约束自带主键索引,唯一约束自带唯一索引

索引的原理 : btree balancetree 平衡二叉树

如果某列作为查询条件的时候,可以提高查询效率,但是修改得时候,会变慢;索引在创建一段时间后,都会去做索引的重构

SQL优化 :

1. 查看执行计划
2. 分析里面的cost和影响行数,想办法降低

PLSQL编程

Procedure Language

declare

	--声明变量
	变量名 变量类型 := 初始值;

begin

	--业务逻辑

end;

例 :

declare 

	i varchar2(10) := '张三';

begin

	dbms_output.put_line(i);

end;

条件判断 :

if 条件 then 语句
elsif 条件 then 语句

else 语句
end if;

循环 :

while 条件 loop

end loop;



for 变量 in 起始值..结束值 loop

end loop;


loop 

	exit when 条件
end loop;

例 : 打印菱形

declare
	m number := 3;
begin
	dbms_output.new_line();
    for y in -m..m loop
        for x in -m..m loop
            if abs(x) + abs(y) <= m then
                dbms_output.put('*');
            else
                dbms_output.put(' ');
            end if;
        end loop;
        dbms_output.new_line();
    end loop;
end;

游标

用来操作查询结果集

cursor 游标名 is 查询结果集

open 游标名

fetch 游标名 into 变量

close 游标名

开发步骤 :

1. 声明游标
2. 打开游标
3. 从游标中取数据
4. 关闭游标

例 :

• 输出指定部门下的员工姓名和工资

  declare
      cursor vrows(dno number) is select * from emp where deptno = dno;
      vrow emp%rowtype;
  begin 
      open vrows(20);
      loop 
          fetch vrows into vrow; 
          exit when vrows%notfound;
          dbms_output.put_line('姓名:'||vrow.ename||'工资:'||vrow.sal);
      end loop;
      close vrows;
  end;
  

• 系统应用游标

  declare 
      vrows sys_refcursor;
      vrow emp%rowtype;
  begin
      open vrows for select * from emp;
      loop
          fetch vrows into vrow;
          exit when vrows%notfound;
          dbms_output.put_line('姓名:'||vrow.ename||'工资:'||vrow.sal);
      end loop;
      close vrows;
  end; 
  

• for循环遍历游标

  declare 
      cursor vrows is select * from emp;
  begin
      for vrow in vrows loop
          dbms_output.put_line('姓名: '||vrow.ename||'  工资: '||vrow.sal);
      end loop;
  end;
  

• 根据不同的职位涨工资

  declare
      cursor vrows is select * from emp;
      vrow emp%rowtype;
  begin
      open vrows;
      loop
          fetch vrows into vrow;
          exit when vrows%notfound;
          if vrow.job = 'PRESIDENT' then 
              update emp set sal = sal + 1000 where empno = vrow.empno;
          elsif vrow.job = 'MANAGER' then
              update emp set sal = sal + 600 where empno = vrow.empno;
          else 
              update emp set sal = sal + 300 where empno = vrow.empno;
          end if;
      end loop;
      close vrows;
      commit;
  end;
  

例外(意外)

程序运行的过程中发生异常

exception

	when 异常1 then

	...
	
	when 异常2 then

	...

	when others then

	...

常见异常 :

1. zero_divide : 除零异常
2. value_error : 类型转换异常
3. too_many_rows : 查询多行记录,但是赋值给了rowtype记录一行数据变量
4. no_data_found : 没有找到数据

自定义异常 :

异常名 exception;	--声明

raise 异常名;		--抛出

存储过程

封装在服务器上已经编译好的的PLSQL代码片段；客户端调用存储过程,执行效率会非常高效

create [or replace] procedure 存储过程名称 (参数名 in|out 参数类型，...) is|as

	--声明变量

begin
	
	--业务逻辑

end；

• 给指定员工涨工资,并打印涨薪前后的工资

  create or replace procedure proc_updatesal(vempno in number,vnum in number) is 
      vsal number;
  begin
      select sal into vsal from emp where empno = vempno;
      dbms_output.put_line('涨薪前: '||vsal);
      update emp set sal = vsal + vnum where empno = vempno;
      dbms_output.put_line('涨薪后: '|| (vsal + vnum));
      commit;
  end;
  
  --调用1
  call proc_updatesal(7369,10);
  
  --调用2
  declare
  begin 
      proc_updatesal(7369,20);
  end;
  

存储函数

封装在Oracle服务器中的一段PLSQL代码片段，是已经编译好的

create [or replace] function 存储函数名称(参数名 in|out) return 参数类型 is|as

	

begin

	

end;

• 查询指定员工的年薪

  create or replace function func_getsal(vempno in number) return number is 
  	vtotalsal number;
  begin
      select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
      return vtotalsal;
  end;
  
  --调用
  declare 
      vsal number;
  begin
      vsal := func_getsal(7788);
      dbms_output.put_line(vsal);
  end;
  
  select ename,func_getsal(empno) from emp;
  

存储函数和存储过程的区别 :

1. 本质上没有区别
2. 函数存在的意义是给过程调用的(存储过程里边调用存储函数)
3. 函数可以直接在SQL语句中调用
4. 存储过程能实现的过程存储函数可以实现,存储函数能实现的存储过程也能实现

触发器

当用户执行了 insert | update | delete 这些操作后,可以触发一系列其他的动作(业务)

create	[or replace] trigger 触发器的名称 
before | after
insert | update | delete
on 表名
[for each row]
declare

begin

end;

语句级触发器 : 不管影响多少行,走只会触发一次

行级触发器 : 影响多少行,就触发多少次

作用 : 在动作执行之前或之后,触发业务处理逻辑

• 新员工入职后,输出一句话

  create or replace trigger tri_test1
  after 
  insert
  on emp
  declare
  	--
  begin
      dbms_output.put_line('欢迎加入我们!!!');
  end;
  
  insert into emp(empno,ename,sal) values('7900','ZOU',3200);
  

• 更新所有工资,输出一句话

  create or replace trigger tri_test2
  after
  update
  on emp
  for each row
  declare
  	--
  begin
      dbms_output.put_line('更新了数据');
  end;
  
  update emp set sal = sal + 10;
  

• 模拟mysql中ID自动增长 auto_increment

  create table person(
      pid number primary key,
      pname varchar2(10)
  );
  --创建序列
  create sequence seq_person_pid;
  --创建触发器
  create or replace trigger tri_add_person_pid 
  before
  insert
  on person
  for each row
  declare
  
  begin 
      dbms_output.put_line(:new.pname);
      select seq_person_pid.nextval into :new.pid from dual;
  end;
  --插入数据
  insert into person values(null,'张三');
  insert into person values(null,'李四');
  insert into person values(null,'王五');