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

原创已于 2023-03-18 11:33:48 修改 · 2k 阅读

20 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

于 2023-03-18 10:34:43 首次发布

Oracle 专栏收录该内容

1 篇文章

订阅专栏

文章展示了如何使用SQL语句创建和插入数据到学生表、课程表以及成绩表中，包括学生信息、课程信息和成绩记录，同时也包含了对表结构的修改和数据更新操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

学生表的创建与数据插入

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,'转专业学习');

课程表的创建与数据插入

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

成绩表的创建与数据插入

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

为表增加一列

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

修改表元素

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

删除表元素

DELETE FROM 表名 WHERE 条件;