MySQL 第五篇：系统函数和联合查询

我把MySQL的内容整理成9篇博客，学完这9篇博客虽不能说能成为大神，但是应付一般中小企业的开发已经足够了，有疑问或建议的欢迎留言讨论。

一、系统函数

一、概念
函数是SQL里的关键字，用于对字段里的数据进行操作。函数是一个命令，通常与字段名称或者是表达式联合使用，处理输入的数据并产生结果

二、控制函数

1、case

语法：case 值 when 比较的值 then 返回的值 when 比较的值 THEN…. END

SELECT s_name,
    CASE sex
WHEN 0 THEN
    '女性'
WHEN 1 THEN
    '男性'
WHEN 2 THEN
    '未知'
END 性别
FROM
    students

SELECT
    s_name,
    CASE
WHEN age > 23 THEN
    '大叔'
ELSE
    '小鲜肉' end
FROM
    students

2、if

语法; IF(表达式,true时候返回值,false的返回值)

select s_name name,IF(AGE>23,'大叔','小鲜肉') type from students

3、IFNULL

语法：IFNULL(字段值,为空时返回的值）

select IFNULL(height,'未知') from students

4、NULLIF

语法：NULLIF(第一个值，第二个值)
如果第一个值与第二个值相等 则返回NULL, 否则的话返回第一个值

select s_name,NULLIF(age,0) from students

三、字符串函数

1、substring

语法：SUBSTRING(值，起始下标，截取长度) 下标从1开始。

select substring(s_name,1,5) from students

2、LEFT / right

语法：left(字段名称,num) ：从 左边 截取num个字符；
right (字段名称,num) ：从 右边 截取num个字符；

select left(s_name,3) from students

3、length

语法：length(字段名) 获取字段的长度，一个中文的长度为3。

select length(s_name),s_name from students;

4、lower/upper

语法：lower(字段名) 将字段的值转为小写；
upper(字段名) 将字段的值转为大写；

select lower(s_name) from students
select upper(s_name) from students

5、replace

语法：replace(字段,”敏感词”,”代替词”) 将字段中的敏感词转为代替词。

select replace(s_name,'法宝','**') from students;

6、concat/concat_ws

语法：concat(str1,str2,str3…) 将多个字段按声明顺序合并在一起，里面的参数可以是字段名，也可以是确定的字符，当为固定字符时，每个结果的对应位置都是该字符。

select CONCAT(';',s_id,s_name,age) from students;

语法：concat_ws(str1,str2,str3…)将多个字段按声明顺序合并在一起，其中第一个参数为连接字符，查询结果中每两个字段中间都会用第一个字符的值隔开，里面的参数可以是字段名，也可以是确定的字符，当为固定字符时，每个结果的对应位置都是该字符。

select CONCAT_ws(';',s_id,s_name,age) from students;

四、数学函数

• abs(num) 取绝对值
• floor(num) 向下取整
• ceil(num) 向上取整
• round(num) 四舍五入
• rand() 在0-1之间取随机值；如果要限制范围就直接在后面乘以范围内的最大值，要取整就要结合取整函数

select ceil(rand()*100)

五、日期函数

DATEDIFF 比较两个日期之间的天数
DATE_ADD 添加时间
STR_TO_DATE 字符串转为时间
DATE_FORMAT 日期格式

select datediff(now(),STR_TO_DATE('2016-09-28','%Y-%m-%d')) from students
select DATE_ADD(now(),INTERVAL '2:4' DAY_HOUR) -- SECOND  MINUTE HOUR MONTH YEA
-- 日期的说明符。大家去查一下参考手册 %Y %m %d %H %i %s
SELECT DATE_FORMAT(Now(),'%Y,%m,%d : %H %i %s');

六、多表联合查询

1、左连接：以left 左边表为主查询数据。

左连接的结果集包括LEFT 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

SELECT
    s.s_name,
    c.class_name
FROM
    class c
LEFT JOIN students s ON s.class_id = c.class_id

2、右连接：以right 右边表为主查询数据.

右连接是左连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

SELECT
    s.s_name,
    c.class_name
FROM
    class c
right JOIN students s ON s.class_id = c.class_id

注意：外连接查询时，主表的数据为准，次表只是作为补充。主表的数据不存在的话，次表多余的数据不会展示出来。

3、内连接

把两个表相关联的数据展示出来，相当于求两个集合的交集。
内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行。

select s.s_name,
    c.class_name from students s inner JOIN class c on s.class_id = c.class_id

这也是内连接

SELECT
    s.s_name,
    c.class_name
FROM
    students s,
    class c
WHERE
    s.class_id = c.class_id

5、组合查询

Union [all]
Union操作符可以组合两个或多个的select语句的结果，不包含重复项。换句话说，如果某行的输出存在于一个查询结果里，那么其他查询结果同一行的记录就不会再输出了。在使用union操作符时，每个select语句里必须选择相同数量的字段、同样的数量的字段表达式、同样的次序（但长度不必一样）如果需要不去除重复项，则加上all关键字。

select s_name from students UNION
select class_id from class UNION
select now()

6、补充：

典型的连接运算，使用像 = 或 <> 之类的比较运算符。包括相等连接和自然连接。

• 连接语法：select 字段1 ,字段2… from 主表 [连接方式] JOIN 次表1 on 连接条件1

可以给每个字段和表加别名（用空格隔开即可），如果要多表查询就直接在后面继续加join on 部分即可。

• 完整语法：select 字段1 别名1 ,字段2 别名1… from 主表 表别名 [连接方式] JOIN 次表1 表别名1 on 连接条件1 JOIN 次表2 表别名2 on 连接条件2 …..

注意：
1、当然后面可以继续加Oder by、limit等语句；
2、连接条件一般是让两张表的主外键相等，当然也可以是<、>或其他的关系。
3、连接方式 left\right\inner可以省略不写，不写默认为inner内联。