oracle最全面的錯誤疑難解決方案和總結(jié)文檔

1、.oracle itcast本文檔對應(yīng)程序在myeclipse的jdbc/src/下分頁查詢中的rownum可以方便刪除重復(fù)記錄等各種方便查詢Oracle 安裝自動生成sys用戶和system用戶sys 超級用戶 具有最高權(quán)限 具有sysDBA角色，有create database權(quán)限該用戶默認(rèn)密碼是change_in_install system 管理操作員 權(quán)限也比較大，具有sysoper角色，沒有create database權(quán)限。該用戶默認(rèn)密碼是 manager這是通過sqlplus客戶端連接數(shù)據(jù)庫時(shí)有多個實(shí)例 采用下面DOS命令：sqlplus scott/tigerzhulin見2

2、.13 oracle創(chuàng)建數(shù)據(jù)庫實(shí)例啟動sqlplus，然后登陸數(shù)據(jù)庫出現(xiàn)錯誤:TNS:協(xié)議適配器錯誤原因有3個：1.監(jiān)聽服務(wù)沒有啟動：services.msc或開始>程序>管理工具>服務(wù)，打開服務(wù)面板：啟動oraclehome92TNSlistener服務(wù)2.database instance沒有啟動：services.msc或開始>程序>管理工具>服務(wù) 啟動oralceserviceXXX,XXX就是你databaseSID如zhulin3.注冊表問題：regedit.msc進(jìn)入HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_Ora

3、Db11g_home1 下的ORACLE_SID值修改為zhulin你的全局?jǐn)?shù)據(jù)庫名字 你的數(shù)據(jù)庫SID即可。ORACLE用SYS和SYSTEM默認(rèn)密碼登錄提示ORA-01017:invalid username/password;logond denied該怎么解決？解決辦法：有可能是你在建數(shù)據(jù)庫的時(shí)候,修改了默認(rèn)的密碼而自己又忘記你可再重新修改過來sqlplus / as sysdbaalter user system identified by manager;alter user sys identified by manager;或者改成其他的你自己容易記住的默認(rèn)scott用戶密碼是

4、tiger1.oralce解鎖步驟先使用system登錄然后輸入alter user scott account unlock； /解鎖scott賬號SQL語句必須帶分號!最好都分號結(jié)束2.oralce開發(fā)工具sqlpulsw和sqlus工具在開始程序oracle oradb_home10gapplication developmentsqlplus或在運(yùn)行欄輸入sqlpluspl/sql developer 這款軟件用的很多 第三方軟件 需要單獨(dú)安裝企業(yè)管理器(web) 首先保證相關(guān)服務(wù)啟動即oracleDBconsole+實(shí)例名啟動在瀏覽器中輸入http:/ip:1158/em ip是指你

5、的具體ip地址或者你的機(jī)器名 1158是端口一般情況下 這個服務(wù)是不啟動 很不安全3.oracle常用sql plus命令 (1)請使用scott用戶登錄oracle數(shù)據(jù)庫實(shí)例，然后切換為身份為system簡單使用 conn 用戶名/密碼登錄后，使用 connect 用戶名/密碼網(wǎng)絡(luò) as sysdba/sysoper(2)show user 顯示當(dāng)前用戶名(3) 斷開連接 disconnect(4)exit 斷開連接和退出sqlplus窗口(5) 修改密碼(前提是system或sys用戶) password 基本用法 password 用戶名如果給自己修改密碼 則可以不帶用戶名如果給別人修改

6、密碼 則需要帶用戶名(6) & 交互命令 可以替代變量的值select * from emp where job=”&job”;(7)edit 用于編輯腳本(文本) SQL>edit d:(8)spool 把屏幕上顯示的記錄，保存到文件中spool onspool d:/bak.sql查詢語句spool off(9)linesize 用戶控制每行顯示多少個字符，默認(rèn)80個字符 每次都要重新設(shè)置基本用法： set linesize 120(10)pagesize 用于每頁顯示多少行 基本用法： set pagesize 1004.oracle用戶管理(1)創(chuàng)建用戶 只有具有

