ORACLE常用SQL命令集

1、o r a c l e 常 用 命 令 集oracle 常 用 命 令 集經(jīng)常用到：1、取出互不相同的記錄：select distinct mycolumn from mytable2、排序查詢結(jié)果：順序 select price from titles order by price 倒序 select price from titles order by price desc3、插入數(shù)據(jù)：insert into mytable (mycolumn) values (some data) identity 總是保存最后一次插入標(biāo)識(shí)字段的值4、刪除記錄：delete mytable where

2、first_column=delete me5、更新記錄：update mytable set first_coumn=updated! where second_column=update6、substr(ordered_item, 1, 2)- 截取字符7、修改表字段：alter table mccus.mc_a_up20itemmodify(upitem5 varchar2(200 byte);8、如何使用 oracle round 函數(shù) (四捨五入) select round(123.456, 0) from dual;回傳 123 select round(123.456, 1) f

3、rom dual; 回傳 123.5 9、從一個(gè)表建立另一個(gè)表create table rain_table as select city,precipitation from trouble;create table rain_table as select city,precipitation from trouble where 1=2;建立表結(jié)構(gòu)，不帶數(shù)據(jù)數(shù)據(jù)類型1、字符型 char 范圍 最大2000個(gè)字節(jié) 定長(zhǎng) char(10) 張三 后添空格6個(gè)把10個(gè)字節(jié)補(bǔ)滿 張三 varchar2 范圍 最大4000個(gè)字節(jié) 變長(zhǎng) varchar2(10) 張三 在數(shù)據(jù)庫中張三 大對(duì)象 字符型

4、大對(duì)象 4000字節(jié) 最大4g clob (character large object) 2、數(shù)字 number 范圍 10的-38次方 到10的38次方 可以表示小數(shù) 也可以表示整數(shù) number(4) 最大表示4位整數(shù) -9999 到 9999 number(5,2) 表示5位有效數(shù)字 2位小數(shù)的 一個(gè)小數(shù) -999.99 到 999.99 3、日期 date 包含年月日和時(shí)分秒 7個(gè)字節(jié) 4、圖片 blob 二進(jìn)制大對(duì)象 圖像/聲音 4g如何建表1、一般建表學(xué)生表student create table student( -學(xué)生表 xh number(4), -學(xué)號(hào) xm varcha

5、r2(10), -姓名 sex char(2), -性別 birthday date, -日期 sal number(7,2) -獎(jiǎng)學(xué)金 ); 班級(jí)class create table class( -班級(jí)表 classid number(2), -班級(jí)編號(hào) cname varchar2(20) -班級(jí)名字 );從一個(gè)表建立另一個(gè)表create table rain_table as select city,precipitation from trouble; -帶數(shù)據(jù)一起建立create table rain_table as select city,precipitation from

6、trouble where 1=2;-建立表結(jié)構(gòu)，不帶數(shù)據(jù)2、添加字段(學(xué)生所在班級(jí)classid) alter table student add (classid number(2);3、修改字段的長(zhǎng)度 alter table student modify (xm varchar2(12) ;4、修改字段的類型(不能有記錄的) alter table student modify (xh varchar2(5);5、刪除一個(gè)字段 alter table student drop column sal;6、刪除表 drop table student;7、表的名字修改 rename stud

7、ent to stu;8、字段如何改名字 -先刪除 a)alter table student drop column sal; -再添加 b)alter table student add (salary number(7,2);如何插入數(shù)據(jù)1、插入數(shù)據(jù) insert語句 所有字段都插入 insert into student values (a001,張三,男,01-5月-05,10); oracle中默認(rèn)的日期格式dd-mon-yy 改日期的默認(rèn)格式 alter session set nls_date_format = yyyy-mm-dd; insert into student v

8、alues (a002,mike,男,1905-05-06,10);alter session set nls_date_format = dd-mon-yy; -恢復(fù)oracle默認(rèn)格式 察看日期的格式 set linesize 1000 select * from nls_session_parameters where parameter=nls_date_format; 永久設(shè)置日期格式:改注冊(cè)表oracle/home0 加字符串nls_date_format 值yyyy-mm-dd 2、部分字段插入 insert into student(xh,xm,sex) values (a00

9、3,john,女); 插入空值 insert into student(xh,xm,sex,birthday) values (a004,martin,男,null);修改update1、改一個(gè)字段 update student set sex=女 where xh=a001;2、改多個(gè)字段 update student set sex=男, birthday=1980-04-01 where xh=a001;3、改為空值 (修改為空時(shí)=null) update student set birthday=null where xh=a001; 把生日為空的人的班級(jí)編號(hào)改為20(條件中的空是is

10、 null / is not null) update student set classid=20 where birthday is null;- 錯(cuò)誤的沒有達(dá)到要求 update student set classid=20 where birthday=null; update student set classid=20 where xm=null; -不表示空值 表示xm是null的字符串 刪除 delete1、delete from student; 刪除所有記錄,表結(jié)構(gòu)還在,寫日志,可以恢復(fù)的,速度慢2、drop table student; 刪除表的結(jié)構(gòu)和數(shù)據(jù)3、delete

11、 from student where xh=a001; 刪除一條記錄4、truncate table student; 刪除表中的所有記錄,表結(jié)構(gòu)還在,不寫日志,無法找回刪除的記錄,速度快查詢 select 1、select * from student;2、select xh,xm,sex from student; 3、select * from student where xh like a%1; %任意多個(gè)字符4、select * from student where xh like a_1; _1個(gè)字符5、select * from student where xh like %a

12、%;select * from student where xh like a%;select * from student where xh like %a; select * from student where xh = a%; 6、select * from student order by birthday ; 升序 (order by birthday asc;) select * from student order by birthday desc; -降序 select * from student order by birthday desc,xh asc; -按birth

13、day 降序 按xh升序(asc/默認(rèn)) 7、select * from student where sex=女 or birthday=1999-02-01;select * from student where sex=女 and birthday=1999-02-01; select * from student where salary 20 and xh b002; (!=)o r a l c e的 函 數(shù)單行函數(shù)（返回值只有一個(gè)） 分組函數(shù)（返回值是多條記錄 group bysumavg）啞元表 dual dual啞元表 沒有表需要查詢的時(shí)候 可以用它 select hello w

14、orld from dual; select 1+1 from dual;-查詢系統(tǒng)時(shí)間單 行 函 數(shù)字符函數(shù)1、concat 連接 | select deptno,dname|-|loc from dept; -顯示dname和loc中間用-分隔 2、initcap 首字母大寫 select ename,initcap(ename) from emp; 3、lower 轉(zhuǎn)換為小寫字符 select ename,lower(ename) from emp;4、upper 轉(zhuǎn)換為大寫 update dept set loc=lower(loc); update dept set loc=uppe

15、r(loc);5、lpad 左填充rpad 右填充 select deptno,lpad(dname,10, ),loc from dept;6、ltrim 去除左邊的空格 rtrim 去除右邊的空格 alltrim 去除兩邊的空格7、replace 替換 select ename,replace(ename,s,s) from emp; -用s去替換ename中的s8、translate 轉(zhuǎn)換 select ename,translate(ename,s,a) from emp;9、 ascii 求asc碼 chr asc碼變字符 select ascii(a) from dual; sel

16、ect chr(97) from dual; select hello|chr(9)|world from dual; t ascii碼是 9 n ascii碼是 10select hello|t|world from dual; 10、substr 字符截取函數(shù) select ename,substr(ename,1,3) from emp; -從第1個(gè)位置開始 顯示3個(gè)字符 select ename,substr(ename,4) from emp; -從第4個(gè)位置開始顯示后面所有的字符11、instr 測(cè)試字符串出現(xiàn)的位置 select ename,instr(ename,s) from

17、 emp; -s第1次出現(xiàn)的位置 select ename,instr(ename,t,1,2) from emp; -從第1個(gè)位置開始測(cè)試t第2次出現(xiàn)的位置12、length 字符串的長(zhǎng)度 select ename,length(ename) from emp; 日期和時(shí)間函數(shù)1、 sysdate 系統(tǒng)時(shí)間 select sysdate from dual; select to_char(sysdate,yyyy/mm/dd hh24:mi:ss) from dual;select to_char(sysdate,ddd) from dual; -一年中的第多少天,并格式為如005形式 se

18、lect to_char(sysdate,d) from dual; -一年中的第多少天 疑問：當(dāng)我的sysdate的日期是2007-1-5時(shí), to_char(sysdate,ddd)的值為005 而to_char(sysdate,d)值為6 select to_char(sysdate,day) from dual; -取星期值select to_char(sysdate,yyyy-mm-dd) from dual;select to_char(sysdate,yyyy年mm月dd日 hh24:mi:ss) from dual;select from dual; -取單引號(hào) select

19、to_char(sysdate,sssss) from dual;-從今天零點(diǎn)以后的秒數(shù)2、 add_months 添加月份 得到一個(gè)新的日期 select add_months(sysdate,1) from dual; select add_months(sysdate,-1) from dual; select trunc(sysdate)-to_date(20050101,yyyymmdd) from dual;select add_months(sysdate,12) from dual; 一年以后的今天select add_months(sysdate,-12) from dual

20、;一年以前的今天select trunc(sysdate) from dual; -截取年月日 select sysdate+2 from dual; -數(shù)字代表的是天數(shù)* 兩個(gè)日期之間的差值代表天數(shù) 3、last_day 某月的最后一天 select last_day(sysdate) from dual; select add_months(last_day(sysdate)+3,-1) from dual; -本月第3天的日期4、months_between 兩個(gè)日期之間的月數(shù) select months_between(sysdate,2005-02-01) from dual; -方

21、向 sysdate - 2005-02-01 select months_between(2005-02-01,sysdate) from dual; 轉(zhuǎn)換函數(shù)1、to_char 把日期或數(shù)字類型變?yōu)樽址?select to_char(sysdate,hh24:mi:ss) from dual; select to_char(sysdate,yyyymmdd hh24:mi:ss) from dual;select sal,to_char(sal,l9,999) from emp; -l本地貨幣2、to_number 把字符串變成數(shù)字 select to_number(19990801) f

22、rom dual; 3、to_date 把字符串變成日期 select to_date(19800101,yyyymmdd) from dual; select to_char(to_date(19800101,yyyymmdd),yyyy年mm月dd日) from dual; 數(shù)學(xué)函數(shù)1、ceil(x) 不小于x的最小整數(shù) ceil（12.4) 13 ceil(-12.4) -122、floor(x) 不大于x的最大整數(shù) floor(12.5) 12 floor(-12.4) -133、round(x) 四舍五入 round(12.5) 13 round(12.456,2) 12.464、t

23、runc(x) 舍去尾數(shù) trunc(12.5) 12 trunc(12.456,2) 12.45 select to_char(trunc(sysdate),yyyymmdd hh24:mi:ss) from dual; -舍去日期的小時(shí)部分5、mod(x,n) x除以n以后的余數(shù) mod(5,2) 1 mod(4,2) 06、power(x,y) x的y次方 select power(3,3) from dual; 混合函數(shù)1、求最大值 select greatest(100,90,80,101,01,19) from dual; 2、求最小值 select least(100,0,-9,

24、10) from dual;3、空值轉(zhuǎn)換函數(shù) nvl(comm,0) 字段為空值 那么就返回0 否則返回本身 select comm,nvl(comm,0) from emp; comm 類型和 值的類型是 一致的 復(fù)雜的函數(shù)1、decode 選擇結(jié)構(gòu) (if . elseif . elesif . else結(jié)構(gòu)) 要求: sal=800 顯示低工資 sal=3000 正常工資 sal=5000 高工資 只能做等值比較 select sal,decode(sal,800,低工資,3000,正常工資,5000,高工資,沒判斷) from emp; 表示如下的if else 結(jié)構(gòu) if sal=8

25、00 then 低工資 else if sal =3000 then 正常工資 else if sal = 5000 then 高工資 else 沒判斷 end if sal 800 sal -800 0 判斷正負(fù)sign(x) x是正 1x是負(fù) -1 x是0 0 select sign(-5) from dual; 如何做大于小于的比較？ sal1000 顯示低工資 sal-10000 sign(sal-1000) = -1 1000=sal=3000 正常工資 3000sal2000的工作?？ 統(tǒng)計(jì)每種工作的平均工資是多少 塞選出平均工資2000的工作 從分組的結(jié)果中篩選 havingse

26、lect job,avg(sal) from emp group by job having avg(sal) 2000; -group by 經(jīng)常和having搭配來篩選計(jì)算工資在2000以上的各種工作的平均工資？ select job,avg(sal) from emp where sal 2000 group by job having avg(sal) 3000; 一般group by 和 having搭配,表示對(duì)分組后的結(jié)果的篩選 where子句 - 用于對(duì)表中數(shù)據(jù)的篩選 3、max min select max(sal) from emp; 公司的最高工資 select min(s

27、al) from emp ; 公司的最低工資找每個(gè)部門的最高和最低的工資? select deptno,max(sal),min(sal) from emp group by deptno;找每個(gè)工作的最高和最低的工資? select job,max(sal),min(sal) from emp group by job;找每個(gè)部門中每種工作的最高和最低的工資? select deptno,job,max(sal),min(sal) from emp group by deptno,job;select max(sal),min(sal) from emp group by deptno,jo

28、b; 單個(gè)字段如果沒有被分組函數(shù)所包含，而其他字段又是分組函數(shù)的話，一定要把這個(gè)字段放到group by中去4、關(guān)聯(lián)查詢 多張表,而表與表之間是有聯(lián)系的,是通過字段中的數(shù)據(jù)的內(nèi)在聯(lián)系來發(fā)生,而不是靠相同的字段名來聯(lián)系的或者是否有主外鍵的聯(lián)系是沒有關(guān)系的 select dname,ename from emp,dept; -笛卡爾積 (無意義的) -多表查詢的時(shí)候一定要有關(guān)聯(lián)的條件-n個(gè)表 關(guān)聯(lián)條件一定有n-1個(gè)select dname,ename from mydept,myemp where mydept.no = myemp.deptno; -使用的表的全名 select dname,en

29、ame from emp,dept where emp.deptno = dept.deptno ; -使用表的別名 select dname,ename,a.deptno from emp a,dept b where a.deptno = b.deptno and a.deptno = 10;-等值連接(內(nèi)連接-兩個(gè)表的數(shù)據(jù)作匹配a.deptno = b.deptno ) select dname,ename,a.deptno from emp a inner join dept b on a.deptno = b.deptnowhere a.deptno = 10;-on寫連接條件的 -

30、where中寫別的條件-使用where/on select dname,ename,a.deptno from emp a,dept b where a.deptno = b.deptno and a.deptno=10; -on中寫連接條件 -where中寫其他的條件 select dname,ename,a.deptno from emp a inner join dept b on a.deptno = b.deptno where a.deptno = 10 ; -外連接 左外連接 右外連接 全外連接 (+)寫法只有在oracle中有效 select dname,ename,b.dep

31、tno from emp a,dept b where a.deptno(+) = b.deptno; -標(biāo)準(zhǔn)寫法 select dname,ename,b.deptno from emp a right outer join dept b on a.deptno = b.deptno; select dname,ename,b.deptno from emp a,dept b where a.deptno = b.deptno(+); -標(biāo)準(zhǔn)寫法 select dname,ename,b.deptno from emp a left outer join dept b on a.deptno

32、 = b.deptno; -標(biāo)準(zhǔn)寫法(全外聯(lián)) select dname,ename,b.deptno from emp a full outer join dept b on a.deptno = b.deptno; -自連接 select a.ename as 員工姓名,b.ename as 經(jīng)理名字 from emp a,emp b where a.mgr = b.empno(+) and a.empno = b.mgr; 5、子查詢 在select語句中嵌套了另一個(gè)select語句 1)where 子句中嵌套子查詢 2)用子查詢的結(jié)果 作為字段來出現(xiàn)（1）、where 子句中嵌套子查詢

