子查询

子查询:在一个查询里嵌套了其他的若干查询(没有数量限制)

1)SELECT语句的WHERE或HAVING或FROM子句中包含另一个SELECT

2)凡是分步实现的查询都可以考虑用子查询来实现

父查询:包含的SELECT语句为外层查询

子查询:被包含的SELECT语句为内层查询,包括相关子查询和不相关子查询

//内层查询必须用小括号括起来,并且与父查询嵌套式用相应的运算符

不相关子查询:子查询的查询条件不依赖于父查询(由内向外逐层处理)

1)本质:先执行子查询,再将子查询的结果用于建立父查询的检索条件进行逐层检索

相关子查询:子查询的查询条件依赖于父查询(由外向内再向外)

1)指定字段时,必须使用完全限定列名

2)子查询执行次数取决于父查询中的数据

3)本质:先取父查询表中第一个数据,根据其与内层查询的相关数据处理;再将内层查询后的查询结果代入外层查询进行查询,得到真正检索结果;以此类推,直至将父查询表中的数据全部带入

完全限定的列名:同时指定列的表名和列名,格式“表名.字段名

1)原因:不同表中的列名可能存在重复问题

SELECT子查询格式:

SELECT 其他字段,(子查询)[AS] 别名

1)子查询的结果通常为显示字段,必须取别名

FROM子查询格式:

FROM (子查询)[AS] 别名

1)子查询的结果通常为多行多列(当做临时表处理),必须取别名

WHERE子查询格式:

WHERE 表达式 运算符
(子查询);

1)表达式是用于比较的,通常为字段名

2)子查询返回的字段数量必须与WHERE指定的字段/表达式数量相同

运算符子:对表达式的值和子查询返回的值进行比较运算

运算符 语法格式 说明 适用情况
比较运算符 <表达式> {=|<|>| >=|<= |< > |!=} <子查询> 包括=、>、<、>=、<= 、<> 主要用于对表达式的值和子查询返回的值进行比较运算 子查询返回单行(单行单列或者单行多列)时使用
[NOT] IN <表达式> [NOT] IN <子查询> 主要用于判断表达式的值是否存在于子查询的结果集中 子查询返回多值(多行单列,包括单行)时使用
ANY|SOME <表达式> {=|<|>|>=|<=|< >|!=} {SOME|ANY} <子查询> 关键字 SOME 和 ANY 是同义的,用于表示表达式只要与子查询结果集中的某个值满足比较关系,子查询就返回 TRUE,否则返回 FALSE 子查询返回多值(多行单列)时使用; 使用时必须同时使用比较运算符
ALL <表达式> {=|<|>| >=|<= |< > |!=} ALL <子查询> 用于表示表达式需要与子查询结果集中的每个值都进行比较,当表达式与每个值都满足比较关系时,会返回 TRUE,否则返回 FALSE 子查询返回多值(多行单列)时使用; 使用时必须同时使用比较运算符
[NOT] EXISTS [NOT] EXIST <子查询> 主要用于判断子查询的结果集是否为空。若子查询的结果集不为空,则返回 TRUE;否则返回 FALSE。NOT EXISTS与之相反 子查询返回逻辑值时使用 都是相关子查询
组合 说明 对应关系
> ANY 大于子查询结果中的某个值 >MIN()
< ANY 小于子查询结果中的某个值 <MAX()
>= ANY 大于等于子查询结果中的某个值 >=MIN()
<= ANY 小于等于子查询结果中的某个值 <=MAX()
= ANY 等于子查询结果中的某个值 IN
<>ANY 不等于子查询结果中的某个值

//表中的所有ANY都可用SOME替换

//ANY|SOME本质就是IN和运算符的结合(通过运算符判断是否符合)

组合 说明 对应关系
> ALL 大于子查询结果中的所有值 >MAX()
< ALL 小于子查询结果中的所有值 <MIN()
>= ALL 大于等于子查询结果中的所有值 >=MAX()
<= ALL 小于等于子查询结果中的所有值 <=MIN()
=ALL 等于子查询结果中的所有值 (通常没有实际意义)
<>ALL 不等于子查询结果中的任何一个值 NOT IN

//ALL和ANY/SOME是两个极端(一个是所有都需满足,一个是其中有一个满足即可)

不相关子查询

不相关子查询:子查询的查询条件不依赖于父查询(由内向外逐层处理)

如:检索数据库中proid_id字段值为TNT2的cust_id字段
 
1)先检索子查询中prod_id为TNT2的order_num,并将结果返回至父查询

2)父查询自己的order_num和子查询返回的order_num进行IN运算

3)显示order_num成功匹配的cust_id

如:检索数据库中proid字段值为TNT的cust_name和cust_contact字段
 
1)先检索子子查询中prod_id为TNT2的order_num,并将结果返回至子查询

2)子查询自己的order_num和子子查询返回的order_num进行IN运算

3)子查询将order_num匹配成功的cust_id结果返回值父查询

4)父查询自己的cust_id和子查询返回的cust_id进行IN运算