7、DBA權(quán)限才能創(chuàng)建比如system sys基本用法：create user 用戶名 identified by 密碼舉例:create user xiaoqiang identified by hao200881037oracle要求用戶密碼不能用數(shù)字開頭后面我將密碼修改為了200881037？為什么創(chuàng)建的用戶無法登陸這是因?yàn)閛racle 剛剛創(chuàng)建的用戶是沒有任何權(quán)限，需要管理員給用戶分配適應(yīng)的權(quán)限，才能夠登陸grant create session to xiaoqiang /會話權(quán)限(1)權(quán)限系統(tǒng)權(quán)限：和數(shù)據(jù)庫管理相關(guān)的權(quán)限：create session;create table;crea

8、te index;create view;create sequence;create trigger對象權(quán)限：和用戶操作數(shù)據(jù)對象相關(guān)的權(quán)限：update;insert;delete;select(2)角色預(yù)定義角色：把常用的權(quán)限集中起來，形成角色(套餐)比如dba connect resource 三種角色自定義角色：自己定義套餐(3)方案(schema)在一個數(shù)據(jù)庫實(shí)例下：當(dāng)一個用戶，創(chuàng)建好后，如果該用戶創(chuàng)建了任意一個數(shù)據(jù)對象(表或觸發(fā)器等),這時(shí)我們的DBMS就會創(chuàng)建一個對應(yīng)的方案與該用戶對應(yīng)，并且該方案名字和用戶名一致。小技巧：如果希望看到某個用戶的方案的數(shù)據(jù)對象，可以使用PL/SQL

9、 developer工具案例1：完成一個功能；讓xiaoqiang用戶去查詢scott的emp表步驟1：先用scott登錄conn scott/tiger步驟2：在scott賬號上給xiaoqiang賦權(quán)限grant selectupdate|delete|insert|all on emp to xiaoqiang這里就可以看出來 方案A和方案B可以有相同名的數(shù)據(jù)庫，但是方案A中不可以有相同名的數(shù)據(jù)庫步驟3：登錄xiaoqiang用戶去查詢emp表錯誤用法：select * from emp 原因是在xiaoqiang登錄狀態(tài)下需要制定emp表來自哪里？正確用法：select * from

10、scott.emp;查詢時(shí)如果查詢其他方案 一定要用帶上其他方案名。如果不帶，就默認(rèn)是select * from xiaoqiang.emp案例2：完成一個功能想辦法將xiaoqiang擁有的對scott.emp的權(quán)限轉(zhuǎn)給stu用戶。scott>xiaoqiang>stu權(quán)限轉(zhuǎn)移conn scott/tiger;grant all on scott.emp to stu with grant option;/with grant option 對象權(quán)限 表示得到權(quán)限的用戶可以把權(quán)限繼續(xù)分配/with admin option系統(tǒng)權(quán)限 如果是系統(tǒng)權(quán)限，則帶with admin ipt

11、ion創(chuàng)建了普通賬戶 xiaoqiang 密碼hao200881037修改密碼(前提是system或sys用戶) password 基本用法 password 用戶名如果給自己修改密碼 則可以不帶用戶名如果給別人修改密碼 則需要帶用戶名表空間：表存在的空間，一個表空間就是指向具體的數(shù)據(jù)文件(4)用戶管理的綜合案例創(chuàng)建的新用戶是沒有任何權(quán)限的，甚至連登錄(會話)的數(shù)據(jù)庫的權(quán)限都沒有，需要為其指定響應(yīng)的權(quán)限，給一個用戶賦權(quán)限使用命令grant，回收權(quán)限r(nóng)evokegrant 權(quán)限/角色 to 用戶(1) 使用system創(chuàng)建xiaoqiang后面我將密碼修改為了200881037(2) 使用sys

12、tem給小紅分配2個常用角色grant connect to xiaoqianggrant resource to xiaoqiangdisconn /切斷連接(3) 讓xiaoqiang登錄conn xiaoqiang/200881037(4) xiaoqiang修改密碼 pasword xiaoqiang即可 然要求你輸入舊密碼 當(dāng)然 超級管理員不需要輸入舊密碼(5) xiaohong創(chuàng)建一張最簡單的表(6) 使用system登錄，然后回收角色。revoke connect from xiaoqiangrevoke resource from xiaoqiang(7) 刪除xiaoqian

