Oracle基础和进阶笔记第一篇

环境和数据准备

一、开发环境准备

学习Oracle需要有Oracle的环境，这里先不讲Oracle数据库的安装和配置，只是简单介绍Oracle的一些日常使用，这里推荐一个免安装的Oracle环境，基本应付平时学习足够，同时也是Oracle官方提供的一个学习和交流平台，非常的简单易用。
oracle官方学习平台 ：https://livesql.oracle.com/
选择右上角的Sign In 登录即可：

如果没有账号，用邮箱创建一个即可：

带*号的都是必填，但是随便填一些东西即可。

二、数据准备

create table student(
	stuid number primary key,
	name varchar(20),
	birthday date,
	sex varchar(20),
	cid number
);

insert into student(stuid,name,birthday,sex,cid) values(1,'小一',to_date('2021-01-1','yyyy-mm-dd'),'男',1);
insert into student(stuid,name,birthday,sex,cid) values(2,'小二',to_date('2021-01-2','yyyy-mm-dd'),'女',1);
insert into student(stuid,name,birthday,sex,cid) values(3,'小三',to_date('2021-01-3','yyyy-mm-dd'),'男',1);
insert into student(stuid,name,birthday,sex,cid) values(4,'小四',to_date('2021-01-4','yyyy-mm-dd'),'女',2);
insert into student(stuid,name,birthday,sex,cid) values(5,'小五',to_date('2021-01-5','yyyy-mm-dd'),'男',2);
insert into student(stuid,name,birthday,sex,cid) values(6,'小六',to_date('2021-01-6','yyyy-mm-dd'),'女',2);
insert into student(stuid,name,birthday,sex,cid) values(7,'小七',to_date('2021-01-7','yyyy-mm-dd'),'男',3);
insert into student(stuid,name,birthday,sex,cid) values(8,'小八',to_date('2021-01-8','yyyy-mm-dd'),'女',3);
insert into student(stuid,name,birthday,sex,cid) values(9,'小九',to_date('2021-01-9','yyyy-mm-dd'),'男',3);
insert into student(stuid,name,birthday,sex,cid) values(10,'小十',to_date('2021-01-10','yyyy-mm-dd'),'女',4);
insert into student(stuid,name,birthday,sex,cid) values(11,'小十一','','男',4);
insert into student(stuid,name,birthday,sex,cid) values(12,'小十二',to_date('2021-01-12','yyyy-mm-dd'),'',4);
insert into student(stuid,name,birthday,sex,cid) values(13,'小十三',to_date('2021-01-13','yyyy-mm-dd'),'女','');
insert into student(stuid,name,birthday,sex,cid) values(14,'小十四','','','');
insert into student(stuid,name,birthday,sex,cid) values(15,'','','','');
commit;

create table classroom(
	cid number primary key,
	classname varchar(20),
	grade varchar(20)
);

insert into classroom(cid,classname,grade) values(1,'一班','一年级');
insert into classroom(cid,classname,grade) values(2,'一班','二年级');
insert into classroom(cid,classname,grade) values(3,'二班','一年级');
insert into classroom(cid,classname,grade) values(4,'二班','二年级');
insert into classroom(cid,classname,grade) values(5,'三班','');
insert into classroom(cid,classname,grade) values(6,'','');
commit;

create table score(
	scoid number,
	subject varchar(20),
	score number,
	examinationtime date,
	stuid number
);

insert into score(scoid,subject,score,examinationtime,stuid) values(1,'数学',89,to_date('2021-09-12','yyyy-mm-dd'),1);
insert into score(scoid,subject,score,examinationtime,stuid) values(2,'语文',60,to_date('2021-09-12','yyyy-mm-dd'),1);
insert into score(scoid,subject,score,examinationtime,stuid) values(3,'英语',86,to_date('2021-09-12','yyyy-mm-dd'),1);
insert into score(scoid,subject,score,examinationtime,stuid) values(4,'数学',98,to_date('2021-09-12','yyyy-mm-dd'),2);
insert into score(scoid,subject,score,examinationtime,stuid) values(5,'语文',81,to_date('2021-09-12','yyyy-mm-dd'),2);
insert into score(scoid,subject,score,examinationtime,stuid) values(6,'英语',87,to_date('2021-09-12','yyyy-mm-dd'),2);
insert into score(scoid,subject,score,examinationtime,stuid) values(7,'数学',89,to_date('2021-09-12','yyyy-mm-dd'),3);
insert into score(scoid,subject,score,examinationtime,stuid) values(8,'语文',92,to_date('2021-09-12','yyyy-mm-dd'),3);
insert into score(scoid,subject,score,examinationtime,stuid) values(9,'英语',80,to_date('2021-09-12','yyyy-mm-dd'),3);
insert into score(scoid,subject,score,examinationtime,stuid) values(10,'数学',80,to_date('2021-09-12','yyyy-mm-dd'),4);
insert into score(scoid,subject,score,examinationtime,stuid) values(11,'语文',93,to_date('2021-09-13','yyyy-mm-dd'),4);
insert into score(scoid,subject,score,examinationtime,stuid) values(12,'英语',83,to_date('2021-09-13','yyyy-mm-dd'),4);
insert into score(scoid,subject,score,examinationtime,stuid) values(13,'数学',87,to_date('2021-09-13','yyyy-mm-dd'),5);
insert into score(scoid,subject,score,examinationtime,stuid) values(14,'语文',97,to_date('2021-09-13','yyyy-mm-dd'),5);
insert into score(scoid,subject,score,examinationtime,stuid) values(15,'英语',82,to_date('2021-09-13','yyyy-mm-dd'),5);
insert into score(scoid,subject,score,examinationtime,stuid) values(16,'数学',90,to_date('2021-09-13','yyyy-mm-dd'),6);
insert into score(scoid,subject,score,examinationtime,stuid) values(17,'语文',93,to_date('2021-09-13','yyyy-mm-dd'),6);
insert into score(scoid,subject,score,examinationtime,stuid) values(18,'英语',100,to_date('2021-09-13','yyyy-mm-dd'),6);
insert into score(scoid,subject,score,examinationtime,stuid) values(19,'',93,to_date('2021-09-13','yyyy-mm-dd'),7);
commit;

