必威体育Betway必威体育官网
当前位置:首页 > IT技术

Oracle数据库深入学习

时间:2019-09-30 04:15:41来源:IT技术作者:seo实验室小编阅读:54次「手机版」
 

oracle数据库学习

一、oracle概念

oracle数据系统是美国oracle(甲骨文)公司提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器或B/S体系结构的数据库之一,oracle数据库时目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完善的数据库管理功能,是关系型数据库,比mysql更为庞大,在现行的关系型数据库中排名第一(oracle、mysql、SqlServer),时间是最为精确的。

二、Oracle的安装

安装教程

三、Oracle数据库的体系结构

1、数据库database

Oracle数据库是数据的物理储存,这就包括(数据文件ORA或者DBF、控制文件、联机日志、参数文件)。其实Oracle数据库的概念和其他数据库不一样,这里的数据库时一个操作系统只有一个库,可以看做Oracle就只有一个大数据库。

2、实例

一个数据库可以有n个实例,有一系列的后台进程和内存结构组成。

3、数据文件dbf

是数据库的物理储存单位,数据库的数据是储存在表空间中的,真正是在某一个或者多个数据文件中,而一个表空间可以由一个或多个数据文件组成,一个数据文件只能属于一个表空间,一旦数据文件被加入到某个表空间后,就不能删除这个文件,如果要删除某个数据文件,只能删除其所属于的表空间才行。

4、表空间

表空间是Oracle对物理数据库上相关数据文件的逻辑映射,一个数据库在逻辑上被划分成一到若干个表空间,每个表空间包含了在逻辑上相关联的一组结构,每个数据库至少有一个表空间(称之为system表空间)

每个表空间由同一磁盘上的一个或多个文件组成,这些文件叫数据文件,一个数据文件只能属于一个表空间。

5、用户

用户是在实例下建立的,不同实例中可以建相同名字的用户,表的数据是由用户放入某一个表空间的,而这个表龙剑会随机把这些表数据进行管理和存放的,但是表不是由表空间去查询的,而是由用户去查询。

6、SCOTT和HR用户

是Oracle帮我们建好的测试账户,Scott账户常用,里面有emp、dept表等。

四、Oracle和MYSQL的差别

1、新建项目的方式

MYSQL : 创建一个数据库,创建相应的表

2、Oracle是多用户的, MYSQL是多数据库的

3、Oracle安全级别要高,MYSQL开源免费

五、基本查询

1、SQL概念

结构化查询语言

2、SQL分类

DDL : 数据定义语言 create alter drop truncate

DML : 数据操纵语言 insert update delete

DCL : 数据控制语言 安全 授权 grant revoke

DQL : 数据查询语言 select from子句 where子句

在oracle中将DQL放入DML中,多了TCL:事务控制语言 commit、rollback

3、Oracle中的数据字典

所谓数据字典就是编写sql语句,全选可以一次性执行不报错

/* 1.删除数据对象 */
-- 删除数据表
drop table t_reply;
drop table t_user;
-- 删除序列
drop sequence sq_user;
drop sequence sq_reply;


/* 2.创建数据对象 */
-- 创建数据表
create table t_user(
   u_id number(4) primary key,
   u_userName varchar2(40) unique not null,
   u_password varchar2(50) not null,
   u_gender number(1) check(u_gender in (0,1,2)),
   u_age number(3) check(u_age between 12 and 120),
   u_registeTime date not null
);
create table t_reply(
   r_id number(10) primary key,
   r_content varchar2(500) not null,
   r_userId number(4) not null,
   foreign key(r_userId) references t_user(u_id)
);
-- 创建序列
create sequence sq_user start with 1001;
create sequence sq_reply start with 10000001;

/* 3.添加测试数据 */
-- 处理全新的数据表和全新的序列在使用时取不到初始值的问题
insert into t_user values
  (1, '1', '1', 1, 25, sysdate);
insert into t_reply values
  (1, '1', 1);
delete from t_reply;
delete from t_user;
commit;
/*insert into t_user values
  (1000, 'owner', '9999', 1, 25, sysdate);
insert into t_reply values
  (10000000, '人员召集...', 1000);*/
 
-- 添加数据
insert into t_user values
  (sq_user.nextval, 'admin', '9999', 1, 25, sysdate);
insert into t_reply values
  (sq_reply.nextval, '系统1.0上线...', sq_user.currval);

/* 4.提交 */
commit;

/* 5.查询语句 */
/*
   select * from t_user;
   select * from t_reply;
*/

3、查询语句的结构

 select [列名] [*] from 表名 [where 条件] [group by 分组条件] [having 过滤] [order by 排序]

4、伪表dual

dual : oracle中的虚表 ,伪表, 主要是用来补齐语法结构,

比如:select 1+1 from dual;
注意:直接写一个常量比写 * 要高效
select count(1) from emp;
select count(*) from emp;

5、别名查询

使用as 关键字, 可以省略,别名中不能有特殊字符或者关键字, 如果有就加双引号(比如空格)

elect ename 姓名, sal 工资 from emp;
select ename "姓       名", sal 工资 from emp;

6、去除重复数据 distinct

加在select和列名后面,多列去除重复: 每一列都一样才能够算作是重复

--单列去除重复
select distinct job from emp;

--多列去除重复的
select distinct job,deptno from emp;

7、查询中四则运算

需要用到伪表dual

select 1+1 from dual;
--查询员工年薪  = 月薪* 12
select sal*12 from emp;

在Oracle 中 ,双引号主要是别名的时候使用, 单引号是使用的值, 是字符

8、空值处理函数

注意: null值 , 代表不确定的 不可预知的内容 , 不可以做四则运算

1)mysql的空值处理

​ ifnull(参数1, 参数2):

​ 判断参数1是否为null,

​ 不为null, 则函数的结果取参数1的值

​ 为null, 则取参数2的值

​ if(参数1, 参数2, 参数3)

​ 判断参数1是否为null,

​ 为null, 则取参数3的值

​ 不为null, 则取参数2的值

