Oracle基础查询

DB:数据库

能够完整保存数据，可以尽可能较小冗余，支持多用户共享的数据集合。

DBMS：数据库管理系统

创建，管理，删除，运行数据库的软件
数据模型：

概念模型
逻辑模型
物理模型

概念模型:解决存什么问题
逻辑模型:解决怎么存问题

逻辑模型

层次模型

网状模型

关系模型（主流的模型）

物理模型:解决存在哪的问题

Oracle三大文件：数据文件，重做日志文件，控制文件

基础命令

sqlplus sys/1 as sysdba
//以系统管理员的身份登录oracle数据库
sqlplus scott/tiger 
//以scott用户的身份登录
conn scott/tiger
//切换scott用户
alter user scott account unlock;
//给用户scott解锁
alter user scott identified by tiger;
//给用户scott 默认密码为：tiger
alter user haha identified by xixi；

SQL: 结构化查询语言

 dql:数据查询语言
 dml:数据操作语言（第11章）
 ddl:数据定义语言
 dcl:数据控制语言
 tpl/tcl:事物处理语言或者事物控制语言

dql:

语法：语言本身不区分大小写

     SELECT  [distinct] | * |{列名 | 表达式 [列别名][,... ]}
     FROM   表名 ;
     
     SELECT ：寻找哪些数据
     FROM: 从哪里寻找数据
     []:被中括号括起来的内容是可有可无的
     | ：或者

关键字
' *' ：所有
列名：表中某一列的名字
表达式：表达式的内容可以和表无关，但是受表行数的影响
字符串表达式 ： || 是拼接操作符号，字符串由'' 括起来，字符串里的内容可以和数据源无关,null值和字符串做拼接没有影响
算数表达式：算数运算
列别名：给列起外号，如果碰到格式中不符合可以使用双引号括起来
distinct：去掉重复数据
,...：
空值/null:任何数和空值做任何运算的结果都为null

选择查询：

   SELECT  [distinct] | * |{列名 | 表达式 [列别名][,... ]}
   FROM   表名 
   [where 逻辑表达式 ];

 逻辑表达式 = 关系表达式 [逻辑运算符 ...]
 关系表达式 = 列名|表达式   比较运算符  列名|表达式

  SELECT  [distinct] | * |{列名 | 表达式 [列别名][,... ]}
  FROM   表名 
  [where {列名|表达式   比较运算符  列名|表达式} [逻辑运算符 ...] ];

 比较运算符：
            常用比较运算符：
            
                          >,< , = , >= ,  <= ,  != , <>
                         对日期的比较需要按照日期的格式
            特殊比较运算符：
                          between  and:
                          in:
                          is null:
                          like:模糊查询
                                 通配符：%: 任意长度的任意字符
                                         _: 一个长度的任意字符
                          escape: 注册转译字符

     逻辑运算符：
                and :  
                 or :
                not :

逻辑运算符的规则：

and	true	false	null
true	true	false	null
false	false	false	false
null	false	false	null

or	true	false	null
true	true	true	true
false	true	false	null
null	false	false	null

案例：

 --查询smith的信息
     
     select * 
     from emp
     where ename =  'SMITH';
     
     --查询工资超过1000的员工信息
     select *
     from emp
     where sal>1000;
     
     --查询10部门职工的工资
     select sal
     from emp
     where 10 = deptno
     
     --查询1980年12月17日入职的员工信息
     select *
     from emp
     where hiredate = '17-12月-80';
     
     --查询1982年之前入职的员工的信息
     select *
     from emp
     where hiredate<='31-12月-81';
     
     
     --查询工资低于2000 并且 工资高于1000的人员信息
     select *
     from emp
     where sal<=2000 and sal>=1000;
     
     select *
     from emp
     where sal between 2000 and 1000;
     
     --查询属于10部门或者20部门或者30部门的员工的名字
     select ename,deptno
     from emp
     where deptno =10 or  deptno = 20 or deptno = 30;
     
     select ename ,deptno
     from emp
     where deptno in(10,20,30);
     
     
     --查询不在10部门上班的员工的信息
     select *
     from emp
     where (deptno <> 10)
     
     --查询奖金为null的人员的信息
     select *
     from emp
     where  comm is not null;
     
     
     --查询人员的实际收入
     select ename ,sal,comm,sal+comm
     from emp
     where comm is not null;
   
     select ename ,sal,comm,sal
     from emp
     where comm is null;
     
     
     --查询名字像KING的员工的信息
     select *
     from emp
     where ename = 'KING';
     
     --查询出姓S的员工信息
     select *
     from emp
     where ename  like 'S%'
     --查询出姓名中包含S的员工信息
     select *
     from emp
     where ename  like '%S%'
     --查询出名字中倒数第二个字母是T的员工信息
     select *
     from emp   
     where ename  like '%T_';
     
     
     --查询所有员工的姓名和工资，要求按照工资的大小，从小往大显示
     
     select ename AS NAME ,job
     from emp
     WHERE NAME = 'SMITH' 
     order by NAME desc
     
     
     --查询名字中包含_的人员的信息
     
     select * from emp 
     where ename like '%\_%' escape '\';

