Oracle笔记

Oracle笔记

​ Oracle数据库

​ 1、什么是数据库？

​ 数据的存储。

​ 2、Oracle数据库？

​ 关系型数据库，使用二维表完成数据的存储。

​ 关系型：表结构 —> 行： 一个数据对象 列：数据的属性

​ 3、数据库的分类？

​ 关系型： Oracle DB2 MySQL SQLServer

​ 非关系型的NoSQL： Redis key-value、MongoDB 文档型/BSON 、HBase 文件…

安装

    1、不建议使用中文路径、避免特殊字符
    2、登陆
    	使用网页  localhost:8080/apex     IE + http://
    	CMD窗口  sqlplus  注意：环境变量是否配置成功
    	
    	用户名/密码  system/口令
    	建议使用oracle中的子用户： 权限更小     hr用户  需要解锁  参考笔记
    	
    3、使用PLSQL工具    安装路径不准出现中文/特殊字符（X86）
      
    验证
       hr用户    查询语句 ----> 107行结果
       select  *   from  employees；

一、SQL命令

DQL 数据的查询语言

1.简单查询

select  数据（列）  from 表    不区分大小写！！ 建议大写。

1、查询所有数据
	select * from employees;
	
2、具体的列名
	select 列名1，列名2 from 表名；
	
3、支持简单的运算  + - * /
	select employee_id,first_name,salary*12 from employees;
	
4、列别名 as 关键字    可以省略
	select employee_id,first_name,salary*12 （as） yearSalary from employees;
	
5、列的合并   ||
	select first_name||last_name as name from employees;
	
	select salary||1*10 as name from employees;
	
6、去重
	select distinct salary from employees;	

2.条件查询 where

1、 比较查询 >  <   =  !=  >=  <=
	select * from employees where employee_id ！= 100;
	select * from employees where employee_id >= 100;
	
2、 逻辑查询   and  or  not
	--查询工资为 24000 或者 17000的员工
	select * from employees where salary = 24000 or salary = 17000
	--查询工资为17000 和 编号为 102 的员工
	select * from employees where salary = 17000 and employee_id = 101;
	注意： 一列中的多个条件用 or    一行中的用 and
	-- 不等于 100
	select * from employees where not employee_id = 100;

3、谓词
	枚举 in     类似 or
		查询工资 等于 24000 17000 10000 9000 的员工信息
        select * from employees
        where salary in(24000,17000,10000,9000);
	
	区间 between...and...    相当于>= 和 <=
		select * from employees
		where salary between 10000 and 24000;
	
	模糊 like
		--查询first_name 以 S 开头的数据
        select * from employees
        where first_name like 'S%';
        --查询first_name 以 S 开头的且总长度为5的数据
        select * from employees
        where first_name like 'S____';
        
        select * from employees
		where first_name like '%a_e%';
        
        % 代表了 占位符 0~N   _ 标识一位

	空值 null   是空 is null
		select * from employees
		where commission_pct is null;
				非空 is not null
		select * from employees
		where commission_pct is not null;

3. 排序 order by

对于查询结果进行排序
    -- 排序  默认升序  按照提成排序
    select * 
    from employees
    where commission_pct is not null
    order by  commission_pct asc;
    --降序  desc
    select * 
    from employees
    where commission_pct is not null
    order by  commission_pct desc;
    
    -- 多列排序 按照提成 以及工资
    select * 
    from employees
    where commission_pct is not null
    order by  commission_pct desc，salary desc/asc；

4.函数

1. 内置函数  日期相关
	sysdate
	--函数  from 只是为了满足语法正确
    --dual 虚表 / 哑表   只是为了满足语法正确 一行一列的表
    select * from dual;
    select sysdate from dual;
    
    -- 进行数据类型的转换     String  <--->  Date
    to_char（）；
    to_date（）；
    --应用在数据的插入
    -- 把 String --->  Date
    -- to_date('String字符串','日期格式');
    select to_date('2000-01-01','yyyy-MM-dd') from dual;

    --查询的展示
    --to_char   Date ----> String
    --to_char(日期 ,'最终转换的数据格式')
    select to_char(sysdate,'yyyy-mm-dd  day') from dual;
    --java   yyyy-MM-dd HH:mm:ss
    --数据库 yyyy-mm-dd hh:mi:ss
    
