Oracle学习笔记的整理总汇

这几天复习了一下Oracle的知识，顺便整理一下笔记

分了十几章的笔记，有些地方还不是很全面，后期有空再补吧。

在学习oracle之前要先安装Oracle数据库。

第一章 登录及修改用户

先打开cmd窗口

运行令命进入 sqlPlus /nolog

解锁Oracle默认的用户账号,令命如下

alter user scott account unlock identified by tiger;

记得要开启Oracle的服务“OracleServiceORCL”和监听服务“OracleOraDb11g_home1TNSListener”

conn--登录令命

sqlPlus 用户名

sqplus 用户名/密码

--连接账号令命

conn

conn 用户名

conn 用户名/密码

也何以用无密码的方式登录 令命如下

conn / as sysdba;

--退出系统

exit

quit

--查看用户名和状态（管理员执行）

select username,account_status from dba_users;

--解锁账户（管理员执行）

alter user 用户名 account unlock;

--锁定用户（管理员执行）

alter user 用户名 account lock;

--修改密码语句

alter user 用户名 identified 密码；

--修改个人密码

password

--修改指定账户密码

password 用户名

--使密码失效

alter user 用户民 password expire;

--查看当前用户有那些表

select table_name from user_tables;

--执行文件

start file_name ;//文件名

一般SQL执行时又右到左执行（把苛刻的语句放在右边，有利于执行效率的提高）

第二章 简单查询

--查询emp表所有记录

select * from emp;

--查询emp表员工部门编号

SELECT empno,ename,deptno FROM emp;

--查询所员工的年薪

SELECT empno,ename,(sal+200)*12 FROM emp;

--员工转正后工资，20%（%不能识别，用小数)

SELECT ename,(1+0.2)*sal FROM emp;

--查询所有员工的年薪，考虑奖金（COMM，有些null）

SELECT empno,ename,(sal+200) * 12 +nvl(comm,0) FROM emp;

--别名（as 关键字），有空格或者特殊字符，加“”

SELECT empno,ename,(sal+200)*12+nvl(comm,0) as 年薪 FROM emp;

--别名(空格)

SELECT empno,ename,(sal+200)*12+nvl(comm,0) 年薪 FROM emp;

--练习三

SELECT ename as 员工姓名,sal as 员工工资,comm as 奖金,sal*6+sal*(1+0.2)*6+comm as 

总收入 FROM emp;

--连接操作字符  ||

SELECT ename|| job as "employees aaa" FROM emp;

--原义字符创'si A'

SELECT ename || 'is A' || job as "employees aaa" FROM emp;

--去掉重复行 distinct

SELECT DISTINCT deptno as 部门编号 FROM emp;

--练习4（计算总收入的表达式一定要加括号)

