Oralce学习之Select语句,条件判断查询,单行函数,多行函数,分组,行转列以及rowid和rownum的学习

最新推荐文章于 2025-05-14 09:50:37 发布

原创最新推荐文章于 2025-05-14 09:50:37 发布 · 800 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

Oralce 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了Oracle数据库的学习，涵盖了SELECT语句的基本语法、查询条件、单行和多行函数、分组及行转列等内容。讲解了如何使用WHERE子句进行比较、集合操作和模糊查询，以及如何利用IN、EXISTS子查询。还讨论了ROWID和ROWNUM在数据区分和记录序号中的作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一数据概述

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库；随着信息技术和市场的发展，数据管理不再仅仅是存储和管理数据，而转变成用户所需要的各种数据管理的方式。数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统都在各个方面得到了广泛的应用。

数据库现在已经成为数据管理的重要技术，也是计算机的重要分支。由于数据库具有数据结构化，最低冗余度、较高的程序与数据独立性，易于扩展、易于编制应用程序等优点，较大的信息系统都是建立在数据库设计之上的。数据库的运用从一般管理扩大到计算机辅助技术、人工智能以及科技计算等领域。

随着数据库技术的发展，计算机技术也随着得到了很大的发展，数据库为我们提供了可以快速存储以及检索的便利，它也为近几年软件可以如此普及贡献不小的力量。

Oracle Database，又名 Oracle RDBMS，或简称 Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说 Oracle 数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的适应高吞吐量的数据库解决方案。

1.常用数据库

分类	产品	特点
小型	access 、foxbase	负载量小,用户大概100人以内(留言板信息管理系统);成本在千元站内
中型	sqlserverl、mysql	负载量,日访问量在5000-10000;成本在万元以内(商务网站);满足日常安全需求
大型	sybase、db2、oracle	海量负载,可以处理海量数据(sybase<oracle<db2海量处理能力);安全性高,相对较贵.

2.DBMS

数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称 DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过 DBMS 访问数据库中的数据，数据库管理员也通过 dbms 进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。大部分 DBMS 提供数据定义语言DDL（Data Definition Language）和数据操作语言 DML（Data Manipulation Language），供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。

根据存储模型可将数据库划分为关系型数据库和非关系型数据库。关系型数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。标准数据查询语言 SQL 就是一种基于关系数据库的语言，这种语言执行对关系数据库中数据的检索和操作。

3.SQL 语言介绍

SQL(Structured Query Language)为数据库的语言，在 1974 年由 Boyce【博伊斯】和 Chamberlin【钱伯林】提出的一种介于关系代数与关系演算之间的结构化查询语言，是一个通用的、功能极强的关系型数据库语言。它包含三部分:DDL DML以及DCL

DDL:数据定义语言-操作表的

DML:数据管理语言,还可以分为DDL和DML两种–操作表中数据的

DDL:数据库查询语言
DML:数据库管理语言

**DCL:**数据库控制语言

分类	命令
DDL	create:创建;drop:删除;alter:修改;rename:重命名;truncate:截断
DML	insert:插入;delete:删除;update:删除;update:更新;select:查询;
DCL	grant:授权;revoke:回收权利;commit:提交事务;rollback:回滚事务

4.表

5.表结构

二表-示例

1.Scott用户表

2.三范式

每一列都是不可在分隔的基本数据项

解释:该列不可在分成更为基础的数据项,假设有A B C三项,每项不可在分为A1 A2 B1 B2 C1 C2
每一行都可以被唯一的区分,通常设置一个主关键字或主键、主码,即非主属性非部分依赖于主键

解释: 非主键的列必须完全依赖于主键，而不能仅仅是依赖主键的其中某个列。假设A是主键,B和C必须完全依赖于A列,其他情况不考虑.
一个数据库表中不包含已在其它表中已包含的非主关键字信息即属性不依赖与其他非主属性

解释:非主键列必须直接依赖主键，不能存在传递依赖。换句话说，就是假设A是主键，那么B，C都是直接依赖A，而不能是C依赖B，B依赖A这种传递依赖方式

待会我们要使用这个表单,请先在数据库中执行下行命令

Rem Copyright (c) 1990 by Oracle Corporation
Rem NAME
REM    UTLSAMPL.SQL
Rem  FUNCTION
Rem  NOTES
Rem  MODIFIED
Rem	gdudey	   06/28/95 -  Modified for desktop seed database
Rem	glumpkin   10/21/92 -  Renamed from SQLBLD.SQL
Rem	blinden   07/27/92 -  Added primary and foreign keys to EMP and DEPT
Rem	rlim	   04/29/91 -	      change char to varchar2
Rem	mmoore	   04/08/91 -	      use unlimited tablespace priv
Rem	pritto	   04/04/91 -	      change SYSDATE to 13-JUL-87
Rem   Mendels	 12/07/90 - bug 30123;add to_date calls so language independent
Rem
rem
rem $Header: utlsampl.sql 7020100.1 94/09/23 22:14:24 cli Generic<base> $ sqlbld.sql
rem
SET TERMOUT OFF
SET ECHO OFF

