1.解鎖用戶
請輸入用戶名：sys
輸入口令：sys as sysdba
alter user scott accout unlock;
用戶已更改.
SQL> commit;
SQL> conn scott/tiger
更改scott口令
新口令：tiger
重新鍵入新口令：tiger
增加權限 用sys登陸完 grant create table, create view to scott; conn scott/root
2.查看表結構
desc tbname
3.從表中查詢數據
select * from tbname;
4.字段運算後再查詢
select ename, sal*12 from emp;
5.純數字運算查詢
select 2*3 from dual; dual是oracle無意義的表
6.查詢當前系統的日期
select sysdate from dual;
7.雙引號保持原來的格式
select ename, sak*12 "anuual sal" from emp;
8.查詢數字時，把為空值的換為0，oracle中空值運算結果都為null
select ename, sal*12 + nvl(comm, 0) from; nvl(,) 如果字段comm為空值時，用0代替
select ename, sal, comm from emp where comm is null;(is not null)(選出comm為空的數據)
9.字符串連接（把兩個字段查詢出來的數據作為一條字符串輸出）兩個單引號代替一個
select ename||sal from emp; select ename || 'ds''fsdf' from emp;
10.去掉重復的值(也會去掉多個字段組合重復的值)
select distinct ziduan from tbname;
11.條件 where
select * from tbname where ziduan > 'CBA' ; =, <, 不等於號<>，
12.條件 between and (包含800和1500)
select ename, sal from emp where sal between 800 and 1500;
select ename, sal from emp where sal >= 800 and sal <= 1500;
13.條件 in (誰的薪水值=800或1500或2000)
select ename, sal comm from where sal in (800, 1500, 2000); 也可以not in ('df', 'dfsd')
14.條件 or
select ename, sal from emp where deptno = 10 or sal > 1000;
15. 模糊查詢 %零個或多個，下橫線_代表一個
select ename from emp where ename like '%All%';
16. 轉義字符 \ 可以制定轉義字符 escape
select ename from emp where ename like '%\%%'; like '%$%%' escape '$';
17.排序 order by 默認升序asc
select empno, ename from emp order by deptno asc, ename desc;先按deptno,再按ename
18.函數 轉化為小寫lower()
select lower(ename) from emp;
19.函數 截子串substr(ename,2,3) 從字符串ename中第2個開始截，一個截3個字符
select substr(ename,2,3) from emp;
20.函數 把數字轉化為相應的字母,相反 ascii('A')
select chr(65) from dual; a
21.函數 四捨五入 round(23.652)
select round(23.652) from dual; 24
select round(23.652, 2) from dual; 23.65 2代表捨到小數點後2位
select round(23.652，-1) from dual; 20 可以是負數
22.函數 把數字或字母或日期轉化為特定的格式 to_char(sal,'$99,999.9999'), $換成L,顯示￥
select to_char(sal, '$99,999.9999') from emp; 百千等位沒有的不顯示
select to_char(sal, '$00,000.0000') from emp; 沒有的位用0補齊
select to_char(hircdate, 'YYY-MM-DD HH24:MI:SS') from emp; 轉化為特定的日期，24位24進制
23.函數 把特定的字符轉化為日期 to_date('', '')
select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');
24.函數 把特定的字符轉化為數字 to_number('$1,250.00', '$9，999.99')
select sal from emp where sal > to_number('$1,250.00', '$9，999.99');
25.組函數 取最大max()，最小min()，平均avg(),函數可以組合使用
select to_char(avg(sal), '99999999.99') from emp;
26.組函數 總和 sum()
select sum(sal) from emp;
27.組函數 求出總共多少條數據 count(*)，count(ename), 凡是不是空值的字段一共有幾個
select count(*) from emp;
select count(distinct ziduan) from tbname; distinct去掉重復
28.函數 分組查詢 group by
select avg(sal), deptno from emp group by deptno;
select deptno, job, max(sal) from emp group by deptno, job;
29.分組查詢，多條輸入，一條輸出
查詢出薪水最高的人的名字（可能不止一個人）
select ename from emp where sal = (select max(sal) from emp);
查詢出每個組中薪水最高的人的名字
select ename from emp where sal in (select max(sal) from emp group by deptno);
group by使用規則，要查詢的字段如果沒出現在組函數中則必須出現在group by中，否則出錯
30.取出按部門編號分組後每個部門的平均薪水
select avg(sal) from emp group by deptno;
31.where語句是處理單條語句，有此語句先執行where語句再進行分組（有group by的話）
having 用來對分組進行限制 此處代替where
查詢出部門平均薪水大於2000的平均薪水和部門編號
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;
32.完整的select語句，按此順序執行
select * from emp
where sal > 1000
group by deptno
having avg(sal) > 2000
order by
33.查詢出薪水大於平均薪水人的名字
select ename, sal from emp where sal > (select avg(sal) from emp);
34.查出按部門分組後，每個部門中薪水最高的人的名字，部門編號 join 表連接，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);
join表連接，on後面是連接條件，此語句等於如下語句
select ename, sal from emp where (deptno,sal) in (select deptno, max(sal) from emp group by deptno);
35.把自己的名字和他經理人的名字取出來(自鏈接)
select t1.ename, t2.ename from emp t1, emp t2 where t1.mgr = t2.empno;
36.SQL1999 cross join 交叉連接
select ename,dname from emp, dept; 1992版的
select ename,dname from emp cross join dept;1999版的
36.SQL1999等值連接 老版用where難分辨哪個是過濾條件哪個是表連接條件，用新版的on，後可加where過濾
select ename,dname from emp,dept where emp.deptno = dept.deptno; 1992版
select ename,dname from emp join dept on (emp.deptno = dept.deptno); 1999版
select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
37.sql1999三表鏈接加where過濾
select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno) join salgrade s on (e.sal between s.losal aand s.hisal) where ename not like '_A%';
38.外鏈接 左外鏈接 left join 會把左邊這張表多余的數據顯示出來（和另外一張表對應不上的數據）同理right join右外連接，full join 全外連接
select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
39.部門平均薪水的的等級
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);
40.部門平均的薪水等級
select deptno, avg(grade) from(select deptno, sal, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) group by deptno;
41.雇員中哪些人是經理人
select ename from emp where empno in (select mgr from emp);
42.不用組函數求最高薪水
select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 left join emp e2 on (e1.sal < e2.sal));
43.平均薪水最高的部門編號與名稱
select dname from dept where deptno = (
select deptno from (select deptno avg(sal) avg_sal from emp group by deptno) where avg_sal = (sel ect max(avg_sal) from (select deptno avg(sal) avg_sal from emp group by deptno)));
44.平均薪水的等級最低的部門的部門名稱
select dname from dept where deptno = (
select deptno 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) where grade = (
select min(grade) from (select grade, deptno from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal betwe en s.losal and s.hisal))));
45.創建視圖 create view v$name as 重復使用的語句. 視圖就是一個子查詢，就是一張表
create view v$name as select * from emp; 以v$開頭
select * from v$name;
46.插入語句
insert into tbname values (50, 'ganbe', 'bj');
insert into tbanme (zd1, zd2) values (50, 60);
insert into dept2 select * from dept;
47.備份表
create table emp2 as select * from emp;
48.偽字段 rownum 默認從第一行往後排列序號1，2，3等，必須< = 號；
select empno, ename from emp where rownum <=5;
select roenum r, ename from emp where r > 10; 這樣可 〉 於號；
49.薪水最高的前5人
select ename, sal from (select ename, sal from emp order by sal desc) where rownum <= 5;
50.部門為10的員工薪水翻一倍
update emp set sal = sal*2, name ename = ename||"-" where deptno = 10;
51.還原沒提交的修改 rollback
rollback;
52.提交 commit 遇到ddl語句事物自動執行 commit，正常斷開連接時自動提交
commit;
53.Oracle有事物回滾機制
事物 transaction
54.約束 自定義名字 非空約束
create table stu (id number(6), name varchar2(20) constraint stu_name_ nn not null);
55.唯一 約束 unique 可以插空置
標級約束 幾個字段的組合唯一約束 不在字段後面，另起一行
constraint syu_name)email_uni unique(email,name)
56.主鍵約束 非空唯一 primary key，可以組合 主鍵
id number(4),
另起一行的話 primary key(id)
57.外鍵約束 牽扯到兩張表、兩個字段references(參考),被參考的字段必須是主鍵
表級別：constraint ysname foreign key (benziduan) references ckbiao(waizd)
58.修改現有表的表結構
alter table tbname add(ziduan varchar2(100)) 添加
alter table tbname drop(ziduan) 刪除
alter table tbname modify(ziduan varchar2(100)) 修改 字段類型容量不能改小
59.去掉約束
alter table tbname drop constraint yueshuname;
60.添加約束
alter table tbname add constraint yueshuname foreign key (class) references class (id);
61.刪除表
delete from tbname;
62.oracle默認的一個表user_tables 裝的當前用戶下有多少表，（數字字典表）
select table_name from user_tables
63.oracle有多少個數字字典表都放在表 dictionary 中
select table_name from dictionary;
64.索引 index
create index syname on tbname(ziduan1,ziduan2);
65.刪除索引
drop index syname;
66.序列 oracle獨特的 自動遞增
create sequence sename;
drop sequence sename;刪粗序列
select sename.nextval from dual;查詢的結果會遞增
67.三范式
不存在冗余數據
第一范式要求：要有主鍵，列不可分
第二范式要求：不能存在部分依賴 （分割為n張表）
第三范式要求：屬性不能依賴其它屬性
68.PL_SQL 語言 斜槓/執行
set serveroutput on;
begin
dbms_output.put_line('HelloWorld');
end;
69.PL_SQL 語言 declare 聲明變量 以v_開頭
declare
v_name varchar(20);
begin
v_name := 'myname'; := 賦值符號
dbms_output.put_line(v_name);
end;
/
69.PL_SQL 語言 異常
declare
v_num number := 0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error');
end;
70.PL_SQL 語言 常用變量的類型
binary_integer: 整數，主要用來計數而不是用來表示字段類型
number：數字類型
char: 定長字符串
varchar2: 變長字符串
date: 日期
long: 長字符串，最長2GB
boolean: 布爾類型，可以取值為 true、false和null值，默認null
71.PL_SQL 語言 constant
相當於java中的 fianl；
72.PL_SQL 語言 -- 單行注釋
73.PL_SQL 語言 %type 屬性
v_empno emp.empno%type; 變量v_empno的類型隨表emp中字段empno的類型變化而變化
SpringMVC+mybatis HTML5 全新高大尚後台框架_集成代碼生成器
74.Table變量類型 相當於java中的數組，type表示定義了一種新的數據類型
declare
type type_table_emp_emono is table of emp.empno%type index by binary_integrt;
v_empnos type_table_emp_empno;
begin
v_empnos(0) := 7369;
v_empnos(2) := 7339;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
75.Record變量類型 相當於java中的類
declare
type type_recors_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 := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
76.使用%rowtype聲明record變量
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
77.PL_SQL語句的運用,select語句中必須有關鍵字into,並且只有一條數據
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename.sal into v_ename.v_sal from emp where empno = 7369;
dbms_output.put_line(v_ename || ' ' || v_sal);
end;
同上
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno = 7369;
dbms_output.put_line(v_emp.ename || ' ' || v_emp.sal);
end;
87.PL_SQL語句的運用 insert
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type := 'aaaa';
v_loc dept.loc%type := 'bj';
begin
insert into dept2 values (v_deptno, v_dname, v_loc);
commit;
end;
88.PL_SQL語句的運用
dbms_output.put_line(sql%rowcount || '條記錄被影響')
89,PL_SQL語句的運 ddl語句在PL_SQL語句中前加 excute immediate
begin
execute immediate 'create table tbname (nnn varchar2(20) default ''aaa'')';
90.PL_SQL語句 if語句 取出7369的薪水，如果<1200,則輸出'low',如果<2000則輸出'middle',否則輸出'high'
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if (v_sal < 1200) then
dbms_output.put_line('low');
elsif (v_sal < 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
91.PL_SQL語句 循環語句
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i)
i := i + 1;
exit when (i >= 11);
end loop;
end;
declare
j binary_integer := 1;
begin
where j < 11 loop
dbms_output.put_line(j);
J := J + 1;
end loop;
end;
begin
for k in 1..10 loop
dbms_output.putline(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
92.PL_SQL語句 異常
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多記錄了');
when others then
dbms_output.put_line('error');
end;
no_data_found 沒找到數據
93.PL_SQL語句 游標（指針） cursor
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c; --開始執行select語句
fetch c into v_emp; --fetch提取游標數據
dbms_output.put_line(v_emp.eename);
close c;
end;
循環
loop
fetch c into v_emp;
exit when (c%notfound);
........;
end loop;
for循環
declare
cusor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
94.PL_SQL語句 帶參數的游標
declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
is
select ename, sal from emp where deptno = v_deprno and job = v_job;
--v_temp c%rowtype;
begin
for v_temp in c(30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop
end;
95.PL_SQL語句 可更新的游標
declare
cursor c
is
select * from emp2 for update;
--v_temp c%type;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c; --current當前的
elsif(v_temp.sal = 5000) then
delect from emp2 where current of c;
end if;
end loop;
commit;
end;
96.存儲過程procedure
創建存儲過程
create or replace procedure p
is
cursor c
is
select * from emp2 for update;
--v_temp c%type;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c; --current當前的
elsif(v_temp.sal = 5000) then
delect from emp2 where current of c;
end if;
end loop;
commit;
end;
執行此存儲過程
exec p;
97.帶參數的存儲過程 默認in
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)
is
begin
if(v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + 1;
end;
調用
declare
v_a number := 3;
v_b number := 4;
v_ret number;
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.outline(v_ret);
dbms_output.putline(v_temp);
end; 答案 4，6
98.函數 調用方式和系統函數調用方式一樣
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal < 2000) then
return 0.10;
elsif(v_sal < 2750) then
return 0.15;
else if;
end;
99.觸發器 必須在表上，在什麼時間，等 for each row 每處理一行觸發一次。刪除觸發器 drop trigger trig;
create or replace trigger trig
after insert or delete or update on emp2 for each row
begin
if inserting then
insert into emp2_log values (USER, 'insert', sysdate);
elsif updating then
insert into emp2_log values (USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values (USER, 'delete', sysdate);
end if;
end;
100. update 執行會有前後兩個狀態 NEW, OLD
create or replace trigger trig
after update on dept
for each row
begin
update emp set deptno =: NEW.deptno where deptno =: OLD.deptno;
end;