13、g用戶:drop user 用戶名cascade當(dāng)我們刪除一個用戶的時(shí)候，若這個用戶自己已經(jīng)創(chuàng)建了數(shù)據(jù)對象(表、觸發(fā)器等)，需要加選項(xiàng)cascade表示把這個用戶刪除同時(shí)，把該用戶創(chuàng)建的數(shù)據(jù)對象一并刪除。否則無法刪除該用戶，oracle用戶認(rèn)為刪除了該用戶就徹底拋棄了(5)賬號鎖定使用profile管理用戶口令，賬號鎖定指用戶登錄時(shí)最多可以輸入密碼的次數(shù)，也可以指定用戶鎖定的時(shí)間(天)一般用DBA的身份去執(zhí)行該命令。 profile文件規(guī)則eg:create profile lock_account limit failed_login_attempts 3 paswword_lock_tim

14、e 2;alter user tea profile lock_account;/其中l(wèi)ock_account是文件名(6)賬號解鎖alter user 用戶名 account unlock;(7)終止口令eg:給tea創(chuàng)建一個profile文件，要求該用戶每隔10天必須修改自家的登錄密碼，款限期為2天。create profile myprofile limit password_life_time 10 password_grace_time 2;/可以繼續(xù)加限制條件alter user tea profile myprofile;(8)刪除profile文件當(dāng)不需要某個profile文件

15、時(shí)，可以刪除該文件。drop profile profile文件名。5.oracle數(shù)據(jù)庫啟動流程oracle可以通過命令行的方式啟動，我們看看具體如何操作：windows下:(1)lsnrctl start (啟動監(jiān)聽)(2)oradim startup sid 數(shù)據(jù)庫實(shí)例名linux下:(1) lsnrctl start (啟動監(jiān)聽)(2) sqlplus sys/chang_on_install as sysdba(以sysdba身份登錄)sqlplus /nologconn sys/chang_on_install as sysdba(3)startup 6.oracle登錄認(rèn)證方式o

16、racle在windows和linux下是不完全相同的：windows下:如果當(dāng)前用戶屬于本地操作系統(tǒng)的ora_dba組(對于windows操作系統(tǒng)而言)，即可通過操作系統(tǒng)認(rèn)證。普通用戶:默認(rèn)是以數(shù)據(jù)庫方式認(rèn)證，比如conn scott/tiger;特權(quán)用戶：默認(rèn)是以操作系統(tǒng)認(rèn)證（即:只要當(dāng)前用戶是在ora_dba組中則可以通過認(rèn)證），比如conn system/manager as sysdba;DBMS一看到as sysdba則認(rèn)為 要以特權(quán)用戶登錄，前面的用戶名和密碼不看，登錄后自動切換成sys用戶<=>conn sys/manager。如果當(dāng)前用戶（win7系統(tǒng)賬號）不在o

17、ra_dba組中，conn sys/manager 輸對了密碼還是可以登錄進(jìn)去的(這時(shí)是采用了數(shù)據(jù)庫方式驗(yàn)證)sqlnet.ora文件在D:xiaoqiangoracleproduct11.2.0dbhome_1NETWORKADMIN目錄下：同時(shí)如果你安裝第三方工具PL/SQL Developer，同時(shí)也需要修改D:xiaoqiangoracleproductinstantclient_11_2目錄下的sqlnet.ora文件通過配置sqlnet.ora文件，可以修改oracle登錄認(rèn)證方式:SQLNET.AUTHENTICATION_SERVICES=(NTS)是基于操作系統(tǒng)驗(yàn)證SQLNE

