常用sql汇总

ABS(X):返回X的绝对值

具体用法：select ABS(-32);

---

IFNULL(expr1,expr2)：如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值

具体用法：

现有学生表（tbl_student）和分数表（score）,查询学生表的所有字段和学生相对于的英语成绩（english_score）sql如下：

select stu.*,IFNULL(score.english_score,0) from tbl_student stu,tbl_score score where 1=1 and stu.stu_id=score.stu_id

以上sql中，如果score表中的english_score有值，则IFNULL(score.english_score,0)就显示english_score，否则，显示0

---

coalesce()解释：返回参数中的第一个非空表达式（从左向右依次类推）

具体用法：

select coalesce(null,2,3); // Return 2

select coalesce(null,null,3); // Return 3

select coalesce(1,2,3); // Return 1

字符串截取函数：left(), right(), substring(), substring_index()。还有 mid(), substr()。其中，mid(), substr() 等价于 substring() 函数，substring() 的功能非常强大和灵活。

具体用法：

substring(str, pos); substring(str, pos, len)

3.1 从字符串的第 4 个字符位置开始取，直到结束。

mysql> select substring(‘example.com’, 4);
±-----------------------------+
| substring(‘example.com’, 4) |
±-----------------------------+
| mple.com |
±-----------------------------+
3.2 从字符串的第 4 个字符位置开始取，只取 2 个字符。

mysql> select substring(‘example.com’, 4, 2);
±--------------------------------+
| substring(‘example.com’, 4, 2) |
±--------------------------------+
| mp |
±--------------------------------+

---

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

具体用法：

SELECT DISTINCT Company FROM Orders
结果：
IBM
W3School
Apple
现在，在结果集中，“W3School” 仅被列出了一次。

---

INNER JOIN 关键字

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释：INNER JOIN 与 JOIN 是相同的。

---

FULL JOIN 关键字：只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。
FULL JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释：在某些数据库中， FULL JOIN 称为 FULL OUTER JOIN。

原始的表 (用在例子中的)：
“Persons” 表：

1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing
“Orders” 表：

1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65
全连接（FULL JOIN）实例
现在，我们希望列出所有的人，以及他们的定单，以及所有的定单，以及定购它们的人。

您可以使用下面的 SELECT 语句：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集：

Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
Bush George
34764
FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 “Persons” 中的行在表 “Orders” 中没有匹配，或者如果 “Orders” 中的行在表 “Persons” 中没有匹配，这些行同样会列出。

---

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

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

SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

下面的例子中使用的原始表：
Employees_China:
01 Zhang, Hua
02 Wang, Wei
03 Carter, Thomas
04 Yang, Ming
Employees_USA:
01 Adams, John
02 Bush, George
03 Carter, Thomas
04 Gates, Bill

使用 UNION 命令
实例
列出所有在中国和美国的不同的雇员名：

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
结果
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill
注释：这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。

---

FIRST() /LAST() 函数函数返回指定的字段中第一个/最后一个记录的值。

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

SQL HAVING 实例
我们拥有下面这个 “Orders” 表：

1 2008/12/29 1000 Bush
2 2008/11/23 1600 Carter
3 2008/10/05 700 Bush
4 2008/09/28 300 Bush
5 2008/08/06 2000 Adams
6 2008/07/21 100 Carter
现在，我们希望查找订单总金额少于 2000 的客户。

我们使用如下 SQL 语句：

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

MID 函数用于从文本字段中提取字符。

SQL MID() 语法
SELECT MID(column_name,start[,length]) FROM table_name
column_name 必需。要提取字符的字段。
start 必需。规定开始位置（起始值是 1）。
length 可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。
SQL MID() 实例
我们拥有下面这个 “Persons” 表：

1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing
现在，我们希望从 “City” 列中提取前 3 个字符。

我们使用如下 SQL 语句：

SELECT MID(City,1,3) as SmallCity FROM Persons
结果集类似这样：

Lon
New
Bei

ROUND 函数用于把数值字段舍入为指定的小数位数。

SQL ROUND() 语法
SELECT ROUND(column_name,decimals) FROM table_name
column_name 必需。要舍入的字段。
decimals 必需。规定要返回的小数位数。
SQL ROUND() 实例
我们拥有下面这个 “Products” 表：

1 gold 1000 g 32.35
2 silver 1000 g 11.56
3 copper 1000 g 6.85
现在，我们希望把名称和价格舍入为最接近的整数。

我们使用如下 SQL 语句：

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
结果集类似这样：

gold 32
silver 12
copper 7

FORMAT 函数用于对字段的显示进行格式化

SQL FORMAT() 语法
SELECT FORMAT(column_name,format) FROM table_name
column_name 必需。要格式化的字段。
format 必需。规定格式。
SQL FORMAT() 实例
我们拥有下面这个 “Products” 表：

1 gold 1000 g 32.35
2 silver 1000 g 11.56
3 copper 1000 g 6.85
现在，我们希望显示每天日期所对应的名称和价格（日期的显示格式是 “YYYY-MM-DD”）。

我们使用如下 SQL 语句：

SELECT ProductName, UnitPrice, FORMAT(Now(),‘YYYY-MM-DD’) as PerDate
FROM Products
结果集类似这样：

gold 32.35 12/29/2008
silver 11.56 12/29/2008
copper 6.85 12/29/2008