create table student_old as select * from student where 1=2;
insert into student_old(stuid,name,birthday,sex,cid) values(1,'小甲',to_date('2009-01-01','yyyy-mm-dd'),'男',1);
insert into student_old(stuid,name,birthday,sex,cid) values(2,'小乙',to_date('2009-01-02','yyyy-mm-dd'),'女',1);
insert into student_old(stuid,name,birthday,sex,cid) values(3,'小丙',to_date('2009-01-03','yyyy-mm-dd'),'男',1);
insert into student_old(stuid,name,birthday,sex,cid) values(4,'小丁',to_date('2009-01-04','yyyy-mm-dd'),'女',2);
insert into student_old(stuid,name,birthday,sex,cid) values(5,'小五',to_date('2021-01-05','yyyy-mm-dd'),'男',2);
insert into student_old(stuid,name,birthday,sex,cid) values(6,'小六',to_date('2021-01-06','yyyy-mm-dd'),'女',2);
insert into student_old(stuid,name,birthday,sex,cid) values(7,'小七',to_date('2021-01-07','yyyy-mm-dd'),'男',3);

进入Live SQL开发环境之后，将以上创建表和插入数据的代码先复制进SQL worksheet控制台，然后再点击右上角的Run执行。

执行完成后，再点击右上角的Save按钮，将创建脚本保存为My Scripts，可以将脚本命名为create table student etc，还能填写脚本是否私有还是公开，还有脚本的描述信息等。

此处保存脚本是因为这是线上开发环境，每次打开都会清空里面的内容，当然也包括刚开始创建的表格，但下面的所有查询都是基于上面的表来做演示的，所以当下次在其他地方打开的时候，只需执行一遍脚本即可。
另外的好处就是可以随时保存自己学习的过程中自己编写的SQL语句，以便下次学习。

一、基础查询

1、基础语法

select [top|distinct] [选择列表]|[*]
from 表
[where 查询条件]
[group by 分组条件]
[having 过滤条件]
[order by 排序条件 asc 升序|desc 降序];

执行的顺序如下：

（5）select [（6-2）top|（5-2）distinct] （5-1）[选择列表]|[*]
（1）from 表
（2）[where 查询条件]
（3）[group by 分组条件]
（4）[having 过滤条件]
（6）[order by asc 升序|desc 降序];

首先先从表也就是数据源里面取数，再根据查询条件过滤表里面的数据，再按照分组的条件进行分组，分组后再根据分组的条件having子句再次过滤数据，之后再根据选择的列表或者叫字段选择相应的字段，再进行DISTINCT去重操作，再根据排序的字段进行升序或者降序排序，最后再根据TOP显示前面的多少行数据。
这个也是整个SQL语句最基本的解析和执行的流程。

2、基本查询

1.2.1 基本查询
（1）* 可以代替所有字段，也可以自由指定其他任何字段；
（2）此处表后面跟的是表的别名，当每次表名很长的时候，前面又需要指定字段时，都会用别名代替表名；
（3）此处字段后面跟的是字段的别名，同理当字段很长或者需要另外命名时，可以用别名替代，可以直接空格后面跟，也可以用关键字 as。
1.2.1.1 查询所有班级的所有信息

-- 查询全部数据
select * from classroom;
-- 使用表别名
select c.cid,c.classname,c.grade from classroom c;
-- 使用列别名
select classname as cname,grade gd from classroom;

复制脚本到SQL Worksheet 选择脚本点击执行即可。
当多行语句是需指定运行哪一行，需要先选中，不选择执行，则默认是执行全部。

1.2.1.2 查询所有学生的姓名，并去掉重复值

select distinct name from student;

1.2.1.3 查询所有男的学生姓名，并去掉重复

select distinct(name) from student where sex = '男';

1.2.1.4 查询计算每个班的学生人数，此处 count() 为计数，as 为列名重新命名

select cid,count(stuid) as studentCount
from student
group by cid;

1.2.1.5 查询计算学生人数大于2人的班级，> 号用于条件的筛选，having是针对分组后每个组的条件查询，不同于where是直接针对表的条件

select cid,count(stuid) as rs
from student
group by cid having count(stuid) > 2;

1.2.1.6 查询计算学生人数大于等于2人的班级，并按照班级3,2,1班的顺序降序排列

select cid,count(stuid) as rs
from student
group by cid having count(stuid) >= 2
order by cid desc;

1.2.2 条件查询
（1）条件运算符 >, >=, <, <=, =, !=, <>,其中!=和<>都是不等于的意思，两个等价，只是写法不同；
（2）逻辑运算符 and, or, not；
（3）其他运算符 like, between, in, not in, is null, is not null。
1.2.2.1 查询分数大于80的学生ID和学生分数

select a.stuid,a.score
from score a
where a.score > 80;

1.2.2.2 查询分数在80-90分之前的学生ID，此处包括80但不包括90

select a.stuid,a.score from score a
where a.score >= 80 and a.score < 90;

1.2.2.3 查询80-90分之间的学生，此处包括80也包括90，相当于 >=80 and <= 90
between 关键字是前后都包含，建议使用 >= 和 <= 因为可读性比较好。

select a.stuid,a.score from score a
where a.score between 80 and 90;

1.2.2.4 查询小于80或者大于90分之间的学生，相当于 < 80 or > 90