2. 组函数   针对一组数据的操作
		max() 最大值   min() 最小值
		avg() 平均     sum() 求和
		count() 计数
		--最大工资 最小工资
        --列中的所有数据    一组数据的操作
        select max(salary) from employees;
        select min(salary) from employees;
        select avg(salary) from employees;
        --所有 *
        select count(*) from employees;
        --commission_pct  非空数据的统计
        select count(commission_pct) from employees;

5.分组 group by

--查询每个部门的员工数     
   --将所有的员工 按照部门编号进行分组
  	select department_id,count(*) from employees
    group by department_id;
    
    
--查询每个部门中每个工种的最大工资
	--查询最大工资 按照部门和工种进行分组操作
	select department_id,job_id,max(salary) from employees
	group by department_id,job_id;
	
	
注意：
	1、如果列不存在 group by 表达式之后，则无法书写在 select 关键字之后进行数据的展示
        为什么？
           分组条件 部门ID/Job_ID 展示的结果一定是唯一值。
           select first_name  有可能会有两条数据  部门ID/Job_ID/最大工资完全相同 则姓名不同，
           可能会在一行一列的数据格中出现多个选项。
	2、组函数为什么可以书写在 select 关键字之后？
		 组函数对于一组数据进行操作，结果一定是单值唯一数据。
	3、内置函数如果应用在 group by的条件中 也可以书写在 select 关键字之后
	
-- 1997 年每个月入职的员工数

--求 1997 年   再 按照月份分组
  select  to_char(hire_date,'mm'),count(*)  from employees
  where to_char(hire_date,'yyyy') = 1997
  group by to_char(hire_date,'mm');

6. having判断

分组之后的条件再判断

-- 查询平均工资大于5000的部门ID       -- 查询部门的平均工资且大于5000
    -- 部门的平均工资
	select department_id,avg(salary) from employees
	group by department_id;
	--再判断 大于 5000
  	select department_id,avg(salary) from employees
	group by department_id
  	having avg(salary)>5000;
  	
  
----查询1997年 每个月入职的员工数大于2人的月份以及数量 按降序排序。
	--1、 获取1997年的员工
	--2、 按照月份分组
	--3、 判断人数大于2
	--4、 排序
      select to_char(hire_date,'mm'),count(*) from employees
      where to_char(hire_date,'yyyy') = 1997
      group by to_char(hire_date,'mm')
      having count(*)>2
      order by count(*) desc;
  
  

关键字？？？   SQL命令未正确结束   SQL语句语法关键字书写顺序错误
select   from   (表存在表连接)    where   group by   having      order by
查询     数据来自于                条件是    进行分组    在次筛选判断   排序

---

Day2

一、子查询

一条SQL语句去嵌套另外一条SQL语句

1、where子查询

一条SQL语句的执行结果是另外一条SQL语句的判断条件

-- 获取110号员工的部门名称
SELECT DEPARTMENT_NAME
  FROM DEPARTMENTS
 WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 110);


select department_name from departments
where department_id = （SQL语句）;

--值过多： 一行中有多余的列信息
--单行子查询返回多个行：一列中存在多个行数据

--获取 first_name 为 Steven 的员工的部门名称

select department_name from departments
where department_id in (select department_id from employees
						where first_name ='Steven')

2、from子查询

表 实际是一条SQL语句的执行结果

--结果表
-- 求 年薪 大于 10 万的员工信息
select * from
 (select first_name,salary*12 yearSalary from employees)
where yearSalary > 100000;

存在数据查询的嵌套
	内部SQL语句的执行结果列（进行了运算等操作非原始数据）一定需要别名（外部的列进行的使用）

二、伪列 — Oracle独有

1、rowid

