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
);