oracle数据库

Oracle ORCL VSS Writer Service 可以禁用

Listener 和OracleServiceORCL改为手动

  • 登录oracle:
    • 用户名:sys
    • 密码:root
    • database:orcl

  • oracle 数据库:一个大的磁盘空间

    在大的磁盘空间中去创建表空间,对应着文件,有名字,对应着一个用户

    sys/stytem 管理员

    在创建数据库的同时,默认创建了system表空间,存放系统用户的信息.

    ​ oracle创建了实例用户, 用户名:scott
    密码:tiger,默认是锁定的.需要用sys帐户登录后,找到user表,->scott->右键edit->general->Account
    locked勾掉,上面Role privileges选项卡,把Grantable下的两个都勾上.

    再次登录,身份是normal

sys 和system用户

1572319243175

数据类型

  • varchar2(size) 变长字符串
  • char(size) 固定长度字符串
  • number(p,s) 变长数值
  • date 日期和时间值
  • long 可存放最多2GB的变长字符数据
  • clob 可存放最多4GB的变长数据
  • RAW and long RAW 二进制数据
  • BLOB 可存放最多4GB二进制数据

数据操作

和MySQL差不多

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
-- 创建OA系统的表空间
create tablespace oaspace
datafile 'D:\gezhongGJ\oracle\space\oa.dbf'
size 10M;


–创建用户，来管理表空间
create user wangwu identified by 123456 default tablespace oaspace;


–dab
–分配权限/角色 用角色管理权限
grant connect,resource to wangwu


–撤销权限/角色
revoke resource from wangwu
–以上语句的执行需要sys用户


–创建表需要切换用户，用户使用的是自己的空间；使用哪个用户登录，表就创建在哪个用户的表空间中
–创建用户表
–如果表名或列名是数据库的关键字，在关键字两旁使用id
create table oa_users(
       user_id integer primary key,
       user_name varchar2(30) not null,
       age integer not null
);


–check 约束
alter table oa_users
add constraints CK_AGE check(age between 18 and 65);


–插入数据
insert into oa_users values(1001,'王五',17);


– 查询
select * from oa_users;


– 创建学生表
create table students(
       stuno char(10) primary key,
       stuname varchar2(30) not null,
       joindate date not null
);
insert into students values('jsj1001','张三','29-10月-2019');
–利用时间函数
insert into students values('jsj1002','李四',sysdate);
select * from students


–插入的数据通过查询得到
–复制一个表 scott
create table mydept as
select * from dept where 1 = 2


insert into mydept
select * from dept where deptno=10;


insert into mydept
select 20,dname,loc from dept;


select * from mydept for update


– 更新子查询
update mydept set (dname,loc)=(select dname,loc from mydept where deptno = 10) where deptno = 11


–连接符操作，列的合并 '-'拼接
select dname ||'-'||loc from mydept


–从员工表中查询所在的部门编号
–消除重复行的数据
select dname from dept where deptno in (select distinct deptno from emp )
select emp1.deptno,dept.dname from (select distinct deptno from emp) emp1,dept where dept.deptno = emp1.deptno


–简单子查询 >= > < <= = !=
–复杂子查询
–批量删除 sql
–把要删除的主键值存入数组，集合
delete from mydept where deptno in (10,20);


–查询各个部门的员工信息
–子查询
select * from emp where sal > (select sal from emp where ename = 'SCOTT')


–联合查询 把多条select的查询结果整合到一个查询结果中
–条件:1.列的数量相同
–     2.列的类型和长度要对应
select deptno,dname from dept union all select deptno,dname from mydept


select deptno,dname from dept union  select deptno,dname from mydept


–创建视图
create view view_emps as select ename,job,dname from emp inner join dept on emp.deptno = dept.deptno;
select * from view_emps

与Java连接

与MySQL的区别：

  1. jar包

  2. 驱动名:oracle.jdbc.driver.OracleDriver

  3. url

序列

  • 自动产生唯一数值
  • 是一个可共享的对象
  • 用于主键值的创建
  • 当序列驻留在缓存中时，可以有效地提高序列的访问效率
  • 序列数值的产生及存储与表无关

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
--序列的使用
--1.创建表
create table t1(
tid number(5,0) primary key,
tname varchar2(30) not null
);
create table t2(
tid number(5,0) primary key,
tname varchar2(30) not null
);
--创建序列
create sequence seq_id 
start with 10 --从10开始
increment by 2  --自增2
nomaxvalue   --没有最大值限制
nocycle   --不循环产生
cache 10;  --缓存10个


insert into t1 values(seq_id.nextval,'aaa');
insert into t2 values(seq_id.nextval,'bbb');


select * from t1;
select * from t2;


select seq_id.currval from dual; –最近产生的最后一个序列值，dual存放值
select * from user_sequences;    –查看所有序列的详细情况，sequences存放序列信息

索引

帮助数据库快速查询数据，相当于目录

