尚學堂馬士兵oracle筆記53節(jié)完整版

1、尚學堂馬士兵老師oracle筆記 (2008-10-30 10:17:39) 第一課：客戶端        1. Sql Plus(客戶端），命令行直接輸入：sqlplus，然后按提示輸入用戶名，密碼。        2. 從開始程序運行:sqlplus，是圖形版的sqlplus.        3. http:/localhost:5560/isqlplus  &#

2、160;            Toad：管理， PlSql Developer:第二課：更改用戶        1. sqlplus sys/密碼 as sysdba        2. alter user scott account unlock;(解鎖賬號)第三課：table structure    

3、;   (系統(tǒng)自帶的表有emp、salgrade、dept、bonus、dual)        1. 描述某一張表：desc 表名        2. select * from 表名第四課：select 語句：       1.計算數(shù)據(jù)可以用空表：比如：.select 2*3 from dual      

4、; 2.select ename,sal*12 annual_sal from emp;與select ename,sal*12 "annual sal" from emp;區(qū)別：加雙引號保持原大小寫，不加全變大寫。任何含有空值的數(shù)學表達式結(jié)果都為空值。             3. select ename | abcd | 用來連接兩個字符串如果連接字符串中含有單引號，用兩個單引號代替一個單引號。第五課：distinct  

5、0;     select deptno from emp;        select distinct deptno from emp;        select distinct deptno ,job from emp        去掉deptno,job兩者組合的重復。更多的項，就是這么多項的組合的不重復組合。第六課：Where

6、60;       select * from emp where deptno =10;        select * from emp where deptno <>10;不等于10               select * from emp where ename ='bike' 

7、;       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;     

8、0;  select ename,sal,comm from emp where ename ( not)in ('smith','king','abc');模糊查詢 like ：%代表任意數(shù)量的任意字符_代表一個任意字符        select ename from emp where ename like '_A%'如果要查詢含有%的，要用轉(zhuǎn)義字符 轉(zhuǎn)義字符可以自定義： escape '自定義的轉(zhuǎn)義字符'比如:select en

9、ame from emp where ename like '%$a%' escape '$'第七課: order by                select * from dept;默認按升序(asc)排列，要按降序(desc)用如下語句：         select * from dept order by dept des

10、c;         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 &#

11、39;_A%' and sal>800        order by sal desc;        select lower(ename) from emp;        select ename from emp        where lower(ename) like '_a%&#

12、39;等同于        select ename from emp where ename like '_a%' or ename like '_A%'        select substr(ename,2,3) from emp;從第二個字符開始截,一共截三個字符.        select chr(65) from dual 結(jié)果為:A

13、60;       select ascii('a') from dual 結(jié)果為:65        select round(23.652,1) from dual; 結(jié)果為: 23.7（第二個參數(shù)為指定四舍五入到哪位數(shù)）        select round(23.652,-1) from dual; 20     

14、0;   select to_char(sal,'$99，999，999') from emp;（用9就可以在沒有數(shù)字的地方不顯示，如果用0的話一定會用0填充滿）        select to_char(sal,'L99_999_999') from emp; L:代表本地符號        這個需要掌握牢:        sele

15、ct hiredate from emp;        顯示為:        BIRTHDATE        -        17-12月-80        -      

16、0; 改為:        select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;               顯示:                BIRTHDAT

17、E        -        1980-12-17 12:00:00        -               select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dua

18、l; /也可以改為:HH12        TO_CHAR(SYSDATE,'YY        -        2007-02-25 14:46:14               to_date函數(shù):   &

19、#160;    select ename,hiredate from emp where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');        如果直接寫 birthdate>'1981-2-20 12:34:56'會出現(xiàn)格式不匹配,因為表中的格式為: DD-MM月-YY.      

20、          select sal from emp where sal>888.88 無錯.但        select sal from emp where sal>$1,250,00;        會出現(xiàn)無效字符錯誤.        改為:  

21、0;     select sal from emp where sal>to_number('$1.250.00','$9,999,99');               把空值改為0        select ename,sal*12+nvl(comm,0) from emp;  &#

22、160;     作用：把comm為空的地方用0代替，這樣可以防止comm為空時,sal*12相加也為空的情況.第九課: Group function 組函數(shù)（即從多行中得到一個輸出）牢記組函數(shù)：max(), min(), avg(), sum(), count()               select to_char(avg(sal),'99999999,99') from emp; 

23、                     select round(avg(sal),2) from emp;        結(jié)果:2073.21               &#

24、160; 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)

25、from emp;第十課: Group by語句注意：count() 是計數(shù)不是空值的數(shù)量        需求:現(xiàn)在想求每個部門的平均薪水.        select avg(sal) from emp group by deptno;        select deptno, avg(sal) from emp group by deptno;   &

26、#160;           select deptno,job,max(sal) from emp group by deptno,job;              求薪水值最高的人的名字.       select ename,max(sal) from emp;出錯,因為max只有一個值,但等于ma

27、x值的人可能好幾個,不能匹配.       應(yīng)如下求:       select ename from emp where sal=(select max(sal) from emp);       Group by語句應(yīng)注意,       出現(xiàn)在select中的字段,如果沒出現(xiàn)在組函數(shù)中,必須出現(xiàn)在Group by語句中.   

28、;         第十一課: Having 對分組結(jié)果篩選             Where是對單條紀錄進行篩選,Having是對分組結(jié)果進行篩選.            select avg(sal),deptno from emp    &#

29、160;  group by deptno       having avg(sal)>2000;             查詢工資大于1200雇員,按部門編號進行分組,分組后平均薪水大于1500,按工薪倒充排列.       select avg(sal) from emp     

30、0; where sal>1200       group by deptno       having avg(sal)>1500       order by avg(sal) desc;          第十二課:子查詢       

31、60;     誰掙的錢最多(誰:這個人的名字,  錢最多)             select 語句中嵌套select 語句,可以在where,from后.                      

32、60;  問那些人工資,在平均工資之上.             select ename,sal from emp where sal>(select avg(sal) from emp);       查找每個部門掙錢最多的那個人的名字.       select ename ,deptno from emp where sal in (

33、select max(sal) from ename group by deptno) 查詢會多值.       應(yīng)該如下:             把select  max(sal),deptno from emp group by deptno;當成一個表.語句如下:       select ename, sal from emp join(

34、select  max(sal) max_sal,deptno from emp group       by deptno) t on (emp.sal=t.max_sal and emp.deptno=t.deptno);             每個部門的平均薪水的等級.       分析:首先求平均薪水(當成表),把平均薪水和另外一張表連接

35、.            第十四課:self_table_connection             把某個人的名字以及他的經(jīng)理人的名字求出來(經(jīng)理人及這個人在表中同處一行)             分析:首先求出這個人的名字,取他的編號

36、,然后從另一張表與其相對應(yīng)編號,然后找到經(jīng)理的名字.             select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.             empno編號和MGR都是編號.第十15課: SQL1999_table_connections  

37、         select ename, dname,grade from emp e,dept d, salgrade swhere e.deptno = d.deptno and e.sal between s.losal and s.hisal andjob <> 'CLERK'           有沒有辦法把過濾條件和連接條件分開來? 出于這樣考慮,Sql1999標

38、準推出來了.有許多人用的還是      舊的語法,所以得看懂這種語句.                     select ename,dname from emp,dept;(舊標準).      select ename,dname from emp cross join dept;(1999

39、標準)            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

40、 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語句較長時，連接語句和過濾語句混在一起。   

41、0;       三張表連接：      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 enam

42、e not like '_A%'      把每張表連接 條件不混在一起，然后數(shù)據(jù)過濾條件全部區(qū)分開來。讀起來更清晰，更容易懂一點。           select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);      左外連接：會把左邊這張表多余數(shù)據(jù)顯示出來。  