​ 注意if函数只能处理数值字段的空值

SELECT e.`ename` 姓名,
	e.`sal` 底薪,
	e.`comm` 奖金,
	(e.`sal`+e.`comm`) 实发,
	IFNULL(e.`sal`+e.`comm`, e.`sal`) 实发1,
	IF(e.`comm`, e.`sal`+e.`comm`, e.`sal`) 实发2
FROM emp e;

2)oracle的空值处理

nvl(参数1,参数2):如果1为null,则返回2,不为null就为1

nvl2(参数1, 参数2, 参数3)1为null,则返回,3,不为null,则返回2

decode(需要判断的字段,

​ 常量值1, 结果值1,

​ 常量值2, 结果值2,

​ …

​ 常量值n, 结果值n,

​ 默认结果值

​ )

select e.ename 姓名,
       e.sal 底薪,
       e.comm 奖金,
       nvl(e.sal+e.comm, e.sal) 实发1,
       nvl2(e.comm, e.sal+e.comm, e.sal) 实发2,
       decode(e.comm, null, e.sal, e.sal+e.comm) 实发3
from emp e;

9、字符串拼接

  • Oracle 特有的连接符: || 拼接
  • concat(str1,str2) 函数, 在mysql和Oracle中都有
--查询员工姓名 :  姓名:SCOTT
select ename from emp;
--使用拼接符
select '姓名:' || ename from emp;

--使用函数拼接
select concat('姓名:',ename) from emp;

六、条件查询

条件查询就是where后面的写法

1、关系运算符和排序

> >= = < <= != <>
注意:不等于既可以用!=也可以用<>但是<>效率更高

2、逻辑运算符

and or not

3、其它运算符

like 模糊查询
in(set) 在某个集合内
between..and.. 在某个区间内
 is null  判断为空
is not null 判断不为空

4、模糊查询: like

% 匹配多个字符

_ 匹配单个字符

如果有特殊字符, 需要使用escape转义

--查询员工姓名中,包含%的员工信息
select * from emp where ename like '%\%%' escape '\';

select * from emp where ename like '%#%%' escape '#';

5、排序order by

升序: asc ascend

降序: desc descend

排序注意null问题 : nulls first | last指定null值显示的位置

同时排列多列, 用逗号隔开

--查询员工信息,按照奖金由高到低排序
select * from emp order by comm desc nulls last;

--查询部门编号和按照工资  按照部门升序排序, 工资降序排序
select deptno, sal from emp order by deptno asc, sal desc;

分析函数:用来处理排名并列问题

rank——排名可并列,之后序号不会顺眼(如2个第二,下一个是第三名)

dense_rank——排名可并列,之后序号会顺眼(如2个第二,下一个是第四名)

row_number——不可并列排名

over——添加分组条件

partition—by——分组

6、分组排序

  • select 分组的条件,分组之后的条件 from 表名 group by 分组的条件 having 条件过滤
  • 在分组查询语句的检索内容中, 只能出现分组字段和聚合函数

sql的编写顺序:

​ select,from,where,group by,having,order by

sql的执行顺序:

​ from,where,group by,having,select,order by…

  • where和having的区别:

    where后面不能跟聚合函数,可以接单行函数

    having是在group by之后执行,可以接聚合函数

    --分组统计所有部门的平均工资,找出平均工资大于1500的部门
    select deptno,avg(sal) from emp group by deptno having avg(sal)>1500;
    
    --报错,给分组之后的条件取别名的时候出错,因为sql语句执行的顺序问题
    select deptno,avg(sal),bb from emp group by deptno having bb>1500;
    

七、函数

函数: 必须要有返回值

1、单行函数

对某一行中的某个值进行处理

1)数值函数

  • 取整

    select ceil(45.926) from dual;  --46 向上取整
    select floor(45.926) from dual; --45  向下取整
    
  • 四舍五入

    round(a,b)

    b如果是正数代表取到小数点后b位数为止

    b如果是负数代表取到十位为止

    elect round(45.926,2) from dual; --45.93
    select round(45.926,1) from dual; -- 45.9
    select round(45.926,0) from dual; --46
    select round(45.926,-1) from dual; --50
    select round(45.926,-2) from dual; --0
    select round(65.926,-2) from dual; --100
    
  • 截断(去尾法)

    select trunc(45.926,2) from dual; --45.92
    select trunc(45.926,1) from dual; -- 45.9
    select trunc(45.926,0) from dual; --45
    select trunc(45.926,-1) from dual; --40
    select trunc(45.926,-2) from dual; --0
    select trunc(65.926,-2) from dual; --0
    
  • 求余

    select mod(9,3) from dual; --0
    select mod(9,4) from dual; --1
    

2)字符函数

 substr(str1,起始索引,长度) 
--注意: 起始索引不管写 0 还是 1 都是从第一个字符开始截取
select substr('abcdefg',0,3) from dual; --abc
select substr('abcdefg',1,3) from dual; --abc

select substr('abcdefg',2,3) from dual; --bcd

--获取字符串长度 24 28
select length('abcdefg') from dual;

--去除字符左右两边的空格
select trim('  hello  ') from dual;

--替换字符串
Select replace('hello','l','a') from dual;

3) 日期函数

-查询今天的日期(查询的是服务器上的日期)
select sysdate from dual;
--查询3个月后的今天的日期
select add_months(sysdate,3) from dual;
--查询3天后的日期
select sysdate + 3 from dual;


--查询员工入职的天数
select sysdate - hiredate from  emp;

select ceil(sysdate - hiredate) from  emp;

--查询员工入职的周数
select (sysdate - hiredate)/7 from emp;

--查询员工入职的月数
select months_between(sysdate,hiredate) from emp;

--查询员工入职的年份
select months_between(sysdate,hiredate)/12 from emp;

4) 转换函数

数值转字符

select to_char(sal,'$9,999.99') from emp;

