数据研发学习笔记05：SQL复习梳理

1 SQL简介

1.1 SQL是什么

SQL 是用于访问和处理数据库的标准的计算机语言。

• SQL，指结构化查询语言，全称是 Structured Query Language。
• SQL 让您可以访问和处理数据库。
• SQL 是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。但是仍然存在着多种不同版本的 SQL 语言，为了与 ANSI 标准相兼容，它们必须以相似的方式共同地来支持一些主要的命令（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等）。除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的专有扩展！

1.2 SQL能做什么

• 面向数据库执行查询
• 可从数据库取回数据
• 可在数据库中插入新的记录
• 可更新数据库中的数据
• 可从数据库删除记录
• 可创建新数据库
• 可在数据库中创建新表
• 可在数据库中创建存储过程
• 可在数据库中创建视图
• 可以设置表、存储过程和视图的权限

1.3 RDBMS

RDBMS 指关系型数据库管理系统，全称 Relational Database Management System。

RDBMS 是 SQL 的基础，同样也是所有现代数据库系统的基础，比如 MS SQL Server、IBM DB2、Oracle、MySQL 以及 Microsoft Access。

RDBMS 中的数据存储在被称为表的数据库对象中。

表是相关的数据项的集合，它由列和行组成。

2 SQL基本命令

您需要在数据库上执行的大部分工作都由 SQL 语句完成。SQL 对大小写不敏感：SELECT 与 select 是相同的。

某些数据库系统要求在每条 SQL 语句的末端使用分号。
分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的 SQL 语句。
在本教程中，我们将在每条 SQL 语句的末端使用分号。

2.1 SELECT

SELECT 语句用于从数据库中选取数据。结果被存储在一个结果表中，称为结果集。

语法如下：

SELECT column_name,column_name
FROM table_name;

或

SELECT * FROM table_name;

SELECT DISTINCT 语句用于返回唯一不同的值。在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。DISTINCT 关键词用于返回唯一不同的值。

语法如下：

SELECT DISTINCT column_name,column_name
FROM table_name;

2.2 WHERE

WHERE 子句用于提取那些满足指定条件的记录。

语法如下：

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

下面的运算符可以在 WHERE 子句中使用：

运算符	描述
=	等于
<>	不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内 where sal between 1500 and 3000
LIKE	搜索某种模式 where ename like ‘M%’
IN	指定针对某个列的多个可能值 where sal in (5000,3000,1500)
is null	空值判断 where comm is null

运算符后若是文本字段需使用引号，若是数值字段不需引号。

2.3 AND & OR

AND & OR 运算符用于基于一个以上的条件对记录进行过滤。

• 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
• 如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
• 也可以把 AND 和 OR 结合起来（使用圆括号来组成复杂的表达式）

语句举例：

SELECT * FROM Websites
WHERE alexa > 15
AND (country='CN' OR country='USA');

2.4 ORDER BY

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。DESC 或者 ASC 只对它紧跟着的第一个列名有效，其他不受影响，仍然是默认的升序。

语法如下：

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

2.5 INSERT INTO

INSERT INTO 语句用于向表中插入新记录。该语句可以有两种编写形式：

• 第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

INSERT INTO table_name
VALUES (value1,value2,value3,...);

• 第二种形式需要指定列名及被插入的值：

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

2.6 UPDATE

UPDATE 语句用于更新表中已存在的记录。

语法如下：

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

请注意 SQL UPDATE 语句中的 WHERE 子句！
WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句，所有的记录都将被更新！

2.7 DELETE

DELETE 语句用于删除表中的行。

语法如下：

DELETE FROM table_name
WHERE some_column=some_value;

请注意 SQL DELETE 语句中的 WHERE 子句！
WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除！

您可以在不删除表的情况下，删除表中所有的行。这意味着表结构、属性、索引将保持不变：

DELETE FROM table_name;

或

DELETE * FROM table_name;

3 SQL高级命令

3.1 SELECT TOP, LIMIT, ROWNUM

SELECT TOP 子句用于规定要返回的记录的数目。该子句对于拥有数千条记录的大型表来说，是非常有用的。

注意: 并非所有的数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。

（1）SQL Server / MS Access 语法

SELECT TOP number|percent column_name(s)
FROM table_name;

--前5行
select top 5 * from table

