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返回空