MySQL-2

前言

对MySQL概念常用命令的总结第二部分:
子查询
联结表
组合查询
全文本搜索
插入数据
更新删除数据

子查询

SQL中的查询即指SELECT语句,可用子查询做为WHERE中的过滤条件,子查询可以针对不同的表。在WHERE中子查询前通常用IN操作符结合使用。

子查询也可作为计算字段,用SELECT显示出来。对于有歧义的列名(通常在子查询的过滤条件处需要来自不同表的列值匹配,一个表列的所有记录会去另一个表中匹配)要用完全限定名指出。

联结表

一张表拆成多张关系表,分开存储可以大大减少信息的重复。采用一个外键来替代一部分有代表性的数据。外键通常是另一个表的主键,此时外键可唯一替换成另一个表中主键所对应的一条记录。如果不是唯一的主键则会拓展为所有拥有这个值的记录。外键实现“一对多”的关系,“一”是外键(在另一张表中是主键),“多”是包含外键的记录。

引入外键后数据的插入会关系到所关联的表,只允许出现合法值。

表的联结意味着在一条SELECT语句中根据指定的关系,将多个关系表合并在一起。

创建联结

用where子句创建联结(完全限定名!):

1
2
3
4
SELECT class_name,teacher_name,teacher_age
FROM classes,teachers
WHERE classes.teacher_id=teachers.teacher_id
ORDER BY class_name,teacher_name;

若没有where则输出笛卡尔积。

内部联结:

1
2
3
SELECT 表1列,表2列
FROM 表1 INNER JOIN 表2
ON 表1.主键=表2.外键;

与用where子句形成的联结等价。

一次可以联结多个表。联结可以和子查询做到相同的查询结果。联结要比子查询更快

表别名

SELECT 列 FROM 表 AS 表别名;表别名一般用于自联结。

自联结

将一张表视为不同的两张表(用别名区分),联结后通常用完全限定名根据一个表的字段过滤SELECT另一个表的字段。

自然联结

建立的内部链接都是自然联结,注意会出现重复的列,只能靠我们自己选择不重复的列显示。选择一个表所有字段用SELECT tab(表名或别名).*

外部联结

包含了那些在相关表中没有关联行的行的联结。没有关联的以NULL显示。

1
2
3
SELECT 表1列,表列
FROM 表1 LEFT(或RIGHT) OUTER JOIN 表2
ON 表1.列=表2.列;

LEFT会把左边的列(表1)全部列出来,没联结的显示NULL。RIGHT是右边。

组合查询

1
2
3
SELECT ...
UNION
SELECT ...

将多个查询结果合并到一个结果集返回,与WHERE 条件1 OR 条件2的效果相同。每个查询的列必须数据类型兼容,列的个数必须相同,列名不必相同(但是不同没有什么意义)。UNION会自动去除重复的行,UNION ALL可以取消这一行为。
组合查询的排序ORDER BY 只能在UNION的最后一个SELECT中,将对整体排序。

全文本搜索

MySQL的数据库引擎:MyISAM和InnoDB,前者支持全文本搜索,后者支持事务处理管理。

全文本搜索能更高效、更准确、更智能地搜索,其不需要分别查看每个行,而是创建指定列各词的一个索引,搜索将针对词进行。

在创建表CREATE TABLE的时候启用全文本搜索:FULLTEXT(指定列名),也可在之后指定(不建议)。MySQL将自动维护索引。

进行搜索:WHERE Match(列名) Against(搜索表达式),Match中的列名必须和FULLTEXT中的一致,BINARY关键字区分大小写。Against中的单词将匹配部分包含的记录。全文本搜索返回的结果集是根据行中词的数目、唯一词的数目、索引中词的总数等计算出来的等级值排序的结果(比LIKE加通配符匹配更智能)。

查询拓展

Against('单词' WITH QUERY EXPANSION),两遍扫描完成搜索,放宽搜索范围,根据包含关键词的记录,分析出相关的词,第二次检索中包含与关键词相关的词的记录也将被检索出来。

布尔文本搜索

Against('搜索表达式' IN BOOLEAN MODE)。全文本布尔操作符:+-><()~*””

插入数据

1
2
3
4
5
6
7
8
9
10
INSERT INTO 表名
VALUES(
值...
);
或者
INSERT INTO 表名(
列名...)
VALUES(
值...
);

前者对每个列必须提供一个值,且必须按在表中定义的顺序填充,不指定值的用NULL。后者(更常用)根据列填充对应值,允许为NULL或自动增量可以不给出列名。

插入多行可用多个INSERT中间分号;隔开,或者一个INSERT多个VALUES关键字逗号隔开。

1
2
3
4
INSERT INTO 表名(
列名
)
SELECT...;

用SELECT的结果按照列的对应次序填充表格。

更新删除数据

1
2
3
4
UPDATE 表名
SET 列1=值1,
列2=值2
WHERE ...;

更新表的列值,不用where过滤则对所有行更新。UPDATE后加IGNORE可在同时更新多个列时忽略错误,完成部分更新。删除一条记录某个列的值只需要设为NULL即可。删除一个记录用DELETE FROM

1
2
DELETE FROM 表名
WHERE ...;

删除行,若没有where过滤则删除所有行。
删除所有行一般用TRUNCATE TABLE;

您的支持是我创造源源不断地动力