数据库进行数据存储时，会创建一个伪列rowid用来唯一标识一行数据在数据库中的存储位置。
如果不主动发起查询则不会进行数据的展示。

    --rowid 唯一
    select rowid from employees;
    select * from employees
    where rowid='AAAC9EAAEAAAABXABD';

重点：表别名！！！
    --展示表中所有的数据以及rowid
    --表别名   注意：没有as关键字
    select rowid,e.* from employees e;

2. rownum

对于查询结果符合条件的数据进行编号

分页
  rownum在进行编号的同时 需要结果数据满足条件判断
  > >=非1 =非1 数据 永远不会满足条件！！！
  需要对于查询结果预先编号  后执行条件判断  使用from子查询
  
  select * from
  		（select rownum,e.* from employees e）
  where rownum >10
  -- 依然没有结果
  
  rownum作为伪列 每一条SQL语句都拥有属于自己的rownum
  where中使用的rownum 是外层SQL的结果，明确使用内层预先编号的rownum进行条件的判断
  -- 起别名
  
  select * from
  		（select rownum r,e.* from employees e）
  where r >10;
  
  --MySQL
  select * from 表 where 条件 limit（1,5）;     1 起始数据  5 取5条数据（到6结束）

查询工资排名3~5名（降序）的员工的employee_id、 first_name 和 salary

– SQL 预期结果 和 实际执行结果
– 有结果只能说明语法没错！！！！不代表你写对了！！！
select t2.* from
(select t1.*,rownum r from
(select employee_id,first_name,salary from employees
order by salary desc)t1) t2
where r between 3 and 5;

三、表连接

员工表 employees 部门表 departments

​ 需求：展示员工的ID、first_name以及部门名称

​ 表和表之间的数据是否存在对应关系（关联关系）

主键 / 外键

主键：表中对于一行数据的唯一标识。 （唯一、非空）

外键：表示多张表之间数据的关联关系 （外键列的数据一定来源于另外一张表的唯一标识）

语法：  关键字
	（）代表省略
	1、 （内）连接   （inner） join ...表 on ...（连接条件 关联关系）
		select e.*,d.* from employees e
		inner join departments d
		on e.department_id = d.department_id
	
	2、左（外）连接   left （outer） join ...表 on ...（连接条件 关联关系）
		--左外链接 107
        select e.employee_id,e.first_name,e.department_id,
               d.department_id,d.department_name 
        from employees e left join departments d
        on e.department_id = d.department_id
        
    3、右（外）连接    right （outer） join ...表 on ...（连接条件 关联关系）
    	--右外链接 122
        select e.employee_id,e.first_name,e.department_id,
               d.department_id,d.department_name 
        from employees e
        right join departments d
        on e.department_id = d.department_id
       
     注意： 左右只是相对关键字的位置
        
    4、全外 了解  123   full outer join ...（表） on ...（连接条件 关联关系）
    
    
    条件判断
     需求：展示工资大于10000的员工的ID、first_name以及部门名称
     	where salary > 10000
     	
     	select e.employee_id,e.first_name,e.department_id,
       		e.salary,d.department_id,d.department_name 
        from employees e
        join departments d
        on e.department_id = d.department_id
        where e.salary>10000




        select e.employee_id,e.first_name,e.department_id,
               e.salary,d.department_id,d.department_name 
        from (select * from employees where salary>10000) e
        join departments d
        on e.department_id = d.department_id

三张表怎么办？

 A B C 表名
     select * from A
     inner join B
     on  a = b
     inner join C
     on  b = c
     
--展示员工的 employee_id first_name department_name location_id street_address 、、、
    select 
            e.employee_id,e.first_name,d.department_id,
            d.department_name,d.location_id,l.street_address
    from employees e
    inner join  departments d
    	on e.department_id = d.department_id
    inner join locations l
    	on d.location_id = l.location_id
    	
   	 where
   	 group by
   	 having
   	 order by ...

自（自己）连接

关联是同一张表中的不同列，将一张表想象成两张数据完全相同的表。

