Oracle--SQL基本语法

发布于：2025-04-21 ⋅ 阅读:(19) ⋅ 点赞:(0)

 前言：本博客仅作记录学习使用，部分图片出自网络，如有侵犯您的权益，请联系删除

1、SQL语句介绍

在Oracle开发中，客户端把SQL语句发送给服务器，服务器对SQL语句进行编译、执行，把执行的结果返回给客户端。常用的SQL语句大致可以分为五类：

  • 数据定义语言（DDL），包括CREATE（创建）、ALTER（修改）、DROP（删除）命令等
  • 数据操纵语言（DML），包括INSERT（插入）、UPDATE（更新）、DELETE（删除）命令等
  • 数据查询语言（DQL），包括基本查询语句、Order By字句，Group By字句等
  • 事务控制语言（TCL），包括COMMIT（提交）、SAVEPOINT（保存点）、ROLLBACK（回滚）命令
  • 数据控制语言（DCL），GRANT（授权）命令、REVOKE（撤销）命令
1.1、SQL语言的编写规则
  • SQL关键字不区分大小写
  • 对象名和列名不区分大小写
  • 字符值区分大小写
1.2、Oracle字段数据类型
数据类型 类型解释
VARCHAR2(length) 字符串类型：存储可变的长度的字符串，length：字符串的最大长度，默认1，最大长度不超过4000
CHAR(length) 字符串类型：存储固定长度字符串，length：字符串的固定长度大小，默认1，最大长度不超过2000
NUMBER(a,b) 数值类型：存储数值类型，可以存整数、浮点数等。a代表数值的最大位数：包含小数位和小数点，b代表小数的位数。比如： number(6,2)，输入321.456789，实际存入：321.45 number(4,2)，输入12312.345，实际存入：错误，超过存储的指定的精度
DATA 时间类型：存储的是日期和时间，包括年、月、日、时、分、秒。比如： 内置函数sysdate获取的就是DATA类型
TIMESTAMP 时间戳类型：存储的不仅是日期和时间，还包含了时区。比如： 内置函数systimestamp获取的就是timestamp类型
CLOB 大字段类型：存储的是大的文本，比如：非结构化的txt文本，字段大于4000长度的字符串
BLOB 二进制类型：存储的是二进制对象，比如图片、视频、声音转换过来的二进制对象
1.3、Oracle算术运算符

+、-、*、/、mod()

 select 5 + 3 from dual;
 select 5 - 3 from dual;
 select 5 * 3 from dual;
 select 5 / 3 from dual;
 select mod(5,3) from dual;
1.4、Oracle的逻辑运算符
符号 说明 符号 说明
= 等于 <>或者!= 不等于
> 大于 >= 大于或等于
< 小于 <= 小于或者等于
1.5、Oracle的逻辑运算符

AND、OR、NOT

1.6、字符串连接符||
 select 'w' || 123 || 'abc' from dual;

2、创建表

create table stuinfo        --用户名.表名
(
    stuid       varchar2(11) not null,      --学号
    stuname     varchar2(50) not null,      --学生姓名
    sex         char(1) not null,           --性别
    age         number(2) not null,         --年龄
    classno     varchar2(7) not null,       --班号
    stuaddress  varchar2(100) default '地址未录入',      --地址
    grade       char(4)  not null,          --年级
    enroldate   date,                       --入学时间
    idnumber    varchar2(18) default '身份证未采集' not null  --身份证
)

tablespace USERS
    storage
    (
        initial 64K   
        minextents 1
        maxextents unlimited
    );

 --表示表stuinfo存储的表空间是users,storage表示存储参数:区段(extent)一次扩展64k,最小区段数是1,最大的区段数不限制
 ​
 comment on table stuinfo
     is '学生信息表';
 comment on column stuinfo.stuid
     is '学号';
 comment on column stuinfo.stuname
     is '学生姓名';

3、添加约束

约束条件 NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK default
说明 非空约束 唯一约束 主键约束 外键约束 条件约束 默认值 
 --非空约束(NOT NULL)
 ALTER TABLE teacher MOFIFY subject NOT NULL;
 ​
 --主键约束(PRIMARY KEY)
 ALTER TABLE stuinfo ADD CONSTRAINT pk_stuinfor_stuid PRIMARY KEY(stuid);
 ​
 --唯一约束(UNIQUE)
 ALTER TABLE teacher ADD CONSTRAINT uk_teacher_idnumber UNIQUE (idnumber);
 ​
 --外键(FOREIGN KEY)约束
 --dept为主表，emp为从表(外键表),emp中的外键列deptno引用dept的主键
 ALTER TABLE emp ADD CONSTRAINT fk_scoftt_emp_teptno FOREIGN KEY(deptno) REFERENCES scofft.dept(deptno);
 ​
 --条件(CHECK)约束
 --给字段年龄age添加约束，学生的年龄只能0-50岁之内的
 alter table stuinfo add constraint ch_stuinfo_age check(age>0 and age<=50);
 --限定sex的值
 alter table stuinfo add constaint ch_stuinfo_sex check(sex='1' or sex='0');
 --限定年级的范围
 alter table stuinfo add constaint ch_stuinfo_grade check(grade>='1900' and grade<='2999');

4、查询语法

用户对表或视图最常进行的操作就是检索数据。检索数据通过SELECT语句来实现，该语句由多个子句组成，通过这些子句可以完成筛选、投影和连接等各种操作，最终得到用户想要查询的结果。SELECT语句的基本语法格式如下：

 SELECT {[ DISTINCT | ALL ] column | *}  --用于选择数据表、视图中的列
 [INFO table name]                       --用于将员表的结构和数据插入新表中
 FROM {tables | views | other select}    --用于指定数据来源，包括表、视图和其他SELECT字句
 [WHERE conditions]                      --用于对检索的数据进行筛选
 [GROUP BY columns]                      --用于对检索结果进行分组显示
 [HAVING conditions]                     --用于从使用GROUP BY字句分组后的查询结果中筛选数据行
 [ORDER BY columns]                      --用于对结果集进行排序（包括升序和降序）

5、简单查询

只包含SELECT子句和FROM子句的查询就是简单查询，SELECT子句和FROM子句都是SELECT语句必须包含整个两个子句。

