数据库技术概论实验报告1

1、山东建筑大学 计算机学院 实验报告班级：信计082 姓名：吴玉坤 学号： 2008121260 实验成绩： 课程： 数据库原理 同组者： 无 实验日期：2010.10.11实验一 数据库的建立及简单查询一实验目的1.学会数据表的创建；2.加深对表间关系的理解；3.理解数据库中数据的简单查询方法和应用。二. 实验内容create database SUP_DB on primary (name = SUP_DB,Filename=D:SUP_DB.mdf,size = 5MB,maxsize = 20MB,Filegrowth = 20%)drop database SUP_DB;create

2、database SUP_DB on primary (name = SUP_DB_DATA, Filename=D:SUP_DB_DATA.MDF,size = 5MB, maxsize = 20MB, Filegrowth = 20%)log on(name = SUP_DB_LOG, Filename = D:SUP_DB_LOG.LDF, size = 5MB, Filegrowth = 2MB);use SUP_DB;create table s(sno char(3) primary key,sname char(10) not null,STATUS int,CITY char(

3、10)create table p(PNO char(3) primary key,PNAME varchar(10) not null,COLOR varchar(8),WEIGHT int);create table J(JNO char(3) primary key,JNAME varchar(10) not null,CITY varchar(8);create table SPJ(SNO char(3),PNO char(3),JNO char(3),QTY int,FOREIGN KEY (PNO) REFERENCES P(PNO),FOREIGN KEY (JNO) REFER

4、ENCES J(JNO);create UNIQUE INDEX index_J on J(JNO);create UNIQUE INDEX index_P on J(JNO);insert into S (SNO,SNAME,STATUS,CITY) values(S1,精益,20,天津);insert into S (SNO,SNAME,STATUS,CITY) values(S2,胜锡,10,北京);insert into S (SNO,SNAME,STATUS,CITY) values(S3,东方红,30,北京);insert into S (SNO,SNAME,STATUS,CITY

5、) values(S4,丰泰盛,20,天津);insert into S (SNO,SNAME,STATUS,CITY) values(S5,为民,20,上海);insert into P (PNO,PNAME,COLOR,WEIGHT) values(P1,螺母,红,12);insert into P (PNO,PNAME,COLOR,WEIGHT) values(P2,螺栓,绿,17);insert into P (PNO,PNAME,COLOR,WEIGHT) values(P3,螺丝刀,蓝,14);insert into P (PNO,PNAME,COLOR,WEIGHT) value

6、s(P4,螺丝刀,红,14);insert into P (PNO,PNAME,COLOR,WEIGHT) values(P5,凸轮,蓝,40);insert into P (PNO,PNAME,COLOR,WEIGHT) values(P6,齿轮,红,30);insert into J (JNO,JNAME,CITY) values(J1,三建,北京);insert into J (JNO,JNAME,CITY) values(J2,一气,长春);insert into J (JNO,JNAME,CITY) values(J3,弹簧厂,天津);insert into J (JNO,JNAME

7、,CITY) values(J4,造船厂,天津);insert into J (JNO,JNAME,CITY) values(J5,机车厂,唐山);insert into J (JNO,JNAME,CITY) values(J6,无线电厂,常州);insert into J (JNO,JNAME,CITY) values(J7,半导体厂,南京);insert into SPJ (SNO,PNO,JNO,QTY)values(S1,P1,J1,200);insert into SPJ (SNO,PNO,JNO,QTY)values(S1,P1,J3,100);insert into SPJ (S

8、NO,PNO,JNO,QTY)values(S1,P1,J4,700);insert into SPJ (SNO,PNO,JNO,QTY)values(S1,P2,J2,100);insert into SPJ (SNO,PNO,JNO,QTY)values(S2,P3,J1,400);insert into SPJ (SNO,PNO,JNO,QTY)values(S2,P3,J2,200);insert into SPJ (SNO,PNO,JNO,QTY)values(S2,P3,J4,500);insert into SPJ (SNO,PNO,JNO,QTY)values(S2,P3,J5

9、,400);insert into SPJ (SNO,PNO,JNO,QTY)values(S2,P5,J1,400);insert into SPJ (SNO,PNO,JNO,QTY)values(S2,P5,J2,100);insert into SPJ (SNO,PNO,JNO,QTY)values(S3,P1,J1,200);insert into SPJ (SNO,PNO,JNO,QTY)values(S3,P3,J1,200);insert into SPJ (SNO,PNO,JNO,QTY)values(S4,P5,J1,100);insert into SPJ (SNO,PNO

10、,JNO,QTY)values(S4,P6,J3,300);insert into SPJ (SNO,PNO,JNO,QTY)values(S4,P6,J4,200);insert into SPJ (SNO,PNO,JNO,QTY)values(S5,P2,J4,100);insert into SPJ (SNO,PNO,JNO,QTY)values(S5,P3,J1,200);insert into SPJ (SNO,PNO,JNO,QTY)values(S5,P6,J2,200);insert into SPJ (SNO,PNO,JNO,QTY)values(S5,P6,J4,500);select SNAME,CITY from S where SNO=S1;select pno from p where color=红;select Jname from J where city=天津;select Sno from SPJ where Pno=