MySQL的常用函数以及多表联查

---

---

一、MySQL常用函数

1、聚合函数

名称	作用
avg()	返回的是指定组中的平均值，空值被忽略
count()	返回的是指定组中的项目个数
max()	返回指定数据中的最大值
min()	返回指定数据中的最小值
sum()	返回指定数据的和，只能用于数字列，空值忽略
group by()	对数据进行分组，对执行完 group by 之后的组进行聚合函数的运算，计算每一组的值，最后用having去掉不符合条件的组，having子句中的每一个元素必须出现在select列表中（只针对于mysql）

具体用法

#平均值的应用
select avg(age) from person where id<=15;
#统计的应用
select count(*) from person where sex='女';
#最大值、最小值的应用
select max(age),min(age) from person where id<15；
#求和应用
select sum(age) from person where sex='女'and id>=15
#分组的应用，group by与having 、order by、 limit组合应用，具体如下：
#asc代表升序，desc代表降序
select hometown,count(hometown) from person group by hometown 
having count(*)>=1  ORDER BY count(hometown) ASC limit 10;
#去重查询,关键字DISTINCT
select DISTINCT name  from person 
SELECT * FROM TABLE WHERE ID IN (SELECT MAX(ID) FROM TABLE GROUP BY [去除重复的字段名列表,....])

2、数值型函数

名称	作用
abs(num)	求绝对值
sqrt(num)	开平方根
pow(x, y)/power	幂次方
mod(x, y)	求余
ceil(num)/ceiling()	向上取整
floor(num)	向下取整
round(num)	四舍五入
rand()	随机数
sign(num)	返回自然数的符号（正：1， 负：-1，0为0）

用法举例如下

#绝对值与保留2位小数应用
SELECT ROUND (ABS(num),2) from number
#求绝对值的平方根并保留2位小数
SELECT ROUND(sqrt(ABS(num)),2) from number

3、字符型函数

名称	作用
length()	获取字符串存储长度，注意中文编码问题
char_length()	字符长度
concat(s1,s2…)	拼接字符串
inster(str,pos,len,newstr)	替换字符串
lower()	转换为小写
upper()	转大写
left(s, len)	从左侧截取len长度的字符串
right(s, len)	从右侧截取len长度的字符串
trim()	清除字符串两侧空格
replace(s,s1, s2)	替换字符串
substring(s, pos, len)	截取字符串
reverse(str)	翻转字符串
STRCMP(expr1,expr2)	比较两个表达式的顺序。若expr1 小于 expr2 ，则返回 -1，0相等，1则相反
INSTR(str,s)	返回第一次出现子串的位置
locate(s, str [,pos])	返回第一次出现子串的位置，pos表示匹配位置

用法与数值型函数相似，参考数值型函数使用方法即可

4、日期函数

名称	作用
curdate 和current_date	两个函数作用相同，返回当前系统的日期值
curtime 和current_time	两个函数作用相同，返回当前系统的时间值
now	返回当前系统的日期和时间值
sysdate	返回当前系统的日期和时间值
date	获取指定日期时间的日期部分
time	获取指定日期时间的时间部分
month	获取指定日期中的月份
montname	获取指定日期中的月份英文名称
dayname	获取指定曰期对应的星期几的英文名称
year	获取年份，返回值范围是 1970〜2069
dayofweek	获取指定日期对应的一周的索引位置值
Week	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
dayofyear	获取指定曰期是一年中的第几天，返回值范围是1~366
dayofmonth 和 day	两个函数作用相同，获取指定日期是一个月中是第几天，返回值范围是1~31
time_to_sec	将时间参数转换为秒数
sec_to_time	将秒数转换为时间，与TIME_TO_SEC 互为反函数\
datediff	返回两个日期之间的相差天数

用法举例如下：

#获取当前日期
SELECT CURRENT_DATE()
#获取当前时间
SELECT CURRENT_TIME()

滤空函数

名称	作用
nvl(a,c)	当a为null的时候，返回c，否则，返回a本身
nvl2(a,b,c)	当a为null的时候，返回c，否则返回b 其中，nvl2中的2是增强的意思，类似于varchar2
nullif(a,b)	当a=b的时候，返回null，否则返回a
coalesce(a,b,c,d)	从左往右查找，当找到第一个不为null的值的时候，就显示这第一个有值的值

转换函数

名称	作用
TO_CHAR	将日期或数字转换为字符
TO_DATE	将字符转换为日期
TO_NUMBER	将字符转换为数字

二、多表联查

1.多表联查分类

主键（Primary Key） ：主键约束，其值唯一，一个值只对应一行
外键（Foreign Key） ：两个表之间建立连接时需要外键，可以是一列也可以是多列，并且一个表可以有一个或者多个外键

2.内连接

准备数据：

