Oracle数据库

de--dede-de

已于 2022-11-01 17:45:28 修改

阅读量245

点赞数

分类专栏： Oracle pl sql 文章标签： oracle

于 2022-10-28 22:19:48 首次发布

本文链接：https://blog.csdn.net/m0_66615798/article/details/127576958

版权

Oracle pl sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Oracle

第三章数据表对象
第四章 SQL应用基础
第五章视图与序列
- 建立视图
- 维护视图
第六章 PL/SQL基础
第七章PL/SQL进阶
- 游标

第三章数据表对象

表

创建表

create table 表名{
字段名 类型(长度)，
……其他字段……
}

从其他表拷贝结构，子查询创建表格的时候，只复制表格的结构，而缺省，约束并不复制。

create table 表名 as select 字段列表 from 已有表 where 表中的内容=什么东西

修改表的结构

--修改表名
rename 原表名 to 新表名
--修改列名
alter table 表名 rename column 列名 to 新列名
--修改字段类型
alter table 表名 modify(字段，类型)
--添加列
alter table 表名 add 字段 类型
--删除列
alter table 表名 drop column 字段
--删除表
drop table 表名
-- 增加注释
comment on table 表名 注释

索引

创建普通索引，主键列会自动建索引，这也就是为什么按主键找表比较快

create index suoyin on biao(userid)
建了索引之后查询的内部流程不一样，没有建立的时候先扫描，建立了索引就列查询，当表很多的时候会提高运行速度(100万条以上的数据)

唯一索引

-- 在部门表部门id列上创建唯一索引（约束），创建唯一和主键约束的时候会自动创建唯一索引
create unique index idx)deptno on department(deptno);

复合索引

-- 当两列或多列出现频率多的时候，可以创建复合索引，查询的时候如果查这两列，就会引用索引
-- 在部门表的部门id和部门名两列上创建复合索引
create index idx_deptno_deptname on department(deptno,deptname);

维护索引

--当频繁的进行insert和delete的时候就要进行索引重建
alter index 索引名 rebuild;
--如果某个索引使用小，可以删除
drop index 索引名;

约束

创建约束

约束	作用
主键约束	具有唯一性，且不能为空
唯一性约束	不允许有重复值
非空约束	不允许为空
外键约束	维护从表与主表之间的引用性完整
check约束	自定义约束

编号	字段名	字段类型	说明
1	userid	number(5)	用户id,主键
2	username	varchar2(30)	用户名，非空，4到20个字符
3	userpwd	vachar2(20)	密码，非空，4-18个字符
4	age	number(3)	年龄，默认18，值大于等于18
5	gender	char(3)	性别，默认男，只能是男女
6	email	varchar2(30)	邮箱，唯一
7	regtime	date	注册日期，默认当前日期

