本文介绍了SQL中的转换函数如To_char,To_date,To_number，展示了如何将数值、日期和空值进行格式化转换，并讲解了NVL,NVL2,NULLIF等函数以及分组函数、JOIN操作、集合操作符（UNION,UNIONALL,INTERSECT,MINUS）和表列重命名的方法。
转换函数：将值从一种数据类型转换成另一种数据类型。
转换函数：
To_char
To_date
To_number
--to_char（）数值转字符，将33转换成货币，L代表人民币，99.99代表保留小数的格式。
SQL> select to_char(33,'L99.99') from dual;
TO_CHAR(33,'L99.
----------------
￥33.00
--to_number（）字符转数值，将‘123’
SQL> select to_number('123') from dual;
TO_NUMBER('123')
----------------
123
--如果里面不是数值就没有办法转换。
SQL> select to_number('123ab') from dual;
select to_number('123ab') from dual
*
第 1 行出现错误:
ORA-01722: 无效数字
--转成字符to_cahr（日期）
SQL> SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日"HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'YYYY"
----------------------
2024年1月07日17:37:27
--decode()函数
以下是几个用来转换空值的函数：
nvl，第一为空返回二；否则返回一。
Nvl2，第一个不空则返回二；否则返回三。
nullif，两个表达式，相等则返回空；否则第一个。
--nvl和nvl2的使用
SQL> select nvl(2,1) from dual;
NVL(2,1)
----------
2
SQL> select nvl('',1) from dual;
N
-
1
SQL> select nvl2(1,2,3) from dual;
NVL2(1,2,3)
-----------
2
SQL> select nvl2('',2,3) from dual;
NVL2('',2,3)
------------
3
--nullif()相等就返回空，不相等返回第二个
SQL> select nullif(2,2) from dual;
NULLIF(2,2)
-----------
SQL> select nullif(1,2) from dual;
NULLIF(1,2)
-----------
1
--分组函数
分组函数基于一组行来返回结果
为每一组行返回一个值
分组函数
avg,min,max.sum,count
Select avg(re_level) from itemfile where p_category=’accessories’;
Select max(max_level) from itemfile;
Select sum(itemtate*max_level) from itemfile;
Select count(*) from itemfile;
Select count(itemrate) from itemfile;
Select count(distinct qty_hand) from itemfile;
--group by 和 having
group by 子句
用于将信息划分为更小的组
每一组行返回针对该组的单个结果
Having子句
用于指定group by 子句检索行的条件‘
多表查询
--先查一下之前建的表，删除掉student表
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$8CrIcMjSSimvyjNonk5/2Q==$0 TABLE
BIN$d/gqhAhvTfeybTubaHIIiA==$0 TABLE
BIN$yBLGNzQwT6O8Q2TbYju47g==$0 TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
STUDENT TABLE
T1 TABLE
T2 TABLE
T3 TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T4 TABLE
成绩 TABLE
已选择13行。
SQL> drop table student purge;
表已删除。
--再删除t1,t2,t3看看还有几个表。
SQL> drop table student purge;
表已删除。
SQL> drop table t1 purge;
表已删除。
SQL> drop table t2 purge;
表已删除。
SQL> drop table t3 purge;
表已删除。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$8CrIcMjSSimvyjNonk5/2Q==$0 TABLE
BIN$d/gqhAhvTfeybTubaHIIiA==$0 TABLE
BIN$yBLGNzQwT6O8Q2TbYju47g==$0 TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
T4 TABLE
成绩 TABLE
已选择9行。
--把t4、成绩也删除掉
SQL> drop table t4 purge;
表已删除。
SQL> drop table 成绩 purge;
表已删除。
--建立新的student表和address表。
create table student(sno number(6),sname varchar2(10),sage int);
SQL> insert into student values(1,'张三',21);
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into student values(2,'张飞飞',22);
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into student values(3,'王五',23);
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into student values(4,'赵云',24);
已创建 1 行。
SQL> commit;
提交完成。
SQL> create table address(sno number(6),zz varchar2(10));
表已创建。
SQL> insert into address values(1,'郑州')；
2
SQL> insert into address values(1, '郑州');
已创建 1 行。
SQL> insert into address values(2, '开封');
已创建 1 行。
SQL> insert into address values(3, '洛阳');
已创建 1 行。
SQL> insert into address values(5, '新乡');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from address;
SNO ZZ
---------- ----------
1 郑州
2 开封
3 洛阳
5 新乡
--等值连接两个表
SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno=a.sno;
SNO SNAME SNO ZZ
---------- ---------- ---------- ----------
1 张三 1 郑州
2 张飞飞 2 开封
3 王五 3 洛阳
--也可以使用 inner join内连接
SQL> select s.sno,s.sname,a.sno,a.zz from student s inner join address a on s.sno=a.sno;
SNO SNAME SNO ZZ
---------- ---------- ---------- ----------
1 张三 1 郑州
2 张飞飞 2 开封
3 王五 3 洛阳
--左外连接，student为左表，address为右表。
第一种左外连接方式，where条件后面的右表加（+） ，左表的信息全显示，右表显示匹配的记录。
SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno=a.sno(+);
SNO SNAME SNO ZZ
---------- ---------- ---------- ----------
1 张三 1 郑州
2 张飞飞 2 开封
3 王五 3 洛阳
4 赵云
--使用left join on也可以实现。
SQL> select s.sno,s.sname,a.sno,a.zz from student s left join address a on s.sno=a.sno;
SNO SNAME SNO ZZ
---------- ---------- ---------- ----------
1 张三 1 郑州
2 张飞飞 2 开封
3 王五 3 洛阳
4 赵云
--右外连接，和左外连接相反在左表处加上（+），或者直接right join on。
SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno(+)=a.sno;
SNO SNAME SNO ZZ
---------- ---------- ---------- ----------
1 张三 1 郑州
2 张飞飞 2 开封
3 王五 3 洛阳
5 新乡
SQL> select s.sno,s.sname,a.sno,a.zz from student s right join address a on s.sno=a.sno;
SNO SNAME SNO ZZ
---------- ---------- ---------- ----------
1 张三 1 郑州
2 张飞飞 2 开封
3 王五 3 洛阳
5 新乡
--全连接full outer join on（只有这一种写法）
SQL> select s.sno,s.sname,a.sno,a.zz from student s full outer join address a on s.sno=a.sno;
SNO SNAME SNO ZZ
---------- ---------- ---------- ----------
1 张三 1 郑州
2 张飞飞 2 开封
3 王五 3 洛阳
5 新乡
4 赵云
--集合操作符
Union
Union all
Intersect
Minus
union和union all的区别是：union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。
可以在最后一个结果集中指定Order by子句改变排序方式。
--使用union
SQL> select * from student union select * from student where sno=1;
SNO SNAME SAGE
---------- ---------- ----------
1 张三 21
2 张飞飞 22
3 王五 23
4 赵云 24
--使用union all
SQL> select * from student union all select * from student where sno=1;
SNO SNAME SAGE
---------- ---------- ----------
1 张三 21
2 张飞飞 22
3 王五 23
4 赵云 24
1 张三 21
--minus 操作符返回从第一个查询结果中排除第二个查询中出现的行，minus不能少减多。
Select orderno from order_master minus select orderno from order_detail;
SQL> select * from student minus select * from student where sno=1;
SNO SNAME SAGE
---------- ---------- ----------
2 张飞飞 22
3 王五 23
4 赵云 24
SQL> select * from student where sno=1 minus select * from student;
未选定行
--intersece取交集
SQL> select * from student intersect select * from student where sno=1;
SNO SNAME SAGE
---------- ---------- ----------
1 张三 21
重命名
重命名表：rename table_name1 to table_name2;
SQL> rename student to newstudent;
表已重命名。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ADDRESS TABLE
BIN$8CrIcMjSSimvyjNonk5/2Q==$0 TABLE
BIN$d/gqhAhvTfeybTubaHIIiA==$0 TABLE
BIN$yBLGNzQwT6O8Q2TbYju47g==$0 TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
NEWSTUDENT TABLE
SALGRADE TABLE
已选择9行。
--重命名列
Alter table table_name rename column col_oldname to colnewname;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
