oracle的sql語句
Oracle WDP 全稱為Oracle Workforce Development Program，是Oracle (甲骨文)公司專門面向?qū)W生、個(gè)人、在職人員等群體開設(shè)的職業(yè)發(fā)展力課程。下面是小編整理的關(guān)于oracle的sql語句，歡迎大家參考!
首先，以超級(jí)管理員的身份登錄oracle
sqlplus sys/bjsxt as sysdba
--然后，解除對(duì)scott用戶的鎖
alter user scott account unlock;
--那么這個(gè)用戶名就能使用了。
--(默認(rèn)全局?jǐn)?shù)據(jù)庫(kù)名orcl)
1、select ename, sal * 12 from emp; --計(jì)算年薪
2、select 2*3 from dual; --計(jì)算一個(gè)比較純的數(shù)據(jù)用dual表
3、select sysdate from dual; --查看當(dāng)前的系統(tǒng)時(shí)間
4、select ename, sal*12 anuual_sal from emp; --給搜索字段更改名稱(雙引號(hào) keepFormat 別名有特殊字符，要加雙引號(hào))。
5、--任何含有空值的數(shù)學(xué)表達(dá)式，最后的計(jì)算結(jié)果都是空值。
6、select ename||sal from emp; --(將sal的查詢結(jié)果轉(zhuǎn)化為字符串，與ename連接到一起，相當(dāng)于Java中的字符串連接)
7、select ename||'afasjkj' from emp; --字符串的連接
8、select distinct deptno from emp; --消除deptno字段重復(fù)的值
9、select distinct deptno , job from emp; --將與這兩個(gè)字段都重復(fù)的值去掉
10、select * from emp where deptno=10; --(條件過濾查詢)
11、select * from emp where empno > 10; --大于 過濾判斷
12、select * from emp where empno <> 10 --不等于 過濾判斷
13、select * from emp where ename > 'cba'; --字符串比較，實(shí)際上比較的是每個(gè)字符的AscII值，與在Java中字符串的比較是一樣的
14、select ename, sal from emp where sal between 800 and 1500; --(between and過濾，包含800 1500)
15、select ename, sal, comm from emp where comm is null; --(選擇comm字段為null的數(shù)據(jù))
16、select ename, sal, comm from emp where comm is not null; --(選擇comm字段不為null的數(shù)據(jù))
17、select ename, sal, comm from emp where sal in (800, 1500,2000); --(in 表范圍)
18、select ename, sal, hiredate from emp where hiredate > '02-2月-1981'; --(只能按照規(guī)定的格式寫)
19、select ename, sal from emp where deptno =10 or sal >1000;
20、select ename, sal from emp where deptno =10 and sal >1000;
21、select ename, sal, comm from emp where sal not in (800, 1500,2000); --(可以對(duì)in指定的條件進(jìn)行取反)
22、select ename from emp where ename like '%ALL%'; --(模糊查詢)
23、select ename from emp where ename like '_A%'; --(取第二個(gè)字母是A的所有字段)
24、select ename from emp where ename like '%/%%'; --(用轉(zhuǎn)義字符/查詢字段中本身就帶%字段的)
25、select ename from emp where ename like '%$%%' escape '$'; --(用轉(zhuǎn)義字符/查詢字段中本身就帶%字段的)
26、select * from dept order by deptno desc; (使用order by desc字段 對(duì)數(shù)據(jù)進(jìn)行降序排列 默認(rèn)為升序asc);
27、select * from dept where deptno <>10 order by deptno asc; --(我們可以將過濾以后的數(shù)據(jù)再進(jìn)行排序)
28、select ename, sal, deptno from emp order by deptno asc, ename desc; --(按照多個(gè)字段排序 首先按照deptno升序排列,當(dāng)detpno相同時(shí)，內(nèi)部再按照ename的降序排列)
29、select lower(ename) from emp; --(函數(shù)lower() 將ename搜索出來后全部轉(zhuǎn)化為小寫);
30、select ename from emp where lower(ename) like '_a%'; --(首先將所搜索字段轉(zhuǎn)化為小寫，然后判斷第二個(gè)字母是不是a)
31、select substr(ename, 2, 3) from emp; --(使用函數(shù)substr() 將搜素出來的ename字段從第二個(gè)字母開始截，一共截3個(gè)字符)
32、select chr(65) from dual; --(函數(shù)chr() 將數(shù)字轉(zhuǎn)化為AscII中相對(duì)應(yīng)的字符)
33、select ascii('A') from dual; --(函數(shù)ascii()與32中的chr()函數(shù)是相反的 將相應(yīng)的字符轉(zhuǎn)化為相應(yīng)的Ascii編碼) )
34、select round(23.232) from dual; --(函數(shù)round() 進(jìn)行四舍五入操作)
35、select round(23.232, 2) from dual; --(四舍五入后保留的小數(shù)位數(shù) 0 個(gè)位 -1 十位)
36、select to_char(sal, '$99,999.9999')from emp; --(加$符號(hào)加入千位分隔符，保留四位小數(shù)，沒有的補(bǔ)零)
37、select to_char(sal, 'L99,999.9999')from emp; --(L 將貨幣轉(zhuǎn)化為本地幣種此處將顯示￥人民幣)
38、select to_char(sal, 'L00,000.0000')from emp; --(補(bǔ)零位數(shù)不一樣，可到數(shù)據(jù)庫(kù)執(zhí)行查看)
39、select to_char(hiredate, 'yyyy-MM-DD HH:MI:SS') from emp; --(改變?nèi)掌谀J(rèn)的顯示格式)
40、select to_char(sysdate, 'yyyy-MM-DD HH:MI:SS') from dual; --(用12小時(shí)制顯示當(dāng)前的系統(tǒng)時(shí)間)
41、select to_char(sysdate, 'yyyy-MM-DD HH24:MI:SS') from dual; --(用24小時(shí)制顯示當(dāng)前的系統(tǒng)時(shí)間)
42、select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:24:45','YYYY-MM-DD HH24:MI:SS'); --(函數(shù)to-date 查詢公司在所給時(shí)間以后入職的人員)
43、select sal from emp where sal > to_number('$1,250.00', '$9,999.99'); --(函數(shù)to_number()求出這種薪水里帶有特殊符號(hào)的)
44、select ename, sal*12 + nvl(comm,0) from emp; --(函數(shù)nvl() 求出員工的"年薪 + 提成(或獎(jiǎng)金)問題")
45、select max(sal) from emp; -- (函數(shù)max() 求出emp表中sal字段的最大值)
46、select min(sal) from emp; -- (函數(shù)max() 求出emp表中sal字段的最小值)
47、select avg(sal) from emp; --(avg()求平均薪水);
48、select to_char(avg(sal), '999999.99') from emp; --(將求出來的平均薪水只保留2位小數(shù))
49、select round(avg(sal), 2) from emp; --(將平均薪水四舍五入到小數(shù)點(diǎn)后2位)
50、select sum(sal) from emp; --(求出每個(gè)月要支付的總薪水)
------------------------/組函數(shù)(共5個(gè))：將多個(gè)條件組合到一起最后只產(chǎn)生一個(gè)數(shù)據(jù)------min() max() avg() sum() count()----------------------------/
51、select count(*) from emp; --求出表中一共有多少條記錄
52、select count(*) from emp where deptno=10; --再要求一共有多少條記錄的時(shí)候，還可以在后面跟上限定條件
53、select count(distinct deptno) from emp; --統(tǒng)計(jì)部門編號(hào)前提是去掉重復(fù)的值
------------------------聚組函數(shù)group by() --------------------------------------
54、select deptno, avg(sal) from emp group by deptno; --按照deptno分組，查看每個(gè)部門的平均工資
55、select max(sal) from emp group by deptno, job; --分組的時(shí)候，還可以按照多個(gè)字段進(jìn)行分組，兩個(gè)字段不相同的為一組
56、select ename from emp where sal = (select max(sal) from emp); --求出
57、select deptno, max(sal) from emp group by deptno; --搜素這個(gè)部門中薪水最高的的值
--------------------------------------------------having函數(shù)對(duì)于group by函數(shù)的過濾 不能用where--------------------------------------
58、select deptno, avg(sal) from emp group by deptno having avg(sal) >2000; (order by )--求出每個(gè)部門的平均值，并且要 > 2000
59、select avg(sal) from emp where sal >1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;--求出sal>1200的平均值按照deptno分組，平均值要>1500最后按照sal的倒序排列
60、select ename,sal from emp where sal > (select avg(sal) from emp); --求那些人的薪水是在平均薪水之上的。
61、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); --查詢每個(gè)部門中工資最高的那個(gè)人
------------------------------/等值連接--------------------------------------
62、select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; --自連接，把一張表當(dāng)成兩張表來用
63、select ename, dname from emp, dept; --92年語法 兩張表的連接 笛卡爾積。
64、select ename, dname from emp cross join dept; --99年語法 兩張表的連接用cross join
65、select ename, dname from emp, dept where emp.deptno = dept.deptno; -- 92年語法 表連接 + 條件連接
66、select ename, dname from emp join dept on(emp.deptno = dept.deptno); -- 新語法
67、select ename,dname from emp join dept using(deptno); --與66題的寫法是一樣的，但是不推薦使用using ： 假設(shè)條件太多
--------------------------------------/非等值連接------------------------------------------/
68、select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal); --兩張表的連接 此種寫法比用where更清晰
69、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%'; --三張表的連接
70、select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno); --自連接第二種寫法，同62
71、select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno); --左外連接 把左邊沒有滿足條件的數(shù)據(jù)也取出來
72、select ename, dname from emp e right join dept d on(e.deptno = d.deptno); --右外連接
73、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);--求每個(gè)部門平均薪水的等級(jí)
74、select ename from emp where empno in (select mgr from emp); -- 在表中搜索那些人是經(jīng)理
75、select sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal)); -- 面試題 不用組函數(shù)max()求薪水的最大值
76、select deptno, max_sal from
(select avg(sal) max_sal,deptno from emp group by deptno)
where max_sal =
(select max(max_sal) from
(select avg(sal) max_sal,deptno from emp group by deptno)
);--求平均薪水最高的部門名稱和編號(hào)。
77、select t1.deptno, grade, avg_sal from
(select deptno, grade, avg_sal 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)
) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from
(select deptno, grade, avg_sal 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)
)
)--求平均薪水等級(jí)最低的部門的名稱 哈哈 確實(shí)比較麻煩
78、create view v$_dept_avg_sal_info as
select deptno, grade, avg_sal 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);
--視圖的創(chuàng)建，一般以v$開頭，但不是固定的
79、select t1.deptno, grade, avg_sal from v$_dept_avg_sal_info t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from
v$_dept_avg_sal_info t1
)
)--求平均薪水等級(jí)最低的部門的名稱 用視圖，能簡(jiǎn)單一些，相當(dāng)于Java中方法的封裝
80、---創(chuàng)建視圖出現(xiàn)權(quán)限不足時(shí)候的解決辦法：
conn sys/admin as sysdba;
--顯示：連接成功 Connected
grant create table, create view to scott;
-- 顯示: 授權(quán)成功 Grant succeeded
81、-------求比普通員工最高薪水還要高的經(jīng)理人的名稱 -------
select ename, sal from emp where empno in
(select distinct mgr from emp where mgr is not null)
and sal >
(
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null)
)
82、---面試題：比較效率
select * from emp where deptno = 10 and ename like '%A%';--好，將過濾力度大的放在前面
select * from emp where ename like '%A%' and deptno = 10;
83、-----表的備份
create table dept2 as select * from dept;
84、-----插入數(shù)據(jù)
insert into dept2 values(50,'game','beijing');
----只對(duì)某個(gè)字段插入數(shù)據(jù)
insert into dept2(deptno,dname) values(60,'game2');
85、-----將一個(gè)表中的數(shù)據(jù)完全插入另一個(gè)表中(表結(jié)構(gòu)必須一樣)
insert into dept2 select * from dept;
86、-----求前五名員工的編號(hào)和名稱(使用虛字段rownum 只能使用 < 或 = 要使用 > 必須使用子查詢)
select empno,ename from emp where rownum <= 5;
86、----求10名雇員以后的雇員名稱--------
select ename from (select rownum r,ename from emp) where r > 10;
87、----求薪水最高的前5個(gè)人的薪水和名字---------
select ename, sal from (select ename, sal from emp order by sal desc) where rownum <=5;
88、----求按薪水倒序排列后的第6名到第10名的員工的名字和薪水--------
select ename, sal from
(select ename, sal, rownum r from
(select ename, sal from emp order by sal desc)
)
where r>=6 and r<=10
89、----------------創(chuàng)建新用戶---------------
1、backup scott--備份
exp--導(dǎo)出
2、create user
create user guohailong identified(認(rèn)證) by guohailong default tablespace users quota(配額) 10M on users
grant create session(給它登錄到服務(wù)器的權(quán)限),create table, create view to guohailong
3、import data
imp
90、-----------事務(wù)回退語句--------
rollback;
91、-----------事務(wù)確認(rèn)語句--------
commit;--此時(shí)再執(zhí)行rollback無效
92、--當(dāng)正常斷開連接的時(shí)候例如exit,事務(wù)自動(dòng)提交。 當(dāng)非正常斷開連接，例如直接關(guān)閉dos窗口或關(guān)機(jī)，事務(wù)自動(dòng)提交
93、/*有3個(gè)表S，C，SC
S(SNO，SNAME)代表(學(xué)號(hào)，姓名)
C(CNO，CNAME，CTEACHER)代表(課號(hào)，課名，教師)
SC(SNO，CNO，SCGRADE)代表(學(xué)號(hào)，課號(hào)成績(jī))
問題：
1，找出沒選過“黎明”老師的所有學(xué)生姓名。
2，列出2門以上(含2門)不及格學(xué)生姓名及平均成績(jī)。
3，即學(xué)過1號(hào)課程有學(xué)過2號(hào)課所有學(xué)生的姓名。
*/答案：
1、
select sname from s join sc on(s.sno = sc.sno) join c on (sc.cno = c.cno) where cteacher <> '黎明';
2、
select sname where sno in (select sno from sc where scgrade < 60 group by sno having count(*) >=2);
3、
select sname from s where sno in (select sno, from sc where cno=1 and cno in
(select distinct sno from sc where cno = 2);
)
94、--------------創(chuàng)建表--------------
create table stu
(
id number(6),
name varchar2(20) constraint stu_name_mm not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50) unique
);
95、--------------給name字段加入 非空 約束，并給約束一個(gè)名字，若不取，系統(tǒng)默認(rèn)取一個(gè)-------------
create table stu
(
id number(6),
name varchar2(20) constraint stu_name_mm not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50)
);
96、--------------給nameemail字段加入 唯一 約束 兩個(gè) null值 不為重復(fù)-------------
create table stu
(
id number(6),
name varchar2(20) constraint stu_name_mm not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50) unique
);
97、--------------兩個(gè)字段的組合不能重復(fù) 約束：表級(jí)約束-------------
create table stu
(
id number(6),
name varchar2(20) constraint stu_name_mm not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50)，
constraint stu_name_email_uni unique(email, name)
);
98、--------------主鍵約束-------------
create table stu
(
id number(6),
name varchar2(20) constraint stu_name_mm not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50)，
constraint stu_id_pk primary key (id),
constraint stu_name_email_uni unique(email, name)
);
99、--------------外鍵約束 被參考字段必須是主鍵 -------------
create table stu
(
id number(6),
name varchar2(20) constraint stu_name_mm not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4) references class(id),
email varchar2(50)，
constraint stu_class_fk foreign key (class) references class(id),
constraint stu_id_pk primary key (id),
constraint stu_name_email_uni unique(email, name)
);
create table class
(
id number(4) primary key,
name varchar2(20) not null
);
100、---------------修改表結(jié)構(gòu)，添加字段------------------
alter table stu add(addr varchar2(29));
101、---------------刪除字段--------------------------
alter table stu drop (addr);
102、---------------修改表字段的長(zhǎng)度------------------
alter table stu modify (addr varchar2(50));--更改后的長(zhǎng)度必須要能容納原先的數(shù)據(jù)
103、----------------刪除約束條件----------------
alter table stu drop constraint 約束名
104、-----------修改表結(jié)構(gòu)添加約束條件---------------
alter table stu add constraint stu_class_fk foreign key (class) references class (id);
105、---------------數(shù)據(jù)字典表----------------
desc dictionary;
--數(shù)據(jù)字典表共有兩個(gè)字段 table_name comments
--table_name主要存放數(shù)據(jù)字典表的名字
--comments主要是對(duì)這張數(shù)據(jù)字典表的描述
105、---------------查看當(dāng)前用戶下面所有的表、視圖、約束-----數(shù)據(jù)字典表user_tables---
select table_name from user_tables;
select view_name from user_views;
select constraint_name from user-constraints;
106、-------------索引------------------
create index idx_stu_email on stu (email);-- 在stu這張表的email字段上建立一個(gè)索引：idx_stu_email
107、---------- 刪除索引 ------------------
drop index index_stu_email;
108、---------查看所有的索引----------------
select index_name from user_indexes;
109、---------創(chuàng)建視圖-------------------
create view v$stu as selesct id,name,age from stu;
視圖的作用: 簡(jiǎn)化查詢 保護(hù)我們的一些私有數(shù)據(jù)，通過視圖也可以用來更新數(shù)據(jù)，但是我們一般不這么用 缺點(diǎn)：要對(duì)視圖進(jìn)行維護(hù)
110、-----------創(chuàng)建序列------------
create sequence seq;--創(chuàng)建序列
select seq.nextval from dual;-- 查看seq序列的下一個(gè)值
drop sequence seq;--刪除序列
111、------------數(shù)據(jù)庫(kù)的三范式--------------
(1)、要有主鍵，列不可分
(2)、不能存在部分依賴：當(dāng)有多個(gè)字段聯(lián)合起來作為主鍵的時(shí)候，不是主鍵的字段不能部分依賴于主鍵中的某個(gè)字段
(3)、不能存在傳遞依賴
==============================================PL/SQL==========================
112、-------------------在客戶端輸出helloworld-------------------------------
set serveroutput on;--默認(rèn)是off，設(shè)成on是讓Oracle可以在客戶端輸出數(shù)據(jù)
113、begin
dbms_output.put_line('helloworld');
end;
/
114、----------------pl/sql變量的賦值與輸出----
declare
v_name varchar2(20);--聲明變量v_name變量的聲明以v_開頭
begin
v_name := 'myname';
dbms_output.put_line(v_name);
end;
/
115、-----------pl/sql對(duì)于異常的處理(除數(shù)為0)-------------
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;
/
116、----------變量的聲明----------
binary_integer:整數(shù)，主要用來計(jì)數(shù)而不是用來表示字段類型 比number效率高
number:數(shù)字類型
char:定長(zhǎng)字符串
varchar2：變長(zhǎng)字符串
date：日期
long：字符串，最長(zhǎng)2GB
boolean：布爾類型，可以取值true，false，null--最好給一初值
117、----------變量的聲明，使用 '%type'屬性
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('Test');
end;
/
--使用%type屬性，可以使變量的聲明根據(jù)表字段的類型自動(dòng)變換，省去了維護(hù)的麻煩，而且%type屬性，可以用于變量身上
118、---------------Table變量類型(table表示的是一個(gè)數(shù)組)-------------------
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table type_table_empno;
begin
v_empnos(0) := 7345;
v_empnos(-1) :=9999;
dbms_output.put_line(v_empnos(-1));
end;
119、-----------------Record變量類型
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
begin
v_temp.deptno:=50;
v_temp.dname:='aaaa';
v_temp.loc:='bj';
dbms_output.put_line(v temp.deptno || ' ' || v temp.dname);
end;
120、-----------使用 %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;
121、--------------sql%count 統(tǒng)計(jì)上一條sql語句更新的記錄條數(shù)
122、--------------sql語句的運(yùn)用
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;
123、 -------- pl/sql語句的應(yīng)用
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7369;
dbms_output_line(v_emp.ename);
end;
124、-------------pl/sql語句的應(yīng)用
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type :='aaa';
v_loc dept.loc%type := 'bj';
begin
insert into dept2 values(v_deptno,v_dname,v_loc);
commit;
end;
125、-----------------ddl語言，數(shù)據(jù)定義語言
begin
execute immediate 'create table T (nnn varchar(30) default ''a'')';
end;
126、------------------if else的運(yùn)用
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if(v_sal < 2000) then
dbms_output.put_line('low');
elsif(v_sal > 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('height');
end if;
end;
127、-------------------循環(huán) =====do while
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when (i>=11);
end loop;
end;
128、---------------------while
declare
j binary_integer := 1;
begin
while j < 11 loop
dbms_output.put_line(j);
j:=j+1;
end loop;
end;
129、---------------------for
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
130、-----------------------異常(1)
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多記錄了');
when others then
dbms_output.put_line('error');
end;
131、-----------------------異常(2)
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('太多記錄了');
end;
132、----------------------創(chuàng)建序列
create sequence seq_errorlog_id start with 1 increment by 1;
133、-----------------------錯(cuò)誤處理(用表記錄：將系統(tǒng)日志存到數(shù)據(jù)庫(kù)便于以后查看)
-- 創(chuàng)建日志表:
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
declare
v_deptno dept.deptno%type := 10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
rollback;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
insert into errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate);
commit;
end;
133---------------------PL/SQL中的重點(diǎn)cursor(游標(biāo))和指針的概念差不多
declare
cursor c is
select * from emp; --此處的語句不會(huì)立刻執(zhí)行，而是當(dāng)下面的open c的時(shí)候，才會(huì)真正執(zhí)行
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename); --這樣會(huì)只輸出一條數(shù)據(jù) 134將使用循環(huán)的方法輸出每一條記錄
close c;
end;
134----------------------使用do while 循環(huán)遍歷游標(biāo)中的每一個(gè)數(shù)據(jù)
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
(1) exit when (c%notfound); --notfound是oracle中的關(guān)鍵字，作用是判斷是否還有下一條數(shù)據(jù)
(2) dbms_output.put_line(v_emp.ename); --(1)(2)的順序不能顛倒，最后一條數(shù)據(jù)，不會(huì)出錯(cuò)，會(huì)把最后一條數(shù)據(jù)，再次的打印一遍
end loop;
close c;
end;
135------------------------while循環(huán)，遍歷游標(biāo)
declare
cursor c is
select * from emp;
v_emp emp%rowtype;
begin
open c;
fetch c into v_emp;
while(c%found) loop
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
close c;
end;
136--------------------------for 循環(huán)，遍歷游標(biāo)
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
137---------------------------帶參數(shù)的游標(biāo)
declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
is
select ename, sal from emp where deptno=v_deptno and job=v_job;
--v_temp c%rowtype;此處不用聲明變量類型
begin
for v_temp in c(30, 'click') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
138-----------------------------可更新的游標(biāo)
declare
cursor c --有點(diǎn)小錯(cuò)誤
is
select * from emp2 for update;
-v_temp c%rowtype;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
else if (v_temp.sal =5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;
139-----------------------------------procedure存儲(chǔ)過程(帶有名字的程序塊)
create or replace procedure p
is--這兩句除了替代declare，下面的語句全部都一樣
cursor c is
select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.deptno = 10) then
update emp2 set sal = sal +10 where current of c;
else if(v_emp.deptno =20) then
update emp2 set sal = sal + 20 where current of c;
else
update emp2 set sal = sal + 50 where current of c;
end if;
end loop;
commit;
end;
--執(zhí)行存儲(chǔ)過程的兩種方法：
(1)exec p;(p是存儲(chǔ)過程的名稱)
(2)
begin
p;
end;
/
140-------------------------------帶參數(shù)的存儲(chǔ)過程
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;
141----------------------調(diào)用140
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.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
142------------------刪除存儲(chǔ)過程
drop procedure p;
143------------------------創(chuàng)建函數(shù)計(jì)算個(gè)人所得稅
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
return 0.20;
end if;
end;
----144-------------------------創(chuàng)建觸發(fā)器(trigger) 觸發(fā)器不能單獨(dú)的存在，必須依附在某一張表上
--創(chuàng)建觸發(fā)器的依附表
create table emp2_log
(
ename varchar2(30) ,
eaction varchar2(20),
etime date
);
create or replace trigger trig
after insert or delete or update on emp2 ---for each row 加上此句，每更新一行，觸發(fā)一次，不加入則值觸發(fā)一次
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;
145-------------------------------通過觸發(fā)器更新數(shù)據(jù)
create or replace trigger trig
after update on dept
for each row
begin
update emp set deptno =:NEW.deptno where deptno =: OLD.deptno;
end;
------只編譯不顯示的解決辦法 set serveroutput on;
145-------------------------------通過創(chuàng)建存儲(chǔ)過程完成遞歸
create or replace procedure p(v_pid article.pid%type,v_level binary_integer) is
cursor c is select * from article where pid = v_pid;
v_preStr varchar2(1024) := '';
begin
for i in 0..v_leave loop
v_preStr := v_preStr || '****';
end loop;
for v_article in c loop
dbms_output.put_line(v_article.cont);
if(v_article.isleaf = 0) then
p(v_article.id);
end if;
end loop;
end;
146-------------------------------查看當(dāng)前用戶下有哪些表---
--首先，用這個(gè)用戶登錄然后使用語句：
select * from tab;
147-----------------------------用Oracle進(jìn)行分頁(yè)!--------------
--因?yàn)镺racle中的隱含字段rownum不支持'>'所以：
select * from (
select rownum rn, t.* from (
select * from t_user where user_id <> 'root'
) t where rownum <6
) where rn >3
148------------------------Oracle下面的清屏命令----------------
clear screen; 或者 cle scr;
149-----------將創(chuàng)建好的guohailong的這個(gè)用戶的密碼改為abc--------------
alter user guohailong identified by abc
--當(dāng)密碼使用的是數(shù)字的時(shí)候可能會(huì)不行
--使用在10 Oracle以上的正則表達(dá)式在dual表查詢
with test1 as(
select 'ao' name from dual union all
select 'yang' from dual union all
select 'feng' from dual )
select distinct regexp_replace(name,'[0-9]','') from test1
------------------------------------------
with tab as (
select 'hong' name from dual union all
select 'qi' name from dual union all
select 'gong' name from dual)
select translate(name,'\\0123456789','\\') from tab;
CREATE OR REPLACE PROCEDURE
calc(i_birth VARCHAR2) IS
s VARCHAR2(8);
o VARCHAR2(8);
PROCEDURE cc(num VARCHAR2, s OUT VARCHAR2) IS
BEGIN
FOR i
IN REVERSE 2 .. length(num) LOOP
s := s || substr(substr(num, i, 1) + substr(num, i - 1, 1), -1);
END LOOP;
SELECT REVERSE(s) INTO s FROM dual;
END;
BEGIN o := i_birth;
LOOP
cc(o, s);
o := s;
dbms_output.put_line(s);
EXIT WHEN length(o) < 2;
END LOOP;
END;
set serveroutput on;
exec calc('19880323');
----算命pl/sql
with t as
(select '19880323' x from dual)
select
case
when mod (i, 2) = 0 then '命好'
when i = 9 then '命運(yùn)悲慘'
else '一般'
end result
from (select mod(sum((to_number(substr(x, level, 1)) +to_number(substr(x, -level, 1))) *
greatest(((level - 1) * 2 - 1) * 7, 1)),10) i from t connect by level <= 4);
--構(gòu)造一個(gè)表，和emp表的部分字段相同，但是順序不同
SQL> create table t_emp as
2 select ename,empno,deptno,sal
3 from emp
4 where 1=0
5 /
Table created
--添加數(shù)據(jù)
SQL> insert into t_emp(ename,empno,deptno,sal)
2 select ename,empno,deptno,sal
3 from emp
4 where sal >= 2500
5 /
select * from tb_product where createdate>=to_date('2011-6-13','yyyy-MM-dd') and createdate<=to_date('2011-6-16','yyyy-MM-dd');
sysdate --獲取當(dāng)前系統(tǒng)的時(shí)間
to_date('','yyyy-mm-dd')
select * from tb_product where to_char(createdate,'yyyy-MM-dd')>='2011-6-13' and to_char(createdate,'yyyy-MM-dd')<='2011-6-16';
select * from tb_product where trunc(createdate)>=? and trunc(createdate)<=?
用trunc函數(shù)就可以了
第一次
1、Oracle安裝及基本命令
1.1、Orace簡(jiǎn)介
Oracleso一個(gè)生產(chǎn)中間件和數(shù)據(jù)庫(kù)的較大生產(chǎn)商。其發(fā)展依靠了IBM公司。創(chuàng)始人是Larry Ellison。
1.2、Oracle的安裝
1) Oracle的主要版本
Oracle 8;
Oracle 8i;i，指的是Internet
Oracle 9i;相比Oracle8i比較類似
Oracle 10g;g，表示網(wǎng)格技術(shù)
所謂網(wǎng)格技術(shù)，拿百度搜索為例，現(xiàn)在我們需要搜索一款叫做“EditPlus”的文本編輯器軟件，當(dāng)我們?cè)诎俣人阉骺蛑休斎?ldquo;EditPlus”進(jìn)行搜索時(shí)， 會(huì)得到百度為我們搜索到的大量關(guān)于它的鏈接，此時(shí)，我們考慮一個(gè)問題，如果在我所處的網(wǎng)絡(luò)環(huán)境周邊的某個(gè)地方的服務(wù)器就提供這款軟件的下載(也就是說提供一個(gè)下載鏈接供我們下載)，那么，我們就沒必要去訪問一個(gè)遠(yuǎn)在地球?qū)γ娴哪硞�(gè)角落的服務(wù)器去下載這款軟件。如此一來就可以節(jié)省大量的網(wǎng)絡(luò)資源。使用網(wǎng)格技術(shù)就能解決這種問題。我們將整個(gè)網(wǎng)絡(luò)劃分為若干個(gè)網(wǎng)格，也就是說每一個(gè)使用網(wǎng)絡(luò)的用戶，均存在于某一個(gè)網(wǎng)格，當(dāng)我們需要搜索指定資源時(shí)，首先在我們所處的網(wǎng)格中查找是否存在指定資源，沒有的話就擴(kuò)大搜索范圍，到更大的網(wǎng)格中進(jìn)行查找，直到查找到為止。
2) 安裝Oracle的準(zhǔn)備工作
關(guān)閉防火墻，以免影響數(shù)據(jù)庫(kù)的正常安裝。
3) 安裝Oralce的注意事項(xiàng)
為了后期的開發(fā)和學(xué)習(xí)，我們將所有數(shù)據(jù)庫(kù)默認(rèn)賬戶的口令設(shè)置為統(tǒng)一口令的，方便管理和使用。
在點(diǎn)擊“安裝”后，數(shù)據(jù)庫(kù)相關(guān)參數(shù)設(shè)置完成，其安裝工作正式開始，在完成安裝時(shí)，不要急著去點(diǎn)擊“確定”按鈕，這時(shí)候，我們需要進(jìn)行一個(gè)非常重要的操作——賬戶解鎖。因?yàn)樵贠racle中默認(rèn)有一個(gè)叫做scott的賬戶，該賬戶中默認(rèn)有4張表，并且存有相應(yīng)的數(shù)據(jù)，所以，為了方便我們學(xué)習(xí)Oracle數(shù)據(jù)庫(kù)，我們可以充分利用scott這個(gè)內(nèi)置賬戶。但是奇怪的是，在安裝Oracle數(shù)據(jù)庫(kù)的時(shí)候，scott默認(rèn)是鎖住的，所以在使用該賬戶之前，我們就需要對(duì)其進(jìn)行解鎖操作。在安裝完成界面中，點(diǎn)擊“口令管理”進(jìn)入到相應(yīng)的口令管理界面，找到scott賬戶，將是否解鎖一欄的去掉，即可完成解鎖操作，后期就可以正常使用scott賬戶。我們運(yùn)行SQLPlus(Oracle提供的命令行操作)，會(huì)提示我們輸入用戶名，現(xiàn)在我們可以輸入用戶名scott，回車后，會(huì)提示輸入口令，奇怪的是，當(dāng)我們輸入在安裝時(shí)設(shè)置的統(tǒng)一口令時(shí)，提示登錄拒絕，顯然是密碼錯(cuò)誤，那么，在Oracle數(shù)據(jù)庫(kù)中，scott的默認(rèn)密碼是tiger，所以使用tiger可以正常登錄，但是提示我們scott的當(dāng)前密碼已經(jīng)失效，讓我們重新設(shè)置密碼，建議還是設(shè)置為tiger。
在Oracle中內(nèi)置了很多賬戶，那么，我們來了解下一下幾個(gè)比較重要的內(nèi)置賬戶：
|-普通用戶：scott/tiger
|-普通管理員：system/manager
|-超級(jí)管理員：sys/change_on_install
4) 關(guān)于Oracle的服務(wù)
在Oracle安裝完成之后，會(huì)在我們的系統(tǒng)中進(jìn)行相關(guān)服務(wù)的注冊(cè)，在所有注冊(cè)的服務(wù)中，我們需要關(guān)注一下兩個(gè)服務(wù)，在實(shí)際使用Oracle的過程中，這兩個(gè)服務(wù)必須啟動(dòng)才能使Oracle正常使用。
|-第一個(gè)是OracleOraDb11g_home1TNSListener，監(jiān)聽服務(wù)，如果客戶端想要連接數(shù)據(jù)庫(kù)，此服務(wù)必須開啟。
|-第二個(gè)是OracleServiceORCL，數(shù)據(jù)庫(kù)的主服務(wù)。命名規(guī)則：OracleService + 數(shù)據(jù)庫(kù)名稱，此 服務(wù)必須啟動(dòng)。
此后，我們可以通過命令行方式進(jìn)入到SQLPlus的控制中心，進(jìn)行命令的輸入。
1.3、SQLPlus
SQLPlus是Oracle提供的一種命令行執(zhí)行的工具軟件，安裝之后會(huì)自動(dòng)在系統(tǒng)中進(jìn)行注冊(cè)。連接到數(shù)據(jù)庫(kù)之后，就可以開始對(duì)數(shù)據(jù)庫(kù)中的表進(jìn)行操作了。
1) 對(duì)SQLPlus的環(huán)境設(shè)置
set linesize 長(zhǎng)度;--設(shè)置每行顯示的長(zhǎng)度
set pagesize 行數(shù);--修改每頁(yè)顯示記錄的長(zhǎng)度。
需要注意的是，上述連個(gè)參數(shù)的設(shè)置只在當(dāng)前的命令行有效，命令行窗口重啟或者開啟了第二個(gè)窗口需要重新設(shè)置。
2) SQLPlus常用操作
在SQLPlus中輸入ed a.sql，會(huì)彈出找不到文件的提示框，此時(shí)點(diǎn)擊“是”，將創(chuàng)建一個(gè)a.sql文件，并彈出文本編輯頁(yè)面，在這里可以輸入相關(guān)的sql語句，編輯完成后保存，在命令行中通過 @ a.sql的方式執(zhí)行命令，如果創(chuàng)建的文件后綴為“sql”，那么在執(zhí)行的時(shí)候可以省略掉，即可以這么寫， @ a。除了創(chuàng)建不存在的文件外，sqlplus中也可以通過指定本地存在的文件進(jìn)行命令的執(zhí)行，方式為 @ 文件路徑。
在SQLPlus中可以通過命令使用其他賬戶進(jìn)行數(shù)據(jù)庫(kù)的連接，如，當(dāng)前連接的用戶是scott，我們需要使用sys進(jìn)行連接，則可以這么操作：conn sys/430583 as sysdba，這里需要說明的是，sys是超級(jí)管理員，當(dāng)我們需要使用sys進(jìn)行登錄的時(shí)候，那么需要額外的加上as sysdba表示sys將以管理員的身份登錄。這里有幾點(diǎn)可以測(cè)試下
|-當(dāng)我們使用sys以sysdba的角色登錄時(shí)，其密碼可以隨意輸入，不一定是我們?cè)O(shè)置的統(tǒng)一口令(430583)。所以，我們得出結(jié)論，在管理員登錄時(shí)，只對(duì)用戶進(jìn)行驗(yàn)證，而普通用戶登錄時(shí)，執(zhí)行用戶和密碼驗(yàn)證。
在sys賬戶下訪問scott下的emp表時(shí)，會(huì)提示錯(cuò)誤，因?yàn)樵趕ys中是不存在emp表的，那么如果需要在sys下訪問scott的表(也就是說需要在a用戶下訪問b用戶下的表)，該如何操作呢?首先，我們應(yīng)該知道每個(gè)對(duì)象是屬于一種模式(模式是對(duì)用戶所創(chuàng)建的數(shù)據(jù)庫(kù)對(duì)象的總稱，包括表，視圖，索引，同義詞，序列，過程和程序包等)的，而每個(gè)賬戶對(duì)應(yīng)一個(gè)模式，所以我們需要在sys下訪問scott的表時(shí)，需要指明所訪問的表是屬于哪一個(gè)模式的，即，我們可以這樣操作來實(shí)現(xiàn)上面的操作：select * from scott.emp;
如果我們需要知道當(dāng)前連接數(shù)據(jù)庫(kù)的賬戶是誰，可以這樣操作：show user;
我們知道，一個(gè)數(shù)據(jù)庫(kù)可以存儲(chǔ)多張表，那么，如何查看指定數(shù)據(jù)庫(kù)的所有表名稱呢?select * from tab;
在開發(fā)過程中，我們需要經(jīng)常的查看某張表的表結(jié)構(gòu)，這個(gè)操作可以這樣實(shí)現(xiàn)：desc emp;
在SQLPlus中，我們可以輸入“/”來快速執(zhí)行上一條語句。例如，在命令行中我們執(zhí)行了一條這樣的語句：select * from emp;但是我們需要再次執(zhí)行該查詢，就可以輸入一個(gè)“/”就可快速執(zhí)行。
3) 常用數(shù)據(jù)類型
number(4)-->表示數(shù)字，長(zhǎng)度為4
varchar2(10)-->表示的是字符串，只能容納10個(gè)長(zhǎng)度
date-->表示日期
number(7,2)-->表示數(shù)字，小數(shù)占2位，整數(shù)占5位，總共7位
第二次
1、SQL語句
1.1 準(zhǔn)備工作--熟悉scott賬戶下的四張表及表結(jié)構(gòu)
第一張表emp-->雇員表，用于存儲(chǔ)雇員信息
empno number(4) 表示雇員的編號(hào)，唯一編號(hào)
ename varchar2(10) 表示雇員的姓名
job varchar2(9) 表示工作職位
mgr number(4) 表示一個(gè)雇員的上司編號(hào)
hiredate date 表示雇傭日期
sal number(7,2) 表示月薪，工資
comm number(7,2) 表示獎(jiǎng)金
deptno number(2) 表示部門編號(hào)
第二張表dept-->部門表，用于存儲(chǔ)部門信息
deptno number(2) 部門編號(hào)
dname varchar2(14) 部門名稱
loc varchar2(13) 部門位置
第三張表salgrade-->工資等級(jí)表，用于存儲(chǔ)工資等級(jí)
grade number 等級(jí)名稱
losal number 此等級(jí)的最低工資
hisal number 此等級(jí)的最高工資
第四張表bonus-->獎(jiǎng)金表，用于存儲(chǔ)一個(gè)雇員的工資及獎(jiǎng)金
ename varchar2(10) 雇員姓名
job varchar2(9) 雇員工作
sal number 雇員工資
comm number 雇員獎(jiǎng)金
1.2、SQL簡(jiǎn)介
什么是SQL?
SQL(Structured Query Language，結(jié)構(gòu)查詢語言)是一個(gè)功能強(qiáng)大的數(shù)據(jù)語言。SQL通常用于與數(shù)據(jù)庫(kù)的通訊。SQL是關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)的標(biāo)準(zhǔn)語言。SQL功能強(qiáng)大，概括起來，分為以下幾組：
|-DML-->Data Manipulation Language，數(shù)據(jù)操縱語言，用于檢索或者修改數(shù)據(jù)，即主要是對(duì)數(shù)據(jù)庫(kù)表中的數(shù)據(jù)的操作。
|-DDL-->Data Definition Language,數(shù)據(jù)定義語言，用于定義數(shù)據(jù)的結(jié)構(gòu)，如創(chuàng)建、修改或者刪除數(shù)據(jù)庫(kù)對(duì)象，即主要是對(duì)表的操作。
|-DCL-->Data Control Language,數(shù)據(jù)控制語言，用于定義數(shù)據(jù)庫(kù)用戶的權(quán)限，即主要對(duì)用戶權(quán)限的操作。
1.3、簡(jiǎn)單查詢語句
簡(jiǎn)單查詢語句的語法格式是怎樣的?
select * |具體的列名 [as] [別名] from 表名稱;
需要說明的是，在實(shí)際開發(fā)中，最好不要使用*代替需要查詢的所有列，最好養(yǎng)成顯式書寫需要查詢的列名，方便后期的維護(hù);在給查詢的列名設(shè)置別名的時(shí)候，可以使用關(guān)鍵字as，當(dāng)然不用也是可以的。
拿emp表為例，我們現(xiàn)在需要查詢出雇員的編號(hào)、姓名、工作三個(gè)列的信息的話，就需要在查詢的時(shí)候明確指定查詢的列名稱，即
select empno,ename,job from emp;
如果需要指定查詢的返回列的名稱，即給查詢列起別名，我們可以這樣操作
select empno 編號(hào),ename 姓名,job 工作 from emp;--省略as關(guān)鍵字的寫法
或者
select empno as 編號(hào),ename as 姓名,job as 做工 from emp; --保留as關(guān)鍵字的寫法
如果現(xiàn)在需要我們查詢出emp中所有的job，我們可能這么操作
select job from emp;
可能加上一個(gè)別名會(huì)比較好
select job 工作 from emp;
但是現(xiàn)在出現(xiàn)了一個(gè)問題，從查詢的結(jié)果中可以看出，job的值是有重復(fù)的，這是為什么呢?因?yàn)槲覀冎酪粋�(gè)job職位可能會(huì)對(duì)應(yīng)多個(gè)雇員，比如，在一個(gè)公司的市場(chǎng)部會(huì)有多名市場(chǎng)人員，在這里我們使用select job from emp;查詢的實(shí)際上是當(dāng)前每個(gè)雇員對(duì)應(yīng)的工作職位，有多少
個(gè)雇員，就會(huì)對(duì)應(yīng)的查詢出多少個(gè)職位出來，所以就出現(xiàn)了重復(fù)值，為了消除這樣的重復(fù)值，我們?cè)谶@里可以使用關(guān)鍵字distinct，接下來我們繼續(xù)完成上面的操作，即
select distinct job from emp;
所以，我們可以看到，使用distinct的語法是這樣的：
select distinct *|具體的列名 別名 from 表名稱;
但是在消除重復(fù)列的時(shí)候，需要強(qiáng)調(diào)的是，如果我們使用distinct同時(shí)查詢多列時(shí)，則必須保證查詢的所有列都存在重復(fù)數(shù)據(jù)才能消除掉。也就是說，當(dāng)我們需要查詢a,b,c三列時(shí)，如果a表存在重復(fù)值，但是b和c中沒有重復(fù)值，當(dāng)使用distinct時(shí)是看不到消除重復(fù)列的效果的。拿scott中的emp表為例，我們需要查詢出雇員編號(hào)及雇員工作兩個(gè)列的值，我們知道一個(gè)工作會(huì)對(duì)應(yīng)多個(gè)雇員，所以，在這種操作中，雇員編號(hào)是沒有重復(fù)值的，但是工作有重復(fù)值，所以，執(zhí)行此次查詢的結(jié)果將是得到每一個(gè)雇員對(duì)應(yīng)的工作名稱，顯然，distinct沒起到作用。
現(xiàn)在我們得到了一個(gè)新的需求，要求我們按如下的方式進(jìn)行查詢：
編號(hào)是：7369的雇員，姓名是：SMITH，工作是：CLERK
那么，我們?cè)撊绾谓鉀Q呢?在這里，我們需要使用到Oracle 中的字符串連接(“||”)操作來實(shí)現(xiàn)。如果需要顯示一些額外信息的話，我們需要使用單引號(hào)將要顯示的信息包含起來。那么，上面的操作可以按照下面的方式進(jìn)行，
select '編號(hào)是' || empno || '的雇員，姓名是：' || ename || '，工作是：' || job from emp;
下面我們?cè)倏匆粋�(gè)新的應(yīng)用。公司業(yè)績(jī)很好，所以老板想加薪，所有員工的工資上調(diào)20%，該如何實(shí)現(xiàn)呢?難道在表中一個(gè)一個(gè)的修改工資列的值嗎?很顯然不是的，我們可以通過使用四則運(yùn)算來完成加薪的操作。
select ename,sal*1.2 newsal from emp;--newsal是為上調(diào)后的工資設(shè)置的列名
四則運(yùn)算，+、-、*、/，同樣有優(yōu)先順序，先乘除后加減。
1.4、限定查詢(where子句)
在前面我們都是將一張表的全部列或者指定列的所有數(shù)據(jù)查詢出來，現(xiàn)在我們有新的需求了，需要在指定條件下查詢出數(shù)據(jù)，比如，需要我們查詢出部門號(hào)為20的所有雇員、查詢出工資在3000以上的雇員信息......，這些所謂的查詢限定條件就是通過where來指定的。我們先看看如何通過限定查詢的方式進(jìn)行相關(guān)操作。我們?cè)谇懊嬷懒撕?jiǎn)單的查詢語法是：
select *|具體的列名 from 表名稱;
那么，限定查詢的語法格式如下：
select *|具體的列名 from 表名稱 where 條件表達(dá)式;
首先，我們現(xiàn)在要查詢出工資在1500(不包括1500)之上的雇員信息
select * from emp where sal>1500;
下面的操作是查詢出可以得到獎(jiǎng)金的雇員信息(也就是獎(jiǎng)金列不為空的記錄)
select * from emp where comm is not null;
很顯然，查詢沒有獎(jiǎng)金的操作就是
select * from emp where comm is null;
我們來點(diǎn)復(fù)雜的查詢，查詢出工資在1500之上，并且可以拿到獎(jiǎng)金的雇員信息。這里的限定條件不再是一個(gè)了，當(dāng)所有的限定條件需要同時(shí)滿足時(shí)，我們采用and關(guān)鍵字將多個(gè)限定條件進(jìn)行連接，表示所有的限定條件都需要滿足，所以，該查詢可以這么進(jìn)行：
select * from emp where sal>1500 and comm is not null;
現(xiàn)在的需求發(fā)生了變化，要求查詢出工資在1500之上，或者可以領(lǐng)取到獎(jiǎng)金的雇員信息。這里的限定條件也是兩個(gè)，但是有所不同的是，我們不需要同時(shí)滿足著兩個(gè)條件，因?yàn)樾枨笾袑懙氖?/p>
“或者”，所以在查詢時(shí)，只需要滿足兩個(gè)條件中的一個(gè)就行。那么，我們使用關(guān)鍵字or來實(shí)現(xiàn)“或者”的功能。
select * from emp where sal>1500 or comm is not null;
需求再次發(fā)生變化，查詢出工資不大于1500(小于或等于1500)，同時(shí)不可以領(lǐng)取獎(jiǎng)金的雇員信息，可想這些雇員的工資一定不怎么高。之前我們使用not關(guān)鍵字對(duì)限定條件進(jìn)行了取反，那么，這里就相當(dāng)于對(duì)限定條件整體取反。我們分析下，工資不大于1500同時(shí)不可以領(lǐng)取獎(jiǎng)金的反面含義就是工資大于1500同時(shí)可以領(lǐng)取獎(jiǎng)金，所以，我們這樣操作：
select * from emp where sal>1500 and comm is not null;
這是原始需求取反后的查詢語句，那么為了達(dá)到最終的查詢效果，我們將限定條件整體取反，即
select * from emp where not(sal>1500 and comm is not null);
在這里，我們通過括號(hào)將一系列限定條件包含起來表示一個(gè)整體的限定條件。
我們?cè)跀?shù)學(xué)中學(xué)過這樣的表達(dá)式：100<a<200，那么在數(shù)據(jù)庫(kù)中如何實(shí)現(xiàn)呢?一樣的，我們現(xiàn)在要查詢出工資在1500之上，但是在3000之下的全部雇員信息，很顯然，兩個(gè)限定條件，同時(shí)要滿足才行。
select * from emp where sal>1500 and sal<3000;
這里不要異想天開的寫成select * from emp where 1500<sal<3000;()試試吧，絕對(duì)會(huì)報(bào)錯(cuò)。 很簡(jiǎn)單，數(shù)據(jù)庫(kù)軟件不支持這樣的寫法，至少現(xiàn)在的數(shù)據(jù)庫(kù)沒有誰去支持這樣的寫法。
在SQL語法中，提供了一種專門指定范圍查詢的過濾語句：between x and y，相當(dāng)于a>=x and a<=y,也就是包含了等于的功能，其語法格式如下：
select * from emp where sal between 1500 and 3000;
現(xiàn)在我們使用這種范圍查詢的方式修改上面的語句如下：
select * from emp where sal between 1500 and 3000;
好了，我們現(xiàn)在開始使用數(shù)據(jù)庫(kù)中非常重要的一種數(shù)據(jù)類型，日期型。
查詢出在1981年雇傭的全部雇員信息，看似很簡(jiǎn)單的一個(gè)查詢操作，這樣寫嗎?
select * from emp where hiredate=1981;()
很顯然，有錯(cuò)。hiredate是date類型的，1981看似是一個(gè)年份(日期)，但是像這樣使用，它僅僅是一個(gè)數(shù)字而已，類型都匹配，怎么進(jìn)行相等判斷呢?繼續(xù)考慮。我們先這樣操作，查詢emp表的所有信息，
select * from emp;
從查詢結(jié)果中，我們重點(diǎn)關(guān)注hiredate這一列的數(shù)據(jù)格式是怎么定義的，
20-2月 -81，很顯然，我們抽象的表示出來是這樣的，
一個(gè)月的第幾天-幾月 -年份的后兩位
所以，接下來我們就有思路了，我們就按照這樣的日期格式進(jìn)行限定條件的設(shè)置，該如何設(shè)置呢?考慮下，查詢1981年雇傭的雇員，是不是這個(gè)意思，從1981年1月1日到1981年12月31日雇傭的就是我們需要的數(shù)據(jù)呢?所以，這樣操作吧
select * from emp where hiredate between '1-1月 -81' and '31-12月 -81';
可以看到，上面的限定條件使用了單引號(hào)包含，我們暫且可以將一個(gè)日期看做是一個(gè)字符串。由上面的范例中我們得到結(jié)論：between...and ...除了可以進(jìn)行數(shù)字范圍的查詢，還可以進(jìn)行日期范圍的查詢。
我們?cè)賮砜聪旅娴睦�，查詢出姓名是smith的雇員信息，嗯，很簡(jiǎn)單，這樣操作
select * from emp where ename='smith';
看似沒有問題，限定條件也給了，單引號(hào)也沒有少，可是為什么查詢不到記錄呢?明明記得emp表中有一個(gè)叫做smith的雇員呀?難道被刪掉了，好吧，我們不需要在這里耗費(fèi)時(shí)間猜測(cè)語句自身的問題了，上面的語句在語法上沒有任何問題，我們先看看emp表中有哪些雇員，
select * from emp;
可以看到，確實(shí)有一個(gè)叫做smith的雇員，但是不是smith，而是SMITH，所以，我們忽略了一個(gè)很重要的問題，Oracle是對(duì)大小寫敏感的。所以，更改如下：
select * from emp where ename='SMITH';
現(xiàn)在看一個(gè)這樣的例子，要求查詢出雇員編號(hào)是7369,7499,7521的雇員信息。如果按照以前的做法，是這樣操作的，
select * from emp where empno=7369 or empno=7499 or empno=7521;
執(zhí)行一下吧，確實(shí)沒任何問題，查詢出指定雇員編號(hào)的所有信息。但是SQL語法提供一種更好的解決方法，使用in關(guān)鍵字完成上面的查詢，如下：
select * from emp where empno in(7369,7499,7521);
總結(jié)下in的語法如下：
select * from tablename where 列名 in (值1，值2，值3);
select * from tablename where 列名 not in (值1，值2，值3);
需要說明的是，in關(guān)鍵字不光可以用在數(shù)字上，也可以用在字符串的信息上�？聪旅娴睦�
查詢出姓名是SMITH、ALLEN、KING的雇員信息
select * from emp where ename in('SMITH','ALLEN','KING');
如果在指定的查詢范圍中附加了額外的內(nèi)容，不會(huì)影響查詢結(jié)果。
模糊查詢對(duì)于提高用戶體驗(yàn)是非常好的，對(duì)于數(shù)據(jù)庫(kù)的模糊查詢，我們通過like關(guān)鍵字來實(shí)現(xiàn)。首先我們了解下like主要使用的兩種通配符：
|-“%”-->可以匹配任意長(zhǎng)度的內(nèi)容，包括0個(gè)字符;
|-“_”-->可以匹配一個(gè)長(zhǎng)度的內(nèi)容。
下面我們看這樣一個(gè)需求，查詢所有雇員姓名中第二個(gè)字母包含“M”的雇員信息
select * from emp where ename like '_M%';
說明下，前面的“_”匹配姓名中第一個(gè)字母(任意一個(gè)字母)，“%”匹配M后面出現(xiàn)的0個(gè)或者多個(gè)字母。
查詢出雇員姓名中包含字母M的雇員信息。分析可知，字母M可以出現(xiàn)在姓名的任意位置，如何進(jìn)行正確的匹配呢?
select * from emp where ename like '%M%';
這里還是使用%，匹配0個(gè)或者多個(gè)字母，即可以表示M出現(xiàn)在姓名的任意位置上。
如果我們?cè)谑褂胠ike查詢的時(shí)候沒有指定查詢的關(guān)鍵字，則表示查詢內(nèi)容，即
select * from emp where ename like '%%';
相當(dāng)于我們前面看到的
select * from emp;
前面我們遇到了一個(gè)這樣的需求，查詢出在1981年雇傭的所有雇員信息，當(dāng)時(shí)我們采取的是使用范圍查詢between ... and ...實(shí)現(xiàn)的，現(xiàn)在我們使用like同樣可以實(shí)現(xiàn)
select * from emp where hiredate like '%81%';
查詢工資中包含6的雇員信息
select * from emp where sal like '%5%';
在操作條件中還可以使用：>、>=、=、<、<=等計(jì)算符號(hào)
對(duì)于不等于符號(hào)，有兩種方式：<>、!=
現(xiàn)在需要我們查詢雇員編號(hào)不是7369的雇員信息
select * from emp where empno<>7369;
select * from emp where empno!=7369;
1.5、對(duì)查詢結(jié)果進(jìn)行排序(order by子句)
在查詢的時(shí)候，我們常常需要對(duì)查詢結(jié)果進(jìn)行一種排序，以方便我們查看數(shù)據(jù)，比如以雇員編號(hào)排序，以雇員工資排序等。排序的語法是：
select *|具體的列名稱 from 表名稱 where 條件表達(dá)式 order by 排序列1,排序列2 asc|desc;
asc表示升序，默認(rèn)排序方式，desc表示降序。
現(xiàn)在要求所有的雇員信息按照工資由低到高的順序排列
select * from emp order by sal;
在升級(jí)開發(fā)中，會(huì)遇到多列排序的問題，那么，此時(shí)，會(huì)給order by指定多個(gè)排序列。要求查詢出10部門的所有雇員信息，查詢的信息按照工資由高到低排序，如果工資相等，則按照雇傭日期由早到晚排序。
select * from emp where deptno=10 order by sal desc,hiredate asc;
需要注意的是，排序操作是放在整個(gè)SQL語句的最后執(zhí)行。
1.6、單行函數(shù)
在眾多的數(shù)據(jù)庫(kù)系統(tǒng)中，每個(gè)數(shù)據(jù)庫(kù)之間唯一不同的最大區(qū)別就在于函數(shù)的支持上，使用函數(shù)可以完成一系列的操作功能。單行函數(shù)語法如下：
function_name(column|expression,[arg1,arg2...])
參數(shù)說明：
function_name：函數(shù)名稱
columne：數(shù)據(jù)庫(kù)表的列名稱
expression：字符串或計(jì)算表達(dá)式
arg1,arg2：在函數(shù)中使用參數(shù)
單行函數(shù)的分類：
字符函數(shù)：接收字符輸入并且返回字符或數(shù)值
數(shù)值函數(shù)：接收數(shù)值輸入并返回?cái)?shù)值
日期函數(shù)：對(duì)日期型數(shù)據(jù)進(jìn)行操作
轉(zhuǎn)換函數(shù)：從一種數(shù)據(jù)類型轉(zhuǎn)換到另一種數(shù)據(jù)類型
通用函數(shù)：nvl函數(shù)，decode函數(shù)
字符函數(shù)：
專門處理字符的，例如可以將大寫字符變?yōu)樾�，求出字符的長(zhǎng)度。
現(xiàn)在我們看一個(gè)例子，將小寫字母轉(zhuǎn)為大寫字母
select upper('smith') from dual;
在實(shí)際中，我們會(huì)遇到這樣的情況，用戶需要查詢smith雇員的信息，但是我們數(shù)據(jù)庫(kù)表中存放的是SMITH，這時(shí)為了方便用戶的使用我們將用戶輸入的雇員姓名字符串轉(zhuǎn)為大寫，
select * from emp where ename=upper('smith');
同樣，我們也可以使用lower()函數(shù)將一個(gè)字符串變?yōu)樾懽帜副硎荆?/p>
select lower('HELLO WORLD') from dual;
那么，將字符串的首字母變?yōu)榇髮懺撊绾螌?shí)現(xiàn)呢?我們使用initcap()函數(shù)來完成上面的操作。
select initcap('HELLO WOLRD') from dual;
在前面的學(xué)習(xí)中我們知道，在scot賬戶下的emp表中的雇員姓名采用全大寫顯示，現(xiàn)在我們需要激昂姓名的首字母變?yōu)榇髮�，該如何操作�?
select initcap(ename) from emp;
我們?cè)谇懊媸褂昧俗址B接操作符“||”對(duì)字符串連接顯示，比如：
select '編號(hào)為' || empno || '的姓名是：' || ename from emp;
那么，在字符函數(shù)中提供concat()函數(shù)實(shí)現(xiàn)連接操作。
select concat('hello','world') from dual;
上面的范例使用函數(shù)實(shí)現(xiàn)如下：
select concat('編號(hào)為',empno,'的姓名是：',ename);?????????
此種方式不如連接符“||”好使。
在字符函數(shù)中可以進(jìn)行字符串的截取、求出字符串的長(zhǎng)度、進(jìn)行指定內(nèi)容的替換
select substr('hello',1,3) 截取字符串，length('hello') 字符串長(zhǎng)度,replace('hello','l','x') 字符串替換 from dual;
通過上面范例的操作，我們需要注意幾以下：
Oralce中substr()函數(shù)的截取點(diǎn)是從0開始還是從1開始;針對(duì)這個(gè)問題，我們來操作看下，將上面的截取語句改為：
select substr('hello',0,3) 截取字符串 from dual;
由查詢結(jié)果可以發(fā)現(xiàn)，結(jié)果一樣，也就是說從0和從1的效果是完全一樣，這歸咎于Oracle的智能化。
如果現(xiàn)在要求顯示所有雇員的姓名及姓名的后三個(gè)字符，我們知道，每個(gè)雇員姓名的字符串長(zhǎng)度可能不同，所以我們采取的措施是求出整個(gè)字符串的長(zhǎng)度在減去2，我們這樣操作，
select ename,substr(ename,length(ename)-2) from emp;
雖然功能實(shí)現(xiàn)了，但是有沒有羽化的方案呢?當(dāng)然是有的，我們分析下，當(dāng)我們?cè)诮厝∽址臅r(shí)候，給定一個(gè)負(fù)數(shù)值是什么效果，對(duì)，就是倒著截取，我們采取這種方式優(yōu)化如下：
select ename,substr(ename,-3) from emp;
數(shù)值函數(shù)：
四舍五入：round()
截?cái)嘈?shù)位：trunc()
取余(取模)：mod()
執(zhí)行四舍五入操作,可以指定保留的小數(shù)位
select round(789.536) from dual;
select round(789.436) from dual;
select round(789.436,2) from dual;
可以直接對(duì)整數(shù)進(jìn)行四舍五入的進(jìn)位
select round(789.536,-3) from dual;--1000
select round(789.536,-2) from dual;--800
trunc()函數(shù)與round()函數(shù)的不同在于，trunc不會(huì)保留任何的小數(shù)位，而且小數(shù)點(diǎn)也不會(huì)執(zhí)行四舍五入的操作，也就是說在使用trunc()函數(shù)時(shí)，它會(huì)將數(shù)值從小數(shù)點(diǎn)截?cái)�，只保留整�?shù)部分。
select trunc(789.536) from dual;--789
當(dāng)然使用trunc()函數(shù)也可以設(shè)置小數(shù)位的保留位數(shù)
select trunc(789.536,2) from dual;--789.53
select trunc(789.536,-2) from dual;--700
使用mod()函數(shù)可以進(jìn)行取余的操作
select mod(10,3) from dual;--1
日期函數(shù)：
在Oracle中提供了很多余日期相關(guān)的函數(shù)，包括加減日期等。但是在日期進(jìn)行加或者減結(jié)果的時(shí)候有一些規(guī)律：
日期-數(shù)字=日期
日期+數(shù)字=日期
日期-日期=數(shù)字(天數(shù)的差值)
顯示部門10的孤雁進(jìn)入公司的星期數(shù)，要想完成此查詢，必須知道當(dāng)前的日期，在Oralce中可以通過以下的操作求出當(dāng)前日期，使用sysdate表示
select sysdate from dual;
如何求出星期數(shù)呢?使用公式：當(dāng)前日期-雇傭日期=天數(shù) / 7 = 星期數(shù)，所以
select empno,ename,round((sysdate-hiredate)/7) from emp;
在Oracle中提供了以下的日期函數(shù)支持：
months_between()-->求出給定日期范圍的月數(shù)
add_months()-->在指定日期上加上指定的月數(shù)，求出之后的日期
next_day()-->下一個(gè)的今天是哪一個(gè)日期
last_day()-->求出給定日期的最后一天日期
查詢出所有雇員的編號(hào)，姓名，和入職的月數(shù)
select empno,ename,round(months_between(sysdate,hiredate)) from emp;
查詢出當(dāng)前日期加上4個(gè)月后的日期
select add_months(sysdate,4) from dual;
查詢出下一個(gè)給定日期數(shù)
select next_day(sysdate,'星期一') from dual;
查詢給定日期的最后一天，也就是給定日期的月份的最后一天
select last_day(sysdate) from dual;
轉(zhuǎn)換函數(shù)：
to_char()-->轉(zhuǎn)換成字符串
to_number()-->轉(zhuǎn)換成數(shù)字
to_date()-->轉(zhuǎn)換成日期
我們先看看前面做的一個(gè)范例，查詢所有雇員的雇員編號(hào)，姓名和雇傭時(shí)間
select empno,ename,hiredate from emp;
但是現(xiàn)在的要求是講年、月、日進(jìn)行拆分，此時(shí)我們就需要使用to_char()函數(shù)進(jìn)行拆分，拆分的時(shí)候必須指定拆分的通配符：
年-->y，年是四位數(shù)字，所以使用yyyy表示
月-->m，月是兩位數(shù)字，所以使用mm表示
日-->d，日是兩位數(shù)字，所以使用dd表示
select empno,ename,to_char(hiredate,'yyyy') year,to_char(hiredate,'mm') month,to_char(hiredate,'dd') day from emp;
我們還可以使用to_char()進(jìn)行日期顯示的轉(zhuǎn)換功能，Oracle中默認(rèn)的日期格式是：19-4月 -87，而中國(guó)喜歡的格式是：1987-04-19
select empno,ename,to_char(hiredate,'yyyy-mm-dd') from emp;
從顯示結(jié)果中我們可以看到，對(duì)于月份和日的顯示中，如果不足10，就會(huì)自動(dòng)補(bǔ)零，哪個(gè)0我們成為前導(dǎo)0，如果不希望顯示前導(dǎo)0的話，則可以使用fm去掉
select empno,ename,to_char(hiredate,'fmyyyy-mm-dd') from emp;
當(dāng)然，to_char()也可以用在數(shù)字上
查詢?nèi)康墓蛦T編號(hào)、姓名和工資
select empno,ename,sal from emp;
我們可以看到，當(dāng)工資數(shù)比較大時(shí)，是不利于讀的，那么，我們可以在數(shù)字中使用“，”每3位進(jìn)行一個(gè)分隔，此時(shí)，就可以使用to_char()進(jìn)行格式化。格式化時(shí)，9并不代表實(shí)際的數(shù)字9，而是一個(gè)占位符
select empno,ename,to_char(sal,'99,999') from emp;
還有一個(gè)問題，工資數(shù)表示的是美元還是人民幣呢?如何解決顯示區(qū)域的問題呢?我們可以使用下面的兩種符號(hào)：
L-->表示Local的縮寫，以本地的語言進(jìn)行金額的顯示
$-->表示美元
select empno,ename,to_char(sal,'$99,999') from emp;
to_number()是可以講字符串變?yōu)閿?shù)字的函數(shù)
select to_number('123') + to_number('123') from dual;
to_date()可以講一個(gè)字符串變?yōu)閐ate的數(shù)據(jù)
select to_date('2012-09-12','yyyy-mm-dd') from dual;
通用函數(shù)：
現(xiàn)在又這樣一個(gè)需求，求出每個(gè)雇員的年薪。我們知道求年薪的話應(yīng)該加上獎(jiǎng)金的，格式為(sal+comm)*12
select empno,ename,(sal+comm)*12 from emp;
查看結(jié)果，可以發(fā)現(xiàn)一個(gè)很奇怪的顯現(xiàn)，竟然有的雇員年薪為空，這是如何引起的呢?我們分析下，首先可以查看下所有雇員的獎(jiǎng)金列數(shù)據(jù)，發(fā)現(xiàn)只有部分雇員才可以領(lǐng)取獎(jiǎng)金，沒有領(lǐng)取獎(jiǎng)金
的雇員其comm列是空的，沒有任何值(null)，由此，上面的四則運(yùn)算顯然沒結(jié)果。為了解決這個(gè)問題，我們需要用到一個(gè)通用函數(shù)nvl，將一個(gè)指定的null值變?yōu)橹付ǖ膬?nèi)容
select empno,ename,(sal+nvl(comm,0))*12 from emp;
decode()函數(shù)，此函數(shù)類似于if...elseif...else語句
其語法格式為：
decode(col/expression,search1,result1[search2,result2,......][,default])
說明：col/expression-->列名或表達(dá)式
search1.search2......-->為用于比較的條件
result1、result2......-->為返回值
如果col/expression和search i 相比較，相同的話返回result i ，如果沒有與col/expression相匹配的結(jié)果，則返回默認(rèn)值default。
select decode(1,1,'內(nèi)容是1',2,'內(nèi)容是2',3,'內(nèi)容是3') from dual;
那么，如何在對(duì)表查詢時(shí)使用decode()函數(shù)呢?我們來定義一個(gè)需求，
現(xiàn)在雇員的工作職位有：
CLERK-->業(yè)務(wù)員
SALESMAN-->銷售人員
MANAGER-->經(jīng)理
ANALYST-->分析員
PRESIDENT-->總裁
要求我們查詢出雇員的編號(hào)，姓名，雇傭日期及工作，將工作替換為上面的中文
select empno 雇員編號(hào),ename 雇員姓名,hiredate 雇傭日期,decode(job,'CLERK','業(yè)務(wù)員','SALESMAN','銷售人員','MANAGER','經(jīng)理','ANALYST','分析員','PRESIDENT','總裁') 職位 from emp;