oracle数据库 函数面试,Oracle数据库与SQL语言总结【面试考点】

原标题：Oracle数据库与SQL语言总结【面试考点】

Mysql基于数据库的管理机制

Oracle基于用户的数据库管理机制

创建一个信息系统，给你分配一个用户，创建空间、创建表

Oracle版本：

Oracle10g(10.21)官方文档:支持xp不支持win7/win8 linux unbutu

Oracle11g(11.2)支持以上

10.21版本是经典版本，两个差别很多

Oracle服务器时实例和数据库组成

一个实例对应一个oracle数据库

将物理文件的集合映射到内存

集群 ： web tomcat6.0

数据库是物理磁盘文件的集合，实例访问数据库

Sql基本命令：

Select * from database

Set linesize 140

Set pagesize 140

140行另一页显示

Host cls 清屏

查找有哪些表：

Select * from table

(table 超级字典 超级管理员分配给普通用户的资源，系统创建的时候已经创建好)

查看字段：

Desc dept

字段的格式：

第一个字段是所在部门编号，第二个字段是名称，第三个字段是所在城市

查看表结构：

Desc data_emp

以员工表为例 data_emp

1、查询所有列：select * from data_emp;

2、查询部分的列：select name , age, salary form data_emp;

3、查询别名：

Select dpartNum as “员工编号”, name “姓名”, salary 月薪，daptName “部门 名称” from data_emp;

如果有些如错误，内容比较对，重复输入比较麻烦，这时使用命令：ed (edit)编辑，默认打开记事本，并且将上次的命令在记事本中显示，在记事本中完成修改之后，回到命令框中会有反斜线出现：表示重复执行上次命令。

查询别名带表达式：

Select dpartNum as “员工编号”, name “姓名”, salary*12 年薪，daptName “部门 名称” from data_emp;

4、查询重复列 检索部门编号 distinct去掉重复列

Select distinct depart from data_emp;

SQL大小写不敏感。可以在一行或者多行写，关键字不能缩写也不能分行，个句子一般要分行写，使用缩进提高语句的可读性。

控制定义： NULL 捣蛋鬼

控制不是空也不是0，是无效的，未指定的，未知的不可预知的值 NULL ！= NULL

注意：(conn表示奖金)

Select empNum,name,salary*12 年薪，(salary*12 + conn) 年收入 from data_emp;

这时候，会有人显示没有年收入，但有年薪。这是因为这里的部分人没有奖金，但是任何值NULL与运算都是NULL所以这里没有年收入。

所以此处需要进行NULL修正，也就是遇空修正。

应用一个修正函数：nvl(conn,0) 意思就是将conn 修正为0；

Select empNum,name,salary*12 年薪，(salary*12 + nvl(conn))即可完成需求。

在看一个NULL ！= NULL的案例：

查询奖金为空的员工：

Select * form date_emp where conn is NULL; 正常显示

Select * form date_emp where conn is not NULL; 正常显示

Select的解锁列可以来自任何列

连接：

将任何两个列可以通过|| 连接起来显示

例如：select “111’’ || ‘’222” “连接”，name, id from data_emp;

虚表(伪表)概念：

查询连接的不依赖于任何一个业务表 关键字 dual

Select “111” || “222” from dual;

Select sysData from dual;

SQL与sqlplus

SqlPlus通sql语言访问oracle服务器，服务器将查询结果通过sqlplus命令显示给salplus浏览器，最后将格式化报表反馈给客户端

Sqlplus与SQL命令：

Sql是一种语言，ANSI标准99，关键字不能缩写，使用语句控制数据库表中的定义信息与表中的数据；

SQLplus是一种环境，oracle特性之一，关键字可以缩写，命令不能够改变数据库中的值，用于集中运行。

Select/update/delete

Ed/set/col(sqlplus 工具命令)

Help topic将查看所有的sqlplus命令

使用： ？ edit (命令) 查看如何使用

