关于SQL的一些知识点

一、先将数据库的日期显示设定为显示全部信息，再实验使用日期函数的情况：

先通过alter session的方式对数据库的日期显示格式进行设置：

 

开始试验所有的日期函数：

1.trunc函数：

 

可以得到trunc函数可以对日期截取到以分为单位，但是trunc函数无法对秒级的日期进行操作；

2last_day函数

 

显然，last_day函数会以天为单位得到当月最后一天的日期，不会对时分秒进行处理；

3.next_day函数：

同样，next_day函数可以求出以当前日期为准，下个星期的某一天是几月几号，但是，依然不会对时分秒进行操作；

4.add_months函数：

 

以sysdate为例，可以看出add_months函数同样不会对数据进行时分秒级的处理，并且，对于add_months需要注意的一点是，此函数并不是简单的将日期加30天，而是加1个月，如下：

由以上结果可知，

1.      Add_months函数中第二个参数为1，就是增加1个月，参数为-1，则减少1个月；

2.      使用add_months函数时增加的是月份而不是天数，如1月9日增加1个月是2月9日，但是1月31日增加1个月是2月28日（2018年是非闰年，2月只有28天），而不是3月2日。

3.      使用add_months函数时，如果当前的日期已经超过了下个月的最大日期，则只会返回下个月最后一天，如：1月30日和1月31日都超过了28，但是使用函数后，只增加到了2月28日。

4.      在add_months函数第二个参数为负值时，如果当前的日期超过了上个月的最后一天，则会返回上个月最后一天的日期，如3月31日和3月30日的日期都超过了28，但是使用函数后，依然返回了2月28日，通过次实验更能看出，add_months函数增加和减少的是月份而不是天数；

5.      在add_months函数第二个参数为负值时，如果当前月的最后一天比上个月的最后一天小，会返回上个月的最后一天，如：2月份最后一天是28日，在使用了add_months函数后，结果返回了1月31日；

通过以上结论，可以知道，add_months函数在执行时，是以月为单位的，而不是以天为单位的；

5.round函数：

 

由实验结果可知，round函数对日期进行操作时：和trunc函数一样，无法对秒级的数据进行操作，但是round函数在进行舍入操作时和正常的情况有一点不同：

1.      对年进行舍入操作时，以月份的6舍7入为标准；

2.      对月份进行舍入操作时，以日的15舍16入为标准（2月份依然通用）

3.      对日进行舍入操作时，以周一至周三，周四至周日进行取舍；

当前日期时1月24日是周三，由于采用周一至周三舍去的原则，函数使用后返回的当前的周一；

4.      对时、分进行舍入操作时，都是按照下一级以30为分界点，大于30就入，小于30就舍去的规则执行；

二、A、B两张完全相同的表，按照A表为B表插入数据

如果表中有主键约束，由于主键约束非空且唯一，所以这种情况比较简单(假设主键列是id)

Insert into b

SELECT * FROM aWHERE id NOT IN (SELECT id FROM b);

将A表和B表的id列进行比较，若A表ID列中有B表没有的值，则将A表中的数据插入到B表中。

由于表中ID列有主键约束，所以他们的ID列的值是非空且唯一的，通过将B表中的ID列和A表中的ID列进行对比，为B表插入数据。

由于谓词in的参数允许有多个，所以可以将B表id列全部查询出来，与A表的id列进行比较。

Where关键字后可以得到true、false、unknown三个值（unknown先不讨论），只有where得到true，SELECT语句才会输出数据，在谓词in前加上not关键字，即对比较出来的结果进行否定，所以当比较出结果为false，则where语句返回true，此行才会被SELECT语句选中并输出。

如果没有主键约束，有两种方法可实现

1.      insert into B

SELECT * FROM aWHERE id not in (select id from b where id is not null);

2.      insert into B

SELECT * FROM a where not exists(select * from b wherea.id=b.id);

第一种方法，此时表中没有主键列，所以对id列进行比较时，需要处理列中的null值，但是对表中的重复值不进行处理。

第二种方法， exists是一种只能得到true或false的谓词，它的参数一般是关联子查询，它的判断方式是关联子查询是否能查询出数据，若能查询到记录则返回真，若不能查询到记录则返回假。

在谓词前加not关键字，对谓词得到的结果进行否定运算，若子查询查到数据，虽然谓词得到真值，但是进行否定运算后，整个where子句得到false，所以SELECT语句不输出此记录。

对没有的主键约束的情况进行演示：

 

通过两种方法插入数据：

第一种方法：

第二种方法：

通过演示可以得到：

1．  谓词in语法简单容易掌握，但是对null值的处理比较劣势；

2．  谓词exists语法比较难理解，但是可以对null值进行有效的处理；

三、按照A表修改B表

有两张表现在有两个表A B

A       ID NAME

         1 a

         2 b

         3 c

        33 d

        44 e

         7 f

        99 g

