什么是数据库

  • 数据库（Database，DB）

  • 数据库管理系统（Batabase Management System，DBMS）

  • 结构化查询语言（Structured Query Language，SQL）

  • 关系型数据库（Relational Database，RDB）：目前应用最广泛的数据库。

    常见的五种关系型数据库管理系统：
    ①Oracle Database：甲骨文公司
    ②SQL Server：微软公司
    ③DB2：IBM 公司
    ④PostgreSQL：开源
    ⑤MySQL：开源

术语及概念简单介绍

  1. 服务器：用于接收并处理其它程序发出的请求的程序（软件），或者是安装此类程序的设备（计算机）。
  2. 客户端：向服务器发出请求的程序（软件），或者是安装此类程序的设备（计算机）。
  3. 表（table）：类似 Excel，由行和列组成的二维表。行（Row）和列（Column）
  4. 字段：表中的列（垂直方向），叫做一个字段
  5. 记录：表的行（水平方向），叫做一条记录。【注意】关系数据库必须以行为单位进行数据读写。
  6. 单元格：行列交汇处。【注意】与 Excel 不同，一个单元格只能输入一个数据。

SQL是什么？

  • 结构化查询语言(Structured Query Language)，是数据库编程的核心语言。

  • SQL 语句：用关键字、表名和列名等组合而成的一条语句。

  • SQL非常接近英语，关键字其实都是英文单词，记住单词的意思，使用起来非常简单。

环境变量

环境变量一般是指在操作系统中用来==指定操作系统运行环境的一些参数==。

SQL分类

  1. DQL（Data Query Language，数据查询语言）:一种用于从数据库中检索数据的语
    言,代表关键字为select。
  2. DDL（Data Definition Language，数据定义语言）:用来创建数据库中的各种对象，
    创建、删除、修改表的结构，代表关键字为create、drop、alter。
  3. DML（Data Manipulation Language，数据操作语言）:用于数据库中对数据的操
    纵，代表关键字为insert、delete 、update。
  4. DCL（Data Control Language，数据控制语言）:用来授予或回收访问数据库的某种
    特权。代表关键字为grant、revoke。
  5. TCL（Trasactional Control Languag, 事务控制语言）：用于维护数据的一致性，包
    括commit、rollback和savepoint三条语句。

去重

关键字：distinct

如果希望一个列表没有重复值，可以利用distinct子句从结果集中除去重复的行。

格式：SELECT DISTINCT 列名称 FROM 表名;

distinct 它只能放在所有列的最前面（它的前面不能写任何列）

distinct 单列：对单列进行去重
distinct 多列：==当且仅当所有列的值同时相等时，才是重复数据==

select distinct deptno from emp;

dual虚拟表

dual是个虚拟表，==只有一行一列，基本上oracle引入dual为的就是符合语法！==

因为在oracle当中查询语句必须要有关键字 select ….from tablename; 只有这样语句才完
整，要是没有表名就无法查询，而时间，日期等不存在于任何表，这个时候就引入了
dual 虚表的概念。

伪列

rownum:不是在物理上真实存在的列，它是对查询结果给了一个从一开始的排列数据，
获取到结果集之后再加上去的一个列

ROWNUM与ROWID不同，==ROWID是插入记录时生成，ROWNUM是查询数据时生成。==
ROWID标识的是行的物理地址。ROWNUM标识的是查询结果中的行的次序。
通过ROWNUM伪列可以限制查询结果集中返回的行数

数据类型

  • char类型，最大长度2000个字节，区别与C语言的char类型一个字节。
  • varchar2存储字符串类型，根据数据实际长度自动调整，最大长度4000个字节。
  • number类型具有精度，格式number(SCALE,PRECISION), 精度指定所有数字位的个数，范围指定小数的位数。
  • data类型 存储日期和时间的组合数。
  • **timestamp:**时间戳类型,可存放世纪、纪元、年、月、日、时、分、秒还可以存放秒后6位。

三大范式

  • 1NF 原子性的，不可分。即实体中的某个属性不能有多个值或者不能有重复的属性。

  • 2NF 实体的属性完全依赖于主关键字。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。

  • 3NF 表中不包含已在其它表中已包含的非主关键字信息。

    用我的话理解就是：

    1. 第一范式：原子性不可分。实体中的属性不能有多个值或多个重复属性。
    2. 第二范式：实体属性完全依赖主键。
    3. 第三范式：表中不含有其他表中的非主键信息。

表和表空间的关系

  • 一个表只能属于一个表空间，
  • 一个表空间可以放任意多个表
  • 一个表空间至少有一个存储文件.dbf文件，可以有多个，而且这多个数据文件可以在不同 位置
  • 一个用户有一个默认的表空间，一个用户可以在默认表空间外的其它表空间建表

创建表空间

create tablespace 表空间名 datafile ‘数据文件路径’ size 初始大小（2G） autoextend on next 每次扩展的大小(100M) maxsize 最大容量（unlimited）;

1
2
3
create tablespace ora
datafile 'c:\test\tab.dbf'
size 2G autoextend on next 100M maxsize unlimited;

创建临时表空间：

create temporary tablespace 临时表空间名 tempfile ‘数据文件路径’ size 初始大小

（2G） autoextend on next 每次扩展的大小(100M) maxsize 最大容量（unlimited）;

1
2
3
create temporary tablespace tabtemp
tempfile 'c:\test\tabtemp.dbf'
size 2G autoextend on next 100M maxsize unlimited;

创建用户

create user 用户名 identified by 密码 default tablespace 表空间名 temporary tablespace

