Oracle基本建表语句-

1 --创建用户
2 create user han identified by han default tablespace
3 users Temporary TABLESPACE Temp;
4 grant connect,resource,dba to han; //授予用户han开发人员的权利

 1 --------------------对表的操作--------------------------
 2 --创建表
 3 create table classes(
 4        id number(9) not null primary key,
 5        classname varchar2(40) not null
 6 )      
 7 --查询表
 8 select * from classes;
 9 
10 --删除表
11 drop table students;
12 
13 --修改表的名称
14 rename alist_table_copy to alist_table;
15 
16 --显示表结构
17 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_seq.currval from dual

--插入数据
insert into classes values(class_seq.nextval,'软件一班')
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 t.count_id=2 for update; --行锁

  1 --alter table stuinfo modify sty_id to stu_id;
  2 
  3 alter table students drop constraint class_fk;
  4 alter table students add constraint class_fk foreign key (class_id) references classes(id);--外键约束
  5 alter table stuinfo add constraint stu_fk foreign key (stu_id) references students(id) ON DELETE CASCADE;--外键约束,级联删除
  6 
  7 alter table stuinfo drop constant stu_fk;   
  8 
  9 insert into students values(stu_seq.nextval,'张三',1,sysdate);
 10 
 11 insert into stuinfo values(stu_seq.currval,'威海');
 12 
 13 select * from stuinfo;
 14 
 15 create table zhuce(
 16        zc_id number(9) not null primary key,
 17        stu_id number(9) not null,
 18        zhucetime date default sysdate
 19 
 20 )
 21 
 22 create table feiyong (
 23        fy_id number(9) not null primary key,
 24        stu_id number(9) not null,
 25        mx_id number(9) not null,
 26        yijiao number(7,2) not null default 0,
 27        qianfei number(7,2) not null
 28        
 29 )
 30 
 31 
 32 create talbe fymingxi(
 33        mx_id number(9) not null primary key,
 34        feiyong number(7,2) not null,     //共7位数字，小数后有两位
 35        class_id number(9) not null
 36 }
 37 
 38 create table card(
 39        card_id number(9) primary key,
 40        stu_id number(9) not null,
 41        money number(7,2) not null default 0,
 42        status number(1) not null default 0   --0表可用,1表挂失
 43 )
 44 
 45 --链表查询
 46 
 47 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; 
 48 insert into students values(stu_seq.nextval,'李四',1,sysdate);
 49 insert into stuinfo values(stu_seq.currval,'南京');
 50 
 51 --函数
 52 select rownum,id,stu_name from students t order by id asc;
 53 
 54 
 55 --中间表实现多对多关联
 56 --（1   1， 1   n，n 1，n n ）
 57 
 58 
 59 --1 n的描述   1的表不作处理   n的表有1表的字段
 60 --1 1的描述   主外键关联
 61 --n n的描述 中间表实现多对多关联
 62 
 63 create　table course(
 64          course_id number(9) not null,
 65          couser_name varchar2(40) not null
 66 )
 67 alter table course to couse;
 68 create table stu_couse(
 69        stu_couse_id number(9) primary key,
 70        stu_id number(9) not null,
 71        couse_id number(9) not null
 72 
 73 )
 74 
 75 create unique index stu_couse_unq on stu_couse(stu_id,couse_id); --唯一学生
 76 create sequence stu_couse_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;
 77 
 78 
 79 create sequence couses_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;
 80 insert into course values(couses_seq.nextval,'计算机原理');
 81 insert into course values(couses_seq.nextval,'编译原理');
 82 insert into course values(couses_seq.nextval,'数据库原理');
 83 insert into course values(couses_seq.nextval,'数据结构');
 84 insert into course values(couses_seq.nextval,'计算机基础');
 85 insert into course values(couses_seq.nextval,'C语言初步');
 86 commit;
 87 
 88 insert into stu_couse values(stu_couse_seq.nextval,1,1);
 89 insert into stu_couse values(stu_couse_seq.nextval,1,3);
 90 insert into stu_couse values(stu_couse_seq.nextval,1,5);
 91 insert into stu_couse values(stu_couse_seq.nextval,1,5);
 92 
 93 insert into stu_couse values(stu_couse_seq.nextval,2,1);
 94 commit;
 95 select * from stu_couse;
 96 select * from course;
 97 
 98 --select s.stu_name,sc.couse_id, c.couser_name from students s,course c,stu_couse sc where stu_id=1
 99 