select to_char(sal,'L9,999.99') from emp;
/*
to_char(1210.73, '9999.9') 返回 '1210.7' 
to_char(1210.73, '9,999.99') 返回 '1,210.73' 
to_char(1210.73, '$9,999.00') 返回 '$1,210.73' 
to_char(21, '000099') 返回 '000021' 
to_char(852,'xxxx') 返回' 354'

*/

日期转字符 to_char() (不区分大小写

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual; --默认的是24小时制度
--只想要年
select to_char(sysdate,'yyyy') from dual;  --2017

--只想要日
select to_char(sysdate,'d') from dual; --2  代表一个星期中第几天(美国周天是一个星期的第一天)
select to_char(sysdate,'dd') from dual;  --10  代表一个月中的第几天
select to_char(sysdate,'ddd') from dual; --100 代表一年中的第几天


select to_char(sysdate,'day') from dual;  --monday(星期的英文)
select to_char(sysdate,'dy') from dual;   --mon  星期的简写

字符转日期

select to_date('2017-04-10','yyyy-mm-dd') from dual;

--查询1981年 -- 1985年入职的员工信息
select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');

5) 通用函数

nvl(参数1,参数2) 如果参数1 = null 就返回参数2
       nvl2(参数1,参数2,参数3) 如果参数1 = null ,就返回参数3, 否则返回参数2
       
       nullif(参数1,参数2) 如果参数1 = 参数2 那么就返回 null , 否则返回参数1
       
       coalesce: 返回第一个不为null的值
       
select nvl2(null,5,6) from dual; --6;

select nvl2(1,5,6) from dual; --5;

select nullif(5,6) from dual; --5
select nullif(6,6) from dual; --null

select coalesce(null,null,3,5,6) from dual;  --3

2、多行函数(聚合函数)

对某一列的所有行进行处理

max() min count sum avg

注意:直接忽略空值

例子:

--统计员工的平均奖金  550  错误    2200/14 =,因为空值产生的影响
select avg(comm) from emp;
--统计员工的平均奖金 157.
select sum(comm)/count(1) from emp;
select ceil(sum(comm)/count(1)) from emp;

3、条件表达式

作用:查询的时候用来替换列中所有数据的值,区别于取别名,取别名只是单纯的给子弹取别名

/*
条件表达式:
	case 字段
	when 值1 then 值
	when 值2 then 值
	else
		默认值
	end "别名"
case、when通用的写法,mysql和oracle中都可以用

oracle特有的写法:decode(字段,if1,then1,if2,then2,else1)
*/
--给表中姓名取一个中文名
select 
	case ename
	when 'SMITH' then '刘备小二'
	when 'ALLEN' then '萨达'
	else 
		'路人甲'
	end "中文名"
from emp;

--oracle 特有写法
select decode(ename,'SMITH','刘备小二','ALLEN','萨达','路人甲') from emp;

八、多表查询

1、笛卡尔积

实际上是两张表的乘积,但是在实际开发中没有太大意义,格式: select * from 表1,表2

select * from emp;
select * from dept;

select * from emp, dept;

select * from emp e1, dept d1 where e1.deptno = d1.deptno;

2、内连接

隐式内联接:

​ 等值内联接: where e1.deptno = d1.deptno;

​ 不等值内联接: where e1.deptno <> d1.deptno;

​ 自联接: 自己连接自己

显式内联接:

​ select * from 表1 inner join 表2 on 连接条件

​ inner 关键字可以省略

       --查询员工编号,员工姓名,经理的编号,经理的姓名
select e1.empno,e1.ename,e1.mgr,m1.ename
from emp e1, emp m1 where e1.mgr= m1.empno;


--查询员工编号,员工姓名,员工的部门名称,经理的编号,经理的姓名
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename
from emp e1, emp m1,dept d1 where e1.mgr= m1.empno and e1.deptno = d1.deptno;

--查询员工编号,员工姓名,员工的部门名称,经理的编号,经理的姓名,经理的部门名称
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename,d2.dname
from emp e1, emp m1,dept d1,dept d2 
where 
     e1.mgr= m1.empno 
 and e1.deptno = d1.deptno
 and m1.deptno = d2.deptno 
;

--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称
select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1.ename,d2.dname
from emp e1, emp m1,dept d1,dept d2,salgrade s1 
where 
     e1.mgr= m1.empno 
 and e1.deptno = d1.deptno
 and m1.deptno = d2.deptno
 and e1.sal between s1.losal and s1.hisal 
;

--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级
select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1.ename,d2.dname,s2.grade
from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2 
where 
     e1.mgr= m1.empno 
 and e1.deptno = d1.deptno
 and m1.deptno = d2.deptno
 and e1.sal between s1.losal and s1.hisal 
 and m1.sal between s2.losal and s2.hisal 
;

--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级
--将工资等级 1,2,3,4 显示成 中文的 一级 二级 三级...

select e1.empno,
       e1.ename,
       d1.dname,
       case s1.grade
         when 1 then '一级'--为所有列更换成你想要的信息
         when 2 then '二级'
         when 3 then '三级'
         when 4 then '四级'
         else
             '五级'
         end "等级",--取别名
       e1.mgr,
       m1.ename,
       d2.dname,
       decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级','五级') "等级"
from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2 
where 
     e1.mgr= m1.empno 
 and e1.deptno = d1.deptno
 and m1.deptno = d2.deptno
 and e1.sal between s1.losal and s1.hisal 
 and m1.sal between s2.losal and s2.hisal 
;

--查询员工姓名和员工部门所处的位置
select e1.ename,d1.loc from emp e1,dept d1 where e1.deptno = d1.deptno;

select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;

2、外连接

外连接: (标准,通用写法)

​ 左外连接: left outer join 左表中所有的记录,如果右表没有对 应记录,就显示空

​ 右外连接: right outer join 右表中的所有记录,如果左表没有对应记录,就显示空

​ outer 关键字可以省略

Oracle中的外连接: (+) 实际上是如果没有对应的记录就加上空值

​ select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);

select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;
insert into emp(empno,ename) values(9527,'HUAAN');
select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);

