2020-12-05 ORACLE期末复习

最新推荐文章于 2024-01-27 22:44:15 发布

向着秃头出发

最新推荐文章于 2024-01-27 22:44:15 发布

阅读量172

点赞数

分类专栏：数据库文章标签： oracle

本文链接：https://blog.csdn.net/qq_46544808/article/details/110527427

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

SQL（Structure Query Language）结构化查询语言

一、分类

1.1数据查询语言(DQL)
select…

1.2数据定义语言(DDL)
create,drop,truncate

1.3数据操作语言(DML)
insert into…,delete,update

1.4数据控制语言
rollback/commit(事务)

二、DDL数据定义语言

（data defination language）

用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如： CREATE TABLE /VIEW /INDEX /SYN /CLUSTER DDL操作是隐性提交的，不能rollback

2.1 数据类型

1.数字类型，可以做所有的数学运算
number
number（4）代表整数，最大能存9999
number（7,2）代表double类型，整数长度为5，小数位2

2.字符型，可以做拼接运算
char（20）定长
varchar2（20）不定长，表示字符窜最长是20
注意：字符串在数据库中用’’

3.日期类型,可以做加减运算
Date 年月日时分秒
Time 时分秒
Timestamp 年月日时分秒，还有小时位，如1.2秒

2.2 建表（create）

–建表
create table ‘表名’(
列名数据类型约束
列名数据类型 [约束]
… … …
)[字符集设置][表空间];

--user 学号，姓名，性别，生日，班号
create table student(
   studentId number(11) primary key,
   sname varchar2(11),
   sex char(1),
   birth date,
   classId number(11)
);

2.3 删除表（drop）

drop table student;

2.4 截断表(truncate)

truncate table 表名（删除表数据保留表结构）

注意：慎用truncate,删除后不可回滚，删除速度快，DELETE语句每删除一条记录都是一个事务，会产生若干"日志"。但TRUNCATE是释放整个数据页（一个页8K）

2.5 修改表结构（alter）

2.5.1 增加列

//语法：alter table 表名 add 列名 列类型；
alter table student add age number(3);

2.5.2 修改列

修改列名

--语法：alter table 表名 rename column 旧列名 to 新列名 
alter table student rename column age to age1;

修改列类型

--语法：alert table 表名 modify 列名 列类型：
alert table student modify age1 varchar2(3);

2.5.3 删除列

--语法：alter table 表名 drop column 列名
alert table student drop column age1;

2.6 约束类型

1.primary key PK ：主键约束，非空唯一(表中最多只能含有一个主键约束)

2.not null NN ：非空

3.unique UK：唯一

4.check CK 含义：自定义约束（往往用来定义业务的约束，如分数不能为负）

5.foreign key FK：外键约束（表示此列的值是引用自己的或其他表的主键）
注意：能够被外键所引用的列，其本身也必须是主键约束或唯一性约束

三、DML数据操作语言

3.1 插入(insert)

--语法：insert into 表名（列名……）values(列值……)
--注意：列名和列值都要一一对应，顺序，类型
--中文占两个字节，0表示男，1表示女
insert into student(studentid,sname,sex,birth,classid)
       values(1,'lala','0',to_date('2000-01-01','yyyy-MM-dd'),1);

--增加外键(score表的studentId列的值必须来自student的studentId的值)
alter table score add foreign key (studentId) references student(studentid);
insert into score(id,studentId,subjectName,studentscore)values(1,1,'语文',99.9);

插入常见错误
列和值个数不对；列和值得数据类型不对；主键重复；没有这个列；当子表插入主键不存在的数据时，会报错.(当存在外键)

3.2 修改(update)

--语法：update 表名 set 列名 =值1，列2=值2，……where 条件；
update student set sex='1' where sname='lala';
commit;

3.3 删除（delete）

--语法：delete 表名 where 条件 ：
delete student where studentid=1;

注意：如果子表存在记录，那么不能删除主表相关的数据

insert into score(id,studentId,subjectName,studentscore)values(1,1,'语文',99.9);
delete student from where studentid=1;--直接执行删除主表数据命令报错

