SQL语句合集

 

UNION (合并)

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名，也可自定义。

SELECT 34 (as) name UNION ALL SELECT 21 (as) sex;  #列名为第一个select语句的自定义列名：name

GROUP BY (分组)

分组，主要作用：把一个集合中根据 group by 后面的关键字(列名/字段)进行分组(去重+排序)。

内含两个计算：

1. 去掉重复值
2. 进行排序

注意：

• group by 与 distinct 有相同的去重作用，但是 distinct 不排序。
• select distinct * from table_name;  表中所有列的值相同的行才只被列出一次。

ORDER BY (排序)

• ORDER BY 语句用于根据指定的列对结果集进行排序(默认升序)。
• ORDER BY 语句不会去重。
• 如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

JOIN (联合查询)

 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。join语句比较复杂，下面将以实例进行说明。

-------------------------------------------------
table1      |table2 
-------------------------------------------------
id   name  | id  score 
1   lee 　　| 1   90 
2   zhang  | 2  100 
4   wang   | 3   70 
-------------------------------------------------

1. 左连接：left join 或 left outer join

左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值(null)。

select * from table1 left join table2 on table1.id=table2.id
-------------结果-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 2 100
4 wang NULL NULL
------------------------------
注释：包含table1的所有字段，根据指定条件返回table2相应的字段，不符合的以null显示

2. 右连接：right join 或 right outer join

右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

select * from table1 right join table2 on table1.id=table2.id
-------------结果-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 2 100
NULL NULL 3 70
------------------------------
注释：包含table2的所有子句，根据指定条件返回table1相应的字段，不符合的以null显示

3. 完整外部联接：full join 或 full outer join 

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

select * from table1 full join table2 on table1.id=table2.id
-------------结果-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 2 100
4 wang NULL NULL
NULL NULL 3 70
------------------------------
注释：返回左右连接的和（见上左、右连接）

因为MySQL不支持FULL JOIN,可用union替代：

left join + union + right join

select * from A left join B on A.id = B.id
union
select *from A right join B on A.id = B.id;

4. 内连接：join 或 inner join

select * from table1 join table2 on table1.id=table2.id
-------------结果-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 2 100
------------------------------
注释：只返回符合条件的table1和table2的列

5. 交叉连接：cross join (不带条件where...)

没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。（table1和table2交叉连接产生3*3=9条记录）

select * from table1 cross join table2
-------------结果-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 1 90
4 wang 1 90
1 lee 2 100
2 zhang 2 100
4 wang 2 100
1 lee 3 70
2 zhang 3 70
4 wang 3 70
------------------------------
注释：返回3*3=9条记录，即笛卡尔积  

等价（与下列执行效果相同）

select * from table1,table2

 

Having（分组筛选）

having的用法

having 子句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。我的理解就是真实表中没有此数据，这些数据是通过一些函数生成的。

SQL实例：

一、显示每个地区的总人口数和总面积

SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region;

先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中的不同字段（一或多条记录）作运算。

 

二、 显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000;

在这里，我们不能用where来筛选超过1,000,000的地区，因为表中不存在这样一条记录。相反，having子句可以让我们筛选成组后的各组数据。

having 一般跟在group by之后，执行记录组选择的一部分来工作的。

某个员工信息表 staff 如下：

id name dept salary edlevel hiredate 
1  张三 开发部 2000 　　3 　　2009-10-11
2  李四 开发部 2500 　　3 　　2009-10-01
3  王五 设计部 2600 　　5 　　2010-10-02
4  王六 设计部 2300 　　4 　　2010-10-03
5  马七 设计部 2100 　　4 　　2010-10-06
6  赵八 销售部 3000 　　5 　　2010-10-05
7  钱九 销售部 3100 　　7 　　2010-10-07
8  孙十 销售部 3500 　　7 　　2010-10-06

1）查询公司2010年入职的各个部门每个级别里的最高薪水：

SELECT dept,edlevel,MAX(salary) AS MAXIMUM  
FROM STAFF  
WHERE hiredate > '2010-01-01'  
GROUP BY dept,edlevel;

查询结果如下：
dept edlevel MAXIMUM
设计部 4 　　　　2300
设计部 5    　　2600
销售部 5    　　3000
销售部 7 　　　 3500

 

2)查找雇员平均工资大于3000的部门的最高薪水和最低薪水：

SELECT dept,MAX(salary) AS MAXIMUM,MIN(salary) AS MINIMUM  
FROM STAFF  
GROUP BY dept  
HAVING AVG(salary) > 3000  
ORDER BY dept;

查询结果如下：
dept   MAXIMUM MINIMUM
销售部  3500    30