Oracle数据库基础（三）(查询)

最新推荐文章于 2024-05-06 21:10:29 发布
最新推荐文章于 2024-05-06 21:10:29 发布
阅读量9.8k 收藏 260
点赞数 41
分类专栏： 数据库基础
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43437465/article/details/90731539
版权

一、SQL（基础查询）

在这里插入图片描述

1.1 基本查询语句

1.1.1 FROM子句
  • SELECT <* , column [ alias ] , …> FROM table;
  • SELECT 用于指定要查询的列
  • FROM 指定要从哪个表中查询

❤️ 注意：
（1）如果要查询所有列，可以在SELECT后面使用*号
（2）如果只查询特定的列，可以直接在SELECT后面指定列名，列名之间用逗号隔开。

eg 1 ：

SELECT * FROM emp;

在这里插入图片描述

eg 2 ：

SELECT ename,sal,job FROM emp;

在这里插入图片描述

1.1.2 使用别名
  • 在SQL语句中可以通过使用列的别名改变标题的显示样式，或者表示计算结果的含义
  • 使用语法是列的别名跟在列名后，中间可以加或不加一个“AS”关键字
  • 如果希望别名中区分大小写字符，或者别名中包含字符或空格，则必须用双引号引起来

❤️ 注意：
（1）别名本身不区分大小写，而且不能含有空格。
（2）若希望别名区分大小写或含有空格，那么可以在别名上使用双引号括起来

eg 1 :

--这两句的效果是一样的
SELECT ename,sal*12   sal FROM emp;
SELECT ename,sal*12 AS sal FROM emp ;

在这里插入图片描述
eg 2 :

--起别名
SELECT ename,sal*12 AS "s a l  " FROM emp ;

在这里插入图片描述

1.1.3 WHERE子句
  • 在SELECT语句中，可以在WHERE子句中使用比较操作符限制查询结果
  • 如果和数字比较，可以使用单引号引起来，也可以不用
  • 如果和字符及日期类型的数据比较，则必须用单引号引起来

eg 1 :

/*
WHERE子句
*/
--查询部门10下的员工信息
SELECT * FROM emp WHERE deptno=10;

在这里插入图片描述
eg 2 :

--查询职员表中职位是“SALESMAN”的职员
SELECT ename,sal,job FROM emp WHERE job='SALESMAN';

在这里插入图片描述

1.1.4 SELECT子句
  • 如果只查询表的部分列，需要在SELECT后指定列名
  • SELECT ename,sal FROM emp;
    eg:
SELECT ename,sal FROM emp;

在这里插入图片描述

1.2 查询条件

1.2.1 使用>,<,<=,>=,!=,<>,=

eg 1：

--查询员工表中薪水低于2000元的职员信息
SELECT ename,sal FROM emp WHERE sal<2000;

在这里插入图片描述
eg 2:

--查询职员表中不属于部门10的员工信息（不等于号“<>”）
SELECT ename,sal,deptno,job FROM emp WHERE deptno <> 10;

在这里插入图片描述

1.2.2 使用AND,OR关键字
  • 在SQL操作中，如果希望返回的结果必须满足多个条件，应该使用AND逻辑操作符连接这些条件
  • 在SQL操作中，如果希望返回的结果满足多个条件之一即可，应该使用OR逻辑操作符连接这些条件

eg 1 ：

--查询薪水大于1000并且职位是‘CLERK’的职员信息
SELECT ename,sal FROM emp WHERE job = 'CLERK' AND sal > 1000;

在这里插入图片描述

❤️ 注意：

AND的优先级高于OR，可以通过括号来提高OR的优先级

eg 2:

SELECT ename,sal,job FROM emp WHERE sal>1000 AND job='SALESMAN'  OR job ='CLERK' ;

在这里插入图片描述

SELECT ename,sal,job FROM emp WHERE sal>1000 AND (job='SALESMAN'  OR job ='CLERK') ;

在这里插入图片描述

1.2.3 使用LIKE条件（模糊查询）
  • LIKE 用于模糊匹配字符串，支持两个通配符：
    _:单一的一个字符
    %:任意个字符
    eg:
