一、Mysql的分页查询
-
-
示例
-- 查询员工表前3名的员工的编号和姓名
select empno,ename from emp limit 3;
-- 或者
SELECT empno,ename FROM emp LIMIT 0,3;
查询员工表第三页的员工信息信息（假设每页显示4条，总共有14条）
SELECT empno,ename FROM emp LIMIT 8,4;MySQL的分页语句中 LIMIT后面的 第一个参数是（page-1)*pagesize 第二个参数是pagesize 其中page表示页码，pagesize表示每一页显示的最大记录数
-
二、Oracle总结
（一）数据库模型、关系模型、三范式、有效性和安全性的实现
-
数据库模型
-
关系型数据模型：Oracle,MySql,SQLServer
-
非关系型数据模型：H2,Redis，MongoDB
-
-
关系模型
-
关系模式：是静态的稳定的
-
关系：关系是关系模式在某一时刻的状态或内容，关系是动态的
-
关系模型：关系模型是指用二维表的形式表示实体和实体间联系的数据模型
-
-
数据库设计步骤
-
doc->从doc文档中找出实体->将实体和属性转换为表格
-
-
三范式
-
1NF:字段不可拆分
-
2NF：表中要有主键
-
3NF：表中不能有别的表的非主键列
-
-
数据库的有效性和安全性通过约束来实现
-
主键约束：唯一确定一条记录，记录不重复，不能为空
-
唯一约束：唯一确定一条记录，记录不重复，可以为空
-
默认约束：有些列不能为空，必须有值，但客户可能忘记赋值，此时用默认约束强制给空记录赋值一个默认值，保证数据的有效性，完整性
-
检查越俗：让数据有效，不能出现意外的数据
-
外键约束：如果依赖的表中没有相应的主键记录，则当前表就无法添加记录，这就是外键依赖，当然要删除依赖表中的记录时，如果记录有被依赖的情况，则要么报错不能删除，要么级联删除当前表中的记录，保证了数据的安全，避免了依赖不存在的问题。
-
(二)SQL语言的分类:DDL、DML、DQL、TCL、DCL
-
SQL语言的描述和作用
-
SQL语言是一种结构化查询语言
-
作用是用来管理关系型数据库
-
-
SQL语言的分类
-
DDL（Data Definition Language):数据定义语言
-
DML（Data Manipulation Language):数据操纵语言
-
DQL（Data Query Language):数据查询语言
-
TCL（Transaction Control Language):事务控制语言
-
DCL（Data Control Language):数据控制语言
-
-
DDL（数据定义语言）
-
关键词：create、alter、drop
-
表空间
-
定义：表空间是存储数据的地方，存储数据库对象的地方，在磁盘上对应有数据文件
-
Oracle中默认有一个系统表空间和临时表空间，如果创建用户不指定用户的表空间，则默认把用户创建在系统表空间，系统表空间类似于windows的C盘，如果把所有软件安装在C盘是不合理的，Oracle中把用户分配在系统表空间也是不合理的，最理想的是一个用户一个表空间，在删除用户时可以删除表空间，这样管理比较好。
-
表空间是虚拟的，可以无限大，数据文件是存储信息的载体
-
临时表空间：数据库操作时临时使用，如排序，去重，统计等大数据时使用，小规模可以在内存中完成
-
操作
-- 授予管理员权限
grant dba to user_test;
-- 授予创建表空间的权限
grant create tablespace to user_test;
-- 创建表空间
create tablespace sp1 datafile ‘sp1.dbf‘ size 1m;
-- 创建临时表空间
create temporary tablespace tsp1 tempfile ‘tsp1.dbf‘ size 2m;
-- 创建用户时指定表空间和临时表空间
create user user_test identified by user_test default tablespace sp1 temporary tablespace tsp1;
-- 授予删除表空间权限
grant drop tablespace to user_test;
-- 删除表空间
drop tablespace sp1 including contents and datafiles;
-- 删除临时表空间
drop tablespace tsp1 including contents and datafiles;
-- 授予扩大表空间的权限
grant alter database to user_test;
-- 扩大表空间
alter database datafile ‘sp1.dbf‘ resize 2m;
-- 授予在表空间增加数据文件的权限
grant alter tablespace to user_test;
-- 增加数据文件
alter tablespacesp1 add datafile ‘sp1_2.dbf‘ size 1m;
-
-
表
-
数据类型
number:数字类型
integer:整数
char:定长字符串
varchar:变长字符串
timestamp:时间戳
blob:二进制数据
clob:放大量的字符数据
bfile:二进制文件 -
对表的操作
-- 创建表
create table t1(id number);
-- 增加一列
alter table t1 add name char(4);
-- 修改列的宽度
alter table t1 modify name char(5);
-- 删除一列
alter table t1 drop column age;
-- 增加系统默认名字的主键约束
alter table t1 add primary key(id);
-- 增加自己命名的主键约束
alter table t1 add constraint t1_pk primary key(id);
-- 删除主键
alter table t1 drop primary key;
-- 删除指定约束的主键
alter table t1 drop constraint t1_pk;
-- 增加唯一约束
alter table t1 add constraint t1_uk unique(name);
-- 增加默认约束
alter table t1 modify age default 18;
-- 增加检查约束
alter table t1 add constraint t1_sex check(sex = 0 or sex = 1);
-- 增加外键约束
(什么都不写相当于no action,当子表有关联时删除父表记录会报错)
alter table t1 add constraint t1_fk foreign key(clazz) references t_clazz(id);
(设置为set null,删除父表记录时，把子表相应外键置为空)
alter table t1 add constraint t1_fk foreign key(clazz) references t_clazz(id) on delete set null;
(设置为cascade级联删除，删除父表时，子表相应的记录也会被删除)
alter table t1 add constraint t1_fk foreign key(clazz) references t_clazz(id) on delete cascade;
-- 删除外键约束
alter table t1 drop constraint
-- 删除表
drop table t1;
truncate table t1;
-
-
-
DML（数据操纵语言）
-
关键词：insert、delete、update
-
对表中的数据进行操作
-- 插入数据
-- 不指定添加的字段
insert into t_clazz values(1,‘name1‘,‘‘);
-- 指定添加的字段
insert into t1(id,name,age,sex,clazz) values(3,‘name1‘,10,0,1);
-- 修改
-- 修改所有记录
update t_clazz set name=‘新名称‘;
-- 修改指定记录
update t_clazz set name=‘新名称1‘ where id=1;
-- 删除
delete from t_clazz where id=1;
-
-
DQL(数据查询语言)
-
关键词：select
-
查询表中的信息
-- 查询当前用户下所有表的信息
select * from user_tables t;
-- 查询当前表的所有字段信息
select * from user_tab_columns where table_name=‘T_CLAZZ‘;
-- 给表起别名
as 或者是空格
select sal,empno,ename,sal+100 sal2 from emp;
-- 判断字段是否为空
select sal from emp where sal is null;
-- where过滤
select empno,ename,sal from emp where sal>=3000;
-- distinct去重
-- 查询部门里有员工的部门号
select distinct deptno from emp;
-- distinct对后面的字段都进行过滤
select distinct deptno,sal from emp;
-- 排序,升序asc,降序desc
-- 默认为升序
select * from emp order by sal;
-- 部门升序，部门中的员工工资降序
select * from emp order by deptno asc,sal desc;
-
-
TCL(事务控制语言)
-- 提交事务
commit;
-- 事务回滚
rollback；
-- 设置标志
savepoint p1;
-- 插入数据
insert into emp(empno) values(5);
-- 回滚到标志处
rollback to p1; -
DCL(数据控制语言)
-- 创建角色
create role 角色名;
-- 分配权限
grant select on class to 角色名; -
truncate,drop,delete三个的区别
-
truncate和drop属于DDL，针对的是表，delete属于DML，针对的是表中的数据
-
delete删除后不会释放空间，可以恢复数据，truncate和drop都会释放空间，不能恢复，truncate只删除表里的内容，drop删除的是全部，表上的所有对象都会删除掉
-
有害化排序：delete->truncate->drop
-
(三)索引
-
作用：快速查询数据
-
优缺点：
-
优点：DQL快速查询，为了保证查询速度，可以牺牲一定的空间和DML的操作时间
-
缺点：占空间（在保存正常业务数据的同时，还要额外的存储索引信息),在维护正常业务数据的同时，还要维护索引的数据（占时间）
-
-
什么情况下适合建立索引
-
一个条件经常出现在where中，就要考虑是否要建立索引
-
对于列中的数据区分度比较高的列也可以建立索引（反例：性别不适合）
-
如果同时又多个查询条件，where c1=a and c2 = b,建立联合索引(c1,c2),索引中的列的顺序和where条件中的顺序尽量一致
-
索引(c1,c2...)条件是where c1 = a and c2=b 这个是好的，条件是where c2=b这个不是特别理想，或者where c2=b and c1 = a这个也不好
-
-
-
索引的分类
-
聚集索引：会改变记录的物理位置
-
非聚集索引：数据的顺序和它插入时的顺序一致，在索引中保存的是记录的rowid，找到索引，然后找到rowid，然后找到记录
-
(四)函数
-
单行函数
-
字符函数
-- 返回字符的ascii值：ascii函数
-- 将连个字符或字符串拼接起来：concat函数
-- 查找字符：instr函数
-- 返回字符串的长度：length函数
-- 将所有字符全部转换为小写：lower函数
-- 将所有字符全部转换为大写：upper函数
-- 去掉字符串中的空格：ltrim(去掉左边的空格),rtrim(去掉字符串右边的空格),trim(去掉字符串两边的空格)
-- 替换字符：replace函数
-- 截取字符中的一段：substr函数 -
数字函数
-- 求绝对值：abs函数
-- 求大于或等于某个数的最小值：ceil函数
-- 求小于或等于某个数的最大值：floor函数
-- 四舍五入：round函数
-- 截断函数：trunc函数
-- 取余：mod函数 -
日期函数
-- 显示当前时间：sysdate关键字
-- 在当前时间上增加一个月：add_months函数
-- 求当前月的最后一天十几号：last_day函数
-- 四舍五入日期：round函数
-- 求月份差：months_between函数
-- 求当前日期的下一个星期几是几号：next_day函数
-- 提取员工的入职月份：extract函数
-- 截断日期：trunc函数 -
转换函数
-- 转换成字符：to_char函数
-- 转换成数字：to_number函数
-- 转换成日期：to_date函数
-
-
多行函数
-
统计函数
-- 求最大值：max函数
-- 求最小值：min函数
-- 求员工的个数：count函数
-- 求平均值：avg函数
-- 求和：sum函数 -
解决空值：nvl
-- nv1(comm,0)函数：如果奖金（comm)为空的话，则转换为0
-- nv2(comm,1,0)函数：如果奖金(comm)不为空，则转换为1，否则，转换为0 -
decode函数
-- decode(job,‘CLERK‘,‘店员‘);
-- 注释：如果job=’CLERK‘，则显示为店员 -
应用
-
行转列
CREATE TABLE resident(NAME VARCHAR(6) NOT NULL,r_month number(2) NOT NULL,sal NUMBER(8) NOT NULL);
INSERT INTO resident VALUES(‘赵一‘,1,10000);
INSERT INTO resident VALUES(‘赵一‘,2,10500);
INSERT INTO resident VALUES(‘赵一‘,3,9000);
INSERT INTO resident VALUES(‘赵一‘,4,10030);
INSERT INTO resident VALUES(‘赵一‘,5,10500);
INSERT INTO resident VALUES(‘赵一‘,6,10000);
INSERT INTO resident VALUES(‘赵一‘,7,12000);
INSERT INTO resident VALUES(‘赵一‘,8,10500);
INSERT INTO resident VALUES(‘赵一‘,9,10800);
INSERT INTO resident VALUES(‘赵一‘,10,10900);
INSERT INTO resident VALUES(‘赵一‘,11,10010);
INSERT INTO resident VALUES(‘赵一‘,12,10800);
SELECT * FROM resident;
DROP TABLE resident;
-- 显示每个人每月的工资，以列的形式显示，2020我国的平均收入是40000元左右，显示该居民是否达到或拖后腿
SELECT NAME,SUM(DECODE(r_month,1,sal)) 一月,
SUM(DECODE(r_month,2,sal)) 二月,
SUM(DECODE(r_month,3,sal)) 三月,
SUM(DECODE(r_month,4,sal)) 四月,
SUM(DECODE(r_month,5,sal)) 五月,
SUM(DECODE(r_month,6,sal)) 六月,
SUM(DECODE(r_month,7,sal)) 七月,
SUM(DECODE(r_month,8,sal)) 八月,
SUM(DECODE(r_month,9,sal)) 九月,
SUM(DECODE(r_month,10,sal)) 十月,
SUM(DECODE(r_month,11,sal)) 十一月,
SUM(DECODE(r_month,12,sal)) 十二月,
DECODE(TRUNC(SUM(sal)/40000),0,‘拖后腿‘,‘合格‘) 是否合格
FROM resident r GROUP BY NAME; -
SQL递归的实现：
-- SELECT * FROM XX STRAT WITH 从什么地方开始 CONNECT BY PRIOR 递归条件
-- 找id为1的子辈
SELECT * FROM cow START WITH ID=1 CONNECT BY PRIOR ID = parent_id;
-
-
(五)序列
-
创建序列
-- 创建序列：minvalue:最小值，maxvale：最大值，start with：开始值，increment：步长，cache：缓存
-- 缓存有最大值限制：
(最大值-最小值)/步长的绝对值，向上取整
create sequence seq1
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 20;
-- 将序列改为循环使用:cycle
alter sequence seq1 cycle; -
序列的两个属性
-- 下一个值
select seq1.nextval from dual;
-- 当前值
select seq1.currval from dual; -
删除序列
drop sequence seq1;
(六)视图
-
引入视图的原因
-- 有些表不能给程序员开放，DBA可以建一个视图，给程序员开放视图的权限，这样就可以间接来访问受限的表。
-- 建立视图可以避免误修改数据的风险，对数据进行横向或纵向的保护 -
作用
-
把SQL存储起来，运行方便
-
可以有效的保护数据，对权限加以控制
-
-
特点
-
视图是虚表，逻辑存在的（不是真实的，抽象的）
-
视图关键字：view
-
以SQL的形式存在
create view v_emp as
select * from emp; -
视图的定义：vw或v
-
-
建立视图
-- 建立视图，若视图已经存在，就进行更新
create or replace view v_emp as
select * from emp;
?
-- 创建带约束的视图:with check option
CREATE OR REPLACE FORCE VIEW v_test AS
SELECT * FROM emp WHERE empno>7788
WITH CHECK OPTION;
?
-- 创建只能查看的视图
CREATE OR REPLACE FORCE VIEW v_test AS
SELECT * FROM emp
WITH READ ONLY;
?
-- 视图涉及多个表
CREATE OR REPLACE VIEW v_emp_dept AS SELECT empno,dname FROM emp e, dept d WHERE e.deptno=d.deptno;
?
-- force:不存在权限也能创建视图
CREATE OR REPLACE FORCE VIEW v_t1 AS SELECT * FROM user22.t1;
-- 分配权限，能够查看此视图
grant select on t1 to scott;
-- 收回权限
revoke select on t1 from scott;
?
-- 查看视图
SELECT * FROM v_emp_dept; -
物化视图
-
引入原因
普通视图在执行SQL时要消耗性能，因为他们是一个SQL，物化视图执行时只查询自己的数据（不再进行计算）
-
分类：ON DEMAND 、ON COMMIT
-
ON DEMAND :在需要时（在查询视图时）再从基表中更新数据到物化同步（物化视图的同步
-
ON COMMIT:在更新视图的同时，同步物化视图，物化视图始终是最新的。（维护数据效率低）
-
-
-
SQL优化：索引和物化视图
(七)in和exists，PL/SQL编程
-
in和exists执行顺序
-
in:由内而外，先执行子查询，将子查询作为结果集，再执行外查询
-
exists：由外而内，相当于loop循环，先从外表取出一条记录，然后进入子查询看这条记录是否符合条件，若符合，返回true,否则返回false，然后继续判断下一条记录
-
-
in和exists例题
-
员工表employee,有2个字段salary , deptid ,查询所有数据,按照部门号从高到低,工资从低到高顺序输出
SELECT * FROM employee ORDER BY deptid DESC,salary; -
员工表employee,有2个字段salary , deptid ,查询各个部门中高于所有员工平均工资的人数:查询的结果列为: 部门,人数
SELECT deptid 部门,COUNT(*) 人数 FROM employee WHERE salary>(
SELECT AVG(salary) FROM employee) GROUP BY deptid; -
员工表employee,有2个字段salary , deptid ,查询出最高工资和最低工资的差是多少
SELECT MAX(salary)-MIN(salary) FROM employee; -
用exists替换下面的sql中的in SELECT * FROM tb1 WHERE u_id IN (SELECT u_id FROM tb2 WHERE name = ‘张三’);
SELECT * FROM tb1 WHERE EXISTS (
SELECT * FROM tb2 WHERE NAME=‘张三‘ AND tb1.u_id=tb2.u_id);
-
-
当子查询返回的结果集很少时，采用in比较好；
当子查询返回的结果集很多时，采用exists比较好
-
PL/SQL编程
-
区域：在Test window进行编程
-
组成：
-
declare部分进行一些变量的定义
-
begin end;部分里面写执行程序
declare
-- Local variables here
-- 进行一些变量的定义
i integer;
begin
-- Test statements here
-- 执行程序在这里编写
end;
-
-
变量定义
-
直接定义字段类型
i integer;
v_username VARCHAR(10); -
常量的定义
pai CONSTANT NUMBER := 3.14; -
字段类型来源于表中
v_empno emp.empno%TYPE; -
行变量的定义
v_emprow emp%ROWTYPE;
-
-
变量的赋值（用:=进行赋值）
-- 在定义变量时进行赋值，或者在使用前进行赋值
v_clazz NUMBER:=11;
v_college NUMBER(10) DEFAULT 1;
-- 在查询时进行赋值:将查询到的最高工资赋值给i
SELECT MAX(sal) INTO i FROM emp;
-- 行变量的赋值
SELECT emp.empno,emp.ename INTO v_emprow.empno,v_emprow.ename FROM emp WHERE rownum=1; -
输出
-
dbms_output.put():输出到缓存
-
dbms_output.put_line()：将缓存中的和要输出的进行输出
dbms_output.put(1);
dbms_output.put_line(2);
dbms_output.put_line(v_username||‘-‘||v_clazz||‘-‘||v_college);
-
-
异常处理：exception
-- sqlcode:异常编号，sqlerrom:异常信息
-- 捕获异常
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(‘没有数据‘);
WHEN too_many_rows THEN
dbms_output.put_line(‘返回太多行‘);
WHEN OTHERS THEN -- 对未知异常的处理
dbms_output.put_line(SQLCODE||‘-‘||SQLERRM); -
选择语句(在编程窗口执行)：IF ELSIF
-- 成绩大于小于60为没有通过，大于60通过
declare
-- Local variables here
i integer;
begin
-- Test statements here
i:=60;
IF i<60 THEN
dbms_output.put_line(‘no pass‘);
ELSE
dbms_output.put_line(‘pass‘);
END IF;
end; -
选择语句(在SQL窗口执行): case when
SELECT score,CASE
WHEN score BETWEEN 0 AND 59 THEN ‘不及格‘
WHEN score BETWEEN 60 AND 80 THEN ‘普通‘
WHEN score BETWEEN 80 AND 90 THEN ‘良好‘
WHEN score BETWEEN 90 AND 100 THEN ‘优秀‘
END
FROM t_score; -
for循环
declare
-- Local variables here
v_score INTEGER;-- 成绩
i integer;
begin
-- Test statements here
DELETE FROM t_score;-- 测试前先删除表数据
FOR i IN 1..10 LOOP -- 2. for循环
v_score:=abs(mod(dbms_random.random,100)); -- 1. 随机数
INSERT INTO t_score(NAME,score) VALUES(‘name‘||seq1.nextval,v_score);
END LOOP;
COMMIT;
dbms_output.put_line(i);
end; -
while循环
-- 变量定义时为初始化为null，无法进行比较
declare
-- Local variables here
i INTEGER;
BEGIN
IF i IS NULL THEN
dbms_output.put_line(‘i is not init‘);
i:=1;
ELSE
IF i<10 THEN
dbms_output.put_line(‘i<10‘);
ELSE
dbms_output.put_line(‘i>=10‘);
-- Test statements here
WHILE i<10 LOOP
i:=i+1;
dbms_output.put_line(i);
END LOOP;
end; -
loop end loop
declare
-- Local variables here
i integer;
begin
-- Test statements here
/*
for ... loop
end loop
while loop
end loop
*/
LOOP
IF i IS NULL THEN
i:=0;
ELSIF i<10 THEN
i:=i+1;
ELSE
EXIT; -- 退出循环
END IF;
dbms_output.put_line(i);
END LOOP;
end;
?
-- 示例2
declare
-- Local variables here
i integer;
begin
-- Test statements here
i:=0;
LOOP
i:=i+1;
EXIT WHEN i>10;
dbms_output.put_line(i);
END LOOP;
end;
-
(八)异常、游标、存储过程、自定义函数
-
自定义一个异常，当i未初始化时抛出异常，处理异常
declare
-- Local variables here
i INTEGER :=0;
myex EXCEPTION;
BEGIN
-- 抛出一个系统自己编号的异常
IF i IS NULL THEN
RAISE myex;
END IF;
-- 抛出一个带有编号和信息的异常,自己定义的编号范围为（-20000,-29999)
IF i=0 THEN
raise_application_error(-20000,‘i值不能为0‘);
END IF;
EXCEPTION
WHEN myex THEN
dbms_output.put_line(‘i未初始化‘);
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE||‘-‘||SQLERRM);
-- Test statements here
end; -
异常不能重复抛出，但是异常可以同时捕获
when ex1 or ex2 or ex3 then
... -
游标：cursor
-
定义
游标是数据的集合，也可以说是数据集合的指针，可以从游标中获取集合中的值，一般在程序中使用，如：存储过程，函数，触发器
-
类型：
-
系统游标（也叫隐式游标）
-
用户游标：包含静态游标和动态游标
-
-
游标的操作：打开游标，遍历游标，关闭游标
-
用户游标
-
静态游标
-
手动打开游标，关闭游标，需要写指针移动的语句
declare
-- Local variables here
-- 静态游标：后面的select语句是固定的
CURSOR c_emp IS SELECT * FROM emp WHERE deptno=10;
-- 定义一个变量，接收游标所指向的记录
v_row emp%ROWTYPE;
begin
-- Test statements here
-- 1.打开游标
OPEN c_emp;
-- 2. 遍历游标
LOOP
-- 游标指针的移动,取出游标所指向的记录，赋值给变量
FETCH c_emp INTO v_row; dbms_output.put_line(v_row.empno||‘-‘||v_row.ename);
-- 当遍历完成的时候，跳出循环
EXIT WHEN c_emp%NOTFOUND;
END LOOP;
-- 3.关闭游标
IF c_emp%ISOPEN THEN
CLOSE c_emp;
END IF;
-- 4.异常处理
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE||‘-‘||SQLERRM);
end; -
简单操作，不用打开和关闭游标
declare
-- Local variables here
i integer;
-- 定义一个静态游标
CURSOR c_emp IS SELECT * FROM emp WHERE deptno=10;
-- 定义一个变量，用来循环
v_row emp%ROWTYPE;
begin
-- Test statements here
-- 使用for循环
FOR v_row IN c_emp LOOP
dbms_output.put_line(v_row.empno||‘-‘||v_row.ename);
END LOOP;
end; -
带参数的静态游标
-- 方式一
declare
-- Local variables here
-- 定义游标
CURSOR c_emp(dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=dno;
-- 定义一个行变量接收游标指向的值
v_row emp%ROWTYPE;
begin
-- Test statements here
-- 1.打开游标
OPEN c_emp(10);
-- 2.遍历游标
LOOP
FETCH c_emp INTO v_row;
dbms_output.put_line(v_row.empno||‘-‘||v_row.ename);
-- 跳出条件
EXIT WHEN c_emp%NOTFOUND;
END LOOP;
-- 3.关闭游标
IF c_emp%ISOPEN THEN
CLOSE c_emp;
END IF;
end;
-- 方式二
declare
-- Local variables here
-- 定义一个游标
CURSOR c_emp(dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=dno;
-- 定义一个行变量，用来实现for循环
v_row emp%ROWTYPE;
begin
-- Test statements here
FOR v_row IN c_emp(10) LOOP
dbms_output.put_line(v_row.empno||‘-‘||v_row.ename);
END LOOP;
end;
-
-
动态游标
declare
-- Local variables here
-- 1.定义动态游标的类型
TYPE dync IS REF CURSOR;
-- 2.定义游标
c_emp dync;
-- 5.定义一个行变量，接收游标所指向的记录
v_row emp%ROWTYPE;
-- 定义一个sql语句
v_sql VARCHAR(100);
v_dno emp.deptno%TYPE;
begin
-- Test statements here
-- 3.打开游标
SELECT deptno INTO v_dno FROM emp WHERE empno=7369;
v_sql :=‘SELECT * FROM emp WHERE deptno=‘||v_dno;
OPEN c_emp FOR v_sql;
-- 4.遍历游标
LOOP
FETCH c_emp INTO v_row;
dbms_output.put_line(v_row.empno||‘-‘||v_row.ename);
-- 跳出循环
EXIT WHEN c_emp%NOTFOUND;
END LOOP;
-- 6.关闭游标
IF c_emp%ISOPEN THEN
CLOSE c_emp;
END IF;
end; -
静态游标和动态游标在定义和打开有区别，动态游标后面的select语句可以用变量来实现拼接
-
-
系统游标（隐式游标）
-
系统已经定义好的，在做DML操作时会触发，输出影响到的行数的信息
declare
-- Local variables here
i integer;
begin
-- Test statements here
UPDATE emp SET sal=sal+0;
dbms_output.put_line(‘影响到的行数‘||SQL%ROWCOUNT);
IF SQL%ROWCOUNT>0 THEN
dbms_output.put_line(‘update success‘);
ELSE
dbms_output.put_line(‘update failure‘);
END IF;
end;
-
-
-
存储过程:procedure
-
定义
存储过程是一段已经编译好，并且已经有名称的程序，可以通过名称来调用他
-
简单的存储过程
-- 1.在sql窗口定义
CREATE OR REPLACE PROCEDURE p1
IS
BEGIN
dbms_output.put_line(‘hello world‘);
END p1;
-- 2.调用，可以在test窗口使用名称直接调用，也可以在sql窗口右键p1，执行 -
带参存储过程:in表示入参，可以省略，out表示出参
-
案例一
CREATE OR REPLACE PROCEDURE p1(v_dno emp.deptno%TYPE)
IS
-- 定义了一个游标
CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_dno;
v_row emp%ROWTYPE;
BEGIN
FOR v_row IN c_emp LOOP
dbms_output.put_line(v_row.empno||‘-‘||v_row.ename);
END LOOP;
END p1; -
案例二
CREATE OR REPLACE PROCEDURE p1(v_dno IN emp.deptno%TYPE,v_count OUT NUMBER)
IS
BEGIN
SELECT COUNT(*) INTO v_count FROM emp WHERE deptno=v_dno;
END p1;
-
-
存储过程处理业务逻辑，如果有返回通过出参返回
-
拓展
-
集群：尽量把业务或操作分开执行，就像以前一个人做事情，现在分成了多个部门，自己做好自己的事情
-
如果把逻辑都给数据库，则数据库处理就成了瓶颈，影响系统的性能，我们会把工作交由应用服务器来处理，数据库只管数据，不管业务，给数据库松绑
-
-
-
自定义函数
-
定义
自定义函数也是一段编译好的由名称的程序，和存储过程的区别是主要是为了得到一些数据（强调的是返回的东西），存储过程强调的是做什么事情，以及过程
-
简单的自定义函数及使用
-- 获取某部门的员工人数
CREATE OR REPLACE FUNCTION f1(v_dno emp.deptno%TYPE)
-- 1. 返回类型
RETURN NUMBER
IS
-- 2.定义变量
v_count NUMBER;
BEGIN
-- 3.赋值
SELECT COUNT(*) INTO v_count FROM emp WHERE deptno=v_dno;
-- 4.返回
RETURN v_count;
END f1; -
带参的自定义函数：in入参，out出参，和存储过程一样
-
(九)触发器
-
概念
触发器不能主动调用，只有在对相应对象操作时自动触发。
-
触发器分类
-
DML触发器
-
替代触发器
-
系统触发器
-
-
DML触发器
-
触发器的粒度
-
语句级触发器：对sql操作只做一次触发
-
行级触发器：对每行数据都触发
-
示例
insert into emp(empno) where empno in (1,2);
-- 语句级触发器就只触发一次
-- 行级触发器会触发两次，因为这条sql语句影响两行
-
-
创建触发器
-
语句级触发器
create or replace trigger 触发器名称
after/before insert or update or delete
on 表名
delclare
变量声明的地方
begin
end; -
行级触发器
create or replace trigger 触发器名称
after/before insert or update or delete
on 表名
for each row
delclare
变量声明的地方
begin
end;
-
-
-
DDL触发器
-
当创建表，删除表，修改表时触发
-
DDL触发器的创建
CREATE OR REPLACE TRIGGER trg_ddl
AFTER DDL
ON scott.schema
DECLARE
BEGIN
INSERT INTO t_log(ID,log_user,log_date,log_text)
VALUES(seq1.nextval,USER,SYSDATE,ora_sysevent||‘-‘||ora_dict_obj_name||‘-‘||ora_dict_obj_type);
END;
-
-
替代触发器
-
解决复杂视图不能插入数据的问题
-
替代触发器的创建
CREATE OR REPLACE TRIGGER trg_vemp
INSTEAD OF INSERT
ON v_emp
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO emp(empno,sal,comm) VALUES(:new.empno,:new.sal,0);
END;
-
(十)事务、锁
-
事务的四大特点
-
原子性：要么都做，要么都不做
-
一致性：事务操作前和操作后是平衡的
-
隔离性：多个事务并发对同一数据进行操作时，会有顺序，互不影响
-
永久性：当事务提交后，数据会永久保存，断电或重启不会产生影响
-
-
锁的分类
-
从限制程序角度
-
排他锁
-
共享锁
-
-
从产生时机角度
-
自动锁
-
显示锁
-
-
从产生的操作角度
-
DML锁
-
DDL锁
-
-
悲观锁和乐观锁
-
(十一)PGA、SGA、存储空间、分区表、union和union all、导入导出、SQL优化
-
PGA
-
程序缓存区，为了特定用户进程服务，是私有的
-
功能
-
排序区
-
会话区
-
堆栈区
-
游标区
-
-
-
SGA
-
系统全局区，所有用户都能使用，是共享的，一个oracle实例，一个SGA区
-
组成
-
固定区：通过这个区查找其他区的地址
-
重做缓冲区
-
块缓冲区
-
共享池
-
大池
-
Java池
-
流池
-
-
-
存储空间
从小到大依次为：数据块，分区，段，表空间
-
数据块设置的大小应合理
-
分区是一系列连续的数据块的集合
-
数据段是分区的上层组织单位
-
表空间中有多个段，段和文件相对应
-
-
分区表
-
将一张表的数据存储到不同的表空间
-
优点
-
增强可用性，一个分区坏了，其他分区还可以用
-
均衡I/O，可以把表的不同分区分配到不同的磁盘I/O来平衡I/O改善性能
-
提高性能，对大表的查询，增加，修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快
-
分区对用户透明，用户感觉是同一张表
-
-
分类：按范围分区，hash分区，复杂分区
-
建立分区
-- 1.建立3个表空间
CREATE TABLESPACE tb1 DATAFILE ‘tb1.dbf‘ SIZE 1m;
CREATE TABLESPACE tb2 DATAFILE ‘tb2.dbf‘ SIZE 1m;
CREATE TABLESPACE tb3 DATAFILE ‘tb3.dbf‘ SIZE 1m;
-- 2.建立分区表，
-- 根据范围分区
CREATE TABLE t_area(ID NUMBER PRIMARY KEY,money NUMBER)
PARTITION BY RANGE(money)
(
PARTITION p1 VALUES LESS THAN (1500) TABLESPACE tb1,
PARTITION p2 VALUES LESS THAN (3000) TABLESPACE tb2,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tb3
);
-- hash分区
CREATE TABLE t_area(ID NUMBER PRIMARY KEY,money NUMBER)
PARTITION BY HASH(money)
(
PARTITION p1 TABLESPACE tb1,
PARTITION p2 TABLESPACE tb2,
PARTITION p3 TABLESPACE tb3
);
-- 3.插入数据
INSERT INTO t_area VALUES(sq.nextval,1000);-- 将插入到分区p1
INSERT INTO t_area VALUES(sq.nextval,2000);-- 将插入到分区p2
INSERT INTO t_area VALUES(sq.nextval,5000);-- 将插入到分区p3
-- 4.查询数据，在相应分区查询相应的money
SELECT * FROM t_area PARTITION(p1);-- 查询p1分区
SELECT * FROM t_area PARTITION(p2);-- 查询p2分区
SELECT * FROM t_area PARTITION(p3);-- 查询p3分区
-- 查询6000工资员工的信息，在p3分区查找
select * from t_area partition(p3) where money=6000;
-- 内容，分区都删除
ALTER TABLE yourTable DROP PARTITION partionName1;
-- 只清除数据
ALTER TABLE yourTable TRUNCATE PARTITION partionName1; -
为分区建立索引
-
全局索引
create index 索引名称 on 表名(字段) global
?
partition by range (字段)
(
partition index_part1 values less than (60),
?
partition index_part2 values less than (80),
?
partition index_partmax values less than (maxvalue)
); -
局部索引
create index 索引名 on 表名 (字段1, 字段2) local;
-
-
union和union all
-
union和union all的要求
-
每条sql的列数相同
-
每条sql的列的类型要相互匹配，char对char，number对number
-
-
区别
union结果没有重复的,union all结果重复
-
-
导入导出
-
导出
-
pl/sql导出：tools->export
-
命令行导出：
exp scott/scott@orcl file="d:/1.sql"
-
-
导入
-
pl/sql导入：tools->import
-
命令行导入：
imp scott/scott@orcl file="d:/1.sql" full=y
-- 成功终止导入，说明导入成功
-
-
-
SQL优化
-
写出具体的列名，不写*，减少SQL的分析时间，不用再去数据字典中找列的信息。
-
创建索引
-
对索引列的过滤时，不用函数，不要隐匿转换数据类型，不用模糊匹配开头查询
-
创建合理的表结构，可以适当对数据进行冗余，减少子查询
-