rem CONGDON    Invoked in RDBMS at build time.	 29-DEC-1988
rem OATES:     Created: 16-Feb-83

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
CONNECT SCOTT/TIGER
DROP TABLE DEPT;
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
	(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
	(
	ENAME VARCHAR2(10)	,
	JOB VARCHAR2(9)  ,
	SAL NUMBER,
	COMM NUMBER
	) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
	LOSAL NUMBER,
	HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

SET TERMOUT ON
SET ECHO ON

三 SELECT

1.SELECT语法

select distinct *|字段|表达式 as 别名 from 表名表别名

SELECT * FROM 表名; ---------------------------查询某个表中所有的记录的所有字段信息
SELECT 列名 FROM 表名; -----------------------查询某个表中所有的记录的指定字段信息
SELECT 列名1,列名2 FROM 表名;------------- 查询某个表中所有的记录的字段1 字段2
SELECT distinct 列名 FROM 表名;-------------去除重复记录
SELECT 表达式 FROM 表名;----------------------查询表达式
SELECT xxx as 别名 FROM 表名表别名------使用别名

2.查询列(字段)

1)查询部分列

-- 1)、检索单个列
select ename from emp; --查询雇员姓名
-- 2)、检索多个列
select deptno,dname,loc from dept; --查询部门表的deptno,dname, loc 字段的数据。
-- 以下查询的数据顺序不同(查询的字段顺序代表数据顺序)
select loc,dname,deptno from dept;
select deptno,dname,loc from dept;

2)查询所有列

查询所有的字段通配符 *( 书写方便、可以检索未知列;但是降低检索的性能 ) ,数据的顺序跟定义表结构的顺序一致:

--1)、检索所有列1
select * from dept; --查询部门的所有信息
--2)、检索所有列2
select deptno,dname,loc from dept; --查询部门的所有信息

3)去除重复

使用distinct去重，确保查询结果的唯一性 :

select distinct deptno from emp; --去重

4)别名

使用别名便于操作识别、隐藏底层信息。存在字段别名和表别名：

select ename as "雇员 姓名" from emp;
select ename "雇员姓名" from emp;
select ename 雇员姓名 from emp;
select ename as 雇员姓名 from emp;
select ename as " Ename" from emp;

as: 字段别名可以使用as;表别名不能使用as
“”:原样输出，可以存在空格与区分大小写

5)字符串

使用’’表示字符串(注意区分””) ，拼接使用 ||

select 'my' from emp;
select ename||'a'||'-->' info from emp;

6)伪列

不存在的列，构建虚拟的列

select empno, 1*2 as count,'cmj' as name,deptno from emp;

解释: count和name 都是不存在的列,我们虚构了两列,里面存的值分为别名前面的值,即 count列下的值为 2,name下面的值为字符串’cmj’

7)虚表

用于计算表达式，显示单条记录的值

select 1+1 from dual;

Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中

8)null

null 遇到数字参与运算的结果为 null,遇到字符串为空串

select 1+null from dual;
select '1'||null from dual;
select 1||'2'||to_char(null) from dual; select ename,sal*12+comm from emp;
--nvl内置函数，判断是否为null,如果为空，取默认值0，否则取字段实际值 
select ename,sal*12+nvl(comm,0) from emp;

遇到这种情况,我们可以采用内置函数 NVL(参数1,参数2),如果参数1为null,函数最终的结果为参数2 ,如果参数1不为null,结果还是参数1。

SLELECT示例

--注释
--select *|字段名1 (as) 别名,字段名2,别名2,字段..from 表名
--select 后是结果集
--查询所有员工的姓名
SELECT ENAME FROM EMP;
--查询所有部门的部门信息
SELECT DEPTNO,DNAME,LOC FROM DEPT;
--查询所有员工编号,名字,薪资
SELECT EMPNO,ENAME,SAL FROM EMP;
-------------------------------
SELECT * FROM EMP;
--查询公司的所有部门编号
   --数据来源:deptno
   --查询数据:dept,zmp
SELECT DEPTNO FROM DEPT;

--distinct 去重
--必须是结果集中的多条数据完全相同才能去重

--有员工存在的部门的部门编号
SELECT DISTINCT DEPTNO FROM EMP;

-- 表达式
SELECT DISTINCT 6*3 FROM EMP;

--计算器
SELECT DISTINCT 123*456*789 FROM EMP;

--上面的是伪列,是不存在的字段

--字符串 ''
SELECT DISTINCT '数据' "标题" FROM EMP;