--查看名字第二个字母是L最后一个字母是N的
SELECT ename FROM emp WHERE ename LIKE '_L%N';

在这里插入图片描述

1.2.4 使用IN和NOT IN
  • 比较操作符IN（list）用来取出符合列表范围中的数据
  • List表示值列表，当列或表达式匹配于列表中的任何一个值时，条件为TRUE，该条记录则被显示出来
  • IN也可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表
  • NOT IN（list）取出不符合此列表中的数据记录

eg 1:

--查询职位是MANAGER或CLERK的员工
SELECT ename,job FROM emp WHERE job IN('MANAGER','CLERK');

在这里插入图片描述

eg 2:

--查询不是部门10或20的员工
SELECT ename,deptno,job FROM emp WHERE deptno NOT IN (10,20);

在这里插入图片描述

1.2.5 BETWEEN … AND…
  • BETWEEN … AND…操作符用来查询符合某个值域范围条件的数据
  • 最常见的是使用在数字类型的数据范围上，但对字符类型和日期类型数据也同样适用

eg：

--查询工资在1500到3000之间的员工(小的在左边大的在右边)
SELECT ename,sal FROM emp WHERE sal BETWEEN 1500 AND 3000;

在这里插入图片描述

1.2.6 使用IS NULL 和 IS NOT NULL

空值NULL是一个特殊的值，比较的时候不能使用“=”号，必须使用IS NULL，否则得不到正确的结果

eg：

--查询那些职员的奖金数量为NULL
SELECT ename,sal,comm FROM emp WHERE comm IS NULL;

在这里插入图片描述

1.2.7 使用ANY和ALL条件
  • ANY和ALL不能单独使用，需要配合单行比较操作符>,>=,<,<=一起使用
>ANY(list)：大于列表中最小的
>ALL(list)：大于列表中最大的
<ANY(list)：小于列表中最大的
<ALL(list)：小于列表中最小的
ANY和ALL常用于子查询

eg 1 :

SELECT ename,job,sal,deptno FROM emp WHERE sal>ANY(500,5000,8000);

在这里插入图片描述

1.2.8 使用函数或者表达式作为过滤条件
  • 当查询需要对选出的字段进行进一步计算，可以在数字列上使用算术表达式（+，-，*，/）
  • 表达式符合四则运算的默认优先级，如果要改变优先级可以使用括号
  • 算术运算主要是针对数字类型的数据，对日期类型的数据可以做加减操作，表示在一个日期值上加或减一个天数

eg 1 :

SELECT ename,sal,job FROM emp WHERE ename = UPPER('scott');

在这里插入图片描述
eg 2:

SELECT ename,sal,job FROM emp WHERE sal*12>50000;

在这里插入图片描述

1.2.9 使用DISTINCT关键字
  • 数据表中有可能存储相同数据的行，当执行查询操作时，默认情况会显示所有行，不管查询结果是否有重复数据
  • 当重复数据没有实际意义，经常会需要去掉重复值，使用DISTINCT实现

eg1 :

--查看公司共有多少种职位
SELECT DISTINCT job FROM emp;

在这里插入图片描述
eg 2 :

--多字段去重，是对这些字段值的组合进行去重
SELECT DISTINCT job, deptno FROM emp;

在这里插入图片描述

1.3 排序

1.3.1 ASC和DESC

ORDER BY可以根据其后指定的字段对结果集按照该字段的值进行升序或者降序排列。

  • ASC：升序，不写默认就是升序
  • DESC:降序

eg 1 :

--降序
SELECT ename,sal FROM emp ORDER BY sal DESC;

在这里插入图片描述
eg 2：

--升序(ASC可以省略，默认按照升序排列)
SELECT ename,sal FROM emp ORDER BY sal ASC;

在这里插入图片描述

1.3.2 使用ORDER BY子句
  • ORDER BY按照多个字段排序
  • ORDER BY首先按照第一个字段的排序方式对结果集进行排序，当第一个字段有重复值时才会按照第二个字段排序方式进行排序，以此类推，每个字段都可以单独指定排序方式。