select a.stuid,a.score from score a
where a.score not between 80 and 90;

在使用between时，普通数字可以理解，涉及日期转化时尤其要注意，因为日期涉及到天的时候，要注意看between的包含关系，建议还是直接用条件运算符直接写，简单易理解，可读性更好。

1.2.2.5 查询数学和语文的学生ID和分数

-- 方式一 使用or
select a.stuid,a.score from score a
where (a.subject = '数学' or a.subject = '语文');
-- 方式二 使用in
select a.stuid,a.score from score a
where a.subject in ('数学','语文');

1.2.2.6 查询除了数学和语文之外的学生的ID和分数

select a.stuid,a.score from score a
where a.subject not in ('数学','语文');

1.2.2.7 查询分数为空/不为空的学生ID和分数

-- 分数为空
select a.stuid,a.score from score a
where a.score is null;
-- 分数不为空
select a.stuid,a.score from score a
where a.score is not null;

1.2.2.8 查询学生名字姓张的学生，like匹配中_下划线匹配单个字符，%匹配多个字符

-- %匹配多个字符
select stuid,name,birthday,sex,cid from student 
where name like '小%';
-- _匹配单个字符
select stuid,name,birthday,sex,cid from student 
where name like '小_';

1.2.2.9 查询名字第二字为“三”的学生，第二字为“三”，后面可以有0或多个字符，前面只能有1个

select stuid,name,birthday,sex,cid from student 
where name like '_三%';

1.2.2.10 查询结尾为“二”字的学生

select stuid,name,birthday,sex,cid from student 
where name like '%二';

1.2.2.11 查询中间包含“十”字的学生，不管开头，结尾，还是中间，包含即可

select stuid,name,birthday,sex,cid from student 
where name like '%十%';

二、进阶查询

1、多表查询

常用的有内连接，左连接、右连接、全连接，左外连接，右外连接和全外连接，这里仅介绍常用的内连接，左连接和外连接，这几个在平时几乎占据80%以上的使用。
多表连接可以看成是列的扩展，或者是字段的扩展，如student表只能获取student的字段，连接classroom可以获取该学生的班级信息，连接score可以获取该学生的分数信息等。
（1）内连接为inner join，可以省略inner，只用join，也可以join都省略直接逗号隔开两个表即可。内连接可以看成左右两个表都是主表，也就是当两个表都存在关联的字段且匹配时（如学生表和班级表都存在cid，且cid是相等的时候）可以根据相同的cid取到学生表的学生姓名（name），班级表的该学生的年级（grade）。
图示如下：

取的值是A表和B表都同时存在的C表的数据。

2.1.1.1 查询学生的个人信息和班级信息，此处是隐式的内连接，也是一般的通用写法

-- 常规写法，较常用
select b.name,b.birthday,b.sex,a.classname,a.grade
from classroom a,student b
where a.cid = b.cid;

2.1.1.2 查询学生的个人信息和班级信息，此处是显式的内连接，多表连接查询的时候用的是on关键词

-- 标准内连接写法
select b.name,b.birthday,b.sex,a.classname,a.grade
from classroom a
inner join student b 
on a.cid = b.cid;
-- 直接使用join
select b.name,b.birthday,b.sex,a.classname,a.grade
from classroom a
join student b 
on a.cid = b.cid;

（2）左连接为left join，这里可以看成左边是主表，右边为副表，因为是左边连接的表的数据会全部显示出来，而右边的表只有匹配到的数据才会显示出来。
图示如下：
2.1.2.1 查询学生的个人信息和班级信息，未被分到的班级信息，也要展示出来，就是说classroom里面有cid，student里面没有cid，此处主表是 classroom

select b.name,b.birthday,b.sex,a.classname,a.grade
from classroom a
left join student b on a.cid = b.cid;

2.1.2.2 查询学生的个人信息和班级信息，有班级没有分到教室的，也要展示出来，就是说student里面有cid，classroom里面没有cid，此处主表是 student

select b.name,b.birthday,b.sex,a.classname,a.grade
from student b
left join classroom a on a.cid = b.cid;

（3）右连接为right join，这里可以看成右边是主表，左边为副表，因为是右边连接的表的数据会全部显示出来，而左边的表只有匹配到的数据才会显示出来。可以直接看成是left join的相反，如 table A right join table B等同于 table B left join table A，所以我们常常习惯将主表放在左边，也就习惯使用left join代替right join，因为他们功能是一样的。
图示如下：

2.1.3.1 查询学生的个人信息和班级信息，有班级没有分到教室的，也要展示出来，就是说student里面有cid，classroom里面没有cid，此处主表是student

select b.name,b.birthday,b.sex,a.classname,a.grade
from classroom a
right join student b on a.cid = b.cid;

2.1.3.2 查询学生的个人信息和班级信息，未被分到的班级信息，也要展示出来，就是说classroom里面有cid，student里面没有cid，此处主表是 classroom

select b.name,b.birthday,b.sex,a.classname,a.grade
from student b
right join classroom a on a.cid = b.cid;

2、联合查询

联合查询可以看成是行的扩展，或者是结果集的扩展，如：之前有张旧的student存储2010年之前的数据，叫做student_old,现在的student表是2010年之后的数据，如果全部数据都要的话，就需要两张表联合起来，可以有如下做法：
2.2.1 查询全部的学生信息的数据，数据需要去除重复的数据

select a.name,a.birthday,a.sex
from student_old a
union 
select b.name,b.birthday,b.sex
from student b;

union 会对数据并集操作，会删除重复行，并且会执行排序，一般较慢。

2.2.2 查询全部的学生信息的数据，如果数据已经确定不会有重复数据，那就不用判断重复数据

select a.name,a.birthday,a.sex
from student_old a
union all
select b.name,b.birthday,b.sex
from student b;

