基本建表語句

1、- - 創(chuàng)建用戶create user han identified by han default tablespace users Temporary TABLESPACE Temp;grant connect,resource,dba to han; / 授予用戶han 開發(fā)人員的權(quán)利 對(duì)表的操作創(chuàng)建表格語法:create table 表名 ( 字段名 1 字段類型( 長度 ) 是否為空 ,字段名 2 字段類型是否為空);- 增加主鍵 alter table 表名 add constraint 主鍵名 primary key (字段名 1);- 增加外鍵 :alter table 表名 a

2、dd constraint 外鍵名 foreign key ( 字段名 1) references 關(guān)聯(lián)表 ( 字段名 2);在建立表格時(shí)就指定主鍵和外鍵create table T_STU (STU_IDchar(5)not null,STU_NAMEvarchar2(8)not null, constraint PK_T_STU primary key (STU_ID) );主鍵和外鍵一起建立：create table T_SCORE (EXAM_SCOREnumber(5,2),EXAM_DATEdate,AUTOIDnumber(10)not null,char(5),STU_IDSU

3、B_IDchar(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(id number(9) not null primary key, classname varchar2(40) not null)- - 查詢表select * from classes;- - 刪除表drop table students;- - 修改表的名稱rename a

4、list_table_copy to alist_table;- - 顯示表結(jié)構(gòu)describe test - 不對(duì)沒查到 對(duì)字段的操作- - 增加列alter table test add 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 n

5、ot null, name varchar2(34)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 where count_i

6、d=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 students add constr

7、aint 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,張三 ,1,sysdate);insert into stuinfo

8、 values(stu_seq.currval,威海 );select * from stuinfo;create table zhuce(zc_id number(9) not null primary key,stu_id number(9) not null, zhucetime 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,2) not nu

9、ll 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,/ 共 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 default 0,status number(1

10、) not null default 0-0 表可用 ,1 表掛失)- - 鏈表查詢select c.classname|_|s.stu_name as 班級(jí)_姓名,si.address fromclasses c,students s , stuinfo si where c.id=s.class_id ands.id=si.stu_id;insert into students values(stu_seq.nextval,李四 ,1,sysdate);insert into stuinfo values(stu_seq.currval,南京 );- - 函數(shù)select rownum,i

11、d,stu_name from students t order by id asc;- - 中間表實(shí)現(xiàn)多對(duì)多關(guān)聯(lián)- - ( 11 ， 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)create table 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 num

12、ber(9) primary key,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 MAXVALU9E99999 NOCYCLE NOCACHE;create sequence couses_seq increment by 1 start with 1 MAXVALUE 999

13、999 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ù)庫原理);insert into course values(couses_seq.nextval,數(shù)據(jù)結(jié)構(gòu));insert into course values(couses_seq.nextval,計(jì)算機(jī)基礎(chǔ));insert into course val

14、ues(couses_seq.nextval,C語言初步);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.nextval,1,5);insert into stu_couse values(stu_couse_seq.nextval,1,5);insert into stu_couse values(stu_couse_

15、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_couse where stu_id=1select cl.classname,s.stu_name,c.couser_name from stu_couse sc, students s,course c

16、,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 wheres.class_id=c.id and s.id=2;select * from students s where s.id=2- - 班級(jí)姓名課程select cl.classname,s.stu_name,c.couse_name from stu_couse sc,stud

17、ents s,classes cl,couse c where sc.stu_id=s.id and sc.couse_id=c.couse_id and s.id=26;- -sql 語句的寫法，現(xiàn)寫出關(guān)聯(lián)到的表，然后寫出要查找的字段，第三寫出關(guān)聯(lián)條件，記住在寫關(guān)聯(lián)到的表時(shí)先寫數(shù)據(jù)多的表，這樣有助于提高sql 的效率select c.couser_name,s.stu_name from stu_couse sc,students s,course cwhere c.course_id=1 and c.course_id=sc.couse_id and sc.stu_id=s.id;sele

18、ct 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,studentss,classes c where s.class_id=c.id and s.id=sc.stu_id group by c.classname;select s.stu_name, count(sc.couse_id) from stu_couse sc,studentss,classes

19、 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,studentss,classes cl where s.id=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

20、_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_cousesc ,students 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

21、_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 as select cl.classname, s.stu_name,c.couse_id, c.couse_name from stu_couse sc,students s,classes cl,couse c wher

22、e 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_id;drop view classstu; - 刪除視圖select * from classstu;create view stu_couse_view as select s.id ,c.couse_name from

23、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 cs,stu_couse_view scv where cs.id=scv.id;select * from csc;select * from classes cross join students; - 全連接，相當(dāng)于

24、select * fromclasses,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; -右連接軟件四班);select * from classes cl full join students s on cl.id=s.class_id; - 全連接insert into classes values(class_seq

25、.nextval,create table sales(nian varchar2(4), yeji number(5);insert 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

26、 s1.nian=s2.nian group 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;年年業(yè)績總和20012002200320042005009001400create table test1(t_id number(4);create table org(org_id number(9) not null primary key, org_name varchar2(40) not nul

27、l, parent_id number(9);create sequence org_seq increment by 1 start with 1 MAXVALUE 999999NOCYCLE NOCACHE; drop sequence org_seq; insert into org values(1, insert into org values(2, insert into org values(3, insert into org values(4,華建集團(tuán),0);華建集團(tuán)一分公司,1)華建集團(tuán)二分公司,1)華建集團(tuán)財(cái)務(wù)部,1);華建集團(tuán)工程部,1);華建集團(tuán)一分公司財(cái)務(wù)處,2);

28、華建集團(tuán)一分公司工程處,2);insert into org values(5,insert into org values(6,insert into org values(7,select * from org;- 不正確 不能實(shí)現(xiàn)循環(huán)select _id , _name ,b.parent_id from org a,org b _id=7 and a.parent_id=_id;select * from org connect by prior parent_id=org_id start with org_id=7order by

29、org_id;select * from org connect by prior org_id=parent_id start with org_id=1 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,62);insert into chengji values(2,2,90);insert into chengji values(3,

30、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_idstu_couse_id;alter table shop_jb change price1 price double;學(xué)生姓名平均分select s.stu_na

31、me,avg(cj.fen) from stu_couse sc,chengji cj,students swhere 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 bys.id,s.stu_name;select s.stu_name,cj.fen

32、from students s,stu_couse sc,chengji cj where s.id=sc.stu_id and sc.stu_couse_id=cj.stu_couse_id and cj.fen60;學(xué)生姓名科目成績select s.stu_name,c.couse_name,cj.fen from stu_couse sc,studentss,couse c,chengji cj where sc.stu_id=s.id and sc.couse_id=c.couse_id and sc.stu_couse_id=cj.stu_couse_id and cj.fen60 order