eg:

SELECT ename,deptno,sal FROM emp ORDER BY deptno DESC , sal DESC;

在这里插入图片描述
❤️ 注意：
排序的字段中含有NULL值，NULL被认作最大值

eg:

SELECT ename,comm FROM emp ORDER BY comm DESC;

在这里插入图片描述

1.3.3 多个列排序
  • 当以多列作为排序标准时，首先按照第一列进行排序，如果第一列数据相同，再以第二列排序，以此类推
  • 多列排序时，不管正序还是倒序，每个列需要单独设置排序方式

1.4 聚合函数

1.4.1 什么是聚合函数
  • 聚合函数
  • 聚合函数又叫多行函数，分组函数
  • 聚合函数就是对结果集某些字段的值进行统计的
1.4.2 MAX和MIN

MAX,MIN求给定字段的最大值或最小值
eg 1 :

--查看公司的最高工资和最低工资？
SELECT MAX(sal),MIN(sal) FROM emp;

在这里插入图片描述

1.4.3 AVG,SUM
  • AVG,SUM求平均值和总和
  • 只能操作数字类型
  • 忽略NULL值

eg:

-AVG,SUM求平均值和总和
SELECT AVG(sal),SUM(sal) FROM emp;

在这里插入图片描述

1.4.4 COUNT 函数
  • COUNT 函数不是对给定的字段的值进行统计的，而是对给定字段不为NULL的记录数统计的
  • 实际上所以聚合函数都忽略NULL值统计

eg：

SELECT COUNT(ename) FROM emp;

在这里插入图片描述

--通常查看表的记录数可以使用COUNT(*)
SELECT COUNT(*) FROM emp;

在这里插入图片描述

1.4.5 聚合函数对空值的处理
  • 聚合函数忽略NULL值
  • 当emp表中的comm列有NULL值，比如某新入职员工没有绩效，比较两条语句的结果：
/*
查看平均绩效
*/
SELECT AVG(comm),SUM(comm) FROM emp;

在这里插入图片描述

SELECT AVG(NVL(comm,0)),SUM(comm) FROM emp;

在这里插入图片描述
❤️ 说明：
因为求SUM时忽略掉了绩效为NULL的员工，所以以第一种方法求每个员工的获得绩效的平均值是不准确的。因此为了获得正确的每个员工的平均绩效值就得用到NVL函数。

1.5 分组

1.5.1 GROUP BY子句
  • 当希望得到每个部门的平均薪水，而不是整个机构的平均薪水
  • 把整个数据表按部门划分成一个个小组，每个小组中包含一行或多行数据，在每个小组中再使用分组函数进行计算，每组返回一个结果
  • 划分的小组有多少，最终的结果集行数就有多少

eg 1：

--查看每个部门的平均工资
SELECT AVG(sal) ,deptno  FROM emp GROUP BY deptno;

在这里插入图片描述
eg 2:

--查看每个职位的最高工资
SELECT MAX(sal),job FROM emp  GROUP BY job;

在这里插入图片描述

1.5.2 分组查询

GROUP BY也可以根据多个字段分组，分组原则为这几个字段值都相同的记录看做一组。
eg 1 :

--查看同部门同职位的平均工资
SELECT AVG(sal) ,deptno,job FROM emp GROUP BY deptno,job;

在这里插入图片描述
❤️ 注意：
当SELECT子句中含有聚合函数时，那么凡不在聚合函数中的其他单独字段都必须出现在GROUP BY子句中，反过来则不是必须的。

1.5.3 HAVING 子句
  • HAVING子句用来对分组后的结果进一步限制，比如按部门分组，得到每个部门的最高薪水，可以继续限制输出结果
  • HAVING子句必须在GROUP BY后面，不能单独存在

eg:

--查看平均工资高于2000的部门的最高工资和最低工资分别是多少？
SELECT MAX(sal),MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000;

在这里插入图片描述

❤️ 1.6 查询语句执行顺序（面试重点）

