oracle学习笔记

熊孩纸 阅读:388 2022-03-21 18:45:42 评论:0

背景:最近在学习oracle,记录下一些有关oracle的知识点主要是sql语句,在这分享给大家,现在笔记还不全,只记录了一部分,我会定时来更新的。

一.Sqlplus命令

1、内容

Oracle安装完成之后会自动提供一个sqlpuls命令,直接运行即可。随后要求输入用户名和密码,密码不回显。

除了以上方式之外,也可以直接运行cmd,输入sqlpul命令.sqlpuls scott/tiger

1.查询语句:

select * from emp;

执行完成后发现显示的格式比较乱,混乱的原因是因为此时没有设置环境。

(1)设置每行显示的数据长度:set linesize 300;此种方法要收到窗口大小的控制。

(2)设备每页显示的条数 set pagesize 30;

(3)设置列占位置: col ename for a10;

这两个指令称为格式化指令。

现在使用的windows操作系统,但是orcale执行的时候是没有图像界面的,所有一般这样的情况下想编写程序代码,那么必须启动本地的记事本程序。

启动方法:

命令:ed 文件名称”(如果不写后缀,默认的后缀就是.slq,输入:ed mldn,如果电脑中没有此文件会弹出一下询问框。

 

 

打开记事本之后,就相当于进入到了一种程序阻塞状态,必须等待记事本关闭后才成执行。

随后要想执行文件中的命令,那么使用@文件名称”(默认找到.sql的文件)

     在整个oracle中提供了四个用户,那么可以在sqlplus中切换用户,切换的语法:

Conn 用户名/密码[as sysdba]

     如果现在使用的是sys用户登录,那么必须协商“as sysdba,否则无法登录。

范例:使用system登录。

      conn system/manager;

范例:使用sys登录。

      Sys/change_on_install as sysdba;

      之前执行了查询emp表数据的操作,现在在sys用户下,执行同样的命令。

Select * from emp;

SQL> select * from emp;

select * from emp

1 行出现错误:

ORA-00942: 表或视图不存在

出现表或试图不存在的错误,因为emp表属于scott用户,严格来讲emp表的名称是“模式名称.表名称”(后来随着发展,模块名称季候等同于用户名,所以emp表的完整名称是“用户名.表名称”,即:scott.emp).

所以查询需要 select * from scott.emp;

sqlplus命令里,除了可以使用oracle自己定义的命令之外,也可以利用host命令调用本机的操作系统命令。

范例:调用echo命令。

    Echo helloworld;

    Host echo helloworld;

范例:调用copy命令。

    Copy源文件路径 拷贝文件路径

    Host Copy源文件路径 拷贝文件路径

2、总结

1.格式化命令:

a) 设置每行的长度: set linesize 长度;

b) 设置每页的长度: set pagesize长度;

2.切换用户:

a) Conn 用户名/密码[as sysdba],如果是sys用户一定要写上sysdba

3.调用本机命令:host作为前缀。

二、Sql简介&数据表分析

1、本次知识点

1.认识一下sql的介绍

2.掌握scott用户的表结构

2、内容

2.1sql简介

Sql指的是结构化查询语言。在世界上80年代的时候基本上存在80多种数据库,每一种数据库都有自己的一套操作命令。

70年代末的时候IBM开发出最牛的sql标准,而oracle是世界上第一个支持sql语法的数据库。后来发展到今天,基本上所有的关系型数据库都支持sql语法了。

即:如果你本身擅长使用DB2数据库,那我们即可以很轻松的上手oracle(支持亿条数据)、mysql(支持400-500万条数据)sqlserver数据库(支持8千万条数据)

Sql语句没有这么复杂: selectfromwheregroup byhavinginsertupdatedeletecreatedropaltercrantrevokeorder by

严格来讲sql会分为三种类型

1.DML(数据操作语言,开发中使用的部分):主要指的是数据库的查询和更新操作

2.DDL(数据定义语言,开发前的设计):主要指数据对象的创建(表、用户),例如:createdropalter,这一部分操作需要使用到相关的设计范式。

3.Dcl(数据库控制语言,系统人员的工作):主要是进行权限的管理操作(需要结合用户来观察),此部分会用DBA负责。

2.2scott用户表的结构

 Scott用户一共有四张表结构

命令一:查询一个用户下的所有数据表;

Select  * from tab;

小计:显示当前登录用户:show user

命令:查询一个表的结构

Desc 表名称;

范例:desc dept;

2.3、简单查询

2.3.1 内容

范例:查询emp表中的全部记录

如果是全部记录指的就是所有的行和列的数据。简单查询是不能控制数据行列,如果是查询全部列,那么就使用通配符*”完成。

Select * From emp;

范例:查询每个雇员的编号、姓名、职位、工资

此时不在表示查询全部的数据列,只表示查询几个固定的数据列

Select empno,ename,job sal from emp;

通过以上的两个查询,就可以发现,所谓的简单查询就是查询所有记录。

范例:查询所有的职位信息

Select job from emp;

此时确实是查询出来所有job列的内容,开始里面发现有重复数据,如果要想去除掉重复信息,可以使用“DISTINCT”完成,此选项只能出现在select字句后面

范例:消除掉重 复数据

Select distIfnct job from emp;

此时的确消除掉了重复的内容,但需要说明的是,如果查询的数据是多个列的时候才可以消除。

范例:观察消除的问题

Select distinct empno,job from emp;

只有在多个列都重复的时候才会消除,一个列重复不会消除

除了进行简单的查询之外,也可以针对于查询的列的返回结构进行四则运算

范例:查询每个雇员的编号、姓名、基本年薪

Select empno,ename,sal*12  from emp;

但是这个时候发现查询出来的结果列上有一些标记不清楚,所以为了改善显示效果,可以使用别名定义

范例:定义别名

Select empno,ename,sal*12 incom from emp;

但是对于别名有一点说明,不建议使用中文