当在表中创建主键(PRIMARY
KEY)\唯一键(UNIQUE)后，会自动创建主键索引\唯一键索引

创建索引的原则

  • 经常用于where子句或作为连接条件的列
  • 所含数据值范围比较大的列
  • 含有大量空值的列
  • 表比较大，但大部分查询返回的结果集小于其总记录的2%-4%
  • 频繁更新的表不建议用索引

索引的分类

  • 从创建的角度：
    • 自动索引
    • 手动索引
  • 从索引的存储结构：
    • B-Tree索引
    • Hash索引

数据结构

  • 堆栈
  • 队列
  • 二叉树
  • 平衡二叉树
    • 左右子树高度差的绝对值不能超过1，且左右子树都是平衡二叉树
  • B-树

PL/SQL编程

  • 基本语法

    BEGIN

    EXCEPTION

    LOOP

    END

  • 常量，变量

    • 声明变量：
    • 声明常量：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
--PL-SQL编程
declare
  v_name varchar2(30);
  --声明变量的数据类型和表中的某个列的类型一致
  V_job emp.job%type; 
begin
  -- 给变量赋值
  v_name:='王五';
--通过select给变量赋值，要保证查询结果的唯一性：添加查询条件
  select ename into v_name from emp where empno = 7788;
  select job into v_job from emp where empno = 7788;
    --合并编写
  select ename,job into v_name,v_job from emp where empno=7788;
  --输出变量的值
  dbms_output.put_line(v_name);
  dbms_output.put_line('job:'||'--------------------------------'||v_job);
end;

  1. sysdate 赋值当前时间

  2. val

  3. NVL(sal,0) 如果sal列为null，用0替代

    1
    		 
    select sal,job,nvl(comm,0)+sal as totalsal from emp;

  4. decode(列名,值1,执行1,值2,执行2,…..)

    相当于多个if

    1
    		 
    select ename,sal,decode(job,'SALESMAN','销售人员','CLERK','文职') from emp;

  5. &：输入

    1
2
3
4
5
6
7
8
9
10
11
12
    		 
    -- PL/SQL输入部门
declare
  v_dno dept.deptno%type;
  v_dname dept.dname%type;
  v_loc dept.loc%type;
begin
  -- 变量赋值为输入的值，&后面的名字随意
  v_dno:=&dno;
  v_dname:='&dname'; --字符串，日期类型需要加引号
  v_loc:='&dloc';
  insert into dept values(v_dno,v_dname,v_loc);
end;

  6. if

    if…then…;

    ​ else…;

    end if

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
    		 
    --输入员工编号和目前薪资，判断
--如果薪资<2500，加薪1000；否则加薪500；
declare 
  v_empno emp.empno%type;
  v_sal emp.sal%type;
begin
  v_empno:=&eno;
  v_sal:=&sal;
  if v_sal<2500
    then 
      update emp set sal = sal+1000 where empno = v_empno;
    else
      update emp set sal = sal + 500 where empno = v_empno;
  end if;
end;

  7. case 基本不用

    1
2
3
4
5
6
7
8
9
10
11
12
    		 
    --case when-then
declare 
  v_empno emp.empno%type;
  v_sal emp.sal%type;
begin
  v_empno:=&eno;
  v_sal:=&sal;
       case
       when v_sal < 2500 then update emp set sal = sal+1000 where empno = v_empno;
       when v_sal > 2500 then update emp set sal = sal + 500 where empno = v_empno;
       end case;
     end;

  8. loop

    1
2
3
4
5
6
7
8
9
10
11
    		 
    --loop循环
declare
  v_age number(3,0):=1;
  begin
    loop
      if v_age>=18 then exit;
      end if;
      v_age:=v_age+1;
    end loop;
    dbms_output.put_line('v_age='||v_age);
  end;

  9. while

    1
2
3
4
5
6
7
8
9
10
11
    		 
    --while
declare
v_num number(3,0):=1;
v_sum number(6,0):=0;
begin
  while v_num <=100 loop
    v_sum := v_sum+v_num;
    v_num := v_num+1;
    end loop;
    dbms_output.put_line('v_sum='||v_sum);
end;

  10. for

    1
2
3
4
5
6
7
8
9
    		 
    --for
declare
v_num number(4,0):=0;
begin
  for i in 1..100 loop
    v_num:=v_num+i;
  end loop;
  dbms_output.put_line('v_num='||v_num);
end;

存储过程

存储过程和函数以命名的数据库对象形式存储于数据库中

  • 创建存储过程

当定义存储过程的参数时，只能指定数据类型，不能指定数据长度

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
--计算部门员工个数的存储过程：参数 部门编号
create or replace procedure pro_countemp(dno emp.deptno%type)
is 
begin
  declare v_count number(3,0);
  begin
    select count(empno) into v_count from emp where deptno = dno;
    dbms_output.put_line(v_count);
  end;
