mysql常用sql语句记录1

1.解决返回值为null的问题

//假设name,addr返回的值中可能存在null值
select id,name,addr from tabe1 where id=1;

//修改的后的sql为
select id,
case when name is null 
	then '' else name 
	end as name,
case when addr is null 
	then '' else addr 
	end as addr
from tabe_name where id=1;

2.insert into select

//假设存在两种表table1,table2
insert into table1 as t1 (id,name,addr) 
values (1,'lisi',(select addr from table2 t2 where t2.id in
(select id from table 3 where id>1)) )

3.对某个需要查询的字段进行计算

//对查询结果中的id字段全部+1,
select id+1 as id,name,addr from tabe1;

//还可以进行字符串拼接
select id,name,addr,name+addr as n  from tabe1;

4.insert into select计算

insert into table1 (age,num) 
select age+1 as age,num+2 as num from table2 where id=3;