范例:要求显示出每个雇员的编号、姓名、基本年薪(每年可以领取15个月的工资,100元车补贴、100元的电话补贴,每年还有5个月的高温补贴200元)

Select  empno,ename,(sal*15+(200+100+100)*12+200*5) incom from emp;

如果执行的是四则运算的部分,那么依然采用先乘除后加减的操作形式完成

在使用select查询数据的时候,除了查询列实际上也可以设置一些常量,对于常量也有如下的三点说明:

1.如果常量是字符串,则要求使用“‘”声明,例如‘hello

2.如果常量是数字,则直接编写,例如10

3.如果常量是日期,则按照日期格式编写,使用“日--年”例如17-12-80

范例:直接查询常量

Select “雇员”,empno,ename from emp;

在进行简单查询操作里面,如果有需要也可以在select 字句里面使用“||” 连接查询结果.

范例:观察连接效果

Select empno||ename from emp;

现在相当于将empno ename连个列的内容合并为一个列了。

范例:转换格式

效果:雇员编号:7369,姓名:smith,收入:800

Select ‘雇员编号:’ || empno||’,姓名:’|| ename || ‘,收入:’ || sal from emp;

加别名:

Select ‘雇员编号:’ || empno||’,姓名:’|| ename || ‘,收入:’ || sal  info from emp;

select 字句中出现的任何字符串都要使用“‘”声明,而别名不需要。

2.3.2 总结

1.简单查询是将一张数据表中的所有数据行的内容都显示出来。

2.在查询语句中先执行from 字句确定数据来源(此时实际上是所有行和列的数据),而后利用select字句控制显示的数据列。

3.如果出现数据重复,可以使用distinct来消除掉重复的数据行显示。

4.Select 字句可以进行四则运算。

5.Sellect字句可以直接输出常量内容,但是对于字符串使用“’”、数字直接编写,日期按照字符串格式。

6.“||”负责进行输出的内容链接,但是需要注意的是,一般此类的操作很少直接在查询中出现。