#创建学生表
CREATE TABLE `person`  (
  `id` int UNSIGNED  AUTO_INCREMENT,
  `name` varchar(50),
  `age` int ,
  `sex` varchar(10) ,
  `hometown` varchar(20) ,
  `classname` int,
  PRIMARY KEY (`id`) USING BTREE
)
#添加数据
INSERT INTO `person` VALUES (4, '杨紫', 23, '女', '北京', 1);
INSERT INTO `person` VALUES (5, '肖战', 23, '男', '武汉', 1);
INSERT INTO `person` VALUES (6, '董洁', 24, '女', '乌鲁木齐', 1);
INSERT INTO `person` VALUES (7, '杨幂', 21, '女', '北京', 2);
INSERT INTO `person` VALUES (9, '王凯', 23, '男', '北京', 2);
INSERT INTO `person` VALUES (10, '易烊千玺', 24, '男', '北京', 2);
INSERT INTO `person` VALUES (11, '刘诗诗', 20, '女', '上海', 3);
INSERT INTO `person` VALUES (12, '吴奇隆', 23, '男', '上海', 3);
INSERT INTO `person` VALUES (13, '刘亦菲', 25, '女', '上海', 1);
INSERT INTO `person` VALUES (14, '黄晓明', 26, '男', '上海', 3);
INSERT INTO `person` VALUES (15, '王俊凯', 27, '男', '西安', 2);
INSERT INTO `person` VALUES (16, '倪妮', 24, '女', '北京', 2);
INSERT INTO `person` VALUES (17, '刘昊然', 19, '男', '长沙', 2);
INSERT INTO `person` VALUES (18, '谭松韵', 25, '女', '长沙', 1);
INSERT INTO `person` VALUES (19, '林心如', 24, '女', '上海', 3);
INSERT INTO `person` VALUES (20, '张一山', 29, '男', '北京', 1);
INSERT INTO `person` VALUES (21, '张一山', 29, '男', '北京', 1);
INSERT INTO `person` VALUES (22, '王一博', 25, '男', '成都', 5);
INSERT INTO `person` VALUES (23, '王源', 22, '男', '西安', 5);

# 创建班级表
CREATE TABLE `class`  (
  `id` int  AUTO_INCREMENT,
  `classname` varchar(255),
  PRIMARY KEY (`id`) USING BTREE
) 
#添加数据
INSERT INTO `class` VALUES (1, '高三（1）班'),(2, '高三（2）班'),(3, '高三（3）班'),(4, '高三（4）班');

显式内连接

语法：
SELECT 表1.列1,表2.列2 FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WHERE 条件；

# 查询高三（1）班的男生
select person.name,class.classname 
from person INNER JOIN class on person.classname=class.id 
where class.id=1 and person.sex='男';
#查询结果
肖战 	高三（1）班
张一山	高三（1）班
张一山	高三（1）班

隐式内连接

语法：
**SELECT 表1.列1,表2.列2 FROM 表1, 表2 where 表1.外键=表2.主键 **；

# 查询高三（1）班的男生
select person.name,class.classname from person,class 
where person.classname=class.id and class.id=1 and person.sex='男';
#查询结果
肖战 	高三（1）班
张一山	高三（1）班
张一山	高三（1）班

显示内连接与隐式内连接的优点：

隐式内连接优点：语法简单，通俗易懂。
显式内连接优点：可以减少字段的扫描，有更快的执行速度，这种速度优势在3张或更多表连接时比较明显

3.外连接

与取得双方表中数据的内连接相比，外连接只能取其中一个表存在的数据，外连接又分为左连接和右连接两种情况；

左连接：左连接以左表为基准进行查询,只查询左表中存在的数据，前提是两个表的键值需要一致

语法：
SELECT 列名 FROM 表1 LEFT JOIN 表2 ON 表1.外键=表2.主键 WHERE 条件

# 查询男生的姓名和班级
select person.name,class.classname from person 
LEFT JOIN class on person.classname=class.id where sex='男'；
#查询结果
肖战		高三（1）班
王凯		高三（2）班
易烊千玺	高三（2）班
吴奇隆	高三（3）班
黄晓明	高三（3）班
王俊凯	高三（2）班
刘昊然	高三（2）班
张一山	高三（1）班
张一山	高三（1）班
王一博	null
王源		null

右连接：右连接以右表为基准进行查询

语法：
SELECT 表1.列1,表2.列2… FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WHERE 条件

# 查询姓名和班级
select person.name,class.classname from person 
RIGHT JOIN class on person.classname=class.id;
#查询结果
张一山	高三（1）班
张一山	高三（1）班
谭松韵	高三（1）班
刘亦菲	高三（1）班
董洁		高三（1）班
肖战		高三（1）班
杨紫		高三（1）班
刘昊然	高三（2）班
倪妮		高三（2）班
王俊凯	高三（2）班
易烊千玺	高三（2）班
王凯		高三（2）班
杨幂		高三（2）班
林心如	高三（3）班
黄晓明	高三（3）班
吴奇隆	高三（3）班
刘诗诗	高三（3）班
null	高三（4）班

4、子查询

子查询指的就是在一个查询之中嵌套了其他的若干查询，在使用select语句查询数据时,有时候会遇到这样的情况，在where查询条件中的限制条件不是一个确定的值，而是一个来自于另一个查询的结果,子查询一般出现在FROM和WHERE子句中。

#查询高三（1）班的男生
select * from 
(select person.name,class.classname from person 
LEFT JOIN class on person.classname=class.id where sex='男')person 
where classname='高三（1）班';
#查询结果
肖战		高三（1）班
张一山	高三（1）班
张一山	高三（1）班

---

总结

本文仅仅简单介绍了MySQL的函数与多表联查使用。