MySQL 24小时入门笔记（2）

查询

SELECT

SELECT是一个特殊的关键字，它的语义是查询，取出结果。

注意：仅为个人理解。

FROM

FROM子句，标识要查询的对象的来源，来源可能是多个的。在查询有多个来源表的情况下，称之为联结查询（Join query）。

最常见的常规写法是SELECT column FROM table，表示从特定表取出所有行的特定列。

WHERE

WHERE子句用于过滤查询的行，只有满足条件的行会被查询出来。

常见的用法有SELECT column FROM table WHERE column <> 0，表示在table表中查询column非空的行，返回这些行的column。

其中的二元关系运算符<>表示不等于，其他常见的关系运算符还有这些。

运算符

含义

=

相等

>

大于

<

小于

>=

大于等于

<=

小于等于

!=

不等于

<>

不等于

此外还有一些SQL关键字可以辅助编写判断逻辑。

SQL关键字IN可以用于判断元素是否在集合中。举例，SELECT 1 IN (1,2,3)，查询1是否在1,2,3这个集合中。被判断的集合需要被小括号包围，并且以逗号分隔元素。

SQL关键字BETWEEN可以判断元素是否在一定区间中。举例，SELECT 1 BETWEEN 0 and 10，查询1是否在0到10的区间内。语法是BETWEEN [low] AND [high]，区间较小的一端必须在左侧，较大的一端必须在右侧。

SQL关键字LIKE可以用非常简单的通配符来判断元素是否匹配一定的规则。举例，SELECT 'abcabcabc' LIKE '%CAB%'，判断字符串abcabcabc是否匹配%CAB%。值得注意的是，模式串中的%代表的是匹配0或任意多个字符，就像是正则表达式中的*一样。此外还有_，下划线，匹配1个任意字符。

MySQL扩展的REGEXP可以用正则表达式来匹配元素是否符合模式串。举例，SELECT 'abcabcabc' REGEXP '.*cab.*'，正则表达式不做赘述，简单的模式串大家都会写。

ORDER BY

ORDER BY就像字面意义上说的那样，按照某个列来进行排序。举例来说，我有一个学生表，记录了学号和姓名，我可以按照学号排序。

SELECT * FROM students ORDER BY id;

默认排序是升序，也可以通过指定DESC或者ASC来决定怎么排。ASC是升序，DESC是降序。

SELECT * FROM students ORDER BY id DESC;

AS

AS常见的用法是建立别名。

SELECT column AS id_alias FROM my_table AS table_alias WHERE table_alias.column <> 1;

这里出现了一个新的语法细节，table_alias.column。用点.连接表名和列名的行为类似于C++中的

typedef table_alias = my_table;
auto id_alias = SELECT(table_alias::column, table_alias::column != 0);

看得出来，table_alias.column是完全限定了column是哪个column，之所以有这种语法，是因为FROM子句需要支持多个表作为查询来源。到时候可能就会用到table1.column <> 1 AND table2.column <> 2这样的写法了。

而查询开头的column AS id_alias则是标识查询结果列叫做id_alias，举例如子查询的情况下，便于引用。

JOIN

JOIN的术语叫做联结，使用了JOIN关键字的查询叫做联结查询。

联结查询和一般的查询不同的地方是，联结查询的数据来源是多个表。

最简单的联结查询是内联结查询。

举例来说，我现在有表students如下，所有学生根据超能力开发等级分配到多个班级。

id

name

class

1

stu1

1

2

stu2

2

3

stu3

3

4

stu4

4

又有表top_class，收录了所有接收高等级超能力者的班级，能进入这些班级的学生都是如同能考上985、211般恐怖如斯的存在。

id

name

1

Lv 5

2

Lv 4

3

Lv 3

现在我们要查询出学生中那些恐怖如斯的存在有哪些。

SELECT students.name AS name FROM students INNER JOIN top_class ON top_class.id = students.class;

语法JOIN [表] ON [条件]也很简单啦。在例子中，JOIN表示要联结表top_class，ON表示查询的对象要符合条件top_class.id = students.class。不好理解？看看伪代码。

for(auto student : students) { // 先过滤 students 表本身，这个过滤应该由 WHERE 子句完成
  for(auto cls : top_class) { // 然后联结表 top_class
    if(student.cls = cls.id) // 判断 ON students.class = top_class.id
      results.push(student); // 得出结果
  }
}

注意，伪代码的查询过程是错误的，为了方便理解 students.class = top_class.id 才这么写。真实数据库实现联结查询的方法应当查阅对应DBMS的文档。

注意的关键点有ON很像但不同于WHERE，在了解LEFT JOIN和RIGHT JOIN时会区分。

LEFT JOIN