排序：

     SELECT  [distinct] | * |{列名 | 表达式 [列别名][,... ]}
     FROM   表名 
     [where {列名|表达式   比较运算符  列名|表达式} [逻辑运算符 ...] ]
     [order by 列名|表达式|列别名 [asc] [desc] [,...] ];

函数

函数：编程中的一个工具，关心函数功能，名字，参数类型，结果。
函数分类：根据返回数据结果集多少

单行函数：一行数据得到一个结果，多行数据得到多个结果
多行函数：多行数据得到一个结果。
单行函数:根据数据类型

字符函数
数字函数
日期函数
转换函数
通用函数

语法：
函数的调用：
函数名（列名|表达式 [,...]）
（）：参数列表

举例：
电饭锅（电，水，米） = 饭

作用场景：
除了from都能用

例子：


 --函数
 
 --字符函数：
 --Lower
 select lower(ename) from emp;
 
 --匹配名字是smith的人员忽略大小写
 select ename from emp where lower(ename) =  'smith';
 
 select * from emp where  lower(ename) = lower('smith')
 --upper
 select upper('xixi') from emp;
 
 --initcap
 select distinct initcap('xiasjdasd') from emp;
 
 
 --dual
 select initcap('xiasjdasd')  from dual;
 
 --concat
 select concat(concat('a','b'),'c')from dual;
 select 'a'||'b'||'c' from dual;
 
 --substr
 select substr('kakaxi',4,2) from dual;
 --instr
 select instr('kakaxi','s',1,2) from dual;
 
 --查询出所有名字带A的人员的姓名中的A
 select substr(ename,instr(ename,'A')) from emp where ename like '%A%';
 
 --查询出名字中包含S的人员信息，要求不允许使用like
 
 select * from emp where  instr(ename,'S')<>0;
 
 --查询出名字是以S开头的人员的信息，要求不允许使用like 和instr
 select *
 from emp
 where substr(ename,1,1) = 'S'
 select ename from emp;
 --replace
 select replace('haha','a','c') from emp;
 --查询出名字中包含S的人员信息，要求不允许使用like ,instr,substr
select * from emp where replace(ename,'S','s') <>ename
 --lpad
 select lpad('abc',1,'$') from dual;
 select rpad('abc',10,'$') from dual;
 --length 字符长度
 SELECT LENGTH('你我他')FROM DUAL;
 --trim
 select trim( '    hah   ah    ') from dual;
 select trim(leading  'h' from 'haha') from dual;
 select trim(trailing  'a' from 'haha') from dual;
 select trim(both  'a' from 'haha') from dual;
 --数字函数：
 --mod ：取余
 select mod(5,3) from dual;
 --round:四舍五入
 select round(4.5) from dual;
 --trunc:截断
 select trunc(4.1) from dual;
 -floor：向下取整
 select floor(4.9) from dual;
 --ceil：向上取整
 select ceil(4.98) from dual; 
 select floor (-4.1) from dual;
 --sign :取表达式或者数的结果为正负
 select  sign(-100) from dual;
 --abs：绝对值
 select abs(-100)  from dual;
 
 --日期函数
 --加一天
 select hiredate,hiredate+1 from emp;
 --加一个小时
 select hiredate,hiredate+1/24/60/60/1000 from emp;
 --加一星期
 select hiredate,hiredate +7 from emp;
 --加一个月 :需要函数
 select hiredate,add_months(hiredate,0.8) from emp;
 select * from emp  for update;
 --17-十二月-80
 --sysdate:系统当前时间
 select sysdate from dual;
 --显示每个员工入职的天数
select HIREDATE ,sysdate - hiredate from emp WHERE HIREDATE ='23-5月-87'
--显示每个员工入职的月数
select months_between(sysdate,hiredate) from emp;
--next_day：下一个周几
select next_day(sysdate,2) from dual;
--last_day:当月最后一天
select last_day(sysdate) from dual;
select trunc(sysdate,'ss')from dual;
--区分年月日
select extract (day from sysdate) from dual;

转换：显示转换和隐式转换
隐式转换：改变数据看原始的类型；一般转换动字符；连接运算其他转字符。

 --数据类型转换