43、0;   select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer      右外連接：把右邊這張表多余數(shù)據(jù)顯示出來。    select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。      

44、60;      全外連接： 即把左邊多余數(shù)據(jù)，也把右邊多余數(shù)據(jù)拿出來，       select ename,dname from emp e full join dept d on(e.deptno =d.deptno);PS：所謂的“外”連接，即把多余的數(shù)據(jù)顯示出來。Outer關(guān)鍵字可以省略16-23 求部門中哪些人的薪水最高：select ename, sal from empjoin (select max(sal) max_sal, deptno from emp group by

45、deptno) ton (emp.sal = t.max_sal and emp.deptno = t.deptno)       A.求部門平均薪水的等級。       select deptno,avg_sal,grade from       (select deptno,avg(sal) avg_sal from emp group by deptno)t    

46、60;  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.

47、hisal) tgroup by deptno       C.那些人是經(jīng)理       select ename from emp where empno in(select mgr from emp);       更高效率的寫法：select ename from emp where empno in(select distinct mgr from emp);    &

48、#160;        D.不準用組函數(shù)，求薪水的最高值（面試題）             select distinct sal from emp where sal not in(       select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal); 

49、            E.平均薪水最高的部門編號             select deptno,avg_sal from       (select avg(sal)avg_sal,deptno from emp group by deptno)    

50、0;  where avg_sal=       (select max(avg_sal)from       (select avg(sal) avg_sal,deptno from emp group by deptno)       )            F.平均薪水最高的部門名稱

51、0;      select dname from dept where deptno=      (        select deptno from        (select avg(sal)avg_sal,deptno from emp group by deptno)      

52、60; where avg_sal=        (select max(avg_sal)from        (select avg(sal) avg_sal,deptno from emp group by deptno)        )       )     

53、60;              組函數(shù)嵌套        如：平均薪水最高的部門編號，可以用更簡單的方法如下：        select deptno,avg_sal from        (select avg(sal) avg_sal,deptno

54、from emp group by deptno)        where avg_sal =        (select max(avg(sal) from emp group by deptno)               組函數(shù)最多嵌套兩層      G.求

55、平均薪水的等級最低的部門的部門名稱        分析：        首先求        1.平均薪水： select avg(sal) from group by deptno;        2.平均薪水等級：  把平均薪水當做一張表，需要和另外一張表連接salgrade  

56、60;     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)   

