{{π型人才养成计划}}Oracle数据库

一．软件安装
二．软件介绍
1.现有文本文件存放数据缺陷
        1）程序复杂，效率低
2）无法体现类型  ， 无法做数据检查
3） 无法存储大量数据
4）增删改查不方便
2. 数据库： 是一个数据存储管理的软件，全称rdbms（关系数据库管理系统），简称
            数据库（db）
   常用数据软件：oracle  mysql   sqlsv   db2
    3. 常见基本概念
       1）表（table）：实际负责数据存储的单元，通常也叫做“实体集”
       2）行（row）：存放了一组相关业务数据，通常叫做“实体”
       3）列（column）：表示业务数据中的一个具体的属性，也成为“字段”
           主键（primary key）：可以唯一标识一组业务数据，非空，唯一
           外键（foreign key）：体现两张表数据关系的字段，值必须取自另一个表的主键，
可以为null，可以重复
4. oracle工作方式
  1）安装成功后，先解锁hr账户
     ① sqlplus sys as sysdba    ② 输入管理员密码
     ③ 解锁hr： alter user hr account unlock;
     ④ 设置hr的密码： alter user hr identified by 密码;
  2) 工作方式

 5. 命令区别
1）sql命令 ： 结构化的查询语言（重点），不可以缩写，不区分大小写，必须以；结
尾
2）plsql命令： oracle数据库方言
3）sqlplus命令：工具自身的命令（可以缩写）
     start --- sqlplus命令，执行sql文件  
     desc --- 描述表结构

三．Sql命令 --- 查询命令

--- 请查询员工的编号，姓名，工资和所在部门编号
    Select employee_id,last_name,salary,department_id from employees;

--- 请查询员工的详细信息 （通过*表示所有字段）
    Select * from employees;

--- 可以对查询的字段做数学运算
--- 请查询员工的编号，姓名，以及年薪
    Select employee_id,last_name,salary*12 from employees;
--- 注意：
1）数字类型+-*/    
2）字符串类型不能做任何数学运算  
3）日期类型值可以加减，以“天”为单位

--- 可以为结果里的字段定义“别名”
    语法：紧跟在列之后空格接着写，如果含有特殊字符或者需要区分大小写，使用双
引号”
        --- select employee_id id, last_name, salary*12 “anual salary”from employees;

--- 字符串连接操作 ：  || 
    ---请显示员工编号，姓名，以及完整邮箱地址
       Select employee_id,last_name,email||’@zparkhr.com.cn’from employees;
    注意：如果在sql命令里使用到了字符串字面值或者日期字面值，需要使用单引号

--- distinct 去除查询结果里的重复数据
   --- 请显示我们公司里所有的职位 job_id 
       Select distinct job_id from employees;

四．排序 – order by

--- 请打印员工详细信息，并按照工资排序
   Select * from employees order by salary ;
--- 排序条件可以通过字段名，别名，或者下标指定
   Select employee_id,last_name,salary*12 from employees order by 3;
--- 多个排序条件
   Select * from employees order by salary,department_id;  
先按工资升序，工资相同在按部门升序排列
       Select * from employees order by salary,department_id desc; 先升再降
       Select * from employees order by salary desc , department_id desc; 
--- 关于null值 ： oracle中null被认定为最大值

五．条件查询（where） ---  逐行筛选

1.等值条件判断    =   !=    【重点】
--- 请查询30部门的员工信息
  Select * from employees where department_id=30;
--- 请查询last_name叫做‘King’的员工信息
  Select * from employees where last_name = ‘King’ ;
  注意：字符串字面值区分大小写

2.逻辑判断   >   >=  <   <=   and   or  !    【重点】
--- 请查询工资介于5000到10000的员工信息
    Select * from employees where salary>=5000 and salary<=10000 ;
--- select * from employees where last_name>=’King’;

3.多条件值查询（枚举） --- in     not in   【了解】
--- 请查询30,50,70部门的员工信息
Select * from employees where department_id=30 or department_id=50 or …
Select * from employees where department_id in(30,50,70);

4.区间查询 --- between …and     not between…and   【了解】
--- 请查询工资介于5000到10000的员工信息
    Select * from employees where salary between 5000 and 10000;  包括边界值
    
