Oracle隨堂筆記

1、知識(shí)點(diǎn)四：多表查詢 emp dept分為4種1、等連接2、外連接3、自連接4、子查詢3-1、等連接問(wèn)題：顯示員工姓名及所在部門的名稱分析：姓名-emp.ename部門名稱-dept.dname關(guān)系：emp(deptno) dept(deptno)- -顯示員工姓名及所在部門的名稱select emp.ename , dept.dname from emp , deptwhere emp.deptno = dept.deptno3-2、外連接問(wèn)題：查詢所有部門名稱和對(duì)應(yīng)的員工姓名，若該部門沒(méi)有員工，只顯示部門名稱分析：部門名稱dept.dname員工姓名 emp.enameOracle中使用(+

2、)表示外連接，可以理解 (+)所在字段的對(duì)側(cè)(以二分開(kāi)左右兩部分)為主要顯示信息- -查詢所有部門名稱和對(duì)應(yīng)的員工姓名，若該部門沒(méi)有員工，只顯示部門名稱select dept.dname , emp.ename from emp , deptwhere emp.deptno (+) = dept.deptno3-3、自連接問(wèn)題：顯示員工姓名及其上級(jí)員工的姓名分析：?jiǎn)T工姓名 emp.ename上級(jí)員工姓名 select emp1.ename下級(jí)員工姓名,emp2.ename 上級(jí)員工姓名 from emp emp1 , emp emp2where emp2.empno = emp

3、1.mgr- -顯示員工姓名及其上級(jí)員工的姓名，進(jìn)行外連接3-4、子查詢問(wèn)題：查詢工資高于公司平均工資的所有員工分析：公司的平均工資select avg(sal) from emp;select * from empwhere sal > ( select avg ( sal ) from emp )補(bǔ)充:Oracle數(shù)據(jù)類型-合理劃分空間1 字符類型Char-固定長(zhǎng)度-12000個(gè)字節(jié)varchar和varchar2-可變長(zhǎng)度-14000個(gè)字符long 長(zhǎng)字符類型2GB2 數(shù)值類型Integer-整型類型Number（長(zhǎng)度，精度）Number （4,1） -999.9999.9Numb

4、er（2） -99993 日期類型date 一般日期類型-顯示到秒timestamp 日期時(shí)間-精確到秒后8 位4 二進(jìn)制類型- 音樂(lè)，圖片和文件LOB 12000字節(jié)VLOB 14000字節(jié)5 大對(duì)象類型CLOB字符大對(duì)象類型4GBBLOB 二進(jìn)制大對(duì)象類型2GBFLOB文件大對(duì)象類型4GBOracle的數(shù)據(jù)庫(kù)對(duì)象七大對(duì)象：用戶、表、約束、序列、視圖、同義詞和索引1、創(chuàng)建用戶?2、授權(quán)?授權(quán) 咆建用戶grant create session to nacy identified by密碼3、鎖定賬戶Alter user 用戶名 account lock4、解鎖賬戶?5、修改用戶?,6、撤銷授