--创建表代码
create table biao (
userid number(5) primary key,
username varchar(30) not null check(length(username)between 4 and 20),
userpwd varchar(20) not null check(length(userpwd) between 4 and 18),
age number(3) default(18) check(age>=18),
gender char(3) default('男') check(gender in('男','女')),
email varchar(30) unique,
regtime date default(sysdate)）

--自定义约束名字
create table biao (
userid number(5), --primary key,  直接追加约束但是名字随机
username varchar2(30) constraint nn_username not null constraint check_username check(length(username)between 4 and 20),
userpwd varchar2(20), --not null check(length(userpwd) between 4 and 18),
age number(3) default(18), --check(age>=18),
gender char(3) default('男'), --check(gender in('男','女')),
email varchar(30), --unique,
regtime date default(sysdate),
constraint pk_username primary key(userid)--给字段新加自定义名字的约束)

--追加约束
alter table 表名 add constraint 约束名 约束类型
eg: alter table biao add constraint chek_gender check(length(gender) between 4 and 20);

维护约束

--删除部门表（department）的主键约束
alter table employee drop
primary key 
--这个只能是你自己或者系统定义约束名字，因为主键约束只有一个所以不用指定
cascade;

--修改约束名
alter table 表名 rename constraint 旧约束名 to 新约束名

--禁止约束
alter table employee disable constraint primary key ;

--激活约束
alter table employee enable constraint primary key;

第四章 SQL应用基础

数据查询

基本查询语句，去重语句

select distinct 查询字段1 别名，查询字段2 别名 from 数据来源
select distinct 查询字段1，查询字段2 as 别名1，别名2 from 数据来源
--升序排列
select distinct 查询字段1，查询字段2 as 别名1，别名2 from 数据来源 oder by 数据名字 asc
--降序排列
select distinct 查询字段1，查询字段2 as 别名1，别名2 from 数据来源 oder by 数据名字 desc
--解析顺序
from -> select -> order by

--查询的时候现实工资而不是sal
select ename,sal 工资 from emp oder by 工资 desc

条件查询

select sal from emp where emp.empno=7499;
去掉 where 后面的语句就为列查询

联合查询
①有相同的列数
②两个查询采用相同的顺序
③两个查询对应列的数据类型相同

--从雇员表，部门表中查询雇员名和部门名，合并到一个集合显示
select ename from emp union select dname from dept

模糊查询

用 % 代替任意字符
用 _ 代替任意一个字符
select ename from emp where emp.ename like 'S%';
select ename from emp where emp.ename like '%S';
select ename from emp where emp.ename like '%S%';
select ename from emp where emp.ename like 'S_aiwk';
select ename from emp where emp.ename like '_Slikqs';

--模糊查询特殊情况
--查询员工名称中包含 % 员工信息
select * from emp where ename like '%a%%' escape('a'); 
--escape 表示转义字符，把a后面跟的%转为正常字符，无特殊意义，a仅仅转义他后面的一位

查询null值

-- 查询emp表中mgr是空和comm不是空的信息
select *from emp where mgr is null and comm is not null

多表查询

隐式内连接查询

--用emp表和deptno表中完全相同的东西将两个表连接起来查询
select e.ename,e.job,e.sal,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno and d.dname='RESEARCH';

外连接查询

--两张表做内查询的时候要查询其中一张表的全部数据（不会因为另一张表的关联而被筛选掉）
--在两张表关联的时候在另一张表的关联字段后面加上(+)就可以做外连接查询
--查询emp表中部门号等于dept表中部门号的数据，期中dept表中的部门数据全部显示(40也显示出出来)

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

--左连接 ： 在做外连接查询的时候，全量表在左边
--右连接 ： 在做外连接查询的时候，全量表在左边
--


--1. 左外连接LEFT JOIN是以左表的记录为基础的,示例中t_A可以看成左表,t_B可以看成右表,它的结果集是
t_A表中的全部数据，再加上t_A表和t_B表匹配后的数据。换句话说,左表(t_A)的记录将会全部表示出来,
而右表(t_B)只会显示符合搜索条件的记录。t_B表记录不足的地方均为NULL。

select * from t_A a left join t_B b on a.id = b.id;

--2. 右外连接 和LEFT JOIN的结果刚好相反,是以右表(t_B)为基础的。它的结果集是t_B表所有记录，再加上
t_A和t_B匹配后的数据。 t_A表记录不足的地方均为NULL。

select * from t_A a right join t_B b on a.id = b.id;

--3. 全外连接 左表和右表都不做限制，所有的记录都显示，两表不足的地方均为NULL。 全外连接不支持（+）
写法。

select * from t_A a full join t_B b on a.id = b.id;

子查询

单行子查询

--查询员工中工资高于allen的员工姓名
select ename from emp where sal > (select sal from emp where emp.ename='ALLEN')

多行子查询

操作	含义
ALL	比较子查询返回的全部值
ANY	比较子查询返回的每一个值
IN	等于列表中的任何成员

ALL 和 ANY 不能单独使用，要和单行比较运算符一起使用

--找到与部门号为20的工作岗位相同工作岗位的信息

select * from emp where job in(select distinct job from emp where emp.deptno=20)

若把 in 改为 any 就只查询任意一个

select * from emp where sal< any(select sal from emp where deptno=30)
--  <ANY 表示小于最大值 >ANY表示大于最小值 =ANY等同于in <ALL表示小于最小值 >ALL 表示大于最大值

系统函数

数值型函数

--abs() --绝对值
--ceil() --向上取整
--floor() --向下取整
--mod(a,b) --返回a%b
--round(a,b) --省略b默认四舍五入的整数位，如果为负数，四舍五入到小数点前第b位，正数为四舍五入小数点后第b位
--select trunc(20.9) from dual 把小数点后面的数全部切掉

字符串类型

concat(a,b)--a,b连接起来
insert(a,b,c,d)--返回从c开始d结束b在a中首次出现的位置
lower(a)--返回a的小写
upper(a)--返回a的大写
lpad(a,b,c)--在a左边填充c到总字符串长度为b
rpad(a,b,c)--在a右边填充c到总字符串长度为b
ltrim(a,b)--删除a左边的b字符
rtrim(a,b)--删除a右边的b字符
trim([leading|trailing|both][trimachar]string)--leading 表示删前面 trailing表示删后缀，both都
删，trimchar为可选项，指明删什么字符，默认为空格，string为待处理字符串
--substr(a,b,c)--截取a中位置b，c个长度的字符
--replace(a,b,c)--替换a中的b为c

日期函数

sysdate --当前系统日期
select sysdate+11 from dual --获取当前日期加11天后的日期
select hiredate,add_months(hiredate,3) from emp; --获取hiredate后三个月的日期，加正负都可以
last_day() --返回当前天所在月份的最后一天
months_between(a,b) 返回ab之前差了多少个月，若天数有差异则返回小数

转换类型函数

--to_char(a,'yyyy-mm-dd')
'yyyy-mm-dd hh:mm:ss'  为年月日时分秒的格式
--将员工表的日期转变为想要的格式
select to_char(hiredate,'mm "月" dd "日" yyyy"年"') from emp

--to_date('yyyy-mm-dd',a);

select to_date('1991/5/7','yyyy-mm-dd')+10 from dual

--to_number(string,几进制，默认为10)
将字符串转换为十进制，可选择最终转换的进制类型

数据分组

所有函数不涉及null运算
count([distinct|all],列名) 统计个数
-- distinct 对统计的值进行筛选
-- all 对所有的值进行统计
AVG([distinct|all],列)  平均值
SUM([distinct|all],列)  和
MAX([distinct|all],列)  最大值
MIN([distinct|all],列)  最小值 


-- select……from……group by……
--oracle库中：group by后面必须加上
--你select后面所查询的所有除聚合函数之外的所有字段。
-- 每个部门的平均工资,只能获得共性
select deptno,avg(sal) from emp group by(deptno)


-- having 过滤组信息
-- 查看平均工资大于2000的部门编号和平均工资
select job,round(avg(sal)) from emp group by(job) having round(avg(sal))>=2000

数据操作

插入数据

insert into 表名(列名，可省略) values (值)
--有缺省值 default 的时候直接使用 default 就可以
insert into department values (11,'aaa',default)

更新数据

update 表名 set 列名 = value where 列名 = 固定值

删除数据

--删除部门表为15的部门
delete from department where deptno = 15;

第五章视图与序列

建立视图

简单视图

--视图的作用为隐蔽固定信息，比如将账户与账号关联在一起而不现实密码，视图
就去添加除了密码之外的信息
create view vw_emp as select empno,ename,sal from emp;
--视图可以用关于表的所有数据操作

复杂视图

-- 创建复杂视图的时候必须创建别名
create view vw_emp(a,b,c) as select deptno,count(*),avg(sal) 
from emp group by deptno;
-- 其中 deptno count(*),avg(sal) 与abc 一一对应

连接视图

-- 连接视图，基于多表查询
create view vw_emdept as select sal,emp.deptno from emp,dept 
where dept.deptno = emp.deptno;

只读视图

create view vw_emdept as select sal,emp.deptno from emp,dept 
where dept.deptno = emp.deptno with read only

添加check约束

-- 在进行添加操作的时候不能添加小于 1500 的人
create view vw_emp as select sal from emp where emp.sal>1500 with check
option constraint ck_sal;

维护视图

修改视图

--试图修改相当于删掉当前视图然后重新创建
create or replace view vw_emp as select sal from emp;

删除视图

drop view view_name;

第六章 PL/SQL基础

集合类型

一维表
将plsql 当作编译器来用

--命名规则
type 表名 is table of 数据类型 [not null] index by 下表数据类型
下表数据类型可以是binary_integer , pls_integer  或者 varchar2

--用数字来当下表
declare
type dname is table of 
dept.dname%type not null index bybinary_integer;
-- 定义变量
dd dname;
begin
  select dname into dd(1) from dept where deptno=20;
  select dname into dd(2) from dept where deptno=40;
  dbms_output.put_line(dd(1));
  dbms_output.put_line(dd(2));
  dd(1):='翟翔杰';
  dd(2):='邓春霖';
  dbms_output.put_line(dd(1));
  dbms_output.put_line(dd(2));
  end;
--用字符来当下表
declare
   type dname is table of number not null index by varchar2(100);
   dd dname;
 begin
   dd('JAPN'):=1;
   dd('AA'):=2;
   dbms_output.put_line('AA');
end;

PL/SQL多维表

--
-- 把表储存起来
declare
type dname is table of dept%rowtype not null index by binary_integer;
dd dname;
begin
  select *into dd(1) from dept where dept.deptno=10;
  dbms_output.put_line(dd(1).deptno);
  dbms_output.put_line(dd(1).dname);
 end;

嵌套表

--必须有初值
declare
type dname is table of dept.dname%type;
dd dname;
begin
  dd:=dname('aa','bb','cc');
  dd(1):='qq';
  dbms_output.put_line(dd(1));
  end;
--select dname from dept where deptno=30;

可变数组

集合方法

方法名	功能说明
exists(n)	元素n是否存在
count	返回总个数
first，last	返回第一，最后，一个
limit	返回varray数组的size
prior(n) ，next(n)	返回第n个元素前一个，后一个。若不存在返回null
delete(m,n)	删除集合m~n之间的元素，若省略m，删除第n个元素若mn都省略，则全删掉，只适用于PL/SQL和嵌套表

运算符与表达式

运算符

<>  != ~= ^= 都是不等于

表达式

boolean 布尔值，只有true false null 三个值
--可转换的复制类型
--char转换number
v_count:=to_number('20');
--number转换char
v_count:=to_char('3000.79')||元
--字符串转日期
v_date:=to_date(hiredate,'yyyy=mm=dd');
--日期到字符串
v_now:=to_CHAR(sysdate,'yyyy.mm.dd hh24:mi:ss');

流程控制语句

if-then 语句
if-then-else 语句

伪代码
if     then

elsif     then 

elsif     then

else    
end if;

case 语句

伪代码
case (    )
    when ( )  then (  )
    when ( )  then (  )
    when ( )  then (  )
    when ( )  then (  )
    else (   )
end case;

循环语句

-- 1. 简单循环
-------------------------------
--循环语句
loop
    pl/sql语句
    exit when 条件
end loop;

--eg:

declare
v_count number(2):=0;
begin
  loop
    v_count:=v_count+1;
    dbms_output.put_line(v_count);
    exit when v_count>10;
    end loop;
end;
-----------------------------------------
2.while 循环
while 条件 loop
     pl/sql语句
end loop;
------------------------------------------
3. for 循环
declare
v_count number(20):=0;
begin
  for i in 1..20 loop
    v_count:=v_count+1;
    for j in 1..5 loop
      v_count:=v_count+1;
      end loop;
    dbms_output.put_line(v_count);
    end loop;
end;

第七章PL/SQL进阶

游标

游标接受数据

declare
--创建游标
cursor dept_cursor is
select deptno,dname from dept;
-- 创建变量类型
v_deptno dept.deptno%type;
v_dname dept.dname%type;
begin
  open dept_cursor;
  loop
  --查找游标值放入变量中
    fetch dept_cursor into v_deptno,v_dname;
    --当游标没值的时候退出循环
    -- %rowcount = 值  表示当游标行数值等于固定值的时候退出循环
    -- %found 存在数值的时候返回true否则返回false
    -- %isopen 检查游标是否打开
    exit when dept_cursor%notfound;
    dbms_output.put_line(v_deptno||v_dname);
    end loop;
    close dept_cursor;
    end;

游标接受记录变量

-- 相应的改一改格式
declare
cursor dept_cursor is
select * from dept;
v_d dept%rowtype;
begin
  open dept_cursor;
  loop
    fetch dept_cursor into v_d;
    exit when dept_cursor%notfound or dept_cursor%rowcount>3;
    dbms_output.put_line(v_d.deptno||v_d.loc||v_d.dname);
    end loop;
    close dept_cursor;
    end;

游标接收集合类型

declare
cursor dept_cursor is
select * from dept;
type v_v is table of dept_cursor%rowtype index by binary_integer;
v_d v_v;
vd number(2):=0;
begin
  open dept_cursor;
  loop
    vd:=vd+1;
    fetch dept_cursor into v_d(vd);
    exit when dept_cursor%notfound ;
    --dbms_output.put_line(v_d.deptno||v_d.loc||v_d.dname);
    end loop;
    for i in 1..4 loop
      dbms_output.put_line(v_d(i).deptno||' '||v_d(i).dname||' '||v_d(i).loc);
      end loop;
    close dept_cursor;
    end;
    --select* from dept;

带参数的游标

declare
cursor dept_cursor(pp number default 20) is
select * from dept where dept.deptno<=pp;
type v_v is table of dept_cursor%rowtype index by binary_integer;
v_d v_v;
vd number(2):=0;
pp number(2);
begin
  --open dept_cursor;
  open dept_cursor(pp=>&pp1);
  loop
    vd:=vd+1;
    fetch dept_cursor into v_d(vd);
    exit when dept_cursor%notfound ;
    --dbms_output.put_line(v_d.deptno||v_d.loc||v_d.dname);
    end loop;
    for i in 1..v_d.count loop
      dbms_output.put_line(v_d(i).deptno||' '||v_d(i).dname||' '||v_d(i).loc);
      end loop;
    close dept_cursor;
    end;

游标for循环

--for循环可以自动执行游标的打开删除操作
--打印前n条部门信息
declare
cursor dept_cursor is
select * from dept ;
type v_v is table of dept_cursor%rowtype index by binary_integer;
v_d v_v;
--vd number(2):=0;
pp number(2);
begin
  for v_d in dept_cursor loop
    dbms_output.put_line(v_d.deptno||' '||v_d.loc);
    exit when dept_cursor%notfound or dept_cursor%rowcount=&n;
    end loop;
    
  end;
    --select* from dept;

游标修改和删除操作

--修改工资1500以下的员工
declare
dee emp.empno%type:=&aa;
cursor dept_cursor is
select * from emp where dee=emp.empno for update nowait ;
type v_v is table of dept_cursor%rowtype index by binary_integer;
v_d v_v;
--vd number(2):=0;
pp number(2);
begin
  for v_d in dept_cursor loop
  if v_d.sal<=1500 then
    update emp set sal = 1800 where current of dept_cursor;
    --update emp set sal = 1800 where emp.empno=dee;
    end if;
    --exit when dept_cursor%notfound;
    end loop;
    
  end;

--------------------------------------------------------------------
  -- 若设计多个表需要用of操作

  declare
dee dept.dname%type:=&aa;
cursor dept_cursor is
select * from emp e join dept d on d.deptno=e.deptno for update of e.deptno ;
type v_v is table of dept_cursor%rowtype index by binary_integer;
v_d v_v;
--vd number(2):=0;
pp number(2);
begin
  for v_d in dept_cursor loop
  if v_d.sal<1500  and lower(dee)=lower(v_d.dname) then
    dbms_output.put_line(v_d.empno);
    end if;
    --exit when dept_cursor%notfound;
    end loop;
    
  end;

游标删数据

update emp set 什么 = 新值 current of 游标名 
改为
delete from emp where current of 游标名