资源描述:
《Oracle基本语句(史上上机考试最全总结)》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、Oracle基本语句一、开机前的准备工作sqlplus/nologConnstud/studassysdba;dropuserbencascade;createuserbenidentifiedby12345;grantdbatoben;connben/12345;showuser;@c:createUser.sql;二、创建表的相关语句1、创建表--创建学生表createtablestudent(studidchar(6)primarykey,studnamevarchar2(16)notnull,genderchar(4)check(ge
2、nderin('男','女')),addrvarchar2(256));--创建课程表createtablecourse(courseidchar(6)primarykey,coursenamevarchar2(32)notnullunique,teachervarchar2(16),scorenumbercheck(scorein(1,3,5)));--创建选课表createtablecourSelc(recidnumber(4)primarykey,studidchar(6),courseidchar(6),scorenumber(2)ch
3、eck(score>=0andscore<=100),constraintfk_studforeignkey(studid)referencesstudent(studid),constraintfk_courforeignkey(courseid)referencescourse(courseid));2、插入数据insertintostudentvalues('100001','李白','男','和平区');insertintostudentvalues('100002','杜甫','男','河西区');insertintostudentv
4、alues('100003','李清照','女','河北区');insertintostudentvalues('100004','蔡琰','女','红桥区');insertintocoursevalues('M1001','数学','奥巴马','3');insertintocoursevalues('J2002','军事理论','拿破仑','5');insertintocoursevalues('S3003','摔跤','普京','1');insertintocourSelcvalues(1,'100001','M1001',70);inse
5、rtintocourSelcvalues(2,'100001','J2002',60);insertintocourSelcvalues(3,'100001','S3003',80);insertintocourSelcvalues(4,'100002','M1001',75);insertintocourSelcvalues(5,'100002','J2002',85);insertintocourSelcvalues(6,'100002','S3003',95);insertintocourSelcvalues(7,'100003','M1
6、001',50);insertintocourSelcvalues(8,'100003','J2002',55);insertintocourSelcvalues(9,'100003','S3003',45);insertintocourSelcvalues(10,'100004','M1001',10);insertintocourSelcvalues(11,'100004','J2002',15);insertintocourSelcvalues(12,'100004','S3003',25);3、修改表语句----alterAlterta
7、blestudentaddtelchar(15);--增加一列telAltertablestudentmodifytelbarchar(17);---修改tel列的属性Altertablestudentdropcolumntel;--删除tel列4、修改表中数据语句----updateUpdatestudentsetstudid=’100008’wherestudname=’李白’;5、删除表语句----dropDroptablestudent;三、创建函数的相关语句1、创建函数(以实验五为例)createorreplacefunctionGe
8、tCodeByName(v_nameinvarchar2)returnvarchar2isv_idprexchar(16);beginv_idprex