select * from emp e1 right outer join dept d1 on e1.deptno = d1.deptno;
select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;

九、子查询

查询语句中嵌套查询语句; 用来解决复杂的查询语句

1、单行子查询

= = < <= <> !=

--查询最高工资的员工信息 
--1.查询出最高工资 --5000
select max(sal) from emp;
--2. 工资等于最高工资
select * from emp where sal = (select max(sal) from emp);


--查询出比雇员7654的工资高,同时和7788从事相同工作的员工信息
--1.雇员7654的工资 1250
select sal from emp where empno = 7654;
--2.7788从事的工作 ANALYST
select job from emp where empno = 7788;
--3.两个条件合并
select * from emp where sal > 1250 and job = 'ANALYST';

select * from emp where sal > (select sal from emp where empno = 7654) and job = (select job from emp where empno = 7788);

--查询每个部门最低工资的员工信息和他所在的部门信息
--1.查询每个部门的最低工资,分组统计
select deptno,min(sal) minsal from emp group by deptno;
--2.员工工资等于他所处部门的最低工资
select * 
from emp e1,
     (select deptno,min(sal) minsal from emp group by deptno) t1 --将查询到的结果作为一张表进行两表连查
where e1.deptno = t1.deptno and e1.sal = t1.minsal; 
--3.查询部门相关信息
select * 
from emp e1,
     (select deptno,min(sal) minsal from emp group by deptno) t1,
     dept d1 
where e1.deptno = t1.deptno and e1.sal = t1.minsal and e1.deptno = d1.deptno; 

2、多行子查询

in not in >any >all exists not exists

通常情况下, 数据库中不要出现null 最好的做法加上Not null

​ null值并不代表不占空间, char(100) null 100个字符

--查询不是领导的信息
select * from emp where empno not in (select mgr from emp);
select * from emp where empno <>all(select mgr from emp);
--正确的写法
select * from emp where empno not in (select mgr from emp where mgr is not null);

3、exists(查询语句)

存在的意思,判断一张表里面的记录是否存在与另外一张表中,作布尔值来处理:, 当查询语句有结果的时候, 就是返回true,否则返回的是false,数据量比较大的时候是非常高效的

--查询有员工的部门的信息
select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno );

十、分页

Oracle的分页查询需要使用伪列辅助

伪列 : 在Oracle的结果集中默认带有的可以使用的

​ 不出现在结果集列中的内容.

rownum : 伪列序号, 从1开始

​ rowid : 伪列地址

SQL执行顺序

​ from … where …group by…having … select…rownum…order by

默认都是从1开始。

rownum按照员工id排序后再分页
select t.*
from (
      select rownum rn,e.*
      from (
            select *
            from emp 
            order by empno
           ) e
     ) t
where t.rn between 5 and 9;  
rowid去除表中重复数据
create table p(
       name varchar2(10)
);

insert into p values('黄伟福');
insert into p values('赵洪');
insert into p values('杨华');

delete from p where 

select rowid,p.* from p;
select distinct * from p;

delete from p p1 where rowid > (select min(rowid) from p p2 where p1.name = p2.name);

十一、集合运算

所有的查询结果可能不是来自同一张表, 将查询结果进行合并,并去除重复项

1、并集

union : 去除重复的,并且排序

union all : 不会去除重复的

select * from emp where sal > 1500
union
select * from emp where deptno = 20;

select * from emp where sal > 1500
union all
select * from emp where deptno = 20;

2、交集运算: intersect

--工资大于1500,并且20号部门下的员工
select * from emp where sal > 1500;
select * from emp where deptno = 20;

select * from emp where sal > 1500
intersect
select * from emp where deptno = 20;

3、差集运算

两个结果相减

--1981年入职员工(不包括总裁和经理)
--1981年入职员工
select * from emp where to_char(hiredate,'yyyy')='1981';

--总裁和经理
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';


select * from emp where to_char(hiredate,'yyyy')='1981'
minus
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';

4、集合运算中的注意事项

1.列的类型要一致

2.按照顺序写

3.列的数量要一致,如果不足,用空值填充

select ename,sal from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;
--列的类型不匹配
select ename,sal from emp where sal > 1500
union
select sal,ename from emp where deptno = 20;

--列的数量不匹配
select ename,sal,deptno from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;

select ename,sal,deptno from emp where sal > 1500
union
select ename,sal,null from emp where deptno = 20;

select ename,sal,deptno from emp where sal > 1500
union
select ename,sal,66 from emp where deptno = 20;

select * from emp;
select * from dept;

十二、DDL语句管理表

1、表空间

逻辑单位, 通常我们新建一个项目,就会去新建表空间,在表空间中创建用户来创建表

  • 创建表空间

    语法:

    ​ create tablespace 表空间的名称

    ​ datafile ‘文件的路径(服务器上)’

    ​ size 大小

    ​ autoextend on 自动扩展

    ​ next 每次扩展的大小

    --创建表空间-
    create tablespace canghe
    datafile 'C:\oracle\canghe.dbf'
    size 100m
    autoextend on
    next 10m;
    
  • 删除表空间

--删除表空间
drop tablespace canghe;

2、创建用户

create user 用户名

identified by 密码

default tablespace 表空间的名称

create user canghe
identified by canghe
default tablespace canghe;
  • 授权
--授予 dba的角色
grant dba to canghe;
select * from scott.emp;

3、创建表

create table 表名(

​ 列名 列的类型 [列的约束],

​ 列名 列的类型 [列的约束]

​ );

 列的类型:
varchar ,在Oracle中,目前是支持的, 但是不保证以后还支持
varchar2(长度) 可变字符长度    varchar2(10)  hello  占5个字符
     char(长度)   固定长度字符      char(10)      hello  占10个字符,用空格填充
     number(总长度,小数长度)     数字类型 --小数长度不能大于等于总长度
     
     date                   年月日时分秒 2017/4/13 9:43:49
     timestamp              时间戳, 比date类型更加精确 13-APR-17 09.44.08.272000 AM +08:00
     
     LONG/CLOB : 存放一本小说
     BLOB      : 存放电影  java 存进去,  再读取出来
