oracle3,多表联合查询，sql92方式，sql99方式，三表联合查询，用户管理，二维表管理，练习

本文链接：https://blog.youkuaiyun.com/qq_45378285/article/details/107386519

oracle3

SQL92方式
SQL99方式
92方式和99方式实现三表查询
子查询
用户管理
二维表管理（简单管理）
练习

SQL92方式

--练习1知识点
  --查找字段的长度 length(字段名)  返回的是字段的长度
  --返回当前的日期时间 关键字：sysdate
  --排序中可以使用别名
----------------------------------------------------------------------
--多表联合查询(需要获取的数据分布在多张表中，使用多表联合)
  --方式：SQL92方式
  --方式：SQL99方式
  --SQL92方式：
              --笛卡尔积：将多张表的数据进行一一对应，所得到的结果是多表的笛卡尔积，结果的数量为所有表数量的乘积
              --（例如A表中有5条数据，B表中有10数据，那末两张表多表联合查询后就是一张表，这张表有50条数据。）
              select * from emp,dept;
              --等值连接（先做多表的笛卡尔积，然后进行筛选，筛选条件是等值筛选）
              select * from emp e,dept d where e.deptno = d.deptno;(要注意：在这查询时，若某个表的deptno的值为null，那末就会省去，所以这时要使用外连接)
                     查询员工姓名，工作，薪资，部门名称
                     select ename ,job,sal dname from emp,dept where emp.deptno=dept.deptno; 
                     --注意可以直接在select的子句中直接使用字段获取数据，但效率较低，建议字段上加上表名，
                     --如果是公共字段，则必须加表名，（也可以使用表名）
                     select emp.ename,emp.job,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno; --加表名
                     select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno; --加表名的和使用别名的
              --不等值连接
              --条件为值相同即可，字段名可以不相同
                     查询员工姓名，工作，工资，工资等级
                     select * from salgrade;
                     select e.ename,e.job,e.sal,s.grade from emp e,salgrade s where e.sal>s.losal and e.sal < s.hisal;
              --自身连接（两张一样的表连接，在emp表中，mgr字段表示上级领导）
                     查询员工姓名，工作，工资，上级领导名字
                     select e1.* ,e2.* from emp e1,emp e2 where e1.mgr = e2.empno;
              --外连接
              --这里主要看+号，加在哪里，那个没有就加在哪里
                     --左外连接
                            查询员工姓名，工作，薪资，部门名称及没有部门的员工信息
                            select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno(+);
                     --右外连接
                            查询员工姓名，工作，薪资，部门名称及由部门没有员工的
                            select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno(+) = d.deptno;

SQL99方式

