联结表

联结

SQL最强大的功能之一是联结。

使用联结可以将不同表中的列联结在一起

这里的联结只是一种表达形式，没有真正的形成物理意义上的联结后的数据库

为什么要使用联结库

因为一般情况下，数据库中会出现相同的数据。

外键：外键为某个表中的一列，它包含领一个表的主键值，定义了两个表之间的关系

使用外键进行小数据库关联，而不是使用一个统一的大的数据库的好处

• 数据不重复，不会浪费空间和时间
• 更新数据时，只需要更改需要涉及的数据库中的数据
• 数据没有重复的，处理数据更加简单
• 关系数据库的可伸缩性远比非关系数据库要好

可伸缩性：能够适应不断增加的工作量而不失败。涉及良好的数据库或应用程序称之为可伸缩性好

为什么要使用联结

联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

重要的是：联结不是物理实体，联结有MySQL根据需要创建，它存在于子查询的执行当中。

创建联结

创建联结十分容易（相对于使用嵌套子查询来说）

语法结构

1
2
3
4

SELECT 表格1中的列名,表格2中的列名,表格1中的列名,.....
FROM 表格1,表格2
SELECT 限制条件
ORDER BY 列名(可以属于表格1，也可以属于表格2)

我们需要注意在限制条件需要用到列名时应该使用完全限定列名，因为可能不同表中有列名相同的，此时系统不知道到底是哪个。

完全限定列名：在引用的列可能出现二义性是，必须使用完全限定列名（用一个点分隔表名和列名）

WHERE子句的重要性

在对两个表格进行联结时，必须要注意WHERE子句的使用。如果不适用WHERE子句对数据进行过滤，第一个表中的每一行将与第二个表中的每一行进行配对，不管他们逻辑上是否可以配在一起。

笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

内部联结

语法结构

1
2
3

SELECT 表格1中的列名,表格2中的列名,表格1中的列名,....
FROM 表格名1 INNER JOIN 表格名2
ON 限制条件;

在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

联结多个表

SQL对于一条SELECT语句中可以联结的表的数目没有限制。

有一点要注意，在SELECT中出现的列所属的表格名一定要在FROM子句中出现

性能考虑：联结的表越多，性能下降月厉害，因此在使用联结时要考虑性能。

使用数据处理函数

函数

与其他计算机语言一样，SQL支持利用函数来处理数据。

使用函数

• 用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数
• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数
• 用于处理日期和时间值并从这些值中提取特点成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数
• 返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数

文本处理函数

常见的文本处理函数

重点：我们这里要注意一个非常有去的函数Soundex函数

**SOUNDEX( )**函数，可以返回发音相同的列值，（应该只限于英文文本）

日期和时间处理函数

常用日期和时间处理函数

由于MySQL中的日期和时间是采用相应的数据类型和特殊的格式储存，所以，通过函数可以快速且方便地对时间数据进行处理

常用日期和时间处理函数

不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd，这样的好处是可以排除多义性

数值处理函数

数值函数是最一致最统一的函数

常用数值处理函数

分组数据

数据分组

我们使用汇总数据可以实现对行的计数、计算求和、求平均值、获得最小值或最大值。

分组允许我们把数据分为多个逻辑组，以便对每个分组进行聚类运算

创建分组

分组是通过SELECT语句中的GROUP BY子句实现的

语法结构

1
2
3

SELECT 列表名 COUNT(*) AS XX
FROM 表格
GROUP BY 列表名;

这样的语法可以实现对列表的分组进行计数汇总

使用GROUP BY子句须知：

• GROUP BY子句可以包含任意数目的列。这使得能够对分组进行嵌套，为数据分组提供更细致的控制。
• 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别列取回数据）
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚类函数）。如果SELECT中使用表达式，则必须在GROUP BY子句中使用相同的表达式。不能使用别名
• 除聚类计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，则他们会被分为一组
• GROUP BY子句必须在WHERE子句之后，ORDER BY子句之前。

对于GROUP BY子句，其还有一个ROLLUP关键字：使用WHERE ROLLIP关键字，可以得到每个分组以及每个分组汇总级别的值

过滤分组

有时我们使用ORDER BY语句也能实现视觉上对数据库中某一列的数据进行了分组（将值相同的分到一起）

通过使用WHERE语句似乎也能对数据进行过滤（但这种过滤不是针对组的，而是针对列的）

