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

数据库实验报告

课程： 数据库原理 同组者： 无 实验日期：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 database SUP_DB on primary (name = 'SUP_DB_DATA', Filename='D:\SUP_DB_DATA.MDF',

use SUP_DB;

create table s(sno char(3) primary key,sname char(10) not null,STATUS int,CITY char(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) REFERENCES J(JNO)); size = 5MB, maxsize = 20MB, Filegrowth = 20%) log on(name = 'SUP_DB_LOG', Filename = 'D:\SUP_DB_LOG.LDF', size = 5MB, Filegrowth = 2MB);

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) values('S4','丰泰盛',20,'天津');

insert into S (SNO,SNAME,STATUS,CITY) values('S5','为民',20,'上海');

数据库实验报告

课程： 数据库原理 同组者： 无 实验日期：2010.10.11 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) values('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,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 (SNO,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',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,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);

数据库实验报告

课程： 数据库原理 同组者： 无 实验日期：2010.10.11

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='P1'and Jno='J1';

backup database SUP_DB to disk='D:\SUP_DB.bak';

restore database SUP_DB from disk='D:\SUP_DB.bak';

三．实验总结

通过本次实验我发现一些关键字还是记不熟悉，上机操作的能力比较差，对于编程工具比较陌生，以后还得多上机实际操作。