Oracle笔记_查询

1 单条件查询

select ……
from ……
where 条件

-- =  >  >=  <  <=  !=  <>
-- 单引号用于数据表示字符串
-- 双引号用于数据库对象名，列名，表名……
-- 数据区分大小写，数据库不区分大小写

2 多条件查询

--与and  或or  非not

select * from emp where deptno in (10,20);      
-- in表示deptno取值在10和20其中一个，not in表示不在集合中；如果结果中含有NULL，不能使用not in，但可以使用in。

select * from emp where sal between 1000 and 2000;      
-- between and 是包括边界值

--SQL在解析where时，是从右向左解析的；使用and时应将易假的值放在右侧，or时应将易真的值放在右侧；

3 模糊查询

select ...
from ...
where 列名 like '模式字符串'
    -- % 表示若干个字符
    -- _ 表示一个字符

select * from emp where ename like '%\_%' escape '\';   -- 以反斜杠作为转义字符

4 排序

select ...
from ...
where 条件 
order by 列名1 asc|desc,列名2....

-- asc表示升序(不填则默认是升序)
-- desc表示降序
select * from emp order by 2;   --按照输出结果集的第2列来进行排序
select ename 名字,sal 月薪 from emp order by 月薪

5 单行函数

单行函数：只对一行数据进行计算，产生一个结果。函数可以没有参数，但必须要有返回值。

5.1 字符函数

lower       --将字符串转小写
upper       --将字符串转大写
initcap     --将首字母变成大写

concat      --字符串连接
    select concat(concat('hello','world'),'123')    --支持两个参数
    select 'hello'||'world'||123        --字符串拼凑
    
substr      --提取子串
    select substr('helloworld',3),     --从第三个字符开始一直取子串到最后
    select substr('helloworld',3,5)       --从第三个字符开始取5个字符
    
instr       --查找某个字符串是否另一个字符串的子串
    select instr('hello world', 'llo') from dual    --结果为3
    
lpad,rpad   --左右填充
    select lpad('hello','10','#') from dual;
        --填充字符到达指定的长度
        --往hello左边填充字符#，使字符串长度到达10
        --如果字符串长度大于指定长度，就截取

trim        --裁剪
    select trim ( '    Hello world    ') from dual;   --默认裁剪空白字符
    select trim ( 'H' from 'Hello worldHHHH') from dual;   --左右裁剪H字符

replace     --替换子串
    select replace('Hello world','l','#') from dual;
length      --长度，length(last_name)

5.2 数值函数

round   --四舍五入
trunc   --截取
ceil    --向上取整
floor   --向下取整
mod     --取模 %
 select round(45.926,2) from dual;   --45.93

5.3 转换函数

to_char     --数字转字符串
    select ename ,sal ,to_char(sal,'L9999') from emp;
        --L  表示本地货币字符
        --9  表示一位数字

to_number   --字符串转数字
to_date     --字符串转日期,以什么格式转化为字符串，就以什么格式转回date类型
    select to_char(sysdate,'dd-mm-yyyy dy hh24:mi:ss') from dual;
    select to_date('30-05-2019 星期四 17:01:57','dd-mm-yyyy dy hh24:mi:ss') from dual;

5.4 日期函数

now         --mysql中获取系统当前时间
sysdate     --oracle中获取系统当前时间
    select sysdate from dual ;
    select sysdate-1 昨天,sysdate 今天 ,sysdate +1 明天 from dual;
    
months_between      --计算两个时间相差的月份，自动计算大小月
    select 
    (sysdate-hiredate)/30 月,
    months_between(sysdate,hiredate)
    from emp;

add_months      --计算明年今日
    select add_months(sysdate,12) from dual;

last_day        --获取当前月份最后一天
    select last_day(sysdate) from dual

next_day        --获取下一个星期几是哪一天
    select next_day(sysdate,'星期二') from dual

5.5 通用函数

nvl(exp,val)        --如果exp为null，那么返回val，否则返回exp的值
nvl2(exp,val1,val2)     --如果exp为null，返回val2 ，否则返回val1
select distinct deptno from emp;        --去重
        --distinct作用于后面所有列；
dual        --虚表
select 3+2 from dual;

nullif(exp1,exp2)       --当exp1=exp2时返回null，不等时返回exp1值
coalesce(a,b,c...,n)    --从左到右找参数中第一个不为空的值

5.6 条件语句

case when then else end
--总裁决定给大家涨工资，主管涨1000，销售涨500，其他涨200
 select ename,job,sal 涨前工资,
    case job
        when 'MANAGER' then sal+1000
        when 'SALESMAN' then sal+500
        else sal + 200
    end  涨后工资
    from emp

decode(expr,val1,val2,val3,val4,....,defaultValue)
--如果expr 值是val1 那么返回val2，如果是val3,那么返回val4,以此类推，都不是，那么就返回最后一个defaultValue的值

6 多行函数

6.1 统计函数

sum     --总和
select sum(sal) from emp

count  计数函数，某行数据有值，那么就计数+1
    --*表示该行数据不管哪列有数据都会统计
    --最终只统计comm该列不为空的数量
    --null不会参与统计函数的计算
select count(comm) 员工数量 from emp;

max/min     --求最高和最低
select max(sal) 最高工资,min(sal) 最低工资 from emp

avg     --求平均
select avg(sal) from emp

6.2 分组统计

select ...
from ...
where cond
group by 列名1[,列名2,....]
having cond
order by ...

--按照group by后给定的表达式，将from后面的table进行分组。
--在select列表中，所有没有包含在组函数中的列，都必须在group by的后面出现。
--统计函数，在没有分组的情况，统计的是全表，在有分组的情况下，统计的是对应的分组
--where里边不允许使用分组函数,改用having
--having里边能够使用统计函数的结果进行比较
--能使用where不要使用having

group_concat(xxx)
select gender,group_concat(name, age, id) from students where gender=1 group by gender;

where 和 having区别

1. 从表集合中查询数据，首先要满足where条件 cond1 ,满足的数据就筛选出来，成为结果集1
2. 从结果集1中使用group by 指定的列进行分组,计算统计函数的值，成为结果集2
3. 结果集2继续进行筛选，筛选条件就是having的条件cond2 ,最终通过筛选之后，成为结果集3
4. 结果集3经过order by 的条件进行排序 ，得到最终的结果集
5. 数据越早能够得到筛选越好，对于后面计算量控制有优势

7 字符格式

9	数字
0	零
$	美元符
L	本地货币符号
.	小数点
,	千位符

8 分页

--limit start, count

-- 限制查询出来的数据个数
select * from students where gender=1 limit 2;

-- 查询前5个数据
select * from students limit 0, 5;

转载于:https://www.cnblogs.com/chungeyuan/p/11413220.html