--方案1：先删除子表记录，再删除主表记录

delete from score where studentid =1;
delete from student where studentid=1;

--插入数据
insert into student(studentid,sname,sex,birth,classid)values(1,'lala','0',to_date('2020-01-01','yyyy-MM-dd'),1);
insert into score(id,studentId,subjectname,studentscore)values(1,1,'语文',80);

--方案2：先把子表的记录置null，再删除主表记录
update score set studentId =null where studentid=1;
delete from student where studentId=1;

3.3.1 级联删除

--如果该主表主键作为从表的外键，删除主表数据时，会级联删除从表中主键值相关联的列
--studentid为主键，score的sstudentid为外键且有级联删除
alter table score 
  add foreign key (studentId)
  reference student(studentId)
  on delete cascade;
--on delete cascade用于指定级联删除选项。
--如果在定义外部键约束时指定了该选项，
--那么当删除主表数据时会级联删除从表的相关数据。  
  
--删除student表中的id=1的数据score表中的studentid=1的数据也会被删除  
delete from student where studentid =1;
select * from score;

3.3.2 级联置空

alter table score 
  add foreign key (studentId) 
  references student(studentId)
  on delete set null;
--on delect set null 用于指定转换相关的外部键值为NULL，
--如果在定义外部键约束时指定了该选项，
--那么当删除主表数据时会将从表外部键列的数据设置为NULL。

delete from student where studentId = 1;

四、DCL数据控制语言

DCL（data control language）数据库控制语言，如commit，revoke之类的，在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

–事务问题
commit/rollback

–授权语句（DBA）
grant

--授予scott dba的角色权限。（要用系统管理员sys登录 ）
grant dba to scott;

五、DQL数据查询语言

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

5.1 单表查询

select [表别名] 列名 [列别名]……
from 表名 [as 表别名]
where 条件
group by 分组的列
having 分组过滤
order by 排序;

(1)查询所有的员工信息

select * from emp;

(2)查询某列，如员工的姓名和工资

select e.ename,e.sal “工资” from emp e;

(3)as给列以别名显示,使用运算符:

select ename,sal*(1+0.1) (as) sal from emp;
(这里的as关键字可以省略)

(4)in(多个值中的任意一个)

–部门编号：10，20，30的员工信息
select *from emp where deptNo in (10,20,30);

(5)and,or

–查询部门号为30的员工和工资大于2000的员工姓名和工资
select ename,sal from emp where deptno =30 and sal>2000;
–查询部门号为30的员工或工资大于3000的员工姓名和工资
select ename,sal from emp where deptno =30 or sal>3000;

–查询部门30和20中工资大于1000的员工姓名和工资
–and优先级大于or
–where的核心，拿每一条记录代入where后面的表达式，如果表达式为true
–那这一条将会放入结果集中，否则不放入结果集中
select ename,sal from emp where (deptno =20 or deptno=30) and sal>1000;

(6)between…and… 在某个范围内

–工资在1000到2000之间的员工姓名和工资
select ename,sal from emp where sal between 1000 and 2000;

(7)null判断 is null;

–comm，没有奖金的员工姓名和工资
select * from emp where comm is null;

(8)通配符

–like % 表示任意个任意字符
–员工姓名包含S字母的员工姓名和工资
select ename,sal from emp where ename like ‘%S%’;
– _ 表示任意一个字符
–倒数第二个字符是T的员工姓名和工资
select ename,sal from emp where ename like ‘%T_’;

(9)–排序:order by 列名 asc(升序（默认）/desc（降序）)

select * from emp e order by e.hiredate;

(10)group by分组(分组了就不能直接返回*，经常和聚合函数count(age)一起使用):(一条记录作为一个参考)

–每个部门的人数
–列，（分组函数列），最大值，最小，平均，总记录，总和
select deptno,count(1) from emp group by deptno;
◆group by有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面。

运行结果：
在这里插入图片描述