使用子查询的方式创建表
     
     create table 表名 as 查询语句; 
     
       注意: 只会复制表结构和表中的数据,不会复制列的约束     
             如果查询语句有结果, 就是复制 表结构和数据
             如果查询语句没有结果, 就是复制 表结构
create table test1(
       name1 varchar2(10),
       name2 char(10),
       age number(4,2)
);
create table 表名 as 查询语句; 复制表
create table emp as select * from scott.emp;

4、修改表

  • 添加咧

    alter table 表名 add 列

  • 修改列

    修改列类型:modify

    修改列名:rename

  • 删除咧

    alter table 表名 drop column 列名;

  • 修改列名

    alter table 表名 rename column 列名;

  • 重命名表

    rename 旧表名 to 新表名;

5、删除表

drop table 表名

6、表的五大约束

  1. 主键约束

    primary key

  2. 非空约束

    not null

  3. 唯一约束

    unique

  4. 外键约束

    添加外键约束:

    alter table 表名 add foreign key(添加约束的字段) references 引用的表名 (引用的字段)

    插入:先主表、再从表

    删除:先从表,再主表

    强制删除表(不建议):

    drop table 表名 cascade constraint;

    级联删除:

    添加外键约束,使用级联约束 ,在删除的时候,使用级联删除

    ----添加外键约束,使用级联约束  ,在删除的时候,使用级联删除
    alter table product add foreign key(cno) references category(cid) on delete cascade;
    
    
    insert into category values(2,'电脑办公');
    insert into product values(11,'外星人',2);
    
    --级联删除 : 首先去从表中找有没有 关联数据, 如果在从表中找到关联数据,先删除从表中关联数据,然后再删除表中的数据
    delete from category where cid = 2;
    
  5. 检查约束

    check( 列名 in (‘值1’,‘值2’,‘值3’))

十三、DML管理表数据

1、插入数据

  • 指定列名插入

    insert into 表名 values(所有列的值都要对应写上)

  • 不指定列名插入

    insert into 表名(列1,列2) values(值1,值2);

  • 使用子查询插入

    insert into 表名 查询语句

2、修改数据

update 表名 set 列名 = 列的值 [where 条件]

3、删除数据

delete from 表名 [where 条件]

 delete和truncate 区别
        
       delete:                 truncate:
        DML                    DDL
        逐条删除               先删除表再创建表
        支持事务操作           不支持事务操作,
                               执行效率要高  

十四、其他数据库对象

1、事务

事务: 就是一系列的操作,要么都成功,要么都失败

​ 四大特性: 原子性,隔离性,持久性,一致性

   如果不考虑隔离级别: 脏读,虚读,不可重复读
        MYSQL隔离级别: READ UNCOMMITTED , READ COMMITTED, REPEATABLE READ, SERIALIAZABLE
        ORACLE隔离级别: READ COMMITTED serializable READ ONLY 
                    默认隔离级别: READ COMMITTED
                    
  提交 : commit
  事务的保存点/回滚点: savepoint 保存点的名称
  回滚: rollback

2、视图

视图: 是对查询结果的一个封装

​ 视图里面所有的数据,都是来自于它查询的那张表,视图本身不存储任何数据

​ 1.能够封装复杂的查询结果

​ 2.屏蔽表中的细节

​ 语法:

​ create [or replace] view 视图的名称 as 查询语句 [ with read only]

​ 注意: 通常不要通过视图去修改,视图创建的时候,通常要加上with read only

--创建一个视图
create or replace view view_test1 as select ename,job,mgr from emp;

--通过视图修改数据
update view_test1 set ename='SMITH2' where ename = 'SMITH';

--创建一个只读视图
create or replace view view_test2 as select ename,job,mgr from emp with read only;

update view_test2 set ename='SMITH3' where ename = 'SMITH2';

-同义词的概念(就是为视图取的一个别名)

create synonym dept for view_test3;

3、序列

oracle中用来控制自增长的

语法:
           create sequence 序列的名称
           start with 从几开始
           increment by 每次增长多少
           maxvalue 最大值 | nomaxvalue
           minvalue 最小值 | nominvalue
           cycle | nocycle  是否循环    1,2,3,1,2,3
           cache 缓存的数量3 | nocache  1,2,3,4,5,6 
           
      如何从序列获取值
          currval : 当前值
          nextval : 下一个值
          
               注意: currval 需要在调用nextval之后才能使用      
               
               永不回头,往下取数据, 无论发生异常, 回滚   
--序列用的最多的一种写法
create sequence seq_test2;
select seq_test2.nextval from dual;

4、索引

 索引:相当于是一本书的目录,能够提高我们的查询效率
       如果某一列,你经常用来作为查询条件,那么就有必要创建索引,数据量比较的情况
       
       语法: 
             create index 索引的名称 on 表名(列)   
        
       注意:主键约束自带主键索引, 唯一约束自带唯一索引
       
       索引原理: btree   balance Tree 平衡二叉树
       
             如果某列作为查询条件的时候,可以提高查询效率,但是修改的时候,会变慢
             
             索引创建好之后,过了一段,DBA都会去做重构索引
             
       SQL调优:
             1.查看执行计划F5
             2. 分析里面的cost 和 影响行数, 想办法降低  

十五、PLSQL编程

1、概述

procedure Language 过程语言 Oracle对SQL的一个扩展

​ 让我们能够像在java中一样写 if else else if 条件, 还可以编写循环逻辑 for while

2、基本语法

declare
                --声明变量
                变量名 变量类型;
                变量名 变量类型 := 初始值;
                  vsal emp.sal%type;  --引用型的变量  
                  vrow emp%rowtype;   --声明记录型变量          
             begin
                --业务逻辑,比如输出一句话:
				dbms_output.put_line()相当于java中 syso 
             end;
declare
   i varchar2(10) := '张三';          
begin
  dbms_output.put_line(i);
end;

