第一課:用戶端
1. sql plus(用戶端),指令行直接輸入:sqlplus,然後按提示輸入使用者名,密碼。
2. 從開始程式運作:sqlplus,是圖形版的sqlplus.
3. [url]http://localhost:5560/isqlplus[/url]
toad:管理, plsql developer:
第二課:更改使用者
1. sqlplus sys/bjsxt as sysdba
2. alter user scott account unlock;(解鎖)
第三課:table structure
1. 描述某一張表:desc 表名
2. select * from 表名
第四課:select 語句:
1.計算資料可以用空表:比如:.select 2*3 from dual
2.select ename,sal*12 annual_sal from emp;與select ename,sal*12 "annual sal" from emp;差別,加雙引号保持原大小寫。不加全變大寫。
3. select ename || "abcd" 如果連接配接字元串中含有單引号,用兩個單引号代替一個單引号。
第五課:distinct
select deptno from emp;
select distinct deptno from emp;
select distinct deptno ,job from emp
去掉deptno,job兩者組合的重複。更多的項,就是這麼多項的組合的不重複組合。
第六課:where
select * from emp where deptno =10;
select * from emp where deptno <>10;不等于10
select * from emp where ename ='bike';
select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)
空值處理:
select ename,sal,comm from emp where comm is (not) null;
select ename,sal,comm from emp where ename ( not)in ('smith','king','abc');
select ename from emp where ename like '_a%';_代表一個字母,%代表0個或多個字母. 如果查詢%
可用轉義字元.\%. 還可以用escape '$'比如:select ename from emp where ename like '%$a%' escape '$';
第七課: orderby
select * from dept;
select * from dept order by dept desc;(預設:asc)
select ename,sal,deptno from emp order by deptno asc,ename desc;
第八課: sql function1:
select ename,sal*12 annual_sal from emp
where ename not like '_a%' and sal>800
order by sal desc;
select lower(ename) from emp;
select ename from emp
where lower(ename) like '_a%';等同于
select ename from emp where ename like '_a%' or ename like '_a%';
select substr(ename,2,3) from emp;從第二字元截,一共截三個字元.
select chr(65) from dual 結果為:a
select ascii('a') from dual 結果為:65
select round(23.652,1) from dual; 結果為: 23.7
select round(23.652,-1) from dual; 20
select to_char(sal,'$99_999_999') from emp;
select to_char(sal,'l99_999_999') from emp;人民币符号,l:代表本地符号
這個需要掌握牢:
select birthdate from emp;
顯示為:
birthdate
----------------
17-12月-80
改為:
select to_char(birthdate,'yyyy-mm-dd hh:mi:ss') from emp;
顯示:
-------------------
1980-12-17 12:00:00
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; //也可以改為:hh12
to_char(sysdate,'yy
2007-02-25 14:46:14
to_date函數:
select ename,birthdate from emp where birthdate > to_date('1981-2-20 12:34:56','yyyy-mm-dd hh24:mi:ss');
如果直接寫 birthdate>'1981-2-20 12:34:56'會出現格式不比對,因為表中的格式為: dd-mm月-yy.
select sal from emp where sal>888.88 無錯.但
select sal from emp where sal>$1,250,00;
會出現無效字元錯誤.
select sal from emp where sal>to_number('$1.250.00','$9,999,99');
把空值改為0
select ename,sal*12+nvl(comm,0) from emp;
這樣可以防止comm為空時,sal*12相加也為空的情況.
第九課: group function 組函數
max,min,avg ,count,sum函數
select to_char(avg(sal),'99999999,99') from emp;
select round(avg(sal),2) from emp;
結果:2073.21
select count(*) from emp where deptno=10;
select count(ename) from emp where deptno=10; count某個字段,如果這個字段不為空就算一個.
select count(distinct deptno) from emp;
select sum(sal) from emp;
第十課: group by語句
需求:現在想求,求每個部門的平均薪水.
select avg(sal) from emp group by deptno;
select deptno avg(sal) from emp group by deptno;
select deptno,job,max(sal) from emp group by deptno,job;
求薪水值最高的人的名字.
select ename,max(sal) from emp;出錯,因為max隻有一個值,但等于max值的人可能好幾個,不能比對.
應如下求:
select ename from emp where sal=(select max(sal) from emp);
group by語句應注意,
出現在select中的字段,如果沒出現在組函數中,必須出現在group by語句中.
第十一課: having 對分組結果篩選
where是對單條紀錄進行篩選,having是對分組結果進行篩選.
select avg(sal),deptno from emp
group by deptno
having avg(sal)>2000;
查詢工資大于1200雇員,按部門編号進行分組,分組後平均薪水大于1500,按工薪倒充排列.
select * from emp
where sal>1200
having avg(sal)>1500
order by avg(sal) desc;
第十二課:字查詢
誰掙的錢最多(誰:這個人的名字, 錢最多)
select 語句中嵌套select 語句,可以在where,from後.
問那些人工資,在平均工資之上.
select ename,sal from emp where sal>(select avg(sal) from emp);
查找每個部門掙錢最多的那個人的名字.
select ename ,deptno from emp where sal in(select max(sal) from ename group by deptno) 查詢會多值.
應該如下:
select max(sal),deptno from emp group by deptno;當成一個表.語句如下:
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);
每個部門的平均薪水的等級.
分析:首先求平均薪水(當成表),把平均薪水和另外一張表連接配接.
第十四課:self_table_connection
把某個人的名字以及他的經理人的名字求出來(經理人及這個人在表中同處一行)
分析:首先求出這個人的名字,取他的編号,然後從另一張表與其相對應編号,然後找到經理的名字.
select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.
empno編号和mgr都是編号.
第十15課: sql1999_table_connections
select ename,dname,grade from emp e,dept d, sqlgrade s
where e.deptno = d.deptno and e.sql between s.losal and s.hisal and
job<>'clerk';
有沒有辦法把過濾條件和連接配接條件分開來? 出于這樣考慮,sql1999标準推出來了.有許多人用的還是
舊的文法,是以得看懂這種語句.
select ename,dname from emp,dept;(舊标準).
select ename,dname from emp cross join dept;(1999标準)
select ename,dname from emp,dept where emp.deptno=dept.deptno (舊)
select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999标準.沒有where語句.
select ename,dname from emp join dept using(deptno);等同上句,但不推薦使用.
select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);
join 連接配接語句, on過濾條件。連接配接,條件一眼分開。如果用where語句較長時,連接配接語句和過濾語句混在一起。
三張表連接配接:
slect 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%';
把每張表連接配接 條件不混在一起,然後資料過濾條件全部區分開來。讀起來更清晰,更容易懂一點。
select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);
左外連接配接:會把左邊這張表多餘資料顯示出來。
select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 後可加outer
右外連接配接:
select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。
即把左邊多餘資料,也把右邊多餘資料拿出來,全外連接配接。
select ename,dname from emp e full join dept d on(e.deptno =d.deptno);
16-23 課:求部門平均薪水的等級
a.求部門平均薪水的等級。
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)
b.求部門平均的薪水等級
select deptno,avg(grade) from
(select deptno,ename, grade from emp join salgrade s on(emp.sal between s.losal and
s.hisal)) t
c.那些人是經理
select ename from emp where empno in(select mgr from emp);
select ename from emp where empno in(select distinct mgr from emp);
d.不準用組函數,求薪水的最高值(面試題)
select distinct sal from emp where sal not in(
select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));
e.平均薪水最高的部門編号
select deptno,avg_sal from
(select avg(sal)avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal)from
(select avg(sal) avg_sal,deptno from emp group by deptno)
)
f.平均薪水最高的部門名稱
select dname from dept where deptno=
(
select deptno from
(select avg(sal)avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal)from
(select avg(sal) avg_sal,deptno from emp group by deptno)
)
g.求平均薪水的等級最低的部門的部門名稱
組函數嵌套
如:平均薪水最高的部門編号,可以e.更簡單的方法如下:
select deptno,avg_sal from
where avg_sal =
(select max(avg(sal)) from emp group by deptno)
組函數最多嵌套兩層
分析:
首先求
1.平均薪水: select avg(sal) from group by deptno;
2.平均薪水等級: 把平均薪水當做一張表,需要和另外一張表連接配接salgrade
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)
上面結果又可當成一張表。
deptno grade avg_sal
-------- ------- ----------
30 3 1566.66667
20 4 2175
10 4 2916.66667
3.求上表平均等級最低值
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.hisa)
)
4.把最低值對應的2結果的那張表的對應那張表的deptno, 然後把2對應的表和另外一張表做連接配接。
select dname ,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 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)
)
)
結果如下:
dname deptno grade avg_sal
-------- ------- -------- --------
sales 30 3 1566.6667
h: 視圖(視圖就是一張表,一個字查詢)
g中語句有重複,可以用視圖來簡化。
conn sys/bjsxt as sysdba;
grant create table,create view to scott;
conn scott/tiger
建立視圖:
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 9t.avg_sal between s.losal and s.hisal)
然後
select * from v$_dept_avg-sal_info
結果如下:
deptno grade avg_sal
然後g中查詢可以簡化成:
select dname,t1.deptno,grade,avg_sal from
v$_dept_avg-sal_info t1
join dept on9t1.deptno =dept.deptno)
where t1.grade=
(
select min(grade) from v$_dept_avg-sal_info t1