ORACLE數據庫 常用命令和Sql常用語句

ORACLE

賬號相關

如何獲取表及權限

1.COPY表空間
backup scott
exp
登錄管理員賬號system
2.創建用戶
create user han identified(認證) by mima default tablespace users（默認的表空間） quota（配額）10M on users;創建賬號
分配權限
grant creatr session,create table,create view to 賬號
grant connect to han; 賦予賬號han登錄權限
3.import the date引入數據

過程全選YES

imp

alter user scott account unlock; 解鎖賬號

?

對表的操作?

建表

--新增2個字段
alter table stu
add(
stu_tel varchar2(11),--電話
stu_addr varchar2(50)--地址
);
alter table stu
modify (stuName varchar2(20),?
stu_tel varchar2(8)?
);?
刪1段alter table stu
drop column stuId;?
刪除多段alter table stu
drop (stu_tel,stu_addr);

?

增
insert into table_name values (值1, 值2,....)
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

改
update table_name set stuAge = 27 where stuno='1001'；

update table_name set stuAge = 27,stuname='abc' where stuno='1002';

刪
delete from emp3;
delete from stu where stuno='1003';

*查
select * from stu where stuno='1001';
select * from stu where stuno='1002';
查詢所有列
select * from stu t
重復的只顯示一條
select distinct stuno from stu;

select * from stu where stuno='1006';
select stuno,stuage from stu;

select distinct stuno from stu;
select * from stu where stuno ='1006' and stuage=29;
select * from stu order by stuage desc;
select * from stu order by stuage desc,stuno asc;

備份表
create table stuBAK as
select * from stu;

?

desc emp;描述EMP表有什么字段

?

一單條select語句（必須熟練！）

查詢語句組成
1.
select * from emp; 查詢emp表的所有的內容
select ename，sal*12 from emp；
select ename，sal*12 anuual_sal（anuual_sal表名可以隨便寫2個單詞用下劃線隔開 不然顯示from不存在 不要占from的位置 簡而言之 名字只能占一個 如果非得占2個 必須帶上雙一號“anuual sal”包住 默認引號內的是名字 而且顯示是小寫 可以認為 “”可以保持你輸入時的狀態 當然 中文也行） from emp；
select enamel||'哈哈（隨便寫字符串）' from emp; ||+'字符' 表示字符串拼接
select enamel||' 哈'哈（隨便寫字符串）' from emp; 是錯誤的 如果字符串里面有單引號 需要轉義
select distinct deptno from emp; 重復的只顯示一次
select distinect deptno，job from emp； 這個有重復的 這個不是表里自己和自己比 而是 2個表 deptno和job 2個表如果有重復的 只顯示一次

2.from 那張表？

3.where 出現在單行語句 作為篩選條件優先執行
select ename，sal from emp where sal=800；
select ename，sal from emp where sal!=800；
select ename，sal from emp where sal>=800 and sal<=1500； 包含800和1500
select ename，sal from emp where sal between and 1500； 包含800和1500
select ename,sal,deptno from emp where sal not in (800,1500); 薪水不是800和1500的
select ename，sal，comm from emp where comm is null； 查出ename，sal，comm里面有null的表 之所以不用=因為 null是空值就是沒值的意思
select ename，sal，comm from emp where comm is not null；
select ename，sal from emp where sal in （800,1500,2000); 選出sal=800或者1500或者2000的表
select ename，sal from emp where ename in （'SMITH','KING','JONES'）; 選出'SMITH','KING','JONES'表
select ename,sal from emp where deptno=10 or sal>1500;
select ename,sal,deptno from emp where deptno=10 or sal>1500;

4.<--group by>（必須掌握）（需求 每個部門的平均薪資）
select avg(sal)from emp group by deptno;

select avg(sal)from emp group by deptno , job;

select deptno ,job,max(sal)from emp group by deptno , job;

