oracle

oracle 数据库

java编写一个程序 数据存在哪里呢？
目前： 数组 集合
文本文件
一、数据的存储
文本文件存储的缺陷：
1.没有数据类型
2.不安全
3.存储的数据量小
4.对数据的访问和修改效率低
....
二、关系型数据库
1.RDBMS(关系型数据库管理系统 relationship Database Management System)
2.常见的关系型数据库： oracle Mysql DB2 SqlServer
3.基本的概念：
3.1 表(table): 数据库中存储数据的单元 也叫做 实体集 entitySet
3.2 行(row):代表一条具体的数据 也叫实体 entity
3.3 列(column):代表每一条具体数据的属性（特点） 也叫 字段 field
3.3 主键(primary key):唯一标识表里的一条信息， 唯一、不能为空
3.4 外键(foreign key):代表本表的记录与其他表记录之间的关系
4.运行的方式
client --- db server (客户端 -- 数据库服务器)
客户端通过sql命令与数据库服务器进行通信
三、oracle数据库
1.安装：
(见word)
OracleServiceXE: 核心服务
OracleXETNSListener:对外提供链接服务的
2.几种访问oracle的方式
2.1 sqlplus 基于命令窗口的
window 键 + R ---输入 cmd 回车
在dos窗口下输入sqlplus
alter user hr account unlock; -- 解锁hr账户
命令：desc 表名 -- 查看表的结构
2.2 isqlplus:oracle提供的基于浏览器的访问方式
http://127.0.0.1:8080/apex
2.3 pl/sql Developer -- 第三方提供的访问oracle的方式
登陆用户名 ： hr
密码： hr
database:XE
Connect as: normal
file -- new -- SqlWindow 进入到命令窗口
四、sql命令 -- 基本的查询
1.简单查询
select 字段名1,字段名2... from 表名;
select : 指定查找的字段
from:从那张表查询
1.1 -- 查询 员工的编号、名字、邮箱、工资 的信息
select employee_id,last_name,email,salary from employees;
1.2 -- 查询全表的信息
select * from employees;

    注意：在实际开发中不建议使用 select *的方式进行全表查询，因为*的语义
        不明确，效率低
    1.3 -- 查询所有人员的 编号、名字、年薪、部门编号    
    select employee_id,last_name,salary*12,department_id from employees
         允许对查询结果的字段进行算数运算 + - * /
         注意：+ 号不能作用于字符串
            可以对日期类型进行算数运算，以天为单位
    1.4 字段起别名 -- as
    语法：select 字段名 as 别名 from 表名;(as可以省略)
        -- 查询所有人员的 employee_id,last_name,salary*12(年薪),department_id
                           编号          名字     年薪            部门编号               
        select  employee_id as "编号",
                last_name as "名字" ,
                salary*12 as "年薪",
                department_id as "部门编号"
        from employees;
    1.5 字符串拼接  ||
        语法： 字符串1 || 字符串2
        --查询 员工编号、姓名、部门编号 的信息
        select employee_id,first_name||'.'||last_name,department_id
        from employees;
2.排序 -- order by【重点】
    语法: select 字段名 ,.. from 表名 order by 字段名 asc(默认)/desc
    asc:升序   desc:降序
    --查询员工表全部信息，对查询结果按工资升序显示
    select * from employees order by salary asc;
    --查询员工表全部信息，对查询结果按工资降序显示    
    select * from employees order by salary desc;
    --查询员工表全部信息，对查询结果 按工资升序排列，
       如果工资相同 按部门编号升序排列
    select * from employees order by salary asc,employee_id asc;
    注意：asc/desc 作用于 前面的字段