-- 数据类型： date ，number ，char
--   转换规则： char->date, char ->number , number <=char => date 
--   转换方法： to_char(number|date ,[fmt]) ;to_date (char,[fmt]);to_number(char[,fmt])     
--   格式码：yyyy   mm  dd hh  mi  ss
--   to_char:
     select to_char(1) from dual ;
     select 1+2 from dual;
--     select to_char(sysdate)+1 from dual;
     select sysdate+1  from dual;
     select to_char(sysdate ,'yyyy$mm$dd $hh24$mi$ss')from dual;
     select * from emp where hiredate>'1-1月-81';
--  to_date :    
     select to_date('1981-1-1','yyyy-mm-dd')+1 from dual;   
--  to_number： 
--  to_char: ,  .  0   9  $  L
    select to_char(400000,'$9999,999.00')  from dual;    
--  
    select  to_number('$400,000.00','$9999,999.00')+100 from dual;  
    --to_char
    select to_char(sysdate,'yy$%-"年"mm') from dual;

--通用函数：

--nvl
select nvl(comm,0) from emp;
select nvl(to_char(comm),'0') from emp;
--nvl2
select nvl2(comm ,comm,0) from emp;
--nullif
select nullif(3,3) from dual;
select 1/nullif(0,0) from dual;
--coalesce
select coalesce(null,null,null,4,null,6) from dual;
--case  ,decode
--查询各个部门的人员姓名，部门编号和部门的名称
select ename,
       deptno,
       (case deptno
         when 10 then
          '10部门'
         when 20 then
          '20部门'
         else
          '30部门'
       end)
  from emp;
  
select ename ,deptno ,decode(deptno ,10,'10部门',20,'20部门','30部门')
from emp ; 
select decode(haha,1,11,2,12,3,13,4,14,5,15,6,16,7,17,8,19)  from dual;

select * from dept;
/*
10  ACCOUNTING
20  RESEARCH
30  SALES
40  OPERATIONS
*/
--要求查询出 所有人员的姓名，工资，（如果工资低于1000，屌丝；如果1000到2000之间 ，
--能活；如果2001到3000之间，哎呦不错哦；如果超过高富帅）

--case 的简单写法
select ename,
       sal,
       case
         when sal < 1000 then
          'X丝'
         when sal between 1000 and 2000 then
          '能活'
         when sal between 2001 and 3000 then
          '哎呦不错哦'
         else
          '高富帅'
       end
  from emp;
    
select ename,
       sal,
       decode(sign(sal - 1000),
              -1,
              'X丝',
              0,
              '能活',
              1,
              decode(sign(sal - 2000),
                     -1,
                     '能活',
                     0,
                     '能活',
                     1,
                     decode(sign(sal- 3000),
                            -1,
                            '哎呦不错哦',
                            0,
                            '哎呦不错哦',
                            1,
                            '高富帅')))
  from emp;
  
--order by 

select *
  from emp
 order by decode(job,
                 'PRESIDENT',
                 1,
                 'ANALYST',
                 2,
                 'MANAGER',
                 3,
                 'SALESMAN',
                 4,
                 'CLERK',5)
--PRESIDENT,ANALYST,MANAGER,SALESMAN,CLERK

create table haha(name  varchar2(10),class varchar2(10) ,cj number(10));
--select * from haha for update

select name,
       sum(case class
         when 'oracle' then
          cj
       end) oracle,
        sum(case class
         when 'java' then
          cj
       end) java,
        sum(case class
         when 'HTML' then
          cj
       end) HTML
  from haha
  group by name;

多行函数

sum：和
count:计数
max:最大
min:最小
avg:平均

多行函数不处理null值
计数：count(1) ;count(*)
多行函数的作用是用来统计分析，每次统计相当于对原始数据进行压缩提炼，会改变原表的结构。

分组语句：分组的作用是细化统计的项目，让统计数据更精准。

group by：分组条件
having: 能够处理分组函数条件的条件子句

     SELECT  [distinct] | * |{列名 | 表达式 [列别名][,... ]}
     FROM   表名 
     [where {列名|表达式   比较运算符  列名|表达式} [逻辑运算符 ...] ]
            [group by  列名|表达式 [,...]]
            [having  （包含分组函数的）条件表达式]
     [order by 列名|表达式|列别名 [asc] [desc] [,...] ];

语句执行顺序：
from
where
group by
having
select
order by


 --函数嵌套
--  单行嵌单行
    select concat(concat('a','b'),'c') from dual;
--  单行嵌多行
    select round(avg(sal)) from emp;
--  多行嵌单行
    select avg(round(sal)) from emp;
--  多行嵌多行
    select avg(count(1)) from emp group by deptno

