MySQL—子查询（where 型子查询 from 型子查询 exists 型子查询 复制表子查询）

MySQL—子查询

知识大纲

• 概念
• where 型子查询
• from 型子查询
• exists 型子查询
• 复制表子查询

1.概念

某些情况下，当进行一个查询时，需要的条件或数据要用另外一个 select 语句的结果， 这个时候，就要用到子查询；

为了给主查询（外部查询）提供数据而首先执行的查询（内部查询）被叫做子查询；

一般根据子查询的嵌入位置分为，where 型子查询，from 型子查询，exists 型子查询。

2.where 型子查询

where 型子查询即把内层 sql 语句查询的结果作为外层 sql 查询的条件.

• 子查询要包含在括号内。
• 建议将子查询放在比较条件的右侧。
• 单行操作符对应单行子查询，多行操作符对应多行子查询。
  • 单行操作符 右边子查询必须返回的是单个值，单行比较运算符（=,>,>=,<,<=,<>）
  • 多行操作符 右边子查询可以返回多行，但必须是单列，ALL, ANY,IN 其 中，ALL 和 ANY 运算符必须与单行比较运算符（=,>,>=,<,<=,<>）结 合使用

IN：等于任何一个
ALL：和子查询返回的所有值比较。例如：sal>ALL(1,2,3)等价于 sal>1 && sal>2 && sal>3，即大于所有。
ANY：和子查询返回的任意一个值比较。例如：sal>ANY(1,2,3)等价于 sal>1 or sal>2 or sal>3，即大于任意一个就可以。
EXISTS：判断子查询是否有返回结果（不关心具体行数和内容），如果返回则为 TRUE，否则为 FALSE。

示例

#1.一个查询的条件[数据] 依赖于另外一个查询的结果
#2.子查询[内部查询-Line10]会先于主查询[外部查询-Line8]执行
#WHERE型子查询 即把内层sql语句查询的结果 作为外层sql查询的条件.
#查询MySQL成绩在80分以上的同学信息
SELECT * FROM student WHERE s_id=
(SELECT s_id FROM souce WHERE MySQL>=80
);
SELECT * FROM student WHERE s_id=(SELECT s_id FROM souce WHERE MySQL>=80);
#不建议
SELECT * FROM student WHERE (SELECT s_id FROM souce WHERE MySQL>=80)=s_id;#单行操作符[=,>,>=,<,<=,<>] 要求：右边子查询必须返回的是单个值
#查询考号为T001的同学信息
SELECT * FROM student WHERE s_id = 
(SELECT s_id FROM souce WHERE t_id='T001'
);
#查询考号为T001、T002的同学信息
/*
由于右侧子查询返回了两个结果[A1001 A1002] 
所以不能在父查询和子查询之间使用比较运算符
=,>,>=,<,<=,<>
*/
SELECT * FROM student WHERE s_id in 
(SELECT s_id FROM souce WHERE t_id in('T001','T002')
);
/*
当右侧子查询返回了多个结果时 我们不能使用比较运算符
但 我们可以使用：多行操作符
多行操作符 右侧子查询返回的结果：多行单列
[可以返回多行结果 但只能是单列数据]
多行操作符：
ALL
ANY
IN[NOT INT]
*/
#查询参加了考试的学生信息
SELECT * FROM student WHERE s_id IN('A1001','A1002','B1001');
#IN关键字 等于查询结果中的任何一个
SELECT * FROM student WHERE s_id IN
(SELECT s_id FROM souce
);
#IN关键字 不等于查询结果中的任何一个
#查询未参加考试的学生信息
SELECT * FROM student WHERE s_id NOT IN('A1001','A1002','B1001');
SELECT * FROM student WHERE s_id NOT IN
(SELECT s_id FROM souce
);

