Oracle常用基本命令(二)
本文主要用于记录常用的Oracle基本命令,由于多使用图形工具进行查询,命令使用较少,便于个人在以后使用时参考,其中有不正确的地方请指正。
1.数据去重
SELECT DISTINCT NAME FROM USER.NAME;
2.数据去重并统计数量
SELECT COUNT(*) AS NUM FROM (SELECT DISTINCT NAME FROM USER.NAME);
3.用户赋权限
GRANT SELECT ON USER.NAME TO ADMIN;
4.多表联合查询
SELECT A.FNAME, A.FCODE, B.NAME FROM USERA.NAME A, USERB.NAME B WHERE A.CODE = B.CODE;
5.创建视图
SELECT * FROM ( SELECT DBMS_LOB.GETLENGTH(PHOTO) AS FPHOTO FROM USER.NAME )WHERE FPSIZE > 10000 AND FPSIZE IS NOT NULL ;
6. 查询字段长度
SELECT * FROM USER.NAME WHERE LENGTH(USER.NAME)>3;
7.创建视图
CREATE OR REPLACE FORCE VIEW ADMIN."企业和用户"
(
"系统",
"编码",
"名称",
"登录名",
"姓名",
"身份证号",
"手机号码",
"证书数量",
"证书号码"
)
AS
SELECT (CASE A.B.FSYSTEMID
WHEN 1 THEN '01'
WHEN 2 THEN '02'
WHEN 3 THEN '03'
WHEN 4 THEN '04'
ELSE '未知
END)
AS 系统,
A.B.FCODE ,
A.B.FNAME ,
C.D.FLOGINNAME ,
C.D.FNAME ,
C.D.FIDCARD ,
C.D.FMOBILEVERIFYPHONE ,
A.B.FMVNUM ,
A.B.FMVCODE
FROM A.B, C.D
WHERE A.B.FID = C.D.FORGANID
AND C.D.FNAME = '测试'
ORDER BY 系统 ASC;