sql总结（一）

一.隐式转换

根据oracle官方的说明，有如下隐式转换：

1.  对于INSERT和UPDATE操作，ORACLE会把插入值或者更新值隐式转换为对应字段的数据类型。
2.  对于SELECT语句，ORACLE会把字段的数据类型隐式转换为变量的数据类型。
3.  当处理数值时，ORACLE通常会调整精度和小数位，以实现最大容量。在这种情况下，由此类操作产生的数字数据类型可能与在基础表中找到的数字数据类型不同。
4.  当比较一个字符型和数值型的值时，ORACLE会把字符型的值隐式转换为数值型。
5.  字符值或NUMBER值与浮点数值之间的转换可能不准确，因为字符类型和NUMBER使用十进制精度表示数字值，而浮点数则使用二进制精度。
6.  将CLOB值转换为字符数据类型（例如VARCHAR2）或将BLOB转换为RAW数据时，如果要转换的数据大于目标数据类型，则数据库将返回错误。
7.   当timestamp类型转换为DATE时（按照第三条，隐式转换不应该把timestamp转换为date，除非insert这样的），timestamp后几位会被truncated忽略，至于忽略几位，取决于数据库版本。
8.  从BINARY_FLOAT到BINARY_DOUBLE的转换是准确的。
9.  从BINARY_DOUBLE到BINARY_FLOAT的转换是不精确的，因为BINARY_DOUBLE精度更高。
10.  当比较字符型和日期型的数据时，ORACLE会把字符型转换为日期型。
11. 如果调用函数（过程）或运算符操作时，如果输入参数的数据类型与函数（存储过程）定义的参数数据类型不一致或不是可接受的数据类型时，则ORACLE会把输入参数的数据类型转换为函数或者过程定义的数据类型。
12. 当使用赋值符号（等号）时，右边的类型转换为左边的类型
13. 当连接操作（concatenation，一般为||）时，ORACLE会隐式转换非字符型到字符型
14. 如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算，则ORACLE会将字符类型的数据转换为合适的数据类型，这些数据类型可能是number、date、rowid等。
  如果CHAR/VARCHAR2 和NCHAR/NVARCHAR2之间作算术运算，则ORACLE会将她们都转换为number类型的数据再做比较。
15. 比较CHAR/VARCHAR2 和NCHAR/NVARCHAR2时，如果两者字符集不一样，则默认的转换方式是将数据编码从数据库字符集转换为国家字符

其中涉及到前几天的一道考试判断题：

oracle中所有的数值型字符串都要加单引号

根据隐式转换第2条即可判断，当我们在字符产等式输入数值型字符串不加单引号时，数据库会将数值类型隐式转换，不影响结果，但是会降低效率。

二.exists 和 in 、having和where、相关与非相关子查询 在 oracle中的区别

这是面试、考试常考题

对于exists和in来说，作为多表连接查询可以决定关键速度的一项关键字.注定产生不同

1.判断方式不同

首先是exists和in的判断方式不同

in是在一堆条件中做等号连接，一个个判断，在此之前，in需要把后面结果集的结果全部查出再进行一一匹配.

即当结果集中有m条数据，in要先查m次得到结果，再匹配m次结果.

select * from emp where deptno in (select deptno from dept);

exists是一种外层的重复循环，每次查询都会进行一次判断，当语句内能够返回时时，条件为真，就返回该条语句，反之跳过

SELECT * FROM EMP WHERE EXISTS (SELECT 1 FROM EMP);
SELECT * FROM EMP WHERE EXISTS (SELECT * FROM EMP WHERE EMPNO = 0);

当结果集条件总为真，即总是返回该条记录

当结果集条件无法满足，即总是跳过该条记录

即当结果集有m条数据，exists将会一条一条将结果集中的记录取出进行判断，共判断m次.

2.限制条件不同

in的使用需要指定该字段，或者指定整体字段,即需要在in前指定唯一整体

SELECT * FROM EMP WHERE DEPTNO NOT IN(10,20,NULL);
SELECT * FROM EMP WHERE (DEPTNO,JOB) IN ((10,'CLERK'),(20,'MANAGER'));