18、T.AUTHENTICATION_SERVICES=(NONE)是基于Oracle驗(yàn)證SQLNET.AUTHENTICATION_SERVICES=(NONE,NTS)是二者共存linux下:默認(rèn)情況下linux下的oracle數(shù)據(jù)庫sqlnet.ora文件沒有SQLNET.AUTHENTICATION_SERVICES參數(shù)，此時(shí)是基于操作系統(tǒng)認(rèn)真和oracle密碼驗(yàn)證共存，加上SQLNET.AUTHENTICATION_SERVICES參數(shù)后，不管SQLNET.AUTHENTICATION_SERVICES設(shè)置為NONE還是NTS都是基于oracle密碼驗(yàn)證。7.oracle丟失管理員密碼怎

19、么辦數(shù)據(jù)庫實(shí)例名是根據(jù)實(shí)際情況命名的。恢復(fù)辦法：把原有密碼文件刪除，生成一個新的密碼文件恢復(fù)步驟如下：(1)搜索名為PWD數(shù)據(jù)庫實(shí)例名.ora文件 (2)刪除該文件，為以防萬一，建議備份 (3)生成新的密碼文件，在DOS控制臺下輸入命令orapwd file=原來密碼文件的全路徑密碼文件名.ora password=新密碼 entries=10;這里密碼文件名是原來的密碼文件名=PWD數(shù)據(jù)庫實(shí)例名entries 表示登錄sys的最多用戶(特權(quán)用戶)如果希望新的密碼生效，則需要重新啟動數(shù)據(jù)庫實(shí)例服務(wù).dos下services.exe還有出現(xiàn)以下情況：ORACLE用SYS和SYSTEM默認(rèn)密碼登錄

20、提示ORA-01017:invalid username/password;logond denied該怎么解決？解決辦法：有可能是你在建數(shù)據(jù)庫的時(shí)候,修改了默認(rèn)的密碼而自己又忘記你可再重新修改過來sqlplus / as sysdbaalter user system identified by manager;alter user sys identified by manager;或者改成其他的你自己容易記住的默認(rèn)scott用戶密碼是tiger8.oracle表管理 類(對象)和表(記錄)之間的關(guān)系創(chuàng)建表基本語法create table table_name(列名 列類型,)數(shù)據(jù)類型 c

21、har(size) 存放字符串 最大2000個字符，是定長eg：char(32) 最多只能放入32個字符 如果超過 就報(bào)錯，如果不夠abc則用空格補(bǔ)全 varchar2(size) 變長 最大可以存放4000個字符 nchar(size) 定長 編碼方式unicode 最大字符數(shù)是2000個 一個漢字占用nchar的一個字符空間，一個漢字，占用char的兩個字符空間 nvarchar2(size) 變長 編碼方式unicode最大字符數(shù)是4000個 clob 字符型大對象 變長 最大8TB blob 變長 說明：我們在實(shí)際開發(fā)中很少把文件存放在數(shù)據(jù)庫中(效率問題)，實(shí)際上我們一般記錄文件的一個

22、路徑(URL或本地路徑)，然后通過IO或網(wǎng)絡(luò)來操作。如果我們要求對文件安全性比較高，可以考慮放入數(shù)據(jù)庫。 number(p,s) p為整數(shù)位，s為小數(shù)位，范圍是1<=p<=38,-84<=s<=-127 變長保存數(shù)據(jù)范圍：-1.0e-130<=number value<=1.0e+126 保存機(jī)器位數(shù)1-22bytee.g number(5,2) 表示一個小數(shù)有5位有效位，2位小數(shù)，范圍-999,99-999,99比如你輸入 573.316則真正保存是573.32,無法保存數(shù)據(jù)1000number(5)等價(jià)于number(5,0)，表示一個5位整數(shù)，范圍-9

