Oracle - Exp(1)_数据定义/数据操纵语言

Oracle中的FLOAT和NUMBER：

 

---

/*
--修改表名
RENAME EXP1_STUDENT TO STUDENT;
RENAME EXP1_COURSE TO COURSE;
RENAME EXP1_TEACH TO TEACH;
RENAME EXP1_SCORE TO SCORE;
RENAME EXP1_S1 TO S1;
*/


/*
--删除5个表
DROP TABLE EXP1_STUDENT;
DROP TABLE EXP1_COURSE;
DROP TABLE EXP1_TEACH;
DROP TABLE EXP1_SCORE;
DROP TABLE EXP1_S1;
*/

/*
--Student表的创建
CREATE TABLE EXP1_STUDENT(
    SNO CHAR(5),
    SNAME VARCHAR2(12) NOT NULL,
    SDEPT CHAR(2) NOT NULL,
    SCLASS CHAR(2) NOT NULL,
    SSEX CHAR(3),
    SAGE NUMBER(3),
    PRIMARY KEY(SNO)
);

--Course表的创建
CREATE TABLE EXP1_COURSE(
    CNO CHAR(3),
    CNAME VARCHAR2(50),
    CTIME NUMBER(3),
    PRIMARY KEY(CNO)
);

--Teach表的创建
CREATE TABLE EXP1_TEACH(
    TNAME VARCHAR2(12),
    TSEX CHAR(3),
    CNO CHAR(3),
    TDATE DATE,
    TDEPT CHAR(2),
    PRIMARY KEY(TNAME,CNO,TDEPT),
    CONSTRAINT CON_TEACH_CNO FOREIGN KEY(CNO) REFERENCES EXP1_COURSE(CNO)
);

--Score表的创建
CREATE TABLE EXP1_SCORE(
    SNO CHAR(5),
    CNO CHAR(3),
    SCORE FLOAT,
    PRIMARY KEY(SNO,CNO),
    CONSTRAINT CON_SCORE_SNO FOREIGN KEY(SNO) REFERENCES EXP1_STUDENT(SNO),
    CONSTRAINT CON_SCORE_CNO FOREIGN KEY(CNO) REFERENCES EXP1_COURSE(CNO)
);

--1.2
ALTER TABLE EXP1_STUDENT DROP COLUMN SSEX;
ALTER TABLE EXP1_STUDENT ADD SSEX CHAR(3);

--1.3
ALTER TABLE EXP1_STUDENT MODIFY SNAME VARCHAR2(20);

--1.4
--S1表的建立
CREATE TABLE EXP1_S1(
    SNO CHAR(5),
    SNAME VARCHAR2(12),
    SD CHAR(2),
    SA CHAR(2)
);
*/

--*******************************************************************************
--*******************************************************************************