SELECT ename || '的第一年总收入为' || (sal*6+sal*(1+0.2)*6+nvl(comm,0) FROM emp;

SELECT DISTINCT job 岗位类型 FROM emp;

第三章 比较逻辑运算符及优先级

--带查询条件的语句，WHERE关键字，放在FROM 后面

--比较操作运算符的运用

SELECT * FROM emp WHERE deptno=10;

SELECT ename,sal FROM emp WHERE sal>=2000;

SELECT * FROM emp WHERE hiredate >'01-1月-1981' --注意oracle中的日期表示

--KING区分大小写的注意

SELECT * FROM emp WHERE ename='KING'  

--特殊的比较运算符

--特殊运算符between...and,表示取值在一个区间范围，包括边界值

SELECT * FROM emp WHERE sal between 2000 and 3000;

--in 要比较的字段与几何里面的任意一个值相等

SELECT * FROM emp WHERE deptno in (10,20);

--like,部分满足，%匹配零个或者任意多个字符，一匹配一个任意字符

SELECT * FROM emp WHERE ename like  'S%';--名字以S开头

SELECT * FROM emp WHERE ename like  '%R';--名字以R结尾

--is null/is not null

SELECT * FROM emp WHERE comm is NULL;--奖金为null

SELECT * FROM emp WHERE comm IS NOT NULL;--奖金不为null

--逻辑运算符：and or not

--AND逻辑与，多个表达式接口同时为true，结果为true，不择false

SELECT * FROM emp WHERE job='SALESMAN' AND sal >=1500;

--逻辑或，多个表大师其中一个表达式为true，结果为true。多个表达式都为false，结果false

SELECT * FROM emp WHERE job='SANLESMAN' OR sal>=1500;

--取反

SELECT * FROM emp WHERE deptno NOT in(10,20);

--运算符优先级，加括号改变优先级

SELECT * FROM emp WHERE(job='SALESMAN' OR job='CLERK')and sal>=1500;

--排序 order by 子句 asc 默认，可以不写。升序 desc 降序

SELECT * FROM emp WHERE deptno=10 ORDER BY asl asc;

多个排序依据

SELECT * FROM emp WHERE deptno = 10 ORDER BY sal asc,empno desc;

--按表达式结果排序

SELECT ename,sal*12 as 年薪 FROM emp ORDER BY sal*12 

--按别名排序

SELECT ename,sal*12 as 年薪 FROM emp ORDER BY 年薪

--按序列号

SELECT empno,ename,sal FROM emp ORDER BY 3,1 desc;

第四章 字符函数 数值函数 日期函数 转换函数 通用函数

函数：单行函数和多行函数

1、单行函数：字符、数值、日期、通用、转换

    字符函数：大小写转换和字符处理函数

    大小写转换：LOWER转小写；UPPER转大写；INITCAP首字母大写

   

     字符处理函数：concat:连接二个值，相当于||

                      substr:取子串

                      length：长度

                      lpad和rpad：左右填充

                      trim:去掉头部和尾部指定字符

                      replace:替换

            instr:返回子串出现的位置

2、数值函数

   ROUND:四舍五入

   TRUNC：截取

   MOD：取余

 

3、日期函数

   日期可以参与数学运算

   RR与YY日期格式

   MONTHS_BETWENNS:两个日期间隔的月数

   ADD_MONTHS：在指定日期上增加月数

   NEXT_DAY:指定日期的下个星期一是什么日子

   LAST_DAY:指定日期所在月的最后一天

   ROUND:日期四舍五入，以15为参考准则

   TRUNC:截取

   EXTRACT：从指定日期取YEAR,MONTH,DAY

   

  

 4、转换函数

     to_char():将日期或者数字转换成字符串

      to_date()：将字符串转换成日期

      to_number()  ：将字符串转换成数值

 

5、通用函数

   （1）null值处理相关的函数，NVL,NVL2，NULLIF,COALESCE

(2)case,decode

 

SELECT ename,empno,sal

FROM emp

WHERE deptno=10;

--简单操作运算符< > >= <= <>

--特殊的运算符between and ,in ,like ,is null

--AND OR NOT

--运算符的优先级

 

--字符函数，大小写转换 ；LOWER转小写；UPPER转大写；INITCAP首字母大写

SELECT LOWER(ename)

FROM emp;

 

SELECT *

FROM emp

WHERE ename=UPPER('smith')

 

--dual一张虚表

SELECT INITCAP('oracle sql')

FROM dual

 

--字符处理函数:

SELECT concat(ename,job),substr(ename,2,2),length(ename)

FROM emp;

 

 

 

--字符处理函数：LPAD(ename,10,'A')左填充，RPAD(ename,10,'*')右填充，

SELECT ename,INSTR(ename,'A',1),RPAD(ename,10,'*'),TRIM('H' from ename  

),REPLACE(ename,'A','AAAA')

FROM emp;

 

 

 

 --数值函数

 SELECT ROUND(49.536,2),ROUND(49.536,-1),TRUNC(49.536,2),TRUNC 

(49.536,-1),MOD(100,33)

 FROM dual;

 

 

 

--日期运算

SELECT (SYSDATE-hiredate)/7 as 工作时间

FROM emp;

 

--员工工作的月数

SELECT MONTHS_BETWEEN(SYSDATE,hiredate) as 月数

FROM emp;

 

--试用期三个月，正式入职日期

SELECT hiredate as  入职日期,ADD_MONTHS(hiredate,3) as  正式入职日期

FROM emp;

 

--指定日期的下个星期一是什么日子，指定日期所在月的最后一天

SELECT NEXT_DAY('11-1月-17','星期一') as 下个星期一,LAST_DAY('11-1月- 

17') as "指定日期最后一天"

FROM dual;

 

--四舍五入，以15为准，

SELECT hiredate,ROUND(hiredate,'MONTH'),TRUNC 

(hiredate,'MONTH'),EXTRACT(YEAR FROM hiredate)

FROM emp

WHERE SUBSTR(hiredate,-2,2)='81'

 

 

 

 

--to_char()函数，把日期或者数字转换成字符串

SELECT hiredate,to_char(hiredate,'YY-MM-DD')

FROM emp

 

SELECT hiredate,to_char(hiredate,'YYYY-MM-DD HH24:MI:SS')

FROM emp

 

SELECT hiredate,to_char(hiredate,'DD "of" MONTH')

FROM emp

 

SELECT ename,sal,to_char(sal,'$9999.9999')

FROM emp

 

SELECT ename,sal,to_char(sal,'L99,999.9999')

FROM emp

 

--to_number(),把字符串转成数值

SELECT to_number('10000')

FROM dual;

 

--不可以转换

SELECT to_number('AAAA1000')

FROM dual;

 

--to_date()字符转换日期

SELECT to_date('1988-12-12','YYYY-MM-DD')

FROM dual;

 

SELECT ename,deptno,

DECODE(

       deptno,

       10,'技术部',

       20,'销售部',

       30,'后勤部',

       '无'

) deptname

FROM emp;

第五章 等值连接 非等值连接 自连接 外连接

多表连接：

1、笛卡尔积

   表1的所有行与表2的所有行连接，无实际意义，没有写连接条件

 

2、等值连接

    要连接的多张表有共同字段，连接时，共同字段是连接的条件（隐含条件）

   WHERE 连接条件，其他条件用逻辑运算符

 

3、多表连接

   N张表连接，至少需要N-1个连接条件

 

4、非等值连接

   要连接的多张表没有共同字段，连接时，考虑非等值连接的条件

   用WHERE 连接条件，其他条件用逻辑运算符

 

5、外连接

    （+），显示不满足连接条件的记录，用null值显示，左外连接，右外连接

     

6、自连接

   跟多表连接相同，连接的对象是自己

 

   --笛卡尔积，多表连接，

SELECT ename,emp.deptno,dname

FROM emp,dept

 

--等值连接,加其他条件用逻辑运算符连接

SELECT empno,ename,d.deptno,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno AND d.loc='NEW YORK'

 

--非等值连接

SELECT empno,ename,sal,grade

FROM emp e, salgrade s

WHERE e.sal BETWEEN s.losal AND s.hisal;

--外连接

SELECT ename,e.deptno,dname

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

--自连接

SELECT w.ename AS 员工姓名,m.ename AS 领导姓名

FROM emp w,emp m

WHERE w.mgr=m.empno;

--第五章课后作业
--1.显示员工SMITH的姓名，部门名称，直接上级名称
SELECT W.ENAME,DNAME,M.ENAME
FROM SCOTT.EMP W,SCOTT.EMP M,SCOTT.DEPT
WHERE W.MGR=M.EMPNO AND W.DEPTNO=DEPT.DEPTNO AND W.ENAME='SMITH';


SELECT * FROM SCOTT.EMP;
SELECT * FROM SCOTT.DEPT;
SELECT * FROM SCOTT.SALGRADE;


--2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。
SELECT ENAME,DNAME,SAL,GRADE
FROM SCOTT.EMP,SCOTT.DEPT,SCOTT.SALGRADE
WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL BETWEEN LOSAL AND HISAL AND GRADE>4;


--3.显示员工KING和FORD管理的员工姓名及其经理姓名。
SELECT W.ENAME 员工姓名,M.ENAME 经理姓名
FROM SCOTT.EMP W,SCOTT.EMP M
WHERE W.MGR=M.EMPNO AND M.ENAME IN('KING','FORD');


--4.显示员工姓名，参加工作时间，经理名，要求参加时间比经理早。
SELECT W.ENAME 员工,W.HIREDATE 参加工作时间,M.ENAME 经理名
FROM SCOTT.EMP W,SCOTT.EMP M
WHERE W.MGR=M.EMPNO AND W.HIREDATE<M.HIREDATE;

SELECT * FROM SCOTT.EMP;

第六章 五个分组函数 分组子句

1、分组函数（多行函数）
   max,min:最大值最小值，可用于任何数据类型
   avg,sum：平均值与总和，只能用于数值类型
   count：计数，count(*)计算满足条件的记录数
          count(列名)，忽略空值null


2、group by子句
   位置：放在where后面，order by 的前面
   group by可以有多个分组依据
   使用了分组，出现在select后面的字段或者表达式，必须用分组函数或者是包含在group by 子句里面


3、having子句
   限制分组条件，紧跟在group by后面


4、select语句6个子句的书写顺序及执行顺序
   书写顺序：
   SELECT deptno,MAX(sal)
   FROM emp
   WHERE deptno in(10,20,30)
   GROUP BY deptno
   HAVING MAX(sal)>=2900
   ORDER BY deptno desc;


   执行顺序：
　　from -> where ->group by ->having -> select ->order by


5、组函数嵌套，最多只能二层






--max最大值,min最小值，可以用于任何类型数据
SELECT MAX(sal) AS 最高工资,MIN(sal) AS 最低工资
FROM emp;


SELECT MAX(hiredate) AS 最晚入职日期,MIN(hiredate) AS 最早入职日期
FROM emp;


--sum总和，avg平均值,对数值数据有效
SELECT SUM(sal) AS 总和,AVG(sal) 平均值,MAX(sal) 最大值,MIN(sal) 最小值
FROM emp;


--count,计数，*计算满足条件的所有行数
SELECT COUNT(*) AS 总人数
FROM emp
WHERE deptno=30;


--计算非空（不为null ）的个数
SELECT COUNT(comm)
FROM emp;


SELECT COUNT(ename)
FROM emp;


--分组函数里，用distinct去掉重复值
SELECT COUNT(DISTINCT deptno)
FROM emp;


--处理null值
SELECT SUM(NVL(comm,0))
FROM emp;


--group by 分组，位置在where后面
SELECT deptno,AVG(sal),SUM(sal),MAX(sal)
FROM emp
WHERE deptno IN(10,20)
GROUP BY deptno
ORDER BY deptno


--每个部门每个岗位,按多例分组(使用了分组，出现select后面的字段或者表达式，必须用分组函数或者是包含在group by 子句里面)
SELECT deptno,job,AVG(sal),SUM(sal),MAX(sal)
FROM emp
WHERE deptno IN(10,20)
GROUP BY deptno,job
ORDER BY deptno






--having子句，只能用在对group by限制分组条件
SELECT deptno,MAX(sal)
FROM emp
GROUP BY deptno
HAVING MAX(sal)>=2900;


--组函数嵌套
SELECT MAX(AVG(sal))
FROM emp
GROUP BY deptno

第七章 子查询 多行多列子查询

1、子查询
    内部查询，放在括号里的查询。
    子查询可以放在 where ,having,from子句里面


2、子查询的类型
    单行子查询，多行子查询，多列子查询


3、单行子查询：值返回一行一列，使用单行运算符（=,>,<,>=,<=,<>）
   子查询中使用分组函数
   在having在使用子查询   
   


4、多行子查询：使用多行运算符,使用多行运算符（IN,ANY,ALL）
   IN：与之前用法一样
   ANY：有一个满足条件即可
   ALL：每一行都要满足条件




5、多列子查询
   在一个表达式内同时和子查询的多个列进行比较，用IN运算符


6、子查询中的空值


7、在from子句中使用子查询


8、ROWNUM


9、TOP-N


10、分页






--子查询
SELECT *
FROM emp
WHERE sal >= (SELECT sal FROM emp WHERE ename='JONES') AND ename<>'JONES';


7369 7876
--子查询，单行子查询，放在where子句
SELECT ename,job
FROM emp
WHERE job = (SELECT job FROM emp WHERE empno=7369) AND
sal > (SELECT sal FROM emp WHERE empno=7876)


--子查询里使用分组函数
SELECT ename,empno,sal
FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp )


--子查询放在having子句
SELECT deptno,COUNT(empno)
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > (
       SELECT avg(COUNT(empno))
       FROM emp
       GROUP BY deptno              
)


--多行子查询：IN（查询是经理的员工姓名，工资）
SELECT ename,sal
FROM emp
WHERE empno IN (SELECT DISTINCT mgr FROM emp)




--多行子查询：ANY（查询不是10号部门，且工资比10号部门任意员工工资高的员工的姓名，工资，工作岗位）
SELECT ename,sal,job
FROM emp
WHERE deptno<>10 AND sal > ANY (SELECT sal FROM emp WHERE deptno=10)


--多行子查询：ANY（查询不是10号部门，且工资比10号部门任意员工工资低的员工的姓名，工资，工作岗位）
SELECT ename,sal,job
FROM emp
WHERE deptno<>10 AND sal < ANY (SELECT sal FROM emp WHERE deptno=10)


--多行子查询：ALL（查询不是10号部门，且工资比10号部门所有员工工资高的员工的姓名，工资，工作岗位）
SELECT ename,sal,job
FROM emp
WHERE deptno<>10 AND sal > ALL (SELECT sal FROM emp WHERE deptno=10)


--多行子查询：ALL（查询不是10号部门，且工资比10号部门所有员工工资低的员工的姓名，工资，工作岗位）
SELECT ename,sal,job
FROM emp
WHERE deptno<>10 AND sal < ALL (SELECT sal FROM emp WHERE deptno=10)


--多列子查询，同时和子查询的多列进行比较（子查询的结果有多列）
--查询和1981年入职员工的任意一个员工的部门和职位完全相同的员工信息
SELECT ename,empno,sal,job,hiredate
FROM emp
WHERE (deptno,job) IN (SELECT deptno,job
                      FROM emp
                      WHERE to_char(hiredate,'YYYY')='1981'
         ) AND to_char(hiredate,'YYYY')<>'1981'


--查询和1981年入职员工的任意一个员工的部门或者职位相同的员工信息
SELECT ename,empno,sal,job,hiredate
FROM emp
WHERE ((deptno IN(SELECT deptno
                 FROM emp
                 WHERE to_char(hiredate,'YYYY')='1981'))
                 
      OR
      (
      job IN(SELECT job
                 FROM emp
                 WHERE to_char(hiredate,'YYYY')='1981')
      )) AND to_char(hiredate,'YYYY')<>'1981'


--null值出现在子查询结果，导致整个主查询没有返回结果
SELECT ename,sal,empno
FROM emp
WHERE empno NOT IN (SELECT  nvl(mgr,0) FROM emp)


--比自己所在部门的平均工资高的员工信息
SELECT ename,sal,job,a.avgsal,e.deptno
FROM emp e,（SELECT deptno, AVG(sal) AS avgsal
          FROM emp
          GROUP BY deptno
            ）a
 WHERE e.deptno=a.deptno AND e.sal>a.avgsal

第八章 联合运算

1、联合
   联合运算 UNION
   完全联合运算 UNION ALL


2、intersect相交


3、minus相减


--联合查询
SELECT job,sal FROM emp WHERE empno=7839
UNION
SELECT job,sal FROM emp_jobhistory WHERE empno=7839;
--完全联合
SELECT job,sal FROM emp WHERE empno=7839
UNION ALL
SELECT job,sal FROM emp_jobhistory WHERE empno=7839;


--练习第五题
SELECT deptno,to_char(NULL),ename,hiredate FROM emp
UNION
SELECT deptno,lo




--1.用集合运算，列出不包含job为SALESMAN 的部门的部门号。
SELECT DEPTNO FROM EMP
MINUS
SELECT DEPTNO FROM EMP  WHERE JOB='SALESMAN';




--2.写一个联合查询，列出下面的信息：EMP表中所有雇员的名字和部门编号,不管他们是否属于任何部门。
--DEPT表中的所有部门编号和部门名称，不管他们是否有员工。
SELECT ENAME,EMP.DEPTNO,TO_CHAR(NULL) FROM DEPT,EMP WHERE DEPT.DEPTNO=EMP.DEPTNO
UNION
SELECT TO_CHAR(NULL),EMP.DEPTNO,DNAME FROM DEPT,EMP WHERE DEPT.DEPTNO=EMP.DEPTNO;


--3.用集合运算查询出职位为SALESMAN和部门编号为10的人员编号、姓名、职位，不排除重复结果。
SELECT EMPNO,ENAME,JOB FROM EMP WHERE JOB='SALESMAN'
UNION ALL
SELECT EMPNO,ENAME,JOB  FROM EMP WHERE DEPTNO=10;


--4.用集合查询出部门为10和20的所有人员编号、姓名、所在部门名称。
SELECT EMPNO,ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.DEPTNO=10
UNION
SELECT EMPNO,ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.DEPTNO=20;
第九章 相关子查询 exists  not exists
1、嵌套子查询
    子查询是以嵌套的方式写在
父查询的WHERE、HAVING、FROM子句中，所以被
称为嵌套子查询。
2、相关子查询
   当子查询中引用了父查询表中的一个
   列时，Oracle服务器执行相关子查询。


3、相关子查询的执行过程：
–1.取得父查询的候选行；
–2.用候选行被子查询引用列的值执行子查询；
–3.用来自子查询的值确认或取消候选行；
–4.重复步骤1、2、3，直到父查询中无剩余的候选行


4、exists与not exists
– EXISTS 子查询并没有确切记录返回，只判断是否有记录存
在，而且只要找到相关记录，子查询就不需要再执行，然后
再进行下面的操作。这样大大提高了语句的执行效率。


– NOT EXISTS正好相反，判断子查询是否没有返回值。如果没
有返回值，表达式为真，如果找到一条返回值，则为假。


--查询比本部门的平均工资高的员工的编号、姓名、工资
--嵌套子查询
SELECT empno,ename,sal
FROM emp e ,(SELECT deptno,avg(sal) avgsal
 FROM emp
 GROUP BY deptno) d
WHERE e.deptno =d.deptno
AND e.sal >d.avgsal;


--相关子查询
SELECT empno,ename, sal
FROM emp outer
WHERE sal >
            (SELECT AVG(sal)
            FROM emp
            WHERE deptno =
             outer.deptno) 
             
--查询各部门名称，人数。相关子查询写select子句
SELECT dname,(SELECT COUNT(empno)
              FROM emp
              WHERE deptno=d.deptno)
FROM dept d


--查询是经理的员工信息
SELECT * 
FROM emp e
WHERE 0 < (
      SELECT COUNT(empno)
      FROM emp
      WHERE mgr=e.empno
      )


 --查询调过2次岗位的员工信息
 SELECT ename,job
 FROM emp e
 WHERE 2<=(SELECT COUNT(*)
            FROM emp_jobhistory
            WHERE empno=e.empno) 
            
            
               
        
 --exists操作符，没有确切记录返回，只有真和假
 --查询是经理的员工信息
 SELECT ename,sal,job
 FROM emp e
 WHERE EXISTS (
       SELECT '1'
       FROM emp
       WHERE mgr=e.empno
 )      
        
 --not exists。
 --查询不是经理的员工信息
  SELECT ename,sal,job
 FROM emp e
 WHERE NOT EXISTS (
       SELECT '1'
       FROM emp
       WHERE mgr=e.empno
 )        
        
      
      


--练习1，第一题
SELECT ename,job
FROM emp a
WHERE a.sal>(SELECT AVG(sal)
              FROM emp b
              WHERE a.job=b.job
              GROUP BY job )
--练习1，第二题
SELECT empno,ename,sal
FROM emp a
WHERE sal=(SELECT MIN(sal)
           FROM emp b
           WHERE a.deptno=b.deptno
           GROUP BY deptno
 
         )
--练习2，第一题      
SELECT EMPNO,ENAME,(SELECT DNAME FROM DEPT B WHERE A.DEPTNO=B.DEPTNO )
FROM EMP A
--练习2，第二题          
 SELECT * 
FROM emp e
WHERE 0 < (
      SELECT COUNT(empno)
      FROM emp
      WHERE mgr=e.empno
      )       
        


--练习2，第四题
--参考代码1
select empno, ename,deptno,sal
from emp e 
where e.empno in(
              select b.empno 
              from (
                   select * 
                   from emp 
                   order by sal
              ) b 
              where e.deptno=b.deptno and rownum<3
              ) 
order by deptno


--参考代码2
SELECT *
FROM emp this
WHERE (
         SELECT COUNT(emp.empno)
         FROM emp
         WHERE emp.deptno = this.deptno AND this.sal>emp.sal
      )<=1


--练习3第一题
SELECT dname
FROM dept d
WHERE EXISTS (
      SELECT '1'
      FROM emp
      WHERE deptno=d.deptno
)
--练习3第二题
SELECT dname
FROM dept d
WHERE NOT EXISTS (SELECT '1'
              FROM emp
              WHERE deptno=d.deptno)
第十一章 插入数据 修改数据 删除数据
1、插入语句 insert into 表名（列名）values （列的值）


2、修改数据update语句


3、删除 语句delete


   SELECT * FROM emp


--插入数据,insert语句
--写出要给值的列名，
INSERT INTO dept(deptno,dname,loc) VALUES(50,'HHHD','GUANGZHOU');


--省略列名，默认所有列都要有值
INSERT INTO dept VALUES(60,'EEEEE','HUNAN');


--某列可以接受null，这样的可以省略，默认 null
INSERT INTO dept(deptno,dname) VALUES(70,'WWWW');


INSERT INTO dept(deptno,dname) VALUES(11,'&WWW&');


--插入日期型数据
INSERT INTO emp VALUES(7777,'JERRY','CLERK',7499,SYSDATE,5500,100,20);
INSERT INTO emp VALUES(7778,'TOM','CLERK',7499,'01-1月-2017',5800,100,30);
INSERT INTO emp VALUES(7779,'TOM','CLERK',7499,to_date('2018-1-21','YYYY-MM-DD'),5800,100,30);


--创建数据表，根据已经某张表创建，结构和某表一样
CREATE TABLE manager AS
SELECT * FROM emp WHERE deptno=10;
--一次插入多条记录，用子查询
INSERT INTO manager 
SELECT * 
FROM emp
WHERE deptno=20


--修改指定条件的记录，修改一行
UPDATE emp 
SET ename='TONNY'
WHERE empno=7779


--不加条件，修改所有行
UPDATE emp
SET deptno=10


--修改多行
UPDATE emp
SET sal=sal+1000
WHERE sal=1250


--修改多列
UPDATE emp
SET sal=sal+1000,comm=comm+100
WHERE empno IN(7777,7778,7779)


--利用自查询修改列的值
UPDATE emp
SET sal=sal+(SELECT AVG(sal) FROM emp)
WHERE mgr=7499


--利用相关子查询来修改数据
ALTER TABLE emp 
ADD (dname VARCHAR(14));


UPDATE emp e
SET dname=(SELECT dname
            FROM dept d
            WHERE e.deptno=d.deptno)


-- 删除选中记录
   DELETE FROM emp
   WHERE job='CLERK'
--删除所有记录
DELETE FROM emp
--基于另一张表删除记录
DELETE FROM  emp
WHERE deptno=(SELECT deptno
               FROM dept
               WHERE dname='SALES')
--删除时完整性错误
DELETE FROM dept
WHERE deptno=10
--相关删除:删除没有员工的部门
DELETE FROM dept d
WHERE NOT EXISTS(
      SELECT ename
      FROM emp
      WHERE deptno=d.deptno
)

第十三章 创建表 修改表 删除修改重命名截断表

1、创建表
   create table  语句


2、常用数据类型


3、修改表：alter table 
    增加列：add
    修改列：modify
    删除列：drop


4、删除表
   drop table 


5、重命名表
   rename 原名 to 新名
 
6、截断表：删除所有行，空间释放，不能rollback
    truncate table 表名


7、数据字典
   desc 表名；
   select 表名 from user_tables;


--创建数据表
CREATE TABLE course
(
       course_no NUMBER(4),
       course_name VARCHAR(20),
       tearcher VARCHAR(12),
       credit NUMBER(2) DEFAULT 4        --默认值
)


--插入二条记录，注意默认值
INSERT INTO course(course_no,course_name,tearcher) VALUES(1001,'ORACLE','mi')
INSERT INTO course VALUES(1002,'JAVAEE','chen',5)


SELECT * FROM course1;


--修改表：增加列
ALTER TABLE  course
ADD (course_loc VARCHAR(10));
--修改已存在的列
ALTER TABLE course
MODIFY(course_name VARCHAR(15));
--删除存在的列
ALTER TABLE course
DROP (course_loc);


ALTER TABLE course
DROP (course_loc,credit);     
--修改列名
ALTER TABLE course
RENAME COLUMN tearcher TO tea_name                 
      
--删除表
DROP TABLE course;
--表重命名
RENAME course TO course1  
--截断表，删除所有记录，不能rollback
TRUNCATE TABLE course1; 
--数据字典
DESC course1;
SELECT course1 FROM user_tables;
             

第十四章 五类约束 约束追加

1、五种约束
  约束：not null/unique/primary key/foreign key/check
  约束:列级约束/表级约束


2、not null 
   非空约束，只能定义列级


3、unique 唯一约束
   


4、主键约束primary key 
   唯一，不能为空，一张表只能一个主键
  用来区分表里面的每一条记录，唯一性标识


5、外键约束：foreign key
   外键列的取值必须在引用列的取值范围内或者为空
   被引用的列必须是所在表的主键或者唯一键


   外键,定义列级的外键约束不要写 FOREIGN KEY 
  外键,定义表级的外键约束一定要写 FOREIGN KEY 


6、检查约束:check
   限制某列数据的必须在规定的取值范围内：
  比如：sal>0


7、追加约束
    （1）增加约束：ADD
     (2)增加not null约束用modify子句
    （3）删除约束 drop
         根据约束名称删除
         根据约束类型删除
    （4）删除外键：原则上不能被删除，可以加上cascade 级联删除


8、禁用/启用约束
   --禁用约束
ALTER TABLE emp1 DISABLE CONSTRAINT emp1_empno_pk;
--启用约束
ALTER TABLE emp1 ENABLE CONSTRAINT emp1_empno_pk;
  


--约束：not null/unique/primary key/foreign key/check
--约束:列级约束/表级约束
CREATE TABLE emp1(
empno NUMBER(4)CONSTRAINT emp1_empno_pk PRIMARY KEY  ,  --主键，唯一，不能为空，一张表只能一个主键
ename VARCHAR2(10)CONSTRAINT emp_ename_uk UNIQUE ,--列级约束,唯一约束
job VARCHAR2(9)CONSTRAINT emp_job_null NOT NULL,  --not null只能定义列级约束
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2) CONSTRAINT emp1_sal_ck CHECK(sal>0) ,
comm NUMBER(7,2),
deptno NUMBER(2) ,--CONSTRAINT emp1_deptno_fk  REFERENCES dept(deptno) --外键,定义列级的外键约束不要写 FOREIGN KEY 
--CONSTRAINT emp_ename_uk UNIQUE(ename)  --表级约束
--CONSTRAINT emp_ename_uk UNIQUE(ename,mgr)  --多列组合的约束只能定义表级
--CONSTRAINT emp_ename_deptno_pk PRIMARY KEY(empno,deptno)  --多列组合作为主键，只能定义表级
CONSTRAINT emp1_deptno_fk  FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE
);