23、9999-99999，輸入57523.316則保存57523原則：如果在做實(shí)際開發(fā)中，我們沒有指定數(shù)據(jù)小數(shù)位，則直接使用numberdate 日期類型包含年月日，時(shí)分秒插入數(shù)據(jù)時(shí)要使用默認(rèn)格式是:'dd-mm-yyy'當(dāng)然 如果用自己格式需要借用to_date函數(shù)SQL> insert into test1 values(to_date('2005-11-11','YYYY-MM-DD');1 row insertedto_char你可以使用select ename, hiredate, sal from emp where deptno

24、= 10;顯示信息，可是，在某些情況下，這個并不能滿足你的需求。 問題：日期是否可以顯示 時(shí)/分/秒 SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp; 9.oracle基本查詢oracle的crud操作(create retrieve/read update delete)添加一個字段SQL>ALTER TABLE student add (classId NUMBER(2); 修改一個字段的長度 SQL>ALTER TABLE student modify (xm

25、VARCHAR2(30); 修改字段的類型/或是名字（不能有數(shù)據(jù)） 不建議做 SQL>ALTER TABLE student modify (xm CHAR(30); 刪除一個字段  不建議做(刪了之后，順序就變了。加就沒問題，應(yīng)為是加在后面)SQL>ALTER TABLE student DROP COLUMN sal; 修改表的名字   很少有這種需求 SQL>RENAME student TO stu; 刪除表 SQL>DROP TABLE student; 添加數(shù)據(jù) 所有字段都插入數(shù)據(jù)INSERT INTO student VALU

26、ES ('A001', '張三', '男', '01-5月-05', 10); oracle中默認(rèn)的日期格式dd-mon-yy dd日子（天） mon 月份  yy  2位的年  09-6月-99 1999年6月9日 修改日期的默認(rèn)格式（臨時(shí)修改，數(shù)據(jù)庫重啟后仍為默認(rèn)；如要修改需要修改注冊表） ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd' 修改后，可以用我們熟悉的格式添加日期類型： INSERT INTO student VALUES

27、('A002', 'MIKE', '男', '1905-05-06', 10); 插入部分字段INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女'); 插入空值INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null); 問題來了，如果你要查詢student表里birth

28、day為null的記錄，怎么寫sql呢？ 錯誤寫法：select * from student where birthday = null; 正確寫法：select * from student where birthday is null; 如果要查詢birthday不為null,則應(yīng)該這樣寫： select * from student where birthday is not null; 修改數(shù)據(jù) 修改一個字段UPDATE student SET sex = '女' WHERE xh = 'A001' 修改多個字段UPDATE student SET s

29、ex = '男', birthday = '1984-04-01' WHERE xh = 'A001' 修改含有null值的數(shù)據(jù) 不要用 = null 而是用 is null； SELECT * FROM student WHERE birthday IS null; 刪除數(shù)據(jù)DELETE FROM student; 刪除所有記錄，表結(jié)構(gòu)還在，寫日志，可以恢復(fù)的，速度慢。 Delete 的數(shù)據(jù)可以恢復(fù)。 savepoint a; -創(chuàng)建保存點(diǎn) DELETE FROM student; rollback to a;  -恢復(fù)到保存點(diǎn) 一個

30、有經(jīng)驗(yàn)的DBA，在確保完成無誤的情況下要定期創(chuàng)建還原點(diǎn)。 DROP TABLE student; -刪除表的結(jié)構(gòu)和數(shù)據(jù)； delete from student WHERE xh = 'A001' -刪除一條記錄； truncate TABLE student; -刪除表中的所有記錄，表結(jié)構(gòu)還在，不寫日志，無法找回刪除的記錄，速度快。oracle基本所有查詢案例在我們講解的過程中我們利用scott用戶存在的幾張表（emp，dept）為大家演示如何使用select語句，select語句在軟件編程中非常有用，希望大家好好的掌握。 emp 雇員表 clerk  普員工 sa

31、lesman 銷售 manager  經(jīng)理 analyst 分析師 president  總裁 mgr  上級的編號 hiredate 入職時(shí)間 sal 月工資 comm 獎金 deptno 部門 dept部門表 deptno 部門編號 accounting 財(cái)務(wù)部 research  研發(fā)部 operations 業(yè)務(wù)部 loc 部門所在地點(diǎn) salgrade   工資級別 grade    級別 losal    最低工資 hisal   

32、最高工資查看表結(jié)構(gòu)DESC emp; 查詢所有列SELECT * FROM dept; 切忌動不動就用select * SET TIMING ON; 打開顯示操作時(shí)間的開關(guān)，在下面顯示查詢時(shí)間。 CREATE TABLE users(userId VARCHAR2(10), uName VARCHAR2 (20), uPassw VARCHAR2(30); INSERT INTO users VALUES('a0001', '啊啊啊啊', 'aaaaaaaaaaaaaaaaaaaaaaa'); -從自己復(fù)制，加大數(shù)據(jù)量 大概幾萬行就可以了

33、0; 可以用來測試sql語句執(zhí)行效率 INSERT INTO users (userId,UNAME,UPASSW) SELECT * FROM users; SELECT COUNT (*) FROM users;統(tǒng)計(jì)行數(shù) 查詢指定列SELECT ename, sal, job, deptno FROM emp; 如何取消重復(fù)行DISTINCT SELECT DISTINCT deptno, job FROM emp; 查詢SMITH所在部門，工作，薪水 SELECT deptno,job,sal FROM emp WHERE ename = 'SMITH' 注意：oracl

34、e對內(nèi)容的大小寫是區(qū)分的，所以ename='SMITH'和ename='smith'是不同的 使用算術(shù)表達(dá)式 nvl  null 問題：如何顯示每個雇員的年工資？ SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp; 使用列的別名SELECT ename "姓名", sal*12 AS "年收入" FROM emp; 如何處理null值使用nvl函數(shù)來處理 如何連接字符串（|）SELECT ename  | &

35、#39; is a ' | job FROM emp; 使用where子句問題：如何顯示工資高于3000的 員工？ SELECT * FROM emp WHERE sal > 3000; 問題：如何查找1982.1.1后入職的員工？ SELECT ename,hiredate FROM emp WHERE hiredate >'1-1月-1982' 問題：如何顯示工資在2000到3000的員工？ SELECT ename,sal FROM emp WHERE sal >=2000 AND sal <= 3000; 如何使用like操作符 %：表示

36、0到多個字符   _：表示任意單個字符 問題：如何顯示首字符為S的員工姓名和工資？ SELECT ename,sal FROM emp WHERE ename like 'S%' 如何顯示第三個字符為大寫O的所有員工的姓名和工資？ SELECT ename,sal FROM emp WHERE ename like '_O%' 在where條件中使用in問題：如何顯示empno為7844, 7839,123,456 的雇員情況？ SELECT * FROM emp WHERE empno in (7844, 7839,123,456); 使用is nu

37、ll的操作符問題：如何顯示沒有上級的雇員的情況？ 錯誤寫法：select * from emp where mgr = '' 正確寫法：SELECT * FROM emp WHERE mgr is null;使用邏輯操作符號 問題：查詢工資高于500或者是崗位為MANAGER的雇員，同時(shí)還要滿足他們的姓名首字母為大寫的J？ SELECT * FROM emp WHERE (sal >500 or job = 'MANAGER') and ename LIKE 'J%' 使用order by字句   默認(rèn)asc 問題：如何

38、按照工資的從低到高的順序顯示雇員的信息？ SELECT * FROM emp ORDER by sal; 問題：按照部門號升序而雇員的工資降序排列 SELECT * FROM emp ORDER by deptno, sal DESC; 使用列的別名排序問題：按年薪排序 select ename, (sal+nvl(comm,0)*12 "年薪" from emp order by "年薪" asc; 別名需要使用“”號圈中,英文不需要“”號 Clear 清屏命令 數(shù)據(jù)分組 max，min， avg， sum， count 問題：如何顯示所有員工中最高工

39、資和最低工資？ SELECT MAX(sal),min(sal) FROM emp e;       最高工資那個人是誰？ 錯誤寫法：select ename, sal from emp where sal=max(sal); 正確寫法：select ename, sal from emp where sal=(select max(sal) from emp); 注意：select ename, max(sal) from emp;這語句執(zhí)行的時(shí)候會報(bào)錯，說ORA-00937：非單組分組函數(shù)。因?yàn)閙ax是分組函數(shù)，而ename不是分組函數(shù).

40、但是select min(sal), max(sal) from emp;這句是可以執(zhí)行的。因?yàn)閙in和max都是分組函數(shù)，就是說：如果列里面有一個分組函數(shù)，其它的都必須是分組函數(shù)，否則就出錯。這是語法規(guī)定的問題：如何顯示所有員工的平均工資和工資總和？ 問題：如何計(jì)算總共有多少員工問題：如何擴(kuò)展要求： 查詢最高工資員工的名字，工作崗位 SELECT ename, job, sal FROM emp e where sal = (SELECT MAX(sal) FROM emp); 顯示工資高于平均工資的員工信息 SELECT * FROM emp e where sal > (SELEC

41、T AVG(sal) FROM emp); group by 和 having子句group by用于對查詢的結(jié)果分組統(tǒng)計(jì) having子句用于限制分組顯示結(jié)果問題：如何顯示每個部門的平均工資和最高工資？ SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno; （注意：這里暗藏了一點(diǎn)，如果你要分組查詢的話，分組的字段deptno一定要出現(xiàn)在查詢的列表里面，否則會報(bào)錯。因?yàn)榉纸M的字段都不出現(xiàn)的話，就沒辦法分組了） 問題：顯示每個部門的每種崗位的平均工資和最低工資？ SELECT min(sal), AVG(sal), deptno,

42、 job FROM emp GROUP by deptno, job; 問題：顯示平均工資低于2000的部門號和它的平均工資？ SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) < 2000; 對數(shù)據(jù)分組的總結(jié)1 分組函數(shù)只能出現(xiàn)在選擇列表、having、order by子句中(不能出現(xiàn)在where中) 2 如果在select語句中同時(shí)包含有g(shù)roup by, having, order by 那么它們的順序是group by, having, order by 3 在選擇列中如果有列、表

43、達(dá)式和分組函數(shù)，那么這些列和表達(dá)式必須有一個出現(xiàn)在group by 子句中，否則就會出錯。 如SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000; 這里deptno就一定要出現(xiàn)在group by 中 問題：顯示雇員名，雇員工資及所在部門的名字【笛卡爾集】？ 規(guī)定：多表查詢的條件是 至少不能少于 表的個數(shù)-1 才能排除笛卡爾集 （如果有N張表聯(lián)合查詢，必須得有N-1個條件，才能避免笛卡爾集合） SELECT e.ename, e.sal, d.dname FROM emp e,

44、dept d WHERE e.deptno = d.deptno; 問題：顯示部門號為10的部門名、員工名和工資？ SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10; 問題：顯示各個員工的姓名，工資及工資的級別？ 先看salgrade的表結(jié)構(gòu)和記錄 SQL>select * from salgrade;     GRADE         LO

45、SAL          HISAL -   -   -         1          700           1200       &#

46、160; 2          1201          1400         3          1401          2000   

47、      4          2001          3000         5          3001       

48、0;  9999 SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; 擴(kuò)展要求： 問題：顯示雇員名，雇員工資及所在部門的名字，并按部門排序？ SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno; （注意：如果用group by，一定要把e.deptno放到查詢列里面） 自連接自連接是指在同一張表的連接查詢 問題：顯

49、示某個員工的上級領(lǐng)導(dǎo)的姓名？ 比如顯示員工FORD的上級 SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD' 請思考：顯示與SMITH同部門的所有員工？ 思路：1 查詢出SMITH的部門號 select deptno from emp WHERE ename = 'SMITH' 2 顯示 SELECT * FROM emp WHERE deptno = (select deptno from

50、 emp WHERE ename = 'SMITH'); 數(shù)據(jù)庫在執(zhí)行sql 是從左到右掃描的， 如果有括號的話，括號里面的先被優(yōu)先執(zhí)行。 請思考：如何查詢和部門10的工作相同的雇員的名字、崗位、工資、部門號 SELECT DISTINCT job FROM emp WHERE deptno = 10; SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10); （注意：不能用job=.，因?yàn)榈忍?是一對一的） 在多行子查詢中使用all操作符問題：如何顯示工資比部門30的所有員