/*
--2.1
--(插入数据)  按前面各表中的数据分别插入到教学数据库的四个数据库表中。
INSERT INTO EXP1_STUDENT VALUES('96001','马小燕','CS','01',21,'女');
INSERT INTO EXP1_STUDENT VALUES('96002','黎明','CS','01',18,'男');
INSERT INTO EXP1_STUDENT VALUES('96003','刘东明','MA','01',18,'男');
INSERT INTO EXP1_STUDENT VALUES('96004','赵志勇','IS','02',20,'男');
INSERT INTO EXP1_STUDENT VALUES('97001','马蓉','MA','02',19,'女');
INSERT INTO EXP1_STUDENT VALUES('97002','李成功','CS','01',20,'男');
INSERT INTO EXP1_STUDENT VALUES('97003','黎明','IS','03',19,'女');
INSERT INTO EXP1_STUDENT VALUES('97004','李丽','CS','02',19,'女');
INSERT INTO EXP1_STUDENT VALUES('96005','司马志明','CS','02',18,'男');

INSERT INTO EXP1_COURSE VALUES('001','数学分析',144);
INSERT INTO EXP1_COURSE VALUES('002','普通物理',144);
INSERT INTO EXP1_COURSE VALUES('003','微机原理',80);
INSERT INTO EXP1_COURSE VALUES('004','数据结构',72);
INSERT INTO EXP1_COURSE VALUES('005','操作系统',80);
INSERT INTO EXP1_COURSE VALUES('006','数据库原理',80);
INSERT INTO EXP1_COURSE VALUES('007','编译原理',60);
INSERT INTO EXP1_COURSE VALUES('008','程序设计',40);

INSERT INTO EXP1_TEACH VALUES('王成刚','男','004',1999.9.5','CS'); 
INSERT INTO EXP1_TEACH VALUES('李正科','男','003',1999.9.5','CS');
INSERT INTO EXP1_TEACH VALUES('严敏','女','001',1999.9.5','MA');
INSERT INTO EXP1_TEACH VALUES('赵高','男','004',1999.9.5','IS');
INSERT INTO EXP1_TEACH VALUES('李正科','男','003',2000.2.23','MA'); 
INSERT INTO EXP1_TEACH VALUES('刘玉兰','女','006',2000.2.23','CS');
INSERT INTO EXP1_TEACH VALUES('王成刚','男','004',2000.2.23','IS');
INSERT INTO EXP1_TEACH VALUES('马悦','女','008',2000.9.6','CS');

INSERT INTO EXP1_SCORE VALUES('96001','001',77.5);
INSERT INTO EXP1_SCORE VALUES('96001','003',89);
INSERT INTO EXP1_SCORE VALUES('96001','004',86);
INSERT INTO EXP1_SCORE VALUES('96001','005',82);
INSERT INTO EXP1_SCORE VALUES('96002','001',88);
INSERT INTO EXP1_SCORE VALUES('96002','003',92.5);
INSERT INTO EXP1_SCORE VALUES('96002','006',90);
INSERT INTO EXP1_SCORE VALUES('96005','004',92);
INSERT INTO EXP1_SCORE VALUES('96005','005',90);
INSERT INTO EXP1_SCORE VALUES('96005','006',89);
INSERT INTO EXP1_SCORE VALUES('96005','007',76);
INSERT INTO EXP1_SCORE VALUES('96003','001',69);
INSERT INTO EXP1_SCORE VALUES('97001','001',96);
INSERT INTO EXP1_SCORE VALUES('97001','008',95);
INSERT INTO EXP1_SCORE VALUES('96004','001',87);
INSERT INTO EXP1_SCORE VALUES('96003','003',91);
INSERT INTO EXP1_SCORE VALUES('97002','003',91);
INSERT INTO EXP1_SCORE VALUES('97002','004',NULL);
INSERT INTO EXP1_SCORE VALUES('97002','006',92);
INSERT INTO EXP1_SCORE VALUES('97004','005',90);
INSERT INTO EXP1_SCORE VALUES('97004','006',85);


--2.2
--(多行插入)  将表Student表中计算机系(‘CS’)的学生数据插入到表S1中。
INSERT INTO EXP1_S1 SELECT SNO,SNAME,SDEPT,SAGE FROM EXP1_STUDENT WHERE SDEPT='CS';


--2.3
--(用create table+select创建表)将student表拷贝放到stu表中
CREATE TABLE EXP1_STU AS SELECT * FROM EXP1_STUDENT;
*/


--2.4
--(修改数据)  将S1表中所有学生的年龄加2
UPDATE EXP1_S1 SET SA=SA+2;


--2.5
--(修改数据)  将Course表中‘程序设计’课时数修改成100
UPDATE EXP1_COURSE SET CTIME=100 WHERE CNAME='程序设计';


--2.6
--(插入数据)  向Score表中插入数据（‘98001’, ‘001’, 95），根据返回信息解释其原因。
INSERT INTO EXP1_SCORE VALUES('98001','001',95);
--'违反完整约束条件 (AHUT.CON_SCORE_SNO) - 未找到父项关键字'

--2.7
--(插入数据)  向Score表中插入数据（‘97001’, ‘010’, 80），根据返回信息解释其原因。
INSERT INTO EXP1_SCORE VALUES('97001','001',80);
--ORA-00001: 违反唯一约束条件 (AHUT.SYS_C007134)

--2.8
--(删除数据)  删除Score表中学号为‘96001’的成绩信息，根据返回信息解释其原因。
DELETE FROM EXP1_SCORE WHERE SNO='96001';
--PASS

--2.9
--(删除数据)  删除Score表中课程号为‘003’ 的成绩信息，根据返回信息解释其原因。
DELETE FROM EXP1_SCORE WHERE CNO='003';
--PASS

--2.10
--(删除数据)  删除学生表stu中计算机系(‘CS’)的学生信息。
DELETE FROM EXP1_S1 WHERE SD='CS';

--2.11
--(删除数据)  删除数据库表S1中所有学生的数据。
DELETE FROM EXP1_STU;

--2.12
--(删除表)    删除数据库表S1和stu。
DROP TABLE EXP1_S1;
DROP TABLE EXP1_STU;