select ename,max(sal)from emp;錯誤 組函數（一個都不能忘！）(多行輸入 只有一行輸出)
select ename from emp where sal=(select max(sal)from emp);

select ename ,max(sal) from emp group by deptno;錯誤 ename必須出現在 max（）和group by 2個中一個，只有這樣才唯一
select ename ,max(sal)from emp group by ename; 正確
select deptno,max(sal)from emp group by deptno;正確

5.having（需求每個部門平均薪資大于2000的有哪些 不能用where 因為where只能由于一條語句或者說一個表的篩選）
select avg(sal),deptno form emp group by deptno having avg(sal)>2000;

6.order by

select * from dept order by deptno desc; 降序排列
select empno,ename from emp; 升序
select empno,ename from emp order by asc; 升序
select empno,ename from emp where deptno!=10 order by empno asc; 部門標號不是10的 按升序排列的表
select ename,sal,deptno from emp order by deptno asc; 按部門編號升序
select ename,sal,deptno from emp order by sal asc;按薪水升序
select ename,sal,deptno from emp order by sal asc,ename desc; 薪水升序的情況下 名字倒序
select ename,sal*12 anuual_sal from emp where ename like '_A%' and sal>800 order by sal desc;

總結 1 select avg（sal），字段名 2 from 表 3 where 4 group by 5 having 6 order by

select avg(sal)from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc;

二,子查詢 (把他當成一張表！）

select ename ,max(sal) form emp;錯誤 因為這個max（sal）可以對應很多ename 不是一一對應關系
select ename,sal from emp where sal=(select max(sal)from emp);

需求 有哪些人的工資在平均工資之上

select ename,sal from emp where sal > (select avg(sal)from emp);

表連接

<--內連接>
<--寫法1 常用>
select d1.dname , d1.loc, e.ename,e.mgr?
from dept d1, emp e?
where d1.deptno = e.deptno;
<--寫法2 >
select d1.dname , d1.loc, e.ename,e.mgr?
from dept d1
inner join emp e?
on d1.deptno = e.deptno;

<--左外鏈接>
select d1.dname , d1.loc, e.ename,e.mgr?
from dept d1
left join emp e?
on d1.deptno = e.deptno;
<--右外鏈接>
select d1.dname , d1.loc, e.ename,e.mgr?
from dept d1
right join emp e?
on d1.deptno = e.deptno;

<--聯合查詢>
<--聯合查詢>
<--相同和合并>
select*from emp
union select * from emp;
<--直接相加>
select*from emp;
union all select *from emp2;

需求：按照部門分組后 每個部門掙錢最多的那個人
select ename,sal,deptno from emp where sal = (select max(sal)from emp group by deptno); 錯誤（單行 子查詢 返回 多個值）
select ename,sal,deptno from emp where sal in (select max(sal)from emp) group by deptno;錯誤 （返回的是 符合3個值（3個部門最大工資）之一的所有可能 那么很有可能別的部門不是最大值的是別的部門的最大值也會選出來 結果就不對了！）

<--錯誤示范2個>
select ename,sal,deptno from emp where sal=(select max(sal)from emp) group by deptno;
select ename,sal,deptno from emp where sal in (select max(sal)from emp) group by deptno;

<--正確>
<--正確 每個部門掙錢最多的那個人>
1.(select max(sal)max_sal,deptno from emp group by deptno)t 命名這倆表是t join是連接
2.on (emp.sal=t.max_sal and emp.deptno = t.deptno); on是限制條件

select ename,sal from emp

join(select max(sal)max_sal,deptno from emp group by deptno)t

on (emp.sal=t.max_sal and emp.deptno = t.deptno);

<-- 需求每個部門平均薪水的等級（表連接 1先寫出每個部門的平均薪水等級 2 和薪水等級那個表做連接）>

select deptno,avg_sal,grade from
(select deptno,avg(sal)avg_sal from emp group by deptno)t
join salgrade s
on (t.avg_sal between s.losal and s.hisal);

<--員工名字和經理人名字>
select empno,ename,mgr from emp;
select empno,ename,mgr from emp;
<-- 自連接 自身和自身鏈接 一個表看成2個一樣表起2個別名做鏈接>
select e1.ename ,e2.ename from emp e1 ,emp e2 where e1.mgr=e2.empno;
<--92年老版本過濾篩選條件where里面寫鏈接表的條件和過濾條件的寫法，99年將連接條件和過濾條件分開寫>
select ename ,dname,grade from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal and
job<>'clerk';
<--過濾和鏈接條件 99年的新寫法 推薦這個寫！>
<--等值鏈接>
<--cross join 交叉連接>
select ename ,dname from emp cross join dept;

<--92語法>
select ename,dname from emp,dept where emp.deptno =dept.deptno;
<--99年語法>
select ename,dname from emp join dept on (emp.deptno=dept.deptno);

<--非等值鏈接>
select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

<--3張表做鏈接>
select ename ,dname,grade from emp e
join dept d on (e.deptno=d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal)
where ename not like '_A%';

<--自連接 用99新語法>
select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr=e2.empno);
<--外鏈接 左外鏈接和右外鏈接 全外鏈接>
select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr=e2.empno);
select e1.ename,e2.ename from emp e1 right join emp e2 on (e1.mgr=e2.empno);

