sql语言基础

tnsping orcl
查看监听状态lsnrctl status
监听停止 lsnrctl stop
监听启动 lsnrctl start
数据库关闭 shutdown immediate,shutdown abort
数据库启动 startup
select status from v$instance;
alter user scott account unlock
alter user scott identified by bsoft
二：sql语言基础
2.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;


字符函数
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;




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 timezone
timestamp with local timezone


create table test5(a date,b timestamp);
insert into test5
values(sysdate,sysdate);
select * from test5




数据类型的转换
隐性类型转换，显性类型转换


隐性类型转换
select * from emp where empno ='7788';
select length(sysdate) from dual;
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;