Oracle实验.docx

2.3.1 Oracle数据库用户创建1、进入oracle数据库系统（以cmd命令行进入oracle数据库系统）Cmd命令行：语句： sqlplus/nolog2、以管理员方式连接数据库语句： SQL connect sys/gis123 as sysdba3、以管理员身份创建用户数据库（提示：创建一个oracle用户相当于在access中创建一个数据库）Oracle中，一般不会轻易在一个服务器上创建多个数据库，在一个数据库中，不同的项目由不同的用户访问，每一个用户拥有自身创建的数据库对象，因此用户的概念在Oracle中非常重要。例：创建一个用户名为： gis 口令为：gis123的用户。语句：Create user gis identified by gis123 account unlock;4、.给用户名授予权限 一般情况下，一个普通的用户（如SCOTT），拥有CONNECT和RESOURCE两个角色即可进行常规的数据库开发工作。SQL grant connect to gis;SQL grant resource to gis;5.登陆用户名 先退出刚刚以管理员方式登录的用户名，然后用刚刚创建的用户名登录，这样我们所建立的表、视图就可以在我们所创建的用户名中操作。SQL exit 退出当前用户名；Sqlplus/nolog再次进入oracle系统；SQLconnect gis/gis123 进入刚才创建的用户名；2.3.2 建表及查询1.创建表（学生表：Student(Sno,Sname,Ssex,Sage,Sdept)，课程表：Course(Cno,Cname,Cpno,Ccredit)，学生选课表：SC(Sno,Cno,Grade) ） 1、创建学生表（Student(Sno,Sname,Ssex,Sage,Sdept）SQL create table student (sno varchar2(20) not null,sname varchar2(20) not null,ssex varchar2(20), sage varchar2(20), sdept varchar2(20); 显示学生表结构：SQLdescribe student 创建学生表约束条件（1）.设置主键（sno）SQLalter table student add primary key (sno);（2）.设置性别只能是男或女SQLalter table student add check (ssex =男 or ssex = 女);（3）设置年龄必须大于0小于100SQLalter table student add check (sage =0 and sage create table course ( cno number(20) not null ,cname varchar2(20),cpno varchar2(20), ccredit number(20);设置课程表主键（cno）SQLalter table course add primary key (cno);3、创建学生选课表（SC(Sno,Cno,Grade)）SQLcreate table sc (sno varchar2(20) not null, cno varchar2(20) not null, grade varchar2(20) not null );设置学生选课表主键（sno ,cno ）SQLalter table sc add primary key (sno,cno);2、添加记录（1）向学生表插入记录参考语句： insert into student values (X21214004,汤健峰,男,20,资环学院); insert into student values (X21214003,李茹,女,18,资环学院); insert into student values (X21214033,方颖,女,18,资环学院); insert into student values (X21214055,徐海洋,男,20,资环学院); insert into student values (X21214018,梅梦媛,女,18,资环学院); insert into student values (X21214034,余倩男,女,18,资环学院); insert into student values (X21214023,韩伟,男,20,资环学院); insert into student values (X21214035,李皖彤,女,28,资环学院); insert into student values (X21214044,王会宇,男,18,资环学院); insert into student values (X21214002,宋丽丽,女,18,资环学院); insert into student values (X21214031,陈公名,男,20,资环学院); insert into student values (X21214020,岳梦梦,女,18,资环学院); insert into student values (X21214053,李可,男,20,资环学院); insert into student values (X21214024,邹必芳,女,18,资环学院); insert into student values (X21214057,马弘扬,男,20,资环学院); insert into student values (X21214027,周先跃,男,20,资环学院); insert into student values (X21214014,李景,女,18,资环学院); insert into student values (X21214016,班婧,女,18,资环学院); insert into student values (X21214056,张晓川,男,19,资环学院); insert into student values (X21214010,孙奉盛,男,20,资环学院); insert into student values (X21214009,赵心域,男,20,资环学院); insert into student values (X21214043,韩真真,女,18,资环学院); insert into student values (X21214039,邱长龙,男,20,资环学院); insert into student values (X21214050,李敬波,男,20,资环学院); insert into student values (X21214041,王祝峰,男,20,资环学院); insert into student values (X21214028,董梦轩,男,20,资环学院); insert into student values (X21214015,李勤,女,18,资环学院); insert into student values (X21214038,孙维维,男,21,资环学院); insert into student values (X21214046,王浩,男,20,资环学院); insert into student values (X21214061,赵玏洋,女,19,资环学院); insert into student values (X21214021,赵小康,男,20,资环学院); insert into student values (X21214029,王自力,男,20,资环学院); insert into student values (X21214017,王潇,女,18,资环学院); insert into student values (X21214012,王广瑟,男,20,资环学院); insert into student values (X21214005,杨昱,女,18,资环学院); insert into student values (X21214022,汤自强,男,20,资环学院); insert into student values (X21214040,宋尚琨,男,20,资环学院); insert into student values (X21214019,靳小阳,男,20,资环学院); insert into student values (X21214049,陈铭,男,20,资环学院); insert into student values (X21214006,张明昕,男,21,资环学院); insert into student values (X21214008,郭纪伟,男,20,资环学院); insert into student values (X21214001,牛陆,男,21,资环学院); insert into student values (X21214060,曹宇剑,男,20,资环学院); insert into student values (X21214011,潘在善,男,21,资环学院); insert into student values (X21214032,张庆,男,20,资环学院); insert into student values (X21214037,马晶,男,21,资环学院); insert into student values (X21214051,陈波,男,20,资环学院); insert into student values (X21214058,姬亚闯,男,21,资环学院); insert into student values (X21214007,黄凯,男,20,资环学院); insert into student values (X21214042,王涛,男,20,资环学院); insert into student values (X21214026,吴迪,男,20,资环学院); insert into student values (X21214036,谢杰,男,20,资环学院); insert into student values (X21214045,袁理侃,男,21,资环学院);insert into student values (X21214062,地理信息系统,男,60,资环学院);（2）向课程表插入记录insert into course values (01,测学量,地理信息系统概论,3);insert into course values (02,地理信息系统概论,3);insert into course values (03,自然地理学,3);insert into course values (04,数据库原理与方法,大学计算机基础,3);insert into course values (05,c语言,大学计算机基础,3);insert into course values (06,空间数据库,数据库原理与方法,3);insert into course values (07,计算机图形学,大学计算机基础,3);insert into course values (08,网络GIS,计算机网络基础,3);insert into course values (09,计算机网络基础,大学计算机基础,3);insert into course values (10,大学计算机基础,3);insert into course values (11,GPS测量原理,地理信息系统概论,3);insert into course values (12,地质地貌学,地理信息系统概论,3);insert into course values (13,高等数学,3);insert into course values (14,大学英语,3);insert into course values (15,大学语文,3);insert into course values (16,大学体育,3);insert into course values (17,gis系统开发,地理信息系统概论,3);insert into course values (18,地理信息系统概论,3);insert into course values (19,c+语言,大学计算机基础,3);insert into course values (20,gis三维建模,3);(3)向选课表中插入记录insert into sc values (X21214010,01,90);insert into sc values (X21214010,02,91);insert into sc values (X21214010,03,92);insert into sc values (X21214010,04,92);insert into sc values (X21214010,05,91);insert into sc values (X21214010,06,96);insert into sc values (X21214001,01,90);insert into sc values (X21214001,02,91);insert into sc values (X21214001,03,92);insert into sc values (X21214001,04,92);insert into sc values (X21214001,05,91);insert into sc values (X21214001,06,96);insert into sc values (X21214002,01,90);insert into sc values (X21214002,02,91);insert into sc values (X21214002,03,92);insert into sc values (X21214002,04,92);insert into sc values (X21214002,05,91);insert into sc values (X21214002,06,96);insert into sc values (X21214003,01,90);insert into sc values (X21214003,02,91);insert into sc values (X21214003,03,92);insert into sc values (X21214003,04,92);insert into sc values (X21214003,05,91);insert into sc values (X21214003,06,96);insert into sc values (X21214023,01,90);insert into sc values (X21214023,02,91);insert into sc values (X21214023,03,92);insert into sc values (X21214023,04,92);insert into sc values (X21214023,05,91);insert into sc values (X21214023,06,96);insert into sc values (X21214033,01,90);insert into sc values (X21214033,02,91);insert into sc values (X21214033,03,92);insert into sc values (X21214033,04,92);insert into sc values (X21214033,05,91);insert into sc values (X21214033,06,96);（4) 更新修改表中错误的记录SQLUpdate student set sage=18 Where sno=X21214035;SQLUpdate student set ssex=男 Where sno=X21214061;(5)删除表中无效的记录SQLdelete from student where sno=X21214062;(6)参照完整性设置即SC(Sno,Cno,Grade) 与Student(Sno,Sname,Ssex,Sage,Sdept) 建立外键关系。SQLalter table sc add foreign key (sno) references student(sno);3、数据查询1）简单查询（1）查询全部列例：从学生选课表中查询学生选课的情况SQL select * from sc;（2）查询经过计算的值例：从学生表中查询学生的学号、姓名以及出生的年份SQLselect sno, sname, 2014-sage from student;（3）查询满足特定条件的元祖例：从学生表中查询年龄大于21岁的学生全部记录SQLselect* from student where sage20;（4）通过查询建立一张新表。例：从学生表中提取性别为女的学生创建一张新表:studentwomanSQLcreate table studentwoman as select*from student where ssex=女;SQLselect * from studentwoman;（5）Order by 语句查询例：从学生表中查询学生的全部记录并且按学号升序排列SQLselect *from student order by sno ;（6）Group by 查询从学生选课表中查询每门课程被选的次数SQLs