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 性别

 students

语法; 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

 class c

LEFT JOIN students s ON s.class_id = c.class_id

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

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

SELECT

 s.s_name,

 c.class_name

 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

 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内联。

MYSQL中UNION（联合查询） 实际业务中，有时候需要把满足多种独立条件的结果集整合到一起，就可以使用 UNOIN 联合查询。使用 UNION 还是 UNION ALL 其中 UNION 联合查询已经去除了重复的结果集 UNION ALL 联合查询没有去除重复结果集，但是 UNION ALL查询性能要比 UNION好一些。最后还是需要根据具体业务选择使用 UNION 还是 UNION ALL。