Oracle sql语句整理合集

目录

1.创建用户，用户名是username，密码是password

2.给指定用户授予connect权限

3.给指定用户的表或者视图授予select（只读）权限，其中test是表名或者视图名

4.给指定视图添加同义词，方便用户查看，username是用户名，test是表名或视图名

5.合并两表数据并创建视图

6.去除某个字段中的逗号

7.获取当前系统日期

8.去除字段中的空格

9.清空表数据

10.去除检索出来的重复数据

11.查询已有日期与当前日期相比大于等于4的数据，并根据查询出来的数据修改表中符合条件的某个字段值 

12.decode进行条件查询筛选出符合条件的数据

---

---

1.创建用户，用户名是username，密码是password

create user username identified by password;

2.给指定用户授予connect权限

grant connect to username;

3.给指定用户的表或者视图授予select（只读）权限，其中test是表名或者视图名

grant select on test to username;

4.给指定视图添加同义词，方便用户查看，username是用户名，test是表名或视图名

create or replace synonym username.test for test;

5.合并两表数据并创建视图

CREATE OR REPLACE VIEW test AS
SELECT
A.XH AS ZH,
A.XM,
A.XBM AS XB,
D.DWH AS YXDM,  --院系
d.zyh as ZYDM,  --专业
C.BH AS BM,
A.SFZJH AS SFZ,
a.sjh AS SJH,
trim(a.xh)||'@163.com' as yx,
'student' AS JS,
'student' AS YHLXBH,
CASE WHEN B.XSDQZTMC='在读' THEN '1' ELSE '0' END AS ZHZT,
'NC'||A.XH AS NC,
'0' AS TBZT,
'-1' AS JGBH
FROM test2 A
LEFT JOIN B ON A.XH=B.XH
LEFT JOIN C ON B.BJM=C.BH
LEFT JOIN D ON C.ZYBH=D.ZYH
WHERE B.XH IS NOT NULL and c.bh is not null and B.XSDQZTMC='在读'

union all

select
js.gh AS ZH,
js.XM,
js.XBM AS XB,
'' AS YXDM,  --院系
'' as ZYDM,  --专业
js.dwh AS BM,
js.SFZJH AS SFZ,
js.sjh AS SJH,
coalesce(js.yx,trim(js.gh)||'@163.com') as yx,
'teacher' AS JS,
'teacher' AS YHLXBH,
'1' AS ZHZT,
'NC'||js.gh AS NC,
'0' AS TBZT,
'-1' AS JGBH
from test3 js where js.dwh is not null

6.去除某个字段中的逗号

select trim(',' from name) as name from t;

7.获取当前系统日期

select to_char(SYSDATE,'YY-MM-DD') from dual;

8.去除字段中的空格

select replace('d sed des',' ','') from test;

9.清空表数据

truncate table test;

10.去除检索出来的重复数据

select distinct(t2.name) from test t2;

11.查询已有日期与当前日期相比大于等于4的数据，并根据查询出来的数据修改表中符合条件的某个字段值 

update TblStudentInfo set Idcard = stuff(Idcard, len(Idcard) -5, 6, '000000') where DATEDIFF(YYYY, ApplyDate, getdate()) >= 4

 12.decode进行条件查询筛选出符合条件的数据

SELECT
        t.sfzjh,
        t.xm,
        decode(t.JDDW, '', t.DWH, (decode((select c.dwh from DWJBSJZL c where t.JDDW = c.DWMC), '', t.DWH, (select c.dwh from DWJBSJZL c where t.JDDW = c.DWMC)))) as DWH,
        t.gh,
        t.lxrq,
        t.sjh,
        t.jddw,
        t.rsxtzt,
        a.dwmc,
        t.xbm 
FROM
        JZGJCSJZL t,
        DWJBSJZL a 
WHERE
        t.DWH = a.DWH