LEFT JOIN又叫左联结，基本思路是写在LEFT JOIN左边的表满足条件即可作为结果，即使右边的表没有满足条件的条目。

还是以上文的学园都市数据库为例（我tm写了什么…）

学生表 students

id

name

class

1

stu1

1

2

stu2

2

3

stu3

3

4

stu4

4

班级表 top_class

id

name

1

Lv 5

2

Lv 4

3

Lv 3

现在我们查询学生都处在哪些班级，得到班级的名字。

SELECT students.name as name, top_class.name as cls
       FROM students LEFT JOIN top_class 
            ON top_class.id = students.class;

查询结果应该是这样子的。

name

cls

stu1

Lv 5

stu2

Lv 4

stu3

Lv 3

stu4

NULL

注意到了吗？stu4虽然不是top_class的学生，但是还是被查询出来了。

RIGHT JOIN

继续拿学园都市做例子……

其实是和左联结一个鸟样。

SELECT students.name as name, top_class.name as cls
       FROM top_class RIGHT JOIN students 
            ON top_class.id = students.class;

我们注意到……我就是把 students和 top_class换了个位置。查询结果其实是一样的。

name

cls

stu1

Lv 5

stu2

Lv 4

stu3

Lv 3

stu4

NULL

CROSS JOIN

交叉联结，查询结果是联结的表和FROM的表的笛卡尔积，这么说听的明白不？听不明白就算了，因为交叉联结基本用不到。

其实就是把两个表的每个行都排列组合一下：

• 表A行1-表B行1
• 表A行1-表B行2
• ……
• 表A行10-表B行1
• 表A行10-表B行2
• 表A行10-表B行3
• ……

JOIN 自己？

术语叫自联结，其实也挺好理解的，直接举个例子看看。

id

name

class

1

stu1

1

2

stu2

1

3

stu3

2

4

stu4

2

注意我数据改了哈。

现在要查询出所有和stu1同一个班级的学生。

一般我们想怎么查？先查出stu1是哪个班级的：SELECT class FROM students WHERE name = 'stu1'，然后查出所有属于这个班级的学生：SELECT name FROM students WHERE class = [上次查出来的班级]。

那么…怎么写成一句话呢？

这时候自联结就可以上场了。

SELECT s1.id, s1.name, s1.class
FROM students AS s1 INNER JOIN students AS s2 
WHERE s1.class = s2.class 
    AND s2.name = 'stu1';

查询结果是

id

name

class

1

stu1

1

2

stu2

1

基本思路是这样的：FROM的表是s1，因此INNER JOIN查询结果来自s1而不是s2。查找s1表中每个行的class在s2表里有没有行具有同样的class属性，同时，s2具有和s1同样class属性的行还必须有个stu1的name。

分析得知，s2中有stu1这个name的行只有1，所以s2表其实长这样。

id

name

class

1

stu1

1

这时候再去看s1表，s1表的class同时存在于s2表的行只有1和2了。

OUTER JOIN

其实OUTER JOIN上面的LEFT JOIN和RIGHT JOIN已经讲过了，LEFT JOIN的完整写法就是LEFT OUTER JOIN，RIGHT JOIN就是RIGHT OUTER JOIN，和INNER JOIN的区别在于OUTER JOIN包含了指定表里不满足ON条件的行。

这有个知识点，就是ON条件不过滤指定OUTER JOIN的表的不满足条件的行，但是WHERE会过滤。

UNION

UNION关键字的术语是联合查询。

作用是将多个SELECT的结果放在一起并返回。

举个例子……我们要查询全美最好的大学american_top_college和中国最好的大学chinese_top_college数据，来决定报考哪个大学（反正都考不上），如果不想写成两句SELECT，然后手工合并成一个表格的话，那么就用UNION查询吧。

SELECT 'american' AS nation, american_top_college.name AS college_name, american_top_college.score_line AS score_line 
FROM american_top_college
UNION
SELECT 'china' AS nation, chinese_top_college.name AS college_name, chinese_top_college.score_line AS score_line;

查询结果…不展示了。

还有个细节可能要注意，如果有大学同时是美国大学和中国大学的话，那么为了在联合查询中排除相同的项目，可以使用UNION ALL而不是UNION。

FULLTEXT

MySQL支持一种实用的文本索引方式，叫做全文本搜索。大家都知道，正则表达式和简单通配符来查找文本是非常消耗性能的操作，而且难以优化（反正我想不出任何减少查询的优化思路）。MySQL提供了全文本搜索的属性来帮助索引文本（但是想到中文支持我觉得已经凉的差不多了），快速查询出包含特定词汇之类的行。

抱歉我觉得不行。不说别的，中文分词就……

跳过了跳过了。