select ename , dname from emp e
join dept d
on(e.deptno = d.deptno);

select ename , dname from emp e
right outer join dept d
on(e.deptno = d.deptno);
<--全外鏈接>
select ename , dname from emp e
full join dept d
on(e.deptno=d.deptno);

<--每個部門平均薪水等級>
select deptno,avg_sal,grade from
(select deptno,avg(sal)avg_sal from emp group by deptno)t
join salgrade s1
on (t.avg_sal between s1.losal and s1.hisal);

<--每個人的薪水等級>
select deptno,ename,grade from emp
join salgrade s
on (emp.sal between s.losal and s.hisal);
<--每個部門（每個員工平均薪水等級的）平均薪水等級>
select deptno, avg(grade)from
(select deptno,ename,grade from emp
join salgrade s
on (emp.sal between s.losal and s.hisal))t
group by deptno;
<-- 那些雇員是經理？>
select ename from emp where empno in(select mgr from emp);
select ename from emp where empno in(select distinct mgr from emp);

select stuno num,stuage age from stu;

?

子查詢
select deptno from emp where ename ='SCOTT';

in慎用 尤其結果大的
select * from emp where deptno in('10','20');
select * from emp where deptno not in('10','20');

select * from emp where( deptno='10' or deptno= '20') and sal>2000;

e1是emp的取名
select * from emp e1 where sal> (select avg(sal)from emp e2 where e1.deptno=e2.deptno);

select dname from dept where deptno where =(deptno from emp where sal>3000);

select dname from dept d where exists (select * from emp e where d.deptno = e.deptno and sal>3000);

<--不相關子查詢>
select dname from dept where deptno =(select deptno from emp where sal>3000);
select dname from dept where deptno = (select deptno from emp where ename ='SMITH');

?

三，騷操作
select stuno "學號“stuage 年齡 from stu;
拼接字符
select 'test'||to_char(sysdate,'yyyy-mm-dd')from dual;
select 's'|| stuno "學 號“stuage 年齡 from stu;

select '測試'，'s'|| stuno "學 號“stuage 年齡 from stu;

select to_char(sysdate,'yyyy-mm-dd')from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日 "hh24:mi:ss')from dual;

?

四，單行函數
select to_number('234')from dual;
select to_date('2017-09-09','yyyy-mm-dd')from dual;

select distinct stuno from stu;

?

五，聚合函數

5個聚合函數
所以數字類型的數字相加用和這個
（1）計數
select count(*)from emp;
（2）求和
select sum(empno) from emp;