2.4、限定查询(重点

Sql变为如下格式:

3控制要显示的数据列】select[DISTINCT] *|列名称[别名],列名称[别名]...

1确定数据来源】FROM表名称[别名]

2确定满足条件的数据行】[WEHERE  过滤条件(s)]

Select决定数据列,where决定数据行

如果想实现限定查询,那么需要掌握一系列的限定查询的符号,有如几种:

·关系运算符:><>=<=<>(!=);

·逻辑运算符:AND ORNOT;

·范围运算符:BEETWEEN...AND;

·谓词运算符:IN NOTIN;

·空判断:IS NULLIS NOT NULL;

·模糊查询:LIKE

2.4.1 关系运算符

关系运算符号主要是进行大小关系比较操作使用的。

范例:要求查询出所有基本工资高于1500的雇员信息

Select * from emp where sal>1500;

范例:查询smith的完整信息

Select * from emp where ename=’SMITH’

oracle数据库之中,所欲的数据是需要区分大小写关系的。

范例:查询工资是500的雇员信息

Select * from emp where sal=’5000’

发现=”可以在数字上使用,也可以在字符串上使用,在oracle之中为了方便开发所有的运算符不区分类型,而直接使用。

范例:查询职位不是销售人员的雇员编号、姓名、职位

Select empno,ename,job from emp where job!=’SALESMAN’;

Select empno,ename,job from emp where job<>’SALESMAN’;

所有的过滤都是采用条件的形式进行过滤。

2.4.2 逻辑运算符

如果说现在多个条件要进行连接,那么就需要根据要求选择是与连接还是或连接。

·与连接:所有的判断条件都要满足;

·或连接:若干个条件有一个满足即可;

范例:查询出工资范围在1500~3000的员工

Select * from emp where sal>=1500 and sal<=3000;

 

范例:查询工资大于2000或者职位是办事员的所有雇员信息。

Select * from emp where sal>2000 or job=’CLERK’;

在逻辑运算之中除了与和或的逻辑之外还会存在有一个非的逻辑,非得逻辑是,如果条件的逻辑结果是真,结果就是假,反之,如果条件的逻辑结果是假,结果就是真。

范例:工资小于2000的雇员信息

Select * from emp where sal<2000;

Select * from emp where not sal>=2000;

对于逻辑操作,有一点说明,不要写过于复杂的逻辑操作。

2.4.3 范围查询

在进行查询条件过滤的时候可以针对某一个范围的数据进行过滤,使用between and.

Between 最小值(数字、日期) and最大值

在此最小值(包含最小值)与最大值(包含最大值)之间的内容都满足条件。

范例:查询工资在1500~2000之间的雇员(包含15002000

select * from emp where sal>=1500 and sal<=2000;

Select * from emp where sal between 1500 and 2000;

注:第二个查询比第一个查询的性能高。

第一个查询需要匹配两个条件,第二查询需要匹配一个条件。

范例:查询所有在1981年雇佣的雇员。

emp表之中可以使用“hiredate”字段来描述雇佣日期,但是现在给出的是一个查询范围,所有应该设置出日期查询的最小值与最大值。本次的两个边界值:

1981-01-01:‘01-1-81’、‘01-1-1981

1981-12-31:‘31-12-81’、‘31-12-1981

Select * from emp where hiredate between ‘01-1-81’ and ‘31-12-81’;

此时所有在1981年雇佣的雇员信息全部显示,在使用between and时,日期和字符串时常用的比较方法,字符串也可以,但一般不用。

Select * from emp where ename between ‘FORD’ and ‘SMITH’;

虽然能查询来,但似乎意义不大、

2.4.4 空判断

空在数据库上解释为不确定的内容。但是需要注意的是,如果数字列上使用null那么绝对表示0。对于空的判断不能使用关系运算符判断。空的操作只能使用IS NULL,或者IS NOT NULL(NOT IS NULL).

范例:查询所有领取佣金的雇员信息(佣金存在,不为空)

Select * from emp where comm is not null;

Select * from emp where not comm is null;

任何的数据库,空的操作只能使用以上的两个标记来判断。

2.4.5 IN操作符

IN操作符类似于BETWWEN AND,但是BETWWEN AND是给了一个大的范围,而IN给出的是一个指定的可选范围。

范例:要求查询出的雇员编号是7369756677889999的雇员编号。

如果不使用in操作,可以使用或操作;

select * from emp where empno=7369 or empno=7566 or empno=7788 or empno=9999;

使用in操作:

select * from emp where empno in(7369,7566,7788,9999);

在指定值查询过程之中,in的操作是最简短的。

既然在指定范围里面使用in,那么如果不在指定的范围之中可以使用not in.

范例:要求查询出的雇员编号不是7369756677889999的雇员编号。

select * from emp where empno not in(7369,7566,7788,9999);

select * from emp where not empno in(7369,7566,7788,9999);

注意:关于not innull的问题:

在使用not in进行范围判断的时候,如果范围里面包含有null,那么不会有任何的结果返回。

范例:使用in操作中包含null--没有任何的影响。

select * from emp where empno in(7369,7566,7788,null);

范例: 使用not in操作中包含null--没有任何的影响。

select * from emp where empno not in(7369,7566,7788,null);

//输出未选定行

实际上使用where的最大用处就是控制显示的数据行,在简单一点:别显示全部数据行(因为要显示全部数据行,几乎是宣判了一个程序的死刑)。使用not in的目的是为了查询部分数据,但是如果有了null(某些数据不能为空),就变成了查询全部了。

2.4.5 模糊查询:LIKE

可以在数据库之中执行数据的模糊查询,在使用like的时候可以使用两个通配符,

_:匹配任意一位字符;

%”:匹配任意零位、一位或多位字符。

范例:查询姓名是以字符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的亮点说明

说明1like可以应用在各种数据类型上,不一定非要是字符串。

Select * from emp where ename like ‘%%’;

说明2:在使用like模糊查询的视乎,如果不设置查询关键字,那么表示查询全部数据。

Select * from emp where ename like ‘%%’;

虽然以上的代码可以查询全部数据,但是与不使用where字句相比,不使用where字句性能更好。

2.4.6 总结

1.where字句一般都写在From字句之后,但是紧跟着FROM字句之后执行。

2.Where字句控制显示的数据行的操作,而select字句控制着显示数据列的操作。

a) Select 字句执行顺序落后于where字句,所以在select字句中定义的别名无法再where字句中使用。

3.要求完整的掌握个个限定查询符号。

·关系运算符:><>=<=<>(!=);

·逻辑运算符:AND ORNOT;

·范围运算符:BEETWEEN...AND;

·谓词运算符:IN NOTIN;

·空判断:IS NULLIS NOT NULL;

·模糊查询:LIKE

4.使用like实现查询模糊查询的时候,如果不设置关键字(‘%%’),表示查询全部

5.使用not in查询是,查询范围里面不允许出现null,否则不会有任何查询结果返回。

2.5、查询排序

1.排序的使用

2.多个字句的关系

2.5.1 排序的使用

当使用查询操作的时候,默认情况下他会按照数据的插入顺序进行数据排序显示,如果要针对指定列内容实现排序,就必须采用order by字句完成。

Sql变为如下格式:

3控制要显示的数据列】select[DISTINCT] *|列名称[别名],列名称[别名]...

1确定数据来源】FROM表名称[别名]

2确定满足条件的数据行】[WEHERE  过滤条件(s)]

4确定满足条件的数据行】[ORDER BY  字段[ASC|DESC],字段[ASC|DESC]]

ORDER BY字句是在select字句之后执行,而order by字句可以使用select字句定义的别名。

对于排序的方式有两种:

1.ASC(默认,不写也是asc):按照升序的方式排列。

2.Desc:按照将序的方式排列

范例:查询所有的雇员信息,要求按照工资有高到低排序。

Select * from emp order by sal desc;

范例:查询所有销售人员的信息,要求按照雇佣日期有早到晚排序。(升序)

Select * from emp where job=’SALESMAN’ ORDER BY SAL DESC;

以上只是针对于一个字段的排序,那么也可以同时设置多个排序字段,

范例:查询所有的雇员信息,要求按照工资有高到低排序,如果工资相同,则按照雇佣日期有早到晚排序。

Select * from emp order by sal desc,hiredate asc;

Select * from emp order by sal desc,hiredate;

Order by 既然在select字句之后执行,那么就可以使用select字句定义的别名。

范例: select empno,ename,sal*12 income from emp order by income;

那么此时的程序就可以正常的调用select定义的别名,在整个sql查询结构中,只有order by字句能调用select定义的别名,其他的都不可以。

2.5.2 总结

1.使用order by查询的时候,排序是在最后完成的。

2.Order by字句是最后一个执行的字句

3.order by之中可以设置多个排序的字段。

4.Order by有两种排序模式,ascdesc.

5.Order by是唯一一个可以使用select字句定义别名的字句。

2.6、综合练习:基本查询

2.6.1 练习

1.选择部门30的所有的员工。

Select * from emp where deptno=30;

2.列出所有办事员(CLERK)的的姓名,编号和部门编号

Select ename,ename,deptno from emp where job=’CLERK’;

3.找出佣金高于新近的60%的员工。

Select * from emp where comm>sal*0.6;

4.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK);

select * from emp where (deptno=10 and job='MANAGER') OR (deptno=20 and job='CLERK');

5.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),即不是经理又不是办事员但薪金大于等于2000的所有员工的详细资料。

Select * from emp where (deptno=10 and job='MANAGER') OR (deptno=20 and job='CLERK') OR (job not in('MANAGER','CLERK') and sal>=2000);

6.找出收取佣金的员工的不同工作。

Select distinct job from emp where comm is not null;

7.找出不收取佣金或收取的佣金低于100的员工。

  Select * from emp where comm is null or comm<100;

