sql笔记，2：亦可用regex
--创建用户①
--GRANT（授权）REVOKE（回收权限）
--创建用户
create user 用户名 identified by 密码
GRANT CONNECT,RESOURCE TO 用户;
GRANT CREATE VIEW TO 用户;
GRANT CREATE SYNONYM TO 用户;
GRANT CREATE JOB TO 用户;
GRANT SELECT ANY TABLE TO 用户;
--
GRANT SELECT ON 表名/视图名 TO 用户/角色；
--Ⅰ给用户分权限Ⅱ创建角色，给用户赋予角色，给角色分权限
--创建用户②
PROMPT =============================================================================================================
PROMPT =====Step One: 定义数据文件存储路径位置(该路径需根据实际部署环境进行调整 important!!!)
PROMPT =====可查阅select * from dba_data_files;视图查看数据文件存储路径.
PROMPT =============================================================================================================
define __DATAFILE_DESTINATION__ = '/Oradata/wzhipdb/wzhip/';
--jgptf替换
--create tablespace
CREATE TABLESPACE tbs_hip_jgptf DATAFILE '/Oradata/wzhipdb/wzhip/tbs_hip_jgptf.dbf' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED;
--create temporary tablespace
CREATE TEMPORARY TABLESPACE tbs_hip_jgptf_temp tempfile '/Oradata/wzhipdb/wzhip/tbs_hip_jgptf_temp.dbf' SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;
--create user CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER yhpt_jgptf IDENTIFIED BY yhpt_jgptf_wz DEFAULT TABLESPACE tbs_hip_jgptf TEMPORARY TABLESPACE tbs_hip_jgptf_temp ACCOUNT UNLOCK;
ALTER USER yhpt_jgptf QUOTA UNLIMITED ON tbs_hip_jgptf;
--grant privs
GRANT CONNECT,RESOURCE TO yhpt_jgptf;
GRANT CREATE VIEW TO yhpt_jgptf;
GRANT CREATE SYNONYM TO yhpt_jgptf;
GRANT CREATE JOB TO yhpt_jgptf;
GRANT SELECT ANY TABLE TO yhpt_jgptf;
--同义词
CREATE [PUBLIC] SYNONYM 同义词名称 FOR 数据库对象；--（加public为公用，不加为私有进创建用户可见）
--sql判断数字字母汉字
一.包含
1.包含中文字符
select * from 表名 where 列名 like '%[吖-座]%'
2.包含英文字符
select * from 表名 where 列名 like '%[a-z]%'
3.包含纯数字
select * from 表名 where 列名 like '%[0-9]%'
二.判断
如：判断中文
select * from dws_new_role where ascii(game_server) > 123
判断数字
select * from dws_new_role where ascii(role_id) between 48 and 57
三.判断sql是否为纯数字
1：regexp_like(data,'^[0-9]+$')纯数字。
2： 亦可用 regexp_like(data, '^[[:digit:]]+$');
3.判断是否为3位纯数字：regexp_like(data,'^[0-9]{3}$')
--nvl、case when
nvl((case when regexp_like(h.C_OP_ID,'^[0-9]+$') then h.C_OP_ID else '' end ),(select c_emp_id from x_user where c_id=h.C_OP_ID))
(case when regexp_like(h.C_OP_ID,'^[0-9]+$') then h.C_OP_ID else (select c_emp_id from x_user where c_id=h.C_OP_ID) end )
--结合and、or运算符
select *
FROM U_PAT a, U_REG b
WHERE b.C_PAT_ID = a.C_ID
AND (( '1' = '3' and a.c_name='小红' ) or
('2' = '3' and a.c_name='小兰' ) or
('3' = '3' and a.c_name='小绿') )
--查询字段在数据库中哪些表中存在
select c.owner, c.table_name, c.* from dba_tab_columns c where c.column_name like '%C_UNIT_YF%';
--查询关键词/字段在数据库中哪些表中存在
declare
v_Sql varchar2(2000);
v_count number;
begin
for xx in (select t.OWNER, t.TABLE_NAME, t.COLUMN_NAME
from dba_tab_columns t
where t.OWNER = '密码') loop
begin
v_Sql := 'select count(1) from ' || xx.owner || '.' || xx.table_name ||
' where ' || xx.column_name || ' like ''%查询字段/关键词%'' ';
execute immediate v_Sql
into v_count;
if (v_count >= 1) then
dbms_output.put_line(xx.table_name || ':' || xx.column_name);
end if;
exception
when others then
null;
end;
end loop;
end;
--查询表空间使用情况及剩余
select 表空间名称,
文件数量,
"文件总大小(G)",
"表空间文件剩余大小(G)",
round(("文件总大小(G)" - "表空间文件剩余大小(G)") / "文件总大小(G)", 4) * 100 as "使用率 %",
"ORACLE表空间剩余大小(G)",
"ORACLE表空间使用大小(G)",
"ORACLE使用率 %"
from (SELECT a.tablespace_name "表空间名称",
fileNumbers "文件数量",
fileNumbers * 32 "文件总大小(G)",
fileNumbers * 32 - round(total / (1024 * 1024 * 1024), 2) "表空间文件剩余大小(G)",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "ORACLE表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "ORACLE表空间使用大小(G)",
round((total - free) / total, 4) * 100 "ORACLE使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
count(*) as fileNumbers,
SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name) a
--只显示排名前n的数据行
where rownum <= n
select * from (写好的sql语句) where rownum <= n
--转换
REPLACE(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str
--多列合并
select wm_concat(colName) from table 把列值合并（用英文逗号分割）
select LISTAGG(c_rytypemc, ',') WITHIN GROUP(ORDER BY c_reg_id) DETAIL_IDS from y_yb_js group by c_reg_id
over(partition by XXX)
select LISTAGG(c_rytypemc, ',') WITHIN GROUP(ORDER BY c_reg_id) over(partition by c_reg_id) DETAIL_IDS from y_yb_js--条数不变
--sql的loop循环 https://blog.csdn.net/w0131/article/details/111590726
--round
round(字段,返回位数)
--oracle 数据回滚 转自 https://dandelioncloud.cn/article/details/1440065238732472322/
1、 – 查询你执行update 语句之前的数据 精确到什么时间
select * from 表名 as of timestamp to_timestamp('2017-07-21 17:16:38', 'yyyy-mm-dd hh24:mi:ss');
2、 – 开启可移动数据命令，执行完就可以回滚数据
alter table 表名 enable row movement;
3、 —正式回滚 update 语句前的数据
flashback table 表名 to timestamp TO_TIMESTAMP('2017-07-21 17:16:38', 'yyyy-mm-dd hh24:mi:ss');