周度学习总结-2021-12-31

1. 工作简要

本周主要学习Oracle数据库和MySQL数据库，并对这两种数据库做出下面汇报总结。此外，针对《校招系统项目》进行研讨，并提出新的需求和改进。

2. 学习内容

2.1 SQL*PLUS命令

2.1.1 set命令

语法：

SET system_variable value

参数说明：

• pagesize：一页的行数（包括标题和最后一行空行），默认14行
• linesize：一行字符数，默认80
• newpage: 页与页间隔，默认为1
• pause: 输出结果是否滚动，默认off
  • off：一次显示完
  • on：滚动翻页，回车键下一页
  • text：与on一起用，暂停后显示字符串

示例：

set linesize 100 --设置一行字符为100个
set newpage 2 --设置页与页间隔为2

2.1.2 help | ?: 查询语法

2.1.3 describe | desc: 描述视图、表结构

2.1.4 spool: 将查询语句和结果存储文件当中

语法：

spool [file_name [ create | replace | append] | off | out]
off:结束输出;out:增加打印

示例：

spool c:\emp.txt
select * from emp;
spool off

2.1.5 show | sho: 显示当前环境中的多种信息

2.2 SQL

2.2.1 语言分类

• 系统控制语句：ALTER SYSTEM
• 会话控制语句:ALTER SESSION,SET USER
• 嵌入SQL语句：DDL、DML、事务控制语句、open、close、fetch、execute
• 数据操作语句：DML
• 事务控制语句：COMMIT、ROLLBACK、SAVEPOINT
• 数据定义语言：DDL、CREATE、DROP、ALTER

2.2.2 模糊查询

• %: 0个或者多个字符
• _: 一个字符

2.2.3 系统函数

• ASCII© 返回ASC码
• CHR(i) 返回字符
• concat(s1, s2) 字符串拼接
• initcap(s) 首字母大写
• instr(s1, s2 [,i][,j]) 第二个字符串在第一个字符串出现的位置，i是s1第i个字符开始，j是s2第j次出现
• length(s) 字符串长度
• lower(s)
• upper(s)
• ltrim(s1,s2)删除s1左边的s2
• rtrim(s1,s2) 删除s1右边的s2
• trim(s1,s2) 删除s1两边的s2
• replace(s1,s2[,s3]) 将s1里面的s2替换为s3
• substr(s,i[,j]) 字符串从第i个位置截取j个字符

2.3 PL/SQL语言

2.3.1 语法

• 声明部分 - 此部分是以关键字DECLARE开头。这是一个可选部分，并定义了程序中要使用的所有变量，游标，子程序和其他元素。
• 可执行命令部分 - 此部分包含在关键字BEGIN和END之间，这是一个强制性部分。它由程序的可执行PL/SQL语句组成。它应该有至少一个可执行代码行，它可以只是一个NULL命令，表示不执行任何操作。
• 异常处理部分 - 此部分以关键字EXCEPTION开头。这是一个可选部分，它包含处理程序中错误的异常。

DECLARE
    <declarations section>
BEGIN 
    <executable command(s)>
EXCEPTION 
    <exception handling> 
END;
/

2.3.2 数据类型

• 标量(SCALAR)类型 - 它是没有内部组件的单个值，例如：NUMBER，DATE或BOOLEAN等。
• 大对象(LOB)类型 - 指向与其他数据项(例如：文本，图形图像，视频剪辑和声音波形)分开存储的大对象的指针。
• 复合类型 - 具有可单独访问的内部组件的数据项。例如，集合和记录。
• 引用类型 - 指向其他数据项。

2.3.3 变量

声明变量如下

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

示例如下

DECLARE 
   -- Global variables  
   num1 number := 95;  
   num2 number := 85;  
BEGIN  
   dbms_output.put_line('Outer Variable num1: ' || num1); 
   dbms_output.put_line('Outer Variable num2: ' || num2); 
   DECLARE  
      -- Local variables 
      num1 number := 195;  
      num2 number := 185;  
   BEGIN  
      dbms_output.put_line('Inner Variable num1: ' || num1); 
      dbms_output.put_line('Inner Variable num2: ' || num2); 
   END;  
END; 
/

Outer Variable num1: 95 
Outer Variable num2: 85 
Inner Variable num1: 195 
Inner Variable num2: 185  

PL/SQL procedure successfully completed.

2.3.4 数组类型

所有varray是由连续的内存位置组成。最低的地址对应于第一个元素，而最后一个元素的地址最高。

创建varray类型数组

模式级别创建
CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

PL/SQL块中创建
TYPE varray_type_name IS VARRAY(n) of <element_type>

遍历数组1
SET SERVEROUTPUT ON SIZE 99999;
DECLARE 
   type namesarray IS VARRAY(5) OF VARCHAR2(10); 
   type grades IS VARRAY(5) OF INTEGER; 
   names namesarray; 
   marks grades; 
   total integer; 
