oracle笔记：新手上路必备。花了两天时间整理出来的，详细的不能再详细了。

总结一下这几天学习oracle的笔记，花了两天时间，终于给搞好了，本篇博客适合初学者学习，大佬可以绕道。。。

Oracle中用户登录、创建用户

​ 在sqlplus中登录，

sqlplus 用户名/密码

//成功后可通过，查看当前账号是谁登录
show user
//将会话环境修改为英文
alter session set nls_language=english;
//修改为简体中文
alter session set nls_language='simplified chinese';

​ 创建用户

• connect角色，基本的连接
• resource角色，程序开发
• DBA角色，数据库管理

//创建用户test，设置密码test1
create user test identified by test1;
//把connect和resource授权给用户
grant connect,resource to test;
//切换用户
conn 用户名/密码
//删除用户
drop user test1 cascade;

sql语句的分类

• ​ DQL (Data Query Language)，数据查询语言

  ​ 用于检索数据库中的数据，主要是SELECT语句

• ​ DML (Data Manipulation Language)，数据操纵语言

  ​ 用于改变数据库中的数据，主要是INSERT,UPDATE,DELETE语句

• ​ DDL（Data Define Langage)，数据定义语言

  ​ 用来建立、修改、删除数据库对象，主要是CREATE、ALTER、DROP、TRUNCATE语句

• ​ TCL (Transaction Control Language)，事务控制语言

  ​ 用于维护数据的一致性，主要是COMMIT,ROLLBACK,SAVEPOINT语句

• ​ DCL（Data Control Language），数据控制功能

  ​ 用于执行权限授予和权限收回操作，主要是GRANT,REVOKE语句

DQL

基础数据查询

基本查询语句：

  --按列名查询信息
  select col_name,...
  from table_name;
  --查询所有信息
  select * 
  from table_name; 

​ 举例子：

--查看所有员工的id，名字(last_name)和薪资(salary)
select id,last_name,salary
from s_emp;

运算查询

在查询的时候可以将查询出来的数据进行四则运算

只改变查询结果而不改变名字

​ 基本语法：

