SQL多表关联查询

--解锁SCOTT用户

alter user scott account unlock

--检索指定的列

select job,ename,empno from emp;

--带有表达是的select子句

select sal*(1+0.2),sal from emp;

--显示不重复的记录

select distinct job from emp;

--比较筛选 =

select empno,ename,sal from emp where sal>1000;

select empno,ename,JOB from emp;

select empno,ename,sal from emp where sal all(3000,950,800);

--特殊关键字筛选

--like 模糊查询

select empno,ename,job from emp where JOB like '%S';

--IN --varchar

select empno,ename,job from emp where job in('PRESIDENT','MANAGER','ANALYST');

--NOT IN

select empno,ename,job from emp where job not in('PRESIDENT','MANAGER','ANALYST') ;

--BETWEEN -numer ,inter

select empno,ename,sal from emp where sal between 2000 and 3000;

--NOT BETWEEN

select empno,ename,sal from emp where sal NOT between 2000 and 3000;

--IS NULL/ is not null

select * from emp where comm is NOT null;

--逻辑筛选

--and ,or,not 关系 于 -或 --非

select empno,ename,sal from emp where （sal>=2000 and sal

select empno,ename,sal from emp where sal3000 ;

--分组查询

select deptno,job from emp group by deptno,job order by deptno ;

select deptno as 部门编号，avg(sal) as 平均工资 from emp group by deptno;

select deptno as 部门编号，avg(sal) as 平均工资 from emp group by deptno having avg(sal)>2000; --group by 子条件 having

--排序查询Order by; desc:逆序 asc默认

select deptno,empno,ename from emp order by deptno,EMPNO;

多表关联查询

在创建关系型数据表时，根据数据库范式的要求，为了降低数据的冗余，提供数据维护的灵活性

将数据分成多个表进行存储，实际工作当中，需要多个表的信息，需要将多个表合并显示

--内连接

selecte.empnoas员工编号,e.enameas员工名称,d.dnameas部门

fromemp einner joindept done.deptno=d.deptno;

--左外连接

insert intoemp(empno,ename,job)values(9527,'EAST','SALESMAN');

selecte.empno,e.ename,e.job,d.deptno,d.dnamefromemp eleft joindept d

one.deptno=d.deptno;

--右外连接

selecte.empno,e.ename,e.job,d.deptno,d.dnamefromemp eright joindept d

one.deptno=d.deptno;

--完全连接

selecte.empno,e.ename,e.job,d.deptno,d.dnamefromemp efull joindept d

one.deptno=d.deptno;

--自然连接(共有的属性，会去除重复列)

selectempno,ename,job,dnamefromempnatural joindeptwheresal>2000;

事务如果不提交,会一直写入以下表空间;

redo(记录日志表空间) undo(记录日志备份表空间)

提交: commit 回滚: rollback

--右外连接过滤

select*fromemp eright joindept done.deptno=d.deptno

wheree.deptnois null;

--左外连接过滤

select*fromemp eleft joindept done.deptno=d.deptno

whered.deptnois null;

--全外连接过滤

select*fromemp efull joindept done.deptno=d.deptno

whered.deptnois null ore.deptnois null;

/*自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

在emp中的每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工，自身也有自己的经理。*/

selectem2.enname 管理者,em1.enname 下属员工fromemp em1leftjoinemp em2onem1.mgr=em2.empnoorderbyem1.mgr;

/*交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数*/

selectcount(*)fromdeptcrossjoinemp;

ROWNUM伪列

返回查询结果集的行号

selectrownum,empno,enamefromempwheredeptno=20;

--返回emp表中前5个员工消息

select rownumseq,empno,ename,salfromempwhere rownum

--查询emp表中2条到第5条的数据

select rownumseq,empno,ename,salfromempwhere rownum>=2and rownum

/*ROWNUM列值会被重置所致,比如在取第1行,where子条件不成立,第1行被丢弃,

但是取下一行时,ROWNUM会被重置为1,而不是2, 导致,ROWNUM永远无法取到正确的值

无法返回 任何行数据*/

select*fromemp;

selectseq,empno,ename,salfrom(select rownumseq,empno,ename,salfromemp)

whereseq>=2andseq

ROWID伪列

返回行的地址,索引通过rowid来定位目标行的位置

通过rowid来得到详细的物理地址信息

RANK() 发生不持续的编号 例如数据值 1,2,2,3 发生的编号将是1,2,3,4

DENSE_RANK() 发生持续的编号 例如数据值 1,2,2,3 发生的编号将是1,2,2,3

ROW_NUMBER() 发生持续的编号(不重复) 例如数据值 1,2,2,3 发生的编号将是1,2,3,4

外键约束的作用

外键是该表是另一个表之间联接的字段 外键必须为另一个表中的主键 外键的用途是确保数据的完整性。它通常包括以下几种： 实体完整性，确保每个实体是唯一的（通过主键来实施）

1 域完整性，确保属性值只从一套特定可选的集合里选择

2 关联完整性，确保每个外键或是NULL（如果允许的话）或含有与相关主键值相配的值

有外键约束的表CRUD操作

1 增加数据：先插入外表，再插入主表

2 删除数据：先删除主表再删除外表，

3 有对应记录的值，不能修改当前列；