文章目录
1.查询
以下以scott用户中的四张表作为基础来进行查询。
Scott用户的表结构
- 雇员表(emp)
雇员表中记录的是一个个雇员的基本信息
- 部门表(dept)
表示一个个部门的具体信息
3. 工资登记表(SALGRADE)
一个公司工资是有等级制度,那么用此表表示一个工资的等级
4. 工资表(BONUS)
表示的是一个雇员的工资及奖金
注意事项
- Oracle不支持在指定字段的时候,用括号括起来的字段
- DISTINCT可以去除重复,但是注意位置是在SELECT的后跟。一定要记住,别名上的内容不要使用” ‘ ”括起来。而只有在 SELECT子句中出现的内容使用。
- 对于别名,尽量建议不要使用中文,只要是程序的开发,都要回避中文
- Oracle的数据内容区分大小写
- Oracle中字符串的索引以1开始,如果输入0会被自动转换成1
简单查询
简单查询指的是查询出一张表中的所有的数据,简单查询的语法如下:
SELECT [DISTINCT] *| 字段 [别名] [,字段 [别名]]
FROM 表名称 [别名]
类似于SQL中的简单查询,这里不再详述,只介绍一些Oracle中的特殊用法。
在进行简单查询的操作之中,也可以使用各个数学的四则运算符。
例:要求显示每一个雇员的姓名、基本年薪,而且由于公司的福利很高,每个月都有 200 元的饭食补贴以及 100 元交通补贴,这个时候的SQL语句如下
- 括号的优先
select ename,(sal + 300) * 12 from emp;
但是这个时候显示列上出现了一个“SAL*12”,这个肯定是显示的查询列,但是这个列 名称不方便浏览,所以此时可以起一个别名。
- 列起别名
select ename,(sal + 300) * 12 as Year from emp;
- 链接查询的字段
在简单查询之中,也可以使用“||”链接查询的字段
例如:要求现在的数据按照如下的格式显示
雇员编号是:7369 的雇员姓名是:SMITH,基本工资是:800,职位是:CLERK!
select '雇员编号是:'||empno||'的雇员姓名是:'||ename||',基本工资是:'||sal||',职位
是:'||job||'!' 雇员信息 from emp;
限定(条件)查询
在之前的简单查询之中,是将所有的记录显示,但是现在可以对显示的记录进行过滤操作,而这就属于限定查询的工作,限定查询就是在之前语法的基础上增加了一个WHERE子句,用于指定限定的条件,此时格式如下:
SELECT [DISTINCT] *| 字段 [别名] [,字段 [别名]]
FROM 表名称 [别名]
[WHERE 条件(s)];
在 WHERE 子句之后可以增加多个条件,最常见的条件就是基本的关系运算:>、>=、<、 <=、!=(<>)、BETWEEN…AND、LIKE、IN、IS NULL 、AND、OR、NOT。
- 关系运算
AND用法:
要求查询出基本工资高于1500,低于3000的所有雇员信息
select * from emp where sal > 1500 and sal < 3000
若满足多条件的查询,可以类比为if语句中的多条件表达式
OR用法
查询出职位是办事员或者是销售人员的全部信息
select * from emp where job = 'CLERK' or job = 'SALESMAN'
多个条件,或的关系,和AND做区分,也就是不需要全部同时成立。可以从or开始拆分为多条语句最终做结果聚合
AND和OR的混合用法
查询出职位是办事员或者是销售人员的全部信息,并且要求这些雇员的工资大于 1200
select * from emp where (job='CLERK' or job='SALESMAN') and sal > 1200
!=的用法
查询所有不是办事员的雇员信息
select * from emp where job != 'CLERK'
类似于反查
- 范围判断
BETWEEN…AND…
“BETWEEN 最小值 AND 最大值”,表示的是一个范围间的判断过程。
例:要求查询出基本工资在 1500-3000 的雇员信息。
SELECT * from emp where sal between 1500 and 3000;
现在也可以对 BETWEEN…AND…操作求反
SELECT * FROM emp where sal not between 1500 and 3000;
注意:取反操作时,not的位置
可是”BETWEEN…AND…”操作符不光只是针对于数字有用,对于日期也同样有用,但是需要使用to_date进行转化
例:要求查询出在 1981 年雇佣的全部雇员信息,时间范围:
select * from emp where hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1981-12-31','yyyy-mm-dd');
注意:日期格式,也可以自己转换成自己想要的格式。
- 判断是否为空
IS (NOT) NULL
使用此语法可以判断某一个字段上的内容是否为”null”,但是 null 和数字 0 以及空字符串 是两个概念。
例: 查询出所有领取奖金的雇员信息
Select * from emp where comm is not null;
NVL函数
如果NVL第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第一个参数本来的值。
例:查询年薪及奖金
select sal*12+comm from emp; // 查出来没有奖金的会导致年薪也没有了
select sal*12+nvl(comm,0) from emp; // 使用nvl用指定的值来替换为空的值
- 指定范围的判断
IN操作符
IN 操作符表示的是一个查询的范围。
例:查询出雇员号是 7369、7566、7799 的雇员信息
select * from emp where empno in (7369,7566,7788);
NOT IN操作符
使用 NOT IN,则表示不在指定范围中
如果现在使用了 IN 操作符,查询的范围之中存在了 NULL,不影响查询;
例:
Select *from emp where empno in(7369,7566,null);
如果现在使用的是 NOT IN 操作符,如果查询的范围之中有了 NULL,则不会有任何的查询结果返回;
例:
Select * from emp where empno not in(7369,7566,null);
对于这个限制现在先作为特点记住,以后会讲解为什么 NOT IN 之中不能出现 NULL,而且记住了,如果NOT IN出现了null则表示的不是查询全部的信息。
需要说明的是,使用 IN 操作符不仅可以用在数字上,还可以用在字符串上,但是数字和字符串不能混用,因为数据类型只可能指定为其中一种
- 模糊查询
LIKE 子句的功能是提供了模糊查找的操作,例如:某些程序上出现的搜索操作,都属于LIKE 子句的实现,但是必须提醒的是,搜索引擎上的查询可不是 LIKE,但是要想使用 LIKE 子句则必须认识两个匹配符号:
-
匹配单个字符: _ ; ->1 个
-
匹配任意多个字符:% ; ->0 个、1 个、多个
例:要求查询雇员姓名中以字母A开头的全部雇员信息
Select *from emp where ename like ‘A%’;
要求查询出雇员姓名中第二个字母是 A 的全部雇员信息
Select * from emp where ename like ‘_A%’;
要求查询出雇员姓名中带有字母A的雇员
Select * from emp where ename like ‘%A%’;
LIKE现在也可以使用 NOT 操作,对操作进行求反功能
LIKE也可以在日期上使用
在开发之中,数据库的模糊查询肯定使用 LIKE 子句,但是在使用 LIKE 子句的时候有一个最大的注意点:如果在模糊查询上不设置任何的查询的关键字(%%)的话,则表示查询全部 记录:
这个特点可以帮助用户节约很多的代码,所以一定要记住。
- 运算符的优先级规则
和算数运算符一样,比较运算符和逻辑运算符同样有优先级的区分,在整个 WHERE 子句中优先级的排列从高到低分别是:比较操作符>NOT>AND>OR
2.排序
当数据返回查询结果之后,所有的数据默认情况下是按照雇员编号排序的,当然,现在 也可以使用”ORDER BY”子句指定所需要的排序的操作列,此时 SQL 语法格式如下:
SELECT [DISTINCT] *| 字段 [别名] [,字段 [别名]]
FROM 表名称 [别名]
[WHERE 条件(s)];
[ORDER BY 字段 [ASC|DESC] [,字段 [ASC|DESC],...]];
”ORDER BY”子句是写在所有的 SQL 语句最后的内容,而且对于排序有以下几点说明:
-
排序的时候可以指定多个排序的字段;
-
支持使用数字和字符串排序
-
排序的方式有两种:
-
升序(ASC):默认,不写也是升序;
-
降序(DESC):用户需要指定,由大到小排序;
-
升序降序排列
例:查询所有雇员的信息,要求按照工资排序
Select * from emp order by sal;
Select * from emp order by sal asc;
进行降序排列
Select * from emp order by sal desc;
显示所有的销售人员的工资,按升序排列。
Select * from emp where job= ’SALESMAN’ order by sal;
使用非选择列表排序
以上例子中都是按照在 SELECT 列表中出现的列,作为排序的依据,但是 SELECT 列表中也可以不出现排序的列。
例如:
Select empno,ename from emp order by sal;
使用表达式或者别名排序
在 SELECT 子句中可以出现算术表达式,比如计算员工的年薪,按照年薪排序:
select ename from emp order by sal*12;
也可以将表达式列或者表达式起别名,利用别名排序:
select ename as name from emp order by name;
使用多列排序
当执行操作时,不仅可以按照单列进行排序,还可以根据多列排序。当以多列作为排序
标准时,首先按照第一列进行排序,如果第一列数据相同,再以第二列排序,以此类推。
要求查出所有的雇员信息,按照工资由高到低排序,如果工资相同,则按照雇佣日期由早到晚排序
select * from emp order by sal,hiredate desc;
需要注意的是:多列排序时,不管正序还是倒序,每个列需要单独设置排列方式。
对于排序操作,一般只在需要的地方上使用。而且一定要记住,ORDER BY 子句是写在所有的 SQL 语句的最后的部分。
注:clear screen 清屏
3.单行函数
虽然各个数据库都是支持 SQL 语句的,但是每一个数据库也有它自己所支持的操作函 数,这些就是单行函数,而如果想要进行数据库开发的话,除了会使用 SQL 之外,就是要多学习函数。
单行函数主要常用的函数有以下五类:字符函数、数字函数、日期函数、转换函数、通用函数。
字符函数
字符函数的功能主要是进行字符串数据的操作,下面给出几个常用的字符函数:
-
UPPER(字符串|列): 将输入的字符串变为大写返回;
-
LOWER(字符串|列): 将输入的字符串变为小写返回
-
INITCAP(字符串|列): 开头首字母大写
-
LENGTH(字符串|列): 求出字符串的长度;
-
REPLACE(字符串|列,原始值,替换值): 进行替换;
-
SUBSTR(字符串|列,开始点 [,结束点]):字符串截取;
Oracle 之中有一点比较麻烦,即使要验证字符串,也必须编写完整的 SQL 语句,所以 Oracle 数据库之中为了用户的查询方便,所以专门提供了一个”dual”的虚拟表。该表没有任何内容,只是为了方便验证函数。
除此之外,还有几个不常用的函数:
- CONCAT(char1,char2):返回两个字符串连接后的结果,这个功能类似于使用连接操作 符"||"
- INSTR(char1,char2[, n [,m]]):用于取得子串在源字符串中的位置,也就是在 char1 中搜 索 char2,从 n 的位置开始搜索,如果没有指定 n,就从第一个字符开始搜索。m 用于指定子 串的第 m 次出现的次数,如果不指定取值为 1。如果在 char1 中没有找到子串,则返回 0.
数字函数
常用的数字函数一共有三个:
-
ROUND(数字|列 [,保留小数的位数]):四舍五入的操作;
-
TRUNC(数字|列 [,保留小数的位数]):舍弃指定位置的内容;
-
MOD(数字 1,数字 2):取模,取余数;
一些其他的数字函数如下:
-
floor(n) 返回小于或是等于 n 的最大整数
-
ceil(n) 返回大于或是等于 n 的最小整数
-
abs(n): 返回数字 n 的绝对值
-
acos(n): 返回数字的反余弦值
-
asin(n): 返回数字的反正弦值
-
atan(n): 返回数字的反正切值
-
cos(n): 返回数字的余弦值
-
exp(n): 返回 e 的 n 次幂
-
log(m,n): 返回对数值
-
power(m,n): 返回 m 的 n 次幂
日期函数
如果现在要想进行日期的操作,则首先有一个必须要解决的问题,就是如何取得当前的日期,这个当前日期可用”SYSDATE”取得,代码如下
Select sysdate from dual;
除了以上的当前日期之外,在日期当中也可以进行若干计算:
-
日期+数字 = 日期,表示若干天之后的日期;
-
日期-数字 = 日期,表示若干天之前的日期;
-
日期-日期 = 数字,表示的是两个日期间的天数,但是肯定是大日期-小日期;
而且很多的编程语言之中,也都会提出一种概念,日期可以通过数字表示出来。
除了以上三个公式之外,也提供了如下的四个操作函数:
-
LAST_DAY(日期):求出指定日期月的最后一天;
-
NEXT_DAY(日期,星期数):求出下一个指定星期 X 的日期;
-
ADD_MONTHS(日期,月份的数量):求出若个月之后的日期,从下个月开始加;
-
MONTHS_BETWEEN(日期 1(大日期),日期 2(小日期)):求出两个日期之间所经历的月份;
转换函数
现在已经接触到了 Oracle 数据库之中的三种数据:数字(NUMBER)、字符串(VARCHAR2)、 日期(DATE),转换函数的主要功能是完成这几个数据间的互相转换操作,一共有三种转换函数:
-
TO_CHAR(字符串|列,格式字符串):将日期或者数字变为字符串显示;
-
TO_DATE(字符串,格式字符串):将字符串变为 DATE 型数据显示;
-
TO_NUMBER(字符串):将字符串变为数字显示;在一定程度上可以不写,可以实际直接使用数字字符串进行数学运算,依旧是可行的
通用函数
通用函数主要有两个:NVL()、DECODE(),这两个函数就是 Oracle 自己的特色函数
- NVL()
NVL(expr1,expr2):
如果 expr1 是 null,则用 expr2 值代替。
- NVL2()
NVL2(expr1,expr2,expr3);
如果 expr1 不是 null,用 expr2 值代替,否则 null 就用 expr3 代
替。
- DECODE()
DECODE()函数非常类似于程序中的 if…else 语句,唯一不同的是 DECODE()函数判断的是数值,而不是逻辑条件。
实际作用就是将查询结果进行指定的替换。
这种判断肯定是逐行进行判断,所以这个时候就必须采用 DECODE()函数,此函数的语法格式:
DECODE(数值|列,判断值 1,显示值 1,判断值 2,显示值 2,判断值 3,显示值 3,…) ;
DECODE()函数是整个 Oracle 之中最具有特点的函数,一定要将其掌握。