查询语句的执行顺序依下列子句次序：

  • （1）FROM子句：执行顺序为从后往前、从右往左
    ❤️ 注意：数据量较少的表尽量放在后面

  • （2）WHERE子句：执行顺序为自下而上、从右往左
    ❤️ 注意：将能过滤掉最大数量记录的条件写在WHERE子句的最右

  • （3）GROUP…BY…子句：执行顺序从左往右分组
    ❤️ 注意：最好在GROUP BY 前使用WHERE将不需要的记录在GROUP BY之前过滤掉

  • （4） HAVING 子句：消耗资源
    ❤️ 注意：尽量避免使用，HAVING会在检索出所以记录之后才对结果进行过滤，需要排序等操作

  • （5）SELECT子句：少于 * 号，尽量取字段名称
    ❤️ 注意：
    Oracle在解析的过程中，通过查询数据字典将*号依次转换成所有的列名，消耗时间

  • （6）ORDER BY 子句：执行顺序为从左到右排序，消耗资源

二、关联查询

在这里插入图片描述

2.1 关联基础

2.1.1 关联的概念
  • 实际应用中所需要的数据，经常会需要查询两个或两个以上的表
  • 这种查询两个或两个以上数据表或视图的查询叫做连接查询
  • 连接查询通常建立在存在相互关系的父子表之间
    图析：
    在这里插入图片描述
    代码实现：
跨表操作：

--查看每个员工的名字以及其所在部门的名字
SELECT ename,dname FROM emp,dept
WHERE emp.deptno=dept.deptno;

在这里插入图片描述
❤️ 注意：

  • 当两张表有同名字段时，SELECT子句中必须明确指定该字段来自哪张表。
  • 在关联查询中，表名也可以添加别名，这样可以简化SELECT语句的复杂度。

eg:

--查看每个员工的名字以及其所在部门的名字
SELECT  e.ename,d.dname FROM emp e,dept d
WHERE e.deptno=d.deptno;

在这里插入图片描述
效果是完全一样的！！！

2.1.2 笛卡尔积
  • 笛卡尔积指做关联操作的每个表的每一行都和其他表的每一行做组合，假设两个表的记录条数分别是X和Y，笛卡尔积将返回X*Y条记录。

图析：
在这里插入图片描述
代码验证：

SELECT  e.ename,d.dname FROM emp e,dept d

在这里插入图片描述
会产生56行数据记录！！！

❤️ 注意：

  • 关联查询要添加连接条件，否则会产生笛卡尔积
  • 笛卡尔积通常是一个无意义的结果集，它的记录数是所有参与查询的表的记录数乘积的结果
  • 要避免出现，数据量大时极易出现内存溢出等现象
  • N张表关联查询至少要有N-1个连接条件

eg:

--查看在NEW YORK工作的员工
SELECT e.ename,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.loc='NEW YORK';

在这里插入图片描述

--查看工资高于3000的员工的名字，工资，部门名以及所在地
SELECT e.ename,e.sal,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND e.sal>3000;

在这里插入图片描述

2.1.3 等值连接
  • 连接查询中最常见的一种，通常是在有主外键关联关系的表间建立，并将连接条件设定为有关系的列，使用等号“=”连接相关的表

2.2 关联查询

2.2.1 内连接
  • 内连接返回所有满足连接条件的记录

图析：
在这里插入图片描述
❤️ 注意：
不满足连接条件的记录是不会在关联查询中被查询出来的

2.1.2 外连接
  • 内连接返回满足连接条件的数据记录
  • 有些情况下，需要返回那些不满足连接条件的记录，需要使用外连接
  • 外连接不仅返回满足连接条件的记录，还将返回不满足连接条件的记录

外连接的分类：
（1）左外连接：以JOIN左侧表作为驱动表（所有数据都会被查询出来），那么当该表中的某条记录不满足连接条件时来自右侧表中的字段全部填NULL。

--实现左外连接的一种简单写法
SELECT e.ename,e.deptno,d.deptno,d.dname
FROM emp e  JOIN dept d
ON e.deptno=d.deptno(+);