BEGIN 
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i in 1 .. total LOOP 
      dbms_output.put_line('Student: ' || names(i) || ' 
      Marks: ' || marks(i)); 
   END LOOP; 
END; 

遍历数组2
变量的元素也可以是任何数据库表的%ROWTYPE或任何数据库表字段的%TYPE表来引用表示。
DECLARE 
   CURSOR c_customers is 
   SELECT  name FROM customers; 
   type c_list is varray (6) of customers.name%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter + 1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter ||'):'||name_list(counter)); 
   END LOOP; 
END; 

2.3.4 存储过程

创建存储过程可选参数列表包含参数的名称，模式和类型。IN表示将从外部传递的值，OUT表示将用于返回过程外的值的参数。

创建存储过程语法如下

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
[exception]
   [dowith_sentences;]
END procedure_name;

删除存储过程
DROP PROCEDURE procedure-name;

示例如下

例1
创建
CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 
执行
exec[ute] greetings;

例2
创建
DECLARE 
   a number; 
   b number; 
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
   IF x < y THEN 
      z:= x; 
   ELSE 
      z:= y; 
   END IF; 
END;   
BEGIN 
   a:= 12; 
   b:= 35; 
   findMin(a, b, c); 
   dbms_output.put_line('两个数：12, 35中的最小值是 : ' || c); 
END; 
两个数：12, 35中的最小值是 : 12

存储过程传递参数三种方式

• 位置符号findMin(a, b, c, d);
• 命名符号findMin(x => a, y => b, z => c, m => d);
• 混合符号findMin(a, b, c, m => d);

2.3.5 函数

创建函数语法如下

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];

示例如下

CREATE OR REPLACE FUNCTION totalCustomers 
RETURN number IS 
   total number(2) := 0; 
BEGIN 
   SELECT count(*) into total 
   FROM customers; 

   RETURN total; 
END; 

调用函数
SET SERVEROUTPUT ON SIZE 99999;
DECLARE 
   c number(2); 
BEGIN 
   c := totalCustomers(); 
   dbms_output.put_line('当前客户的总数为: ' || c); 
END;

2.3.6 游标

游标是指向此上下文区域的指针。PL/SQL通过游标控制上下文区域，游标保存SQL语句返回的行(一个或多个)。 游标所在的行集称为活动集。PL/SQL中有两种类型的游标：隐式游标、显式游标

隐式游标如下

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE customers 
   SET salary = salary + 500; 
   IF sql%notfound THEN 
      dbms_output.put_line('没有找到客户信息~'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line('一共有：' || total_rows || ' 个客户的工资被更新！ '); 
   END IF;  
END; 
/

显示游标语法

CURSOR cursor_name IS select_statement;

示例如下

声明游标
CURSOR c_customers IS 
   SELECT id, name, address FROM customers;
打开游标
OPEN c_customers;
获取游标
FETCH c_customers INTO c_id, c_name, c_addr;
关闭游标
CLOSE c_customers;
示例：
DECLARE 
   c_id customers.id%type; 
   c_name customers.name%type; 
   c_addr customers.address%type; 
   CURSOR c_customers is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_id, c_name, c_addr; 
      EXIT WHEN c_customers%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customers; 
END; 

2.3.7 触发器

[FOR EAH ROW] - 这指定了一个行级别的触发器，即触发器将被执行的每一行受到影响。否则触发器将在执行SQL语句时执行一次，这称为表级触发器。OLD和NEW引用不可用于表级触发器。

语法如下

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name | view_name | user_name | db_name
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END;

示例如下

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old salary: ' || :OLD.salary); 
   dbms_output.put_line('New salary: ' || :NEW.salary); 
   dbms_output.put_line('Salary difference: ' || sal_diff); 
END;

2.3.8 程序包

示例如下

创建程序包示例
create or replace package body pack_emp is
  function fun_avg_sal(num_deptno number) return number is  --引入“规范”中的函数
    num_avg_sal number;--定义内部变量
  begin
    select avg(sal)
    into num_avg_sal
    from emp
    where deptno = num_deptno;--计算某个部门的平均工资
    return(num_avg_sal);--返回平均工资
  exception
    when no_data_found then--若未发现记录
      dbms_output.put_line('该部门编号不存在雇员记录');
    return 0;--返回0
  end fun_avg_sal;

  procedure pro_regulate_sal(var_job varchar2,num_proportion number) is--引入“规范”中的存储过程
  begin
    update emp
    set sal = sal*(1+num_proportion)
    where job = var_job;--为指定的职务调整工资
  end pro_regulate_sal;
end pack_emp;
/


执行程序包
set serveroutput on
declare
  num_deptno emp.deptno%type;--定义部门编号变量
  var_job emp.job%type;--定义职务变量
  num_avg_sal emp.sal%type;--定义工资变量
  num_proportion number;--定义工资调整比例变量
begin
  num_deptno:=10;--设置部门编号为10
  num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);--计算部门编号为10的平均工资
  dbms_output.put_line(num_deptno||'号部门的平均工资是：'||num_avg_sal);--输出平均工资
  
  var_job:='SALESMAN';--设置职务名称
  num_proportion:=0.1;--设置调整比例
  pack_emp.pro_regulate_sal(var_job,num_proportion);--调整指定部门的工资
