Oracle基本建表语句

--创建用户

create user han identified by han default tablespace users Temporary TABLESPACE

Temp;

grant connect,resource,dba to han; //

授予用户

han

开发人员的权利

------------ 对表的操作 -----------------------

创建表格语法 :

create table 表名 （

字段名 1 字段类型 （长度） 是否为空 ,

字段名 2 字段类型 是否为空

）;

- 增加主键

alter table 表名 add constraint 主键名 primary key （ 字段名 1）;

- 增加外键 :

alter table 表名

add constraint 外键名 foreign key （ 字段名 1） references 关联表

（ 字段名 2）;

在建立表格时就指定主键和外键

create table T_STU (

STU_ID

char(5)

not null,

STU_NAME

not null,

constraint PK_T_STU primary key (STU_ID)

）;

varchar2(8)

主键和外键一起建立：

create table T_SCORE (

number(5,2),

EXAM_SCORE

EXAM_DATE

date, number(10) not null,

AUTOID

char(5),

char(3),

STU_ID

constraint PK_T_SCORE primary key (AUTOID),

SUB_ID

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

NOCYCLE NOCACHE;

select class_l from dual

-- 插入数据

insert into classes values(class_l,'

commit;

-- 更新数据

软件一班 ')

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

-- 创建唯一索引

create unique index username on stu_account(username);

不能插入相同的数据

-- 行锁 在新打开的对话中不能对此行进行操作

select * from stu_account t where _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) references

classes(id);-- 外键约束

alter table stuinfo add constraint stu_fk foreign key (stu_id) references students(id)

ON DELETE CASCADE;-- 外键约束 , 级联删除

alter table stuinfo drop constant stu_fk;

insert into students values(stu_l,' 张三 ',1,sysdate);

insert into stuinfo values(stu_l,' 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 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, // 共 7 位数字，小数 后有两位

class_id number(9) not null

}create 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) not null default 0

失

)

--0 表可用 ,1 表挂

-- 链表查询

select ame||'_'||_name as