创建部门信息表dept、员工信息表emp、工资等级表salgrade，并插入测试数据。

 CREATE TABLE DEPT
     (DEPINO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
     DNAME VARCHAR2(14),
     LOC VARCHAR2(13));
 CREATE TABLE EMP
     (ENPNO 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),
      DEPINO NUMBER(2) CONSTRAINT PK_DEPTNO REFERENCES DEPT);
 CREATE TABLE salgrade (
     grade NUMBER,
     losal NUMBER,
     hisal NUMBER);
     
 --插入测试数据 --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','BOSTOW');
 ​
 ​
 --插入测试数据 --emp
 INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_daate('17-12-1980','dd-mm-yyyy'),800.NULL,20);
 INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_daate('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-07-87','dd-mm-yyyy')-85,3000,NULL,20);
 INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NUL,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-07-87','dd-mm-yyyy')-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);
 ​
 -- 插入测试数据 --salgrade
 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;
5.1、检索所有列
 --SELECT后跟星号 * 检索所有列
 select from * 表名;

FROM子句后面还可以指定多张数据表，每张数据表名之间使用逗号(,)分隔：

 select * from  表名1,表名2,...,表名n;
5.2、检索指定的列

可指定查询表中的某些列（也称为投影操作），而不是全部列，并且被指定列的顺序不收限制。

 select 列名1,列名2,...,列名n from 表名;

在Oracle数据库中，有一个标识行中唯一特性的行标识符ROWID。ROWID是Oracle数据库内部使用的隐藏列，由于该列实际上不是定义在表中，因此也被称为伪列。伪列ROWID长度为18位字符，包含该行数据在Oracle数据库中的物理地址。

 --查询emp表中的rowid,job,ename三列数据
 select rowid,job,ename from emp;
5.3、查询日期列

日期列是指数据类型为DATE的列，查询日期列与查询其他列没有任何区别，但日期列的默认显示格式为DD-MON-RR。可以指定查询数据中日期的显示格式：

（1）以简体中文格式显示日期结果

 --将sql*plus的nls_date_language参数设置为中文格式
 alter session set nls_date_language = 'SIMPLIFIED GHINESE';
 --查询emp表
 select ename,hiredate from emp;

（2）以美式英语格式显示日期结果

 ----将sql*plus的nls_date_language参数设置为美式英语格式
 alter session set nls_date_language = 'AMERICAN';
 --查询emp表
 select ename,hiredate from emp;

（3）以特定格式显示日期结果

 ----将sql*plus的nls_date_format参数设置为xxxx年xx月xx日格式
 alter session set nls_date_format = 'YYYY"年"MM"月"DD"日"';
 --查询emp表
 select ename,hiredate from emp;
带有表达式的SELECT子句

在SELECT语句中，数字和日期既可以使用（+）、减（-）、乘（*）。除（/）和括号等算术运算符

 --将emp表的sal列的数值显式为原值的1.1倍后的值
 select sal*(1+0.1),sal from emp;
5.4、为列指定别名

在Oracle，为列指定别名既可以使用AS关键字，也可以不使用任何关键字而直接指定

 --使用as指定列别名
 select empno as "员工编号",ename as "员工名称",job as "职务" from emp;
 ​
 --不使用关键字而直接指定
 select empno "员工编号",ename "员工名称",job "职务" from emp;
5.5、显示不重复记录

在SELECT语句中，可以使用DISTINCT关键字 来限制显示 重复的数据，该关键字使用在SELECT子句的列表前面

 --查询emp表中的job列，可以看到有重复值
 select job from emp;
 ​
 --使用DISTINCT来去除重复值
 select distinct job from emp;
5.6、处理NULL值

NULL表示未知值，它既不是空格，也不是0。插入数据时，如果没有为特定列提供数据，并且该列没有默认值，那么其结果为NULL。当算术表达式中包含NULL时，如果不处理NULL，显示结果将为空。

 --comm列值为NULL的，计算sal+comm的值也为NULL
 select ename,sal,comm,sal+comm from emp;

可以使用NVL函数 处理NULL；

 --使用NVL函数处理comm列，如果值为数值，返回原数值，如果值为NULL则转换为0
 select ename,sal,comm,sal_nvl(comm,0) from emp;
5.7、连接字符串

连接字符串可以使用"||"操作符或者CONCAT函数。当连接字符串时，如果是在字符串中加入数字值，可以直接指定数字值；而如果是在字符串中加入字符或者日期值，那么必须将值放在单引号中。

 --使用||将ename类与job列进行连接
 select ename||''''||'s job is '||job from emp;

在字符串中使用单引号，需要使用两个连续的单引号来表示一个单引号字符。

 --使用concat连接ename列和sal列
 select concat(concat(ename,'''s salary is '),sal) from emp;

6、筛选查询

SELECT语句中使用WHERE子句实现对数据行的筛选操作，只有满足WHERE子句判断条件的行才会显示在结果集中

 SELECT columns_list         
 FROM table_name
 WHERE conditional_expression
6.1、比较查询

可以在WHERE子句中使用比较运算符来筛选数据，基本的"比较筛选"操作主要有以下6种：

  • A=B：是否相等
  • A!B或A<>B：是否不相等
  • A>B：A是否大于B
  • A<B：A是否小于B
  • A>=B：A是否大于等于B
  • A<=B：A是否小于等于B
 查询emp表中工资(sal)大于2000的数据记录
 select empno,ename,sal from emp where sal > 2000;

除了基本的"比较筛选"操作，还有以下两个特殊的"比较筛选"操作

  • A(operator)ANY(B)：表示A与B中的任何一个元素进行operator运行符的比较，只要有一个比较值为TRUE，就返回数据行
  • A(operator)ALL(B)：表示A与B中的所有元素进行operator运算符的比较，只要与所有元素比较值都为TRUE，才返回数据行
 --示例:使用all关键字过滤工资(sal)同时不等于3000,5000,800的员工记录
 select empno,ename,sal from emp where sal <> all(3000,5000,800)

注意：在进行比较筛选的时候，字符串和日期的值都必须使用单引号标识，否则Oracle会提示标识符无效

6.2、使用特殊关键字筛选

SQL语言提供LIKE、IN、BETWEEN和IS NULL等关键字来筛选数据

（1）LIKE关键字--字符串模式匹配或字符串模糊查询

LIKE关键字 需要使用通配符在字符串内查找指定的模式，主要使用以下两个通配符：

  • %：代表0或多个字符

  • _：代表一个且只能是一个字符

 --查询emp表中以A开头的员工名称
 select empno,ename,job from emp where ename like 'A%';

在LIKE关键字前面加上NOT，表示否定的判断，如果LIKE为真，则NOT LIKE为假。另外，也可以在IN、BWTWEEN、IS NULL和IS NAN等关键字加上NOT来表示否定的判断

 --查询工作是MANAGER的员工，但是不记得MANAGER的准确拼写了，仅记得第一个字母是M，第三个字母是N，第五个字母是G
 select empno,ename,job from emp where job like 'M_N_G%';