5)显示cust_id成功匹配的cust_name和cust_contact

相关子查询

相关子查询:子查询的查询条件依赖于父查询(由外向内再向外)

1)指定字段时,必须使用完全限定列名

如:检索数据库中每个customers字段在orders的行数个数
 
1)先读取父查询中customers表中的cust_id字段值

2)代入子查询,与子查询中的cust_id字段值匹配

3)匹配成功后,调用聚合COUNT()函数计算行数个数

4)并将结果返回至父查询,再调用父查询中customers表中的cust_id字段的值

5)以此类推,直至父查询中customers表中cust_id字段值都带入一次

调用[NOT]EXISTS的子查询是相关子查询(子字段列名通常都用*)

1)EXISTS用于检查子查询是否至少会返回一行数据(实际上子查询并不返回任何数据,而是返回True或False)

2)根据EXISTS查询结果判断:将父查询表的指定行,代入子查询进行匹配

//如果子查询返回结果为非空,则EXISTS返回True(且这一行可作为父查询的结果行,不能作为结果)

如:查询选修了全部课程的学生姓名(EXISTS子查询)

SELECT SName
FROM Student S
WHERE NOT EXISTS
(SELECT *
FROM course C 
WHERE NOT EXISTS 
(SELECT  *
FROM register R
WHERE S.SId=R.SId AND C.CId=R.Cid)); 

//“选修全部课程”等价于“不存在一门课程没有选修”
//EXISTS查询本质就是父查询与子查询的笛卡尔积后再进行数据匹配

连接查询

连接查询:实现多表查询(表与表之间通过外码约束相互关联)

1)关系数据库模型的主要特点(区别于其他类型数据库管理系统的一个标志)

如:连接查询有以下方式
 

内连接

内连接(INNER JOIN):根据连接条件将多个表中的字段组合成新的表

连接方式 说明
等值连接 连接条件中使用“=”运算符比较连接字段上的取值 (查询结果包括重复列)
非等值连接 连接条件中使用运算符比较连接字段上的取值 (>、>=、<=、<、<>)
自连接 表与表自身连接
自然连接 特殊的等值连接 (查询结果去掉重复的列)

连接条件的设置(内连接的关键之处):

[表名1.]字段1 <比较运算符> <表名2>.<字段2\>

1)字段1和字段2为连接字段

2)连接字段为同名字段,必须使用完全限定列名

3)两个表若没有连接条件进行连接,则会成两个表的笛卡尔积表

//可给表名设置别名,但其他子句也需同样要使用别名

等值(非等值)连接格式1:

FROM 表名1,表名2
WHERE 连接条件;

1)连接和筛选条件统一写在WHERE子句后,用AND进行连接

2)连接条件一般都选择外键(连接条件不一定都是外键)

如:连接vendors和products表
 

等值(非等值)连接格式2:

FROM 表名 1 [INNER] JOIN 表名 2 ON 连接条件;

1)JOIN ON子句是成对出现的,可以连续使用JOIN ON实现多个表内连接

2)内连接是系统默认的表连接,所以FROM子句后可以省略INNER

//连接条件常选择外键(连接条件不一定都是外键)

如:连接orderitem、products和vendors表
 

自连接格式:

FROM 表名1 别名1 JOIN 表名1 别名2 ON 连接条件;

1)自连接一定要有别名(相同的表有一样的属性不利于操作)

2)自连接可代替所有从相同表检索数据的子查询

//也可使用类似等值连接格式1进行连接

如:检索products表中和prid_id为DTNTR具相同vend_id字段值的其他字段
 

自然连接格式:

FROM 表名1 NATURAL JOIN 表名2 连接条件;

1)进行连接的必须是相同的字段名,且无须添加连接条件

2)结果会自动去掉重复的列,与DISTINCT不同(去掉重复的行)

如:自然连接customers和orders表(两者均有cust_id字段)
 

外连接

外连接:表连接时把一方表要舍弃的行保留下,另一方对应的字段上填NULL

连接方式 说明
左外连接 把左表要舍弃的行保留下,右表对应的字段上填NULL
右外连接 把右表要舍弃的行保留下,左表对应的字段上填NULL
全外连接 把左表和右表要舍弃的行都保留下,相对应的列字段填NULL

外连接格式:

FROM 表名1 LEFT/RIGHT [OUTER] JOIN 表名2 ON 连接条件;

1)左外连接和右外连接可以相互转换

2)多个表进行连接时,若存在外连接需要考虑是否连续使用外连接

//不能第一个使用外连接后面就不使用了,会导致前面得到数据丢失

如:检索数据库中cust_id字段所对应的order_num字段
 

**交叉连接:**不带连接条件的查询,返回被连接的表所有数据行的笛卡尔积

1)检索结果的行数是左表的行数乘以右表的行数

2)不用添加连接条件

3)交叉连接格式:

FROM 表名1 CROSS JOIN 表名2;

//没有实际意义,通常用于测试所有可能的情况

联合查询

联合查询(UNION):将多个检索结果合并成一个检索结果显示

