Oracle基本语法查询语句

Oracle基本查询语法

本文含含: 基础/基本查询/排序/函数(单/多)

一.前言知识

1.oracle如何操作硬盘上的文件,最终都是以进程方式操作硬盘上的文件,最基本进程:读 写 ,还有其他客户端访问内存中的数据库实例,实例在内存中有很多块,小方块取决于客户端的个数,大方块叫做SGA:system global area 系统全局区域,一个内存实例只有一个,

小方块叫PGA:process global area 进程全局区域,(见图)

在写数据时,客户端操作的PGA先写到SGA,SGA再统一写到硬盘(分两阶段提交),可以减少访问数据库的次数,提高写入速度

2.oracle的事务提交之后,数据不一定会保存下来,临时表的数据是会被删除的

//回滚使用rollback; ,需要事务,

Oracle开启事务是自动默认开启的,我们的操作本身就在事务中,

Oracle数据库事务默认是不自动提交.

oracle中事务提交后,也可以撤销,通过闪回方式(flashback) ,

mysql开启事务是手动开启, start transaction ,commit提交

MYSQL数据库事务默认是自动提交的.

二.语句基础知识

1.学前基础知识:

1.字符串大小写,日期格式,都有严格格式要求.

2.查看设置: select * from v$nls_parameters; //查看当前设置:字符集,日期格式 ........

修改: alter session/system set NLS_CALENDAR='yyyy-mm-dd'; //设置日期格式

Oracle显示会自动分页, 设置分页大小: set pagesize 20;

连接oracle:sqlplus scott/scott@192.168.200.128/orcl

所有表: select * from tab;//列出当前用户下的表和视图

spool d:\基本查询.txt //录屏语句,会把命令窗体的内容给记录下来

show user; //查询当前用户

desc emp //查看表的结构

/ //表示执行上一条语句

host cls //Windows清屏

host clear //linux清屏

lsnrctl statusstart /stop; //oracle的监听器状态 查看,启动或关闭

netca可视化监听配置

show linesize; //查看当前显示的行宽

set linesize 150; //设置行宽

clo(colum缩写) ename for(format) a8 // 设置列宽, 此处设置名字(字符)这个列宽度为8, for表示格式

col sal for 9999; //设置数字的宽度 ,一个9代表一个数字,

set time on //可以让sql语句前显示时间,如下:

11:23:21 SQL> set time off

set timing on //可以开启时间记录,记录程序执行时间

2.字符串 :

字符串可以是select 列表中的一个字符,数字,日期; 日期和字符只能在单引号中出现,

双引号用于列的别名;

3.区分sql语句 还是sqlplus语句

sqlplus是oracle提供的命令行工具.在此工具中可以执行sql语句,

sql语句的关键词不能使用缩写,如select.insert,Update,delete都是sql语句,

sqlplus语句的关键词可以缩写,如 ed, c , for col ,desc,

isqlplus是sqlplus的网页版

4.修改错误语句:c代表change命令

select *  

froo emp ; //from输入错误, 修改错误字符如下:

第2行出错了, 输入:2 //回车;定位到第二行

输入: c/froo/from 回车, c /修改谁/改成谁

输入 / 执行上一条语句即可

5..乱码处理:

1.查看服务器端编码

select userenv('language') from dual;

我实际查到的结果为:AMERICAN_AMERICA.ZHS16GBK

2.执行语句 select * from V$NLS_PARAMETERS

查看第一行中PARAMETER项中为NLS_LANGUAGE 对应的VALUE项中是否和第一步得到的值一样。

如果不是，需要设置环境变量.否则PLSQL客户端使用的编码和服务器端编码不一 致,插入中文时就会出现乱码.

3.设置环境变量

计算机->属性->高级系统设置->环境变量->新建

设置变量名:NLS_LANG,变量值:第1步查到的值， 我的是

       SIMPLIFIED CHINESE_CHINA.ZHS16GBK

6.修改oracle密码

1.管理员登录 : sqlplus system/system as sysdba;