51、工的工資高的員工的姓名、工資和部門號？ SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30); 擴(kuò)展要求： 大家想想還有沒有別的查詢方法。 SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 執(zhí)行效率上， 函數(shù)高得多 在多行子查詢中使用any操作符問題：如何顯示工資比部門30的任意一個員工的工資高的員工姓名、工資和部門號？

52、SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30); 擴(kuò)展要求： 大家想想還有沒有別的查詢方法。 SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30); 多列子查詢單行子查詢是指子查詢只返回單列、單行數(shù)據(jù)，多行子查詢是指返回單列多行數(shù)據(jù)，都是針對單列而言的，而多列子查詢是指查詢返回多個列數(shù)據(jù)的子查詢語句。 請思考如何查詢與SM

53、ITH的部門和崗位完全相同的所有雇員。 SELECT deptno, job FROM emp WHERE ename = 'SMITH' SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); 在from子句中使用子查詢請思考：如何顯示高于自己部門平均工資的員工的信息 思路： 1. 查出各個部門的平均工資和部門號 SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno; 2. 把

54、上面的查詢結(jié)果看做是一張子表 SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 如何衡量一個程序員的水平？ 網(wǎng)絡(luò)處理能力， 數(shù)據(jù)庫， 程序代碼的優(yōu)化程序的效率要很高 小總結(jié)： 在這里需要說明的當(dāng)在from子句中使用子查詢時(shí)，該子查詢會被作為一個視圖來對待，因此叫做內(nèi)嵌視圖，當(dāng)在from子句中使用子查詢時(shí)，必須給子查詢