多表连接

--查询smith的员工姓名，部门名称，部门所在的地址
select empno ,ename ,deptno from emp where ename = 'SMITH';
select dname ,loc from dept where deptno = 20;
--多表连接
select * from emp , dept
WHERE DEPT.DEPTNO = EMP.DEPTNO

--sql 99


 --sql99
 --cross join   (交叉连接)
 select * from emp cross join dept;
 --oracle  笛卡尔积
 select * from emp , dept;
 
 --natural joib  (自然连接)
 select * from emp natural join dept;
 emp salgrade
 
 --using 连接（natural 二代）
 select * from emp join  dept using(deptno);
 
 select * from emp a join emp b using(empno)
 
 --oracle 等值连接
 select * from emp ,dept  where emp.deptno = dept.deptno
 
 
 --on 连接
 
 select *
   from emp
   join salgrade
     on (emp.sal between salgrade.losal and salgrade.hisal and ename = 'SMITH');
     
select  * from emp join dept 
 on(emp.deptno = dept.deptno)  ;   
     
  --不等值连接
 select * 
 from emp 
 ,salgrade
 where emp.sal between salgrade.losal and salgrade.hisal;
 
 --left outer join
 --查询领导的姓名和员工的姓名

 select a.ename ,b.ename
 from emp b ,emp a
 where  b.mgr =a.empno (+)

 select a.ename ,b.ename
 from emp b left outer join emp a on(b.mgr =a.empno);
 
 --right outer join 
 --查询部门的名称和员工的名称 （所有）
 select dname ,ename
 from  emp,dept
 where   emp.deptno (+)= dept.deptno;
 select dname ,ename
 from emp right outer join dept on( emp.deptno = dept.deptno)

/*
1.显示员工SMITH的姓名，部门名称，直接上级名称
2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。
3.显示员工KING和FORD管理的员工姓名及其经理姓名。
4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。
*/

SELECT  A.ENAME ,C.DNAME ,B.ENAME
FROM EMP A, EMP B,DEPT C
WHERE A.MGR = B.EMPNO  AND A.DEPTNO = C.DEPTNO AND A.ENAME = 'SMITH';

SELECT ENAME ,DNAME ,SAL ,GRADE 
FROM EMP A ,DEPT B,SALGRADE C
WHERE A.DEPTNO =  B.DEPTNO AND A.SAL BETWEEN  C.LOSAL AND C.HISAL AND GRADE >4;

SELECT A.ENAME ,B.ENAME
FROM EMP A,EMP B
WHERE A.MGR = B.EMPNO AND B.ENAME IN ('KING','FORD');

SELECT A.ENAME ,A.HIREDATE ,B.ENAME ,B.HIREDATE
FROM EMP A ,EMP B
WHERE A.MGR = B.EMPNO  AND A.HIREDATE<B.HIREDATE;