--后5行
select top 5 * from table order by id desc  --desc 表示降序排列 asc 表示升序

（2）MySQL 语法

SELECT column_name(s)
FROM table_name
LIMIT number;

（3）Oracle 语法

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

3.2 LIKE

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

语法如下：

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

下面的 SQL 语句选取 name 包含模式 “oo” 的所有客户：

SELECT * FROM Websites
WHERE name LIKE '%oo%';

提示："%" 符号用于在模式的前后定义通配符（默认字母）。以下将详细介绍通配符。

3.3 SQL通配符

通配符可用于替代字符串中的任何其他字符。在 SQL 中，通配符与 SQL LIKE 操作符一起使用，用于搜索表中的数据。

MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

在 SQL 中，可使用以下通配符：

通配符	描述
%	替代0个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符列中的任何单一字符

# 选取 url 以字母 "https" 开始的所有网站：
SELECT * FROM Websites
WHERE url LIKE 'https%';

# 选取 url 包含模式 "oo" 的所有网站：
SELECT * FROM Websites
WHERE url LIKE '%oo%';

# 选取 name 以一个任意字符开始，然后是 "oogle" 的所有客户：
SELECT * FROM Websites
WHERE name LIKE '_oogle';

# 选取 name 以 "G" 开始，然后是一个任意字符，然后是 "o"，然后是一个任意字符，然后是 "le" 的所有网站：
SELECT * FROM Websites
WHERE name LIKE 'G_o_le';

# 选取 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]';

3.4 IN

IN 操作符允许在 WHERE 子句中规定多个值。
语法如下：

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

# 案例
SELECT * FROM Websites
WHERE name IN ('Google','菜鸟教程');

# 转换成=：
select * from Websites 
where name='Google' or name='菜鸟教程';

IN 与 = 的异同

• 相同点：均在WHERE中使用作为筛选条件之一、均是等于的含义
• 不同点：IN可以规定多个值，等于规定一个值

3.5 BETWEEN

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

语法如下：

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

# 选取 name 不介于 'A' 和 'H' 之间字母开始的所有网站：
SELECT * FROM Websites
WHERE name NOT BETWEEN 'A' AND 'H';

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

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

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

3.6 SQL别名

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

（1）列的 SQL 别名语法

SELECT column_name AS alias_name
FROM table_name;

# 指定了两个别名，一个是 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;

（2）表的 SQL 别名语法

SELECT column_name(s)
FROM table_name AS alias_name;

# 选取 "菜鸟教程" 的所访问记录。我们使用 "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="菜鸟教程";

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

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

3.7 JOIN

SQL join 用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段

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

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

3.8 UNION

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

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

语法如下：

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

# 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

3.9 SELECT INTO / INSERT INTO SELECT

（1）SELECT INTO

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

语法如下：

# 我们可以复制所有的列插入到新表中：
SELECT *
INTO newtable [IN externaldb]
FROM table1;

# 或者只复制希望的列插入到新表中：
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

# SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可：
SELECT *
INTO newtable
FROM table1
WHERE 1=0;

（2） INSERT INTO SELECT

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

语法如下：

# 我们可以从一个表中复制所有的列插入到另一个已存在的表中：
INSERT INTO table2
SELECT * FROM table1;

# 或者我们可以只复制希望的列插入到另一个已存在的表中：
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

（3）两者异同

• 相同点：select into from 和 insert into select 都是用来复制表。
• 不同点： select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在。

3.10 CREATE

# 创建数据库
CREATE DATABASE dbname;

# 创建数据库中的表。表由行和列组成，每个表都必须有个表名。
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

# 创建索引。以便更加快速高效地查询数据。用户无法看到索引，它们只能被用来加速搜索/查询。
CREATE INDEX index_name
ON table_name (column_name)

# 创建视图。视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

3.11 Constraints 约束

SQL 约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

语法如下：

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

# 实例
CREATE TABLE Persons
(
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (Id_P)  //PRIMARY KEY约束
)
CREATE TABLE Persons
(
    Id_P int NOT NULL PRIMARY KEY,   //PRIMARY KEY约束
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
)

在 SQL 中，我们有如下约束：