8.心事不带有R”的员工的姓名。

Select * from emp where ename not like ‘%R%’;

9.显示姓名字段的任何位置包含A” 的所有员工的姓名,显示的结果按照基本工资由高到低排序,如果基本工资相同,则按雇佣年限有早到晚排序,如果雇用日期相同,则按照职位排序。

Select ename from emp where ename like ‘%A%’ order by sal desc,hiredate,job;

2.6.2 总结

1.清楚学习过的每一个字句的作用:selectFROMWHEREORDER BY.

2.多个条件判断的时候一定要使用逻辑连接,而且尽量使用()”做一个区分;

3.遇到问题慢慢的分析

2.7、单行函数

虽然各个数据库有自己的开发架构,但是对于所有的数据库而言,基本上对开发者就两块重要的内容:sql语句+单行函数。

所有的单行数据,严格来讲,程序都可以处理。

所谓的单行函数就是完成某一操作功能的函数,例如:转大写、或者说进行日期格式的装换等等,一般而言,单行数据的格式:“返回值 函数名称(参数)”。

单行函数按照类型分为以下几种:字符串函数、数值函数、日期函数、转换函数、通用函数。

2.7.1 字符串函数

字符串函数主要是处理字符串数据的(对于字符串的数据有可能是从列上找到的或者是直接设置的字符串常量),包含的函数有以下几种:

NO

函数名称

返回类型

描述

1

UPPER(|字符串)

字符串

将传入的字符串变为大写字母形式的字符串

2

LOWER(|字符串)

字符串

将传入的字符串变为小写字母形式的字符串

3

INITCAP(|字符串)

字符串

开头首字母大写,,其他的字符全部变为小写

4

LENGTH(|字符串)

数字

取得指定字符串的长度

5

SUBSTR(|开始索引,[长度])

 

 

字符串

进行字符串的截取,如果没有设置索引,默认从开始索引一直截取到结尾

6

REPLACE(|字符串,旧内容,新内容)

字符串

将指定字符串的数据以新数据替换旧数据

 

oracle中,所有的函数如果想进行验证,也必须编写sql语句。为了方便用户进行一些验证或者一些不需要查询标的查询操作,专门提供了一个dual的虚拟机表。

1.转大写、小写操作。

范例:观察基本操作

select upper(‘hello’) from dual;

select lower(‘hello’) from dual;

select lower(ename)from emp;

oracle中本身数据是区分大小写,但是为什么又需要提供以上的两个函数?

在项目开发中会发现有许多的代码本身是不区分大小写关系的,所以用户进行信息查询的时候也往往不会考虑到字母的大小写问题。

范例:有用户输入要查询的雇员姓名,而后显示雇员的完整信息

oracle里面要实现数据的输入操作,可以使用替代变量的方式完成,格式“&标记”。

select * from emp where ename=&inputname;

上面的&”表示信息的输入。

 

要想查询smith信息,直接输入smith,会报如上的错误,是因为少单引号,所有输入‘smith’就正确了,如下图。

 

但是按照我们的习惯,一般不会输入单引号,所以我们可以直接设上单引号。

 

问题来了,用户输入时不会考虑大小写,在输入小写的时候就不会查出数据,我们可以把输入的内容统一一下在查询。

 

一般在一些不区分数据大小写的情况下都会统一的将所有的内容转成大写或小写的数据,这就是upperlower的用处。

2.首字母大写其他字母小写。

范例:将所有的雇员姓名以首字母大写的形式保存。

select ename,initcap(ename) from emp;

运行结果如下:

 

 

3.取得字符串的长度

范例:基础操作

select length(‘helloworld’) from dual;

范例:查询雇员姓名长度为5的全部雇员信息。

select * from emp where length(ename)=5;

4.字符串截取

从指定位置截取到结尾:字符串substr(| 字符串,开始索引)

截取部分内容:字符串substr(| 字符串,开始索引,长度)

范例:验证函数

select substr(‘helloworld’,6) from dual;

select substr(‘helloworld’,1,5) from dual;

注:在程序之中所有的字符串的首字符索引都是0,但在oracle里面,所有的字符串的首字符的索引都是1,如果设置的是0,那么他也会按照1的方式来处理。

也就是说substr的下标从1开始,如果写成0,也会从1开始,即substr(‘helloworld’,1,5)substr(helloworld,0,1)返回的结果是一样的。

范例:要求截取每个雇员姓名的前三个字母

select  ename,substr(ename,1,3) from emp;

范例:取每个雇员姓名的后三位字母

如果要想取出后三位字母,那么首先必须知道截取的开始索引,因为每一个雇员姓名的长度是不一样的。

如果按照原始的正常思路,那么应该计算出截取的开始点,而这个开始点可以利用我们的字符串的长度得来。

select ename ,substr(ename,length(ename)-2) from emp;

对于oracle有点设计的比较厉害,就是在设置substr()函数的时候考虑到了由后截取的情况,也可以直接设置为负数,表示后面的第几位索引。

select ename ,substr(ename,-3) from emp;

此类的设置方式只有oracle数据库才有。

2.7.2 数值函数

数值函数主要是进行数字的处理,最为核心的数值函数一共有三个。

NO

函数名称

返回值

描述

1

ROUND(|数字[,小数位])

数字

实现数据的四舍五入,可以设置保留小数位

2

TRUNC(|数字[,小数位])

数字

实现数据的截取,即不进位

3

MOD(|数字,列 |数字)

数字

求模(计算余数)

 

1. ROUND()函数

范例:使用ROUND()函数

select round(789.567123) from dual;

如果没有设置小数点的保留位数,那么会直接不保留小数位进行进位。

范例:使用ROUND()函数

select round(789.567123,2) from dual;

如果设置了小数位,那么就会在指定的小数位上实现四舍五入。

范例:设置负数,往上进位

select  round(789.567123,-2) from dual;

返回800

select round(719.567123,-2) from dual;

返回700

如果设置为负数,那么就表示进行整数位的四舍五入。

