Websites表格数据:
id | name | url | alexa | country |
---|---|---|---|---|
1 | https://www.google.cm/ | 1 | USA | |
2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
4 | 微博 | http://weibo.com/ | 20 | CN |
5 | https://www.facebook.com/ | 3 | USA | |
6 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
通配符
选取 name 以一个任意字符开始,然后是 “oogle” 的所有客户:
SELECT * FROM Websites
WHERE name LIKE '_oogle';
选取 name 以 “G”、“F” 或 “s” 开始的所有网站:
SELECT * FROM Websites
WHERE name REGEXP '^[GFs]';
选取 name 以 A 到 H 字母开头的网站:
SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';
选取 name 不以 A 到 H 字母开头的网站
SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';
IN
选取 name 为 “Google” 或 “菜鸟教程” 的所有网站:
SELECT * FROM Websites
WHERE name IN ('Google','菜鸟教程');
BETWEEN
选取 alexa 介于 1 和 20 之间的所有网站:
SELECT * FROM Websites
WHERE name BETWEEN 1 AND 20;
INNER JOIN
INNER JOIN 关键字在表中存在至少一个匹配时返回行。
“access_log”表格记录了网站的访问数据:
aid | site_id | count | date |
---|---|---|---|
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 Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log