Oracle數(shù)據(jù)庫(kù)初級(jí)常用操作總結(jié).doc

此文檔收集于網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系網(wǎng)站刪除查詢(xún)語(yǔ)句基本數(shù)據(jù)查詢(xún)select 內(nèi)容 from 表名；select distinct job from emp；去除重復(fù)行限定查詢(xún)（查詢(xún)滿(mǎn)足條件的行）SELECT * | 列名FROM 表名WHERE 條件; SELECT empno,ename,commFROM empWHERE comm IS NOT NULL; 限定查詢(xún)-IS NULL和IS NOT NULL的使用SELECT empno,ename,commFROM empWHERE comm IS NULL查詢(xún)誰(shuí)沒(méi)有獎(jiǎng)金限定查詢(xún)-AND 的使用 查詢(xún)工資大于1500，并且可以領(lǐng)取獎(jiǎng)金的雇員 SELECT empno,ename,sal,commFROM EMPWHERE sal 1500 AND comm IS NOT NULL;限定查詢(xún)-OR 的使用 查詢(xún)工資大于1500和可以領(lǐng)取獎(jiǎng)金的雇員 SELECT empno,ename,sal,commFROM EMPWHERE sal 1500 OR comm IS NOT NULL;限定查詢(xún)-使用NOT對(duì)條件整體取反 查詢(xún)工資不大于1500并且不能領(lǐng)取資金的雇員 SELECT empno,ename,sal,commFROM empWHERE NOT (sal1500 AND comm IS NOT NULL);限定查詢(xún)-BETWEEN . AND.的使用 查詢(xún)基本工資大于等于1500并且小于等于3000的雇員SELECT empno,ename,sal,commFROM empWHERE sal BETWEEN 1500 AND 3000限定查詢(xún)-IN 的使用 查詢(xún)出雇員編號(hào)是7369，7499，7521的雇員的具體信息 SELECT *FROM empWHERE empno IN(7369,7499,7521);限定查詢(xún)-NOT IN 的使用 查詢(xún)出雇員編號(hào)不是7369，7499，7521的雇員的具體信息 SELECT *FROM empWHERE empno NOT IN(7369,7499,7521);限定查詢(xún)-LIKE 的使用 查詢(xún)中雇員的名字第二個(gè)字符是M的雇員信息 SELECT empno,ename,comm,salFROM empWHERE ename LIKE _M%; 說(shuō)明： _ 匹配一個(gè)字符，% 匹配0個(gè)或多個(gè)字符限定查詢(xún)-LIKE 匹配日期 查詢(xún)1982年入職的所有雇員的信息 SELECT *FROM empWHERE hiredateLIKE %82%限定查詢(xún)-LIKE 匹配數(shù)字 查詢(xún)工資中包含 5 的雇員信息 SELECT *FROM empWHERE salLIKE %5%對(duì)結(jié)果排序-ORDER BY帶有ORDER BY 子句的SQL語(yǔ)句基本格式SELECT 列. FROM 表 WHERE 條件 ORDER BY 列 查詢(xún)員工資大于1500的信息，按工資排序 SELECT * FROM emp WHERE sal1500 ORDER BY sal查詢(xún)工資大于1500員工的信息，按工資降序,按雇傭日期升序排序 SELECT * FROM emp WHERE sal1500ORDER BY salDESC,hiredate ASC 說(shuō)明: ASC 排序，DESC降序，默認(rèn)ASC左、右外連接查詢(xún)員工編號(hào)，姓名，所在部門(mén)號(hào)，部門(mén)名稱(chēng)，將沒(méi)有員工的部門(mén)也顯示出來(lái)SELECT e.ename,d.deptno,d.dnameFROM emp e,dept dWHERE e.deptno(+)=d.deptno;注：（+）在左邊，表示右連接，會(huì)列出右表中出現(xiàn)但是沒(méi)有在左表中出現(xiàn)的行交叉連接（CROSS JOIN）：用來(lái)產(chǎn)生笛卡爾積的SELECT * FROM emp CROSS JOIN dept;自然連接（NATURAL JOIN）：自動(dòng)進(jìn)行關(guān)聯(lián)字段的匹配SELECT * FROM emp NATURAL JOIN dept;USING子句：直接指定操作關(guān)聯(lián)列SELECT * FROM emp JOIN dept USING(deptno);ON 子句：用戶(hù)自己編寫(xiě)連接條件SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno;RIGHT JOIN：右外連接SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;子查詢(xún)-IN 的使用 查詢(xún)和SMITH或JONES在同一部門(mén)，同一職位工作的員工SELECT * FROM emp WHERE (deptno,job) IN(SELECT deptno,job FROM emp WHERE enameIN(SMITH,JONES);子查詢(xún)-ANY 的使用 =ANY ： 與 IN操作符的效果一致查詢(xún)和SMITH或JONES在同一部門(mén)，同一職位工作的員工SELECT * FROM emp WHERE (deptno,job) =ANY (SELECT deptno,jopFROM emp WHERE ename IN(SMITH,JONES);Select * from ANY ： 只要大于子查詢(xún)中的任何一個(gè)值即可SELECT * FROM emp WHERE sal ANY(SELECT MIN(sal) FROM EMP GROUP BY deptno);ALL：比最大的值大SELECT * FROM emp WHERE salALL(SELECT MIN(sal) FROM emp GROUP BY deptno);ANY ： 只要小于子查詢(xún)中的任何一個(gè)值即可SELECT * FROM emp WHERE sal ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);ALL：比最小的值小SELECT * FROM emp WHERE salALL(SELECT MIN(sal) FROM emp GROUP BY deptno);插入語(yǔ)句intsert into 表名 values(值1 值2 值3.) 插入的數(shù)值的字段類(lèi)型要與定義的時(shí)候一樣intsert into 表名 values（值1，值2，null，值3） 有不想寫(xiě)的可以用null 來(lái)標(biāo)示intsert into 表名(字段1.字段2，字段3) values(值1 值2 值3) 類(lèi)型對(duì)應(yīng)insert into test (smallclass,bigclass) values (asp教程,好好學(xué)習(xí)吧)Insert into INSERT INTO SALFLDG (ACCNT_CODE,PERIOD) VALUES (SELECT ACCNT_CODE,PERIOD From SALFLDG GROUP BY ACCNT_CODE,PERIOD) 注意：數(shù)值的 類(lèi)型 個(gè)數(shù) 順序 不能超過(guò)最大值插入記錄到表中INSERT 的語(yǔ)法INSERT INTO 表名(字段名1,字段名2,.)VALUES(值1,值2.);為MYEMP中增加一條記錄INSERT INTO myemp(empno,ename,job)VALUES(7899,張三,清潔工)注：給定的值要和指定的字段數(shù)一致Insert into myemp(empno,ename,job) values (7899,張三,清潔工)當(dāng)為表中的所有列都插入數(shù)據(jù)數(shù)，可以采用如下簡(jiǎn)單寫(xiě)法aINSERT INTO 表名VALUES(值1,值2.);INSERT INTO myemp VALUES(7991,李四,清潔工,7369,14-2月 -1995,9000,300,40)注：要求給定值的數(shù)目和表中列的數(shù)目一致，并且值的順序和表中列的順序一致。新增加一條記錄到MYEMP中，但是該員工沒(méi)有獎(jiǎng)金也沒(méi)有上級(jí)領(lǐng)導(dǎo) 第一種寫(xiě)法：INSERT INTO myemp (empno,ename,job,hiredate,sal,deptno)VALUES(7899,張三,清潔工,14-2月 -1995,9000,40) 第二種寫(xiě)法：(用null來(lái)指定沒(méi)有值的列)INSERT INTO myemp VALUES(7898,王五,清潔工,null,14-2月 -1995,9000,null,40)新增加一條記錄到MYEMP中，但日期格式按1988-09-09的方式指定 TO_DATE函數(shù)的使用INSERT INTO myemp(empno,ename,job,hiredate,sal,deptno)VALUES(7896,趙六,清潔工,TO_DATE(1984-09-09,yyyy-mm-dd),9000,40);修改記錄update 表名 set 字段= 值，字段 =值 ，字段= 值. where 條件;例子：Update customers Set credit=200000.00,state=021 Where ID in (80,90,100,120)注意：條件必須寫(xiě)Update customers set credit=2000,00 state=021 where id in (80,90,100,120)刪除記錄delete from 表名 where 條件；注意：條件必須寫(xiě) ROLLBACK （回滾）在sqlplus 中如果使用 ROLLBACK的話(huà)那么將 撤銷(xiāo)操作直到 上次執(zhí)行 commit 的時(shí)候的狀態(tài)標(biāo)量函數(shù)的使用轉(zhuǎn)化大小寫(xiě) select upper(sddfa) from dual select lower(sddfa) from dual將首字母大寫(xiě) 其余小寫(xiě) select initcap （ename）from emp；連接字符串：select count（deptno）from dept；select contact (hello,woeld) from dual;select abc| jkh| sdf from dual;求子串：select substr (字符串，起始位，結(jié)束位) from dual; 起始位寫(xiě) 0或者1 都可以代表第一位select substr (字符串，起始位) from dual; 從起始位開(kāi)始往后截取求字符串長(zhǎng)度：select length（ename ）from emp;字符串替換select replace （原始字符串，想要被替換的字符串，替換字符串 ）from dual；輸出字符串的后三位select ename ，substr（ename，length（ename）-2） from dual ；select ename substr（ename，-3）from dual ；四舍五入select round（132.78454）from dual； 四舍五入取整select round（132.78345，2）from dual； 四舍五入并且指定保留小數(shù)點(diǎn)的位數(shù)select round（1323.7858416，-2）from dual；小數(shù)不要 并且 整數(shù)位兩位取整 本代碼得1300截?cái)嘈?shù)位select trunc （546541.151） from dual 不管四舍五入 直接截?cái)嘈?shù) 5465541select trunc（789.536，2） from dual 截?cái)嘈?shù) 指定小數(shù)的數(shù)位 789.53select trunc（789.536，-2） from dual 截?cái)嘈?shù) 并且對(duì)整數(shù)進(jìn)行截?cái)?700取余select mod（10，3）from dual ； 取余數(shù) 1運(yùn)算規(guī)律：日期+ 數(shù)字=日期日期-數(shù)字=日期日期日期 = 數(shù)字顯示系統(tǒng)時(shí)間select sysdate from dual；select to_char(sysdate,yyyy-mm-dd,hh24:mi:ss) from dual;顯示某本門(mén)員工進(jìn)入公司星期數(shù)：select empno，ename ，round（sysdate-hiredate）/7）from emp where deptno=10；在指定的日期加上指定的月數(shù)后的日期：select add_months(sysdate,2) from dual;求出給定日期范圍的月數(shù)：select empno ename month_between(sysdate,hiredate) from emp;下一個(gè)給定的星期是那個(gè)日期：select next_day(sysdate,星期一) from dual;求出給定日期所在的 月份的最后一天的日期select last_day(sysdate) from dual; 轉(zhuǎn)換函數(shù)轉(zhuǎn)換成字符串(TO_CHAR)查詢(xún)雇員號(hào)，姓名，以及入職的年份SELECT empno,ename,TO_CHAR(hiredate,yyyy)FROM emp;說(shuō)明：yyyy 匹配年份，mm 匹配月份，dd 匹配日使用TO_CHAR設(shè)置日期的顯示格式 SELECT empno,ename,TO_CHAR(hiredate,yyyy-mm-dd)FROM emp;使用fm去掉前導(dǎo) 0 SELECT empno,ename,TO_CHAR(hiredate,fmyyyy-mm-dd)FROM emp;通過(guò)TO_CHAR設(shè)置數(shù)值的格式 SELECT empno,ename,TO_CHAR(sal,99,999) FROM emp; 說(shuō)明：9 代表 1 位數(shù)字顯示金額 SELECT empno,ename,TO_CHAR(sal,$99,999) FROM emp; 說(shuō)明：$代表美元，L代表本地轉(zhuǎn)換成數(shù)字(TO_NUMBER) SELECT TO_NUMBER(123) +TO_NUMBER(123) FROM dual;轉(zhuǎn)換成日期(TO_DATE) SELECT TO_DATE(2009-07-31 ,yyyy-mm-dd) FROM dual;通用函數(shù)查詢(xún)每個(gè)員工的年收入SELECT empno,ename,(sal+NVL(comm,0)*12 incomeFROM emp;說(shuō)明：(COMM,0) 當(dāng)comm的值為null時(shí)，用 0 替換常用的列函數(shù) SUM(expression) 求和 MAX(expression) 求最大值 MIN(expression) 求最小值 COUNT(expression) 統(tǒng)計(jì)記錄數(shù) COUNT(DISTINCT COLNAME) 統(tǒng)計(jì)去除重復(fù)行記錄數(shù)SQL語(yǔ)句的執(zhí)行順序FROMWHEREGROUP BYHAVINGSELECTORDER BY創(chuàng)建表create table 表名（字段1 數(shù)值類(lèi)型 defult xx， 字段2 數(shù)值類(lèi)型，）;defult 是默認(rèn)值 可寫(xiě) 可不寫(xiě) create table 表名2 as 表名1 復(fù)制表2 到 表1 中create table 表名 as （子查詢(xún)） 復(fù)制子查詢(xún)結(jié)果這張表，當(dāng)子查詢(xún)結(jié)果沒(méi)有記錄時(shí) 這時(shí) 只會(huì)復(fù)制 表結(jié)構(gòu)（數(shù)據(jù)類(lèi)型） 而不會(huì)增加數(shù)據(jù)（有時(shí)目的只是復(fù)制表結(jié)構(gòu) 那么就使用這種方法 比如查詢(xún)語(yǔ)句中 使用 select 1=2 from 表名 ）刪除表drop table 表名; 注意：delete 是刪除表里面的數(shù)據(jù)修改表結(jié)構(gòu)增加一列：alter table 表名add（列名稱(chēng) 列數(shù)據(jù)類(lèi)型 defult 默認(rèn)值）注意：defult 和默認(rèn)值 可寫(xiě) 可不寫(xiě)刪除列：alter table 表名 drop column 列名稱(chēng) 注意：不管有沒(méi)有數(shù)據(jù)都會(huì)刪除修改列的數(shù)據(jù)類(lèi)型：alter table 表名 modify （列名稱(chēng) 數(shù)據(jù)類(lèi)型 default 默認(rèn)值）注意：修改的時(shí)候（和原來(lái)相同數(shù)據(jù)類(lèi)型） 長(zhǎng)度 要求不能小于數(shù)據(jù)中 數(shù)據(jù)的最小長(zhǎng)度，但是能增大，要想 改變數(shù)據(jù)類(lèi)型的話(huà)，必須將字段下的數(shù)據(jù)全部刪除 才可以更改數(shù)據(jù)類(lèi)型表名重命名rename 表名（舊） to 表名（新）；截?cái)啾韙runcate 表名；注意：清空表中的所有數(shù)據(jù) 立即釋放資源，該操作不可回滾ROWNUM 表示行號(hào)， 是一個(gè)偽列，可以在每一張表中出現(xiàn)應(yīng)用：查詢(xún)表中記錄的前五行SELECT *FROM EMP WHERE ROWNUM BETWEEN =5;現(xiàn)在要查詢(xún)從6 到 10條記錄，使用子查詢(xún)SELECT *FROM(SELECT ROWNUM num,empno,ename,salFROM emp) eeWHERE num BETWEEN 6 AND 10;視圖創(chuàng)建視圖語(yǔ)法：CREATE VIEW 視圖名稱(chēng) AS 子查詢(xún)例如：創(chuàng)建部門(mén)20員工的雇員信息，包含EMPNO,ENAME,SAL,DEPTNOCREATE VIEW empv20 ASSELECT empno,ename,sal,deptno FROM emp;注:當(dāng)創(chuàng)建視圖以后，可以像操作表一樣的操作視圖注：當(dāng)創(chuàng)建視圖時(shí)，視圖的操作會(huì)影響到對(duì)表的操作，這是不安全的，我們可以采用如下選項(xiàng)來(lái)創(chuàng)建視圖 WITH CHECK OPTION 不能更新創(chuàng)建視圖的條件 WITH READ ONLY 創(chuàng)建只讀視圖刪除視圖語(yǔ)法：DROP VIEW 視圖名稱(chēng)DROP VIEW empv20; 刪除視圖 empv20;注:當(dāng)刪除視圖所在的表時(shí)，則視圖也不能再被使用序列創(chuàng)建序列語(yǔ)法：CREATE SEQUENCE 序列名稱(chēng)MAXVALUE num | NOMAXVALUEMINVALUE num | NOMINVALUEINCREMENT BY num START WITH 10CACHE num | NOCACHE;CYCLE | NOCYCLE創(chuàng)建序列CREATE SEQUENCE myseq;序列的操作 nextVal ：取得序列的下一個(gè)內(nèi)容 currVal：取得當(dāng)前序列的內(nèi)容創(chuàng)建表驗(yàn)證序列的操作 CREATE TABLE testseq(next NUMBER;CURR NUMBER;) INSERT INTO testseq VALUES(myseq.nextVal,myseq.currVal); 將以上插入語(yǔ)句，執(zhí)行5次，觀(guān)察效果，不難得出結(jié)論，nextVal每次都會(huì)加1，而currVal都是取當(dāng)前值創(chuàng)建序列指定每次增長(zhǎng)的增量 CREATE SEQUENCE myseq INCREMENT BY 2;創(chuàng)建序列指定開(kāi)始的序列，默認(rèn)的序列從1開(kāi)始。 CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 10;創(chuàng)建一個(gè)循環(huán)序列 1，3，5，7，9。 CREATE SEQUENCE myseq MAXVALUE 9 INCREMENT BY 2START WITH 1 CACHE 2 CYCLE;數(shù)據(jù)庫(kù)的常用操作語(yǔ)言：show user; 察看當(dāng)前連接的用戶(hù)connect scott/passwored 采用scott的用戶(hù)名/密碼連接數(shù)據(jù)庫(kù)desc table_name; 察看tableName表結(jié)構(gòu)quit | exit ; 退出disconnect; 斷開(kāi)連接clear screen; 清屏，相當(dāng)于Windows下的cls命令select * from tab; 列出當(dāng)前用戶(hù)下的所有表。 path 執(zhí)行path指定的腳本文件Oracle 常用基本數(shù)據(jù)類(lèi)型:varchar2/varchar 變長(zhǎng)字符串char 定長(zhǎng)字符串Integer 整型number ( m, n) 數(shù)字型smallint 短整型float 浮點(diǎn)數(shù)decimal 十進(jìn)制數(shù)字date 日期型SQL分類(lèi) DML(Data Manipulation Language,數(shù)據(jù)操作語(yǔ)言) 用于檢索或修改數(shù)據(jù) DDL(Data Definition Language，數(shù)據(jù)定義語(yǔ)言)用于定義數(shù)據(jù)的結(jié)構(gòu)，如創(chuàng)建修改或者刪除數(shù)據(jù)庫(kù)對(duì)象 DCL(Data Control Language，數(shù)據(jù)控制語(yǔ)言)用于定義數(shù)據(jù)庫(kù)用戶(hù)的權(quán)限約束constraint 約束名 約束 字段主鍵約束（PRIMARY KEY）CONSTRAINT persion_pid_pk PRIMARY KEY(pid);非空約束：name VARCHAR2(30) NOT NULL, 唯一約束：name VARCHAR2(30) UNIQUE NOT NULL, 在創(chuàng)建表時(shí)聲明CONSTRAINT person_name_uk UNIQUE(pid) 在表的結(jié)尾聲明檢查約束：（check）age NUMBER(3) NOT NULL CHECK(age between 1 and 150),主外鍵約束：（如一本書(shū)要屬于一個(gè)人）CREATE TABLE person(pid VARCHAR2(18),name VARCHAR2(30) NOT NULL,age NUMBER(3) NOT NULL,birthdate DATE,sex VARCHAR2(2) DEFAULT 男CONSTRAINT person_pid_pk PRIMARY KEY(pid),CONSTRAINT person_name_uk UNIQUE(name),CONSTRAINT person_age_ck CHECK(age BETWEEN 1 AND 150),CONSTRAINT person_sex_ck CHECK(sex IN (男,女,中);CREATE TABLE book(bid NUMBER PRIMARY KEY NOT NULL,bname VARCHAR(30),bprice NUMBER(5,2),pid VARCHAR2(18),CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid);增加約束ALTER TABLE 表名稱(chēng) ADD CONSTRAINT 約束名稱(chēng) 約束類(lèi)型（約束字段） 例：分別增加主鍵約束和外鍵約束給book表ALTER TABLE book ADD CONSTRAINT book_bik_pk PRIMARY KEY(bik);ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGNKEY(pid) REFERENCES person(pid) ON DELETE CASCADE;約束的命名規(guī)范（建議） PRIMARY KEY ：表名稱(chēng)_主鍵名稱(chēng)_pk; UNIQUE ：表名稱(chēng)_字段名稱(chēng)_uk CHECK ：表名稱(chēng)_字段名稱(chēng)_ck 刪除約束 ALTER TABLE 表名稱(chēng) DROP CONSTRAINT 約束名稱(chēng);表空間管理創(chuàng)建表空間CREATE TABLESPACE 表空間名稱(chēng) DATAFILE 數(shù)據(jù)文件的全路徑 SIZE表空間的大小; 刪除表空間 DROP TABLESPACE 表空