本文共 3716 字，大约阅读时间需要 12 分钟。

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_infoFROM Websites;SELECT Websites.name, Websites.url, access_log.count, access_log.dateFROM 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.dateFROM Websites INNER JOIN access_logON Websites.id=access_log.site_id;SELECT Websites.name, access_log.count, access_log.dateFROM Websites LEFT JOIN access_logON Websites.id=access_log.site_id ORDER BY access_log.count DESC;SELECT Websites.name, access_log.count, access_log.dateFROM access_log RIGHT JOIN WebsitesON 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.dateFROM Websites FULL OUTER JOIN access_logON Websites.id=access_log.site_idORDER 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 ALLSELECT 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;

转载地址：https://blog.csdn.net/qq_34170700/article/details/104501739 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！