-- 求 员工的 employee_id,first_name,manager_id 以及领导的first_name
-- 领导本质上也是员工 数据也保存在员工表中
-- 关联关系  on   manager_id  领导的ID   =  employee_id  员工的ID
-- employees 员工表
    select *
    from employees e1
    inner join employees e2
    on e1.manager_id = e2.employee_id

Day3

一、DDL 数据定义语言

数据库中 表的创建，删除（修改）

1、创建表

关键字
 	create table 名称（）
表：组成
	列 : 表可以存储的数据的信息  （列 名称/数据的类型）
	
	create table 名称(
    	列名  数据类型，
        列名  数据类型
    );

数据类型

1、数字类型

​ number(位数)

​ number(5) 五位整数 99999

​ number(5，2) 五位数,两位是小数 999.99

​ Integer 整数 相当于 number(38,0)

2、字符类型

​ char(位数) 定长

​ varchar(位数) 变长

​ 存储空间：

​ 定长— char(5) 无论数据的实际长度是多少，都会在存储数据的时候开辟5位的长度

​ 去存储数据,如果数据长度不够会补充空格。 存： ‘a’ 取：'a ’

​ 变长— 根据数据的实际长度进行存储空间的开辟

​ 中文不一定就是两个英文长度，取决于编码方式。

​ varchar2()!!!

​ Oracle独有的数据格式，在Oracle数据库中使用建议使用varchar2()!!!

3、日期类型

​ Date

    create table my_table(
           id number(32),
           name varchar2(64),
           age number(3), 
           sex char(2),
           birthday date
    );
    
    存在：约束 （可选项）
    
   	create table 名称(
    	列名  数据类型 【约束】，
        列名  数据类型 【约束】
    );

约束：

​ 1、主键约束

​ primary key —> 列中的数据一定是唯一且非空

​ 2、非空约束 not null

​ 3、唯一约束 unique

​ 4、检查约束（check） 了解

​ 对于存入数据的格式进行校验 (基本使用前端或者后端程序进行了处理)

​ check(email like ‘%@%’) / check(sex in(‘M’,‘F’))

​ 5、外键约束

​ references 表名(列名)

​ 外键数据来源于另外一张表的唯一约束（主键比较方便）

​ 外键需要建立在数据的多方（存在外键的表就称之为子表，被外键引用的表称之为父表）

        例子：班级和学生   部门和员工  用户和地址  分类和商品（结合生活常识）

​ 创建表：需要先创建父表再创建子表

​ 删除表：需要先删除子表再删除父表

​ 外键的参照完整性：外键列的数据一定要来源于另一张表的唯一标识且已经存在。

        create table my_table1(
               id number(32) primary key,
               name varchar2(64) not null,
               phone char(11) unique,
               email varchar2(64) ,
               age number(3), 
               sex char(2),
               birthday date
        );
        
        --学生表   班级表  关联关系
        create table clazz(
              cla_id integer primary key,
              cla_name varchar2(24)
        );

        create table student(
              id integer primary key,
              name varchar2(32),
              sex char(3),
              cla_id integer references clazz(cla_id)
        );      
        -- 记忆语法规范和关键字！！！ （后续实际工作中绝大多数都会使用专业的工具）

2、删除表

​ drop table 表名；

​ 注意：删除中需要先删除子表再删除父表。

​ 级联删除： 强制删除父表（外键的关联关系），保留子表。

​ drop table clazz cascade constraint; 不建议使用，使表中存在大量的无用数据

​ 不遵循外键的参照完整性约束。

3、修改表 alter … 基本没用

二、DML 数据的修改语言

增删改！！！！

注意 ： 需要执行commit提交或rollback回滚。

1、添加
	insert into 表名(列名，列名...) values(数据，数据....);
	
	简写：向表中所有的列添加数据
		insert into 表名 values(数据，数据....);
		注意：数据的顺序必须和列的顺序一致
2、删除
	delete from 表名      删除表中的所有数据。
	delete from 表名 where 条件。
	
	truncate table 表名；  删除表中的所有数据，但是不需要commit操作。
		表的删除再创建，效率高！！！