临时表空间名;

default后面的语句是为用户指定默认表空间和临时表空间，如果不指定，默认的表空间是users表空间，临时表空间是temp

1
2
create user orastudy
identified by oracle default tablespace ora temporary tablespace tabtemp;

用户权限赋予

1
2
3
4
5
6
7
8
9
10
11
grant 权限 to 用户名;
grant resource,connect to ora; --连接权限和资源权限
grant create any table to ora; --建表权限
grant create any tablespace to ora; --建表空间权限
grant select any table to ora; --只读权
grant create any view to bw; --创建视图权限
grant select any table to ora; --给ora用户预编译表的权限

--查看角色权限信息
select * from role_sys_privs;
grant dba to ora; --管理员权限

删除表空间

1
drop tablespace student1 including contents;

including contents选项用于删除表空间时包含其内容。如果不使用这个选项，表空间会被删除，但数据文件仍然存在，磁盘空间不会被释放。使用这个选项可以确保表空间及其内容被完全删除，从而释放磁盘空间

扩容 表空间

1
2
alter tablespace student
add datafile 'C:\test\student.dbf' size 100m autoextend on next 10m maxsize unlimited;

约束

1. 列级约束（Column-Level Constraints）

定义位置：直接在列的定义中声明约束。

适用场景：约束仅作用于单个列。

特点：

  • 简洁直观，适合单列约束。

  • 无法定义涉及多列的约束（如复合主键）。

  • 外键约束的列级语法需要直接引用父表列。

2. 表级约束（Table-Level Constraints）

  • 定义位置：在所有列定义之后单独声明约束。所有使用alter语句添加的约束基本都是 表级约束。

  • 适用场景：

    • 约束涉及多个列（如复合主键、联合唯一键）。

    • 需要显式命名约束（便于后续管理）。

    • 外键约束需要指定父表列名。
      [ CONSTRAINT <约束名> ] <约束类型> 约束名：约束不指定名称时，系统会给定一个名称。

六大约束

  1. 非空约束 not null

  2. 默认约束 default

  3. 唯一约束 unique

  4. 主键约束 primary key

  5. 检查约束 check(条件表达式)

  6. 外键约束 references 主表(主键列)

    ==另起一行constraint开头的是表级约束==

操作符与增删改

insert插入

  • Insert into 表名(列名1，列名2，列名3)values(value1，value2,value3);

  • Insert into 表名 values (value1,value2,value3);

    如果值中需要空值，需要在value中输入null

  • Insert into 表名(列名1，列名2，列名3) select * from 表名 where

    插入一整个表

update修改

  • Update 表名 set 列名 =’values’ ,列名=’values’ where

  • Update 表名 set 列名=(select * from 表名) where

delete删除

delete删除的是一整条记录，而不是其中一个字段值。

  • Delete from 表 where 条件
  • Delete from 表 where sal>(select avg(hissal) from salgrade where grade=4)

delete，drop，truncate的区别

delete turncate drop
删除表数据 √ √ √
删除表结构 × × √
是否能回滚 √ × ×
执行速度 慢 中 快

Merge

根据与源表联接的结果，对目标表执行插入、更新或删除操作。

1
2
3
4
5
Merge into 目标表 as 别名
Using 源表 as 别名
On (values=values |and )
When matched then update 和 delete --匹配则更新和删除
When not matched then insert       --不匹配则插入

操作符优先级

算术操作符>链接操作符>比较操作符合>not逻辑操作符>and逻辑操作符>or逻辑操作符

1
2
3
4
5
>all: 表示大于最大值        大于全部值
<all：表示小于最小值        小于全部值
>any：表示大于最小值		大于任意一个值		
<any：表示小于最大值   		小于任意一个值
=any: 和in类似				等于任意一个值

and取交集
or取并集
not取反结果

操作符 含义
between…and… 在两个值之间(包含边界)
in(set) 等于值列表中的一个
like 模糊查询
is null 空值

like：
在where子句中使用like关键字查询数据的方式也称为字符串模式匹配或字符串模糊查询，like关键字需要使用通配符在字符串内查找指定的模式。

要查询的字符串中含有“%”或__“”时，可以使用转义（escape）关键字实现。

在“\”之后的“”字符已不是通配符，而是他本来的含义

排列、分组查询

排列查询

1
2
3
4
5
6
select col_name from table_name order by col_name1,col_name2....(asc/desc)

order:排列查询的关键字
by:介词，后面跟列名
asc:升序（默认）
desc:降序

order by 是唯一一个可以使用别名的关键词,与sql语句的执行顺序有关

