函数查询加强

合计/聚合函数--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时
  1. 当我们对单列进行统计时，是正确的，不会出错的
  2. 如果是多列，同一记录多个字段 : null值加任何数据均为空
• 解决方案
  1. 分开统计
  2. 使用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)