5.关于null值查询  is null      is not null
--- 请查询部门编号为null的员工信息
Select * from employees where department_id is null;

6.模糊查询  ----  like    not like

--- 请查询last_name是以‘K’开头的员工信息
   Select * from employees where last_name like ‘K%’;
--- 请查询last_name是由四个字符组成的员工信息
   Select * from employees where last_name like ‘____’;
--- 请查询last_name是以’S_’开头的员工信息

Escape 声明转义字符

六．oracle提供的函数（单行函数   组函数）

1.单行函数 ： 作用于表里的一行数据，产生一个结果的函数
1）abs函数 --- 计算一个数据的绝对值
   --- select abs(salary) from employees;    --- 107行
   --- select abs(-126) from employees;    ----107行
2）dual ---哑表，oracle提供的单行单列表，为了维护select语句的完整性，里面的
数据没有任何意义，也不允许向里面存放数据
          --- select abs(-126) from dual;   --- 1行
       3）sysdate --- 获取db sv里的当前系统日期
          --- select sysdate from dual;
          --- 数据库默认日期格式  dd-mon-rr 
       4) to_char( date日期值 ，‘格式字符串’)   【重点】
作用 ： 将给定的日期值按照格式字符串的要求转换成字符串类型值
--- 请显示详细的当前系统时间
   Select to_char(sysdate,’yyyy-mm-dd,day,hh24:mi:ss’) from dual;
--- 请显示2005年入职的员工信息
   Select * from employees where hire_date like ‘%05’;
   Select * from employees where to_char( hire_date, ‘yyyy’)=2005;
    --- 按照入职月份升序的方式显示员工信息
       Select * from employees order by to_char(hire_date,’mm’);
  5) to_date(string,’格式字符串’)
 将给定的string按照格式字符串的要求变成数据库支持的日期值
 --- 请写一句sql命令，查询你出生的那天是星期几   1998-06-13
 To_char( to_date(‘1998-06-13’,’yyyy-mm-dd’)  , ‘day’)

2.组函数：作用于一组数据执行一次产生一个结果（表是一个超级大组）
1）max() , min()  ： 最大，最小值 ， 适用于所有类型的数据
2）avg() : 求平均值 ，适用于数字类型字段
3）sum() : 求和， 适用于数字类型字段
4）count() : 统计数量
      注意： 所有组函数在统计时，null值都不在统计范畴
      --- select max(salary),min(salary),avg(salary),sum(salary) from employees;
      --- select max(salary) from employees where department_id=30;
      --- count(字段名)  ： 统计指定字段里非空值的数量
      --- count(*) : 统计结果中行的数量
      --- 统计30部门的人数
          Select count(*) from employees where department_id=30;
 --- 请统计公司里有多少种职位
     Select  count( distinct job_id)  from employees;

一．分组语句 group by

--- group by last_name ( 姓一样的同学被分到一组)
--- group by last_name，first_name( 只有姓和名完全一样的同学被分到一组)

--- 请查询各部门最高工资
   思路：
1.确定数据来源表：  from employees   107行
2.数据筛选 ：     没有               107行
3.确定分组条件： group by department_id   
4.统计数据：     select department_id, max(salary) 
       合并：
         Select department_id,max(salary) from employees group by department_id ;

--- 请统计1997年各部门的入职员工人数
  思路：
1.from employees      107行
2.where to_char(hire_date,‘yyyy’)=1997      29行
3.group by department_id
4.select department_id, count(*) 
      合并：
        Select department_id，count(*) from employees 
where to_char(hire_date,’yyyy’)=1997 group by department_id;

— 请统计1997年各个月入职的员工人数
1.from employees
2.where to_char(hire_date,’yyyy’)=1997
3.group by to_char(hire_date,’mm’)
4.select to_char(hire_date,’mm’),count(*)

二．分组后数据筛选 ： having

--- 请查询各部门人数，只显示人数大于2的信息
        Select department_id,count(*) From employees 
group by department_id having count(*)>2

