Oracle_基本建表語句

--建立使用者

create user han identified by han default tablespace

users Temporary TABLESPACE Temp;

grant connect,resource,dba to han; //授予使用者han開發人員的權利

--------------------對表的操作--------------------------

建立表格文法:

    createtable 表名(

字段名1 字段類型(長度) 是否為空,

字段名2 字段類型是否為空

);

-增加主鍵

    altertable 表名 add constraint 主鍵名 primary key (字段名1);

-增加外鍵:

    altertable 表名

      add constraint 外鍵名foreign key (字段名1)

        references 關聯表 (字段名2);

在建立表格時就指定主鍵和外鍵

create tableT_STU (

     STU_ID              char(5)                        not null,

     STU_NAME            varchar2(8)                    not null,

     constraint PK_T_STU primary key (STU_ID)

);

主鍵和外鍵一起建立：

create tableT_SCORE (

     EXAM_SCORE          number(5,2),

     EXAM_DATE           date,

      AUTOID              number(10)                     not null,

     STU_ID              char(5),

     SUB_ID              char(3),

      constraint PK_T_SCORE primary key(AUTOID),

      constraint FK_T_SCORE_REFE foreign key(STU_ID)

       references T_STU (STU_ID)

)

--建立表

create table classes(

      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;

--顯示表結構

describe test --不對沒查到

-----------------------對字段的操作-----------------------------------

--增加列

alter table test add address varchar2(40);

--删除列

alter table test drop column address;

--修改列的名稱

alter table test modify address addresses varchar(40;

--修改列的屬性

alter table test modi

create table test1(

      id number(9) primary key not null,

      name varchar2(34)

     )

rename test2 to test;

--建立自增的序列

create sequence class_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLENOCACHE;

select class_seq.currval from dual

--插入資料

insert into classes values(class_seq.nextval,'軟體一班')

commit;

--更新資料

update stu_account set username='aaa' where count_id=2;

--建立唯一索引

create unique index username on stu_account(username);   --唯一索引不能插入相同的資料

--行鎖 在新打開的對話中不能對此行進行操作

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 constraint class_fk foreign key (class_id) referencesclasses(id);--外鍵限制

alter table stuinfo add constraint stu_fk foreign key (stu_id) referencesstudents(id) ON DELETE CASCADE;--外鍵限制,級聯删除

alter table stuinfo drop constant stu_fk;  

insert into students values(stu_seq.nextval,'張三',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,

      zhucetime date default sysdate

create table feiyong (

      fy_id number(9) not null primary key,

      mx_id number(9) not null,

      yijiao number(7,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) notnull,     //共7位數字，小數後有兩位

      class_id number(9) not null

}

create table card(

      card_id number(9) primary key,

      money number(7,2) not null default 0,

      status number(1) not null default0   --0表可用,1表挂失

--連結清單查詢

select c.classname||'_'||s.stu_name as 班級_姓名,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,'李四',1,sysdate);

insert into stuinfo values(stu_seq.currval,'南京');

--函數

select rownum,id,stu_name from students t order by id asc;

--中間表實作多對多關聯

--（1   1，1   n，n 1，n n ）

--1 n的描述   1的表不作處理  n的表有1表的字段

--1 1的描述主外鍵關聯

--n n的描述 中間表實作多對多關聯

createtable course(

        course_id number(9) not null,

        couser_name varchar2(40) notnull

alter table course to couse;

create table stu_couse(

      stu_couse_id number(9) primary key,

      couse_id number(9) not null

create unique index stu_couse_unq onstu_couse(stu_id,couse_id); --唯一學生

create sequence stu_couse_seq increment by 1 start with 1 MAXVALUE 999999NOCYCLE NOCACHE;

create sequence couses_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLENOCACHE;

insert into course values(couses_seq.nextval,'計算機原理');

insert into course values(couses_seq.nextval,'編譯原理');

insert into course values(couses_seq.nextval,'資料庫原理');

insert into course values(couses_seq.nextval,'資料結構');

insert into course values(couses_seq.nextval,'計算機基礎');

insert into course values(couses_seq.nextval,'C語言初步');

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,2,1);

select * from stu_couse;

select * from course;

--select s.stu_name,sc.couse_id, c.couser_name fromstudents s,course c,stu_couse sc where stu_id=1

--select couse_id from stu_couse where stu_id=1

select cl.classname,s.stu_name,c.couser_name from stu_cousesc, students s,course c,classes cl where s.id=sc.stu_id andsc.couse_id=c.course_id and s.class_id=cl.id and s.id=1;

--班級——姓名

select c.classname,s.stu_name from students s,classes c where s.class_id=c.idand s.id=2;

select * from students s where s.id=2

--班級——姓名——課程

select cl.classname,s.stu_name,c.couse_name from stu_cousesc,students s,classes cl,couse c where sc.stu_id=s.id andsc.couse_id=c.couse_id and s.id=26;

--sql 語句的寫法，現寫出關聯到的表，然後寫出要查找的字段，第三 寫出關聯條件，記住在寫關聯到的表時先寫資料多的表，這樣有助于提高sql的效率

select c.couser_name,s.stu_name from stu_couse sc,studentss,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 wheres.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 cwhere 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_cousesc,students s,classes cl where s.id=sc.stu_id group by s.id,s.stu_name havingcount(sc.stu_couse_id)>3;

班級 學生 選課數量

select cl.classname,count(sc.stu_couse_id) from stu_couse sc,students s,classescl where s.id=sc.stu_id and s.class_id=cl.id group by cl.classname;

--班級 學生 選課數量

select cl.classname,s.stu_name,count(sc.stu_couse_id) from stu_cousesc,students s,classes cl where s.id=sc.stu_id and s.class_id=cl.id group bys.stu_name;

select cl.classname,s.stu_name,count(sc.stu_couse_id) fromstu_couse sc ,students s,classes cl where sc.stu_id=s.id and s.class_id=cl.idgroup by s.id;

select cl.classname,s.stu_name,count(sc.stu_couse_id) fromstu_couse sc,students s,classes cl where sc.stu_id=s.id and s.class_id=cl.idgroup by s.stu_name;

--班級 學生 所選課程id 所選課程名稱

--建立試圖 目的把表聯合起來 然後看成一個表，在與其他的聯合進行查詢

create view xsxk as select cl.classname, s.stu_name,c.couse_id, c.couse_namefrom stu_couse sc,students s,classes cl,couse c where sc.stu_id=s.id andsc.couse_id=c.couse_id and s.class_id=cl.id;

select * from xsxk

create view classstu as select s.id,c.classname,s.stu_name from studentss,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 stu_cousesc,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 classstucs,stu_couse_view scv where cs.id=scv.id;

select * from csc;

select * from classes cross join students; --全連接配接，相當于select* from classes,students;

select * from classes cl left join students s oncl.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.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);

select * from sales;

drop table sale;

select s1.nian,sum(s2.yeji) from sales s1,sales s2 where s1.nian>=s2.niangroup by s1.nian order by s1.nian desc;

select s1.nian,sum(s2.yeji) from sales s1,sales s2 wheres1.nian>=s2.nian group by s1.nian;

s

年年業績總和

      2001     200

      2002     500

      2003     900

      2004     1400

      t_id number(4)

create table org(

      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 1MAXVALUE 999999 NOCYCLE NOCACHE;

drop sequence org_seq;

insert into org values(1,'華建集團',0);

insert into org values(2,'華建集團一分公司',1);

insert into org values(3,'華建集團二分公司',1);

insert into org values(4,'華建集團财務部',1);

insert into org values(5,'華建集團工程部',1);

insert into org values(6,'華建集團一分公司财務處',2);

insert into org values(7,'華建集團一分公司工程處',2);

select * from org;

--不正确 不能實作循環

select b.org_id , b.org_name ,b.parent_id from org a,org b where a.org_id=7 anda.parent_id=b.org_id;

select * from org connect by prior parent_id=org_id start with org_id=7 orderby org_id;

select * from org connect by prior org_id=parent_id start with org_id=1 orderby 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,3,85);

insert into chengji values(4,4,45);

insert into chengji values(5,5,68);

insert into chengji values(6,6,87);

select * from chengji;

--在oracle 中好像不适用 alter tablechengji change stu_cou_id stu_couse_id;alter table shop_jb change price1 pricedouble;

學生姓名平均分

select s.stu_name,avg(cj.fen) from stu_couse sc,chengji cj,students s wheres.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_idand 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,chengji cj wheres.id=sc.stu_id and sc.stu_couse_id=cj.stu_couse_id and cj.fen>60;

學生姓名科目成績

select s.stu_name,c.couse_name,cj.fen from stu_couse sc,students s,cousec,chengji cj where sc.stu_id=s.id and sc.couse_id=c.couse_id and sc.stu_couse_id=cj.stu_couse_idand cj.fen>60 order by=;

--集合運算

--選擇了課程3的學生   union 選擇了課程5的學生并集

--選擇了課程3 或者 選擇了課程5的學生

select s.stu_name from students s,couse c,stu_couse sc where s.id=sc.stu_id andsc.couse_id=c.couse_id and c.couse_id=3

union

select s.stu_name from students s,couse c,stu_couse sc where s.id=sc.stu_id andsc.couse_id=c.couse_id and c.couse_id=5

--選擇了課程3，5，2 的學生 intersect 選擇課程1，2，4的學生交集

--求選擇了課程 2 并且 選擇了課程 3 的學生交集

select s.stu_name from students s,couse c,stu_couse sc where s.id=sc.stu_id andsc.couse_id=c.couse_id and c.couse_id=2

intersect

select s.stu_name from students s,couse c,stu_couse sc where s.id=sc.stu_id andsc.couse_id=c.couse_id and c.couse_id=3;

--選擇了課程3，5，8的學生   minus 選擇了課程1，7，8的學生   --差集

-- 求所有課程的成績都大于 60 的學生 差集

select distinct(s.stu_name) from stu_couse sc,students s,couse c,chengji cjwhere sc.stu_id=s.id and sc.couse_id=c.couse_id andsc.stu_couse_id=cj.stu_couse_id and cj.fen>60

minus

select distinct(s.stu_name) from stu_couse sc,students s,couse c,chengji cjwhere sc.stu_id=s.id and sc.couse_id=c.couse_id andsc.stu_couse_id=cj.stu_couse_id and cj.fen<60;

==================================================================================

1.-- 連接配接

conn hu/aaa;

-- 建立表空間

Create tablespace computer2005 nologging datafile 'd:\oracle\product\10.2.0\oradata\test\computer.dbf' size 50m blocksize 8192 extent management local uniform size 256k segment space management auto;

-- 建立學生基本資訊表

create table student(學号 varchar2(20 byte) not null,

                         姓名 varchar2(8 byte),

                         性别 varchar2(4 byte),

                         民族 varchar2(8 byte),

constraint pk_stud primary key(學号) using index tablespace computer2005

pctfree 10 initrans 2 maxtrans 255);

-- 建立課程資訊表

create table course(課程編号 varchar2(5 byte) not null,

                     課程類型 varchar2(6 byte),

                     課程名 varchar2(20 byte),

                     周學時 integer,

                     任課教師 varchar2(10 byte),

                     開設學期 integer,

                     考核方式 varchar2(4 byte),

constraint pk_cour primary key(課程編号) using index tablespace computer2005

-- 建立學生成績表

create table score(學号 varchar2(20 byte) not null,

                     課程編号 varchar2(5 byte) not null,

                     成績 number,

constraint pk_sc primary key(學号,課程編号)

using index tablespace computer2005 pctfree 10 initrans 2 maxtrans 255);

-- 添加外鍵

alter table score add constraint fk_stud_score foreign key(學号) references student(學号);

alter table score add constraint fk_cour_score foreign key(課程編号) references course(課程編号);

--移動表到工作表空間

alter table student move tablespace computer2005;

alter table course move tablespace computer2005;

alter table score move tablespace computer2005;

-- 使用SQL loader導入資料

host sqlldr hu/aaa control=d:\oracle\insert4.ctl log=d:\oracle\4.log

host sqlldr hu/aaa control=d:\oracle\insert5.ctl log=d:\oracle\5.log

host sqlldr hu/aaa control=d:\oracle\insert6.ctl log=d:\oracle\6.log

2.-- 建立使用者

create user hu identified by aaa;

-- 賦予權限

grant dba,connect to hu with admin option;

-- 連接配接

create tablespace student_app nologging datafile 'd:\oracle\product\10.2.0\oradata\test\student.dbf' size 50m blocksize 8192 extent management local uniform size 256k segment space management auto;

create table 學生基本資訊(學号 varchar2(20 byte) not null,

constraint pk_baseinfo primary key(學号) using index tablespace student_app

create table 課程資訊(課程編号 varchar2(5 byte) not null,

constraint pk_course primary key(課程編号) using index tablespace student_app

create table 學生成績(學号 varchar2(20 byte) not null,

constraint pk_grade primary key(學号,課程編号)

using index tablespace student_app pctfree 10 initrans 2 maxtrans 255);

alter table 學生成績 add constraint fk_info_grade foreign key(學号) references 學生基本資訊(學号);

alter table 學生成績 add constraint fk_course_grade foreign key(課程編号) references 課程資訊(課程編号);

alter table 學生基本資訊 move tablespace student_app;

alter table 課程資訊 move tablespace student_app;

alter table 學生成績 move tablespace student_app;

-- 使用SQL lpader導入資料

host sqlldr hu/aaa control=d:\oracle\insert1.ctl log=d:\oracle\1.log

host sqlldr hu/aaa control=d:\oracle\insert2.ctl log=d:\oracle\2.log

host sqlldr hu/aaa control=d:\oracle\insert3.ctl log=d:\oracle\3.log