学习笔记 | SQL高级语句实践

SQL高级语句实践

/* 01 规定要返回的记录的数目。 */
SELECT * FROM Websites LIMIT 2;
SELECT TOP 50 PERCENT * FROM Websites;

/* 02 LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。*/
SELECT * FROM Websites WHERE name LIKE 'G%';
SELECT * FROM Websites WHERE name LIKE '%k';
SELECT * FROM Websites WHERE name LIKE '%oo%';
SELECT * FROM Websites WHERE name NOT LIKE '%oo%';

/*  03 SQL语句选取name不以A到H字母开头的网站。 */
SELECT * FROM Websites WHERE name REGEXP '^[^A-H]';

/*  04 SQL语句选取name以 "G"、"F" 或 "s"开始的所有网站：。 */
SELECT * FROM Websites WHERE name REGEXP '^[GFs]';

/*  05 IN */
SELECT * FROM Websites WHERE name IN ('Google','菜鸟教程');

/*  06 BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。 */
SELECT * FROM Websites WHERE alexa BETWEEN 1 AND 20;
SELECT * FROM Websites WHERE alexa NOT BETWEEN 1 AND 20;
SELECT * FROM Websites WHERE (alexa BETWEEN 1 AND 20) AND country NOT IN ('USA', 'IND');
SELECT * FROM Websites WHERE name BETWEEN 'A' AND 'H';

/* 07 别名 */
SELECT name AS n, country AS c FROM Websites;

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

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="菜鸟教程";

/* 08 JOIN */
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites INNER JOIN access_log
ON Websites.id=access_log.site_id;

SELECT 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;


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

/* FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。*/
SELECT Websites.name, access_log.count, access_log.date
FROM Websites FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

/* 09 UNION */
SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;

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

SELECT country, name FROM Websites WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps WHERE country='CN' ORDER BY country;

/* 10 INSERT INTO SELECT */
INSERT INTO Websites (name, country) SELECT app_name, country FROM apps;

INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps WHERE id=1;

💡 请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！

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

因此，请检查您的数据库是如何处理 BETWEEN 操作符！

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

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

不同的 SQL JOIN

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行

apps.sql脚本

/*
 Navicat MySQL Data Transfer

 Source Server         : 127.0.0.1
 Source Server Version : 50621
 Source Host           : localhost
 Source Database       : RUNOOB

 Target Server Version : 50621
 File Encoding         : utf-8

 Date: 05/18/2016 15:52:17 PM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `apps`
-- ----------------------------
DROP TABLE IF EXISTS `apps`;
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;

-- ----------------------------
--  Records of `apps`
-- ----------------------------
BEGIN;
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');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;