100 --select couse_id from stu_couse where stu_id=1
101 
102 select 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;
103 
104 --班级——姓名
105 select c.classname,s.stu_name from students s,classes c where s.class_id=c.id and s.id=2;
106 
107 select * from students s where s.id=2
108 --班级——姓名——课程
109 
110 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;
111 
112 
113 --sql 语句的写法，现写出关联到的表，然后写出要查找的字段，第三 写出关联条件   ，记住在写关联到的表时先写数据多的表，这样有助于提高sql的效率
114 
115 select c.couser_name,s.stu_name 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;
116 
117 select s.stu_name from students s,stu_couse sc where s.id=sc.stu_id group by s.id,s.stu_name;
118 
119 
120 select c.classname,count(sc.couse_id) from stu_couse sc,students s,classes c where s.class_id=c.id and s.id=sc.stu_id group by c.classname;
121 
122 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;
123 班级 学生 选课数量
124 select cl.classname,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 cl.classname;
125 
126 
127 --班级 学生 选课数量
128 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;
129 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.id;
130 
131 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;
132 --班级 学生 所选课程id 所选课程名称
133 
134 
135 --创建试图 目的把表联合起来 然后看成一个表，在与其他的联合进行查询 
136 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 where sc.stu_id=s.id and sc.couse_id=c.couse_id and s.class_id=cl.id;
137 
138 select * from xsxk
139 
140 
141 create view classstu as select s.id,c.classname,s.stu_name from students s,classes c where c.id=s.class_id;
142 drop view classstu; --删除视图
143 select * from classstu;
144 
145 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;
146 select * from stu_couse_view;
147 
148 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;
149 select * from csc;
150 
151 
152 select * from classes cross join students; --全连接，相当于select * from classes,students;
153 
154 select * from classes cl left join students s on cl.id=s.class_id; --左连接 不管左表有没有 都显示出来
155 select * from classes cl right join students s on cl.id=s.class_id; --右连接
156 select * from classes cl full join students s on cl.id=s.class_id; --全连接
157 
158 
159 insert into classes values(class_seq.nextval,'软件四班');
160 
161 create table sales(
162        nian varchar2(4),
163        yeji number(5)
164        
165 );
166 insert into sales values('2001',200);
167 insert into sales values('2002',300);
168 insert into sales values('2003',400);
169 insert into sales values('2004',500);
170 commit;
171 select * from sales;
172 drop table sale;
173 
174 
175 select s1.nian,sum(s2.yeji) from sales s1,sales s2 where s1.nian>=s2.nian group by s1.nian order by s1.nian desc;
176 
177 select s1.nian,sum(s2.yeji) from sales s1,sales s2 where s1.nian>=s2.nian group by s1.nian;
178 
179 s
180        年       年业绩总和 
181        2001     200
182        2002     500
183        2003     900
184        2004     1400
185 
186 
187 create table test1(
188        t_id number(4)
189 );
190 
191 create table org(
192        org_id number(9) not null primary key,
193        org_name varchar2(40) not null,
194        parent_id number(9)
195 );
196 
197 create sequence org_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;
198 drop sequence org_seq;
199 insert into org values(1,'华建集团',0);
200 insert into org values(2,'华建集团一分公司',1);
201 insert into org values(3,'华建集团二分公司',1);
202 insert into org values(4,'华建集团财务部',1);
203 insert into org values(5,'华建集团工程部',1);
204 insert into org values(6,'华建集团一分公司财务处',2);
205 insert into org values(7,'华建集团一分公司工程处',2);
206 
207 select * from org;
208 --不正确 不能实现循环
209 select b.org_id , b.org_name ,b.parent_id from org a,org b where a.org_id=7 and a.parent_id=b.org_id;
210 select * from org connect by prior parent_id=org_id start with org_id=7 order by org_id;
211 select * from org connect by prior org_id=parent_id start with org_id=1 order by org_id;
212 
213 create table chengji(
214        cj_id number(9) not null primary key,
215        stu_cou_id number(9) not null,
216        fen number(4,1)
217        
218 );
219 insert into chengji values(1,1,62);
220 insert into chengji values(2,2,90);
221 insert into chengji values(3,3,85);
222 insert into chengji values(4,4,45);
223 insert into chengji values(5,5,68);
224 insert into chengji values(6,6,87);
225 commit;
226 select * from chengji;
227 select * from stu_couse;
228 --在oracle 中好像不适用 alter table chengji change stu_cou_id stu_couse_id;alter table shop_jb change price1 price double;
229 
230 学生姓名   平均分
231 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;
232 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;
233 select s.stu_name,cj.fen 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.fen>60;
234 
235 学生姓名   科目   成绩
236 select s.stu_name,c.couse_name,cj.fen from stu_couse sc,students s,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.fen>60 order by=;
237 
238 select * from stu_couse;
239 
240 --集合运算
241 --选择了课程3的学生   union 选择了课程5的学生   并集
242 --选择了课程3 或者 选择了课程5的学生
243 select s.stu_name from students s,couse c,stu_couse sc where s.id=sc.stu_id and sc.couse_id=c.couse_id and c.couse_id=3
244 union
245 select s.stu_name from students s,couse c,stu_couse sc where s.id=sc.stu_id and sc.couse_id=c.couse_id and c.couse_id=5
246 
247 --选择了课程3，5，2 的学生 intersect 选择课程1，2，4的学生    交集
248 --求选择了课程 2 并且 选择了课程 3 的学生   交集
249 select s.stu_name from students s,couse c,stu_couse sc where s.id=sc.stu_id and sc.couse_id=c.couse_id and c.couse_id=2
250 intersect
251 select s.stu_name from students s,couse c,stu_couse sc where s.id=sc.stu_id and sc.couse_id=c.couse_id and c.couse_id=3;
252 
253 
254 --选择了课程3，5，8的学生   minus 选择了课程1，7，8的学生   --差集
255 -- 求所有课程的成绩都大于 60 的学生 差集
256 select distinct(s.stu_name) from stu_couse sc,students s,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.fen>60
257 minus
258 select distinct(s.stu_name) from stu_couse sc,students s,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.fen<60

posted on 2011-12-28 12:57 阿童沐阅读(912) 评论(0) 编辑收藏举报

刷新页面返回顶部

→阿童沐

导航

公告

Oracle基本建表语句-