SQL 常用关键字

1：关于group by的用法 原理

写在前面的话：用了好久group by，今天早上一觉醒来，突然感觉group by好陌生，总有个筋别不过来，为什么不能够select * from Table group by id，为什么一定不能是*，而是某一个列或者某个列的聚合函数，group by 多个字段可以怎么去很好的理解呢？不过最后还是转过来了，简单写写吧，大牛们直接略过吧。

=========正文开始===========

　　先来看下表1，表名为test：

 

表1

　　执行如下SQL语句：

1 2	SELECT name FROM test GROUP BY name

　　你应该很容易知道运行的结果，没错，就是下表2：

 

表2

　　可是为了能够更好的理解“group by”多个列“和”聚合函数“的应用，我建议在思考的过程中，由表1到表2的过程中，增加一个虚构的中间表：虚拟表3。下面说说如何来思考上面SQL语句执行情况：

1.FROM test：该句执行后，应该结果和表1一样，就是原来的表。

2.FROM test Group BY name：该句执行后，我们想象生成了虚拟表3，如下所图所示，生成过程是这样的：group by name，那么找name那一列，具有相同name值的行，合并成一行，如对于name值为aa的，那么<1 aa 2>与<2 aa 3>两行合并成1行，所有的id值和number值写到一个单元格里面。

 

3.接下来就要针对虚拟表3执行Select语句了：

（1）如果执行select *的话，那么返回的结果应该是虚拟表3，可是id和number中有的单元格里面的内容是多个值的，而关系数据库就是基于关系的，单元格中是不允许有多个值的，所以你看，执行select * 语句就报错了。

（2）我们再看name列，每个单元格只有一个数据，所以我们select name的话，就没有问题了。为什么name列每个单元格只有一个值呢，因为我们就是用name列来group by的。

（3）那么对于id和number里面的单元格有多个数据的情况怎么办呢？答案就是用聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如cout(id)，sum(number)，而每个聚合函数的输入就是每一个多数据的单元格。

（4）例如我们执行select name,sum(number) from test group by name，那么sum就对虚拟表3的number列的每个单元格进行sum操作，例如对name为aa的那一行的number列执行sum操作，即2+3，返回5，最后执行结果如下：

 （5）group by 多个字段该怎么理解呢：如group by name,number，我们可以把name和number 看成一个整体字段，以他们整体来进行分组的。如下图

（6）接下来就可以配合select和聚合函数进行操作了。如执行select name,sum(id) from test group by name,number，结果如下图：

2： UNION 用法

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

//联合两个表，没有重复
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

• 1
• 2
• 3
• 4

数据源： 
 
结果： 

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。 
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

//联合两个表，允许重复
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

• 1
• 2
• 3
• 4

结果： 

3： JOIN用法

直接举例说明各种方法：

 

CREATE table A
(
id int,
name varchar(200)
)
CREATE table b
(
id int,
name varchar(200)
)

INSERT INTO b VALUES(1,'张三')
INSERT INTO b VALUES(2,'李四')
INSERT INTO b VALUES(3,'王五')
INSERT INTO b VALUES(4,'马六')
INSERT INTO b VALUES(5,'冯七')

INSERT INTO a VALUES(1,'赵八')
INSERT INTO a VALUES(2,'张三')
INSERT INTO a VALUES(3,'冯七')
INSERT INTO a VALUES(4,'田九')
INSERT INTO a VALUES(5,'凌十')

 

第一种：Inner join

解释：产生的结果是A和B的交集（相同列里面的相同值）。内连接是最常见的一种连接，它也被称为普通连接，只连接匹配的行（仅对满足连接条件的CROSS中的列）。它又分为等值连接（连接条件运算符为"="）和不等值连接（连接条件运算符不为"="，例如between...and）。

SELECT *FROM b INNER JOIN A
on b.name=A.name

第二种：Full Outer Join

解释：产生的结果是A和B的并集（如果没有相同的值会用null作为值）。FULL JOIN 会从左表和右表那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替

SELECT * FROM b FULL OUTER JOIN A
on b.name=A.name

第三种：Left outer join

解释：产生表B的完全集，而A表中匹配的则有值（没有匹配的则以null值取代）。LEFT JOIN返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。

SELECT * FROM b Left outer join A
on b.name=A.name

第四种：Right Outer Join

解释：参考第三种Left outer join

SELECT * FROM b Right Outer Join A
on b.name=A.name

第5种：cross join

解释：cross对两个表执行笛卡尔乘积。它为左表行和右表行的每种可能的组合返回一行。返回（左表行数*右表行数）行的表。ps：很少用到这个语法。注意如果不是使用嵌套的select语句，一般系统都会产生笛卡尔乘积然再做过滤。这是对于性能来说是非常危险的，尤其是表很大的时候。

SELECT * FROM b  cross join A

特殊：Natural Join（oracle的用法，sqlserver里面不支持这种写法。ps：大神可以纠正用法这里）

解释：自然连接是一种特殊的等值连接,在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

4: trunc，to_date用法

select * from zj_investsettlerecord d where trunc(d.dsettledate) = to_date('2017-10-14','yyyy-mm-dd') 

5：distinct 用法

--查询某人 2018-01-27号结算利息的项目数
select count(distinct(t.sguid)) from zj_investsettlerecord d inner join zj_investproject t on d.sinvestprojectid=t.sguid where t.iborrowtype=2  and trunc(d.dsettledate) = to_date('2018-01-27','yyyy-mm-dd') 
and d.isettletype='0' and d.smemberno='HY20160319001189'

6: rownum

select rownum as rn,er.* from hy_memberregister er where er.sname='测试' and rownum=2   --查不到数据，rownum从1开始

select rownum as rn,er.* from hy_memberregister er where er.sname='测试' and rownum=1  --可以查到第一条数据

select * from (select rownum as rn,er.* from hy_memberregister er where er.sname='测试') where rn=4   ----如果要取第4条数据