Oracle基本语句(史上上机考试最全总结).doc

Oracle基本语句 一、开机前的准备工作 sqlplus /nolog Conn stud/stud as sysdba; drop user ben cascade; create user ben identified by 12345; grant dba to ben; conn ben/12345; show user ; @ c:\createUser.sql; 二、创建表的相关语句 1、创建表 -- 创建学生表 create table student ( studid char(6) primary key, studname varchar2(16) not null, gender char(4) check(gender in(男,女)), addr varchar2(256) ); -- 创建课程表 create table course ( courseid char(6) primary key, coursename varchar2(32) not null unique, teacher varchar2(16) , score number check(score in (1,3,5)) ); -- 创建选课表 create table courSelc ( recid number(4) primary key, studid char(6), courseid char(6), score number(2) check(score =0 and score =100), constraint fk_stud foreign key(studid) references student(studid), constraint fk_cour foreign key(courseid) references course(courseid) ); 2、插入数据 insert into student values(100001,李白,男,和平区); insert into student values(100002,杜甫,男,河西区); insert into student values(100003,李清照,女,河北区); insert into student values(100004,蔡琰,女,红桥区); insert into course values(M1001,数学,奥巴马,3); insert into course values(J2002,军事理论,拿破仑,5); insert into course values(S3003,摔跤,普京,1); insert into courSelc values(1,100001,M1001,70); insert into courSelc values(2,100001,J2002,60); insert into courSelc values(3,100001,S3003,80); insert into courSelc values(4,100002,M1001,75); insert into courSelc values(5,100002,J2002,85); insert into courSelc values(6,100002,S3003,95); insert into courSelc values(7,100003,M1001,50); insert into courSelc values(8,100003,J2002,55); insert into courSelc values(9,100003,S3003,45); insert into courSelc values(10,100004,M1001,10); insert into courSelc values(11,100004,J2002,15); insert into courSelc values(12,100004,S3003,25); 3、修改表语句alter Alter table student add tel char(15); --增加一列tel Alter table student modify tel barchar(17); 修改tel列的属性 Alter table student drop column tel; --删除tel列 4、修改表中数据语句update Update student set studid=’1