SQL批量模板

1、批量插入

insert into user VALUES
(null,"admin","123","超级管理员",1),
(null,"admin1","123","管理员",0)

2、批量修改
优势在于只执行一条SQL语句

update user set 
sensortypeId = (case when id=1 then "123"
					 when Id=2 then “1234”
					 when Id=3 then "12345"
					 when Id=4 then "123456"

					 else 0
					 end
							)

else会将其他id不等于1/2/3/4的值改为0
如果加上筛选条件 where id in(1,2,3,4)
等同于
UPDATE user SET password=“123” where id=1
UPDATE user SET password=“1234” where id=2
UPDATE user SET password=“12345” where id=3
UPDATE user SET password=“123456” where id=4

3、批量删除

delete from user where id>1

4、批量查询
limit (2-1)*5,5 (第几页-1)*每页条数,每页条数

select * from user limit 5,5