第三章 - SQL语言基础
第三章 - SQL语言基础
3.1 SQL语言
3.1.1 SQL语言介绍
SQL（Structured Query Language）是结构化查询语言。SQL语言集数据定义、查询、更新和控制功能于一体。
SQL语言已经成为关系数据库的标准语言，是关系数据库的基础，oracle，SQL Server和DB2都使用SQL。
目前，很多数据库产品都对SQL语句进行再开发与扩展，如oracle提供的PL/SQL（Procedure Language and SQL）就是对SQL的一种扩展。
3.1.2 SQL的特点
1、综合统一
集数据定义语言（DDL）、数据操纵语言（DML）、数据控制语言（DCL）功能一体。
可以独立完成数据库生命周期中的全部活动：
定义和修改、删除关系模式、定义和删除视图、插入数据、建立数据库；
对数据库中的数据进行查询和更新；
数据库重构和维护
数据库安全性、完整性控制以及事务控制
嵌入式SQL和动态SQL定义
用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据库的运行
数据操作符统一
2、 高度非过程化语言
非关系数据模型的数据操纵语言“面向对象”，必须指定存取路径。
SQL只要提出“做什么”，无需了解存取路径。
存取路径的选择以及SQL的操作过程由系统自动完成
3、面向集合操作方式
非关系数据模型采用面向记录的操作方式，操作对象是一条记录
SQL采用估计和操作方式
操作对象、查找结果可以是元组的集合
一次插入、删除、更新操作的对象可以是元组的集合。
4、以同一种语法结构提供多种使用方式
SQL是独立的语言，能够独立地用于联机狡猾的使用方式
SQL又是嵌入式语言，SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用
可作为交互式语言独立使用、也可作为子语言嵌入宿主语言中使用，但语法结构一致。
5、语言简捷、易学易用
完成核心功能只用9个动词，SQL接近英语口语或自然语言，简单易学。这9个动词为：CREATE、DROP、ALTER,INSERT、UPDATE、DELETE、SELECT、GRANT、REVOKE。
| SQL功能 | 命令动词 |
|---|---|
| 数据定义 | CREATE、DROP、ALTER |
| 数据操纵 | INSERT、UPDATE、DELETE |
| 数据查询 | SELECT |
| 数据控制 | GRANT、REVOKE、 COMMIT、ROLLBACK |
3.1.3 SQL的基本概念
存储文件
逻辑结构组成了关系数据库的内模式
物理结构对用户是隐蔽的
基本表
本身独立存在的表
SQL中一个关系就对应一个基本表
一个（或多个）基本表对应一个存储文件
一个表可以带若干索引
视图
从一个或几个基本表导出的表
数据库中只存放视图的定义而不存放视图对应的数据
视图是一个虚表
用户可以在视图上再定义视图
3.1.4 用户模式
在Oracle数据库中，为了便于管理用户创建的数据库对象（如数据表、索引、视图等），引入了模式的概念，某个用户创建的数据库对象都属于该用户模式。
3.2 数据定义语言
SQL的数据定义部分包括对数据库、SQL模式、基本表、视图和索引、存储过程、触发器、数据库链路等对象的创建、修改、删除操作。
3.2.1 基本表的定义
1、创建基本表
语法
CREATE TABLE <基本表名>
( <列名 ><数据类型> [列级完整性约束条件]
[,<列名 ><数据类型> [列级完整性约束条件]]
……
[,表级完整性约束条件]）；
如果完整性约束条件涉及到该表的多个属性列时，必须在表级定义该约束条件，否则既可以定义在列级，也可以定义在表级。
CREATE TABLE -- 关键字，必须要写
<基本表名> -- 表的名字
<列名 > -- 列名，字段名
<数据类型> -- 字段内值的数据类型
[列级完整性约束条件] -- 完整性约束条件
建表时需要注意：
- 表命或列名不得超过30个字符。
- 表名或列名命名规则：第一个字符必须是字母， 后面可以跟字母、数字、三个特殊符号（_、$、#） ，表名或列名中不可以包含空格。
- 一个用户中表名要唯一，一个表中列名要唯一。
- 表名和列名不区分大小写，但显示出来都是大写。
- 保留字不能用作表名或列名。
1.所有的关键字都是绿色的
2.空格/换行，不会影响查询
3.语句书写顺序为从左到右，从上到下
4.所有标点符号必须为英文符号
5.报错会用红色波浪线标记错误位置
6.两种注释方法：（1）--，单行注释
（2）/* */，多行注释
7.在oracle中，语句不区分大小写
2、数据类型
- 数值型
INTEGER 定义数据类型为整数类型，可简写成INT。
SMALLINT 定义数据类型为短整数类型。
NUMERIC（p,s）定义数据类型为数值型，并给定精度p（总的有效位，不包含符号位及小数点）或标度s（十进制小数点右边的位数）。
FLOAT（p）定义数据类型为浮点数值型，p为指定的精度。
REAL 定义数据类型为浮点数值型，它的精度由执行机构确定。
DOUBLE PRECISION 定义数据类型为双精度浮点类型，它的精度由执行机构确定。
- 字符类型
CHAR（n）定义指定长度的字符串，n为字符数的固定长度。
VARCHAR（n）定义可变长度的字符串，其最大长度为n，n不可省略。
- 位串型
BIT（n）定义数据类型为二进制位串，其长度为n。
BIT VARYING（n）定义可变长度的二进制位串，其最大长度为n，n不可省略。
- 时间型
DATE 用于定义日期，包含年、月、日，格式为YYYY-MM-DD。
TIME 用于定义时间，包含时、分、秒，其格式为HH:MM:SS。
- 布尔型
BOOLEAN 定义布尔类型，其值可以是:TRUE（真）、FALSE（假）。
【示例3-1】建立职工关系模式（emp），包含员工编号（eno）、员工名字（ename）、部门（dno）、性别（sex）、生日（birthday）。
CREATE TABLE emp(
eno number(10),
ename varchar2(10),
dno varchar(6),
sex varchar(2),
birthday date
);
【示例3-2】建立部门关系模式（dept），包含部门编号（dno）、部门名称（dname）、（loc）。
CREATE TABLE dept(
dno varchar2(6),
dname varchar(8),
loc varchar(10)
);
3、约束条件
主键约束（PRIMARY KEY） ：体现了实体完整性。要求某一列的值既不能为空，也不能重复。
外键约束（FOREIGN KEY） ：体现参照完整性。外键的取值或者为空或者参考父表的主键。
属性约束 ：体现了用户定义的完整性。属性约束主要限制某一属性的取值范围。分为以下几类：
非空约束(NOT NULL)*：要求某一属性的值不允许为空值。
唯一约束（UNIQUE）：要求某一属性的值不允许重复。
检查约束（CHECK）：CHECK约束可以对某一个属性列的值加以限制。
【示例3-3】建立样本数据库中的STUDENT表，要求所有约束条件均为列级完整性约束，且该表满足下列的条件。
CREATE TABLE STUDENT(
Sno CHAR(8) PRIMARY KEY, /*主键约束*/
Sname VARCHAR2(20) UNIQUE , /*唯一约束*/
Sex CHAR(4) NOT NULL, /*非空约束*/
Age INT CHECK(Age>16), /*检查约束*/
Dept VARCHAR2(15)
);
【示例3-4】建立样本数据库中的COURSE表，要求所有约束条件均为列级完整性约束，且该表满足表中所示的条件。
CREATE TABLE COURSE(
Cno CHAR(8) PRIMARY KEY, /*主键约束*/
Cname VARCHAR2(10),
Tname VARCHAR2(10),
Cpno CHAR(8) REFERENCES Course(Cno), /*外键约束*/
Credit NUMBER
);
CREATE TABLE COURSE(
Cno CHAR(8),
Cname VARCHAR2(10),
Tname VARCHAR2(10),
Cpno CHAR(8),
Credit NUMBER,
PRIMARY KEY (Cno), /*主键约束*/
FOREIGN KEY (Cpno) REFERENCES Course(Cno) /*外键约束*/
);
【示例3-5】建立样本数据库中的SC表(选课表)，要求所有约束条件均为列级完整性约束，且该表满足表中所示的条件。(Sno，Cno)属性组合为主键。
CREATE TABLE SC(
Sno CHAR(8),
Cno CHAR(8),
Grade NUMBER,
PRIMARY KEY(Sno,Cno), -- 主键约束
FOREIGN KEY(Sno) REFERENCES Student(Sno), -- 外键约束
FOREIGN KEY(Cno) REFERENCES Course(Cno) -- 外键约束
);
3.2.2 修改基本表结构
修改基本表的结构包括增加、删除和修改表 的属性列，默认值和约束条件。
1、语法
ALTER TABLE <基本表名>
[ADD <新列名> <数据类型> [列级完整性约束] ]
[DROP COLUMN <列名>]
[MODIFY <列名> <新的数据类型>]
[ADD CONSTRAINT <完整性约束>]
[DROP CONSTRAINT <完整性约束>];
ADD 增加列
DROP COLUMN 删除列
MODIFY 为列修改新的数据类型
ADD CONSTRAINT 添加一个完整性约束
DROP CONSTRAINT 删除一个完整性约束
【示例3-6】向 STUDENT 表中增加一个身高“Height”属性列，数据类型为 INT。
ALTER TABLE Student ADD Height INT;
新增加的属性列总是表的最后一列。不论表中是否已经有数据，新增加的列值为空。所以新增加的属性列不能有NOT NULL约束，否则就会产生矛盾。
【示例3-7】将 STUDENT 表中的“Height”属性列的数据类型改为 real。
ALTER TABLE Student MODIFY Height real;
修改原有的列定义有可能会破坏已有数据，所以在修改时需要注意：可以增加列值的宽度及小数点的长度，只有当某列所有行的值为空或整张表是空时，才能减少其列值宽度，或改变其列值的数据类型。
【示例3-8】给 STUDENT 表中“Height”属性列增加一个 CHECK 约束，要求学生的身高要超过140 厘米才行。
ALTER TABLE Student ADD CONSTRAINT Chk1 CHECK(Height>140);
Chk1是Height属性列上新增加的CHECK约束的名字。
【示例3-9】删除“Height”属性列上的 CHECK 约束。
ALTER TABLE Student DROP CONSTRAINT Chk1;
查看表中约束名称：
Select constraint_name,constraint_type from user_constraints where table_name='STUDENT'; -- 必须大写
-- P->主键，R->外键，U->unique约束
-- C->check约束,not null约束也归为check约束。
【示例3-10】删除“Height”属性列上的 CHECK 约束。
ALTER TABLE Student DROP COLUMN Height;
3.2.3 基本表的删除
如果想要删除一个数据表，可以使用DROP关键字。
语法
DROP TABLE <表名> [CASCADE CONSTRAINTS];
DROP TABLE emp；
DROP TABLE dept；
删除基本表时要注意以下几点:
- 表一旦被删除,则无法恢复。
- 如果表中有数据，则表的结构连同数据一起删除。
- 在表上的索引、约束条件、触发器，以及表上的权限也一起被删除。
- 当删除表时，涉及该表的视图、存储过程、函数、包被设置为无效。
- 只有表的创建者或者拥有DROP ANY TABLE权限的用户才能删除表。
- 如果两张表之间有主外键约束条件，则必须先删除子表，然后再删除主表。
- 如果加上CASCADE CONSTRAINTS，在删除基本表的同时，相关的依赖对象也一起被删除。
【实例3-11】删除学生选课表SC。
DROP TABLE SC;
基本表定义一旦被删除，表中的数据、此表上建立的索引和视图都将自动被删除掉。因此执行删除基本表的操作一定要格外小心。删除表时要先删除从表，再删除主表。
3.3 数据操纵语言
3.3.1 插入数据
当基本表建立以后，就可以使用 INSERT 语句向表中插入数据了。
INSERT 语句有两种插入形式：
(1) 插入单个元组(单个行或记录)
(2) 插入多个元组（插入子查询结果:select）
1、插入单个元组
向基本表中插入数据的语法格式如下：
INSERT INTO <基本表名> [(<列名1>，<列名2>，…，<列名n>)] VALUES（<列值1>，<列值2>，…，<列值n>） ;
注意：
（1）向表中插入数据之前，表的结构必须已经创建。
（2）插入的数据及列名之间用逗号分开。
（3）在INSERT语句中列名是可以选择指定的，如果没有指定列名，则表示这些列按表中或视图中列的顺序和个数。
（4）插入值的数据类型、个数、前后顺序必须与表中属性列的数据类型、个数、前后顺序匹配。
【示例3-12】向学生表中插入一个新的学生记录。
-- 方法一：省略所有列名
INSERT INTO Student VALUES ('05880111', '张晓三', '男',23, '数学系');
-- 方法二：指出所有列名
INSERT INTO student(Sno,Sname,Sex,Age,Dept) VALUES ('05880111', '张晓三', '男',23, '数学系');
上面两种方法的作用是相同的。
【示例3-13】向学生表中指定的属性列插入数据
INSERT INTO Student1(Sno,Sname,Sex) VALUES ('05880112', '王晓五', '女');
其中，没有插入数据的属性列的值均为空值。
2、插入多个元组
向基本表中插入数据的语法格式如下：
INSERT INTO <基本表名> [(<列名1>，<列名2>， …，<列名n>)] 子查询;
【实例3-14】如果已经创建了课程平均成绩记录表course_avg（cno,ave），其中ave表示每门课程的平均成绩，向course_avg表中插入每门课程的平均成绩。
-- 插入数据
INSERT INTO Course_avg(Cno,Ave)
-- 查询数据
SELECT Cno, AVG(Grade) FROM SC GROUP BY Cno;
3.3.2 修改数据
如果表中的数据出现错误，可以利用UPDATE命令进行修改。UPDATE 语句用以修改满足指定条件的元组信息。
UPDATE 语句一般语法格式为：
UPDATE <基本表名> SET <列名1> = <表达式> [，<列名2> = <表达式>]…
[WHERE <条件>] ;
其中，UPDATE关键字用于定位修改哪一张表，SET关键字用于定位修改这张表中的哪些属性列，WHERE<条件>用于定位修改这些属性列当中的哪些行。
修改某一个元组的值
【示例3-15】将java课程的学分改为5学分。
UPDATE Course SET Credit=5 WHERE Cname='java';
修改多个元组的值
【示例3-16】将所有男同学的年龄增加2岁。
UPDATE Student SET Age=Age+2 WHERE Sex='男';
【示例3-17】将所有课程的学分减1。
UPDATE Course SET Credit=Credit-1;
3.3.3 删除数据
如果不再需要学生选课系统中的某些数据，此时应该删除这些数据，以释放其所占用的存储空间。
DELETE 语句的一般语法格式为：
DELETE FROM <表名> [WHERE <条件>] ;
DELETE语句的功能是从指定表中删除满足WHERE<条件>的所有元组。如果省略WHERE<条件>，表示删除表中全部的元组信息。
DELETE语句只删除表中的数据，而不能删除表的结构，所以表的定义仍然在数据字典中。
删除某一个元组的值
【示例3-18】删除学号为“05880110”的学生记录。
DELETE FROM Student WHERE Sno= '05880110 ';
删除多个元组的值
【示例3-19】删除学号为“05880102”学生的选课记录。
DELETE FROM SC WHERE Sno='05880102';
每一个学生可能选修多门课程，所以DELETE语句会删除这个学生的多条选课记录。
【示例3-20】删除所有学生的选课记录。
DELETE FROM SC;
3.4 数据查询
SQL语言中最重要、最核心的操作就是数据查询。关系代数的运算在关系数据库中主要由SQL数据查询来体现。SQL语言提供SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。
3.4.1 查询语法
查询数据使用 SELECT 语句完整的句法：
SELECT [ALL|DISTINCT] <目标列表达式>[，<目标列表达式>]…
FROM <表名或视图名>[，<表名或视图名>]…
[ WHERE<条件表达式> ]
[ GROUP BY<列名1> [ HAVING <组条件表达式> ] ]
[ ORDER BY<列名2> [ ASC|DESC ] ];
其中：
SELECT -- 子句说明要查询的数据。
ALL --表示筛选出数据库表中满足条件的所有记录，一般情况下省略不写。
DISTINCT --表示输出结果中无重复记录。
FROM --子句说明要查询的数据来源。
WHERE --子句指定查询条件。
GROUP BY --子句表示在查询时，可以按照某个或某些字段分组汇总。HAVING子句必须跟随GROUP BY一起使用，表示在分组汇总时，可以根据组条件表达式筛选出满足条件的组记录。
ORDER BY --子句表示在显示结果时，按照指定字段进行排序。ASC表示升序，DESC表示降序，省略不写默认情况下是ASC。
/*select 后面可以写表中不存在的字段，添加表中不存在的列时-会自动生成一列，列名与值均相同*/
select '你好' from emp;--字符型,需要用单引号''
select 123 from emp;--数值型
select '123' from emp;--新建列
下面我们以学生选课数据库系统为例，说明 SELECT 语句的各种用法。
该数据库包含学生表STUDENT、课程表COURSE和选课表SC三张表。
数据准备
1、数据库创建，前面我们已经创建过三个数据表了，这里如果忘记可以参考。
-- 学生表STUDENT1
/*
* 字段名 字段类型 是否为空 说明 字段描述
* SNO CHAR(8) NOT NULL 主键 学生学号
* SNAME VARCHAR(20) UNIQUE 唯一约束 学生姓名
* SEX CHAR(4) NOT NULL 非空约束 性别
* AGE INT 年龄大于16岁 年龄
* DEPT VARCHAR(15) 学生所在的系别名称
* */
CREATE TABLE STUDENT1 (
SNO char(8) NOT NULL PRIMARY KEY,
SNAME varchar(20) UNIQUE ,
SEX char(4) NOT NULL ,
AGE int，
DEPT varchar(15)
);
-- 课程表COURSE1
/*
* 字段名 字段类型 是否为空 说明 字段描述
* CNO CHAR(8) NOT NULL 主键 课程编号
* CNAME VARCHAR(10) 课程名称
* TNAME VARCHAR(10) 授课教师名
* CPNO CHAR(8) 外键 先修课程号
* （参照课程表中的课程编号）
* CREDIT NUMBER 学分
* */
CREATE TABLE COURSE1 (
CNO char(8) PRIMARY KEY,
CNAME varchar(10),
TNAME varchar(10),
CPNO char(8) REFERENCES COURSE1(CNO),
CREDIT NUMBER
);
-- 选课表SC1
/*
* 字段名 字段类型 是否为空 说明 字段描述
* SNO CHAR(8) NOT NULL 外键（参照学生表中的学生编号） 学生学号
* CNO CHAR(8) NOT NULL 外键（参照课程表中的课程编号） 课程编号
* GRADE NUMBER 选修成绩
* */
CREATE TABLE SC1(
SNO char(8),
CNO char(8),
GRADE NUMBER,
PRIMARY KEY (SNO,CNO),
FOREIGN KEY (SNO) REFERENCES STUDENT1(SNO),
FOREIGN KEY (CNO) REFERENCES COURSE1(CNO)
);
2、数据库数据准备
-- 学生表STUDENT
-- student1 数据插入
INSERT INTO SCOTT.STUDENT1(SNO,SNAME,SEX,AGE,DEPT) VALUES (20230001,'赵一','男',17,'计算机系');
INSERT INTO SCOTT.STUDENT1(SNO,SNAME,SEX,AGE,DEPT) VALUES (20230002,'钱二','女',20,'信息系');
INSERT INTO SCOTT.STUDENT1(SNO,SNAME,SEX,AGE,DEPT) VALUES (20230003,'孙三','女',19,'计算机系');
INSERT INTO SCOTT.STUDENT1(SNO,SNAME,SEX,AGE,DEPT) VALUES (20230004,'李四','男',22,'信息系');
INSERT INTO SCOTT.STUDENT1(SNO,SNAME,SEX,AGE,DEPT) VALUES (20230005,'周五','男',22,'数学系');
INSERT INTO SCOTT.STUDENT1(SNO,SNAME,SEX,AGE,DEPT) VALUES (20230006,'郑六','男',19,'数学系');
INSERT INTO SCOTT.STUDENT1(SNO,SNAME,SEX,AGE,DEPT) VALUES (20230007,'王七','女',23,'日语系');
INSERT INTO SCOTT.STUDENT1(SNO,SNAME,SEX,AGE,DEPT) VALUES (20230008,'吴四','男',31,'日语系');
INSERT INTO SCOTT.STUDENT1(SNO,SNAME,SEX,AGE,DEPT) VALUES (20230009,'张三','女',19,'管理系');
INSERT INTO SCOTT.STUDENT1(SNO,SNAME,SEX,AGE,DEPT) VALUES (20230010,'陈七','女',21,'管理系');
-- 课程表COURSE
INSERT INTO SCOTT.COURSE1 (cno,CNAME,TNAME,CREDIT) VALUES ('c1','maths','李老师',3);
INSERT INTO SCOTT.COURSE1 (cno,CNAME,TNAME,CREDIT) VALUES ('c2','english','赵老师',5);
INSERT INTO SCOTT.COURSE1 (cno,CNAME,TNAME,CREDIT) VALUES ('c3','japanese','陈老师',4);
INSERT INTO SCOTT.COURSE1 (cno,CNAME,TNAME,CPNO ,CREDIT) VALUES ('c4','database','张老师','c1',4);
INSERT INTO SCOTT.COURSE1 (cno,CNAME,TNAME,CPNO ,CREDIT) VALUES ('c5','java','王老师','c1',3);
INSERT INTO SCOTT.COURSE1 (cno,CNAME,TNAME,CPNO ,CREDIT) VALUES ('c6','jsp_design','刘老师','c5',2);
-- 选课表SC
-- 选课表SC 数据导入
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230001','c1','75');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230001','c2','95');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230001','c3','82');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230001','c4','88');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230002','c1','89');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230002','c3','61');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230002','c5','55');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230003','c1','72');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230003','c2','45');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230003','c3','66');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230003','c5','86');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230004','c2','85');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230004','c3','97');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230005','c1','52');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230005','c5','56');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230006','c6','74');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230007','c1','57');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230007','c6','80');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230007','c4','');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230008','c1','');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230009','c1','86');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230009','c2','67');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230009','c3','80');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230009','c4','72');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230009','c5','36');
INSERT INTO SCOTT.SC1 (SNO,CNO,GRADE) VALUES ('20230009','c6','52');
3.4.2 单表查询
1．选择表中若干列
选择表中的全部列或部分列，这就是投影运算。
（1）查询指定的列
【示例3-21】查询全体学生的学号、姓名和年龄。
SELECT SNO ,SNAME ,AGE FROM SCOTT.STUDENT1 ;
【示例3-22】查询全部课程的课程名称和授课教师名。
SELECT CNAME ,TNAME FROM SCOTT.COURSE1 ;
注意
1. 当查询的值不在其中时，不会报错，无结果；
2. 在Oracle中，值严格区分大小写（值指的是表中的内容）；
3. 查询内容时，如果是数值则不需要引号；
4. 当类型不一致时会报错。
（2）查询全部列
【示例3-23】查询全部课程的详细记录。
SELECT * FROM SCOTT.COURSE1 ;
（3）查询经过计算的值
【示例3-24】查询全体学生的姓名、性别及其出生年份。
这里因为系统中没有出生年份，但是有年龄，我们可以使用当前年份减去年龄就可以得到出生年份。
SELECT SNAME ,SEX ,2023-AGE FROM SCOTT.STUDENT1 ;
（4）指定别名来改变查询结果的列标题
起别名 方法就是：在列名的后面加上一个空格 或者是 as ，然后写上它的别名。在查询结果显示时就用别名代替列名了。
给字段起别名
--关键词 as 可以省略
select ename as 姓名,empno from emp;
--字段可以重复出现，相同字段给其中一个起别名不会影响其他字段
select ename 姓名,empno,empno from emp;
--双引号分情况可以省略：汉字，字母；数字和特殊符号不可以省略
select ename "$",empno from emp;
--别名是临时的，只对当此查询有效
select ename from emp;
给表起别名--不能使用关键词as，起别名后看不到
select * from emp 员工信息表;
select 员工信息表.*,ename from emp "员工信息表";
select emp.*,ename from emp;--通配符与其他字段的使用的方法
select '姓名' "***" from emp;--新建列
select '姓名'||ename||'的薪资为：'||sal from emp;--新建列与字段合并
注意事项
- 注意点：
1.给表起别名不能使用关键词 as
2.给表起别名后看不到
3.给表起别名后，原本的名字就暂时不能用了
- 思考：一般什么时候会起别名？
1.名字特别长不方便看的时候
2.名字含义不明的时候
3.公司有要求的时候
4.因为select运行顺序在from之后，所以select能看到表中的别名，并使用
连接符
连接符：|| 将两个或两个以上字段连接起来
select ename sal from emp;
select ename,sal from emp;
select ename || sal from emp;
思考题：
1.输出emp表中"XXX的薪资为多少元"。
select ename || '的薪资为' || sal || '元' 薪资情况 from emp;
【示例3-25】查询全体学生的姓名、性别及其出生年份。
-- 使用 AS
SELECT SNAME ,SEX ,2023-AGE AS 出生年份 FROM SCOTT.STUDENT1 ;
-- 使用空格
SELECT SNAME ,SEX ,2023-AGE 出生年份 FROM SCOTT.STUDENT1 ;
2．选择表中若干行
选择表中若干行，这就是选择运算。
（1）消除取值重复的行
【示例3-26】查询所有选修了课程的学生学号。
SELECT SNO FROM SCOTT.SC1 ;
由于存在一名同学选修多门课程的情况，所以查询的结果中包含了许多重复的行。如果想去掉重复的行，必须指定DISTINCT关键字。
SELECT DISTINCT SNO FROM SCOTT.SC1 ;
（2）查询满足条件的元组
查询满足指定条件的元组可以通过 WHERE 子句来实现。使用 WHERE 子句时，应该注意以下几点：
1、如果该列数据类型为字符型，需要使用单引号把字符串括起来。如：WHERE Cname=‘java’，单引号内的字符串大小写是有区别的。
2、如果该列数据类型为日期型，需要使用单引号把日期括起来。
3、如果该列数据类型为数字型，则不必用单引号。如：WHERE Age>20。
4、在WHERE子句中可以使用列名或表达式，但不能使用它的别名。
查询满足条件的元组是通过WHERE子句实现。在WHERE子句中常用的查询条件如下表所示。
| 查询条件 | 谓词 |
|---|---|
| 比 较 | =、> 、 < 、 >= 、 <= 、!= 等 |
| 确定范围 | BETWEEN AND 、NOT BETWEEN AND |
| 确定集合 | IN 、 NOT IN |
| 字符匹配 | LIKE 、NOT LIKE |
| 空 值 | IS NULL 、IS NOT NULL |
| 多重条件 | AND 、OR |
① 比较大小
【示例3-27】查询数学系全体学生的姓名。
SELECT SNAME FROM SCOTT.STUDENT1 WHERE DEPT = '数学系';
【示例3-28】查询年龄超过20岁的学生姓名及其年龄。
SELECT SNAME ,AGE FROM SCOTT.STUDENT1 WHERE AGE >20;
【示例3-29】查询考试成绩有不及格的学生的学号。
SELECT DISTINCT SNO FROM SCOTT.SC1 WHERE GRADE <60;
语句中使用了DISTINCT关键字，目的是当某一个学生有多门课程不及格时，他的学号只显示一次。
② 确定范围（谓词BETWEEN AND）
【示例3-30】查询年龄在16至20岁（包括16岁和20岁）之间的学生姓名和年龄。
SELECT SNAME ,AGE FROM SCOTT.STUDENT1 WHERE age BETWEEN 16 AND 20;
【示例3-31】查询年龄不在16至20岁之间的学生姓名和年龄。
SELECT SNAME ,AGE FROM SCOTT.STUDENT1 WHERE age NOT BETWEEN 16 AND 20;
③ 确定集合（谓词IN）
包含关系：包含 in() 不包含 not in() 一次可查询一个或多个内容
in()中的数据类型与字段的数据类型不一致
in()不能与逻辑运算符同时使用
null 空值，在表中显示为黄色填充
--注意点：in/not in 不能统计空值，工作中能不使用 not 就不使用（空值不属于任何数据类型，可以跟任何数据类型一块使用）
【示例3-32】查询计算机系、日语系和管理系的学生姓名和性别。
SELECT SNAME ,SEX FROM SCOTT.STUDENT1 WHERE DEPT IN ('计算机系','日语系','管理系');
【示例3-33】查询既不是计算机系、日语系，也不是管理系的学生姓名和性别。
SELECT SNAME ,SEX FROM SCOTT.STUDENT1 WHERE DEPT NOT IN ('计算机系','日语系','管理系');
④ 字符匹配（谓词LIKE）
模糊查询使用谓词 LIKE，NOT LIKE 可以用来进行字符串的匹配。
其中：
% (百分号)代表任意长度(长度可以为0)的字符串。
__(下横线)代表任意单个字符。有且只有一个字符
【示例3-34】查询所有姓张的学生姓名、年龄和系别名称。
SELECT SNAME ，AGE ,DEPT FROM SCOTT.STUDENT1 WHERE SNAME LIKE '张%';
如果换成NOT LIKE，表示不姓张的同学。
SELECT SNAME ，AGE ,DEPT FROM SCOTT.STUDENT1 WHERE SNAME NOT LIKE '张%';
【示例3-35】查询姓名中，第二个汉字是“七”的学生姓名和年龄。
SELECT SNAME ,AGE FROM SCOTT.STUDENT1 WHERE SNAME LIKE '_七%';
-- 可以使用 _ 来确定字符的长度，如：查询名字长度为3的
name like '___'
如果用户查询的匹配字符串本身就含有 % 或 _ ，这时就要使用ESCAPE’<换码字符>’ 短语对通配符进行转义。
转义字符：escape 自定义一个字符,使其作为转义字符使用,转义字符本身不输出，作用是使其后面跟着的第一个字符以原样输出。
一般用于转换并输出有特殊含义的字符
一个转义字符只能对应一个LIKE语句
【示例3-36】查询以“jsp_”开头，且倒数第2个字符为g的课程的详细信息
SELECT * FROM SCOTT.COURSE1 WHERE CNAME LIKE 'jsp\_%g_' ESCAPE '\';
⑤涉及空值的查询
【示例3-37】查询选修了课程，但没有成绩的学生学号和相应的课程号。
SELECT SNO ,CNO FROM SCOTT.SC1 WHERE GRADE IS NULL ;
注意：这里“IS”不能用等号 “=” 代替。
【示例3-38】查询选修了课程，并且有成绩的学生学号和相应的课程号。
SELECT SNO ,CNO FROM SCOTT.SC1 WHERE GRADE IS NOT NULL ;
⑥多重条件查询
逻辑运算符 AND 和 OR 可用来联结多个查询条件。AND 的优先级高于OR，但用户可以通过括号来改变优先级。
【示例3-39】查询日语系女同学的姓名和年龄。
SELECT SNAME ,AGE FROM SCOTT.STUDENT1 WHERE DEPT = '日语系' AND SEX = '女';
【示例3-40】查询管理系或年龄在20岁以下的学生姓名。
SELECT SNAME FROM SCOTT.STUDENT1 WHERE DEPT = '管理系' OR AGE < 20;
⑦对查询结果进行排序
ORDER BY 子句可指定按照一个或多个属性列的升序（ASC）或者降序（DESC）重新排列查询结果。省略不写，默认为升序排列。
注意：order by 子句只能用于最终的查询结果。
【示例3-41】查询选修c3课程的学生学号及成绩，查询结果按照成绩的降序排列。
SELECT CNO ,GRADE FROM SCOTT.SC1 WHERE CNO = 'c3' ORDER BY GRADE DESC ;
【示例3-42】查询所有学生的基本信息，查询结果按学生年龄的升序排列，年龄相同时则按学号降序排列。
SELECT * FROM SCOTT.STUDENT1 ORDER BY AGE ASC ,SNO DESC ;
3.4.3 使用聚组函数
聚组函数返回值是根据一组输入得到的，输入个数只有在执行完查询并且所有行都取出时才确定。
聚组函数分类：
计数
COUNT（[DISTINCT|ALL] *）-- 统计所有行数（元组个数）
COUNT（[DISTINCT|ALL] <列名>）-- 统计某一列中值的行数
计算总和
SUM（[DISTINCT|ALL] <列名>） --（返回一列值的和,该列必须是数值型）
计算平均值（返回一列值的平均值,该列必须是数值型）
AVG（[DISTINCT|ALL] <列名>）--（返回一列值的平均值,该列必须是数值型）
最大最小值（返回一列值中的最大值和最小值）
MAX（[DISTINCT|ALL] <列名>）
MIN（[DISTINCT|ALL] <列名>）
--（返回一列值中的最大值和最小值）
【示例3-43】查询学生表中的总人数。
SELECT COUNT(*) FROM SCOTT.STUDENT1 ;
SELECT COUNT(SNAME) FROM SCOTT.STUDENT1 ;
【示例3-44】查询选修了课程的学生总人数。
SELECT COUNT(DISTINCT SNO) FROM SCOTT.SC1 ;
【示例3-45】查询选修c3课程的平均成绩、最高成绩和最低成绩。
SELECT AVG(GRADE), MAX(GRADE),MIN(GRADE) FROM SCOTT.SC1 WHERE CNO = 'c3';
【示例3-46】查询学号为20230001学生选修课程的成绩总和。
SELECT SUM(GRADE) FROM SCOTT.SC1 WHERE SNO = '20230001';
3.4.4 分组查询
在SELECT语句中可以使用GROUP BY子句将查询结果按照某一列或多列的值分组，值相等的为一组，然后使用聚组函数返回每一个组的汇总信息。而且，还可以使用HAVING子句限制返回的结果集。
GROUP BY子句分组： 细化聚组函数的作用对象
- 未对查询结果分组，聚组函数将作用于整个查询结果
- 对查询结果分组后，聚组函数将分别作用于每个组
- 作用对象是查询的中间结果表
- 按指定的一列或多列值分组，值相等的为一组
【示例3-47】查询选课表中每门课程的课程号及这门课程的选修人数。
SELECT CNO ,COUNT(SNO) FROM SCOTT.SC1 GROUP BY CNO ;
【示例3-48】查询选修3门及其以上课程的学生学号
SELECT SNO FROM SCOTT.SC1 GROUP BY SNO HAVING COUNT(CNO)>=3;
使用group by 和 having子句需注意
带有group by子句的查询语句中，在select子句中指定的列要么是group by子句中指定的列，要么包含聚组函数，否则出错。
可以使用多个属性列进行分组。
聚组函数只能出现在select,having,order by 子句中。在where 子句中是不能使用聚组函数的。
HAVING子句与WHERE子句的区别是：
① WHERE子句的作用是在分组之前过滤数据。
WHERE条件中不能包含聚组函数。
使用WHERE条件选择满足条件的行。
② HAVING子句的作用是在分组之后过滤数据。
HAVING条件中经常包含聚组函数。
使用HAVING条件选择满足条件的组。
使用HAVING子句时必须首先使用GROUP BY进行分组。
3.4.5 连接查询
连接查询是指通过两个或两个以上的关系表或视图的连接操作来实现的查询。连接查询是关系数据库中最主要的查询，包括等值连接、非等值连接、自然连接、自身连接、外连接和复合条件连接等。
连接查询中用于连接两个表的条件称为连接条件或连接谓词。
一般格式为：
[<表名1.>][<列名1>] <比较运算符> [<表名2.>][<列名2>]
1、等值连接
当连接运算符为“=”时，称为等值连接。使用其他运算符时，称为非等值连接。
【示例3-49】查询每个同学基本信息及其选修课程的情况。
SELECT * FROM SCOTT.STUDENT1 ,SCOTT.SC1 WHERE STUDENT1.SNO = SC1.SNO;
SELECT STUDENT1.*,SC1.* FROM SCOTT.STUDENT1 ,SCOTT.SC1 WHERE STUDENT1.SNO = SC1.SNO;
2、自然连接
如果是按照两个表中的相同属性进行等值连接，并且在结果中去掉了重复的属性列，我们称之为自然连接。
【示例3-50】用自然连接来完成查询每个同学基本信息及其选修课程的情况。
SELECT student1.sno,sname,sex,age,dept,CNO ,GRADE FROM SCOTT.STUDENT1 ,SCOTT.SC1 WHERE student1.sno = sc1.sno;
这里就不会出现两次sno。
3、复合条件连接
连接条件用于多个表之间的连接，限制条件用于限制所选取的记录要满足什么条件，这种连接称为复合条件连接。
【示例3-51】查询选修课程号为c1，并且成绩不及格的学生学号、姓名和系别名称。
SELECT student1.SNO ,sname,dept FROM SCOTT.STUDENT1 ,SCOTT.SC1 WHERE student1.sno = sc1.sno AND CNO = 'c1'AND GRADE <60;
多个表之间连接，WHERE子句中有多个连接条件。n个表之间的连接至少由 n-1个连接条件。
4.自身连接
连接操作不仅可以在两个表之间进行，也可以是一个表与其自身进行连接，称为表的自身连接。自身连接要求必须给表取别名，把它们当作两个不同的表来处理。
【示例3-52】在SC表中查询至少选修课程号为c1和c2的学生学号。
SELECT s1.SNO FROM SCOTT.SC1 s1 ,SCOTT.SC1 s2 WHERE s1.SNO = S2.SNO AND s1.CNO = 'c1' AND s2.cno = 'c2';
5、外连接
在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。如示例-40和示例-41 的结果中没有20230010学生的信息，原因在于他没有选课，在SC表中没有相应的元组。如果想以Student表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，只输出学生的基本信息，其选课信息可以为空值，此时就需要使用外连接了。
外连接的表示方法为，在连接条件的某一边加上操作符（+）(有的数据库系统中用*)。（+）号放在连接条件中信息不完整的那一边。外连接运算符（+）出现在连接条件的右边，则称为左外连接；若出现在连接条件的左边，则称为右外连接。
【示例3-53】查询每个学生及其选修课程的情况(若某一个学生没有选课，只输出学生的基本情况信息，其选课信息为空值。)
SELECT student1.sno,sname,sex,age,dept,cno,GRADE FROM SCOTT.STUDENT1 ,SCOTT.SC1 WHERE student1.sno = sc1.sno(+);
外连接的操作符是（+）。（+）号放在连接条件中信息不完全的
那一边（即没有匹配行的那一边）。
外连接运算符（+）出现在连接条件的右边， 称为左外连接；
外连接运算符（+）出现在连接条件的左边，称为右外连接。
3.5 嵌套查询（多表查询）
一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件中的查询称为嵌套查询。
Select sname from student where sno in -- 外查询（父查询）
( select sno from sc where cno='c2'); -- 内查询（子查询）
3.5.1 子查询原则
子查询必须用括号括起来。
子查询中不能含有order by子句，只能对最终结果排序。
子查询可以在许多SQL语句中使用，如select、insert、update、delete语句中。
子查询可分成 不相关子查询 和 相关子查询
3.5.2 不相关子查询
子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。
不相关子查询的执行过程：先执行子查询，将子查询的结果作为父查询的条件，然后执行父查询。
不相关子查询的特点
Select sname from student where sno in( select sno from sc where cno='c2');
- 子查询能够独立执行,不依赖于外层父查询
- 子查询只执行一次。
- 先执行子查询，后执行父查询
1、带有IN谓词的子查询
【示例3-54】查询选修课程号为c2的学生姓名。
方法一：采用多表连接查询实现
SELECT s1.SNAME FROM SCOTT.STUDENT1 s1 ,SCOTT.SC1 s2 WHERE S1.SNO = s2.SNO AND CNO = 'c2';
方法二：采用不相关子查询实现
SELECT sname FROM SCOTT.STUDENT1 WHERE SNO IN (SELECT SNO FROM SCOTT.SC1 WHERE CNO = 'c2');
执行过程：先执行子查询，得到选修c2课程的学生学号的集合，然后将该集合作为外层父查询的条件，执行父查询，从而得到集合中学号对应的学生姓名。
【示例3-55】查询既没有选修课程号c1，也没有选修课程号c2的学生的学号
SELECT sno FROM SCOTT.SC1 WHERE sno NOT IN (SELECT SNO FROM SCOTT.sc1 WHERE cno='c1') AND sno NOT IN (SELECT SNO FROM SCOTT.SC1 WHERE CNO = 'c2');
【示例3-56】查询选修了课程名为“java”的学生学号和姓名。
(用多表连接查询来实现)
SELECT s1.sno,sname FROM SCOTT.STUDENT1 s1 ,SCOTT.sc1 s2 ,SCOTT.COURSE1 c WHERE s1.SNO = s2.SNO AND c.CNO = S2.cno AND cname = 'java';
（多层嵌套查询）
SELECT sno,sname FROM SCOTT.STUDENT1 WHERE sno IN (SELECT sno FROM SCOTT.SC1 WHERE cno IN (SELECT cno FROM SCOTT.COURSE1 WHERE CNAME = 'java'));
(1)首先在Course表中找出‘java’的课程号c2
(2)然后在SC表中找出选修了c2号课程的学生学号
(3)最后在Student表中取出Sno和Sname
2、带有比较运算符的子查询
可以用 >、<、=、>=、<=、!=或 <> 等比较运算符。
【示例3-57】查询与学号“20230001”学生在同一系别的学生学号和姓名。
SELECT sno,sname FROM SCOTT.STUDENT1 WHERE DEPT = (SELECT DEPT FROM SCOTT.STUDENT1 WHERE SNO = '20230001');
也可以用前面学习的IN谓词代替=来实现。
注意：当子查询的结果是单个值时，谓词in和“=”的作用是等价的。当子查询的结果是多个值时，只能用谓词in。
3、带有ANY谓词或ALL谓词的子查询
注意：使用any或all谓词前必须同时使用比较运算符
| 语法 | 含义 |
|---|---|
| > ANY | 大于子查询结果集中的某个值 |
| > ALL | 大于子查询结果集中的所有值 |
| < ANY | 小于子查询结果集中的某个值 |
| < ALL | 小于子查询结果集中的所有值 |
| >= ANY | 大于等于子查询结果集中的某个值 |
| >= ALL | 大于等于子查询结果集中的所有值 |
| <= ANY | 小于等于子查询结果集中的某个值 |
| <= ALL | 小于等于子查询结果集中的所有值 |
| = ANY | 等于子查询结果集中的某个值 |
| = ALL | 等于子查询结果集中的所有值（无意义） |
| <> ANY | 不等于子查询结果集中的某个值（无意义） |
| <> ALL | 不等于子查询结果集中的任何一个值 |
<>all 等价于 not in
=any 等价于 in
=all、<>any没有意义
【示例3-58】查询选修课程号为c2的学生姓名。
（IN与=ANY等价）
SELECT sname FROM SCOTT.STUDENT1 WHERE SNO =ANY (SELECT SNO FROM SCOTT.sc1 WHERE cno = 'c2');
【示例3-59】查询比所有男同学年龄都大的女同学的学号、姓名和年龄。
SELECT SNO ,SNAME ,AGE FROM SCOTT.STUDENT1 WHERE SEX = '女' AND AGE >ALL (SELECT AGE FROM SCOTT.STUDENT1 WHERE SEX = '男');
用聚组函数实现子查询通常比直接用ANY或ALL查询效率高，ANY与ALL与聚组函数的对应关系如表所示。
| ANY | ALL | |
|---|---|---|
| = | IN | 无意义 |
| <> | 无意义 | NOT IN |
| < | < MAX | < MIN |
| <= | <= MAX | <= MIN |
| > | > MIN | > MAX |
| >= | >= MIN | >= MAX |
【示例3-60】查询其他系中比数学系所有学生年龄都大的学生姓名和年龄。
SELECT SNAME ,AGE FROM SCOTT.STUDENT1 WHERE DEPT <> '数学系' AND AGE >ALL (SELECT age FROM SCOTT.STUDENT1 WHERE DEPT = '数学系');
或者大于最大的那个就是大于所有：
SELECT * FROM SCOTT.STUDENT1 WHERE DEPT <> '数学系' AND AGE > (SELECT max(AGE) FROM SCOTT.STUDENT1 WHERE DEPT = '数学系');
3.5.3 相关子查询
子查询的查询条件依赖于外层父查询的某个属性值，称这类查询为相关子查询。
相关子查询的特点:
1>子查询不能独立运行，子查询的条件依赖父查询中取的值。
2>子查询多次运行。
3>先执行父查询，后执行子查询。
1、带有比较运算符的相关子查询
【例3-61】查询所有课程成绩均及格的学生学号和姓名。
SELECT SNO ,sname FROM SCOTT.STUDENT1 WHERE (SELECT MIN(grade) FROM SCOTT.SC1 WHERE student1.sno = sc1.sno)>=60;
SELECT sno,sname FROM SCOTT.STUDENT1 WHERE 60<=all(SELECT grade FROM SCOTT.SC1 WHERE student1.sno = sc1.sno);
2、有EXISTS谓词的子查询
1. EXISTS谓词
存在量词
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空，则外层的WHERE子句返回真值
若内层查询结果为空，则外层的WHERE子句返回假值
2. NOT EXISTS谓词
若内层查询结果非空，则外层的WHERE子句返回假值
若内层查询结果为空，则外层的WHERE子句返回真值
3. 由exists引出的子查询
其目标列表达式通常用*，因为带 exists 的子查询只返回真值或假值，给出列名无实际的意义。
【示例3-62】查询选修c2课程的学生姓名。
SELECT sname FROM SCOTT.STUDENT1 WHERE EXISTS (SELECT * FROM SCOTT.SC1 WHERE CNO = 'c2' AND student1.sno = sc1.SNO);
【示例3-63】查询没有选修c2课程的学生姓名。
SELECT sname FROM SCOTT.STUDENT1 WHERE NOT EXISTS (SELECT * FROM SCOTT.SC1 WHERE student1.sno = sc1.sno AND CNO = 'c2');
【示例3-64】假设全体同学都选修了相应的课程且有成绩，那么查询所有课程成绩均大于80分的学生学号和姓名。
SELECT Sno,Sname FROM SCOTT.Student1 WHERE NOT EXISTS (SELECT * FROM SCOTT.SC1 WHERE Student1.Sno=SC1.Sno and Grade<=80);
【示例3-65】查询选修全部课程的学生姓名。
（相当于查询这样的学生，没有一门课程是他不选的。）
SELECT sname FROM SCOTT.STUDENT1 WHERE NOT EXISTS
(SELECT * FROM SCOTT.COURSE1 WHERE NOT EXISTS
(SELECT * FROM SCOTT.SC1 WHERE student1.sno = sc1.sno AND sc1.cno = course1.CNO));
3.6 空值处理
空值就是“不知道”或“不存在”或“无意义”的值。
一般有以下几种情况：
该属性应该有一个值，但目前不知道它的具体值；
该属性不应该有值；
由于某种原因不便于填写；
3.6.1 空值的产生
空值是一个很特殊的值，含有不确定性。对关系运算带来特殊的问题，需要做特殊的处理。
空值的产生是因为在插入数据时，对于某一列给了null或者''的值。
【示例3-66】将student1表中的学生号为20230001的学生所属的系改为空值。
UPDATE SCOTT.STUDENT1 SET DEPT = NULL WHERE SNO = '20230001';
SELECT * FROM SCOTT.STUDENT1;
【示例3-67】向student1表中插入一个元组，cno：'20230011',name：'刘能',性别：'男',age：NULL,dept：NULL。
INSERT INTO SCOTT.STUDENT1 VALUES ('20230011','刘能','男',NULL,NULL );
SELECT * FROM SCOTT.STUDENT1;
3.6.2 空值的判断
判断一个属性的值是否为空值，用is null 或 is not null来表示
【示例3-68】从student表中找出漏填了数据的学生信息。
SELECT * FROM SCOTT.STUDENT1 WHERE SNAME IS NULL OR SEX IS NULL OR AGE IS NULL OR DEPT IS NULL;
3.6.3 空值的约束条件
在创建表的时候，我们可以给属性定义时用 NOT NULL 来约束条件不能去空值。
或者加了 UNIQUE 限制的属性不能去空值。
码属性不能取空值。
3.6.4 空值的运算
空值与另一个值（包括另一个空值）的算数运算的结果为空值
空值与另一个值包括另一个空值）的比较运算的结果为NUKNOWN。
有UNKNOWN后，传统二值（TRUE，FALSE）逻辑就扩展成了三只逻辑
| x | y | x AND y | x OR y | NOT x |
|---|---|---|---|---|
| T | T | T | T | F |
| T | U | U | T | F |
| T | F | F | T | F |
| U | T | U | T | U |
| U | U | U | U | U |
| U | F | F | U | U |
| F | T | F | T | T |
| F | U | F | U | T |
| F | F | F | F | T |
【示例3-69】找出选修1号课程的不及格的学生(查询结果不包括空值，null)。
SELECT * FROM SCOTT.SC1 WHERE GRADE < 60 AND cno = '1';
【示例3-70】选出选修c1号课程的不及格的学生以及缺考的学生
SELECT SNO,GRADE FROM SCOTT.SC1 WHERE GRADE < 60 AND CNO = 'c1' UNION SELECT SNO,GRADE FROM SCOTT.SC1 WHERE GRADE IS NULL AND CNO = 'c1';
3.7 Oracle常用函数
函数分为字符型函数，数值型函数，日期型函数，转化函数，聚合函数，窗口函数（开窗函数）
函数的特点：
--1.函数都带有返回值，必定输出结果
--2.函数和都带有括号
| 函数 | 含义 |
|---|---|
| CHR() | 字符类函数，实现整型转字符 |
| CONCAT() | 字符串链接函数 |
| CEIL() | 数字类函数，返回指定小数的整数 |
| SYSDATE | 返回系统当前日期 |
| ADD_MONTHS() | 返回日期加上指定的月 |
SELECT chr(90),chr(72) FROM DUAL ;
SELECT CONCAT('hello','world') information FROM DUAL ;
SELECT CEIL (7.3)FROM DUAL ;
SELECT SYSDATE AS 系统日期 FROM DUAL ;
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL ;
3.7.1 转换函数
1、类型转换函数
to_char()
to_number()
to_date()
to_char( ) 转成字符型
字符型三种转换方式
1.to_char(字段,'日期格式')
2.to_char(数值) 无指定格式转换
select to_char(123),'123' from emp;
dual--临时表/伪表
select * from dual;
select to_char(123),'123' from dual;
3.to_char(数值,'格式') 指定格式转换
格式：9 --代表转换成任意数值，数值位数不够时用空格填充
0 --代表转换成任意数值，数值位数不够时用0填充
例题
select to_char(123456,'999999') from dual;--前面固定有一个空格
select to_char(123456,'000000') from dual;
select to_char(12345,'999999') from dual;--前面有两个空格
select to_char(12345,'000000') from dual;--前面有一个空格，多了一个0
select to_char(123456,'99999') from dual;--不报错，输出格式位数+1个#
select to_char(123456,'00000') from dual;--不报错，输出格式位数+1个#
select to_char(12345.678,'999999') from dual;--**12346(此处*表示空格)
select to_char(12345.678,'00000.000') from dual;--*12345.678
select to_char(12345.678,'00000.00') from dual;--*12345.68
思考：
select to_char(12345.678,'909090.9099') from dual;--**12345.6780--小数点后的格式多于数值的位数时，均以0 填充后面
select to_char(12345.678,'009090909.9090') from dual;--*000012345.6780
select to_char(12345.678,'0900909.0909') from dual;--*0012345.6780
--（小数点前面的为从后往前填充，小数点后面的的为从前往后填充）
2、货币符与千位符
货币符号和千位符
货币符号：￥ 人民币--代码：l
$ 美元 --代码：$
千位符：,(英文逗号)
--提问：要怎么么表示001？
selec '001' from dual;
select to_char(1,'000') from dual;
人民币l：
select to_char(12345.678,'L99999.999') from dual;-- ￥12345.678
select to_char(12345.678,'99999.999L') from dual;-- 12345.678￥
select to_char(12345.678,'999L99.999') from dual;--报错
select to_char(12345.678,'00000L000') from dual;-- 12345￥678
select to_char(12345.678,'L99,999.999') from dual;-- ￥12,345.678
select to_char(12345.678,'l99,999.999') from dual;-- ￥12,345.678（l不区分大小写）
select to_char(12345.678,'￥99,999.999') from dual;--报错
美元$：
select to_char(12345.678,'$99999.999') from dual;-- $12345.678
select to_char(12345.678,'99999.999$') from dual;-- $12345.678--哪怕美元符号写在格式最后，
select to_char(12345.678,'999$99.999') from dual;-- $12345.678--美元符号可以写在格式的任意位置，最后都会显示在最前面
select to_char(12345.678,'$99,999.999') from dual;-- $12,345.678
3、进制转换
1.十进制转十六进制：to_char(数值,'XXXXXXXXX')
select to_char(123456,'XXXXXXX') from dual;--1e240
2.十六进制转十进制
select to_number('1E240','XXXXXXXXXX') from dual;--123456--十六进制转十进制
4、日期转字符
to_char(字段.'日期格式')
yyyy-mm-dd 年月日
sysdate 系统时间
systimestamp 时间戳
select sysdate from dual;--年月日 时分秒 24小时制
select systimestamp from dual;--年月日 时分秒 毫秒上午/下午 时区 12小时制
select sysdate-hiredate from emp;--默认单位是天
select sysdate-1 from dual;
日期格式
1.--年
yyyy 2022
yyy 022
yy 22
y 2
yyyyyy 202222
select to_char(hiredate,'YYYYYY') from emp;--198080
2.--月
mm--08
m--报错
select to_char(sysdate,'MM') from dual;--08
3.--日
ddd--本年第几天
dd--本月第几天
d--本周第几天
select to_char(sysdate,'D') from dual;--3,说明一周默认从星期日开始算第一天
select to_char(sysdate,'DD') from dual;--16
select to_char(sysdate,'DDD') from dual;--228
4.--时
hh
hh12--12小时制
hh24--24小时制
h--报错
select to_char(sysdate,'HH12') from dual;
select to_char(sysdate,'HH24') from dual;
5.--分
mi
select to_char(sysdate,'MI') from dual;
6.--秒
ss
select to_char(sysdate,'SS') from dual;
7.--毫秒
ff--后面可以不写，也可以写1-9
select to_char(systimestamp,'FF') from dual;--默认为6位
select to_char(systimestamp,'FF1') from dual;--1位
8.--季度
q
select to_char(sysdate,'Q') from dual;--3
select to_char(sysdate,'QQQ') from dual;--333
9.--中文月份
month
mon
select to_char(sysdate,'MONTH') from dual;--8月
select to_char(sysdate,'MON') from dual;--8月
10.--周
ww--本年第几周
w--本月第几周
select to_char(sysdate,'WW') from dual;--33
select to_char(sysdate,'W') from dual;--3
select to_char(to_date('2022-07-12','YYYY-MM-DD'),'W') from dual;--2
11.--中文日
day--今天周几
dy--今天周几
select to_char(sysdate,'DAY') from dual;--星期二
select to_char(sysdate,'DY') from dual;--星期二
5、to_number()
1.to_number('字符串')把字符串前面的0去掉
select to_number('00123') from dual;
2.to_number('字符串','格式')
select to_number('123456.78','999999.99') from dual;
select to_number('123456.78','999,999.99') from dual;--报错
select to_number('123,456.78','999999.99') from dual;--123456.78
select to_number('123,456.78','999,999.99') from dual;--123456.78
--千位符如果加在前面的字符串中，后面的格式可加可不加；如果前面灭有，后面不能有
select to_number('123,456.78','99999999.99') from dual;--格式多于位数时，不会用空格填充
select to_number('123,456.78','000000.00') from dual;--123456.78
select to_number('123,456.78','00000000.000') from dual;--0会报错
select to_number('123,456.78','000000.000') from dual;--123456.78
select to_number('123,456.78','0000000.00') from dual;--说明整数部位的格式位数不能多于字符串的整数部分的位数
思考题：如果9多了会怎样？
select to_number('123,456.78','99999999.99') from dual;--123456.78
select to_number('123,456.78','999999.9999') from dual;--123456.78
select to_number('123,456.78','99999999.9999') from dual;--123456.78
--9多了都没有任何影响
select to_number('123,456.78','L999999.99') from dual;--报错
select to_number('123,456.78','999999.99L') from dual;--报错
select to_number('￥123,456.78','L999999.99') from dual;--123456.78
select to_number('￥123,456.78','999999.99L') from dual;--符号的位置需要对应
select to_number('123,456.78￥','999999.99L') from dual;--123456.78
思考题：美元呢？
select to_number('123,456.78','$999999.99') from dual;--报错
select to_number('123,456.78','999999.99$') from dual;--报错
select to_number('$123,456.78','$999999.99') from dual;--123456.78
select to_number('$123,456.78','999999.99$') from dual;--123456.78
select to_number('123,456.78$','999999.99$') from dual;--美元符号只能放在字符串前面，格式位置任意
3.将十六进制换成十进制
to_number('字符串','XXX')
select to_number('123456','XXXXXXXX') from dual;--1193046
select to_number(123456,'XXXXXXXX') from dual;--1193046
select to_number('123E56','XXXXXXXX') from dual;--1195606
select to_number('1E240','XXXXXXXX') from dual;--123456
6、to_date()
1.to_date('字符串','日期格式')
select to_date('1981','YYYY') from dual;--1981/8/1
--注意点：to_date()后面的日期八位一定要写全
--to_date在转换日期时，如果日期不全，会按照系统时间补正月日
select to_date('1981-12','YYYY-MM') from dual;--1981/12/1
select to_date('1981-12-12','YYYY/MM/DD') from dual;--1981/12/12
select to_date('19811212','YYYYMMDD') from dual;--1981/12/12
select to_date('19811212','YYYY-MM-DD') from dual;--1981/12/12
select to_date('1981-12-12','YYYYMMDD') from dual;--报错
select to_date('1981-12/12','YYYY*MM*DD') from dual;--1981/12/12
select to_date('1981-12/12','YYYY*MM+DD') from dual;--1981/12/12
--字符串中有符号的，格式中的符号必须有；字符串中没有符号，格式中可有可无
3.7.2字符型函数
1、ascii()
1.关于ascii码值和字符之间的转换
z 122 > a 97 > z 90 > a 65 > 9 57 > 0 48 > 空格 32
ascii('字符')--字符转ascii码值
chr(数值)--ascii码值转字符
select ascii('Z') from dual;
select chr('90') from dual;
select ascii('转') from dual;--15252908
select ascii(' ') from dual;--32
select ascii('ZAB') from dual;--90,只识别z
2、连接符函数concat()
concat(参数1,参数2)
concat(参数1,参数2)--括号里面只能写两个参数
select concat('1','2') from dual;
select concat('1','a') from dual;--参数数据类型可以不一致
例题：我的姓名为某某某工资为多少？
select concat(concat(concat('我的姓名为',ename),'工资为'),sal) from emp;
3、大小写转换函数
大写：upper()
小写：lower()
首字母大写：initcap()--其他全部小写
select initcap(ename) from emp;
select initcap('xu yao yao') from dual;--xu yao yao
--字符之间有空格，首字母都会大写
4、替换函数
replace('字符串','要被替换的字符','替换成的字符')
select replace('ACDDEFG','D','B') from dual;--将所有满足条件的全部替换
select replace((select to_char(123456.78,9999999999.99) from dual),' ','') from dual;--123456.78
select replace(to_char(123456.78,9999999999.99),' ','') from dual;--123456.78
select replace('ACDDEFG','D') from dual;--不会报错，去掉了d，说明如果没有替换成的字符，默认用空值代替
select replace('ACDDEFG','D','') from dual;
5、去除函数
trim('要被去除的内容' from '字符串')--去除字符串两端的内容
ltrim('字符串','要被去除的内容')--去除字符串左端的内容
rtrim('字符串','要被去除的内容')--去除字符串右端的内容
trim('字符串')--去除字符串两侧的空格
ltrim('字符串')--去除字符串左侧的空格
rtrim('字符串')--去除字符串右侧的空格
select trim(' HUANG YING JIE ') from dual;--只能去除字符串两侧的空格
select ltrim(' HUANG YING JIE ') from dual;--去除字符串左侧的空格
select rtrim(' HUANG YING JIE ') from dual;--去除字符串右侧的空格
trim('要被去除的内容' from '字符串')--去除字符串两端的内容
ltrim('字符串','要被去除的内容')--去除字符串左端的内容
rtrim('字符串','要被去除的内容')--去除字符串右端的内容
select trim('D' from 'ABCDEFG') from dual;--abcdefg
select trim('D' from 'DABCDEFGDD') from dual;--abcdefg
--只会去除字符串两侧符合条件的内容，遇到第一次不符合的内容停止
select ltrim('DADBDCDD','D') from dual;--adbdcdd
select rtrim('DADBDCDD','D') from dual;--dadbdc
--只会去除字符串左（右）侧符合条件的内容，遇到第一次不符合的内容停止
select trim('DD' from 'DABCDEFGDD') from dual;--报错
--在trim 里，要被去除的内容只能够有一个字符
select ltrim('DABCDEFGDD','DD') from dual;--abcdefgdd
select rtrim('DABCDEFGAAB','AB') from dual;--dabcdefgdd
select ltrim('ABAAABCDEFGDD','AB') from dual;--cdefgdd
--左（右）去除，只要满足条件的内容都会被去除
思考题：怎样去除'ABC DEF'中间的空格？
select replace('ABC DEF',' ','') from dual;
select replace('ABC DEF',' ') from dual;
select 'ABC'||ltrim('ABC DEF','ABC ') from dual;
6、截取函数
substr('字符串',开始位置,截取长度)
--固定从左往右截取，长度不足时全部输出
--截取长度不能小于1，不能为负数（不会报错，输出空值）
select substr('ABACDAAFEG',2,3) from dual;--bac
select substr('ABACDAAFEG',2) from dual;--bacdaafeg--如果没有截取长度，默认截取到最后
select substr('ABACDAAFEG',2,0) from dual;--空值
select substr('ABACDAAFEG',2,-1) from dual;--空值
--截取长度 <1 时，输出空
select substr('ABACDAAFEG',2,30) from dual;--bacdaafeg
--截取长度过长时，输出全部，多出的不会用空格补充
select substr('ABACDAAFEG',0,3) from dual;--aba
--开始位置 =0 时，默认从1开始
select substr('ABACDAAFEG',-1,3) from dual;--g
--开始位置 <0 时，从右往左找截取位置，截取方向仍然为从左往右
select substr('ABACDAAFEG',20,3) from dual;--空值
--开始位置大于字符串长度时，输出空值
思考题：
1.用两种方法找到名字中最后两位是th的员工信息
select * from emp where ename like '%TH';
select * from emp where substr(ename,-2,2)='TH';
select * from emp where substr(ename,length(ename)-1,2)='TH';
2.将名字中首字母为a的a全部替换成k
select replace(substr(ename,1,1),'A','K')||substr(ename,2) from emp;
select upper(replace(initcap(ename),'A','K')) from emp;
select case when ename like 'A%' then 'K'||substr(ename,2) else ename end from emp;
7、填充函数
lpad('字符串','填充后总长度','填充内容')
rpad('字符串','填充后总长度','填充内容')
select lpad(ename,10,'*') from emp;
select rpad(ename,6,'*') from emp;
select lpad(ename,2,'*') from emp;--如果填充后的总长度小于字符串长度时，会输出从第一位到总长度
select lpad(ename,10) from emp;--如果没有填充的内容，默认用空格填充
select lpad(ename,10,'好') from emp;--一个汉字占2个长度（一个汉字占3个字节），长度不够时，用空格填充
select lpad(ename,10,'好的') from emp;--填充的内容会反复填充，
8、测量长度
length('字符串')--测量字符长度
lengthb('字符串')--测量字节长度
select length('周'),lengthb('周') from dual;
select length('A'),lengthb('A') from dual;
--汉字，字母，数字，特殊符号都只占一个字符长度
--汉字占三个字节长度，字母，数字，特殊符号占一个字节长度
9、查找位置
instr('字符串','要找的内容',从第几位开始找,在字符串中出现的次数)
select instr('ABACADAA','A',2,1) from dual;--3
select instr('ABACADAA','A',2,2) from dual;--5
select instr('ABACADAA','A',3,1) from dual;--3
--如果开始的位置符合要找的内容，算作出现一次
--不论从第几位开始找，最后算位置都是从第一位开始算
select instr('ABACADAA','A',3) from dual;--没有出现次数时，默认为1
select instr('ABACADAA','A') from dual;--如果后面两个参数都没有，默认都是1
select instr('ABACADAA','A')-1 from dual;--函数能进行运算
-------------------------------
select ascii('Z')-50 from dual;
-------------------------------
select instr('ABACADAA','A',2,0) from dual;--报错
select instr('ABACADAA','A',2,-1) from dual;--报错
select instr('ABACADAA','A',2,0.5) from dual;--报错
select instr('ABACADAA','A',2,1.5) from dual;--报错
--次数必须 >=1
--出现次数为小数时，只认整数部分
select instr('ABACADAA','A',-2,1) from dual;--7
select instr('ABACADAA','A',-2,2) from dual;--5
select instr('ABACADAA','A',0,1) from dual;--0
select instr('ABACADAA','A',0.5,1) from dual;--0
select instr('ABACADAA','A',1.5,1) from dual;--1
--开始位置为负数时，从右往左算开始位置，从右往左数出现次数，结果仍然从左往右数
--开始位置为小数时，只认整数部分
select instr('ABCDEFGAADD','DEFG',4,1) from dual;--4
select instr('ABCDEFGAADD','DEFG',4,2) from dual;--0
select instr('ABCDEFGAADD','DEEFG',4,1) from dual;--0
--如果要查找的字符为多位时，按照首字母位置输出
--如果要查找的字符为多位，且不存在时，输出0
提问：查找的位置或者出现的次数很大会怎么样？查找的内容不止一个会怎么样？
select instr('ABACADAA','A',10,1) from dual;--0
select instr('ABACADAA','A',-2,10) from dual;--0
select instr('ABCDEFGAADD','DEFG',4,1) from dual;--4
select instr('ABCDEFGAADD','DEFG',4,2) from dual;--0
select instr('ABCDEFGAADD','DEEFG',4,1) from dual;--0
--多个字符当一个整体，整体作为一个位置
--如果要查找的字符为多位时，按照首字母位置输出
--如果要查找的字符为多位，且不存在时，输出0
思考题：
1.zhangsan@qq.com现在不知道@前面有多少位，求@前面的内容，至少有三种方法
select replace('ZHANGSAN@QQ.COM','@QQ.COM') from dual;
select rtrim('ZHANGSAN@QQ.COM','@QQ.COM') from dual;
select substr('ZHANGSAN@QQ.COM',1,length('ZHANGSAN@QQ.COM')-7) from dual;
select substr('ZHANGSAN@QQ.COM',1,instr('ZHANGSAN@QQ.COM','@',1)-1) from dual;
2..zhangsan@qq.com现在不知道@前后有多少位，求@前面的内容，至少有三种方法
select substr('ZHANGSAN@QQ.COM',1,instr('ZHANGSAN@QQ.COM','@',1)-1) from dual;
3.7.3数值型函数
1、取绝对值函数
abs(数值/字段)
select abs(-12345) from dual;
select abs(0) from dual;--0的绝对值是0
select abs(123+456) from dual;--579
select abs('123'+'456') from dual;--579--发生了隐式转换
select '123'+456 from dual;--579
2、判断正负
sign(数值)
select sign(123456) from dual;--(1)
select sign(-123456) from dual;--(-1)
select sign(0) from dual;--0
select sign('') from dual;--空值
select sign(123*456) from dual;--(1)
3、取整函数
ceil(数值)--向上取整--只要小数点后面有值，不论大小都进一
floor(数值)--向下取整--舍弃小数点后的全部内容
select ceil(198.56) from dual;--(199)
select ceil(-198.56) from dual;--(-198)
select floor(198.56) from dual;--(198)
select floor(-198.56) from dual;--(-199)
4、取余函数
mod(被除数,除数)
select mod(198,5) from dual;--3
select mod(16,4) from dual;--0
select mod(-16,3) from dual;--(-1)
select mod(16,-3) from dual;--1
select mod(16,0) from dual;--16
select mod(0,16) from dual;--0
--结果的符号和被除数是一致的
5、四舍五入
round(数值,精度)--按照精度对数值进行四舍五入
select round(123456.789) from dual;--没有精度时，默认精确到个位
select round(123456.789,0) from dual;--精度为0时，精确到个位
select round(123456.789,1) from dual;--123456.8
select round(123456.789,-1) from dual;--123460
select round(123456.789,-4) from dual;--120000
select round(123456.789,-6) from dual;--0
select round(523456.789,-6) from dual;--1000000
select round(523456.789,2.9) from dual;--523456.79
select round(523456,1) from dual;--523456.79
--1.精度不写或为0时，默认精确到个位
--2.精度为正数时，精确到小数点后几位（数值不带小数时，输出本身）
--3.精度为小数时，不会报错，但精度的小数位不会被识别
--4.精度为负数时，精确到小数点前n+1位，四舍为0，五入为1
--5.当数值里的小数部分的长度小于精度时，不会用0填充
--当数值里的小数部分的长度等于精度时，输出本身
--当数值里的整数部分的长度小于精度的绝对值时，输出0
--当数值里的整数部分的长度大于精度的绝对值时，四舍五入后加0
--6.没有到达指定精度时，不会补充0
select round(567.89,1),round(567.89,0),round(567.89,-9),round(567.89,-2),round(567.89),round(-567.89,1),round(-567.89,-1) from dual;
--567.9 568 0 600 568 -567.9 -570
6、截断函数
trunc(数值,精度)--按照精度对数值进行截断操作（舍去精度后的值）
select trunc(123456.789) from dual;--没有精度时，默认精确到各位
select trunc(123456.789,0) from dual;
select trunc(123456.789,1.5) from dual;--123456.7
select trunc(123456.789,-1) from dual;--123450
select trunc(123456.789,-4) from dual;--120000
select trunc(123456.789,-7) from dual;--0
select trunc(523456.789,-6) from dual;--0
--1.精度不写或为0时，精确到个位，小数点后全部舍去
--2.精度为正数时，数值带小数的会精确到小数点后几位，精度后的直接而舍弃
--当数值里的小数部分的长度小于精度时，不会用0填充
--数值不带小数或小数部分的长度等于精度时，输出数值本身
--3.精度为负数时，精确到小数点前几位
--当数值里的整数部分的长度小于或等于精度的绝对值时，会输出0
--4.精度为小数时，不会报错，但精度的小数位不会被识别
7、round / trunc 对日期的截断
select round(sysdate)-1/24/60/60 from dual;--昨天最后一秒
select trunc(sysdate) from dual;--取得当天00:00:00
select trunc(sysdate)+1-1/86400 from dual;--取得当天23：59：59
--日期精度默认单位为天/'dd'
--系统默认一个星期的第一天为星期天
select round(sysdate),trunc(sysdate) from dual;--2022/8/19 , 2022/8/18
select round(sysdate,'DD'),trunc(sysdate,'DD') from dual;--2022/8/19 , 2022/8/18
--节点：11：59：59/12:00:00
select round(sysdate,'MM'),trunc(sysdate,'MM') from dual;--2022/9/1, 2022/8/1
--节点：15号 23：59：59/16号 00：00：00
select round(sysdate,'YYYY'),trunc(sysdate,'YYYY') from dual;--2023/1/1 , 2022/1/1
--节点：7月1号 00：00：00/6月30号 23：59：59
select round(sysdate,'DAY'),trunc(sysdate,'DAY') from dual;--2022/8/21 , 2022/8/14
--节点：周三 11：59：59/周三 12：00：00
select round(sysdate,'Q'),trunc(sysdate,'Q') from dual;--2022/10/1, 2022/7/1
--节点：每个季度中间月份的15号 23：59:59/16号 00：00：00（一个月默认30天）
select round(to_date('2022-08-17 20-00-00','YYYY-MM-DD HH24-MI-SS') )from dual;--2022/8/18
select round(to_date('2022-02-15 13-59-59','YYYY-MM-DD HH24-MI-SS'),'Q' )from dual;--2022/1/1
--精确到天(dd)，界限是12点整
--精确到周(day)，节点为星期三12点整
--精确到月(mm)，界限是16号，（2月的界限也是16号）
--精确到季度(q)，界限是中间月份的16号
--精确到年(yyyy)，界限是7月1号
--1.当以’dd‘为精度时，天时间过半到明天的0点，不过半到今天的0点，以当天11：59：59/12：00：00为节点
--2.当以’day‘为精度时，周时间过半到下周的第一天，不过半到本周的第一天，以周三 11：59：59/12：00：00为节点
--3.当以’mm‘为精度时，月时间过半到下个月的第一天，不过半到本月的第一天，以每月15号 23：59：59 /16号 00：00：00为节点
--4.当以’q‘为精度时，季度时间过半到下个季度的第一天，不过半到本季度的第一天，以每个季度中间月份的15号 23：59：59 /16号 00：00：00为节点
--5.当以‘yyyy’为精度时，年时间过半到下一年的第一天，不过半到本年的第一天，以六月三十日 23：59：59和七月一日 00：00：00 为节点
3.7.4日期函数
1、求两个日期之间的相差几个月
months_between(日期1,日期2)--日期1 - 日期2
select months_between(sysdate,hiredate) from emp;
select months_between(hiredate,sysdate) from emp;--差值可以为负数
select months_between(to_date('2022-08-31','YYYY-MM-DD'),to_date('2022-02-28','YYYY-MM-DD')) 差值 from dual;--6
select months_between(to_date('2022-08-28','YYYY-MM-DD'),to_date('2022-02-28','YYYY-MM-DD')) 差值 from dual;--6
select months_between(to_date('2022-08-29','YYYY-MM-DD'),to_date('2022-02-28','YYYY-MM-DD')) 差值 from dual;--6.03225806451613
select months_between(to_date('2022-08-30','YYYY-MM-DD'),to_date('2022-02-28','YYYY-MM-DD')) 差值 from dual;--6.06451612903226
--小数部分按 1/31 算一天的差值
--两个月底相减、日期相同相减会得到整数
2、求本月最后一天
last_day(日期)
select last_day(sysdate) from dual;
select last_day(to_date('2022-08-18','YYYY-MM-DD')) from dual;
select last_day(sysdate)+1 from dual;
3.增加（减少）月份
add_months(日期,数值)
select add_months(sysdate,3) from dual;
select add_months(sysdate,-3) from dual;
select add_months(sysdate,-3.14) from dual;--小数部分不识别
4、找下一个星期几是几月几号
next_day(日期,'星期几')
select next_day(sysdate,'星期五') from dual;
select next_day(sysdate,'星期日') from dual;
select next_day(sysdate,'星期四') from dual;--如果当天符合条件，也是输出下一个
select next_day(sysdate,2) from dual;--下一个星期的第二天
select next_day(sysdate,8) from dual;--报错（周中的日无效）
select next_day(sysdate,6) from dual;--2022/8/19 17:16:06
--1-5可以从下一周开始找，6-7只会从本周开始找
思考题：给定一个日期，假如是2022-06-17，求那个星期的星期四
select trunc(to_date('2022-06-17','YYYY-MM-DD'),'DAY')+4 from dual;
select next_day(trunc(to_date('2022-06-17','YYYY-MM-DD'),'DAY'),'星期四') from dual;
select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff9') from dual;--2022-08-04 16:00:09 445000000
--时间戳默认ff6，最高精确到ff9
3.7.5 聚合函数
sum()--求和
avg()--平均值
max()--最大值
min()--最小值
count(1)--计数--新生成一个全是1的列，然后统计行数，空值也会计算（忽略所有列，用1代表代码行，不会忽略空值）
count(*)--计数--统计表中最长的列的行数，空值也会计算（包括所有列，相当于行数，不会忽略空值）
count(字段)--单列计数--（只包括列名那一列，会忽略空值）
3.7.6 函数总结
一、字符型函数：
1.asciii值与数值的转换 ascii(字符)
chr(数值)
2.连接符函数 concat(参数1,参数2)
3.大小写转换 upper(字符串)
lower(字符串)
initcap(字符串)
4.替换函数 replace(字符串)
5.去除函数 trim(要被去除的内容 from 字符串)
ltrim(字符串,要被去除的内容)
rtrim(字符串,要被去除的内容)
6.截取函数 substr(字符串,开始位置,截取长度)
7.填充函数 lpad(字符串,填充后总长度,填充内容)
rpad(字符串,填充后总长度,填充内容)
8.测量函数 length(字符串)
lengthb(字符串)
9.查找函数 instr(字符串,要查找的内容,开始位置，出现的次数)
二、数值型函数
1.取绝对值函数 abs(数值)
2.判断正负 sign(数值)
3.取整函数 ceil(数值)--向上取整
floor(数值)--向下取整
4.取余函数 mod(被除数,除数)
5.四舍五入 round(数值,精度)
6.截断函数 trunc(数值,精度)
7.对日期的操作 round / trunc
三、日期型函数
1.求两个日期之间的相差几个月 months_between(日期1,日期2)
2.求本月最后一天 last_day(日期)
3.增加(减少)月份 add_months(日期,数值)
4.找下一个星期几是几月几号 next_day(日期,'星期几')