1)任何WHERE子句具有多个条件表达式的查询都可用联合查询代替

使用联合查询的有以下两种情况:

1)在单个查询中从不同的表返回类似结构的数据;

2)对单个表执行多个查询,并按单个查询返回数据;

MySQL不支持INTERSECT(交操作),EXCEPT或MINUS(差操作)

区别 UNION OR
操作对象 连接结果集 连接表达式

联合查询格式:

SELECT查询语句1
UNION [ALL]
SELECT查询语句2

1)联合查询中每个查询必须包含相同的列、表达式或聚合函数

2)数据类型可不完全相同,但必须兼容(系统可自动转换类型输出)

3)联合联系的UNION结果默认去除重复的行,若显示全部行UNION ALL

4)联合查询中只能调用一次ORDER BY进行排序

//若结果集的列名不一致,默认第一条查出来的列名作为显示列名

//ORDER BY语句必须在最后一个查询语句,针对合并后检索结果进行排序

如:检索products表中prod_price小于等于5或vend_id为1001或1002的行
 

如:续上,且包含重复行显示
 

如:续上,且对合并后的结果进行排序
 

CASE表达式

CASE表达式:不是完整的SQL语句(不能单独执行,作为表达式使用)

1)CASE表达式分为简单CASE和搜索CASE

2)CASE后跟的字段名必须是SELECT检索的字段

3)CASE函数一般直接跟在SELECT语句后

//CASE函数本质就是对查询到的值进行一种转换(使用另外一种形式表达)

简单CASE函数:只做数据匹配(不进行比较运算和逻辑运算)

CASE 字段名
WHEN 值1 THEN 转换值1
WHEN 值2 THEN 转换值2
ELSE 转换值n
END [[AS] 别名]

搜索CASE函数:进行更为复杂的数据转换

CASE
WHEN 布尔表达式1 THEN 转换值1
WHEN 布尔表达式2 THEN 转换值2
END [[AS] 别名]

全文本搜索

全文本搜索:调用Match()Against()函数对长文本进行良好程度搜索

1)Match()指定索引的字段,Against()指定搜索的表达式

2)MyISAM存储引擎才具有的功能(Innodb存储引擎不支持)

//优点:快速高效地搜索指定列,而且能提供一种智能化的结果

表支持全文本搜索需在其创建时指定其接受FULLTEXT子句

1)已创建或导入数据后也可修改表使其接受FULLTEXT子句(但所有数据需进行索引,占用较长时间)

2)设置完成后,后续系统会自动维护/更新该表的索引

//使用全文本搜索的前提是表支持进行全文本搜索

如:创建productnotes表,并使其支持全文本搜索
 

如:使用全文本搜索productnotes表中含有rabbit词的行
 
1)传递给Match()的参数必须是配置FULLTEXT时指定的字段/组合字段

2)其结果默认按降序等级排序显示

全文本搜索等级:由该行词的数目、唯一词的数目、索引中词的总数和词位于该行的位置计算得出

//以上符合则给该行加等级,最后排序等级为0的行再进行显示

全文本搜索由以下规定:

1)索引全文本数据时,短词被忽略且从索引中去除

2)MySQL中有内建的非用词(StopWord),这些词在索引中被排除

3)50%规则:若一个词出现频率大于50%,则将其视为非用词忽略

4)表中的行数若少于3行,则全文本搜索不反回结果(50%规则)

5)忽略词中的单引号(如:don’t索引为dont)

//短词:默认定义具有3个或3个以下字符的词(可修改)

//50%规则不用于布尔文本搜索

查询扩展

查询扩展(WITH QUERY EXPANSION):将全文本搜索结果的范围放宽

1)本质是对表进行两次全文本搜索(找出可能相关的结果,即使不能包含搜索指定的词)

查询扩展执行过程:

1)执行基本的全文本搜索,找出与搜索条件匹配的所有行,系统会检索这些匹配行并选择所有有用的词;

2)根据上次全文本搜索返回的词,再次执行全文本搜索

如:使用查询扩展搜索productnotes表中相关anvils词的行

1)搜索匹配的行,并检索有用的词
 
2)根据返回的词,再词执行全文本搜索
 

布尔文本搜索

布尔文本搜索(IN BOOLEAN MODE):全文本搜索的另一种形式

1)不同于全文本搜索在于其没有FULLTEXT索引也可查询

如:使用布尔文本搜索productnotes表中相关heavy词的行
 

布尔操作符 说明
+ 包含
- 不包含
> 若包含,则增加等级
< 若包含,则减少等级
() 包词组成子表达式
~ 取消一个词的排序值
* 通配符
“” 定义一个短语

如:使用布尔文本搜索productnotes表中相关heavy和不包含rope开头的行
 

如:使用布尔文本搜索productnotes表中同时包含rabbit和bait的行
 

如:使用布尔文本搜索productnotes表中包含rabbit或bait的行
 

如:使用布尔文本搜索productnotes表中包含rabbit bait短语的行
 

如:使用布尔文本搜索productnotes表中包含safe和combination的行,搜索到combination则减少等级