oracle中的的表查询语句

SELECT * FROM CUSINFO


SELECT C.CUSNO,C.CUSNAME,(
CASE
  WHEN C.CUSCREDIT='1' THEN '☆'
    WHEN C.CUSCREDIT='2' THEN '☆☆'
     WHEN C.CUSCREDIT='3' THEN '☆☆☆'
         WHEN C.CUSCREDIT='4' THEN '☆☆☆☆'
             WHEN C.CUSCREDIT='5' THEN '☆☆☆☆☆'
               END  
)AS DJ
FROM CUSINFO C
--左裁剪
SELECT LTRIM('FWEJFHWF','FWE')
FROM DUAL
--右裁剪
SELECT RTRIM('CDJBDSJ','SJ')
FROM DUAL
--安字符翻译
SELECT TRANSLATE('BDDHIEWUHBCAJ','ABCED','12348')
FROM DUAL
--替换
SELECT REPLACE('BLACK AND BLUE','B','C')
FROM DUAL
--查找字符位置
SELECT INSTR('HAVSAYUGQ','A')
FROM DUAL
--取子字符串
SELECT SUBSTR('YGWUIWPGUEIBI',3,4)
FROM DUAL
--长度
SELECT LENGTH('HJSBVVJSLFH')
FROM DUAL
--合并
SELECT CONCAT('FAL','LAY')
FROM DUAL
--首字母大写
SELECT INITCAP('WORD')
FROM DUAL
--转化为小写
SELECT LOWER('GGHHHJ')
FROM DUAL
--转化为大写
SELECT UPPER('JHDSH')
FROM DUAL


SELECT * FROM CARD
INSERT INTO CARD(PASSWORD) VALUES('OIUYO2QOIED')
SELECT TRANSLATE(C.PASSWORD,'OI','01')
FROM CARD C




SELECT * FROM SELLRECORD
SELECT *
FROM SELLRECORD S
ORDER BY SUBSTR(S.LISTNUMBER,0,INSTR(S.LISTNUMBER,'-')-1),LENGTH(SUBSTR(S.LISTNUMBER,INSTR(S.LISTNUMBER,'-')+1,LENGTH(S.LISTNUMBER))),SUBSTR(S.LISTNUMBER,INSTR(S.LISTNUMBER,'-')+1,LENGTH(S.LISTNUMBER));
NJX_WORK
WHERE CUSNAME LIKE '%聪海%' AND CUSREGION='北京' AND CUSLEVEL='战略合作伙伴'
ORDER BY CUSNO DESC;


select t.*, t.rowid from YUPQ_WEBTHEME t
select  months_between(to_date('2014-10-10','yyyy-MM-dd'),to_date('2014-2-10','yyyy-MM-dd')) from dual;
select  to_date('2014-10-10','yyyy-MM-dd') from dual;
select months_between('2014-10月-10','2014-5月-10')from dual
select to_char(sysdate ,'ddd') from dual
select to_char(sysdate ,'dd') from dual
select to_char(sysdate ,'d') from dual
select to_char(sysdate ,'ww') from dual
select to_char(sysdate ,'mm') from dual
select to_char(sysdate ,'yyyy-MM-dd dy hh-mi-ss') from dual
select *from emp;
select decode(e.deptno ,20,'部门编号20',10，'部门编号10'，30，'部门编号30','其他else') as decode , e.deptno
from emp e


select count(1) from cusinfo c
where c.cuscredit=3 ;
select * from cusinfo c
where c.cuscredit=3 ;




select *from cusorder;
select *from cusorder co;
SELECT C.CUSLEVEL, COUNT(1)
FROM CUSINFO C
WHERE C.CUSREGION='北京'
GROUP BY C.CUSLEVEL
HAVING COUNT(1)>1
--内连接
select  *
from cusinfo c inner join cuslinkman l
on  c.cusno =l.cusno
select *
from cusinfo c,cuslinkman l
where  c.cusno =l.cusno