2. TRUNC()函数

trunc()函数与round()函数的在使用形式上差别不大,唯一的区别在于trund()是不会进位的。

范例:

select trunc(789.567123),trunc(789.567123,2),trunc(789.5671232,-2) from dual;

3.求模函数

范例:

select mod(10,3) from dual;

在以后的学习工作中,见到round这个单词一般都表示四舍五入。

2.7.3 日期函数

如果要想处理任何日期,那么都有一个基本的前提,必须知道当前的日期是什么。如果要想取得当前的日期时间,在oracle中专门提供了一个伪列“SYSDATE(SYSTIMESTAMP).

范例:验证伪列

select SYSDATE from dual;

select SYSTIMESTAMP from dual;

范例:进一步观察伪列

select ename,job,sal,SYSDATE from emp;

SYSDATE伪列里面包含有日期时间的内容,只不过现在只显示了日期数据。如果清楚了当前日期时间的取得之后,还需要清除三个日期的操作公式:

(1).日期+数字=日期(表示若干天之后的日期)

(2).日期-数字=日期(表示若干天之前的日期)

(3).日期-日期=数字(天数)

范例:实现日期的基本操作

select SYSDATE-7,SYSDATE+120 from dual;

对于日期而言,由于每个月的天数是不同的,所以直接进行天数加法实现月数是不准确的。

范例: 要求查询出每个雇员的编号、姓名、职位、已经被雇佣的天数

select empno,ename,job,SYSDATE-hiredate day from emp;

如果直接使用天数来实现年或月的计算,那么最终的结果一定是不准确的。

为了准确的进行日期操作,在oracle里面提供有四个日期处理函数。

NO

函数名称

返回值

描述

1

ADD_MONTHS(|日期,月数 )

日期

在指定的日期上增加若干个月之后的日期

2

MONTHS_BETWEEN(|日期,列|日期)

数字

返回连个日期之间的所经历的月数

3

LAST_DTY(|日期)

日期

取得指定日期所在月的最后一天

4

NEXT_DAY(|日期,星期X)

日期

返回下一个指定的一周时间数对应的日期

 

1. ADD_MONTHS

范例: 在当前月下增加指定的月份

select add_months(SYSDATE,4) from dual;

2. MONTHS_BETWEEN

范例:计算每个雇员到今天为止雇佣月数

select ename,months_between(SYSDATE,hiredate) from emp;

3. LAST_DTY

范例:计算当前时间所在月的最后一天日期

select last_day(SYSDATE) from dual;

范例:要求查询出所有在雇佣所在月倒数第三天雇佣的雇员信息。

本题目最麻烦的问题是怎么知道倒数第三天,那么如果要想知道某一个日期的前三天,最好的办法就是日期减数字。

where里面需要的条件:雇佣的日期=所在月最后一天的日期减2

select ename ,hiredate from emp where hiredate=(last_day(hiredate)-2);

日期函数操作起来比较麻烦,但是利用日期函数操作的日期是最准确的。

4. NEXT_DAY

范例:验证next_day函数

查询出下一个星期二:

select next_day(SYSDATE,’星期二’) from dual;

5. 综合练习

范例:要求以年、月、日的方式计算出每一个雇员到现在为止的雇佣年限。

例如:7698的雇员BLAKE,雇佣日期是:1981-05-01,现在的日期是2015-08-11,此雇员到现在为止已经雇佣了34年、3月、10.本次额要求是很精确的,那么精确的时间只能使用日期函数来处理。

第一步:求出每一位雇员到现在为止雇佣的年份。

在整个oracle中提供了两种方式来计算年份:

方式一:(日期-日期=天数)÷365=(此种方式无法取出到闰年的时候)

方式二:MONTHS_BETWEEN(SYSDATE,hiredate) ÷12=年;

select empno,ename,hiredate,trunc(months_between(SYSDATE,hiredate)/12) from emp;

第二步:求出雇佣的月数

在计算年的时候使用了“总月数”÷12,但是计算的时候里面发现有余数,这些余数实际上就是剩余的月数。

SELECT empno,ename,hiredate,

TRUNC(MONTHS_BETWEEN (SYSDATE,hiredate)/12) year ,

TRUNC(MOD(MONTHS_BETWEEN (sysdate,hiredate),12) )month

from emp;

第三步:求出雇佣的天数

oracle中提供的计算天数的操作只用一种形式“日期1-日期2=天数”;

日期1:使用SYSDATE,表示当前的时间日期

日期2ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))

回避:由于时间跨度太长了,必须回避掉年和月的问题。

分析:雇佣日期+(雇佣日期到现在日期为止所经历的月数)

SELECT empno,ename,hiredate,

TRUNC(MONTHS_BETWEEN (SYSDATE,hiredate)/12) year ,

TRUNC(MOD(MONTHS_BETWEEN (sysdate,hiredate),12) )month,

TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) DAY

from emp;

2.7.4 通用函数

通用函数一般都是指的是oracle特色函数,主要有连个通用函数。

 

NO

函数名称

返回值

描述

1

NVL(| NULL,默认值)

数字

如果传入的内容是null,则使用默认数值处理,如果不是则使用原始数值处理

2

DECODE(|字符串 |数值,比较内容1,显示内容1,比较内容2,显示内容2...[,默认显示内容])

数据类型

设置的内容会与每一个比较内容进行比较,如果内容相同,则会使用显示内容进行输出,如果都不相同,则使用最后默认的信息输出。

 

1.处理null函数

范例:要求查询出每个雇员的编号、姓名、基本工资、佣金、年薪(佣金+工资)

select empno,ename,sal,comm,(sal+comm)*12 from emp;

 

运行后发现一部分的年薪为空,没有佣金的雇员就没有年薪存在了,因为佣金为空的时候所做的人合计计算结果都为空。为了保证数据的正确性,必须将null替换为0,那么这就属于NVL()函数的作用范畴了。