LIKE关键字还可以帮助简化某些WHERE子句：

 --在emp表中，显示1981年入职的员工信息
 select empno,ename,sal,hiredate from emp where hiredate like '%81%';

如果要查询的字符串中含有"%"或"_"，可以使用转义escape关键字来实现

 --创建一张与dept表的结构和数据都相同的表dept_temp
 create table dept_temp as select from dept;
 --插入一条数据
 insert into dept_temp values(60,'IT_RESEARCH','SHANGHAI')
 --提交
 commit;
 --显示临时表dept_temp中所有部门名称以IT_开头的数据行;
 --通过转义字符\将_转义为本来的含义下划线而不是单字符通配符
 select * from dept_temp where dname like 'IT\_%' escape '\';
 select * from dept_temp where dname like 'ITa_%' escape 'a';  --使用a作转义符是一样的

没有必要一定使用"\"字符来作为转义符，可以使用任何字符来作为转义符。

（2）IN关键字--测试一个数据值是否匹配一组目标值中的一个

IN关键字 的格式是IN（目标值1，目标值2，目标值3,......）

 --emp表中，使用IN关键字查询服务为CLERK、MANAGER或ANALYST的员工信息
 select empnp,ename,job from emp where job in('CLERK','MANAGER','ANALYST');

NOT IN 表示查询指定的值不在某一组目标值中，这种方式也很常见

 --查询职务不为CLERK,MANAGER或ANALYST的员工信息
 select empno,ename,job from emp where job not in('CLERK','MANAGER','ANALYST');
6.3、逻辑筛选

逻辑筛选是指在WHERE子句中使用逻辑运算符AND、OR和NIT进行数据筛选操作，这些逻辑运算符可以把多个筛选条件组合起来

AND逻辑运算符 表示两个逻辑表达式之间是"逻辑与"关系，可以使用AND运算符加比较运算符来代替BETWEEN...AND关键字

 --在emp表中，使用AND运算符查询工资(sal)为1000-2000的员工信息
 select empno,ename,sal from emp where sal >= 1000 and sal <= 2000;
 ​
 --同样的，使用between查询
 select empno,ename,sal from emp where sal betweem 1000 and 2000;

OR逻辑运算符 表示两个逻辑表达式之间是"逻辑或"的关系，两个表达式的结果中有一个为TRUE，则这个逻辑或表达式的值就为TRUE

NOT逻辑运算符 用于对表达式执行逻辑非运算，即条件取反，如果条件为true，使用not运算符后该条件变为false。

7、分组查询

在查询结果集中使用GROUP BY子句 对记录进行分组。在SELECT语句中，GROUP BY子句位于FROM子句之后：

 SELECT column_list
 FROM table_name
 [WHERE conditional_expression]
 GROUP BY columns_list
7.1、使用GROUP BY子句进行单列分组

单列分组是指基于列生成分组统计结果。进行单列分组时，会基于分组列的每个不同值生成一个统计结果

 --在mep表中，按照部门编号(deptno)列分组，并显示每个部门有几个岗位
 select deptno,count(*) as 岗位数 from emp group by deptno order by deptno;
7.3、使用ORDER BY子句分组排序结果

GROUP BY子句执行分组统计时，会自动基于分组列进行升序排序。使用ORDER BY子句可以改为降序；

 --查询每个部门的部门编号和工资总额按默认排序排列
 select deptno,sum(sal) from emp group by deptno;
 --查询每个部门的部门编号和工资总额并按降序排列
 select deptno,sum(sal) from emp group by deptno order by sum(sal) desc;
7.4、使用HAVING子句限制分组结果

GROUP BY子句进行分组的结果可使用HAVING子句对分组的结果做进一步的筛选

 --筛选部门平均工资高于1000的部门编号和平均工资
 select deptno as 部门编号,avg(sal) as 平均工资
 from emp
 group by deptno
 having avg(sal) > 1000;
7.、在GROUP BY子句中使用ROLLUP和CUBE操作符
（1）使用ROLLUP操作符创建多层次的汇总数据

ROLLUP操作符在Oracle中用于生成分组汇报表，它可以在GROUP BY子句中指定多个列，并生成这些列的所有可能组合的汇总行

 --显示每个部门的平均工资。所有员工的平均工资
 select deptno as 部门编号,job as 岗位,avg(sal) as 平均工资
 from emp
 group by rollup(deptno,job);
（2）使用CUBE操作符根据指定的列表生成不同的排列组合，并根据每一种组合结果生成汇总统计
 --显示各岗位的平均工资、每个部门的平均工资、所有员工的平均工资
 select deptno as 部门编号,job as 岗位,avg(sal) as 平均工资
 from emp
 group by cube(deptno,job);
（3）使用GROUPING函数确定统计结果是否使用了特定列
 --使用GROUPING函数确定统计结果所使用的列
 select deptno,job,sum(sal),grouping(deptno),grouping(job)
 from emp
 group by rollup(deptno,job);
（4）在ROLLUP操作符中使用复合列

复合列被看作是一个逻辑单元的列组合，当引用符合列时，需要用括号括住相关列。通过在ROLLUP操作符中使用复合列，可以略过ROLLUP操作符的某些统计结果

例如，子句GROUP BY ROLLUP(a,b,c)的统计结果等同于GROUP BY(a,b,c),GROUP BY(a,b),GROUP BY a以及GROUP BY()的并集;而如果将(b,c)作为复合列，那么子句GROUP BY ROLLUP(a,(b,c))的结果等同于GROUP BY(a,b,c),GROUP BY a以及GROUP BY()的并集。

 --在emp表中显示特定部门特定岗位的工资总额以及所有员工的工资总额
 select deptno,job,sum(sal) from emp group by rollup((deptno,job));
 --显示特定部门特定岗位的工资总额,部门的工资总额以及所有员工的工资总额
 select deptno,job,sum(sal) from emp group by rollup(deptno,job);

group by rollup((deptno,job))等同于group by(deptno,job)与group by()的并集，即特定部门特定岗位的工资总额与所有员工的工资总额的并集，不包括部门的工资总额；

（5）在CUBE操作符中使用复合列