练习题：
查询20号部门的所有员工信息：
查询奖金（COMM）高于工资（SAL）的员工信息：
查询奖金高于工资的20%的员工信息:
查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息:
查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信息：
查询没有奖金或奖金低于100的员工信息:
查询员工工龄大于或等于10年的员工信息:
查询员工信息，要求以首字母大写的方式显示所有员工的姓名:
查询在2月份入职的所有员工信息:
显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序:
查询'JONES'员工及所有其直接、间接下属员工的信息:
查询SCOTT员工及其直接、间接上级员工的信息：
试用SQL语言完成下列查询(多表查询)：
查询各个部门的详细信息以及部门人数、部门平均工资:
查询10号部门员工以及领导的信息:
查询工资为某个部门平均工资的员工信息:
统计各个工种的人数与平均工资:
统计每个部门中各个工种的人数与平均工资:
查询所有员工入职以来的工作期限，用“年月**日”的形式表示。
查询人数最多的部门信息：
部门平均薪水最高的部门编号:
比普通员工的最高薪水还要高的经理人名称:
查询所有员工工资都大于1000的部门的信息:
查询所有员工工资都大于1000的部门的信息及其员工信息:
查询所有员工工资都在900~3000之间的部门的信息:
查询所有工资都在900~3000之间的员工所在部门的员工信息:
查询每个员工的领导所在部门的信息：
查询工作等级为2级，1985年以后入职的工作地点为DALLAS的员工编号、姓名和工资：

  查询20号部门的所有员工信息：
  select *
  from emp 
  where deptno = 20;
  查询奖金（COMM）高于工资（SAL）的员工信息：
  select * 
  from emp
  where comm>sal;
  查询奖金高于工资的20%的员工信息:
  select * from emp
  where comm>sal*1.2;
  查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息:
  select *
  from emp
  where (deptno = 10 and job = 'MANAGER' )  or ( deptno = 20  and job = 'CLERK');
  查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信息：
  select * from emp
  where job not in ('MANAGER','CLERK') and  sal>=2000;
  查询没有奖金或奖金低于100的员工信息:
  select * from emp
  where comm is null or comm <100;
  
  查询员工工龄大于或等于10年的员工信息:
  select * from emp
  where months_between (sysdate,hiredate)/12>=10
  
  查询员工信息，要求以首字母大写的方式显示所有员工的姓名:
  select initcap(ename)
  from emp ;
  
  查询在2月份入职的所有员工信息:
  select * from emp
  where to_char(hiredate,'mm') = '02';
  显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序:
  select ename ,to_char(hiredate,'yyyy-mm')
  from emp
  order by to_char(hiredate,'mmyy')
  
  查询'JONES'员工及所有其直接、间接下属员工的信息:

  select * from emp a
  start with ename ='JONES'
  connect by PRIOR EMPNO = MGR
  
  查询SCOTT员工及其直接、间接上级员工的信息：

  select * from emp a
  start with ename ='SCOTT'
  connect by PRIOR mgr = empno
 
  试用SQL语言完成下列查询(多表查询)：

  查询各个部门的详细信息以及部门人数、部门平均工资:
  
  select dept.deptno, dname, loc, count(1), avg(sal)
    from dept, emp
   where dept.deptno = emp.deptno
   group by dept.deptno, dname, loc
  
  查询10号部门员工以及领导的信息:
  select *
  from emp a ,emp b
  where a.mgr = b.empno and a.deptno = 10;
  
  查询工资为某个部门平均工资的员工信息:
  
  select *
  from emp
  where sal in(select avg(sal) from emp group by deptno);  
  
  统计各个工种的人数与平均工资:
  
  select job,count(1),avg(sal)
  from emp
  group by  job;
  
  统计每个部门中各个工种的人数与平均工资:
  
    
  select deptno,job,count(1),avg(sal)
  from emp
  group by  deptno,job;
  
  查询所有员工入职以来的工作期限，用“**年**月**日”的形式表示。 
  
  select to_char(hiredate,'yy"年"mm"月"dd"日"')
  from emp 
  
  查询人数最多的部门信息：
  select *
    from dept
   where deptno =
         (select deptno
            from emp
          having count(1) = (select max(count(1)) from emp group by deptno)
           group by deptno);
  
   select a.*
     from dept a,
          (select deptno, count(1) counts from emp group by deptno) b,
          (select max(count(1)) counts from emp group by deptno) c
    where a.deptno = b.deptno
      and b.counts = c.counts

/*   部门平均薪水最高的部门编号:
  比普通员工的最高薪水还要高的经理人名称:
  查询所有员工工资都大于1000的部门的信息:
  查询所有员工工资都大于1000的部门的信息及其员工信息:
  查询所有员工工资都在900~3000之间的部门的信息: 
  查询所有工资都在900~3000之间的员工所在部门的员工信息:
  查询每个员工的领导所在部门的信息：
  查询工作等级为2级，1985年以后入职的工作地点为DALLAS的员工编号、姓名和工资：*/
  
--1  
  select deptno
    from emp
   group by deptno
  having avg(sal) = (select max(avg(sal)) from emp group by deptno)
--2
  select ename
    from emp a
   where exists
   (select 1 from emp b where a.empno = b.mgr)
     and a.sal >
         (select max(sal)
            from (select *
                    from emp a
                   where not exists
                   (select 1 from emp b where a.empno = b.mgr)));
                   
  select ename
    from emp a
   where empno in (select mgr from emp)
     and a.sal >
         (select max(sal)
            from emp
           where empno not in (select mgr from emp where mgr is not null));
  select a.ename
    from emp a,
         (select max(sal) maxsal
            from emp
           where empno not in (select mgr from emp where mgr is not null)) b
   where a.sal > b.maxsal
     and a.empno in (select mgr from emp);
--3     
   select *
     from dept
    where deptno in
          (select deptno from emp group by deptno having min(sal) > 1000);
--4
   select *
     from dept, emp
    where dept.deptno in
          (select deptno from emp group by deptno having min(sal) > 1000)
      and dept.deptno = emp.deptno;
--5
  select *
     from dept
    where deptno in
          (select deptno from emp group by deptno having min(sal) >=900 and max(sal)<=3000);
--7
  select *
    from dept
   where deptno in
         (select deptno from emp where empno in (select mgr from emp));