–每个部门工资最高的工资是多少
select deptno,max(sal) from emp group by deptno order by deptno;
结果：

(11)分组过滤having

--平均工资大于1000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno 
having avg(sal)>1000;

–（此处不能用where 条件，where筛选的是数据库表里面本来就有的字段，而having筛选的字段是从前筛选的字段筛选的）
–若没有select avg(sal)则不可用having 只能用where
–having的原理是先select 然后从select出来的进行筛选。而where是先筛选在select。

(12)子查询

<1>子查询放在select后面，作为其中的一个字段返回。
select u.username,(select d.departname from t_depart d where d.departid = u.departid) from t_user u;
(返回用户和所属的部门)
<2>子查询放在from后面，作为一张临时表。
select * from (select username,sex s from t_user where departid=1) where s = '男';
<3>子查询放在where后面，作为条件的一部分。
select * from t_user where departid = (select departid from t_depart where departname = '财务部');

–(子查询)各个部门工资最高的员工姓名和工资 (多个列的用in)
select ename,sal from emp where (deptno,sal) in
(select deptno,max(sal) from emp group by deptno);

–人数最多的岗位
select job from emp group by job having count(1) =
(select max(count(1)) total from emp group by job);

(13)rownum序号

select rownum rn,e.* from emp e where deptno=30;

--求工资最高的3个人

select b.ename,b.sal from
(select rownum rn,a.* from (select e.ename,e.sal from emp e order by sal desc)a)b
where b.rn >=1 and b.rn<=3;

select * from 
(select e.ename,e.sal from emp e order by sal desc) a
where rownum >=1 and rownum <=3;

5.2 多表连接

查询字段来自多个表时，会用到连接查询

（1）内连接

–语法 select * from 表1 inner join 表2 on 关联关系
–核心就是通过关联关系把多个表变成一个表

a.等值连接（关联关系一般主外键,以两边中相同的字段作为连接条件）

--求员工姓名emp和所在的部门的名称dept
select e.ename,d.dname 
from emp e inner join dept d 
on e.deptno=d.deptno;

b.非等连接(范围筛选连接条件)

--select * from 表1 inner join 表2 on ……between ...and..
--每个员工姓名 emp 和工资等级 salgrade
select e.ename,s.grade 
from emp e inner join salgrade s 
on e.sal between s.losal and s.hisal;

(2)自连接（自己连接自己）使用自连接可以将自身表的一个镜像当作另一个表来对待
–核心：把多个表变成一个表，再去操作其他

--每个员工的名字和直属经理的名字
select e.ename 员工,m.ename 直属经理 
from emp e inner join emp m 
on e.mgr=m.empno;

--求员工姓名工资等级和所在部门的的地址
select * from salgrade;
select * from dept;

select e.ename,s.grade,d.loc 
from emp e inner join dept d 
on e.deptno=d.deptno 
inner join salgrade s 
on e.sal between s.losal and s.hisal;

--简写
select e.ename,s.grade,d.loc 
from emp e,dept d,salgrade s 
where e.deptno=d.deptno 
and e.sal between s.losal and s.hisal;

--求平均工资高的部门比平均工资低的部门的平均工资高多少
select
((select max(avg(sal))from emp group by deptno)-
(select min(avg(sal))from emp group by deptno)) 平均工资最高与最低的差 from dual;

（3）外连接

–原理
–用第一个表的第一条记录用关联关系去与第二个匹配，如果匹配上了，就会合并两条记录
–生成一条新的记录，放入结果集中；如果没有匹配上，则第一个表的记录出现在结果集中，但是其他列的值都为null

a.左连接

--左外连接(主表，从表)（左边表示主表，右边表是从表）
--select * from 表1 left join 表2 on 关联关系
select * from dept d left join emp e on e.deptno=d.deptno;
select * from dept d join emp e on e.deptno=d.deptno;

b.右连接

--右外连接（右边表示主表，左边表是从表）
--select * from 表1 right join 表2 on 关联关系；
select * from dept d right join emp e on e.deptno =d.deptno;

五、序列

序列的用法

