oracle培训第一天

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；