可这种分组和过滤只是视觉上的，我们并不能对其进行过滤等的操作

真正实现对数据的分组需要用到GROUP BY，真正实现过滤需要用到HAVING

HAVING支持所有的WHERE操作符

语法结构

1
2
3
4

SELECT 列名 COUNT(*) AS XX
FROM 表格名
GROUP BY 列名
HAVING 条件;

上边的语法就实现的列出符合条件的组的组名以及组值

HAVING与WHERE的区别

由两者语法结构的不同可以看出：

• HAVING是对分组过得数据进行过滤
• WHERE是对分组前的数据进行过滤

分组和排序

总的来说排序可以实现虚假的分组

ORDER与GROUP BY的区别

由于GROUP BY输出的可能不是分组的顺序，所以我们要使用ORDER BY，来实现输出的是正确的排序。

SELECT子句排序

汇总数据

聚集函数

用SELECT检索的例子

• 确定表中函数（或者满足某个条件或包含某个特定值得行数）
• 获得表中行组的和
• 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值

聚集函数：运行在行组上，计算和返回单个值得函数

SQL聚集函数

聚集不同值

• 对所有的行执行计算，指定ALL参数或不给阐述
• 只包含不同的值，指定DISTINCT参数

在聚类时，如果遇到行值相同就跳过，值聚类行值不同的

创建计算字符

字段：基本上与列的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上

拼接字段

拼接：将值联结到一起构成单个值

在MySQL的SELECT语句中，可以1使用Concat()函数来拼接两个列。

语法结构

1
2
3

SELECT Concat(列名,' (',列名,')')
FROM 表格
ORDER BY 列名;

元素分析：Concat( )拼接串，即把多个串连接起来形成一个较长的串

Concat( )需要一个或多个指定的串，各个串之间用逗号分隔。上边的SELECT语句连接需要以下四个元素：

• 存储在XX列中的名字
• 包含一个空格和一个左圆括号的串；
• 存储在XX列中元素；
• 包含一个右圆括号的串

删除数据右侧多余的空格来整理数据，用RTrim()函数来实现

RTrim（）函数去掉值右边的所有空格。通过使用RTrim()对列进行整理。

LTrim（）函数去掉值左边的所有空格。

使用别名

别名：是一个字段或值得替换名。

别名需要使用AS关键字赋予

语法结构

1
2
3

SELECT Concat(列名,' (',RTrim,')') AS 别名
FROM 表格
ORDER BY 列名;

如果没使用列名，客户机不能使用被拼接的字符，而使用别名后，客户机就能对其进行引用了。

执行算术计算

可以对列中的数值进行算术计算。

MySQL算术操作符

用正则表达式进行搜索

正则表达式的介绍

正则表达式的使用环境

• 如果你想从一个文本文件中提取电话号码
• 如果你需要查找名字中间有数字的所有文件
• 如果你想在一个文本块中找到所有重复的单词
• 如果你想替换一个页面中的所有URL为这些URL的实际HTML链接

基本字符匹配

语法结构

1
2
3
4

SELECT 列名
FROM 表格
WHERE 列名 REGEXP 'XX'
ORDER BY 列名

表示返回列中列名中包含(XX)子列的所有行

除了关键字LIKE被REGEXP代替外，这个语法结构和LIKE的语句相同

LIKE与REGEXP的区别

如果LIKE语句想和REGEXP语句有相同的作用，需要结合(_或%)来使用

进行OR匹配

语法结构

1
2
3
4

SELECT 列名
FROM 表格
WHERE 列名 REGEXP 条件一 | 条件二
ORDER BY 列名;

这个语句用于检索列表中满足条件一或条件二的所有行

匹配几个字符之一

匹配任何单一字符。但是如果匹配特定的字符，怎么办，可以通过指定一组用[和]括起来的字符来完成。

语法结构

1
2
3
4

SELECT 列名
FROM 表格
WHERE 列名 REGEXP '[abc] XX'
ORDER BY 列名;

该语句意义为：删选除列值满足a XX 或 b XX 或 c XX的所有的列

正则表达式[abc]xx是[a|b|c]xx的缩写

匹配范围

要是匹配范围，将匹配字符的[abc]换成[a-c]即可

匹配特殊字符

要匹配列值中包含特殊字符，必须使用\ \为作为前导

空白元字符

匹配字符类

字符类

匹配多个实例

重复元字符

定位符

定位元字符

用通配符进行过滤