3、变量

%type --引用型变量

%rowtype --记录型变量

4、PL条件判断

 if then
     
     elsif then
       
     else 
     
     end if;
declare
   age number := &aaa;
begin
  if age < 18 then
     dbms_output.put_line('小屁孩');
  elsif age>=18 and age <=24 then
     dbms_output.put_line('年轻人');
  elsif age>24 and age < 40 then
    dbms_output.put_line('老司机');
  else 
      dbms_output.put_line('老年人');    
  end if;
end;

5、循环操作

while 循环
      while 条件 loop
        
      end loop;
    
  for循环
      for 变量  in [reverse] 起始值..结束值 loop
        
      end loop;
  
  loop循环  
      loop
        exit when 条件
      end loop;
--输出1~10
declare
  i number :=1;
begin
  while i<=10 loop
    dbms_output.put_line(i);
    i := i+1;    
  end loop;
end;

--输出1~10
declare

begin
  for i in reverse 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

--输出1~10
declare
   i number :=1;
begin
   loop
     exit when i>10;
      dbms_output.put_line(i);  
     i := i+1;
   end loop;
end;

/*

   *
  ***
 *****
  ***
   *   
输出 m  
   x : [-m,m]
   y : [-m,m]
   
   输出所有满足条件的 : abs(y)+abs(x) <=m
   
   m取值
*/
--使用PLSQL输出菱形
declare
   m number := 10;
begin
   for x in -m..m loop
     for y in -m..m loop
       if abs(y) + abs(x) <= m then
         dbms_output.put('*');
       else
         dbms_output.put(' ');
       end if;      
     end loop;
     dbms_output.new_line();
   end loop;  
end;

--使用PLSQL输出三角形,只要是三个角
declare
   m number := 10;
begin
   for x in reverse -m..m loop
     for y in -m..m loop
       if abs(y) + abs(x) <= m and x>=0 then
         dbms_output.put('*');
       else
         dbms_output.put(' ');
       end if;      
     end loop;
     dbms_output.new_line();
   end loop;  
end;

十六、游标

用来操作查询的结果集.相当于JDBC中的ResultSe

语法: cursor 游标名[(参数名 参数类型)] is 查询结果集
       
       开发步骤:
           1. 声明游标
           2. 打开游标       open 游标名
           3. 从游标中取数据  fetch 游标名 into 变量
                         游标名%found :找到数据
                         游标名%notfound : 没有找到数据 
           4. 关闭游标       close 游标名
           
      系统引用游标
           1. 声明游标 : 游标名 sys_refcursor
           2. 打开游标: open 游标名 for 结果集
           3. 从游标中取数据
           4. 关闭游标
                
     for循环遍历游标:
           不需要声明额外变量
           不需要打开游标
           不需要关闭游标      

1、不带参数游标

*/
--输出员工表中所有的员工姓名和工资(不带参数游标)
/*
   游标:所有员工
   声明一个变量,用来记录一行数据  %rowtype
*/
declare
   --游标
   cursor vrows is select * from emp;
   --s声明变量,记录一行数据
   vrow emp%rowtype;
begin
   --1.打开游标  
   open vrows;
   --2.从游标提取数据
   --循环取数据
   loop
       fetch vrows into vrow; 
       exit when vrows%notfound;  
       dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal);
   end loop;
   --3.关闭游标
   close vrows;
end;

2、带参数的游标

--输出指定部门下的员工姓名和工资
/*
   游标: 指定部门的所有员工
   声明一个变量记录一行数据
*/
declare
   --声明游标
   cursor vrows(dno number) is select * from emp where deptno = dno;
   --声明变量
   vrow emp%rowtype;
begin
  --1.打开游标 , 指定10号部门
  open vrows(10);
  --2. 循环遍历,取数据
  loop
     fetch vrows into vrow;
     exit when vrows%notfound;    
      dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal);
  end loop;
  close vrows;
end;

3、系统引用游标

--输出员工表中所有的员工姓名和工资
declare
  --声明系统引用游标
  vrows sys_refcursor;
  --声明一个变量
  vrow emp%rowtype;
begin
  --1.打开游标
  open vrows for select * from emp;
  --2.取数据
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
     dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal);
  end loop;
  close vrows;
end;

4、-使用for循环遍历游标

--按照员工工作给所有员工涨工资,总裁涨1000,经理涨800,其他人涨400
/*
    游标 : 所有员工
    声明一个记录一行数据   
*/
declare
   --声明游标
   cursor vrows is select * from emp;
   --声明一个变量
   vrow emp%rowtype; 
begin
  --1.打开游标
  open vrows;
  --2.循环取数据
  loop
       --取数据
       fetch vrows into vrow;
       --退出条件
       exit when vrows%notfound;  
       --根据不同的职位,涨工资 总裁涨1000,经理涨800,其他人涨400
       if vrow.job = 'PRESIDENT' then
          update emp set sal = sal + 1000 where empno = vrow.empno;
       elsif vrow.job = 'MANAGER' then
          update emp set sal = sal + 800 where empno = vrow.empno;
       else
          update emp set sal = sal + 400 where empno = vrow.empno; 
       end if;       
  end loop;
  --3.关闭游标
  close vrows;
  --4.提交事务
  commit;
end;

十七、例外

例外:(意外)程序运行的过程发生异常,相当于是JAVA中的异常

declare
       --声明变量
   begin
       --业务逻辑
   exception
       --处理异常
       when 异常1 then
         ...
       when 异常2 then
         ...
       when others then
         ...处理其它异常
   end;
   
   zero_pide : 除零异常
   value_ERROR : 类型转换异常
   too_many_rows : 查询出多行记录,但是赋值给了rowtype记录一行数据变量
   no_data_found : 没有找到数据
       
   
   自定义异常:
       异常名  exception;
       raise 异常名  
