Marco's Java【Oracle数据库基础】

Oracle数据库

---

---

数据库简单来讲就是一个存放数据的仓库，并存放在计算机中，实现数据的持久化，数据库的中数据要按照一定的格式来存放，并可以实现多个用户的数据共享。Oracle数据库是基于标准SQL语言的数据库产品

数据库DataBase包含5部分：

• 数据库管理系统 DataBaseManagementSystem
• 数据库应用系统 DataBaseAplicationSystem
• 数据库管理员 DataBaseAdministrator
• 最终用户 FinalUser
• 数据库系统 DataBaseSystem
  

重要的NET文件配置：
listener.ora:监听器配置(服务器端)：通过增加客户端的IP，端口，协议来监听客户端，客户端就有访问此数据库的权限
tnsnames.ora:本地配置(客户端)：通过增加服务端的IP，端口，协议来访问对应的服务端，前提是服务端口已经配置好了监听你的权限，否则无法访问

Tips：数据库中的oradata/orcl(全局数据库名称)文件放置的是你自己存放好的数据

交互式操作使用SQL语句：
数据库表 table
索引 index
视图 view
序列 sequence
同义词 synonym
表空间 tablespace

批处理操作使用PL/SQL编程(属于ORACLE SQL的扩展语句)：
存储过程 procedure
函数 function
触发器 trigger
包 package

一般我们使用的最多的SQL语句是数据库表table，表与表之间的关系有三种

• 一对一：一个员工编号对应一个员工
• 一对多：一个部门对应多个员工
• 多对多：一个员工可能做了多个项目，那么一个项目下面可能又有很多员工分工完成

---

SQL(Structured Query Language)语句

DQL(数据查询语言)
select(查询)
DML（数据操作语言）
insert(插入元素内容)、update(修改或更新元素内容)、delete(删除元素内容)
DDL（数据定义语言）
create(创建表结构)、alter(更改表结构)、drop(删除表结构)
DCL（数据控制语言）
grant(授予权限)、revoke(撤销权限)

需要区分的是DML（数据操作语言）和DDL（数据定义语言）都有修改，删除功能
但是数据操作语言主要是针对表中的数据，而数据定义语言主要是针对数据库对象（表、索引、视图、触发器、存储过程、函数、表空间等）

---

DQL(数据查询语言)

DQL中的关键字只有select，但是确实这四种语句中最常用也最为复杂的语句，要了解查询语句首先需要了解表中的一些概念

关系：整个二维表
关系名：表格名称：例如员工信息表(EMP)
元组：行数据（记录）：类似于一个员工对象中存放的信息
属性：列数据（字段）
属性名：列名称（字段名）：每一列字段的名称，例如name，age等
主键：唯一确定元组的属性组（关键字）：类似于Map集合中的键值，有唯一性
域：属性的取值范围：就是一个属性的上限下限范围

那么列举我们常用的简单的select查询操作
简单查询语句：

select * from emp;//查询员工表所有数据
select * from dept;//查询部门表所有数据
select empno, ename, sal  from emp;//从员工表中查询员工号，员工姓名和工资

使用算术表达式查询：

select empno, ename, sal, sal*12 from emp//查询员工号，员工姓名和年工资

字段别名：

select empno, ename "Ename", sal*12 "Anual Salary" from emp;//查询员工号和年工资

那么table中显示工资的字段就不再是salary*12而是Anual Salary，增强了可读性，字段别名的命名方式有三种：
1.sal * 12 Anual Salary
2.sal * 12 “Anual Salary”
3.sal * 12 as Anual Salary
注意： 别名如果含有空格或其他特殊字符或大小写敏感，需用双引号括起来
否则可能会读取失败，当然as可以省略，不过加上as也变相的增强了可读性

连接运算符：

select ename || ' is a ' || job from emp;

使用上述方式可以将 ename和job这两个字段合并成一个字段，配合字段别名会有意想不到的效果

select ename || ' is a ' || job as SelfInfo from emp;

注意：
1.ORACLE数据库中的 + 只有算数运算的功能，连接两个字符串的功能仍需要 || 来实现
2.ORACLE数据库中" "中括起来的内容只能是字段别名，字符串需要使用’ '括起来

