標籤：
筆試題設有關係EMP(ENO,ENAME,SALARY,DNO)其中各屬性的含義依次為職工號、姓名、工資、所在部門號， 以及關係DEPT(DNO,DNAME,MANAGER)其中各含義依次為部門號、部門名稱、部門經理的職工號1.請通過SQL語句建立表EMP、DEPT。create table emp(eno number(5) primary key ,ename varchar2(5),salary number(8),dno number(3));create table dept(dno number(3) primary key,dname varchar2(10),manager number(5));2.試用SQL陳述式完成以下查詢：列出各部門中工資不低於600元的職工的平均工資。select avg(salary) from emp where salary>=600;3.寫出“查詢001號職工所在部門名稱”的關係代數運算式select dname from dept where dno in (select dno from emp where eno=‘001‘)4.請用SQL語句將”銷售部“的那些工資低於600元的職工工資上調10%。update emp set salary=salary*1.1 where sal<600 and dno=(select dno from dept where dname=‘銷售部‘)5.請用SQL語句查詢”銷售部“員工數量select count (*) from emp group by dno where dno in (select dno from dept where dname="銷售部")
進階查詢隨機返回5條記錄select * from (select ename,job from emp order by
dbms_random.value())where rownum<=5;處理空值排序select * from emp order by comm desc
nulls last(first);查詢跳過表中的偶數行select ename from (select row_number() over (order by name) rn,ename from emp) x where mod(rn,2)=1;查詢員工資訊與其中工資最高最低員工select enmae,sal,max(sal) over(),min(sal) over() from emp;連續求和select ename,sal,sum(sal) over(),
sum(sal) over(order by name) from emp;//sum(sal) over(order by ename)指的是連續求和，以ename排序的。若有兩個這樣的視窗函數，以後面的排序為主分部門連續求和select deptno,sal,
sum(sal) over(partition by deptno order by ename
) s from emp;得到當前行上一行或者下一行的資料select enamel，sal,lead(sal) over(order by sal) aaa,lag(sal) over (order by sal) bbb from emp;根據子串分組select to_char(hiredate,‘yyyy‘),avg(sal) from emp group by to_char(hiredate,‘yyyy‘);確定一年內的天數select add_months(trunc(sysdate,‘y‘),12)-trunc(sysdate,‘y‘) from dual;trunc(sysdate,‘X‘)時間截取函數，X表示參數dd--截取今天d-截取本周第一天mm-截取本月第一天y-年hh-小時mi-分鐘查詢EMP員工表下每個部門工資前兩名的員工資訊select deptno,ename,sal from emp e1 where (select count(*) from emp e2 where e2.deptno=e1.deptno and e1.ename!=e2.ename and e2.sal>e1.sal)<2 order by deptno,sal desc;select * from (select deptno,ename,sal,row_number() over (partition by deptno order by sal desc)rn from emp)where rn<3;rownum和row_number()區別http://wang09si.blog.163.com/blog/static/1701718042012101424439858/rownum 是偽列一個表中的ID有多個記錄，把所有這個ID的記錄查出來，並顯示共有多少條記錄數。（華為面試題）select id ,count (*) from tb group by id having count(*)>1;
資料字典查詢某使用者下所有表select table_name fom all_tables where owner=‘scott‘查詢EMP表中所有欄位（列）select * from all_tab_columns where table_name=‘emp‘列出表的索引列select * from sys.all_ind_columns where table_name=‘EMP‘;列出表中約束select * from all_comstraints where table_name=‘EMP‘;在oracle中描述資料字典視圖select table_name ,comments from dictionary where table_name like ‘%TABLE%‘;
Oracle 資料類型由於char是固定長度的，所以它的速度會比varchar2快得多！但程式處理起來麻煩一點，要用trim之類的函數把兩邊的空格去掉varchar2一般適用於英文和數字，Nvarchar2適用於中文和其他字元，其中N表示Unicode常量，可以解決多語言字元集之間的轉換問題Number(4,2)指的是整數佔兩位，小數佔2位Number預設為38位元據類型number（p[,s]）p表示有效資料位元數，s表示小數位varchar2()在oracle有更好的相容性，基本不用varchardate to_date(‘2015-01-01 13:14:15‘,‘yyyy-mm-dd hh24:mi:ss‘)分鐘為mi因為sql中不區分大小寫資料類型總結：http://www.cnblogs.com/yshb/archive/2012/06/19/2554279.html
Oracle 體繫結構
DDL（改變表結構）建表create table test（ID number(5) primary key,--主鍵name varchar2(10) not null,--非空birthday date,email varchar2(25) unique,--唯一age number check(age between 0 and 150),--check約束deptno number references emp(id) on delete cascade --外鍵 串聯刪除）create table emp3 as select * from emp where sal >1000;顯示表結構:describe test刪除表：drop table test修改表名稱：rename test to testing--------------------對欄位操作-----------------------------------增加列 alter table test add address varchar2(40);刪除列 alter table test drop column address;修改列名稱 alter table test address插入資料 insert into test values(1,‘ling‘,3000,1) insert into test (eno,salary) values(3,3000)修改資料 update test set ename=‘zhangsan‘ where eno=3;刪除資料 delete test （無條件刪除所有資料,逐條刪除） truncate test (不產生回退資訊，速度快) delete from test where eno=3;刪除一張表重複記錄，age,name相同即為重複記錄，id是自增唯一的delete from test where id not in (select min(id) from test group by name,age);
DML(改變資料結構)表間資料拷貝 insert into dept(id,name) select deptno,dnamem from dept;update myemp set (job,mgr)=(select job mgr from myemp where empno=7556)where empno=7779;
merge into test2 using test1on(test1.eid=test2.eid)when matched then update set name=test1.name,birth=test1.birth,sal=test1.salwhen not matched then insert (eid,name,birth,sal) values(test1.eid,test1.name,test1.birth,test1.sal);
約束not null--非空約束primary key--主鍵約束（不能重複，不可為空）unique--唯一約束，值不能重複（空值除外）check--條件約束，插入的資料必須滿足某些條件foreign key--外鍵添加主鍵 alter table person add constraint person_pid_pk PRIMARY KEY(pid)添加唯一約束 alter table person add constraint person_tel_uk UNIQUE(tel)添加檢查約束 alter table person add constraint person _age_ck CHECK(age between 0 and 150)添加主-外鍵約束，要求帶串聯刪除 alter table book add constraint person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE;刪除約束 alter table student drop unique(tel)alter table book drop CONSTRAINT person_book_pid_fk啟用約束 ALTER TABLE book enable CONSTRAINT person_book_pid_fk;禁用約束 ALTER TABLE book disable CONSTRAINT person_book_pid_fk;
視圖建立視圖 CREATE VIEW 視圖名字（欄位） AS 子查詢CREATE VIEW empv20 (empno,ename,sal) AS select empno,ename,sal from emp where deptno=20;進階視圖create or replace view empv20 (deptno,msal) as (select deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20)) with check option constraint empv20_ck;
SQL最佳化1.盡量少用IN操作符2.盡量用NOT EXISTS 或者外串連替代NOT IN操作符3.盡量不用“<>”或者“!=”操作符4.在設計表時，把索引列設定為NOT NULL5.盡量不用萬用字元“%”或者“_”作為查詢字串的第一個字元6.Where 字句中避免在索引列上使用計算7.用“>=”替代“>”8.利用SGA共用池，避開parse階段9.where後面的條件順序要求10.使用表別名，並將之作為每列的首碼11.進行了顯式或隱式的運算欄位不能進行索引12.UNION all代替union13.其他動作盡量使用packages盡量使用cached sequence 來產生 primary key很好的利用空間：如用varchar2資料類型代理char等利用SQL最佳化工具：如SQLexpert，toad,explain-table,PL/SQL;OEM14.通過改變oracle的SGA（資料庫的系統全域區）的大小大體先看這麼多，其他的一些模組該檔案內也有介紹http://pan.baidu.com/s/1o6iK00y
oracle從零開始學習筆記 三