条件查询：

Select ….form data_emp where colname = 20; 查看行名为20的…..所有数据

Select …. From data_emp where colname between 100 and 200; 100到200之间的数据。

Select …fron data_emp where colname in (10,20); 查看colname是10和20 的两种的

再将 = 变换为 > < 即可 所有语法！

获取日期格式：

Select sysdate from dual;

获取系统配置参数：

Select * from v$nls parmaters; 获取系统配置参数

修改：

Alter session set MLS_DATE_FORMAT = “DD_Mon_RR”;

Alter session set MLS_DATE_FORMAT = “yyyy_mm_dd”;

查找的时候：必须按照特定格式，非中国人习惯的格式

Select * from emp where WIREDATE < ’08-09月-01’;检索名中日期是格式名敏感的。

Sql语句中字符串是使用单引号

薪水在1000到2000的之间的员工

Select * from data_emp where salary >1000 and salary <2000;

Select * from data_emp where salary between 1000 and 2000;

查询是10和20号部门的员工信息：

Select * from data_emp where dapartNum in (10 ,20);

查询不是10和20号部门的员工信息：

Select * from data_emp where dapartNum not in (10 ,20);

再看：查询一个员工信息在10 20 或者不在任何部门的员工信息：

Select * from data_emp where dapartNum in (10,20,null);

Select * from data_emp where dapartNum not in (10,20,null) ; 不是10 20 也不是任何部门

Null第三点：如果集合里面有空值，不能使用not in 但可以使用 in查询

NULL前两点：NULL不等于0 NULL不能使用 ！= 操作，而是使用 is not NLL

LIKE模糊查询：

使用like运算查询类似的值，选择条件可以包括字符或者数字；

%代表零个或者多个任意个字符

—代表一个字符

查询名字以S开通的员工信息

Select * from data_emp where name like ‘S%’;

查询员工名字是四个字符的Select * from data_emp where name like ‘-----’;

查询名字中带有转义字符的员工信息：

查询名字中带有下划线的员工信息

Select * from data_emp where name = ‘%\_%’

有关排序：

使用order by 默认是从小到大，使用order by desc 将是降序 从大到小

Select * from data_emp order by salary;

Select * from data_emp order by salary desc, nameNum desc;

2、order by 后面可以跟列名 表达式 别名 序号

3、order by 后面可以跟多个列

4、NULL :如何将NULL排在最后面

按照奖金进行排序查询，员工信息

Select * from emp order by comm desc nulls last;

Sql函数

函数分为单行函数和多行函数

单行函数：字符、通用、数值、转换、日期五类函数

字符函数：

大小写控制函数：

LOWER UPER INITCAP(首字母大写)

Select lower(‘hEllo wOrld’) as 转小写，upper(’hello world’) 转大写，initcap(‘hello’) from dual

字符控制函数：

CONCAT

Select concat(‘aaa’ ,‘bbb’)from dual; 三个不行 只能连接两个 可以嵌套进行三个

Select concat(‘aaa’ ,concat(‘bbb’,‘vvv’)) from dual;

SUBSTR

Substr从a字符串的第b位开始取 Substr从a字符串的第b位开始取c个

Select substr from dual; 结果为defg

Select substr from dual; 结果为de

LENGTH/LENGTHB 求 字符和字节 数 一个中文字符是两个字节

Select length(‘中国abc’),字符数，lengthb(‘中国abc’) 字节数 from dual;

结果是5、7

INSTR(‘母串’，‘子串’); 从母串中查找子串的位置

Select instr(‘abcdefghhhh’,’hhhh’) from dual;

LPAD | RPAD 填充

Lpad(‘abcd’,10,’*’) 给左边填充*使够十个 rpad(‘abcd’,10,’*’)给右边补*

Select Lpad(‘abcd’,10,’*’),左填充 ，rpad(‘abcd’,10,’*’) 右填充 from dual;

TRIM

