1、-- Sequence 序列
-- 如果我们在表中设置了主键，序列可以让主键自动增长。
-- 在 Oracle 中，我们使用序列来实现主键增长。
-- 在 MySql 中，我们使用 auto_increment 关键字来设置即可。
-- 这个序列并不是单独属于某个表的，它是独立的，任何表都可以使用它。
-- 但一般情况下，为了避免数据混乱，我们建议单张表对应一个序列就好。
-- 创建序列的时候，名字不能重复，因为它是一个对象来的。
-- 在 Oracle 中常见的对象：表、序列、索引、同义词、数据库连接、视图等等。
-- 如果要使用序列，需要先创建，再使用。
eg:
--创建序列
create sequence seqTest;
--使用序列
insert into tb_student(id,name,sex) values(seqTest.nextval,'王八','男');
--创建一个升序序列seqCus
create sequence seqCus
increment by 1 --递增1
start with 1001 --初始值
maxvalue 9999 --最大值
nocycle
nocache
order;
2、--Synonym 同义词
-- 其实就是一个别名
使用scott用户创建同义词时可能会出现权限不足，此时要去用system用户给scott用户授权
"grant create synonym to scott;"
sql plus界面
--创建同义词
create synonym synTest
for scott.tb_student;
--使用同义词
select * from synTest;
3、--DBLink
-- D:\Oracle安装路径\product\11.1.0\db_1\NETWORK\ADMIN\可以看到对应的文件
-- 1）listener.ora 监听器
-- 2）sqlnet.ora 网络
-- 3）tnsnames.ora tns配置文件
-- 创建连接
create database link hello
connect to system identified by "1234"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.25.161.113)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Oracle)
)
)';
-- 测试连接
select * from scott.emp@hello;
4、--索引
/****************************************************
面试:如何优化你的数据库查询?
1.数据库的查询方式?
-- 全表扫描：最慢 select * from tb_emp;
-- 利用索引扫描：快，推荐使用。也是一张表，只有两列。key-value
-- 共享语句：必须建立在全表扫描的基础之上，已经查过的数据会保留在缓存中，直接去缓存中查找。
索引 index
作用: 在数据库中用来加速对表的查询。
原理: 通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
特点:
与表独立存放，但不能独立存在，必须属于某个表
由数据库自动维护，表被删除时，该表上的索引自动被删除。
索引的创建:
自动: 当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时, 数据库自动创建一个对应的索引.
手动: 用户可以创建索引以加速查询.（不能乱指定，最好是经常要查的字段）
当创建索引的时候，Oracle会默认创建一个和当前表相关的索引页,
而索引页中保存了索引字段和真实的磁盘地址，
当用户发送sql语句带了索引的时候，Oracle会到索引页中查询索引字段，
直接定位磁盘IO，提取数据。所以索引数据快于全表扫描。
索引的维护
1.建立索引后，查询的时候需要在 where 条件中带索引的字段才可以使用索引。
2.在经常查询的字段上面建立索引。不要在所有字段上面建立索引。
3.因为索引是用来加快查询速度的，如果一张表经常做insert、delete、update，
而很少做 select,不建议建立索引，因为Oracle需要对索引进行额外的维护
如果一张表字段很少，不建议建立索引。
4.索引是由Oracle自动维护的。索引使用久了会产生索引碎片（磁盘碎片），
影响查询效果，所以使用久了需要手动进行维护(删除再重建)。
********************************************************************/
--创建索引
create unique index ixTest --在tb_student表的name列创建一个索引
on tb_student(name);
--修改索引
alter index ixTest --把索引ixTest重命名为indexTest
rename to indexTest;
--使用索引
-- 在使用的时候，不用理，Oracle 会自动去调用索引进行查询
select * from tb_student where name = '张飞';
-- 删除索引
drop index indexTest;
/*************************************************
sql语句的优化：
多使用共享语句 尽量使你的sql语句能够使用索引。
怎样使sql语句能够使用到索引呢：
当sql语句中包含not in,<>,is null,is not null,like '%%'的时候不会用索引。
IN: in 会拆成一堆 or 的,可以使用表的索引。
NOT IN:强列推荐不使用，因为它不能应用表的索引。
<> 操作符（不等于）: 不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
优化方案:用其它相同功能的操作运算代替，如a<>0 改为 a>0 or a<0;a<>’’ 改为 a>’’.
IS NULL 或IS NOT NULL操作（判断字段是否为空）:
判断字段是否为空一般是不会应用索引的，
因为 B 树索引(oracle大多是使用 B 树索引)是不索引空值的。
优化方案:用其它相同功能的操作运算代替，如 a is not null 改为 a>0 或a>’’等。
is null 时，用一个缺省值代替空值，例如业务申请中状态字段不允许为空，缺省为申请。
LIKE：LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，
但是如果用得不好则会产生性能上的问题，
优化方案:如LIKE ‘%001%’ 这种查询不会引用索引，会产生全表扫描，
而LIKE ‘001%’则会引用范围索引。进行范围的查询，性能肯定大大提高。
eg: select * from aaa where comm is null; -- 不会用到 index
select * from bbb where email = 'zsfz053@126.com'; -- 会用到 index
**********************************************************/
5、--视图
使用scott用户创建视图时可能会出现权限不足，此时要去用system用户给scott用户授权
"grant create viewto scott;"
--创建视图
create or replace view vwTb_student
as
select id,name,sex
from tb_student;
--查询视图
select * from vwTb_student;
--更新视图（指通过视图进行增删改操作数据）注意：可以更新视图，但是不建议
/*********************
满足一下条件才可更新视图
1）创建视图没有包含只读性
2）没有使用连接函数、集合运算函数和组函数
3）创建视图的select语句中没有聚合函数且没有group by、onnect by、start with 子句及distinct关键字
4）创建视图的select语句中不包含从基表列通过计算所得
****************************/
insert into vwTb_student
values(7,'李二狗','女');
--删除视图
drop view vwTb_student;
6、--数据建模
/**************
软件开发过程:
1. 需求调研,与客户进行沟通
2. 需求分析，将现实工作中的动作模拟到计算机
数据建模
3. 开发
4. 测试
5. 上线部署
从关系数据库的表中删除冗余信息的过程称为规范化，
是得到高效的关系型数据库表的逻辑结构最好和最容易的方法。
获得数据规范化的方法: 三范式
步骤1:
第一范式：必须要有主键,并且每个属性值,都是不可再分的最小数据单位，
则称R是第一范式的关系。
第二范式：所有非主关键字都完全依赖于主关键字(通常用于联合主键)
第三范式：非主关键字不能依赖于其他非主关键字（通常用于一个主键）
*********************/
/*****
数据建模
1. 根据三个范式
2. 分析实体之间的关系, ER 图 Entity-Reference
一对一：一个人只有一个身份证，唯一外键关联或者主键关联。
一对多：一个班级可以有多个学生。一个学生只属于一个班级（clazz - student）
关联：一对多使用主外键关联，通常在多方（student）建立外键
多对多：一个学生可以选择多门课程，一门课程可以被多个学生选修 （student - course）
关联：多对多通常使用中间表（再多建一张表存储）关联数据
通常中间表会有两张表的id作为联合主键，并且作为外键指向关联表
***************/
7、-- PLSQL
-- PL/SQL 也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。
-- PL/SQL 是 Oracle 数据库对 SQL 语句的扩展。
-- 在普通 SQL 语句的使用上增加了编程语言的特点，
-- 所以 PL/SQL 把数据操作和查询语句组织在 PL/SQL 代码的过程性单元中，
-- 通过逻辑判断、循环等操作实现复杂的功能或者计算。
-- PL/SQL 只有 Oracle 数据库有。
-- MySQL 目前不支持 PL/SQL 的，但支持 Navicat Premium。
-- 实际开发中，我们一般都是通过 Java 代码来处理数据，将处理好的结果，再交给 SQL 去执行即可。
-- 语法格式
declare -- 声明部分
-- 定义变量
begin -- 开始
-- 主要内容
exception -- 处理异常
end; -- 结束
/****打印语句****/
dbms_output.put_line("HelloWord!");
--复杂类型
eg:
declare
type player is record (
name varchar(20),
price number
);
-- 如果要使用复杂类型数据，需要赋值给一个变量才行
vplayer player; --声明一个类型为player的变量vplayer
begin
vplayer.name := '贝尔';
vplayer.price :=9000;
dbms_output.put_line(vplayer.name || '的价格为：'||vplayer.price||'万欧元');
end;
--判断语句
--if-then-elsif-then-else结构
declare
a int;
begin
a :=1;
if a=0 then
dbms_output.put_line('hello');
elsif a=1 then
dbms_output.put_line('sorry');
else
dbms_output.put_line('????');
end if;
end;
--case语句
declare
a int;
begin
a :=1;
case a
when 0 then dbms_output.put_line('a为0');
when 1 then dbms_output.put_line('a为1');
else dbms_output.put_line('a不为0也不为1');
end case;
end;
--循环结构(以下有5中循环结构)
--1)loop-exit-end循环
declare
a int;
vsum int;
begin
a :=1;
vsum :=0;
loop
vsum :=vsum+a;
a :=a+1;
if a=5 then
exit;
end if;
end loop;
dbms_output.put_line('1累加到4的和为:'||vsum);
end;
--2)loop-exit-when-end循环
declare
a int;
vsum int;
begin
a :=1;
vsum :=0;
loop
vsum :=vsum+a;
a :=a+1;
exit when a=6;
end loop;
dbms_output.put_line('1累加到5的和为:'||vsum);
end;
--3)while-loop-end循环
declare
a int;
vsum int;
begin
a :=1;
vsum :=0;
while a<7
loop
vsum :=vsum+a;
a :=a+1;
end loop;
dbms_output.put_line('1累加到6的和为:'||vsum);
end;
--4)for-in-loop-end循环
declare
a int;
vsum int;
begin
a :=1;
vsum :=0;
for a in 1..4 --每次循环一次，循环变量自动增长1个步长的值，直至循环变量的值超过终值
loop
vsum :=vsum+a;
end loop;
dbms_output.put_line('1累加到4的和为:'||vsum);
end;
--5）goto语句
declare
a int;
vsum int;
begin
a :=1;
vsum :=0;
<<cz>> --标号
vsum :=vsum+a;
a :=a+1;
if a<5 then
goto cz; --去到标号为cz的那个位置
end if;
dbms_output.put_line('1累加到4的和为:'||vsum);
end;
网友评论