select col_name[+/-/*/% 基数]
from table_name;

​ 举例子：

--查询id,last_name 以及所有的年薪
select id,last_name,salary * 12
from s_emp;

别名：

只改变查询出来的名字，而不改变查询结果

""只用在起别名，其他地方都用的’’

​ 基本语法：

--注意，中括号里面的as是可以有，也可以没有的
select old_column [as] new_column_name from tb_name;

​ 举个例子：

select id,last_name as "名字",salary*16 "year_salary"
from s_emp;

拼接

列的拼接

将多列数据拼接成一列数据

数据库中的字符串，需要使用单引号括起来

​ 基本语法：

select col_name||'spe_char'||col_name from tb_name

​ 举个例子：

--将first_name字段和last_name字段拼接起来，中间用_分隔开
select first_name || '_' || last_name "姓名"
from s_emp;

nvl

空值操作

使用nvl函数可以将null进行替换

基本语法：

select nvl(col_name,change_value) from table_name;

​ 举个例子：

-- 查询提成比例，如果为空则显示0.否则是什么就显示什么
select nvl(commission_pct,0)
from s_emp;

distinct 去重

去重,必须跟在select后边，列的前边

基本语法：

select distinct col_name from table_name;

​ 举个例子：

select distinct salary,last_name
from s_emp;

排序

asc升序、desc降序。如果不写默认升序
排序是将查询出来的结果按照某个规则进行调整，并不改变查询的结果
所以它的执行优先级是最低的
多列排序：如果第一列排序相同，则根据第二列进行排序

字符串排序

按照字母顺序去排序

​ 基本语法：

select col_name,...
from table_name
order by col_name [asc|desc],col+name1 [asc|desc];

​ 举个例子：

--查询员工id，name，salary，按照薪水降序排序,如果薪水一样，则按照id降序排序
select id,last_name,salary
from s_emp
order by salary desc,id desc;

-- null排序，null默认无限大
select *
from s_emp
order by commission_pct desc,id;

条件查询

限制查询条件，使用where子句

条件可以多个，使用逻辑操作符或者小括号进行条件的逻辑整合

where子句的优先级别最高

between x and y [x,y] 闭区间

in 在…里面 in(11,12,13,14) x=11 || x=12 || x=13 || x=14 not in()反之

like 模糊查询，在值不精确的时候使用 %表示0个或多个字母 _表示一个字母 \转义 escape:声明转义字符

• %，通配0到多个字符
• _，通配一个字符，并且是一定要有一个字符
• \，转义字符，需要使用escape关键字指定，转义字符只能转义后面的一个字符

is null 对空值进行操作。判断值为null的时候使用，null值的判断不能使用等号。不为空则为 is not null

and、or，逻辑操作符，当条件有多个的时候可以使用。and(优先级)>or(优先级)

​ 举个例子：

--查询员工工资大于1500的员工薪水id,last_name
select id,last_name,salary
from s_emp
where salary > 1500;
--查看员工薪水在1400-1500之间的员工,并且包含1400、1490
select *
from s_emp
where salary between 1400 and 1490;
-- 查询员工编号为11  或者是12  或者是500
select *
from s_emp
where id in(11,12,500);
-- 查询名字像Smith的员工信息
select *
from s_emp
where last_name like 'Smith';
-- 查询名字以S开头的信息
select *
from s_emp
where last_name like 'S%';
-- 查询名字包含下划线的员工信息,escape为转义
select *
from s_emp
where last_name like '%\_%' escape '\';
-- 查询提成为空的员工 
select *
from s_emp
where commission_pct is null;
--查询名字长度不小于5，且工资大于700的员工
select *
from s_emp
where last_name like '_____%' and salary > 700;

单行函数

哑表

Oracle中，有一张特殊的表：dual

dual被称之为哑表，它是一个单行单列的虚拟表，是Oracle内部自动创建的，这个表只有1列

注意，只有oracle数据库中有这个哑表 dua

字符函数

upper(arg)	将字符串转化为大写
lower(arg)	将字符串转换为小写
initcap(str)	首字母大写，其他字母小写
ASCII(X)	如果有多个字母，则返回第一个字母的ASCII
concat(x,y)	将xy连接起来,一次只可以连接两个
instr(x,str)	从x中查询str所在的位置，如果多个，永远取第一个、如果查找的结果不存在，则返回0，如果指定了位置就从指定的位置开始查找，如果指定的-1。就从末尾开始查找
length(str)	返回字符串的长度
LTRIM(x,str)	将x截取str的字符串,从左边截掉。若果字符串在左边不存在，那么不截取
RTRIM(x,str)	从字符串右边开始截取。截取字符串中只要包含前边的字符就可以被截取
trim()	从字符串两边同时截取
replace(str,old,new)	从str中找到old，替换成new
substr(str,x,length)	截取字符串，将str从x位置开始截取，截取length个长度。（x=-1,从末尾截取，x=0/1 从头开始截取）

/*
	upper(arg)
	lower(arg)
*/
select id,upper(last_name),lower(first_name)
from s_emp
where id<5;
/*
     ASCII(X)
*/
select ascii(06)
from dual;
/*
     concat(x,y)
*/
select concat('hello','world')
from dual;
select concat(concat(last_name,'_'),first_name)
from s_emp;
/*
     instr(x,str)  
*/
select instr('hello Fother','o',6)
from dual;
/*
     length(str) 
*/
select last_name
from s_emp
where length(last_name)>5;
/*
      initcap(str)
*/
select initcap(last_name)
from s_emp;
/*
     LTRIM(x,str) 
     RTRIM(x,str) 
     trim()  
 */
select ltrim('hello world','he')
from dual;

select rtrim('hello world','rologkwgd ')
from dual;

select trim('='FROM'=Hello=') as result 
from dual;
/*
     replace(str,old,new)   
*/
select replace('hello hello','lo','中国')
from dual;
/*
   substr(str,x,length) 
*/
select substr('hello world',5,2)
from dual;

数字函数

abs(num)	num的绝对值
power(x,y)	x的y次方
round(43.99548,1)	四舍五入 保留1位小数。小数点后边的0自动省去
trunc(-43.984212,1)	舍弃小数点后，只舍弃不进位。保留1位小数
mod(5,3)	取余 ,任何数对0取余都是本身

select power(2,10)
from dual;

select round(43.99548,1)
from dual;

select trunc(-43.984212,1)
from dual;

select mod(0,5)
from dual;

日期函数

sysdate	获取的是当前操作系统的时间
sysdate + 1	往后推一天
months_between(date1,date2)	两个日期之间相差了多少个月。如果拿date1-date2超过了一个月，但是不满两个月，返回的是一个小数。
add_months(sysdate,x)	返回一个日期，写入小数不起作用,往后推x月的日期
select next_day(sysdate,‘星期一’)	返回一个日期数据：表示一个时间点后的下一个星期几在哪一天
last_day(sysdate)	返回一个日期数据：表示一个日期所在月份的最后一天
round(sysdate)	对日期中的天进行四舍五入，超过十二点进一，返回操作后的日期数据
round(sysdate+2,‘Month’)	如果天数 >=16 月份进一
round(sysdate,‘year’)	如果月份>=7 年份进一
trunc(sysdate,‘month’)	对日期进行截取和round类似，但是只舍弃不进位

select sysdate+1  
from dual;

select months_between(sysdate + 30,sysdate-15)
from dual;

select add_months(sysdate,-2)
from dual;

select next_day(sysdate,'星期五')
from dual;

select last_day(sysdate)
from dual;

select round(sysdate+2,'Month')
from dual;

select round(sysdate,'year')
from dual;

select trunc(sysdate)
from dual;

select trunc(sysdate,'month')
from dual;

转换函数查询

to_char

把一个数字或日期数据转换为字符

转换中只能用特殊符号

hh默认12小时 hh24 采用24小时制

yyyy	四位数的年份
rrrr	四位数的年份
yy	两位数的年份
rr	两位数的年份
mm	两位数的月份（数字)
D	一周的星期几
DD	一月的第几天
DDD	一年的第几天
hh	小时
mi	分钟
ss	秒