--- 请查询30,50部门的最高工资
1.Select department_id,max(salary) from employees where department_id in(30,50)
Group by department_id; 
2.Select department_id,max(salary) from employees group by department_id
Having department_id in(30,50)

三．select六条子句总结

四．伪列 
1.数据库表里不存在的，通过select * 无法查询到的列 --- rownum  rowid
Select * , rownum , rowid from employees;   -- error
解决
Select  employees.* , rowid , rownum from employees; 
Select  e.*,rowid,rownum from employees e ;

2.Rowid : 是一条记录在数据库里的唯一标识，是对记录所在空间的物理地址运算得到

3.Rownum：数据库服务器会为每一个出现在查询结果里的，满足要求的记录进行编号，
从1开始编号
      --- 请打印员工表的前五行数据
          Select * from employees where rownum<=5;
      --- 请打印员工表的第6到第10行数据
          Select * from employees where rownum between 6 and 10;      error

      注意： rownum必须从1开始使用（<   <=    =1  >=1   between 1 and … ） 

五．子查询
1.概念 ：
 -- 请查询公司里工资最高的员工信息
-- 1. 查询最高工资是多少   result
   select max(salary) from employees ;  
-- 2. 查询员工信息，工资等于result
   select * from employees where salary = result ;
-- 合并
   select * from employees 
where salary = ( select max(salary) from employees );
       概念：在一个查询命令执行过程中，嵌入另外一个查询语句（子查询）
       作用：提供主查询需要的数据

2.子查询结果为单行单列 --- 一个值  【重点】
      -- 请查询公司里工资高于平均工资的员工信息
-- 1. select avg(salary) from employees;  result
-- 2. select * from employees where salary > result ;
select * from employees
where salary > ( select avg(salary) from employees) ;

3.子查询的结果为多行一列 ---- 多个值 【了解】
-- 请查询与‘King’在同一部门工作的员工信息
-- 1. 查询King所在的部门编号
select department_id from employees where last_name='King';  
80和90
-- 2. 查询在 80和90部门工作的员工
select * from employees where department_id in (select department_id from employees where last_name='King');

4.子查询结果是多行多列 --- 一张表（虚拟表）【重点】
作用：可以针对虚拟表做二次查询
--请查询员工表里工资最高的五名员工信息
-- 1. 对员工表数据做排序 按照工资降序排列
        select * from employees order by salary desc;  --- result 虚拟表
-- 2. 对结果数据筛选 where rownum<=5
select * from (select * from employees order by salary desc) where rownum<=5
   
     数据分页：对满足要求的数据分段显示【重点】
1）显示的数据不需要排序
-- 打印表里的第六到第10名员工信息
-- 1.显示表里的前10行数据
  select e.*,rownum rn from employees e where rownum<=10
-- 2. 利用虚拟表里的普通字段rownum进行数据查询
          select * 
from (select e.*,rownum rn from employees e where rownum<=10) where rn between 6 and 10;
          总结 – 查询第m到第n行数据
          第一步：先查询前n行数据，同时生成rn字段（rownum的别名）、
          第二步：根据rn字段获取需要的数据

2）显示的数据需要排序
开发步骤---查询第m到第n行数据
第一步：先对表里的数据按要求排序  tab1
第二步：针对tab1先查询前n行数据，同时生成rn字段（rownum的别名）、
第三步：根据rn字段获取需要的数据
         -- 打印工资最高的第六到第10名员工信息
-- 1. 先排序
 		select * from employees order by salary desc   -- t1
-- 2. 查询前n行，生成rn
   		select t1.*,rownum rn from t1 where rownum<=10  -- t2
-- 3. 根据rn获取需要的数据
   		select * from t2 where rn between 6 and 10;
-- 合并
   		select * 
   		from (  select t1.*,rownum rn 
           		from (select * from employees order by salary desc) t1 
           		where rownum<=10 ) 
  		where rn between 6 and 10;

5.关联子查询【 了解 】
--- 打印各部门工资最高的员工信息

   Select * from employees e1 where salary = (select max(salary) from employees e2
         Where e2.department_id= e1.department_id ) ;
   难点：
1)Where是逐行判断
2)表的别名可以标志查询语句

一、表连接