（3）最小
select min(sal)from emp;
（4）最大
select max(sal)from emp;
（5）平均
select avg(sal)from emp;

?

分析函數（略）

截取字符函數
select substr('sdsfds',2) from dual;

select substr('sdsfds',2,4) from dual;
select substr('20170909120910',1,8) from dual;

NVL函數
如果是空 就打印后邊的4
select nvl(null,4)from dual;
打印5
select nvl(5,4)from dual;

select nvl(comm,0)*1.1 from emp; 工作經驗
上面為啥這么寫呢？因為如果comm是null的話 *1.1不合適 所以 我們這么寫
如果是null就改成0

判斷字符是否一樣 1一樣 0不一樣
select decode('男','女',1,0)from dual; 0
select decode('男','男',1,0)from dual; 1
select decode('男','女',1,'男'，0，2)from dual;
select decode('男','女',1,'男'，0，2)from dual;
select decode('男','女',1,''，0，2)from dual;

?

<分頁查詢>
偽列
<--分頁>
<--偽列>
<rowid rownum>
;
<--先抓取5個 然后排序>
select sal from emp where rownum<6
order by sal desc;
<--收入前五>
select *from(select * from emp order by sal desc) where rownum<6;
select *from(select * from emp order by sal desc) where rownum<16;

<--先找到需要排序的表排序 2然后 對這個表進行rownum固化 3.對固化后的表加限定條件>
select * from (
select e.*,rownum rn from
(select * from emp order by sal desc)
e
)
where rn=5;

數據遷移
1、找到數據文件 執行select*from 表； 導出sql文件:點 綠色的按鈕 導出查詢結果 得到sql文件
2.建表 可以COPY→ 查看 sql(v) 里面有多余的部分 不要 只要建表操作
3.導入sql 新建命令窗口 @ 找到要導入的sql 文件

?

oracle 數據庫編程語言 pl/sql 過程化語言和結構化語言的語言
存儲過程

declare聲明 變量 常量賦值 c_rate_iner number(7,2) comstant ：=1.10; 變量 select ename,sal*c_rate_iner into v_name,v_rate from emp where empno='7788';
begin sql語句
exception 異常
end；

loop循環
DBMS_OUTPUT.PUT_LINE(''+變量)

if 條件 then
goto ming

else null；

end if

end loop；
<<ming>>

?

--變量類型 %type屬性 他的意思可以根據別的表的數據類型定義自己的數據類型，
--表的某個字段數據類型 綁定 自己的數據類型，表的某個字段類型變成什么，他就跟著變成什么 >
例子
declare
--v_num 的數據類型是emp表下面的empno這個字段的數據類型
v_num emp.empno%type :=5566;
begin
dbms_output.put_line(v_num);
end;

--table數據類型 相當于java里面的數組 自定義類型 先聲明一種類型 再用這個類型 聲明一個變量
declare
--標準寫法 type表明 我要自定義一個類型 type_table_emp_empno是類型名字 is table of 是一張表
--emp.empno%type表的類型是emp這個表的empno這個字段的類型（綁定） index by索引下標 binary_integer索引下標的類型
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
--v_empnos 類型名 和定義好的數組類型
v_empnos type_table_emp_empno;
begin
v_empnos(0):=7369;
v_empnos(1):=7370;
v_empnos(2):=7860;
v_empnos(-1):=9999;
dbms_output.put_line(v_empnos(-1));
end;

--record類型 相當于java里面的類
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);

v_temp type_record_dept;

begin
v_temp.deptno:=50;
v_temp.dname:='aaaa';
v_temp.loc:='beijing';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;

--

使用 %rowtype屬性 聲明 record變量 （解決了原來的表dept改變了 record會跟著編號 解決了維護麻煩的問題）
declare
v_temp dept%rowtype;
begin
v_temp.deptno:=50;
v_temp.dname:='aaaa';
v_temp.loc:='beijing';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;