通过在CUBE操作符中使用复合列，可以略过CUBE操作符的某些统计结果

 例如,子句GROUP BY CUBE(a,b,c)的统计结果等同于GROUP BY(a,b,c)、GROUP BY(a,b)、GROUP BY(a,c)、GROUP BY(b,c)、GROUP BY a、GROUP BY b、GROUP BY c以及GROUP BY()的并集;而如果将(a,b)作为复合列，那么子句GROUP BY CUBE((a,b),c)的结果就等同于GROUP BY(a,b,c)、GROUP BY(a,b)、GROUP BY c以及GROUP BY()的并集。

 --在CUBE操作符中使用复合列，在emp表中显示特定部门特定岗位的工资总额以及所有员工的工资金额
 select deptno,job,sum(sal) from emp group by cube ((deptno,job));
（6）使用GROUPING SETS操作符合并多个分组的统计结果
 --使用部门编号(deptno)执行分组统计每个部门的平均工资
 select deptno,avg(sal) from emp group by deptno;
 --使用岗位(job)显示每个岗位的平均工资
 select job,avg(sal) from emp group by job;
 --使用grouping sets显示部门的平均工资和岗位的平均工资
 select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);

使用grouping sets(deptno,job)即显示了部门的平均工资，又同时显示了岗位平均工资

8、排序查询

在SELECT语句中，可以使用ORDER BY子句对检索的结果集进行排序，该子句位于FROM子句之后，其语法格式如下：

 SELECT columns_list
 FROM table_name
 [WHERE conditonal_expression]
 [GROUP BY columns_list]
 ORDER BY { order_by_expression [ ASC | DESC ]}[,..n ]

ORDER BY子句可以根据查询结果中的一个列或多个列对查询结果进行排序，并且第一个排序项是主要的排序依据，其他的是次要的排序依据。

 --使用默认排序查询
 select deptno,empno,ename from emp;
 --按deptno升序排序
 select deptno,empno,ename from emp order by deptno;
 --按deptno升序排序后，再按empno升序排序（多列排序）
 select deptno,empno,ename from emp order by deptno,empno;

9、多表关联查询

多表关联查询时在关系型数据库中，通过使用多个表之间的关联条件来查询相关的数据。这种查询通常用于解决复杂的数据关系问题，涉及多个表之间的关联和数据整合表的别名

在多表关联查询时，如果多张表之间存在同名的列，则必须使用表名来限制列的引用。与列别名类似，可以给表指定一个别名，使用简短的表别名可以替代原有较长的表名称，可以大大缩减语句的长度

 --指定emp表别名为e,dept表别名为d,查询岗位MANAGER的员工信息及部门
 select e.empno as 员工编号,e.ename as 员工名称,d.dname as 部门名称
 from emp e,dept d
 where e.deptno=d.deptno and e.job='MANAGER';
9.1、内连接

内连接是一种常见的多表关联查询方式，一般使用关键字INNER JOIN来实现。INNER关键字可以省略。内连接使用JOIN指定用于连接的两张表，使用ON指定连接表的连接条件。内连接只显示与连接条件匹配的行，语法：

SELECT columns_list
FROM table_name1 [INNER] JOIN table_name2 ON join_condition;

 --通过deptno字段来连接emp表和dept表，并检索这两张表中相关字段的信息
 select e.empno as 员工编号,e.ename as 员工名称,d.dname as 部门
 from emp e inner join dept d on e.deptno=d.deptno
9.2、外连接

外连接分为以下3类：

  • 左外连接：关键字位LEFT OUTER JOIN或LEFT JOI
  • 右外连接：关键字为RIGHT OUTER JOIN或RIGHT JOIN
  • 完全外连接：关键字为FULL OUTER JOIN或FULL JOIN

外连接不只列出与连接条件匹配的行，还能够列出左表（左外连接时）、右表（右外连接时）中所有符合搜索条件的数据行

（1）左外连接

左外连接的查询结果中不仅包含满足连接条件的数据行，还包括左表中不满足连接条件的数据行。左外连接以左边的表为主表，显示主表所有数据。

 --向emp表中插入新纪录（没有为deptno和dname列插入值，即他们的值为NULL）
 insert into emp(empno,ename,job) values(9527,'EAST','SALESMAN');
 --通过deptno进行emp表和dept表的左外连接
 select e.emptno,e.ename,e.job,d.deptno,d.dname
 from emp e left join dept d on e.deptno=d.deptno

deptno为空的数据也被查询出来，左外连接就是以左边的表emp为主表，显示emp主表的所有记录，以条件e.deptno匹配dept表中是数据，即使EMPNO=9527的记录没有匹配上dept表的记录也会显示出来。

（2）右外连接

右外连接的查询结果中不仅包含满足连接条件的数据行，还包含右表中不满足连接条件的数据行。右外连接以右边的表为主表，显示主表所有数据

--通过deptno进行emp表和dept表的右外连接
select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e right join dept d on e.deptno=d.deptno

在右外连接中也可以使用外连接的连接运算符，外连接的连接运算符为"(+)"，该连接运算符可以放在等号的左边，也可以放在等号的右边，但一定要放在显示较少行（完全满足连接条件行）的一端；

上面的右外连接查询语句还可以这么写

 select e.empno,e.ename,e.job,d.deptno,d.dname
 from emp e right join dept d 
 where e.deptno(+)=d.deptno

(+)在哪个表的列名后面，则另一个表为主表，e.depno(+)=d.deptno则dept为主表，即语句为右连接，e.deptno=d.deptno(+)则emp为主表，即语句为左连接；

（3）完全外连接

在执行完全外连接时，Oracle会执行一个完整的左外连接和右外连接查询，然后将查询结果合并，并消除重复记录行；

 select e.deptno,e.ename,e.job,d.deptno,d.dname
 from emp e full join dept d
 on e.deptno=d.deptno;
（4）自然连接

自然连接是指在检索多张表时，Oracle会将第一张表中的列与第二张表中具有相同名称的列进行自动连接。在自然连接中，用户不需要明确指定进行连接的列，这个任务由Oracle系统自动完成，自然连接使用NATURAL JOIN关键字

 --查询工资(sal)高于1000的记录，并实现emp表与dept表的自然连接
 select empno,ename,job,dname
 from emp natural join dept
 where sal > 1000;

自然连接强制要求表之间具有相同名称的列名称，容易在设计表时出现不可预知的错误，所以在实际应用系统开发中很少使用自然连接

（5）自连接

自连接主要用在自参照表上，显示上下级关系或层次关系。自参照表是指在同一张表的不同列之间具有参照关系或主从关系的表。自连接是在同一张之间的连接查询，必须定义表别名

 --查询所有管理者所管理的下属员工关系
 select em2.ename 上层管理者,em1.ename as 下属员工
 from emp em1 left join emp em2
 on em1.mgr=em2.empno
 order by em1.mgr
（6）交叉连接