--SQL99方式（多表联合查询）
  --注意：
   --想要看笛卡尔积，必须单独使用关键字进行查看
  --笛卡尔积
    关键字：cross join
    --select * from 表名1 corss join 表名2；
    select * from emp cross join dept;
  --筛选：
    --自然连接
           --关键字：natural join
           --select * from  表名1 natural join 表名2；
                    --注意：
                      --底层使用笛卡尔积，然后按照所有的的同名同值进行自动筛选
                    --问题：
                      --1：若想按照部分字段进行多表查询怎末办？
                            --解决：
                               --使用指定的字段名对联合查询的结构进行等值筛选，
                               --指定的字段必须是同值同名；
                               --关键字： inner join ,using;
                               --select * from 表名1  inner join 表名2 using(字段名)
                               select  * from emp inner join dept using(deptno);
                     --2:若先按照值进行筛选，字段名不同怎末办？
                           --解决：
                               --使用on关键字进行自定义连接筛选（等值筛选，不等值筛选）
                               --普通条件的筛选使用where进行筛选，不要使用on进行，SQL语句的阅读性强。
                               -- 关键字 inner join,on;
                               --select 内容 from 表名1 inner join 表名2 on 连接条件 where 普通条件；
                               select * from emp inner on dept on emp.deptno = dept.deptno;
    --外连接
      --左外连接 ：
         --关键字：left join,left outer join,其中outer可以省略
         --格式 select 内容 from 表名 left outer join 表名1 on 连接条件
         select * from emp left join dept on emp.deptno = dept.deptno;
      --右外连接：
         --关键字：right join;right outer join,其中outer可以省略
         --格式：select 内容 from 表名 right outer join 表名1 on 连接条件
         select * from emp right outer join dept on e`mp.deptno = dept.deptno;
      --全连接：
         --关键字：full outer join;full join ,其中的outer可以省略
         --格式：select 内容 from 表名 full outer join 表名1 on 连接条件
         select * from emp full outer join dept on emp.deptno = dept.deptno;
    --自连接：（在一个表中必须有字段名不同，但是值是相同的才可以进行自连接）
         --select * from 表名 inner join 表名 on 连接条件
         select * from emp e1 inner join emp e2 on e1.mgr = e2.empno;

92方式和99方式实现三表查询

--三表联合查询
--建立city表，使用界面操作建立
select * from city;
--插入数据
insert into city values(1,'天宫'，'美好');
insert into city values(2,'天'，'美好');
insert into city values(3,'宫'，'美好');
insert into city values(4,'地'，'美好');
--查看dept表(对于loc字段进行修改，使dept表的loc字段与city表的cid字段的值相同)
select * from dept;
--修改dept表的loc字段
       --方法一
         select * from dept for update; --这样可以在界面直接进行更改
       --方法二
         update dept set loc = '1' where deptno = 10; 
         update dept set loc = '2' where deptno = 20; 
         update dept set loc = '3' where deptno = 30; 
         update dept set loc = '4' where deptno = 40; 
--------------------------------------------------------------------------------------
--使用SQL92方式实现三表查询
         --易于读写，难于阅读
         --SQL语句的结构不清晰（连接条件，普通筛选条件混到一起）
         --格式：
         --select 内容(别名，连接符，racle函数，逻辑运算，去重)
         --from 表名，表名1...
         --where 条件（连接条件，普通筛选条件，where子查询条件）
         --group by 分组字段
         --having 多行函数进行筛选
         --order by 排序字段
         查询员工的信息，部门名称，城市名称并且员工的工资大于2000的或者有奖金的
         select e.* ,d.dname,c.cname from emp e,dept d,city c  where (e.deptno = d.deptno and d.loc = c.cid and e.sal > 2000) or (e.deptno = d.deptno and d.loc = c.cid and comm is not null); 
--使用SQL99方式实现三表查询
         --SQL难于书写，易于阅读
         --格式：
         --select 内容 (别名，连接符，racle函数，逻辑运算，去重) from 表名1
         --inner join 表名2
         --on 连接条件
         --inner join 表名3
         --on 连接条件
         --....(可以在接着连接别的表，就是这种写法)
         --where 普通筛选条件
         --group by 分组字段
         --having 多行函数进行筛选
         --order by 排序字段
         查询员工的信息，部门名称，城市名称并且员工的工资大于2000的或者有奖金的
         select e.* ,d.dname,c.cname from emp e
         inner join dept d
         on e.deptno = d.deptno
         inner join city c 
         on d.loc = c.cid
         where e.sal > 2000 or e.comm is not null;

子查询

--多表查询的使用
  --当需要查询的数据在多张表中的时候，建议使用多表联合查询。
--单表查询的使用
  --当需要查询的数据在一张表中的时候，建议使用单表联合查询
-----------------------------------------------------------------
--子查询
   --包括单行查询和多行查询
   --单行查询
      --使用:筛选条件不明确时，需要进行一次查询，并且查询结果中只有一个数据（一个字段名，值唯一）
      --where 子句中出现查询语句，该查询语句成为子查询。
      --格式：
        --select 内容 from 表名 字段名 比较运算符 子查询语句
        查询所有比雇员"CLARK"工资高的员工信息
        select * from emp where sal > (select sal from emp where ename = 'CLARK');
        查询工资高于平均工资的员工的名字和工资
        select ename ,sal from emp where sal > (select avg(sal) from emp );
        查询和SCOTT属于同一部门且工资比它低的员工资料
        select * from emp where deptno = (select deptno from emp where ename = 'SCOTT') and sal < (select sal from emp where ename='SCOTT');
        查询工资最高的员工的资料
        select * from emp where sal = (select max(sal) from emp);
        查询职务和SCOTT相同，雇佣时间早的员工信息
        select * from emp where job =(select job from emp where ename='SCOTT') and hiredate < (select hiredate from emp where ename='SCOTT');
        查询职务和SCOTT相同，或者雇佣时间早的员工信息
        select * from emp where job =(select job from emp where ename='SCOTT') or hiredate < (select hiredate from emp where ename='SCOTT');
   --多行查询
        --使用：子查询的结果只有一个字段，字段的值有好几个，考虑使用多行子查询。
        --使用关键字
        --any(任意) 
             --格式：select 内容 from 表名 字段名 比较运算符 any 子查询语句
        --all（所有）
             --格式：select 内容 from 表名 字段名 比较运算符 all 子查询语句
        --in ，not in;(in()相当于比较运算符 any())
             --格式：select 内容 from 表名 字段名 in(同一字段的值...) 
             --格式：select 内容 from 表名 字段名 not in(同一字段的值...) 
             --格式：select 内容 from 表名 字段名 比较运算符 any(同一字段的值...)   
         查询工资高于任意一个工作为CLERK的所有的员工信息
         select * from emp where sal > any(select sal from emp where job='CLERK');
         查询工资高于所有SALESMAN的员工信息
         select * from emp where sal > all(select sal from emp where job='SALESMAN');
         查询部门在20，部门也在10中雇员工作一致的员工信息
         select * from emp where job in (select job from emp where deptno = 10) and deptno = 20;
         select * from emp where job = any(select job from emp where deptno = 10 ) and deptno = 20;
         select * from emp where job not in (select job from emp where deptno = 10 ) and deptno = 20;

用户管理

--oracle的学习
  --oracle管理系统的介绍(客户端和服务器之间的应用)
  --oracle的增删改查（crud）
  --oracle的用户管理
  --oracle的二维表管理
    --创建二维表
    --维护二维表
    --删除二维表
-----------------------------------------------------------------------------
--oracle的用户管理
    --权限：具有处理某些事情的能力称为权限
    --角色：多个权限的集合成为角色。
    --oracle的自带账户
      --system 管理账户 1234
        --作用：具有oracle的大部分的操作权限，用来管理普通用户和用户数据。
        --使用人：oracle使用者（数据维护者）
      --sys 超级管理员 1234
        --作用：具有system的所有的操作权限，并且还具有其他的权限
        --使用人：oracle工程师
    --创建角账户：
        --使用system账户，并使用dba身份，登录oracle管理系统
        --create user 用户名 indetified by 密码
        create user scott indetified by 1234;
    --赋予权限(connect,resource,dba)
        --格式：grant 权限或角色 to 用户名
        grant connet to scott; --赋予用户登录权限
        grant resouce to scott; --赋予用户操作资源的权限
        grant dba to scott;  -- 赋予用户dba角色。
        select * from scott.emp ；--查看其他用户的表， 用户名.表名，该用户的权限应该时dba;
    --删除权限
        --格式：revoke 权限或角色 from 用户名
        revoke connect from scott;
        revoke resource from scott;
        revoke dba from scott;
    --删除用户
        --格式：drop user 用户名
        drop user scott;

二维表管理（简单管理）

--oracle二维表的管理（简单管理不包含约束的学习）
-- 创建二维表（1，可以通过界面操作创建，2.可以通过SQL语句进行创建）
--创建二维表
   --格式：create table 表名（字段名 类型，字段名 类型....）
   create table student(
          sno number(10,2),
          sname varchar2(100),
          sage number(3),
          ssex char(4),
          sfav varchar2(500),
          sbir date
   )
   select * from student;
--插入数据
   insert into student values (1,'小强','22','男','打篮球','01-1月-1997');
   insert into student values (2,'小金','32','女','游泳',to_date('1999-10-10','yyyy-mm-dd'));
--删除表
   drop table 表名;
   drop table student;
--数据类型
   --数值类型：number()
        --number (a,b) 总长度为a,小数点总长度的b， 小数点的位数可以不写。
        --number(a) 总长度为a,是整数位，没有小数点
   --varchar类型
        --varchar2(ln)：ln(length)表示字符的最大的长度（字节），实际的存储长度根据字符的大小决定，
        --动态分配内存，节省空间
        --char类型
        --char(ln):不管字符数据的长度为多大，直接开辟出ln字节的存储空间。
        --存储的效率高于varchar.
   --date类型。

练习

select * from emp;
select * from dept;
select * from dept for update;
select * from salgrade;
update dept set loc ='BOSTON' where deptno = 40;
--1.列出所有雇员的姓名及其直接上级的姓名
select e1.ename , e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
select e1.ename,e2.ename from emp e1 
inner join emp e2 on e1.mgr = e2.empno;
--2.列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门;
select d.dname,e.ename from emp e,dept d where e.deptno(+) = d.deptno;
select d.dname,e.ename from emp e 
right join dept d on e.deptno = d.deptno;
--3.显示所有部门在"NEW YORK"(dept表 loc字段)的员工姓名
select e.ename from emp e,dept d where e.deptno =d.deptno and d.loc='NEW YORK';
select e.ename from emp e 
inner join dept d on e.deptno=d.deptno 
where d.loc='NEW YORK';
--4.显示员工"SMITH"的姓名，部门名称
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.ename='SMITH';
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno where e.ename='SMITH';
--5.显示员工姓名，部门名称，工资，工资级别(salgrade表 grade字段),要求工资级别大于4级
select e.ename,d.dname,e.sal,s.grade from emp e,dept d,salgrade s where e.deptno = d.deptno and e.sal < s.hisal and e.sal > s.losal and s.grade > 4 ;
select e.ename,d.dname,e.sal,s.grade from emp e 
inner join dept d on e.deptno = d.deptno 
inner join salgrade s on e.sal < s.hisal and e.sal > s.losal
where s.grade >4;
--6.显示员工"KING"和"FORD"管理的员工姓名及其经理姓名(里面有的没有上级，采用外连接)
select e1.ename,e2.ename,e3.ename from emp e1,emp e2,emp e3 where (e1.mgr = e3.empno and e2.mgr = e1.empno(+) and e1.ename='KING') or (e1.mgr = e3.empno and e2.mgr = e1.empno(+) and e1.ename='FORD');
select e1.ename,e2.ename,e3.ename from emp e1
inner join emp e2 on e2.mgr = e1.empno
full join emp e3 on e1.mgr = e3.empno
where e1.ename = 'KING' or e1.ename ='FORD';
--7.显示员工名，参加工作时间，经理名，参加工作时间：参加工作时间比他的经理早
select e1.ename ,e1.hiredate,e2.ename,e2.hiredate from emp e1,emp e2 where e1.mgr = e2.empno and e1.hiredate < e2.hiredate; 
select e1.ename,e1.hiredate,e2.ename,e2.hiredate from emp e1 
inner join emp e2 on e1.mgr = e2.empno
where e1.hiredate < e2.hiredate; 
--8、求出部门名称中，带'S'字符的部门员工的工资总和 、部门人数
select  sum(e.sal) , count(*) from emp e,dept d where e.deptno = d.deptno and d.dname like '%S%'  group by e.deptno ;
select sum(e.sal) ,count(*) from emp e 
inner join dept d on e.deptno = d.deptno 
where d.dname like '%S%' group by e.deptno;
--9、列出所有员工的年工资，按年薪从低到高排序
select * from emp order by ((sal+nvl(comm,0))*12);
--10、列出各种工作的最低工资以及从事此工作的雇员姓名
select min(sal) ,job from emp group by job;
select * from emp e,(select min(sal) msal,job from emp group by job) s where e.job = s.job and e.sal = s.msal;
select * from emp e
inner join (select min(sal) msal,job from emp group by job) s  on e.job = s.job
where e.sal =s.msal;
--11、列出所有部门的详细信息和部门人数
--select d.deptno,count(e.ename) from emp e,dept d where e.deptno=d.deptno group by d.deptno;(查询部门的人数)
select * from dept d,(select d.deptno,count(e.ename) from emp e,dept d where e.deptno=d.deptno group by d.deptno) s where d.deptno = s.deptno;
select * from dept d
inner join (select d.deptno,count(e.ename) from emp e,dept d where e.deptno=d.deptno group by d.deptno) s
 on d.deptno = s.deptno ;
--12、列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal from emp e, dept d where e.deptno = d.deptno;
select e.ename, d.dname, e.sal from emp e 
inner join dept d on e.deptno = d.deptno; 
--13、查询员工信息： 员工的名字 老板的名字  
select e.ename,s.ename from emp e,(select * from emp where mgr is  null) s ;
select e.ename,s.ename from emp e
cross join (select * from emp where mgr is null) s;
--14、要求查询出每一个雇员的编号、姓名、工资、部门名称、工资在所在公司的工资等级 
select * from emp;
select e.empno,e.ename,e.sal,d.dname ,s.grade from emp e,dept d,salgrade s where e.deptno =d.deptno and e.sal < s.hisal and e.sal > s.losal;
select e.empno,e.ename,e.sal,d.dname ,s.grade from emp e
inner join dept d on e.deptno =d.deptno
inner join salgrade s on e.sal < s.hisal and e.sal > s.losal;
--15、按照职位分组，求出每个职位的最高工资、最低工资以及平均工资 
select job,max(sal),min(sal),avg(sal)  from emp e group by e.job
--16、统计平均工资大于2000的部门的详细信息
select e.deptno,avg(sal) from emp e group by e.deptno;
select * from dept d,(select e.deptno,avg(sal) asal from emp e group by e.deptno) s where d.deptno = s.deptno and s.asal > 2000;
select * from dept d 
inner join (select e.deptno,avg(sal) asal from emp e group by e.deptno) s
on d.deptno = s.deptno
where s.asal > 2000;