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<=3000 ;
select empno,ename,sal from emp where sal<2000  or sal>3000 ;
--分组查询
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;

 

 

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

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

 

 

--内连接
select e.empno as 员工编号, e.ename as 员工名称, d.dname as 部门 
from emp e inner join dept d on e.deptno=d.deptno;         
 
--左外连接
insert into emp(empno,ename,job) values(9527,'EAST','SALESMAN');
        
select e.empno,e.ename,e.job,d.deptno,d.dname from emp e left join dept d
on e.deptno=d.deptno;
--右外连接
select e.empno,e.ename,e.job,d.deptno,d.dname from emp e right join dept d
on e.deptno=d.deptno;

--完全连接
select e.empno,e.ename,e.job,d.deptno,d.dname from emp e full join dept d
on e.deptno=d.deptno;

--自然连接(共有的属性，会去除重复列)
select empno,ename,job,dname from emp natural join dept where sal>2000;

事务如果不提交,会一直写入以下表空间;
redo(记录日志表空间)  undo(记录日志备份表空间)
提交:  commit    回滚: rollback

--右外连接过滤
select * from emp e right join dept d on e.deptno=d.deptno
where e.deptno is null;
--左外连接过滤
select * from emp e left join dept d on e.deptno=d.deptno
where d.deptno is null;
--全外连接过滤
select * from emp e full join dept d on e.deptno=d.deptno 
where d.deptno is null or e.deptno is null;


/*自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。
在emp中的每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工，自身也有自己的经理。*/
select em2.enname 管理者,em1.enname  下属员工 from  emp em1 left  join  emp em2 on  em1.mgr=em2.empno order by em1.mgr;

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

 ROWNUM伪列

select rownum,empno,ename  from emp where deptno=20;
--返回emp表中前5个员工消息
select rownum seq,empno,ename,sal from emp where rownum<=5;

--查询emp表中2条到第5条的数据
select rownum seq,empno,ename,sal from emp where rownum>=2 and rownum<=5;

/*ROWNUM列值会被重置所致,比如在取第1行,where子条件不成立,第1行被丢弃,
但是取下一行时,ROWNUM会被重置为1,而不是2, 导致,ROWNUM永远无法取到正确的值
无法返回 任何行数据*/
select * from emp;
select seq,empno,ename,sal from (select rownum seq,empno,ename,sal from emp)
where seq>=2 and seq<=5

 

外键约束的作用 

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

 

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

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

有外键约束的表CRUD操作

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

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

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