全连接查询和系统提供的内置函数

一、全连接查询

当我们使用外连接查询时：
select *from 表1 left join 表2 on(表1.字段1=表2.字段2)###左外连接查询映射出join左侧这张表的所有数据

mysql> select * from kiki left join t_kiki on(kiki.id=t_kiki.tid);
+----+-----------+------+------+------+--------------+
| id | name      | sex  | tid  | tid  | bumen        |
+----+-----------+------+------+------+--------------+
|  1 | 之润泽    | 男   |    1 |    1 | 矿大         |
|  2 | 麻药      | 男   |    3 |    2 | 宁大         |
|  3 | 李飞      | 女   |    6 |    3 | 二民院       |
|  4 | 张金玉    | 女   |    4 |    4 | 宁职         |
|  5 | 王丽      | 女   |    3 | NULL | NULL         |
|  6 | 赵云      | 男   |    2 |    6 | 华东理工     |
|  7 | 招娣      | 男   | NULL | NULL | NULL         |
+----+-----------+------+------+------+--------------+
7 rows in set (0.00 sec)

select *from 表1 right join 表2 on(表1.字段1=表2.字段2)###右外连接查询映射出join右侧这张表的所有数据

mysql> select * from kiki right join t_kiki on(kiki.id=t_kiki.tid);
+------+-----------+------+------+------+--------------+
| id   | name      | sex  | tid  | tid  | bumen        |
+------+-----------+------+------+------+--------------+
|    1 | 之润泽    | 男   |    1 |    1 | 矿大         |
|    2 | 麻药      | 男   |    3 |    2 | 宁大         |
|    3 | 李飞      | 女   |    6 |    3 | 二民院       |
|    4 | 张金玉    | 女   |    4 |    4 | 宁职         |
| NULL | NULL      | NULL | NULL | NULL | 司法         |
|    6 | 赵云      | 男   |    2 |    6 | 华东理工     |
+------+-----------+------+------+------+--------------+
6 rows in set (0.00 sec)

：不管是left join还是right join都只能映射出一边的数据，如果想要将左边右边所有数据全部映射则要用到全连接。
全连接：union去重复，union all展示重复

mysql> select * from kiki left join t_kiki on(kiki.id=t_kiki.tid) union select * from kiki right join t_kiki on(kiki.id=t_kiki.tid);
+------+-----------+------+------+------+--------------+
| id   | name      | sex  | tid  | tid  | bumen        |
+------+-----------+------+------+------+--------------+
|    1 | 之润泽    | 男   |    1 |    1 | 矿大         |
|    2 | 麻药      | 男   |    3 |    2 | 宁大         |
|    3 | 李飞      | 女   |    6 |    3 | 二民院       |
|    4 | 张金玉    | 女   |    4 |    4 | 宁职         |
|    5 | 王丽      | 女   |    3 | NULL | NULL         |
|    6 | 赵云      | 男   |    2 |    6 | 华东理工     |
|    7 | 招娣      | 男   | NULL | NULL | NULL         |
| NULL | NULL      | NULL | NULL | NULL | 司法         |
+------+-----------+------+------+------+--------------+
8 rows in set (0.11 sec)

二、系统提供的内置函数

1、聚合函数：

count（）
max（）
min（）
sum（）
avg（）

mysql> select * from sanguoyanyi;
+----+-----------+-----+------+-----------------+
| id | name      | sex | age  | address         |
+----+-----------+-----+------+-----------------+
|  1 | 刘备      | 男  |   50 | 陕西渭南        |
|  2 | 周瑜      | 男  |   33 | 江东            |
|  3 | 关羽      | 男  |   38 | 河北涿郡        |
|  4 | 张飞      | 男  |   35 | 宁夏吴忠        |
|  5 | 诸葛亮    | 男  |   23 | 南阳            |
|  6 | 赵云      | 男  |   29 | 河北石家庄      |
|  7 | 貂蝉      | 女  |   18 | 陕西西安        |
|  8 | 小乔      | 女  |   18 | 江苏杭州        |
|  9 | 马超      | 男  |   35 | 西凉            |
| 10 | 魏延      | 男  |   34 | 湖南长沙        |
| 11 | 黄忠      | 男  |   62 | 湖南长沙        |
| 12 | 张辽      | 男  |   26 | 内蒙            |
+----+-----------+-----+------+-----------------+
12 rows in set (0.00 sec)