LIKE操作符

通配符：用来匹配值的一部分的特殊字符。

百分号（%）通配符

最常使用的通配符是百分号(%),%表示任何字符出现任意次数。

语法结构

1
2
3

SELECT 列名
FROM 表格
WHERE 列名 LIKE 'XX%'

表示用于搜索以“XX”开头的产品，是需要区分大小写的

如果结构是（“%XX%”）

表示匹配任何位置包含文本XX的值，不论它之前或之后出现什么字符

如果结构是（“XX%xx”）

表示匹配以XX开头，以xx结尾的数据

下划线(_)通配符

另一个有用的通配符是下划线(_)。下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符

排序检索数据

排序数据

使用ORDER BY子句可以进行排序，子句可以取一个或多个列的名字

自己排序是是根据其对应的数字大写升序排序的

语法结构

1
2
3

SELECT 列名
FROM 表名
ORDER BY 列名;

按多个列排序

这里的“按多个列排序”是有比较的先后级的

• 先按照选择的第一列进行排序
• 在对第一列中重复的按照第二列进行排序，以后的以此中模式进行

1
2
3

SELECT 列名,列名,列名
FORM 表名
ORDER BY 列名,列名;

指定排序方向

数据排序在默认情况下是按照升序排序的，不过也可以通过使用DECS来实现降序排序

语法结构

1
2
3

SELECT 列名
FROM 表名
ORDER BY 列名 DESC;

注意：如果要实现多个列上进行降序排序，必须在每个列指定DESC关键字

过滤

组合WHERE子句

为了进行更强的过滤控制，MySQL允许给出多个WHERE子句，以AND子句的方式或OR子句的方式使用

AND操作符

关键字AND用于添加多个过滤条件，每添加一条就要使用一个AND

语法结构

1
2
3

SELECT 列名
FROM 表格
WHERE 条件一 AND 条件二;

意义:筛选出即符合条件一并且符合条件二的数据

OR操作符

关键字OR也是用于添加过滤条件，每添加一条条件就需要使用一个OR

语法结构

1
2
3

SELECT 列名
FROM 表格
WHERE 条件一 OR 条件二;

意义:筛选出符合条件一或者符合条件二的数据

计算次序

在复合使用AND和OR时，需要注意，AND的优先级要高于OR，因此如果要正确的表达你所要表达的意思，一般配合括号**()**来使用。

IN操作符

IN操作符用来指定条件范围，范围中的每一个条件都可以进行匹配。IN取合法值得由逗号分隔的清单全部括在圆括号里.

语法结构

1
2
3

SELECT 列名
FROM 表格
WHERE 列名 IN (xx,XX);

IN(xx,XX)是等同于xx OR XX

那么为什么要是用IN而不是OR呐？

使用IN操作符的优势

• 在使用长的合法选项清单时，IN操作符的语法更加清楚，且更加直观
• 在使用IN时，计算的次序容易管理
• IN操作符一般比OR操作符清单执行更快
• IN的最大优点在于可以包含其他SELECT语句，是能够更动态地建立WHERE子句。

NOT操作符

NOT操作符用于对其他条件进行否定，因此NOT操作符要配合其他条件一起使用

语法结构

1
2
3

SELECT 列名
FROM 表格
WHERE 列名 NOT 条件;

MySQL中的NOT ，支持对IN、BETWEEN和EXISTS子句取反

过滤数据

使用WHERE子句

数据WHERE子句中指定的搜索条件进行过滤WHERE子句在表名（FROM子句）之后给出。

语法结构

1
2
3

SELECT 列名
FROM 表格
WHERE 条件;

它检查的是一个列是否具有指定的值据此进行过滤

WHERE子句操作符

WHERE子句操作符

注意，在比较时，我们有事会使用到单引号（’’）

• 当比较的数据是数字类型则不需要用单引号
• 当比较的数据是字符串类型则需要用单引号将子句括起来

范围值检查

范围值检查使用的是关键字BETWEEN

在使用BETWEEN时，必须给出范围所需的低端值和高端值，这两个值必须使用AND关键字分隔.。

语法结构

1
2
3

SELECT 列名
FROM 表格
WHERE 列名 BETWEEN x AND x;

空值检查

空值检查是可以用**(NULL)**表示的，但是可以检索到，不能提取出来

语法结构

1
2
3

SELECT 列名
FROM 表格
WHERE 列名 IS NULL;