11.4 SQL语句

八、BETWEEN 操作符

 BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期  

语法

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

eg 选取 alexa 介于 1 和 20 之间的所有网站：

SELECT * FROM Websites WHERE alexa BETWEEN 1 AND 20;

运行结果

如需显示不在上面实例范围内的网站，使用 NOT BETWEEN：

SELECT * FROM Websites WHERE alexa NOT BETWEEN 1 AND 20;

运行结果

##带有IN的BETWEEN 实例

eg 选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站：

SELECT * FROM Websites WHERE (alexa BETWEEN 1 AND 20) AND country NOT IN ('USA', 'IND');

运行结果

带有文本值的 BETWEEN 操作符实例

选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站：

SELECT * FROM Websites WHERE name BETWEEN 'A' AND 'H';

运行结果

带有文本值的 NOT BETWEEN 操作符实例

选取 name 不介于 'A' 和 'H' 之间字母开始的所有网站：

SELECT * FROM Websites WHERE name NOT BETWEEN 'A' AND 'H';

运行结果

以access_log 表为例

带有日期值的 BETWEEN 操作符实例

选取 date 介于 '2016-05-10' 和 '2016-05-14' 之间的所有访问记录

SELECT * FROM access_log WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

运行结果

###ATTENTION

在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。

在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。

在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。

九、别名

通过使用 SQL，可以为表名称或列名称指定别名，创建别名是为了让列名称的可读性更强。

列的别名语法

SELECT column_name AS alias_name(别名) FROM table_name;

表的别名语法

SELECT column_name(s) FROM table_name AS alias_name;

eg 列的别名实例

指定两个别名，一个是 name 列的别名，一个是 country 列的别名。提示：如果列名称包含空格，要求使用双引号或方括号：

SELECT name AS n, country AS c FROM Websites;

 运行结果

把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM Websites;

运行结果

表的别名实例

下面的 SQL 语句选取 "菜鸟教程" 的所有访问记录。我们使用 "Websites" 和 "access_log" 表，并分别为它们指定表别名 "w" 和 "a"（通过使用别名让 SQL 更简短）：

SELECT w.name, w.url, a.count, a.date FROM Websites AS w, access_log AS a WHERE a.site_id=w.id and w.name="菜鸟教程";

运行结果

不带别名的相同的 SQL 语句

SELECT websites.name, websites.url,access_log.count,access_log.date FROM websites,access_log WHERE websites.id = access_log.site_id AND websites.name = '菜鸟教程'; 

运行结果

在下面的情况下，使用别名很有用：

• 在查询中涉及超过一个表
• 在查询中使用了函数
• 列名称很长或者可读性差
• 需要把两个列或者多个列结合在一起

十、连接(JOIN)

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

JOIN 子句基于表之间的共同字段把来自两个或多个表的行结合起来。

最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。

"Websites" 表中的 "id" 列指向 "access_log" 表中的字段 "site_id"。

这两个表是通过 "site_id" 列联系起来的。

SELECT websites.id, websites.name, access_log.count, access_log.date FROM websites,access_log WHERE websites.id = access_log.site_id;

运行结果

不同的 SQL JOIN类型：

INNER JOIN：如果表中有至少一个匹配，则返回行

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

FULL JOIN：只要其中一个表中存在匹配，则返回行

1.INNER JOIN 关键字

INNER JOIN 关键字在表中存在至少一个匹配时返回行。

语法

SELECT column_name(s) 
FROM table1 
INNER JOIN table2 
ON table1.column_name=table2.column_name;

或

SELECT column_name(s) 
FROM table1 
JOIN table2 
ON table1.column_name=table2.column_name;

INNER JOIN 与 JOIN 是相同的。

返回所有网站的访问记录：

SELECT websites.name,access_log.count, access_log.date 
FROM websites 
INNER JOIN access_log
ON websites.id = access_log.site_id 
ORDER BY access_log.count;

运行结果

INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Websites" 表中的行在 "access_log" 中没有匹配，则不会列出这些行。

不管 on 中的条件是否为真，都会返回左边表中的记录。

2.LEFT JOIN 关键字

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

返回所有网站及他们的访问量（如果有的话）。

SELECT websites.id, websites.name, access_log.count, access_log.date 
FROM websites
LEFT JOIN access_log
ON websites.id = access_log.site_id
ORDER BY access_log.count DESC;

运行结果

LEFT JOIN 关键字从左表（Websites）返回所有的行，即使右表（access_log）中没有匹配。

3. RIGHT JOIN 关键字

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

语法

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

操作前先在 access_log 表添加一条数据，该数据在 Websites 表没有对应的数据：

INSERT INTO access_log (aid, site_id, count, date) VALUES('10', '10','500','2016-05-16');

返回网站的访问记录。

以下实例中我们把 Websites 作为左表，access_log 作为右表：

SELECT websites.name, access_log.count, access_log.date 
FROM websites
RIGHT JOIN access_log
ON access_log.site_id = websites.id
ORDER BY access_log.count DESC;

运行结果

RIGHT JOIN 关键字从右表（access_log）返回所有的行，即使左表（Websites）中没有匹配。

4.FULL OUTER JOIN 关键字

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

###但是在Mysql上无法使用，只能是LEFT JOIN和RIGHT JOIN 并（UNION）起来，而且，在排序时，只能放在句末，且排序的那一列不再属于任何一个表。

 详情见

​​​​​​​MySQL之Full Outer Join用法_vandet100的博客-CSDN博客

SELECT websites.id, websites.name, access_log.count, access_log.date 
FROM websites
LEFT JOIN access_log
ON websites.id = access_log.site_id
UNION
SELECT websites.id, websites.name, access_log.count, access_log.date 
FROM websites
RIGHT JOIN access_log
ON websites.id = access_log.site_id
ORDER BY count DESC;

count不再属于access_log。

运行结果

 

注释：FULL OUTER JOIN 关键字返回左表（Websites）和右表（access_log）中所有的行。如果 "Websites" 表中的行在 "access_log" 中没有匹配或者 "access_log" 表中的行在 "Websites" 表中没有匹配，也会列出这些行。

####

A inner join B 取交集。

A left join B 取 A 全部，B 没有对应的值为 null。

A right join B 取 B 全部 A 没有对应的值为 null。

A full outer join B 取并集，彼此没有对应的值为 null。

对应条件在 on 后面填写。

十一、UNION 操作符

UNION 操作符合并两个或多个 SELECT 语句的结果。

UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

SQL UNION语法

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）：

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

运行结果

注释：UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。使用 UNION ALL 来选取重复的值！

UNION ALL 实例

使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）：

SELECT country FROM websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

运行结果

带有 WHERE 的 SQL UNION ALL

使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据（也有重复的值）：

SELECT name, country FROM websites
WHERE country = 'CN'
UNION ALL
SELECT app_name, country FROM apps
WHERE country = 'CN';

 ##两个里都要有WHERE语句

运行结果