union all 对数据并集操作，不会对数据做任何重复的判断，直接合并，一般比union快。

2.2.3 查询旧学生表和新学生表是否有相同的数据

select a.name,a.birthday,a.sex
from student_old a
intersect
select b.name,b.birthday,b.sex
from student b;

intersect 交集运算，查询两个数据的交集部分，也即上面查询的字段两个表都有的部分数据。

2.2.4 查询旧学生表里面有，而新学生表里面没有的数据，也就是旧学生表里面要排除新学生表里面有的数据

select a.name,a.birthday,a.sex
from student_old a
minus
select b.name,b.birthday,b.sex
from student b;

minus 差集运算，查询上面表的数据，并除去上面表和下面表共有的部分，即查出上面表数据有，而在下面表数据没有的数据。

3、子查询

将一个查询的结果，作为另一个查询的条件，这种可以称之为子查询。
2.3.1 查询名字为小二的学生所在的班级

select cid,classname,grade from classroom
where cid in (
	select cid from student where name = '小二');

in 可以匹配一个或者多个，子查询中查出来的cid有一个或者多个时，都能匹配到并显示相关信息。

2.3.2 查询名字为小二的学生所在的班级以外的所有班级

select cid,classname,grade from classroom
where cid not in (
	select cid from student where name = '小二');

not in 的使用跟 in 相反

2.3.3 查询比任意数学成绩高的所有成绩

-- 用 any 实现，表示只要大于任意一个数学成绩即可，即只要大于最小的数学成绩即可
select * from score 
where score > any (select score from score where subject = '数学');
-- 用 some 实现，表示只要大于任意一个数学成绩即可，即只要大于最小的数学成绩即可
select * from score 
where score > some (select score from score where subject = '数学');
-- 用聚合函数实现，表示只要大于任意一个数学成绩即可，即只要大于最小的数学成绩即可
select * from score 
where score > (select min(score) from score where subject = '数学');

any 和 some 的基本功能是一样的，取 > 时表示大于最小的，取 < 时表示小于最大的，取 = 时，相当于in的用法，即匹配多个相同的值。

2.3.4 查询比数学成绩最高的分数还要高的分数

-- 使用 all 实现，表示要大于最大的数学成绩
select * from score 
where score > all (select score from score where subject = '数学');
-- 使用聚合函数实现，表示要大于最大的数学成绩
select * from score 
where score > (select max(score) from score where subject = '数学');

all 的功能，其实就是里面所有值的含义，即取 > 时表示大于最大的，取 < 时表示小于最小的，all 没有取 = 的做法。

2.3.5 查询有学生信息存在的班级信息

select cid,classname,grade from classroom a
where exists (select cid from student b where a.cid = b.cid);

exists 的用法，即查询出student表和classroom表中cid相匹配的classroom表的信息。

2.3.6 查询没有学生信息存在的班级信息

select cid,classname,grade from classroom a
where not exists (select cid from student b where a.cid = b.cid); 

not exists 的用法，即查询出student表和classroom表中cid不相匹配的classroom表的信息。
exists 和 not exists 也是嵌套查询的用法，并且exists 和 not exists 的使用效率会比 in 和 not in 的效率更高一点，更推荐使用。

4、嵌套查询

将一个表的查询结果当成一个临时表，在外面再嵌套其他查询的语句，一般叫做嵌套查询，子查询和嵌套查询一般都是统一来讲。
2.4.1 查询所有男同学所在的班级和班级的信息

select b.name,b.birthday,b.sex,a.classname,a.grade
from classroom a
join(
	select stuid,name,birthday,sex,cid
	from student
	where sex = '男') b on a.cid = b.cid

此处将student表筛选“男”之后，再作为一个新的student表来使用，嵌套和子查询使用方式多变，这里仅以简单例子作为代表。

三、常用函数查询

Oracle提供了很多的函数供我们使用，一般需要用到的时候再来查询，有用到新的也再补充，这里只列出一般常用的函数的使用。
这里先介绍Oracle自带的空表dual，是由Oracle默认提供的最小的一个表，只有一行一列，通常用来获取时间，做运算，获取随机数，或者测试Oracle里面的函数用法，具体举例如下：

--（1）.获取系统时间
select sysdate from dual;
--（2）.做运算
select 99*99 from dual;
--（3）.获取随机数
select DBMS_RANDOM.random from dual;
--（4）.测试函数的用法，这里测试字符串拼接
select concat('123','abc') from dual;

1、字符型函数

3.1.1 大小写装换函数

-- 将整个字符串转换为小写  LOWER()		
select lower('ABC') from dual;
-- 将整个字符串转换为大写  UPPER()		
select upper('abc') from dual;
-- 将整个字符串首字母变成大写  INITCAP()	
select initcap('abc') from dual;

3.1.2 字符和ASCII码的转化

-- 返回字符的ASCII码  ASCII(X)	
select ascii('a') from dual;
-- 返回ASCII码的字符
select chr(98) from dual;

3.1.3 截取字符串

-- 截取子字符串（全角算2字符）,从第0个开始，截取3个字符  SUBSTR()	
select substr('abcdefgh',0,3) from dual;
-- -- 截取子字符串（全角算2字符）,从倒数第3个开始，截取2个字符
select substr('abcdefgh',-3,2) from dual;

3.1.4 清除字符串的空格

-- 删除左边的空格  LTRIM()
select ltrim(' abc edf ') from dual;
-- 删除右边的空格  RTRIM()
select rtrim(' abc edf ') from dual;
-- 删除左右两边的空格  TRIM()，但不会删除中间的空格
select trim(' abc edf ') from dual;

3.1.5 查找字符串

-- 字符串中搜索字符位置（全角算1字符）,在字符串中查找后面字符串的位置  INSTR()		
select instr('abcefd','b') from dual;