消除重复：

select distinct job,deptno from emp;//查找员工的职位和部门编号字段并去除重复

注意：
Distinct是对所有字符的组合去重

排序：

select ename, sal from emp order by sal desc;//查找员工的姓名和工资字段并按照工资升序排序
select * from emp order by deptno asc, sal desc;//先按照部门的号码升序排序，再按照工资降序排序

注意：
1.asc(ascend)为升序，desc(descend)为降序
2.默认排序方式为升序
3.需要对哪个信息排序就要在后面加对应的关键字，并用逗号隔开

查询具体指定信息：
此时需要使用到关键字where，where后面加上需要查找的条件

select * from emp where hiredate = '23 1月 1982';//查找入职日期为1982/1/23的员工信息
-- 查找员工的姓名，职位，工资字段的信息，并满足工资在2000-4000区间并按照工资大小排序
select ename, job, sal from emp where sal > 2000 and sal < 4000 order by sal;
select ename, deptno from emp where deptno = 10 or deptno = 20;
select * from emp where job != 'MANAGER';//查找职位不为'MANAGER'的员工信息
select * from emp where comm is not null;//查找comm不等于null的员工信息

注意：
1.ORACLE中日期有自己的格式：例如1982/1/23的格式是23 1月 1982，并且要用’ '括起来
2.ORACLE中的与逻辑运算符不是&&而是and
3.ORACLE中的与逻辑运算符不是||而是or

Tips:
1.sal > 2000 and sal < 4000 也可以替换为 sal between 2000 and 4000
2.deptno = 10 or deptno = 20 也可以替换为 deptno in (10,20)
3.job != ‘MANAGER’ 也可以替换为 job <> ‘MANAGER’ 或者 not in ‘MANAGER’

模糊运算符：
此时需要使用到关键字like，主要用于模糊查找(不具体的查找)

select * from emp where ename like '%A';//查找员工姓名最后一个字母为A的的员工信息
select * from emp where ename like 'A%';//查找员工姓名第一个字母为A的的员工信息
select * from emp where ename like '%A%';//查找员工姓名包含字母为A的的员工信息
select * from emp where ename like '_A%';//查找员工姓名第二个字母为A的的员工信息

这里涉及到ORACEL数据库的通配符概念：
%：表示0个或者多个字母，和正则表达式不一样，正则中有一个类似的表达式 X* 表示的是X的值可以有0个或者多个，而这里指代的是0个或者多个不同或者相同的字母
_: 表示任意的一个字母
注意：以上字符如果不当作通配符使用需要转义，例如’%_%’ escape ‘’，这就代表一个 ’ _ '字符的意思

Oracle数据库函数

Oracle函数就和Java中方法的意思差不多，函数只是取出库中值的副本进行处理，并不改变库中数据本身的值
Oracle函数包含了两大类 - 单行函数 - 多行函数

单行函数	多行函数
数字函数，日期函数，字符函数，转换函数，通用函数	聚合函数

单行函数

数字函数

数字函数和Java的java.lang.Math类中很多方法类似
对元素取绝对值：

select abs(sal) as Salary from emp;//

向上取整和向下取整：

select ceil(10.1) as Salary from dual;//向上取整
select floor(-10.1) as Salary from dual;//向下取整

幂：

select power(10,3) as Salary from dual;//10的3次幂

四舍五入：

select round(123.1234,2) as Salary from dual;//保留小数点后两位并四舍五入

开方：

select sqrt(9) from dual;//对9开方

字符函数

Concat：将两个字符串连接起来，用 || 也可以实现这个功能

select concat('I ','love you') as Message from dual;

Lower/Upper：将字符串全部小写/全部大写

select lower(ename), sal as ename from emp;

Initcap: 首字母大写，其他字母小写

select initcap(ename), sal as ename from emp;

Replace: 替换字符串中的某一个字符为另一个字符

select replace ('He love you','He','I') as MSG from dual;//输出I love you

日期函数

Add_months: 返回原始date加上指定月数后的日期

select hiredate,add_months(hiredate,12) from emp where empno = 7844;

Months_between: 返回两个日期相差的月份值

select months_between('3 5月 2008','3 5月 2005') from dual;