5、權(quán)?7、刪除賬戶?知識(shí)點(diǎn)一：用戶 -User -賬戶、管理員-一切對(duì)象的宿主知識(shí)點(diǎn)二：表 -table -維護(hù)使用sql語(yǔ)句完成數(shù)據(jù)存儲(chǔ)1、創(chuàng)建表 class student2、修改表student添力口 telephone和address兩個(gè)字段-創(chuàng)建 class 表classid , classname 】 create table class (I classid number ( 2), classname varchar2 (10)-studentstuid,stuname,stusex,stuReg,classid create table student( stuid numbe

6、r ( 2), stuname varchar2 (10), stusex char ( 2) default '男'check(stusex in (男，女),stuReg date default sysdate , -date timestamp classid number ( 2) )-修改表student 添加telephone 和address 兩個(gè)字段alter table studentadd (telephonevarchar2 (11),3、修改student表中telephone這個(gè)字段 number(8)類型address varchar2 ( 50)

7、- 修改student 表中telephone這個(gè)字段number(8)類型alter table studentmodify (telephonenumber (8)-創(chuàng)建表的過(guò)程中添加主鍵約束（列級(jí)約束）create table class1 （4、修改student表，刪除stuname字段-修改student 表，刪除address字段alter table student dropcolumn address5、修改字段名稱-修改student表，把字段名telephone改成 phonealter table student renamecolumn telephoneto pho

8、ne6、給表改名字-修改student 表名為sturename studentto stu7、刪除表? ? ? ?知識(shí)點(diǎn)三：約束 -constraint嗷據(jù)完整性和一致性大體分類：實(shí)體完整性-減少數(shù)據(jù)冗余-主鍵約束域完整性-數(shù)據(jù)的準(zhǔn)確性-check約束引用完整性-數(shù)據(jù)的一致性-外鍵約束Oracle 約束象 5 大分類：主鍵（primary key）、非空（not null）、唯一性（unique）、檢查（check）、外鍵（foreign key） 創(chuàng)建約束的兩種方式：create table添加約束、alter table添加約束1、如何添加主鍵約束主鍵約束不允許為空classid num

9、ber ( 2) primary key , classname varchar2 (10) )-向已創(chuàng)建的表中添加主鍵約束alter table class add primary key (classid )-刪除表中主鍵約束1.查找主鍵名稱select* from user constraints2.通過(guò)主鍵名稱刪除主鍵alter table classl drop constraintSYS C0011510-添加主鍵約束的過(guò)程中自定義主鍵約束的名稱 alter table class1 add constraint pk classid primary key ( classid )

10、-刪除表中自定義名稱的主鍵約束 ? ? ? ? ? -創(chuàng)建表的時(shí)候添加自定義名稱的主鍵約束(表 級(jí)別約束) create table class2 ( classid number ( 2),classname varchar2 (10), constraint pk_classid2 primary key (classid ) )2、唯一約束unique允許數(shù)據(jù)為空，而且可以多行數(shù) 據(jù)為空-向已經(jīng)創(chuàng)建的表中添加唯一性約束altertableclassaddunique (classname );-創(chuàng)建表的過(guò)程中添加唯一性約束(列級(jí)約束)SYS C0011516- -創(chuàng)建表的過(guò)程中添加唯一性

11、約束(表級(jí)約束) create table class2 ( classid number ( 2),classname varchar2 ( 30), constraintpk_classid primarykey (classid ), constraintun classnameunique (classname ) )- -向已經(jīng)創(chuàng)建的表中添加唯一性約束(自定義名 稱) alter table class add constraint un classnameunique (classname );- -刪除表中唯一性約束alter table class1 drop constrai

12、nt un classname3、非空null(default)列級(jí)約束-向已經(jīng)創(chuàng)建的表中添加非空約束alter table stu modify (stuname not null )- -向已經(jīng)創(chuàng)建的表中刪除非空約束alter table stu modify (stuname null ) -創(chuàng)建表的過(guò)程中添加非空約束(只有列級(jí)約束) create table stul ( stuid number (2) primary key , stuname varchar2 ( 10) not null , stusex varchar2 (4), stureg date ,classid n

13、umber ( 2) ) Default 約束它的用法和not null 是一樣的 -向已經(jīng)創(chuàng)建的表中添加default 約束 alter table stu modify (stusex default default null )-向已經(jīng)創(chuàng)建的表中刪除default 約束 alter table stu modify (stuname- -創(chuàng)建表的過(guò)程中添加default 約束(只有列級(jí)約束)create table stu1 (stuidnumber (2) primary key,stuname varchar2 (10) not null , stusex varchar2 (4)

14、default '男 stureg date ,classid number ( 2) )4、check 約束- -向已經(jīng)創(chuàng)建的表中添加check約束alter table stu1 add check (stusex in (男，女)- -創(chuàng)建表的過(guò)程中添加check約束(列級(jí)約束) create table stu1 ( stuidnumber (2) primary key ,stuname varchar2 ( 10) not null , stusex varchar2 ( 4) check(stusex in (男，女)， stureg date ,classid numb

15、er ( 2) )- -創(chuàng)建表的過(guò)程中去創(chuàng)建check約束(表級(jí)約束) create table stu2 ( stuid number (2) primary key ,stuname varchar2 ( 10) not null , stusex varchar2 (4) default '男', stureg date default sysdate ,classid number ( 2),constraint ck_stusex check (stusex in(男，女)constraint notNull_stuname check(stuname is not

16、null); constraint default_stusex check(stusex defalt男');)-不提倡這么做-刪除創(chuàng)建的check約束 ?5、外鍵約束向已經(jīng)創(chuàng)建的表中添加外鍵約束 alter table stu1 addconstraint fk_classid foreign key (classid ) references class (classid ) -創(chuàng)建表的過(guò)程中添加外鍵約束(列級(jí)約束) create table stu3 ( stuid number (2) primary key , stuname varchar2 ( 10) not null

17、 , stusex varchar2 (4) default '男' check ( stusex in ('男'，'女')， stureg date default sysdate , classid number ( 2) references class (classid ) )-創(chuàng)建表的過(guò)程中添加外鍵約束（表級(jí)約束） create table stu3 （stuidnumber （2） primary key,stuname varchar2 （10） not null ,stusex varchar2 （4） default '

18、男' check （ stusex in （'男'，'女'）， stureg date default sysdate , classid number （ 2）,constraintfk1 classid foreignkey （ classid ） references class （classid ） ）注意:如果報(bào)錯(cuò)ORA-01950: 對(duì)表空間'USERS' 無(wú)權(quán)限步驟一：conn system/123456步驟二：alter user zhangsan0323 quota unlimited on users;總結(jié)：添加約束【

19、主鍵、唯一、 check、foreign keyAlter table 表名 add constraint 自定義約束名primary key（段名）|unique（段名）| check（.| 段名 between 0 and 100| 段名 in（男，女）foreign key（段名）references主鍵表名（主鍵段）添加約束【非空、缺省】Altert table 表名 modify （列名非空|缺?。┳⒁猓簊elect * from user_constraints;select * from user_cols_cons知識(shí)點(diǎn)四：序列（sequence）來(lái)實(shí)現(xiàn)字段的自增長(zhǎng)特性dba

20、sqlserver identifiedmysql auto_increment語(yǔ)法：create sequence序歹U名seq_描述start with 起始值 10increment by 步長(zhǎng) 3maxvalue 最大值 20minvalue 最小值nocycle不循環(huán)cycle循環(huán)cache緩存1的數(shù)字問(wèn)題1 :建立序列，從1開(kāi)始每次增加1問(wèn)題2:如何得到序列值?問(wèn)題3:建立序列，從10開(kāi)始每次增加3個(gè) 最大值20,循環(huán)？問(wèn)題 4 ： minvalue 和 startwith 關(guān)系？1 . create sequence seq_1start with 1increment by 1

21、2 .create sequence seq_1（默認(rèn)從1開(kāi)始每次增加1,不循環(huán)）序列名.currval:獲得序列當(dāng)前值select seq_1.currval fromdual序列名.nextval:實(shí)現(xiàn)序列自增加select seq_1.nextval fromdual-建立序列，從10開(kāi)始每次增加3個(gè)，最大值20 ,循環(huán)create sequence seq_3start with 10increment by 3minvalue 10maxvalue 20cyclecache 21. start with 不能小于 minvalue 的值2 .如果 minvalue 小于 start

22、with ,序列從 start with 開(kāi)始, 進(jìn)入循環(huán)后從 minvalue值開(kāi)始至maxvalue3 .如果不設(shè)定 minvalue循環(huán)后從1開(kāi)始問(wèn)題5:序列的增長(zhǎng)超過(guò)了 maxvalue的結(jié)果？Cycle參數(shù)存在，則從minvalue開(kāi)始循環(huán)Nocycle參數(shù)存在（默認(rèn)值），則報(bào)錯(cuò)誤刪除序列：drop sequence seq_1修改序列？ alter sequence序列名 注忌：startwith不能修改刪除序列重新建立-利用序列完成class表中classid的數(shù)據(jù)插入功能- -步驟一：創(chuàng)建一個(gè)表create table newclass (classid number (4)

23、primary key , classname varchar (20) not null )- -步驟二：創(chuàng)建序列create sequence seq_classid- -步驟三：利用序列向表中插入三組數(shù)據(jù)(小班，中班，大班) insert into newclass (classid , classname )values ( seq_classid.nextval ,'大班')知識(shí)點(diǎn)五：視圖view -簡(jiǎn)化查詢、提高安全性語(yǔ)法 ：create or replace view v_名as復(fù)雜的select語(yǔ)句查詢使用： select * from v_名【視圖定義名稱】w

24、here group by having order by注意：有權(quán)限才能創(chuàng)建視圖grant create view to用戶 不在resource角色的權(quán)限里步驟一：在system用戶下給用戶賦予創(chuàng)建視圖的系統(tǒng)權(quán)限grant create view to scott;步驟一：定義視圖- -希望去查詢員工姓名和部門名稱，以及員工的工資等級(jí)作為一個(gè)視 圖create or replace view v e d sasselect emp.ename , dept.dname , salgrade.gradefrom emp , dept , salgradewhere emp.deptno =

25、dept.deptno and emp.sal > salgrade.losal and emp.sal <salgrade.hisal步驟二：使用視圖select * from v e d s where grade = 4;select * from v e d sorder by grade desc步驟三：刪除視圖drop view v e d s;知識(shí)點(diǎn)六：同義詞（synonym）對(duì)象table的別名 語(yǔ)法： create public synonym 名 for 對(duì)象 分類：公有-授權(quán)用戶可以使用，私有-創(chuàng)建用戶可以使用問(wèn)題1 ：創(chuàng)建scott用戶下的emp表的同義詞問(wèn)題2 :公有同義詞創(chuàng)建私有同義詞create synonym sy_e f