3、修改
	update 表名 set 列名=数据,列名=数据... where 条件
	一般不建议去修改主键！！！

三、TCL 事务控制语言

一种数据库机制，保证业务操作的完整性，要么一起彻底的成功，要么一起彻底的失败。

commit / rollback : 没有针对一条DML操作语句，范围从上一次commit/rollback到本次执行过程中所有的DML操作语句一起生效。

 转账（业务操作): 对于用户数据库余额数据的修改。
 	----> 多条SQL语句的共同执行结果   你的账户修改update（减钱） 目标账户修改update(加钱)
 多条DML操作语句共用一个commit或者rollback语句	
    转账(){
    	update    A -钱  -1000
    	//执行一半发生了未知错误 不可抗力
    	update    B +钱  +1000
    	COMMIT/ROLLBACK
    }
    
  当执行完DML操作后数据存入回滚段中，commit提交当前回滚段中所有的数据，rollback撤销回滚段中所有的数据。
  事务是保证多条SQL语句(DML操作)一起进行数据的提交或者回滚。

A : 原子性 — 业务操作（多条SQL语句），不可分割的原子操作。

C : 一致性 — 业务操作前后数据保持一致。

I : 隔离性 — 并发访问中操作者的事务相互独立

D : 持久性 — 操作完成后需要将数据保存入(持久化操作)数据库

补充：Oracle中的对象

一、sequence 序列

oracle 中的数字自增长策略

--创建
--默认创建 （参数） 默认从1开始每一次增加1
	create sequecne 名称；
	-- create sequence my_seq start with 23; 从几开始
	-- increment by 每次增加几 
	-- minvalue maxvalue  最小和最大值
	-- cycle  循环
	-- Cache size 缓存大小  序列并不是使用的时候临时创建，而是一次创建多个 默认一次创建20。
--使用   名称.nextval
	insert into student values(my_seq.nextval,'刘洋','F',1);
	
--销毁
	drop sequecne 名称；

二、view 视图

一条SQL语句的查询结果。

--创建
--create view 名称 as (SQL语句)
	create view my_view as (select rownum r,e.* from employees e)
	
--使用和表一模一样
	
--销毁
	drop view 名称
	
视图： 简化操作（数据筛选屏蔽复杂的数据查询操作）、屏蔽敏感字段、对于分组再连接的SQL语句提升可读性
注意： 不能使用视图进行DML操作！！！ 没有执行效率的提升。

三、index 索引

提升查询效率 类似目录的概念

--创建
-- create index 索引名称 on 表名(列名)
	create index my_index on student(name);
	
--使用 自动使用
	当你使用含有索引的列作为条件进行查询操作，会自动使用索引提升查询效率。
	
--销毁
	drop index 名称；
	
	索引不要随意创建，会降低增删改效率，Oracle会自动为主键添加唯一索引。

Cache size 缓存大小 序列并不是使用的时候临时创建，而是一次创建多个 默认一次创建20。
–使用 名称.nextval
insert into student values(my_seq.nextval,‘刘洋’,‘F’,1);

–销毁
drop sequecne 名称；

##### 二、view 视图

> 一条SQL语句的查询结果。

```sql
--创建
--create view 名称 as (SQL语句)
	create view my_view as (select rownum r,e.* from employees e)
	
--使用和表一模一样
	
--销毁
	drop view 名称
	
视图： 简化操作（数据筛选屏蔽复杂的数据查询操作）、屏蔽敏感字段、对于分组再连接的SQL语句提升可读性
注意： 不能使用视图进行DML操作！！！ 没有执行效率的提升。

三、index 索引

提升查询效率 类似目录的概念

--创建
-- create index 索引名称 on 表名(列名)
	create index my_index on student(name);
	
--使用 自动使用
	当你使用含有索引的列作为条件进行查询操作，会自动使用索引提升查询效率。
	
--销毁
	drop index 名称；
	
	索引不要随意创建，会降低增删改效率，Oracle会自动为主键添加唯一索引。