转换函数

转换函数分为自动类型转换函数和强制类型转换函数
select 10 + '20' from dual 输出的结果为30，这说明Oracle数据库中存在着隐式类型转换，但是建议写上转换函数，避免产生歧义

• to_number() ： 强制转为数字类型
• to_char()： 强制转为字符类型
• to_date()： 强制转为日期类型

to_date()函数是可以指定日期格式的，这一点和Java中的Dataformat类有些相似

select to_date('2010/12/12','yyyy/mm/dd') from dual;

注意：
1.字符转为数字中的字符串必须是有效的数字字符串
2.数字类型不能直接转为日期类型，需要以char为媒介，因此转换函数之间是可以互相嵌套的
3.to_date()中如果要进行格式转换不能包含中文！

其他函数

NVL: 如果exp1的值为null，则返回exp2的值，否则返回exp1的值

-- 查询员工编号，姓名，职位，年薪(工资*12 +奖金)
select empno, ename, job, sal*12 + nvl(comm,0) as AnualSal from emp;

NVL2: 如果exp1的值不为null，则返回exp2的值，exp2为努力了则返回exp3的值(用的不多)

-- 以下结果和上面的结果一样
select empno, ename, job, NVL2(comm,sal*12 + comm, sal*12) as AnualSal from emp;

多行函数

聚合函数

聚合函数见名知意，就是通过函数将多行聚合为一行。那么我们常用的几种函数有
max(): 求最大值
min(): 求最小值
sum(): 求和
avg(): 求平均值
另外还有一个很重要的语句叫分组语句 group by(分组)

案例：现在我们有个需求，需要按照部门编号分组并展示每一个部门员工的最高工资，最低工资，部门总工资，部门平均工资，并按照部门编号升序排序

select deptno, 
       max(nvl(sal,0)) as maxsalary, //求最大值
       min(nvl(sal,0)) as minsalary, //求最小值
       sum(nvl(sal,0)) as totalsalary, //求和
       round(avg(nvl(sal,0)),2) as avgsalary, //求平均值，并且保留小数后2位数四舍五入
       count(*) from emp group by deptno order by deptno; //按照部门编号分组并按部门编号升序排序

注意：
1.出现在select列表中的字段，若不是包含在多行字段中，那么该字段必须同时在group by 子句中。
就如deptno不在select例表中，所以就必须在group by 子句中。

2.包含在group by子句中的字符则不必须出现在select列表中。这个也很好理解，也就是说deptno如果已经在group by 子句中了，那么也可以出现在select列表中，或者不出现。

3.group by 后面允许出现多个字段

select deptno, mgr max(sal) from emp group by deptno, mgr;//先按照部门分组，再按照经理编号分组

4.having关键字
假设我们需要按照部门分组并且统计每一个部门有多少个人数，那么此时就不能使用where来筛选

select deptno, count(*) from emp group by deptno having count(*) > 3;

注意：
where 后面不能加多行函数(例如sum(),avg())，having后面可以加上多行函数
where: 分组之前的筛选条件，写在group by 之前，用来过滤行
having: 分组之后的筛选条件，写在group by 之后，用来过滤组

---

DML(数据操作语言)

插入语句：

记录插入

全值插入：insert into 表名 values(值1，值2…)
部分插入：insert into 表名(字段1，字段2…) values(值1，值2…)
注意： 字段的个数和值的个数，类型，顺序三者要一致

结果集插入

// emp2和emp1的结构、个数、顺序要完全一致
insert into emp2 select * from emp1;
// 插入的表中含有哪些元素，被插入的表中必须申明这些元素
insert into emp2(empno，ename) select empno，ename from emp1;

修改语句：
update 表名 set 字段名 = 修改的值

update emp set sal = 3500, comm = 500 where empno = 7521;//修改员工的工资和奖金

需要修改多个字段的时候用逗号隔开即可

删除语句：
delete [from] 表名 [where…]

delete * from emp where enam = 'SCOTT';//删除某个员工的记录

