常用的SQL语句

常用的SQL语句

查询数据库

Websites表：

数据格式

SELECT column_name,column_name FROM table_name;

案例

SELECT * FROM  Websites;

SQL SELECT DISTINCT

在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。

DISTINCT 关键词用于返回唯一不同的值。

数据格式：

SELECT DISTINCT column_name,column_name FROM table_name;

实例

SQL WHERE 子句

WHERE 子句用于提取那些满足指定条件的记录。
数据格式：

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

实例

where子句中的运算符

案例

Select * from emp where empno=7900;
Select * from emp where ename='SMITH';
Select * from emp where sal > 2000 and sal < 3000;
Select * from emp where sal > 2000 or comm > 500;
select * from emp where not sal > 1500;
Select * from emp where comm is null;
// 查询 emp 表中 SAL 列中大于 1500 的小于 3000 的值。
Select * from emp where sal between 1500 and 3000;
// 查询 EMP 表 SAL 列中等于 5000，3000，1500 的值。
Select * from emp where sal in (5000,3000,1500);
// 查询 EMP 表中 Ename 列中有 M 的值，M 为要查询内容中的模糊信息。
Select * from emp where ename like 'M%';
% 表示多个字值，_ 下划线表示一个字符；
 M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
 %M% : 表示查询包含M的所有内容。
 %M_ : 表示查询以M在倒数第二位的所有内容。

SQL AND & OR 运算符

AND & OR 运算符用于基于一个以上的条件对记录进行过滤。
如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

websites数据库：

AND 实例

从 "Websites" 表中选取国家为 "CN" 且alexa排名大于 "50" 的所有网站：

OR 实例

从 "Websites" 表中选取国家为 "USA" 或者 "CN" 的所有客户：

结合 AND 和 OR

从 "Websites" 表中选取 alexa 排名大于 "15" 且国家为 "CN" 或 "USA" 的所有网站：

SQL ORDER BY 关键字

ORDER BY 关键字用于对结果集进行排序。
ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。
ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

语法结构：

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

ORDER BY 实例：

从 "Websites" 表中选取所有网站，并按照 "alexa" 列排序：

ORDER BY DESC 实例

从 "Websites" 表中选取所有网站，并按照 "alexa" 列降序排序：

ORDER BY 多列 实例

从 "Websites" 表中选取所有网站，并按照 "country" 和 "alexa" 列排序：

ORDER BY 多列的时候，先按照第一个column name排序，在按照第二个column name排序；如上述教程最后一个例子：

1. 先将country值这一列排序，同为CN的排前面，同属USA的排后面；
2. 然后在同属CN的这些多行数据中，再根据alexa值的大小排列。
3. ORDER BY 排列时，不写明ASC DESC的时候，默认是ASC。

SQL INSERT INTO 语句

INSERT INTO 语句用于向表中插入新记录。
INSERT INTO 语句可以有两种编写形式。
第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

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

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

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

INSERT INTO 实例

假设我们要向 "Websites" 表中插入一个新行。

在指定的列插入数据

插入一个新行，但是只在 "name"、"url" 和 "country" 列插入数据（id 字段会自动更新）：

SQL UPDATE 语句

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

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

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

SQL UPDATE 实例

把 "菜鸟教程" 的 alexa 排名更新为 5000，country 改为 USA。

SQL DELETE 语句

DELETE 语句用于删除表中的记录。
语法：

DELETE FROM table_name
WHERE some_column=some_value;

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

SQL DELETE 实例

从 "Websites" 表中删除网站名为 "百度" 且国家为 CN 的网站 。

删除所有数据

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

DELETE FROM table_name;
或
DELETE * FROM table_name;

注释：在删除记录时要格外小心！因为您不能重来！

SQL SELECT TOP, LIMIT, ROWNUM 子句

SQL SELECT TOP 子句

SELECT TOP 子句用于规定要返回的记录的数目。

SELECT TOP 子句对于拥有数千条记录的大型表来说，是非常有用的。

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

MySQL 语法

SELECT column_name(s)
FROM table_name
LIMIT number;