33、,執(zhí)行順序是先執(zhí)行子查詢，再執(zhí)行主查詢 找出工資高于公司平均工資的所有員工? select * from emp where sal+nvl(comm,0) (select avg(sal+nvl(comm,0) from emp);高于部門30中員工最高工資的其他員工? select * from emp where sal+nvl(comm,0) all (select sal+nvl(comm,0) from emp where deptno = 30);低于部門30中員工工資的其他員工? select * from emp where sal+nvl(comm,0) all (sele

34、ct sal+nvl(comm,0) from emp where deptno = 30);select * from emp where sal+nvl(comm,0) a.sal) = 0 and a.deptno is not null;顯示每個(gè)部門的工資前2名的員工select * from emp a where (select count(*) from emp where deptno = a.deptno and sal a.sal) =1 and a.deptno is not null;6、層次查詢-level 偽列 層次查詢的時(shí)候可以使用的層的編號(hào)select lpad

35、(+,level, )|ename from emp connect by prior empno = mgr -父子關(guān)系 父結(jié)點(diǎn)中的empno = 子節(jié)點(diǎn)中的mgr start with mgr is null;-從 mgr is null的節(jié)點(diǎn) 開始遍歷select lpad(+,level, )|ename from emp connect by prior empno = mgr start with ename = blake;7、top 前幾行 (sqlserver) rownum (oracle偽列) -emp表的前2行 -rownum必須使用=的關(guān)系比較運(yùn)算符select *

