1、组合查询
1.1、多条查询结果组合显示
格式:
SELECT
列名1
FROM
表名
UNION
SELECT
列名2
FROM
表名2;
演示:
1.2、使用UNION组合3个以上的表
命令:
(SELECT * FROM WHERE empid=’A102’)
UNION
(SELECT * FROM empid=’A103’)
UNION
(SELECT * FROM WHERE empid=’A104’)
UNION
(SELECT * FROM tb WHERE empid=’A107’);
等价于
命令:
SELECT * FROM tb WHERE empid NOT IN(‘A101);
或者
SELECT * FROM WHERE empid IN(‘A102’,’A103’,’A104’,’A107’);
1.3、按条件合并多条提取结果并显示
操作命令:
(SELECT empid FROM tb WHERE sales>=200) UNION (SELECT empid FROM tb1 WHERE age>=35);
演示:
操作命令:
(SELECT empid FROM tb WHERE sales>=200) UNION ALL (SELECT empid FROM tb1 WHERE age>=35);
演示:
通过对比,我们发现,事实上,当我们使用UNION提取记录时,系统自动去除重复记录。而想要提取数据中包含重复数据,这时候就需要使用UNION ALL了。
1.4、UNION规则
- 从1.1~1.3的实际操作就可以看出,UNION中的每个查询必须包含相同的列,表达式或聚集函数(不过,各个列不需要以相同的次序列出)。
- 列的数据类型必须兼容,类型不必完全相同,但必须是DBMS可以隐含转换的类型。
- 如果结合UNION使用的SELECT语句遇到不同的列名,它会返回第一个名字。这样行为带来一个有意思的副作用。由于只使用第一个名字,那么想要排序也只能用这个名字。
- 在用UNION组合查询时,只能使用一条ORDER BY子句,它必须位于最后一条SELECT语句之后。
2、连接多个表进行查询(内连接)
2.1、使用其他表的记录进行处理
格式:
SELECT 列名 FROM 表1 JOIN 要连接的表2 ON 表1的列 = 表2的列;
演示:
像这样把不同的表中匹配的记录提取出来的连接方式称为内连接。如果要明确指出某一处理是内连接,可以将JOIN部分写出INNER JOIN。
2.2、选择列进行显示
操作命令:
SELECT tb.empid,tb1.name,tb.sales
FROM tb
JOIN tb1
ON tb.empid=tb1.empid;
演示:
2.3、给表添加别名
格式:
表名 AS 别名
操作命令:
SELECT x.empid,y.name,x.sales
FROM tb as x
JOIN tb1 as y
ON x.empid=y.empid;
演示:
2.4、使用USING使ON~的部分更容易阅读
在前面的实例中,作为连接键的两个列恰好都是empid这个名字。在使用相同列名进行指定情况下,我们可以使用USING(作为连接的键名)。
操作命令:
SELECT tb.empid,tb1.name,tb.sales FROM tb JOIN tb1 USING(empid);
演示:
2.5、通过WHERE设置条件从连接表中查询记录
操作命令:
SELECT tb.empid AS 员工号,tb1.name AS 姓名,tb.sales AS 销售额
FROM tb
JOIN tb1
USING(empid)
WHERE tb.sales>=100;
演示:
2.6、查询多个表中的记录
格式:
SELECT ~ FROM
表名1
JOIN 表名2 连接条件
JOIN 表名3 连接条件
...
;
演示:
3、外连接
3.1、什么是外连接?
外连接具有以下特征:即使与连接键不匹配,外连接也会提取另一个表中的所有记录。
3.2、外连接的种类
- 左外连接(LETF JOIN):显示相匹配的记录和左表的全部记录。
- 右外连接(RIGHT JOIN):显示相匹配的记录和右表的全部记录。
- LEFT JOIN也可以写出LEFT OUTER JOIN。同样,RIGHT JOIN也可以写出RIGHT OUTER JOIN。
3.3、使用左外连接
格式:
SELECT 列名 FROM 表1 LEFT JOIN 要连接的表2 ON 表1的列=表2的列;
演示:
3.4、使用右外连接
格式:
SELECT 列名 FROM 表1 RIGHT JOIN 要连接的表2 ON 表1的列 = 表2的列;
演示:
4、自连接
我们可以将表与其自身,也就是和相同的表进行连接。这种连接方式称为自连接。
格式:
SELECT 列名 FROM 表名 AS 别名1 JOIN 表名 AS 别名2;
演示:
注意:使用自连接来提取数据,结果是取笛卡尔积所得。
5、子查询(从SELECT的记录中SELECT)
5.1、显示最大值
操作命令:
SELECT * FROM tb WHERE sales IN (SELECT MAX(sales) FROM tb);
演示:
5.2、提取大于等于平均值的记录
操作命令:
SELECT * FROM tb1 WHERE age >=(SELECT AVG(age) FROM tb1);
演示:
5.3、使用IN(返回列的子查询)
格式:
SELECT 显示的列 FROM 表名 WHERE 列名 IN (通过子查询SELECT语句提取的列);
演示:
5.4、使用EXISTS,仅以存在的记录为对象
操作命令:
SELECT * FROM tb1 WHERE EXISTS (SELECT * FROM tb WHERE tb.empid=tb1.empid);
演示:
5.5、使用NOT EXISTS
操作命令:
SELECT * FROM tb1 WHERE NOT EXISTS (SELECT * FROM tb WHERE tb.empid=tb1.empid);
演示: