SQL实用技巧与优化-优快云博客

1、替换函数REPLACE()

REPLACE(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str。

三个参数可以是字符或二进制数据类型；from_str不能为空字符串 ('')；不支持 text,ntext类型字段的替换

如果其中的一个输入参数数据类型为 nvarchar，则返回 nvarchar；否则 REPLACE 返回 varchar。

如果任何一个参数为 NULL，则返回 NULL。

查询
SELECT REPLACE('abcdefg bcd','bcd','xxx')
select city_name,REPLACE(city_name,'市','') as city from tmp_city
更新
update T_RISK set FILE_PATH = REPLACE (FILE_PATH,'webs','web') where FILE_PATH is not null;

2、表锁

锁表

锁全表：select * from tablename where 1=1 for update
锁部分行：select * from tablename where 字段1=参数1 for update

解锁

--查看被锁的表 
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
--查看那个用户那个进程造成死锁
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
--杀锁
alter system kill session 'sid,serial#';

3、查询索引

根据表名查表的索引

 select INDEX_NAME,INDEX_TYPE FROM USER_INDEXES WHERE table_name='T_MCHT_ORDER_PAY_SERIAL';

根据索引名查索引详细信息

select dbms_lob.substr(dbms_metadata.get_ddl('INDEX','IND_T_MCHT_ORDER_PAY_SERIAL_ID'))from dual;

修改唯一索引

--修改唯一索引
--从BIZ_TYPE, ORG_PARTY_NO
--变更到BIZ_TYPE, ORG_PARTY_NO, SUB_BIZ_TYPE

--delete  indexes 
drop index IDX_CHARGE_PARTY_CONFIG;

-- Create/Recreate indexes 
create unique index IDX_CHARGE_PARTY_CONFIG on T_CHARGE_PARTY_CONFIG (BIZ_TYPE, ORG_PARTY_NO, SUB_BIZ_TYPE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

4、修改表

modidy修改字段属性

--单个字段
alter table table_name modify column column_name varchar(255) default '' COMMENT '注释';
--多个字段
alter table student modify(id number(4),studentName varchar2(100));

如果字段有数据，且类型不能直接修改，可以先修改原字段名SUB_ORG为SUB_ORG_COPY，新增字段SUB_ORG(目标类型)，把SUB_ORG_COPY中的数据转换复制给SUB_ORG，删除SUB_ORG_COPY

UPDATE 表名 SET SUB_ORG = CAST(SUB_ORG_COPY AS VARCHAR2(30));

alter添加字段

alter table T_CHARGE_PARTY_CONFIG add (SUB_ORG_NO VARCHAR2(32));

-- 修改表名

rename table old_table to new_table;
或
alter table old_table rename as new_table;

-- 修改列名称

alter table table_name  change column old_name new_name varchar(255);

5、数据类型转换函数

and to_char(crt_time, 'yyyyMMdd') >='20181101'
and crt_time >=to_date('2018-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')

6、常用系统sql

--查看数据库版本
select * from v$version;

7、Mysql设置创建时间字段和更新时间字段自动获取时间，填充时间

1、引言
在实际开发中，每条数据的创建时间和修改时间，尽量不需要应用程序去记录，而由数据库获取当前时间自动记录创建时间，获取当前时间自动记录修改时间。

2、创建语句
（1）–添加CreateTime 设置默认时间 CURRENT_TIMESTAMP

　　　　ALTER TABLE `table_name`
　　　　ADD COLUMN `CreateTime` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;
（2）–修改CreateTime 设置默认时间 CURRENT_TIMESTAMP

　　　　ALTER TABLE `table_name`
　　　　MODIFY COLUMN `CreateTime` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;
（3）–添加UpdateTime 设置 默认时间 CURRENT_TIMESTAMP 设置更新时间为 ON UPDATE CURRENT_TIMESTAMP

　　　　ALTER TABLE `table_name`
　　　　ADD COLUMN `UpdateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间' ;
（4）–修改 UpdateTime 设置 默认时间 CURRENT_TIMESTAMP 设置更新时间为 ON UPDATE CURRENT_TIMESTAMP

　　　　ALTER TABLE `table_name`
　　　　MODIFY COLUMN `UpdateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间' ;

8、mysql取整函数

ROUND(X) -- 表示将值 X 四舍五入为整数，无小数位
ROUND(X,D) -- 表示将值 X 四舍五入为小数点后 D 位的数值，D为小数点后小数位数。若要保留 X 值小数点左边的 D 位，可将 D 设为负值。

　SELECT ROUND('123.456')
　　123

SELECT ROUND('123.456',2)
　　123.46

FLOOR(X)表示向下取整，只返回值X的整数部分，小数部分舍弃。

　SELECT CEILING('123.456')
　　123

CEILING(X) 表示向上取整，只返回值X的整数部分，小数部分舍弃。

　SELECT CEILING('123.456')
　　124

9、mysql字符串拼接、截取函数

CONCAT(string1,string2,…)，此处是直接把string1和string2等等的字符串拼接起来（无缝拼接

CONCAT_WS(separator,string1,string2,…)，但是可以多个字符串用指定的字符串进行拼接（带分隔符拼接

substring(str,index,len) 截取str,从index开始，截取len长度，index从1开始，设0返回空