Oracle DML查询(Query)语句单表查询（简单查询、条件查询、排序查询）& 常用函数【敲敲极详细！！】

最新推荐文章于 2023-06-20 17:25:09 发布

大鱼吃狗

最新推荐文章于 2023-06-20 17:25:09 发布

阅读量408

点赞数

分类专栏： Oracle数据库

本文链接：https://blog.csdn.net/weixin_45648410/article/details/103431822

版权

Oracle数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

3.1 查询

select :查询； * ：表中的所有字段； from：被查询的表

所有Demo都以plsqldev系统自带表为例

1.单表查询

1.1简单查询

指的是查询一张数据表的所有数据行的内容

查询全表

select * from 表名;

查询指定列

select 列名1，列名2，… from 表名;

自定义标题

select 列名1 as ‘自定义标题1’，列名2 as ‘自定义标题2’，… from 表名;

注意：

as可以省略
" "可以省略

四则运算

--查询员工年薪 + 奖金
select ename, sal * 12 + 2000 from emp;

常量的使用

select '员工'，ename, sal from emp;

拼接 ||

select '姓名', ename || '薪资' || sal, '员工编号' || empno from emp;

1.2条件查询

select * from 表名 where 条件

1.2.1关系运算符

不等于：<> 和 !=

1.2.2逻辑运算符

and or not

--Demo not
--查询emp表中，基本工资<不>在 1200~2500之间 的雇员的所有信息
--条件: not ( sal > 1200 and sal < 2500 )
select * from emp where not ( sal > 1200 and sal < 2500 )

1.2.3区间范围

between …and…

select * from emp where sal between 1200 and 2500;

查询时间（hiredate）

--时间的格式不对
--select * from emp where hiredate between '2019-10-14' and '2019-11-26';

--格式1	14-10月-2019 
select * from emp where hiredate between '14-10月-2019' and '26-11月-2019';

--格式2	14-10月-19(2019省略20)
select * from emp where hiredate between '14-10月-19' and '26-11月-19';

1.2.4空判断

IS NULL：是否为空

IS NOT NULL：是否不为空

select * from emp where comm is null;

select * from emp where comm is not null;

注意：

comm = null 是错误格式
null 和 0 有区别

1.2.4.1nvl函数

解决查询表中数据有部分为null的问题，

e.g. 提成（部分没有提成）+ 奖金（number + null =null

select ename, job, sal + nvl(comm,0) from emp;

1.2.5等值范围

in ; not in

简化代码

--这种写法也是对的，只是繁琐
select * from emp where empno = 7844 or empno = 7846 or empno = 7850;
--简化
select * from emp where empno in(7844,7846,7850);

1.2.6模糊查询

通配符 ‘_’ ：匹配一位；

通配符 %’ ：匹配 0 至多位；

查询emp中，雇员姓名‘A’字符开头的雇员信息

select * from emp where ename like 'A%';

查询emp表中，雇员姓名以 S字符结尾的雇员信息

select * from emp where ename like '%S';

查询emp表中，雇员姓名的第二位字符是 A 的雇员信息

select * from emp where enaem like '_A%';

查询emp表中，雇员姓名中包含 A 字符的雇员信息

select * from emp where ename like '%A%';

查询emp表中，雇员薪资中包含 9 的雇员信息（可以使用在数值上）

select * from emp where ename like '%9%';

1.3排序查询

升序：asc ；降序： desc ;

默认升序

select [字段1],[字段2]… from 表名 order by [需要排序字段] [升序/降序];

--查询所有雇员信息，按照入职时间，从早到晚(升序)排序，如果入职时间相同，则按照薪资从高到低(降序)排序
select * from emp order by hiredate asc,sal desc;

1.4常用函数

dual : 伪表，用于函数测试

1.4.1字符串函数

对字符串操作

	说明
select asscii(‘char’) from dual；	查询ASSCII码
select concat (‘字符串’, ‘字符串’) from dual	字符拼接
select instr (‘字符串’, ‘字符串’，起始位置) from dual	下标1开始，字符串不存在返回0,起始位置不影响位置，只影响输出的字符串
select length(‘字符串’) from dual	查询字符串长度
select * from emp where length(ename) = 4;	查询emp表中ename长度为4的数据
select lower(‘HelloWorld’) from dual	小写
select upper(‘HelloWorld’) from dual;	大写
select ltrim(’===HelloWorld’,’=’) from dual;	（字符串，去除的字符）去除左边侧
select rtrim(‘HelloWorld===’,’=’) from dual;	（字符串，去除的字符）去除右侧
select trim(’=’ from ‘=HelloWorld==’) from dual;	（去的字符，字符串）去除两侧
select replace(‘ABCDE’,‘AB’, ‘HEHE’) from dual;	（字符串，被替代字符，替代字符）替换
select substr(‘ABCDEFG’, 3, 4) from dual;	（字符串，起始位置，截取长度）