3.条件判断查询 -- where   【重点】
    语法：select 字段名 ... from 表名 where 条件 order by 字段名
    作用于一行数据，对数据库的每行数据进行筛选
    3.1 等值查询 =
        --查询90号部门的员工信息
        select * 
        from employees where department_id = 90;
    3.2 不等值条件  > >= < <= !=(<>) and(并且)  or （或者）
        --查询工资大于5000的员工信息
        select *
        from employees where salary > 5000;
        --查询工资大于5000 并且 小于 10000的员工信息
        select * 
        from employees 
        where salary > 5000 and salary < 10000;
        -- 查询90号部门的信息和工资大于5000的所有人员信息
        select * 
        from employees
        where department_id = 90 or salary>5000;
    3.3 区间查询 --- between ... and ..(包括边界值)
        语法： where 字段名 between 条件1 and 条件2
        --查询工资大于等于5000 并且 小于等于10000 的员工信息
        select *
        from employees
        where salary between 5000 and 10000;
    3.4 枚举查询 -- in 
        语法：where department_id in (条件1，条件2，...)
        -- 查询50,70,90号部门的员工信息
        select * 
        from employees
        where department_id in(50,70,90) ;
    3.5 oracle对null值的处理  is null  is not null
        --查询没有提成(commission_pct)的员工信息
        select *
        from employees
        where commission_pct is null;
    3.6 模糊查询 -- like
        语法： select 字段名.... from 表名 where 字段名 like '条件'
        通配符： _ 代表一个字符
                % 代表n个字符
        -- 查询first_name由4个字符组成的人员信息
        select * 
        from employees
        where first_name like '____';
        --查询first_name以字母 'S'开头的人员信息  'S%'
        select *
        from employees
        where first_name like 'S%';
        --查询first_name以S开头的 由 5个字母组成的人员信息
        select *
        from employees
        where first_name like 'S____';
    注意：' '里面严格区分大小写
        select 语句oracle中不分大小写

五、函数
dual(哑表) -- oracle给我们提供的一个哑表，用于维护select语句的完整性
这个表只有一行一列。
--打印当前系统的时间 -- sysdate
select sysdate from dual;
abs(数值) -- 求绝对值
--select abs(-2) from dual;
mod(num1,num2) -- 求num1模num2的值
1.单行函数
作用于一行数据，产生一个结果
1.1 to_char(time,'字符串格式')【重点】
年 yyyy yy rr rrrr year
月 mm month
日 dd ddth
星期 day
小时 hh hh24
分 mi
秒 ss
--打印当前系统时间，按格式：年-月-日 星期 时：分：秒
yyyy-mm-dd day hh24:mi:ss
select to_char(sysdate,'yyyy-mm-dd, day,hh24:mi:ss') from dual;
--打印今天是星期几
select to_char(sysdate,'day') from dual;
1.2 to_date('字符串格式的日期','对应日期格式的字符串')【重点】
'2016-08-11' 'yyyy-mm-dd'
注意：字符串的表现格式 必须和前面的日期格式一样
作用：将一个字符串格式的日期，转换成oracle数据库的标准日期显示
-- 将'2000-8-11' 转换成数据库标准时间格式 显示
select to_date('2000-08-11','yyyy-mm-dd') as "时间" from dual
-- 打印'2000-08-11'是星期几
select
to_char(to_date('2000-08-11','yyyy-mm-dd'),'day') from dual;

        -- 查询员工表中 1997年 入职的员工
        select *
        from employees
        where to_char(hire_date,'yyyy') = '1997';
2、组函数（作用于每一组，产生一个值）
    2.1 sum() 求和 -- 适用于数值类型
        --求所有人员月工资的和
        select sum(salary) from employees;
        
    2.2 avg() 求平均值  -- 适用于数值类型
        --求所有人员平均工资
        select avg(salary) from employees;
        
    2.3 min() 求最小值
        --求所有人员工资最小值
        select min(salary) from employees;
    2.4 max() 求最大值
        --求所有人员工资最大值
        select max(salary) from employees;
    注意：oracle中null是最大值
    2.5 count() ：统计个数 -- 括号里面可以写 * 也可以写字段名
        --打印 1997 年入职的员工总人数
        select count(*)
        from employees
        where to_char(hire_date,'yyyy') = '1997';
    count(字段)：容易出现统计不准确的情况，因为他会自动忽略null
    count(1) 和 count(*) 效果是一样的

