三表联合查询&&二维表的简单创建&&用户操作&&子查询

--SQL92&SQL99实现三表联合查询
  --创建city表：使用图形操作即可
  --给city表添加测试数据
    insert into city values(1,'商丘','历史闻名古都');
    insert into city values(2,'邯郸','历史闻名古都');
    insert into city values(3,'洛阳','历史闻名古都');
    insert into city values(4,'开封','历史闻名古都');
  --将部门表中的loc字段设置为城市表的城市编号
    update dept set loc='1' where deptno=50;
    update dept set loc='2' where deptno=40;
    update dept set loc='3' where deptno=30;
    update dept set loc='4' where deptno=20;
    update dept set loc='4' where deptno=10;
  --完成三表联合查询
    --SQL92实现：查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
        --特点：易于书写，难于阅读
        --缺点：92的SQL语句结构不清晰
        --用法：
             --select  内容 (别名，连接符，去除重复，oracle函数，逻辑运算)
             --from  表名1，表名2，表名3...
             --where  条件(连接条件，普通筛选条件，where子句关键字)
             --group by 分组字段
             --having 多行函数筛选
             --order by 排序字段
        select e.*,d.dname,c.cname 
        from emp e,dept d,city c
        where (e.deptno=d.deptno and d.loc=c.cid and sal>2000) or (e.deptno=d.deptno and d.loc=c.cid and comm is not null) 
        order by e.sal 
   --SQL99实现:查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
        --特点：难于书写，易于阅读
        --使用：
              --select 内容 from 表名1
              -- inner join 表名2
              -- on 连接条件
              --inner join 表名3
              --on 连接条件
              --where  普通筛选条件
              --group by 分组
              --having 多行函数筛选
              --order by 排序
      select * 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
      order by e.sal

-------------------------------------------------------------------------------------------

 

---Oracle的二维表管理
    --oracle二维表的创建
---------------------------------------
 --创建表:
    --使用：create table 表名(字段名 类型，字段名 类型，....);
    --数据类型：
        --number类型
              --数值类型
                   --整数类型 number(a)   总长度为a
                   --浮点数类型 number(a,b) 总长度为a，小数位长度为b，小数位可以不写。
        --varchar2类型
                --字符类型 varchar2(ln) ln表示字符的最大长度，实际存储内存长度是根据字符大小来分配，但是最大不能超过ln 
                --特点：动态分配存储空间，节省空间
        --char类型
                --字符类型 char(ln) 不管字符数据长度是多大，直接开辟ln大小的空间存储数据
                --特点：存储效率高于varchar2
        --date类型
    create table student(
      sno number(10),
      sname varchar2(100),
      sage number(3),
      ssex char(100),
      sfav varchar2(500),
      sbirth date   
    )
 --添加测试数据
    insert into student values(1,'柳岩',18,'女','拍电影'，'01-1月-1985');
    insert into student values(2,'古力娜扎',20,'女','拍电影'，to_date('1990-01-01','yyyy-mm-dd'));
select * from student

-----------------------------------------------------------------------------------------------------------

---Oracle学习
  --oracle管理系统介绍(客户端和服务器端的交互模式)
  --oracle数据库的数据管理(增删改查 查询)
  --oracle账户管理
  --oracle二维表管理
          --创建表
          --维护表
          --删除表
-----------------------------------------------------------------------
--oracle的账户管理学习：
    --权限：具备某类事物的操作的能力，此能力称为权限。
    --角色：一系列权限的集合
    --oracle自带账户：
      -- system 管理账户  bjsxt
         --特点：具备大部分oracle的操作权限，主要用来管理普通账户及oralce的数据
         --使用人：oracle数据维护工作人员
      -- sys 超级管理员账户 bjsxt
         --特点：具备system的所有权限，同时又具备其他的权限
         --使用人：oracle攻城狮
    --创建账户
         --使用system账户，并使用dba身份，登录oracle管理系统
         --创建用户
             create user bjsxt identified by bjsxt;         
    --维护账户
            --赋予权限  grant 权限或者角色名 to 用户名
            
             grant connect to bjsxt;--给用户赋予登录权限
             grant resource to bjsxt;--给用户资源操作权限
             grant dba to bjsxt;--给用户赋予dba权限
             select * from scott.emp--查看其它用户的表 使用用户名.表名  
             
            --删除权限 revoke 权限或者角色名 from 用户名
             revoke dba from bjsxt;
    --删除账户 drop user 用户名
            drop user bjsxt;
-------------------------------------------------------------------------------------------------

-单表查询：
  --当需要的数据在一张表中，考虑使用单表查询
--多表联合查询：
  --当需要查询的数据分布在多张表中，考虑使用多表联合
--子查询学习：
  --使用时机：当查询的筛选条件不明确时，考虑使用子查询。
  --单行子查询
  --多行子查询
----------------------------------------------------------------
--单行子查询:
  --使用时机：筛选条件不明确需要执行一次查询，并且查询结果一个字段并值只有一个
  --注意：where子句中允许出现查询语句，该查询语句称为子查询
  --使用：select 内容 from 表名 where 字段名 比较运算符 子查询语句
    --查询所有比雇员“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 )
    --查询和soctt属于同一部门且工资比他低的员工资料
    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 empno,ename from emp where job=(select job from emp where ename='SCOTT') or hiredate <(select hiredate from emp where ename='SCOTT')
----------------------------------------------------------------------------
----多行子查询：
     --使用:子查询的结果只有一个字段但是字段有n个值，考虑使用多行子查询，其实就是使用关键字
       --关键字1:any 任意
            --select 内容 from 表名 where 字段名 比较运算符 any 子查询语句
       --关键字2：all 所有
            --select 内容 from 表名 where 字段名 比较运算符 all 子查询语句
       --关键字3：in 表示任意存在,相当于 = any  
            --select 内容 from 表名 where 字段名 in 子查询语句   
            --select 内容 from 表名 where 字段名 not in 子查询语句   
    --查询工资高于任意一个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 job from emp where deptno=10
    select *from emp where (job='MANAGER' or job='PRESIDENT' or job='CLERK') and deptno=20
    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