--这里虽然写的是AM，但是它是一个变化的值，下午的时候就会自动显示为PM
select to_char(sysdate+12,'yyyy-mm-dd hh24:mi:ss AM')
from dual;

select to_char(sysdate+12,'mm')
from dual;

to_number

把字符转换为数字

select to_number('1000')
from dual;

to_date

把字符转换为日期

select to_date('2019-2-9','yyyy-mm-dd')
from dual;

select to_date('2019-2-2','yyyy-mm-dd')
from dual;

alter session set nls_language=english;
select to_date('25-MAY-22','dd-MONTH-yy') as result
from dual;

alter session set nls_language='simplified chinese';
select to_date('22/5月/25','yy/month/dd') as result
from dual

多表查询

等值连接

利用一张表中某列的值，和另一张表中某列的值相等的关系，把俩张表连接起来，满足条件的数据才会组合

-- 查询每个部门所属区域名称
select r.name,d.name
from s_dept d,s_region r
where d.region_id = r.id;

--查询每个员工所属的部门名称以及该部门所属的区域名称
select e.last_name,d.name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id = d.id
and
d.region_id = r.id;

不等值连接

--先创建一个表，
create table salgrade(
       id number primary key,
       gradeName varchar(255),
       losal number,
       hisal number
);
--添加一些数据
insert into salgrade values(1,'初级程序员',2000,5000);
insert into salgrade values(2,'中级程序员',5100,15000);
insert into salgrade values(3,'高级程序员',16000,40000);
commit;

--查询每个员工的薪资等级名称
select e.last_name,e.salary,g.gradeName
from s_emp e,salgrade g
where
e.salary >= g.losal
and
e.salary <= g.hisal;

左外连接、右外连接、全连接

看以下例子

口诀：我们想把s_emp表中的多出的一个数据tom查询并显示出来，那么就在另一张表上面添加一个加号(+)

--tom不属于任何部门
-- st部门下没有任何员工
insert into s_emp(id,last_name) values(26,'tom');
insert into s_dept(id,name) values(60,'st');
commit;

/*
	这个时候，再使用等值连接的话，查询出来的数据就会少：
		新增的员工tom和部门表中的数据连接不上
		新增的部门st也和员工表中的数据连接不上
		那么，这俩条数据都是在等值连接中查询不出来的。
		这时候就需要用外连接
*/

-- 左外连接
--查询每个员工所属的部门名称
select e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id = d.id(+); -- 这种格式Ocalr特有的

select e.last_name,d.name  -- 这种写法各种数据库通用
from s_emp e left outer join s_dept d     --outer可以省略
on e.dept_id = d.id;

--右外连接
-- 查询员工所属的部门，没有员工的部门也要查
select e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id(+) = d.id