Select trim(‘H’, from ‘HellHoeHHHHHH’) from dual;去掉前面的H和后面的H 中间的不管，可以是字符也可以是空格，一般就是为了去掉空格而设计的。

REPLACE 替换

Replace(‘abcde’,b,m)将字符b替换为m;

数字函数：

Round(45.926,2) 四舍五入保留两位有效数字 45.93

Trunc(45.926,2) 截取，保留两位 45.92

Mod(1600,300) 求余 100；

oracle日期

select to_char(sysdate,’yyyy-mm—dd hh24:mi-ss’ from dual;

强转为字符串并且按照中国人的格式显示日期和时间

Mysql日期

Date datetime……..多种类型

日期的运算

计算多少天多少周多少年

Select name, (sysdate-hrdate) 天，(sysdate-hrdate)/7 周，(sysdate-hrdate)/30 月，(sysdate-hrdate)/365 年from data_emp ;员工来了….年月日的时间

两个日期之间可以相减，返回值是天数.

求昨天 今天和 明天

Select sysdate-1 昨天， sysdate 今天，sysdate+1 明天 from dual;

使用to_char(sysdate-1,’yyyy—mm—dd’)改为中国习惯

精确的计算员工的入职月数 两个日期相差的月数

Select name 姓名， (sysdate-hrdate)/30 粗算月,Month_between(hiredate,sysdate) 入职月精确数 from date_emp;

在sql语句中使用if_then_else逻辑

两种方法：

1、 Case表达式，sql99的语法，类似于basic,比较繁琐

2、 Decode函数，oracle自己的函数，类似于java,比较简介

涨工资：总裁涨1000 经理涨800 员工涨500

Select last_name ,job_id 工种,salary 涨前工资 ,

(Case job_id when ‘president’ then salary+1000

When ‘manager; then salary+800

When ‘ordinary’ then salary+500’

Else salary end) 涨后工资，from data_emp;

使用decode函数：

Select last_name,job_id,salary 涨前工资,

Decode(job_id, ‘president’, salary+1000,

‘manager; then salary+800,

‘ordinary’ then salary+500’

Salary) 涨后工资，

From data_emp;

多行函数：分组函数不是难，而是很多情况下不知道使用分组函数，或者说想不到使用分组函数

求平均、平均奖金……(avg count max min sum)

分组数据

分组过滤

使用group by进行分组，使用having子句进行过滤分组结果集

求员工表中最高工资、最低工资、平均工资 所有工资：

Select max 最高工资，min 最低工资，avg 平均工资，salary 工资 from dat_emp;

使用nvl(comm,0)修正函数，将comm奖金修正为零

求出员工表中各个部门的平均工资(分组数据)

Select---

From----

Where colum >20

Order by--------

Group by-----------

Group by是检验数据库实力的一个标准

Select deaprtNum, avg(salary) from data_emp group by departNum;

按照系别分组求从员工信息表中求取每个组的平均工资

Select deaprtNum, avg(salary) from data_emp order by name_id group by departNum;

检索的数据列即紧跟select后面的数据，必须是分组group byz分组后的，如果没有在分组中，语法就错误。

Group by 后面可以跟多个列

这个问题很多人都出错

分组过滤案例：

查询平均工资在2000以上的部门

分析：最终落脚点是部门，所以需要用到分组，外加大于两千的条件，即分组过滤

Select departNum, avg(salary) from emp group by departNum having avg(salary) > 2000;

Where子句与having 子句区别：

案例：

求10号部门的平均工资

先分组后过滤

Select departNum , avg(salary) from data_emp group by departNum having departNum = 10;

先过滤，后分组

Select departNum, avg(salary) from data_emp where departNum =10 group by departNum;

致谢：传智播客

本文是在B站学习数据时期的笔记，部分内容为了求做笔记速度，比较仓促，基本根据上下文及字面意思加上理解，不会影响阅读！返回搜狐，查看更多

责任编辑：