MySQL学习笔记4—进阶查询

MySQL学习笔记

本系列文章将结合书本、网络教程和实操层面，总结以新手角度学习MySQL的须知须会。

8. 进阶查询

8.1 子查询

8.1.1 用于过滤

• 概念：子查询就是嵌套在其他查询中的查询

• 用例

  SELECT cust_id from orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

  说明：子查询从内向外处理，先找到产品编号为TN2的订单编号，再查询购买该编号的顾客信息。

8.1.2 作为计算字段

• 用例

  SELECT cust_name, (SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) FROM customers;

​ 说明：返回的列为子查询创建的计算字段。

8.1.3 多列子查询

• 语句 SELECT * from tab WHERE (col1, col2) = (SELECT col1，col2 from tab WHERE...);

8.2 联结表

8.2.1 关系表

• 概念：把信息分解成多个表，各表通过常用的值(即关系)互相关联。
• 优点：避免重复储存相同信息，信息改动更方便。
• 外键：表中的一列，包含了另一个表的主键。

8.2.2 联结

• 概念：用来在一条SELECT语句中关联表的机制

• 创建：SELECT * FROM t1,t2 WHERE t1.col1 = t2.col1;

  • 使用完全限定列名(表名.列名)防止重复
  • 联结条件最少为表的数量-1。

• 笛卡尔积：由没有联结条件的表关系返回的结果

  • 语句：SELECT * FROM table1,table2;

  • 结果：从第一张表的第一行与第二张表的所有行依次组合，以此类推，得到row1*row2行。

  • 解决方法：使用联结

• 内部联结：又称等值联结，即上述提及的联结，可以用INNER JOIN…ON等价，如

  SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1;

8.3 高级联结

8.3.1 表别名

• 语句：SELECT * FROM t1 AS a,t2 AS b WHERE a.col1 = b.col1;
• 优点：缩短语句；单条语句中多次使用相同的表（用于自联结）。

8.3.2 自联结

• 概念：在同一张表的联结查询，即将一张表看成两张表
• 语句：SELECT * FROM tablename AS a,tablename AS b WHERE a.col1 = b.col1;
• 通过别名将表看成两张表

8.3.3 自然联结

• 用途：排除多次出现，使每个列只返回一次
• 方法：在SELECT语句中对一个表使用*，而对所有其他表使用明确的列。
• 语句：SELECT a.* b.column FROM t1 AS a,t2 AS b WHERE a.col1 = b.col2;

8.3.4 外部联结

• 概念：联结包含在相关表中没有关联的行。

• 左外部联结：左侧表完全显示 ,即右侧表无左侧表对应的信息也显示，没有的内容为NULL。

  • 关键字：LEFT OUTER JOINT…ON

  • 语句： SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

    说明：查询顾客ID和对应的订单号，没有下过单的顾客也显示。

• 右外部联结：右侧表完全显示

  • 关键字：RIGHT OUTER JOINT…ON

8.4 跨语句操作

8.4.1 表的复制

• 从其他表复制内容：

  • INSERT INTO t1 (col1,col2,...) SELECT col1,col2 FROM t2;

  • INSERT INTO t1 SELECT DISTINCT * FROM t2;

  分别是复制两列和复制所有不同行。

• 自我复制

  INSERT INTO tablename SELECT * FROM tablename;

8.5 组合查询

8.5.1 概念

• 定义：执行多个查询并将结果作为单个结果返回

• 场景：单次查询不同表，单次对一个表查询多次

• 关键字：UNION

8.5.2 创建

• 方法：在各条语句之间插入关键字UNION
• 注意：每个查询要包含相同的列，类型要兼容但不必完全相同。
• 结果：使用UNION会自动去除重复的行，若要保留重复行则使用UNION ALL。

8.5.3 排序

• 使用组合查询时，排序子句ORDER BY放在最后一条语句之后。
• 只能对组合结果进行相同类型的排序。

8.6 索引

8.6.1 概念

• 用途：提高查询效率

• 类型

  1. 主键索引，即主键都为索引
  2. 唯一索引，UNIQUE键为索引
  3. 普通索引，定义为INDEX
  4. 全文索引（8.7详述）

• 规则：适合于常用查询项(如ID)，

  ​ 不适合于唯一性差、频繁更新、不会出现在WHERE字句中的字段。

8.6.2 创建

• 语句

  1. CREATE [UNIQUE] INDEX indexname ON tablename(col);
  2. ALTER TABLE tablename ADD PRIMARY KEY(col);
  3. ALTER TABLE tablename ADD INDEX indexname (col);

• 说明

  • 语句1创建普通或唯一索引，对不变的列优先考虑唯一索引。
  • 语句2创建主键索引

8.6.3 显示和删除

• 显示：

  • SHOW INDEX FROM tablename;
  • SHOW INDEXED FROM tablename;
  • SHOW KEYS FROM tablename;

• 删除：

  • DROP INDEX indexname ON tablename;
  • ALTER TABLE tablename DROP PRIMARY KEY;(删除主键索引)

8.7 全文本搜索

8.7.1 概念

• 定义：通过创建指定列中各词的索引达到快速匹配的过程
• 特点：搜索文本，返回行。
• 支持：MyISAM引擎

8.7.2 创建

• 语句

  1. CREATE TABLE tablename (col1 text , col2 ty2,..., FULLTEXT(col) ENGINE=MyISAM;

  2. ALTER TABLE tablename ADD FULLTEXT(col);

• 说明：推荐在导入数据后再启用索引，即语句2。

8.7.3 搜索

• 函数

  • MATCH(col) ：指定被搜索的列
  • AGAINST(str)：指定使用的搜索表达式

• 用例

  SELECT note_text FROM notes WHERE MATCH(note_text) AGAINST('rabbit');

  在note_text列中搜索含rabbit的内容，具有较高等级的行先返回。

8.7.4 查询扩展

• 含义：找出与搜索对象相关的项

• 语句

  SELECT col FROM tablename WHERE MATCH(col) AGAINST(‘str’ WITH QUERY EXPANSION);

8.7.5 布尔文本搜索

• 用途：可查询出匹配词、排斥词、排列提示、表达式分组和其他一些内容

• 特点：没有定义索引也可使用

• 语句

  SELECT col FROM tablename WHERE MATCH(col) AGAINST(‘str’ IN BOOLEAN MODE);

• 全文本布尔操作符

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不存在
>	包含，而且增加等级值
<	包含，且减少等级值
()	把词组成子表达式
~	取消一个词的排序值
*	词尾的通配符
“”	定义一个短语

• 用例

  1. SELECT note_text FROM notes WHERE MATCH(note_text) AGAINST('heavy -rope*');

     查询含heavy且不含rope开头的词。

  2. SELECT note_text FROM notes WHERE MATCH(note_text) AGAINST('"rabbit bait"');

     查询rabbit bait词组。