--字符串 拼接 ||
--给所有员工前面加一个'ABC'
SELECT ENAME FROM EMP;
SELECT 'ABC'||ENAME as 员工姓名 FROM EMP;


--别名  如果别名中出现空格等特殊符号,或者想要原封不动显示 使用""  中的内容原封不动使用
--""不是字符串
--给字段别名  字段名 (as) 别名
--给表其别名  表名 别名  切记:不能使用as
--别名不能使用''
SELECT ENAME 姓名,sal "薪水",COMM AS "奖金" FROM EMP E;

--null值
--处理null值 nvl(参数1,参数2),如果参数1为null,函数最终结果为参数2,如果参数不为null,结果还是参数一
--查询所有员工的姓名,薪资和奖金,在原奖金上加100
SELECT ENAME,SAL,COMM,NVL(COMM,0)+100 AS "实际奖金" FROM EMP;


--null值和数字运算还是null
SELECT ENAME,SAL,COMM,COMM+100 FROM EMP;
--null值和字符串运算还是原串
SELECT ENAME,SAL,COMM||100 FROM EMP;--这样是拼接每个字符
--虚表 DUAL
SELECT 3*3 FROM DUAL;
SELECT SYSDATE FROM DUAL;

--课后作业
SELECT * FROM EMP;
--查询所有员工的名字, 工种, 年薪(不带奖金)
SELECT ENAME,JOB,SAL FROM EMP;
--查询所有员工的名字,工种,年薪(带12月奖金的)
SELECT ENAME,JOB,SAL,COMM,12*(SAL+NVL(COMM,0)) "年薪" FROM EMP;
--查询所有员工的名字, 工种, 年薪(带一次奖金的)
SELECT ENAME,JOB,SAL,COMM,SAL*12+NVL(COMM,0) "年薪" FROM EMP;

四查询行(记录) 其实就是通过判断是否符合当前条件

where过滤行记录条件,条件有

形式:select 字段名1,字段名2… from 表名判断条件

类型	符号
比较	= 、>、 <、 >=、 <=、 !=、 <>
且或非	and、 or、 not
null	null不能使用条件判断，只能使用is
集合	Union:并集、Union All全集、Intersect交集、Minus差集
模糊查询	like %零个及以上(任意个数的)字符 _:一个字符
匹配所有	*

a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and
b)、and 、or、 not、 union、 union all、 intersect 、minus
c)、null :is null、 is not null、 not is null
d)、like :模糊查询 % _ escape('单个字符')
f)、in 、 exists(难点) 及子查询

1)比较

使用比较运算符

--查询不是20部门的员工信息
select * from emp where deptno !=20;
select * from emp where deptno <>20;
select * from emp where sal between 800 and 950; --between and是成对出现的
错误示例:
(select ename,job,12*(nvl(comm,0)+sal) income from emp) where income>2000;
--因为是先执行的where,在再执行的select,所以不能先识别 别名>2000的数据
--查询 员工的年薪大于20000的 员工名称、岗位 年薪


-- 查询20部门的员工信息
SELECT * FROM EMP WHERE DEPTNO=20;
-- 查询工资大于1000的员工的姓名 工作岗位  工资   所属部门编号
--查询数据:姓名 工作岗位 工资 所属编号
--查询来源:EMP
--查询条件:工资大于1000
SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE SAL>1000;

-- 查询不在20部门工作的员工信息
--方式一,不等于
SELECT * FROM EMP WHERE DEPTNO!=20;
--方式二,大于小于 必须是大于小于
SELECT * FROM EMP WHERE DEPTNO<>20;
--方式三,取反
SELECT * FROM EMP WHERE NOT DEPTNO=20;


-- 工资在2000到3000之间的员工信息 字段放在WHERE后面
SELECT * FROM EMP WHERE SAL>=2000 AND SAL<=3000;
SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

2)且或非

使用且(and)查询

-- 工资在2000到3000之间的员工信息 字段放在WHERE后面
SELECT * FROM EMP WHERE SAL>=2000 AND SAL<=3000;
SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;
---查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资
SELECT ENAME,DEPTNO,SAL FROM EMP WHERE JOB='CLERK' AND DEPTNO=20;

-- 查询 岗位 不为 CLERK 并且部门编号不为 20的员工名称 部门编号，工资
     --方式一:并的关系
     SELECT ENAME,DEPTNO,SAL,JOB FROM EMP WHERE DEPTNO!=20 AND JOB!='CLERK';

使用或(OR)查询,结果与IN(参数1,参数2)相等

--检索 工资 2000, 3000员工名称 岗位 工资
SELECT * FROM EMP WHERE SAL=2000 OR SAL=3000;
SELECT * FROM EMP WHERE SAL IN (2000,3000);
-- 查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号，工资
SELECT ENAME,DEPTNO,SAL FROM EMP WHERE JOB='CLERK' OR DEPTNO=20;

