0.常用命令
tnsping orcl（Transparent Network Substrate透明网络底层）
查看监听状态lsnrctl status
监听停止 lsnrctl stop
监听启动 lsnrctl start
sqlplus /nolog
conn /as sysdba
数据库关闭 shutdown immediate,shutdown abort
数据库启动 startup
upgrade
select status from v$instance;
alter user scott account unlock;
alter user scott identified by bsoft;
1. oracle命令类别
数据
库操纵语言 DML：select,insert,delete,update,merge
merge into products p using newproducts np on (p.product_id = np.product_id)
when matched then
update set p.product_name = np.product_name
when not matched then
insert values(np.product_id, np.product_name, np.category)
数据定义语言 DDL：create ,alter,drop,truncate,rename,comment
create table TEST
（
ID VARCHAR2(10),
NAME VARCHAR2(30)
）;
ALTER TABLE test ADD address varchar2(20);
ALTER TABLE test MODIFY address varchar2(30);
ALTER TABLE test RENAME column address TO newaddress;
ALTER TABLE test DROP COLUMN newaddress;
ALTER TABLE test RENAME TO test1;
rename test1 to test;
comment on table test is '测试表';
select * from user_tab_comments where TABLE_NAME='TEST';
comment on column TEST.NAME is '名称' ;
select * from user_col_comments where TABLE_NAME='TEST' and column_name='NAME';
事务控制语言 TCL: commit,rollback,savepoint
insert into test values(1,'a');
savepoint A;
insert into test values(2,'b');
savepoint B;
insert into test values(3,'c');
savepoint C;
rollback to A;
commit;
数据控制语言 DCL: grant,revoke
grant dba to scott;
revoke dba from scott;
2.常用函数
字符函数
select upper('bsoft') from dual;
select initcap('bsoft') from dual;
select initcap('bSOFT') from dual;
select concat('welcome ',concat('to ','bsoft')) from dual;
select substr('welcome to bsoft',12,7) from dual;
select instr('welcome to bsoft','bsoft',11) from dual;
select length('bsoft张振磊') from dual;
select lengthb('bsoft张振磊') from dual;
select lpad('张振磊',10,'1') from dual;
select rpad('bsoft',11,'bsoft') from dual;
select replace('bsoft张振磊','张','zhang') from dual;
select trim('b' from 'bbbbsoftbzzlbb') from dual;
数值函数
select round(45.5,0) from dual;
select round(155.1,-1) from dual;
select round(155.1,-2) from dual;
select trunc(155.1) from dual;
select trunc(155.23,-1) from dual;
select mod(11,3) from dual;
日期函数
select to_date('2016.08.11 10:10:10','yyyy-mm-dd hh24:mi:ss') from dual;
select sysdate + 10 from dual;
select sysdate,sysdate + 10/24 from dual;
select ename,hiredate,sysdate,(sysdate - hiredate)/365 from scott.emp;
select ename,hiredate,sysdate,months_between(sysdate,hiredate)/12 from emp;
select add_months(sysdate,1) from dual;
select last_day(sysdate) from dual;
select next_day(sysdate,7) from dual;
select round(sysdate,'month') from dual;
select trunc(sysdate+10,'month') from dual;
select round(sysdate,'year') from dual;
select trunc(sysdate,'year') from dual;
几个有用的函数
select job,sal,decode(job,'CLERK',sal*1.1,'SALESMAN',sal*1.2,sal) from emp；
select job,sal,case job when 'CLERK' then sal*1.1 when 'SALESMAN' then sal*1.2 else sal end from emp；
select job,sal,case when job='CLERK' then sal*1.1 when job='SALESMAN' then sal*1.2 else sal end from emp；
select case when 1=1 then 2 when 2=2 then 3 end from dual；
select ename,job,sal,case when sal>=5000 then '高级' when sal >=3000 then '中级' else '低级' end from emp；
select distinct job from emp；
select sys_context('userenv','ip_address') from dual;
select sys_context('userenv','sid') from dual；
select sys_context('userenv','terminal') from dual
select ascii('d') from dual;
select chr(100) from dual;
3.sql数据类型
字符型
char()固定字长 最大2000
varchar2()可变字长 最大4000
create table test(a char(3));
insert into test
select 1 from dual;
select length(a) from test;
create table test2(a varchar(3))
insert into test2
select 1 from dual;
select length(a) from test2;
alter user scott quota unlimited on users;
数值型
number(p,s)
int
create table test3(a number(4,2));
insert into test3
values(111.11);
create table test4(a int);
insert into test4
values(111.11);
commit;
select * from test4;
日期型
date
timestamp
timestamp with time zone
timestamp with local time zone
create table test5(a date,b timestamp,c timestamp with time zone,d timestamp with local time zone);
insert into test5
values(sysdate,sysdate,sysdate,sysdate);
select * from test5
数据类型的转换
隐性类型转换，显性类型转换
隐性类型转换
select * from emp where empno ='7788';
select length(sysdate) from dual;
length(sysdate) =9？
select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';
select * from nls_instance_parameters where parameter='NLS_DATE_FORMAT';
select * from nls_database_parameters where parameter='NLS_DATE_FORMAT';
千年虫
select '12.5' + 11 from dual;
select 10 + '12.5' ||11 from dual;
select 10 + ('12.5' ||11) from dual
显性类型转换
to_char
to_date
to_number
select ename,to_char(hiredate,'DD-MON-YY') from emp;
select ename,to_char(hiredate,'yyyy-mm-dd'),to_char(hiredate,'fmyyyy-mm-dd') from emp;
select ename,sal,to_char(sal,'L999.99') from emp;
select to_date('2016.08.31','yyyy.mm.dd') from dual;
select to_number('$2016','$9999.99') from dual;
4.where 子句中常用的运算符
算数运算符
+-*/
逻辑运算符
not ,and ,or
比较运算符
单行比较=,>,>=,<=,<
多行比较符>any,>all,<any,<all,in,not in
模糊比较like(%,_)
特殊比较is null
()优先级最高
between and
select ename,sal from emp where sal between 3000 and 5000;
转义
create table test6(a varchar(20));
insert into test6
values('AZZL');
insert into test6
values('A%ZZL');
select * from test6 where a like 'A%%'
select * from test6 where a like 'A\%%' escape '\'
'' 和 ""
''内表示字符
"" 别名 ，保持原样
select a from test6
select "a" from test6
select "A" from test6
连续两个'表示转义
select empno||'''s name is ' ||ename from emp
in 检验一个值是否在一个列表中
select * from emp where empno in(7788,7900)
交互输入变量符&
select * from emp where empno=&emp_no
使用逻辑操作符
select * from emp where sal >1000 and job ='CLERK';
select * from emp where sal >1000 or job ='CLERK';
select * from emp where job not in('CLERK');
5.分组函数
最重要的5个分组函数
sum(),avg(),count(),max(),min()
select sum(sal) sum,avg(sal) avg,max(sal) max,min(sal) min ,count(*) count from emp;
select job, sum(sal) sum,avg(sal) avg,max(sal) max,min(sal) min ,count(*) count from emp group by job;
select min(hiredate),max(hiredate) ,min(ename),max(ename) from emp ;
select count(*),count(comm) from emp ;
select deptno,avg(comm) from emp group by deptno;
select deptno,avg(nvl(comm,0)) from emp group by deptno;
group by 创建组
一旦使用了group by ，select后面只能有两种列：组函数和分组特性列
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000；
select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno；
select deptno,avg(sal) from emp where sal>2000 group by deptno；
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000 order by avg(sal);
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000 order by job;
select from where group by having order by
分组函数的嵌套
单行函数可以嵌套任意层，分组函数最多可以嵌套两层
select sum(sal) from emp group by deptno;
select avg(sum(sal)) from emp group by deptno;
select count(avg(sum(sal))) from emp group by deptno;
6.数据限定与排序
where限定 from后面的表或视图，限定的选项只能是表的列或列单行函数或列表达式，where后不可以直接使用分组函数
select job,ename,sal from emp where sal >2000;
select job,ename,sal from emp where length(sal)>3;
select job,ename,sal from emp where sal +comm >2000;
having 限定group by的结果，限定的选项必须是group by 后的聚合函数或分组列，不可以直接使用where后的限定选项
select deptno,sum(sal) from emp group by deptno having deptno=10;
select deptno,sum(sal) from emp group by deptno having sum(sal)>8000;
排序
order by 总是在一个select语句的最后边
排序可以使用列名，列表达式，列函数，列别名，列位置编号，select的投影列可不包含排序列，除指定的列位置编号外
升序asc 降序 desc，有空值的列的排序，缺省（升序）时，null排在最后面
使用多个列排序，多列用逗号隔开，每列后面可以指定升降序
select ename,sal from emp order by sal；
select ename,sal salary from emp order by salary；
select ename,sal salary from emp order by 2；
select ename,sal,comm ,sal+comm from emp order by sal +comm；
select deptno,avg(sal) from emp group by deptno order by avg(sal) desc；
select ename,job,sal+comm from emp order by 3 desc；
select ename,job,sal+comm from emp order by 3 desc nulls last；
select ename,job,deptno from emp order by deptno asc,job desc；
