Oracle学习VI —— SQL语句介绍及案例所需表结构

SQL语句介绍及案例所需表结构

SQL语句介绍

SQL是一个结构化的的查询语言（Structured Query Language ）

在 Oracle 开发中，客户端把 SQL 语句发送给服务器，服务器对 SQL 语句进行编译、执行，把执行的结果返回给客户端。常用的SQL语句大致可以分为五类：

• 数据定义语言（DDL）： CREATE（创建）命令、 ALTER（修改）命令、 DROP（删除）、TRUNCATE（删除）命令。
• 数据操纵语言（DML）： INSERT（插入）命令、 UPDATE（更新）命令、 DELETE（删除）命令、 SELECT … FOR UPDATE（查询）。
• 数据查询语言（DQL）： 基本查询语句、 Order By 子句、 Group By 子句。
• 事务控制语言（TCL）： COMMIT（提交）命令、 SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
• 数据控制语言（DCL）： GRANT（授权）命令、 REVOKE（撤销）命令。

案例所需表结构

表结构执行脚本：

-- Create table01
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 --表段STUINFO放在表空间USERS中
  pctfree 10 --块保留10%的空间留给更新该块数据使用
  initrans 1 --初始化事务槽的个数
  maxtrans 255 --最大事务槽的个数
  storage --存储参数
  (
    initial 64K --区段(extent)初次扩展64k
    next 1M --区段(extent)下一次扩展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 table02
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 table03
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 table04
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 table05
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
  );