#图书表
CREATE TABLE book
(b_id INT primary key,b_name VARCHAR(20),b_author VARCHAR(20),b_price FLOAT,b_numbers INT
);
insert into book values(1001,'张三的传说','张三',9.9,100);
insert into book values(1002,'倚天屠龙记','金庸',18.5,10);
insert into book values(1003,'7种武器','古龙',29,10);
insert into book values(1004,'天才相师','天才',99,10);
insert into book values(1005,'圣墟','辰东',199,10);
insert into book values(1006,'三毛流浪记','三毛',1.83,10);
insert into book values(1007,'三毛流浪记2','三毛',1.83,10);#借阅表
CREATE TABLE readbook
(r_id INT primary key,r_reader VARCHAR(20),r_times TIMESTAMP,b_id INT
);
insert into readbook values(100101,'蛇精病','2010-12-13',1001);
insert into readbook values(100102,'蛇精病','2010-12-17',1002);
insert into readbook values(100103,'精神病','2014-11-23',1003);
insert into readbook values(100104,'大神','2015-1-3',1001);
insert into readbook values(100105,'大蛇精病','2011-12-13',1004);
insert into readbook values(100106,'大蛇精病','2011-12-13',1006);SELECT * FROM book;
SELECT * FROM readbook;#查询未参加考试的学生信息
SELECT * FROM student WHERE s_id <>ALL
(SELECT s_id FROM souce
);# <>all 相当于：not in
# =any 相当于：in
#查询被借阅书籍的信息
SELECT * FROM book WHERE b_id IN 
(SELECT b_id from readbook
);
SELECT * FROM book WHERE b_id =ANY 
(SELECT b_id from readbook
);#查询没有被借阅书籍的信息
SELECT * FROM book WHERE b_id NOT IN 
(SELECT b_id from readbook
);
SELECT * FROM book WHERE b_id <>ALL
(SELECT b_id from readbook
);# >all相当于: 大于子查询中的最大值
#SELECT * FROM book WHERE b_id > 1006;
#查询图书编号大于被借阅图书最大编号的书籍
SELECT * FROM book WHERE b_id >ALL
(SELECT b_id FROM readbook
);# <all相当于: 小于子查询中的最小值
DELETE FROM readbook WHERE b_id=1001;
#查询图书编号小于被借阅图书最小编号的书籍
SELECT * FROM book WHERE b_id <ALL
(SELECT b_id FROM readbook
);# <any相当于: 小于子查询中的最大值
SELECT * FROM book WHERE b_id <ANY
(SELECT b_id FROM readbook
);# >any相当于: 大于子查询中的最小值
SELECT * FROM book WHERE b_id >ANY
(SELECT b_id FROM readbook
);

3.from型子查询

from 型子查询即把内层 sql 语句查询的结果作为临时表供外层 sql 语句再次查询

示例

#FROM类型子查询 [把内层sql语句查询的结果作为临时表 供外层sql语句再次查询]
SELECT * FROM book;
SELECT * FROM (SELECT * FROM book WHERE b_price>=90) books;#查询书籍价格在90元以上 且被借阅书籍信息
SELECT books.b_id,books.b_name,books.b_price FROM (SELECT * FROM book WHERE b_price>=90) books INNER JOIN readbook rON books.b_id=r.b_id;

4.exists 型子查询

判断子查询是否有返回结果（不关心具体行数和内容），如果返回则为 TRUE，否则为 FALSE。

示例

#EXISTS类型子查询 [父查询是否执行 依赖于子查询是否有内容返回]
#子查询有内容返回 EXISTS true；主查询执行
SELECT * FROM book WHERE EXISTS 
(SELECT * FROM readbook WHERE b_id=1002
);
#子查询没有内容返回 EXISTS false；主查询不会执行
SELECT * FROM book WHERE EXISTS 
(SELECT * FROM readbook WHERE b_id=1001
);

5.复制表子查询

1）复制表

（1）拷贝表结构

CREATE TABLE newadmin LIKE admin;

（2）拷贝表结构和数据（但约束与索引除外）

CREATE TABLE newadmin AS (SELECT * FROM admin);

（3）拷贝表结构+数据

CREATE TABLE newadmin LIKE admin;

INSERT INTO newadmin SELECT * FROM admin;

（4）跨数据库拷贝表

CREATE TABLE newadmin LIKE shop.admin;

CREATE TABLE newshop.newadmin LIKE shop.admin;

（5）拷贝一个表中其中的一些字段（指定新名），其中一些数据

（6）在创建表的同时定义表中的字段信息。

2）复制数据

在 INSERT 语句中加入子查询。

不必书写 VALUES 子句。

子查询中的值列表应与 INSERT 子句中的列名对应。

示例

#复制表 - 拷贝表[结构]
#基于student表的结构 产生 一张新表newStudent
CREATE TABLE newStudent LIKE student;#复制表 - 拷贝表[数据]
INSERT INTO newStudent SELECT * FROM student;
#INSERT INTO newStudent(c1,c2) SELECT c1,c2 FROM student;SELECT * FROM newStudent;#复制表 - 拷贝表[结构+数据][不会复制约束和索引]
#基于student表的结构和数据 产生 一张新表newStudent1
CREATE TABLE newStudent1 AS (SELECT * FROM student);
SELECT * FROM newStudent1;#跨数据库拷贝表 
/*
数据库MYSQL1中存在A表
假设当前在使用数据库MYSQL2
*/
#跨库拷贝数据库MYSQL1中A表的 表结构
CREATE TABLE 新表B LIKE MYSQL1.A;
CREATE TABLE MYSQL2.新表B LIKE MYSQL1.A;SELECT * from mysql02.empinfo;
SELECT * from empinfos;
CREATE TABLE mysql01.empinfos LIKE mysql02.empinfo;SELECT * FROM student;
#拷贝一个表中其中的一些字段，其中一些数据
CREATE TABLE newStudent2 AS (SELECT s_id,name FROM student where age>=18);SELECT * FROM newStudent2;