一、MySQL基本函数
1、MySQL 字符串函数
常用函数：
CONCAT(s1,s2...sn) 字符串 s1,s2 等多个字符串合并为一个字符串
| 函数 | 描述 | 实例 |
| CONCAT(s1,s2...sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 |
合并多个字符串 SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook")AS ConcatenatedString; |
|
CONCAT_WS(x, s1,s2...sn) |
同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 |
合并多个字符串，并添加分隔符： SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString; |
|
FORMAT(x,n) |
函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。 | 格式化数字 "#,###.##" 形式：
SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56 |
| LOWER(s) | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写：
SELECT LOWER('RUNOOB') -- runoob |
| SUBSTR(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 |
从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| SUBSTRING(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 |
从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| TRIM(s) | 去掉字符串 s 开始和结尾处的空格 |
去掉字符串 RUNOOB 的首尾空格： SELECT TRIM(' RUNOOB ') AS TrimmedString; |
| UCASE(s) | 将字符串转换为大写 |
将字符串 runoob 转换为大写： SELECT UCASE("runoob"); -- RUNOOB |
| UPPER(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写：
SELECT UPPER("runoob"); -- RUNOOB |
2、MySQL 数字函数
| 函数 | 描述 | 实例 |
| COUNT(expression) | 返回查询的记录总数，expression 参数是一个字段或者 * 号 |
返回 Products 表中 products 字段总共有多少条记录： SELECT COUNT(ProductID) AS NumberOfProducts FROM Products; select count(l.second_prod_code='cash_short' or null) 短期个数FROM Products |
| SUM(expression) | 返回指定字段的总和 |
计算 OrderDetails 表中字段 Quantity 的总和： SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails; select sum(l.second_prod_code='cash_short' ) 短期个数 FROM Products |
| AVG(expression) | 返回一个表达式的平均值，expression 是一个字段 | 返回 Products 表中Price 字段的平均值：
SELECT AVG(Price) AS AveragePrice FROM Products; |
| TRUNCATE(x,y) | 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） | SELECT TRUNCATE(1.23456,3) -- 1.234 |
3、MySQL 日期函数
| 函数 | 描述 | 实例 |
| CURDATE() | 返回当前日期 |
SELECT CURDATE(); |
| CURRENT_DATE() | 返回当前日期 |
SELECT CURRENT_DATE(); |
| DATEDIFF(d1,d2) | 计算日期 d1->d2 之间相隔的天数 |
SELECT DATEDIFF('2001-01-01','2001-02-02')； -> -32
|
| TIMEDIFF(time1, time2) | 计算时间差值 |
SELECT TIMEDIFF("13:10:11", "13:10:10"); |
| DATE_FORMAT(d,f) | 按表达式 f的要求显示日期 d |
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') %M 月名 %m 月，数值(00-12)。%D 带有英文前缀的月中的天 %d 月的天，数值(00-31) %e 月的天，数值(0-31) ） |
| ADDDATE(d,n) |
计算起始日期 d 加上 n 天的日期 （天数可正可负）（类似的函数有ADDTIME(t,n)时间 t 加上 n 秒的时间） |
SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); |
4、MySQL 高级函数
| 函数 | 描述 | 实例 |
|
CASE expression |
CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result， 而当有一个成立之后，后面的就不执行了。 |
select l.loan_number,second_prod_code,admin_rate aa, else 0 end from finance.loan_detail l group by l.loan_number, l.second_prod_code ; |
| COALESCE(expr1, expr2, ...., expr_n) |
返回参数中的第一个非空表达式（从左向右） |
SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com');
-> runoob.com |
| IF(expr,v1,v2) | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 | SELECT IF(1 > 0,'正确','错误')
->正确 |
| IFNULL(v1,v2) | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 |
SELECT IFNULL(null,'Hello Word') |
二、分组函数
• AVG()
• COUNT()
• MAX()
• MIN()
• SUM()
GROUP BY 子句语法
1、WHERE一定放在FROM后面
2、在SELECT 列表中所有未包含在组函数中的列都应该包含 在 GROUP BY 子句中。
3、包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
4、在GROUP BY子句中包含多个列
三、多表查询
1、等值连接
2、内连接
– 内连接 [inner] join on
3、 外连接
• 左外连接 left [outer] join on
• 右外连接 right [outer] join on
注：full join在mysql中不好使，oracle中支持。mysql可以通过left join + union + right join实现(利用union的去重机制)