select empno,ename,sal,comm,(sal+NVL(comm,0))*12 from emp;

2.DECODE()函数

DECODE()函数类似于程序的if else,但与if else不同的是,此处不能够判断关系,只能够判断内容是否相同。

范例:将所有的职位信息替换为中文显示

select ename,job,DECODE(job,’CLERK’,’办事员’,’SALESMAN’,’销售’,’manager’,’经理’,’...’) from emp;

在一些笔试里面,DECODE()函数还是会被经常问到的。

2.7.5 总结

1.每一个函数都是完成一个独立的功能, 并且所有的函数都可以在sql语句中验证。

2.要记下每一个函数的函数的函数名即作用,这样在编写代码的时候就可以直接用了。

2.8、多表查询

2.8.1 多表查询的基本概念

在之前所做的全部查询里面都有一个共同特点,在FROM字句里面只设有一张数据表,如果要从多张数据表里面取出数据,那么旧数据多表查询,就是要在FROM后面设置多张数据表。

3控制要显示的数据列】select[DISTINCT] *| 列名称[别名],列名称[别名]...

1确定数据来源】FROM 表名称[别名],表名称[别名]

[2确定满足条件的数据行】WEHERE  过滤条件(s)]

[4 针对于返回结果进行排序】ORDER BY 字段[ASC | DESC],.....]

那么下面准备将emp表进行多表查询,在查询操作之前,首先

范例:统计emp表中的数据量

SELECT COUNT(*) FROM emp;---14行记录

 

范例:统计dept表中的数据量

SELECT COUNT(*) FROM dept;---4行记录

 

范例:实现多表查询

 SELECT * FROM emp,dept;

此时的确实现了多表查询,但是查询的结果发现了产生了56行的记录。如果按照集合的概念来说,是两个集合发生了乘积的管理,而这样的乘积关系在数据库上称为笛卡尔积问题。

如果说现在要想消除掉笛卡尔积的问题,那么必须要想办法向两张数据库表设置关系。

有可能在进行查询的时候有可能不同的数据库表会存在相同的列名称,那么在这种情况下就需要在列的前边加上表名称。

范例:消除掉积的问题

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

以上的代码实际上只是显示的消除了笛卡尔积,而在数据库的多表查询之中,笛卡尔积会一直存在,只要有数据表,那么一定会存在有笛卡尔积。

实验:多表查询所带来的问题

例如,在oracle数据库里面存在有sh用户,而且一般在设计标的时候往往同样的列名称都可能称为关联字段。

SELECT COUNT(*) FROM costs;

 

SELECT COUNT(*) FROM sales;

 

 SELECT COUNT(*) FROM sales,costs WHERE sales.prod_id=costs.prod_id;

以上的代码确实提供了消除笛卡尔积的条件,但是现在的问题是,执行的速度依然很慢,因为总的数据量750亿,所以要记住一个原则:多表查询性能是很差的,在开发当中尽可能不要使用多表查询,尤其是大数据量时。

但是在进行列访问的时候发现使用了表名称,严格来讲这样也不好,万一表名称很长呢?

在进行多表查询的时候一般都建议使用别名。

范例:使用别名

 SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;

任何情况下,如果要实现多表查询,永远都有一个前提:要关联的数据表一定要存在有关联字段或关联条件,不存在这些要求的,一定不能够使用多表查询。

范例:要求显示每一个雇员的编号、姓名、职位、工资、部门名称、部门位置。

1.确定要使用的数据表:

a) emp表:雇员的编号、姓名、职位、工资

b) dept表:部门名称、部门位置

2.确定已知的关联字段:

a) 雇员与部门:emp.deptno=dept.deptno;

第一步:显示每一个雇员的编号、姓名、职位、工资,只需要emp一张数据表即可。

SELECT e.empno,e.ename,e.job,e.sal

FROM emp e;

第二步:加入部门名称与位置信息的显示,一定要在FROM字句增加新的dept表名称,但是一旦增加了新的表,就必须消除掉两张表之间

SELECT job,COUNT(empno),AVG(SAL)

FROM emp

GROUP BY job;

存在

SELECT job,COUNT(empno),AVG(SAL)

FROM emp

GROUP BY job;

的笛卡尔积,使用 WHERE 字句消除显示的笛卡尔积。

SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno;

范例:要求显示每个雇员的编号、姓名、职位、工资、工资等级

1.确定所需要的数据表:

b) emp表:雇员的编号、姓名、职位、工资;

a) salgrade表:工资等级;

2.确定已知的关联字段:

a) 雇员与等级:emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

第一步:显示每一个雇员的编号、姓名、职位、工资,只需要emp一张数据表即可。

SELECT e.empno,e.ename,e.job,e.sal

FROM emp e;

第二步:引入salgrade表,一旦增加了新的表,就必须消除掉两张表之间存在的笛卡尔积,使用WHERE字句消除显示的笛卡尔积。

SELECT e.empno,e.ename,e.job,e.sal, s.grade  

FROM emp e,salgrade s

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

范例:查询每个雇员的编号、姓名、职位、工资、工资等级、部门名称

1.确定所需要的数据表:

a) emp表:雇员的编号、姓名、职位、工资

b) salgrade表:工资等级

c) dept表:部门名称

2.确定已知的关联字段:

a)  雇员与等级:emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

b) 雇员与部门:emp.deptno=dept.deptno

一般情况下如果是多个笛卡尔积条件都会使用AND进行连接。

第一步:显示每一个雇员的编号、姓名、职位、工资,只需要emp一张数据表即可。

SELECT e.empno,e.ename,e.job,e.sal

FROM emp e;

第二步:引入salgrade表,此时要加入WHERE 字句;

SELECT e.empno,e.ename,e.job,e.sal, s.grade  

FROM emp e,salgrade s

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

第三步:查询部门信息,增加dept表的查询,此时需要增加一个消除笛卡尔积的条件,与之前的条件使用AND连接。

SELECT e.empno,e.ename,e.job,e.sal, s.grade,d.dname

