使用Mysql你不知道的十大误区
1> IFNULL语法
IFNULL只针对查询出来的结果集进行处理, 并不会改变本身的数据
eg:
-
SELECT departmentid FROM employees WHERE IFNULL(departmentid,1); //结果无改变
-
SELECT IFNULL(departmentid,0) FROM employees; //departmentid为null则显示的值为0
2> COUNT(expr)函数
COUNT(expr)函数只会对expr不为null的数据进行统计
eg:
- SELECT department_id FROM employees; // 显示数据107条
- SELECT COUNT(department_id) num FROM employees; //显示数据106条
3> ORDER BY语法
ORDER BY不仅能处理表的字段, 还能处理别名.
注意: ORDER BY 仅能处于SELECT语句的末尾
eg:
-
SELECT departmentid id FROM employees ORDER BY departmentid;
-
SELECT department_id id FROM employees ORDER BY id;
4> 常用函数的处理数据类型
可以对数值型数据使用AVG 和 SUM 函数;
可以对任意数据类型的数据使用 MIN 和 MAX 函数;
COUNT(*) 返回表中记录总数适用于任意数据类型。
5>非法使用聚合函数
不能在 WHERE 子句中使用聚合函数。
可以在 HAVING 子句中使用聚合函数。
eg:
- SELECT departmentid, AVG(salary) FROM employees WHERE AVG(salary) > 80 GROUP BY departmentid; //错误写法
- SELECT departmentid, AVG(salary) FROM employees GROUP BY departmentid HAVING AVG(salary) > 80; //正确写法
原因:
sql语句的执行顺序为
from子句 --> where 子句 --> group by 子句 --> having 子句 --> order by 子句 --> select 子句
因为聚合函数是对查询出来的结果集运算的,当在where子句使用聚合函数时,此时根据group by 分割结果集的子句还没有执行,此时只有from 后的结果集。
所以无法在where子句中使用聚合函数。
6> SUM()和AVG()执行时是否计算null值呢?
答案是: 不计算。分组函数基本上都是不计算null值的, 我们知道求平均数是先求出所有数字的和再除以数字的数量, 而MySQL的AVG()函数会自动过滤掉null。那么问题来了,假如我们想要那些为null的也计入有效结果怎么办呢?很简单!使用IFNULL函数!
eg:
- SELECT avg(salary), SUM(IFNULL(salary,0))/COUNT(*) FROM employees;
结果:
7> Mysql有两种存储引擎MYISAM和INNODB, 它们之间谁效率更高呢?为什么?
1、MyISAM:默认表类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法。不是事务安全的,而且不支持外键,如果执行大量的select,insert MyISAM比较适合。同样因为MYISAM自带计数器, 在执行COUNT(*)时效率较高.
2、InnoDB:支持事务安全的引擎,支持外键、行锁、事务是他的最大特点。如果有大量的update和insert,建议使用InnoDB,特别是针对多个并发和QPS较高的情况。
详细比较推荐这篇博客: https://blog.****.net/wjtlht928/article/details/46641865