Oracle数据库还提供了另一个删除关键字truncate table，叫做截断表，使用的方法是一样的，但是两者有本质的区别
面试题：
Oracle数据库中delete和truncate的区别和联系？
相同点：
都可以删除表中的数据
不同点：
1.delete语句后面可以根where子句
2.truncate table语句效率比delete更高
3.delete有日志记录，才会有回滚操作，truncate没有日志记录，所以删除之后不能撤掉操作。

---

多表联合查询

目前使用最多的两个多表联合查询的版本分别是SQL92版本和SQL99版本

SQL92版本

笛卡尔积查询：

select empno, ename, job, sal, dname from emp,dept;

假设左表的记录有X条，右表的记录有Y条，那么根据笛卡尔积查询到的结果为X*Y。但是实际情况下，左表的记录中的某一个字段和右表中的某一个字段是相同的，那么此刻我们想要的结果没有那么多，比如说员工表中有部门ID，一个员工对应了一个部门，员工表有4个参数，部门表3个参数，我们预想的是联合表有4行值，但是笛卡尔积查询后有12条，最终查询到的结果没有意义。因此这种查询很少会用到。

等值连接：

select empno, job , sal , dname from emp, 
	dept where emp.deptno = dept.deptno;

注意：
1.等值连接只和值有关系，跟名称没有关系( 比如说 emp.deptno 完全可以替换成 emp.no 和 dept.deptno 做匹配) ，不过为了后期好区分，建议还是将相关联的字段取相同的名字。
2.当select后面需要查询的子句字段中包含了where后面的等值字段时，我们需要指定这个字段是属于哪一个表的(比如 emp.deptno 或者 dept.deptno 指代不同表的相同值)
3.如果表的名字很长，我们也可以给表取别名(比如 emp e 或者 dept d，和字段别名命名差不多)
案例：查询部门为20的员工名称和部门名称

select ename, dname from emp e, dept d 
	where e.deptno = d.deptno//连接条件
	and d.deptno = 20;//筛选条件

非等值连接：

//查询员工的工资等级并列出员工姓名，工资和等级
select ename, sal, grade 
	from emp e, salgrade s
	where e.sal > s.losal and e.sal < s.hisal

外连接：
使用外连接可以看到参与连接的某一方不满足连接条件的记录，就好比部门编号一共有4个10、20、30、40，但是表中的员工数据目前只有10、20、30，如果我想要将40的数据列举出来，那么就要使用外连接
左外连接:查询左表所有匹配信息和右表中所有信息
右外连接:查询右表所有匹配信息和左表中所有信息
下面这个案例使用的是左外连接，原因是dept表中的deptno的个数比emp中的多

select * from emp e, dept d 
	where e.deptno(+) = d.deptno;

自连接：
假设员工表中有这样的两条信息1.员工编号 2.经理编号，而经理编号实则又是员工编号，那么如何在同一张表中将员工的信息和经理的信息放在同一列，就要使用到自连接。自连接的逻辑很简单，就是将一张表分为两张表来使用。

select e1.empno, e1.ename, e2.ename from emp e1, emp e2 
	where e1.mgr = e2.empno;

SQL99版本

交叉连接(笛卡尔积)：

select * from emp cross join dept;//功能和92版本一样，写法不相同

on连接(等值连接)：

select * from emp e join dept d on e.deptno = d.deptno;

自然连接：
自然连接和等值连接的区别：
1.相同字段名的名称一定要相同，也就是说要有同名列作为连接条件
2.不能起别名
3.同名列中的数据类型不同，则会出错
4.当有多个同名列时，自然连接必须要满足多个条件才会有结果，求出来的是两个表中所有相同列匹配到的交集

USING连接：
假设员工表和部门表中有两列相同属性，顺序和名称的字段
deptno和loc(部门所在地)

select * from empno join deptno using(deptno,loc);

通过这种方式我们可以添加两个字段名来进行分析，求出来的是我们指定的字段名的字段值匹配到的交集，这种方式和自然连接有些类似但不完全一样。

USING连接和自然连接的区别

比自然连接更加灵活多变，自然连接求出来的是两个表中所有相同列匹配到的交集，而USING连接可以指定的字段名去匹配

外连接：
相较于92版本更加了全外连接获取两个表中所有指定数据，并且完善了left和right，使用其中任何一个都可以达到相似的效果
左外连接:获取左表中所有数据和右表中的匹配数据

