一、基本查询(DQL)
1、查询结构
（5）SELECT * |列名|表达式 -- 表示表的所有列名称;多个列或者多个表达式用逗号分隔
（1）FROM 表名|结果集 -- 查看的对象为表或者结果集
（2）WHERE 条件 -- 多个条件用and/or/not 连接
（3）GROUP BY 列名 -- 多个列用逗号分隔，分组
（4）HAVING 条件 -- 一般为聚合函数表达式,分组后过滤，前面要有GROUP BY,多个表达式 AND/OR/NOT 连接
（6）ORDER BY 列名 [ ASC / DESC ] -- ASC 升序(默认),DESC 降序
2、条件查询
- 运算符
> 、>= 、< 、<= 、 = 、<=> 、 != 、<>
- 逻辑运算符
AND/OR/NOT
- 模糊运算符
- LIKE ：字符匹配操作可以使用通配符
% 和_ ，% 代表零个或多个字符，_ 代表任意一个字符 - BETWEEN AND：查询包含在指定区间内（包含边界）的 值
- NOT BETWEEN AND:查询不在范围内的数据
- IN/NOT IN/NOT EXISTS:如果查询语句使用了NOT IN，那么对内外表都进行全表扫描，没有用到索引；而NOT EXISTS的子查询依然能用到表上的索引。所以无论哪个表大，用NOT EXISTS都比NOT IN要快
--查询名字中包含'A'
SELECT * FROM emp
WHERE ENAME LIKE '%A%';
--按时间查询
SELECT * FROM emp
WHERE HIREDATE BETWEEN DATE '1980-01-01' AND DATE'1982-01-01';
3、子查询
- 单行子查询：不向外部返回结果,或者只返回一行结果
SELECT * FROM emp
WHERE SAL >(SELECT SAL FROM EMP WHERE ENAME = 'FORD')
- 多行子查询：向外部返回零行、一行或者多行结果
SELECT * FROM emp
WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO)
4、多表查询 (结果集增加列)
- 内连接
- 隐式内连接接
SELECT * FROM emp E,DEPT D WHERE E.DEPTNO = D.DEPTNO;
- 显式内连接
SELECT * FROM emp E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
- 左外连接
- 隐式左外连接
SELECT * FROM emp E,DEPT D WHERE E.DEPTNO = D.DEPTNO(+);
- 显式左外连接
SELECT * FROM emp E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
- 右外连接
- 隐式左外连接
SELECT * FROM emp E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO;
- 显式左外连接
SELECT * FROM emp E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
- 交叉连接（产生笛卡尔积）
- 隐式交叉连接
SELECT * FROM emp,DEPT
- 显式交叉连接
SELECT * FROM emp E CROSS JOIN DEPT D
- 全外连接
SELECT * FROM emp E FULL JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
5、集合运算 (结果集增加行)
<!--查询所返回的列数以及列的类型必须匹配,列名可以不同，只有UNION ALL不会去重。其他三个都需要排序后去重,性能比较差-->
- 并集运算：将两个查询结果进行合并
- UNION（去重，排序）
SELECT * FROM emp WHERE DEPTNO = 10
UNION
SELECT * FROM emp WHERE SAL <2000;
- UNION ALL（不去重，不排序）
SELECT * FROM emp WHERE DEPTNO = 10
UNION ALL
SELECT * FROM emp WHERE SAL <2000;
- 交集运算（两者共有部分）
- INTERSECT
SELECT * FROM emp WHERE SAL>1500
INTERSECT
SELECT * FROM emp WHERE DEPTNO =20;
- 差集运算（前者有后者无)
- MINSU
SELECT * FROM emp WHERE DEPTNO =20
MINUS
SELECT * FROM emp WHERE SAL>1500
6、伪列
- ROWID
- 表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址
- 使用ROWID可以快速的定位表中的某一行
- ROWID值可以唯一的标识表中的一行
-- 去重，删除重复数据
DELETE FROM EMP E WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP E GROUP BY EMPNO);
- ROWNUM
- 在查询的结果集，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推
- 通过ROWNUM伪列可以限制查询结果集中返回的行数,可以实现分页查询
- rownum 必须从1开始查询
--查询emp表第3-7列数据
SELECT ENAME,SAL,DEPTNO,RN
FROM
(SELECT E.*,ROWNUM RN FROM emp E WHERE ROWNUM<8)
WHERE RN >2;
二、数据定义（DDL）
<!--数据定义语言（DDL），包括CREATE（创建）命令、ALTER（修改）命令、DROP（删除对象）、TRUNCATE（删除表数据）命令等。-->
1、数据类型
常见类型 | 示例 |
CHAR(length) | 存储固定长度的字符串。参数length指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节 |
VARCHAR2(length) | 存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符 |
NUMBER(p，s) | 存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。p是总长度，s是小数，整数部分长度是p-s。 |
DATE | 存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日 |
NULL | 在查询条件中NULL值用IS NULL作条件,非NULL值用 IS NOT NULL做条件 <br/>空值跟任何值进行算术运算,得到的结果都为空值 <br/>空值跟任何值进行关系运算,得到的结果都为不成立 <br/>空值不参与任何聚合运算 排序的时候,空值永远是最大的 |
2、CREATE（创建）命令
-- 语法格式
create table 表名 （列名1 数据类型， 列名2 数据类型，...);
-- 加注释
comment on table 表名 is '表注释名'；
comment on table 表名.列名1 is '字段注释名';
-- 创建一张学生表
create table student_info --学生信息表
(id number(4) primary key --主键
,sno number(4) --学号
,sname varchar2(20) not null --姓名
,sex varchar2(2) check(sex = '男'or sex = '女') --性别
,graduated_year number(4) --毕业年龄
,major varchar2(40) --专业
,province varchar2(20) --省份
,dnum number constraint pk_1 references emp(empno) --外键
);
3、ALTER 更改表结构
<!--修改的列没有数据时,可以随便更改类型；修改的列有数据时,只能在同一种类型上增加长度-->
-- 语法格式
-- 修改列数据类型
alter table 表名 modify 列名 新的数据类型;
alter table student_info modify sno varchar2(20);
-- 修改表名
alter table 表名 rename to 新表名;
alter table sutdent_info to student_info_new;
-- 修改列名
alter table 表名 rename column 旧列名 to 新列名;
alter table student_info rename column sno to snumber;
-- 增加列
alter table 表名 add 列名 数据类型;
alter table student_info add age number(3);
-- 删除列
alter table 表名 drop column 列名;
alter table student_info drop column sex;
4、DROP 删除表对象
-- 语法格式
drop table 表名;
drop table student_info;
5、TRUNCATE(对象是表)删除表数据
<!--清空整张表数据, 不会将清除的数据写入日志,不能跟where-->
-- 语法格式
truncate table 表名;
truncate table student_info;
6、表的约束
<!--表的约束是Oracle数据库中应用在表数据上的一系列强制性规则。当向已创建的表中插入数据或修改表中的数据时，必须满足表的完整性约束所规定的条件。例如，学生的性别必须是“男”或“女”，各个学生的学号不得相同等。在设计表的结构是，应该充分考虑在表上需要施加的完整性约束。表的完整性约束既可以在创建表时制定，也可以在表创建之后再指定。可以对一个或多个字段进行约束。-->
分类
- PRIMARY KEY：主键约束
- FOREIGN KEY：外键约束
- CHECK：检查约束
- UNIQUE：唯一约束
- NOT NULL：非空约束
创建命令
<!--一张表只有一个主键，主键非空且唯一-->
<!--约束不能修改，只能删除重建-->
--添加主键约束
alter table 主表名 add constraint 约束名 primary key(列名1[,列名2...])
alter table student_info add constraint p_sno primary key(sno);
--添加外键约束
alter table 主表名 add constraint 约束名 foreign key(列名1[,列名2...]) references 从表名(列名1[,列名2...])
--添加CHECK约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(条件)
--添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)
--添加非空约束
ALTER TABLE 表名 MODIFY 列名 NOT NULL
7、创建分区表
create table students_info(
id number(4) primary key,
name varchar2(30),
dnum number,
datatime date )
partition by range(datatime)
(partition part1 values less than(to_date('1999-4-1','yyyy/mm/dd')),
partition part2 values less than(to_date('1999-4-2','yyyy/mm/dd')),
partition part3 values less than(to_date('2000-4-2','yyyy-mm-dd'))
)
--添加分区
alter table students_info add partition part4 values less than(to_date('2000-4-3','yyyy/mm/dd'));
--查询分区数据
select * from students_info partition(part4);
8、创建自增序列
<!--因为是首次查询序列的当前值，内存中之前并没有缓存序列的任何值，所以需要先查询 一下序列的下一个值（此时，Oracle会自动缓存已查询的序列值），再查询序列的当前值。-->
create sequence SEQ_ID
minvalue 1 ---最小者=值
maxvalue 99999999 ---最大值
start with 1 -- 从1开始计数
increment by 1 -- 每次加几个
nocache -- cache/nocache：nocache不缓存。cache缓存。开启缓存，效率高
;
select SEQ_ID.currval from dual;----查看序列当前值
select SEQ_ID.nextval from dual;--查看序列当前下个值，每次运行语句都会增加
9、索引
- 类型：唯一索引（主键、唯一）、组合索引、基于函数的索引、反向键索引、位图索引
- 创建索引
--语法结构
CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])
- 删除索引
--语法结构
DROP INDEX 索引名
- 索引失效场景
- 没有查询条件，或者查询条件没有建立索引
- 隐式转换
- 查询条件中有or
- 对索引列进行运算、使用内部函数
- like查询是以%开头
- not in、not exists
- 优点：加快检索速度；减少分组合排序的时间；唯一性；
- 缺点：占用空间；占用时间；
三、数据操纵（DML）
*数据操纵语言（DML），包括INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）命令、SELECT(查找）命令等。对数据表进行DML操作后,一定要主要对数据进行提交（commit）或者回滚（rollback）*
1.INSERT INTO 插入数据到表中
--语法格式
INSERT INTO 表名(列名1,列名2,……) VALUES (值1,值2……)
INSERT INTO student_2(ename,age,birthday) VALUES ('张三',20,date'2000-12-01');
INSERT INTO student_2(ename,age,birthday) VALUES ('李四',22,date'1998-12-01');
2.UPDATE 更新表数据
--语法格式
UPDATE 表名 SET 列名1=值, 列名2=值,…… WHERE 条件
3.DELETE 删除表数据
--语法格式
DELETE FROM 表名 WHERE 条件; 删除的数据可以从日志中找回
四、函数
1、字符函数
表达式 | 示例 |
CONCAT(x,y) | 连接字符串x和y |
LENGTH(x) | 返回x的长度 |
LOWER(x) | x转换为小写 |
UPPER(x) | x转换为大写 |
TRIM(x) | 去掉字符串两端的多余的空格 |
REPLACE(x,old,new) | 在x中查找old，并替换为new |
SUBSTR(x,start[,length]) | 返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾 |
2、数字函数
表达式 | 示例 |
ABS(x) | x绝对值<br/>ABS(-3)=3 |
MOD(x,y) | x除以y的余数<br/>MOD(8,3)=2 |
POWER(x,y) | x的y次幂<br/>POWER(2,3)=8 |
ROUND(x[,y]) | x在第y位四舍五入<br/>ROUND(3.456,2)=3.46 |
TRUNC(x[,y]) | x在第y位截断，直接截取，不四舍五入<br/>TRUNC(3.456,2)=3.45 |
3、日期函数
表达式 | 示例 |
add_months(d,n) | 在某一个日期d上，加上指定的月数n，返。d表示日期，n表示要加的月数<br/>select sysdate,add_months(sysdate,3) from dual |
last_day(d) | 返回指定日期当月的最后一天<br/>select sysdate,last_day(sysdate) from dual; |
round(d,'fmt') | 返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。默认fmt为DDD，即月中的某一天 |
trunc(d,'fmt') | 对日期的操作，TRUNC与ROUND非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天 |
--例
select sysdate, --22/4/25 12:14:34
round(sysdate), --22/4/26
round(sysdate,'day'), --22/4/24
round(sysdate,'month'), --22/5/1
round(sysdate,'year') --22/1/1
from dual;
4、转换函数
- 转换为字符串：to_char
SELECT TO_CHAR(SYSDATE,'YYYYMMDD')
FROM dual;
- 转换为日期：to_date
SELECT TO_DATE('20170731123245','YYYYMMDD HH24:MI:SS')
FROM dual;
- 转换为数字：to_number
SELECT TO_NUMBER('123456')
FROM dual;5、其他常用函数
- nvl(x,value) --如果x为空，返回value，否则返回x。
SELECT ENAME,COMM,NVL(COMM,0)+100
FROM emp
WHERE SAL<2000;
- nvl2(x,value1,value2) --如果x非空，返回value1，否则返回value2
SELECT ENAME,NVL2(COMM,COMM+100,200)
FROM emp;
- decode(列|表达式，值1，value1，值2，value2,...,默认值)
<!--当参数的值为判断值1，则返回value1……当参数的值匹配不到时，则返回默认值-->
--例
SELECT E.ENAME,
DECODE(E.JOB,
'CLERK','业务员',
'SALESMAN','销售员',
'MANAGER','经理',
'ANALYST','分析员',
'PRESIDENT','总裁') FROM emp E
- case when end
<!--适用于多条件判断-->
-- 第一种语法
CASE 参数
WHEN 判断值1 THEN 返回值1
WHEN 判断值2 THEN 返回值2
……
ELSE 默认值 END
--例
Select E.Ename,
case e.job
WHEN 'CLERK' THEN'业务员'
WHEN 'SALESMAN' THEN'销售员'
WHEN 'MANAGER' THEN'经理'
WHEN 'ANALYST' THEN'分析员'
WHEN 'PRESIDENT' THEN'总裁'
END
FROM EMP E
--第二种语法
CASE
WHEN 条件1 THEN 返回值1
WHEN 条件2 THEN 返回值2
……
ELSE 默认值 END
--例
select e.*,
case
when sal>3000 then '工资很高'
when sal>1000 then '工资一般'
else '工资很低'
end
from emp e
- 行列转换
--创建表 emp014
create table emp014(id number,q number,amt number)
id q AMT
01 1 100
01 4 100
02 1 200
01 2 110
02 2 150
02 3 100
01 3 130
02 4 200
--创建表 emp015
create table emp015(y number,q1 number,q2 number,q3 number,q4 number)
insert into emp015( Y,Q1,Q2, Q3, Q4)values(01,100,110,130,100);
insert into emp015( Y,Q1,Q2, Q3, Q4)values(02 ,200,150,100,200);
--emp014 转 emp015
with aa as
(select id y,
max(case when q =1 then amt end) q1,
max(case when q =2 then amt end) q2,
max(case when q =3 then amt end) q3,
max(case when q =4 then amt end) q4
from emp014
group by id)
select * from aa
--emp015 转 emp014
with aa as
(select id,
max(case when q=1 then amt end) as q1,
max(case when q=2 then amt end) as q2,
max(case when q=3 then amt end) as q3,
max(case when q=4 then amt end) as q4
from emp014
group by id )
select id , 1 as q1,q1 as amt
from aa union all
select id , 2 as q1,q2 as amt
from aa union all
select id , 3 as q1,q3 as amt
from aa union all
select id , 4 as q1,q4 as amt
from aa
6、分析函数
- 分析函数与聚合函数不同，聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by 分组，并且每组每行都可以返回一个统计值
- 函数()OVER(PARTITION BY 字段 ORDER BY 字段 [ASE/DESC])
--求每个部门工资高于部门平均工资的员工数量
SELECT DEPTNO,COUNT(*)
FROM (SELECT E.*,
AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AVG
FROM EMP E) A
WHERE SAL>AVG
GROUP BY DEPTNO;
- MAX(),MIN(),SUM(),AVG(),COUNT() --加了ORDER BY 是累计求值
- 排序分析函数 100 90 90 80 70
- ROW_NUMBER() --派号码排序， 1 2 3 4 5
- RANK() --跳跃排序派号码 1 2 2 4 5
- DENSE_RANK() --不跳跃排序派号码 1 2 2 3 4
-- 各部门工资排名前3的员工的所有信息
SELECT *
FROM (SELECT E.*,
DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN
FROM emp E)
WHERE RN<4
- 聚类分析函数
- MAX(column_name)OVER()
- MIN(column_name)OVER()
- SUM(column_name)OVER()
- AVG(column_name)OVER()
- COUNT(column_name)OVER()
--加了ORDER BY 是累计求值
--例
SELECT T.*,
SUM(SAL)OVER() D_SUM_SAL,
SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY EMPNO DESC) SUM_SAL
FROM emp T;
- 最大值、最小值分析函数
--语法格式
MAX()/MIN() KEEP (DENSE_RANK FIRST/LAST ORDER BY )
--查询工资最大、最小员工
SELECT
MAX(EMPNO)KEEP(DENSE_RANK FIRST ORDER BY SAL) MIN_SAL,
MAX(EMPNO)KEEP(DENSE_RANK LAST ORDER BY SAL) MAX_SAL
FROM emp T;
- 按层次查询
- 平均分配，如果不能平均分配，则较小层次分配额外的行，并且各个层次能放的行数最多相差1
SELECT T.*,NTILE(5)OVER(ORDER BY SAL DESC) N FROM emp T
- 位移分析函数
--LAG(取值字段,N)OVER(ORDER BY 排序字段) --N 表示步长，表示取前 N 个的结果
--LEAD(取值字段,N)OVER(ORDER BY 排序字段) --N 表示步长，表示取后 N 个的结果
--例1
SELECT T.*,
LAG(SAL,1,0)OVER(ORDER BY SAL) A,
LEAD(SAL,2,1)OVER(ORDER BY SAL) B,
FROM emp T;
--例2
SELECT T.*,
LAG(SAL,1)OVER(PARTITION BY DEPTNO ORDER BY SAL) C,
LEAD(SAL,1)OVER(PARTITION BY DEPTNO ORDER BY SAL) D
FROM emp T;
