学生表的创建与数据插入
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 条件;