使用非(not)查询

-- 查询 岗位 不为 CLERK 并且部门编号不为 20的员工名称 部门编号，工资
     --方式二:取反
     SELECT DISTINCT ENAME,DEPTNO,SAL,JOB FROM EMP WHERE NOT(DEPTNO=20 OR JOB='CLERK');

3)null

----存在佣奖金的员工名称 与null比较必须用is,如果是not 必须放在 字段名的前面,对于null值的判断要用is
SELECT ENAME FROM EMP WHERE COMM IS NOT NULL;
SELECT ENAME FROM EMP WHERE NOT COMM IS NULL;
--不存在奖金的员工名称
SELECT ENAME FROM EMP WHERE COMM IS NULL;

注意:对于null值的比较一定要使用is,不能使用=。

4)集合操作

并集(去重)

– Union，并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序；

--连接部门为30或工资大于2000的员工信息
SELECT * FROM EMP WHERE DEPTNO=30
UNION
SELECT * FROM EMP WHERE SAL>2000;

全集(不去重)

– Union All，全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序；

--连接部门为30和工资大于2000的员工信息
--不能用UNION ALL会重复,重复添加了 员工部门为30且工资大于2000的人员
SELECT * FROM EMP WHERE DEPTNO=30
UNION ALL
SELECT * FROM EMP WHERE SAL>2000;

交集(找出重复)

– Intersect，交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

差集(减去重复)

–Minus，差集(减去重复) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序

5)like:模糊查询

--LIKE后面加''代表字符串
--like 模糊匹配  %任意个任意字符     _一个任意字符
SELECT * FROM EMP WHERE ENAME LIKE 'SMITH';--精确匹配
--查询公司中员工姓名 以A开头的员工信息
SELECT * FROM EMP WHERE ENAME LIKE 'A%';
--查询公司中员工姓名 第二个字符为A的员工信息
SELECT * FROM EMP WHERE ENAME LIKE '_A%';

6)获取所有行的记录

--查询所有的行记录
--方式一
SELECT EMPNO,ENAME FROM EMP;--推荐使用字段名,非*因为效率高
--方式二
SELECT * FROM EMP;
SELECT * FROM EMP WHERE 1=1;
--为什么采用WHERE拼接呢,这个是因为 在拼接字符串的时候,可以直接省略where判断

--方式三
SELECT * FROM EMP WHERE ENAME LIKE '%';

拼接字符串sql

//例如:判断一个和数组中是否有值
sql+"where dname ="+dname1+"or dname ="+daname;
//如果在sql语句中就直接采用where 1=1,后面就可以写成下列形式
sql+"or "+danme+"or "+dname

in exits与排序

IN(参数1,参数2)

-- in (值1,值2,值3...) 当对一个字段的多个值进行等值匹配,可以使用in
-- 查询工资为i 1500， 2000， 2500， 5000的员工的信息
SELECT * FROM EMP WHERE SAL IN (1500,2000,2500,5000);

子查询

子查询的定义: 子查询是将一个查询语句嵌套在另一个查询语句中；

特点（规范）：

①子查询必须放在小括号中

②子查询一般放在比较操作符的右边，以增强代码可读性

③子查询(小括号里的内容)可出现在几乎所有的SELECT子句中(如：SELECT子句、FROM子句、WHERE子句、ORDER BY子句、HAVING子句……)

示例一

--部门名称为 SALES 或 ACCOUNTING 的雇员信息
--我们EMP表中,没有部门名称的信息,但是可以通过DEPT表获取部门编号,在通过筛选部门编号筛选出符合要求的雇员信息
--拆分 部门名称 在 DEPT 表中,雇员信息在 EMP表中,可以通过 部门编号连接起来
     --第一步:先从 DEPT 表,获取对应的部门编号
     SELECT DEPTNO FROM DEPT WHERE DNAME IN ('SALES','ACCOUNTING');
     --第二部:从  EMP表中 筛选出符合条件的雇员信息
     SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME IN ('SALES','ACCOUNTING'));
--子查询
SELECT *
  FROM EMP
 WHERE DEPTNO IN
       (SELECT DEPTNO FROM DEPT WHERE DNAME IN ('SALES', 'ACCOUNTING'));

示例二

--查询工资等级为 2的员工信息
--查询的数据 :员工信息
--数据的来源 : emp员工表
--条件 :工资等级 = 2
--工资等级为2的最低薪资和最高薪资
     --第一步 先判断等级为2的工资区间
     SELECT LOSAL FROM SALGRADE WHERE GRADE=2;
     SELECT HISAL FROM SALGRADE WHERE GRADE=2;
     --第二部,取出每个人的薪水,在于工资区间做比较
     SELECT * FROM EMP WHERE SAL BETWEEN (SELECT LOSAL FROM SALGRADE WHERE GRADE=2) AND ( SELECT HISAL FROM SALGRADE WHERE GRADE=2);