在这里插入图片描述
（2）右外连接：以JOIN右侧表作为驱动表（所有数据都会被查询出来），那么当该表中的某条记录不满足连接条件时来自左侧表中的字段全部填NULL。

--实现右外连接的一种简单写法
SELECT e.ename,e.deptno,d.deptno,d.dname
FROM emp e  JOIN dept d
ON e.deptno(+)=d.deptno;

在这里插入图片描述

（3）全外连接：全外连接是指，除了返回满足条件的记录，还会返回不满足连接条件的所有其他行。全外连接是左外连接和右外连接查询结果的总和。
在这里插入图片描述

❤️ 简记：
（1）（+）写哪边，哪边加NULL
（2）全外连接不能采用这种写法

2.1.3 自连接
  • 自连接是一种特殊的连接查询，数据的来源是一个表，即关联关系来自于单表中的多个列
  • 表中的列参照同一个表中的其他列的情况称作自参照表
  • 自连接是通过将表用别名虚拟成两个表的方式实现，可以是等值或不等值连接。

图析：
在这里插入图片描述
简记：

  • 自连接即当前表的一条记录可以对应当前表自己的多条记录
  • 自连接是为了解决同类型数据但是有存在上下级关系的树状结构数据时使用

eg:

--查看每个员工及其领导的名字
SELECT e.ename,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno;

在这里插入图片描述
小试牛刀：
案例：查看SMITH的上司在哪个城市工作？

方法一：


SELECT e.ename,m.ename,m.deptno,d.loc
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno
AND m.deptno=d.deptno
AND e.ename='SMITH';

在这里插入图片描述
方法二：

SELECT e.ename,m.ename,m.deptno,d.loc
FROM emp e JOIN emp m
ON e.mgr=m.empno
JOIN dept d
ON m.deptno=d.deptno
WHERE e.ename='SMITH';

在这里插入图片描述
❤️ 结果：
对比两种方法，尽量优先第二种方案，查询快。

Alive~
关注
  • 41
    点赞
  • 踩
  • 260
    收藏
    觉得还不错? 一键收藏
  • 4
    评论