INSERT INTO emp1 VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,100,10);
INSERT INTO emp1 VALUES (7902,'FORD','ANALYST',7782,to_date('23-1-1982','dd-mm-yyyy'),1200,200,10);
INSERT INTO emp1 VALUES (7945,'FORD','ANALYST',7782,to_date('23-1-1982','dd-mm-yyyy'),-1300,NULL,10);


DROP TABLE emp1;




--增加约束
ALTER TABLE emp1
 ADD CONSTRAINT emp1_mgr_fk FOREIGN KEY(mgr) REFERENCES emp1(empno)
--增加一个not null约束，用modify
ALTER TABLE emp1
MODIFY(comm NUMBER(7,2) NOT NULL )
--删除约束，根据约束名称删除约束
ALTER TABLE emp1
DROP CONSTRAINT emp1_mgr_fk
--删除约束，根据约束类型删除约束
ALTER TABLE emp1
DROP UNIQUE(ename);
--删除外键约束，原则上不能被删除，加上cascade才可以，表示外键一起删除
ALTER TABLE dept
DROP PRIMARY KEY CASCADE;


--禁用约束
ALTER TABLE emp1 DISABLE CONSTRAINT emp1_empno_pk;
--启用约束
ALTER TABLE emp1 ENABLE CONSTRAINT emp1_empno_pk;