--8查询工作等级为2级，1985年以后入职的工作地点为DALLAS的员工编号、姓名和工资：
  select empno, ename, sal
    from emp a, salgrade s, dept d
   where a.deptno = d.deptno
     and a.sal between s.losal and s.hisal
     and s.grade = 2
     and loc = 'DALLAS'
     and to_number(to_char(hiredate, 'yyyy')) > 1985;

子查询

--分步查询
select sal from emp where ename ='JONES';
SELECT ENAME FROM EMP WHERE SAL>2975.00;
--多表连接
SELECT B.ENAME
FROM EMP A, EMP B
WHERE A.ENAME ='JONES' AND a.sal <b.sal;

--子查询

SELECT ENAME
FROM EMP
WHERE SAL > (select sal from emp where ename = 'JONES');

--多行子查询
in， any ，all
any：翻译是一些的意思

--多列子查询
where中多个条件绑定判断需要通过多列子查询处理。

--NOT IN 空值问题
如果子查询的返回值中有null值，需要通过is not null处理空值。

FROM子查询：通过from子查询能够把一个子查询作为临时表供给主查询当数据源使用，在使用from子查询的时候一般需要给子查询起表别名，其中的分组函数起列别名。
from子查询的作用一般处理分组函数和非分组条件同时显示的问题。

分页：