示例三

-- 查询 销售部（SALES） 中 工资大于1500的员工信息
   --第一步查询销售部的对应的部门编号
   SELECT DEPTNO FROM DEPT WHERE DNAME='SALES';
   --第二部 判断是否大于1500
   SELECT * FROM EMP;
   SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES') AND SAL>1500;

EXITS(结果集)

示例一

--从from中拿出一条数据 去where 中进行判断 ,如果exists(结果集)中有值,当前这一条就保留|满足条件,如果exists(结果集)中没有值,当前这一条就不保留
--结果:查询出所有的结果
select *
  from emp
 where exists
 (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));
--结果:空的
select * from emp where exists (select * from emp where deptno = 40);

--'SALES', 'ACCOUNTING'的员工信息
select *
  from emp e
 where exists (select deptno, dname
          from dept d
         where dname in ('SALES', 'ACCOUNTING') --(10,30)
           and e.deptno != d.deptno);
/*判断上述代码是 
        比如e.depton  d.deptno=(10,30)
        第一条是 10 ,10==10 但  10!=30                                       
        第二条是 20 ,20!=10 且  20!=30  
        第三条是 30 ,30!=10 但  30==30
        只要一个满足 就可以了 
*/

示例二

--查询20部门的员工信息
select *
  from emp e
 where not exists (select deptno, dname
          from dept d
         where dname in ('SALES', 'ACCOUNTING') --(10,30)
           and e.deptno = d.deptno);

五单行函数

单行函数:一条记录返回一个结果

--函数
--内置函数和自定义函数
--单行函数 : 一条记录返回一个结果的
--多行函数|组函数|聚合函数 : 多条记录返回一条结果的

-- 当前时间
SELECT DISTINCT SYSDATE FROM EMP;
SELECT SYSDATE FROM DUAL;
SELECT CURRENT_DATE FROM DUAL;
--加减日期
--2天以后是几号
SELECT SYSDATE+2 FROM DUAL;
--所有员工入职前三天是几号
SELECT ENAME,HIREDATE,HIREDATE-3 FROM EMP;
--add_months(日期对象,月份数)
-- 查询所有员工的试用期期到期（转正的日期） 3个月试用期
SELECT ENAME,HIREDATE,ADD_MONTHS(HIREDATE,3) FROM EMP;

--months_between(大月份,小月份)
-- 查询所有员工到目前为止一共工作了几个月
SELECT HIREDATE,SYSDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;

--last_day()
-- 查询当前月的最后一天
SELECT ENAME,HIREDATE, LAST_DAY(HIREDATE) FROM EMP WHERE HIREDATE IS NOT NULL;

--next_day('星期三')
-- 下一个星期三是几号(即将要过的星期三)
SELECT NEXT_DAY(SYSDATE,'星期三') FROM DUAL;


--to_date(数据,格式)
--to_char(数据,格式)
-- 设定一个特定的时间（用一个特定的时间字符串转换为日期） 日期才可以直接进行加减操作
-- 设定一个时间   就是今天 '2018-9-5 16:18:25'
SELECT TO_DATE('2012/9/5 16:18:25','YYYY/MM/DD HH24:MI:SS')+3 FROM DUAL;
-- 将日期转为特定格式的字符串 
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh12:mi:ss') from dual;
SELECT TO_CHAR(SYSDATE,'YYYY"年"mm"月"dd"日" hh12:mi:ss') FROM DUAL;

--判定函数   decode(判定字段,值1,结果1,值2,结果2,值3,结果3....(,默认结果))
--给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十...
select deptno,dname,loc, decode(deptno,10,'十',20,'二十',30,'三十','四十')  中文部门编号 from dept;
SELECT DEPTNO,DNAME,LOC,DECODE(DEPTNO,10,'十',20,'二十',30,'三十','四十') FROM DEPT;

-- 给20部门的所有员工都涨薪10%，显示出员工的名称， 原来的薪水， 所属部门编号， 涨薪后的薪水
SELECT ENAME,SAL,DEPTNO,1.1*SAL as "涨薪" FROM EMP WHERE DEPTNO=20;
-- 10部门涨薪10%， 20涨薪20%，30降薪1% ， 40部门翻倍3倍
SELECT ENAME,DEPTNO,SAL,DECODE(DEPTNO,10,1.1*SAL,20,1.2*SAL,30,0.99*SAL,40,3*SAL) "新增" FROM EMP;

六组函数

类型	符号
计算个数	count()
最大值和最小值	max() min()
求和	sum()
平均	avg()

count()