DROP TABLE emp;

第十五章的视图

视图：
是 一 个命名的查询 ， 用于改变基表数据的显示 ， 通过视图能简
化查询；访问方式与表相同
说白了就是数据集合


创建试图语法：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW viewname
[(column[, column]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraintname]]
[WITH READ ONLY]


OR REPLACE：如果所创建的视图已经存在，该选项表示修改原视图的定义；
 FORCE：不管视图所基于的基表是否存在，都会创建该视图；
 NOFORCE：只有视图所基于的基表都存在，才会创建该视图；
 viewname ：视图的名称；
 column ：列名，列名的数量必须和视图所对应查询语句的列数量相等；
 subquery：一条完整的SELECT语句；
 WITH CHECK OPTION：一个约束条件，通过视图所插入或修改的数据行必须
满足视图所定义的查询； constraintname：约束名；


WITH READ ONLY：确保在该视图上不能进行任何DML操作；


创建一个视图
SQL> CREATE VIEW  empvu10
2 AS SELECT empno, ename, job
3 FROM emp
4 WHERE deptno = 10;


您可以通过视图 删除基表中数据，只要视图中不出现
以下情况：
– Group 函数；
– GROUP BY 子句；
– DISTINCT 关键字；


删除视图
DROP VIEW view（视图名）;