mysql> select count(sanguoyanyi.age) from sanguoyanyi;###统计数量
+------------------------+
| count(sanguoyanyi.age) |
+------------------------+
|                     12 |
+------------------------+
1 row in set (0.00 sec)

mysql> select max(sanguoyanyi.age) from sanguoyanyi;###求最大年龄
+----------------------+
| max(sanguoyanyi.age) |
+----------------------+
|                   62 |
+----------------------+
1 row in set (0.00 sec)

mysql> select min(sanguoyanyi.age) from sanguoyanyi;###求最小年龄
+----------------------+
| min(sanguoyanyi.age) |
+----------------------+
|                   18 |
+----------------------+
1 row in set (0.00 sec)

mysql> select sum(sanguoyanyi.age) from sanguoyanyi;###求所有人加起来的年龄
+----------------------+
| sum(sanguoyanyi.age) |
+----------------------+
|                  401 |
+----------------------+
1 row in set (0.00 sec)

mysql> select avg(sanguoyanyi.age) from sanguoyanyi;###求平均年龄
+----------------------+
| avg(sanguoyanyi.age) |
+----------------------+
|              33.4167 |
+----------------------+
1 row in set (0.01 sec)

2、数值型函数：

ABS 求绝对值
SQRT 求平方根
POW 和 POWER 两个函数的功能相同，返回参数的幂次方
MOD 求余数
CEIL 和 CEILING 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR 向下取整，返回值转化为一个BIGINT
RAND 生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND 对所传参数进行四舍五入
SIGN 返回参数的符号

3、字符串函数：

LENGTH 计算字符串长度函数，返回字符串的字节长度
CHAR_LENGTH 计算字符串长度函数，返回字符串的字节长度，注意两者的区别
CONCAT
合并字符串函数，返回结果为连接参数产生的字符串，参数可以使
一个或多个
INSERT(str,pos,len,newstr) 替换字符串函数
LOWER 将字符串中的字母转换为小写
UPPER 将字符串中的字母转换为大写
LEFT (str,len) 从左侧字截取符串，返回字符串左边的若干个字符
RIGHT 从右侧字截取符串，返回字符串右边的若干个字符
TRIM 删除字符串左右两侧的空格
REPLACE(s，s1，s2) 字符串替换函数，返回替换后的新字符串
SUBSTRING(s，n，len) 截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
STRCMP(expr1,expr2)
比较两个表达式的顺序。若expr1 小于 expr2 ，则返回 -1，0相等，1
则相反
LOCATE(substr,str [,pos]) 返回第一次出现子串的位置
INSTR(str,substr) 返回第一次出现子串的位置

4、日期和时间函数：

CURDATE()
CURRENT_DATE()
CURRENT_DATE
两个函数作用相同，返回当前系统的日期值
CURTIME
CURRENT_TIME()
CURRENT_TIME
两个函数作用相同，返回当前系统的时间值
NOW 返回当前系统的日期和时间值
SYSDATE 返回当前系统的日期和时间值
DATE 获取指定日期时间的日期部分
TIME 获取指定日期时间的时间部分
MONTH 获取指定日期中的月份
MONTHNAME 获取指定曰期对应的月份的英文名称
DAYNAME 获取指定曰期对应的星期几的英文名称
YEAR 获取年份，返回值范围是 1970〜2069
DAYOFWEEK
获取指定日期对应的一周的索引位置值，也就是星期数，注意周日是开
始日，为1
WEEK
获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1
〜53
DAYOFYEAR 获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH 和 DAY
两个函数作用相同，获取指定日期是一个月中是第几天，返回值范围是
1~31
DATEDIFF(expr1,expr2)：
返回两个日期之间的相差天数，如
SELECT DATEDIFF(‘2007-12-31 23:59:59’,‘2007-12-30’);
SEC_TO_TIME： 将秒数转换为时间，与TIME_TO_SEC 互为反函数
TIME_TO_SEC：
将时间参数转换为秒数，是指将传入的时间转换成距离当天00:00:00的秒
数，00:00:00为基数，等于 0 秒

5、流程控制函数

IF(expr,v1,v2) = 判断，流程控制，当expr = true时返回 v1，当expr = false、null 时返回v2
IFNULL(v1,v2) = 判断是否为空，如果 v1 不为 NULL，则 IFNULL 函数返回 v1，否则返回 v2
CASE = 搜索语句