数据库实验源代码大全实验一：数据定义语言create user U099074235 IDENTIFIED BY XHM123 DEFAULT TABLESPACE DXPDA TASPACE1,创建表StudentCREATE TABLE Student (SNO CHAR(5) ,SNAME CHAR(10) NOT NULL,SDEPT CHAR(2) NOT NULL,SCLASS CHAR(2) NOT NULL,SAGE NUMBER(2),SSEX CHAR(2),CONSTRAINT SNO_PK PRIMARY KEY(SNO));2，创建表CourseCREATE TABLE Course(CNO CHAR(3),CNAME V ARCHAR2(16) ,CTIME NUMBER(3),CONSTRAINT CNO_PK PRIMARY KEY(CNO))3,创建表TeachCREATE TABLE Teach(TNO V ARCHAR(6),TNAME V ARCHAR(8),TSEX CHAR(2),CNO CHAR(3),TDA TE DATE,TDEPT CHAR(2),CONSTRAINT TT_PK PRIMARY KEY(TNO),CONSTRAINT CNO_FK FOREIGN KEY(CNO) REFERENCES Course(CNO))CREATE TABLE Teach(TNO V ARCHAR(6),TNAME V ARCHAR(8),TSEX CHAR(2),CNO CHAR(3),TAGE NUMBER(2),TDEPT CHAR(2),CONSTRAINT TT_PK PRIMARY KEY(TNO),CONSTRAINT CNO_FK FOREIGN KEY(CNO) REFERENCES Course(CNO))4,创建表ScoreCREATE TABLE Score(SNO CHAR(5),CNO CHAR(3),SCORE NUMBER(5,2),CONSTRAINT SC_PK PRIMARY KEY(SNO,CNO),CONSTRAINT SNO_FK FOREIGN KEY(SNO) REFERENCES Student(SNO), CONSTRAINT CNOM_FK FOREIGN KEY(CNO) REFERENCES Course(CNO))二、插入数据1，StudentINSERT INTO Student V ALUES('96001','马小燕','CS','01',21,'女');INSERT INTO Student V ALUES('96002','黎明','CS','01',18,'男');INSERT INTO Student V ALUES('96003','刘东明','MA','01',18,'男');INSERT INTO Student V ALUES('96004','赵志勇','IS','02',20,'男');INSERT INTO Student V ALUES('97001','马蓉','MA','02',19,'女');INSERT INTO Student V ALUES('97002','李成功','CS','01',20,'男');INSERT INTO Student V ALUES('97003','黎明','IS','03',19,'女');INSERT INTO Student V ALUES('97004','李丽','CS','02',19,'女');INSERT INTO Student V ALUES('96005','司马志明','CS','02',18,'男');2，CourseINSERT INTO Course V ALUES('001','数学分析',144);INSERT INTO Course V ALUES('002','普通物理',144);INSERT INTO Course V ALUES('003','微机原理',72);INSERT INTO Course V ALUES('004','数据结构',72);INSERT INTO Course V ALUES('005','操作系统',64);INSERT INTO Course V ALUES('006','数据库原理',64);INSERT INTO Course V ALUES('007','DB_Design',48);INSERT INTO Course V ALUES('008','程序设计',56);3，TeachINSERT INTO Teach V ALUES('9401','王成钢','男','004',TO_DA TE( '1999-09-05', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9402','李正科','男','003',TO_DA TE( '1999-09-05', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9403','严敏','女','001',TO_DATE( '1999-09-05', 'YYYY-MM-DD'),'MA');INSERT INTO Teach V ALUES('9404','赵高','男','004',TO_DATE( '1999-09-05', 'YYYY-MM-DD'),'IS');INSERT INTO Teach V ALUES('9405','李正科','男','003',TO_DA TE( '2000-02-23', 'YYYY-MM-DD'),'MA');INSERT INTO Teach V ALUES('9406','李玉兰','女','006',TO_DA TE( '2000-02-23', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9407','王成钢','男','004',TO_DA TE( '2000-02-23', 'YYYY-MM-DD'),'IS');INSERT INTO Teach V ALUES('9408','马悦','女','008',TO_DATE( '2000-09-06', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9409','王成钢','男','007',TO_DA TE( '1999-09-05', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9401','王成钢','男','004',35,'CS');INSERT INTO Teach V ALUES('9402','李正科','男','003',40,'CS');INSERT INTO Teach V ALUES('9403','严敏','女','001',33,'MA');INSERT INTO Teach V ALUES('9404','赵高','男','004',28,'IS');INSERT INTO Teach V ALUES('9405','李正科','男','003',32,'MA');INSERT INTO Teach V ALUES('9406','李玉兰','女','006',43,'CS');INSERT INTO Teach V ALUES('9407','王成钢','男','004',49,'IS');INSERT INTO Teach V ALUES('9408','马悦','女','008',35,'CS');INSERT INTO Teach V ALUES('9409','王成钢','男','007',48,'CS');to_date( '05-09-1999', 'DD-MM-YYYY' );4,ScoreINSERT INTO Score V ALUES('96001','001',77.5);INSERT INTO Score V ALUES('96001','003',89);INSERT INTO Score V ALUES('96001','004',86);INSERT INTO Score V ALUES('96001','005',82);INSERT INTO Score V ALUES('96002','001',88);INSERT INTO Score V ALUES('96002','003',92.5);INSERT INTO Score V ALUES('96002','006',90);INSERT INTO Score V ALUES('96005','004',92);INSERT INTO Score V ALUES('96005','005',90);INSERT INTO Score V ALUES('96005','006',89);INSERT INTO Score V ALUES('96005','007',76);INSERT INTO Score V ALUES('96003','001',69);INSERT INTO Score V ALUES('97001','001',96);INSERT INTO Score V ALUES('97001','008',95);INSERT INTO Score V ALUES('96004','001',87);INSERT INTO Score V ALUES('96003','003',91);INSERT INTO Score V ALUES('97002','003',91);INSERT INTO Score V ALUES('97002','004','');INSERT INTO Score V ALUES('97002','006',92);INSERT INTO Score V ALUES('97004','005',90);INSERT INTO Score V ALUES('97004','006',85);INSERT INTO Score V ALUES('97004','008',75);INSERT INTO Score V ALUES('97003','001',59);INSERT INTO Score V ALUES('97003','003',58)实验一、1，(建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空ALTER TABLE Student DROP COLUMN SSEXALTER TABLE Student MODIFY SNAME VARCHAR(8) NULL例1-2：(修改数据库表) 在Student表中增加SEX(C,2) 字段。