Oracle实用教程（第五版）（Oracle11g版）学生表、课程表、成绩表的创建与数据插入。

Oracle实用教程（第五版）（Oracle11g版）学生表、课程表、成绩表的创建与数据插入。

1. 学生表的创建与数据插入

CREATE TABLE XSB
(学号     char(6)      PRIMARY KEY NOT NULL,             姓名     char(12)      NULL, 性别     char(4)      DEFAULT '男' NULL,出生时间  date         NULL,专业     char(16)     NULL,           总学分   number(2)    DEFAULT 0 CHECK(总学分<=100 and 总学分>=0) NULL,备注     varchar(120)   NULL
);INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151101','王林','男',to_date('1997/2/10', 'RRRR-MM-DD'),'计算机',50,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151102','程明','男',to_date('1998/2/1', 'RRRR-MM-DD'),'计算机',50,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151103','王燕','女',to_date('1996/10/6', 'RRRR-MM-DD'),'计算机',50,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151104','韦严平','男',to_date('1997/8/26', 'RRRR-MM-DD'),'计算机',50,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151106','李方方','男',to_date('1997/11/20', 'RRRR-MM-DD'),'计算机',50,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151107','李明','男',to_date('1997/5/1', 'RRRR-MM-DD'),'计算机',54,'提前修完《数据结构》，并获学分');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151108','林一帆','男',to_date('1996/8/5', 'RRRR-MM-DD'),'计算机',52,'已提前修完一门课');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151109','张强民','男',to_date('1996/8/11', 'RRRR-MM-DD'),'计算机',50,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151110','张蔚','女',to_date('1998/7/22', 'RRRR-MM-DD'),'计算机',50,'三好学生');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151111','赵琳','女',to_date('1997/3/18', 'RRRR-MM-DD'),'计算机',50,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151113','严红','女',to_date('1996/8/11', 'RRRR-MM-DD'),'计算机',48,'有一门功课不及格，待补考');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151201','王敏','男',to_date('1996/6/10', 'RRRR-MM-DD'),'通信工程',42,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151202','王林','男',to_date('1996/1/29', 'RRRR-MM-DD'),'通信工程',40,'有一门功课不及格，待补考');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151203','王玉民','男',to_date('1997/3/26', 'RRRR-MM-DD'),'通信工程',42,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151204','马琳琳','女',to_date('1996/2/10', 'RRRR-MM-DD'),'通信工程',42,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151206','李计','男',to_date('1996/9/20', 'RRRR-MM-DD'),'通信工程',42,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151210','李红庆','男',to_date('1996/5/1', 'RRRR-MM-DD'),'通信工程',44,'已提前修完一门，并获得学分');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151216','孙祥欣','男',to_date('1996/3/19', 'RRRR-MM-DD'),'通信工程',42,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151218','孙研','男',to_date('1997/10/9', 'RRRR-MM-DD'),'通信工程',42,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151220','吴薇华','女',to_date('1997/3/18', 'RRRR-MM-DD'),'通信工程',42,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151221','刘燕敏','女',to_date('1996/11/12', 'RRRR-MM-DD'),'通信工程',42,'');
INSERT INTO XSB ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "备注") VALUES ('151241','罗林琳','女',to_date('1997/1/30', 'RRRR-MM-DD'),'通信工程',50,'转专业学习');

2. 课程表的创建与数据插入

CREATE TABLE KCB
(课程号     char(3)     NOT NULL PRIMARY KEY,课程名     char(21)    NULL,开课学期   number(1)    DEFAULT 1 NULL CHECK(开课学期<=8 and 开课学期>=1),学时      number(2)    DEFAULT 0 NULL,学分      number(1)    DEFAULT 0 NULL
);INSERT INTO KCB ("课程号", "课程名", "开课学期", "学时", "学分") VALUES ('101','计算机基础',1,80,5);
INSERT INTO KCB ("课程号", "课程名", "开课学期", "学时", "学分") VALUES ('102','程序设计与语言',2,68,4);
INSERT INTO KCB ("课程号", "课程名", "开课学期", "学时", "学分") VALUES ('206','离散数学',4,68,4);
INSERT INTO KCB ("课程号", "课程名", "开课学期", "学时", "学分") VALUES ('208','数据结构',5,68,4);
INSERT INTO KCB ("课程号", "课程名", "开课学期", "学时", "学分") VALUES ('210','计算机原理',5,85,5);
INSERT INTO KCB ("课程号", "课程名", "开课学期", "学时", "学分") VALUES ('209','操作系统',6,68,4);
INSERT INTO KCB ("课程号", "课程名", "开课学期", "学时", "学分") VALUES ('212','数据库原理',7,68,4);
INSERT INTO KCB ("课程号", "课程名", "开课学期", "学时", "学分") VALUES ('301','计算机网络',7,51,3);
INSERT INTO KCB ("课程号", "课程名", "开课学期", "学时", "学分") VALUES ('302','软件工程',7,51,3);

3. 成绩表的创建与数据插入

CREATE TABLE CJB
(学号      char(7)      NOT NULL,课程号     char(3)     NOT NULL,成绩      number(3)   NULL,PRIMARY KEY(学号，课程号)
);INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151101','101',80);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151101','102',78);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151101','206',76);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151103','101',62);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151103','102',70);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151103','206',81);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151104','101',90);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151104','102',84);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151104','206',65);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151102','102',78);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151102','206',78);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151106','101',65);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151106','102',71);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151106','206',80);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151107','101',78);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151107','102',80);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151107','206',68);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151108','101',85);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151108','102',64);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151108','206',87);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151109','101',66);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151109','102',83);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151109','206',70);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151110','101',95);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151110','102',90);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151110','206',89);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151111','101',91);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151111','102',70);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151111','206',76);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151113','101',63);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151113','102',79);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151113','206',60);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151201','101',80);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151202','101',65);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151203','101',87);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151204','101',91);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151210','101',76);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151216','101',81);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151218','101',70);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151220','101',82);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151221','101',76);
INSERT INTO CJB ("学号", "课程号", "成绩") VALUES ('151241','101',90);

4. 为表增加一列

ALTER TABLE 表名 ADD 增加的列名 数据类型 NULL/NOT NULL;

5. 修改表元素

UPDATE 表名 SET 想要进行怎样的修改 WHERE 条件;

6. 删除表元素

DELETE FROM 表名 WHERE 条件;