SQL 子查询

所谓 SQL 子查询就是嵌套在其他查询中的查询。子查询通常用于 WHERE 子句的 IN *** 作符中进行过滤，以及用来填充计算列。下面我们从这两种使用场景展开学习。

本节涉及到关系表如下：

最上方的订单表 Orders 存储了订单 ID 、订单日期以及顾客 ID ；具体的订单信息存储在 OrderItems 表中，通过 order_num 进行关联；具体的顾客信息存储在顾客表 Customers 中，通过 cust_id 字段进行关联。

下面，假设我们需要检索出购买了 RGA01 产品的所有顾客信息，应该怎么做呢？

首先，我们只列出步骤：

接下来，我们来完成第一步：

包含了产品 RGAN01 的订单编号：

有了订单编号，就可以从订单表检索出顾客 ID 了：

运行结果：

接下来，我们来合并上面的两步：把第一个查询变为子查询，放在 WHERE 语句的 IN *** 作符之后：

运行结果和上述第二步获得的结果是一样的：

在 SELECT 语句中子查询从内向外处理：首先执行 SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01' 子查询，将返回的订单号作为 IN *** 作符的参数，执行外部查询： SELECT cust_id FROM Orders WHERE order_num IN ( 20007, 20008 )。

最后，我们来看第三步，根据顾客 ID 检索出顾客相关信息。

运行结果：

同理，我们可以将上述的 WHERE 子句替换为子查询：

至此，我们的检索任务就完成了：查询购买了 RGAN01 商品的所有顾客姓名及联系方式。

子查询的另一个使用场景为创建计算字段。同样，我们以一个案例来学习：检索每个顾客的订单的总数。

顾客表与订单表是一对多的关系，即一个顾客对应多个订单，但一个订单只对应一个顾客。

如下的 SQL 检索出顾客 1000000001 的订单数：

要对每个顾客的订单计数，应该将其作为子查询：

运行结果：

上述 SQL 对 Customers 表中每个顾客返回三列： cust_name cust_state 和 orders 。其中 orders 为子查询创建的计算字段，该子查询对检索出的每个顾客都执行一次，一共执行了 6 次子查询。

在子查询的条件中，我们使用了表的完全限定列名 Orders.cust_id = Customers.cust_id ，这是因为 Orders 表和 Customers 表中包含了字段名名称相同的列。

本节我们学习了在 SELECT 语句中使用子查询的两种用法：将子查询应用于 WHERE 子句的 IN *** 作符中，进行条件过滤，以及用子查询创建计算字段。

子查询的检索效率不够理想，下一篇文章中小鱼将和大家展开联结表的学习~ 连接表 是数据检索的精华和重点，我们拭目以待吧！

1、子查询就如递归函数一样,有时侯使用起来能达到事半功倍之效,只是其执行效率同样较低,有时用自身连接可代替某些子查询,另外,某些相关子查询也可改写成非相关子查询。

2、表连接都可以用子查询，但不是所有子查询都能用表连接替换，子查询比较灵活，方便，形式多样，适合用于作为查询的筛选条件，而表连接更适合与查看多表的数据。

3、子查询是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时，称之为子查询。

4、子查询是本质上就是一个完整 的SELECT 语句，它可以使一个 SELECT、SELECT...INTO 语句、INSERT...INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。子查询的输出可以包括一个单独的值(单行子查询)、几行值(多行子查询)、或者多列数据(多列子查询)。

5、连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。

增加外键

创建表的时候增加外键：在所有的表字段之后，使用foreign key(外键字段) references 外部表(主键字段)

在新增表之后增加外键：修改表结构，使用alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段)

修改外键&删除外键

alter table 表名 drop foreign key 外键名

外键条件

外键要存在，首先必须保证表的存储引擎是innodb

列类型必须与父表的主键类型一致

一张表中的外键名字不能重复

增加外键的字段数据已经存在，必须保证数据与父表主键要求对应

外键约束

有三种约束模式

district：严格模式（默认的）

cascade：级联模式

set null：置空模式

语法：foreign key(外键字段) references 父表(主键字段) on delete 模式 on update 模式

联合查询

基本语法：

select 语句1

union [union 选项]

select 语句2……

union 选项

all：保留所有，不管重复

distinct：去重，默认的

子查询（sub query）

按位置分类

from子查询

where子查询

exists子查询

按结果分类

标量子查询

列子查询

行子查询

表子查询

子查询

列子查询

=any等价于in -- 其中一个即可

any等价于some-- 二者是一样的