--外连接 左外连接
select  c.cusname ,l.lkmname
from cusinfo c left join cuslinkman l
on  c.cusno =l.cusno
--简写方式
select  c.cusname ,l.lkmname
from cusinfo c , cuslinkman l
where  c.cusno =l.cusno(+)
--外连接 右外连接
select  c.cusname ,l.lkmname
from cusinfo c right join cuslinkman l
on  c.cusno =l.cusno
--全外连接
select  c.cusname ,l.lkmname
from cusinfo c full join cuslinkman l
on  c.cusno =l.cusno


SELECT C.CUSNO, C.CUSNAME, A.ATVDATE, A.ATVTITLE, L.LKMNAME, L.LKMTEL
  FROM CUSINFO C, CUSLINKMAN L, CUSACTIVITY A
 WHERE C.CUSNO = A.CUSNO
   AND C.CUSNO = L.CUSNO
select c.cusno ,c.cusname ,a.*
from cusinfo c left join cusactivity a
on c.cusno=a.cusno


select co.cusno, sum(co.ordmoney) s
 from cusorder co
 group by co.cusno
 
 select  c.cusno,c.cusname ,n.s
 from cusinfo c ,(select co.cusno, sum(co.ordmoney) s from cusorder co group by co.cusno) n
 where c.cusno=n.cusno
 
 select co.ordno ,co.orddate,cd.oddproductid,cd.oddprice,cd.oddamount
 from cusorder co ,cusorderdetail cd
 where co.ordno=cd.ordno
 
 SELECT C.CUSREGION, COUNT(1) S
   FROM CUSINFO C
  WHERE C.CUSBANKROLL IS NOT NULL
  GROUP BY C.CUSREGION
 HAVING COUNT(1) > 3
 select * from cusorder
 select * from cusinfo
 select c.cusno,c.cusregion, c.cusname ,co.orddate,co.ordmoney
 from cusorder co ,cusinfo c
 where c.cusno=co.cusno




select n.cusregion, sum (n.ordmoney)
from (
select c.cusno,c.cusregion, c.cusname ,co.orddate,co.ordmoney
 from cusorder co ,cusinfo c
 where c.cusno=co.cusno
 ) n
 group by n.cusregion 
select c.cusregion, sum (o.ordmoney)
from cusinfo c ,cusorder o
where c.cusno=c.cusno
group by c.cusregion
select *
   from cusinfo c
  where c.cusno in (select o.cusno
                      from cusorder o, cusorderdetail dd
                     where o.ordno = dd.ordno
                       and dd.oddproductid = 'pc-00001');
--以上子查询做为条件放在where后面


--一些子查询放在from后面作为表
select *
  from (select * from cusinfo c where c.cusbankroll >= 500000) A
 where a.cuscredit > 2;
 
 
 SELECT C.CUSNAME, A.S
  FROM (SELECT O.CUSNO, SUM(O.ORDMONEY) S FROM CUSORDER O GROUP BY O.CUSNO) A,
       CUSINFO C
 WHERE A.CUSNO = C.CUSNO;
 
--子查询放在select后面，作为结果的列


select
l.lkmname,
l.lkmmobile,
(select c.cusname from cusinfo c where l.cusno = c.cusno ) cname
from cuslinkman l;


select *from scott.emp;
select *from scott.dept;
select *from scott.salgrade;
select * from cusorder;
select * from cusinfo;
select *from cuslinkman
select o.ordreceiver
from cusorder o
where o.ordreceiver in(
select l.lkmname 
from cuslinkman l
where l.cusno=
(select  c.cusno
from cusinfo c
where c.cusname='聪海信息科技有限公司'))


select * 
from cusinfo c2
where c2.cuslevel =(
select c.cuslevel
from cusinfo c
where c.cusno='KH071202001')


select *
from cusinfo c
where c.cusno not in(
select  o.cusno
from cusorder o
)