?
?
?卸載好注冊表,然后安裝11g
?
?
?
?
?每次在執行orderby的時候相當于是做了全排序,思考全排序的效率
會比較耗費系統的資源,因此選擇在業務不太繁忙的時候進行
--給表添加注釋
comment on table emp is '雇員表'
--給列添加注釋;
comment on column emp.empno is '雇員工號';select empno,ename,job
from emp
where deptno=10;select distinct deptno from emp;select e.empno 雇員編號,e.ename 雇員名稱,e.job 雇員工作
from emp e
where e.deptno=10;在sql中的null值必須要用is和is not來判斷
?between
?exists
nvl(arg1,arg2),如何arg1是空,則返回arg2,如果arg1不為空,則返回arg1
?dual是oracle數據庫中的一張虛擬表,沒有實際的數據,可以用來做測試
?
/*
--給表添加注釋
comment on table emp is '雇員表'
--給列添加注釋;
comment on column emp.empno is '雇員工號';select empno,ename,job
from emp
where deptno=10;select distinct deptno from emp;select e.empno 雇員編號,e.ename 雇員名稱,e.job 雇員工作
from emp e
where e.deptno=10;
*/
select * from emp where deptno <>20;
select sal from emp where sal> any(1000,1500,3000);
select sal from emp where sal> some(1000,1500,3000);
select sal from emp where sal> all(1000,1500,3000);select * from emp where comm is null;
select * from emp where comm is not null;
select * from emp where sal between 1500 and 3000;
select * from emp where deptno in(10,20);
select * from emp where deptno =10 or deptno= 20;
--and的優先級高于or
select *
from emp e
where exists(select deptnofrom dept dwhere (d.deptno=10 or d.deptno=20) and e.deptno=d.deptno)
select * from emp where ename like('S%');
select * from emp where ename like('S%T_');
select * from emp where ename like('%\%%')escape('\');
--escape轉義字符
select * from emp where ename like('JO%');
select * from emp order by sal;
select * from emp order by sal desc;
select * from emp order by sal desc ,ename asc;--計算字段
select 'my name is'||ename from emp;
select concat('my name is',ename) from emp;
select ename,(e.sal+nvl(e.comm,0))*12 from emp e
select 100+null from dual
--并集union
--全集 union all
select * from emp where ename like('S%') minus
select * from emp where sal between 300 and 2000;
?
函數
?
?組函數又稱為聚合函數
輸入多個值,最終只會返回一個值
select sum(sal) from emp;
select count(*) from emp;
select initcap(ename) from emp;
select upper(ename) from emp;
select lower(ename )from emp;
select lpad(ename,10,'*')from emp;
select rpad(ename,10,'*')from emp;
select trim(ename) from emp;
select ltrim(ename) from emp;
select rtrim(ename) from emp;
select instr(ename,'A') from emp;
select length(ename) from emp;
select substr(ename,0,2)from emp;
select replace('abcdefg','ab','hehe')from emp;
select round(123.129,2) from dual;
select trunc(123.128,2) from dual;
select mod(10,4)from dual;
select ceil(10.555)from dual;
select floor(1255.222)from dual;)
)
