特殊形式的查询:
1、 子查询:SELECT 字段名称,… FROM tb1_name WHERE col_name=(SELECT col_name FROM tb2_name);
——内层语句的查询结果可以作为外层语句的查询条件
a) 由IN引发的子查询
SELECT * FROM emp WHERE depId [NOT]IN (SELECT id FROM dep);
b) 由比较运算符引发的子查询:SELECT 字段名称,… FROM tb1_name WHERE col_name>=[<=…](SELECT col_name FROM tb2_name);
c) 由EXISTS引发的子查询(内层查询语句不返回记录,而返回Boolean值):
SELECT 字段名称,… FROM tb1_name WHERE EXISTS (SELECT col_name FROM tb2_name);——内层查询语 句为 真,则查询外层语句,反之不查询
SELECT * FROM emp WHERE EXISTS SELECT depName FROM dep WHERE id=10;
d) ANY SOME ALL的子查询
运算符\关键字 | ANY | SOME | ALL |
>、>= | 最小值 | 最小值 | 最大值 |
<、<= | 最大值 | 最大值 | 最小值 |
= | 任意值 | 任意值 |
|
<>、!= |
|
| 任意值 |
Eg: SELECT * FROM stu WHERE score>= ANY(SELECT score FROM level);----大于等于内层查询的最小值
SELECT * FROM stu WHEREscore>= ALL(SELECT score FROM level);----大于等于内层查询的最大值
e) INSERT… SELECT:
INSERT user1(name) SELECT nameFROM user;
f) CREATE… SELECT:
CREATE TABLE user1(
id tinyint unsigned auto_increment key,
name varchar(20) unique
)engine=innodb charset=utf8 select id,name from emp;
g) INSERT tb_name SET col_name=(SELECT…);
INSERT user2 SET name=(SELECT name FROM user WHERE id=1);
2、 联合查询
a) UNION:会去掉表中的重复记录
SELECT 字段名称… FROM tb1_name
UNION
SELECT 字段名称… FROM tb2_name;
b) UNION ALL:简单的合并
SELECT 字段名称… FROM tb1_name
UNION ON
SELECT 字段名称… FROM tb2_name;
3、 自身连接查询:无限级分类
//无限级分类表设计
CREATE TABLE cate(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(40) NOT NULL UNIQUE,
pId SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父id'
)ENGINE=INNODB CHARSET=UTF8;
INSERT cate(cateName) VALUES('服装'),
('数码'),
('玩具');
INSERT cate(cateName, pId)VALUES('男装',1),
('女装',1),
('内衣',1);
INSERT cate(cateName, pId)VALUES('电视',2),
('冰箱',2),
('洗衣机',2);
INSERT cate(cateName, pId)VALUES('爱马仕',3),
('LV',3),
('GUCCI',3);
INSERT cate(cateName, pId)VALUES('夹克',4),
('衬衫',4),
('裤子',4);
INSERT cate(cateName, pId) VALUES('液晶电视',10),
('等离子电视',10),
('背投电视',10);
//查询各个分类的父分类
SELECT s.id, s.cateName ASsCateName, p.cateName AS pCateName FROM cate AS s
LEFT JOIN cate AS p
ON s.pId=p.id;
//查询父分类下有哪些子分类
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;
//统计各分类下的条数
SELECT p.id, p.cateName AS pCateName, COUNT(s.cateName) FROM cate AS s
RIGHT JOIN cate AS p
ON s.pId=p.id
GROUP BY p.cateName
ORDER BY id;
//统计各分类下的条数,及显示详细信息
SELECT GROUP_CONCAT(s.id),GROUP_CONCAT(s.cateName) AS sCateNames, COUNT(s.cateName), p.cateName AS pCateName FROM cate AS s
LEFT JOIN cate AS p
ON s.pId=p.id
GROUP BY p.cateName;