--查询指定编号的员工,如果没有找到,则抛出自定义的异常
/*
     --错误的演示
     
     1.声明一个变量 %rowtype
     2.查询员工信息,保存起来
     3.判断员工信息是否为空
     4. 如果是 则抛出异常
*/
declare
  --   1.声明一个变量 %rowtype
  vrow emp%rowtype;
  --2 .声明一个自定义的异常
  no_emp exception;  
begin
  --查询员工信息,保存起来
  select * into vrow from emp where empno = 8888;   --抛出异常
  
  if vrow.sal is null then
    raise no_emp; --抛出自定义的异常
  end if;
exception
  when no_emp then
     dbms_output.put_line('输出了自定义的异常');  
  when others then
     dbms_output.put_line('输出了其它异常'||sqlerrm);  
end;

--查询指定编号的员工,如果没有找到,则抛出自定义的异常
/*
     游标来判断
       %found %notfound
    声明一个游标
    声明一个变量,记录数据
    从游标中取记录
       如果有,则不管它
       如果没有就抛出自定义的异常
*/
declare
  --声明游标
  cursor vrows is select * from emp where empno=8888;   
  --声明一个记录型变量
  vrow emp%rowtype;
  --声明一个自定义异常
  no_emp exception;  
begin
  --1.打开游标
  open vrows;
  --2.取数据
  fetch vrows into vrow;
  --3.判断游标是否有数据
  if vrows%notfound then
    raise no_emp;
  end if;
  close vrows;
exception
  when no_emp then
    dbms_output.put_line('发生了自定义的异常');
end;

十八、存储过程

    存储过程: 实际上是封装在服务器上一段PLSQL代码片断,已经编译好了的代码
              1.客户端取调用存储过程,执行效率就会非常高效
         语法:
              create [or replace] procedure 存储过程的名称(参数名 in|out 参数类型,参数名 in|out 参数类型)
              is | as
               --声明部分
              begin
               --业务逻辑 
              end; 
*/
--给指定员工涨薪,并打印涨薪前和涨薪后的工资
/*
    参数 : in 员工编号
    参数 : in 涨多少
    
    声明一个变量 : 存储涨工资前的工资
    
    查询出当前是多少
    打印涨薪前的工资
    更新工资
    打印涨薪后的工资          
*/
create or replace procedure proc_updatesal(vempno in number,vnum in number)
is
   --声明变量.记录当前工资
   vsal number;    
begin
  --查询当前的工资
  select sal into vsal from emp where empno = vempno;
  --输出涨薪前的工资
  dbms_output.put_line('涨薪前:'||vsal);
  --更新工资
  update emp set sal = vsal + vnum where empno = vempno;
  --输出涨薪后的工资
  dbms_output.put_line('涨薪后:'||(vsal+vnum));
  --提交
  commit;
end;

--调用方式1
call proc_updatesal(7788,10);

--调用方式2 用的最多的方式
declare

begin
  proc_updatesal(7788,-100);
end;

十九、存储函数

1、基本语法和例子

存储函数: 实际上是一段封装是Oracle服务器中的一段PLSQL代码片断,它是已经编译好了的代码片段

    语法: 
         create [or replace] function 存储函数的名称(参数名 in|out 参数类型,参数名 in|out 参数类型) return 参数类型
         is | as
         
         begin
           
         end;
    存储过程和函数的区别:
         1.它们本质上没有区别
         2.函数存在的意义是给过程调用   存储过程里面调用存储函数
         3.函数可以在sql语句里面直接调用
         4.存储过程能实现的,存储函数也能实现,存储函数能实现的,过程也能实现
         
    默认是 in     
*/
--查询指定员工的年薪
/*
    参数 : 员工的编号
    返回 : 年薪          
*/
create or replace function func_getsal(vempno number) return number
is
  --声明变量.保存年薪
  vtotalsal number;     
begin
  select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
  return vtotalsal;
end;

--调用存储函数
declare
  vsal number;
begin
  vsal := func_getsal(7788);
  dbms_output.put_line(vsal);
end;

2、java调用存储过程、存储函数

1)基本套路

/*
    JAVA调用存储过程
       JDBC的开发步骤:
          1.导入驱动包
          2.注册驱动
          3.获取连接
          4.获取执行SQL的statement
          5.封装参数
          6.执行SQL
          7.获取结果
          8.释放资源   
*/

/*
   封装一个存储过程 : 输出所有表中的记录
   
   输出类型 : 游标  
*/
create or replace procedure proc_getemps(vrows out sys_refcursor)
is

begin
  --1.打开游标, 给游标赋值
  open vrows for select * from emp;
end;

2)调用存储过程

/**
	 * 查询指定员工的年薪,用存储过程实现
	 * create or replace procedure proc_gettotalsal(vempno in number,vtotalsal out number)
		is
		       
		begin
		  select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
		end;
	 * @throws Exception
	 */
	public void test1() throws Exception {
		//注册驱动
		class.forname("oracle.jdbc.driver.OracleDriver");
		//获取连接
		String url="jdbc:oracle:thin:@106.13.43.205:1521:orcl";
		String user="canghe";
		String password="canghe";
		Connection conn = DriverManager.getConnection(url, user, password);
		//获取执行对象
		String sql="{call proc_gettotalsal(?,?)}";
		CallableStatement state = conn.prepareCall(sql);
		//为问号赋值
		state.setInt(1, 7654);//设置员工编号
		state.registerOutparameter(2, Oracletypes.NUMBER);
		//执行statement
		state.execute();
		//获取结果
		int totle = state.getInt(2);
		//输出结果
		System.out.println(totle);
		//释放资源
		state.close();
		conn.close();
	}

3)调用存储函数

//调用存储函数
		/*
		 create or replace function func_getsal(vempno number) return number
			is
			  --声明变量.保存年薪
			  vtotalsal number;     
			begin
			  select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
			  return vtotalsal;
			end; 
		 */