end;
/

2.4 Oracle归档日志

2.4.1 日志模式

• 归档日志模式
• 非归档日志模式（默认）

查看归档日志模式
select log_mode from V$database;
启用归档模式
alter database archivelog
关闭归档模式
alter database noarchivelog

2.4.2 查看归档日志

• 使用数据字典和动态性能视图
• 使用archive log list（需要在sys用户下）

数据字典查看
select dest_name from v$archive_dest;
sys用户下（sys as sysdba）
archive log list

2.5 Oracle控制文件

2.5.1 介绍

• 在数据库创建的时候自动创建
• 二进制文件
• 包含数据库结构信息、数据文件和日志文件信息
• 控制文件被不断更新
• 及时备份控制文件（至少有一个副本）
• 保护控制文件

2.5.2 多路复用

• 更改control_files
• 复制控制文件

alter system set control_files=
path1.control01.CTL,
path2.control02.CTL,
...
scope=spfile;

2.5.3 备份和恢复控制文件

• 二进制文件备份
• 脚本文件备份

恢复控制文件重新设置control_files路径，重启数据库

2.5.4 删除控制文件

关闭数据库、编辑初始化参数control_files，清除控制文件名字、重启数据库

2.6 Oracle重做日志

2.6.1 添加新的重做日志文件组

alter database add logfile [group id] 

2.6.2 创建日志成员文件

alter database add log member 

2.6.3 删除重做日志文件

alter database droop logfile member 

2.6.4 更改重做日志组

alter database rename file oldpath to newpath;

2.7 Oracle表空间

2.7.1 默认表空间

• system表空间：内部数据和数据字典，sys用户的各个对象和其他用户的少量对象
• sysaux表空间：充当system的辅助表空间，主要用于存储数据字典以外的其他数据对象

2.7.2 创建表空间

2.7.3 临时表空间

主要存储排序区

使用条件：

• select distinct不重复检索
• union联合索引
• minus计算
• analyze分析
• 连接两个没有索引的表

创建临时表空间

create temporary tablespace tablespace_name tempfile|datafile ‘xx.dbf’ size xx;

临时表空间组：

多个临时表空间组成一个临时表空间组

• 避免临时表空间不足
• 一个用户的多个会话可以使得使用组中的不同临时表空间
• 使并行的服务器在单节点上能够使用多个临时表空间

撤销表空间

旧数据（撤销数据）保存在撤销表空间

• 读写一致
• 可以回退事务
• 事务恢复
• 闪回操作

2.7.4 创建表空间

create undo tablespace
datafile
path1.dbf
size 3G;

2.7.5 设置默认表空间

更改默认临时表空间 
alter database default temprory tablespace {tablespace_name} 
更改默认永久表空间
alter datacase default tablespace {tablespace_name}

2.7.6 表空间状态

• 只读：不能进行DML操作（insert update delete），但对某些对象的删除操作可以进行（索引和目录）
• 可读：默认状态

alter tablespace tablespace_name read only;
alter tablespace tablespace_name read write;

2.7.8 重命名表空间

alter tablespace tablespace_name rename to new_tablespace_name;

2.7.9 删除表空间

drop tablespace tablespace_name rename [including contents 包含数据删除] [cascade constraints 删除完整性约束];

2.7.10 表空间添加数据文件

alter tablespace users 
add datafile 'e:\app\Administrator\oradata\orcl\users02.dbf'
size 10m 
autoextend on next 5m 
maxsize unlimited;

2.7.11 表空间删除数据文件

alter tablespace users
drop datafile 'e:\app\Administrator\oradata\orcl\users02.dbf';

2.8 Oracle数据表

2.8.1 创建数据表

创建学生表
create table students(
stuno number(10) not null, --学号
stuname varchar2(8),	--姓名
sex char(2),	--性别
age int,	--年龄
departno varchar2(2) not null,	--系别编号
classno varchar2(4) not null,	--班级编号
regdate date default sysdate	--建档日期
);

复制表
create table students_2
as select *
from students;

2.8.2 表约束

• 非空：not null
• 主键约束：primary key
• 唯一性约束：unique
• 外键约束：foreign key references departments(department_id);
• 禁用约束：默认激活，DISABLE
• 删除约束：alter table table_name drop constraint con_name;

修改约束
alter table table_name modify col_name not null;

2.8.3 维护数据表

增加字段

alter table table_name add(col_name varchar(20)); 

删除字段

alter table table_name drop column col_name; 

修改字段

alter table table_name modify col_name varchar(4) 

重命名表

alter table table_name rename to new_table_name 

改变表空间参数

alter table table_name move tablespace new_tablespace_name; 

删除表

drop table table_name [cascade constraints] 

修改表的状态

alter table table_name read only; alter table table_name read write;

2.9 Oracle分区技术

**2.9.1 优点 **

​ （1）减少维护工作量，独立管理每个分区比管理单个大表要轻松得多。

