- <div><p>--创建用户
- create user han identified by han default tablespace
- users Temporary TABLESPACE Temp;
- grant connect,resource,dba to han; //授予用户han开发人员的权利</p><p>--------------------对表的操作--------------------------</p><p></p><p>创建表格语法:
- create table 表名(
- 字段名1 字段类型(长度) 是否为空,
- 字段名2 字段类型 是否为空
- ); </p><p>-增加主键
- alter table 表名 add constraint 主键名 primary key (字段名1);</p><p>-增加外键:
- alter table 表名
- add constraint 外键名 foreign key (字段名1)
- references 关联表 (字段名2);</p><p>在建立表格时就指定主键和外键</p><p> create table T_STU (
- STU_ID char(5) not null,
- STU_NAME varchar2(8) not null,
- constraint PK_T_STU primary key (STU_ID)
- );</p><p>
- 主键和外键一起建立:
- create table T_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)
- )</p><p></p><p></p><p>
- --创建表
- create table classes(
- id number(9) not null primary key,
- classname varchar2(40) not null
- )
- --查询表
- select * from classes;</p><p>--删除表
- drop table students;</p><p>--修改表的名称
- rename alist_table_copy to alist_table;</p><p>--显示表结构
- describe test --不对没查到</p><p>-----------------------对字段的操作-----------------------------------
- --增加列
- alter table test add address varchar2(40);</p><p>--删除列
- alter table test drop column address;</p><p>--修改列的名称
- alter table test modify address addresses varchar(40;</p><p>--修改列的属性
- alter table test modi</p><p>create table test1(
- id number(9) primary key not null,
- name varchar2(34)
- )
- rename test2 to test;</p><p>--创建自增的序列
- create sequence class_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;</p><p>select class_seq.currval from dual</p><p>--插入数据
- insert into classes values(class_seq.nextval,'软件一班')
- commit;</p><p>--更新数据
- update stu_account set username='aaa' where count_id=2;
- commit;</p><p>--创建唯一索引
- create unique index username on stu_account(username); --唯一索引 不能插入相同的数据</p><p>--行锁 在新打开的对话中不能对此行进行操作
- select * from stu_account t where t.count_id=2 for update; --行锁</p><p>
- --alter table stuinfo modify sty_id to stu_id;</p><p>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;--外键约束,级联删除</p><p>alter table stuinfo drop constant stu_fk; </p><p>insert into students values(stu_seq.nextval,'张三',1,sysdate);</p><p>insert into stuinfo values(stu_seq.currval,'威海');</p><p>select * from stuinfo;</p><p>create table zhuce(
- zc_id number(9) not null primary key,
- stu_id number(9) not null,
- zhucetime date default sysdate</p><p>)</p><p>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
- )</p><p>
- create talbe fymingxi(
- mx_id number(9) not null primary key,
- feiyong number(7,2) not null, //共7位数字,小数后有两位
- class_id number(9) not null
- }</p><p>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表挂失
- )</p><p>--链表查询</p><p>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,'南京');</p><p>--函数
- select rownum,id,stu_name from students t order by id asc;</p><p>
- --中间表实现多对多关联
- --(1 1, 1 n,n 1,n n )</p><p>
- --1 n的描述 1的表不作处理 n的表有1表的字段
- --1 1的描述 主外键关联
- --n n的描述 中间表实现多对多关联</p><p>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</p><p>)</p><p>create unique index stu_couse_unq on stu_couse(stu_id,couse_id); --唯一学生
- create sequence stu_couse_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;</p><p>
- create sequence couses_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;
- 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语言初步');
- commit;</p><p>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);</p><p>insert into stu_couse values(stu_couse_seq.nextval,2,1);
- commit;
- select * from stu_couse;
- select * from course;</p><p>--select s.stu_name,sc.couse_id, c.couser_name from students s,course c,stu_couse sc where stu_id=1</p><p>--select couse_id from stu_couse where stu_id=1</p><p>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;</p><p>--班级——姓名
- select c.classname,s.stu_name from students s,classes c where s.class_id=c.id and s.id=2;</p><p>select * from students s where s.id=2
- --班级——姓名——课程</p><p>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;</p><p>
- --sql 语句的写法,现写出关联到的表,然后写出要查找的字段,第三 写出关联条件 ,记住在写关联到的表时先写数据多的表,这样有助于提高sql的效率</p><p>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;</p><p>select s.stu_name from students s,stu_couse sc where s.id=sc.stu_id group by s.id,s.stu_name;</p><p>
- 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;</p><p>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;
- 班级 学生 选课数量
- 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;</p><p>
- --班级 学生 选课数量
- 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;</p><p>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;</p><p>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;
- --班级 学生 所选课程id 所选课程名称</p><p>
- --创建试图 目的把表联合起来 然后看成一个表,在与其他的联合进行查询
- 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;</p><p>select * from xsxk</p><p>
- create 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 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;</p><p>
- select * from classes cross join students; --全连接,相当于select * from classes,students;</p><p>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; --全连接</p><p>
- insert into classes values(class_seq.nextval,'软件四班');</p><p>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 sale