本文章主要写的是我自己在工作中使用到的十分有用的SQL语句,为防止遗忘故特将写此文章记录下来,后续文章的内容将不断的更新、完善。
一、更新(Update)
1.批量更新SQL
UPDATE categories SET display_order = CASE id
WHEN 'id1 'THEN 'value1 '
WHEN 'id2 'THEN 'value2'
WHEN 'id3 'THEN 'value3'
END
WHERE id IN (id1,id2,id3)
这句sql的意思是,更新display_order 字段,如果id=id1 则display_order 的值为value1,如果id=id2 则 display_order 的值为value2,如果id=value3 则 display_order 的值为value3。
即是将条件语句写在了一起。这里的where部分不影响代码的执行,但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数,这里只有3条数据进行更新,而where子句确保只有3行数据执行。
UPDATE categories
SET display_order = CASE id
WHEN 'id1 'THEN 'value1 '
WHEN 'id2 'THEN 'value2'
WHEN 'id3 'THEN 'value3'
END,
SET title = CASE id
WHEN 'id1 'THEN 'value1 '
WHEN 'id2 'THEN 'value2'
WHEN 'id3 'THEN 'value3'
END
WHERE id IN (id1,id2,id3)
2.修改某个字段(替换关键字内容)
2.1 语法
UPDATE 表名 SET 字段名= REPLACE( 替换前的字段值, '替换前关键字', '替换后关键字' ) WHERE 条件。
2.2举例
update goods_table SET goods_name = REPLACE( goods_name, '2017', '2018' ) where goods_name like '%2017%';
原来字段“2017新款”,执行之后“2018新款”
二、添加(Insert into)
1.批量添加
INSERT INTO MyTable(ID,NAME)
SELECT 4,'000'
UNION ALL
SELECT 5,'001'
UNION ALL
SELECT 6,'002'
三、删除(Delete)
1.批量删除
DELETE FROM TableName WHERE id IN (640,634,633);
2.Delete与truncate的区别
2.1语法
delete from 表名;
truncate table 表名;
2.2区别
a、不带where参数的delete语句可以删除mysql表中所有内容,使用truncate table也可以清空mysql表中所有内容。效率上truncate比delete快,但truncate删除后不记录mysql日志,不可以恢复数据。
b、delete的效果有点像将mysql表中所有记录一条一条删除到删完,而truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表。
四、查询(Select)
1.查询结果替换
1.1语法
select 字段名,insert(字段名,替换开始位置,需替换长度,'替换后的值'),replace(c,'需要替换的值','替换后的值') from 表名;
1.2举例
select c,insert(c,2,2,''),replace(c,'cd','') from example;
解释:用这两个函数分别用零长度字符串将字段C中第二个字符开始长度为2的字符去掉,以及将字段C中所有的字符“cd”清除掉。
结果:
五、修改字段类型
alter table 表名 rename column 字段名称 to 字段重命名称;
alter table 表名 add 表名 需要修改的字段类型(varchar/int);
update 表名 set 字段名称=字段重命名称;
alter table 表名 drop column 字段重命名称;
六、select for update的用法及实例解析
作用:select for update 是为了在查询时,避免其他用户以该表进行插入,修改或删除等操作,造成表的不一致性。
用法:
select * from t for update 会等待行锁释放之后,返回查询结果。
select * from t for update nowait 不等待行锁释放,提示锁冲突,不返回结果
select * from t for update wait 5 等待5秒,若行锁仍未释放,则提示锁冲突,不返回结果
select * from t for update skip locked 查询返回查询结果,但忽略有行锁的记录
使用 FOR UPDATE WAIT 子句的优点如下:
1、防止无限期地等待被锁定的行。
2、允许应用程序中对锁的等待时间进行更多的控制。
3、对于交互式应用程序非常有用,因为这些用户不能等待不确定。
4 、若使用了skip locked,则可以越过锁定的行,不会报告由wait n 引发的‘资源忙’异常报告。