​ （2）增强数据库的可用性，如果表的一个或几个分区由于系统故障而不能使用，而表其余的分区仍然可以使用；如果系统故障只影响表的一部分分区，那么，只有这部分分区需要修复，这就比修复整个大表耗费的时间少许多。

​ （3）均衡I/O，减少竞争，通过把表的不同分区分配到不同的磁盘来平衡I/O改善性能。

​ （4）分区对用户保持透明，最终用户感觉不到分区的存在。

​ （5）提高查询速度：对大表的查询、增加、修改等操作可以分解到表的不同分区中来并行执行，这样就可以加快运行速度，在数据仓库的TP查询特别有用。

2.9.2 创建表分区

范围分区

create table ware_retail_part --创建一个描述商品零售的数据表
(
  id integer primary key,--销售编号
  retail_date date,--销售日期
  ware_name varchar2(50)--商品名称
)
partition by range(retail_date)
(
  --2011年第一个季度为part_01分区
  partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBSP_1,
  --2011年第二个季度为part_02分区
  partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBSP_1,
  --2011年第三个季度为part_03分区
  partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBSP_2,
  --2011年第四个季度为part_04分区
  partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBSP_2 
);

hash分区

create table ware_retail_part3 --创建一个描述商品零售的数据表
(
  id integer primary key,--销售编号
  retail_date date,--销售日期
  ware_name varchar2(50)--商品名称
)
partition by hash(id)
(
  partition par_01 tablespace TBSP_1,
  partition par_02 tablespace TBSP_2
);

列表分区（省份可枚举）

create table clients
(
  id integer primary key,
  name varchar2(50),
  province varchar2(20)
)
partition by list(province)
(
  partition shandong values('山东省'),
  partition guangdong values('广东省'),
  partition yunnan values('云南省')
);

组合分区

 create table person2       --创建以一个描述个人信息的表
(
  id number primary key,    --个人的编号
  name varchar2(20),	--姓名
  sex varchar2(2)		--性别
)
partition by range(id)--以id作为分区键创建范围分区
subpartition by hash(name)--以name列作为分区键创建hash子分区
subpartitions 2 store in(tbsp_1,tbsp_2)--hash子分区公有两个，分别存储在两个不同的命名空间中
(
  partition par1 values less than(5000),--范围分区，id小于5000
  partition par2 values less than(10000),--范围分区，id小于10000
  partition par3 values less than(maxvalue)--范围分区，id不小于10000
);

interval分区

create table saleRecord
(
 id number primary key, --编号
 goodsname varchar2(50),--商品名称
 saledate date,--销售日期
 quantity number--销售量
)
partition by range(saledate)
interval (numtoyminterval(1,'year'))
(
  --设置分区键值日期小于2012-01-01
  partition par_fist values less than (to_date('2012-01-01','yyyy-mm-dd'))
);

2.9.3 管理表分区

添加分区

alter table table_name add partition 

删除分区(分区数据也将删除)

alter table table_name drop partition 

并入分区

--创建表和分区
create table sales--创建一个销售记录表
(
  id number primary key,--记录编号
  goodsname varchar2(10),--商品名
  saledate date--销售日期
)
partition by range(saledate)--按照日期分区
(
  --第一季度数据
  partition part_sea1 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace tbsp_1,
  --第二季度数据
  partition part_sea2 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace tbsp_2,
  --第三季度数据
  partition part_sea3 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace tbsp_1,
  --第四季度数据
  partition part_sea4 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace tbsp_2
);

--创建局部索引
create index index_3_4 on sales(saledate)
local(
partition part_seal tablespace tbsp_1,
partition part_sea2 tablespace tbsp_2,
partition part_sea3 tablespace tbsp_1,
partition part_sea4 tablespace tbsp_2
);

--并入分区
alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4;

--重建局部索引
alter table sales modify partition part_sea4 rebuild unusable local indexes;

2.9.4 创建索引分区

创建本地索引分区

--创建3个表空间

create tablespace ts_1 datafile 'D:\OracleFiles\OracleData\ts1.dbf'
size 10m
extent management local autoallocate;
create tablespace ts_2 datafile 'D:\OracleFiles\OracleData\ts2.dbf'
size 10m
extent management local autoallocate;
create tablespace ts_3 datafile 'D:\OracleFiles\OracleData\ts3.dbf'
size 10m
extent management local autoallocate;


--创建分区表
create table studentgrade
(
  id number primary key,--记录id
  name varchar2(10),--学生名称
  subject varchar2(10),--学科
  grade number --成绩
)
partition by range(grade)
(
  --小于60分，不及格
  partition par_nopass values less than(60) tablespace ts_1,
  --小于70分，及格
  partition par_pass values less than(70) tablespace ts_2,
  --大于或等于70分，优秀
  partition par_good values less than(maxvalue) tablespace ts_3
);


--创建本地索引分区
create index grade_index on studentgrade(grade)
local--根据表分区创建本地索引分区
(
  partition p1 tablespace ts_1,
  partition p2 tablespace ts_2,
  partition p3 tablespace ts_3
);

--查询所创建的索引分区信息
select partition_name,tablespace_name from dba_ind_partitions where index_name = 'GRADE_INDEX';