当使用子查询返回结果返回多值时，in限制只能用一个字段

SELECT * FROM STUDENT WHERE SNO IN(SELECT SNO FROM SCORE);
SELECT * FROM STUDENT WHERE SNO IN(SELECT SNO,CNO FROM SCORE);--ERROR

exists不需要指定字段，且不受到子查询结果字段唯一的限制

exists 会避免 not in 和null值带来的问题

 select empno,ename,sal,mgr from emp e where not exists(select 1 from emp a where e.mgr = a.mgr);
 select empno,ename,sal,mgr from emp e where mgr not in(select mgr from emp a);--没有结果

由于存在mgr为空的员工，而in无法判断所以只能用exists.

3.适用场景不同

大家总是说exists优于in，事实上大部分是

但是存在即合理，in最明显的优势就是对新手友好，它不需要初学者了解循环逻辑就可以去在结果集中判断返回值.

这里引入其他编程语言的复杂度概念

我们都知道，程序的好坏有一部分受到时间复杂度和空间复杂度的影响.

sql总是在语句上追求时间复杂度低的方法.

作为多表连接查询

SELECT * FROM A WHERE B IN (SELECT B FROM C WHERE A.X=C.X);

当子查询结果集很大，而外部表很小时，如果用in，需要先将主查询挂起，将所有的结果集得出，再进行一次判断，这样相对于exists的 block nested loop 的嵌套循环能力来说，会显得很无助，exists的查询效率会优于in.

当子查询结果集很小，而外部表很大时，这时候exists的嵌套查询优化能力就会黯然失色。in的外表挂起索引会发挥出优势，这时in的查询效率会高于exists

2.where和having在oracle中的区别

where和having作为sql语句的关键字，都具有条件过滤的作用，主要有如下区别：

1）过滤时机不同：where在group by 前进行过滤即where先执行、而having在group by后过滤即having后执行

2）可跟内容不同：where后不可以跟聚合函数，having可以跟where所有操作符、也可以跟聚合函数

3）过滤内容不同：where过滤行，having过滤列

3.相关子查询与非相关子查询在oracle中的区别

1）处理次数不同

相关子查询不只处理一次，需要反复通过参照列进行求值，以供主查询使用

非相关子查询只处理一次，当内层得到结果即返回给外层

2）依赖不同

相关子查询的查询条件依赖于外层查询的某个值

非相关子查询是独立于主查询的子查询，不依赖于外层的值

3）效率不同

相关子查询可以多次嵌套，但是越嵌套效率越低

非相关子查询也可以嵌套，但效率略高于相关子查询

三.时间的真正格式、sql的转换说明

1.如果我们使用函数对日期进行选择性输出

select substr(hiredate,1) from emp;

结果如图所示：

我们知道日期的格式是：yyyy/mm/dd hh24/mi/ss

但是从图中我们知道日期在数据中真正的排列顺序是dd/mm/yyyy

所以在用函数时得到的结果：dd（不足两位补2位）/mm月/yy(两位末尾数字)

2.关于oracle数据类型转换

     怎么说呢 画图比较简单明了

在Oracle三种数据类型、三种转换函数、四种转换 方式中

任何类型都可以互相转换，举例如下：

SELECT TO_DATE(TO_CHAR(1234,'9999'),'YYYY') FROM DUAL;
SELECT TO_NUMBER('123.123','999.999') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
SELECT TO_CHAR(1234.123,'9999.999') FROM DUAL;

  ORACLE的三种数据类型，四种转换方式，其中TO_CHAR作为中间桥梁可以转两种，其余number和date各自一种，可以通过char这个桥梁来连接转换

此外，当TO_CHAR转的时候如果format里面整数不够前面number的数字会输出#####

四.union的使用及部分感悟

使用union可以将两个结果集合并成一个结果集，

select ...

union

select...

当使用union时，两个结果集重复的数据将只出现一遍

而如果使用union all时

select ...

union all

select...

union all 会将所有的数据进行拼接，包括重复的数据会多次出现