```sql

 --rownum:伪列
 select b.*
   from (select rownum rn, a.*
           from (select * from emp order by sal desc) a
          where rownum <= 5) b
  where rn > 0
  ;
  
 select b.*
   from (select rownum rn, a.* from emp a   order by sal desc) b
  where rn<=10 and rn > 5;  
 --分页

查询语句分析整理

 --查询emp表
 select * from emp;
 --查询人员的姓名
 select ename from emp;
 --查询10部门人员的姓名
 select ename from emp where deptno = 10;

 --查询出smith的部门名称
 select deptno from emp where ename ='SMITH';
 select dname  from dept where deptno = 20;
 --不能处理所有人
 
 --单行函数：更好处理数据，来源于原表，根据使用需求调整数据
 
 --查询smith，king，scott的部门名称
 --子查询
        select dname
          from dept
         where deptno in
               (select deptno
                  from emp
                 where ename in ('SMITH', 'KING', 'SCOTT'));
                 
         --查询出和1981年入职的任意一个员工的部门和职位完全相同员工姓名、部门、职位、入职日期,
         select ename ,deptno ,job,hiredate
         from emp
         where (deptno,job)in(select deptno,job from emp where to_char(hiredate,'yyyy')='1981')
         --查询smith所在部门的平均工资,我们想要通过统计更清晰的理解数据情况
         select avg(sal)
         from emp 
         where deptno = (select deptno from emp where ename ='SMITH');
           --如果多行函数当条件：必须having
           --如果多行函数做查询：select中有，groupby 必须有
           
           
         --多列子查询
         --查询所在部门工资最高的人的人员信息
                  --所在，其，自己
          select *
            from emp
           where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);
         
           
      --高级子查询     
        --显示每个部门工资最低的人的姓名和所在部门的最低工资         
        --查询比所在部门平均工资高的人员信息
        --查询员工的姓名和其所在部门部门的名称
          select ename, (select dname from dept b where a.deptno = b.deptno) dname
            from emp a

        select ename, sal
          from emp a
         where sal > (select avg(sal) from emp b where a.deptno = b.deptno);
         
         
         --查询员工的姓名和领导的姓名
        select ename, (select ename from emp b where a.mgr = b.empno) ename
          from emp a;
        select a.ename, b.ename from emp a, emp b where a.mgr = b.empno(+)
        
        --查询哪些员工是经理
        
        select * from emp where empno in(select mgr from emp);
        
        select distinct b.* from emp a,emp b where a.mgr = b.empno;
        
        select *
          from emp a
         where empno =
               (select distinct mgr from emp b where a.empno = b.mgr);
           --高级子查询改写，原因是：尴尬
        select *
          from emp a
         where (select count(1) from emp b where a.empno = b.mgr) > 0;
        --查询是领导的员工信息
        select *
          from emp a
         where exists (select 1 from emp b where a.empno = b.mgr)
         
         
           --查询出每个部门工资前三的人员信息
           
--           使用集合


              select *
                from emp e
               where sal = (select max(sal)
                              from (select *
                                      from emp
                                    minus (select *
                                            from emp a
                                           where sal = (select max(sal)
                                                          from emp b
                                                         where a.deptno = b.deptno)
                                          union
                                          select *
                                            from emp c
                                           where sal = (select max(sal)
                                                          from (select *
                                                                  from emp
                                                                minus (select *
                                                                        from emp a
                                                                       where sal =
                                                                             (select max(sal)
                                                                                from emp b
                                                                               where a.deptno =
                                                                                     b.deptno))) d
                                                         where c.deptno = d.deptno))) f
                             where e.deptno = f.deptno)
              union
              select *
                from emp a
               where sal = (select max(sal) from emp b where a.deptno = b.deptno)
              union
              select *
                from emp c
               where sal = (select max(sal)
                              from (select *
                                      from emp
                                    minus (select *
                                            from emp a
                                           where sal = (select max(sal)
                                                          from emp b
                                                         where a.deptno = b.deptno))) d
                             where c.deptno = d.deptno)
              union
              select *
                from emp a
               where sal = (select max(sal) from emp b where a.deptno = b.deptno)

        --使用相关子查询
           select *
             from emp a
            where (select count(1)
                     from emp b
                    where a.deptno = b.deptno
                      and a.sal < b.sal) <= 2;
                      
        --exists
          select *
            from emp a
           where exists (select 1
                    from emp b
                   where a.deptno = b.deptno
                     and a.sal < b.sal having count(1) <= 2);
 
 --多表连接
       select distinct dname
         from emp, dept
        where emp.deptno = dept.deptno
          and ename in ('SMITH', 'KING', 'SCOTT');

        --多行函数使用问题
        --显示每个部门工资最低的人的姓名和所在部门的最低工资
        select ename ,minsal
        from emp a, (select deptno ,min(sal)minsal from emp group by deptno)b
        where a.deptno = b.deptno 
        and a.sal = b.minsal;
        
        --查询比所在部门平均工资高的人员信息
        select ename, sal
          from emp a,
               (select deptno, avg(sal) avgsal from emp group by deptno) b
         where a.deptno = b.deptno
           and a.sal > b.avgsal;



/* 如下练习，用相关子查询完成
1.查询所有雇员编号，名字和部门名字。
2.查询哪些员工是经理？
3.查询哪些员工不是经理？
4.查询每个部门工资最低的两个员工编号，姓名，工资。
*/

--1
select empno ,ename ,(select dname from dept b where a.deptno = b.deptno) dname
from emp a ;

select a.empno ,a.ename ,b.dname
from emp a ,dept b
where a.deptno = b.deptno ;
--2
select *
from emp where empno in(select mgr from emp);
select distinct a.*
from emp a ,emp b
where  a.empno = b.mgr;
select *
from emp a where exists(select 1 from emp b where a.empno =b.mgr);
--4
select empno ,ename ,sal,deptno
from emp a 
where exists(select 1 from  emp b where a.sal>b.sal and a.deptno = b.deptno  having count(1)<=1)

层次查询

select level,a.* from emp a
--start with ename ='SMITH'
where level = 2
start with ename ='KING'
connect by prior  empno = mgr and  ename <>'BLAKE'

level：层级
start with ：起点
connect by prior :层次条件

DML

DELETE

删除数据
delete 代替select * ,其他和查询一直

INSERT

添加数据有两种：
添加主数据：
insert into 表（列名[,...]） values(值[,...])；
添加业务数据：
insert into 表（列名[,...]） select语句（查询值）;

UPDATE

update语句分成两部分：分为修改哪些数据？修改的数据内容为什么？

思路：根据“值等于”和“为什么”断句，分别书写两个查询语句再合成一个语句

insert语句一般和多表配合，update一般和子查询配合使用

事物：一组sql语句（一般对应前台的一个业务操作），从第一个当前对话或者连接中运行的dml语句开始到第一个tpl语句结束。
作用：保证数据的一致性。
特性：原子性，一致性，持久性，隔离性。
隔离性是通过锁来实现的。

DDL

数据库对象分为：表，约束，索引，视图，序列，同义词，（函数，过程，触发器）等。
定义和操作数据库对象的语句为ddl语句：
create drop truncate alter
数据库对象命名：字母开头，（数字，字母，下划线，$,#）组成。

数据类型：
varchar2对比char ，能够节省空间，可变长度；缺点是速度比char慢。如果是性别类型的字段最好用char，varchar2适合如名字这样的字段。

number：可以不跟长度，但是最好是有长度的。（如果是两个参数，第一个数总长度，第二个是小数长度）

clob和blob：4g大小的字段，一个是字符，一个是图片

drop table：删除表的结构和数据，可以还原
delete ：删除某些数据，可以还原
truncate table：删除全部数据不能还原

alter table：修改表的结构。

 --修改平均工资最低的部门的所有的做领导的人员       的工资为10部门平均工资
 update emp a
    set sal =
        (select avg(sal) from emp where deptno = 10)
  where empno in
        (select empno
           from emp b,
                (select avg(sal) avgsal, deptno from emp group by deptno) d,
                (select min(avg(sal)) minavgsal from emp group by deptno) c
          where empno in (select distinct mgr from emp)
            and b.deptno = d.deptno
            and d.avgsal = c.minavgsal);
            
            
  update emp a
     set sal =
         (select avg(sal) from emp where deptno = 10)
   where exists (select 1
            from emp b,
                 (select deptno
                    from emp
                   group by deptno
                  having avg(sal) = (select min(avg(sal))
                                      from emp
                                     group by deptno)) c
           where a.empno = b.mgr
             and a.deptno = c.deptno
             and a.deptno = b.deptno)


 --修改工资等级在3级的员工的领导的     工资为20部门的最低工资
 update emp a
    set sal =
        (select min(sal) from emp where deptno = 20)       
  where empno in (select mgr
                    from emp, salgrade
                   where emp.sal between losal and hisal
                     and grade = 3)
 
 --修改工作地点在芝加哥的部门中工资最低的人员的    岗位为他们所在部门工资最高的人员的岗位
 
 update emp a
    set job =
        (select job
           from emp b
          where a.deptno = b.deptno
            and b.sal = (select max(sal) from emp where deptno = b.deptno))
  where EXISTS (SELECT 1
           FROM DEPT B
          WHERE A.DEPTNO = B.DEPTNO
            AND LOC = 'CHICAGO')
    AND EXISTS (SELECT 1
           FROM EMP B
          WHERE A.DEPTNO = B.DEPTNO
            AND A.SAL < B.SAL HAVING count(1) <= 0)
 
 update emp a
    set job =
        (select job
           from emp,
                dept,
                (select min(sal) maxsal, deptno from emp group by deptno) c
          where emp.deptno = dept.deptno
            and loc = 'CHICAGO'
            and c.deptno = dept.deptno
            and c.maxsal = emp.sal)
  where empno in
        (select empno
           from emp,
                dept,
                (select min(sal) maxsal, deptno from emp group by deptno) c
          where emp.deptno = dept.deptno
            and loc = 'CHICAGO'
            and c.deptno = dept.deptno
            and c.maxsal = emp.sal);

约束


--定义约束
  --列级约束
  create table  Person(
         name  varchar2(50) not null,
         sex   char(1) constraint  sex_person_ck check ( sex in ('1','2','0')),
         id    char(18) constraint id_person_uk unique,
         dna   char(50) constraint dna_person_pk primary key,
         classid  varchar2(10) constraint classid_Person_class_fk references class(classid)
  
  )


--追加约束
  --表级约束
  create table class(
         classid varchar2(10) ,
         classcount number ,
         classname varchar2(10) not null,
         constraint classid_class_pk  primary key (classid),
         constraint classcount_class_ck check (classcount>0)
         
  )
  
  alter  table Person add constraint classid_Person_class_fk foreign key (classid)
  references class(classid)

视图


create or replace view haha
as
select * from emp
where deptno = 10
with check option constraint hahacon
;

or replace 是替换的意思
with check option 根据视图的查询条件做约束
视图分为：简单和复杂
复杂视图一般包含多个表，分组，函数等。
复杂视图根据实际情况看是否能进行dml操作，简单视图可以做dml操作。
with read only:只读视图

序列

使用序列：生产系统的id
nextval ，currval 通过这两个属性使用
一般不适用循环，缓存属性可能会丢值

索引

索引：通过对某个字段建立索引表，对索引表进行排序之后使用二分查找提高查询效率，最后通过rowid找回原表的数据位置。
索引：可以是某一列或者某几列；如果给了主键或者唯一键约束默认给索引。
索引的优缺点:
提高查询速度，浪费控件，降低数据修改的速度

什么使用适合使用索引：
数据量很大，查询的内容占总内容较少，表本身不经常被修改，数据超过20万

同义词

只有和权限配合才有意义

用户，权限，角色

用户是oracle最大的对象，包含其他对象。
权限：
系统权限：
对象权限：
通过dcl语句来操作：
grant create session to scott
grant select on emp to scott
revoke create session from scott

角色：权限的集合，系统常用的是dba，connect，resourse

远程连接

客户端：需要配置本地服务
本地服务由：网络服务名=数据库名+ip地址+端口组成
服务器：需要配置监听器

最后编辑于：2018.12.21 13:17:24

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 159,458评论 4赞 363
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,454评论 1赞 294
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 109,171评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,062评论 0赞 207
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,440评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,661评论 1赞 219
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,906评论 2赞 313
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,609评论 0赞 200
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,379评论 1赞 246
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,600评论 2赞 246
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,085评论 1赞 261
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,409评论 2赞 254
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,072评论 3赞 237
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,088评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,860评论 0赞 195
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,704评论 2赞 276
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,608评论 2赞 270