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、表的五大约束
-
主键约束
primary key
-
非空约束
not null
-
唯一约束
unique
-
添加外键约束：
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; -
检查约束
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)中，我们介绍了
一、背景 实时计算应用主要分为以下四类：（1）ETL：ETL应该是目前实时计算最普遍的应用场景。例如在TDBank的数据链路中，TDSort读取消息缓
巴科斯范式（Backus-Naur Form）是一种由Backus和Naur引入的形式化符号表示编程语言的语法。 规范 引号中的字符表示这些他们本身，quo
分布式的发展史1946年情人节(2.14)，世界上第一台电子数字计算机诞生在美国宾夕法尼亚大学，他的名字叫ENIAC,这台计算机占地170米，重