2.解锁: alter user scott account unlock/lock ;

3.改密码: alter user scott identified by 新密码;

7.用户查询

select * from dba_users; 查看数据库里面所有用户，前提你是有dba权限帐号，如sys,system

select * from all_users;  查看你能管理的所有用户！

select * from user_users; 查看当前用户信息 ！

三.查询语句

1.,基本查询

select * from emp; //查询所有, * 表示所有

select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp; //通过列名查询指定列

select ename,job,sal from emp;

//算数计算

select empno, ename, sal,sal*12 from emp;//查询工号,姓名,月薪,年薪

2..空值问题 :空值是无效的,未指定的,未知的或不可预知的值,不是空格或者0;

1.null永远 != null

2.包含null的表达式都为null; 因此判断一个值是否为空,不能用 = 或 != ;

enameis null; 或 ename is not null;

3.如果集合中有空值,不能使用not in(set), 但可以使用 in(set)

4. oracle中,空值最大,因此降序排序空值在最前,升序则在最后,

降序使用 nulls last,会让null值排后面,

升序使用 nulls first ,会让空值排前面.

5.组函数（多行函数）自动滤空;可用滤空函数来屏蔽他的滤空功能

3..虑空函数: nvl(a,b) //即当a的值为空时,返回b, a不为空返回a本身,

nvl2

//错误查询 奖金为空的人没有年收入: 查询员工,姓名,月薪,年薪,奖金,年收入

//select empno,ename,sal,sal*12,comm, sal*12+comm from emp; //因为含null的表达式都为null

select empno,ename,sal,sal*12,comm, sal*12+nvl(comm,0) from emp; //正确查询,使用虑空函数

4..起别名:

select empno as "员工号", ename "姓名" ,sal 月薪 ,sal*12,comm,sal*12+nvl(comm,0) from emp; //as 用于起别名,可以省略!

//普通别名可以不加双引号, 若别名中有 空格/关键字/特殊字符/数字, 需要加"",

5.去重复记录:select distinct deptno from emp;

select distinctdeptno ,job from emp; //distinct 作用于后面所有列,这些列组合起来是不一样的,那就算是不重复的,

6.连接符: ||

concat函数 //连接字符串

select concat('Hello',' world'); //这是错的

// 由于oracle遵循sql99标准,没有from 不行,但from后面是表,如果操作的数据跟表没有关系,就使用 from dual; 做占位符.

dual表,仅仅是为了满足oracle的语法要求,是伪表,占位用的, 同样有伪列; 如:

select concat('Hello',' world') from dual;//这是对的.

select 3+2 from dual;

查询员工薪水:

select ename||'的薪水是'||sal 信息 from emp; //连接字符串,日期和数字只能用单引号,

7.where 语句若有多条件时,解析的顺序是从右往左.

因此,对于and条件,应该把为假的条件放右边,这样就不判断左边的了,,短路,提高效率

对于 or 尽可能把为真的条件放右边,

四.条件过滤

1.where条件筛选

select * from emp where ename='KING'; //字符串区分大小写

select * from emp where hiredate='17-11月-81'; //默认格式: DD-MM月-YY;(设置格式见上面)

2.比较运算: =, >, >=, <, <= , <>或!=

赋值使用 := 符号 如 : a number := 0 ; //变量名在前,变量类型 := 变量值

between.... and ... 在两个值之间,含边界, 必须小值在前,大值在后, 日期也是一样小值在前.

in(set) 等于值列表中的一个

like 模糊查询

is null ,判断空值

//查询薪水 在1000~2000之间

select * from emp where sal>1000 and sal<2000;

select * from emp where sal between 1000and 2000;

//查询10号20号部门的员工,或查询此2部门之外的员工

select * from emp where deptno not in(10,20);

注: 3.如果集合中有空值,不能使用not in(set), 但可以使用 in(set)

select * from emp where deptno not in(10,20,null); //错误语句,

//模糊查询 % :表示任意个字符 _:表示一个字符

select * from emp where ename like 'S%'; //查询姓名s开头的员工;