MySQL SELECT LIMIT 实例

从 "Websites" 表中选取头两条记录：

SQL SELECT TOP PERCENT 实例

在 Microsoft SQL Server 中还可以使用百分比作为参数。

下面的 SQL 语句从 websites 表中选取前面百分之 50 的记录：

SQL LIKE 操作符

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

语法：

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

SQL LIKE 操作符实例

选取 name 以字母 "G" 开始的所有客户：

选取 name 以字母 "k" 结尾的所有客户：

选取 name 包含模式 "oo" 的所有客户：

通过使用 NOT 关键字，您可以选取不匹配模式的记录。

选取 name 不包含模式 "oo" 的所有客户：

SQL 通配符

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

使用 SQL % 通配符

选取 url 以字母 "https" 开始的所有网站：

选取 url 包含模式 "oo" 的所有网站：

使用 SQL _ 通配符

选取 name 以一个任意字符开始，然后是 "oogle" 的所有客户：

选取 name 以 "G" 开始，然后是一个任意字符，然后是 "o"，然后是一个任意字符，然后是 "le" 的所有网站：

使用 SQL [charlist] 通配符

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

下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站：

选取 name 以 A 到 H 字母开头的网站：

选取 name 不以 A 到 H 字母开头的网站：

SQL IN 操作符

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

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

IN 操作符实例

选取 name 为 "Google" 或 "菜鸟教程" 的所有网站：

SQL BETWEEN 操作符

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

语法：

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

BETWEEN 操作符实例

选取 alexa 介于 1 和 20 之间的所有网站：

NOT BETWEEN 操作符实例

如需显示不在上面实例范围内的网站，请使用 NOT BETWEEN：

带有 IN 的 BETWEEN 操作符实例

选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站：

带有文本值的 BETWEEN 操作符实例

选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站：

带有文本值的 NOT BETWEEN 操作符实例

选取 name 不介于 'A' 和 'H' 之间字母开始的所有网站：

带有日期值的 BETWEEN 操作符实例

选取 date 介于 '2016-05-10' 和 '2016-05-14' 之间的所有访问记录：

请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！ 在某些数据库中，BETWEEN
选取介于两个值之间但不包括两个测试值的字段。 在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。
在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。

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

SQL 别名

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

列的 SQL 别名语法：

SELECT column_name AS alias_name
FROM table_name;

表的 SQL 别名语法：

SELECT column_name(s)
FROM table_name AS alias_name;

列的别名实例

SQL 语句指定了两个别名，一个是 name 列的别名，一个是 country 列的别名。提示：如果列名称包含空格，要求使用双引号或方括号：

SQL 语句中，我们把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名：

表的别名实例

下面是选自 "Websites" 表的数据：

下面是 "access_log" 网站访问记录表的数据：

选取 "菜鸟教程" 的所访问记录。我们使用 "Websites" 和 "access_log" 表，并分别为它们指定表别名 "w" 和 "a"（通过使用别名让 SQL 更简短）：

不带别名的相同的 SQL 语句：

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

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

SQL 连接(JOIN)

SQL join 用于把来自两个或多个表的行结合起来。

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

SQL JOIN

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

最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。

下面是选自 "Websites" 表的数据：

下面是 "access_log" 网站访问记录表的数据：

请注意，"Websites" 表中的 "id" 列指向 "access_log" 表中的字段 "site_id"。上面这两个表是通过 "site_id" 列联系起来的。

然后，如果我们运行下面的 SQL 语句（包含 INNER JOIN）：

在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：

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

SQL INNER JOIN 关键字

INNER JOIN 关键字在表中存在至少一个匹配时返回行。

语法：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

注释：INNER JOIN 与 JOIN 是相同的。

SQL INNER JOIN 实例

返回所有网站的访问记录：

SQL LEFT JOIN 关键字

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

语法：

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

SQL LEFT JOIN 实例

返回所有网站及他们的访问量（如果有的话）。

以下实例中我们把 Websites 作为左表，access_log 作为右表：

注释：LEFT JOIN 关键字从左表（Websites）返回所有的行，即使右表（access_log）中没有匹配。