全局索引分区

create index index_SalePrice on Books(SalePrice)
global partition by range(SalePrice)
(
  partition p1 values less than (30),
  partition p2 values less than (50),
  partition p3 values less than (maxvalue)
);

2.9.5 管理索引分区

重命名索引分区

alter index index_saleprice rename partition p3 to p_new;

删除索引分区

alter index index_name drop partition

2.10 Oracle角色

2.10.1 创建用户

语法

create user user_name identified by pass_word
[or identified exeternally]
[or identified globally as ‘CN=user’]
[default tablespace tablespace_default]
[temporary tablespace tablespace_temp]
[quota [integer k[m]] [unlimited] ] on tablesapce_ specify1
[,quota [integer k[m]] [unlimited] ] on tablesapce_ specify2
[,…]…on tablespace_specifyn
[profiles profile_name]
[account lock or account unlock]

示例

create user mr identified by mrsoft
default tablespace users
temporary tablespace temp;

2.10.2 修改用户

alter user [option]

2.10.3 删除用户

drop user user_name [cascade]

2.10.4 用户权限管理

语法

grant sys_privi | role to user | role | public [with admin option]

示例

--创建用户dongfang
create user dongfang identified by mrsoft
default tablespace users
quota 10m on users;
--创建用户xifang
create user xifang identified by mrsoft
default tablespace users
quota 10m on users;
--授权
grant create session,create table to dongfang with admin option;
connect dongfang/mrsoft;
grant create session,create table to xifang;
--连接到xifang
connect xifang/mrsoft;
create table tb_xifang
( id number,
  name varchar2(20)
);
-- 回收权限
revoke sys_privi | role from user | role | public
revoke resource from east;

2.10.5 对象授权

语法

Grant obj_privi | all column on schema.object to user | role | public [with grant option] | [with hierarchy option]

示例

grant select,insert,delete,update on scott.emp to xifang;

2.10.6 回收对象权限

语法

revoke obj_privi | all on schema.object from user | role | public cascade constraints

示例

revoke delete,update on scott.emp from xifang;

2.10.7 查看用户与权限

创建角色

create role role_name [ not identified | identified by [password] | [exeternally] | [globally]]

示例

create role designer identified by 123456;

2.11 Oracle事务

2.11.1 事务特性

• 原子性
• 一致性
• 隔离性
• 持久性

2.12.2 提交事务

• 显示提交：使用commit命令
• 自动提交：在SQL*plus里执行"set autocommit on;"
• 隐式提交：发出DDL命令、程序终止命令和关闭数据库命令

2.12.3 回滚事务

回滚事务是指撤销对数据库进行的全部操作，Oracle利用回退段来存储修改前的数据，通过重做日志来记录对数据所做的修改。

• 首先使用回退段数据撤销对数据库所做的修改
• Oracle后台服务进程释放掉事务所使用的系统资源
• 最后显示通知，告诉用户事务回退成功。

rollback针对未提交的事务。

2.12.4 回退点

示例

savepoint s1;
insert into dept ...;
savepoint s2;
delete from dept...;
rollback s2; --未删除状态
rollback s1; --未添加状态

2.12.5 锁

锁机制

• 共享锁
• 独占锁

死锁

锁类型

• DML类型：保护数据的完整性
• DDL锁：保护方案对象的定义
• 内部所：内部数据库和内部结构，不可访问

2.12 优化

2.12.1 常规优化

• 建议不用"*"代替所有的列名：*会通过查询数据字典进行解释
• 使用truncate代替delete：truncate不会将删除的数据保存在撤销表空间
• 在确保完整性的情况下多用commit语句：及时释放资源
• 尽量减少表的查询次数

2.12.2 表连接优化

• 驱动表的选择：即最先被访问到的表

2.12.3 WHERE字句连接顺序

• Oracle采用自下而上的顺序解析WHERE子句，所以过滤最大数据记录的条件写在WHERE子句的末尾。

2.12.4 合理使用索引

建立索引的使用规则

• 以查询关键字为基础，表的行随机排序
• 包含的列数相对比较少的表
• 表中的大多数查询都包含相对简单的WHRE从句
• 对于经常以查询关键字为基础的表，并且该表中的行遵从随机分布
• 缓存命中率低，并且不需要操作系统权限

选择索引列的原则：

（1）WHERE从句频繁使用的关键字。

（2）SQL语句中频繁由于进行表连接的关键字。

（3）可选择性高（重复性少的）关键字。

（4）对于取值较少的关键字或表达式，不要采用标准的B树索引，可以考虑建立位图索引。

（5）不要将那些频繁修改的列作为索引列。

（6）不要使用包含操作符或函数的WHERE从句中的关键字作为索引列，如果需要的话，可以考虑建立函数索引。

（7）如果大量并发的INSERT、UPDATE、DELETE语句访问了父表或者子表，则考虑使用完整性约束的外部键作为索引。

（8）在选择索引列时，还要考虑该索引所引起的INSERT、UPDATE、DELETE操作是否值得。