--组函数|聚合函数|多行函数 : 对结果集进行组函数计算
--多行记录返回一个结果 
--count(条件) sum(条件) max() min() avg()
--注意: 组函数不能和非分组字段一起使用
-- 统计一下一共有多少个员工
SELECT COUNT(EMPNO) FROM EMP;
SELECT COUNT(DEPTNO) FROM EMP;
SELECT COUNT(*) FROM EMP;
SELECT COUNT(1) FROM EMP;
-- 统计一共有几个部门 
SELECT COUNT(1) FROM DEPT;
SELECT COUNT(DEPTNO) FROM DEPT;
-- 统计有员工存在的部门总数
--查询有员工存在的部门编号的结果集,对这个结果集求个数
   --方法一
   SELECT COUNT(DISTINCT DEPTNO) FROM EMP;
   --方法二 把有员工存在的部门 作为 判断条件
   SELECT COUNT(1)
          FROM DEPT
          WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP);

-- 统计20部门一共有多少人
SELECT COUNT(1) FROM EMP WHERE DEPTNO=20;

max()和min()

-- 查询本公司的最高工资和最低工资
SELECT MIN(SAL) FROM EMP;
SELECT MAX(SAL) FROM EMP;

--查看30部门的最高工资和最低工资
SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30;
SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30;

sum()

-- 计算本公司每个月一共要在工资上花费多少钱
SELECT SUM(12*SAL+NVL(COMM,0)) FROM EMP;

-- 计算20部门每个月的工资花销
SELECT SUM(12*SAL+NVL(COMM,0)) FROM EMP WHERE DEPTNO=20;

-- 计算出所有员工的奖金总和  null 不参与运算
SELECT SUM(12*SAL+NVL(COMM,0)) FROM EMP;
-- 统计一共有多少个员工 null 不参与运算
SELECT COUNT(1) FROM EMP WHERE COMM IS NULL;
-- 统计有奖金的员工有几个
SELECT COUNT(1) FROM EMP WHERE COMM IS NOT NULL;

avg()

-- avg 平均工资
SELECT AVG(SAL) FROM EMP;

-- 请查询出 20部门的平均工资
SELECT AVG(SAL) FROM EMP WHERE DEPTNO=20;

作业

示例一难点

--条件:高于本部门薪水
       --第一步,查询某一部门的平均薪资
       SELECT AVG(SAL) FROM EMP WHERE DEPTNO=20;
       --第二步,比较该部门下的员工是否大于平均薪水
       SELECT ENAME,SAL,DEPTNO FROM EMP E1 WHERE SAL>(SELECT AVG(SAL) FROM EMP E2 WHERE E1.DEPTNO=E2.DEPTNO);

以后凡是遇到比较本部门的数据的时候,一定要想到是动态的比较,那么就是可以外部的某条数据与内部的某条数据进行比较

七分组

分组: group by 分组字段
查询公式:select 数据 from 数据来源 where 行过滤条件 group by 分组字段1,… having 过滤组信息(以组为单位过滤) order by 排序字段…;
执行流程: from – where --group by --having --select – order by
注意:
1)select 后如果出现了组函数|分了组,组函数不能与非分组字段,可以与其他组函数或分组字段一起使用
2)where 后不能使用组函数因为还没有组,执行流程问题

代码

--分组: group by 分组字段
--查询公式:select 数据 from 数据来源 where 行过滤条件 group by 分组字段1,.. having 过滤组信息(以组为单位过滤) order by 排序字段..;
--执行流程: from -- where --group by --having --select  -- order by
--注意:
--      1)select 后如果出现了组函数|分了组,组函数不能与非分组字段,可以与其他组函数或分组字段一起使用
--      2)where 后不能使用组函数   因为还没有组,执行流程问题       

--求出所有有员工存在的部门编号
SELECT DISTINCT DEPTNO FROM EMP;
-- 找出20部门和30部门的最高工资 
SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20;
SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30;
--20部门和30部门中的所有员工中的最高工资 
SELECT MAX(SAL) FROM EMP WHERE DEPTNO IN (20,30);
--找出20部门和30部门中每个部门的最高工资 
SELECT MAX(SAL),DEPTNO FROM EMP WHERE DEPTNO IN (30,20) GROUP BY DEPTNO;
-- 求出每个部门的平均工资
   --数据:每组的平均薪资
   --来源:员工表
   --条件:一个部门一个平均薪资,以部门为单位,如果不分组函数对所有满足条件的数据进行计算,如果分组了以组为单位
   SELECT AVG(SAL),DEPTNO FROM EMP GROUP BY DEPTNO;
   
   SELECT AVG(SAL),DEPTNO FROM EMP WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP) GROUP BY DEPTNO; 