交叉连接实际上就是不需要任何连接条件的连接，它使用CROSS JOIN关键字来实现，其语法：

SELECT columns_list
FROM table_name1 CORESS JOIN table_name2

交叉连接的执行结果是一个笛卡尔积，这种查询结果是非常冗余的，但可以通过WHERE子句来过滤出有用的记录信息。

笛卡尔积（Cartesian product）是指在没有明确指定连接条件的情况下，将两个或多个表中的所有行进行组合。这种组合操作不考虑表之间的关联关系，只是简单地将每一行与其他表中的每一行进行组合。

 --计算dept表与emp表的记录两两组合的行数
 select count(*) from dept cross join emp;

可以看到dept表中有4条数据，emp表中有15条数据，交叉连接将dept表中的每一条数据都与emp表中数据进行组合，最终结果为4*15=60条数据

在执行数据操作（增删改查）的过程中，如果某个操作需要依赖另一个SELECT语句的查询结果，那么可以把SELECT语句嵌入该操作语句中，从而形成一个子查询。

10、子查询和关联子查询

子查询是在SQL语句内的另一条SELECT语句，也被称为内查询或内SELECT语句，在SELECT、INSERT、UPDATE或DELETE命令中允许是一个表达式的地方都可以包含子查询，子查询甚至可以包含在另一个子查询中。

 --在emp表中查询部门名称为SALES的员工信息
 select empno,ename,job from emp
 where deptno=(select deptno from dept where dname='SALES');

在emp表中是不存在dname字段（部门名称），但在emp表中存在deptno字段（部门代码）；dname字段、deptno字段存在与dept表中，所有deptno为两张表之间的关联字段。

也可以通过多表关联来实现在emp表中查询部门名称为SALES的员工信息；子查询与多表关联查询能实现一样的查询功能，子查询易读，更容易理解，多表关联查询效率高于子查询；

 select deptno,ename,job from emp
 join dept on emp.deptno=dept.deptno
 where dept.dname='SALES';

在执行子查询操作的语句中，子查询也被称为内查询，包含子查询的查询语句也被称为外查询或主查询

在一般情况下，外查询语句检索一行，子查询语句需要检索一遍数据，然后判断外查询语句的条件是否满足，如果满足则外查询语句将会检索到的数据行添加到结果集中；如果条件不满足，则外查询语句继续检索下一行数据，所以子查询相对多表关联查询要慢一写。

在使用子查询时，需要注意以下规则：

  • 子查询必须使用括号"()"括起来
  • 子查询中不能包括ORDER BY子句
  • 子查询允许嵌套多层，但不能超过255层

在Oracle中，通常把子查询再细化为单行子查询、多行子查询和关联子查询3种

4.1、单行子查询

单行子查询是指返回一行数据的子查询语句。当在WHERE子句中引用单行子查询时，可以使用单行比较运算符（=、>、<、<=、>=和<>）。

 --查询emp表中既不是最高工资，也不是最低工资的员工信息
 select empno,ename,sal from emp
 where sal > (select min(sal) from emp)
 and sal < (select max(sal) from emp);
4.2、多行子查询

多行子查询是指返回多行数据的子查询语句。当在WHERE子句中使用多行子查询时，必须使用多行比较符（IN、ANY、ALL）。

（1）使用IN运算符

当在多行子查询中使用IN运算符时，外查询会尝试与子查询结果中的任何一个结果进行匹配，只要有一个匹配成功，则外查询返回当前检索的记录

 --查询不是研发部门（RESEARCH）的员工信息
 select empno,ename,job
 from emp where deptno in
 (select deptno from dept where dname<>'RESEARCH');
（2）使用ANY运算符

ANY运算符必须与单行操作符结合使用，并且返回行只要匹配子查询的任何一个结果即可。

 --查询工资高于部门编号为20的任何一个员工工资的其他部门的员工信息
 select deptno,ename,sal from emp where sal > any
 (select sal from emp where deptno = 20) and deptno <> 20;
（3）使用ALL运算符

ALL运算符必须与单行运算符结合使用，并且返回行必须匹配所有子查询结果

 --查询工资高于部门编号为30的所有员工工资的员工信息
 select deptno,ename,sal from emp where sal > all
 (select sal from emp where deptno = 30);
4.3、关联子查询

在单行子查询和多行子查询中，内查询和外查询是分开执行的，就是说，内查询的执行与外查询的执行时没有关系的，外查询仅仅是使用内查询的最终结果。

在一些特殊需求的子查询中，内查询的执行需要借助外查询，而外查询的执行又离不开内查询的执行，这时，内查询和外查询是相互关联的，这种子查询就被称为关联子查询。

 --使用关联子查询检索工资高于同职位的平均工资的员工信息
 select empno,ename,sal
 from emp f
 where sal > (select avg(sal) from emp where job = f.job)
 order by job;

在上述查询语句中，内层查询使用关联子查询计算每个职位的平均工资，而关联子查询必须知道职位的名称。为此，外层查询就使用f.job字段值为内层查询提供职位名称，以便于计算出某个职位的平均工资。如果外层查询正在检索的数据行的工资高于平均工资，则会对该行的员工信息进行显示；否则不显示。

注意：在执行关联子查询的过程中，必须遍历数据表中的每条记录，因此如果被遍历的数据表中有大量数据记录，则关联子查询的执行速度会比较缓慢。

11、Oracle集合运算

Oracle集合运算就是把多个查询结果组合成一个查询结果，oralce的集合运算包括：INTERSECT（交集）、UINION ALL(并集重复)、UNION（并集不重复）、MINUS（补集）。表取别名时不要用as,直接用空格

  • interset（交集），返回两个查询共有的记录。
  • union all（并集重复）：返回各个查询的所有记录，包括重复记录
  • union（并集不重复）：返回各个查询的所有记录，不包括重复记录（重复的记录只取一条）
  • minus（补集）：返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

当我们使用Oracle集合运算时，要注意每个独立查询的字段名的列名尽量保持一致（列名不同时，取第一个查询的别名）、列的数据类型、列的个数要一致。不然会报错

 --0、数据准备
 create table emp01 as select * from emp;
 select * from emp           --14条记录
 select * from emp01;        --复制emp表的14条记录
 ​
 --1、INTERSET(交集)，返回两个查询共有的记录
 select * from emp
 interset    --交集
 select * from emp01;        --两个表相同的记录，这里也是14条
 ​
 --2、UNION ALL(并集重复)，返回各个查询的所有记录，包括重复记录
 select * from emp
 union all   --并集重复
 select * from emp01;        --28条记录
 ​
 --3、UNION(并集不重复),返回各个查询的所有记录，不包括重复记录(重复的话只取一条)
 select * from emp
 union       --并集不重复
 select * from emp01         --14条记录
 ​
 --4、MINUS(补集),返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录
 select * from emp
 minus       --补集
 select * from emp01;        --0条记录