select * from emp where ename like '____'; //查询姓名4个字的员工

//查询姓名含有"_"下划线的

使用 escape 声明 转义字符 ,习惯用 \ ,

select * from emp where ename like '%\_%'escape '\';

3.逻辑运算符 注:where 语句若有多条件时,解析的顺序是从右往左.

and 逻辑并or 逻辑或not 逻辑否

如: where condition1 and condition2

where condition2 and condition1 是两条不一样的语句 ,但结果一样,

3.排序order by 此句在select语句结尾

1.ASC 默认升序(ascend) DESC(descend)降序

order by 后面可以跟 : 列名, 表达式 , 别名 , 序号

select empno ,ename ,sal,sal*12 from emp order by sal*12 desc; //表达式做排序条件

select empno ,ename ,sal,sal*12 年薪 from emp order by 年薪 desc; //别名

select empno ,ename ,sal, sal*12 from emporder by 4 desc; //按序号, 序号须在有效范围内

2.. 多个列排序

order by 作用于所有列先按左边(最近) 排, 相同了,再按右边排

//降序只作用于最近列

select empno ,ename,sal,deptno from emp order by deptno,sal; //默认升序

select empno ,ename,sal,deptno from emp order by deptno,sal desc;//降序只作用于最近列

select empno ,ename,sal,deptno from emp order by deptno desc, sal asc;

排序的时候,列中如果有空值,采用降序,空值会排在前面,

原因:oracle中空值最大

解决: null last 或null last

select * from emp order by comm desc nulls last;

五.函数

单行函数:只对一行进行变换得到一个结果,如nvl(), concat()

多行函数:对多行操作产生结果,如:max()等

1.单行函数:

字符,数值,日期,转换,通用,条件表达式

1.字符函数 -单行

1.大小写控制函数:lower , upper , initcap(首字母大写)

select lower('Hello world') 转小写,upper('Hello world') 转大写,initcap('hello') 首字母大写 from dual;

2. 字符控制函数

concat(a,b) 连接拼接a,b两个字符串

substr 求一个串的子串 substr(a,b) 从a中第b位取到最后

substr(a,b,c) 从a中 第b位 取c个

length(s) / lengthb(s) 长度:字符数 /字节数

instr(a,b) 在一个串中查找子串 instr(a,b) 在a中查找b,返回下标;

lpad(a,b,c) | rpad(a,b,c) 左填充 右填充 对a填充, 填充b位, 用c填充

trim 去掉前后指定字符

replace(a,b,c) 替换a中的b,用c替换

举例:

select instr('helloworld','ll') from dual; //查找子串

select substr('helloworld',3,3) from dual; //截取子串

select length('被in个') 字符,lengthb('北京') 字节 from dual; //长度

select lpad('abc',10,'*') 左, rpad('asd',10,'*') 右 from dual; //左右填充

select trim('h' from 'helloh') from dual; //去掉前后指定字符

select replace('hello','l','*') from dual; //替换

2.数值函数-单行

round(a,b) 四舍五入,对a保留b位小数,

round(45.926,-1) 为50 , round(45.926,-2) 为0

trunc(a,b) 截断,保留几位小数, 对日期也可以截断

trunc(45.926,0)为45 trunc(45.926,-1) 为40 , trunc(45.926,-2) 为0

3.日期函数-单行

months_between(a,b) 计算两个日期差的月数

add_months(a,b) 向指定日期a 加上若干月数b

next_day(a) 指定日期的下一个星期几是几号

last_day(a) 本月最后一天

round(sysdate,'month') 日期四舍五入:按月四舍五入

trunc 日期截断

select now(); //mysql中查询当前时间

select sysdate from dual; //查询当前日期

举例:

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; //日期和时间

1.日期运算: 只有加减 ,且不允许 日期+日期

select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;

2.计算员工的工龄： 天 星期 月 年

select empno,ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp;

3.months_between(a,b) 计算两个日期差的月数

select ename,hiredate,(sysdate-hiredate)/30 ,months_between(sysdate,hiredate) from emp