2.13 Oracle备份和恢复

2.13.1 相关概念

• 物理数据丢失：数据文件、控制文件、重做日志文件等丢失
• 逻辑数据丢失：表、索引、和表记录等数据库主键的丢失
• 恢复：联机备份恢复，并且可以通过归档的重做日志文件恢复到任意时间
• 备份文件：所有的数据文件、控制文件、归档的重做日志文件

2.13.2 RMAN工具

执行备份和恢复的客户端应用程序

组件：

• RMAN命令执行器
• 目标数据库

通道：

• 备份和恢复通过通道执行。

run{
allocate channel ch_1 device type disk
format = 'd:\oraclebf\%u_%c.bak';
backup tablespace system,users,tbsp_1,ts_1 channel ch_1;
}

2.13.3 备份

类型

• 完全备份
• 增量备份

可备份对象

• 归档重做日志
• 数据文件
• 数据库
• 表空间
• 控制文件
• 备份集

示例

backup database format 'D:\OracleFiles\Backup\oradb_%Y_%M_%D_%U.bak' maxsetsize 2G;
 
run{
allocate channel ch_1 type disk;
backup tablespace tbsp_1,ts_1
format 'D:\OracleFiles\Backup\%d_%p_%t_%c.dbf';
}

backup datafile 1,2,3 filesperset 3;

backup tablespace tbsp_1 include current controlfile;

2.13.4 恢复

noarchivelog：执行restore命令就可以将数据库文件恢复到正确的位置

示例

--在SQL*Plus模式下
connect system/1qaz2wsx as sysdba;
select log_mode from v$database;
---在CMD环境下
rman target system/1qaz2wsx nocatalog;
--在RMAN环境下
shutdown immediate
startup mount



run{
allocate channel ch_1 type disk;
backup database
format 'D:\OracleFiles\Backup\orcl_%t_%u.bak';
}


alter database open;

shutdown immediate;
--然后手动删除users01.dbf文件，并试图使用startup命令启动数据库




startup mount

run{
allocate channel ch_1 type disk;
restore database;
}

alter database open;

archivelog模式示例

--SQL*Plus模式下
select log_mode from v$database;



---在CMD模式下：
rman target system/1qaz2wsx nocatalog;




--在rman模式下：
run{
allocate channel ch_1 type disk;
allocate channel ch_2 type disk;
backup tablespace users
format 'D:\OracleFiles\Backup\users_tablespace.bak';
}



shutdown immediate; 
--然后手动删除users表空间对应的数据文件


startup mount;


run{
allocate channel ch_1 type disk;
restore tablespace users;
recover tablespace users;
}

alter database open;

2.13.5 部分恢复

• 基于时间的不完整恢复
• 基于更改的不完全恢复

示例

---在CMD模式下：
rman target system/1qaz2wsx nocatalog;


--在rman模式下：
shutdown immediate;

startup mount;


run{
allocate channel ch_1 type disk;
allocate channel ch_2 type disk;
backup database format 'D:\OracleFiles\Backup\database_%t_%u_%c.bak';
backup archivelog all format 'D:\OracleFiles\Backup\archive_%t_%u_%c.bak';
}


--在sqlplus环境下：
select to_char(sysdate,'hh24:mi:ss')
from dual;

alter session set nls_date_format = 'yyyy-mm-dd';
insert into scott.emp(empno,ename,job,hiredate,sal)
values(1234,'东方','manager','1975-01-12',5000);

insert into scott.emp(empno,ename,job,hiredate,sal)
values(6789,'西方','salesman','1980-12-12',3000);

commit;


---在rman环境中
shutdown immediate;

startup mount;



run{
sql'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"';
allocate channel ch_1 type disk;
allocate channel ch_2 type disk;
set until time '2012-01-05 14:37:35';
restore database;
recover database;
sql'alter database open resetlogs';
}


---在SQL*Plus环境竟中
select empno,ename from scott.emp

--在sqlplus环境下
delete from scott.emp;
commit;
alter system switch logfile;




exec dbms_logmnr_d.build('e:\orcldata\logminer\director.ora','e:\orcldata\logminer');




exec dbms_logmnr.add_logfile('f:\app\Administrator\oradata\orcl\redo01a.log',dbms_logmnr.new);
exec dbms_logmnr.add_logfile('f:\app\Administrator\oradata\orcl\redo02a.log',dbms_logmnr.new);
exec dbms_logmnr.add_logfile('f:\app\Administrator\oradata\orcl\redo03a.log',dbms_logmnr.new);



exec dbms_logmnr.start_logmnr(dictfilename=>'e:\orcldata\logminer\director.ora');




select scn,sql_redo
from v$logmnr_contents
where seg_name ='EMP';



exec dbms_logmnr.end_logmnr;






---在rman环境下
run
{
allocate channel ch_1 type disk;
allocate channel ch_2 type disk;
set until scn 6501278;
restore database;
recover database;
sql'alter database open resetlogs';
}

2.13.6 数据泵

• expdp：数据导出
• expdp：数据导入

示例