12、数据操纵语言DML

插入、删除和更新操作使用的SQL语言，称为数据操纵语言，他们分别对应INSERT、DELETE和UPDATE这3中语句。在Oracle中，DML除了包括上述提到的3种语句，还包括TRUNCATE、CALL、LOCKTABLE和MERGE等语句

12.1、插入语句（INSERT语句）

Oracle数据库通过INSERT语句来实现插入数据记录，该语句既可以实现向数据表中一次插入一条记录，也可以使用SELECT子句将查询结果批量插入数据表中

使用INSERT语句有以下注意事项：

  • 当为数字列增加数据时，可以直接提供数字值，或者用单引号引住
  • 当为字符列或日期列增加数据时，必须使用单引号引住
  • 当增加数据时，数据必须要满足约束规则，并且必须为主键列和NOT NULL列提供数据
  • 当增加数据时，数据必须与列的个数和顺序保持一致
（1）插入单条数据

插入单条数据时INSERT语句最基本的用法，其格式：

 INSERT INTO table_name [(column_name1[,column2]...)] VALUES(express1[,express2]...)

当使用INSERT语句插入数据时，既可以指定列，也可以不指定列。如果不指定列，那么在VALUES子句中必须为每一列提供数据，并且数据顺序必须与列表顺序完全一致；如果指定列，则只需要为相应列提供数据

（2）指定列增加数据
 --向dept表中的deptno,dname两列插入数据
 insert into dept(deptno,dname) values(90,'abc');
（3）不指定列增加数据

可以省略INSERT INTO子句后面的列表清单，这种方法必须根据表中定义的列的顺序，为所有的列提供数据。

 --不指定列，向dept中插入数据
 insert into dept values(88,'design','beijing');
（4）使用特定格式插入日期值

当增加日期数据时，默认情况下日期值必须匹配于日期格式和日期语言，否则在插入数据时会出现错误信息，如果希望使用习惯方式插入数据，那么必须使用TO_DATE函数进行转换

 --使用特定格式插入日期值
 insert into emp (empno,ename,job,hiredate)
 values(1356,'MARY','CLERK',to_date('1983-10-20','YYYY-MM-DD'));
（5）使用DEFAULT提供数据

当指定DEFAULT时，如果列存在默认值，则会使用其默认值；如果列不存在默认值，则自动使用NULL

 --使用DEFAULT插入数据
 insert into dept values(60,'MARKET',DEFAULT);
 select * from dept where deptno = 60;

可看到LOC列没有默认值，DEFAULT自动使用了NULL空值；

（6）批量插入数据

可以使用SELECT语句替换原来的VALUES子句，这样由SELECT语句提供添加的数值，通过INSERT向表中添加一组数据，其语法：

INSERT INTO table_name [(column_name1[,column_name2]...)] selectSubquery

 --创建一个与EMP表结构一样的EMP_TEMP表
 create table EMP_TEMP
 (
     empno   NUMBER(4) not null,
     ename   VARCAHR2(10),
     job     VARCAHR2(9),
     mgr     NUMBER(4),
     hiredate    DATE,
     sal     NUMBER(7,2),
     comm    NUMBER(7,2),
     deptno  NUMBER(2)
 )
 --将emp表中sal大于等于3000的数据插入emp_temp表
 insert into emp_temp select * from emp where sal >= 3000;

INSERT INTO子句指定的列名可以与SELECT子句指定的列名不同，但它们之间的数据类型必须是兼容的

12.2、更新数据（UPDATE语句）

在更新数据时，更新的列数可以由用户自己指定，列与列之间用逗号(，)分隔；更新的条数可以通过WHERE子句来加以限制，使用WHERE子句时，系统只更新符合WHERE条件的记录信息。其语法：

UPDATE table_name SET {column_name=express1[,column_name2=express2...] | (column_name1[,column_name2])=(selectSubquery)}[WHERE condition];

使用UPDATE语句有以下注意事项：

  • 更新数字列时，可以直接提供数字值，或者用单引号引住
  • 更新字符列或日期时，必须用单引号引住
  • 更新数据时，数据必须要满足约束条件
  • 更新数据时，数据必须与列的数据类型匹配
（1）更新单列数据

当更新单列数据时，SET子句后只需要提供一个列

 --将emp表中员工名称ename为SCOTT的工资调整为6000
 update emp set sal = 6000 where ename = 'SCOTT'
（2）更新多列数据

当修改多列时，列之间用逗号隔开

 --将emp表中员工名称ename为JONES的工资sal上调20%，部门编号deptno调整为30
 update emp set sal = sal*1.2,deptno=30 where ename='JONES';
（3）更新日期列数据

当更新日期列数据时，数据格式要与日期格式语言匹配，否则会显示错误信息，可以使用TO_DATE函数进行日期格式转换：

 --将员工编号7788的入职时间hiredate修改为1986/01/01
 update emp set hiredate = TO_DATE('1986/01/01','YYYY/MM/DD') where empno=7788;
（4）使用DEFAULT选项更新数据

可以使用DEFUALT选项提供的数据来更新数据，使用此方法时，如果列存在默认值，则会使用默认值更新数据；如果列不存在默认值，则使用NULL

 --更新员工姓名为SOCTT的岗位为默认值
 update emp set job = DEFAULT where ename = 'SCOTT';
（5）使用子查询更新数据

UPDATE语句也可以与SELECT语句组合使用来达到更新数据的目的

 --将狗子sal低于2000的员工工资调整为管理者的平均工资水平
 update emp set sal = (select avg(sal) from emp where job = 'MANAGER')
 where sal < 2000;

注意：与SELECT语句组合使用时，必须保证SELECT语句返回单一的值，否则会出现错误提示，导致更新数据失败

12.3、删除数据（DELETE语句和TRUNCATE语句）
（1）DELETE语句

DELETE语句用来删除数据库中的所有记录和指定范围的记录

DELETE FROM table_name [WHERE condition];

 --删除员工信息ename为SCOTT的员工信息
 delete from emp where ename='SCOTT';
 ​
 --删除emp表中的所有数据
 delete from emp;

使用DELETE语句删除数据时，Oracle系统会产生回滚记录，所有这种操作可以使用ROLLBACK语句来撤销

（2）TRUNCATE语句

