函数查询加强
2019-08-07 本文已影响0人
温柔倾怀
合计/聚合函数--count
count 函数是进行统计满足条件的记录数有多少条, 是按照分组的形式统计
1.统计班里共有多少学生
select count(*) from student;
select count(*) from student where math > 90;
- count() count(列名) 区别
count(列名) 会忽略空值
进一步理解:count() 是统计记录,而count(列名)而是依据指定的字段去统计,空值不统计在内。
聚合函数--sum
Sum函数返回满足where条件的记录的总和
select sum(math) from student; //返回的结果是班级所有同学数学成绩总和
select sum(math)+sum(chinese)+sum(english) from student; //这种写法是一定正确的
select sum(math+chinese+english) from student; //这种写法要求所求记录中不能包含空值,数值会出错
select sum(chinese)/count(*) from student;
- 当使用sum,如果有数据为null时
- 当我们对单列进行统计时,是正确的,不会出错的
- 如果是多列,同一记录多个字段 : null值加任何数据均为空
- 解决方案
- 分开统计
- 使用ifnull函数
ifnull函数 ifnull(expr1,expr2),
如果expr1为null,则取expr2的值
如果expr1不为空,就取第一个表达式的值
select sum(ifnull(math,0.0)+ifnull(chinese,0.0)+ifnull(english,0.0)) from student where id >= 100;
聚合函数--avg
AVG函数返回满足where条件的列的平均值
select avg(math) from student;
聚合函数--Max/Min
返回满足where条件的列的最大/最小值
也需要保证每个字段中不能包含空值
聚合函数 group by
group by 就是对数据(列)进行分组统计, 如果我们需要对分组的结果进行过滤则可以使用关键字 having
select max(salary),min(salary),deptno from emp group by deptno;
- 可以对多列进行分组
dual
亚元表,可以作为一个测试表,可以当做一个空表,没有表用的时候可以拿来用,补位的意思
mysql> select now() from dual;
+---------------------+
| now() |
+---------------------+
| 2019-08-07 01:29:06 |
+---------------------+
1 row in set (0.01 sec)
Mysql的日期函数
- current_date() 当前日期
- current_time() 当前时间
- current_timestamp() 当前时间戳
- date(datetime) 返回datetime的日期部分
- now() 当前时间
字符串函数
- charset(str) 返回字串字符集
mysql> select charset('nihao');
+------------------+
| charset('nihao') |
+------------------+
| gbk |
+------------------+
1 row in set (0.01 sec)
- concat(string[,string2...]) 连接字串
mysql> select concat('name:',name,' year:',year) as info from user;
+---------------------+
| info |
+---------------------+
| name:huahua year:4 |
+---------------------+
1 row in set (0.00 sec)
- inser(string,substring) 返回substring在string中出现的位置,没有返回0
- ucase(string) 转换成大写
- lcase(string) 转换成小写
mysql> select ucase('abc'),lcase('DEF');
+--------------+--------------+
| ucase('abc') | lcase('DEF') |
+--------------+--------------+
| ABC | def |
+--------------+--------------+
1 row in set (0.00 sec)
- left(string,length) 从string左边起取length个字符
mysql> select left('nihao',3);
+-----------------+
| left('nihao',3) |
+-----------------+
| nih |
+-----------------+
1 row in set (0.01 sec)
- length(string) string长度(按照字节)
mysql> select length('nihao');
+-----------------+
| length('nihao') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
- replace(str,search_str,replece_str) 在str中用replace_str替换 search_str
如图,全部查找,全部替换
mysql> select replace('nihaoyyyyyy','y','!');
+--------------------------------+
| replace('nihaoyyyyyy','y','!') |
+--------------------------------+
| nihao!!!!!! |
+--------------------------------+
1 row in set (0.00 sec)
- strcmp(string1,string2) 逐字符比较两个字串大小,不区分大小写
string1>string2 返回1
string1<string2 返回-1
string1=string2 返回0
mysql> select strcmp('a','a');
+-----------------+
| strcmp('a','a') |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)
mysql> select strcmp('a','A');
+-----------------+
| strcmp('a','A') |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)
------------------------------------------------
mysql> select strcmp('a','b');
+-----------------+
| strcmp('a','b') |
+-----------------+
| -1 |
+-----------------+
1 row in set (0.00 sec)
- substring(str,position[,length]) 从str的position开始取length个字符
下图可见,如果省略length,返回其后所有
mysql> select substring('nihao world',6,6);
+------------------------------+
| substring('nihao world',6,6) |
+------------------------------+
| world |
+------------------------------+
1 row in set (0.00 sec)
mysql> select substring('nihao world',6);
+----------------------------+
| substring('nihao world',6) |
+----------------------------+
| world |
+----------------------------+
1 row in set (0.00 sec)
- ltrim(string) rtrim(string) trim(string) 去除前端或后端或两端空格
mysql> select length(' nihao! ');
+----------------------------+
| length(' nihao! ') |
+----------------------------+
| 16 |
+----------------------------+
mysql> select length(trim(' nihao! '));
+----------------------------------+
| length(trim(' nihao! ')) |
+----------------------------------+
| 6 |
+----------------------------------+
1 row in set (0.00 sec)
数值函数
- abs(num) 绝对值
- bin(decimal_num) 十进制转二进制
- ceiling(num) 向上取整
- conv(num,from_base,to_base) 进制转换
mysql> select conv(10,10,2);
+---------------+
| conv(10,10,2) |
+---------------+
| 1010 |
+---------------+
1 row in set (0.00 sec)
mysql> select conv(10,10,16);
+----------------+
| conv(10,10,16) |
+----------------+
| A |
+----------------+
1 row in set (0.00 sec)
- floor(num) 向下取整
- format(num,decimal_places) 保留小数位数
- hex(decimal_num) 转十六进制
- least(num1,num2...) 求最小值
- mod(num,denominator) 求余
- rand([seed])
流程控制函数
- if(expr1,expr2,expr3)
- ifnull(expr1,expr2)
- select case
select case
when expr1 then expr2
when expr3 then expr4
else expr5
end;
其他函数
- user() 查询用户
- database() 数据库名称
- md5(str) 为字符串算出一个md5 128比特检查和,通常用于对应用程序使用到的表的某个字段加密
- password(str) 从原文密码str计算并返回密码字符串,通常用于对mysql数据库的用户密码加密
mysql> select md5('nihao');
+----------------------------------+
| md5('nihao') |
+----------------------------------+
| 194ce5d0b89c47ff6b30bfb491f9dc26 |
+----------------------------------+
1 row in set (0.01 sec)
mysql> select password('nihao');
+-------------------------------------------+
| password('nihao') |
+-------------------------------------------+
| *364870DF09C7E82F6A8ED19ED529F3375E57B3CF |
+-------------------------------------------+
1 row in set (0.00 sec)