--创建序列
create sequence mySeq2
increment by 5  --步长默认1
start with 10   --初始值默认为1
maxvalue 100;   --最大

--NextVal返回序列中下一个有效的值
select mySeq2.NextVal from dual;
--执行一次加1
--CurrVal产生序列的当前值，调用n次都不会产生序列的下一个值
select mySeq2.CurrVal from dual;

--用序列实现主键自增
insert into student2(id,classId,name,cardId,age)
values(mySeq2.nextval,10,'zt221','000022',20);

六、视图

–视图（虚表）
–视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。
–那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。

--创建视图用户需要有create view系统权限
--授予scott dba的角色权限。（要用系统管理员sys登录 ）
grant dba to scott;
--语法：create view 视图名 as 子查询
--一个表
create view v_emp as 
select empno,ename,job,mgr,deptno from emp;

--多个表
create view v_emp_dept as
select e.empno,e.ename,e.sal,e.deptno edeptno,d.* 
from emp e,dept d 
where e.deptno=d.deptno;

--删除视图
drop view v_emp_dept;

--create or replace view 视图名 as 子查询
--or replace: 如果存在同名的视图, 则使用新视图"替代"已有的视图
create or replace view v_emp_dept as
select e.empno,e.name,e.sal,e.deptno edeptno,d.* 
from emp e,dept d where e.deptno=d.deptno;

--with read only 创建的视图只能查询数据，不能操作
create or replace view v_emp as select empno,ename,job,mgr,deptno from emp with read only;
update v_emp set deptno =20 where empno=7369;

--不建议操作视图，可查询
update v_emp set deptno =30 where empno=7369;

七、索引

--单列索引
--语法：create index 索引名 on 表名（列名）
create index emp_index on emp(deptno);
select * from emp where deptno=20;

--复合索引（多个列）
--语法：create index 索引名 on 表名(列1，列2……)
create index emp_ej_index on emp(ename,job);

--大表
--经常在where后面出现的列
--经常修改的列不要建索引

八、pl/sql 基本语法

1、基本结构

DECLARE
声明部分:声明要使用的变量，记录类型，及游标等，还包括局部的存储过程和函数
BEGIN
执行部分：过程及SQL语句，及程序的主要部分
EXCEPTION
执行异常部分：对我们的错误进行处理/
END;

declare
  name varchar2(10):='zs';
  flag1 number(9,2):=12;
begin
  dbms_output.put_line(name);
  dbms_output.put_line(flag1);
end;

（1）–引用变量

--引用已经存在的表字段类型 变量名 表名.字段名%type
declare 
  myname emp.ename%type;  --emp表的ename列的类型
begin
  select ename into myname from emp where empno =7369;
  dbms_output.put_line(myname);
end;

（2）–记录型变量

--返回的是整个表的字段类型
declare
  myemp emp%rowtype;  --一条记录
begin
  select * into myemp from emp where empno=7369;
  dbms_output.put_line(myemp.ename || ' ' ||myemp.sal);
end;

2、if结构

（1）-语法1：
if 条件 then
语句……
end if;

  declare
    enum number := &num;--输入
   begin
     if enum=1 then 
       dbms_output.put_line('enum是1');
      end if;
   end;

（2）语法2：
if 条件 then
语句1……
else
……
end if;
end;

（3）语法3
if 条件 then
…
elsif 条件 then
…
else
…
end if；
end;

3、循环结构

–语法1：
while 条件 loop
…
end loop;
end;

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

–语法2
loop
exit when 条件 --退出条件
end loop

–语法3
for i in 1…10 loop
……
end loop;

4、游标cursor

    以循环取SQL语句的SELECT内容，它是存放结果集的数据对象。使用游标,我们只能逐条记录地得到查询结果。 
    作用：查询数据库，获取记录集合(结果集)的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。
    使用：用游标有四种基本的步骤:声明游标(declare)、打开游标(open)、提取数据(fetch)、关闭游标(close)。 
    ★当你要往每一行插入一个数据只能用游标，或者更新结果集中的每行记录时也可用游标,用存储过程返回一个结果集。
    ★触发器和存储过程会和数据库绑定，即一直保存在数据库中，而游标不会，它是任意时刻创建再打开再执行再关闭，
      与数据库没有任何直接关系。
    ★在Oracle中,不需要显示销毁游标.因为在Oracle中,很多东西是由JAVA写的.Oracle会自动销毁游标。