=all为全部

-- 创建外键

create table my_foreign1(

idint primary key auto_increment,

name varchar (20)not null comment

'学生姓名',

c_idint comment'班级id',

-- 增加外键

foreign key(c_id)references

my_class(id)

)charset utf8

-- 创建表

create table my_foreign2(

idint primary key auto_increment,

name varchar (20)not null comment

'学生姓名',

c_idint comment'班级id'  -- 普通字段

)charset utf8

-- 增加外键

alter table my_foreign2add

-- 指定外键的名字

constraint student_class_1  -- 可以指定多个外键 但是名字不能相同

-- 指定外键的字段

foreign key(c_id)

-- 引用父表主键

references my_class(id)

-- 删除外键

alter table my_foreign1drop

foreign key my_foreign1_ibfk_1  -- my_foreign1_ibfk_1 通过外键的名字来删

-- 插入数据；外键字段在父表不存在

insert into my_foreign2values (

null,'郭富城',4)  -- 没有4号班级

insert  into my_foreign2values (

null,'项羽',1)

insert  into my_foreign2values (

null,'刘邦',2)

insert  into my_foreign2values (

null,'韩信',3)

-- 更新父表的记录

update my_classset id=4 where id=1  -- 失败；id=1记录已经被学生引用

update my_foreign2set c_id=2 where id=4  -- 更新

update my_classset id=4 where id=3  -- 可以；没有学生引用此班级

-- mysql中添加外键约束遇到一下情况：

-- cannot add foreign key constraint

-- 出现这个问题的原因是，外键的使用：

-- 1. 外键字段不能为该表的主键；

-- 2. 外键字段参考字段必须为参考表的主键

-- 插入数据

insert into my_foreign1values (

null,'马超','3'

)

-- 增加外键

alter table my_foreign1add

foreign key(c_id)references

my_class(id)  -- 失败；因为没有3号班了

-- 创建外键，指定模式；删除置空；更新级联

create table my_foreign3(

idint primary key auto_increment,

name varchar (20)not null,

c_idint,

-- 增加外键

foreign key (c_id)

-- 引用表

references my_class(id)

-- 指定删除模式

on delete set null

-- 指定更新模式

on update cascade

)charset utf8

-- 插入数据

insert into my_foreign3values (

null,'刘备',1),

(null,'曹 *** ',1),

(null,'孙权',1),

(null,'祝贺量',2),

(null,'周瑜',2)

-- 解除My_foreign2表的外键

alter table my_foreign2drop

foreign key student_class_1

-- 更新父表主键

update my_classset id=3 where id=1

-- 删除父表主键

delete from  my_classwhere id=2

-- 联合查询

select * from my_class

union  -- 默认去重

select * from my_class

select * from my_class

union all  -- 不去重

select * from my_class

select id,c_name,roomfrom my_class

union all  -- 不去重

select name,number,idfrom my_student

-- 需求；男生升序；女生降序（年龄）

(select * from my_student

where sex='男'

order by ageasc limit9999999)

union

(select * from my_student

where sex='女'

order by agedesc limit9999999)

select * from my_studentwhere

c_id=(

-- 标量子查询

select idfrom my_classwhere

c_name='python1903')-- id一定只有一个值（一行一列）

insert into my_classvalues (1,

'python1907','B407')

-- 列子查询

select * from my_studentwhere

c_idin(select idfrom my_class)

-- any,some,all

select * from my_studentwhere

c_id=any(select idfrom my_class)

select * from my_studentwhere

c_id=some(select idfrom my_class)

select * from my_studentwhere

c_id=all(select idfrom my_class)

select * from my_studentwhere

c_id!=any(select idfrom my_class)  -- 所有结果（null除外）

select * from my_studentwhere

c_id!=some(select idfrom my_class)  -- 所有结果（null除外）

select * from my_studentwhere

c_id!=all(select idfrom my_class)  -- 所有2号班级（null除外）

select * from my_studentwhere

age=(select max(age)from

my_student)

and

height=(select max(height))from

my_student)

-- 行子查询

select * from my_student

-- (age,height)称之内为行元素

where (age,height)=(select max(

age),max(height)from my_student)

update my_studentset height=188

where name='王五'

select * from my_studentorder by

agedesc,heightdesc limit1

select * from my_studentorder by

heightdesc

-- 表子查询

select * from my_studentgroup by

c_idorder by heightdesc  -- 每个班选出第一个学生再按身高排序

select * from (select * from

my_studentorder by heightdesc)

as studentgroup by student.c_id

---