FROM emp e,salgrade s,dept d

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

AND e.deptno=d.deptno;

多表查询操作的使用:

1.没有关联字段或者关联条件的两张数据表是永远不可能实现多表查询的;

2.在进行多表查询这样的复杂查询是,强烈建议分步骤解决问题。

 

2.8.2 表连接

现在既然可以连接多张数据表进行数据查询,那么在进行表连接的时候就需要设置主控方。表的连接主要分为两种形式:

1.内连接(等值连接):所有满足条件的数据都会被显示出来。

2.外连接(左外连接、右外连接、全外连接):控制左表与右表的数据是否全部显示。

为了能够更好的描述当前的问题,下面想emp表之中增加一行记录,此记录属于没有部门的雇员;

INSERT INTO emp(empno,ename,job) VALUES(8888,'张三','CLERK');

1. 内连接(等值连接)

之前使用的都是等值连接

SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno;

 

如图只显示了14条记录,ename是“张三”的数据没有显示出来。

因为现在只有当“e.deptno=d.deptno”这个条件满足之后才会显示出对应的数据,如果此时数据为空,那么此条件无法满足,则数据无法显示。

如果想显示“张三”这条数据,需要使用外链接

2. 外连接

让等值判断左右两边有一边的数据可以全部显示出来,如果想要实现外链接,则需要使用(+)”的语法完成。

1.左外连接:字段=字段(+);

2.右外连接:字段(+)=字段;

范例:左外连接

SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno(+);

 

此时全部的雇员信息全部显示出来了。如图,现在ename是“张三”的这条数据就查询出来了。但是由于没有对应的部门数据,所以此行记录的部门数据为空。

范例: 右外连接

SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno;

 

如上图,此时发现部门的数据全部查询出来了,但是由于此部门没有雇员,所以雇员信息为空。

在实现外连接的过程之中,强烈不建议大家去刻意的区分左连接或者是右连接,一切以数据查询结果为主,如果发现数据查询结果之中缺少了内容,那么久利用外连接来控制。

范例:查询每一个雇员的姓名、职位、领导姓名

1.确定要使用的数据库表:

a) emp表:雇员的姓名、职位;

b) emp表:领导姓名(雇员姓名)

2.确定一直的关联字段:

a) 雇员与领导;emp.mgr=memp.empno;

第一步:实现emp表的自身关联查询。

SELECT e.ename,e.job,m.ename

FROM emp e,emp m

WHERE e.mgr=m.empno;

 

但是现在发现一个问题,此时的数据显示不完整,如果发现查询的数据不完整,那么就使用外连接。

第二步:使用外连接

SELECT e.ename,e.job,m.ename

FROM emp e,emp m

WHERE e.mgr=m.empno(+);

 

现在数据显示完整了。

这种自己关联自己的查询,但是不管是不是自身关联,程序认的只是FROM字句之后有多少张数据表,有多张表就必须编写消除笛卡尔积的条件。

2.8.3 SQL1999语法支持

之前给出的(+)”标记只是Oracle才有的,那么其他的数据库怎么办呢?所以可以使用SQL:1999语法来实现查询。整个SQL:1999语法定义的结构如下:

SELECT  [DISTINCT] * |[别名]

FROM 表名称1

[CROSS JOIN 表名称2]

[NATURAL  JOIN  表名称2]

[JOIN 表名称 ON(条件) | USING(字段)]

[LEFT | RIGHT | FULL OUTER JOIN 表名称2];

实际上以上的语法也是有多个部分所组成,下面拆分来观察:

1. 交叉连接:CROSS JOIN, 主要的功能是产生笛卡尔积,简单的实现多表查询。

SELECT * FROM emp CROSS JOIN dept;

2. 自然连接:NATURAL JOIN,自动使用关联字段消除笛卡尔积(一般关联字段是外检,但是此处是名字相同为主),属于内连接的概念。

SELECT * FROM emp NATURAL JOIN dept;

在返回查询结果的时候,默认情况下回将关联字段设置在第一列上,重复的列不在显示。

3. USING字句:如果说现在要一张表里有多个关联字段在,那么可以使用USING字句明确的设置一个关联字段。

SELECT * FROM emp JOIN dept USING(deptno);

4. ON字句:如果现在没有关联字段,则可以使用ON字句设置关联条件。

SELECT * FROM emp e JOIN salgrade s ON(e.sal BETWEEN s.losal AND s.hisal);

5. 外连接,在SQL:1999语法标准里面明确的定义了数据表的左外连接、右外连接、全外连接的操作。

范例:左外连接

SELECT * FROM emp LEFT OUTER JOIN dept USING(deptno);

范例:右外连接

SELECT * FROM emp RIGHT OUTER JOIN dept USING(deptno);

范例:全外连接

SELECT * FROM emp FULL OUTER JOIN dept USING(deptno);

只有在SQL:1999语法里面才明确的可以使用全外连接,但是这样的操作几乎是没有意义的。

2.8.4 数据集合操作

之前都数据多张数据表的关联操作,但是数据的结婚操作主要负责连接的是查询结果。对于查询结果的操作提供四中操作符:UNIONUNION ALLINTERSECTMINUS,利用这几个符号实现多个查询语句的连接。

3控制要显示的数据列】select[DISTINCT] *| 列名称[别名],列名称[别名]...

1确定数据来源】FROM 表名称[别名],表名称[别名]

[2确定满足条件的数据行】WEHERE  过滤条件(s)]

[4 针对于返回结果进行排序】ORDER BY 字段[ASC | DESC],.....]

UNION | UNION ALL | INTERSECT | MINUS

3控制要显示的数据列】select[DISTINCT] *| 列名称[别名],列名称[别名]...

1确定数据来源】FROM 表名称[别名],表名称[别名]

[2确定满足条件的数据行】WEHERE  过滤条件(s)]

[4 针对于返回结果进行排序】ORDER BY 字段[ASC | DESC],.....]