SQL RIGHT JOIN 关键字

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

语法：

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

SQL RIGHT JOIN 实例

下面的 SQL 语句将返回网站的访问记录。

以下实例中我们把 access_log 作为左表，Websites 作为右表：

注释：RIGHT JOIN 关键字从右表（Websites）返回所有的行，即使左表（access_log）中没有匹配。

SQL FULL OUTER JOIN 关键字

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

语法：

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL FULL OUTER JOIN 实例

选取所有网站访问记录。

MySQL中不支持 FULL OUTER JOIN，你可以在 SQL Server 测试以下实例。

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;

注释：FULL OUTER JOIN 关键字返回左表（Websites）和右表（access_log）中所有的行。如果 "Websites"表中的行在 "access_log" 中没有匹配或者 "access_log" 表中的行在 "Websites"表中没有匹配，也会列出这些行。

SQL UNION 操作符

SQL UNION 操作符合并两个或多个 SELECT 语句的结果。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法：

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

SQL UNION ALL 语法：

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

SQL UNION 实例

从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）：

注释：UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

SQL UNION ALL 实例

使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）：

带有 WHERE 的 SQL UNION ALL

使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据（也有重复的值）：

SQL 函数

SQL 拥有很多可用于计数和计算的内建函数。

SQL Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值.
有用的 Aggregate 函数：

1. AVG() - 返回平均值
2. COUNT() - 返回行数
3. FIRST() - 返回第一个记录的值
4. LAST() - 返回最后一个记录的值
5. MAX() - 返回最大值
6. MIN() - 返回最小值
7. SUM() - 返回总和

SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。
有用的 Scalar 函数：

1. UCASE() - 将某个字段转换为大写
2. LCASE() - 将某个字段转换为小写
3. MID() - 从某个文本字段提取字符，MySql 中使用
4. SubString(字段，1，end) - 从某个文本字段提取字符
5. LEN() - 返回某个文本字段的长度
6. ROUND() - 对某个数值字段进行指定小数位数的四舍五入
7. NOW() - 返回当前的系统日期和时间
8. FORMAT() - 格式化某个字段的显示方式

SQL AVG() 函数

AVG() 函数返回数值列的平均值。

SELECT AVG(column_name) FROM table_name

下面是选自 "access_log" 表的数据：

SQL AVG() 实例

从 "access_log" 表的 "count" 列获取平均值：

选择访问量高于平均访问量的 "site_id" 和 "count"：

SQL COUNT() 函数

COUNT() 函数返回匹配指定条件的行数。
COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECT COUNT(column_name) FROM table_name;

SQL COUNT() 语法
COUNT() 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name;

SQL COUNT(DISTINCT column_name) 语法
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：

SELECT COUNT(DISTINCT column_name) FROM table_name;

SQL COUNT(column_name) 实例

计算 "access_log" 表中 "site_id"=3 的总访问量：

SELECT COUNT(count) AS nums FROM access_log
WHERE site_id=3;

SQL COUNT(*) 实例

计算 "access_log" 表中总记录数：

SQL COUNT(DISTINCT column_name) 实例

计算 "access_log" 表中不同 site_id 的记录数：

SQL MAX() 函数

MAX() 函数返回指定列的最大值。

SELECT MAX(column_name) FROM table_name;

SQL MAX() 实例

从 "Websites" 表的 "alexa" 列获取最大值：

SQL MIN() Function

MIN() 函数返回指定列的最小值。

SELECT MIN(column_name) FROM table_name;

从 "Websites" 表的 "alexa" 列获取最小值：

SQL SUM() 函数

SUM() 函数返回数值列的总数。

SELECT SUM(column_name) FROM table_name;

SQL SUM() 实例

查找 "access_log" 表的 "count" 字段的总数：

SQL GROUP BY 语句

GROUP BY 语句可结合一些聚合函数来使用

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

GROUP BY 简单应用

统计 access_log 各个 site_id 的访问量：

SQL GROUP BY 多表连接

统计有记录的网站的记录数量：