select * from emp e left join dept d on e.deptno = d.deptno

右外连接:获取右表所有数据和左表中的匹配数据

select * from emp e right join dept d on e.deptno = d.deptno

全外连接:获取左表所有数据和右表中所有数据

select * from emp e full join dept d on e.deptno = d.deptno

三表联合查询

三表查询的前提是三个表中两两之间要有相同数据，类型的字段，作为互连条件

案例： 查询姓名是MARCO的学生学了哪个老师的哪门课程
SQL92：

select sid, sname, sex, age, tname, cname 
		from student s, teacher t, course c 
       where s.tid = t.tid and t.cid = c.cid //连接条件
       and s.sname = 'MARCO';//筛选条件

SQL99：

select sid, sname, sex, age, tname, cname from student s
       join teacher t on s.tid = t.tid //连接条件 
       join course c on t.cid = c.cid//连接条件
       where s.sname = 'MARCO';  //筛选条件 

通过上述比较发现SQL92相对来说较为简洁，SQL99的语法更加清晰，用 join…on 作为连接条件，where 作为筛选条件区分开来，不过使用的话还是看个人喜好。

子查询

案例：

>> 列出薪金高于公司平均薪金的所有员工。
select * from emp 
       where sal > nvl((select avg(sal) from emp),0);//子查询     
>> 列出与“SCOTT”从事相同工作的所有员工。
select * from emp 
       where job = (select job from emp where ename);//子查询

子查询步骤：
1.先查询子查询(例如我可以先用select语句查询到员工的平均工资作为一个筛选后的结果)
2.将子查询的结果作为主查询的判断辅助条件
3.最终需要的是主查询的结果，子查询可以是多重嵌套，不过嵌套越多，查询的效率越低

以上的案例都是子查询中的一个类型，叫单行子查询 ，这里我们就要涉及到子查询的分类了
单行查询：查询的结果只有一行(例如select avg(sal) from emp) 的结果是员工工资平均值，只有一个数值)
多行查询：查询的结果有多行记录(见以下详解)

多行子查询
多行子查询的结果一般会有多个结果，例如select sal from emp where job = 'SALESMAN'的结果可能会有多个
但是where后面作比较的只能对应一个值。
那么我们就可以用到all和any关键字(实则上可以用max(),min()合理替换)

ALL: 和子查询返回的所有值来比较(也就是说> ALL 表示大于所有值，即大于我们的最大值)

select * from emp where sal < all(select sal from emp where job = 'SALESMAN');

ANY:和子查询返回的任意值来比较(也就是说> ANY 表示至少大于一个值，即大于我们的最小值)

select * from emp where sal < any(select sal from emp where job = 'SALESMAN');

案例：
查询部门20中职务同部门10的雇员一样的雇员信息

select * from emp 
	where job in (select job from emp where deptno = 10)//这里可能会产生多行的结果，所以用in
	and deptno = 20;

查询每个部门平均薪水的等级

select d.deptno,d.dname,round(t.sal_avg,2),grade
from salgrade s,(select deptno,avg(sal) sal_avg from emp group by deptno) t,dept d
where t.deptno = d.deptno//连接条件
     and t.sal_avg >= s.losal//筛选条件
     and t.sal_avg < = s.hisal 
     order by t.sal_avg

子查询注意事项：
1.子查询必须使用()括住
2.除非进行分页查询分析，否则不要在子查询中使用ORDER BY 子句
3.子查询可以放在where子句以及from子句后面，当放在from子句后的话，可以把查询结果当作另一个表来使用

---

数据库常用对象

用户 User

权力大小： 角色 > 权限
打个比方老师就是一个角色，老师有点名的权限，有监考的权限，因此一个角色可能会有多个权限，也就是说角 色是权限的集合，实际开发的时候，直接给用户角色，我们先来引入数据库的常见角色
数据库角色：
DBA：管理员角色
CONNECT：登录角色
RESOUCE：资源角色
步骤
创建用户： create user 用户名 Identified by 密码
授予角色或者权限：
grant connect to marco //授予临时用户角色
grant resouce to marco//授予正式用户角色
grant dba to marco//授予管理员角色(==这个角色只能由sys来授予==，一般不去授予)
注意：
1.普通用用户是不能给自己授权的
2.一次性可以授予多个权限(例如：grant connect,resouce to marco)