select e.last_name,d.name
from s_emp e right join s_dept d
on e.dept_id = d.id

-- 全连接
--查询每个员工所属的部门，没有部门的员工查询出来，没有员工的部门也要查询出来
select e.last_name,d.name
from s_emp e full join s_dept d
on e.dept_id = d.id;

自连接

自连接就是一张表，自己和自己连接后进行查询

--自连接
select e.last_name,m.last_name
from s_emp e,s_emp m
where e.manager_id = m.id;

操作结果集

union

俩个结果集的并集

select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id = s_dept_id(+)
union
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+) = s_dept_id;

union all

俩个结果集合在一起显示出来

select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id = s_dept_id(+)
union all
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+) = s_dept_id;

minus

第一个结果集除去第二个结果集和它相同的部分

select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id = s_dept_id(+)
minus
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+) = s_dept_id;

intersect

俩个结果集的交集

select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id = s_dept_id(+)
intersect
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+) = s_dept_id;

rownum

Oracle中，有一个特殊的关键字rownum，被称为：伪列。rownum只有Oracle数据中才有

rownum（伪列），就像表中的列一样，但是在表中并不存在。

伪列，可以根据查询结果的条数，自动生成，并且一定是从1开始连续不断的数字

只能等于1，等于其他值的话也查询不出任何东西

伪列大于的话只能大于0或者大于等于1

可以小于任何数

select *
from s_emp
where rownum>=1 and rownum<7;

聚合函数

select语句执行顺序：

1. from子句，组装来自表的数据，有可能是多张表
2. where子句，基于指定的条件对记录行进行筛选
3. group by子句，将数据划分为多个分组
4. 使用聚合函数对没个小组中的数据进行计算
5. having子句，进行条件筛选，这里可以使用聚合函数的计算结果
6. 计算所有的运算表达式，主要是select部分
7. order by子句，对结果集进行排序

​ 常见的组函数（聚合函数）：

avg	平均值
max	最大值
min	最小值
count	求数量
sum	求和

group by、having

在使用聚合函数的时候：

• 如果还使用了group by分组，那么就表示先分组，然后对每一个小组使用聚合函数
• 如果没有使用group by分组，那么就表示全部数据是一个默认小组，然后对这个全部数据使用聚合函数

group by 有多列先对第一列进行分组，然后对第二列进行分组

group by 可以单独存在，having不可以单独存在

聚合函数只有在group by后面执行的才可以出现

聚合函数只能出现在having / order by / select后

在select或者order by 后面出现组函数的列，那么就必须在group by 后面出现

--查询每个部门的最大工资
select max(e.salary),e.dept_id
from s_emp e
group by e.dept_id;
--查询41号部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id
having dept_id=41;
--查询平均工资比1245高的部门
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>1245;
--查询每个部门的平均工资超过八百的员工，按平均工资降序排序
select avg(salary),dept_id
from s_emp
group by dept_id
having avg(salary)>800
order by avg(salary) desc;

子查询

子查询，也称嵌套查询，即一个select语句中嵌套了另外的一个或者多个select语句

子查询的思路就是，把第一个sql语句的查询结果，在第二个sql语句中使用，这时候第一条sql语句的结果，在第二条sql中就可以充当一个where条件中的一个值，或者充当一张虚拟的表。

--查询s_emp表中，部门的平均工资大于等于1400的部门，
--并且显示出这些部门的名字，同时按照部门编号进行排序
select d.name,avg(e.salary),e.dept_id
from s_emp e,s_dept d
where e.dept_id = d.id
group by e.dept_id,d.name
having avg(salary)>1400
order by e.dept_id;
--查询员工信息，这些员工的工资要比自己所在部门的平均工资高，同时显示部门的名称以及所在地区
-- 1.查询每个部门的平均工资和部门编号
select dept_id,avg(salary)
from s_emp
group by dept_id;
--2.查询员工工资比所在部门平均工资高的员工
select e.last_name,e.salary,s.did,d.name,r.name
from s_emp e,(
select dept_id did,avg(salary) avg
from s_emp
group by dept_id) s,s_dept d,s_region r
where 
      e.dept_id = s.did
and
      d.region_id = r.id
and
      d.id = e.dept_id
and
      e.salary > s.avg;

分页查询

--查询第5到第10行
select rr.*
from (
     select rownum rn,s_emp.*
     from s_emp
     where rownum <= 10
) rr
where rr.rn >= 5;