B       IDNAME

         1 a

        22 b

        33 d

        44 e

           c

         7 f

 

要求根据B表ID列对A表进行修改，如果B是空值则A不动

         这里面有个需要注意的地方，由于A表中name列有g，如果根据全表进行修改，会在B表上产生null值。

1.      update A

set ID=（SELECT idFROM b WHERE a.name=b.name）

where exists(select* from b where a.name = b.name and id is not null);

通过谓词exists对修改的数据进行范围限定，并且当表B中id列有null值时，不对表A进行修改；

2.      如果B表没有重复行时可以

Update A

Set id = (selectid from b where a.name=b.name)

Where name in (selectname from b);

由于B表中没有重复行，所以可以直接用谓词in对修改范围进行限定。

演示：

根据B表修改A表：

将结果进行对比可以看出，如果不对范围进行限定，会将A表中的c行和g行同时更新成null值，就和要求不符合，所以必须对范围进行限定； 

四、删除表test中重复的行

1.先将重复的行找出来

Select * from testwhere (a,b) in (select a,b from test group by a,b having count(*)>1);

通过group by子句和having子句，用count函数确认出拥有重复行的数据。若count(*)>1则说明这一行数据有重复值。

2.通过delete语句将重复数据删除

 Deletefrom test

Where (a,b) in (selecta,b from test group by a,b having count(*)>1);                  

但是，这样会删除所有的行，如果想留下一行，需要再加入条件

Deletefrom test

Where(a,b) in (select a,b from test group by a,b having count(*)>1)

Androwid not in (select min(rowid) from test group by a,b having count(*)>1);

 演示:

对此表进行删除重复行操作

首先通过查询语句对重复行进行查询

通过这种方法虽然可以删除重复行，但是会删除所有的行，这不符合我们的要求，所以，对词语句进行加工：

通过rowid伪列将重复行中，rowid最小的那一列不进行删除操作，就能得到我们想要的数据了。

删除前后对比：

五、在dual表中输出1000行数据；

 Dual表是一张只有1行1列的表，用普通的select查询时无法查出1000行数据的，所以，要用到level伪列：level伪列是Oracle自带的一种伪列，使用方法：

Selectlevel from dual connect by level<=1000;

由于1000行数据无法全部显示，我使用count函数将查询出的结果进行了一次统计；

我们可以看到，用这种就可以在dual表中输出1000行数据了；但是level伪列还有一些需要注意的地方，他不能直接通过where子句设定条件，而是通过connect by进行条件设定；

六、通过A表得到B表：

A表：

B表：

结果演示：

这种方法使用了group by语句和三个字符函数，但是由于group by对ID列进行分组并对数据行进行排序时是无规则排序的，导致在wm_concat函数中，字符也是无规则组合的，所以无法得到我们想要的结果。然后我使用了另一种方法对其进行查询：

 

此时由于字符的排序是在关联子查询中进行的，不受ID列的影响，得到了我们想要的结果；

数据库函数的一些用法：

1.      通过使用 substr函数和length函数截取某一列中除了首字母以外所有的字符:

Select substr(a,2,length(a))from test;

结果：

通过length函数将字符串的长度读取出来，并作为substr函数的参数应用，将字符串除了首字母以外的所有字符串提取出来。

并且通过这个实验可以知道，将函数进行嵌套使用，函数的执行顺序是从内到外；

2.      trunc函数的特殊用法

    1.      查询某个月第一天：

        Select trunc(日期，mm) fromdual；

    2.      查询某个季度第一天

        Select trunc(日期，q) fromdual;

         结果：

在第一个特殊用法中，我先使用了数据类型转换函数，将数字类型的20171010转换成日期类型，然后通过trunc函数和参数’mm’对日期进行操作，得到了当月的第一天；

在第二个特殊用法中，参数’q’对函数进行操作，得到了本季度的第一天，Oracle从一月份开把全年12个月以每三个月为一季度进行划分，所以得到的数据是2017年10月1日；

Dual表是Oracle中的一张特殊的表，它只有一行一列，他本身没有任何数据，只是为了Oracle数据库的完整性创造出来的一张表；

3.Wm_concat列转行函数：     

Selectwm_concat(列名) from 表；就可以将一张表中的某一列以行的形式显示出来

原表中的数据是以列的形式存储，通过函数就可以转换为行显示，通常此函数和replace函数一起用

只使用wm_concat函数，每一行数据之间是用‘，’隔开的，通过replace函数就可以将’,’转换成’|’。

4.列转行函数pivot;把某张表的两列通过函数合并成一行进行输出：

select * from test

pivot(sum(b) for a in('behavior','bride','custom','exotic','groom','offensive','tradition'));

 

注意：使用此函数时，pivot（列1 for 列2）中，列1必须使用函数，否则会报错，并且进行查询的表中只能包含pivot函数中出现的列；