撤销权限
revoke RESOURSE from marco ;
删除用户
drop user marco;

---

表(Table)

制表格式：

create table 表名	( 
      字段1  数据类型,
      字段2  数据类型,
      字段3  数据类型，
      ...
      字段n 数据类型
)

---

Oracle的数据类型

字符类型

• 固定字符类型 Char
  Char(10)：字节长度为10的字符串
  如果数据长度不够总长度，数据库会默认用空格补齐
• 可变字符类型 Varchar2
  Varchar2(10)： 字节长度为10的字符串
  如果数据长度不够，将不够的部分截取
• 可变字符类型 Nvarchar2
  Nvarchar2(10)：字符长度为10的字符串
  如果数据长度不够，将不够的部分截取

以上三种字符类型的最长字节长度都是4000，一般比较常用的是Varchar2，一般客户注册时输入账号密码的时候是不知道后台

Varchar和Varchar2的区别
1.varchar是标准sql语句中的，而varchar2是oracle提供的独有的数据类型。
2.varchar对于汉字占两个字节，对于英文是一个字节，占的内存小，varchar2都是占两个字节。
3.varchar对空串不处理，varchar2将空串当做null来处理。
4.varchar最大长度是2000，varchar2最大长度是4000

数字类型

• 整型
  number(3)： 3位整数 -999~999
• 浮点型
  number(5,2)： 5代表总长，2是小数点之后的部位
  如果存储超过了小数规定的位数，会进行四舍五入，并保留规定的小数的位数

如果括号中什么都不写number()默认的情况下，精度为38位，取值在1-38之间

日期类型

Date ：存储日期和时间 在Java的类中对应的是 java.sql.Date(该类是Java.util.Date的子类)
Timestamp：比date更精确(可以精确到时分秒)

LOB

BLOB：存储二进制对象，如图像、音频和视频文件 （实际开发，存储的都是多媒体的地址）
CLOB：存储字符格式的大型对象

---

Oracle的约束(Constraint)

1.主键约束primary key：列数据唯一且不能为空，主键可以包含表的一列(可以在字段定义)或者多列(必须在表级定义)
2.非空约束not null：值不能为空
3.唯一约束unique: 字段(列)值必须唯一
4.检查约束check: 某列取值范围限制、格式限制等
5.外键约束foreign key: 用于两表间建立关系，需要指定引用主表的那列，外键通常用来约束两个表之间的数据关系，定义外键的那张表称为子表，另一张表称为主表。在表的创建过程中，应该先创建主表，后创建子表。

主键约束

要求学生的学号不能为空且必须唯一

sid varchar2(5) primary key//字段级约束写法
constraint pk_students_sid primary key(sid)//表级约束写法

非空约束

要求学生姓名不能为空

sname varchar2(20) not null//字段级约束写法

唯一约束

要求email必须是唯一的

email varchar(50) unique//字段级约束写法
constraint uk_students_email unique(email)//表级约束写法

检查约束

要求性别必须为男或者女

sex varchar2(6) check(sex = 'men' or sex = 'women')//字段级约束写法
constraint ck_students_sex check(sex = 'men' or sex = 'women')//表级约束写法

外键约束

假设现在有两张表，一张班级表(主表)，一张学生表(子表)，通过班级编号cid将两个表关联起来
通过约束让学生表中cid必须在班级表的cid规定的范围之内，这就叫做外键约束

constraints fk_students_cid foreign key(cid) reference clazz (cid);

注意：
1.子表中的cid类型、长度要和主表中的cid一致，但是名称可以不一样
2.必须先创建主表，再创建子表，因为子表的数据是被关联的，若主表数据不存，子表的数据就不能引用主表的
3.插入数据的时候要先给主表插入，再给子表插入数据
4.必须先删除子表，再删除主表
5.Oracle支持级联删除，意思就是将主表的记录删除了，子表被关联的记录跟着就被删除了
6.Oracle支持set null，意思就是将主表的记录删除了，子表被关联的外键那一列置为null
7.外键约束可以为多个，且外键约束可以为null

