学习Oracle重要笔记
来源：互联网 发布：淘宝假冒品牌退款处理 编辑：程序博客网 时间：2024/06/03 12:04
Oracle重要笔记
第一节：查询
========================================
------------------------
dual是指只输出一行结果
------------------------
命令：SQL> select * from dual;
执行结果：
D
------------ //dual指输出一行
X
============================================
------------------------------------------------
在语句的后面添加语句相当于给之前的语句取别名
------------------------------------------------
命令：SQL> select ename,sal * 12 anuual_sal from emp;
执行结果：
ENAME ANUUAL_SAL
--------- ------------
SMITH 9600
ALLEN 19200 //意思是为sal * 12取个别名anuual_sal且全部自动转换成大写
WARD 15000
-----
---------------------------------------------------
//(若加上双引号，则为字符串，则显示小写，例子如下）
----------------------------------------------------
命令：SQL> select ename,sal * 12 “anuual_sal” from emp;
执行结果：
ENAME anuual_sal
--------- ------------
SMITH 9600
ALLEN 19200 //意思是为sal * 12取个别名anuual_sal
WARD 15000
-----
========================================
----------------
切记：NULL != 0
----------------
命令：SQL> select ename,sal * 12 + comm from emp;
执行结果：
ENAME SAL * 12 + COMM
------ --------------
SMITH
ALLEN 19000
WARD 15000 //NULL值不等0，空值（NULL）与任何一个数相加都为NULL值
JONES
MARTIN 16400
BLAKE
========================================
------------------------------------------------------
符号||相当于JAVA语言里的“+”号，把两个字符串连接在一起
------------------------------------------------------
命令：SQL> select ename || sal from emp;
执行结果：
ENAME||SAL
-----------
SMITH800
ALLEN1600
WARD1250 //符号||相当于JAVA语言里的“+”号
JONES2975 //其结果相当于两个字符串相加
========================================
---------------------------------------
SQL语句里用单引号引起来表示字符串的方法
---------------------------------------
命令：SQL> select ename || 'asdengejangehang' from emp;
执行结果：
ENAME||'ASDENGEJANGEHANG'
-------------------------
SMITHasdengejangehang
ALLENasdengejangehang
WARDasdengejangehang
JONESasdengejangehang //字符串连接方法用' '
MARTINasdengejangehang
=========================================
--------------------------------------------------------
当字符串里有单引号的时候，要用两个单引号来表示一个单引号
--------------------------------------------------------
命令：SQL> select ename || 'shanghai''beijing' from emp;
执行结果： //注意，是两个连续的单引号，而不是一个双引号
ENAME||'SHANGHAI''BEIJING'
--------------------------
SMITHshanghai'beijing
ALLENshanghai'beijing
WARDshanghai'beijing
JONESshanghai'beijing
----
-------------------------------------
若上条命令只用一个单引号，则会报错
-------------------------------------
命令：SQL> select ename || 'shanghai'beijing' from emp;
执行结果：
ERROR:
------引号内的字符串没有正确结束
========================================
-----------------------------------------
distinct表示删除相同的记录，如编号等等
-----------------------------------------
命令：SQL> select distinct deptno from emp;
执行结果：
DEPTNO
---------- //这条语句执行的结果是删除编号相同的
30 //默认按降序（desc）排列
20
10
命令：SQL> select distinct deptno,job from emp;
执行结果：
DEPTNO JOB
---------- --------- //这条语句执行的结果是把编号和工作都相同的记录删掉
20 CLERK //把编号和工作当一个组合
30 SALESMAN
20 MANAGER
30 CLERK
10 PRESIDENT
30 MANAGER
10 CLERK
10 MANAGER
20 ANALYST
---------------------
------------------------------------------------------------------------------------
注意：修饰两个或者两个字段以上的时候要用英文的逗号（,）隔开，若用句号（.），则会出错
-------------------------------------------------------------------------------------
命令：SQL> select distinct deptno.job from emp;
执行结果：
select distinct deptno.job from emp
*
第 1 行出现错误:
ORA-00904: "DEPTNO"."JOB": 标识符无效
==========================================
------------------
where是过滤条件
-------------------
命令：SQL> select * from emp where deptno = 10;
-----//这句语句的意思是把编号等于10的记录取出来-----
执行结果：
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
7839 KING PRESIDENT 17-11月-81 5000
10
7934 MILLER CLERK 7782 23-1月 -82 1300
10
----------------------------------------------------------------
注意：<>符号表示不等于，这与其他语言里用！=表示不等于是不一样的
----------------------------------------------------------------
===================================================================
-------------------------------------------
表示从多少到多少用between.....and.....表示
-------------------------------------------
命令：SQL> select ename,sal from emp where sal between 800 and 1500;
执行结果：
ENAME SAL
---------- ----------
SMITH 800
WARD 1250 //表示薪水从800到1500，其中包含了边界800和1500
MARTIN 1250
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
--------------------------------------------------------
此例还有另外一种写法，结果一样（用>= 和 <=代替between）
--------------------------------------------------------
命令：SQL> select ename,sal from emp where sal >= 800 and <=1500;
执行结果：
ENAME SAL
---------- ----------
SMITH 800
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
========================================
------------------
NULL（空值）的处理
------------------
命令：SQL> select ename,sal,comm from emp where comm is NULL;
执行结果：
ENAME SAL COMM
---------- ---------- ----------
SMITH 800
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
已选择10行。
----------------------------------------------
注意：以下这种表达是错误的，非空值不能这么处理
----------------------------------------------
命令：SQL> select ename,sal,comm from emp where comm = NULL;
执行结果：
未选定行
===========================================
---------------------
非NULL(空值)的处理
----------------------
命令：SQL> select ename,sal,comm from emp where comm is not NULL;
执行结果：
ENAME SAL COMM
---------- ---------- ----------
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
TURNER 1500 0
==========================================
---------------------
用in表示在落在里面的
---------------------
命令：SQL> select ename,sal,comm from emp where sal in (800,1600,2000,3000);
执行结果
ENAME SAL COMM //表示取出薪水在800,1600,2000,3000的记录
---------- ---------- ----------
SMITH 800
ALLEN 1600 300
SCOTT 3000
FORD 3000
-------------
命令：SQL> select ename,sal,comm from emp where ename in ('SMITH','KING','ABC');
执行结果：
ENAME SAL COMM //表示取出名字为SMITH,KING,ABC的记录
---------- ---------- ----------
SMITH 800
KING 5000
==============================================
----------------------------------------
日期的处理格式（记得要加单引号引起来）
----------------------------------------
命令：SQL> select ename,sal,hiredate from emp where hiredate > '20-2月-81';
执行结果：
ENAME SAL HIREDATE
---------- ---------- --------------
WARD 1250 22-2月 -81
JONES 2975 02-4月 -81
MARTIN 1250 28-9月 -81
BLAKE 2850 01-5月 -81
CLARK 2450 09-6月 -81
SCOTT 3000 19-4月 -87
KING 5000 17-11月-81
TURNER 1500 08-9月 -81
ADAMS 1100 23-5月 -87
JAMES 950 03-12月-81
FORD 3000 03-12月-81
ENAME SAL HIREDATE
---------- ---------- --------------
MILLER 1300 23-1月 -82
-----------------------------------------------
若日期格式的处理忘记了，可以这么处理，如下例子
------------------------------------------------
命令：SQL> select sysdate from emp;
执行结果：
SYSDATE
--------------
09-4月 -13
09-4月 -13
09-4月 -13
09-4月 -13
09-4月 -13
09-4月 -13
09-4月 -13
09-4月 -13
09-4月 -13
09-4月 -13
09-4月 -13
SYSDATE
--------------
09-4月 -13
09-4月 -13
09-4月 -13
------------------------------------------------------------
切记：hiredate表示过去记录的时间，sysdate表示当前记录的时间
------------------------------------------------------------
====================================
---------------------------
灵活使用not，or,and
---------------------------
=====================================
-----------------
模糊查询用（%代表一个或者多个字母）
-----------------
命令：SQL> select ename,JOB from emp where ename like '%AL%';
执行结果： //表示名字里含有字母AL的记录
ENAME JOB
---------- ---------
ALLEN SALESMAN
----------------
命令：SQL> select ename,JOB from emp where ename like '%L%';
执行结果： //表示名字里含有字母L的记录
ENAME JOB
---------- ---------
ALLEN SALESMAN
BLAKE MANAGER
CLARK MANAGER
MILLER CLERK
============================================
-----------------------
一个下横线代表一个字母
-----------------------
命令：SQL> select ename,JOB from emp where ename like '_L%';
执行结果：
ENAME JOB //表示第二个字母是L的记录
---------- ---------
ALLEN SALESMAN
BLAKE MANAGER
CLARK MANAGER
===========================================
-----------------
转义字符的定义
-----------------
命令：SQL> select ename from emp where ename like '%$%%' escape '$';
执行结果： //自己定义一个转义字符'$'
未选定行
=========================================
第二节：排序
=========================================
-------------------------------
降序：desc
升序：asc
（默认是升序，所以不写asc也行）
--------------------------------
命令：SQL> select * from dept order by deptno desc;
执行结果：
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
-------------
命令：SQL> select * from dept order by deptno asc;
执行结果： （不写asc也行）
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
==================================================
--------------------------------------------------
升序（asc）降序（desc）也可以结合过滤条件（where）
--------------------------------------------------
命令：SQL> select ename,deptno from emp where sal > 1000 order by deptno;
执行结果： //薪水大于1000且按编号升序排列记录
ENAME DEPTNO
---------- ----------
CLARK 10
KING 10
MILLER 10
FORD 20
JONES 20
SCOTT 20
ADAMS 20
ALLEN 30
WARD 30
TURNER 30
BLAKE 30
ENAME DEPTNO
---------- ----------
MARTIN 30
===========================================
--------------------------------
按两个字段进行排序
-----------------------------
命令：SQL> select ename,deptno from emp where sal > 1000 order by deptno asc,ename desc;
执行结果：
ENAME DEPTNO //先按编号升序排序，排序号以后再按名字降序排序
---------- ----------
MILLER 10
KING 10
CLARK 10
SCOTT 20
JONES 20
FORD 20
ADAMS 20
WARD 30
TURNER 30
MARTIN 30
BLAKE 30
ENAME DEPTNO
---------- ----------
ALLEN 30
====================================================
第三节：各种格式的转换
=====================
----------------------------
lower转换成小写字母输出记录
----------------------------
命令：SQL> select lower(ename) from emp;
执行结果：
LOWER(ENAM
----------
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
LOWER(ENAM
----------
james
ford
miller
==========================================================
------------------------------------------------------------------
先转换成小写字母（临时的），再把第2个字母是a的记录按升序排序输出
-------------------------------------------------------------------
命令：SQL> select ename from emp where lower(ename) like '_a%';
执行结果：
ENAME
----------
WARD
MARTIN
JAMES
=======================================================
-------------------
substr截取字符串
------------------
命令：SQL> select substr(ename,1,2) from emp;
执行结果： //表示从第一个字符开始截取2个字符
SUBS
----
SM
AL
WA
JO
MA
BL
CL
SC
KI
TU
AD
SUBS
----
JA
FO
MI
===========================================
---------------------------
把ASCII码转换成字符chr输出
---------------------------
命令：SQL> select chr(43) from dual;
执行结果： //把ASCII码43转换成字符输出记录
C
----
+
----------------------------
把字符chr转换成ASCII输出
----------------------------
命令：SQL> select ASCII('K') from dual;
执行结果： //把字符K转换成ASCII输出记录
ASCII('K')
----------
75
========================================
----------------
round四舍五入
---------------
命令：SQL> select round(6329.445) from dual;
执行结果：
ROUND(6329.445)
---------------
6329
---------------------------------------------
round还可以指定第二位参数，表示四舍五入到几位
---------------------------------------------
命令：SQL> select round(6329.445,2) from dual;
执行结果： //表示四舍五入到小数点后面2位
ROUND(6329.445,2)
----------------- //明显与上面那个结果不一样
6329.45
===========================================
命令：SQL> select round(6329.445,-1) from dual;
执行结果： //表示四舍五入到个位
ROUND(6329.445,-1)
------------------
6330 //因为默认是精确到0位，所以-1即是精确到个位
----------------------------------------
命令：SQL> select round(6329.445,-2) from dual;
执行结果： //表示四舍五入到十位
ROUND(6329.445,-2)
------------------
6300 //因为默认是精确到0位，所以-2即是精确到十位
=====================================================
-----------------------------------------------
===========================================================================================
把数字或者日期转化成相关的字符串，并且每一种都有相关的格式控制符，即to_char的使用（重点一）
============================================================================================
命令：SQL> select to_char(sal,'$999,999.0000') from emp;
执行结果： //注意，这里必须用9，若用0，结果则不一样
TO_CHAR(SAL,'$
--------------
$800.0000
$1,600.0000
$1,250.0000
$2,975.0000
-------------------------------------------------------------------
若把上例的$改成L（大小写都可以），则会转化成本地货币符号，即￥符号
-------------------------------------------------------------------
命令：SQL> select to_char(sal,'L999,999.0000') from emp;
执行结果：
TO_CHAR(SAL,'L999,999.0
-----------------------
￥800.0000
￥1,600.0000
￥1,250.0000
￥2,975.0000
-----------------------------------------
//注意，这里必须用9，若用0，结果则不一样
-----------------------------------------
用0的结果如下例：
--------------------
命令：SQL> select to_char(sal,'L000,000.0000') from emp;
执行结果：
TO_CHAR(SAL,'L000,000.0
-----------------------
￥000,800.0000
￥001,600.0000
￥001,250.0000
===================================================
==================================================================
把相关的特定的字符串转换成时间或者日期，即to_date的使用（重点二）
==================================================================
-----------------------------------------------------------------
（hiredate表示过去记录的时间，sysdate表示当前记录的时间）
--------------------------------------------------------
命令：SQL> select hiredate from emp;
执行结果：
HIREDATE
--------------
17-12月-80
20-2月 -81
22-2月 -81
02-4月 -81
------------------------------
常见的日期时间格式
-----------------------------
hiredate表示过去记录的时间
---------------------------
命令：SQL> select to_char(hiredate ,'YYYY-MM-DD HH:MI:SS') from emp;
执行结果：
TO_CHAR(HIREDATE,'Y
-------------------
1980-12-17 12:00:00
1981-02-20 12:00:00
1981-02-22 12:00:00
1981-04-02 12:00:00
============================
sysdate表示当前记录的时间
============================
----------------------
12进制时间的表示
------------------
SQL> select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from emp;
TO_CHAR(SYSDATE,'YY
-------------------
2013-04-09 11:54:34
2013-04-09 11:54:34
2013-04-09 11:54:34
2013-04-09 11:54:34
-------------------------------------------------
24进制时间的表示（HH后面加上24，表示按24小时制）
--------------------------------------------------
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from emp;
TO_CHAR(SYSDATE,'YY
-------------------
2013-04-09 23:55:05
2013-04-09 23:55:05
2013-04-09 23:55:05
===========================================
日期格式的转换，to_date的使用
===========================================
------------
旧的表达式
------------
命令：SQL> select ename,hiredate from emp where hiredate > '12-3月-1981' order by hire
date desc;
执行结果：
ENAME HIREDATE
---------- --------------
ADAMS 23-5月 -87
SCOTT 19-4月 -87
MILLER 23-1月 -82
---------------------------
新的表达式（要使用to_date）
---------------------------
命令：SQL> select ename,hiredate from emp where hiredate > to_date('1981-03-22 12:23:4
3','YYYY-MM-DD HH24-MI-SS') order by hiredate desc;
执行结果：
ENAME HIREDATE
---------- --------------
ADAMS 23-5月 -87
SCOTT 19-4月 -87
MILLER 23-1月 -82
====================================================================
//虽然执行结果一样，但是新的表达式比旧的表达式好，不用切换成拼音//
====================================================================
===============================================================
把特定格式的数字转化成为字符串类型，即to_number的使用（重点三）
===============================================================
命令：SQL> select sal from emp where sal > to_number('$1,234.00','$9,999.99');
执行结果：
SAL //注意：1的后面以及后面的9后面都是用英文的逗号隔开
-------------------
1600
1250
2975
1250
2850
----------------------------------------------------------------------
注意：1的后面以及后面的9后面都是用英文的逗号(,)隔开，否则出错，如下例
----------------------------------------------------------------------
命令：SQL> select sal from emp where sal > to_number('$1.234.22','$9.999.99');
执行结果：
select sal from emp where sal > to_number('$1.234.22','$9.999.99')
*
第 1 行出现错误:
ORA-01481: 无效的数字格式模型
==============================================================================
--------------------------------------------
NULL的避免以及使用，即nvl(comm,0)函数的使用
--------------------------------------------
----------==================--------------
使用了nvl(comm,0)函数的执行结果
------------==================-------------
命令；SQL> select ename,sal * 12 + nvl(comm,0) anuual_sal from emp;
执行结果：
ENAME ANUUAL_SAL //nvl(comm,0)的意思是，若为NULL值，则用0代替
---------- ----------
SMITH 9600
ALLEN 19500
WARD 15500
JONES 35700
MARTIN 16400
BLAKE 34200
------------==================-------
未使用了nvl(comm,0)函数
-----------==================--------
命令：SQL> select ename,sal * 12 + comm anuual_sal from emp;
执行结果：
ENAME ANUUAL_SAL
---------- ----------
SMITH
ALLEN 19500
WARD 15500
JONES
MARTIN 16400
BLAKE
CLARK
SCOTT
KING
------------------------------------------------------------------------
注意：结果表明，使用了nvl(comm,0)和未使用nvl(comm,0)函数的结果大不相同
------------------------------------------------------------------------
============================================================================
第四节：分组函数
-----------------------------------
一组或者多条记录，只会产生一个输出
------------------------------------
----------------------
单条的select语句的学习
----------------------
=========================================
------------------------------
max,min,avg，sum,count的使用
------------------------------
命令：SQL> select max(sal) from emp;
执行结果： //输出薪水最高的记录
MAX(SAL)
----------------
5000
========================================
命令：SQL> select min(sal) from emp;
执行结果： //输出薪水最低的记录
MIN(SAL)
-------------------
800
=========================================
命令：SQL> select avg(sal) from emp;
执行结果： //输出薪水平均值的记录
AVG(SAL)
----------------
2073.21429
=========================================
----------------------
精确到小数点后面2位
----------------------
命令：SQL> select to_char(avg(sal),'9999.99') from emp;
执行结果： //精确到小数点后面2位数
TO_CHAR(
--------
2073.21
------------------------------------------------
命令：SQL> select sum(sal) from emp;
执行结果： //输出薪水总和的记录
SUM(SAL)
----------
29025
命令：SQL> select count(*) from emp;
执行结果： //输出emp里有共有几条记录
COUNT(*)
----------
14
---------------------------------------------
命令：SQL> select count(distinct deptno) from emp;
执行结果： //输出去除相同编号后共有几条记录
COUNT(DISTINCTDEPTNO)
---------------------
3
===================================================
=======================
分组函数Group by的使用
=======================
=====================================================
------------------------------
平均薪水按部门编号分组输出记录
-------------------------------
命令：SQL> select avg(sal) from emp group by deptno;
执行结果：
AVG(SAL)
----------
1566.66667
2175
2916.66667
----------------------------------------
平均薪水和部门编号按部门编号分组输出记录
-----------------------------------------
命令：SQL> select deptno, avg(sal) from emp group by deptno;
执行结果：
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
--------------------------------------------
平均薪水和工作按部门编号和工作分组输出记录
--------------------------------------------
命令：SQL> select deptno, job,avg(sal) from emp group by deptno,job;
执行结果：
DEPTNO JOB AVG(SAL)
---------- --------- ----------
20 CLERK 950
30 SALESMAN 1400
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 3000
============================================
----------------------------------------------------------
分组函数是多行的输入，但是只有一行输出，下面这个例子会出错
---------------------------------------------------------
因为ename可能有多行的输出
---------------------------
命令：SQL> select ename,max(sal) from emp;
执行结果：
select ename,max(sal) from emp
*
第 1 行出现错误:
ORA-00937: 不是单组分组函数
============================================
------------------------------------------
上面例子的方法是不行的，所以要用到子查询
------------------------------------------
命令：SQL> select ename from emp where sal = (select max(sal) from emp);
执行结果：
ENAME
----------
KING
----------------------------------------------------
命令：SQL> select ename,max(sal) from emp group by deptno;
执行结果：
select ename,max(sal) from emp group by deptno
*
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
------------------------
命令：SQL> select deptno,max(sal) from emp group by deptno;
执行结果：
DEPTNO MAX(SAL)
---------- ----------
30 2850
20 3000
10 5000
思考：为什么前面一个会出错，后面一个不会？
----------------------------------------------------------------
----------------
对分组的限制
----------------
===========================================================
where是对单条的记录，单条的数据进行过滤
----------------------------------------
having语句的使用，对分组进行限制
============================================================
------------------------
按部门编号进行分组
------------------------
命令：SQL> select avg(sal),deptno from emp group by deptno;
执行结果：
AVG(SAL) DEPTNO
---------- ----------
1566.66667 30
2175 20
2916.66667 10
------------------------------------
用having把工资大于2000的分组记录输出
-------------------------------------
命令：SQL> select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
执行结果：
AVG(SAL) DEPTNO
---------- ----------
2175 20
2916.66667 10
===========================================
----------------------------
order by对最后的结果进行排序
----------------------------
===============================
总结：select语句的严格排序如下
===============================
SQL> select * from emp
2 where sal > 1000
3 group by deptno
4 having
5 order by
----------------------
严格按照这个顺序执行
----------------------
==============================================================
例如：求一个平均薪水，薪水大于1200的雇员，按部门编号进行分组，
分组之后的平均工资大于1500，查询分组之后的平均工资，
按照平均薪水倒序排列的记录。
===============================================================
------------------------------
命令：SQL> select avg(sal) from emp
2 where sal > 1200
3 group by deptno
4 having avg(sal) > 1500
5 order by avg(sal) desc;
执行结果：
AVG(SAL)
----------
2991.66667
2916.66667
1690
=================================
第五节：子查询
----------------------------------
在select语句里面套select语句
----------------------------------
******笔记中所谓的新的语法（新的连接）和旧的语法（旧的连接）***********
******分别指的是SQL1997年的标准*******和***SQL1992年的标准*************
=============
*****----------------------------****
子查询的例子（select单句查询）
****-----------------------------****
--------------------------------------
把薪水大于平均薪水的名字和薪水记录输出
---------------------------------------
命令：SQL> select ename,sal from emp where sal > (select avg(sal) from emp);
执行结果：
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
-------------
---------------------------------------
按部门编号把最高薪水和部门编号记录输出
---------------------------------------
命令：SQL> select max(sal),deptno from emp group by deptno;
执行结果：
MAX(SAL) DEPTNO
---------- ----------
2850 30
3000 20
5000 10
--------------
*****----------------------******
两张表连接
*****----------------------******
命令：SQL> select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by
deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);
执行结果：
ENAME SAL
---------- ----------
BLAKE 2850
SCOTT 3000
KING 5000
FORD 3000
-------------------------------------------------------------------------------
用子查询的时候，把它当做一张表t，再做子连接，如上例中把求出来的结果当做一张表t
--------------------------------------------------------------------------------
===================================================================
===================================================================
*******-------------------------------------------*****
自连接的应用（自己和自己连接，也叫内连接）
*******-------------------------------------------*****
==========================================================
方法：给同一张表取不同的别名，相当于两张表，当成两张表来用
==========================================================
--------------------------------
例子：求一个雇员的经理人的名字
-------------------------------
命令：SQL> select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
执行结果： //给emp表取e1,e2，相当于两张表
ENAME ENAME
---------- ----------
FORD JONES
SCOTT JONES
TURNER BLAKE
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
JONES KING
ENAME ENAME
---------- ----------
CLARK KING
SMITH FORD
已选择13行。
-------------------
为什么只选择了13行？
------------------------------------
因为KING是主席，是最高的，没有经理人
-------------------------------------
=============================================
-----------------
SQL 1992的标准
-----------------
命令：SQL> select ename,dname,grade from emp e,dept d,salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal //这句语句是数据的连接条件
and job <> 'CLETK'; //数据的过滤条件
执行结果：
ENAME DNAME GRADE
---------- -------------- ----------
SMITH RESEARCH 1
JAMES SALES 1
ADAMS RESEARCH 1
WARD SALES 2
MARTIN SALES 2
MILLER ACCOUNTING 2
TURNER SALES 3
ALLEN SALES 3
CLARK ACCOUNTING 4
BLAKE SALES 4
JONES RESEARCH 4
ENAME DNAME GRADE
---------- -------------- ----------
SCOTT RESEARCH 4
FORD RESEARCH 4
KING ACCOUNTING 5
已选择14行。
===========================================
----------------
SQL1999年的标准
----------------
命令：SQL> select ename,dname from emp cross join dept;
执行结果： //交叉连接
ENAME DNAME
---------- --------------
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
JONES ACCOUNTING
MARTIN ACCOUNTING
BLAKE ACCOUNTING
CLARK ACCOUNTING
SCOTT ACCOUNTING
KING ACCOUNTING
TURNER ACCOUNTING
ADAMS ACCOUNTING
ENAME DNAME
---------- --------------
JAMES ACCOUNTING
FORD ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
ALLEN RESEARCH
WARD RESEARCH
JONES RESEARCH
MARTIN RESEARCH
BLAKE RESEARCH
CLARK RESEARCH
SCOTT RESEARCH
ENAME DNAME
---------- --------------
KING RESEARCH
TURNER RESEARCH
ADAMS RESEARCH
JAMES RESEARCH
FORD RESEARCH
MILLER RESEARCH
SMITH SALES
ALLEN SALES
WARD SALES
JONES SALES
MARTIN SALES
ENAME DNAME
---------- --------------
BLAKE SALES
CLARK SALES
SCOTT SALES
KING SALES
TURNER SALES
ADAMS SALES
JAMES SALES
FORD SALES
MILLER SALES
SMITH OPERATIONS
ALLEN OPERATIONS
ENAME DNAME
---------- --------------
WARD OPERATIONS
JONES OPERATIONS
MARTIN OPERATIONS
BLAKE OPERATIONS
CLARK OPERATIONS
SCOTT OPERATIONS
KING OPERATIONS
TURNER OPERATIONS
ADAMS OPERATIONS
JAMES OPERATIONS
FORD OPERATIONS
ENAME DNAME
---------- --------------
MILLER OPERATIONS
已选择56行。
===================================================
-----------------------
旧的连接条件
----------------------
命令：SQL> select ename,dname from emp,dept where emp.deptno = dept.deptno;
执行结果：
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
TURNER SALES
ALLEN SALES
ENAME DNAME
---------- --------------
JAMES SALES
BLAKE SALES
MARTIN SALES
已选择14行。
================================================
--------------------------
新的连接条件
--------------------------
命令：SQL> select ename,dname from emp join dept on (emp.deptno = dept.deptno);
执行结果： //join是连接，on后面跟着连接条件
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
TURNER SALES
ALLEN SALES
ENAME DNAME
---------- --------------
JAMES SALES
BLAKE SALES
MARTIN SALES
已选择14行。
======================================================
-----------------------------------------------------------
如果是等值连接，还可以用（using)这种简便方法（不推荐使用）
-----------------------------------------------------------
执行结果与上面的结果一样
---------------------------
命令：SQL> select ename,dname from emp join dept using(deptno);
执行结果： //using(deptno)的意思是emp表和dept表的deptno相等
ENAME DNAME //注意：两张表都要有deptno
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
TURNER SALES
ALLEN SALES
ENAME DNAME
---------- --------------
JAMES SALES
BLAKE SALES
MARTIN SALES
已选择14行。
=========================================================
----------------
连接2张表
---------------
命令：SQL> select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
执行结果： //连接表emp和表salgrade，并且各自取别名e和s，求
ENAME GRADE //e表中工资介于s表中最小工资和最高工资的名字和等级的记录
---------- ----------
SMITH 1
JAMES 1
ADAMS 1
WARD 2
MARTIN 2
MILLER 2
TURNER 3
ALLEN 3
CLARK 4
BLAKE 4
JONES 4
ENAME GRADE
---------- ----------
SCOTT 4
FORD 4
KING 5
已选择14行。
==================================================
--------------------------------------------------
三张表的连接，用join连接，连接条件是on（新的语法）
--------------------------------------------------
命令：SQL> select ename,dname,grade from emp e join dept d on (e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where ename not like '_A%';
执行结果：
ENAME DNAME GRADE
---------- -------------- ----------
SMITH RESEARCH 1
ADAMS RESEARCH 1
MILLER ACCOUNTING 2
TURNER SALES 3
ALLEN SALES 3
CLARK ACCOUNTING 4
BLAKE SALES 4
JONES RESEARCH 4
SCOTT RESEARCH 4
FORD RESEARCH 4
KING ACCOUNTING 5
已选择11行。
==========================================
----------------------
自连接（新的语法）
----------------------
命令：SQL> select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);
执行结果：
ENAME ENAME
---------- ----------
FORD JONES
SCOTT JONES
TURNER BLAKE
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
JONES KING
ENAME ENAME
---------- ----------
CLARK KING
SMITH FORD
已选择13行。
------------------------------
===================================
**********外连接****************
===================================
----------------------
第一：左外连接（left）
----------------------
命令：SQL> select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
执行结果： //e1是左边的表，e2是右边的表
ENAME ENAME
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
ENAME ENAME
---------- ----------
JONES KING
SMITH FORD
KING //注意，这里把主席KING也显示出来了
已选择14行。 //左外连接把左边那张不能显示出来的多余的数据也给显示出来
===========================================
---------------------------------------
接下来比较这两个连接有什么不一样？
---------------------------------------
命令：SQL> select ename,dname from emp e join dept d on (e.deptno = d.deptno);
执行结果：
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
TURNER SALES
ALLEN SALES
ENAME DNAME
---------- --------------
JAMES SALES
BLAKE SALES
MARTIN SALES
已选择14行。
------------
-------------
右外连接
-------------
命令：SQL> select ename,dname from emp e right join dept d on (e.deptno = d.deptno);
执行结果：
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
TURNER SALES
ALLEN SALES
ENAME DNAME
---------- --------------
JAMES SALES
BLAKE SALES
MARTIN SALES
OPERATIONS
已选择15行。
-----------------------------------------------------------------------------
发现后面一个比前面一个多了一行OPERATIONS，那是因为采用了右外连接（right）
-----------------------------------------------------------------------------
********==================================*******
外连接总结：
左外连接时，会把左边多余的数据给显示出来;
右外连接时，会把右边多余的数据给显示出来;
*******===================================*********
---------------------------------
补充：（全外连接，用关键词full）
-------------------------------------
（SQL1992标准不支持，只有SQL1999支持）
-------------------------------------
命令：SQL> select ename,dname from emp e full join dept d on (e.deptno = d.deptno);
执行结果： //全外连接是把左边和右边的多余的数据都拿出来
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
ENAME DNAME
---------- --------------
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
OPERATIONS
已选择15行。
-----------------------------
题目：求雇员里哪些人是经理人？
-----------------------------
命令：SQL> select ename,deptno from emp where empno in (select mgr from emp);
执行结果：
ENAME DEPTNO
---------- ----------
JONES 20
BLAKE 30
CLARK 10
SCOTT 20
KING 10
FORD 20
已选择6行。
-------------
求平均薪水的等级最低的部门的部门编号
--------------------------------
为什么有了变长字符串还要定长字符串呢？
答案：定长字符串的效率比定长字符串效率高。但是定长字符串浪费空间，是拿空间换时间。
---------------------------------
字段级约束：只能约束一个字段
表级约束：可以用于多个字段
--------------------------------
创建索引：
命令：SQL> create index idx_stu_email on stu (email);
执行结果：
索引已创建。
--------------------
删除索引：
命令：SQL> drop index idx_stu_email;
执行结果：
索引已删除。
------------------
切记：不要轻易建立索引，除非字段少的时候。
----------------------
===============
试图
==========
默认用V$建立
---------------
缺点：视图的维护比较困难，比如把基本表修改了，视图就要跟着更改；
---------------
视图可以更新吗？
答案：可以，但是视图是虚表，实际更新的是基本表里的数据
--------------------------------------------------------
视图的优点：便于查询，但是不便于维护
-------------------------------------------------
马上讲：logo 画一只马，然后，有一个人在马上演讲
讲真话：
反腐风云：
------------------------------------------------
序列创建：
命令：SQL> create sequence seq;
执行结果：
序列已创建。
-----------------------------------
下一个字段：
SQL> select seq.nextval from dual;
NEXTVAL
----------
1
SQL>
SQL> select seq.nextval from dual;
NEXTVAL
----------
2
------------------------------------
三范式（不存在冗余数据）
------------------------------------
1.第一范式第一要求：
（1）要有主键；
（2）列不可分；
2.用2个或者2个以上字段做主键的时候，其他字段不可以存在部分依赖于主键
----------------------------------------------------------------------
PL\SQL语言：带有分支和循环的语言
PL\SQL语言的格式：
-------------------------------------
---最简单的语句块---
--------------------
SQL> begin
2 dbms_output.put_line('HelloWorld!');
3 end;
4 /
PL/SQL 过程已成功完成。
--------------------------------------
若要输出，则还要加一句语句：
SQL> set serveroutput on;
否则，不能输出
------------------------------------------
SQL> set serveroutput on;
SQL> begin
2 dbms_output.put_line('helloworld!');
3 end;
4 /
helloworld!
PL/SQL 过程已成功完成。
-----------------------------------
declare是声明变量
------------------
---简单的PL/SQL语句块---
------------------------
declare
v_name varchar2(20);
begin
v_name := 'myname';
dbms_output.put_line(v_name);
end;
-----------------------------------
-----语句块的组成------
declare
v_num number := 0;
begin
v_num := 2/v_num;
exception //若有别的情况，则输出error
when others then
dbms_output.put_line('error');
end;
----------------------------------------------
----变量声明的规则
-------------------
1. 变量名不能够使用保留字，如from、select等；
2. 第一个字符必须是字母；
3. 变量名最多包含30个字符；
4. 不要与数据库的表或者列同名；
5. 每一行只能声明一个变量；
------------------------------------------------
-----常用变量类型-----
----------------------
1. binary_integer:整数，主要用来计数而不是用来表示字段类型
2. number：数字类型
3. char：定长字符串
4. varchar2：变长字符串
5. date：日期
6. long：长字符串，最长2GB
7. boolean：布尔类型，可以取值为true、false和null值
---------------------------------------------------
-----变量声明-----
------------------
SQL > declare
v_temp number(1);
v_count binary_integer := 0;
v_sal number(7,2) := 4000.00;
v_date date := sysdate;
v_pi constant number(3,2) := 3.14; //constant相当于java里的finally
v_valid boolean := false; //不能打印boolean类型的值
v_name varchar2(20) not null := 'myname';
begin
dbms_output.put_line('v_temp value:'|| v_temp);
end;
/
-----------------------------
---变量声明，使用%type属性---
-----------------------------
SQL> declare
v_empno number(4);
v_empno2 emp.empno%type; //意思是v_empno2这个变量类型跟emp表里的empno类型一样
v_empno3 v_empno2%type;
begin
dbms_output.put_line('Test');
end;
----------------------------------------
-----变量类型-----
------------------
命令：SQL> declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno; //type相当于java里的数组
begin
v_empnos(0) := 7369;
v_empnos(2) := 7839;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
/
---------------------------------------
-----Record变量类型-----//相当于java里的类
------------------------
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno||''||v_temp.dname);
end;
------------------------------------------------------------------
---使用%rowtype声明record变量---
--------------------------------
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno||''||v_temp.dname);
end;
-----------------------------------------------------------
- 学习Oracle重要笔记
- cocos2dx2.2.3重要学习笔记
- ORACLE学习几个重要的后台进程
- 学习Oracle需要掌握的重要知识点
- cocos2d-x学习笔记(3)--重要概念
- unity3d学习笔记 重要函数方法总结
- html 5 学习笔记-重要知识点 1
- WebGL学习笔记01-------几个重要参数
- 【前端学习笔记】重要知识点积累
- Servlet学习笔记之---重要细节
- C++学习重要笔记之呱呱坠地
- ARMv8 Load / store 指令学习重要笔记
- hibernate3.3.2学习笔记---重要细节
- UCOS 学习笔记之重要概念问答
- Oracle学习的一些建议 兴趣很相当重要
- Oracle学习笔记--Oracle入门
- Oracle学习笔记
- 学习ORACLE笔记
- 求链表倒数第K个节点
- JAVA基础知识学习
- nefu117 - 素数个数的位数
- java获取本地局域网的外网IP 笔记
- 构建的可伸缩性和达到的性能：一个虚拟座谈会
- 学习Oracle重要笔记
- Windows下Qt 插件开发
- 图解：2013年百度搜索引擎工作原理
- MVC框架
- QT Designer中安装QWT插件
- ajax xhr.status ！= 200 时的意外
- 搜索引擎工作的基础流程与原理
- 搜索引擎分类与工作原理
- 单链表