3.1.6 替换字符串

-- 替换子字符串,将里面的a替换成def  REPLACE()	
select replace('abc ','a','def') from dual;

3.1.7 计算字符串长度

-- 返回字符串的长度（全角算1字符）  LENGTH()	
select length('abc') from dual;

3.1.8 拼接字符串

-- 连接两个字符串  CONCAT()	
select concat('abc','dbd') from dual;

3.1.9 反转字符串

-- 将字符串反转 REVERSE()
select reverse('abcdtf') from dual;

2、数值型函数

-- 返回绝对值  abs()
select abs(-7) from dual;
-- 返回余弦值  cos()
select cos(0.5) from dual;
-- 返回正弦值 sin()
select sin(0.5) from dual;
-- 向上取整，返回大于或等于给定值的值  ceil()
select ceil(5.4) from dual;
-- 向下取整，返回小于或等于给定值的值  floor()
select floor(5.4) from dual;
-- 返回余数 mod()
select mod(8,3) from dual;
-- 求幂，返回2的3次方 power()
select power(2,3) from dual;
-- 求平方根  sqrt()
select sqrt(4) from dual;
-- 四舍五入，保留几位小数  round()
select round(3.456,2) from dual;
-- 直接截断，保留几位小数，就只要前面的几位小数，后面的全部截断不要  trunc()
select trunc(3.456,2) from dual;
-- 当后面无参数时，只保留整数，trunc()日期的时候，除去时分秒，只保留天
select trunc(3.456) from dual;
select trunc(sysdate) from dual;

3、日期时间函数

3.3.1 获取当前的日期或者时间

-- 获取当前的时间，精确到秒
select sysdate from dual;
-- 获取当天的日期,dd代表天，但一般可以省略不写
select trunc(sysdate) from dual;
select trunc(sysdate,'dd') from dual;
-- 获取当月的第一天,mm代表月
select trunc(sysdate,'mm') from dual;
-- 获取当年的第一天,yyyy代表4位的年，yy则代表2位的年
select trunc(sysdate,'yyyy') from dual;
-- 获取日期的年/月/日/时/分/秒 yyyy/mm/dd/hh/mi/ss，第二个参数直接用字符串代替
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'hh') from dual;
select to_char(sysdate,'mi') from dual;
-- 获取特定格式的日期，因为to_char转化得到的是字符串，需要用to_date转换成日期
select to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') from dual;
select to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd') from dual;

3.3.2 日期的加减
日期的天/时/分/秒可以直接用数值运算，月或者年可以用函数实现。

-- 日期、月份和年份
select sysdate as sys_date,-- 当前系统日期
sysdate - 2 as "date-2-days",-- 当前日期减两天
sysdate + 2 as "date+2-days",-- 当前日期加两天
add_months(sysdate,-2) as "date-2-months",-- 当前日期减两个月
add_months(sysdate,+2) as "date+2-months",-- 当前日期加两个月
add_months(sysdate,-2*12) as "date-2-years",-- 当前日期减2年
add_months(sysdate,+2*12) as "date+2-years"-- 当前日期加两年
from dual;
-- 时分秒的计算
select sysdate as sys_date,-- 系统日期
sysdate - 2/24/60/60 as "second-2",-- 减2秒
sysdate + 2/24/60/60 as "secode+2",--加2秒
sysdate - 2/24/60 as "minute-2",--减两分
sysdate + 2/24/60 as "minute=2",--加2分
sysdate - 2/24 as "hour-2",--减2小时
sysdate + 2/24 as "hour+2"--加2小时
from dual;

3.3.3 其他日期函数

-- 两日期相差多少月，返回两个日期间隔月数，前面的月份-后面的月份   MONTHS_BETWEEN
select months_between(date'2021-03-01',date'2021-02-15') from dual;
-- 参数为1时，返回下周的第一天，下周第一天是按照星期日开始计算的，也就返回下周的星期日  NEXT_DAY
-- 参数为2时，返回下周的第二天，下周第二天是按照星期日开始计算的，也就返回下周的星期一
select next_day(sysdate,1) from dual;
-- 计算指定日期所在月份的最后一天的日期，返回本月最后一天的日期  LAST_DAY
select last_day(sysdate) from dual;

4、数据类型转化函数

Oracle中一般涉及的类型转换为数值型，字符型和日期型。一般为数值和字符的转换，以及日期和字符的转换，数值和日期转换为字符类型用to_char，字符转换为日期用to_date，字符转换为数值用to_number。
3.4.1 字符和数值转换

-- 字符转数值
select to_number('89') from dual;
-- 数值转字符
select to_char(89) from dual;
-- 其中9表示数值位，前面加0表示前面不够的位置补0
select to_char(1234567890,'099999999999999')  from dual;
-- 逗号表示千分位
select to_char(12345678,'999,999,999,999')  from dual;
-- . 点号表示后面保留几位小数
select to_char(123456,'999,999.999')  from dual;
-- $ 符号表示金额
select to_char(89,'$999,999.000') from dual;
-- x 表示16进制，将10进制转换成16进制
select to_char(4567,'xxxx') from dual;
select to_char(456,'xxx') from dual;

更多使用方法可以查看to_char的参数设置。

3.4.2 字符转日期

-- 关于日期的转换时，需要带上转换的格式，也即后面的那串格式
select to_date('2022-10-12','yyyy-mm-dd') from dual;
-- 或者直接 date + 日期字符串也是可以的
select date'2022-10-12' from dual;

3.4.3 日期转字符

-- 一般日期格式转换
-- yyyy表示年，前面mm表示月，dd表示日，HH表示小时，24表示进制还有12进制，MM表示分，SS表示秒
select to_char(sysdate,'yyyy/mm/dd HH24:MM:SS') from dual;
-- timestamp类型转换成日期格式，需要先转换成字符格式，再转换成日期格式
select to_date(to_char(systimestamp,'yyyy-mm-dd'),'yyyy-mm-dd') from dual;
-- 或者直接timestamp类型 + 0 即可转换成日期格式，系统将会自动转换
select systimestamp + 0 from dual;

