查询
- 查询序列的下一个值
SELECT EXAMPLE_SEQ.NEXTVAL FROM DUAL;
- 模糊查询的内容中存在%这样的特殊符号
select * from ip_table where regexp_like(ip,'%78');
PS: 如果是直接like,带有%会被直接默认为匹配符
- 查询关联的外键
select a.constraint_name, a.table_name, b.constraint_name from user_constraints a, user_constraints b where a.constraint_type = 'R' and b.constraint_type = 'P' and a.r_constraint_name = b.constraint_name and
a.constraint_name = 'FK_xxxx'
- 查询行数大于10万的表
select table_name, num_rows from dba_tables t where t.owner = upper('collect') and num_rows > 100000;
修改
- 一句SQL插入多条数据
insert all
into table_name (id,code,name)values(1,'c1','wo')
into table_name (id,code,name)values(2,'c2','ni')
into table_name (id,code,name)values(3,'c3','ta')
select * from dual;
PS: 在使用jdbcTemplate插入数据的时候,用这个sql需要去掉最后的;分号
- 将查询结果插入表中
insert into tbl_ref (id,ori_ip,ori_id,target_ip,target_id)
select seq_common.nextval,'192.168.5.101',123,ip,id from tbl_res where ip in(
'2409:8080:5a0a:5023::32f',
'2409:8080:5a0a:5023::3ae');
- 修改字段
ALTER TABLE tableName modify(columnName 类型);
DBLINK
- 查询已创建的dblink
select * from dba_db_links;
- 创建dblink
创建dblink语句:
create public database link 链路名 connect to 用户 identified by 口令 using ‘连接字符串’;
举例:
create public database link dblink_name connect to accept identified by pwd123 using '192.168.56.101:1521/ORCL'
- 删除dblink
举例:删除名字为dblink_name的dblink
drop public database link dblink_name
索引
- 创建
-- idx:索引名称
-- table_name:表名
-- column_name:字段名
CREATE INDEX idx_name ON table_name (column_name)
- 删除
-- idx:索引名称
DROP INDEX idx_name
存储过程
- 手动执行存储过程
-- 存储过程名称XXX
BEGIN
XXX;
END;