就相当于将多个查询结果链接为一个查询结果返回,那么也就有一个明确的一个要求,多个结果返回的列的结构必须相同。

范例:UNION操作

SELECT empno,ename,job FROM emp WHERE deptno=10

UNION

SELECT empno,ename,job FROM emp;

此时就是讲两个查询结果合并在一起了,但是UNION的操作特点是如果遇见了相同的内容,那么不会重复显示。

范例:UNION ALL

SELECT empno,ename,job,deptno FROM emp WHERE deptno=10

UNION ALL

SELECT empno,ename,job,deptno FROM emp;

UNION ALL也是将两个查询结果合并在一起,但是不会去掉重复的数据。

范例:INTERSECT

SELECT empno,ename,job,deptno FROM emp WHERE deptno=10

INTERSECT

SELECT empno,ename,job,deptno FROM emp;

返回两个查询的交集数据

范例:MINUS

SELECT empno,ename,job,deptno FROM emp WHERE deptno=10

MINUS

SELECT empno,ename,job,deptno FROM emp;

 

SELECT empno,ename,job,deptno FROM emp

MINUS

SELECT empno,ename,job,deptno FROM emp WHERE deptno=10;

 

返回第一个查询数据减去第二个查询数据的结果。

2.8.5 总结

1. 只要多表查询就一定存在笛卡尔积,所以优秀的系统设计的时候绝对不可能去考虑多表查询。

2. 多表连接查询时,必须存在关联字段或关联条件。

3. 大部分情况下使用的都是内连接操作,外连接Oracle提供的是”(+)”.

4. Oracle之外的数据库(SQL ServerMySQL),那么就必须利用SQL:1999语法实现外连接操作。

可以使用集合操作将多个查询结果合并到一起显示,但是要求,多个查询结果返回的结构必须相同。

2.9.1 统计函数

在之前使用过一个COUNT()”函数,此函数的功能是统计表中的数据量,那么在SQL只用定义了五个常用的函数:

统计个数:COUNT(*|[DISTINCT]字段)

MAX(字段)MIN(字段)

SUM(数字字段)AVG(数字字段).

范例:查询所有员工之中最高和最低工资。

SELECT MAX(sal),MIN(sal) FROM emp;

 

对于MAX() MIN()的操作除了可以使用数据之外,也可以使用日期。

范例:查询出所有雇员最早雇佣和最晚雇佣的雇佣日期

SELECT MAX(hiredate),MIN(hiredate) FROM emp;

 

范例:统计所有雇员的总工资和平均工资

SELECT SUM(sal),AVG(sal) FROM emp;

 

对于SUM()AVG()两个函数,只有是数值型数据都可以使用。

范例: 要求统计出雇员的平均服务年限。

SELECT AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12) FROM emp;

 

如果想去掉小数,可以使用TRUNC函数:

 

面试题:请解释COUNT(*)COUNT(字段)COUNT(DISTINCT  字段)的区别:

1. COUNT(*):明确的返回表中数据的个数,是最准确的。

2. COUNT(字段):不统计为null的数据个数,如果某一列的数据不可能为空,那么结果与COUNT(*)相同。

3. COUNT(DISTINCT 字段):消除掉重复数据之后的数据个数。

2.9.2 分组统计查询

在具体的讲解操作语法之前首先来思考一个问题:什么情况下可能分组?

例如:在上厕所的时候一定是男生一组排队,女生一组排队;

例如:在公司举办活动的时候,每个部门分为一组进行对抗;

例如:所有戴眼镜的同学一组,不戴眼镜的同学一组;

能够分组的时候往往是指的是部分数据具备某些共性。理论上一个人也可以进行分组,但是没有用。而要想分组,使用GROUP BY字句完成。

4控制要显示的数据列】select[DISTINCT] 分组字段 | 列名称[别名],列名称[别名]...

1确定数据来源】FROM 表名称[别名],表名称[别名]

[2确定满足条件的数据行】WEHERE  过滤条件(s)]

[3针对数据进行分组】GROUP BY 分组字段,分组字段。。。。]

[5 针对于返回结果进行排序】ORDER BY 字段[ASC | DESC],.....]

范例:要求按照职位分组,统计出每一个职位的名称、人数、平均工资;

SELECT job,COUNT(empno),AVG(SAL)

FROM emp

GROUP BY job;


查询结果如下:

 

 

范例:要求查询出每个部门编号,以及每个部门的人数、最高与最低工资

  

SELECT deptno,COUNT(empno),MAX(sal),MIN(sal)

FROM emp

GROUP BY deptno;

查询结果如下:

 

所选择的jobdeptno连个字段上都有重复数据。

以上实现了分组操作,但是对于分组操作严格来讲还是存在一些使用上的限制的:

限制一:没有编写GROUP BY 字句的时候(全表作为一组),那么SELECT字句之中,只允许出现统计函数,不允许出现任何的其他字段。

错误的代码

正确的代码

SELECT COUNT(empno),ename FROM emp;

 

SELECT COUNT(empno) FROM emp;

 

限制二:在使用GROUP BY字句分组的时候,SELECT字句中只允许出现分组字段,其他字段不允许出现。

错误的代码

正确的代码

SELECT job,COUNT(empno),ename FROM emp  GROUP BY job;

 

SELECT job,COUNT(empno) FROM emp  GROUP BY job;

 

限制三: 统计函数允许嵌套查询,但是在嵌套后的统计查询中,SELECT字句里面不允许出现任何的字段,包括分组字段,只能够使用嵌套的统计函数。

错误的代码

正确的代码

SELECT deptno,MAX(AVG(sal)) FROM emp  GROUP BY deptno;

嵌套之后不允许出现任何的字段

SELECT  deptno,AVG(sal) FROM emp  GROUP BY deptno;

SELECT MAX(AVG(sal)) FROM emp  GROUP BY deptno;



 


标签:oracle
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

关注我们

一个IT知识分享的公众号