-- 请查询员工的编号，姓名，工资，以及所在部门的名称
select employee_id,last_name,salary,department_name
from employees , departments 
where employees.department_id = departments.department_id;

1. 概念

当查询结果数据来自于多两张表时，需要使用特定的连接条件将两张表的记录连接在一起，这种语法“表链接”

2. 分类

  内连接     外连接     自连接     多表连接（语法）  
distinct

3. 内连接

语法：使用关键字inner join连接两张表，其中inner可以省略，使用关键字on给定连接条件，其他判定条件仍

然使用where指定

-- 请查询60部门的员工编号，姓名，工资，以及部门名称
select e.employee_id,e.last_name,e.salary,d.department_name
from employees e inner join  departments d
on  e.department_id = d.department_id 
where e.department_id = 60 ;

特点：只考虑连接条件成立的记录，对于连接条件出现null的记录直接舍弃，不出现在结果中

4. 外连接

特点：可以处理连接条件为null的记录

分类：左外连接【重点】 右外连接 全外连接

• 左外连接：使用关键字left outer join连接两张表，其中outer可以省略 ，连接条件使用on给定

  在表连接过程中，以左表为主（左表记录全部出现），右表辅助

• ​ 右外连接：使用关键字right outer join连接，outer可以省略，连接条件用on给定

  ​ 在表连接过程中以右表为主，左表辅助

• ​ 全外连接：使用关键字full outer join连接，outer可以省略，两张表的记录全部出现

  -- 请查询员工表的所有信息，以及对应的部门信息
  -- 左外
     select e.*,d.*
     from employees e left join departments d 
     on e.department_id = d.department_id; 
  -- 右外
     select e.*,d.*
     from departments d right join employees e 
     on e.department_id = d.department_id ;
  -- 全外
     select e.*,d.*
     from employees e full join departments d 
     on e.department_id = d.department_id ；
  

5. 自连接

-- 请打印员工的姓名，以及他上司的姓名
select  e.last_name, m.last_name
from employees e left  join  employees m 
on  e.manager_id = m.employee_id ;

概念：是一种特殊的表连接，通过为一张表定义两个别名的方式，完成表连接查询过程

6. 多表连接（语法）

-- 请查询员工的姓名，所在部门的名称，以及所在的城市
select e.last_name,d.department_name,lo.city
from employees e left join departments d
on e.department_id = d.department_id 
left join locations lo
on d.location_id = lo.location_id;

二. 建表

1. 合法标识符

• 由 字母 、数字、_、$、# 组成 ， 其中数字不能开始
• 不区分大小写，长度控制在30个字符
• 不能是关键字，不能与数据库当前用户下的其他对象命名冲突

2. 数据类型

• 数字类型（不区分整数和浮点类型）

  1) number( v1, v2 )  ---  有效位数为v1，其中小数点后占v2位， v1最大可取值为38
  ​         number(7,2) --- 表示的最大数字 99999.99
  2) number（v1)  ---- 一个整数
  3) number  ----  表示一个很大的浮点数 ， 类似于java里的double
  

• 字符串类型（不区分字符和字符串）

  1)  varchar2( n ) : 可变长字符串，最大容量n个字节，根据实际值的长度调整存储空间，n的最大取值4000
  2)  char(n) ： 定长字符串，长度n个字节，使用固定长度存储数据，n的最大取值为4000
  3) nvarchar2(n) : 可变长字符串，最大容量是n个字符，n最大取值为1333
  

• 日期类型

  1)  date :  标准日期格式  dd-mon-rr ，可以精确到秒       sysdate
  2)  timestamp ： 时间戳  ， 可以精确到10负六次方秒     systimestamp
  

• 大数据类型

  1. blob : 二进制大对象， 最大空间4G
  2. clob : 字符大对象 ， 最大空间 4G

3. 约束

• 主键约束：使用关键字 primary key 定义， 特点是非空唯一 ，简称pk

• 非空约束： 使用关键字not null定义 ， 特点是字段必须有数据，简称nn

• 唯一约束：使用关键字unique定义，当前字段里的值不重复（有值时，值不重复），简称uk唯一键

