目录
一、关系型数据库：存储结构化数据，以表的形式存储数据（列：字段）（行：数据）
Ⅰ 数据库:
一、关系型数据库：存储结构化数据，以表的形式存储数据（列：字段）（行：数据）
典型：
Oracle 大型 甲骨文公司
Oracle用户：
SYSTEM——管理员用户——权限最高——123456
Scott——普通用户——tiger
scott用户下四张表：部门表 dept 雇员表 emp 工资等级表 salgrade 工资表 bonus
orcl——oracle默认的实例名
mysql 小型 甲骨文公司
sqlserver 小型 微软公司 只支持Windows
db2 大型 IBM公司
DM 武汉达梦
GaussDB 华为
瑶池数据库 阿里云
二、非关系型数据库
Ⅱ 数据库标准语言：SQL（结构化查询语言）
分为：
dql——数据查询语言
DML——数据操纵语言
DDL——数据定义语言
dcl——数据控制语言
tcl——事务控制语言
一、DQL
1. 简单查询
语法
SELECT（查找）
*（所有字段）/ 列名1（单个字段） /列名2,列名3（多个字段）谁在前就先展示谁
FROM（从）
表名注意：表名、列名、关键字、不区分大小写，数据区分大小写；关键词前后要有空格；所有标点都是英文状态
字段
EMPNO 员工编号 Ename 员工姓名 JOB 职位岗位 MGR 领导编号 HIREDATE 入职日期 SAL 工资 COMM 奖金 DEPTNO 部门编号
字段
DEPTNO 部门编号 DNAME 部门名称 LOC 地址
列别名
SELECT 列名1 AS "别名1",列名2 AS "别名2" FROM 表名;
AS、双引号可省略不写，如果别名为纯数字或者特殊符号，双引号不可省略
简化后：
SELECT 列名1 别名1 FROM 表名;
表别名
SELECT 列名 FROM 表名 表别名;别名命名规则：必须以字母开头；最好是缩写；表别名可以缩写也可以写为T_1 T_2
别名并没有改变原来的名字，只是当前语句使用（分号之前）
连接符||
SELECT 列名1||列名2 FROM 表名;将两个或多个字符拼接到一起，变成一个字符
SELECT 列名1||'文本'||列名2 FROM 表名;
2. 条件查询
语法
SELECT */列名/计算/常量 FROM 表名 WHERE 过滤条件常见的数据类型：数值、字符、日期
（1）逻辑运算
< > <= >= = <>/!=
数值比较
查询工资大于2000的员工信息
字符比较
<>/!= 或 =
查询姓名是SMITH的员工信息
注意：数据区分大小写，字符要加单引号
日期比较
查询入职日期在1981/1/1之前入职的员工信息
转换函数：将目标字符转换成想要的日期格式
to_date(数据，日期格式)
（2）空值判断
IS NULL 是空
IS NOT NULL 不是空
查询有奖金的员工信息（奖金不是空）
（3）包含查询
IN(集合)：在集合里面即满足条件
NOT IN(集合)：不在集合里面即满足条件
集合必须是同属性的数据放在一起
查询部门编号是10和20的员工信息
（4）模糊查询
LIKE '目标格式'：像目标格式即满足条件
NOT LIKE '目标格式'：不像目标格式即满足条件
目标格式：% 代表占有零或多个字节
_ 代表只占一个字节
查询姓名以s开头的员工信息
查找姓名是以A开头且倒数第二位是M的员工信息
查询姓名中有A的员工信息
（5）范围查询
BETWEEN 值1 AND 值2：在1和2之间即满足条件
NOT BETWEEN 值1 AND 值2：不在1和2之间即满足条件
查询工资在1000到3000之间的员工信息
注意：BETWEEN AND 包含边界，小值写在前面，大值在后面
（6）ANY和ALL
ANY(集合)、ALL(集合)通常和<、>联用
>ANY(100,200) 大于集合中任意值即满足条件
>ALL(100,200) 大于集合中所有值即满足条件
（7）条件的连接
AND：并且，要同时满足两个条件
OR：或者，满足一个条件即可
查询10号部门的经理
查询10号部门的经理或者30号部门的销售
注意：AND优先于OR，有括号先执行括号
例题
查询薪资超过1000并且小于3000的员工信息（2种）
查询部门编号是10号或20号的员工信息（2种）
查询岗位是销售SALESMAN，并且奖金超过400的员工信息
查询20号部门的经理
查询所有20号部门的员工或岗位是MANAGER的员工信息
查询10号部门的部门经理或20号部门的分析师ANALYST
查询10号部门的员工、30号部门的经理及所有的分析师ANALYST
SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 3000;
SELECT * FROM EMP WHERE SAL>1000 AND SAL<3000;
SELECT * FROM EMP WHERE DEPTNO IN(10,20);
SELECT * FROM EMP WHERE DEPTNO='10' OR DEPTNO='20';
SELECT * FROM EMP WHERE JOB='SALESMAN' AND COMM>400;
SELECT * FROM EMP WHERE DEPTNO='20'AND JOB='MANAGER';
SELECT * FROM EMP WHERE DEPTNO='20'AND JOB='CLERK' OR JOB='MANAGER';
SELECT * FROM EMP WHERE (DEPTNO='10'AND JOB='MANAGER')OR(DEPTNO='20' AND JOB='ANALYST');
SELECT * FROM EMP
WHERE DEPTNO='10'AND JOB='CLERK' OR DEPTNO='30' AND JOB='MANAGER' OR JOB='ANALYST';3. 排序查询
定义：对查询结果按照规则排序
语法：
SELECT */列名/计算/常量/函数 FROM 表名 WHERE 过滤条件
ORDER BY 字段
升序:order by 字段 [asc];默认asc可省
降序:order by 字段 desc;查询员工信息，结果按照工资升序排列
查询工资大于2000的员工，结果按部门升序，按工资降序
ORDER BY后可以写多个字段，先按第一字段排序，第一字段相同再按第二字段排序，列和列之间永远都是逗号；NULL值在排序的时候默认最大
查询员工姓名、薪资、佣金、薪资佣金合计，按薪资佣金合计值升序排列，薪资佣金以“总计” 二字显示
统计员工姓名及员工资历，并按资历排序，将老员工排在前，新员工排在后
4. 分组查询
定义：按照一定规则分组，统一分析各组情况，每组返回一个值
（1）聚合函数（分组函数）：先分组后聚合
max(字段) min(字段) avg(字段) sum(字段) count(字段/*/数字)
最大 最小 平均 求和 计数
查询emp表中所有员工的各种薪资情况
注意：空值不参与聚合函数的统计
统计
四个人有奖金，表中有14行数据，
注意：COUNT(字段)，统计非空值的个数；COUNT(*)，统计该组所有行数；COUNT(数字)，统计该组所有行数
（2）GROUP BY和HAVING
SELECT 字段1，聚合函数 GROUPT BY 字段1 HAVING 聚合函数;按照字段分组，统一分析各组情况，每一组返回聚合函数的值
查询每个部门的平均工资
注意：在统计查询中，select子句后只允许出现分组字段和统计函数，其它非分组字段不能使用；GROUP BY 后能放多个字段，先按第一组分，相同情况下按第二组分；如果没有group by 子句，则在select子句中只允许出现统计函数，其它字段不允许出现；
统计函数可以嵌套使用，但是嵌套统计函数之后的select子句中不允许再出现别的字段，包括分组字段,且必须有group by。
错：select id,max(avg(sal)) from t_ls_rkxx group by id;
正：select max(avg(sal)) from t_ls_rkxx group by id;
查询每个部门的每个岗位的平均薪资
查询每个部门平均薪资大于2000的岗位
注意：WHERE后面不能使用聚合函数，对接分组前的条件；HAVING后只能使用聚合函数，对接分组后的条件，没有GROUP BY 就没有HAVING
例题：
查询薪资合计超过5000的岗位及其平均薪资
SELECT JOB,AVG(SAL),SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING SUM(SAL)>5000
查询部门人数超过4人的部门编号及薪资合计
SELECT DEPTNO,SUM(SAL) 薪资合计
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(EMPNO)>4;
查询各部门下，人数超过两人的岗位有哪些，显示部门编号、岗位名称
SELECT DEPTNO,JOB,COUNT(EMPNO) FROM EMP GROUP BY DEPTNO,JOB HAVING COUNT(EMPNO)>2;
查询姓名重复的员工的姓名
SELECT ENAME,COUNT(ENAME)
FROM EMP
GROUP BY ENAME
HAVING COUNT(ENAME)>1;
5. 完整查询语句
SELECT */字段/常量/计算/函数
FROM 表名
WHERE 过滤条件 --分组前的过滤条件，不能使用聚合函数
GROUP BY 字段（分组内容）
HAVING 过滤条件 --分组后的条件，可以使用聚合函数
ORDER BY 字段 [ASC]（排序内容） --正序排列
DESC --降序执行顺序： FROM>WHERE>GROUP BY>HAVING>SELECT>ORDER BY
注意：WHERE后面不能用别名
例题：
1.查询 1982年以前入职的员工，按部门、岗位分组，查出
平均薪资超过800的部门和岗位类型及其平均薪资和最高薪资，按最高薪资降序排列
SELECT DEPTNO,JOB,AVG(SAL) 平均薪资,MAX(SAL) 最高薪资
FROM EMP
WHERE HIREDATE<TO_DATE('1982/1/1','yyyy/mm/dd')
GROUP BY DEPTNO,JOB
HAVING AVG(SAL)>=800
ORDER BY MAX(SAL) DESC
2.查询部门人数超过3的部门的部门编号、最高薪资、最低薪资、部门人数，按部门人数升序排列
SELECT DEPTNO,MAX(SAL),MIN(SAL),COUNT(EMPNO) FROM EMP
GROUP BY DEPTNO
HAVING COUNT(EMPNO)>3
ORDER BY COUNT(EMPNO)
3.查询岗位名称不以S开头、并且岗位平均薪资超过2000的岗位的岗位名称、岗位人数、岗位平均薪资,按岗位平均薪资升序排列
SELECT JOB,COUNT(EMPNO) 岗位人数,AVG(SAL) FROM EMP
WHERE ENAME NOT LIKE'S%'
GROUP BY JOB
HAVING AVG(SAL)>2000
ORDER BY AVG(SAL)
4.查询各部门部门编号、最高薪资、最低薪资，以及最高和最低薪资的差值，按部门编号降序排列
SELECT DEPTNO,MAX(SAL),MIN(SAL),MAX(SAL)-MIN(SAL) 薪资差值 FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO DESC
5.统计岗位类型、最高薪资、最低薪资、薪资合计、平均薪资，以及人数，
各字段以中文列名展示
SELECT JOB 岗位类型,MAX(SAL) 最高薪资,MIN(SAL) 最低薪资,SUM(SAL) 薪资合计,AVG(SAL) 平均薪资,COUNT(EMPNO) 人数 FROM EMP
GROUP BY JOB
6.统计薪资在1000到3000之间的人员中，有佣金的员工总数及无佣金的员工总数，
SELECT COUNT(COMM) 有佣金的员工数,COUNT(*)-COUNT(COMM) 无佣金的员工数 FROM EMP
WHERE SAL BETWEEN 1000 AND 30006.数值计算
SELECT 字段运算符 数值/字段 FROM 表名
注意：空值（NULL值）不参与计算，任何数和NULL值进行计算得到的还是NULL值
空值不参与逻辑比较
7.子查询
定义：在一个查询语句中嵌套一个或多个语句
注意：
- 子查询要在括号内，括号内子查询只返回一行
- 子查询放在比较条件右侧增强可读性
- 单行操作符对应单行子查询，多行操作符对应多行子查询
- 子和主查询可以不是一张表
- 子可以放在SELECT后面，只能使用单行单列或相关子查询
- 子查询的底层思路：
①单行单列子查询
- (>,<,=,<>,>=,<=)内部SELECT子句只返回一行结果
- 子查询返回的是一个具体列的内容，可以理解为一个单值数据；
例题
查询工资最低的员工
②单行多列子查询
- (>,<,=,<>,>=,<= in)内部SELECT子句只返回一行结果
- 子查询返回一行数据中多个列的内容；
- 多行多列子查询要使用多行操作符（字段，字段，...）
例题
查询工资和scott相同，岗位也和scott相同的员工信息
③多行单列子查询
- (all, any, in,not in)
- 子查询返回多行记录之中同一列的内容，相当于给出了一个操作范围；
-
< any :小于最大值
> any :大于最小值
= any :等价于 in
-
< all :小于最小值
> all :大于最大值
!= all :等价于not in
例题
查询是领导的员工(mgr存的是员工的领导工号,如果他的empno在mgr里有位置那就说明他是领导)
查询不是领导的员工
为什么要排除空值？
在 Oracle 中，如果子查询返回的结果集中包含 NULL 值，那么主查询中使用 NOT IN 子句可能会导致结果为空。这是因为 SQL 中的 NULL 值比较特殊，它与任何值的比较都会返回 UNKNOWN，而不是 TRU E 或 FALSE。使用NOT IN时不能对null操作
查询工资比职位是 clerk 的员工中任意(某)一个低的员工信息(ANY)
查询工资比各部门平均工资都高的员工(ALL)
注：加上GROUP BY DEPTNO的作用是计算每个部门的平均工资。这是因为你想要比较员工的工资与其所在部门的平均工资。如果不加GROUP BY DEPTNO，子查询中只会返回整个表的平均工资，而不是每个部门的平均工资。
④多行多列子查询
- 查询返回的结果是一张临时表，返回满足条件的多个信息
例题
查询工作与7369相同且工资大于7876的员工信息（这个表信息太少只返回了一个）
查询工资高于平均工资的所有员工的员工号和工资
查询前三个工资最高的员工信息
- 在这个查询中，
t.*中的t是一个别名，它表示子查询结果的别名。在外部查询中，我们需要明确指定所选列来避免歧义，因为可能存在多个表或子查询。因此，t.*表示选择子查询t中的所有列，这样就能够正确地指定要选择的列，并且保持查询的简洁性。 rownum是一个伪列，用于表示返回的行的行号。在这个查询中，rownum<4表示只选择行号小于4的数据，即选择前三行数据。这样可以实现对结果集进行分页或者限制返回的行数。- 子查询返回一个表，可以在FROM后
⑤相关子查询
- 子(里层的查询)不能单独运行
- 子(里层的查询)会牵扯主(外层的查询)的部分内容
- 主只在乎与子的关系
⑥非相关子查询
- 子能单独运行
- 子不会牵推主的内容
- 主只在乎子的结果
查询没有员工的部门的部门名称和工作地点
为什么要加DEPTNO=DEPTNO？
目的是对两个表（DEPT和EMP）之间的关联关系进行筛选。具体来说，它要求两个表中的 DEPTNO 列相等，以确保我们只选择那些在两个表中都存在的相匹配的部门，如果两个表中的DEPTNO有空值会影响查询结果
⑦ 典型例题
査询员工姓名 岗位 薪资 部门编号 部门名称 部门地址
注：根据员工表中的部门编号查找部门表中的部门信息，多个表查询要加表别名
查询 员工的 姓名 岗位 薪资 以及对应岗位的平均工资
查询员工工资比该员工所在岗位平均工资高的员工信息
查询中的子查询 SELECT AVG(SAL) FROM EMP WHERE JOB = e.JOB 的逻辑是用来匹配主查询和子查询中的字段。具体逻辑如下：
- 对于主查询中的每一行员工记录（使用别名
e表示），子查询会查找具有相同岗位（JOB）的所有员工。 - 然后，子查询会计算这些员工的工资的平均值。
- 最后，主查询中的条件
SAL > (子查询结果)会将每个员工的工资与其所在岗位的平均工资进行比较，只返回工资高于平均工资的员工信息。 -
JOB = e.JOB这个条件是在主查询中的每个员工记录（使用别名e表示）与子查询中计算的每个岗位的平均工资记录进行匹配。这里的e.JOB表示主查询中的员工记录的岗位，而JOB表示子查询中计算的岗位的平均工资记录的岗位。这个条件的目的是确保每个员工记录都与其所在岗位的平均工资记录相匹配，以便在结果中将员工的工资和其所在岗位的平均工资对应起来。
子查询使用HAVING
- 首先执行子查询
- 想主查询的having语句返回结果
查询哪些部门最低工资/比/20号部门最低工资高
查询平均工资最高的职位（只显示一行结果，可以用子查询操作）
8.联合查询（表连接）
定义：通过字段之间的关联条件将多张表的内容拼接到一起，子查询返回的表也可以连接
使用场景：当出现多个数据源时使用表连接
（1）内连接
- 按照条件连接两张表，只显示能匹配成功的数据（存在关联条件），匹配不成功的数据会忽略不显示
语法：
SELECT
FROM 表1 INNER JOIN 表2
ON 关联条件 --表1、表2按照关联条件进行连接
INNER JOIN 表3
ON 关联条件 --表1+2、表3按照关联条件进行连接
..................
WHERE
GROUP BY
HAVING
ORDER BY 
EMP和DEPT表的关联条件是DEPTNO
查询部门地址是纽约的员工信息以及对应的部门信息
（2）外连接
OUTER关键字在语法中是用来明确指出这是一个外连接操作
①左外连接
- 左表为主，左表全部显示，没有匹配成功的数据右表以空值填充，右表没有匹配成功的会忽略不显示
语法：
FROM 表1 LEFT [OUTER] JOIN 表2 ON 关联条件
②右外连接
- 右表为主，右表全部显示，没有匹配成功的数据左表以空值填充
语法：
FROM 表1 RIGHT [OUTER] JOIN 表2 ON 关联条件③全外连接
- 两张表全部数据都会显示，对应的表以空值填充匹配不成功的数据
语法：
FROM 表1 FULL [OUTER] JOIN ON 表2 ON 关联条件（3）交叉连接（类似笛卡尔积）
语法：
FROM 表1 CROSS JOIN 表2 --不需要关联条件（4）特殊连接
①自然连接
- 自动寻找相同列进行连接，只显示匹配成功的数据，去除重复字段
语法：
FROM 表1 NATURAL JOIN 表2 --不需要关联条件②自连接
- 相同的表相连，没有关键词，只是一种特殊形式
查询员工信息以及员工对应的领导信息
查询工资比对应领导工资高的员工信息
③不等值连接
- 表连接的一种特殊形式，没有关键词，关联条件不再是等号
查询员工信息以及对应的工资等级
ON和WHERE的区别？
LEFT JOIN 和INNER JOIN的选择？
如果你只对两个表中共有的数据感兴趣，使用 INNER JOIN；如果你想要保留左表的所有行，并在右表中找到匹配的数据，使用 LEFT JOIN。
例题 ：
查询 员工的名字,工资, 比该员工工资高的人数（两种方法都可以）
FROM emp t1确定了查询的主表t1，即我们要列出的员工表。LEFT JOIN emp t2 ON t1.sal<t2.sal将emp表的每一行（别名为t1）与工资更高的员工（别名为t2）进行连接。这里，我们关心的是t1员工相对于t2员工的工资情况。COUNT(t2.empno)统计了对于每个t1员工，工资高于他们的t2员工的数量。GROUP BY是必需的，因为我们在SELECT语句中引用了聚合函数COUNT()，同时还选择了其他列。
查询各部门工资的前两名
9.行列转换
（1）行转列
①CASE WHEN
原sc表:
此时要对cno课程编号进行行转列：
展示的为每个学生他的每一门课程成绩
总结：
要求把查询的哪一列转成列名就放在case后面,并把它的列中值（这个列中值是之后的列名，用列别名显示）进行分类放在when后面；
比如学生成绩表总共就三列（学生号，课程编号，学生成绩），我们要查询每个学生的每科成绩展示，就需要对课程编号cno进行分类转换，因此把课程编号cno放在case后面，然后把课程编号cno中所包含的所有值进行分类，即全部课程科目c001--c0010，分类放在when的后面！！
要把哪一列内容放在 转换后的列（C001-C0010）中值 就放在then 后面；
意思就是我们最后要看的结果值，比如对应上面查询，要查看的是学生成绩score，此时就把学生成绩score放在then的后面即可。
这种办法可以实现我们对需求的解决实现，但是使用比较麻烦，可能会理解错误，而且代码语句写的比较多，因此可以换种方法来更简单实现行转列！！！②DECODE
③PIVOT
语法
PIVOT(被聚合的列 FOR 行转列的列 in(列中值1,列中值2...))
select *
from 表
pivot (聚合函数(被聚合的列) for 行转列的那一行的列名 in (列中值1,,列中值2...))
被聚合的列：变成转换之后列中值的列；
列中值1,列中值2..：新增加的列名（即为行转列的那一行）就是列中值1，列中值2...。
备注：被聚合的列要加聚合函数。
注
- PIVOT函数必须在FROM子句中使用，因此需要将原始查询包装在一个子查询中。
- aggregate_function是要应用于column2的聚合函数，可以是SUM、AVG、COUNT、MAX、MIN等。
- FOR子句指定要在新列中显示的值。在IN子句中指定这些值，并在别名中指定新列的名称。
（2）列转行
语法
SELECT * FROM 表名
unpivot(列名1（接收转换前该列的数据） for 列名2（列转行的那一列的新列名） in (字段1,字段2...))字段1,字段2...：指的是要列转行的列名，既为要放到列转行的列的新列名里的列中值，就是列转行之前视图的多个列。
十、集合运算（SQL语句之间的运算）
1.并集 UNION
语法
SQL1 UNION SQL2 --去除重复数据
SQL1 UNION ALL SQL2 --不去除重复数据
2.交集 INTERSECT（找相同的数据）
语法
SQL1 INTERSECT SQL2
3.差集 MINUS
语法
SQL1 MINUS SQL2
返回在 SQL1 中出现但不在 SQL2 中出现的行。
注
- 差集分上下顺序，执行顺序从上到下，但是有（）先执行（）
- 除了 UNION ALL 以外 其余的 都会 按照第一列升序排列
二、DML
定义：针对数据进行操作，进行增 删 改 操作
1.INSERT 增加数据
语法
insert into 表名[(字段1，字段2，....)] values(值1，值2，.....)
插入一行注意
- dml语句需要提交才能生效，提交完之后不能回滚
- 数值类型数据可以不用引号
- 列名和值一一对应
- 表名后的字段如果不写，插入时默认为空值
insert into dept(deptno,dname,loc) values(50,'DEVELOPMENT','CQ');如果不声明哪些列要插入数据，则values中必须体现所有列：
insert into dept values(50,'DEVELOPMENT','CQ');- 空值的插入
省略插入（字段不可以省略）
insert into dept(deptno,dname) values(50,'DEVELOPMENT');指定NULL（字段可以省略）
insert into dept[(deptno,dname,loc)] values(50,'DEVELOPMENT',NULL);常见错误：
- 对not null列使用null
- 违反唯一约束
- 数据类型不匹配
- 值不在列约束范围内
（1）快捷插入
语法
INSERT INTO 表名[(字段1,字段2...)] sql语句
将sql查询结果插入到表中
查询结果的数量、顺序、属性、要和列一致
不能使用values如果你想将一个表（比如 EMP）中的所有记录复制到另一个已存在的表（比如 EMP1），并且这两个表结构相同或者EMP1表能够兼容EMP表中的所有列，你可以使用INSERT INTO ... SELECT ...语句来完成这个任务
INSERT INTO 表名1 SELECT * FROM 表名2
（2）同时向多张表插入多条数据
语法
INSERT ALL
INTO 表1 VALUES(字段)
INTO 表2 VALUES(字段)
INTO 表3 VALUES(字段)
..........
SELECT...FROM 表 [WHERE条件]执行逻辑
- 执行
SELECT查询从表中获取数据。 - 根据
SELECT查询的结果，对每行数据执行所有的INTO子句。每个INTO子句指定了数据应该被插入到哪个表以及如何映射到目标表的列。 WHERE子句用于过滤SELECT查询的结果，只有满足条件的行会被处理。SELECT语句不仅可以从单个表中选择数据，还可以通过连接多个表来合并数据，从而作为插入操作的数据源。这提供了极大的灵活性，使得可以根据复杂的业务逻辑从多个表中提取并组合数据，然后分发到不同的目标表中。
向10部门和20号部门分别插入相关员工
注意：此语法不可省略字段
2. DELETE 删除数据
语法
DELETE FROM 表名 [WHERE条件]若省略WHERE，则删除所有行
3.修改数据
语法
UPDATE 表名 SET 字段1=新值1,字段2=新值2...[WHERE]注意
- 字段和值不能写反
- 值可以是具体的值，也可以是单行单列子查询，也可以是函数返回的值
- 若省略WHERE，则修改所有行
- 值可以是多列子查询，前面的字段要使用多行操作符
- 改为空值：SET 字段=NULL 或SET 字段=''
- where 后不能使用=NULL 、='' 、<>'' 、<>null，可以使用 is NULL 或者is NOT NULL
将员工编号为7499的员工岗位和部门编号修改成与7698（员工编号）相同
4.手动增删改
语法
SELECT * FROM 表名 [WHERE] FOR UPDATE优点：简单方便快捷
缺点：只能修改少量数据 临时修改
5.MERGE INTO
作用：
使用原始表更新目标表数据
如果存在就更新（update），不存在就插入（insert）
语法：
MERGE INTO 表名1 --目标表（增删改的表）
USING 表名2 --数据源
ON(关联条件) --对比列（必须是主键列或者唯一列）
WHEN MATCHED THEN --当匹配成功，则根据表2的数据对表1更新
UPDATE SET 表1.字段1=新值1,表1.字段2=新值2,表1.字段3=表2.字段3...
WHERE 条件1 --选择第几行
_________________________________________________________________下面的语法如不需要可以省略
DELETE
WHERE 条件2 --必须满足以上所有条件（条件1也要满足）
WHEN NOT MATCHED THEN --当匹配不成功，则将表2的值插入到表1中
INSERT[(表1.字段1,表1.列名2,表1.列名3...)]
VALUES(表2.字段1,表2.列名2,表2.列名3...)
字段属性
每一个字段都自带属性，该属性决定了该字段存储的数据类型；常见的字段属性：字符型、数值型、日期型
（1）字符型字段属性
CHAR(n)
固定长度，该字段只能存储字符型数据，n代表总长度，数据长度不得超过n；若长度不足n，则右侧填充空格补齐，以n个长度存储
VARCHAR2(n)
可变长度，该属性只能存储字符型数据，n代表总长度，数据长度不得超过n；若长度不足n则以实际长度存储
（2）数值型字段属性
NUMBER(p,s)
常见的数值字段属性，该字段只能存储数值型数据，p代表总长度（包含了s），s代表精度（保留小数点后几位）；p可省略（默认38位），s可省略（默认保留整数）
INT
存储整数
（3）日期型字段属性
DATE
该字段只能存储日期，包含世纪年月日时分秒，但不一定都显示
TIMESTAMP
时间戳类型，比date多出了笔秒部分
三、DDL
定义：对数据库对象（表，视图，索引，序列，存储过程，函数）进行操作的语言
1.CREATE
- 创建数据库对象
语法
CREATE TABLE 表名(字段1 字段属性,字段2 字段属性....)命名规范
- 长度不能超过30字节
- 必须以字母开头
- 不建议使用下划线_以外的特殊符号
- 不能和关键字重复
- 同一张表，字段不能重复
从另一张表复制数据
CREATE TABLE 表2 AS SELECT * FROM 表1 --复制全部数据
CREATE TABLE 表2 AS SELECT * FROM 表1 WHERE 1=2 --只复制字段创建一张表 要求 带有 员工姓名岗位薪资部门编号部门名称以及对应的部门平均工资
注意：在子查询 SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO 中，WHERE DEPTNO = E.DEPTNO 子句是用来限制在计算平均工资时所使用的员工记录，确保只有属于当前部门的员工的工资被考虑在内。这样可以确保计算出来的平均工资是当前部门的工资平均值，而不是整个 EMP 表中所有员工的平均工资。子查询内部使用 GROUP BY 是没有意义的，因为子查询只是用来计算每个部门的平均工资，而不是为了分组。子查询中已经通过 WHERE 条件限制了计算平均工资的范围，确保只计算了属于当前部门的员工的工资。因此，在这个特定的子查询中，不需要使用 GROUP BY。
2.DROP（工作中不要使用）
-
删除数据库对象
语法
DROP TABLE 表名3.ALTER
- 修改数据库对象（表名、字段、字段属性），添加字段，删除字段
语法
ALTER TABLE 旧表名 RENAME TO 新表名
ALTER TABLE 表名 RENAME COLUMN 旧字段 TO 新字段ALTER TABLE ZY70 ADD(字段1 字段属性,字段2 字段属性...)ALTER TABLE 表名 DROP(字段1,字段2....)ALTER TABLE 表名 MODIFY(字段1 新字段属性,字段2 新字段属性...)（1）修改属性大小
（2）修改属性
注意：删除字段的时候至少保留一个
4.TRUNCATE
- 清空所有数据
语法
TRUNCATE TABLE 表名
DELETE与TRUNCATE区别
- DELETE是DML语句，TRUNCATE是DDL语句
- DELETE会产生回滚日志，TRUNCATE没有回滚日志
- DELETE效率低于TRUNCATE
- TRUNCATE会清空水位线，可以释放空间；DELETE不会清空水位线，不释放空间
- DELETE可以删除部分数据
四、DCL
定义：数据控制语言
安装Oracle时，若没有为下列用户重设密码，则其默认密码如下：
用户名 / 密码 登录身份 说明
sys/change_on_install SYSDBA 或 SYSOPER 不能以 NORMAL 登录，可作为默认的系统管理员
system/manager SYSDBA 或 NORMAL 不能以 SYSOPER 登录，可作为默认的系统管理员
sysman/oem_temp sysman 为 oms 的用户名
scott/tiger NORMAL 普通用户
aqadm /aqadm SYSDBA 或 NORMAL 高级队列管理员
Dbsnmp/dbsnmp SYSDBA 或 NORMAL 复制管理员
1.GRANT
赋权
GRANT 权限语句 TO 用户名2.REVOKE
收权
REVOKE 权限语句 FROM 用户名3.SYSTEM
管理员用户
4.DROP
删除用户
DROP USER 用户名 --只能删除没有任何数据库对象的用户
DROP USER 用户名 CASCADE --连同数据库对象一起删除5.创建用户并赋予权限的过程
6.角色
定义：一系列权限的集合，可以一次性向数据库赋予大量权限，分为系统角色和自定义角色
语法：
CREATE ROLE 角色名创建角色并赋权的过程
GRANT DBA TO user：将 DBA 角色授予给指定的用户。DBA（Database Administrator）是 Oracle 数据库中最高权限的角色，拥有对数据库几乎所有操作的完全访问权限。
五、TCL
定义：事务控制语言
事务：为了完成某项业务或者目的，由一系列看得见的SQL和看不见的后台进程组成逻辑工作单元
事务的属性：（ACID）
- 原子性：事务是不可分割的
- 一致性：所有事物保持数据状态一致
- 隔离性：当前事务在进行操作时，其他事物只能查看操作之前的状态
- 持久性：当事务提交之后数据永久保存
1.COMMIT 提交事务
2.ROLLBACK 回滚事务
六、函数
定义
- 封装好的程序
- 系统函数：已经封装好的函数，比如TO_DATE
- 自定义函数：根据自己的需求创建函数
特点
- 有且只有一个返回值
1.系统函数
（1）字符型函数
- CONCAT(列名1,列名2) CONCAT(字符1,字符2) 将两个字段或两个字符连接成一个字符CONCAT可以嵌套 返回型：字符
- UPPER(字符) 将字符变成大写 返回型：字符
- LOWER(字符) 将字符变成小写 返回型：字符
- INITCAP(字符) 将首字母大写，其余小写 字符（一个）之间可以用空格或-分开，分开的两个字符首字母分别大写 返回型：字符
- REPLACE(目标字符,目标字符中要被替换的部分,替换的值) 将目标字符中某字节替换成想要的值 返回型：字符（替换后的字符）
- TRIM(字符) 去除字符两侧的空格 LTRIM(字符) 去除左侧空格 RTRIM(字符) 去除右侧空格 返回型：字符
- SUBSTR(目标字符,位置参数n,[长度参数l]) 截取函数，返回截取的字符 将目标字符从第n个位置截取l个长度 返回型：字符（截取的字符）
注：若长度l省略，截到最后一位；位置参数可以是负数：-5 倒数第五位往后找 - LPAD(目标字符,长度n,值） 向目标字符左侧填充值使其长度达到n为止 返回型：字符 RPAD 向右侧填充
- LENGTH(字符) 获取字符长度 返回型：数字
- INSTR(目标字符，目标字符中要被找到的字节S,[位置参数n1],[次数n2]) 从第n1个位置查找第n2次出现的字节S，返回s所在目标字符中的位置 返回型：数值
注：n1 n2可以省略，省略后默认为1(模糊查询）
注：空格也算字符，可以被连接/替换，同样占一个位置参数
例题
- 查询姓名长度有5位的员工信息
-
SELECT ENAME,LENGTH(ENAME) FROM EMP WHERE LENGTH(ENAME)=5 - 查询姓名长度是五位并且首字母是A
-
SELECT ENAME,LENGTH(ENAME) FROM EMP WHERE LENGTH(ENAME)=5 AND ENAME LIKE('A%') SUBSTR(ENAME,1,1)='A' - 将员工按照姓名长度分组计算每组的人数 结果按照姓名长度升序
-
SELECT LENGTH(ENAME),COUNT(EMPNO) FROM EMP GROUP BY LENGTH(ENAME) ORDER BY LENGTH(ENAME) - 找出姓名找至少有两个A的员工信息
-
SELECT * FROM EMP WHERE INSTR(ENAME,'A',1,2)>0 - 找出名字正好有两个A 的员工信息
-
SELECT * FROM EMP WHERE INSTR(ENAME,'A',1,2)>0 AND INSTR(ENAME,'A',1,3)=0
（2）转换函数
- TO_DATE('目标字符','日期格式') 将目标字符转换为日期类型 返回型：日期
- TO_CHAR(目标数值或'日期',['格式']) 将数值或日期按照格式转换成字符，格式省略后默认为原格式 返回型：字符
- TO_CHAR函数本身并不会向表中添加字段。它是一个用于数据转换的函数，将指定的数据类型转换为字符型，并返回转换后的结果。
①数值转换成字符
0代表格式，当整数位无数字则强制显示0，当小数位无数字则以0补全；当小数位精度不足则按照后面那一位四舍五入，当整数位精度不足则显示成#########
多余的0变成0
9代表格式，当整数位无数字则格式不生效，当小数位无数字则以0补全；当小数位精度不足则按照后面那一位四舍五入，当整数位精度不足则显示成#########
多余的9变成空格
按照4，四舍五入
整数位可以加逗号
整数位可以加货币符号:
$美元
L本地货币
②将日期转换成字符
SYSDATE 系统当前日期
返回当前日期+时间
SELECT TO_ChaR(SYSDATE,'DAY') FROM dual --今天星期几
（3）日期型函数
- MONTHS_BETWEEN(日期1，日期2) 获取两个日期相差的月份数（前面的减后面的） 返回型：数值
例题
计算现在距25年元旦还有多少个月，保留整数
- ADD_MONTHS(日期,n) 在原有日期的基础上加/减n个月 返回型：日期
- LAST_DAY(日期） 获取日期所在月份的最后一天 返回型：日期
注：日期-日期=天数 日期+ -天数=日期
（4）数值型函数
- ABS(数值) 获取数值的绝对值 返回型：数值
- CEIL(数值) 获取离数值最近的且较大的整数 返回型：数值
- FLOOR(数值) 获取离数值最近的且较小的整数 返回型：数值
- MOD(x,y) 获取x/y的余数 返回型：数值
选择员工表 EMP 中名字长度为奇数的员工信息
注：
SELECT * 语句会选择所有列，包括 ENAME 列和名字长度列。而 LENGTH(ENAME) 表达式在 SELECT 语句中返回名字长度。这样就导致了两个名字长度列的存在。
要解决这个问题，你可以选择具体的列，而不是使用 *。
- ROUND(数值,[精度p]) 对数值按照精度p（把小数点当成0，正数为小数点第几位，负数为整数第几位）进行四舍五入（只入一位），不写精度p默认保留整数 整数位被舍以0代替 返回型： 数值|日期
- TRUNC(数值，[精度p]) 截断数值，不四舍五入，整数位被截断以0代替 返回型：数值|日期
- SIGN(数值) 判断数值正负，负数返回-1，正数返回1，0返回0 返回型：数值|日期
ROUND、TRUNC的精度p可以是年季月日，返回日期（年初、月初、季初、星期
ROUND是选取时间跨度相对最近的那一天
国外把星期天作为一周的第一天，所以本周的意思是上一周的星期天到这一周的周六
显示在一个月为 30 天的情况所有员工的日薪,忽略余数
判断emp表中的员工是平年入职还是闰年入职，并输出平年和闰年
29最好加单引号
（5）聚合函数（分组函数）
（6）开窗函数（分析函数）(包括Hive)
语法
分析函数名() OVER(分析子句)
分析函数名
- SUM AVG MIN MAX COUNT 聚合开窗
按照一定规则分组，统一分析各组情况，每一条数据返回一个值
- ROW_NUMBER() RANK() DENSE_RANK() 排序开窗
注：排序开窗的分析函数内不能有内容，分析子句必须有ORDER BY
- LAG() LEAD() 偏移开窗
- ntile() 切片分组排序开窗
分析子句
- PARTITION BY ：分组子句，表示分析函数的计算范围，不同的组互不相干；
- ORDER BY：排序子句，表示分组后，组内的排序方式；
- ROWS/RANGE：窗口子句，是在分组(PARTITION BY)后，组内的子分组（也称窗口），此时分析函数的计算范围窗口，而不是PARTITON。窗口有两种，ROWS和RANGE
- 聚合开窗的分析子句没有要求（可以不写），分析函数内必须要有东西
关于ntile(n)
ntile(n) over(partition by ...A... order by ...B...) -- 均分成n份
- ntile()函数将一个排序的结果集分成指定数量的桶（buckets），并返回一个表示每行所属桶编号的整数值。例如，如果将结果集分成4个桶，则ntile()函数返回一个值为1到4之间的整数，表示每行属于哪个桶。
- ntile()不支持ROWS BETWEEN ，比如NTILE(2) OVER(PARTITION BY ... ORDER BY ...ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
- 如果切片不均匀，默认增加第一个切片的分布
关于rows/range
- rows between unbounded preceding and current row--包括本行和之前所有的行
- rows between current row and unbounded following--包括本行和之后所有的行
- rows between 3 preceding and current row--包括本行和前三行（4行）
- rows between current row and 3 following--包括本行和后三行（4行）
- rows between 3 preceding and 1 following--从前三行到下一行（5行）
- RANGE BETWEEN CURRENT ROW AND 350 FOLLOWING：指当前行到当前行数据+350的范围内的数据
- RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING：指当前行数据幅度减5加5后的范围内的数据
假设有表datadev.t_student，数据如下
| id | score |
| stu_1 | 1 |
| stu_1 | 2 |
| stu_1 | 3 |
| stu_1 | 4 |
| stu_1 | 5 |
| stu_1 | 5 |
测试 rows between ... and ...
SELECT id, score,
sum(score) over (PARTITION by id) as a1,
sum(score) over (PARTITION by id order by score) as a2,
sum(score) over (PARTITION by id order by score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as a3,
sum(score) over (PARTITION by id order by score ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as a4,
sum(score) over (PARTITION by id order by 1) as a5
from datadev.t_student;测试结果如下：
分析：
- sum(score) over (PARTITION by id) as a1：按照id分组直接加总score，这种大家最熟悉了
- sum(score) over (PARTITION by id order by score) as a2：按照score排序，从起始行到当前行进行加总，与a3中的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW不同的是，当score相同时，算相同排名，会一起加总。类似rank的概念。
- sum(score) over (PARTITION by id order by score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)：从起始行到当前行进行加总，与a2不同的是，当score相同时，排名不同，不会加总到当前行。类似row_number的概念。
- sum(score) over (PARTITION by id order by score ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)：从起始行到末尾行进行加总，与a1相同。
- sum(score) over (PARTITION by id order by 1)：作用与a2一样，这里order by 1，相当于score相同，因此全部加总。
关于lag(...) over(...) 、lead(...) over(...)
相对于当前的行偏移
Lag取出同一字段的前N行的数据作为独立的列。
Lead取出同一字段的后N行的数据作为独立的列。
在实际应用中，若要用到取今天和昨天的某字段差值时(将行与行之间的运算改成列与列之间的运算），Lag和Lead 函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是LAG和LEAD与 left join 、right join 等自连接相比，效率更高，SQL更简洁。
lag(字段,offset,defval) over(partition by ...... oredr by ......)
lead(字段,offset,defval) over(partition by ...... oredr by ......)
- offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第五行，offset 为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）。offset默认值为1；
- defval 默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag()函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。
查询员工姓名岗位薪资以及对应岗位的平均薪资
查询员工姓名岗位薪资以及对应岗位的平均薪资,每组按照工资排序
查询员工工资比该员工岗位平均薪资高的员工信息
查询每个部门的工资前两名
窗口函数在查询的结果集中计算，而WHERE 子句在数据被选取之前执行。因此，在 WHERE 子句中不能直接引用窗口函数的结果，因为窗口函数的计算发生在 WHERE 子句之后，所以要用子查询解决这个问题
（7）其他函数
- NVL(字段,值|字符) 将该列空值替换成想要的数值 用于去空 返回型：数值|字符
- NVL(字段，值1,值2) 将该列空值替换成值1，非空值替换成值2 返回型：数字|字符
要在 NVL 函数中使用字符替换空值，需使用相同数据类型的字符作为替换值（字段可以是函数） - DISTINCT 字段 将查询结果去重
- DECODE(判断目标,目标返回的值1,重新定义值n
目标返回的值2,重新定义值n2
……………………………….
,其他情况定义为值n3 )
条件判断取值函数 值1和值n可以是不同的字段也可以是相同的字段，判断目标最重要，可以是函数也可以是字段，DECODE可以嵌套DECODE
- CASE WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
ELSE 其他结果 END
无其他结果，则省略ELSE
THEN后面要有空格
对奖金进行判断当有奖金时 翻译成有奖金 没有奖金翻译成没有奖金
当岗位是经理的时候工资-500其他的时候工资+500
显示员工姓名薪资 ,如果工资大于1000小于1999 则定义为工资小于2000 大于2000小于2999则小于3000..………………其他情况（两种方法都可以）
显示员工姓名 姓名长度 当姓名长度是偶数的时候翻译成偶数 奇数 翻译成奇数
给1、3季度入职的员工加200 给 24季度入职的员工-200
（8）开窗函数累加专题
如果order by 之后的排序字段没有重复值,就可以省略rows
对2019年和2020年的数量总额按月累计，按年汇总
两个分组条件（group by） 其中开窗函数的分析子句也需要以年为组划分 否则会求两年的累加额
select
year(crdate) 年,
month(crdate) 月,
sum(amount) 每月总量,
sum(sum(amount)) over(partition by year(crdate) order by month(crdate)) 累计 from test
where year(crdate) in (2019,2020)
group by year(crdate),month(crdate);2.自定义函数
创建函数语法
CREATE [OR REPLACE] FUNCTION 函数名(形参1 形参数值属性,形参2 形参数值属性)
RETURN 返回值属性 --以上所有数值属性不要加长度
IS --声明部分
BEGIN --执行部分，实现函数
RETURN 结果; --函数最终返回的值
END;
使用函数语法
SELECT 函数名 FROM创建一个函数返回当前日期的上个月的倒数第二天
创建一个函数返回任意日期的上个月的倒数第二天（要在函数后面加形参）
PLSQL创建一个函数，输入两个数值，返回较大数值
创建函数，传员部门号，输出该部门的平均工资
注：函数创建的参数可以在下面的SQL使用
传入一个数值 计算该数值的阶乘
传入两个数值返回最大公约数
传入两个数值 返回最小公倍数
Ⅲ、其他数据库对象
一、约束 CONSTRAINT
定义：强加在表中的规则或条件，有了约束会使表更加符合实际
行级约束：在创建表的同时跟随 字段和字段属性 直接声明约束
CREATE TABLE 表名(字段1 字段属性 约束)表级约束：在创建表的同时声明 字段和字段属性结束后再单独声明约束
CREATE TABLE 表名(字段1 字段属性,字段2 字段属性,...,约束)分类：
- 按照约束作用不同分类
1.唯一约束 UNIQUE
作用：该列不能出现重复的非空数据
建完表增加约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] UNIQUE(列名)建表时声明约束
CREATE TABLE 表名(字段1 字段属性 [CONSTRAINT 约束名] UNIQUE)CREATE TABLE 表名 (
字段1 字段属性,
字段2 字段属性,
[CONSTRAINT 约束名] UNIQUE (字段1, 字段2, ...)
);
注意：
- 唯一约束不允许该字段插入重复数据
- 该字段如果有违反约束的数据，则创建约束会失败
如果在两个字段上创建唯一约束，则两个字段的属性都一样才算重复
2.主键约束 PRIMARY KEY
作用：该列不能出现重复数据，也不能出现空值
建完表
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] PRIMARY KEY(字段...)建表时
CREATE TABLE 表名 (字段1, 属性 [CONSTRAINT 约束名] PRIMARY KEY)
CREATE TABLE 表名 (字段1 属性,
字段2 属性,
...
[constraint 约束名] PRIMARY KEY(字段1,字段2,...))注意：每张表只能有一个主键约束
3.外键约束 FOREIGN KEY
作用：该列数值取值来自于另一张表的某字段（主键列）
注意：
- 该字段如果有违反约束的数据，则创建约束会失败
- 外键约束可以插入空值
- 由于外键约束的顽固性，工作中很少使用
No action:当子表有记录的时候，父表不能删除
Cascade: 删除父表的数据，子表一同删除
Set null:删除父表数据，子表字段值为空
建完表
ALTER TABLE 表名
ADD [CONSTRAINT 约束名]
FOREIGN KEY (字段)
REFERENCES 父表(主键列或唯一列) --有主键约束或唯一约束的那个字段
SELECT * FROM ZY70建表时
CREATE TABLE 表名(字段 属性
[CONSTRAINT 约束名] REFERENCES 父表(主键列或唯一列))CREATE TABLE 表名(字段1 属性,字段2 属性,...,
[CONSTRAINT 约束名] FOREIGN KEY(字段1,字段2,...)
REFERENCES 父表(主键列或唯一列))--只能参考一个字段4.检查约束 CHECK
作用：根据自己实际需求创造约束 相当于WHERE条件
两个字段的条件用OR连接，属性满足其中一个即可
建完表
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] CHECK(条件1 AND/OR 条件2...)建表时
CREATE TABLE 表名(字段1 属性 [CONSTRAINT 约束名] CHECK(条件),
字段2 属性))
--行级写法只能引用当前字段（字段1）CREATE TABLE 表名(字段1 属性,字段2 属性,...,
[CONSTRAINT 约束名]
CHECK(条件...))5.非空约束 NOT NULL
作用：该列不能出现空值
建完表
ALTER TABLE 表名 MODIFY 字段 NOT NULL建表时
CREATE TABLE 表名(字段1 属性 NOT NULL,字段2 属性 NOT NULL...)6.默认值约束 DEFAULT
作用：当该列不插入数据时，默认填入一个值
建完表
ALTER TABLE 表名 MODIFY 字段1 DEFAULT 值1建表时
CREATE TABLE 表名(字段1 属性 DEFAULT 值1,
字段2 属性 DEFAULT 值2...)7.删除约束
适用于unique,primary key,foreign key,check
ALTER TABLE 表名 DROP CONSTRAINT 约束名
适用于not null,default
ALTER TABLE 表名 MODIFY 字段 NULL
ALTER TABLE 表名 MODIFY 字段 DEFAULT NULL二、视图VIEW
定义：将SQL语句查询的结果动态保存到数据库中，是一张虚拟表
- 视图基于的表，叫基表
- 可以基于一张表创建，也可以基于多张表创建，也可以基于视图创建视图
- 视图本身不包含数据，数据来源于基表
1、创建视图
语法
CREATE [OR REPLACE] --创建或替换（替换已经创建的视图）
VIEW 视图名 AS SQL语句
[WITH READ ONLY] --只读视图注意：
- 给视图插入数据时必须符合基表属性和约束的要求
- 可以通过对视图增删改来达到对基表的增删改，但不能增删改虚拟列（有别名的那一列）
- 插入的数据没有符合视图的定义，就只能展示在基表中
创建视图 要求 展示 部门名称部门地址 小写的员工姓名 薪资 奖金 薪资+奖金
创建一个视图 要求 基于刚才的视图 展示 部门名称 以及各个部门的薪资总和
注：基于原视图创建的新视图，如果原视图基表发生变化，则新视图也会发生变化
2.修改视图
语法：
CREATE OR REPLACE --创建或替换（替换已经创建的视图）
VIEW 视图名 AS SQL语句
[WITH READ ONLY] --只读视图3.删除视图
语法：
DROP VIEW 视图名三、索引（索引不是越多越好）
1.伪列
定义：伪装起来不容易发现的列，除非特定查询才能看见
（1）ROWNUM
- 对查询结果自动生成一个从1开始的连续的自然数序号
查询前三个工资最高的员工信息
为什么不能用SELECT * FROM emp WHERE ROWNUM<4 ORDER BY sal？
- 在Oracle数据库中，ROWNUM是在查询结果返回之前赋值的，这意味着它是在结果集的排序之前确定的。当你使用ROWNUM与ORDER BY组合时，尤其是在你的查询中尝试按照薪水（sal）排序时，会遇到一个问题：ROWNUM先被赋值，然后才进行ORDER BY排序。这样，你得到的结果可能不是你期望的那样。
- 这里是具体的解释：
- SELECT * FROM emp WHERE ROWNUM < 4 ORDER BY sal;这个查询首先查找满足ROWNUM < 4条件的前3行数据，然后尝试对这些数据进行排序。但是，由于ROWNUM的值是在行被选中时立即分配的（即在任何排序发生之前），这个查询实际上会随机选择表中的前3行，然后对这些已选中的行按照sal列进行排序。这并不会先按sal排序所有行，然后选择工资最低的3行。
- 正确的做法是先对所有行进行排序，然后选择排序后的前3行。在这个查询中，内部的子查询首先根据薪水排序所有员工。然后，外部的查询选择了这个有序列表中的前3行。这样，你就能得到实际上薪水最低的前3名员工。
（2）ROWID
定义：类似于身份证 ，每条数据在存入到数据库当中时，oracle 会自动分配一串18位的不重复的字符串，用于记录每条数据的物理位置
注：伪列不能随意修改
2.索引INDEX分类
- 定义：指向表中的数据，通过建立索引可以提高查询效率
- 缺点：降低DML语句的速度
- oracle会自动使用和维护索引
根据存储内容不同分为：
（1）B_TREE
-
索引列原始数据+ROWID
-
默认的索引类型，最常用
-
适用于数据量大、列基数大的字段
（2）位图索引
-
位图+ROWID
-
适用于列基数比较小的字段
（3）反向键索引
-
索引列反向存储+ROWID
-
适用于反向数据比较明显，或者某叶数据占比过高
（4）基于函数的索引
-
索引列原始数据经过函数处理+ROWID
根据列的个数分为：
（1）单列索引
基于一个字段创建
（2）复合索引（联合索引）
基于多个字段创建
根据唯一性分为：
（1）唯一索引
-
索引列数据不能重复
-
位图索引创建不了唯一索引，添加主键列或唯一约束列的时候会自动创建一个同名的唯一索引
（2）非唯一索引
索引列数据可以重复
（1）全盘扫描
- 按照条件列，在对应的数据列，从第一条数据检索到最后一条数据，将符合条件的数据的ROWID取出，根据ROWID取出整行数据
（2）索引扫描
- 按照条件列，在对应的索引列，从大概位置开始检索，检索到最后一条符合条件的数据为止，将符合条件的数据的ROWID取出，根据ROWID取出整行数据
（3）创建索引
语法
B_TREE
CREATE INDEX 索引名 ON 表名(字段1,字段2...)
位图
CREATE BITMAP INDEX 索引名 ON 表名(字段1,字段2)位图:位索引在创建时，会扫描整张表，为索引列每个取值创建一个不重复的位图，来描述该取值
反向键
CREATE INDEX 索引名 ON 表名(字段1,字段2) REVERSE基于函数
CREATE INDEX 索引名 ON 表名(函数(字段1))唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段1,字段2)（4）索引失效情况
（5）删除索引
DROP INDEX 索引名
（6）修改索引
ALTER INDEX 索引名 RENAME TO 新索引名
（7）禁用索引
ALTER INDEX 索引名 UNUSABLE
（8）接近索引
ALTER INDEX 索引名 REBUILD
四、序列 SEQUENCE
定义：ORACLE提供的一组能够自动增长的序号
作用：为主键列提供值，提供唯一标识
1.创建序列
语法
CREATE SEQUENCE 序列名 --这里可以结束
[ START WITH N --初始值n，默认1
INCREMENT BY N --增长幅度n，默认1，可以是负数，此时递减
MAXVALUE N | NOMAXVALUE --最大值n，或没有最大值，默认10的27次方
MINVALUE N | NOMINVALUE --最小值N，或没有最小值，默认-10的27次方
CYCLE | NOCYCLE --循环或不循环，默认不循环
CACHE N | NOCACHE --缓存N，或没有缓存，默认20个序号
]
注：缓存必须小于循环值（最大值/增长幅度）
2.序列的使用
- 当前值CURRVAL
- 下一个值NEXTVAL
此时序列还没有初始值
第一次使用序列要用NEXTVAL，返回初始值
循环之后初始值为1
3.删除序列
DROP SEQUENCE 序列名
4.使用ALTER SEQUENCE重新设置起始值：
ALTER SEQUENCE your_sequence_name INCREMENT BY -N; -- N是当前值与目标重置值之间的差值
SELECT your_sequence_name.NEXTVAL FROM dual; -- 使改变生效
ALTER SEQUENCE your_sequence_name INCREMENT BY 1; -- 恢复增量为1
309
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
