整理一波Mysql中易忘基础知识点和也许你也会踩的一些坑

很多人不理解mysql中select，字面意思理解它就是挑选，其实当它是java中的System.out.println()函数就行。

比如下面这样写就是输出以下值：

# 查询常量值
SELECT 100;
SELECT 'john';
# 查询表达式
 SELECT 100%98;
 #查询函数
 SELECT VERSION();

演示以上一个例子，你就懂了。

来看看DESC关键字 表;

直接上图

DISTINCT关键字

DISTINCT关键字是为了保证查出来的字段没有重复的。

可见他会把不重复的所有job_id找出来。如果字段job_id不加关键字DISTINCT就会列举出所有的值，加了distinct之后，只会列举不重复的值，注意只有这一种写法，SELECT DISTINCT job_id，employee_id FROM employees 就会导致失效 SELECT employee_id, DISTINCT job_id FROM employees; 这样写也会报错。为什么呢，道理很简单，因为你前面job_id是重复的可能对应许多employee_id，你现在把它变成不重复的，那么这个job_id，应该选择哪个employee_id来进行对应尼。

TRIM()

注意trim函数这种用法

SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;

这种写法意思就是把from后面字符串以aa开头和结尾的aa都删除掉，直到删除掉到一直不匹配为止。

IFNULL(字段，value)这个的意思用java代码来表示就是

if(字段 == null){

字段 = value;

}

再来看看Mysql如何进行字符串拼接

在java中字符串拼接采用+，java中的+号功能：

①运算符，两个操作数都为数值型。

②连接符，只要有一个操作数为字符串。

下面看看Mysql中的+号

mysql中的+号：

仅仅只有一个功能那就是运算符

select 100+90;  #两个操作数都为数值型，则做加法运算

select '123'+90; # 输出为213

只要其中一方为字符型，试图将字符型数值转换成数值型如果转换成功，则继续做加法运算

select 'john'+90; # 输出为 90	
如果转换失败，则将字符型数值转换成0
select null+10; # 输出为 null
只要其中一方为null，则结果肯定为null

这个转换规格很重要切记，是面试题，而且会和索引结合，你只要知道遇到字符串无法转换就变成0，这个很重要。

那么Mysql是如何处理字符串拼接问题的呢？

CONCAT(字段1,字段2,...,字段) mysql 不像java一样可以直接拼接字符串，它提供了一个拼接字符串的函数。

相当于java中String str = ""+字段1+""+...+""+字段n

SELECT CONCAT('a','b','c'); # 输出为"abc"

SUBSTR(字符串, 起始位置从1开始, 截取的长度);

substr("abc",1,2) # 输出ab

TRUNCATE

SELECT TRUNCATE(1.69999,1);保留小数点后几位

mysql 中的条件查询的不等于使用<>

mysql中用<>与!=都是可以的，但sqlserver中不识别!=,所以建议用<>

=或<>(mysql中不等于的用法)不能用于判断null值,is null或is not null 可以判断null值，如果你非不想用is，也是可以使用安全等于 <=>可以进行判空操作。

IS NULL:仅仅可以判断NULL值，可读性较高，建议使用。

<=> :既可以判断NULL值，又可以判断普通的数值，但是可读性较低。

LENGTH() 可用于测量varchar类变量的长度。

mysql 模糊查询的like关键字一般和通配符搭配使用

通配符：

% 任意多个字符,包含0个字符

_ 任意单个字符

select 	* from employees where last_name like '%a%';# abc,ejacd,jda等等 在a的前后可以有任意0个或n个字符都满足条件
select 	* from employees where last_name like '%a'; # jnga 这种就必须是以a结尾的，前面任意0或多个字符都可以
select 	* from employees where last_name like 'a%'; # ajsj 这种是以a开头后面有任意多个字符都可以

看两个例子来感受一下。

# 查询员工名中第三个字符为e，第五个字符为a的员工名和工资
select last_name, salary FROM employees WHERE last_name LIKE '__e_a%'; # _ 代表匹配单个字符

# 查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '#39;;
# 解释一下 ESCAPE关键字，它相当于一个转义作用，%和_在模糊查询中有着明确的作用，但是，如果我们想查询的名字中需要包含%和_怎么办，那就加ESCAPE关键字，来转义。

mysql 如何写出想java switch-case一样的表达

SELECT last_name,job_id AS  job,
CASE job_id
WHEN 'AD_PRES' THEN 'A' 
WHEN 'ST_MAN' THEN 'B' 
WHEN 'IT_PROG' THEN 'C' 
WHEN 'SA_PRE' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM employees
WHERE job_id = 'AD_PRES';
# 其中last_name,job_id分别是你的表的字段名，当你job_id = 'AD_PRES'就输出'A',以此类推，END AS就是将你输出的结果取一个别名

再来说说我们经常使用的聚合函数的值支持什么类型，像大家经常使用估计也就是数值型变量，不知道你们知不知道一下操作。

先来看看我们的表结构信息。

# 在上述表中执行一下查询语句，我分别在下边给出查询结果图片，我的这个表一共有107条数据
SELECT SUM(last_name) ,AVG(last_name) FROM employees; # 不支持
SELECT SUM(hiredate) ,AVG(hiredate) FROM employees; # 支持
SELECT MAX(last_name),MIN(last_name) FROM employees; # 支持
SELECT MAX(hiredate),MIN(hiredate) FROM employees; # 支持
SELECT COUNT(commission_pct) FROM employees; # 支持，统计非空的
SELECT COUNT(last_name) FROM employees; # 支持，统计非空的

那么这些函数是否支持null呢？先说结果，这些函数默认都会忽视null。

# 执行一下sql可以来验证一下，我的commission_pct字段中存在null值
SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees; 
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;

再来看看使用聚合函数和distinct搭配的情况

# 看下面两条sql执行结果
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

如果不在salary前面加DISTINCT，那么sum求和的时候就会把所有salary重复的数也会相加。

count函数的详细介绍

# 一下三个sql执行结果一样但是只是效率不一样
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

效率：

MYISAM存储引擎下 ，COUNT(*)的效率高

INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些

下面说一下我遇到的两个坑，希望你能别踩了。

SELECT department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;

1、当使用分组函数的字段有多个字段的时候，请注意只有department_name,d.`manager_id`这两个值同时相等的时候才会被算成一个分组，其他情况不会合并分组。这里篇幅有限我就不多说了，具体可以看看这个例子。

2、两个datetime相减并不能得到准确的毫秒值

https://zhidao.baidu.com/question/164072931.html

SELECT DATEDIFF('1995-2-7','1995-2-6');  # 这个sql计算两个日期之间相差的天数 

坑在：datetime不能直接相减，实际是mysql的时间相减是做了一个隐式转换操作，将时间转换为整数，但并不是用unix_timestamp转换，而是直接把年月日时分秒拼起来，如2013-04-21 16:59:33 直接转换为20130421165933，由于时间不是十进制，所以最后得到的结果没有意义，这也是导致上面出现坑爹的结果。

详细参考这篇博客

https://blog.csdn.net/github_38838414/article/details/81974363

3、# lpad：函数语法：lpad(str1,length,str2)。其中str1是第一个字符串，length是结果字符串的长度，str2是一个填充字符串。如果str1的长度没有length那么长，则使用str2填充；如果str1的长度大于length，则截断。但汉字是无法截断的，而且汉字好像补齐也不对，他会把汉字当成一个字节计算，实际我们的汉字如果按照utf-8编码是三个字节的。

SELECT LPAD('殷素素',2,'*') AS out_put;

英文没有问题，一个符号就是一个字节，中文这个地方是一个坑！！