Oracle
文章目录
一 下载
1.安装说明
2.软件地址
二 学习
资料 |
---|
查询语句.pdf |
SQL高级查询.pdf |
数据库的规范化设计.pptx |
SQL查询和SQL函数.pptx |
PL/SQL.pptx |
三 Oracle 的数据定义
1. Oracle 的数据类型
-
sql 支持下列类别的命令:
- 数据定义语言(DDL)
- 数据操纵语言(DML)
- 事务控制语言(TCL)
- 数据控制语言(DCL)
-
DDL:数据的定义(create,drop,alter) DML:数据的操作(insert,update,delete,select) TCL:事务控制(commit,rollback,savepoint) DCL:数据控制(grant,revoke)
-
数据的定义主要是一些数据库对象(表,序列,视图,存储过程,触发器……)
- 数据类型
-
字符
char(定长的) varchar2(变长的,4000字节) long(变长的,最多可以存储 2GB 的数据)
-
数值类型
number(P,S) P:总位数 S:小数部分的位数 P和S都可以省略 int Oracle 后面会自动转为 number 进行存储
-
日期时间类型
date timestamp
-
LOB 类型
存储一些二进制数据(图像,声音,word,excel 文档之类的数据) blob clob raw longraw
- 数据类型
2.掌握 DDL 语句
-
create(创建)
create table 表名 ( 列名 数据类型 约束, 列名 数据类型 约束, 列名 数据类型 约束, 列名 数据类型 约束 ); 约束:保证数据库中的数据的完整性和一致性 1.主键约束:primary key(这一列的数据不能重复,唯一) 2.非空约束:not null(约束这一列的数据不能是 null 值) 3.默认值约束:default 值(当这一列不插入数据的时候可以会有默认值) 4.检查约束:check(检查条件)(可以限制某些数据不能进来) 5.外键约束:references 主表(主键列) 外键: 1.有主表,子表 2.主表中有主键,子表中的列引用主表中的主键列的数据 3.子表中存在有外键列 建立外键约束的语法 表建立完成以后再加约束 alter table score add constraint FK_sno foreign key(sno) references student(sno);
-
alter(修改)
-
添加字段
alter table stuInfo add(msg varchar2(100));
-
修改字段
alter table stuInfo modify(msg varchar2(50));
-
删除字段
alter table stuInfo drop(msg);
-
查看表结构
desc stuInfo; (需要在交互窗口调用)
-
-
drop(删除)
3. DML 语句
DML:数据操作语句(增加,删除,修改,查询)
增加语句:
insert into 表名(列名1,列名2,列名3...) values(值1,值2,值3...);
注意1:列名如果不写,意味着写了全部的列
注意2:值(数字,字符串,日期时间),只有数字不用加 '' ,其他的类型都加上,日期时间可以使用 to_date() 函数,或者是 'dd-mm-yy' 格式的字符串
例如:
insert into student values(3,'小红','女',18,'平塘村','1-11月-2022');
注意默认值的插入问题
修改语句:
update 表名 set 列名1=新的值,列名2=新的值,列名3=新的值... where 修改的条件;
删除语句:
delete from 表名 where 条件;
truncate table emp;(截断表,会删除表的所有数据,还不能回滚)
4. Oracle 用户管理
- Oracle 的数据存储的逻辑结构
- 表空间的管理
-
创建表空间
create tablespace 表名 datafile 'e:\svse.dbf' -- 指定表空间物理文件的存储位置 size 50m autoextend on; -- 自动扩展 create tablespace qld datafile 'd:\qld.dbf' size 10m autoextend on;
-
每当开发一系统的时候,我们都会创建一个用户,以及表空间
create user 用户名 identified by "密码" default tablespace 表空间名称;
-
该用户没有登录 Oracle 的权限 create session 的权限
grant create session to qld; -- 授予 xx 权限给用户 grant connet,resource to qld; -- 授予连接和资源的权限 revoke 权限 from 用户; -- 移除某用户的某个权限
-
5. 了解 Oracle 的函数
1. case when 函数
case when:判断
case when 条件表达式1 then 值1
when 条件表达式1 then 值1
...
else
其他值
end
case 条件表达式
when 值1 then 处理1
when 值1 then 处理1
...
else
其他处理
end
else 可以省略,when 判断时一个条件成立,将不在继续执行
-
查询 score 表,将学生成绩分级,<60 不及格,70< and >=60 及格,<80 and >=70 中等,<90 and >=80 良好,>=90 优秀
select score.*, case when s_score>=90 then '优秀' when s_score>=80 then '良好' when s_score>=70 then '中等' when s_score>=60 then '及格' else '不及格' end as scoreGrade from score;
select score.*, case floor(s_score/10) when 10 then '优秀' when 9 then '优秀' when 8 then '良好' when 7 then '中等' when 6 then '及格' else '不及格' end as scoreGrade from score;
-
统计男女的年龄大于 20 在总人数的百分比
select s_sex,sum(cese when s_age>20 then 1 else 0 end)/count(s_no) from student group by s_sex;
-
行转列——查询学生分数按行显示
select s_no, max(case when c_no='c001' then s_score else null end) as c001, max(case when c_no='c002' then s_score else null end) as c002, max(case when c_no='c003' then s_score else null end) as c003, max(case when c_no='c004' then s_score else null end) as c004 from score group by s_no;
-
行转列——pivot( Oracle 专有)
select * from score pivot(sum(s_score) for c_no in ('c001' as c001,'c002' as c002,'c003' as c003,'c004' as c004));
-
列转行——union all
select s_no,'c001' as c_no,c001 as score from sc1 where c001 is not null union all select s_no,'c002' as c_no,c002 as score from sc1 where c002 is not null union all select s_no,'c003' as c_no,c003 as score from sc1 where c003 is not null union all select s_no,'c004' as c_no,c004 as score from sc1 where c004 is not null ;
-
列转行——unpivot( Oracle 专有)
select * from sc1 unpivot(s_score for c_no in(c001,c002,c003,c004));
2. rownum 函数
四 Oracle 数据库对象
1.同义词
- 同义词:就是一个数据库对象的别名,我们通过起别名的方式来访问对象
- 分类:
- 私有:
create synonym 同义词的名称 for 数据库对象; -- 超管授权 grant create synonym to 用户;
- 公有
create public synonym 同义词的名称 for 数据库对象; -- 超管授权 grant create public synonym to 用户; grant drop public synonym to 用户;
- 数据字典:user_synonyms(视图,存储有该用户下的所有的同义词)
- 私有:
- 操作顺序(练习)
- 建立一个用户,授予连接权限
- 登录 scott,将表的权限授予该用户(grant all on emp to 用户)
- 登录该用户,尝试访问这个表
- 建议一个私有、公有同义词(注意授权)
- 分别访问一次
- 删除以上两个同义词
- 在数据字典 dba_synonyms 中记录了数据库中所有的同义词,包括每个用户创建的私有同义词和 dba 创建的公共同义词。这个视图只有 dba 能够访问,它的结构除了包含数据字典 user_synonyms 的所有列外,还有一个列 owner 代表同义词的创建者。
select synonym_name,table_name,table_owner from dba_synonyms where owner='PUBLIC';
2.序列
-
作用:用于生成唯一的 id 号
-
数据库中有些列是需要自动的递增,不同的数据库有不同的处理方式
-
mysql:自动增长列 auto_increment(1,1)
-
sqlserver:自动增长列 ide…
-
Oracle:
create sequence 序列名 start with 1 -- 初始值 increment by 1 -- 递增值 maxvalue -- 最大值 minvalue -- 最小值 nocycle -- 是否循环 cache 4 -- 缓存
- 访问序列:
- 当前值:currval
- 下一个值:nextval
- 访问序列:
-
-
创建序列
- 创建序列需要 create sequence 系统权限,序列的创建语法如下:
create sequence 序列名 [ increment by n ] [ start with n ] [ { maxvalue/minvalue n | nomaxvalue } ] [ { cycle | nocycle } ] [ { cache n | nocache } ]
其中:
(1):increment by 用于定义序列的步长,如果省略,则默认为1,如果出现负值,则代表 Oracle 序列的值是按照此步长增减的 (2):start with 定义序列的初始值(即产生的第一个值),默认为1 (3):maxvalue 定义序列生成器能产生的最大值,选项 nomaxvalue 是默认选项,代表没有最大值定义,这时对于递增 Oracle 序列,系统能够产生的最大值是10的27次方;对于递减序列,最大值是-1 (4):minvalue 定义序列生成器能产生的最小值,选项 nomaxvalue 是默认选项,代表没有最小值定义,这时对于递减序列,系统能够产生的最小值是?10的26次方;对于递增序列,最小值是1 (5):cycle 和 nocycle 表示当序列生成器的值达到限制值后是否循环,cycle 代表循环,nocycle 代表不循环。如果循环,则当递增序列达到最大值时,循环到最小值;对于递减序列达到最小值时,循环到最大值。如果不循环,达到限制值后,继续产生新值就会发生错误 (6):cache(缓冲)定义存放序列的内存块的大小,默认为20。nocache 表示不对序列进行内存缓冲。对序列进行内存缓冲,可以改善序列的性能 大量语句发生请求时,申请序列时,为了避免序列在运用层实现序列而引起的性能瓶颈。Oracle序列允许将序列提前生成 cache x个先存入内存,在发生大量申请序列语句时,可直接到运行最快的内存中去得到序列。但 cache 个数也不能设置太大,因为在数据库重启时,会清空内存信息,预存在内存中的序列会丢失,当数据库再次启动后,序列从上次内存中最大的序列号 +1 开始存入 cache x个。这种情况也能会在数据库关闭时也会导致序号不连续。 (7):nextval 返回序列中下一个有效的值,任何用户都可以引用 (8):currval 中存放序列的当前值,nextval 应在 currval 之前指定,二者应同时有效
- 创建序列需要 create sequence 系统权限,序列的创建语法如下:
-
序列缓存问题
缓存中的数据个数要小于循环的最大个数,意思就是没必要产生那么多的缓存数据。默认为20
3.视图
- 视图:一个虚拟的表,在原表(基表)上的一次查询,并且做了存储
- 创建语法:
create ( or replace ) view 视图名 as 查询语句
- 视图使用注意
- 可以在简单视图中使用 DML 操作
- 视图中定义
group by / 分组函数 / rownum 之类的语句的时候,DML 不允许
4.索引(难点)
- 解释:索引就是类似于书籍的目录,字典中的。数据库表中的数据如果量比较大时,需要将这些数据做一个目录出来
- 索引建立的目的在于:快速的查找
- 索引的分类
- 聚集索引:索引的顺序与数据的存储顺序是一致的,一个表只有一个
- 非聚集索引
- 建立一个索引
create index idx_sname on student(sname);
- 删除一个索引
drop index 索引名;
五 PL-SQL 编程与存储过程
-
PL-SQL 编程
- sql 语言的扩展:
- PL-SQL 组成:PL/SQL 分为三个部分,声明部分(定义变量)、可执行部分(核心代码)和异常处理部分(处理异常)
- sql 语言的扩展:
-
if 语句
-- 统计出 emp 的平均工资,如果 >2000,显示公司有钱,<2000,公司很穷 declare avgSal emp.sal%type; begin select avg(sal) into avgSal from emp; dbms_output.put_line(avgSal); if avgSal <=2000 then dbms_output.put_line('穷公司'); else dbms_output.put_line('公司有钱'); end if; end;
-
多重 if 语句
-- 查询出 7839 这个员工的工资, -- <=2000:穷鬼 -- <=3000:一般般 -- <=4000:还可以 declare mySal emp.sal%type; begin select sal into mySal from emp where empno=7839; if mySal <=2000 then dbms_output.put_line('穷鬼'); elsif mySal <=3000 then dbms_output.put_line('一般般'); elsif mySal <=4000 then dbms_output.put_line('还可以'); else dbms_output.put_line('土豪'); end if; end;
-
loop 循环
loop 循环语法: loop exit when 条件表达式; 执行语句; end loop;
-- 计算 1-10 相加的总和 declare i int:=1; sum1 int:=0; begin loop exit when i>10; sum1:=sum1+i; i:=i+1; end loop; dbms_output.put_line(sum1); end;
-
while 循环
while 循环语法: while 循环条件 loop 执行语句; end loop;
-- 计算 1-10 相加的总和 declare i int:=1; s int:=0; begin while i<=10 loop s:=s+i; i:=i+1; end loop; dbms_output.put_line(s); end;
-
for 循环
for 循环语法: for 变量 in 起始值..结束值 loop 执行语句; end loop;
-- 计算 1-10 相加的总和 declare i int:=1; s int:=0; begin for i in 1..10 loop s:=s+i; end loop; dbms_output.put_line(s); end;
-
异常处理
-
存储过程
将一些 sql 的代码存储下来,并且命了一个名字:存储过程需要调用create or replace procedure pro_savemoney(money int) as hasMoney int; no_money_error exception; begin -- 取款业务 select bmoney into hasMoney from bank where bid=1; if money > hasMoney then raise no_money_error; end if; -- 增加交易记录 insert into tradeinfo values(6,1,'取款',money,default); -- 修改账户信息 update bank set bmoney=bmoney-money where bid=1; -- 提交 commit; exception when no_money_error then dbms_output.put_line('余额不足11'); -- 回滚 rollback; when others then dbms_output.put_line('余额不足22' || SQLERRM); rollback; end;