-- 求出每个部门员工工资高于1000的的部门平均工资
   --数据:平均工资
   --来源:员工表
   --条件:每个部门员工工资高于1000,SAL>1000,以部门为单位,按照部门进行分组
   select avg(sal),deptno from emp where sal>1000 group by deptno;
   
   
-- 求出10和20部门部门的那些工资高于1000的员工的平均工资
   --数据:平均工资
   --来源:员工表
   --条件:10和20部门,且员工工资高于1000
         --先罗列出要的数据,在判断,首先工资要大于1000,然后进行分组,我们只要其中两组
         select deptno,avg(sal) from emp where sal>1000 group by deptno having deptno in(10,20); 
   --不推荐使用,效率相对较低 重练
   --先求出每个部门中大于1000工资的员工的平均工资,在把它作为结果集
   select * from (select avg(sal),deptno from emp where sal>1000 group by deptno) where deptno in(10,20);
-- 找出每个部门的最高工资
   SELECT MAX(SAL),DEPTNO FROM EMP GROUP BY DEPTNO;

-- 求出每个部门的平均工资高于2000的部门编号和平均工资
   select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
   SELECT AVG(SAL),DEPTNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2000;
   select avg(sal),deptno from emp group by deptno having avg(sal)>2000 ;
   select * from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal>2000;
   
--练习:
--按 部门岗位(job) 查询 平均工资和工种
   --数据:平均工资和工种
   --来源:emp表
   --条件:平均工资
   SELECT JOB,AVG(SAL) FROM EMP GROUP BY JOB;
   
   
   
--按 岗位查询 平均工资，且平均工资大于2000的岗位
   SELECT JOB,AVG(SAL) FROM EMP GROUP BY JOB HAVING AVG(SAL)>2000;

--查询 最低平均工资的部门编号
     --数据:部门编号
     --来源:EMP表
     --条件:最低平均工资
     
     --思路:
     --第一步:找所有部门的平均薪资和部门编号
     SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;
     --第二步:找最低工资的部门的工资 一个值
     SELECT MIN(AVG(SAL)) FROM EMP GROUP BY DEPTNO;
     --第二步:再找与最低工资匹配的 部门编号  先求部门平均薪资,部门最低平均薪资,然后 对比 找出
     SELECT * FROM (SELECT DEPTNO,AVG(SAL) AS AVG_SAL FROM EMP GROUP BY DEPTNO) WHERE AVG_SAL=(SELECT MIN(AVG(SAL)) FROM EMP GROUP BY DEPTNO);
     
-- 统计每个部门的员工数，和部门编号,按照员工个数升序排序
   SELECT DEPTNO,COUNT(ENAME) FROM EMP GROUP BY DEPTNO ORDER BY COUNT(ENAME);
   
   
   
-- 查询平均工资在1500到2000之间的部门平均工资和部门编号
   --查询:部门编号,平均工资
   --来源:EMP表
   --条件:在1500和2000之间
   --第一步:先求出所有部门的平均薪资和部门
   SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;
   --第二步:在判断是否区间在15000在2000之间
   SELECT DEPTNO,X  FROM (SELECT DEPTNO,AVG(SAL) X FROM EMP GROUP BY DEPTNO) WHERE X BETWEEN (1500) AND (2500);

-- 查询工资高于20部门平均工资的员工
   --数据:员工信息
   --来源:EMP
   --条件:高于20部门的平均工资
   --第一步:先求20部门的平均工资
   SELECT AVG(SAL) FROM EMP WHERE DEPTNO=20;
   
   --第二步:对比每个员工的工资
   SELECT * FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=20);

八行转列

代码

/*
id name course score
1 张三 语文 81
2 张三 数学 75
3 李四 语文 81
4 李四 数学 90
5 王五 语文 81
6 王五 数学 100
7 王五 英语 90
*/
--创建表
create table tb_student(
 id number(4) ,
 name varchar2(20),
 course varchar2(20),
 score number(5,2)
);
--插入数据
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit; --提交
--删除表
DROP TABLE TB_STUDENT CASCADE CONSTRAINTS;
--查询表
SELECT * FROM TB_STUDENT;