补充：外键的级联cascade删除和set null置空删除

constraints fk_students_cid foreign key(cid) reference clazz(cid) on delete set cascade
constraints fk_students_cid foreign key(cid) reference clazz(cid) on delete set null

这两方式的区别就是当使用cascade删除主表外键关联值时，子表关联的外键的所有信息将一并被删除
当使用set null删除主表外键关联值时，子表的外键关联字段将置为null，这种做法相对来讲比较合理，当然还是要根据需求要进行选择

---

约束还分为字段级约束和表级约束
字段级约束
约束直接写在字段之后，系统会自动的给每一个约束起名字，所以如果发生错误，程序员不方便去查看到底是哪个约束出现了错误，但是这种方法相对来说很简单
例如：sid varchar2(5) primary key
表级约束
所有的约束全都写在所有的字段后面，程序员可以自己给表取名字，因此发生错误了，程序员可以知道问具体出现在哪个地方，但是这种方法相对来说比较复杂
例如：constraints pk_students_sid primary key(sid);

注意：
1.一个表最多只能有一个主键
2.一个主键可以包含多个字段(因此我们可以把需要用到主键约束的字段全部放在主键约束的括号中)
3.只有表级约束才能写联合主键(约束多个字段)
constraints pk_students_sid_sname primary key(sid, sname);
4.一个字段可以写多个约束
5.唯一约束可以为空，而且可以多个为空(打个比方，如果邮箱设置了unique，如果多个成员都没有写有邮箱，也是被允许的)
6.not null只能写在字段(列)级约束中，不能写在表约束

约束的完整性

域完整性约束(非空+检查)：代表字段(列)的约束，一般是约束列的规则
实体完整性约束(主键+唯一)：一般是约束行的规则
引用完整性约束(外键约束)：表和表之间的约束

---

表的操作

除了之前提到的create创建表，还有以下表操作

删除表:
drop table students;

添加一列：
alter table students add habits varchar2(50);
添加多列：
alter table students add habits varchar2(50) add qqNumber varchar2(20);

删除一列:
alter table students drop column qqNumber;
删除多列:
alter table students drop column(habits ,qqNumber);

添加约束:
alter table students add constraints pk_students_habits primary key(habits);
删除约束:
alter table students drop constraints pk_students_habits;

---

序列(Sequence)

我们的每张表基本本上都会设一个主键，用来区分每条记录没有重复，但是客户在输入的时候并不知道自己的输入的数据是否重复，并且如果输入了重复，会报错，因此为了保证主键这一列永远不会重复(让系统自动生成主键)，我们可以使用序列来解决。使用序列(一种对数字类型做自增或者自减的操作)。

创建序列

create sequence seq_1 //默认序列，初始值为1，增量为1

查询序列

select seq1.nextval from dual;//查看下一个值
select seq1.currval from dual;//查看当前值

使用序列

当我们使用insert语句的使用就要使用序列

insert into student values(seq1.nextval,'marco','men',18);//此处的seq1.nextval就替代了手动输入学号

删除序列

drop sequence seq_1/

注意： 当序列中包含序列编号的行被删除之后，那么这个序列将永久删除，这个编号将不会再出现

---

索引(Index)

索引就好比是字典的目录，按照索引来查找时，查找的速度会更快，数据库会默认的给主键或者Unique去创建索引，因此为我们按照主键/Unique值来查找数据速度会更快。当我们不想按照索引查找，想使用其他字段查找数据并且达到和主键一样的效果，就可以手动去添加索引。

create index index_1 on student(sname)//给student表的sname字段创建索引

注意： 当使用查询语句select的时候索引才会生效

索引的结构

索引默认采用的是m阶B树结构(balance tree of order m)，是一种平衡树
树根
树干
树枝
树叶： 数据全部集中在叶子节点
ROWID的格式：

数据对象编号	文件编号	块编号	行编号
AAABnl	AAF	AAAAAP	AAA

假如我们对student表中的sname创建了索引，当我们去查询sname的时候，会先通过索引表找到索引，索引是按照B树结构，分区块范围查找，假设我们要查找的sname的索引为30，那么我们查找数据的顺序如下

