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