end pro_countemp; --end后面的名字加不加都行
--调用存储过程
begin
  pro_countemp(10);
end;

输入输出参数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
--存储过程：完成业务功能处理，不能返回值
--存储过程的参数类型：in 输入参数--向存储过程内传递值
--                      out 输出参数--得到存储过程里的值
create or replace procedure pro_countemp2(dno in emp.deptno%type,empcount out number)
is 
begin
  declare v_count number(3,0);
  begin
    select count(empno) into v_count from emp where deptno = dno;
    dbms_output.put_line(v_count);
    empcount:=v_count;
  end;
end pro_countemp2;
--调用存储过程
declare v_count number(3,0):=0;
begin
  pro_countemp2(10,v_count);
  --输出v_count的值
  dbms_output.put_line(v_count);
end;

函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
--申明函数
create or replace function fun_countemp(dno emp.deptno%type)
return number
is
begin
  declare v_count number(3,0):=0;
  begin
          select count(empno) into v_count from emp where deptno = dno;
   return v_count;
   end;
end;
--调用函数
--声明接收返回值的变量
declare
  v_empCount number(3,0):=0;
  begin
    v_empCount:=fun_countemp(10);
    dbms_output.put_line(v_empCount);
  end;

常见的数据库系统函数

1.全局变量

@@DATEFIRST:返回当前设置的一周的第一天
@@ERROR:返回当前连接中最后执行sql语句的错误代码
@@IDENTITY:返回当前连接中作为最后一条insert或者select into
语句的结果插入的标识值（最后插入的标识值）
@@OPTIONS:返回通过使用set命令设置的选择的信息
@@REMSERVER:仅在存储过程中使用，返回调用存储过程的服务器的值
@@ROWCOUNT:返回最后一条语句所影响的行数
@@SERVERNAME:返回正在运行脚本的本地服务器的名称
@@TRANCOUNT:返回当前连接的活动事务数（实质是事务嵌套级别）
@@VERSION:返回当前的SQL SERVER安装版本以及日期，处理器和O/S体系结构

SCOPE_IDENTITY()类似@IDENTITY，但返回在当前会话和作用域中插入的最后一个标识，
SCOPE_IDENTITY() 只返回插入到当前作用域中的值；@@IDENTITY
不受限于特定的作用域。

2.检索错误的系统函数
ERROR_NUMBER():实际错误号，若在存储过程里，只在catch块中有效
ERROR_SEVERITY():错误级别
ERROR_STATE():位置标记
ERROR_PROCEDURE():使用不多因为他只与存储过程，函数和触发器相关。这提供了导致错误的存储过程的名称
ERROR_LINE():错误所在的行号
ERROR_MESSAGE():消息文本

3.日期和时间函数
（表示所需取的时段：原词-缩写（year-yy，month-mm,day-dd,hour-hh,minute-mi,second-ss）
）
datepart(hh,日期字段)獲取這個字段的小時
dateadd(yy,4,日期字段)将这个字段往后加四年（日期/时间值上加上日期单位间隔）可以增加需要處理的任意長的時間
eg:dateadd(yy,4,’2018-4-4’)—-结果是：2022-4-4 00:00:00:0000
datediff(month,日期字段1，日期字段2)返回两个时间之间的差值
eg:datediff(month,’2018-8-8’,’2018-12.9’)—结果是：4个月
getdate():返回当前日期时间

游标

游标是数据段的一个内存空间，存放select查询结果，可以对游标中的数据进行提取使用

分类：

  1. 显示游标：

    声明游标： 把select的结果存入对应的内存空间中

    打开游标：
    提取游标：提取游标中的数据
    关闭游标：2、隐式游标：系统自带游标

使用方式1：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
--声明游标
declare
  cursor cur_emp is select * from emp where deptno=30;
  --声明 cur_emp游标类型的一个变量
  c cur_emp%rowtype;
  --存放总薪资
  v_sum emp.sal%type:=0;
begin
--for遍历提取游标中的数据
--在循环中自动打开关闭游标
  for c in cur_emp loop
    dbms_output.put_line(c.empno||'====='||c.ename);
  end loop;
  dbms_output.put_line('30部门的总薪资是:'||v_sum);
end;

使用方式2：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
declare
  cursor cur_emp2 is select * from emp where deptno=30;
  c cur_emp2%rowtype;
  v_sum emp.sal%type;
begin
--打开游标
  open cur_emp2;
  loop
  --提取游标中的数据，每次提取一行
  fetch cur_emp2 into c;
  --循环结束条件
  exit when cur_emp2%notfound;
  v_sum:=v_sum+c.sal;
  end loop;
  --关闭游标
  close cur_emp2;
  dbms_output.put_line('30部门的总薪资是:'||v_sum);
end;
  • 游标的属性：

%notfound:fetch是否提取到数据,没有true;提取到false;

%found:与notfound相反

%rowcount:

%isopen:布尔值，游标是否已经打开

  • 动态游标

  • 游标参数