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;
9万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