public void test2() throws Exception{
			//注册驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2.获取连接
			String url = "jdbc:oracle:thin:@106.13.43.205:1521:orcl";
			String username = "canghe";
			String password = "canghe";
			Connection conn = DriverManager.getConnection(url, username,password);
			//3.获取执行SQL的statement
			String sql = " {?= call func_getsal(?)}";
			CallableStatement state = conn.prepareCall(sql);
			//4.封装参数
			//注册返回类型参数
			state.registerOutParameter(1, OracleTypes.NUMBER);
			//设置第二个参数
			state.setInt(2, 7788);
			//5.执行SQL
			state.execute();		
			//6.获取结果
			int totalsal = state.getInt(1);
			System.out.println("年薪 :  ====" +totalsal);		
			//7.释放资源
			state.close();
			conn.close();
		}

3)通过游标输出结果集

/**
	 * 通过游标输出结果集
	 * @throws Exception
	 */
	public void test3() throws Exception{
		//注册驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
		//2.获取连接
		String url = "jdbc:oracle:thin:@106.13.43.205:1521:orcl";
		String username = "canghe";
		String password = "canghe";
		Connection conn = DriverManager.getConnection(url, username,password);
		//3.获取执行SQL的statement
		String sql = "{call proc_getemps(?)}";
		CallableStatement call = conn.prepareCall(sql);
		//接口  --- > 对象 -->实现类的名称
		System.out.println(call.getClass().getName());
		OracleCallableStatement oracleCall = (OracleCallableStatement)call;
		//4.注册输出类型的参数
		call.registerOutParameter(1, OracleTypes.CURSOR);
		//5.执行SQL
		call.execute();
		//6.获取执行的结果
		ResultSet resultSet = oracleCall.getCursor(1);
		while(resultSet.next()){
			int empno = resultSet.getInt("empno");
			String name = resultSet.getString("ename");
			System.out.println(empno +" ==== "+name);
		}
		
		//7.释放资源
		resultSet.close();
		call.close();
		conn.close();
	}

二十、触发器

触发器: 当用户执行了 insert | update | delete 这些操作之后, 可以触发一系列其它的动作/业务逻辑
       作用 : 
            在动作执行之前或者之后,触发业务处理逻辑
            插入数据,做一些校验
            
       语法:
           create [or replace] trigger 触发器的名称
           before | after
           insert | update | delete 
           on 表名
           [for each row]
           declare
           
           begin
             
           end;
           
       触发器的分类:
           语句级触发器:   不管影响多少行, 都只会执行一次
           
           行级触发器:     影响多少行,就触发多少次
                  :old  代表旧的记录, 更新前的记录
                  :new  代表的是新的记录
--新员工入职之后,输出一句话: 欢迎加入黑马程序员
create or replace trigger tri_test1
after
insert
on emp
declare

begin
  dbms_output.put_line('欢迎加入黑马程序员');
end;

insert into emp(empno,ename) values(9527,'HUAAN');

--数据校验, 星期六老板不在, 不能办理新员工入职
--在插入数据之前
--判断当前日期是否是周六
--如果是周六,就不能插入
create or replace trigger tri_test2
before
insert 
on emp
declare
 --声明变量
 vday varchar2(10);
begin
  --查询当前
  select trim(to_char(sysdate,'day')) into vday from dual;
  --判断当前日期:
  if vday = 'saturday' then
     dbms_output.put_line('老板不在,不能办理入职');
     --抛出系统异常
     raise_APPlication_error(-20001,'老板不在,不能办理入职');
  end if;
end;

insert into emp(empno,ename) values(9528,'HUAAN2');

--更新所有的工资 输出一句话
create or replace trigger tri_test3
after
update
on emp 
for each row
declare

begin
  dbms_output.put_line('更新了数据');
end;

update emp set sal = sal+10;



--判断员工涨工资后的工资一定要大于涨工资前的工资
/*
   200 --> 100
   触发器 : before
      旧的工资 
      新的工资
      如果旧的工资大于新的工资 , 抛出异常,不让它执行成功   
      
      
   触发器中不能提交事务,也不能回滚事务 
*/
create or replace trigger tri_updatesal
before
update
on emp
for each row
declare

begin
  if :old.sal > :new.sal then
    raise_application_error(-20002,'旧的工资不能大于新的工资');
  end if;
end;

update emp set sal = sal + 10;
select * from emp;

update emp set sal = sal - 100;


/*
   模拟mysql中ID的自增属性 auto_increment 
   insert into person(null,'张三');  
   
   触发器:
   
   pid=1  insert  pid=1
   
   序列 : create sequence seq_person_pid;       
*/
create table person(
    pid number primary key,
    pname varchar2(20)   
);

insert into person values(null,'张三'); 

create sequence seq_person_pid;

--触发器
create or replace trigger tri_add_person_pid
before
insert
on person
for each row
declare

begin
  dbms_output.put_line(:new.pname);
  --给新记录 pid 赋值
  select seq_person_pid.nextval into :new.pid from dual;
end;

insert into person values(null,'张三'); 


select * from person;

相关阅读

解决sql server2008数据库安装之后,web程序80端口被占

前言: 原来电脑上的Apache一直使用正常,在安装sql server2008后,突然发现Apache无法启动,检查了一下是因为80端口被强制占用了。 解

强化学习系列(七):n-step Bootstrapping (步步为营)

一、前言 在强化学习系列(五):蒙特卡罗方法(Monte Carlo)和强化学习系列(六):时间差分算法(Temporal-Difference Learning)中,我们介绍了

腾讯Oceanus实时计算平台架构设计---学习总结

一、背景 实时计算应用主要分为以下四类:(1)ETL:ETL应该是目前实时计算最普遍的应用场景。例如在TDBank的数据链路中,TDSort读取消息缓

BNF学习笔记

巴科斯范式(Backus-Naur Form)是一种由Backus和Naur引入的形式化符号表示编程语言的语法。 规范 引号中的字符表示这些他们本身,quo

分布式架构的演进(来自咕泡学院学习笔记)

分布式的发展史1946年情人节(2.14),世界上第一台电子数字计算机诞生在美国宾夕法尼亚大学,他的名字叫ENIAC,这台计算机占地170米,重

分享到:

栏目导航

推荐阅读

热门阅读