第十六章，序列，伪列，索引，同义词

CREATE SEQUENCE test_seq
START WITH 10 -- 序列从10 开始
INCREMENT BY 2 -- 序列每次增加2
MAXVALUE 100 -- 序列最大值100
MINVALUE 9 -- 序列最小值9
环 环  增 到 回 到  CYCLE -- 序列循环, 每次 增加2, 一 直 到100 后 回到9 从新开始
CACHE 10 ； --
重点要记住




 CURRVAL：表示序列返回的当前值；
– NEXTVAL：表示序列返回的下一个值；
– CURRVAL在被引用之前，必须先使用NEXTVAL来产生一个序
列值；


序列属性
• NEXTVAL和CURRVAL伪列
– 您可以在下列情况使用NEXTVAL 和 CURRVAL：
• SELECT 语句的SELECT列表中，但不包括子查询中的
SELECT语句
• INSERT 语句中的子查询SELECT列表中
• INSERT 语句的VALUES子句中
• UPDATE 语句的SET子句中
– 下列情况不能使用 NEXTVAL 和 CURRVAL：
• 在视图的SELECT列表中
• 包含DISTINCT关键字的 SELECT语句中
• 含有 GROUP BY, HAVING, ORDER BY子句的SELECT语句
中
• SELECT, DELETE, UPDATE 语句的子查询中
• 含有 DEFAULT表达式的 CREATE TABLE、 ALTER TABLE
语句中