5、聚合函数

--求数学的最高分  max()最大值函数
select max(score) from score where subject = '数学';
--求数学的最低分  min()最小值函数
select min(score) from score where subject = '数学';
--求数学所有的总分和  sum()求和函数
select sum(score) from score where subject = '数学';
--求数学的平均分，并保留2位小数，四舍五入 avg()求平均值函数
select round(avg(score),2) from score where subject = '数学';
--求数学一共有多少人考（计算行数）  count()计数函数，计数里面可以填字段，数字1或者*
select count(score) from score where subject = '数学'; 
select count(1) from score where subject = '数学'; 
select count(*) from score where subject = '数学'; 

聚合函数一般跟group by having 子句配合使用，group by是按照什么分组，having是在分组后按照条件再筛选。

-- 求各科的最高分
select subject,max(score) from score group by subject;
-- 求各科的最高分，并要求最高分要大于等于98
select subject,max(score) from score group by subject having max(score) >= 98;

6、分析函数*（带*为重点和难点）

分析函数也叫开窗函数over()，这个是属于比较难理解的一个函数，搭配各种聚合函数使用起来用法也是很多。总的理解就是我们一般的查询相当于全部数据开了一个窗口，就是将全部数据都算入统计计算中。当聚合的时候，聚合后每个聚合条目只剩下一条数据。over就允许我们在任意行上面开窗，并且开窗可以有任意的大小（最大就是全部数据，最小就是自己单独一行开窗，也能包括当前含的前面几行，当前行的后面几行等）或者根据字段相同的内容局部开窗，开窗聚合后的数据也会再重新归属每条数据。
当需要原始数值，并且需要小范围的聚合值的时候，就会用到了。

如图所示，是score表的数据，黑色款就是全部数据，当按照stuid聚合score时，最后只能得到一条数据，不管是max、min还是sum。如果我们想计算每一行的分数占总分数的比重时，就需要先算出总分数，再按照stuid跟score原表关联，按照每个科目的score跟总分数求比例。但如果我们能在后面再添加一列按照stuid聚合算出来的总分数呢，这样就比较简单了。
就是要按照stuid开窗（上图红色框），然后在这3行中聚合可以算总分数，算完再单独列出一列，这样就不需先计算再关联，而是直接原表操作，当然我们也还可以按照subject的数学进行开窗，计算出数学科目的平均分，或者数学科目的排名，具体看下面的代码示例。
over 的语法为：聚合函数 + over(partition by … order by …)
其中partition就是小范围聚合的字段，也是开窗的窗口，order为是否要排序。

3.6.1 统计每个学生每个科目的分数以及总分、平均分，最高分，最低分和科目数量

-- 按照stuid开窗，即按照每个相同的stuid统计一次sum（sorce）的值，并将其均匀赋给每一行
-- 其他聚合函数含义一样
select stuid,subject,score,
	sum(score) over(partition by stuid) as sumScore,
	round(avg(score) over(partition by stuid),2) as avgScore,-- 保留2位小数
	max(score) over(partition by stuid) as maxScore,
	min(score) over(partition by stuid) as minScore,
	count(score) over(partition by stuid) as countScore
from score;

统计部分结果如下：
这样当需要再统计分数占总分的比例，分数跟平均分的差距，分数跟最高分的差距等都比较方便。

3.6.2 统计每个学生每个科目的分数，以及该分数在科目的排名

-- 这里就要按照subject来开窗，而不是stuid，因为是按照每个subject来排名的
-- desc为降序排序，即分数高的拍前面；默认为升序排序，也可以显示指定 asc
select stuid,subject,score,
	row_number() over(partition by subject order by score desc) as rowNumber,
	rank() over(partition by subject order by score desc) as rowRank,
	dense_rank() over(partition by subject order by score desc) as rowDense
from score;

以上就是按照subject开个窗口，并给窗口里面的值排序的效果，这样就能很快的再查出每个科目的前3名，只需条件判断里面的排名 <= 3即可。
三种排名方式的解释：
（1）row_number() 称为排名也可以称之为行数更为贴切，因为因为就是行号，即排序后每一行就是一个递增的序号，跟分数是否相等无关。
（2）rank() 意思为排列，相同的分数，计算相同的排名，但会占用后续的排名的位置，如上面3，3，5，没有4的排名。
（3）dense_rank() 意思为密集排列，同样相同的分数，会计算相同的排名，但不会占用后续的排名，如上面的3，3，4
不会占用4的排名。

3.6.3 查询各科按照学生成绩排名，并在后面显示单科的最高和最低分

-- 上面说到单科的最高和最低分可以通过max() over()或者min() over()来实现，同样也可以通过
-- first_value()和last_value()来实现，这两个取的是开窗排序后的第一个值和最后一个值。
select stuid,subject,score,
first_value(score) over(partition by subject order by score desc) as first_value,
last_value(score) over(partition by subject order by score desc) as last_value,
last_value(score) over (partition by subject order by score desc 
	rows between unbounded preceding and unbounded following) last_value_all
from score;

