presto 查询分页分页查询page和size

转载

mob6454cc696f04 2024-03-23 19:40:20

文章标签 presto 查询分页数据 ide 表空间 文章分类 云原生云计算

Oracle总结及Mysql的分页查询

一、Mysql的分页查询

limit接受一个或两个数字参数，参数必须是一个整数常量。第一个参数指明从哪条记录开始，第二个参数指明页数大小

示例

-- 查询员工表前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中的inSELECT * 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优化

程序缓存区，为了特定用户进程服务，是私有的
功能

排序区
会话区
堆栈区
游标区

系统全局区，所有用户都能使用，是共享的，一个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的分析时间，不用再去数据字典中找列的信息。
创建索引
对索引列的过滤时，不用函数，不要隐匿转换数据类型，不用模糊匹配开头查询
创建合理的表结构，可以适当对数据进行冗余，减少子查询

软件下载提取码：qwer

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：Springboot 通过feignClient 发送json参数 springboot feign原理

下一篇：spring扫描service spring扫描自定义注解

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

presto 查询分页 分页查询page和size

presto 查询分页 分页查询page和size

Oracle总结及Mysql的分页查询

一、Mysql的分页查询

二、Oracle总结

（一）数据库模型、关系模型、三范式、有效性和安全性的实现

(二)SQL语言的分类:DDL、DML、DQL、TCL、DCL

(三)索引

(四)函数

(五)序列

(六)视图

(七)in和exists，PL/SQL编程

(八)异常、游标、存储过程、自定义函数

(九)触发器

(十)事务、锁

(十一)PGA、SGA、存储空间、分区表、union和union all、导入导出、SQL优化

51CTO博客

presto 查询分页分页查询page和size

presto 查询分页分页查询page和size