-- 创建directory对象
create directory dump_dir as 'd:\dump';
-- 授权
grant read,write on directory dump_dir to scott;
-- 导出表
expdp scott/1qaz2wsx directory=dump_dir dumpfile=tab.dmp tables=emp,dept
-- 导出模式
expdp system/1qaz2wsx directory = dump_dir dumpfile=schema.dmp schemas = scott,hr
-- 导出表空间
expdp system/1qaz2wsx directory = dump_dir dumpfile = tablespace.dmp tablespaces = tbsp_1
-- 导出数据库
expdp system/1qaz2wsx directory=dump_dir dumpfile=fulldatabase.dmp full=y

-- 导入表
impdp system/1qaz2wsx directory=dump_dir dumpfile=tab.dmp tables=scott.dept,scott.emp remap_schema=scott:system
-- 导入模式
impdp system/1qaz2wsx directory=dump_dir dumpfile=schema.dmp schemas=scott remap_schema=scott:system;
-- 导入表空间
impdp system/1qaz2wsx directory=dump_dir dumpfile=tablespace.dmp tablespaces=tbsp_1
-- 导入数据库
impdp system/1qaz2wsx directory=dump_dir dumpfile=fulldatabase.dmp full=y

2.14 Java连接Oracle数据库

2.14.1 配置数据库连接

spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:ORCL
spring.datasource.username=scott
spring.datasource.password=123456

2.14.2 添加Oracle驱动依赖

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <scope>runtime</scope>
</dependency>

2.14.3 添加orai18n依赖

如果不加可能“Oracle启动报错：不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK”

<dependency>
    <groupId>com.oracle.ojdbc</groupId>
    <artifactId>orai18n</artifactId>
    <version>19.3.0.0</version>
</dependency>

2.14.4 添加实体对象

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private int id;
    private String name;
    private int age;
}

2.14.5 添加Mapper

@Repository
@Mapper
public interface UserMapper {

    @Select("select * from \"user\" where id = #{id} and rownum<=1")
    User getUserById(int id);
}

2.14.6 测试

@SpringBootTest
class OracletestApplicationTests {
    
    @Resource
    UserMapper userMapper;

    @Test
    void contextLoads() {
        System.out.println(userMapper.getUserById(11).toString());
    }

}

2.14.7 小结

• 使用工具（navicat）创建表的表名和字段名字会添加双引号
• 使用IDEA工具创建的数据库表名会添加双引号
• Oracle数据库和MySQL数据语法有差异，比如Oracle数据库不支持MySQL数据库的limit关键字

--从第offset行开始
[ OFFSET offset ROWS]
-- 返回row_count行
-- 默认only，with ties配合order by可以返回额外的没有显示的行
FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ]

2.15 Netty创建Telnet服务器

2.15.1 介绍

​ Telnet协议是TCP/IP协议族中的一员，是Internet远程登录服务的标准协议和主要方式。它为用户提供了在本地计算机上完成远程主机工作的能力。在终端使用者的电脑上使用telnet程序，用它连接到服务器。终端使用者可以在telnet程序中输入命令，这些命令会在服务器上运行，就像直接在服务器的控制台上输入一样。可以在本地就能控制服务器。

2.15.2 添加依赖

<dependency>
    <groupId>io.netty</groupId>
    <artifactId>netty-all</artifactId>
</dependency>

2.15.3 创建Server

package com.example.oracletest.service;

import com.example.oracletest.utils.NettyTelnetInitializer;
import io.netty.bootstrap.ServerBootstrap;
import io.netty.channel.Channel;
import io.netty.channel.ChannelOption;
import io.netty.channel.EventLoopGroup;
import io.netty.channel.nio.NioEventLoopGroup;
import io.netty.channel.socket.nio.NioServerSocketChannel;
import io.netty.handler.logging.LogLevel;
import io.netty.handler.logging.LoggingHandler;


public class NettyTelnetServer {

    // 指定端口号
    private static final int PORT = 8888;

    // 用来处理accept事件
    private final EventLoopGroup bossGroup = new NioEventLoopGroup(1);
    // 用来处理通道的读写事件
    private final EventLoopGroup workerGroup = new NioEventLoopGroup();

    public void open() throws InterruptedException {

        ServerBootstrap serverBootstrap = new ServerBootstrap();
        // 用来初始化服务端可连接队列
        // 服务端处理客户端连接请求是按顺序处理的，所以同一时间只能处理一个客户端连接，多个客户端来的时候，服务端将不能处理的客户端连接请求放在队列中等待处理，backlog参数指定了队列的大小。
        serverBootstrap.option(ChannelOption.SO_BACKLOG, 1024);
        serverBootstrap.group(bossGroup, workerGroup)
                .channel(NioServerSocketChannel.class)  // 绑定服务端通道
                .handler(new LoggingHandler(LogLevel.INFO))
                .childHandler(new NettyTelnetInitializer()); //绑定handler，处理读写事件，NettyTelnetInitializer

        // 绑定对应的端口号,并启动开始监听端口上的连接
        Channel ch = serverBootstrap.bind(PORT).sync().channel();

        // 等待关闭,同步端口
        ch.closeFuture().sync();
    }
    public void close(){
        bossGroup.shutdownGracefully();
        workerGroup.shutdownGracefully();
    }
}

