深入浅出MySQL(六)

特殊形式的查询

子查询

子查询的基本形式为：

SELECT 字段名称 FROM tbl_name 
WHERE col_name = (SELECT col_name FROM tbl_name)

其中内层语句的查询结果可以作为外层语句的查询条件

1. 由IN引发的子查询
   有emp员工表和dep部门表

   
   emp
   dep
   

   现有需求，查询员工的部门编号在部门id中的所有员工

SELECT * FROM emp 
WHERE depId IN (SELECT id FROM dep);

2. 由比较运算符引发的子查询
   现有学院表stu和分数级别表level

   
   stu
   level
   

   需求：查询出所有没有得到评级的学员

SELECT id,username,score FROM stu
WHERE score <= (SELECT score FROM level WHERE id = 3);

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

1. CREATE...SELECT
   测试用例：

-- 创建一个user1表 id,username
-- 创建数据表的同时导入emp表的数据
CREATE TABLE user1(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20)
)SELECT id,username FROM emp;

2. 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

使用联合查询：
联合查询有两种形式

1. 使用UNION关键字

SELECT 字段名称,... FROM tbl_name1
UNION
SELECT 字段名称,... FROM tbl_name2

2. 使用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