1.4.2数字函数

[,y] 表示可以省略

函数	说明	示例
ABS(x)	x绝对值	ABS(-3)=3
ACOS(x)	x的反余弦	ACOS(1)=0
COS(x)	余弦	COS(1)=1.57079633
CEIL(x)	大于或等于x的最小值	CEIL(5.4)=6
FLOOR(x)	小于或等于x的最大值	FLOOR(5.8)=5
LOG(x,y)	x为底y的对数	LOG(2,4)=2
MOD(x,y)	x除以y的余数	MOD(8,3)=2
POWER(x,y)	x的y次幂	POWER(2,3)=8
ROUND(x[,y])	x在第y位四舍五入	ROUND(3.456,2)=3.46
SQRT(x)	x的平方根	SQRT(4)=2
TRUNC(x[,y])	x在第y位截断	TRUNC(3.456,2)=3.45

ROUND(x[,y])
- 默认y为0 （e.g. ROUND(3.56)=4
- y为正数，即保留y位小数（e.g. ROUND(5.654,2)=5.65
- y为负数，即小数点左边y位为零，进行四舍五入（e.g. ROUND(351.654,-2)=400
TRUNC(x[,y])【不四舍五入】
- 默认y为0 （e.g. ROUND(3.56)=3
- y为正数，即保留y位小数（e.g. TRUNC (5.654,2)=5.65
- y为负数，即小数点左边y位为零（e.g. TRUNC (351.654,-2)=300

1.4.3日期函数

sysdate – 系统时间

ADD_MONTHS(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期（日期，加的月数）

        sysdate -- 系统是时间 
        select sysdate from dual; 
        select add_months(sysdate, 12) from dual;

LAST_DAY(d)，返回指定日期当月的最后一天

select LAST_DAY(sysdate) from dual;

ROUND(d[,fmt])，返回一个以fmt为格式的四舍五入日期值【默认为:DDD】(日期，返回的日期类型)

YEAR:前半年舍去，后半年作为下一年

MONTH:前月舍去，后半月作为下一月

DDD:前半天舍去，后半天作为第二天

DAY:上半周舍去，下半周作为下一周周日

        --date:2019-11-26 13:56:32
        select sysdate, round(sysdate), -- DDD 默认格式化  
            round(sysdate, 'YEAR'),  -- 2019 
            round(sysdate, 'MONTH'), -- 12 
            round(sysdate, 'DDD'), -- 27 
            round(sysdate, 'DAY')   -- 舍入到最近的周的周日
            FROM DUAL;

EXTRACT(fmt FROM d)，提取日期中的特定部分

fmt为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。
其中YEAR、MONTH、DAY可以为DATE类型匹配，也可以与TIMESTAMP类型匹配
但是HOUR、MINUTE、SECOND必须与TIMESTAMP类型匹配

        SELECT EXTRACT(YEAR FROM SYSDATE),
            EXTRACT(MONTH FROM SYSDATE) ,
            EXTRACT(DAY FROM SYSDATE) ,
            EXTRACT(HOUR FROM SYSTIMESTAMP) , -- 格林尼治时间  (北京时间 + 8 )
            EXTRACT(MINUTE FROM SYSTIMESTAMP) ,
            EXTRACT(SECOND FROM SYSTIMESTAMP)  FROM DUAL;

       -- 查询 emp表中1980入职的员工 
       条件截取年份:  EXTRACT(year from hiredate) = 1980; 
       select * from emp where 1981 = EXTRACT(year from hiredate);
       
       -- 查询 12 分 入职的人   
       条件截取月份 : EXTRACT(month from hiredate) = 1980; 
       select * from emp where 12 = EXTRACT(month from hiredate);

1.4.4日期转换函数

yyyy表示四位年份
mm表示两位月份
dd表示两位天数
hh24表示24小时，hh12表示12小时
mi表示分钟
ss表示秒钟

to_date : 将字符串转换成日期
to_char : 将日期转换成字符串

 			select sysdate,
                to_char(sysdate,'yyyy/mm/dd hh:mi:ss'),
                to_char(sysdate, 'yyyy-mm-dd'),
                to_char(sysdate, 'yyyy-mm'),
                to_char(sysdate, 'yyyy')  
                from dual;

 	  -- 查询emp 表的入职时间xxxx-mm-dd 
      select ename,to_char(hiredate,'yyyy-mm-dd') from emp;

	  -- 也可单独的获取时间中的某一个字段和EXTRACT函数效果一样 
      select ename,to_char(hiredate,'mm') from emp;
      
      -- 1981年入职的员工 
      year = 1981  --> to_char(hiredate,'yyyy') = 1981
      
      -- to_char把date转成了string
      -- string  = 1981进行比较(数据内部会进行数据进行比较)
      select * from emp where to_char(hiredate,'yyyy') = 1981;
      -- string  = '1981'直接是字符串的比较 
      select * from emp where to_char(hiredate,'yyyy') = '1981';
      
      -- 把字符串转成日期 
      select to_date('2019-11-11','yyyy-mm-dd') from dual;

1.4.5其他函数

nvl(x,n) 判断x是否为null,如果为null可以设置默认值 n

在Oracle中  null + value  结果会为 null 
-- 查询emp的薪资 (sal + comm(提成))     
select empno,ename,sal + comm 薪资 from emp;

--解决的办法:使用nvl函数 
select empno,ename,sal + nvl(comm,0) 薪资 from emp;

decode: 类似于Java当中 if 、if else、if else if else

	  --创建一张表: 
      create table t_stu(
          id number(3),
          name varchar2(20),
          sex  number(1)
      );
      
      --添加数据 : 
      insert into t_stu(id,name,sex) values (1,'张无忌',1);
      insert into t_stu(id,name,sex) values (2,'赵敏',2);
      insert into t_stu(id,name,sex) values (3,'纪晓芙',2);
      insert into t_stu(id,name,sex) values (4,'灭绝师太',3);
      insert into t_stu(id,name,sex) values (5,'李宇春',3);
      commit; 
      
      --decode 函数可以进行判断 
      select id,name,sex ,
         decode(sex,1,'男'),
         decode(sex,2,'女'),
         decode(sex,3,'其他')
      from t_stu;

2.多表查询

多表查询的前提：需要表与表之间创建外键

2.1合并查询

对两张表进行合并

前提：字段个数，字段数据类型一致

union all 不去除重复并且合并
union 去除重复并且合并

	  create table s1(
         id number(2),
         name varchar2(20)
      );
      --添加数据: 
      insert into s1 values (1,'a');
      insert into s1 values (2,'b');
      insert into s1 values (3,'c');
      commit;
      
      create table s2(
         id number(2),
         name varchar2(20)
      );
      
      insert into s2 values (4,'d');
      insert into s2 values (5,'e');
      insert into s2 values (3,'c');
      commit;
      
      -- 2个表的查询进行合并 
      select * from s1
         union   -- 2个查询合并 去除了重复的数据 
      select * from s2
      
      select * from s1
         union  all -- 2个查询合并 显示所有的数据
      select * from s2

2.2笛卡尔乘积

emp 表中有 14 条数据
dept 表中有 4 条数据

select * from emp,dept;
--产生56条数据 (14 * 4) = 56

2.2.1解决重复查询数据

显示结果的时候去除重复，但底层还是没有解决笛卡尔乘积问题，依然查询了56条数据

select * from emp,dept where emp.deptno =dept.deptno;

2.3简写

可以给表名进行简写 (e.g. emp e

select e.empno,e.ename from emp e;

2.4多表查询的例子

查询每个雇员的编号，姓名，职位，工资，部门名称，部门位置。

1. 查询每个雇员的编号，姓名，职位，工资，部门名称，部门位置。
          select e.empno,e.ename,e.job,e.sal,d.dname,d.loc  
          from emp e,dept d
          where e.deptno = d.deptno;

查询每个雇员的编号，姓名，职位，工资，工资等级。

2. 查询每个雇员的编号，姓名，职位，工资，工资等级。
           select  e.empno,e.ename,e.job,e.sal,s.grade 
           from emp e,salgrade s 
           where s.losal <= e.sal and e.sal <= s.hisal;

查询每个雇员的编号，姓名，职位，工资，工资等级，部门名称。

 3. 查询每个雇员的编号，姓名，职位，工资，工资等级，部门名称。        
              select e.empno,e.ename,e.job,e.sal,s.grade,d.dname from 
              emp e,salgrade s,dept d
              where s.losal <= e.sal and e.sal <= s.hisal and e.deptno= d.deptno;

大鱼吃狗

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle DML查询(Query)语句单表查询（简单查询、条件查询、排序查询）& 常用函数【敲敲极详细！！】

3.1 查询select :查询； * ：表中的所有字段； from：被查询的表所有Demo都以plsqldev系统自带表为例文章目录3.1 查询1.单表查询1.1简单查询1.2条件查询1.2.1关系运算符1.2.2逻辑运算符1.2.3区间范围1.2.4空判断1.2.4.1nvl函数1.2.5等值范围1.2.6模糊查询1.3排序查询1.4常用函数1.4.1字符串函数1.4.2数字函数1....
复制链接

扫一扫