六.分组语句 -- group by 【重点】
语法： select 字段名 ... from 表名 where 条件 group by 字段名 order by
-- 查询人员的信息，按照部门编号进行分组
select department_id
from employees
group by department_id;
语法规则：
*只有出现在group by 语句里的字段 才能单独出现在select语句当中
在group by语句当中对字段使用了哪个单行函数，那么select语句里面
*也必须使用相同的
*如果一个字段没有出现在group by语句中，那么他必须配合组函数才能
出现在select 语句中

    --查询1997年入职员工 各个月份的总人数
     1）确定分组条件： 月份分组
     2）where to_char(hire_date,'yyyy')= '1997'
     
      -- select to_char(hire_date,'mm'),count(*)
         from employees
         where to_char(hire_date,'yyyy')= '1997'
         group by to_char(hire_date,'mm');
    --统计查询 各个部门员工的总工资   
     分组条件: department_id
         select sum(salary)
         from employees
         group by department_id;

七、having -- 对分组后的数据做条件判断的【了解】
where 是对分组前的数据进行筛选，坚决不能跟组函数
having 对分组后的数据进行再次筛选，不能跟单行函数

    --查询1997年入职员工 各个月份的总人数(人数大于2的月份)
    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;

    注意：如果where having都可以完成一个需求的时候，优选where

八、select 命令语句总结
select 字段名... from 表名 where 条件 group by 字段名 having 条件 order by 字段名 asc/desc;
from 确定查询数据来自的表
where 对表中的数据进行筛选，生成查询的基础数据
group by 对基础数据进行分组
having 对分组后的数据进一步筛选
select 对筛选后的数据生成结果集
order by 对结果集排序

一、伪列
他就是个不存在的列，通过select 语句是查询不到的，我们可以通过
手动给出。
常见的伪列：rowid rownum
rowid:数据库里面一条数据的唯一标识，通过对物理地址计算得出的。
rownum:数据库会给每次出现在查询结果里的行 进行编号，从1开始，查询执行一次
编一次号。
--可以给表起别名
--select e.,rowid,rownum from employees e
--取出employees表里面的前5行数据
select *
from employees
where rownum <=5;
--取出表里前6 到 10 条的数据
select *
from employees
where between 6 and 10; -- !error(错误的)
注意：rownum 必须从一开始 ，只能做 >=1 =1 < <= 关系运算
二、子查询（嵌套查询）
--查询employees表里面 最高工资的员工
1.查询出最高工资
select max(salary) from employees; -- r1
2.查询出最高工资的员工
select * from employees where salary = r1;
3.合并
select *
from employees
where salary = (select max(salary) from employees);
1.子查询结果是一行一列，常用在where里面做条件判断
--查询公司里工资高于平均工资的人员信息
1.1 统计查询平均工资
select avg(salary) from employees; -- r1
1.2 查询出高于平均工资的人员信息
select * from employees where salary > r1;
1.3 合并
select *
from employees
where salary > (select avg(salary) from employees);
2.子查询结果是多行一列，经常用于 where的条件判断
--查询和'King'在一个部门的人员信息
1.查询出'King'(last_name)所在的部门
select department_id from employees where last_name = 'King'; -- r2
2.查询出和'King'在一个部门的人员
select * from employees wher
e department_id in r2;
3.合并
select *
from employees
where department_id in (select department_id
from employees where last_name = 'King');
3.子查询结果是多行多列，经常出现在from 子句里，他其实是个虚表，可以再次
对虚表进行查询；
--查询工资最高的前5个员工信息
1.对employees表进行按工资排序(由高到低)
select * from employees order by salary desc; -- tab1
2.在tab1里面取出前5个人员的信息
select * from tab1 where rownum <=5;
3.合并
select *
from (select * from employees order by salary desc)
where rownum <=5;

