MySQL语句进阶记录（三）

1、在使用update更新多个字段时不能使用and

将所有to_date为9999-01-01的全部更新为NULL

应该不止我一个人在使用update更新多个字段的时候使用and的吧！！！

# 在使用update更新多个字段时，应该使用,进行分隔
update titles_test set to_date = NULL, from_date = '2001-01-01'
where to_date = '9999-01-01'

分析一波：使用update的时候用and进行连接，MySQL会将and后的信息考虑为一个条件

# 假设此处想要修改指定数据的to_date和from_date
update titles_test set to_date = '2001-02-03' and from_date = '1995-12-04'
where to_date = '9999-01-01' and from_date = '1995-12-03';
# 该条语句将会这也解析
update titles_test set to_date = ('2001-02-03' and from_date = '1995-12-04')
where to_date = '9999-01-01' and from_date = '1995-12-03';
# 解析一下括号内的条件结果
# '2001-02-03'会转成数字类型，这时会报错Truncated incorrect DOUBLE value:'2001-02-03'（无法正常转换）
# from_date='1995-12-04'由于是false值，会转成0
# 所以当你在使用update多个字段时使用and，你可能会很幸运的发现你的语句正常执行了，但是结果却是错的，甚至你可能没有机会发现执行结果错误，直到上线！！！

2、replace的使用

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

# replace函数使用：replace(字段名,需要修改的值,修改后的值)
# 使用replace进行查询（将title字段里的 “Senior” 替换为 “Staff” 并显示数据表）
select *,replace(title,"Senior","Staff");
# 使用replace更新数据（将emp_no字段里的 “10001” 替换为 “10005” 并更新）
update titles_test
set emp_no = replace(emp_no,10001,10005)
where id = 5;
# 使用replace插入数据（若存在主键为5的数据，则会进行替换。若不存在则直接插入）
insert replace into titles_test 
values('5', '10005', 'Senior Engineer', '1986-06-26', '9999-01-01')

3、给表添加外键约束（实际开发中很少会用到外键了！！！）

在audit表上创建外键约束，其emp_no对应employees_test表的主键id

/*
    ALTER TABLE <表名>
    ADD CONSTRAINT FOREIGN KEY (<列名>)
    REFERENCES <关联表>（关联列）
*/
alter table audit
add constraint foreign key(emp_no)
references employees_test(id);

4、统计字段中某个字符出现的次数

查找字符串中逗号出现的次数

# 使用空字符替换指定字符获取其长度，再用之前字段的长度与之相减，进而统计出该字符出现的次数
select 
    id,
    length(string)-length(replace(string,",","")) as cnt
from strings;

5、获取字段后两个字符或者前两个字符

获取employees中的first_name，要求根据first_name后两个字符进行升序

# substr(字段名,起始位置,截取字段长度)
# right(字段名,从右边开始向左截取多少长度字符)
# left(字段名,从左边开始向右截取多少长度字符)
# 获取字段first_name前两个字段
substr(first_name,0,2) 等价于 left(first_name,2)
# 获取字段first_name后两个字段
substr(first_name,-2,2) 等价于 right(first_name,2)

6、将查询出来的数据用指定字符进行拼接

按照dept_no进行汇总

# group_concat(字段名,拼接字符)	若拼接字符没有指定默认使用','进行进行拼接
select dept_no,group_concat(emp_no)
from dept_emp
group by dept_no;