• NOT NULL： 指示某列不能存储 NULL 值。
• UNIQUE ： 保证某列的每行必须有唯一的值。
• PRIMARY KEY ：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY ： 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK ：保证列中的值符合指定的条件。
• DEFAULT ： 规定没有给列赋值时的默认值。

4 SQL函数

4.1 GROUP BY

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

语法如下：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

# 统计 access_log 各个 site_id 的访问量：
SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

# 统计有记录的网站的记录数量：
SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

4.2 HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。

语法如下：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

# 查找总访问量大于 200 的网站，并且 alexa 排名小于 200：
SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200 
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

4.3 EXISTS

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

语法如下：

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

# 查找总访问量(count 字段)大于 200 的网站是否存在：
SELECT Websites.name, Websites.url 
FROM Websites 
WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

# EXISTS 可以与 NOT 一同使用，查找出不符合查询语句的记录：
SELECT Websites.name, Websites.url 
FROM Websites 
WHERE NOT EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

4.4 AVG() / SUM() / COUNT()

# AVG() 函数返回数值列的平均值：
SELECT AVG(column_name) FROM table_name

# SUM() 函数返回数值列的总数：
SELECT SUM(column_name) FROM table_name;

# COUNT() 函数返回匹配指定条件的行数：
SELECT COUNT(*) FROM table_name;   
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(DISTINCT column_name) FROM table_name;

4.5 FIRST() / LAST()

（1）FIRST()

# FIRST() 函数返回指定的列中第一个记录的值（只有 MS Access 支持 FIRST() 函数。）：
SELECT FIRST(column_name) FROM table_name;

# SQL Server 语法
SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;

# MySQL 语法
SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;

#Oracle 语法
SELECT column_name FROM table_name
ORDER BY column_name ASC
WHERE ROWNUM <=1;

（2）LAST()

# LAST() 函数返回指定的列中最后一个记录的值（只有 MS Access 支持 LAST() 函数）：
SELECT LAST(column_name) FROM table_name;

# SQL Server 语法：
SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;

# MySQL 语法：
SELECT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1;

# Oracle 语法：
SELECT column_name FROM table_name
ORDER BY column_name DESC
WHERE ROWNUM <=1;

4.6 MAX() / MIN()

# MAX() 函数返回指定列的最大值：
SELECT MAX(column_name) FROM table_name;

# MIN() 函数返回指定列的最小值：
SELECT MIN(column_name) FROM table_name;

4.7 UCASE() / LCASE()

# UCASE() 函数把字段的值转换为大写：
SELECT UCASE(column_name) FROM table_name;

# 用于 SQL Server 的语法：
SELECT UPPER(column_name) FROM table_name;


# LCASE() 函数把字段的值转换为小写：
SELECT LCASE(column_name) FROM table_name;

# 用于 SQL Server 的语法：
SELECT LOWER(column_name) FROM table_name;

4.8 MID() / LEN()

# MID() 函数用于从文本字段中提取字符：
SELECT MID(column_name,start[,length]) FROM table_name;

# 从 "Websites" 表的 "name" 列中提取前 4 个字符：
SELECT MID(name,1,4) AS ShortTitle
FROM Websites;


# LEN() 函数返回文本字段中值的长度：
SELECT LEN(column_name) FROM table_name;

# MySQL 中函数为 LENGTH():
SELECT LENGTH(column_name) FROM table_name;

# 从 "Websites" 表中选取 "name" 和 "url" 列中值的长度：
SELECT name, LENGTH(url) as LengthOfURL
FROM Websites;

4.9 ROUND() / NOW() / FORMAT

# ROUND() 函数用于把数值字段舍入为指定的小数位数：
SELECT ROUND(column_name,decimals) FROM table_name;

mysql> select ROUND(-1.23);
        -> -1
mysql> select ROUND(-1.58);
        -> -2
mysql> select ROUND(1.58);
        -> 2

mysql> select ROUND(1.298, 1);
        -> 1.3
mysql> select ROUND(1.298, 0);
        -> 1


# NOW() 函数返回当前系统的日期和时间：
SELECT NOW() FROM table_name;

# 从 "Websites" 表中选取 name，url，及当天日期：
SELECT name, url, Now() AS date
FROM Websites;


# FORMAT() 函数用于对字段的显示进行格式化
SELECT FORMAT(column_name,format) FROM table_name;

# 从 "Websites" 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期：
SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;