三、数据库的分页 【重点】
--查询工资最高的第6个到第10个人员的信息
笨方法：
1.对employees表按工资进行排序(从高到低)
select * from employees order by salary desc; -- tab1
2.从tab1里面取出前10条，再次按工资排序（倒序）
select * from tab1 where rownum <=10 order by salary ; -- tab2
3. 从tab2里面取出前5条（就是工资最高的第6-10 个人员的信息）
select *
from (select * from
(select * from employees order by salary desc)
where rownum <=10 order by salary )
where rownum <=5;
专业的：
1.对employees表按工资进行排序(降序)
select * from employees order by salary desc; -- tab1
2.给rownum起个别名rn，让他成为 employees表的一个具体字段
select e. , rownum rn
from tab1 e; --tab2
3.从tab2里面取出 第6 到第10条信息
select *
from tab2
where rn between 6 and 10;
4.合并
select *
from (select e. , rownum rn
from (select * from employees order by salary desc) e)
where rn between 6 and 10;
四、表连接
概念：同过某个条件将两张表或者多张表的记录 合并成一个记录结果展示
1.内连接
关键字： inner join （inner可以省略） on给定连接条件
--查询员工的信息 以及其对应部门的信息
select e.,d.*
from employees e join departments d
on e.department_id = d.department_id;
注意：内连接不能处理null值
2.外连接 ：左外连接，右外连接，全外连接
关键字：
左外连接 left [outer] join on连接条件【重要】 outer可以省略
以左表为主表，右表为辅表，左表数据会全部出现在查询结果里
右外连接 right [outer] join on连接条件
以右表为主表，左表为辅表，主表的记录会全部出现在查询结果里面

            注意：如果辅表的连接条件跟主表的连接 没匹配上 会用null来代替
        全外连接 full outer join on连接条件
            所有的数据都会出现在结果里
    语法：
        select 字段名...from 表1 连接关键字 表2 on 连接条件   where ...group by ... having...order by.
    --查询员工的所有信息 以及其部门的所有信息 
     左连 --select e.*,d.*
     from employees e left outer join departments d 
     on e.department_id = d.department_id;
     右连 --select e.*,d.*
     from employees e right outer join departments d 
     on e.department_id = d.department_id;
     全连：-- select e.*,d.*
     from employees e full join departments d
     on e.department_id=d.department_id;    
3.多表连接
    --查询员工的姓名、部门的名称、城市名称
    select e.last_name,d.department_name,l.city
    from employees e left join departments d on e.department_id = d.department_id
    left join locations l on d.location_id = l.location_id;
4.自连接
    --查询员工的姓名以及 他的领导的姓名
    select e1.last_name,e2.last_name
    from employees e1 left join employees e2
    on e1.manager_id = e2.employee_id;

一、建表 -- create table
1.确定表名
2.确定表有哪些字段(属性)
3.语法：
create table 表名(
列名1 数据类型 默认值(default) 约束 ,
列名2 数据类型 约束
....
);
4.标示符命名规范
4.1 由字母、数字、、$、#组成，数字不能作为开头
4.2 长度不能超过30个字符，不能与关键字重名
5.数据类型
5.1 数值类型
number(大小限制) -- 相当于JAVA中的double 【---常用】
比如： number(2) -- 最大存两位数
number(5,2) -- 最多存储5位数，其中小数最多2位
整数位最多3三位
integer -- 一个很大的整数
5.2 字符串类型
varchar2(大小限制) -- 表示一个可变长的字符串，按照你实际存储的数据
大小来开辟空间，最多4000个字符
比如：varchar2(20) -- 最多存储20个字符 【---常用】
char(大小限制) -- 表示一个定长的字符串，限定大小是多少，就按多少开辟
存储空间
比如:char(5) -- aa 他的大小还是5个字符大小
5.3 日期类型
date -- 精确到秒 【---常用】
timestamp -- 精确到秒后面的小数
5.4 大数据类型
clob -- 存储字符型大对象，最大限制4G
blob -- 存储二进制大对象，最大限制4G
long -- 存储字符，最大限制2G
6.约束
6.1 主键约束 -- primary key: 唯一、非空 ，一张表只能有一个主键
6.2 非空约束 -- not null: 此列值不能是null
6.3 唯一约束 -- unique : 值必须唯一 ，可以为空， 空值可以重复
6.4 检查约束(自定义约束) -- check() ： 比如说对手机号码和邮箱的验证 等等
邮箱验证：check(字段名 like '%@%')
6.5 外键约束(foreign key)-- references : 代表本表记录和其他表记录之间的
关系，通常作为表连接的条件,值可以为空，可以重复，但是值必须来自
其他表的主键，唯一约束的列
7.创建一个学生表和一个班级表
create table clazzs( -- 父表
c_id number(3) primary key, -- 班级编号
c_name varchar2(20) not null, -- 班级名称
create_date date -- 创建日期
);
create table t_students( -- 子表
s_id number(3) primary key,
s_name varchar2(20) not null ,
sex char(1) check(sex in ('m','g')),
mobile_num varchar2(11) unique check(length(mobile_num) = 11),
email varchar2(50) check(email like '%@_%'),
c_id number(3) references clazzs(c_id)
);
二、sql命令的分类
1.DQL: 数据查询语言 比如 select
2.DML: 数据操纵语言 insert update delete
3.DDL: 数据定义语言 create drop alter
4.DCL: 数据控制语言 grant revoke
5.TCL: 事务控制语言 commit rollback