如果确定要删除表中的所有记录，Oracle建议使用TRUNCATE语句，这比DELETE快的多，这是因为使用TRUNCATE语句删除数据时，它不会产生回滚记录。当然，执行了TRUNCATE语句的操作也就无法使用ROLLBACK语句撤销

 --删除emp_temp表的所有数据
 truncate table emp_temp;

在TRUNCATE语句中还可以使用REUSE STORAGE关键字或DROP STORAGE关键字，前者表示删除记录后任然报错记录所占用的空间，后者表示删除记录后立即回收记录占用的空间。默认情况下使用DROP STORAGE关键字。

13、常用系统函数

13.1、Oracle字符型函数
函数 说明
ASCII(X) 求字符X的ASCII码
CHR(X) 求ASCII码对应的字符
LENGTH(X) 求字符串X的长度
CONCATA(X,Y) 返回连接两个字符X和Y的结果
INSTR(X,Y[,START]) 查找字符串X中字符串Y的位置，可以指定从Start位置开始搜索，不填默认从头开始
LOWER(X) 把字符串X中大写字母转换为小写
UPPER(X) 把字符串X中小写字母转换为大写
INITCAP(X) 把字符串X中所有单词首字母转换为大写
LTRIM(X[,Y]) 去掉字符串X左边的Y字符串，Y不填时，默认的是字符串X左边去空格
RTRIM(X[,Y]) 去掉字符串X右边的Y字符串，Y不填时，默认的是字符串X右边去空格
TRIM(X,[,Y]) 去掉字符串X两边的Y字符串，Y不填时，默认的是字符串X左右去空格
REPLACE(X,start[,length]) 查找字符串X中old字符，并利用new字符替换
SUBSTR(X,start[,length])) 截取字符串X，从start位置（其中start是从1开始）开始截取长度为length的字符串，length不填默认为截取到字符串X末尾
RPAD(X,length[,Y]) 对字符串X进行右补字符Y使字符串长度达到length长度
LPAD(X,length[,Y]) 对字符串X进行左补字符Y使字符串长度达到length长度
13.2、日期函数
函数 说明
SYSDATE 得到系统当前时间
SYSTIMESTAMP 得到系统当前时间，该时间包含时区信息，精确到微秒
DBTIMEZONE 返回数据库时区
to_char和to_date 转换日期时间格式
ADD_MONTHS(r,n) 返回在指定日期r上加上月份数n后的日期
LAST_DAY(r) 返回指定r日期的当前月份的最后一条日期
NEXT_DAY(r,c) 返回指定r日期的后一周的与r日期字符（c:表示星期几，1:周天。2：周1）对应的日期
EXTRACT(time) 返回指定time时间当中的年、月、日、分等日期部分
substr(to_char()) 截取年、月、日、时、分、秒
MONTHS_BETWEEN(r1,r2) 返回r1日期和r2日期直接的月份。当r1>r2时，返回的是正数
ROUND(r,[,f]) 将日期r按f的格式进行四舍五入，如果f不填，则四舍五入到最近的一天
TRUNC(r[,f]) 将日期r按f的格式进行截取。如果f不填，则截取到当前的日期
13.3、Oracle数值型函数
函数 说明
ABS(X) 求数值X的绝对值
COS(X) 求数值X的余弦
ACOS(X) 求数值X的反余弦
CEIL(X) 求大于或等于数值X的最小整数
FLOOR(X) 求小于或等于数值X的最大整数
log(x,y) 求x为底y的对数
mod(x,y) 求x除以y的余数
power(x,y) 求x的y次幂
sqrt(X) 求x的平方根
round(x,[,y]) 求数值X在y为进行四舍五入。y不填默认为0，当y>0时，是四舍五入到小数点右边y位；当y<0时，是四舍五入到小数点左边|y|位
trunc(x,[,y]) 求数值X在y位进行直接截取，y不填默认为0，当y>0时，是截取到小数点右边y位；当y<0时，是截取到小数点左边
13.4、Oracle转换函数
  • 1、to_cahr()函数：将DATE或者NUMBER转换为字符串
  • 2、to_date()函数：将number、char转换date
  • 3、to_number()函数：将char转换为number
  • 4、CAST(expr AS type_name)函数：用于将一个内置数据类型或集合类型转变为另一个内置数据类型或集合类型。expr为列名或值，type_name数据类型
 SELECT CAST('123.4' AS int) from dual; --结果:123(可进行四舍五入操作)
 SELECT CAST('123.447654' AS decimal(5,2)) as result from dual;   --结果:123.45
  • 5、TO_MULTI_BYTE(c1)函数：将字符串c1中的半角转化为全角。TO_MULTI_BYTE和TO_SINGLE_BYTE是相反的两个函数
  • 6、TO_SINGLE_BYTE(c1) 函数：将字符串c1中的全角转化为半角
  • 7、TIMESTAMP_TO_SCN(timestamp)函数：用于根据输入的timestamp返回所对应的scn值，其中timestamp用于指定日期时间。作为对于闪回操作（flashback）的一个增强，Oracle10g提供了对于SCN和时间戳进行相互转换
  • 8、SCN_TO_TIMESTANP(number)函数：根据输入的scm值返回对应的大概日期，其中number用于指定scn值
  • 9、CONVERT(string,dest_set[,source_set])函数：将字符串string从source_set所表示的字符集转换为由dset_set所表示的字符集，如果source_set没有被指定，它缺省的被设置为数据库的字符集

ZHS16GBK表示采用GBK编码模式、16位（两个字节）简体中文字符集

WE8ISO8859P1（西欧、8位、ISO标准8859P1编码）

AL32UTF8（其中AL代表ALL，指使用与所有语言）、zhs16cgb231280

13.5、Oracle分析（窗口）函数

分析（窗口）函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于某种统计值，并且每一组的每一行都可以返回一个统计值

分析函数语法

 function_name(<argument>,<argument>...)
 over(<partition_Clause>     --over():开窗函数,partition_...:分区子句
     <order by_Clause>       --排序子句，数据记录集排序
     <windowing_Clause>      --开窗子句，定义分析函数在操作行的集合，三种:rows,range,Specufying
     );

注意：使用开窗子句时一定要有排序子句

13.6、聚合、累计类型分析函数使用
 --1、count(...)over(...);求各部门员工数
 select
 distinct e.deptno deptno,
 d.dname dname,
 count(*)over(partition by e.deptno,d.dname) totar
 from emp e join dept d on e.deptno=d.deptno;
 ​
 --2、sum(...)over(...);求各部门员工递加的工资总和
 select 
 ename,deptno,
 sum(sal) over(partition by deptno order by ename) sun_sal
 from emp
 ​
 --3、avg(...)over(...);求各部门的平均工资
 select
 distinct deptno
 avg(sal)over(partition by deptno) avg_sal
 from emp;
 ​
 --4、min(...)/max(...)over(...);求各职位的最低和最高薪资
 select 
 distinct job,
 min(sal)over(partition by job) min_sal
 max(sal)over(partition by job) max_sal
 from emp;