数据库设计

主键

主键的作用，就是用来唯一标识一行数据的

特点：

能做主键的列必要满足非空唯一的特点，只要满足非空唯一的任何列都可以做主键

可以让表中一个有意义的列做主键例如，学号，它既表示学生的学号，又作为表中的主键，因为这个列满足非空唯一的条件

可以找一个没有意义的列做主键，其作用就是标识一行数据，大部分情况下都是用没有意义的列去做主键，例如ID列，

可以让多个列联合在一起做表中的主键这个主键就是一个联合主键，要求这几个列的值联合在一起是非空唯一的

外键

外键的作用，就是用来标识这个类中的数据，是引用另一种表的一个字段值

特点：

表中的某一个列声明为外键列，一般这个外键列都会引用另外一张表的主键列的值。

其实只要是具体唯一约束的列，就可以被另一种表的外键列所引用。

一张表的主键列中出现过的值，都可以在另一张表的外键列中使用。

外键列值也可以为空的，提前是这个外键列没有做主键或联合主键。

如果把B表中的联合主键，引用到A表中做外键，那么这个外键就是一个联合外键

范式

设计关系数据库时，要遵从规范要求，才能设计出更加合理的表结构

• 第一范式(1NF)
• 第二范式(2NF)
• 第三范式(3NF)巴斯-科德范式(BCNF)
• 第四范式(4NF)
• 第五范式(5NF，又称完美范式)

满足最低要求的范式是第一范式(1NF)。在第一范式的基础上，进一步满足更多规范要求的称为第二范式(2NF)，其余范式以次类推。

一般说来，数据库只需满足第三范式(3NF)就行了

第一范式：一个表中，每个列里面的值是不能再分割的

第二范式：第二范式是在满足第一范式的基础上，表中的非主键列都必须依赖于主键列

第三范式：第三范式是在满足第二范式的基础上，表中的非主键列都必须直接依赖于主键列，而不能间接的依赖，也就是不能出现依赖传递

规则

在常见的实体关系中，对应的数据库中表的设计规则如下：

1.一对一关系假设是A表和B表，这种情况下，外键列设置在任意一张表中，都是可以的。

2.一对多关系假设是A表和B表，这种情况下，外键列要设置在多的一方。

3.多对多关系假设是A表和B表，这种情况下，需要设计第三张表（桥表），桥表中设置俩个外键，分别引用A表的主键和B表的主键。

建表

命名

对应表和列的名字：

1. 必须是字母开头

2. 必须是1-30个字符之间的长度

3. 表名中只能出现字母、数字、_ 、#

4.不能和数据库中己有对象的名字重复

5.不能是数据库中的关键字

数据类型

• char 、 varchar、 varchar2（Oracle特有的类型） 都是存储字符串
• number(p,s)p 表示最大位数（整数位+小数位）， s表示保留的小数位（四舍五入），也可以为负数。
• date日期类型
• blob存二进制对象，例如视频，音频，图片等
• clob存储大文本，例如很多很多文字

约束

列的约束，就是对这个列中的值的要求（限制）：

1. 主键约束 PRIMARY KEY primary key
2. 外键约束 FOREIGN KEY foreign key
3. 唯一约束 UNIQUE unique
4. 非空约束 NOT NULL not null
5. check约束 CHECK check

在创建约束的时候（行级、表级都可以），还可以给约束起一个名字，这时候就要使用关键字constraint

注意，给列中的约束起名字，并不是必须的要求，如果不需要，那么也可以不起名字

constraint关键字：

1.constraint是约束的意思

2.建表的时候可以给约束起一个名字，这个名字起的规律一般会是：表名 _ 列名 _ 约束类型

3.如果没有给约束起名字，那么系统也会给这个约束起一个默认的名字

4.将来我们可以根据之前给约束起好的名字，而找到这个约束，然后进行修改获取其他操作

​ 列的约束，又分为：

• ​ 列级约束

• ​ 表级约束

  表级约束和列级约束对比：

  1.表级约束和列级约束所写的位置不一样

  2.notnull约束不能用表级约束来声明

  3.表级约束和列级约束声明语法稍有所不同

  4.如果要声明的约束为联合主键、联合外键、联合唯一的时候，就一定要用表级约束

  举个列子：