DML:
--insert -- 插入 向表里面插入数据
    语法：inser into 表名 values (值1，值2 ...) -- 全部字段插入
        inser into 表名(字段1，字段2,...) values (值1，值2 ...)
        注意：values 里面的值 一定要与表名后面的字段相匹配
    例子：
        insert into clazzs values (62,'JAVA班',sysdate);
        insert into t_students(s_id,s_name,sex,mobile_num,email,c_id)
                values(1,'小明','g','11111111111','xiaom@zpark.com.cn',1)
--update -- 更新数据
    语法：
        update 表名 set 字段1= 新值1，字段2= 新值2  where ..
        注意：如果没有条件判断会全表更新
    例子：
        update t_students set s_name = '小贝';
--delete -- 删除
    语法：delete from 表名 where ...
    注意：在使用delete命令的时候 ，务必要确定好条件
    例子：
        delete from t_students where s_id = 1;
DDL: 
    drop -- 用于删除数据库的实例对象
    语法: drop table 表名;
        drop table clazzs;
        
        drop table clazzs cascade constraint; -- 级联删除
    注意：在删除父表前，必须删除字表的外键关系
    
    alter -- 修改列的一些操作 【了解】
        修改列名：
            alter table 表名 rename column 老名 to 新名;
        加列：
            alter table 表名 add 列名  数据类型
        修改列的数据类型：
            alter table 表名 modify 列名  数据类型
            注意：被修改的列，必须没有数据
DCL：
    grant -- 授权 
        grant 权限 to 用户 
    revoke --撤销授权
        revoke 权限 from 用户
TCL:
    commit -- 提交事务
    rollback -- 事务回滚

三、序列 -- sequence 【重点】
1.概念：
sequence是由数据库系统提供的，一个自增长序列号，经常用于生成
表的主键(PK)
2.创建：
create sequence 序列名 【..】
start with n -- 代表此序列从n开始生成
increment by n -- 代表此序列每次自增长的数量
maxvalue n -- 最大可生成的值
minvalue n -- 最小从几开始
cycle no cycle -- 是否循环生成
cache n /no cache -- 每次缓存多少 默认缓存20个
3.使用：
序列名.nextval
比如： select clazzs_seq.nextval from dual
4.删除序列
drop sequence 序列名;
四、视图 -- view
其实就是对一个查询语句起个别名，存在数据库里面，以便以后重复使用
注意：视图实质上是存储的sql语句
创建：create view 视图名 as 查询语句
create view emp_view as (select * from employees);
1.简化查询
-- 查询工资最高的第6 到第10个人的信息
1.select * from employees order by salary desc; -- tab1
2.select e.,rownum rn from tab1 e; -- tab2
select *
from (select e.,rownum rn from
(select * from employees order by salary desc) e)
where rn between 6 and 10;

    通过创建视图实现;
        create view emps1 as (select * from (select e.*,rownum rn from 
            (select * from employees order by salary desc) e));
            
        select * from emps1 where rn between 6 and 10;
