一、Oracle的查询
(一)、基本查询
1、SQL中的null
- 包含null的表达式都为null
- null永远!=null (在使用 is null ; is not null)
- null 如果集合中,含有null,不能使用not in;但是可以使用in
select * from dept where dname is not null
select * from emp where deptno not in (10,20,null);//不能够使用not in ,不然查询出的条数为空;
2、distinct 去掉重复记录
select distinct deptno from emp;
3、nvl(a,b) , 表示sql的一个滤空函数,意思是:如果a是null值,那么该a字段的值为b,如果a的值不是null值,那么a的值是他本身;
select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp;
(二)、过滤查询
1、where条件后的字段名称对应的 字符串 值的过滤
注意:orcal对字符串值的大小写敏感;
SQL> --查询名叫KING的员工
SQL> select * from emp where ename='KING';
2、orcal中的条件查询中对日期值的格式敏感;
注意:默认情况下,orcal的日期格式为
SQL> --日期格式敏感 DD-MON-RR
SQL> --查询入职日期是 17-11月-81的员工
select * from emp where hiredate='17-11月-81'
3、比较过滤 > >= < <= <> !=
between ... and 1. 含有边界 2. 小值在前 大值在后
select * from emp where sal between 1000 and 2000;
select * from emp where ename like '%_%';
select * from emp where ename like '%\_%' escape '\'; #注意其后的转义字符的使用;
4、逻辑过滤
注意:在过滤查询中,在sql优化中注意where条件的执行顺序是从右到左的;
5、oracle中的排序
注意:
--order by 后面 + 列,表达式,别名,序号
select empno,ename,sal,sal*12 from emp order by sal*12 desc;
--order by 作用于后面所有的列,先按照第一个列排序,如果相同,再按照第二列排序;以此类推
select * from emp order by deptno,sal desc
--null 值 null的排序,在oracle中null值为最大,所以desc,会出现null值在最上边;为了避免如下:
select * from emp order by comm desc nulls last;(意思是,先按照comm 降序排序,如果有null,就把null放到最后)
(三)、单行函数
一个简单的定义:
1、字符函数
SQL> --字符函数
SQL> select lower('Hello World') 转小写,upper('Hello World') 转大写,
2 initcap('hello world') 首字母大写
3 from dual;
转小写 转大写 首字母大写
----------- ----------- -----------
hello world HELLO WORLD Hello World
SQL> --substr(a,b) 从a中,第b位开始取
SQL> select substr('Hello World',3) 子串
2 from dual;
子串
---------
llo World
SQL> --substr(a,b,c) 从a中,第b位开始取,取c位
SQL> select substr('Hello World',3,4) 子串 from dual;
子串
----
llo
SQL> --length 字符数 lengthb字节数
SQL> select length('Hello World') 字符,lengthb('Hello World') 字节 from dual;
字符 字节
---------- ----------
11 11
SQL> ed
已写入 file afiedt.buf
1* select length('北京') 字符,lengthb('北京') 字节 from dual
SQL> /
字符 字节
---------- ----------
2 4
SQL> --instr(a,b) 在a中,查找b
SQL> select instr('Hello World','ll') 位置 from dual;
位置
----------
3
SQL> --lpad 左填充 rpad右填充
SQL> -- abcd ---> 10位
SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右
2 from dual;
左 右
---------- ----------
******abcd abcd******
SQL> --trim 去掉前后指定的字符
SQL> select trim('H' from 'Hello WorldH') from dual;
TRIM('H'FR
----------
ello World
SQL> --replace
SQL> select replace('Hello World','l','*') from dual;
REPLACE('HE
-----------
He**o Wor*d
2、数值函数
SQL> --四舍五入
SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,
2 round(45.926,-1) 四,round(45.926,-2) 五
3 from dual;
一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.93 45.9 46 50 0
----截断
SQL> ed
已写入 file afiedt.buf
1 select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,
2 trunc(45.926,-1) 四,trunc(45.926,-2) 五
3* from dual
SQL> /
一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.92 45.9 45 40 0
SQL> host cls
---求余函数MOD(1600,300) 100
3、日期函数
在oracle中的日期数据类型实际含有两个值:日期和时间。默认的情况下日期的格式是:DD-MON-RR
注意:
在mysql中,有两种表示日期类型的数据类型:date 表示日期 ;datetime 表示日期和时间;
在oracle中,只有一种date格式,表示 日期和时间 ;
3.1、oracle中只有一个sysdate
SQL> --当前时间
SQL> select sysdate from dual;
3.2、
SQL> --格式化显式一个时间
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
3.3、日期的数学运算
在日期上加上或者减去一个数字,结果扔为日期,表示这个日期中日的加减;
SQL> --昨天 今天 明天
SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;
两个日期相减,返回的是日期之间相差的天数;(日期和日期只能够相减,相加没有意义)
SQL> --计算员工的工龄:天 星期 月 年
SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp;
日期函数:
SQL> --months_between 两个日期相差的月份
SQL> select ename,hiredate,(sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二 from emp;
SQL> --add_months 指定日期中加上若干个月
SQL> select add_months(sysdate,73) from dual;
SQL> --last_day 本月的最后一天
SQL> select last_day(sysdate) from dual;
SQL> --下一个星期五
SQL> select next_day(sysdate,'星期五') from dual;
sql中日期的四舍五入
select round(sysdate,'month'),round(sysdate,'year') from dual;
4、转换函数
4.1、转换函数的定义:
转换的分类:
、
1、隐式转换:
该语句中的字符日期,就隐式转换成了date格式;
select * from emp where hiredate='17-11月-81'
2、显式转换:
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI
----------------------------------
2018-02-02 16:59:40今天是星期五
SQL> --查询员工薪水:两位小数,千位符,货币代码 把数字类型,转变为字符类型
SQL> select to_char(sal,'L9,999.99') from emp;
5、通用函数
所谓通用函数,就是对任何数据类型都通用,具体如下:
SQL> --nvl2(a,b,c) 当a=null时候,返回c;否则返回b
SQL> select sal*12+nvl2(comm,comm,0) from emp;
SQL> --nullif(a,b) 当a=b时候,返回null,否则返回a
SQL> select nullif('abc','abc') 值 from dual;
SQL> --coalesce 从左到右 找到第一个不为null的值
SQL> select comm,sal,coalesce(comm,sal) "第一个不为null的值" from emp;
单行函数中的,条件函数:
SQL> select ename,job,sal 涨前,
2 case job when 'PRESIDENT' thensal+1000
3 when 'MANAGER' then sal+800
4 else sal+400
5 end 涨后
6 from emp;
SQL> select ename,job,sal 涨前,
2 decode(job,'PRESIDENT',sal+1000,
3 'MANAGER',sal+800,
4 sal+400) 涨后
5 from emp;
例如:用decode函数,计算员工的税率:
select ename ,sal ,decode(
trunc(sal/2000,0),
0,0.00,
1,0.10,
2,0.20,
0.30
)
from emp
(四)、多行函数
1、分组函数:
查询出部门中,员工的个数:
select count(*) from emp group by deptno;
COUNT(*)
------
6
5
3
注意,组函数的一个特点是,组函数有自动滤空null的特点:
SQL> --null 5. 组函数自动滤空SQL> select count(*),count(nvl(comm,0)) from emp;
COUNT(*) COUNT(NVL(COMM,0))
---------- ------------------
14 14
2、利用group by 分组数据
应该注意的是:
SQL> --多个列的分组
SQL> select deptno,job,sum(sal) from emp group by deptno,job order by 1;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
3、使用分组函数的条件查询使用 having
SQL> --查询平均工资大于2000的部门
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having avg(sal) > 2000;
DEPTNO AVG(SAL)
---------- ----------
20 2175
10 2916.66667
4、group by语句的增强作用
例如:使用group by 语句实现如下图的sql语句:
对上面的可以拆分成下面的三个group by语句;
SQL> 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> ===
SQL>
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
最终实现语句:
SQL> break on deptno skip 2
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
(回复plsql格式:break on null)
(五)、多表查询
1、笛卡尔积的出现:
select d.deptno ,d.dname,e.ename from emp e ,dept d;
2、为了避免笛卡尔积的出现,使用了等值连接、不等值连接、外联结(左外联结、右外联结)、自连接
2.1、等值连接:
查询寻出部门编号、员工姓名、员工的薪水和部门的名称
SQL> select e.empno,e.ename,e.sal,d.dname
2 from emp e,dept d
3 where e.deptno=d.deptno;
2.2、不等值连接
查询出员工薪水的等级;
SQL> select e.empno,e.ename,e.sal,s.grade
2 from emp e,salgrade s
3 where e.sal between s.losal and s.hisal;
2.3、外联结
左外联结 ...... from 表名1 left join 表名2 on 连接条件;
特点:除了查询出等值连接的表的内容以外,还会把表1的表所有的内容查询出来;
--按照部门统计人数 :部门号、部门名称、人数 emp表作为左表
SQL> select d.deptno,d.dname,count(e.empno) from emp e left join dept d on e.deptno=d.deptno group
by d.deptno,d.dname;
DEPTNO DNAME COUNT(E.EMPNO)
---------- -------------- --------------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
--按照部门统计人数 :部门号、部门名称、人数 dept表作为右表
SQL> select d.deptno,d.dname,count(e.empno) from dept d left join emp e on e.deptno=d.deptno group
by d.deptno,d.dname;
DEPTNO DNAME COUNT(E.EMPNO)
---------- -------------- --------------
10 ACCOUNTING 3
40 OPERATIONS 0
20 RESEARCH 5
30 SALES 6
2.4、自连接
自连接就是把一张表变成两张表,通过这两张表的关联进行查询的一个连接查询;
--查询emp表中,员工的姓名、员工老板的姓名
SQL> --自连接:通过表的别名,将同一张表视为多张表
SQL> select e.ename 员工的姓名,b.ename 老板姓名
2 from emp e,emp b
3 where e.mgr=b.empno;
也可以通过子查询的办法完成上面的查询:
select e.ename 员工的姓名,(select ee.ename from emp ee where ee.empno=e.mgr) 老板的姓名 from emp e;
二、sql的查询语句优化的几种原则
(一)、 尽量使用列名代替 *
原因是因为*号有:从*号解析成相应表的字段的过程,中间有个字符解析的过程;
(二)、在where条件的执行语句中,是从右 向 左执行的;
(三)、SQL 优化 3. 尽量使用 where
用的环境是:当有having与where时,如果条件字句中没有组函数,尽量使用where代替having;
(牵涉到的是,where是先过滤后分组的情况,having是先分组再过滤的情况)
三、orcal的事务与mysql中的事务
mysql的事务是手动开启的;
oracle的事务是自动开启的;