内外连接、组函数、DDL、DML和TCL

前言 cross join ，是笛卡尔积；nature join 是自然连接。 正文 内外连接 inner join inner join 的inner可以省略。 内连接 在一个表中能够找到在另一个表中与之对应的记录，来组成新的记录显示出来，舍弃表中在另一个表中没有对应记录的记录。 等值连接 一

前言

cross join ，是笛卡尔积；nature join 是自然连接。

正文

内外连接 inner join

inner join 的inner可以省略。

内连接

在一个表中能够找到在另一个表中与之对应的记录，来组成新的记录显示出来，舍弃表中在另一个表中没有对应记录的记录。

等值连接

一个表中的某个字段是另一个表的外键

例如。emp表和dept表存在多对一的关联关系，由外键字段deptno来维护，即emp表来主动维护这一关系。

非等值连接

between and 来实现非等值连接；

select e.ename，e.sal，s.grade from emp e join salgrades on e.sal between s.losal and s.hisal；

自连接

表中的字段之间寻在引用关系，通过得同一个表取不同别名来实现自身连接。

select e.empno，m.name from emp e join emp m on m.empno=e.mgr；

外连接

外连接【陪陪时会将驱动表中所有记录显示

左外连接 left join

左外连接会将主表对应的所有信息显示，从表与之匹配的记录显示。

select * from emp join dept on emp.deptno=dept.deptno(+); --加号在join 右边的表的属性上位左连接

select * from emp left join dept on emp.deptno=dept.deptno;

右外连接 right join

加号在join 左边的表的属性上位右连接

全连接 full join

将两表中所有匹配和不匹配记录显示出来。

组函数

多行函数，输入一组记录，输出一行记录。max、min、avg、sum和count函数。

分组查询 group by

1.如果在select之后有某个字段名称，那么此字段必须作为分组的条件之一。

select deptno,max(sal) from emp group by deptno;--在分组查询中，select 之后除了组函数以外，查询的字段名称和个数最好和group by 之后的字段名称和个数一致。

2.过滤分组查询可以使用having关键字，having用于过滤分组之后的结果

select deptno，avg（sal） from emp group by deptno having avg(sal)>2000;--having不能单独使用，它必须出现在group by 之后。

3.where 不可接分组函数

4.分组查询的执行顺序

先执行from，在执行where，或在分组，最好才执行select。

子查询

查询中含有查询

非关联子查询

单列子查询

查询结果是一行一列

单行子查询可以使用比较运算符（<,>,=,!=,<>)

select enamel ,sal rom emp where sal>(select sal from emp where ename='SOTT');

单行子查询

子查询的结果是一行多列

select enamel，sal。deptno from emp where （deptno，sal） in（select deptno，max(sal) from emp group by deptno having deptno=10）；

多行子查询

子查询的结果是多行一列

select * from emp where deptno in（select deptno from emp where job='CLERK'）；

注：

=any 跟in的效果一样，匹配子查询的所有值

>any 大于最小的

多列子查询

子查询的结果是多行多列

select enamel，sal。deptno from emp where （deptno，sal） in（select deptno，max(sal) from emp group by deptno ）；

关联子查询（循环主查询）

子查询和主查询进行关联查询

select * from emp out where sal>(select avg(sal) from emp where out.deptno=deptno group by deptno);--先将主表out的第一条记录的deptno传到子查询，并将查询结果返回到主查询，主查询将符合条件的记录放入到结果集中，依次循环该过程，直到循环到主表的最后一条记录。

例子

select * from emp out where empno in(select mgr from emp);;--查询出所有的领导

select * from emp out where exists(select 'x' from emp where mgr=out.empno);--exists，假如存在，子查返回的是true,并不关注子查询返回的具体的结果。

集合

集合查询中的字段个数和类型要一致，这称为select的同构。

select job from emp deptno=10 union all select job from emp deptno=20;

注：

union all 并集，并且不去除重复

union 并集，去除重复

intersect 交集

minus 差集

DDL create、drop、truncate和alter

oracle数据库的基本数据类型

number 数字类型

varchar2字符串类型；可变长

char 字符串类型，固定长度，占用空间不变，执行效率高

date日期类型

clob 字符型大数据类型

blob 字节型大数据类型

创建表

create table 表名（字段名 数据类型 约束，字段名 数据类型 约束）；

复制表

复制表只能复制表结构和数据，复制不了约束。

create table 表名1 as select * from emp where empno=10000;---如果where条件不成立，查不到任何东西时，该语句只复制了表结构；

注：user_constraints 存储当前用户的相关信息的数据字典

删除表

drop table 表名

修改表

重命名

rename 旧表名 to 新表名

修改表的字段名

alter table 表名 rename column 旧字段名 to 新字段名；

修改字段类型长度

修改字段类型的前提是当前的字段值是空的，即修改的列为空。

alter table 表名 modify(字段名称 字段类型（长度）)；

添加字段

alter table 表名 add(字段名称 字段类型（长度）);

删除字段

alter table 表名 drop （字段名称）；

alter table 表名 drop column（字段名称）；

截断表 truncate

删除表中的所有记录，效率比delete更高

DML insert、delete、update

添加记录

字段的值与表中字段的类型和长度要匹配

insert into 表名（字段名称1，字段名称2） value（字段1的值，字段2的值）；

1.一次插入多条记录

insert into emp(empno,ename) select empno,ename from emp;

修改记录

update 表名 set 被修改的字段名1=新值，被被修改的字段名2=新值

删除记录

delete 【from 】 表名；

TCL commit、rollback和savapoint

事务ACID特性

数据库运行的最小单元，是一系列的数据操作，是数据库应用程序的基本逻辑单元。事务是恢复和并发控制的基本单元。

1.在sql中，定义事务的预计有3条

begin transaction

commit

rollback

通常事务以begin transaction 开始，以commit或rollback结束。

注：

1.rollback 回滚事务，事务恢复到上一个事务结束之前的状态。

2.dml执行会开启一个事务。

3.commit 提交事务，当前操作会执行应用到数据库

4.commit和rollback会显式的提交事务

5.savepoint 事务点名；设置事务点

rollback to 事务点名；--在事务点之后的其他dml操作都会被取消

6.ddl语句隐式的提交事务

原子性 atomicity

事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性 consistency

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。

隔离性 isolation

一个事务的执行不能被其他事务干挠。

持续性 durability

也称永久性，值一个事务一旦提交，它对数据库中数据的改变就应该使永久性的。

注：事务不处理

1.表和行级别的锁不会释放

2.当前会话的操作在其他会话中查不到

3.当前事务可以回滚

4.当前事务回滚段的资源不会释放

总结