`注意：

降序排序时如果有空值，那么空值会作为最大值排在最前面

可以在order by后通过数字指定列进行排序，通常不使用这种方法

分组查询

目的是用来汇总数据或为某个分组显示单行的汇总信息，通常在查询结果集

中使用group by 子句对记录进行分组。

1
2
3
4
select col_name from table_name group by col_name
group:分组的关键字
by:介词，后面加列名
Having：过滤组关键字

聚合函数：定义：也叫组函数，对一组数据（一列或多列）进行处理，返回单个结果

使用group by子句和聚合函数，可以实现对查询结果中每一组数据进行分类统计。

where 和 having的区别

where having
处理目标 行过滤 分组过滤
书写位置 from后 一般在group by后
执行优先 分组前过滤 分组后过滤
是否加过滤条件 √ √
加聚会函数过滤条件 × √
效率 快 慢

where条件里为什么不能有聚合函数

基于整列数据进行计算的，而where子句则是对数据行进行过滤的(这里过滤是在一个记录里边过滤的,基于”行”
为聚集函数要对全列数据时行计算，因而使用它的前提是：结果集已经确定！

递归查询

递归查询的书写方案

1
2
3
SELECT TO DATE(2017-12-01','YYYY - MM - DD')+LEVEL * INTERVAL'1'day
FROM dual
CONNECT BY LEVEL <=24

同环比

1
2
3
4
5
6
7
8
9
10
select a.dat,a.amount,b.amount,c.amount
from tab a
left join tab b on to_date (a.dat,'yyyymm')=add_months(to_date(b.dat,'yyyymm'),1)
left join tab c on to_date(a.dat,'yyyymm')=add_months(to_date(c.dat,'yyyymm'),12)
                                                               
                                                               
select a.dat,a.year,a.month a.amount
from tab a
left join tab b on ta.year=b.year and a.month=b.month+1)or (a.year=b.year+1 and a.month=1 and b.month=12)
Left join tab c on a.month=c.month and a.year=c.year+1

关键词执行顺序

目标 书写顺序 执行顺序
select 列 1 5
from 表 2 1
where 条件 3 2
group by 列 4 3
having 条件:聚合 5 4
order by 列 6 6

分组后select 后面只能跟分了组的列和聚合函数
group by后面产生所有的聚合函数，select只是展示

函数

函数:SQL 函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。

常用函数

序号 函数名 含义
1 length(str) 返回一个字符串长度
2 concat(str1,str2) 字符串连接函数
3 chr() 将一个ASCII码转换为字符
4 ascii(字符) 将一个字符转换为ASCII码值，ASCII码转换的是键盘交互的所有的键
5 instr(str1,str2,start,n) instr(源字符串,目标字符串,开始位置,匹配序号)，返回匹配位置的下标。替换like 查找第几位有什么字符，确定原始字符里有多少个字符。如果start是负数，则从后往前数，从前往后截取。
6 substr(str,start,len) 从start位置开始截取长度为len的字符串，返回一个字符串。如果start是负数，则从后往前数，到开始位置停下了往后截取。
7 initcap(str) 将首字母大写其他字母小写(以空格来区分单词的)
8 lower/upper() 大小写转换函数
9 replace(str,s,d) 字符串替换函数，将字符串str中的s字符替换成字符d。使用功能: 1. 替换 2.删除 3.脱敏
10 translate(char, from,to) 返回将出现在from中的每个字符替换为to中的相应字符
11 round(num,[num1,]) 四舍五入函数，num1是正数时精度是正数小数点之后，是负数时小数点之前
12 mod(num1，num2) 求余函数
13 trunc() 截取函数
14 floor() 向下取整
15 ceil() 向上取整
16 power(n, m) 返回n的m次幂
17 sqrt(n) 返回数字n的平方根
18 to_date(str) 将字符串转换成日期yyyy,MM,dd,hh24,mi,ss
19 to_number() 将字符串转换成数字
20 to_char() 字符串转换函数。跟日期有关的不管是日期转字符还是字符转日期都要带日期格式。to_char()的站位用法：’990.99’:9代表占一个位置，0代表把小数点之前的0补齐
21 last_day(日期) 取当前日期月的最后一天
22 next_day(sysdate,n) 取下一个(最近的)一周的第几天，1是星期日
23 add_months(日期,月) 给一个日期加上若干个月
24 months_between(date1,date2) 取两个日期相差的月数
25 nvl2(列名,值1,值2) 空值转换函数，当第一个参数的值是空时，返回结果是第3个参数的值，当第一个参数不为空时，返回结果是第2个参数的值

数据类型转换

  1. 隐式转换
    指Oracle在执行SQL语句时自动将数据类型转换成需要的数据类型。隐式转换可能会导致精度损失或数据不准确，因此在使用时需要谨慎。

  2. 显示转换
    显式转换是指在SQL语句中使用一些函数强制将某个数据类型转换成另一个数据类型。
    to_number ：字符转换为数字
    to_date()：作用将字符类型按一定格式转化为日期类型。
    to_char 数字转化为字符
    to_char 日期转化为字符,必须加单引号,并且区分大小写

转换函数示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

select to_number('0.9890')+3 from dual;
select to_date('1999-09-09 ','yyyy/MM/dd') from dual;
select to_date('19990909','yyyyMMdd') from dual;
select to_date('1999/09/09 22:56:18','yyyy/MM/dd hh24:mi:ss') from dual;
select to_date(19990909,'yyyyMMdd') from dual;---数字也可以去转
select to_char('.83','0.99') from dual;
select to_char(sysdate,'dd') from dual;
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy/MM/dd hh24:mi:ss') from dual;
select last_day(sysdate) from dual;
select next_day(sysdate,4) from dual;
select add_months(sysdate,-3) from dual--求3个月前的今天
select sysdate-hiredate from emp;----得出结果以天为单位的
select months_between(sysdate,to_date('2020-3-3','yyyy/MM/dd')) from emp
select comm,nvl2(comm,comm,1) from emp;

空值赋值nvl

【语法】NVL (expr1, expr2)
【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。

注意两者的类型要一致

【语法】NVL2 (expr1, expr2, expr3)
【功能】expr1不为NULL，返回expr2；expr2为NULL，返回expr3。

expr2和expr3类型不同的话，expr3会转换为expr2的类型

TRANSLATE(c1,c2,c3)

【功能】将字符表达式值中，指定字符替换为新字符

c1 希望被替换的字符或变量
c2 查询原始的字符集
c3 替换新的字符集，将c2对应顺序字符，替换为c3对应顺序字符

如果c3长度大于c2，则c3长出后面的字符无效
如果c3长度小于c2，则c2长出后面的字符均替换为空(删除)
如果c3长度为0，则返回空字符串。
如果c2里字符重复，按首次位置为替换依据

Case when

  1. 翻译

  2. 行转列

  3. 判断输出

  4. 简单Case函数

1
2
3
4
5
6
7
select deptno,case deptno
		when 10 then '第一部门'
		when 20 then '第二部门'
		when 30 then '第三部门'
			else '第四部门'
				end
from emp;
  1. Case搜索函数
1
2
3
4
5
6
7
8
select deptno,
case when deptno=10 then '第一部门'
	 when deptno=20 then '第二部门'
	 when deptno=30 then '第三部门'
			else '第四部门'
				end
from emp;
**Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。**

注意：

  1. *Case when 生成的列也可以丢到group by 后面去分组的,where,having都可以使用CASE表达式需注意的点

  2. ==Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。==

decode 等值翻译

1
2
select ename,deptno,
decode(deptno,10,'十号部门',20,'二十号部门',30,'三十号部门','其他部门') from emp;

decode与case when 的比较

函数 Oracle SQL Server MySQL informix
decode支持 √ × × √
case when支持 √ √ √ √
  1. decode 只能用做相等判断，但是可以配合sign函数进行大于，小于，等于的判断
  2. CASE when可用于=,>=,<,<=,<>,is null,is not null 等的判断；

聚合case实现行转列

  1. 行转列方法一
1
2
3
4
5
6
select deptno,
max(case when job='SALESMAN' then sal end) salesman,
max(case when job='MANAGER' then sal end) manager,
max(case when job='CLERK' then sal end) clerk
from emp
group by deptno

行转列 列转行 函数

pivot格式

1
2
3
4
5
6
SELECT * FROM （数据查询集）
PIVOT
(
SUM(Score/行转列后 列的值/) FOR
coursename/需要行转列的列/ IN (转换后列的值)
)
  1. 行转列方法二
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select * from
(select deptno,job,sal from emp)
pivot
(
max(sal) for job in ('SALESMAN','MANAGER','CLERK')
)

--行转列 列转行

select * from
(select deptno,job,sal from emp)
pivot
(
max(sal) for job in ('SALESMAN' salesman,'MANAGER' manager,'CLERK' clerk)
)
unpivot
(
newsal for job in(salesman,manager,clerk)
)

开窗函数over()

窗口函数

（分析函数）（主要做排序）

开窗函数格式： 函数名(列) OVER(选项)

over(partition by 分组列 order by 排序列)

注：order by后面可以是处理后的列

over 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，==使用 over 关键字来区分这两种用法。==

ROW_NUMBER()

ROW_NUMBER()函数作用就是将select查询到的数据进行排序，每一条数据加一个序号。排序的序号和rownum伪列相同，连续序号，不考虑值相等的情况（值相同序号不相同）

RANK()–并列跳号

RANK()函数，顾名思义排名函数，可以对某一个字段进行排名，可以用来做排序，它序号不连续，考虑重复数据，如果值相等序号相同

DENSE_RANK()–并列不跳号

DENSE_RANK()密集的排名，紧密排序，可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同他和RANK()区别在于，排名的连续性，DENSE_RANK()排名是连续的，RANK()是跳跃的排名

用聚合函数来开窗

avg() max() count() sum() min()

可以不加任何条件，如果加order by 则是按照顺序依次进行计算，一般不加order by

wm_concat函数

wm_concat(列名),该函数可以把列值以”,”号分隔起来,并显示成一行

1
select wm_concat(ename) from emp

listagg函数

LISTAGG 将多行合并成一行

  1. 作为普通函数，对工资进行排序，用逗号进行拼接。
1
2
3
select listagg(ename,',')within group(order by sal)name from emp;

select * from emp order by sal;
  1. 作为分组函数：
1
select deptno,listagg(ename,',')within group(order by sal)name from emp group by deptno;
  1. 作为分析函数：
1
2
select deptno,ename,sal,listagg(ename,',')within group(order by sal)over(partition by
   deptno)name from emp;

listagg()里面的order by语法是必带的，搭配开窗，开窗里的order by 不用带

偏移分析函数

lead(params,m,n) 以params为目标向下m位取数，当取不到时默认为 n

lag(params,m,n) 以params为目标向上m位取数，当取不到时默认为 n

例如：lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。

group by 和partition by

在 SQL 中，PARTITION BY 和 GROUP BY 都用于对数据进行分组，但它们的 核心目的、使用场景 和 结果形态 有本质区别。

1. 核心作用

GROUP BY PARTITION BY
对数据进行 聚合分组，生成汇总结果（每个分组返回一行）。 对数据进行 分区，定义窗口函数的计算范围（不减少行数）。
常与聚合函数（SUM, AVG, COUNT）结合使用。 常与窗口函数（ROW_NUMBER, RANK, SUM OVER）结合使用。

2. 结果集形态

GROUP BY PARTITION BY
结果集中的行数 减少，每个分组返回一行。 结果集中的行数 不变，原始数据行全部保留。
示例：按部门分组统计总工资，每个部门一行。 示例：按部门分区，计算每个员工的部门内工资排名。

  1. ==group by有去重效果==

  2. ==partition by没有去重效果==

    求每个部门最高工资的人员信息，max()函数少用，不止一个工资最高也可能存在其他漏洞

3. 语法与使用场景

(1) GROUP BY

  • 语法：配合聚合函数，生成汇总统计结果。

    1
    2
    3
     SELECT department, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department;  -- 每个部门返回一行汇总数据

  • 结果示例：

    department total_salary
    HR 250000
    IT 380000

(2) PARTITION BY

  • 语法：在窗口函数中定义数据分区范围，保留所有原始行。

    1
    2
    3
    4
    5
    6
    SELECT 
      employee_id, 
      department, 
      salary,
      RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
    FROM employees;

  • 结果示例：

    employee_id department salary dept_rank
    101 HR 80000 1
    102 HR 75000 2
    201 IT 95000 1

4. 关键区别总结

特性 GROUP BY PARTITION BY
行数变化 减少（每个分组一行） 不变（保留所有原始行）
函数类型 聚合函数（SUM, AVG） 窗口函数（RANK, ROW_NUMBER）
结果内容 分组键 + 聚合值 原始数据 + 窗口计算结果
典型用途 生成汇总统计报表 排名、累计值、前后行对比分析

5. 如何选择？

  • **用 GROUP BY**：
    需要生成汇总统计结果（如部门总工资、订单总数），且不需要保留原始明细数据。
  • **用 PARTITION BY**：
    需要在保留所有原始行的基础上，添加分组计算列（如部门内排名、累计销售额）。

一句话总结

  • GROUP BY 是 纵向压缩（聚合分组，减少行数）。
  • PARTITION BY 是 横向扩展（分区计算，保留所有行）。

子查询、联合查询

子查询(最好加别名)

==子查询就是嵌套在查询中的查询。==

很多时候，我们对数据的查询处理不是一个查询语句就可以完成的；

不能完成的原因，可能是查询条件比较复杂，也可能是受困于SQL自身语法的限制；

单行子查询

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

查询结果表现形式：单行单列 多行单列 多行多列 单行多列

​ 单行单列，一个值 一个列 一个表

多行子查询

多行子查询是指返回多行数据的子查询语句。当where子句引用多行子查询时，必须使用多行比较符（in、any、all、exist等）

查询结果表现形式:多行单列 一个列 一个表

例子：

多条件查询，大多数可以使用开窗代替

查看每个部门工资最高的员工姓名和他的工资

开窗的做法

1
2
3
4
5
select * from
(
select ename,sal,deptno,rank()over(partition by deptno order by sal desc) r from emp
)
where r=1;
  • 多条件多行操作
ename,sal,deptno from emp
1
where (deptno,sal) in (select deptno,max(sal) from emp group by deptno)
  • 多行多列 单行多列 一个表
1
2
3
4
5
6
7
select ename,sal,rank()over(order by sal desc) from emp
select *
from
(
select ename,sal,rank()over(order by sal desc) paiming from emp
)
where paiming<=3;

联合查询

SQL联合查询主要是涉及两个表或者多个表（自身表多次）的查询。

主要分为连接查询和集合查询两大类，其中，

连接查询分：

  • 内连接（inner Join 或 Join）

  • 左外连接（left outer Join 或 left Join）

  • 右外连接（right outer Join 或 right Join）

  • 全外连接（full outer Join 或 full Join）

集合查询分:

  • minus（差集）

  • intersect（交集）

  • union （并集）排序去重

  • union all（并集）不排序去重

连接查询（JOIN）

  • inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。

  • left join （左连接，左外连接）：返回包括左表中的所有记录和右表中连接字段相等的记录，如果没有匹配上，以null值代表右边表的列。

  • right join （右连接，右外连接）：返回包括右表中的所有记录和左表中连接字段相等的记录，如果没有匹配，以null值代表左边表的列

  • full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录，没有匹配上，以null值代表左右边表的列。

image-20250509164008543

image-20250509164028422

笛卡尔连接(交叉连接)：

把笛卡尔积所有的结果给显示出来了

1
2
3
A cross join B
select * from emp cross join dept;
select * from emp,dept;

交叉连接的左外右外写法(+)写在左边就是右外，写在右边就是左外

1
select from emp,dept where emp.deptno=dept.deptno(+)

自然连接：一种特殊的内连接

没有链接条件on，如果两个表里面有一列相等，系统默认把这一列作为链接条件，并且把这两列合成一列放到表的最面,适用于知道表里面有这样一列，并且要用这一列的时候

1
2
3
4
natural join
select * from emp natural join dept
using:跟自然连接作用相同，不同在于它，当有多个列相同时，可以指定用哪一列来做链接
select * from emp join dept using(deptno)

内连接

1
2
3
select * from emp,dept where emp.deptno=dept.deptno
select * from emp inner join dept on emp.deptno=dept.deptn

自连接

–查询出每个员工的上级领导(查询内容：员工编号、员工姓名、领导编号、领导姓名)

1
2
select yg.empno,yg.ename,ld.empno,ld.ename
from emp yg inner join emp ld on yg.mgr=ld.empno

不等值连接：（过滤条件的符号不是等号）

–查询员工的工资级别

1
2
select ename,grade,sal from emp
inner join salgrade on emp.sal between losal and hisal

多表连接

1
2
3
select yg.empno,yg.ename,ld.empno,ld.ename,dname
from emp yg inner join emp ld on yg.mgr=ld.empno
inner join dept on ld.deptno=dept.deptno

外连接

1
2
3
4
5
6
7
select * from emp left outer join dept on emp.deptno=dept.deptno

select * from dept right outer join emp on emp.deptno=dept.deptno
select * from emp right outer join dept on emp.deptno=dept.deptno

select * from dept left outer join emp on emp.deptno=dept.deptno
select * from emp full outer join dept on emp.deptno=dept.deptno

–筛选出来emp表中不满足条件的数据

1
2
select * from emp left join dept on emp.deptno=dept.deptno
where dept.deptno is null

–筛选出DEPT表中不满足条件数据

1
2
select * from emp e right join dept d on e.deptno=d.deptno
where e.deptno is null

–筛选出emp和dept表中不满足条件的数据

1
2
select * from emp e full join dept d on e.deptno=d.deptno
where d.deptno is null or e.deptno is null

on和where使用区别

内连接无差别，外连接用on效率高

1. ON 的作用

  • 应用场景：专门用于 JOIN 操作（如 INNER JOIN、LEFT JOIN 等）。

  • 作用时机：在 表连接过程中 定义连接条件。

  • 核心逻辑：

    • 决定两张表的行如何匹配。

    • 如果条件不满足，仍可能保留主表的行（取决于 JOIN 类型）。

2. WHERE 的作用

  • 应用场景：用于 最终结果集的全局过滤。
  • 作用时机：在 所有表连接完成后 筛选数据。
  • 核心逻辑：
    • 过滤掉不满足条件的行。
    • 如果条件不满足，直接丢弃整行数据。

3. 关键区别总结

特性 ON WHERE
作用阶段 表连接过程中 表连接完成后
影响范围 定义表之间的匹配规则 全局过滤结果集
保留主表数据 是（如 LEFT JOIN） 否（不满足条件则丢弃所有行）
性能影响 可能减少连接的数据量 对最终结果过滤
典型用途 指定连接条件 筛选最终结果

4. 不同 JOIN 类型下的行为

场景：左表有数据，右表无匹配

  • LEFT JOIN + ON 条件不满足
    • 左表行保留，右表字段填充 NULL。
  • LEFT JOIN + WHERE 条件不满足
    • 整行被丢弃。

5. 何时用 ON 或 WHERE？

  • **用 ON**：
    • 定义表之间的连接逻辑。
    • 在 JOIN 过程中过滤右表数据（同时保留主表数据）。
  • **用 WHERE**：
    • 对最终结果集进行全局过滤。
    • 需要严格筛选所有行时。

6. 一句话总结

  • ON 决定如何连接表，WHERE 决定最终显示什么。
  • 在 LEFT JOIN 中，若希望保留主表数据，右表的过滤条件应写在 ON 中；若想严格过滤结果，则用 WHERE。

集合查询

  1. 集合查询后的结果字段名显示为第一条select语句字段名

  2. union/union all,intersect 没有上下关系，minus有（永远是上减下）

  3. 集合做运算的时候，可以不仅是一列，多列做运算时，要求列数量相等，并且列的数据类型相同

  4. union、union all、intersect、minus运算可以进行混合运算、它们之间优先级相同。

  5. 可以使用圆括号控制集合运算的优先级，它具有最高的优先级

  6. union拼接后结果排序，去重，union all ，不排序，不去重，union all 效率更高

并集

1
2
3
4
5
6
select deptno from emp
union
select deptno from dept
select deptno from emp
union all
select deptno from dept

union all 并集全部显示

（列名可以不一样，类型一样就可以了）要

1
2
3
select deptno from emp
union all
select sal from emp

交集

1
2
3
select deptno from emp
intersect
select deptno from dept

差集minus

永远都是上减下

1
2
3
4
5
6
7
8
select deptno from dept
minus
select deptno from emp
(select deptno from emp
union all
select deptno from dept)
minus
select deptno from emp

灵活应用

1
2
3
4
select empno,ename from emp
union
select 333,'test' from dual
order by empno desc;

exits 和 exit

一、EXIT：退出流程控制

适用场景

主要用于 PL/SQL（Oracle 的过程化 SQL）中，控制循环或代码块的执行流程。

功能

  • 终止循环：立即退出 LOOP、WHILE 或 FOR 循环。
  • 退出代码块：在特定条件下提前结束程序执行。

*语法示例

1
2
3
4
5
6
BEGIN
  FOR i IN 1..10 LOOP
    EXIT WHEN i = 5;  -- 当 i=5 时退出循环
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;

输出：

1
2
3
1
2
3

二、EXISTS：存在性检查

适用场景

用于 SQL 查询 中，检查子查询是否返回至少一行数据。

功能

  • 条件过滤：结合 WHERE 子句，判断子查询是否有结果。
  • 高效验证：子查询一旦找到匹配记录即终止扫描，性能优于 IN 或 JOIN。

语法示例

1
2
3
4
5
6
7
8
-- 查询有订单的客户
SELECT customer_id, name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

结果：仅返回至少有一个订单的客户。

三、核心区别总结

特性 EXIT EXISTS
用途 控制流程（退出循环/代码块） 检查子查询是否存在结果
语境 PL/SQL 编程 SQL 查询
执行逻辑 终止当前循环或代码块 快速验证子查询是否返回数据
常见场景 循环中的条件终止 筛选关联数据、优化复杂查询

四、常见误区

1. 误将 EXIT 用于 SQL 查询

  • EXIT 是 PL/SQL 的关键字，不能直接在纯 SQL 语句中使用（如 SELECT 查询）。

2. 混淆 EXISTS 和 IN

  • EXISTS 关注子查询是否有结果，而 IN 用于匹配具体值列表。
  • 性能差异：EXISTS 通常比 IN 高效，尤其是子查询数据量大时。

五、总结

  • EXIT 是 PL/SQL 的流程控制工具，用于终止循环或代码块。

  • EXISTS 是 SQL 的逻辑运算符，用于高效验证子查询是否存在数据。

exists和in的区别

IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况,这样效率会高的。

带索引分析exists和in的区别

 in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in；

结论

​ 正如所看到的，not in出现了不期望的结果集，存在逻辑错误，所以，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists（它会调用关联子查询）。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。如果子查询字段有非空限制，这时可以使用not in。如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而notexists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。

2种批量插入方式

方法一

  1. 执行创表语句，如下：
1
select * from shuiguo for update
  1. 点击开锁，鼠标图标向右，右滑全选中全蓝色，选择需要负责的数据，到工具里面粘贴，打钩√。

方法二

  1. 工具->ODBC导入器->填用/系统Excel Files->连接->填写路径并选择文件->选择插入的表->选择完说有字段并设置格式->导入

定义操作控制等语句

索引视图序列

在对包含很多行的表进行检索其中几行数据时，都应该创建索引，以加快检索速度。

  1. 索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

  2. 索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率

  3. 索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；

  4. 索引一旦建立，会自动管理索引，索引删除，不会对表产生影响

  5. oracle创建主键时会自动在该列上创建索引

索引的分类

一、按存储形式（即索引中存储的内容不同）

  1. B-TREE索引(索引列原始数据+ROWID)

  2. 位图索引(位图+ROWID)

  3. 反向键索引(索引列原始数据的反向存储+ROWID)

  4. 基于函数的索引(将索引列原始数据经函数处理后存储+ROWID)

索引的命名规范：IND_TBNAME_COLNAME

B-TREE索引 位图索引 反向键索引 基于函数的索引
说明 ORACLE的默认索引类型，工作中最常见、使用范围最广的索引 位图索引在创建时，会扫描整张表，为索引列的每个取值建立一个不重复的位图（BITMAP）来描述该取值 可以视作一种特殊的B-TREE索引，存储索引列的反向值 可以视作一种特殊的B-TREE索引，存储函数处理后的数据
语法 create index ind_name on TB_NAME(COL_NAME); CREATE BITMAP INDEX IND_NAME ON TB_NAME(COL_NAME); CREATE INDEX IND_NAME ON TB_NAME(COL_NAME) REVERSE; CREATE INDEX IND_NAME ON TB_NAME(FUNCTION(COL_NAME));
背景 列基数比较大的时候使用（行业、身高） 列基数比较小的时候使用（性别、婚姻状况） 为防止B-TREE索引在某叶上数据量占比过高而使用的一种索引 在某个字段上以原值建立了索引，但是在筛选时在该字段上经常加函数，导致索引无法生效

二、按唯一性（索引列中的数据是否有重复值）

  1. 唯一索引 –索引列中不可能出现重复值

语法：

1
CREATE UNIQUE INDEX IND_NAME ON TB_NAME(COL_NAME);

注意点：

  1. B-TREE索引可以建立唯一索引，位图索引不能建立唯一索引

  2. 如果在某列上建立了唯一约束或主键约束，ORACLE会自动在该列上建立一个同名的唯一索引

  3. 非唯一索引 –索引列中可能出现重复值

语法：

1
CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

三、按列的个数（索引覆盖的列的个数）

  1. 单列索引 –基于一个列建立的索引

语法：

1
CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);
  1. 复合索引(也称为联合索引） –基于两个或两个以上列建立的索引

语法：

1
CREATE INDEX IND_NAME ON TB_NAME(COL_NAME1,COL_NAME2...);

创建索引

1
Create [unique|BITMAP] index 索引名称 on 表名（列名[，列名]） tablespace 表空间名；

  1. Unique 用于指定是否强制要求索引列为唯一性数据，表空间可选择是否指定，不指定则用默认表空间。

  2. 出于性能考虑索引表空间和表的表空间要分开。

修改索引

1
Alter index 索引名 rename to 新索引名；

删除索引

1
通过drop index 索引名；

视图

视图实际上是一个或多个表的预定义查询，视图的使用方法和表一样。视图不存储数据，他们只访问基表中的行。

创建视图

1
2
3
Create or replace view 视图名 as select * from 表名 with read only

Drop view 视图名；删除视图

scott账号要使用system账号赋予权限才能创建视图

1
grant create any view to scott

事务

事务：它是数据库在执行一系列操作时，保证所有的操作都正确完成，要么都执行，要么都不执行，保证数据的完整性。

必须具备以下四个属性，简称ACID 属性：

A:原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。

C:一致性（Consistency）：一个查询的结果必须与数据库在查询开始时的状态保持一致（读不等待写，写不等待读）。

I:隔离性（Isolation）：数据库中每一个用户的操作都是互不影响的，对于其他会话来说，未完成的（也就是未提交的）事务必须不见。

D:持久性（Durability）：事务一旦提交完成后，数据库就不可以丢失这个事务的结果，数据就永久的保存到数据库中。

事务的开始和结束

事务采用隐性的方式，起始于session的第一条DML语句，

事务结束于：

  1. COMMIT（提交）或ROLLBACK（回滚）

  2. DDL语句被执行（提交）

  3. DCL语句被执行（提交）

  4. 用户退出SQLPLUS（正常退出是提交，非正常退出是回滚）

  5. 服务器故障或系统崩溃（回滚）

  6. shutdowm immediate(回滚）

在一个事务里如果某个DML语句失败，之前其他任何DML语句将保持完好，而且不会提交！

Oracle 的事务保存点功能

savepoint命令允许在事务进行中设置一个标记（保存点），这个标记可以控制rollback的效果，即在一个事务中回滚掉最近的部分dml语句，保留下保存点之前的的dml语句，并使事务本身继续执行(考点）。也就是说回滚到保存点这个动作并不使事务结束。

commit 提交

rollback 回滚

保存点：savepoint 可以把在保存点之后的事务给撤销掉

操作完之后->savepoint 保存点名字

回退到保存点->rollback to 保存点名字

排它锁和共享锁

数据库中两种基本类型的锁：排它锁和共享锁。当数据被上了排它锁，就不能被其他事

务读取和修改；而上了共享锁，只能被其他事务读取，但是不能被修改数据库。

update emp set sal=sal*1.1 where deptno=10;–行级排他锁

查看自己的sessionid

select userenv(‘sid’) from dual;

解决锁定冲突

  1. 执行commit或者rollback结束事务

  2. 终止会话

  3. 管理员杀死进程

Oracle锁表解锁 system用户登录

既然清楚了锁表如何形成的，那么我们就要开始解锁。解锁的方法很简单，通过Sql语句

就可实现。

1.查看被锁的表

1
2
3
Select b.owner,b.object_name,a.session_id,a.locked_mode
From v$locked_object a,dba_objects b
Where b.object_id = a.object_id;

2.查看那个用户那个进程造成死锁

1
2
3
4
5
6
7
8
9
SELECT s.sid, q.sql_text
FROM v$sqltext q, v$session s
WHERE q.address = s.sql_address AND s.sid = &sid -- 这个&sid 是第一步查询出来的
ORDER BY piece;--查看导致锁死的SQL

Select
b.username,b.sid,b.serial#,logon_time
From v$locked_object a,v$session b
Where a.session_id = b.sid order by b.logon_time;

3.杀掉进程

1
2
SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock" FROM v$session
WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);

两个事务并发访问数据库数据时可能存在的问题

  1. 幻想读：

　　事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录

并commit，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以

看到T2插入的记录，这条新纪录就是幻想。

  1. 不可重复读取：

　　事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录并commit，然后T1再

次查询，发现与第一次读取的记录不同，这称为不可重复读。

  1. 脏读：

　　事务T1更新了一行记录，还未提交所做的修改，这个T2读取了更新后的数据，然后

T1执行回滚操作，取消刚才的修改，所以T2所读取的行就无效，也就是脏数据

同义词

同义词：它是给数据库中的对象起一个别名，可以和使用原对象一样去使用同义词

同义词一般是给原对象取一个简单易记的名字，管理员system创建同义词

创建语法：

1
create or replace synonym 同义词名 for 对象名;

示例：

1
2
3
4
5
create synonym e for scott.emp; --给表创建同义词
select * from e;
select * from scott.emp;
create or replace synonym de for scott.dept_emp; --给视图创建同义词
select * from de;

同义词可以用来做只读用户，同义词可以用来做只读用户提高安全性

Oracle数据库中提供了同义词管理的功能。

同义词是数据库方案对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。在使用同义词时，Oracle数据库将它翻译成对应方案对象的名字。与视图类似，同义词并不占用实际存储空间，只在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象，如表、视图、同义词、序列、存储过程、包等等，数据库管理员都可以根据实际情况为他们定义同义词

数据字典

数据库字典：是数据库自己维护一组表，它存放了用户创建的所有对象的信息。

数据库字典视图：就是数据库字典视图

dba_:管理员数据库字典视图

user_:用户的数据库字典视图

all_:所有用户都可见的数据库字典视图

数据导入导出

数据的迁移和备份，导入导出，虚拟机cmd进行操作

exp 账号/密码@数据库名 file=文件存放路径 full=y –全库导出，命令行语句，不需要登录数据，只需要在命令行运行

1
2
3
4
5
6
7
8
--按用户导出：
exp scott/oracle@orcl file=c:\test\exp_0512_scott.bak OWNER=scott
select * from emp;
drop table emp;

--导入
imp scott/oracle@orcl file=c:\test\exp_0711_scott.bak ignore=y
select * from emp;

with..as 子查询

with..as关键字，是以‘with’关键字开头的sql语句，在实际工作中，我们经常会遇到同一个查询sql会同时查询多个相同的结果集，即sql一模一样，这时候我们可以将这些相同的sql抽取出来，使用with..as定义。with..as相当于一张中间表，可以简单理解为sql片段。

–with as 可以理解为一张临时表或者理解成sql片段，在多次查询语句相同的时候可以抽取出来，达到’一次解析，多次使用’

–如果每个部分都去执行一遍的话，则成本比较高，可以使用with as短语，则只要执行一遍即可

例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
with temp as (select avg(sal) a,max(deptno) m from emp)
select * from emp
where sal<(select a from temp) and deptno=(select m from temp)

--with as 非常适合在union 语句中
--注意：with as 语句最后面不能加分号，否则报缺失select关键字错误。
with temp1 as
(select 'female' sex, 'zhangsan' stu_name from dual),
temp2 as
(select 'male' sex, 'lisi' stu_name from dual),
temp3 as
(select 'female' sex, 'wangwu' stu_name from dual)
select *
from temp1
union all
select *
from temp2
union all
select * from temp3

--前面定义的with..as语句可以在后面定义的with..as语句使用
with temp1 as
(select 'female' sex, 'zhangsan' stu_name from dual),
temp2 as
(select 'male' sex, 'lisi' stu_name from dual),
temp3 as
(select * from temp2)
select *
from temp1
union all
select *
from temp2
union all
select * from temp3