• 检查约束（自定义约束）：使用关键字check定义，简称ck

• 外键约束：使用关键字 references 表名（字段名）定义 ， 简称fk，特点值必须来自于关联表的主键

  -- team（id ，name ，amount）
  create table team(               父表
     id number(3) primary key,
     name varchar2(15) not null,
     amount number(2)
  );
  -- student（id ，name，mobile,email，study_date，married，tid）
  create table student(                    子表
     id number(5) primary key,
     name varchar2(15) not null,
     mobile char(11) not null，
     email varchar2(30) unique check( email like '%_@_%'),
     study_date date default sysdate,
     married number(1) default 0,
     tid number(3) references team(id)
  );
  

4. 特殊约束的定义（联合键）

三. 增删改数据

1. 添加数据 insert

• 全表插入（为表里所有的字段插入数据）

  语法： insert into 表名 values( 值1，值2，… )

  要求：值的个数，类型，顺序必须与表里字段完全一致

  --请向team表插入一条数据
  insert into team values(1,'超神',8);
  --请向学生表里插入一条数据
  insert into student values(1,'zhangsan','12345678909','zhangsan@123.com',sysdate,0);
  

• 选择插入

  语法：insert into 表名（列1，列2，…） values(值1，值2，…)

  要求： 值的个数，类型，顺序必须与前面小括号里的选择一致

  对于表里非空，并且没有默认值的字段，必须入选

  ~~~sql
  

  –请向student表里有选择的插入一条数据
  insert into student(id,name,mobile) values(3,‘wangwu’,‘90876543212’)；
  ~~~

2. 修改数据 update

语法： update 表名 set 字段名=新值，字段名=新值 where … ;

  update student set email='wangwu@123.com',tid=1 where id=3;

3. 删除数据 delete

语法： delete from 表名 where 条件 ;

~~~sql

delete from student where id=3； ok
delete from team where id=1; 不成功
~~~

四. 关于删除功能的补充

1. 关于数据的删除

​ 1） delete 删除数据 ： 直接清空表里的数据，保留数据所占空间

​ 2） truncate命令 ： 截断表里的数据，数据被删除，同时数据所占空间被释放，只保留表头

语法： truncate table 表名 ;

​ 3） drop 命令： 删除表（ 数据 和 结构 ）

语法： drop table 表名 【cascade constraint 】;

2. 关于关联表的删除

​ 1） 有外键指向的两个表创建时： 先父后子

​ 2）删除动作 ： 先子后父

• ​ 希望删除父表数据 — 先删除子表数据，在删除自己 ； 或者先清空子表相应的外键，在删除自己

• ​ 希望删除父表 — drop table 表名 cascade constraint;

  ​ cascade constraint : 级联操作子表的外键约束，在删除父表的同时，将子表里的外键约束删

五. SQL命令的分类

1. DQL ： 数据查询语言，完成数据的查询操作 （ select） 【重点】

2. DML ：数据操纵语言 ，完成对数据的访问操作（insert， update，delete）【重点】

3. DDL ：数据定义语言，用来操作数据库里对象的命令

（create【重点】 drop truncate alter rename）

4. DCL ：数据控制语言，权限管理的命令 （ grant revoke）

5. TCL ：transaction control language 事务控制语言 【重点 *****】

（commit 提交 rollback 回滚）

一、事务（transaction）

1. 概念

​

  是操作数据库的最小单位，是由一组不可再分的sql命令组成的集合，事务的大小取决于实际业务的难易程度

2.实现原理

数据库服务器会为每一个连接上来的client，开辟一小块内存空间（回滚段），用来暂时保存sql命令的执行结果，在事务没有结束之前，回滚段里的数据只对当前client可见，当事务结束时，需要client明确处理回滚段中的数据。 事务成功则发出commit指令，数据库会将回滚段的数据写入数据文件；失败则发出rollback，db会将回滚段数据清空。

3. 事务边界

1. begin：执行第一条sql命令时，事务被开启

2. end：

​

      1) 执行了一组DML（insert update delete）命令，都需要手动输入commit或者rollback来结束事务

​      2) 执行了一条DDL 或者 DCL 命令，命令后自带commit，执行成功事务直接结束

