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

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