文章目录
- 1 常用函数
- 1.1 聚合函数
- 1.2 数值型函数
- 1.3 字符串函数
- 1.4 日期和时间函数
- 1.5 流程控制函数
- 1.6 加密函数
- 1.7 提醒
- 2 常用语法
- 2.1 分组查询
- 2.2 排序查询
- 2.3分页查询
- 2.4模糊查询
- 3 连接查询
- 3.1 内连接
- 3.2 左外连接查询
- 3.3 右外连接查询
- 3.4 全连接查询
1 常用函数
我们在进行项目开发中，SQL是我们必须要用的东西，与数据库进行交互和持久化，Mysql是我们经常使用的数据库，其中有很多函数，在我们写sql时提供很大的帮助，本文主要讲了几类常用函数的用法。
1.1 聚合函数
聚合函数是我们平时用的比较多的一类函数，这里列举如下：
- count(col) 统计查询结果的总记录数
- min(col) 查询指定列的最小值
- max(col) 查询指定列的最大值
- sum(col) 求和，返回指定列的总和
- avg(col) 求平均值，返回指定列数据的平均值
1.2 数值型函数
数值型函数主要是对数值型数据进行处理，得到我们想要的结果，常用的几个列举如下，具体使用方法大家可以试试看。
- ABS(x) 返回x的绝对值
- BIN(x) 返回x的二进制
- CEILING(x) 返回大于x的最小整数值
- EXP(x) 返回值e（自然对数的底）的x次方
- FLOOR(x) 返回小于x的最大整数值
- GREATEST(x1,x2,…,xn) 返回集合中最大的值
- LEAST(x1,x2,…,xn) 返回集合中最小的值
- LN(x) 返回x的自然对数
- LOG(x,y) 返回x的以y为底的对数
- MOD(x,y) 返回x/y的模（余数）
- PI() 返回pi的值（圆周率）
- RAND() 返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
- ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
- TRUNCATE(x,y) 返回数字x截短为y位小数的结果
1.3 字符串函数
字符串函数可以对字符串类型数据进行处理，在程序应用中用处还是比较大的，同样这里列举几个常用的如下：
- length(s) 计算字符串长度函数，返回字符串的字节长度
- concat(s1,s2…,sn) 合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个
- insert(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
- lower(str) 将字符串中的字母转换为小写
- upper(str) 将字符串中的字母转换为大写
- left(str,x) 返回字符串str中最左边的x个字符
- right(str,x) 返回字符串str中最右边的x个字符
- trim(str) 删除字符串左右两侧的空格
- replace 字符串替换函数，返回替换后的新字符串
- substring 截取字符串，返回从指定位置开始的指定长度的字符换
- reverse(str) 返回颠倒字符串str的结果
1.4 日期和时间函数
- CURDATE 和 CURRENT_DATE 两个函数作用相同，返回当前系统的日期值
- CURTIME 和 CURRENT_TIME 两个函数作用相同，返回当前系统的时间值
- NOW 和 SYSDATE 两个函数作用相同，返回当前系统的日期和时间值
- UNIX_TIMESTAMP 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
- FROMUNIXTIME 将 UNIX 时间戳转换为时间格式，与UNIXTIMESTAMP互为反函数
- MONTH 获取指定日期中的月份
- MONTHNAME 获取指定日期中的月份英文名称
- DAYNAME 获取指定曰期对应的星期几的英文名称
- DAYOFWEEK 获取指定日期对应的一周的索引位置值
- WEEK 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
- DAYOFYEAR 获取指定曰期是一年中的第几天，返回值范围是1~366
- DAYOFMONTH 获取指定日期是一个月中是第几天，返回值范围是1~31
- YEAR 获取年份，返回值范围是 1970〜2069
- TIMETOSEC 将时间参数转换为秒数
- SECTOTIME 将秒数转换为时间，与TIMETOSEC 互为反函数
- DATE_ADD 和 ADDDATE 两个函数功能相同，都是向日期添加指定的时间间隔
- DATE_SUB 和 SUBDATE 两个函数功能相同，都是向日期减去指定的时间间隔
- ADDTIME 时间加法运算，在原始时间上添加指定的时间
- SUBTIME 时间减法运算，在原始时间上减去指定的时间
- DATEDIFF 获取两个日期之间间隔，返回参数 1 减去参数 2 的值
- DATE_FORMAT 格式化指定的日期，根据参数返回指定格式的值
- WEEKDAY 获取指定日期在一周内的对应的工作日索引
1.5 流程控制函数
流程控制类函数可以进行条件操作，用来实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台，列举如下：
- if(test,t,f) 如果test是真，返回t；否则返回f
- ifnull(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2
- nullif(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
- case when[test1] THEN [result1]…ELSE [default] END 如果testN是真，则返回resultN，否则返回default
- case [test] WHEN[val1] THEN [result]…ELSE [default]END 如果test和valN相等，则返回resultN，否则返回default
1.6 加密函数
加密函数主要用于对字符串进行加密，常用的几个列举如下：
- ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
- ENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储
- MD5() 计算字符串str的MD5校验和
- PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
- SHA() 计算字符串str的安全散列算法(SHA)校验和
1.7 提醒
(1)多行函数不能用在where子句中
(2)多行函数和是否分组有关，分组与否会直接影响多行函数的执行结果。
(3)多行函数在统计时会对null值进行过滤，直接将null值丢弃，不参与统计。
多行函数需要注意的问题:
- 多行函数和是否分组有关，如果查询结果中的数据没有经过分组，默认整个查询结果是一个组，多行函数就会默认统计当前这一个组的数据。产生的结果只有一个。
- 如果查询结果中的数据经过分组（分的组不止一个），多行函数会根据分的组进行统计，有多少个组，就会统计出多少个结果。
2 常用语法
2.1 分组查询
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT，SUM，AVG，MAX，MIN等函数。
语法：SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;
2.2 排序查询
使用 ORDER BY 子句将结果集中记录根据指定的列排序后再返回
语法：SELECT 列名 FROM 表名 ORDER BY 列名 [ASC|DESC] ASC(默认)升序，即从低到高；DESC 降序，即从高到低。
2.3分页查询
在mysql中，通过limit进行分页查询，查询公式为：limit (页码-1)*每页显示记录数, 每页显示记录数
2.4模糊查询
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
可以和通配符（%、_）配合使用，其中"%"表示0或多个任意的字符,"_"表示一个任意的字符。
语法：SELECT 列 | * FROM 表名 WHERE 列名 LIKE 值
3 连接查询
3.1 内连接
INNER JOIN(内连接，或等值连接):获取两个表中字段匹配关系的记录。
3.2 左外连接查询
可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可。
3.3 右外连接查询
可以将右边表中的所有记录都查询出来，左边表只显示和右边相对应的数据，如果右边表中某些记录在左边没有对应的数据，可以显示为null。
3.4 全连接查询
扩展：如果想将两张表中的所有数据都查询出来(左外+右外并去除重复记录)，可以使用全外连接查询，但是mysql又不支持全外连接查询。
可以使用union将左外连接查询的结果和右外连接查询的结果合并在一起，并去除重复的记录。
需要注意的是：union可以将两条SQL语句执行的结果合并，但是有前提:
(1)两条SQL语句查询的结果列数必须一致
(2)两条SQL语句查询的结果列名、顺序也必须一致
并且union默认就会将两个查询中重复的记录去除(如果不希望去除重复记录，可以使用union all)
