特殊形式的查询
子查询
子查询的基本形式为：
SELECT 字段名称 FROM tbl_name
WHERE col_name = (SELECT col_name FROM tbl_name)
其中内层语句的查询结果可以作为外层语句的查询条件
-
由IN引发的子查询
有emp员工表和dep部门表
emp
dep
现有需求，查询员工的部门编号在部门id中的所有员工
SELECT * FROM emp
WHERE depId IN (SELECT id FROM dep);
-
由比较运算符引发的子查询
现有学院表stu和分数级别表level
stu
level
需求：查询出所有没有得到评级的学员
SELECT id,username,score FROM stu
WHERE score <= (SELECT score FROM level WHERE id = 3);
- 由EXISTS引发的子查询
基于上述的员工表和部门表：
如果EXISTS后面的子查询语句为真那么就可以执行外层查询语句，如果EXISTS后面的子查询语句为假，也就是没有查询结果，那么外层查询语句就不会执行。
对于语句：
SELECT * FROM emp WHERE EXISTS(SELECT depName FROM dep WHERE id = 2);
返回的结果为：
result
对于一个子查询语句为空的测试：
SELECT * FROM emp WHERE EXISTS(SELECT depName FROM dep WHERE id = 7);
返回结果为空
result
带有ANY,SOME,ALL关键字的子查询
| ANY | SOME | ALL | |
|---|---|---|---|
| >,>= | 最小值 | 最小值 | 最大值 |
| <,<= | 最大值 | 最大值 | 最小值 |
| = | 任意值 | 任意值 | |
| <>,！= | 任意值 |
对于这个表，举例说明 大于ANY 等同于大于表中的最小值
拿stu表于level表进行举例：
stu
level
现有需求：查看哪些学员可以获得评级
SQL语句如下：
SELECT * FROM stu
WHERE score >= ANY(SELECT score FROM level) ;
-- 或者
SELECT * FROM stu
WHERE score >= SOME(SELECT score FROM level) ;
需求：查询出分数评级为第一级的所有学生
SELECT * FROM stu
WHERE score >= ALL(SELECT score FROM level) ;
INSERT...SELECT与CREATE...SELECT
- CREATE...SELECT
测试用例：
-- 创建一个user1表 id,username
-- 创建数据表的同时导入emp表的数据
CREATE TABLE user1(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20)
)SELECT id,username FROM emp;
- INSERT...SELECT
测试用例：
-- 将user表中的用户名插入到user1中
INSERT user1(username) SELECT username FROM user;
联合查询的使用
创建user2表：
-- 创建user2表
CREATE TABLE user2 LIKE user1;
-- 将user1表的数据导入到user2中
INSERT user2 SELECT id,username FROM user1;
-- 将stu表中的kim插入到user2中
INSERT user2 SET username = (SELECT username FROM stu WHERE id = 1);
user2表如下所示：
user2
使用联合查询：
联合查询有两种形式
- 使用UNION关键字
SELECT 字段名称,... FROM tbl_name1
UNION
SELECT 字段名称,... FROM tbl_name2
- 使用UNION ALL 关键字
SELECT 字段名称,... FROM tbl_name1
UNION ALL
SELECT 字段名称,... FROM tbl_name2
UNION会去掉表中的重复记录，UNION ALL只是简单的合并，测试用例如下：
SELECT * FROM user1
UNION
SELECT * FROM user2;
查询结果为：
union
SELECT * FROM user1
UNION ALL
SELECT * FROM user2;
查询结果为：
union all
自身连接查询
实现无限极分类表
CREATE TABLE cate(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(100) NOT NULL UNIQUE,
pId SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
-- 插入顶级分类
INSERT cate(cateName,pId) VALUES('服装',0);
INSERT cate(cateName,pId) VALUES('数码',0);
INSERT cate(cateName,pId) VALUES('玩具',0);
-- 插入服装的子分类
INSERT cate(cateName,pId) VALUES('男装',1);
INSERT cate(cateName,pId) VALUES('女装',1);
INSERT cate(cateName,pId) VALUES('内衣',1);
-- 插入数码的子分类
INSERT cate(cateName,pId) VALUES('电视',2);
INSERT cate(cateName,pId) VALUES('冰箱',2);
INSERT cate(cateName,pId) VALUES('洗衣机',2);
-- 插入玩具的子分类
INSERT cate(cateName,pId) VALUES('爱马仕',3);
INSERT cate(cateName,pId) VALUES('LV',3);
INSERT cate(cateName,pId) VALUES('GUCCI',3);
-- 插入男装的子分类
INSERT cate(cateName,pId) VALUES('夹克',4);
INSERT cate(cateName,pId) VALUES('衬衫',4);
INSERT cate(cateName,pId) VALUES('裤子',4);
-- 插入电视的子分类
INSERT cate(cateName,pId) VALUES('液晶电视',7);
INSERT cate(cateName,pId) VALUES('等离子电视',7);
INSERT cate(cateName,pId) VALUES('背投电视',7);
表如下所示：
cate
其中pId所表示的含义为：指向自己父级的类别。例如电视的pId编号为2指向的就是数码产品这一个类别，例如液晶电视的pId指向的就是7，也就是电视这个类别。这样我们就实现了一个无限极的分类表。
自身连接查询
自身连接查询，故名思意，就是自己连接自己。来看如下几个测试用例：
用例1：
-- 查询所有的分类信息，并且得到其父分类
SELECT s.id,s.cateName AS sCateName,p.cateName AS pCateName
FROM cate AS s
LEFT JOIN cate AS p
ON s.pId = p.id;
结果为：
test1
用例2：
-- 查询所有的分类及其子分类
SELECT p.id,p.cateName AS pCateName,s.cateName AS sCateName
FROM cate AS s
RIGHT JOIN cate AS p
ON s.pId = p.id;
test2
用例3：
-- 查询所有的分类并且得到子分类的数目
SELECT p.id,p.cateName AS pCateName,COUNT(s.cateName) AS `count`
FROM cate AS s
RIGHT JOIN cate AS p
ON s.pId = p.id
GROUP BY p.cateName
ORDER BY id ASC;
test3
网友评论