如上面数据可明显看出数学科目时，FIRST_VALUE取出了score第一行的数据，也就是第一个值，但LAST_VALUE并没有按照预期得到数学窗口的最后一行的值80，也即最后一个值。这里的last_value和fitst_value除了正常的功能外，其实还有一个默认的条件设定，就是rows between unbounded preceding and current row，这里可以先简单理解一下：
unbounded preceding 就是前面无边界；
current row 就是当前行；
unbounded following 就是后面无边界；
所以简单说就是last_value求最后一个值的时候，它的范围是前面所有行到当前行的值，在这个范围内求最后一个值。如果当是降序排序的时候，last_value求的最后一个值就是该值本身，那要怎么让last_value按照预期实现功能呢，就是修改它的边界条件，改成rows between unbounded preceding and unbounded following，就是前面无边界，后面无边界，也就是整个subject相同的数学窗口，就是后面的last_value_all的值，就能实现预期功能了。
当是升序排序是，fitst_value需要添加无边界的条件设定。
这里其实就引出了over()窗口函数的边界，也即前面说的能任意开窗的大小了，就是通过后面的rows between来实现的。

3.6.4 查询各科按照学生成绩排名后，前一名跟后一名的分数差值
位移类分析函数，就是将2行数据放到一行上来进行对比，可以计算两个的差值等

select stuid,subject,score,
	-- 向上位移可以写成下面的语句
	lag(score,1,100) over(partition by subject order by score desc) pre_score,
	-- 向下位移可以写成下面的语句
	lead(score,1,0) over(partition by subject order by score desc) next_score
from score;

lag(score,1,100) 向上位移1位，因为第1位上面没有值，则使用默认的100作为它的值，如果这里不添加，这默认为空。位移的位置也是可以通过第二个参数来调整的。
lead(score,1,0) 向下位移1位，因为最后1位的下面没有值，使用默认的值0作为它的值，如果这里不添加，则默认为空。位移的位置也是可以通过第二个参数来调整的。

7、列处理函数*（也是重点）

这里只介绍部分常用的函数，以后有用到再来补充。
3.7.1 查询所有student的信息，当cid为空时显示为0

select stuid,name,nvl(cid,0) cid from student;

nvl(expression,value) 当查出来的列expression为空时，可以按照默认的值value显示，常常用在缺失的数据时，或者数值有可能为0时，要来做除法，就必须对其中的某些值做转换。

3.7.2 查询所有student的信息，且性别显示为男生和女生

select stuid,name,decode(sex,'男','男生','女生') sex from student;

decode(expression,value,result1,result2)，当查出来的列expression=value时，返回result1的值，否则返回result2的值。

3.7.3 使用 decode() + max() 还能实现列转行的功能

select stuid,max(decode(subject,'语文',score,null)) as "语文",
		max(decode(subject,'数学',score,null)) as "数学",
		max(decode(subject,'英语',score,null)) as "英语"
from score
group by stuid order by stuid;

3.7.4 给学生的分数评级，95分（含）以上为优秀，90分（含）以上为优良，80分以上为良好，60分以上为及格，其他为不及格

-- 使用 case when 判断分数的分级
select stuid,subject,score,
	(case when score>=95 then '优秀' 
		when score>=90 then '优良' 
		when score>=80 then '良好'
		when score>=60 then '及格' 
		else '不及格' end) pingji
from score;

3.7.5 统计学生表男女的人数

-- 使用 case when 针对不同的条件的计数
select count(case when sex = '男' then 1 end) countMale,
    count(case when sex = '女' then 1 end) countFemale
from student;

case when 可以针对每个字段的值再做判断。

3.7.6 统计每个班的名单，姓名用“ ， ” 隔开，并按照学生学号依次排列

select cid,listagg(name,' , ') within group (order by stuid) as abc
from student
group by cid;

listagg 后面接的是需要排列的字段和隔开的符号，order by 里面接的是内部 name 的排序规则，这里是按照学号 stuid 排序，如果是按照 name 排序的话，结果会有些许不同，可以改成 name 再跑数据看看。班级的排序在 group by 之后再添加 order by 即可。

3.7.7 将分数表行转列，每个科目一行，每个学号一列

-- 分数表全部字段
select * from score;
-- 先将不要的字段排除，留下三个字段
select subject,score,stuid from score;
-- 行转列
select * from (
    select subject,score,stuid from score)
pivot (sum(score) for stuid in (1,2,3,4,5,6));

语法为：
pivot (任一聚合函数 for 需专列的值所在列名 in (需转为列名的值))；

四、DDL语言(Data Defination Language)数据定义语言

DDL包含CREATE 、ALTER 、DROP 、RENAME 、TRUNCATE等命令，用来对数据库对象进行创建、修改、删除、重命名等操作。

1、数据类型

（1）字符串类型
CHAR：定长字符串，它会用空格填充来达到其最大长度，最多可以存储2000字节的信息
NCHAR：这是一个包含UNICODE格式数据的定长字符串，最多可以存储2000字节的信息
VARCHAR2：变长字符串，它不会用空格填充来达到其最大长度，最多可以存储4000字节的信息
NVARCHAR2：这是一个包含UNICODE格式数据的变长字符串，最多可以存储4000字节的信息
（2）数值类型
NUMBER：NUMBER(p,s)是最常见的数字类型，关于NUMBER的有效位§和精确位(s)遵循以下规则：
p：是有效数据总位数，取值范围为[1-38]，默认值是38
s：表示精确到多少位，取值范围为[-84-127]，默认值是0
INTEGER：INTEGER是NUMBER的子类型，它等同于NUMBER(38,0)，用来存储整数
FLOAT：Float(n)是NUMBER的子类型，数 n 指示位的精度，n值的范围可以从1到126
（3）日期类型
DATE：DATE是最常用的日期数据类型，它可以存储日期和时间信息，虽然可以用字符或数字类型表示日期和时间信息，但是日期数据类型具有特殊关联的属性。Oracle为每个日期值存储以下信息：世纪、年、月、日期、小时、分钟和秒，一般占用7个字节的存储空间
TIMESTAMP：这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同，因为TIMESTAMP可以包含小数秒，带小数秒的TIMESTAMP在小数点右边最多可以保留9位

2、表操作

4.2.1 创建表的语法
create table 表名(
列名 列的类型 [列的约束],
列名 列的类型 [列的约束]
);

--	如创建学生表
create table student(
	stuid number primary key,
	name varchar(20),
	birthday date,
	sex varchar(20),
	cid number
);

表的约束

CREATE TABLE 表名(
		列名 列的类型 primary key,--主键约束
		列名 列的类型 not null,--非空约束
		列名 列的类型 unique,--唯一约束
		列名 列的类型 check(列名 in (检查列表)),--检查约束
		constraint 约束名 foreign key(字段名) references 主表(被引用列)--外键约束
	);

其中主键约束就是非空约束加上唯一约束，就是主键必须是非空的，且每个值都是唯一的；
非空约束就是不允许该值为空；
唯一约束就是不允许该值出现重复的情况，只能是唯一值；
检查约束就是只能出现检查列表里面的值，像性别只能出现‘男’或‘女’，但其实数据库里面一般用0或1来表示，所以只能出现0或1或为空，不允许出现其他值；
外键约束就是该值只能是出现在主表(被引用列)中的值，如student中的cid只能是出现在classroom中的cid，不允许出现没有在classroom中没有的cid的数据，我这里的student表是没有加外键约束的。

-- 以学生表为例，前期正确的创建表方法
create table student01(
	stuid number primary key,				--主键约束，唯一且非空
	name varchar(20) not null,				--名字不能为空，非空约束，就是数据不能为空
	birthday date not null,					--出生年月不能为空，非空约束
	sex char(1) check(sex in ('0','1')),	--检查约束，性别必须是0或1表示
	cid number,
	stuno varchar2(30) unique, 				--学号必须唯一，唯一约束，就是数据的值必须每一个都是唯一的，不能有重复
	constraint fk_classroom_id foreign key(cid) references classroom(cid)	--cid外键约束,cid作为classroom的主键，这里作为外键
	);

4.2.2 复制表
复制表可以复制源表的所有或者部分数据，所有或者部分字段，或者直接复制表的结构，而不要任何的数据。

-- 复制全表数据
create table studentA as select * from student;
-- 复制性别为男的学生数据
create table studentB as 
	select * from student where sex = '男';
-- 复制student表的stuid，name和birthday字段
create table studentC as 
	select stuid,name,birthday from student;
-- 只复制student表的表结构，不要任何数据
create table studentD as 
	select * from student where 1=2;

4.2.3 重命名表

rename studentA to student02;

4.2.4 删除表

-- 将之前的新加的表全部删除，包含表结构和表数据
drop table student02;
drop table student01;
drop table studentB;
drop table studentC;
drop table studentD;
-- truncate 表，只清空表的数据，而保留表的结构，这里表已经删除，仅做演示，或者可以自行先清空，再删除
truncate table studentD;

4.2.5 添加或删除约束
当在创建表时，没有添加约束，可以在后续再添加或删除约束。

-- 1、主键约束
-- 添加主键约束
alter table student_tmp add constraint pk_student_stuid primary key(stuid);
-- 删除主键约束
alter table student_tmp drop constraint pk_student_stuid;
-- 或者
alter table student_tmp drop primary key;
-- 2、非空约束
-- 添加非空约束
alter table student_tmp modify name not null;
-- 删除非空约束
alter table student_tmp modify name null;
-- 3、唯一约束
-- 添加唯一约束
alter table student_tmp add constraint uk_student_stuid unique(stuid);
-- 删除唯一约束
alter table student_tmp drop constraint uk_student_stuid;
-- 或者
alter table student_tmp drop unique(stuid);
-- 4、检查约束
-- 添加检查约束
alter table student_tmp add constraint ck_student_sex check(sex in ('0','1'));
-- 删除检查约束
alter table student_tmp drop constraint ck_student_sex;
-- 5、外键约束
-- 添加外键约束
alter table student_tmp add constraint fk_student_cid foreign key(cid) references category(cid);
-- 删除外键约束
alter table student_tmp drop constraint fk_student_cid;

4.2.6 列操作
当在创建表时，未能确定列名或者列的类型，或者是长度大小，都可以通过后续再修改。

alter table 表名 add 列名 列的类型;  -- 添加列
-- 如给学生表添加学号列
alter table student add stuno varchar2(20); 
alter table 表名 rename column 旧列名 to 新列名; -- 修改列名
--	如将学号列stuno修改为stunumber
alter table student rename column stuno to stunumber;
alter table 表名 modify 列名 列的类型; --	修改列类型
--	如将学号列的类型修改为varchar2(30)
alter table student modify stunumber varchar2(30);
alter table 表名 drop column 列名; -- 删除列
--	如删除学号列
alter table student drop column stunumber;

五、DML语言（Data Manipulation Language）数据操作语言

数据操作语言为添加、更新或删除数据的操作语言。

5.1.1 给学生表插入信息
语法为：insert into 表名(列名1,列名2,…) values(值1,值2,…);

-- 如学生表插入数据
insert into student(stuid,name,birthday,sex,cid) values(16,'小红',to_date('2021-01-1','yyyy-mm-dd'),'男',1);
-- 提交修改
commit;	

5.1.2 更新学生表信息

-- 如将小红改成小明，用stuid=1的条件找到需要修改的数据行，然后将要修改的内容写在set后面
update student set name='小红' where stuid = 16;
-- 提交修改
commit;

语法为：update 表名 set 列名1=值1,列名2=值2,… where 查询条件;

5.1.3 删除学生表信息

-- 如将学生表中新添加的数据删除
delete from student where stuid = 16;
commit;

语法为：delete from 表名 where 查询条件;

commit 为数据库的提交命令，即将修改正式提交给数据库。

部分参考文档：https://blog.csdn.net/qq_38490457/article/details/107976731