优化或报错有奖通过前面的讲解，我们知道SQL语句可以对Oracle进行对象创建、删除，数据的插入、删除、更新，以及数据库的管理等操作，SQL是一个结构化的的查询语言（Structured Query Language ），不仅仅适用于ORACLE数据库，再其它的数据也适用。
在 Oracle 开发中，客户端把 SQL 语句发送给服务器，服务器对 SQL 语句进行编译、执行，把执行的结果返回给客户端。常用的SQL语句大致可以分为五类：
数据定义语言（DDL），包括 CREATE（创建）命令、 ALTER（修改）命令、 DROP（删除）命令等。
数据操纵语言（DML），包括 INSERT（插入）命令、 UPDATE（更新）命令、 DELETE（删除）命令、 SELECT … FOR UPDATE（查询）等。
数据查询语言（DQL），包括基本查询语句、 Order By 子句、 Group By 子句等。
事务控制语言（TCL），包括 COMMIT（提交）命令、 SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
数据控制语言（DCL）， GRANT（授权）命令、 REVOKE（撤销）命令。
在Oracle基础教程所有案例所需的表结构的SQL执行的脚本语句如下：
-- Create table create table STUINFO ( stuid VARCHAR2(11) not null, stuname VARCHAR2(50) not null, sex CHAR(1) not null, age NUMBER(2) not null, classno VARCHAR2(7) not null, stuaddress VARCHAR2(100) default '地址未录入', grade CHAR(4) not null, enroldate DATE, idnumber VARCHAR2(18) default '身份证未采集' not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Add comments to the table comment on table STUINFO is '学生信息表'; -- Add comments to the columns comment on column STUINFO.stuid is '学号'; comment on column STUINFO.stuname is '学生姓名'; comment on column STUINFO.sex is '学生性别'; comment on column STUINFO.age is '学生年龄'; comment on column STUINFO.classno is '学生班级号'; comment on column STUINFO.stuaddress is '学生住址'; comment on column STUINFO.grade is '年级'; comment on column STUINFO.enroldate is '入学时间'; comment on column STUINFO.idnumber is '身份证号'; -- Create/Recreate primary, unique and foreign key constraints alter table STUINFO add constraint PK_STUINFO primary key (STUID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Create table create table CLASS ( classno VARCHAR2(7) not null, classname VARCHAR2(50), monitorid VARCHAR2(11), monitorname VARCHAR2(50), headmasterid VARCHAR2(8), headmastername VARCHAR2(50), classaddress VARCHAR2(50), enterdate DATE ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Add comments to the table comment on table CLASS is '班级信息表'; -- Add comments to the columns comment on column CLASS.classno is '班级号'; comment on column CLASS.classname is '班级名称'; comment on column CLASS.monitorid is '班长学号'; comment on column CLASS.monitorname is '班长姓名'; comment on column CLASS.headmasterid is '班主任教师号'; comment on column CLASS.headmastername is '班主任姓名'; comment on column CLASS.classaddress is '班级地址'; comment on column CLASS.enterdate is '录入时间'; -- Create/Recreate primary, unique and foreign key constraints alter table CLASS add constraint PK_CLASS primary key (CLASSNO) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255; -- Create table create table COURSE ( courseid VARCHAR2(9) not null, schyear VARCHAR2(4), term VARCHAR2(4), coursename VARCHAR2(100) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Add comments to the table comment on table COURSE is '课程表'; -- Add comments to the columns comment on column COURSE.courseid is '课程id'; comment on column COURSE.schyear is '学年'; comment on column COURSE.term is '学期'; comment on column COURSE.coursename is '课程名称'; -- Create/Recreate primary, unique and foreign key constraints alter table COURSE add constraint PK_COURSE primary key (COURSEID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Create table create table STUCOURSE ( selectid VARCHAR2(18) not null, stuid VARCHAR2(11), courseid VARCHAR2(9), schyear VARCHAR2(4), term VARCHAR2(4), redo VARCHAR2(1), selectdate DATE ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Add comments to the table comment on table STUCOURSE is '学生选课表'; -- Add comments to the columns comment on column STUCOURSE.selectid is '选课id'; comment on column STUCOURSE.stuid is '学号'; comment on column STUCOURSE.courseid is '课程id'; comment on column STUCOURSE.schyear is '年度'; comment on column STUCOURSE.term is '学期'; comment on column STUCOURSE.redo is '是否重修'; comment on column STUCOURSE.selectdate is '选课时间'; -- Create/Recreate primary, unique and foreign key constraints alter table STUCOURSE add constraint PK_STUCOURSE primary key (SELECTID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255; -- Create table create table SCORE ( scoreid VARCHAR2(18) not null, stuid VARCHAR2(11), courseid VARCHAR2(9), score NUMBER, scdate DATE ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Add comments to the table comment on table SCORE is '学生成绩表'; -- Add comments to the columns comment on column SCORE.scoreid is '学生成绩id'; comment on column SCORE.stuid is '学生学号'; comment on column SCORE.courseid is '课程id(年度+上下学期+课程序列)'; comment on column SCORE.score is '成绩'; comment on column SCORE.scdate is '成绩录入时间'; -- Create/Recreate primary, unique and foreign key constraints alter table SCORE add constraint PK_SCORE primary key (SCOREID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
转载本站内容时，请务必注明来自W3xue，违者必究。