MySQL 24小时入门笔记(2),查询

原创
2018/06/23 15:41
阅读数 84

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是否在010的区间内。语法是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,收录了所有接收高等级超能力者的班级,能进入这些班级的学生都是如同能考上985211般恐怖如斯的存在。

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_classON表示查询的对象要符合条件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 JOINRIGHT 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;

我们注意到……我就是把 studentstop_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表中每个行的classs2表里有没有行具有同样的class属性,同时,s2具有和s1同样class属性的行还必须有个stu1name

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

id name class
1 stu1 1

这时候再去看s1表,s1表的class同时存在于s2表的行只有12了。

OUTER JOIN

其实OUTER JOIN上面的LEFT JOINRIGHT JOIN已经讲过了,LEFT JOIN的完整写法就是LEFT OUTER JOINRIGHT 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提供了全文本搜索的属性来帮助索引文本(但是想到中文支持我觉得已经凉的差不多了),快速查询出包含特定词汇之类的行。

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

跳过了跳过了。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部