13.7、排名类型分析函数使用
 --1、整体排名:rank()/row_number()/dense_rank() over(...);按照薪资降序整体排名
 select emp.*,
 rank()over(order by sal desc) rank, --占空排名,跳跃排名,如1-2-2-4-5
 row_number()over(order by sal desc) row_number, --顺序递增(减)排名,如1-2-3-4-5
 dense_rank()over(order by sal desc) dense_rank --不占空排序,如:1-2-2-3-4
 from emp;
 ​
 --2、组内排名:rank()/row_number()/dense_rank()over(...);按照各部门内部薪资降序排名
 select emp.*,
 rank()over(partition by deptno order by sal desc) rank
 row_number()over(partition by deptno order by sal desc) row_number,
 dense_rank()over(partition by deptno order by sal desc) dense_rank
 from emp;
13.8、lag(col,n)、lead(col,n)、ntile(n)、first_value()、last_value()分析函数的使用
  • lag(列名,往前的行数n.[行数为null时的默认值，不指定为null]) --往前第n行
  • lead(列名,往后的行数n.[行数为null时的默认值，不指定为null]) --往后第n行
  • first_value取分组内排序后，截止到当前行，第一个值
  • last_value取分组内排序后，截止到当前行，最后一个值
  • ntile(n)把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号
 --1、lag(col,n)、lead(col,n)使用
 select ename,hiredate,
 lag(hiredate,2,null)over(order by sal) lag, --往前第2行的值
 lead(hiredate,2)over(order by sal) lead --往后第2行的值
 from emp;
 --组内取往前/往后第n行的值
 select ename,hiredate,
 lag(hiredate,1,null)over(partition by deptno order by hiredate) lag,
 lead(hiredate,1)over(partition by deptno order by hiredate) lead
 from emp
 ​
 --2、first_value()、last_value()的使用
 select ename,first_value(salary) over() from emp;
 select ename,first_value(salary) over(order by sal desc) from emp;
 select ename,first_value(salary) over(partition by job) from emp;
 select ename,first_value(salary) over(partition by job order by sal desc) from emp;
 ​
 select ename,last_value(ename) over() from emp;
 select ename,last_value(ename) over(order by sal desc) from emp;
 select job,ename,last_value(ename) over(partition by job) from emp;
 select job,ename,sal,last_value(ename) over(partition by job order by sal desc) from emp;
 ​
 --3、ntile(n)使用
 --按照薪资降序整体分成3组
 select ename,sal,ntile(2) over(order by sal desc) from emp;
 --给各部门按照薪资降序分为2组
 select deptno,ename,sal,ntile(2) over(partition by deptno order by sal desc) from emp;

14、Oracle行转列

case when语法结构

 case 列名
 when 条件值1 then 选项1
 when 条件之2 then 选项2
 ......
 else 默认值 end
 ​
 --Oracle行列转换
     --数据，使用fox用户的emp表数据
     select * from emp t where rownum<=50;
 --需求:查询emp表中，每个部门的人数，并进行行转列显示
 select
 max(case when deptno=10
     then total else 0 end) "10",
 max(case when deptno=20
     then total else 0 end) "20",
 max(case when deptno=30
     then total else 0 end) "30"
 from 
     (select
         deptno,
         count(*) total
     from emp
     group by deptno
     );

15、Oracle数据合并（存在则更新，不存在就插入）

Merge into关键字

 --Merge into
 MEGER INTO table_name alias1
 USING table|view|sub_query alias2
 ON (join condition)
 WHEN MATCHED THEN
     UPDATE SET
     col1 = col_val1,
     col2 = col2_val
 WHEN NOT MATCHED THEN
     INSERT (column_list)
     VALUES (column_values)
 --示例:创建表
 create table test_merge_A(
 empno number,
 ename varchar2(30),
 sal number);
 --创建表
 create table test_merge_B(
 empno number,
 ename varchar2(30),
 sal number);
 --插入数据
 insert into test_merge_A values(1122,'AA',1500);
 insert into test_merge_A values(1133,'BB',1600);
 insert into test_merge_A values(1144,'CC',1700);
 insert into test_merge_A values(1155,'DD',1800);
 ​
 insert into test_merge_B values(1144,'DD',2500);
 insert into test_merge_B values(1166,'EE',5000);
 insert into test_merge_B values(1177,'FF',3000);
 insert into test_merge_B values(1122,'AA',3000);
 --查看数据
 select * from test_merge_A;
 select * from test_merge_B;
 --将B表的数据合并到A表，如果存在则更新，如果不存在则插入
 merge into test_merge_A a
 using test_merge_B b
 on (a.empno=b.empno)
 when matched then
 update set a.ename=b.ename,a.sal=b.sal
 when not matched then
 insert (a.empno,a.ename,a.sal)
     values (b.empno,b.ename,b.sal);
 ----查看数据
 select * from test_merge_A;

16、Oracle递归用法

递归语法：

 select  

        [level], --伪列，用于返回层次查询的层次(1:根行 2:第2级行 3:第3级行...)  

        column,  

        expr,...  

from table  

[where condition]  

start with condition

--start with:用于指定层次关系查询的根行;决定了爬树的起点  

connect by [prior column1 = column2 |

        column1 = prior column2];

-- connect by:用于指定父行和子行的关系 当定义父行和子行的关系时，必须使用prior关键字，决定了爬树的方向;piror指定哪个是父级别

 --示例:递归查找
   --数据，使用fox用户的emp表数据
   select * from emp t
   where rownum<=50
   --表字段说明
   --empno,ename,job,mgr,hiredate,sal,comm,deptno
   --员工编号，员工姓名，岗位，领导编号，入职日期，薪资，奖金，部门编号
   
 --需求1：向上递归查出empno=7369的所有上级领导的编号和姓名
     --7369-->7902-->7566-->7839
 select 
     empno,
     ename
 from emp
 start with empno = 7369
 connect by empno = prior mgr;
 ​
 --需求2：向下递归查出7839的所有"10"部门的下属及层级
 select 
     empno,
     ename,
     deptno
 from emp
 where deptno = 10
 start with empno = 7839
 connect by prior empno = mgr;

学习永无止境，让我们共同进步！！

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布