2.15.4 创建Handler读写事件初始化接口

package com.example.oracletest.utils;

import com.example.oracletest.service.NettyTelnetHandler;
import io.netty.channel.ChannelInitializer;
import io.netty.channel.ChannelPipeline;
import io.netty.channel.socket.SocketChannel;
import io.netty.handler.codec.DelimiterBasedFrameDecoder;
import io.netty.handler.codec.Delimiters;
import io.netty.handler.codec.string.StringDecoder;
import io.netty.handler.codec.string.StringEncoder;

public class NettyTelnetInitializer extends ChannelInitializer<SocketChannel> {

    private static final StringDecoder DECODER = new StringDecoder();
    private static final StringEncoder ENCODER = new StringEncoder();

    @Override
    protected void initChannel(SocketChannel channel) {

        ChannelPipeline pipeline = channel.pipeline();

        // Add the text line codec combination first,
        pipeline.addLast(new DelimiterBasedFrameDecoder(8192, Delimiters.lineDelimiter()));
        // 10秒没有读取到数据自动关闭连接
        pipeline.addLast(new ReadTimeoutHandler(10));
        
        // 添加编码和解码的类
        pipeline.addLast(DECODER);
        pipeline.addLast(ENCODER);

        // 添加处理业务的类
        pipeline.addLast(new NettyTelnetHandler());

    }
}

2.15.5 创建Handler类

package com.example.oracletest.service;

import io.netty.channel.ChannelFuture;
import io.netty.channel.ChannelFutureListener;
import io.netty.channel.ChannelHandlerContext;
import io.netty.channel.SimpleChannelInboundHandler;

import java.net.InetAddress;
import java.util.Date;

public class NettyTelnetHandler extends SimpleChannelInboundHandler<String> {

    @Override
    public void channelActive(ChannelHandlerContext ctx) throws Exception {
        // Send greeting for a new connection.
        ctx.write("Welcome to " + InetAddress.getLocalHost().getHostName() + "!\r\n");
        ctx.write("It is " + new Date() + " now.\r\n");
        ctx.flush();
    }

    @Override
    public void exceptionCaught(ChannelHandlerContext ctx, Throwable cause) {
        cause.printStackTrace();
        ctx.close();
    }

    @Override
    protected void channelRead0(ChannelHandlerContext ctx, String request) {
        
        String response;
        boolean close = false;
        if (request.isEmpty()) {
            response = "Please type something.\r\n";
        } else if ("bye".equals(request.toLowerCase())) {
            response = "Have a good day!\r\n";
            close = true;
        } else {
            response = "Did you say '" + request + "'(" + ctx.channel().id() + ")?\r\n";
        }

        ChannelFuture future = ctx.write(response);
        ctx.flush();
        if (close) {
            future.addListener(ChannelFutureListener.CLOSE);
        }
    }
}

2.15.6 测试类

package com.example.oracletest;

import com.example.oracletest.service.NettyTelnetServer;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class OracletestApplicationTests {

    NettyTelnetServer nettyTelnetServer = new NettyTelnetServer();

    @Test
    void contextLoads() {
        try {
            nettyTelnetServer.open();
        } catch (InterruptedException e) {
            nettyTelnetServer.close();
        }
    }

}

2.15.7 小结

因为Telnet是Tcp/IP的一种，而Netty能通过编程自定义各种协议

• 一个channel绑定一个客户端，可以用channel比作session记录用户
• 可以提供ReadTimeoutHandler设置自动断开时

2.16 MySQL基础

2.16.1 中文数据问题

show charater set; --查看数据库支持字符集
show variables like 'charcter_set%'; --默认客户端默认编码
set character_set_client = gbk; -- 改变服务端认为的客户端的编码,会话级别
set character_set_result = gbk; -- 改变服务端返回的客户端的编码,会话级别

set name gbk;   -- 快捷方式

2.16.2 校对集

show collation;
三种格式：
    _bin:二进制编辑哦
    _cs:大小写敏感
    _ci:大小写不敏感
注意：校对集在数据前修改，数据后修改无效

2.16.3 WEb乱码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lVPtqrU6-1640946604873)(D:\有道云\data\weixinobU7VjmYfkCV36SsUJRIzyoEsXDs\b95cc0e29bac47b5a50f6f248f041f7b\clipboard.png)]

2.16.4 语法
语法类似Oracle

3. 总结

​ 虽然以前也学习过SQL，用过Mysql、postgresql、redis、sqlite等数据库，但是只是停留在简单的使用而已，有一些数据类型和语法也没有用过。在这次系统的课程学习中，我也深刻认识到自己对数据库了解的不足，比如对于一些可以用数据库的函数、触发器操作的业务完全可以替代代码，此外对数据库的存储结构也有了一定的了解，这对以后设计数据库又很大的帮助，好的设计不仅可以节省空间，也可以优化sql语句来提升查询效率。