4.select add_months(sysdate,53) from dual; //53个月后的日期

5. select next_day(sysdate,'星期三') from dual; //下一个星期三是几号

6.select round(sysdate,'month'), round(sysdate,'year') from dual; 四舍五入

4.转换函数: -单行

1.隐式 :oracle自动完成数据类型转换

2.显式:手动完成,需要指定格式

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day')from dual;

//把日期按格式转换成字符串,由于里面含文字,不是格式一部分,用""括起来

//查询员工薪水：两位小数，千位符，本地货币代码

select to_char(sal,'L9,999.99')from emp; //把数字按格式转成字符串

5.通用函数-单行

1.nvl(a,b) 当a为空时,返回b, 通用于任何类型

2.nvl2(a,b,c) 当a为空返回c , 否则返回b

3.nullif(a,b) 当a=b时,返回null ,否则返回a,

4.coalesce(a1,a2,a3,...) 从左往右,找到第一个不为空的值

举例:select sal*12 + nvl2(comm,comm,0) from emp;

select nullif('abc','abc') 值 from dual;

select nullif('abc','abcd') 值 from dual;

select comm,sal,coalesce(comm,sal) "第一个不为null的值" from emp;

6.条件表达式-单行

在sql语句中使用if - else 逻辑

两种方式: case表达式:sql99的语法,类似basic,比较繁琐

decode函数:oracle自己的语法,类似java,简单

decode( 表达式, 条件1,值1,条件2,值2,...., default ) from 表;

举例: 1.select empno,ename,job,sal 涨前,

casejobwhen 'PRESIDENT'then sal+1000

when 'MANAGER' then sal+800

else sal+400

end 涨后 from emp;

2. select empno,ename,job,sal 涨前,

decode(job,'PRESIDENT',sal+1000,

'MANAGER',sal+800,

sal+400) 涨后 from emp;

2. 多行函数

1.组函数: 分组函数作用于一组数据,并对一组数据返回一个结果

avg count max min sum

//组函数有自动虑空功能,不计算为空的内容,可以滤空函数来屏蔽他的滤空功能

举例:

count(distinct expr) //返回表达式非空且不重复的记录数

select sum(sal) from emp; //求工资总额

select count(*) from emp; //总人数

select sum(sal)/count(*) 一,avg(sal) 二 from emp; //平均工资 两种方式

select sum(comm)/count(*) 一,sum(comm)/count(comm)二,avg(comm) 三 from emp;

//三种方式计算奖金, 后两中方式结果相同 , 因为只有4个人有奖金, 因为有null, 具体问题具体分析

select count(*), count(nvl(comm,0)) from emp; //结果相同了,虑空了

2.分组函数 group by

注:1.group by先按照第一个列分组；再按照第二列分组；以此类推

2.所有没有包含在组函数中的列,必须写在group by 后面, 不写group by则错

如 : select a,b,c 组函数(X) from 表 group by a,b,c,d;

举例:

//求每个部门的平均工资

select deptno,avg(sal) from empgroup by deptno;

//按不同部门的不同职位 计算总工资

select deptno,job,sum(sal) from empgroup by deptno,joborder by 1;

3.having 过滤分组数据

1.where和having的区别：where后面不能使用多行函数

行已经被分组,使用了组函数,满足having条件的分组被显示

//按部门分组,求各部门平均工资

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

//查询10号部门的平均工资

select deptno,avg(sal) from emp group by deptno having deptno=10;

4.group by的增强

select deptno,job,sum(sal) from emp group by deptno,job

SQL> +

SQL> select deptno,sum(sal) from emp group by deptno

SQL> +

SQL> select sum(sal) from emp

SQL> ===

上面三条语句的相加就是下面这条语句的结果

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job)

SQL> 抽象

SQL> group by rollup(a,b)

SQL> =

SQL> group by a,b

SQL> +

SQL> group by a

SQL> +

SQL> 没有group by

SQL> */

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

break on deptno skip 2 //部门之间显示2个空行

/ //执行上一条语句