55、指定別名。 注意：別名不能用as，如：SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 在ds前不能加as，否則會報(bào)錯  （給表取別名的時(shí)候，不能加as；但是給列取別名，是可以加as的） 10.oracle分頁查詢mysql: select * from 表名 where 條件 limit 從第幾條取

56、，取幾條 見mysql分頁查詢sql server: select top 取幾條 * from 表名 where id not in(select top 4 id from 表名 where 條件) 也可以使用行集函數(shù) 見3.sql server分頁查詢排除前4條，再取4條，這個案例實(shí)際上是取5-8條oracle：以scott/tiger賬號登陸進(jìn)行查詢：分頁查詢模板select t2.* from (select t1.*,rownum rn from(select * from emp) t1 where rownum<=6) t2 where rn>=4;先找到小于6的

57、然后找到大于4的【順序可以反】select t2.* from (select t1.*,rownum rn from(select * from emp) t1 where rownum>=4) t2 where rn<=6;oracle使用三層過濾:第一層:select * from emp第二層: select t1.*,rownum rn from (select * from emp) t1where rownum<=6第三層: select t2.* from (select t1.*,rownum rn from(select * from emp) t1 wh

58、ere rownum<=6) t2 where rn>=4;上面是一個分頁模板，6表示取到第幾條，4表示從第幾條取(1)刪除重復(fù)記錄在幾千條記錄里，存在著些相同的記錄，請用sql語句刪除?！咀⒁狻?.表中肯定是沒有主鍵的，這才叫記錄相同2.若有主鍵(主鍵肯定不同)，那請你把其他字段變成一個臨時(shí)表，再使用下面方法準(zhǔn)備：-創(chuàng)建表create table people(peopleId number,peopleName varchar(50),peopleAge number);-插入數(shù)據(jù)insert into people values(1,'haozl',22);i

59、nsert into people values(2,'wangx',23);insert into people values(3,'liwr',24);insert into people values(4,'zhanggh',25);insert into people values(5,'cheng',26);-自我復(fù)制insert into people(peopleId,peopleName,peopleAge) (select peopleId,peopleName,peopleAge from people);in

60、sert into people values(6,'hancl',27);insert into people values(7,'yangqp',22);insert into people values(8,'wangt',23);insert into people values(9,'nieyp',18);insert into people values(10,'tianx',19);insert into people(peopleId,peopleName,peopleAge) (select peopleId,peopleName,peopleAge from people);insert into people values(11,'hansm',41);insert into people values(12,'haog',31);insert into people values(13,'chengyy',51);