序列的使用：
INSERT INTO student
VALUES (student_seq.NEXTVAL 'Scott' 'Computer Science' 11);
上面红色的部分就是序列名


查看student_seq序列当前值：
SELECT student_seq.CURRVAL FROM dual;






修改序列的语法如下：
ALTER SEUENCE schema.seuencename Q [ ] q
[INCREMENT BY n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
– 修改序列的语法没有START WITH子句。


删除序列的语法如下：
DROP SEQUENCE [schema.]sequencename ；




ROWID：
– 是一个伪列，系统自动产生。
– ROWID能唯一标示每一条数据库行记录的物理地址，通过
ROWID 能快速定位到一条行记录。




例: 使用ROWID 修改数据
UPDATE emp
=  SET ename = ename || '*'
WHERE ROWID = 'AAAMg6AAFAAAABUAAA';




索引( INDEX ):
– 是对数据库表中一个或多个列的值进行排序的一种数据库
对象。
– 在数据库中,通过索引可以加速对表的查询速度;


创建索引语法：
CREATE INDEX indexname
ON table (column[ column] );


在emp表的deptno和job的组合上创建索引
CREATE INDEX idx_emp_deptnojob
ON emp (deptno,job);




创建测试索引环境表
CREATE TABLE e1 AS SELECT * FROM emp;
INSERT INTO e1 SELECT * FROM e1;-- 多次运行
的 UPDATE e1 SET empno=ROWNUM; -- 更新所有记录的 的empno ，
-- 以使其数值唯一
commit;-- 提交


索引缺点
• 占用空间；
• 降低DML的操作速度；


删除索引的语法
DROP INDEX index;




同义词


创建同义词的语法如下：
CREATE [PUBLIC] SYNONYM  同义词
FOR [schema.] 对象名；


 同义词两种类型：
• 私有（PRIVATE）。是在指定的方案中创建的，并且只允
许拥有它的方案访问
• 公有（PUBLIC）。由PUBLIC方案所拥有，所有的数据库
方案都可以引用他们。

第17章 创建，授予，角色

创建用户
– 语法
CREATE USER user
IDENTIFIED BY password 
[default tablespace  默认表空间名
temp tablespace 名 临时表空间名 quota 小 配额大小 on  表空间名]
– deafult tablespace:用户的默认表空间;
– temporary tablespace: 用户的临时表空间;
– quota on :表示允许该用户在表空间中使用的空间大小,
可以设置多个不同的表空间;
– 执行该语句的用户需要有“创建用户”的权限，一般为系
统的DBA用户。




以SYSTEM用户登录，创建test用户
CREATE USER test IDENTIFIED BY test;
授予登陆权限
GRANT CREATE SESSION TO test;


Conn system/oracle;
例 用户身份执行建表操作
GRANT CREATE TABLE TO test;




修改配额
ALTER USER  用户名
QUOTA 10m ON  表空间名;
– 例：以SYSTEM用户身份执行，给test分配USERS表空间的10M
配额
ALTER USER test
QUOTA 10m ON users;---表空间名是什么；和表名有什么关系？


修改密码
ALTER USER user IDENTIFIED BY  新密码




状态管理语句：
ALTER USER user PASSWORD EXPIRE;-- 密码过期
ALTER USER user ACCOUNT LOCK[UNLOCK];-- 帐户锁定/


系统特权
– SYSOPER：启动停止数据库，恢复数据库等；
– SYSDBA：所有SYSOPER功能的管理权限；创建数据库等权限。


授予和回收权限
GRANT sys_priv_list TO user_list [WITH ADMIN OPTION]
REVOKE sys_priv_list FROM user_list ;


 注意：使用 WITH ADMIN OPTION 选项授予的权限，在回收时候
的回收策略如下：
• 如果A授予权限给B，B又把该权限赋予给C ，如果此时A把权
从  么  即  限从B处收回，那么B给予出去的权限是 继续保留，即C继续
拥有该权限。