57、            上面結(jié)果又可當成一張表。               DEPTNO    GRADE    AVG_SAL      -  -  -     &#

58、160;  30           3   1566.66667        20           4   2175        10     

59、60;     4   2916.66667        3.求上表平均等級最低值               select min(grade) from        (       

60、   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)    &

61、#160;    )        4.把最低值對應(yīng)的2結(jié)果的那張表的對應(yīng)那張表的deptno, 然后把2對應(yīng)的表和另外一張表做連接。         select dname ,t1.deptno,grade,avg_sal from(select deptno,grade,avg_sal from (select deptno,avg(sal) avg_sal from emp group by dep

62、tno)t join salgrade s on(t.avg_sal between s.losal and s.hisal) ) t1join 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)

63、60;        結(jié)果如下：               DNAME  DEPTNO  GRADE  AVG_SAL      -  -  -   -      &#

64、160;SALES        30        3    1566.6667                   H: 視圖（視圖就是一張表，一個子查詢）       

65、60;      G中語句有重復，可以用視圖來簡化?！灸Jscott賬戶沒有創(chuàng)建視圖的權(quán)限，可通過如下語句授權(quán)：       首先登陸超級管理員：conn sys/bjsxt as sysdba;       授權(quán)：grant create table,create view to scott;       再以scott賬戶登陸：conn scott/tiger】&

66、#160;      創(chuàng)建視圖：       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  

67、0;    join salgrade s on 9t.avg_sal between s.losal and s.hisal)            然后G中查詢可以簡化成：       select  dname,t1.deptno,grade,avg_sal from       v$_dept_avg-sal_info

68、t1       join dept on (t1.deptno =dept.deptno)       where t1.grade =       (select min(grade) from v$_dept_avg-sal_info t1)24、-求比普通員工最高薪水還要高的經(jīng)理人的名稱 - select ename, sal from emp where empno in (select distinct

69、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)25、-面試題：比較效率（理論上前一句效率高，但實際上可能Oracle可能會自動對代碼優(yōu)化，所以不見得后一句就會慢）select * from emp where deptno = 10 and ename like '%A%' /效率高，因為將過濾力度大的放在前面 select * fr

70、om emp where ename like '%A% and deptno = 10;-以上為select 語句的內(nèi)容-創(chuàng)建新用戶-1、backup scott/備份exp/導出2、create usercreate user guohailong identified(認證) by guohailong default tablespace users quota(配額) 10M on usersgrant create session(給它登錄到服務(wù)器的權(quán)限),create table, create view to guohailong3、import dataImp25、-

71、取消操作-rollback-事務(wù)確認語句-commit;/此時再執(zhí)行rollback無效當正常斷開連接的時候例如exit,事務(wù)自動提交。 當非正常斷開連接，例如直接關(guān)閉dos窗口或關(guān)機，事務(wù)自動提交-表的備份 create table dept2 as select * from dept;-插入數(shù)據(jù) insert into dept2 values(50,'game','beijing'); -只對某個字段插入數(shù)據(jù)insert into dept2(deptno,dname) values(60,'game2');-將一個表中的數(shù)據(jù)完全插入另一

72、個表中（表結(jié)構(gòu)必須一樣）insert into dept2 select * from dept;-求前五名員工的編號和名稱（使用偽字段rownum 只能使用 < 或 = 要使用 > 必須使用子查詢）select empno,ename from emp where rownum <= 5;-求10名雇員以后的雇員名稱-select ename from (select rownum r,ename from emp) where r > 10;-求薪水最高的前5個人的薪水和名字-select ename, sal from (select ename, sal fro

73、m emp order by sal desc) where rownum <=5;-求按薪水倒序排列后的第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-面試題： 有3個表S，C，SC S（SNO，SNAME）代表（學號，姓名） C（CNO，CNAME，CTEACHER）代表（課號，課名，教師） SC（SNO，CNO，SCGRADE）代表

74、（學號，課號成績） 問題： 1，找出沒選過“黎明”老師的所有學生姓名。 2，列出2門以上（含2門）不及格學生姓名及平均成績。 3，即學過1號課程有學過2號課所有學生的姓名。答案：1、 select sname from s join sc on(s.sno = sc.sno) join c on (o = o) where cteacher <> '黎明'2、 select sname where sno in (select sno from sc where scgrade < 60 group by sno having count(*) >=2)

75、;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); )27、-創(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

76、(4), email varchar2(50) unique);28、五種約束條件：非空約束、唯一約束、主鍵約束、外鍵約束、檢查約束（check）-給name字段加入 非空 約束，并給約束一個名字，若不取，系統(tǒ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)

77、;-給nameemail字段加入 唯一 約束 兩個 null值 不為重復- 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);-兩個字段的組合不能重復 約束：表級約束- create table stu ( id number(6), name varch

78、ar2(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);29、-主鍵約束- create table stu ( id number(6), name varchar2(20) constraint stu_name_mm not null, sex number

79、(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); -外鍵約束 被參考字段必須是主鍵 - create table stu ( id number(6), name varchar2(20) constraint stu_name_mm not null, sex number(1)

80、, 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);31、修改表結(jié)構(gòu)：-添加字段-alter table stu a