基本建表語(yǔ)句

1、-創(chuàng)建用戶create user han identified by han default tablespaceusers Temporary TABLESPACE Temp;grant connect,resource,dba to han; /授予用戶han開發(fā)人員的權(quán)利-對(duì)表的操作-創(chuàng)建表格語(yǔ)法:     create table 表名(       字段名1 字段類型(長(zhǎng)度) 是否為空,        字段名2 字段類型

2、60;      是否為空); -增加主鍵     alter table 表名 add constraint 主鍵名 primary key (字段名1);-增加外鍵:     alter table 表名       add constraint 外鍵名 foreign key (字段名1)         references 關(guān)聯(lián)

3、表 (字段名2);在建立表格時(shí)就指定主鍵和外鍵    create table T_STU (      STU_ID               char(5)                  &

4、#160;      not null,       STU_NAME             varchar2(8)                     not n

5、ull,      constraint PK_T_STU primary key (STU_ID);主鍵和外鍵一起建立：     create table T_SCORE (       EXAM_SCORE           number(5,2),       EXAM_DATE&

6、#160;           date,        AUTOID               number(10)              &

7、#160;       not null,       STU_ID               char(5),       SUB_ID           

8、0;   char(3),       constraint PK_T_SCORE primary key (AUTOID),       constraint FK_T_SCORE_REFE foreign key (STU_ID)        references T_STU (STU_ID)-創(chuàng)建表create table classes(    &

9、#160;  id number(9) not null primary key,       classname varchar2(40) not null)      -查詢表select * from classes;-刪除表drop table students;-修改表的名稱rename alist_table_copy to alist_table;-顯示表結(jié)構(gòu)describe test -不對(duì)沒查到-對(duì)字段的操作-增加列alter table test add

10、address varchar2(40);-刪除列alter table test drop column address;-修改列的名稱alter table test modify address addresses varchar(40;-修改列的屬性alter table test modicreate table test1(       id number(9) primary key not null,       name varchar2(34) 

11、     )rename test2 to test;-創(chuàng)建自增的序列create sequence class_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;select class_seq.currval from dual-插入數(shù)據(jù)insert into classes values(class_seq.nextval,'軟件一班')commit;-更新數(shù)據(jù)update stu_account set username='aaa' wh

12、ere count_id=2;commit;-創(chuàng)建唯一索引create unique index username on stu_account(username);   -唯一索引 不能插入相同的數(shù)據(jù)-行鎖 在新打開的對(duì)話中不能對(duì)此行進(jìn)行操作select * from stu_account t where t.count_id=2 for update; -行鎖-alter table stuinfo modify sty_id to stu_id;alter table students drop constraint class_fk;alter table stu

13、dents add constraint class_fk foreign key (class_id) references classes(id);-外鍵約束alter table stuinfo add constraint stu_fk foreign key (stu_id) references students(id) ON DELETE CASCADE;-外鍵約束,級(jí)聯(lián)刪除alter table stuinfo drop constant stu_fk;   insert into students values(stu_seq.nextval,&

14、#39;張三',1,sysdate);insert into stuinfo values(stu_seq.currval,'威海');select * from stuinfo;create table zhuce(       zc_id number(9) not null primary key,       stu_id number(9) not null,       zhuce

15、time date default sysdate)create table feiyong (       fy_id number(9) not null primary key,       stu_id number(9) not null,       mx_id number(9) not null,       yijiao number(7,

16、2) not null default 0,       qianfei number(7,2) not null       )create talbe fymingxi(       mx_id number(9) not null primary key,       feiyong number(7,2) not null,  &

17、#160;  /共7位數(shù)字，小數(shù)后有兩位       class_id number(9) not nullcreate table card(       card_id number(9) primary key,       stu_id number(9) not null,       money number(7,2) not null

18、 default 0,       status number(1) not null default 0   -0表可用,1表掛失)-鏈表查詢select c.classname|'_'|s.stu_name as 班級(jí)_姓名,si.address from classes c,students s , stuinfo si where c.id=s.class_id and s.id=si.stu_id; insert into students values(stu_seq.nextval,&

19、#39;李四',1,sysdate);insert into stuinfo values(stu_seq.currval,'南京');-函數(shù)select rownum,id,stu_name from students t order by id asc;-中間表實(shí)現(xiàn)多對(duì)多關(guān)聯(lián)-（1   1， 1   n，n 1，n n ）-1 n的描述   1的表不作處理   n的表有1表的字段-1 1的描述   主外鍵關(guān)聯(lián)-n n的描述 中間表實(shí)現(xiàn)多對(duì)多關(guān)聯(lián)createtable

20、course(         course_id number(9) not null,         couser_name varchar2(40) not null)alter table course to couse;create table stu_couse(       stu_couse_id number(9) primary key, 

21、60;     stu_id number(9) not null,       couse_id number(9) not null)create unique index stu_couse_unq on stu_couse(stu_id,couse_id); -唯一學(xué)生create sequence stu_couse_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;create sequence couse

22、s_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;insert into course values(couses_seq.nextval,'計(jì)算機(jī)原理');insert into course values(couses_seq.nextval,'編譯原理');insert into course values(couses_seq.nextval,'數(shù)據(jù)庫(kù)原理');insert into course values(couses_seq.nextval,'

23、;數(shù)據(jù)結(jié)構(gòu)');insert into course values(couses_seq.nextval,'計(jì)算機(jī)基礎(chǔ)');insert into course values(couses_seq.nextval,'C語(yǔ)言初步');commit;insert into stu_couse values(stu_couse_seq.nextval,1,1);insert into stu_couse values(stu_couse_seq.nextval,1,3);insert into stu_couse values(stu_couse_seq.ne

24、xtval,1,5);insert into stu_couse values(stu_couse_seq.nextval,1,5);insert into stu_couse values(stu_couse_seq.nextval,2,1);commit;select * from stu_couse;select * from course;-select s.stu_name,sc.couse_id, c.couser_name from students s,course c,stu_couse sc where stu_id=1-select couse_id from stu_c

25、ouse where stu_id=1select cl.classname,s.stu_name,c.couser_name from stu_couse sc, students s,course c,classes cl where s.id=sc.stu_id and sc.couse_id=c.course_id and s.class_id=cl.id and s.id=1;-班級(jí)姓名select c.classname,s.stu_name from students s,classes c where s.class_id=c.id and s.id=2;select * fr

26、om students s where s.id=2-班級(jí)姓名課程select cl.classname,s.stu_name,c.couse_name from stu_couse sc,students s,classes cl,couse c where sc.stu_id=s.id and sc.couse_id=c.couse_id and s.id=26;-sql 語(yǔ)句的寫法，現(xiàn)寫出關(guān)聯(lián)到的表，然后寫出要查找的字段，第三 寫出關(guān)聯(lián)條件   ，記住在寫關(guān)聯(lián)到的表時(shí)先寫數(shù)據(jù)多的表，這樣有助于提高sql的效率select c.couser_name,s.stu_nam

27、e from stu_couse sc,students s,course c where c.course_id=1 and c.course_id=sc.couse_id and sc.stu_id=s.id;select s.stu_name from students s,stu_couse sc where s.id=sc.stu_id group by s.id,s.stu_name;select c.classname,count(sc.couse_id) from stu_couse sc,students s,classes c where s.class_id=c.id a

28、nd s.id=sc.stu_id group by c.classname;select s.stu_name, count(sc.couse_id) from stu_couse sc,students s,classes cl where s.id=sc.stu_id group by s.id,s.stu_name having count(sc.stu_couse_id)>3;班級(jí) 學(xué)生 選課數(shù)量select cl.classname,count(sc.stu_couse_id) from stu_couse sc,students s,classes cl where s.i

29、d=sc.stu_id and s.class_id=cl.id group by cl.classname;-班級(jí) 學(xué)生 選課數(shù)量select cl.classname,s.stu_name,count(sc.stu_couse_id) from stu_couse sc,students s,classes cl where s.id=sc.stu_id and s.class_id=cl.id group by s.stu_name;select cl.classname,s.stu_name,count(sc.stu_couse_id) from stu_couse sc ,stude

30、nts s,classes cl where sc.stu_id=s.id and s.class_id=cl.id group by s.id;select cl.classname,s.stu_name,count(sc.stu_couse_id) from stu_couse sc,students s,classes cl where sc.stu_id=s.id and s.class_id=cl.id group by s.stu_name;-班級(jí) 學(xué)生 所選課程id 所選課程名稱-創(chuàng)建試圖 目的把表聯(lián)合起來 然后看成一個(gè)表，在與其他的聯(lián)合進(jìn)行查詢 create view xsxk

31、 as select cl.classname, s.stu_name,c.couse_id, c.couse_name from stu_couse sc,students s,classes cl,couse c where sc.stu_id=s.id and sc.couse_id=c.couse_id and s.class_id=cl.id;select * from xsxkcreate view classstu as select s.id,c.classname,s.stu_name from students s,classes c where c.id=s.class_

32、id;drop view classstu; -刪除視圖select * from classstu;create view stu_couse_view as select s.id ,c.couse_name from stu_couse sc,students s,couse c where s.id=sc.stu_id and sc.couse_id=c.couse_id;select * from stu_couse_view;create view csc as select cs.classname,cs.stu_name,scv.couse_name from classstu

33、 cs,stu_couse_view scv where cs.id=scv.id;select * from csc;select * from classes cross join students; -全連接，相當(dāng)于select * from classes,students;select * from classes cl left join students s on cl.id=s.class_id; -左連接 不管左表有沒有 都顯示出來select * from classes cl right join students s on cl.id=s.class_id; -右連接s

34、elect * from classes cl full join students s on cl.id=s.class_id; -全連接insert into classes values(class_seq.nextval,'軟件四班');create table sales(       nian varchar2(4),       yeji number(5)       );insert

35、 into sales values('2001',200);insert into sales values('2002',300);insert into sales values('2003',400);insert into sales values('2004',500);commit;select * from sales;drop table sale;select s1.nian,sum(s2.yeji) from sales s1,sales s2 where s1.nian>=s2.nian group

36、by s1.nian order by s1.nian desc;select s1.nian,sum(s2.yeji) from sales s1,sales s2 where s1.nian>=s2.nian group by s1.nian; s       年       年業(yè)績(jī)總和        2001     200   

37、60;   2002     500       2003     900       2004     1400create table test1(       t_id number(4);create table org(    &

38、#160;  org_id number(9) not null primary key,       org_name varchar2(40) not null,       parent_id number(9);create sequence org_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;drop sequence org_seq;insert into org val

39、ues(1,'華建集團(tuán)',0);insert into org values(2,'華建集團(tuán)一分公司',1);insert into org values(3,'華建集團(tuán)二分公司',1);insert into org values(4,'華建集團(tuán)財(cái)務(wù)部',1);insert into org values(5,'華建集團(tuán)工程部',1);insert into org values(6,'華建集團(tuán)一分公司財(cái)務(wù)處',2);insert into org values(7,'華建集團(tuán)一分公司工程

40、處',2);select * from org;-不正確 不能實(shí)現(xiàn)循環(huán)select _id , _name ,b.parent_id from org a,org b where _id=7 and a.parent_id=_id;select * from org connect by prior parent_id=org_id start with org_id=7 order by org_id;select * from org connect by prior org_id=parent_id start with org_id=1

41、order by org_id;create table chengji(       cj_id number(9) not null primary key,       stu_cou_id number(9) not null,       fen number(4,1)       );insert into chengji values(1,1,

42、62);insert into chengji values(2,2,90);insert into chengji values(3,3,85);insert into chengji values(4,4,45);insert into chengji values(5,5,68);insert into chengji values(6,6,87);commit;select * from chengji;select * from stu_couse;-在oracle 中好像不適用 alter table chengji change stu_cou_id stu_couse_id;a

43、lter table shop_jb change price1 price double;學(xué)生姓名   平均分select s.stu_name,avg(cj.fen) from stu_couse sc,chengji cj,students s where s.id=sc.stu_id and sc.stu_couse_id=cj.stu_couse_id group by s.id,s.stu_name;select s.stu_name from students s,stu_couse sc,chengji cj where s.id=sc.stu_id and sc.stu_couse_id=cj.stu_couse_id group by s.id,s.stu_name;select s.stu_name,cj.fen from students s,stu_couse sc,chen