专栏目录
oracle经典查询
HoneyQIm的博客
01-02 581
oracle经典查询题目
Oracle查询
wangchunyang51的博客
01-17 431
1.单表查询 1.精确查询 查询语句： select * from T_OWNERS where watermeter = '1'; select id,name from T_OWNERS where watermeter = '1'; 2.模糊查询 select * from t_owners where name like '%xi%
Oracle 查询
cs_quan的博客
05-27 3684
2 Oracle 查询 目录 单表查询 连接查询 子查询 分页查询 单行函数 行列转换 分析函数 集合运算 总结 1 单表查询 简单条件查询 select * from T_OWNERS where watermeter='30408' 模糊查询 select * from t_owners where name like '%刘%' and 和 or 运算符 select * from t_owners where name like '%刘%' and housenumber li
Oracle-查询篇（简单查询、多表联查、子查询、分组聚合、字符串函数、单行函数、日期函数）
JunDong的博客
06-04 1907
适合具有sql基础的同志，通过例子能够很快回忆起查询的各种用法。
《Oracle 12c数据库基础教程》教学课件—03Oracle数据库管理工具.pdf
06-22
《Oracle 12c数据库基础教程》教学课件—03Oracle数据库管理工具.pdf《Oracle 12c数据库基础教程》教学课件—03Oracle数据库管理工具.pdf《Oracle 12c数据库基础教程》教学课件—03Oracle数据库管理工具.pdf《Oracle...
oracle数据库基础.ppt
11-13
Oracle数据库基础 Oracle数据库是全球广泛使用的数据库管理系统之一，由Oracle公司开发，其名称源于古希腊神话中的神谕，象征预知未来的能力。在中国，Oracle因商代甲骨文的象征意义而被译为“甲骨文”。Oracle...
oracle数据库基础1.ppt
11-12
Oracle数据库基础1.ppt概述了Oracle数据库的基本概念和学习的重要性，以及Oracle公司在数据库领域的地位。Oracle数据库不仅是全球广泛使用的大型关系数据库管理系统，还涵盖了多种操作系统平台。课程内容包括Oracle...
oracle数据库基础知识.ppt
11-21
"Oracle数据库基础知识" Oracle数据库基础知识是指Oracle数据库的基本概念、原理和操作技术。Oracle数据库是一种关系型数据库管理系统，提供了强大的数据存储、管理和检索功能。本文档将对Oracle数据库基础知识进行...
Oracle数据库基础知识
07-26
Oracle数据库基础知识 Oracle数据库基础知识是指对Oracle数据库的基本概念、结构、管理和操作的了解。Oracle数据库是一种关系型数据库管理系统，由 Oracle Corporation 开发和维护。 课程目标： * 了解Oracle...
oracle查询语句大全(oracle 基本命令大全一)
10-05 2783
1.create user username identified by password;//建用户名和密码oracle ,oracle 2.grant connect,resource,dba to username;//授权 grant connect,resource,dba,sysdba to username; 3.connect username/password//进入。 4.se
oracle简单查询
产品经理，程序人生
11-25 1393
--================= --author:yeeXun --date:2010-11-25　16:46:00 --address:17-304 --=================   oracle简单查询 查看表结构 SQL>desc  table_name;   查询所有列 SQL>select * from table_name; ❤少用此查询，因为
ORACLE 查询
热门推荐
一起摆摊啊
04-04 1万+
查询作为SQL语言中作重要的组成部分，其作用也是巨大的。一个完整的select语句由select … from …组成。而这个基础的语句可以扩充为： Select … From … Where … (Group by) … Having(where) … Order by… 下面举个最简单的例子： 得到的查询结果： 可以将通配符*换成student表中你需要的字段，如SNO,SNAME，从而得...
Oracle之简单查询
兮动人
06-02 791
在数据库之中如果要进行查询使用的就是SQL-DML语法操作，定义查询的复杂度，本次先使用单张数据表（ emp）进行查询，单张表如下（固定的语法组成采用大写字母，可更换部分采用小写字母)： ② select [distinct] *|列名称 [别名],列名称 [别名],...|数据计算 ① from 数据表 [别名] from子句确定要查询的数据来源; distinct:消除重复数据行的显示; “*”:表示杳询该表中所有的数据列的内容; 案例:查询所有雇员的详细信息（雇员表的所有行和列的内容) .
【ORACLE】数据库的查询
最新发布
2301_76210107的博客
05-06 1129
【例1】SELECT 姓名 AS NAME,地址 AS ADDRESS,电话号码 AS PHONENUMBER FROM EMPLOYEES EHERE SEX=1;完全外连接（FULL OUTER JOIN）：结果表中除了满足连接条件的行外，还包括两个表所有行。左外链接（LEFT OUTER JOIN）：结果表中除了满足连接条件的行外，还包括左表的所有行。外连接的结果不但包含满足连接条件的行，还包括相应表中的所有行。结果表中包含编号相同的EMPLOYEES表中的姓名列和SALARY表中的所有列。
oracle查询最新,Oracle 基本查询
weixin_42356892的博客
04-02 438
目录Oracle 体系结构1、数据库 -> 数据库实例 -> 表空间 -> 数据文件。2、通常情况下 Oralce 数据库只会有一个实例叫 " orcl "。Mysql 是多数据库的：新建一个 Java Web 应用，就会在 Mysql 中创建一个数据库，然后建表。Oracle 是多用户的：新建Java Web 应用，创建表空间，创建用户，用户去创建表。3、Sql(结构化查询语...
oracle查询
ellatester的博客
11-16 420
CREATE TABLE student ( id INTEGER, name VARCHAR2(20) NOT NULL , sex VARCHAR2(4) , birth VARCHAR2(10), department VARCHAR2(20) , address VARCHAR2(50) ); CREATE TABLE score ( id INTEGER, stu_id INTEGER ...
oracle数据库一行数据去重语句
03-22
可以使用以下语句进行一行数据去重： SELECT DISTINCT column_name FROM table_name; 其中，column_name 是要去重的列名，table_name 是要去重的表名。
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值