--列级约束主键
create table teacher(
       id varchar2(18) constraint teacher_id_pk primary key,
       name varchar2(5) constraint teacher_name_nn not null,
       gender varchar2(10) constraint teacher_gender_c check(gender in('男','女')),
       email varchar2(10) constraint teacher_email_un unique,
       salary number(11,2),
       birthday date constraint teacher_birthday_nn not null
       
);
--表级约束的主键
create table students(
       id number,
       name varchar2(5) constraint teacher_name_nn not null,
       salary number(6,2),
       score number(4,1),
       constraint teacher_id_pk primary key(id),
       unique(salary)
);

create table boy_friend(
       id number constraint boy_friend_id_pk primary key,
       name varchar2(20) constraint boy_friend_nn not null,
       salary number not null,
       brithday date
);
--带有表级约束的外键
create table gril_friend(
       id number primary key,
       name varchar2(20) not null,
       birthday date,
       boy_friend_id number constraint girl_boy_friend_id_fk references boy_friend(id)
);

--------------------------****表的对应关系***----------------------------------
/*
       1...n   1对多
               father...son
*/
create table father(
       id number primary key,
       name varchar2(20) not null,
       salary number
);
--列级约束的外键
create table son(
       id number primary key,
       name varchar2(20) bot null,
       father_id number references father(id)
);
/*
       n...m   多对多
                 teacher...student
*/
create table teacher(
       id number primary key,
       name varchar2(20) not null,
);

create table student(
       id number primary key,
       name varchar2(20) not null,
);
--联合主键
create table teacher_student(
       teacher_id references teacher(id),
       student_id references student(id),
       primary key(teacher_id,student_id)
);

DML语句

insert

​ 基本语法：

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

注意:违反任意一种约束那么就插入数据失败

注意:insert语句中，最后面的那个关键字是values，而不是value

也可以指明向表中的哪些列插入数据

不指定时，默认按照表的顺序插入，所有列都要插入

insert into boy_friend(id,name,salary,brithday) 
       values(1,'彭于晏',50000.0,'21-7月-1888');
insert into boy_friend(id,name,salary) 
       values(2,'彭于晏',50000.0);
insert into gril_friend values(1,'刘聪',sysdate,4);

update

​ 基本语法：

update 表名 set 列1=值1,列2=值2,.... where 条件;

注意，如果不加条件，就表示把表中所有数据更新

update gril_friend set name='刘总';
update gril_friend set name='总刘' where id = 1;

delete

​ 基本语法：

delete from table_name where 条件

注意，如果不加条件，就表示把表中所有数据删除

delete from gril_friend;
delete from boy_friend where id = 4;

关联

测试俩张表有主外键关联的情况下的DML操作

假如有以下两个表：

create table t_customer( 
    id number, 
    name varchar2(20) constraint customer_name_nn not null,
    constraint customer_id_pk primary key(id)
);
create table t_order( 
    id number, 
    price number,
    customer_id number,
    constraint order_id_pk primary key(id), 
    constraint order_cid_fk foreign key(customer_id) references t_customer(id) 
);

insert：

t_customer表中插入数据

insert into t_customer(id,name) values(1,'tom1');
insert into t_customer(id,name) values(2,'tom2');
insert into t_customer(id,name) values(3,'tom3');

t_order表中插入数据

insert into t_order(id,price,customer_id) values(1,1000,1);
insert into t_order(id,price,customer_id) values(2,2000,6);

插入出错，因为6这个值并没有在t_customer表中出现过的

t_order表中插入数据，默认情况下，外键列上的值是可以为空的

insert into t_order(id,price,customer_id) values(3,3000,null);
insert into t_order(id,price) values(4,4000);

注意，如果在外键列上加一个非空约束，那么这个外键列的值就不能为null了

可以使用表级约束，给一个列上添加多种约束，多声明俩次就可以了，但是非空约束只能是列级

如果是实体中是maybe的关系，那么这个外键值是可以为null的，如果是must be的关系，则不能为null

t_order表中插入数据，默认情况下，外键列上的值是可以重复的

insert into t_order(id,price,customer_id) values(5,5000,1);
insert into t_order(id,price,customer_id) values(6,6000,1);

注意，如果在外键列上加一个唯一约束，那么这个外键列的值就不能重复了可以使用表级约束，

给一个列上添加多种约束，多声明俩次就可以了

update:

把t_order表中id=1的数据的customer_id列修改为20

