Oracle數(shù)據(jù)庫操作基本語法(共6頁)

1、精選優(yōu)質(zhì)文檔-傾情為你奉上數(shù)據(jù)庫執(zhí)行是從左到右執(zhí)行的創(chuàng)建表SQL>create table classes(classId number(2),cname varchar2(40)，birthday date);添加一個字段SQL>alter table studentadd (classId number(2);修改字段長度SQL>alter table student modify(xm varchar2(30);修改字段的類型/或是名字（不能有數(shù)據(jù)）SQLalter table student modify(xm char(30);刪除一個字段SQL>alter

2、table student drop column sal;修改表的名字SQL>rename student to stu;刪除表SQL>drop table student;插入所有字段數(shù)據(jù)SQL>insert into student values (001,salina,女,01-5月-05，10)；修改日期輸入格式SQL>alter session set nls_date_format = yyyy-mm-dd;/臨時生效，重啟后不起錯用SQL>insert into student values (001,salina,女,to_date(01-5 -

3、05,yyyy-mm-dd)，10)；SQL>insert into student values (001,salina,女,to_date(01/5 -05,yyyy/mm/dd)，10)；插入部分字段SQL>insert into student (xh,xm,sex所要插入列的列名) values(001,lison,女所要插入列的內(nèi)容);插入空值SQL>insert into student (xh,xm,sex,birthday) values(021,BLYK,男,null);一條插入語句可以插入多行數(shù)據(jù)SQL> insert into kkk (Myid

4、,myname,mydept) select empno ,ename,deptno from emp where deptno=10;查詢空值/(非空)的數(shù)據(jù)SQL>select * from student where brithday is null（/not null）;修改（更新）數(shù)據(jù)SQLupdate student set sal=sql/2（如果還有，則加“，”以此類推） where sex =男（如果是空的，則用“is null”）；更改多項數(shù)據(jù)SQL> update emp set (job,sal,comm)=(select job,sal,comm from

5、 emp where ename='SMITH') where ename='SCOTT'刪除數(shù)據(jù)1. 保存還原點SQL>savepoint aa保存點的名稱;2. 刪除數(shù)據(jù)【1】 SQL>delete from student; /刪除表的數(shù)據(jù)【2】 SQL>drop table student; /刪除表的結(jié)構(gòu)和數(shù)據(jù)【3】 SQL>delete from student where xh=001; /刪除一條記錄【4】 SQL>truncate table student; /刪除表中的所有記錄，表結(jié)構(gòu)還在，不寫日志，無法扎找回

6、的記錄，速度快查看表結(jié)構(gòu)SQL>desc student;查詢指定列SQL>select sex,xh,xm from student;如何取消重復(fù)SQL>select distinct deptno,job列名 from student表名;打開顯示操作時間的開關(guān)SQL>set timing on;為表格添加大的數(shù)據(jù)行（用于測試反應(yīng)時間）SQL>insert into users (userid,username,userpss) select * from users;統(tǒng)計表內(nèi)有多少條記錄SQL>select count(*) from users;屏蔽

7、列內(nèi)相同數(shù)據(jù)SQL>select distinct deptno,job from emp;查詢指定列的某個數(shù)據(jù)相關(guān)的數(shù)據(jù)SQLselect deptno,job,sal from emp where ename=smith;使用算數(shù)表達(dá)式SQL>select sal*12 from emp; 使用類的別名SQL>select ename “姓名”，sal*12 as “年收入” from emp；處理null(空)值SQL>select sal*13+nvl(comm,0)*13 “年工資”，ename,comm from emp;連接字符串（|）SQLselect

8、ename | is a | job from emp;Where子句的使用【1】SQL>select ename,sal from emp where sal>3000;/number的范圍確定【2】SQL>select ename,hiredate from emp where hiredate>1-1月-1982;/日期格式的范圍確定【3】SQL>select ename,sal from emp where sal>=2000 and sal<=2500;/組合條件 Like操作符：%、_SQL>select ename,sal from

9、 emp where ename like S%;/第一個字符【名字第一個字符為S的員工的信息（工資）】SQL>select ename,sal from emp where ename like _O%;/其它字符【名字第三個字符為O的員工的信息（工資）】批量查詢SQL>select * from emp where in(123,456,789);/查詢一個條件的多個情況的批量處理查詢某個數(shù)據(jù)行的某列為空的數(shù)據(jù)的相關(guān)數(shù)據(jù)SQL >select * from emp where mgr is null;條件組合查詢（與、或）SQL>select * from emp

10、where (sal>500 or job=MANAGER) and ename like J%;Order by 排序【1】SQL>select * from emp order by sal （asc）;/從低到高默認(rèn)【2】SQL>select * from emp order by sal desc;/從高到低【3】SQL>select * from emp order by deptno (asc),sal desc;/組合排序【4】SQL>select ename,sal*12 “年薪” from emp order by “年薪” (asc);SQL&

11、gt; select ename,(sal+nvl(comm,0)*12 as "年薪" from emp order by "年薪"資料分組（max、min、avg、sum、count）SQL>select max(sal),min(sal) from emp;SQL>select ename,sal from emp where sal=(select max(sal) from emp); /子查詢，組合查詢SQL> select * from emp where sal>(select avg(sal) from emp)

12、; /子查詢，組合查詢SQL> update emp set sal=sal*1.1 where sal<(select avg(sal) from emp) and hiredate<'1-1月-1982'/將工資小于平均工資并且入職年限早于1982-1-1的人工資增加10%Group by 和 having 子句/group by用于對查詢出的數(shù)據(jù)進行分組統(tǒng)計/having 用于限制分組顯示結(jié)果SQL>select avg(sal),max(sal),deptno from emp group by deptno;/顯示每個部門的平均工資和最低工資S

13、QL>select avg(sal),max(sal),deptno from emp group by deptno;/顯示每個部門的平均工資和最低工資SQL> select avg (sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;SQL> select avg (sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal);多表查詢笛卡爾集:規(guī)定多表查詢的條件是至少不

14、能少于：表的個數(shù)-1SQL> select a1.ename,a1.sal,a2.dname from emp a1,dept a2用于區(qū)別多個表中的列名 where a1.deptno=a2.deptno多表查詢中多表的關(guān)聯(lián)符;SQL> select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10; /顯示部門編號為10的部門名、員工名和工資SQL> select a1.ename,a1.sal,a2.grade from emp a1,salgr

15、ade a2 where a1.sal between某個條件 a2.losal and a2.hisal;SQL> select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;/多表排序SQL> select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;/自連接（多表查詢的特殊情況）SQL> select worker.ename,b

16、oss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='FORD'子查詢SQL> select * from emp where deptno=(select deptno from emp where ename='SMITH');SQL> select distinct過濾關(guān)鍵詞 job from emp where deptno=10;SQL> select * from emp where job in (select distin

17、ct job from emp where deptno=10);/ 如何查詢和部門10的工作相同的雇員的名字、崗位、工資、部門號。SQL> select ename ,sal,deptno from emp where sal>all (select sal from emp where deptno=30);/如何查詢工資比部門30的所有員工的工資高的員工的姓名、工資和部門號SQL> select ename ,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);SQL&g

18、t; select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');內(nèi)嵌視圖/當(dāng)在from子句中使用子查詢的時候，必須給子查詢指定別名SQL>select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) (as ) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.m

19、ysal;分頁select a1.*,rownum rn from (select * from emp) a1;/orcle為表分配的行號SQL> select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6;/查詢內(nèi)容的變化1. 所有的改動（指定查詢列）只需更改最里面的子查詢2. （排序）只需更改最里面的子查詢子查詢（用查詢結(jié)果創(chuàng)建新表）SQL> create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;合并查詢union(求并集), union all , intersect（取交集）, minus （差集）SQL> select ename,sal,job fr