此外 在使用查询语句来创建表时，只会复制表的结构但是并不会复制主外键

2022.7.2

 从周四开始就没怎么学，周末又觉得懈怠也没事，人总会给自己找借口，将零碎的一些问题记录在这篇文章吧，明天加班再写两篇博客，但愿被我浪费的时光能少些，焦虑少一些.

尽管老师一直说，子查询以后只有在特定的情况下才能遇到，大部分都是用的表连接，但是实际上我个人的感受是子查询是必不可少的一部分内容

从初学者就开始知道数据的来源有三种途径：表，子查询，试图

子查询让我深刻的感受到了查询项与数据来源的角色互换性，突破语法的约束更加灵活的实现目标

并感受到嵌套性质的逻辑思维.

1.SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO

首先是聚合函数的嵌套，以前只知道聚合函数只有返回单个值的函数可以嵌套（也就是单行单列的值），在了解了子查询后就明白了，聚合函数的嵌套相当于一个微小、内置的子查询，内层子查，外层主层，如果内层返回多个值自然会导致外层不知道选择哪个，报错.

时刻清楚的知道你的内层返回的数据有哪些，作为表的话，哪些能用，哪些不能用.

2.之前学group by的时候总觉得如果有having，group by 就也得出现

这是错误的：

反例一： 

具体原因待补充

反例二：

当出现参照列条件时 即： TABLE1.CLOUMN_NAMEX=TABLE2.CLOUMN_NAMEX

使用having不再需要group by ，最初我认为是这个参照列会分组，后来老师跟我说是因为在参照列出现时，由于数据一条一条的读取，不再需要分组...

总之就是参照列可以代替group by

3.当sql语句被规则束缚，如果不是觉得思路问题，就应该考虑到多层嵌套，改变数据的方向，数据不仅可以出现在条件后作为满足项，也可以通过作为返回值来换位调试

4.查询公司内没有员工的部门
正解：select * from EMP dept where deptno not in(select distinct deptno from emp)

我的答案： SELECT DEPTNO FROM EMP HAVING COUNT(EMPNO) = 0 GROUP BY DEPTNO;//这是错误的

并不是因为语法错误导致题目做不出来，它告诉我，我失去了最本质的：应用

试想一个员工表里的数据，他没有EMPNO,ENAME 它却有其他属性，这道题我的方法确实可以做出来，但是这样的数据不会存在，是垃圾数据，我已经在sql的路上渐行渐远，忘记了数据库的本质.

五.关于字符转换和日期转换函数

1.关于日期转换问题

查询入职时间在1980年或1981年的员工信息

SELECT * FROM EMP WHERE '1980'=TO_CHAR(HIREDATE,'YYYY') OR '1981'=TO_CHAR(HIREATE,'YYYY');

TO_CHAR 中左边是字符串，右边也是字符串，左边是已有的字符串，肯定要加引号

左边是日期，如果右边一开始就加引号，那还要他转什么char？

而且tochar不会增加默认值，不需要补齐mm/dd

就算他有默认你也不能输入，因为前后参数要求一致，前面只有年后面就只写yyyy

查询入职时间在1980年1月1日到1981年12月31日之间的员工信息
SELECT * FROM EMP WHERE HIREDATE BETWEEN TO_DATE('1980','YYYY') AND TO_DATE('1981','YYYY');

同理左边是日期，右边肯定加引号，因为就是把字符串转日期，前后参数要求一致，前面只有年后面就只写yyyy

这里涉及到了两个方法 TO_CAHR和TO_DATE

TO_CHAR 是将日期转成字符

TO_DATE 是将字符转成日期

两者的作用，都可以使用等式匹配数值进行查询操作

需要注意的是 当to_date的格式不满足‘yyyy/mm/dd’时 会设置默认值的月和日，而to_char不会：

select to_date('1980','yyyy') from emp;

select to_char(hiredate,'yyyy') from emp;

这时我们看向第一题，如果使用to_date则会运行不出结果，原因是在判断=时，你以为只匹配年，实际该方法设置了默认值，所以导致没有结果。

而to_char则没有默认值，所以这时要使用to_char进行判断。