--使用一条sql语句，查询每门课都大于80分的学生姓名
   --数据:学生姓名
   --来源:TB_STUDENT
   --条件:1.学生考试科目 等于 最多考试科目
    ------2.这个人所有的考试科目的最低分大于80
    
    --先计算考试科目 总数
    SELECT COUNT(DISTINCT COURSE) FROM TB_STUDENT;
    --求每个人的最低分
    SELECT NAME,MIN(SCORE) FROM TB_STUDENT GROUP BY NAME;
    --求每个人的科目数
    SELECT NAME,COUNT(DISTINCT COURSE) FROM TB_STUDENT GROUP BY NAME;
    --拼接满足上述两个条件的
    SELECT NAME FROM TB_STUDENT GROUP BY NAME HAVING COUNT(DISTINCT COURSE)=(SELECT COUNT(DISTINCT COURSE) FROM TB_STUDENT)
    AND MIN(SCORE)>80;
    --重打 按名字分组,比较每个人的科目和所有科目 每个人最低分数(组分数)和最低分数
    SELECT NAME FROM TB_STUDENT GROUP BY NAME HAVING COUNT(DISTINCT COURSE)=(SELECT COUNT(DISTINCT COURSE) FROM TB_STUDENT)
    AND MIN(SCORE)>80;
    
    SELECT * FROM TB_STUDENT;
    --decode() 是单行函数
    SELECT NAME,DECODE(COURSE,'语文',SCORE) 语文,DECODE(COURSE,'数学',SCORE) 数学,DECODE(COURSE,'英语',SCORE) 英语 FROM TB_STUDENT;
    select name,decode(course,'语文',score) 语文,decode(course,'数学',score) 数学,decode(course,'英语',score) 英语 from tb_student;
    --行转列 
    select name,max(decode(course,'语文',score)) 语文,min(decode(course,'数学',score)) 数学,max(decode(course,'英语',score)) 英语 from tb_student group by name;
    SELECT NAME,MIN(DECODE(COURSE,'语文',SCORE)) 语文,MAX(DECODE(COURSE,'数学',SCORE)) 数学,AVG(DECODE(COURSE,'英语',SCORE)) 英语 FROM TB_STUDENT GROUP BY NAME;

九 rowid和rownum

rowid 和 rownum 都是伪列
rowid理解为记录在插入到数据库的表中时候就存在的数据的地址(对象的地址),其实不是地址,根据地址得到的值
如果一个表中没有主键,没有不可重复的字段,可能会出现多条一模一样的数据,无法区分重复数据,可以根据rowid进行区分

rowid

代码

--rowid 和 rownum 都是伪列
--rowid理解为记录在插入到数据库的表中时候就存在的数据的地址(对象的地址),其实不是地址,根据地址得到的值
--如果一个表中没有主键,没有不可重复的字段,可能会出现多条一模一样的数据,无法区分重复数据,可以根据rowid进行区分

select deptno,dname,loc,rowid from dept;
select empno,ename,rowid from emp;

insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);

insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);

insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
--重复插入数据
select * from tb_student;

--当一个表中有多条一模一样的数据的时候,实现去重,重复数据只保留一条


--去重方法一:采用 distinct
       --去重,保留不重复的数据
       select distinct id,name,course,score from tb_student;
       --查到要删除的数据  无法做到
       --select * from tb_student where not id in (select distinct id,name,course,score from tb_student);
       --此方法无法查询到要删除的数据
--去重方法二:采用rowid(唯一值)
       --去重,保留不重复的数据
                --第一步:先根据 name course score进行分组,求出每个组中最小的rowid
                 select min(rowid) from tb_student group by id,name,course,score;
                --第二步: 根据rowid的值,判断出需要的不重复的数据
                select * from tb_student where rowid in (select min(rowid) from tb_student group by id,name,course,score);
       --查到要删除数据
       select * from tb_student where not rowid in (select min(rowid) from tb_student group by id,name,course,score);
       --删除这些数据
       delete  from tb_student where not rowid in (select min(rowid) from tb_student group by id,name,course,score);
       --查询表
       select * from tb_student;

rownum

rownum为结果集的序号,select查到的结果

rownum从1开始每次+1,要保证rownum从1开始

代码

--分页:在oracle中使用rownum.因为rownum规律可循,控制rownum序号从1开始,每次+1,方便判断
--查询
--如果根据主键字段进行排序,先排序后确定rownum
select deptno,dname,rownum from dept order by deptno desc;
--如果根据非主键字段进行排序,先确定rownum 后排序
select deptno,dname,rownum from dept order by dname desc;

--保证一定先排序后确定rownum
select deptno,dname,rownum num from dept order by loc;     
--在结果集的外边 在套一个 select
select rownum,deptno,dname,num from(select deptno,dname,rownum num from dept order by loc);


--根据薪资降序,查看rownum
--明显工资从小到大排序,但是rownum的顺序混乱
select empno,ename,sal,comm,rownum num from emp order by sal desc;
--在结果集的外边套 套 通往rownum,可以实现 rownum是从小到大排序
select empno,ename,sal,comm,rownum,num from (select empno,ename,sal,comm,rownum num from emp order by sal desc);

--分页需求
--i 页数 num每页显示几个
--num=3 i=4
--每一页要实现的数据 rownum 第一个 rownum>=num*(i-1)+i 最后一个:row<=num*i
select * from (select empno,ename,sal,comm,rownum num2,num from (select empno,ename,sal,comm,rownum num from emp order by sal desc)) where num2>3*(i-1)+
i and num2<=3*i;