MySQL sql注意点

本文列取了常用但是容易遗漏的一些知识点。另外关键词一般大写，为了便于阅读所以很多小写。也为了给自己查缺补漏。

distinct（去重）

也许你经常对单个字段去重，并且知道不建议用distinct，而是group by，因为大多数情况下distinct会引起全表扫描。但是还是需要了解：

#此时distinct 是对field1 + field2联合去重。
select distinct field1,field2 from table;

describe & desc（查询表结构）

也许你经常使用下面sql显示表结构，如果有天你要通过代码同步表结构，会用得上。

show create table table_name;

而忘记了下面这个

#显示表详细表结构
describe table_name;
#同上
desc table_name;

least & greatest（查找给定字符串之间的最值）

select LEAST(1,2,0,3);#    0
select GREATEST(1,2,0,3);#    3
select LEAST("c","a","b");#    a
select GREATEST("c","a","b");#    c

加减乘除取模运算

select 1 + '-1';#    0
#整数+符串，字符串隐式转换不成功，当作0
select 1 + 'a';#    1
select 1 + "a";#    1
select 1 + NULL;#    NULL
select 1 * -1.0;#    -1.0
select 1 / -1;#    -1.0000
select 1 / 1.0;#    1.0000
select 1 / 0;#    NULL
select 1 % 1.0;#    0.0
select 1 % 0;#    NULL
select -3 % 2;#    -1
select -3 % -2;#    -1
select 3 % -2;#    1

等于 =

#整数和符串比较，字符串隐式转换不成功，当作0
select 0 = 'a';#    1

#NULL参与判断，结果为NULL，这也解释了为什么关联查询时，关联字段存在NULL，结果显示NULL的原因
select 1 = NULL;#    NULL
select NULL = NULL;#    NULL

安全等于 <=>

#没有NULL参与相当于等号，有NULL参与如下
select 1<=> NULL;#    0
select NULL <=> NULL;#    1
select NULL <=> 'NULL', NULL <=> "NULL";#	0 0

#当然也可以这样比较
select 1 IS NULL;#    0
select 1 IS NOT NULL;#    1
select NULL IS NULL;#    1
select ISNULL(1);#    0
select ISNULL(NULL);#    1

not & !

select NOT 0;#    1
select NOT 100;#    0
select NOT 'A';#    1
select NOT (select 0 = 1);#    1
select 1 NOT between 0 and 2;#    0

select ! 0;#    1
select ! 100;#    0
select ! 'A';#    1
select ! (select 0 = 1);#    1

字符转义的两种写法

# \
select a from (select "_abc" as a) t where a like '\_%';#    _abc
# ESCAPE 函数
select a from (select "_abc" as a) t where a like 'a_%' escape 'a';#    _abc
select a from (select "_abc" as a) t where a like '$_%' escape 'a'$';#    _abc

xor（异或）

select 0 xor 0;#	0
select 0 xor 1;#	1
select 1 xor 0;#	1
select 1 xor 1;#	0
select 1 xor NULL;#    NULL

and & or 优先级

#如果按顺序来执行按理结果应是0，而实际结果是1，所以and优先级高于or
select 1 or 1 and 0;#    1
#等价于
select 1 or (1 and 0);#    1

offset (mysql 8.0)

#
select * from table_name limit 初始位置（从0开始）,记录数

#展示第1条到第10条数据
select * from table_name limit 0,10

#mysql 8.0新增写法
select * from table_name limit 10 offset 0;

join（关联）

当写多了sql之后已经形成了肌肉记忆，觉得理所当然，而忘记了这些形式。

using（连接条件）

select * from t1 join t2 on t1.f1 = t2.f1 and t1.f2 = t2.f2
#可替代如下写法，但是要求关联表字段名相同
select * from t1 join t2 USING(f1, f2);

 round（四舍五入）

select round(123.49);#   123
select round(123.50);#   124

#括号第二个参数表示从数点后截断几位（四舍五入后再截断）
select round(123.49, 1);#   123.5
select round(123.495, 2);#   123.50
select round(123.4, -1);#   120

truncate（数值截断）

#括号第二个参数表示从数点后截断几位
SELECT truncate(123.34, 1);#    123.3
SELECT truncate(123.3, -2);#    100

concat & concat_ws（字符串拼接）

select concat('My', 'S', 'QL');#    MySQL
select concat('My', NULL, 'QL');#    NULL

select concat_ws(',','one', NULL, 'three');#    one,three
select concat_ws(NULL,'one', 'two', 'three');#    NULL

replace（字符串替换）

select replace('www.mysql.com', 'w', 'Ww');#    WwWwWw.mysql.com
#如果它的任何参数是NULL,那么将返回NULL
select replace('www.mysql.com', NULL, 'Ww');#    NULL

instr & locate（返回子字符串第一次出现的位置）

常用于查询条件中，取代模糊查询

select instr('foobarbar', 'bar');#    4
#位置不同
select locate('bar', 'foobarbar');#    4

待续。。。