SQL进阶之子句、关键字和操作符
1. 创建实验资源
开始实验之前，您需要先创建实验相关资源。
在实验室页面，单击创建资源。
（可选）在实验室页面左侧导航栏中，单击云产品资源列表，可查看本次实验资源相关信息（例如IP地址、用户信息等）。
说明：资源创建过程需要3~5分钟。
2. 连接数据库并创建数据库和数据表
本步骤指导您如何连接ECS实例上的MySQL数据库，并查看实验所使用的数据库和表。
1、执行如下命令，登录数据库。
mysql -uroot -p
2、输入root用户登录密码，密码为Test123!。
返回结果如下，表示登录成功。
3、执行如下SQL语句，查看所有的数据库。
show databases;
返回结果如下，您可查看到MySQL数据库中有五个数据库，其中Test数据库是实验室提前创建好的，后续实
验所有操作都在Test数据库中执行。
4、执行如下SQL语句，选择数据库Test。
use Test;
5、执行如下SQL语句，查看所有的表。
show tables;
返回结果如下，您可查看到Test数据库中有一个名为Test表，其中Test表是实验室提前创建好的，后续实验所有操作都在Test表中执行。
6、执行如下SQL语句，查询Test表中所有数据。
select * from Websites;
返回结果如下，您可查看到Test表中所有数据
3、WHERE子句
WHERE子句用于提取满足指定标准的记录。
- WHERE子句的语法：
SELECT column_name,column_nameFROM table_nameWHERE column_name operator value;
- WHERE子句的示例：
执行如下SQL语句，从Websites表中选取国家为CN的所有网站。
SELECT * FROM Websites WHERE country='CN';
- WHERE子句中的文本字段和数值字段。
在SQL语句的WHERE子句中，文本字段使用单引号来环绕文本值（大部分数据库系统也接受双引号）。在上个示例中'CN'文本字段使用了单引号。如果是数值字段，请不要使用引号。
执行如下SQL语句，从Websites表中选取id为1的网站。
SELECT * FROM Websites WHERE id=1;
- WHERE子句中的运算符。
下方的运算符可以在WHERE字句中使用。
运算符 |
描述 |
= |
等于 |
<> |
不等于 说明：在SQL的一些版本中，该操作符可被写成！=。 |
> |
大于 |
< |
小于 |
>= |
大于等于 |
<= |
小于等于 |
BETWEEN |
在某个范围内 |
LIKE |
搜索某种模式 |
IN |
制定针对某个列的多个可能值 |
4. AND和OR运算符
AND和OR运算符用于基于一个以上的条件对记录进行过滤。
如果第一个条件和第二个条件都成立，则AND运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则OR运算符显示一条记录。
- AND运算符的示例：
执行如下SQL语句，从Websites表中选取国家为CN且rank排名大于50的所有网站。
SELECT * FROM Websites WHERE country='CN'AND rank > 50;
- OR运算符的示例：
执行如下SQL语句，从Websites表中选取国家为USA或者CN的所有客户。
SELECT * FROM Websites WHERE country='USA'OR country='CN';
- 结合AND和OR运算符的示例：
执行如下SQL语句，从Websites表中选取rank排名大于15且国家为CN或USA的所有网站。
SELECT * FROM Websites WHERE rank > 15 AND (country='CN' OR country='USA');
5. ORDER BY关键字
ORDER BY关键字用于对结果集按照一个列或者多个列进行排序。
ORDER BY关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用DESC关键字。
- ORDER BY的语法：
SELECT column_name,column_nameFROM table_nameORDER BY column_name,column_name ASC|DESC;
- ORDER BY的示例：
执行如下SQL语句，从Websites表中选取所有网站，并按照rank列排序。
SELECT * FROM Websites ORDER BY rank;
- ORDER BY DESC的示例：
执行如下SQL语句，从Websites表中选取所有网站，并按照rank列降序排序。
SELECT * FROM Websites ORDER BY rank DESC;
- ORDER BY多列的示例：
执行如下SQL语句，从Websites表中选取所有网站，并按照country和rank列排序。
SELECT * FROM Websites ORDER BY country,rank;
6. SELECT TOP, LIMIT, ROWNUM子句
SELECT TOP, LIMIT, ROWNUM子句用于规定要返回的记录的数目。其中，SELECT TOP子句适用于SQL Server数据库，SELECT LIMIT子句适用于MySQL数据库，SELECT ROWNUM适用于Oracle。
说明：因本实验安装的是MySQL数据库:，所以以SELECT LIMIT子句为例。
- SELECT LIMIT子句的语法。
SELECT column_name(s)FROM table_nameLIMIT number;
- SELECT LIMIT子句的示例：
执行如下SQL语句，从Websites表中选取选取头两条记录。
SELECT * FROM Websites LIMIT 2;
7. LIKE操作符
LIKE操作符用于在WHERE子句中搜索列中的指定模式。
- LIKE操作符的语法：
SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern;
- LIKE操作符的示例：
执行如下SQL语句，从Websites表中选取name列以字母G开始的所有网站。
说明：%符号用于在模式的前后定义通配符（缺省字母）。您将在下一步骤中学习更多有关通配符的知识。
SELECT * FROM WebsitesWHERE name LIKE 'G%';
执行如下SQL语句，从Websites表中选取name列以字母k"结尾的所有网站。
SELECT * FROM WebsitesWHERE name LIKE '%k';
执行如下SQL语句，从Websites表中选取name列包含模式oo的所有网站。
SELECT * FROM WebsitesWHERE name LIKE '%oo%'
通过使用NOT关键字，您可以选取不匹配模式的记录。
执行如下SQL语句，从Websites表中选取name列不包含模式oo的所有网站。
SELECT * FROM WebsitesWHERE name NOT LIKE '%oo%';
8. 通配符
通配符可用于替代字符串中的任何其他字符。
在SQL中，通配符与SQL LIKE操作符一起使用。SQL通配符用于搜索表中的数据。
- 在SQL 中，可使用以下通配：
通配符 |
描述 |
% |
替代0个或多个字符 |
- |
替代一个字符 |
[charlist] |
字符列中的任何单一字符 |
[^charlist]或[!charlist] |
不在字符列中的任何单一字符 |
- 使用%通配符。
执行如下命令，选取url以字母https开始的所有网站。
SELECT * FROM WebsitesWHERE url LIKE 'https%';
执行如下命令，选取url包含字母oo的所有网站。
SELECT * FROM WebsitesWHERE url LIKE '%oo%';
- 使用_通配符。
执行如下命令，选取name以一个任意字符开始，然后是oogle的所有网站。
SELECT * FROM WebsitesWHERE name LIKE '_oogle';
执行如下命令，选取name以字母G开始，然后是一个任意字符，然后是字母o，然后是一个任意字符，然后是字母le的所有网站。
SELECT * FROM WebsitesWHERE name LIKE 'G_o_le';
- 使用[charlist]通配符。
说明：MySQL中使用REGEXP或NOT REGEXP运算符（或 RLIKE 和 NOT RLIKE） 来操作正则表达式。
执行如下命令，选取name以G、F或s开始的所有网站。
SELECT * FROM WebsitesWHERE name REGEXP '^[GFs]';
执行如下命令，选取name不以A到H字母开头的网站。
SELECT * FROM WebsitesWHERE name REGEXP '^[^A-H]';
9. IN操作符
IN操作符允许您在WHERE子句中规定多个值。
- IN操作符的语法：
SELECT column_name(s)FROM table_nameWHERE column_name IN (value1,value2,...);
- IN操作符的示例：
执行如下语句，选取name为Google或淘宝的所有网站。
SELECT * FROM WebsitesWHERE name IN ('Google','淘宝');
10. BETWEEN操作符
BETWEEN操作符选取介于两个值之间的数据范围内的值，这些值可以是数值、文本或者日期。
- BETWEEN操作符的语法：
SELECT column_name(s)FROM table_nameWHERE column_name BETWEEN value1 AND value2;
- BETWEEN操作符的示例：
执行如下语句，选取rank介于1和20之间的所有网站。
SELECT * FROM WebsitesWHERE alexa BETWEEN 1 AND 20;
- NOT BETWEEN操作符实例。
执行如下语句，选取rank不介于1和20之间的所有网站。
SELECT * FROM WebsitesWHERE alexa NOT BETWEEN 1 AND 20;
- 带有IN的BETWEEN操作符实例。
执行如下语句，选取rank介于1和20之间但country不为USA所有网站。
SELECT * FROM WebsitesWHERE (rank BETWEEN 1 AND 20)AND NOT country IN ('USA');
- 带有文本值的BETWEEN操作符实例。
执行如下语句，选取name以介于A和H之间字母开始的所有网站。
SELECT * FROM WebsitesWHERE name BETWEEN 'A' AND 'H';
- 带有文本值的NOT BETWEEN 操作符实例。
执行如下语句，选取name不介于A和H之间字母开始的所有网站。
SELECT * FROM WebsitesWHERE name NOT BETWEEN 'A' AND 'H';
- 带有日期值的BETWEEN 操作符实例。
执行如下语句，创建一个网站访问记录表，并插入数据，然后选取date介于2016-05-10'和2016-05-14之间的所有访问记录。
DROP TABLE IF EXISTS `access_log`; CREATE TABLE `access_log` ( `aid` int(11) NOT NULL AUTO_INCREMENT, `site_id` int(11) NOT NULL DEFAULT '0' COMMENT '网站id', `count` int(11) NOT NULL DEFAULT '0' COMMENT '访问次数', `date` date NOT NULL, PRIMARY KEY (`aid`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; INSERT INTO `access_log` VALUES ('1', '1', '45', '2016-05-10'), ('2', '3', '100', '2016-05-13'), ('3', '1', '230', '2016-05-14'), ('4', '2', '10', '2016-05-14'), ('5', '5', '205', '2016-05-14'), ('6', '4', '13', '2016-05-15'), ('7', '3', '220', '2016-05-15'), ('8', '5', '545', '2016-05-16'), ('9', '3', '201', '2016-05-17'); SELECT * FROM access_logWHERE date BETWEEN '2016-05-10' AND '2016-05-14';
11. SQL别名
通过使用SQL，可以为表名称或列名称指定别名。基本上，创建别名是为了让列名称的可读性更强。
- 列的SQL别名语法：
SELECT column_name AS alias_nameFROM table_name;
- 表的SQL别名语法：
SELECT column_name(s)FROM table_name AS alias_name;
- 列的别名示例：
执行如下语句，指定了两个别名，一个是name列的别名，一个是country列的别名。
说明：如果列名称包含空格，要求使用双引号或方括号。
SELECT name AS n, country AS cFROM Websites;
执行如下语句，我们把三个列（url、rank 和 country）结合在一起，并创建一个名为site_info的别名。
SELECT name, CONCAT(url, ', ', rank, ', ', country) AS site_infoFROM Websites
- 表的别名示例：
执行如下语句，选取阿里云大学的所有访问记录。我们使用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="阿里云大学";
12. UNION操作符
UNION操作符合并两个或多个SELECT语句的结果集。
请注意，UNION内部的每个SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个SELECT语句中的列的顺序必须相同。
- UNION操作符的语法：
说明：默认地，UNION操作符选取不同的值。如果允许重复的值，请使用 UNIONALL。
SELECT column_name(s) FROM table1UNIONSELECT column_name(s) FROM table2;
- UNION操作符的示例：
执行如下语句，创建一个APP数据表，并插入数据，然后从Websites和apps表中选取所有不同的country（只有不同的值）。
CREATE TABLE `apps` ( `id` int(11) NOT NULL AUTO_INCREMENT, `app_name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称', `url` varchar(255) NOT NULL DEFAULT '', `country` char(10) NOT NULL DEFAULT '' COMMENT '国家', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; INSERT INTO `apps` VALUES ('1', 'QQ APP', 'http://im.qq.com/', 'CN'), ('2', '微博 APP', 'http://weibo.com/', 'CN'), ('3', '淘宝 APP', 'https://www.taobao.com/', 'CN'); SELECT country FROM WebsitesUNIONSELECT country FROM appsORDER BY country;
- UNION ALL的示例：
执行如下语句，从Websites和apps表中选取所有的country（也有重复的值）。
SELECT country FROM WebsitesUNION ALLSELECT country FROM appsORDER BY country;
- 带有WHERE的UNION ALL的示例：
执行如下语句，从Websites和apps表中选取所有的CN的数据（也有重复的值）。
SELECT country, name FROM WebsitesWHERE country='CN'UNION ALLSELECT country, app_name FROM appsWHERE country='CN'ORDER BY country;
13. SELECT INTO语句
SELECT INTO语句从一个表复制数据，然后把数据插入到另一个新表中。
注意：MySQL数据库不支持SELECT ... INTO语句，但支持INSERT INTO ... SELECT。因本实验使用MySQL数据库，索引本步骤仅供学习参考使用，无需操作。
- SELECT INTO的语法：
复制所有的列插入到新表中：
SELECT *INTO newtable [IN externaldb]FROM table1;
只复制希望的列插入到新表中：
SELECT column_name(s)INTO newtable [IN externaldb]FROM table1;
- SELECT INTO的示例：
执行如下语句，创建Websites的备份复件。
SELECT *INTO WebsitesBackupFROM Websites;
执行如下语句，只复制一些列插入到新表中。
SELECT name, urlINTO WebsitesBackupFROM Websites;
执行如下语句，只复制中国的网站插入到新表中。
SELECT *INTO WebsitesBackup2016FROM WebsitesWHERE country='CN';
执行如下语句，复制多个表中的数据插入到新表中。
SELECT Websites.name, access_log.count, access_log.dateINTO WebsitesBackup2016FROM WebsitesLEFT JOIN access_logON Websites.id=access_log.site_id;
14. INSERT INTO SELECT语句
INSERT INTO SELECT语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。
- INSERT INTO SELECT的语法：
从一个表中复制所有的列插入到另一个已存在的表中。
INSERT INTO table2SELECT * FROM table1;
只复制希望的列插入到另一个已存在的表中。
INSERT INTO table2(column_name(s))SELECT column_name(s)FROM table1;
- INSERT INTO SELECT的示例：
执行如下语句，复制apps表中的数据插入到Websites表中。
INSERT INTO Websites (name, country)SELECT app_name, country FROM apps;
执行如下语句，只复QQ的APP到Websites表中。
INSERT INTO Websites (name, country)SELECT app_name, country FROM appsWHERE id=1;
实验链接：https://developer.aliyun.com/adc/scenario/b84af5d2bfc144489289db0eee9e30fc