insert into stude nts values(stu_seq .n extval,'

insert into stuinfo values(stu_l,'

班级—姓名，s from

李四：1,sysdate);

南京');

classes c,students s , stuinfo si where =_id and =_id;

-- 函数

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

-- 中间表实现多对多关联

-- (1 1， 1 n ，n 1 ， n n )

n 的表有 1 表的字段

--1 n 的描述 1 的表不作处理

主外键关联

--1 1

的描述

--n 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 number(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); -- 唯

一学生

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 999999

NOCYCLE NOCACHE;

insert into course values(couses_l,' insert

计算机原理 ');

编译原理 ');

into course values(couses_l,' insert into

course values(couses_l,' insert into course

数据库原理 ');

数据结构 ');

values(couses_l,' insert into course

计算机基础 ');

values(couses_l,' insert into course

语言初步 ');

values(couses_l,'C commit;

insert into stu_couse values(stu_couse_l,1,1);

insert into stu_couse values(stu_couse_l,1,3);

insert into stu_couse values(stu_couse_l,1,5);

insert into stu_couse values(stu_couse_l,1,5);

insert into stu_couse values(stu_couse_l,2,1);

commit;

select * from stu_couse;

select * from course;

--select _name,_id, _name from students s,course

c,stu_couse sc where stu_id=1

--select couse_id from stu_couse where stu_id=1

select ame,_name,_name from stu_couse sc, students

s,course c,classes cl where =_id and _id=_id and

_id= and =1;

-- 班级——姓名

select ame,_name from students s,classes c where _id= and

=2;

select * from students s where =2

-- 班级——姓名——课程

select ame,_name,_name from stu_couse sc,students

s,classes cl,couse c where _id= and _id=_i

=26;

and

--sql 语句的写法，现写出关联到的表，然后写出要查找的字段，第三 联条件 ，记住在写关联到的表时先写数据多的表，这样有助于提高 效率

select _name,_name from stu_couse sc,students s,course c

where _id=1 and _id=_id and _id=;

写出关

sql 的

select _name from students s,stu_couse sc where =_id by

,_name;

group

select ame,count(_id) from stu_couse sc,students s,classes c

where _id= and =_id group by ame;

select _name, count(_id) from stu_couse sc,students

s,classes cl where =_id group by ,_name having

count(_couse_id)>3;

班级 学生 选课数量

select ame,count(_couse_id) from stu_couse sc,students

s,classes cl where =_id and _id= group by ame;

-- 班级 学生 选课数量

select ame,_name,count(_couse_id) from stu_couse sc,students

s,classes cl where =_id and _id= group by _name;

select ame,_name,count(_couse_id) from stu_couse

sc ,students s,classes cl where _id= and _id=

by ;

group

select ame,_name,count(_couse_id) from stu_couse sc,students

s,classes cl where _id= and _id= group by _name;

-- 班级 学生 所选课程 id 所选课程名称

-- 创建试图 目的把表联合起来 然后看成一个表，在与其他的联合进行查询 create

view xsxk as select ame, _name,_id, _name from

stu_couse sc,students s,classes cl,couse c where _id= and

_id=_id and _id=;

select * from xsxk

create view classstu as select ,ame,_name from students s,classes

c where =_id;

drop view classstu; -- 删除视图

select * from classstu;

create view stu_couse_view as select ,_name from stu_couse

sc,students s,couse c where =_id and _id=_id; select *

from stu_couse_view;

create view csc as select ame,_name,_name from classstu

cs,stu_couse_view scv where =;

select * from csc;

select * from classes cross join students; -- 全连接，相当于 select * from

classes,students;

select * from classes cl left join students s on =_id; -- 左连接 不管左表有没有 都显示出来

select * from classes cl right join students s on =_id; -- 右连接

select * from classes cl full join students s on =_id; -- 全连接

insert into classes values(class_l,' 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 ,sum() from sales s1,sales s2 where >= group by

order by desc;

select ,sum() from sales s1,sales s2 where >= group by

;

s

年 年业绩总和 2001 200

2002 500

2003 900

2004 1400

create table test1(

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 1 MAXVALUE 999999

NOCYCLE NOCACHE;

drop sequence org_seq;

insert into org values(1,'

华建集团 ',0); 华建集团一分公司 ',1); 华建集团二分公司

insert into org values(2,'

',1); 华建集团财务部 ',1); 华建集团工程部 ',1); 华建集团一insert into org values(3,'

分公司财务处 ',2) 华建集团一分公司工程处 ',2)

insert into org values(4,'

insert into org values(5,'

select * from org;

insert into org values(6,'

-- 不正确 不能实现循环

insert into org values(7,'

select _id , _name ,_id from org a,org b

where _id=7 and _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 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,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;alter

table shop_jb change price1 price double;

学生姓名 平均分

select _name,avg() from stu_couse sc,chengji cj,students s where

=_id and _couse_id=_couse_id group by ,_name;

select _name from students s,stu_couse sc,chengji cj where =_id and

_couse_id=_couse_id group by ,_name;

select _name, from students s,stu_couse sc,chengji cj where

=_id and _couse_id=_couse_id and >60;

学生姓名 科目 成绩

select _name,_name, from stu_couse sc,students

s,couse c,chengji cj where _id= and _id=_id and

_couse_id=_couse_id and >60 order by=;

select * from stu_couse;

-- 集合运算

-- 选择了课程 3 的学生 union 选择了课程 5 的学生 并集

-- 选择了课程 3 或者 选择了课程 5 的学生

select _name from students s,couse c,stu_couse sc where =_id and

_id=_id and _id=3 union

select _name from students s,couse c,stu_couse sc where =_id and

_id=_id and _id=5

-- 选择了课程 3，5，2 的学生 intersect 选择课程 1，2，4 的学生 交

集

-- 求选择了课程 2 并且 选择了课程 3 的学生 交集 select _name from students

s,couse c,stu_couse sc where =_id and _id=_id and

_id=2 intersect

select _name from students s,couse c,stu_couse sc where =_id and

_id=_id and _id=3;

-- 选择了课程 3，5，8 的学生

差集

minus 选择了课程 1，7，8 的学生

-- 求所有课程的成绩都大于 60 的学生 差集

select distinct(_name) from stu_couse sc,students s,couse c,chengji cj where

_id= and _id=_id and _couse_id=_couse_id

and >60

minus

select distinct(_name) from stu_couse sc,students s,couse c,chengji cj where

_id= and _id=_id and _couse_id=_couse_id

and <60;