属性：
–（1）%FOUND：SQL 语句影响了一行或多行时为 TRUE；
–（2）%NOTFOUND： SQL 语句没有影响任何行时为TRUE；
–（3）%ROWCOUNT：SQL 语句影响的行数；
–（4）%ISOPEN：游标是否打开，始终为false。

--打印员工姓名
--声明游标 定义游标的名称和相关的SELECT语句：
declare
  cursor ce 
  is 
  select * from emp;
  pe emp%rowtype;--定义一个pe变量 类型是emp表中的类型
begin
  --打开游标游标分配内存，使得它准备取的SQL语句转换成它返回的行：
  open ce;
  --遍历游标
  loop--循环抓取数据(loop是其中一种循环方式)
    --将一行记录放入到变量中，可用LIMIT关键字来限制条数，如果没有默认每次抓取一条：
    fetch ce into pe;
    --如果没有结果就退出
    exit when ce%notfound;
    dbms_output.put_line('员工姓 名'||pe.ename);
  end loop;
  --关闭游标
  close ce;
end;

5、存储过程及存储函数

–存储过程

语法：
  create or replace procedure 过程名(参数名 in/out 数据类型)
  is
    变量
  begin
    内容
  end;

--编译
  create or replace procedure hello(a in number,b out number)is
  begin
    dbms_output.put_line(a);
    b:=a+10;
  end hello;
  
--sql编译的过程-->执行
declare
  c number;
begin 
  hello(10,c);
  dbms_output.put_line(c); 
end;

--入参是员工编号，给这个员工涨薪10%，并打印涨薪前和涨薪后的工资
create or replace procedure zx(
eno in myemp.empno%type,qsal out myemp.sal%type,hsal out myemp.sal%type)
is 
begin
  select sal into qsal from myemp where empno=eno;
  update myemp set sal=sal*1.1 where empno=eno;
  select sal into hsal from myemp where empno=eno;
  commit;
end zx;

-->执行
declare 
  a number:=&a;
  b number;
  c number;
begin
  zx(a,b,c);
  dbms_output.put_line(b);
  dbms_output.put_line(c);
end;

–存储函数

create or replace function 函数名（参数 in|out 类型）return 数据类型
is
  变量名  类型和return返回类型一致
begin
 return 变量名;
end;

--根据员工编号，查询员工的工资
create or replace function empSal(eno in emp.empno%type)
return number 
is
rsal emp.sal%type;
begin
  select sal into rsal from emp where empno=eno;
  return rsal;
end;

declare
  rsal number;
begin 
  rsal :=empSal(7788);
  dbms_output.put_line(rsal);
end;

注意：

--任何值与null计算结果都为null
select null+10 from dual;
--空值处理函数，nvl(值,0)(若传入值为null，处理为0)
select nvl(null,0)+10 from dual;

6、触发器

语法：

create or replace trigger 触发器名称
[before|after]
(delete|update|insert|of 列名)
on 表名
[for each row where 条件]  --行级触发器 如果要使用:new或:old必须加上
begin
  ……
end 触发器名;

实例：

--修改工资，必须大于原来的工资
--:old取原来内容 :new取新增内容
--raise_application_error（参数1，参数2）,oracle系统级异常
--参数1：错误代码 在-2001~ -20999 间
--参数2：提示错误信息
create or replace trigger updateSal
before 
update of sal 
on emp 
for each row
declare
  week varchar2(20);
begin
  if(:old.sal>:new.sal)then
    insert into result values('非法修改金额');
    raise_application_error('-20002','涨薪不能低于原来的工资');
  end if;
end updateSal;

update emp set sal =1000 where empno=7788;
commit;