update t_order set customer_id = 20 where id = 1；

注意，sql执行出错，因为就没id=20的顾客

delete:

删除t_order表中的的所有数据

delete from t_order

注意，可以成功删除，因为删除t_order不会对t_customer表的数据产生任何影响

删除t_customer表中id=3和 id=1的数据

delete from t_customer where id = 3;
delete from t_customer where id = 1;

注意，删除成功，因为t_order表中外键列中没有引用过这个值

注意，删除失败，因为t_order表中外键列中已经引用了这个值

delete on

用户在删除A表中的一条数据，而这条数据被B表中的外键列所引用了，这个时候on delete xxx语句的设置可以告诉oracle这个时候该如何处理

1. on delete no action
2. on delete cascade
3. on delete set null

如果在建外键的时候，不加on delete语句，默认就是on delete no action

如果在建外键的时候加上on delete cascade,表示级联删除，在上述删除t_customer表中id=3和 id=1的数据`时候就可以把id=1的删除，同时也级联删除了t_order表中的数据

如果在建外键的时候加上on delete set null，在上述删除t_customer表中id=3和 id=1的数据时候也可以把id=1的删除，同时把t_order表中对应的那条数据的外键设置为null

数据库事务

事务（Transaction），是由一系列对数据库表中数据，进行访问与更新的操作，所组成的一个执行逻辑单元

• 只有DML语句才会产生事务，其他语句不会产生事务
• DML语句执行的时候，如果当前有事务，那么就使用这个事务。如果当前没有事务，则产生一个新事务
• commit、rollback、DDL语句都可以把当前事务给结束掉
• commit和DDL语句结束事务的方式是把这个事务给提交了，然后DML操作永久生效
• rollback结束事务的方式是把这个事务给回滚了，默认回滚到事务开始的状态

特征(ACID)

1. 原子性：Atomicity一个事务中所有的DML操作，同时成功或者同时失败
2. 一致性：Consistency事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态
3. 隔离性：Isolation 事务操作应该相互独立
4. 持久性：Durability事务所做的影响，在事务结束之后应该能够是持久的

回滚

在进行事务回滚的时候，默认是回滚到事务开始时候的状态

使用关键字savepoint，设置回滚点，让事务回滚到指定位置

注意，rollback到回滚点之后，这个事务并没结束，这个时候还可以接着回滚或者commit提交事务。

insert into t_user values(1,'tom',1000);
savepoint A;

insert into t_user(id,name) values(2,'zs');
savepoint B;

rollback to B;

隔离

在数据库中，使用事务隔离级别，来解决事务在并发访问中，所产生的一些问题

默认情况下，事务中经常会产生以下问题：

1. 脏读：主要针对update操作。一个事务A读到另一个事务B中修改过，但是还没有提交的数据
2. 不可重复读：主要针对update操作。一个事务A在第一次读数据和第二次读数据之间，有另一个事务B把这个数据更改并提交了,所以就出现了事务A里面读一个数据俩次，但是读到的结果是不同的。
3. 幻读：主要针对的是insert/delete操作。事务A第一次用where条件筛选出了10条数据，事务A第二次用通样的where条件筛选出的却是11条数据。因为事务B在事务A的第一次和第二次查询之间进行了插入操作，并且插入的这个数据满足事务A的where筛选条件

在这种情况下，数据库中的事务隔离级别，就是来解决这些问题的：

1. read-uncommitted 不提交也能读
2. read-committed 提交之后才能读，解决了脏读
3. repeatable-read 解决了脏读和不可重复读
4. serializable 三个问题都解决了

注意，级别越高解决的问题越多但是效率越低注意，并不是所有数据库都支持这四种事务隔离级别例如，oracle就只支持第二种和第四种这俩种。而mysql四种全都支持注意，具体的支持情况，不仅和数据库有关，也和数据库的版本有关。

注意，oracle里面默认的事务隔离级别是第二种：read-committed

Set Transaction Isolation Level Read Uncommitted
Set Transaction Isolation Level Read Committed
Set Transaction Isolation Level Read Repeatable
Set Transaction Isolation Level Serializabl

DDL

alter

在表创建好的情况下，可以使用alter关键字，来修改表的信息。

主要包括：

• 在表中添加新列

• 删除表中的列

• 给表中添加约束

• 删除表中的约束

• 修改表名

• 修改列的数据类型

• 设置约束失效

  在表中添加一个新的列

alter table 表名 birthday 列名;

​ 删除表的某列

alter table 表名 column 列名;

​ 给表中的列添加约束

​ 这个约束相当于之前的表级约束

alter table 表名 constraint user_name_un unique(列名)

​ 删除表中的约束

alter table 表名 constraint user_name_un;

​ 修改表的名字

rename 表名1 to 表名2;

​ 修改表中某列的数据类型

alter table 表名 modify (列名 数据类型);

​ 截断表中的数据(删除)

​ truncate操作，不需要提交，默认已经提交，并且不能回滚，因为truncate属于DDL操作

truncate table t_user;
-- 相当于:
delete from t_user;
commit;

序列

它是一种数据库对象

它作用主要用来帮助表去创建自动增长的主键

• INCREMENT BY，用于定义序列的步长，如果省略，则默认为1
• START WITH，定义序列的初始值(即产生的第一个值)，默认为1
• 3MAXVALUE定义序列能产生的最大值。NOMAXVALUE是默认值，代表没有最大值定义
• MINVALUE定义序列能产生的最小值。NOMAXVALUE是默认值，代表没有最小值定义
• CYCLE和NOCYCLE表示当序列的值达到限制值后是否循环
• CACHE定义存放序列值的缓冲区的大小，默认为20。NOCACHE表示不对序列进行内存缓冲

create sequence 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}

​ 序列的使用

--创建序列
create sequence test_seq
increment by5
start with 10
maxvalue 100
minvalue 10
cycle
nocache;
--使用序列
insert into t_user(id,name,salary) values(t_user_seq.nextval,'tom',2000);

视图

它也是一种数据库对象

视图其实就是提取一张表或者多张表的数据生成一个映射

视图分为俩种：

• 简单视图：没有group by语句，没有组函数，查询的只有一张表的视图
• 复杂视图：如果有group by语句，或者有组函数，或者查询的是多张表

简单视图和复杂视图的区别：

​ 通过简单视图可以修改原来表中的数据，通过复杂视图是不能修改原来的数据的，如果进行删除操作，删除的都是有外键的一方的数据，如果创建视图是多表且提取的数据没有任何关联，那么不能删除。

​ 创建视图：

--给用户创建视图的权限
grant create view to briup;
create or replace view 
视图名字
as
sql语句

with read only 创建只读视图

create or replace view dept_view_read_only
as
select * 
from s_emp
with read only;

with check option语句创建视图

表示通过视图进行的修改，那么也必须可以通过这个视图能够显示出来，否则就操作失败

create or replace view emp_view 
as 
select id,last_name
from s_emp
where id = 2;
with check option;

insert into emp_view values(312,'asdasd');

注意，这里会报错，因为这个操作通过视图显示不出来。视图只能显示id=2的信息，所有插入id=312的会报错，显示不出来

索引

1. 如果没有索引，查找这个记录时，需要搜索表中所有的记录，因为不能保证只有一个tom，那么就必须将表中数据全部搜索一遍
2. 如果在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值在什么位置按照一定的规则进行排列，然后构建索引条目，并存储起来，在查询name为tom时，可以直接查找该数据所在的对应地方。

注意,创建了索引并不一定就会使用，因为oracle在自动统计表的信息后，会决定是否使用索引，表中数据很少时，使用全表扫描速度已经很快了，那么就没有必要使用索引了。

​ 创建索引有俩种情况：

1. 自动创建索引,当在表中指定了primary Key或者unique约束时，会自动创建唯一值索引。
2. 用户创建索引,用户可以创建非唯一值索引以提高在访问数据时的效率。

​ 给某列创建索引的原则：

1. 列经常作为where子句的限定条件或者作为连接条件
2. 列包含的数据量很大，并且很多非空的值。
3. 在经常需要order by,group by,distinct 列上创建索引，可以利用索引加快排序查询时间；
4. 索引不是越多越好，不是索引越多越能加速查找。
5. 要建立索引的表不经常进行修改操作

create index 索引名 
on 表名(列名);

--例子
create index emp_last_name_index
on s_emp(last_name);
drop index emp_last_name_index;

B-tree索引：

默认的索引就是这种结构。

1. 适合大量的增、删、改
2. 不能用包含OR操作符的查询
3. 适合唯一值较多的列
4. 典型的树状结构