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;
-----------------------------------------------------------