PLSQL指令总结 v1.0

一、基本查询

(表：emp  列：ename ,depton,sal  、表示换行）

1.查当前用户  SQL> show user

2.查表结构  SQL> desc emp  （emp：表名）  --查询出名称、类型、是否为空

3.清屏  SQL> host cls

4.设置行宽  SQL> show linesize 、 set linesize 120     设置列宽  SQL> col ename for a8 、 col sal for 9999

5.包含 null 的表达式都为 null 。

   解决办法：nvl(a,b)  --如果 a为null 则返回 b；不为空返回 a

   --nvl(a,b,c)  如果 a为null 则返回 b；否则返回 c

6.distinct：去掉重复记录  SQL> select distinct depton from emp

7.||：连接符 、concat：连接函数

   SQL> select concat('hello','  world') from emp       --注：select函数后面必须要有from关键字

8.伪表：dual

   SQL> select 'hello' || '  world' 字符串 from dual 、 SQL> select ename||'的薪水是'||sal 信息 from emp

 

二、过滤，排序

1.注意：字符串的大小写严格区分、日期格式敏感 如  DD-MON-RR：'17-11月-81'

2.修改日期格式  SQL > alter session set NLS_DATE_FORMAT='yyyy-mm-dd'  --修改格式为2017-11-81

3.设置区间：between and  --小值在前大值在后

4.在设置集合中：in    SQL> select * from emp where deptno in (10,20)     

   --对应的，不在就是 not in    另外，如果及合作含有 null 则不能使用 not in 仅可使用 in

   SQL> select * from emp where deptno in (10,20,null)

5.模糊查询

   查询名字以 S 开头的员工    SQL> select * from emp where ename like 'S%'    --'%’表示n个字符

   查询名字是四个字的员工    SQL> select * from emp where ename like '----'    --'_'表示单个字符

   查询名字中含有下划线的    SQL> select * from emp where wname like '%\_%'    --'\'表示转义字符

6.排序：order by    desc：降序  asc：升序    --by后参数可以直接写数字，对应select列表中的列

   多列排序  SQL> select * from emp order by deptno desc , sal desc    --desc只作用于它前面的列

   为保证空值在后面可以添加 nulls last    --默认null是最大的值

 

三、函数

1.字符函数    

   1)SQL> select lower('Hello World') 转小写,upper('Hello World') 转大写,initcap('hello world') 首字母大写

  2)substr  --取子字符串    SQL> select substr ('hello world',4) 子串 from dual

   3)length/lengthb 字符/字节 数  SQL> select length('hello world') 字符,lengthb('hello world') 字节 from dual

   4)instr(a,b)  --在a中查找b    SQL> select instr('hello world','ll') 位置 from dual

   5)lpad/rpad  --左/右 填充    SQL> select lpad('abcd',10,'*')  --在abcd左填充6个*使串达到10位

   6)trim  --去掉前后指定的字符    SQL> select trim('H' from 'Hello WorldH') from dual  --取两个H中间部分

   7)replace  --不显示某字符    SQL> select replace('hello world','l','*') from dual    --用*代替 l

2.四舍五入 round 

   SQL> select round(45.926,1) 1,round(45.926,0) 2,round(45.926,-1) 3,round(45.926,-2)

   1--45.9  、2--46  、 3--50 、 4--0

3.当前时间  sysdate    10个月后：add_months(sysdate,10)

4.根据job不同进行不同操作

1) select ename,job,sal,

    case job when 'yuangong' then sal+800

                 when 'jingli' then sal+1000

                 else sal+500

    from emp

2) select ename,job,sal,

         decode(job,'yuangong',sal+800,

                            'jingli',sal+1000,

                            sal+500)

    from emp

5.求和：sum（列名）   求记录数：count（*/列名）  求平均：avg（列名）

6.having：可以使用多行函数而where不行

   select deptno,avg(sal) from emp group by deptno having deptno=10

 

四、多表联查

1.外连    SQL> select d.deptno,d.dname,count(e.empno) from emp e,dept d where ~

  左外连会把最后不成立的左表项包含在结果中，右外连则包含右表

2.子查询

  1)可以在主查询的 where select having from 后使用子查询

  2.除top-n问题外一般不在子查询中排序

 

五、对表的操作

1.取另表数据创表    SQL> create table emp00 as select * from emp where deptno=20

  SQL> create table empinfo as 

                select e.empno,e.ename,e.sal,d.dname from emp e,dept where e.deptno=d.deptno

2.修改列    SQL> alter table emp modify ename varchar2(40)

   删除列    SQL> alter table emp drop column photo

   重命名列    SQL> alter table emp rename column tname to username

   重命名表    SQL> rename text1 to text2

 

六、其它

1.分页

    SQL> select *

        from (select rownum r,e1.* from (select * from emp order by sal ) e1

            where rownum <= 8 )

         ) where r >= 5