找到区段中对应的索引，最后再找到我们要查询字段的ROWID，通过ROWID定位到数据。因此我们对数据做查询，实则是查询它的ROWID。

索引的缺点

1.索引会单独的占用内存空间，消耗空间资源
2.当我们执行了delete或者insert语句之后，DBMS后台要对索引做大量的位移维护操作

建立索引的场景

1.主键(Primary key)和唯一键(Unique)
2.数据重复率较低的字段可以建立索引，比如电话，邮箱
3.经常查询的字段可以建立索引
4.需要经常排序的字段可以建立索引
5.联合字段索引，例如省+市，必须查询到多个字段的时候才会起作用

create index index_m on map(province,city)；//联合查询

注意：
1.表的数据是按照ROWID排序的
2.根据ROWID查询某条数据，查询的速度最快

ROWNUM

rownum(伪列): 也就是假的列，不是数据表中原本的列
Oracle可以跨用户访问，前提是你必须是DBA角色

select e.* from SCOTT.emp e

案例：查询员工中工资前三的工资

select rownum, e.* from (
	select * from SCOTT.emp order by sal desc) e

注意：
1.rownum支持 <,<=，不支持 >，>=
2.rownum支持 = ， 但是只支持 = 1，因为指针只能从1开始
3.rownum虽然不支持 > ，但是rownum取得别名可以支持 > ,这个比较难理解，举个栗子 ：查询员工表中工资6-10的工资（三层分页查询）
1.查询最里层数据并按照数据排序，生成一个临时的表
2.第二层按照最里层的结果集查询一个rownum，并给它起一个别名,并按照相应需求操作(比如 <= 10)
3.最外层按照第二层的结果查询你所需要的字段，并且在where子句中对第二层的rownum的别名做 >= 操作

select e2.* from (
	select rownum r2,e1.* from (
		select e0.* from SCOTT.emp e0 order by sal desc) e1 
		where rownum <= 10) e2
		where r2 >= 6

---

视图(View)

创建新视图：

creat view name
as .....(查询到的表)

视图的作用：
1.封装复杂的sql语句，方便下次调用
2.可以做表的权限划分

视图的特点：
1.视图是一张虚拟的表，但表能做的所有操作，视图一样也能操作
2.修改视图的数据会连带的影响到原表数据，因此开发中我们会将视图设为只读视图，防止数据被随意篡改，只需要在创建视图语句的末尾加上with read only

create view view_4 as
	select * from  emp
	with read only;//只读

3.删除视图不会影响表的数据，但是删除表视图的数据也就不复存在，视图是基于表而存在的

---

事务(Transaction)

事务是一个操作序列。这些操作要么都成功，要么都失败，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位。事务是为了保证数据库的完整性
就以取钱为例子 对应的就是DML中的update语句
1.插卡
2.数钱的数目，按确定（查询语句）
3.取款 （修改语句）

update account set money = money - 1000//取钱

4.退卡
这个时候如果停电了，但是钱已经被扣除了，这个时候该如何处理呢？
答案就是rollback回滚
当执行sql语句的时候，如果出现异常，表示sql有误，那么就让他
进入到catch语句中对事物进行rollback()。如果没有出现异常，表示sql语句正常执行，在代码的最后执行commit() 提交数据即可。

事务的开启：
执行DML语句时

事务的结束：
1.commit(提交)，rollback(回滚)的时候
2.当执行create，drop，alter，grant语句的时候，事务自动提交
3.当正常关闭sql窗口时，事务commit，当异常关闭时，事务rollback

事务的四大特点：
1.atomicity(原子性)：事务内的操作是一个整体，要么都成功，要么都失败
2.consistency(一致性)：当事务中的一个操作失败了，所有更改的数据必须回滚到修改前的状态
3.isolation(隔离性)：事务查看数据是，数据所处的状态，要么是另一个事务修改数据前的状态，要么是修改后的状态，是无法看到中间状态的
4.durability(持久性)：事务完成后，对于系统的影响是持久性的

事务的隔离级别

脏读： 当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。

不可重复读： 不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

幻读： 幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。

因此，当某一个时间段，只有一个用户操作数据库的某个表时候，不会出现上述问题，效率会降低，但是安全性会提高。