SQL基础语句
oracle数据特点： 完整的数据管理功能。
数据的大量性 、数据保存的持久性 、数据的共享性 、数据的可靠性 、完备关系的产品 、信息准则一关系型DBMS的所有信息都应在逻辑上用一种方法,即表中的值显示地表示 、保证访问的准则 、视图更新准则一只要形成视图的表中的数据变化了，相应的视图中的数据同时变化 、数据物理性和逻辑性独立准则
Oracle Server服务器：包含两个部分 : oracle instance 实例 ; oracle database 数据库
SYS用户和SYSTEM 用户：
sys是超级用户，具有最高权限。--sysdba，create database权限
system是管理操作员 --sysoper角色，没有create database
scott普通用户
SELECT语句
SELECT * FROM EMP --查询emp表中所有信息
* 表示通配符，全部匹配出来。但是能不用就不用,非常耗资源,而且查看不方便
SELECT 字段 FROM 表 WHERE 字段 比较运算符 字段、子查询、值等 --
WHERE子句中的运算符： = <> != < >= BETWEEN、 LIKE、 IN、 NOT IN、 IS NULL
在SQL中，关键词、表、字段 大小写不敏感；对表中所存储的值大小写敏感。
LIKE操作符
SELECT * FROM 表名 WHERE 字段 LIKE '查找的值'
- '%a' 以a结尾的数据
- 'a%' 以a开头的数据
- '%a%' 含有a的数据
- 'a' 三位并且中间字母是a的
- '_a' 两位并且结尾字母是a的
- 'a_' 两位并且开头字母是a的
ESCAPE -> %,_的转义
注意:如果有多个like查询则每个后面都要跟一个转义
SELECT * FROM EMP
WHERE ENAME LIKE'_%_' ESCAPE '' OR ENAME LIKE '%_%' ESCAPE '';
其他操作符
- IN : 允许在WHERE中规定多个值；
- BETWEEN : 用于选取介于两个数值之间的数据范围内的值，注意左闭右闭
- AS : 别名操作符， 用法： SELECT 字段 AS 别名 FROM 表 注意AS可以不写，但是要有空格
SQL语言
6 大类语言：DDL ,DML, DQL, DCL, TCL, CCL
数据定义语言DDL：针对数据库对象进行操作
①建表语句：CREATE TABLE
CREATE TABLE 表名(字段1 数据类型1(长度), 字段2 数据类型2,...); CREATE TABLE 表名 AS SQL语句;
--相当于全盘复制
CREATE TABLE 表名 AS SELECT * FROM 表名2 WHERE 1=2;--创建一张只要表2结构（只有字段没有数据）的备份表
另:
对表做解释：
COMMENT ON TABLE TB_NAME IS '解释语句'
对表的字段做解释：
COMMENT ON COLUMN TB_NAME.COL_NAME IS '解释语句'
②修改表：ALTER
修改字段类型\约束： ALTER TABLE 表名 MODIFY 字段 修改后的类型 [修改后的约束]; -- MODIFY（修改），更改数据类型时列必须为空。
修改字段名字： ALTER TABLE 表名 RENAME COLUMN 原字段名 TO 新字段名 -- RENAME(重命名)COLUMN(列)
添加字段： ALTER TABLE 表名 ADD 字段名 数据类型; --不需要写COLUMN
删除字段： ALTER TABLE 表名 DROP 字段名;
③删除表：DROP
DROP TABLE 表名; --这种方式，如果开启了回收站，可以通过回收站恢复
DROP TABLE 表名 PURGE; -- 强制删除，物理上删除
SELECT VALUE FROM V$PARAMETER WHERE NAME LIKE 'recycle%' --查询数据字典可以观察回收站是否开启 FLASHBACK TABLE 表名 TO BEFORE DROP; --通过闪回命令恢复表
④清空表：TRUNCATE
TRUNCATE TABLE 表名;
-- 清空一张表，不走日志无法恢复，但是删除效率快，相当于格式化，不写WHERE
注意: oracle的截断,只是针对数据,不会清除比如约束等表结构; mysql的截断则相当于先删除再建立一张表,其中的表结构会改变.
数据操作语言DML：针对数据对象进行的操作
①插入数据：INSERT
INSERT INTO 表名(字段1，字段2,...)VALUES(值1，值2，...);
INSERT INTO 表名[字段1，字段2,..] SQL语句 --将SQL语句得到的结果集全部插入到目标表中去，如果前后字段顺序相同，则目标表后不需写字段排序。
oracle显示提交: 当不点提交（绿色箭头），数据保存在内存中，点了提交才把数据写入硬盘中 ; mysql隐式提交,运行insert就提交.
②删除数据：DELETE
DELETE FROM 表名 [WHERE 条件] --不写WHERE删除所有数据，删除效率慢，但是走日志，可以恢复。
delete是对行的操作，where条件是针对的行，可以写子查询，但是要返回单行。
MYSQL等一些数据库删除后自动走提交，无法恢复。
③更新数据：UPDATE
UPDATE 表名[别名] SET 字段1=新值1，字段2=NULL，字段3=SQL语句 [WHERE条件]; -- 在更新中用 = 赋值NULL
UPDATE EMP SET (EMPNO.ENAME)=(SELECT EMPNO,ENAME FROM EMP1 WHERE EMPNO=7788) [WHERE条件]; -- 以扩起来一起写
④合并数据：MERGE INTO
MERGE INTO 表1 [别名1] USING 表2|(SQL语句) [别名2] ON (别名1.字段 = 别名2.字段) --ON条件有括号！其中如果有多个条件用 AND 连接
WHEN MATCHED THEN -- 当条件匹配时，更新字段
UPDATE SET 别名1.字段1=别名2.字段1 ， 别名1.字段2=别名2.字段2
WHEN NOT MATCHED THEN -- 当条件不匹配时，插入行
INSERT (别名1.字段1，别名1.字段2,...)VALUES(别名2.字段1，别名2.字段2,...);
数据查询语言DQL:也叫数据检索语句，用于从表中获得数据
SELECT为用的最多的关键字 ，其他的还有 WHERE, ORDER BY, GROUP BY, HAVING 等
数据控制语言DCL:通过grant或revoke获得许可，确定单个用户和用户组对数据库对象的访问
CREATE USER 名字 IDENTIFIED BY 密码; --创建用户和密码
GRANT CREATE SESSION TO 名字; -- 给予创建对话权限
GRANT CONNECT,RESOURCE TO 名字; -- 连接资源权限
GRANT CREATE ANY TABLE TO 名字; --创建任意表权限
GRANT DBA TO 名字; -- DBA权限
GRANT SELECT ANY TABLE TO 名字; --给予查询任意表的权限
GRANT EXECUTE ANY PROCEDURE TO 名字; --给予调用存储过程的权限
以此类推,查询任意表\修改任意表等等的权限
事务控制语言TCL:实现被DML影响的表的所有行得以及时更新
COMMIT提交 ； ROLLBACK 回滚
指针控制语言CCL:用于对一个或多个表单独进行操作
如，DECLARE(声明) ， CURSOR(游标) ， FETCH INTO（抓取游标） 等。
伪表以及一些内置函数
伪表: DUAL ,提供一些四则运算以及函数的验证。
查询系统的时间 ： SELECT SYSDATE FROM DUAL;
oracle 中的内置函数
1.LENGTH(VAR)
返回字段或者字符、数值等的长度
查询名字长度为4的员工信息： SELECT * FROM EMP WHERE LENGTH(ENAME)=4;
2.CONCAT(VAR1,VAR2)
（1）CONCAT(VAR1,VAR2) :只能两个对象拼接，将两列或者列表+字段无缝拼接成新列。
（2）‘||’ :拼接符，可以多个对象拼接。
数值可以和字符串直接拼接 -- > 百分数
SELECT 66 || '%' FROM DUAL; -- 66%
SELECT CONCAT(66,'%') FROM DUAL; -- 66%
3.字符转ASCII码
SELECT ASCII('A'), ASCII('B') FROM DUAL; -- 65 66
SELECT CHR(65),CHR(66) FROM DUAL; --A B
注：字符本身就可以比较大小（有隐式转换），不需要转码。
4.INSTR(VAR,'查找的字符',[X],[Y])
VAR:字段，字符串，数字串等
X:查找的起始位置，不写默认第一个开始(字符下标从1开始)
Y:表示查找字符的第几次出现，不写默认第一次的出现
语法：
SELECT INSTR('ABCDEFGHD','D', 5) -- 从左开始第5个开始查找D第一次出现的位置，返回9
,INSTR('ABCDEFGHD','D', -5, 1) -- 逆向第5位开始查找第一次出现D ，但是结果正向显示 ，返回4
FROM DUAL;
--查找EMP 表中ENAME 字段中含有S的员工信息
SELECT * FROM EMP WHERE INSTR(ENAME, 'S')>0; -- 只要出现过，位置一定大于0，所以表示含有
ENAME
----------
SMITH
JONES
SCOTT
ADAMS
JAMES
5.SUBSTR(VAR,IND,[len])
VAR: 字段，字符串，数字串等
IND: 截取的起始位置(包含)，必须写
len: 截取的长度，不写默认截到最后
语法：
SELECT SUBSTR('ABCDE', 0, 1) FROM DUAL; -- 截取结果为A
起始位置写0和写1没啥影响;
SELECT SUBSTR('ABCDE', -4, 2) -- 结果：BC （只能往右截取）
FROM DUAL;
-- 截取EMP表中的JOB 字段， 用员工姓名的长度为截取起始位置，截取到最后
SELECT SUBSTR(JOB , LENGTH(ENAME)) FROM EMP;
SUBSTR(JOB,LENGTH(
------------------
K
SMAN
ESMAN
GER
MAN
GER
GER
YST
SIDENT
MAN
K
K
LYST
6.TRIM(VAR) 去空格函数
只能去除两边的，不能去除中间的。
（1）LTRIM(VAR):去除左边空格
（2）RTRIM(VAR):去除右边的空格
（3）TRIM(VAR): 去两边的空格
7.PAD 填充函数
（1）LPAD(VAR , LEN , '填充格式') ：左填充
（2）RPAD(VAR , LEN , '填充格式') ：右填充
SELECT ENAME , LPAD(ENAME,2,'01') A,RPAD(ENAME,10,'01') B,LPAD(ENAME,10,'01') C FROM EMP;
ENAME A B C
---------- ---- -------------------- --------------------
SMITH SM SMITH01010 01010SMITH
ALLEN AL ALLEN01010 01010ALLEN
WARD WA WARD010101 010101WARD
JONES JO JONES01010 01010JONES
MARTIN MA MARTIN0101 0101MARTIN
BLAKE BL BLAKE01010 01010BLAKE
CLARK CL CLARK01010 01010CLARK
SCOTT SC SCOTT01010 01010SCOTT
KING KI KING010101 010101KING
TURNER TU TURNER0101 0101TURNER
ADAMS AD ADAMS01010 01010ADAMS
JAMES JA JAMES01010 01010JAMES
FORD FO FORD010101 010101FORD
MILLER MI MILLER0101 0101MILLER
注意点: len没有原来串长就是截取 ; 填充格式不是一个整体，填充格式从左往右依次填充
8.REPLACE 替换函数
REPLACE(VAR,'被替换的字符或字符串','替换后的字符或字符串')
SELECT 'ABCDEFGBB', REPLACE('ABCDEFGBB', 'B', '&') --结果： A&CDEFG&&
FROM DUAL ;
SELECT REPLACE('ABCDEFGBB', 'B', 'qqqq') --结果： AqqqqCDEFGqqqqqqqq (一次性全部替换，整个字符是一个整体)
FROM DUAL ;
1.如果被替换的匹配到多次，则全部替换。
2.替换后的字符串为一个整体，每次都是全部替换，不是挨个替换。
9. INITCAP(CAR)
将英文字符串首字母大写，其余小写， INIT--> 初始化
语法：
SELECT INITCAP('ABCAB') -- Abcab
FROM DUAL ;
10.LOWER(VAR) : 将大写字符变为小写
11.UPPER(VAR) : 将小写字符变成大写
12. ROUND(NUM, N) 四舍五入函数
NUM :数字
SQL> SELECT 2333.456 原, ROUND(2333.456, 2) 小数点后, ROUND(2333.456, -2) 小数点前 FROM DUAL;
原 小数点后 小数点前
---------- ---------- ----------
2333.456 2333.46 2300
13. TRUNC(NUM, N) 截取函数
不进行四舍五入的保留！ N:保留小数点(后|前)N位
SQL> SELECT 2333.456 原, TRUNC(2333.456, 2) 小数点后, TRUNC(2333.456, -2) 小数点前 FROM DUAL;
原 小数点后 小数点前
---------- ---------- ----------
2333.456 2333.45 2300
14.FLOOR(NUM)
向下（小）取整
SQL> SELECT FLOOR(2.9) FROM DUAL;
FLOOR(2.9)
----------
2
15.CEIL(NUM)
向上（大）取整
SQL> SELECT CEIL(2.1) FROM DUAL;
CEIL(2.1)
----------
3
16. MOD(M ,N)
取余函数 ,相当于其他编程里的 M % N
SQL> SELECT MOD(5,2) FROM DUAL;
MOD(5,2)
----------
1
17. POWER(M,N)
M的N次幂
SQL> SELECT POWER(2,3) FROM DUAL;
POWER(2,3)
----------
8
18.ABS(M)
绝对值函数,将负数转为正数.
SQL> SELECT ABS(-2) FROM DUAL;
ABS(-2)
----------
2
19.TO_NUMBER（'m'）
将一个字符串类型的数值转为数字类型 ,一般用来防止隐式转换造成的资源消耗;
20.NVL(VAR, N)
将一个NULL(无)值处理成一个非NULL;
NVL 不一定处理成数值类型，也可以是字符类型,或者其他字段.但是不管返回什么,要注意和本字段的数据类型相匹配;
NULL值不参与运算，只要式子有null值，整个式子为null ！！
求员工的工资，SAL和COMM 栏相加
SQL> SELECT SAL,COMM,SAL+COMM , SAL + NVL(COMM,0) FROM EMP;
SAL COMM SAL+COMM SAL+NVL(COMM,0)
---------- ---------- ---------- ---------------
800 800
1600 300 1900 1900
1250 500 1750 1750
2975 2975
1250 1400 2650 2650
2850 2850
2450 2450
3000 3000
5000 5000
1500 0 1500 1500
1100 1100
950 950
3000 3000
1300 1300
如果不处理null值,则算数运算的结果为null;
附加：
NVL2(字段，不为空返回这个值，为空返回这个值)
SQL> SELECT COMM, NVL2(COMM,'不为空','为空') FROM EMP;
COMM NVL2(C
---------- ------
为空
300 不为空
500 不为空
为空
1400 不为空
为空
为空
为空
为空
0 不为空
为空
为空
为空
为空
21.COALESCE(VAR1, VAR2, VAR3...)
返回第一个非空的值;
SELECT EMPNO, ENAME, JOB, COALESCE(TO_CHAR(COMM), ENAME, JOB) FROM EMP;
SQL> SELECT EMPNO, ENAME, JOB, COALESCE(TO_CHAR(COMM), ENAME, JOB) FROM EMP;
EMPNO ENAME JOB COALESCE(TO_CHAR(COMM),ENAME,JOB)
---------- ---------- --------- ----------------------------------------
7369 SMITH CLERK SMITH
7499 ALLEN SALESMAN 300
7521 WARD SALESMAN 500
7566 JONES MANAGER JONES
7654 MARTIN SALESMAN 1400
7698 BLAKE MANAGER BLAKE
7782 CLARK MANAGER CLARK
7788 SCOTT ANALYST SCOTT
7839 KING PRESIDENT KING
7844 TURNER SALESMAN 0
7876 ADAMS CLERK ADAMS
7900 JAMES CLERK JAMES
7902 FORD ANALYST FORD
7934 MILLER CLERK MILLER
这个语句不会用到job栏 ;
22. SQRT()
平方根函数 ;
SQL> SELECT SQRT(10) , SQRT(16) FROM DUAL;
SQRT(10) SQRT(16)
---------- ----------
3.16227766 4
不能整除的会自动保留一些小数;
23.随机函数
(1)DBMS_RANDOM.RANDOM
产生一个任意大小任意正负的随机数;
1.常规用法
SQL> SELECT DBMS_RANDOM.RANDOM FROM DUAL;
RANDOM
----------
1912709653
2.与其他函数结合使用,产生一个100以内的随机数
SQL> SELECT ABS(MOD(DBMS_RANDOM.RANDOM,100)) FROM DUAL;
ABS(MOD(DBMS_RANDOM.RANDOM,100))
--------------------------------
66
(2)DBMS.RANDOM.VALUE
产生一个 0-1 的随机数。
1.常规用法
SQL> SELECT DBMS_RANDOM.VALUE FROM DUAL;
VALUE
----------
.583473718
2.产生一个50~100的随机数(括号内可以指定范围)
SQL> SELECT DBMS_RANDOM.VALUE(50,100) FROM DUAL;
DBMS_RANDOM.VALUE(50,100)
-------------------------
56.1782139
3.产生一个100-1000的随机数
SQL> SELECT TRUNC(DBMS_RANDOM.VALUE * 900 + 100) FROM DUAL;
TRUNC(DBMS_RANDOM.VALUE*900+100)
--------------------------------
390
(3）DBMS_RANDOM.STRING(OPT , N)
产生随机字符串
第一个参数OPT 表示printable，随机字符的规定:
'u' , 'U'(大写字母)
'l' , 'L'(小写字母)
'a' , 'A'(大、小写字母)
'x' , 'X'(数字、大写字母)
'p' , 'P'(可打印字符)
第二个参数表示返回字符串的长度。
SQL> SELECT DBMS_RANDOM.STRING('u',3) FROM DUAL;
DBMS_RANDOM.STRING('U',3)
--------------------------------------------
ZTL
SQL> SELECT DBMS_RANDOM.STRING('L',3) FROM DUAL;
DBMS_RANDOM.STRING('L',3)
--------------------------------------------
iss
SQL> SELECT DBMS_RANDOM.STRING('A',3) FROM DUAL;
DBMS_RANDOM.STRING('A',3)
-------------------------------------------
aKl
SQL> SELECT DBMS_RANDOM.STRING('X',3) FROM DUAL;
DBMS_RANDOM.STRING('X',3)
-------------------------------------------
6EV
(4）DBMS_RANDOM.NORMAL 产生正态分布的随机数
此正态分布标准偏差为1，期望值为0。 这个函数返回的数值中有68%是介于-1与+1之间，95%介于-2与+2之间，99%介于-3与+3之间。
(5）随机日期
SELECT TO_DATE(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J') FROM DUAL;
通过下方语句可以获得日期的基数：
SELECT TO_CHAR(SYSDATE,'J') FROM DUAL;
24.REVERSE 倒序查询
SELECT REVERSE(COL_NAME) FROM TB_NAME;
是将查询的字段中的数据倒序显示
SQL> SELECT REVERSE('ABCD') FROM DUAL;
REVE
----
DCBA
日期函数
1.DATE + - N
返回一个日期加减N天后的日期值，可以精确到秒。
SQLPLUS中一开始只能显示年月日,修改session可以改变形式
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> SELECT SYSDATE FROM DUAL; -- 当时的时间
SYSDATE
-------------------
2023-02-06 09:22:00
SQL> SELECT SYSDATE + 2 FROM DUAL;
SYSDATE+2
-------------------
2023-02-08 09:22:17
2.ADD_MONTHS(DATE,[-]N)
返回一个日期加或减N个月的日期值，可以精确到秒。
特殊用法：+-12 表示加减一年。
SQL> SELECT ADD_MONTHS(SYSDATE,+12) FROM DUAL;
ADD_MONTHS(SYS
--------------
2024-02-06 09:22:59
3.MONTHS_BETWEEN(DATE1, DATE2)
返回两个日期的月份差，是一个带小数的数值。
4.LAST_DAY(DATE)
返回输入日期所在月份的最后一天的日期，精确到当前时间的秒。
SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;
LAST_DAY(SYSDATE)
-------------------
2023-02-28 09:26:04 -- 今年2月28天
5.NEXT_DAY(DATE, 1-7 | '星期日~星期六')
返回输入日期下，下一次星期几出现的日期，精确到当前时间的秒。
注意：外国星期日星期六 对应 17
6.TO_DATE('字符类型的日期', 'YYYY-MM-DD HH:MI:SS')
前面是字符串，后面是给定转成日期的格式。
注意：存在小时制的问题， HH是12小时制 ； HH24 是24小时制。
SQL> SELECT TO_DATE('2022-09','YYYY-MM') FROM DUAL;
TO_DATE('2022-09','
-------------------
2022-09-01 00:00:00
会自动填充到当月第一天开始，因为日期格式最少也要输出到天。
7.TO_CHAR(DATE,'日期格式')
返回一个转为字符类型的日期
TO_CHAR(SYSDATE, 'YYYY') -- 年
TO_CHAR(SYSDATE,'YYYY-MM') -- 年-月
TO_CHAR(SYSDATE,'YYYY-MM-DD') -- 年-月-日
TO_CHAR(SYSDATE,'MM') --当年第几月（数值）
TO_CHAR(SYSDATE,'MONTH') -- 当年第几月的全称（字符串）
TO_CHAR(SYSDATE,'D') --当周的第几天
TO_CHAR(SYSDATE,'DD') --当月的第几天
TO_CHAR(SYSDATE,'DDD') --当年的第几天
TO_CHAR(SYSDATE,'HH | HH24') --小时（注意小时制）
TO_CHAR(SYSDATE,'MI') --分钟
TO_CHAR(SYSDATE,'SS') --秒
TO_CHAR(SYSDATE,'Q') --当年第几季度
TO_CHAR(SYSDATE,'CC') --世纪
TO_CHAR(SYSDATE,'W') --当月第几个周
TO_CHAR(SYSDATE,'WW') --当年第几个周
TO_CHAR(SYSDATE,'DAY') -- 该日期是星期几
TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=AMERICAN') --用英文输出星期几,DAY大写， day小写
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; --提取日期中的指定
8.TRUNC \ ROUND (DATE,'日期格式')
(1) TRUNC(DATE,'日期格式') ：对日期进行截取,返回一个日期，精确到给定
TRUNC(DATE) :精确到当前日期的年月日
TRUNC(DATE,'YYYY') :精确到当前日期所在年份的第一天
TRUNC(DATE,'MM') :精确到当前日期所在月份的第一天
TRUNC(DATE,'HH | HH24') :精确到当前日期所在整点
（2）ROUND :不同与截取，如果当前日期对于给定大于一半，则到下个，否则到本个
SELECT ROUND(DATE,'YYYY') FROM DUAL -- 精确到'YYYY' :日期在7月1号或者以后则返回下一年的第一天。
SELECT TRUNC(ADD_MONTHS(SYSDATE,-1),'MM') FROM DUAL; -- 获取上月第一天
两个日期相减得到的是天数的差，返回一个数值
对断续的数据进行分组
1.断续的数字
使用一个连续的序号减去排序号的断续的数字，上下连续的得到相同的值，方便分组
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 5 | 4 | 1 |
| 6 | 5 | 1 |
| 8 | 6 | 2 |
| 9 | 7 | 2 |
| 10 | 8 | 2 |
| 11 | 9 | 2 |
2.断续的日期
直接用断续的日期减去连续的数字，则其中上下连续的日期会得到相同的日期，以此来分组
SYSDATE - ROW_NUMBER()OVER(ORDER BY 日期)
排序操作
一.排序操作
(1)ASC :升序，默认是升序，可以省略不写，是由小到大
(2)DESC: 降序，必须要写，由大到小
语法：
SELECT * FROM 表 [WHERE] ORDER BY 字段1,字段2，... ASC|DESC
（有多个字段，左字段相同则使用右字段继续排序）
二. SQL 的执行顺序
SELECT -- 3
FROM -- 1
WHERE -- 2
ORDER BY -- 4 排序永远在最后，所以可以使用之前设定的别名
三.ROWID
当向一张表中插入数据时，就生成一个ROWID，这是在物理上真是存在的，是唯一的。
SELECT E.* ,ROWID FROM EMP E; --查询一张表的ROWID时需要对表起别名。
删除重复数据
原理：一张表中即使每行数据全部相同，它们的ROWID也不会相同，利用这个特性可以删除表中的重复数据。
重复的只有两个:
DELETE FROM TB_NAME
WHERE ROWID IN (
SELECT MAX(ROWID) --用MAX选择其中一个行去掉。
FROM TB_NAME
GROUP BY COL_NAME1,COL_NAME2,... --表中需要去重的字段，可以是多个
HAVING COUNT(*)>1 --把需要去重的行筛选出来。
);
重复的多于两个:
DELETE FROM EMP1 E
WHERE NOT EXISTS(
SELECT EMPNO -- select后面随便写个什么
FROM EMP1
GROUP BY EMPNO -- 分组后面写需要去重的字段
HAVING MAX(ROWID)=E.ROWID -- 每组只保留一个rowid的值,其余没对上的删掉
);
四.ROWNUM
伪列，根据返回的结果集，生成相应的序号
1.查询emp表中前五条员工的信息
SELECT E.*,ROWNUM FROM EMP E WHERE ROWNUM <= 5;
注：ROWNUM只能 < 查找 ，不能 > 查找； 可以在where中使用
2.调整页数，一页显示指定条数
员工表，按照员工姓名排序，调整为一页6条记录，使用ROWNUM做。
实现语句：
SELECT E.*
,ROWNUM
,CEIL(ROWNUM/6) 页码 --页码通过伪列除每页指定得条数，向上取整得到
,DECODE(MOD(ROWNUM,6),0,6,MOD(ROWNUM,6)) 记录数 --伪列对6取余，不为零返回本身，为零返回6
FROM (SELECT * FROM EMP ORDER BY EMPNO) E --子查询排序
五.LEVEL
基本信息
伪列，在树形结构中，表示层级的伪列。
level可以使查询循环，这是它作为伪列的特殊用法。
树形结构，和 CONNECT BY 联合使用来确定上下级。
1.构造连续的数字
SQL> SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 5;
LEVEL
----------
1
2
3
4
5
2.构造连续的日期
SQL> SELECT SYSDATE + LEVEL FROM DUAL CONNECT BY LEVEL <= 5;
SYSDATE+LEVEL
-------------------
2023-02-09 15:42:19
2023-02-10 15:42:19
2023-02-11 15:42:19
2023-02-12 15:42:19
2023-02-13 15:42:19
六.树查询语法
概念
树形查询通常由根节点、父节点、子节点、叶子节点组合
根节点：当前节点之上没有其他节点的
父节点：当前节点之下有其他节点的
子节点：当前节点之上有其他节点的
叶子节点：当前节点之下没有其他节点的, 是索引查询的入口
树形查询语法
SELECT
FROM
[WHERE]
[START WITH 条件] -- 表示从哪个开始,不写则默认全表作为一个根节点
CONNECT BY PRIOR 儿子列 = 父亲列 -- 查下级
父亲列 = 儿子列 -- 查上级
ORDER SIBLING BY 列 ASC|DESC --同一级别排序条件
解释: 当使用表连接来查询员工上级的信息时，会使用自连接；第一张员工表的MGR 字段 和 第二张上级表的EMPNO 进行连接。所以对于表本身来说，MGR 为父亲列，EMPNO 为儿子列。
PRIOR 表示构造树的源头方向，写在儿子列前面表示指向下级。
查询举例:查询KING 的下属，显示节点深度。
SQL> SELECT ENAME , LEVEL
2 FROM EMP
3 START WITH ENAME = 'KING'
4 CONNECT BY PRIOR EMPNO = MGR;
ENAME LEVEL
---------- ----------
KING 1
JONES 2
SCOTT 3
ADAMS 4
FORD 3
SMITH 4
BLAKE 2
ALLEN 3
WARD 3
MARTIN 3
TURNER 3
JAMES 3
CLARK 2
MILLER 3
KING的等级为1；下边2的为他的直系下属。下属后面跟着自己的下属。
但是，这样还时不方便观察，我们可以利用填充函数，进行一个行的缩进，更加直观判断。
SQL> SELECT LPAD(ENAME,LEVEL*8,' ') FROM EMP START WITH ENAME = 'KING' CONNECT BY PRIOR EMPNO = MGR;
LPAD(ENAME,LEVEL*8,'')
-------------------------------------------------------------------------------------------
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
KING是第一个，第二梯队对齐的是level为2，第三梯队对齐的是level为3，每个下级都跟在所属的上级后面。
聚合函数
聚合函数
① SUM ： 求和
② AVG ： 平均值
③ MAX ： 最大值
④ MIN ： 最小值
⑤ COUNT ： 计数 ，空行不算入计数行
使用上面函数的时候都要注意空行的影响！！
⑥ WM_CONCAT(字段)
可以跟group by、over一起使用 , 将一个分组里的行在一行上显示，并用 ',' 分隔
⑦LISTAGG 连接
语法：
LISTAGG(字段,'连接符号') WITHIN GROUP(ORDER BY 字段)
把定义的字段一个分组内的在一行显示，可以自定义连接符号，连接的时候可以自定义某种顺序。ORDER BY 后面不一定写被连接的字段，比如人名连接，可以根据工资升降序排序。
1.正常写
SQL> SELECT LISTAGG(ENAME,'-') WITHIN GROUP(ORDER BY SAL DESC) FROM EMP;
LISTAGG(ENAME,'-')WITHINGROUP(ORDERBYSALDESC)
-------------------------------------------------------------------------------------------
KING-FORD-SCOTT-JONES-BLAKE-CLARK-ALLEN-TURNER-MILLER-MARTIN-WARD-ADAMS-JAMES-SMITH
2.有group by
分组函数再下边,这里写在一起.
SQL> SELECT
2 DEPTNO,
3 LISTAGG ( ENAME, '=' ) WITHIN GROUP ( ORDER BY SAL DESC )
4 FROM
5 EMP
6 GROUP BY
7 DEPTNO;
DEPTNO LISTAGG(ENAME,'=')WITHINGROUP(ORDERBYSALDESC)
---------------------------------------------------------------
10 KING=CLARK=MILLER
20 FORD=SCOTT=JONES=ADAMS=SMITH
30 BLAKE=ALLEN=TURNER=MARTIN=WARD=JAMES
分组函数
1.GROUP BY 分组函数
SELECT 字段1，字段2，... ,COUNT()|MAX()|MIN()|AVG()|SUM()
FROM 表
GROUP BY 字段1，字段2,....
SELECT 和 聚合函数之间，要么不写，要么写的字段和GROUP BY 后的一样。
2. HAVING 分组函数过滤
因为，计算机先走WHERE 再走 GROUP BY ，所以聚合函数不能再WHERE中进行条件筛选，这时需要用到HAVING 函数。
SELECT 字段1，字段2.... , COUNT()|AVG()|MAX()|MIN()|SUM()
FROM 表
WHERE(如果需要)
GROUP BY 字段
HAVING COUNT()|AVG()|MAX()|MIN()|SUM() 的条件表达式;
3.DISTINCT 去重函数
语法：
SELECT DISTINCT E.DEPTNO FROM EMP -- 查询EMP 表中的部门号
附加：NULL，不能参与四则运算，查询中NULL不能用等号查，要用 IS 或者 IS NOT 来判断。
总结
WHERE : 组前过滤，只能过滤普通条件，是先对表中的条件进行过滤，扔掉本来就用不到的数据，尽量减少运行内存。
HAVING : 可以对聚合函数进行过滤，必须跟在GROUP BY 后面，不能单独使用
执行顺序：
SELECT --4
FROM --1
WHERE --2
GROUP BY --3
HAVING --5 HAVING
分析函数（开窗函数）
排名类
1.ROW_NUMBER()OVER([PARTITION BY 字段...] ORDER BY 字段,... ASC | DESC)
PARTITION BY -- 确定分组字段
ORDER BY -- 确定排序字段
生成连续的序号：1，2，3，4，5，6，7....
2.RANK()OVER([PARTITION BY 字段...] ORDER BY 字段,... ASC|DESC)
遇见相同的会跳号 ： 1，2，2，4，4，6 .....
3.DENSE_RANK()OVER([PARTITION BY 字段..] ORDER BY 字段,... ASC | DESC)
遇见相同的不跳号： 1，2，2，3，3，4，4，5，5，5，6，7，8，...
4.FIRST_VALUE()OVER(PARTITION BY .. ORDER BY ..)
返回分组中的第一个值，分三种情况
不写ORDER BY ，返回默认状态的第一个值；
写ORDER BY .. ASC 返回分组中最小的值；
写ORDER BY .. DESC 返回分组中最大的值；
5.LAST_VALUE() OVER(PARTITION BY ... ORDER BY ...)
类比于上面，倒过来考虑；
聚合类
聚合类开窗，可以在每行输出计算得到的数值！！
1.COUNT(字段)OVER(PARTITION BY 字段,... ORDER BY 字段,...)
计数。
2.AVG(字段)OVER(PARTITION BY 字段,... ORDER BY 字段,...)
平均值
3.MIN(字段)OVER(PARTITION BY 字段,.. ORDER BY 字段,...)
最小值
4.MAX(字段)OVER(PARTITON BY 字段,.. ORDER BY 字段,.. )
最大值
5.SUM(字段)OVER(PARTITON BY 字段,.. ORDER BY 字段,..)
汇总函数,通过分组和排序可以完成累加的效果
也就是说，PARTITION BY 是分组, ORDER BY 是每个组里进行排序，排序最好选择带有主键约束的字段保证各不相同。否则排序累加会出现相同的值。
SQL> SELECT
2 ENAME,
3 JOB,
4 DEPTNO,
5 SAL,
6 SUM( SAL ) OVER ( PARTITION BY DEPTNO ORDER BY EMPNO ) 编号排序工资累加
7 ,
8 SUM( SAL ) OVER ( PARTITION BY DEPTNO ORDER BY JOB ) 工种排序工资累加
9 FROM
10 EMP;
ENAME JOB DEPTNO SAL 编号排序工资累加 工种排序工资累加
---------- --------- ---------- ---------- ---------------- ----------------
CLARK MANAGER 10 2450 2450 3750
KING PRESIDENT 10 5000 7450 8750
MILLER CLERK 10 1300 8750 1300
SMITH CLERK 20 800 800 7900
JONES MANAGER 20 2975 3775 10875
SCOTT ANALYST 20 3000 6775 6000
ADAMS CLERK 20 1100 7875 7900
FORD ANALYST 20 3000 10875 6000
ALLEN SALESMAN 30 1600 1600 9400
WARD SALESMAN 30 1250 2850 9400
MARTIN SALESMAN 30 1250 4100 9400
BLAKE MANAGER 30 2850 6950 3800
TURNER SALESMAN 30 1500 8450 9400
JAMES CLERK 30 950 9400 950
编号为主键,不重复,索引递增出来的工资不会有重复的;
工种一个部门中可以重复,所以工资递增时会出现相同的值;
6. 拓展
ROWS : 表示所有的行
UNBOUNDED ： 无界限
N PRECEDING : 从分区的当前行向前第N行开始
M FOLLOWING : 到分区的当前行向后第M行结束
CURRENT ROW : 分区的当前行开始或者结束
求EMP表中每个部门的所有工资汇总，在每一行上显示
SELECT E.*
,SUM(E.SAL)OVER(
PARTITION BY E.DEPTNO
ORDER BY E.SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM EMP E;
按部门分组，按工资升序排序进行向下累加求和
SELECT E.*
,SUM(E.SAL)OVER(
PARTITION BY E.DEPTNO
ORDER BY E.SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM EMP E;
偏移类
1.LAG(字段,M,N)OVER(PARTITION BY 字段 ORDER BY 字段)
向下偏移
参数M : 表示偏移的行数
参数N : 表示偏移后空出来的行用什么填充（注意类型要和该字段一致）
2.LEAD(字段,M,N)OVER(PARTITION BY 字段 ORDER BY 字段)
向上偏移
一半用来算前后的各种差，因为这是行级数据库，每次只能对同一行的数据进行操作。
注：开窗函数不能写在WHERE当中，因为不是一行。
行列转换函数
CASE WHEN 函数
第一种用法
新值会输出到 新列表 和 字段判断值 在同一行上。
CASE WHEN 字段 = 值1 AND 字段2 = 值1 THEN '新值1'
WHEN 字段 = 值2 AND 字段2 = 值2 THEN '新值2'
ELSE '新值N' --THEN 和 ELSE 后的值要类型一致
END 别名
这种写法，可以进行多条件判断，可以一个字段判断多次，可以多个字段都有条件判断
WHEN 后面可以跟表达式判断，大于小于，in is 等等，不只判断等于！！！！！！
想要将所有的部门编号用中文显示(10:十号 20 二十号 30 三十号 )
SQL> SELECT E.*,
2 CASE WHEN E.DEPTNO = 10 THEN '十号'
3 WHEN E.DEPTNO = 20 THEN '二十号'
4 WHEN E.DEPTNO = 30 THEN '三十号'
5 END DEPTNO_CN
6 FROM EMP E;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ------
7369 SMITH CLERK 7902 17-12月-80 800 20 二十号
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 三十号
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 三十号
7566 JONES MANAGER 7839 02-4月 -81 2975 20 二十号
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 三十号
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 三十号
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 十号
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 二十号
7839 KING PRESIDENT 17-11月-81 5000 10 十号
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 三十号
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 二十号
7900 JAMES CLERK 7698 03-12月-81 950 30 三十号
7902 FORD ANALYST 7566 03-12月-81 3000 20 二十号
7934 MILLER CLERK 7782 23-1月 -82 1300 10 十号
第二种用法
CASE 字段
WHEN 值1 THEN 新值1
WHEN 值2 THEN 新值2
ELSE 新值3
END 别名
-- 这种写法只能做单字段判断
嵌套
SELECT E.DEPTNO ,E.JOB
,CASE WHEN E.DEPTNO = 10 THEN -- 嵌套要在THEN 后面
(CASE
WHEN E.JOB = 'CLERK' THEN '办事员'
WHEN E.JOB = 'MANAGER' THEN '经理'
WHEN E.JOB = 'PRESIDENT' THEN '老总'
END)
WHEN E.DEPTNO = 20 THEN 'A'
WHEN E.DEPTNO = 30 THEN 'B'
END
FROM EMP E ;
DECODE 函数
Oracle中还有一个跟CASE WHEN 类似的一个语法,这个语法是Oracle独有的,但是并不表示别人不会去模仿
DECODE(字段, 值1, 新值1, 值2, 新值2,..., [默认值|原字段本身])
它只能做单值判断(也只能是等值判断),不可以进行多条件判断;值用于判断，新值用于返回
最后相当于else，之前都不匹配则返回，不写默认为空值；
值的地方都可以写字段名，即每行的字段之间比较，返回的新值也可以写字段名！！
PIVOT、UNPIVOT(sql自定义的行列转换函数)
1.PIVOT 行转列函数
它是跟在查询语句后面的，而不是写在查询中。执行时要一起选中
SQL> SELECT * FROM SCORE_1; -- 原表
STUDENT COURSE SCORE
------------ ------- ----------
王五 MATH 77
王五 CHINESE 66
李四 ENGLISH 75
李四 MATH 85
李四 CHINESE 95
张三 ENGLISH 90
张三 MATH 80
张三 CHINESE 70
王五 ENGLISH 88
-- 要求实现行转列
SQL> select * from SCORE_1
2 pivot(max(SCORE) for COURSE in('CHINESE','MATH','ENGLISH'));
STUDENT 'CHINESE' 'MATH' 'ENGLISH'
------------ ---------- ---------- ----------
王五 66 77 88
李四 95 85 75
张三 70 80 90
语句解释
前面是查询语句得到的结果集
PIVOT(
MAX(SCORE) --MAX是对每个列进行去空值操作，只保留有用值的行，这里也可以写min
FOR COURSE --for后跟将要由行转为字段名的列，相当于case when后的判断字段 IN('CHINESE','MATH','ENGLISH') --in后括号，写判断字段等于的值，当匹配时返回第一行的MAX里写的字段的值，这里写几个，最后就会产生几列。 这里相当于写了三个分组聚合下的casewhen。 )
2.UNPIVOT 列转行函数
同上，也是跟在查询语句后的，且运行时要一起选中。
原表
SQL> SELECT * FROM SCORE_2;
STUDENT CHINESE MATH ENGLISH
------------ ---------- ---------- ----------
张三 70 80 90
李四 95 85 75
王五 66 77 88
要求列转行
SQL> SELECT * FROM SCORE_2
2 UNPIVOT(SCORE FOR COURSE IN (CHINESE,MATH,ENGLISH));
STUDENT COURSE SCORE
------------ ------- ----------
张三 CHINESE 70
张三 MATH 80
张三 ENGLISH 90
李四 CHINESE 95
李四 MATH 85
李四 ENGLISH 75
王五 CHINESE 66
王五 MATH 77
王五 ENGLISH 88
解释：
前面是sql语句
UNPIVOT(
SCORE -- 列转行后值列的名字
FOR COURSE --列转行后字段名成为每一行的值，这是他们的字段名 IN(CHINESE,MATH,ENGLISH) --需要列转行的字段名 。 )
集合
交集
INTERSECT :相交 ，两个结果集所共有的，不是全部，是写在查询后面的字段。
语法: SELECT * FROM A INTERSECT SELECT * FROM B
差集
MINUS : 相减
该函数运用在两个sql语句上。返回的是第一个语句中没有在第二个语句中返回的数据 ;
SELECT DEPTNO FROM EMP MINUS SELECT DEPTNO FROM DEPT;
并集(列转行函数)
UNION : 会去重，消耗性能
UNION ALL : 不去重，把两张表的全部数据汇总在一起
并集在意的是列数相同，同一列的数据类型相同 ，内容、字段名不需要相同
SELECT DEPTNO FROM EMP UNION SELECT DEPTNO FROM DEPT;
子查询
子查询的四种形式
1.单行单列
SELECT MAX(E.SAL) FROM EMP;
2.单行多列
SELECT ENAME,EMPNO FROM EMP WHERE EMPNO=7788;
3.多行单列
SELECT ENAME FROM EMP;
4.多行多列
SELECT * FROM EMP;
子查询应用的位置
不管应用在哪里，都要带括号扩起来，起别名，这是防止出错的必要步骤
1.WHERE条件中:如果子查询和主查询有关联，则需要对每张表起别名，用于区分字段来自哪里
2.HAVING 条件中:是带有聚合函数的子查询的结果来做条件，注意同上。
3.FROM 中:sql 的结果集相当于一张虚拟的，临时的表，对其进行查询操作
称为 ：内嵌视图或者内联视图
4.SELECT 中.注意，不管有多少个字段，必须是单行！！！！
5.MYSQL等其他的数据库，子查询必须要起别名，否则查不出来。
扩展补充
1. ANY
① >ANY :返回比子查询中最小值还要大的值
② <ANY :返回比子查询中最大值还要小的值
③ =ANY : 相当于 IN ，有一个匹配就返回
2. ALL
① >ALL :返回比子查询中最大的还要大的值
② <ALL :返回比子查询中最小值还要小的值
③ <>ALL :相当于NOT IN ，即对所有结果都不匹配
3. EXISTS | NOT EXISTS 判断函数
EXISTS:不关心结果集返回数据的具体，只要子查询返回数据就为真，没有数据就是假。
NOT EXISTS相反.
WITH AS语句
也是子查询的一种，可以将逻辑代码模块化，每个逻辑单独分开来写。
方便我们之后的代码更简单明了。
语法： WITH 临时名1 AS(SQL语句：简单查询|子查询|表连接|UNION 等) , 临时名2 AS(SQL语句) --第二个临时表只写 ',表名 AS' 就行
①优点：将复杂代码运行出来的结果放入临时表，对临时表进行操作，减少后的代码行数。
②缺点：WITH AS 必须和使用它的代码放在一起选中运行才行，必须放在最前面，括号后不加分号表示没走完。
表连接
当一张表中的字段信息满足不了当前的需求时，需要从其他表中提取更多的字段信息。大部分的子查询都可以写成表连接。SQL99 ,SQL92都是标准的SQL语言，但是99比92在逻辑上更清晰。
内连接
INNER JOIN
语法：
SELECT FROM 表1 [别名1]
JOIN 表2|(SQL语句) [别名2]
ON 别名1.字段1=别名2.字段2
AND ... --ON后是写关联的条件字段，也可以写范围！
内连接是返回两个表满足连接条件的行，不满足的扔掉不显示。
外连接
1.LEFT [OUTER] JOIN 左外连接
SELECT E.* ,D.DNAME, D.LOC
FROM EMP1 E -- 主表
LEFT JOIN DEPT D --从表
ON E.DEPTNO = D.DEPTNO;
返回两个表满足连接条件的行，而且返回主表中未满足条件的行，扔掉从表中未满足条件的行。
2. RIGHT [OUTER] JOIN 右外连接
SELECT E.*,D.DNAME,D.LOC
FROM EMP E -- 从表
RIGHT JOIN DEPT D -- 主表
ON E.DEPTNO=D.DEPTNO;
返回两个表满足连接条件的行，而且返回主表中未满足条件的行，扔掉从表中未满足条件的行。
一般来说，右外连接都改成左外连接，比较方便操作和记录。
3. FULL [OUTER] JOIN 全外连接
SELECT E.* , D.*
FROM EMP E
FULL JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;-- 不分主从表
返回两个表中满足连接条件的和不满足连接条件的行，
其中不满足条件的行对应的另一个表的行是NULL值填充。
上边左右连接同理。
交叉连接
也叫 笛卡尔连接，笛卡尔积。
CROSS JOIN
SELECT E.* ,D.* FROM EMP E CROSS JOIN DEPT D; --不写ON条件。
会将两张表的所有行依次连接依次，比如两张12行的表交叉连接返回144行。
表连接不需要字段名相同，只要保证字段中的值可以进行关联判断就好。
自连接
不是一种函数，而是一张表自己连接自己。
比如，员工编号和上级经理编号在一张表中，而经理也是员工的一种，这样。就可以右自连接的前提条件了。
SQL92的写法
语法：
SELECT * | 字段
FROM 表1,表2 , 表3 ... --将需要连接的表全部写入FROM
WHERE 表1.字段1 = 表2.字段2 --将需要匹配的字段条件写在WHERE
AND 表2.字段1 = 表3.字段1
AND ... ;
-- 1.内连接 --
SELECT * FROM EMP1 E , DEPT D WHERE E.DEPTNO = D.DEPTNO ;
-- 2.外连接 --
-- (1)左连接 SELECT * FROM EMP1 E , DEPT D WHERE E.DEPTNO = D.DEPTNO(+) ;--带+号对面的是主表，带+号本身的是从表 -- (2)右连接 SELECT * FROM EMP1 E , DEPT D WHERE E.DEPTNO(+) = D.DEPTNO ;
-- 没有全连接！！ --
SELECT *
FROM EMP1 E , DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO(+) ;--报错！
-- 3.交叉连接 --
SELECT *
FROM EMP E , DEPT D ; --不用任何条件
扩展
1.自然连接（内连接）
SELECT *
FROM EMP
NATURAL JOIN DEPT ;
使用自然连接，不需要指定两张表中的关联字段，会自己去匹配关联的字段
但是，必须满足两表中有相同的 字段名！！！
2.USING 连接（内连接）
使用USING时，不需要指定关联的字段来源哪个表，只需要写上两张表中可关联的字段，字段名要相同
SELECT *
FROM EMP E
JOIN DEPT D
USING(DEPTNO);
SQL执行顺序
1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.HAVING
7.SELECT
8.DISTINCT
9.ORDER BY
区分两个式子的不同
SELECT *
FROM DEPT D
LEFT JOIN EMP E
ON D.DEPTNO =E.DEPTNO
AND D.DEPTNO = 40;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
AND跟在ON后面，判定条件为假，从表不返回数据，但是主表返回，从表的行NULL填充。
SELECT D.DEPTNO,D.DNAME,E.EMPNO
FROM DEPT D
LEFT JOIN EMP E
ON D.DEPTNO= E.DEPTNO
WHERE D.DEPTNO =40;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
40 OPERATIONS BOSTON
WHERE是强制条件是对连接完成后的结果集进行的筛选，最后只剩下连接后的，DEPT表的部门号是40 的行。
视图
定义
将代码运行的结果放入一张表中，再使用的时候直接查询那张表即可
封装：
将逻辑代码封装后发送名称给别人，其他人只要查询所给的名称就行,封装的逻辑代码的其中一种就是视图。
视图：
将一段SQL语句，进行封装，但是它不存储数据，视图所查询出来的数据完全是基于数据表中的数据.
注意:
在view中尽量不要引用function，否则会增加一定的通讯开销。简单的判断尽量用decode，nvl，case when等实现。
优点：
不占用存储空间
缺点：
如果封装了逻辑很复杂，数据表中的数据量很大，那么它返回数据的结果时间很长，直到逻辑彻底跑完后才有结果。
所以视图时候简单逻辑，数据量不是很大的，可以用于当借口对外提供。
语法
创建视图：
CREATE [OR REPLACE] VIEW 视图名称 AS
SQL 语句 --SQL语句中，如果有函数、加减 等操作，要对字段写别名，否则视图报错
[WITH READ ONLY] ; --只读约束
查看视图： SELECT * FROM 视图名;
命名规范：
字母数字下划线，数字不能开头 ，V_XXX , XX_XXX等。
视图可以进行DML操作，修改数据和内容，当然只读不行；
序列
创建语法：
CREATE SEQUENCE 序列名
START WITH 起始值 --不能小于最小值
INCREMENT BY 步长 --一次加几个数。可以只写到步长，则默认开始到引用结束 ，多用作序列号
MINVALUE 最小值
MAXVALUE 最大值
CYCLE | NOCYCLE --循环 | 不循环（不写默认不循环）
CACHE [N] --先缓存出来几个数，为了加快进程，不大的话没啥用。
循环的时候，缓存的个数要大于步长。
使用语法：
DROP SEQUENCE 序列名; -- 删除序列
SELECT 序列名.NEXTVAL FROM DUAL; -- 调用序列
SELECT 序列名.CURRVAL FROM DUAL; -- 查看序列到那个值了
表空间
表空间
1.定义
安装数据库后，数据文件存放在表空间中，数据保存在文件中。
ORACLE的架构：
物理架构： 日志文件LOG, 数据文件DBF, 控制文件CTL
逻辑架构：表空间， 段 ，区， 块。
2.创建表空间
语法：
----创建表空间并定义路径
create tablespace AAA --表空间名
datafile 'D:/app/Administrator/oradata/nacos/nacos.dbf' --先建文件夹才能写路径。
size 500m --大小初始值
autoextend on --自动扩展
next 50m maxsize 20480m --每次扩展50m，最大为20480m
extent management local uniform size 256K;
分区表
单表数据2G 以后，需要进行分区 ，可以提高查询效率。
只能在建表的时候创建分区，不能对一个已经存在并且未分区的表进行分区。
1.RANGE 范围分区
创建range分区表
CREATE TABLE 表名(
字段1 类型1
, 字段2 类型2, ...
)PARTITION BY RANGE(被分区的字段)( --在字段写完的括号后写分区
PARTITION 分区名1 VALUES LESS THAN(10) [TABLESPACE 表空间名1],
PARTITION 分区名2 VALUES LESS THAN(20) [TABLESPACE 表空间名2],
PARTITION 分区名3 VALUES LESS THAN(30) [TABLESPACE 表空间名3],
PARTITION 分区名4 VALUES LESS THAN(MAXVALUE) [TABLESPACE 表空间名4]，
);
① 表空间可以不指定，此时分区默认在USERS空间下；分区不一定放在不同的表空间下，但前提是这个表空间存在
② RANGE分区是连续的，但是数据不需要连续
③ MAXVALUE：大于上一个范围的数据全部存入此分区，包括空值。
通过数据字典查询分区表
SELECT TABLE_NAME FROM USER_TABLES WHERE PARTITIONED = 'YES'; -- 查看所有的分区表
SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='TEST3';--查看一张表是否为分区表
添加range分区
ALTER TABLE 表名 ADD PARTITION 分区名 VALUES LESS THAN(值);
添加的分区条件必须大于现有分区的值，如果range采用的maxvalue则需要的是切分最大的分区表，而不是添加分区操作
删除range分区
ALTER TABLE 表名 DROP PARTITION 分区名;
如果删除的分区是表中唯一的分区，则此时分区不能被删除；需要执行删除表操作。
查询分区表
SELECT * FROM 表名; --查询全部数据
SELECT * FROM 表名PARTITION(分区名); --查询分区数据
截断分区
截断某个分区是指清空某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。截断分区会使全局索引无效，需要重建
注意：如果截断的分区表有约束，需要先关闭约束。
alter table 表名 disable/enable constraint 约束名 ; --关闭/打开表中指定的约束
ALTER TABLE 表名 TRUNCATE PARTITION 分区名; --这样会使全局分区的索引失效；
ALTER TABLE 表名 TRUNCATE PARTITION 分区名 UPDATE INDEXES; --这种方式使全局分区索引有效UPDATE GLOBAL INDEXES
合并分区
将相邻的分区合并为一个，采用较高分区为界限；不能将分区合并到界限较低的分区！
ALTER TABLE 表名 MERGE PARTITION 分区名1，分区名2 INTO 分区名2;-- 分区2的界限要大于分区1
拆分分区
将一个分区拆分为两个新分区，拆分后原来的分区就不存在了。HASH分区不可拆分。
--比如想把2022-01-31日期的分区再拆的细一点
ALTER TABLE 表名
SBLIT PARTITION 分区名
AT(TO_DATE('2022-01-15','YYYY-MM-DD')) INTO (PARTITION P1,PARTITION P2);
--小的分区给前面，大的给后面
--P1 是 2022-01-15 到上一个分区最大值; P2 是 2022-01-30 到 2022-01-15;
移动分区
把分区移动到另一个表空间，移动后要重建索引
ALTER TABLE 表名 MOVE PARTITION 分区名 TABLESPACE 表空间名; -- 移动分区
ALTER INDEX 需要重建的索引名 REBUILD; -- 重建索引
查询是否移动成功。
SELECT TABLE_OWNER
,TABLE_NAME
,PARTITION_NAME
,TABLESPACE_NAME
,SUBPARTITION_COUNT
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER='SCOTT';
DBA_TAB_PARTITIONS -- 对分区表的各种查询
删除表空间
DROP TABLESPACE 表空间名 [INCLUDING CONTENTS];
--如果一个表中只有最后一个分区则无法删除，此时还想继续删除则添加 INCLUDING CONTENTS
2.LIST 列表分区
创建list分区：
CREATE TABLE 表名 ( 字段1 类型1, 字段2 类型2 )
PARTITION BY LIST(分区字段)(
PARTITION 分区1 VALUES(10,20) [TABLESPACE 表空间名1],
PARTITION 分区2 VALUES(30,40) [TABLESPACE 表空间名2],
PARTITION 分区3 VALUES(60,70) [TABLESPACE 表空间名3]
);
list分区之间可以断开; 添加，删除同上，改改类型就行。
3.HASH 哈希分区
创建hash分区：
CREATE TABLE 表名 (
字段1 类型1
, 字段2 类型2
)PARTITION BY HASH(分区字段)(
PARTITION 分区1 [TABLESPACE 表空间名1], --不需要写范围，自动给分区。
PARTITION 分区2 [TABLESPACE 表空间名2],
PARTITION 分区3 [TABLESPACE 表空间名3]
);
一次性创建多个分区：
CREATE TABLE TB_NAME
PARTITION BY HASH（COL_NAME）
PARTITION NUM --数量
AS SQL 语句; --创建hash分区表的时候最好来个不相关(1=2)条件，只取表头不取数据；
注意点:
hash分区是利用oracle中的hash算法，相同的值会放在同一个分区；
hash只支持等值查询或者IN查询；
列基数很高的情况下使用hash；列基数很低的时候用list分区；
分区表的优点
增强可用性，减少修复时间：如果一个表的分区由于系统故障不能使用，表的其余好的分区仍可以使用
维护轻松：如果需要建表的话，独产管理每个分区比管理单个表要轻松很多
均衡I/O:把表的不同分区划分到不同的磁盘来平衡I/O的性能
改善性能：对大表的查询、增加、修改操作可以分解到不同的分区来执行，使运行的速度更快，对数仓的TP查询特别有用
行移动
关键词: row movement;
开启行移动：
alter table 表名 enable row movement;
作用：分区表时，分区字段的更改会导致这条数据移动到其他分区，此时需要开启这张分区表的行移动功能； 闪回也用得到；
数据类型
一.数值类型
number
在Oracle中取消了在mysql中的int类型，使用number代替，如果你在创建数据库表的时候使用了int类型会自动转换成number类型，并且Oracle没有这个”auto_increment”属性，所以它没法像MySQL般在表内定义自增主键。
但是，Oracle里的序列（SEQUENCE），可间接实现自增主键的作用；
number有两个参数：number（m,n）:m用来表示有几位有效数字最大是38位，n表示小数点以后可以有几位，多出来的位数要四舍五入。
二：字符类型
char
用于存储固定长度的字符串，一个空间占用1个字节，最多存储2000个字节，并且在存数据的时候，给的数据占不满给定的空间也会自动用空格补满空间，如果长度超出了就会报错；
varchar2
是Oracle特有的数据类型，Oracle中不提倡使用varchar； varchar2用于存储可变长度的字符串。最大长度是char的两倍，也就是4000个字节，在存数据的时候，给的数据占不满给定的空间会自动截断，省空间，如果长度超出了会补长，不会报错；并且它将原本varchar可以存储空字符串的特性转换成了可以存储null值，提供了向后兼容的能力；
但是为什么不直接全部使用varchar2呢？
1.VARCHAR2虽然比CHAR节省空间，但是假如一个VARCHAR2列经常被修改，而且每次被修改的数据的长度不同，这会引起‘行迁移’(RowMigration)现象，而这会造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用CHAR代替VARCHAR2会更好一些；
2.还有就是当我们存储已知固定长度的数据时，比如：手机号（11位）、身份证号码（18位）等，可以考虑使用 char。因为，在查询数据时，对于 char 类型字段，是全字符整体匹配；而 varchar2 是一个字符一个字符的进行匹配；
3.并且char的效率要比varchar2高一些，为了提高效率就必须牺牲一些空间。
nchar
用于存储固定长度的字符串，一个空间占用2个字节，最多存储2000个字节，并且在nchar中存储字符，数字和文字都是占用两个字节；
nvarchar2
用于存储可变长度的字符串，一个空间占用2个字节，最大长度是 NCHAR 的两倍，也就是4000个字节。
三：日期类型
Oracle日期类型主要使用 date 和 timestamp 数据类型。
date
用于存储日期和时间类型，date 类型的默认格式为：DD-MM-YYYY。
timestamp
时间戳类型，与 date相比较，timestamp类型具有小数位毫秒数，比 DATE 的精度更高。
四：大数据类型与二进制类型
long
用于存储可变长度的超长字符串，最大长度为2G，通常用于存储备注字段，或者varchar2 和 nvarchar2 不够存储时。
lob
clob ：是一种字符型大型对象（Character Large Object），最大长度为4G，存储与字符集相关。
nclob：根据字符集而定的字符数据，最大长度为4G。
blob：是一种二进制大型对象（Binary Large Object），最大长度为4G，适用于存储非文本的字节流数据，如：视频、音频等。
raw：固定长度的二进制数据，最大长度 2000个字节，可存储多媒体视频、音频格式等。
long raw：可变长度的二进制数据，最大长度2G，与 RAW 类似。
bfile：存储在数据库之外的二进制数据，最大长度4G。
rowid
行地址，十六进制串，表示行在所在的表中唯一的行地址，该数据类型主要用于返回ROWID伪列，常用在可以将表中的每一条记录都加以唯一标识的场合。数据表中记录的唯一行号，占10个字节。
nrowid
二进制数据表中记录的唯一行号，最大长度4000个字节。