36、from emp where rownum = 2;select top 2 * from emp; -sqlserver的寫法select * from emp where rownum = 1;-公司工資最高的2個(gè)人 /*select * from emp where rownum = 2 order by sal desc;*/ 錯(cuò)誤的select * from (select * from emp order by sal desc) where rownum = 9 and num = 13 and num = 14;建立表如下:學(xué)生基本信息表create student(stude

37、ntidintidentity(1,1)not null primary key,-主鍵studentnamecharnot null)課程信息表create subject(subjectidcharnot null primary key, -主鍵subjectnamecharnot null)成績(jī)表create grade(studentidintnot null,-聯(lián)合主鍵subjectidcharnot null,-聯(lián)合主鍵grade intnot null,primary key (studentid,subjectid)1.將建表命令改為oracle語句在oracle中建表cre

38、ate table student( -學(xué)生表studentid number(3) primary key, -學(xué)生編號(hào)studentname varchar2(20) -學(xué)生的姓名); create table subject( -課程表subjectid char(3) primary key, -課程編號(hào)subjectname varchar2(20) -課程的名字);create table grade( -分?jǐn)?shù)表studentid number(3) references student(studentid), -學(xué)生idsubjectid char(3) references s

39、ubject(subjectid), -課程idmark number(3), -分?jǐn)?shù)primary key (studentid,subjectid) -聯(lián)合主鍵);insert into student values (101,張三);insert into student values (102,李云);insert into student values (103,未);insert into subject values (a01,c+);insert into subject values (a02,asp);insert into subject values (a03,java

40、);insert into grade values (101,a01,59);insert into grade values (101,a02,72);insert into grade values (101,a03,90);insert into grade values (102,a01,75);insert into grade values (102,a02,91);insert into grade values (103,a01,71);2.作如下4題第一問：查詢出以下信息學(xué)號(hào) 學(xué)生姓名 課程名稱 成績(jī) （要全部學(xué)生信息）關(guān)聯(lián)查詢 (多張表的) 別名select a.studentid as 學(xué) 號(hào),studentname 學(xué)生姓名,subjectname 課程名稱,mark 成 績(jī)from student a , subject b , grade cwhere a.studentid