​      3) 执行了一组DML命令后，断开client连接，正常退出做commit，非正常退出做rollback

-- 案例：   insert  update  commit  delete  update  create  insert  update  exit
-- 结论：  含有三个事务，其中第二个事务的commit是create命令自带的，delete和update结果随着create的
--        commit一起被写入数据库

4. 数据安全

1) 数据库服务器为表里的每一行数据提供一把“锁”，称之为“行级锁”

2) 写锁 ： 是因为事务对记录做了（insert update delete）添加了“行级锁”

​             当前事务对表里的记录做了增删改动作之后，数据库服务器会将事务操作过的记录加“行级锁”，锁没有 

​             打开之前，其他事务不能在对该条记录做DML命令。当事务结束时，行级写锁会自动打开释放，随机分配

​             给其他等待这个锁标记的client。

3) 查询功能不受写锁控制（查到的内容是数据库里的原始数据）

4) 读锁： 可以在查询过程中对记录加锁（手动完成）

​

      select * from xxx for update ;      得到所有查询结果 并 对结果里的行加锁 

​          select * from xxx for update nowait；  查询数据同时获取记录的锁，如果无法成功立刻返回

​         select * from xxx for update wait n ;      获取锁时可以等待n秒，如果n秒后无法获取立刻返回

5. 事务特性（ACID）

​

  1） A（atomic）：原子性，构成事务的这组sql命令是一个整体，不可再分，一起成功或者失败

​     2）C（consitancy）：一致性，事务进行过程中，数据的状态保持一致

​     3） I（isolation）： 隔离性，多个事务并发运行时，不能彼此互相影响

​     4）D（durable）：持久性，事务结束时必须对数据做持久化操作（commit，rollback）

二、主键的自动生成策略

1. max函数生成

​

 1） 第一步：通过max函数查询表里的最大主键值 select  max(id) from xxx ;   ---result

​    2）第二步：对max(id)+1 生成新的主键插入到表里 insert into xxx values( result+1,  ......)

​    3）缺陷： 多个用户并发访问时，还是有可能出现主键冲突

2. 通过系统提供的函数生成主键

​    1）sys_guid() :  会返回一个128bit的数字，注意需要将之间字段的数据类型改为number

​    2）dbms_random.random() : 可以返回一个随机整数（可能正，可能负）

3. 通过自定义表来生成主键

三. 序列（sequence）

​    1）作用： 是一种oracle数据库对象，可以生成一系列唯一的数值，用来作为主键插入表

​    2）创建：

​

3）使用

​         通过序列提供的变量 nextval 来获取序列里的值

​         ① 查看 ： select  seq1.nextval  from  dual; 

​         ② 插入 ： insert into test1 values( seq1.nextval , 'aaa' );

   4） 删除

​          drop sequence 序列名 ； 

四、视图（view）

1. 概念：

​        一个起了名字的查询语句（只存储命令，不存储数据）

2. 创建

​

        create view  视图名  as   查询语句 ;

​        访问使用视图，相当于在调用视图里存储的查询语句 。 

3. 作用：

​ 1）简化查询

create view v1 as  select * from employees order by salary desc;

-- 查询工资最高的前五名员工
select  *
from v1
where rownum <=5;

select *
from (  select v1.*,rownum rn from v1 where rownum<=10 ) t2
where rn between 6 and 10;

​ 2）帮助权限控制

  create view  emp as select employee_id,last_name,...,department_id from employees；
  create view boss as select * from employees;
  -- 取消employees表的访问权限  

4. 删除视图

​ drop view v1;

五、索引（index）

1. 组成

​

      是一个两列多行的表格，第一列一般是对字段值的排序，第二列一般是这个值所在的地址。

​       案例： 针对name字段添加索引   （ 对name字段的所有值排序，rowid）

2. 作用

​       提高查询效率（db sv 会自动为表里的 pk 和  uk 建立索引）

3. 创建

​          create index  索引名  on  表（字段）;

4. 索引时间开销

​

    索引不是越多越好

​     1）如果查询的结果记录比较多，不适合用索引

​     2）频繁增删改的数据库表不适合建立过多的索引

​