删除： drop view 视图名;

注意：对视图的操作会影响到原表 ，尽量不要对视图做 update delete insert

五、索引 -- index
相当与一个目录，提高查询的效率
创建：
create index 名字 on 表名（列名）;
删除：
drop index 索引名字
主要作用：提高查询效率
在oracle中会自动给主键和唯一列 加上索引
注意：并不是说什么情况都需要加索引提高效率，一般是在复杂的表中才需要，
因为他会占用我们的内存资源

六、事务控制 (transaction)
银行转账：
1.验证账户 -- 张三给他的儿子转账
2.输入转账金额 -- money 1000
3.更新余额
更新本人账户的余额 -- -money 1000
(银行停电 出现意外 导致系统终止)
更新对方账户余额 -- +money

1、是由一条或多条SQL组成，是操作数据库的最小单位
2、事务的原理：
    数据库会为每一个连接上来的client开辟一小块内存(回滚段)，用于暂时缓存
sql命令的执行结果，当事务结束的时候，需要client给出明确指示：
    commit -- 提交事务  永久保存数据
    rollback -- 事务回滚  丢弃所有sql的执行结果。
3、事务的边界
    begin:从写第一条sql开始
    end:
        3.1 如果是DML命令，那么需要 显示的输入commit/rollback
        3.2 如果是DDL，那么事务成功后 会自动带commit/rollback
    **注意：正常退出事务会自动提交，如果非正常退出 事务自动回滚；
4.  事务的特性 (ACID)
    A 原子性 ：组成事务的一组sql是一个不可再分的整体，要么一起成功
                要么一起失败；
    C 一致性 ：当事务结束的时候，保证数据的状态要与事务操作里的完全一致
    
    I 隔离性 : 多用户并发访问数据库的时候，要保证事务之间不能互相影响
    D 持久性 ：事务结束的时候，保证事务对数据库数据的影响是永久性的(持久化)

=============================================================
总结：
一、简单查询
select * from 表名 where 条件 group by 条件字段 having 再次筛选条件
order by 可跟多个字段 asc/desc;
二、函数
单行函数
to_char(time,'字符串格式') -- 将日期转成字符串
to_date('字符串','字符串') -- 将字符串转成oracle标准日期格式
length(字符串) -- 求字符串长度
分组函数：
sum() -- 求和
avg() -- 求平均值
max() -- 最大值
min() -- 最小值
count() -- 统计数量
三、子查询
1.结果是一行一列 -- 常用在 where子句 做条件判断
2.结果是多行一列 -- 常用在 where子句 做条件判断
3.结果是多行多列 -- 常用在from子句中
伪列：
rownum -- 每次从一开始 只能做 =1 >=1 < <=
***数据库分页:
主要是用 子查询 和rownum 来解决的

四、表链接
    1.内连接 -- inner join .. on ..  不能处理null值
    2.外连接
        左外链接 -- left join .. on ..
        右外链接 -- right join .. on ..
        全外链接 -- full join .. on ..
    3.多表连接
        oracle中表与表之间的连接 是两两连接
    4.自连接
        把一张表看成两张表
五、建表
    create table 表名(
        列名 数据类型 约束
    );
    1.数据类型
        number()
        varchar2()
        date
    2.约束
        primary key --主键约束 非空 且 唯一
        unique -- 唯一约束
        not null -- 非空约束
        check() -- 检查约束 也成自定义约束
        references -- foreign key 外键约束 值必须来自其他表的主键或是
                                唯一列，可以为null，可以重复
六、DML
    insert into 表名 (列名 ...) values(对应的值 ...);
    update 表名 set 列名 = 新值 where 条件;
    delete from 表名 where 条件;
七、DDL
    drop table 表名;
    drop sequence 序列名;
    drop view 视图名;
    drop index 索引名;
    
    alter table 表名 add 列名 数据类型;

八、事务
    commit -- 提交事务
    rollback -- 事务回滚
    
    1.事务的原理 
    2.事务的边界
    3.事务的特性 ACID