oracle简单查询.docx
《oracle简单查询.docx》由会员分享，可在线阅读，更多相关《oracle简单查询.docx（20页珍藏版）》请在冰点文库上搜索。
oracle简单查询
知识点
1.SQL语法的基本格式及其简单查询、限定查询、分组统计的使用；
2.scott用户下的主要表的结构，需要记下。
Scott用户的表结构（重点）
SQL语句要在scott用户下完成，这个用户下一共有四张表，可以使用：
SELECT*FROMtab；
查看所有数据表名称，如果现在要想知道每张表结构：
DESC表名称；
1.部门表dept
NO
名称
类型
描述
1
DEPETNO
NUMBER
（2）
表示部门编号，由两位数字所组成
2
DNAME
VARCHAR2（14）
部门名称，最多由14个字符所组成
3
LOC
VARCHAR2（13）
部门所在的位置
2.雇员表emp
NO
名称
类型
描述
1
EMPAO
NUMBER（4）
雇员的标号，由思维数字组成
2
ENAME
VARCHAR2（10）
雇员的姓名，由10位字符所组成
3
JOB
VARCHAR2（9）
雇员的职位
4
MGR
NUMBER（4）
雇员对应的领导编号，领导也是雇员
5
MGRHIREDATE
DATE
雇员雇佣日期
6
SAL
NUMBER（7,2）
基本工资，其中有两位小数，五位整数，一共七位
7
COMM
NUMBER（7,2）
奖金，佣金
8
DEPTNO
NUMBER
（2）
雇员所在部门编号
3.工资等级表salgrade
No
名称
类型
描述
1
GRADE
NUMBER
工资等级
2
LOSAL
NUMBER
此等级最低工资
3
HISAL
NUMBER
此等级最高工资
4.工资表bonus
No
名称
类型
描述
1
ENAME
VARCHR2（10）
雇员姓名
2
JOB
VARCHR2（9）
雇员的职位
3
SAL
NUMBER
雇员的工资
4
COMM
NUMBER
雇员奖金
3.2简单查询（重点）
查询语句形式：
SELECT*FROMemp;
SQL是一个强大的数据库语言mSQL通常用于与数据库的通讯。
ANSL（美国标准学会）声称，SQL是关系数据库管理的标准语言。
Oracle数据库之所以发展好，主要因为oracle是全世界最早采用SQL语句的数据库产品。
SQL功能强大，概括起来，可以分为以下几组：
DML（DataManipulationLanguage,数据操作语言）—用于检索或者修改数据
DDL（DataDefinitionLanguage,数据定义语言）—用于定义数据的结构，如创建、修改或者删除数据库对象
DCL（DataControlLanguage,数据控制语言）—用于定义数据库用户的权限
而简单查询指的是查询出一张表中的所有数据，简单查询的语法如下：
SELECT[DISTINCT]*|字段[别名][字段[别名]]
FROM表名称[别名]
范例：
查询dept表的全部记录
SELECT*FROMdept;
范例：
查询出每个雇员的编号、姓名、基本工资
SELECTempno,enameFROMemp;
范例：
查询每个雇员的职位
SELECTjobFROMemp;
这个时候发现查询出来的job内容出现了重复的数据，而之所以数据会有重复，主要的原因是现在没有消除掉重复记录，可以使用DISTINCT除掉所有的重复内容：
SELECTDISTINCTjobFROMemp;
但是对于重复数据，指的是一行中的每个列的记录都重复，才叫重复数据。
范例：
查询每个雇员的姓名、职位
SELECTDISTINCTename,jobFROMemp;
在进行简单查询的操作之中，也可以使用各个数学的四则运算符。
范例：
要求显示每一个雇员的姓名、职位、基本年薪。
SELECTename,job,sal*12FROMemp;
但是这个时候显示列上出现了一个“SAL*12”，这个肯定是显示的查询列，但是这个列名称不方便浏览，所以此时可以起一个别名。
SELECTename.job.sal*12FROMemp;
但是对于别名，建议不要使用中文，所说的“易语言”。
范例：
由于公司福利很高，每个月都有200元饭食补贴以及100元车费补助，这时候年薪
SELECTename,job,（sal+300）*12incomeFROMemp;
范例：
每年多发一月工资。
SELECTename,job,（sal+300）*12+salincomeFROMemp;
再简单查询之中也可以使用“||”连接查询字段
范例：
观察“||”的使用
SELECTempno||’,’||enameFROMemp;
由于“，”属于原样输出的字符串，所以必须使用“‘’”括起来，在SQL中表示字符串。
范例：
要求现在数据按照如下格式显示：
雇员编号:
7369的雇员姓名是:
SMTTH,基本工资是:
800,职位是:
CLERK!
现在相当于查找：
empno、ename、sal、job字段，并且使用“||”进行连接。
SELECT‘雇员编号是:
’||empno||’的雇员姓名是:
’||ename||’,基本工资是:
’||sal||’,职位是:
’||job||’!
’雇员信息FROMemp;
一定要记住别名的内容不要使用“‘”括起来，而只有在SELECT字句中出现内容使用“’”。
3.3限定查询（重点）
可以显示的记录进行查询进行过滤的操作，而这就属于限定查询的工作，限定查询就是在之前语法的基本上增加了一个where子句，用于指定限定条件，
SELECT[DISTNCT]*|字段[别名][字段[别名]]
FROM表名称[别名]
[WHERE条件（S）]，
在WHERE子句之后可以增加多个条件，最常见条件就是基本的关系运算：
>、>=、!
=（<>）、BETWEEN.AND,LIKE,IN,ISNULL,AND,OR,NOT.
1.关系运算
范例：
要求查询基本工资高于1500的所有雇员信息。
WHERE*FROMEMP;
WHEREsal>1500;
范例：
查询出所有职位是办事员的
SELECT*FROMempWHEREjob=’clerk’;
发现错误，原因是数据库区分大小写，
SELECT*FROMempWHEREjob=’CLERK’;
以上操作了一个条件，现在也可以操作多个条件，而这个条件之间可以使用AND或OR进行操作。
范例：
查询工资在1500-3000之间的全部雇员信息。
SELECT*FROMemp
WHEREsal=1500ANDsal=3000;
范例：
查询出职位是办事员，或者是销售的全部信息,工资大于1200
SELECT*FROMemp
WHERE（job=’CLERK’ORjob=’SALESMAN’）ANDSAL>1200;
范例：
查询所有不是办事员的雇员信息
SELECT*FROMempWHEREjob<>’CLERK’;
SELECT*FROMempWHEREjob!
=’CLERK’;
SELECT*FROMempWHERENOTjob=’CLERK’;
2.范围判断：
BETWEEN…AND…
“BETWEEN最小值AND最大值”，表示的是一个范围间的判断过程。
范例：
要求查询出基本工资在1500-3000的雇员信息。
SELECT*FROMemp
WHEREsalBETWEEN1500and3000;
范例：
现在也可以对BETWEENAND操作求反
SELECT*FROMemp
WHERENOTsalBETWEEN1500and3000;
可是“BETWEEN..AND”操作符不光只是针对于数字有用，对于日期也同样有用
范例：
要求查询出在1981年雇佣的全部雇员信息
·时间范围：
1981-01-01—1981-12-31，使用hiredate字段表示雇佣日期；
·Hiredate字段的内容可以使用字符串表示；’01-1月-1981’-’31-12月-81’
SELECT*FROMemp
WHEREhiredateBETWEEN’01-1月-1981’-’31-12月-81’;
3.判断是否为空：
IS（NOT）NULL
使用此语法可以判断某一个字段的内容是否是“null”但是null和数字0以及空字符串是两个概念。
范例：
查询出所有领取奖金的雇员信息。
SELECT*FROMemp
WHEREcommISNOTNULL;
SELECT*FROMemp
WHERENOTcommISNULL;
范例：
查询出所有不领取奖金的雇员
SELECT*FROMemp
WHEREcommISNULL;
4.指定范围判断：
IN
SELECT*FROMemp
WHEREempno=7369ORempno=7566ORempno=7799;
或
SELECT*FROMemp
WHEREempnoIN（7369,7566,7799）;
使用的是NOTIN则表示不再指定的范围之中。
SELECT*FROMemp
WHEREempnoNOTIN（7369,7566,7799）;
注意点：
NOTIN问题
如果现在使用了IN操作符，查询范围之中存在了Null,不影响查询；
SELECT*FROMemp
WHEREempnoIN（7369,7566,null）;
如果现在使用费是NOTIN操作符，如果查询中有了null，则不会有任何的查询结果返回；
SELECT*FROMemp
WHEREempnoNOTIN（7369,7566,null）;
NOTIN之中不能出现null，而且记住，如果NOTIN中出现null表示的就是查询全部数据。
5.模糊查询：
LIKE子句
LIKE子句的功能室提供模糊查找的操作，例如：
某些程序上出现的搜索操作，都属于LIKE子句的实现，但是必须提醒，搜索引擎的查询可不是LIKE,
但是想要使用LIKE子句则必须认识两个匹配符号：
匹配单个字符：
_;→1个
匹配任意多个字符：
%；→0个、1个、多个
范例：
要求查询雇员姓名中以字母A开头的全部雇员信息；
SELECT*FROMemp
WHEREenameLIKE'A%';
范例：
要求查询出雇员姓名中第二个字母为A的全部雇员信息
SELECT*FROMemp
WHEREenameLIKE'_A%';
范例:
要求查询出雇员姓名中带有字母A的雇员。
SELECT*FROMemp
WHEREenameLIKE'%A%';
也可以用NOT操作，对操作进行求反的功能；
SELECT*FROMemp
WHEREenameNOTLIKE'%A%';
但是对于LIKE子句不一定只能在字符串上表示，可以再任意数据上表示
SELECT*FROMemp
WHEREenameLIKE'%1%'ORhiredateLIKE'%1%'ORsalLIKE'%1%';
说明:
关于LIKE子句使用
数据库的模板查询肯定使用LIKE子句，但是使用LIKE子句的时候有一个最大的注意点；如果在模糊查询上不设置任何查询关键字的话（'%%'）怎表示查询全部记录；
SELECT*FROMemp
WHEREenameLIKE'%%'ORhiredateLIKE'%%'ORsalLIKE'%%';
这一个特点可以帮助用户节约很多代码所以一定要记住。
3.4数据的排序（重点）
当数据返回查询结果之后，所有的数据默认值情况下按照固原编号排序的，当然，现在，也可以使用ORDERBY子句指定所需要的排序操作列，而这个时候的SQL语法如下：
SELECT[DISTINCT]*|字段[别名][字段[别名]]
FROM表名称[别名]
[WHERE条件（S）]
[ORDERBY字段[ASC|DESC][字段[ASC|DESC],]]
“ORDERBY”子句中是写在所有的SQL语句最后内容，而且对于排序有以下几点说明：
·排序的时候可以指定多个排序的字段；
·排序方式有两种：
|-升序（ASC）:
默认，不写也是升序；
|-降序（DESC）:
用户需要指定，由大到小排序；
范例：
查询所有雇员的信息，要求按照工资排序；
SELECT*FROMempORDERBYsal;
SELECT*FROMempORDERBYsalASC;
范例：
进行降序排列
SELECT*FROMempORDERBYsalDESC;
范例：
要求查询出所有的雇员信息，按照工资由高到低，如果工资相同按照，雇佣日期有早到晚来排序。
此时肯定需要两个字段排序：
工资（DESC）,雇佣日期（ASC）;
SELECT*FROMempORDERBYsalDESC,hiredateASC;
对于排序操作，一般只需要的地方上使用，而且一定要记住的是，ORDERBY子句是卸载所有的SQL语句的最后部分。
3.5单行函数（重点）
虽然各个数据库支持SQL语句，但是每一个数据库也有每一个数据自己所支持的操作函数，这些就是单行函数，而如果要想进行数据库开发的话，除了SQL外，就是要多学习函数。
单行函数主要分为以下五类：
字符函数、数字函数、日期函数、转换函数、通用函数；
3.5.1字符函数
字符函数的功能是进行字符串函数的操作，下列给出几个字符字符函数：
·UPPER（字符串|列）：
将输入的字符串变为大写返回；
·LOWER（字符串|列）：
将输入的字符串变为小写返回；
·INTCAP（字符串|列）：
开头首字母大写；
·LENGTH（字符串|列）：
求出字符串的长度；
·REPLACE（字符串|列）：
进行交换；
·SUBSTR（字符串|列.开始点[结束点]）:
字符串截取；
范例：
观察转大写的函数
SELECTUPPER（‘hello’）FROMdual;
此时如果输入是小写，则肯定无法查询出数据，所以这个时候不能要求用户这么多，所以这个时候只能由程序自己去使用，假如一个函数：
SELECT*FROMempWHEREename=UPPER（‘&STR’）;
当然以上的“&”的操作属于替代变量的内容，此部分内容不做重点。
范例：
观察转小写的操作，将所有的雇员姓名按照小写字母返回
SELECTLOWER（ename）FROMemp;
范例：
将每一个雇员姓名的开头首字母大写
SELECTINITCAP（ename）FROMemp;
范例：
查询每个雇员姓名的长度
SELECTenameLENGTH（ename）FROMemp;
范例：
查询姓名长度正好是5的雇员信息。
SELECTenme,LENGTH（ename）FROMemp
WHERELENGTH（ename）=5;
范例：
使用字母“_”替换掉姓名中的所有字母“A”;
SELECTREPLACE（ename,’A’,’_’）FROMemp;
字符截取操作有两种语法；
语法一：
SUBSTR（字符串|列，开始点），表示从开始点一直截取到结尾；
SELECTename,SUBSTR（ename,3）FROMemp;
语法二：
SUBSTR（字符串|列，开始点，结束点），表示从开始点截取到结束点，截取部分内容；
SELECTename,SUBSTR（ename,0,3）FROMemp;
SELECTename,SUBSTR（ename,1,3）FROMemp;
范例：
要求截取每个雇员姓名的后三个字母
·正常思路：
通过长度-2确定开始点
SELECTename,SUBSTR（ename,LENGTH（ename）-2）FROMemp;
·新思路：
设置负数，表示从后指定截取位置；
SELECTename,SUBSTR（ename,-3）FROMemp;
面试题：
请问SUBSTR（）函数的时候下标从0还是从1开始？
·在ORACLE数据库之中，SUBSTR（）函数从0或1开始都是一样的；
·SUBSTR（）也可以设置为负数，表示由后指定截取的开始点；
3.5.2数字函数
数字函数一共有三个：
·ROUND（数字|列[保留小数的位数]）：
四舍五入的操作；
·TRUNC（数字|列[保留小数的位数]）：
舍弃指定位置的内容；
·MOD（数字1，数字2）：
去模，取余数；
范例：
验证ROUND（）函数
SELECTROUND（903.53567）,ROUND（-903.53567）,ROUND（903.53567.2）,ROUND（903.53567,-1）FROMdual;
范例：
验证TRUNC（）函数
SELECTTRUNC（903.53567）,TRUNC（-903.53567）,TRUNC（903.53567,2）,TRUNC（903.53567,-1）FROMdual;
范例：
取模操作
SELECTMOD（10,3）FROMdual;
以上的三个主要的数学函数，在以后学习JAVA中也会有相匹配的内容。
3.5.3日期函数
如果现在要想进行日期操作，则首先有一个必须要解决的问题，就是如何取得当前的日期，这个当前日期可以使用“SYSDATE”取得，代码如下：
SELECTSYSDATEFROMdual;
除了以上的当前日期之外，在日期中也可以进行若干计算；
·日期+数字=日期，表示若干天之后的日期；
SELECTSYSDATE+3，SYSDATE+300FROMdual;
·日期—数字=日期，表示若干天前的日期；
SELECTSYSDATE-3，SYSDATE-300FROMdual;
日期—日期=数字，表示的是两个日期间的天数，但是肯定是大日期—小日期；
范例:
求出每个雇员到今天为止的雇佣天数
SELECTename,hiredate,SYSDATE-hiredateFROMemp;
而且很多编程语言之后总，也都会提出一种概念，日期可以通过数字表示出来。
除了以上的三个分公式，也提供了如下的四个操作函数；
·LAST_DAY（日期）：
求出指定日期的最后一天；
范例：
求出本月最后一天日期
SELECTLAST_DAY（SYSDATE）FROMdual;
·NEXT_DAY（日期，星期数）：
求出下一个指定星期X的日期；
范例：
求出下一个周一
SELECTNEXT_DAY（SYSDATE,’星期一’）FROMdual;
·ADD_MONTHS（日期，数字）：
求若干月之后的日期；
范例：
求出你们学习的最晚毕业时间（即：
找到工作的最晚时间）
SELECTADD_MOMTHS（SYSDATE,4）FROMdual;
·MONTHS_BETWEEN（日期1，日期2）：
求出两个日期之间所经历的月份；
范例：
求出每个雇员到今天为止的雇员月份。
SELECTename,hiredate,TRUNC（MONTHS_BETWEEN（SYSDATE,hiredate））FROMemp;
在所有的开发之中，如果是日期的操作，建议使用以上的函数，因为这些函数可以避免闰年的问题。
3.5.4转换函数（核心）
现在已经接触到Oracle数据库之中的三种数据：
数字（NUMBER）、字符串（VARCHAR2）、日期（DATE），装换函数的主要功能是完成这几种数据间的互相转换的操作，一共有三个转换函数；
·TO_CHAR（字符串|列，格式字符串）：
将日期或者是数字变为字符串显示；
·TO_DATA（字符串,格式字符串）:
将字符串变为DATE数据显示；
·TO_NUMBER（字符串）：
将字符串变为数字显示；
1.TO_CHAR（）函数
在之前查询过当前的系统日期时间：
SELECTSYSDATEFROMemp;
这个时候按照“日-月-年”的格式显示，很明显这种显示格式不正常的思路，正常是“年-月-日”，所以这种情况下可以使用TO_CHAR（）函数，但是使用此函数的话需要一些格式字符串：
年（YYYY）,月（mm）,日（dd）.
SELECTTO_CHAR（SYSDATE,’yyyy-mm-dd’）,TO_CHAR（SYSDATE,’yyyy’）
Year,TO_CHAR（SYSDATE,’mm’）months,TO_CHAR（SYSDATE,’dd’）dayFROMdual;
但是这个时候的显示数据之中可以发现会存在前导0的话，可以加入一个“fm”.
SELECTTO_CHAR（SYSDATE,’fmyyyy-mm-dd’）dayFROMdual;
正常人都加0，所以这个标记知道就行了，可是在Oracle之中，DATE里边是包含了时间的，但是之前的代码没有显示出时间，要想显示时间则需要增加标记：
SELECTTO_CHAR（SYSDATE,’fmyyyy-mm-ddhh24:
mi:
ss’）dayFROMdual;
一定要注意，使用TO_CHAR（）函数之后，所有的内容都是字符串，不再是之前的DATE型数据，TO_CHAR（）函数也可以用于数字的格式化上，这个时候每一个“9”表示一位数字的概念，而不是数字9的概念。
SELECTTO_CHAR（89078907890,’L999,999,999,999,999’）FROMemp;
其中的字母“L”,表示的是“Local”的含义，即：
当前的所在的语言环境下的货币符号。
2.TO_DATE（）函数
此函数的主要功能是将一个字符串变为DATE型数据。
SELECTTO_DATE（‘1989-09-12’,’yyyy-mm-dd’）FROMdual;
一般此函数在更新数据库的时候使用较多；
3.TO_NUMBER（）函数：
基本不用
TO_NUMBER（）函数一看就知道是将字符串变数字的；
SELECTTO_NUMBER（‘1’）+TO_NUMBER（‘2’）FROMdual;
但是在Oracle之中真的很智能，所以以上的功能不使用TO_NUMBER（）也可以完成；
SELECT‘1’+’2’FROMdual;
所以现在的TO_NUMBER（）函数基本上已经是不考虑了，重点的函数在TO_CHAR（）上，其次是TO_DATE（）函数。
3.5.5通用函数（核心）
通用函数主要有两个：
NVL（）、DECODE（），这两个函数算是Oracle自己的特色函数了；
1.NVL（）函数，
升级会员 