数据库上机实验报告

数据库上机实验报告计算机科学与技术学院

实验一

一、实验平台

操作系统：Windows2000

使用软件：Microsoft SQL Server

二、实验结果

初始化：数据库创建

SQL语句：

CREATE DATABASE SPJ

实验结果：

CREATE DATABASE 进程正在磁盘'SPJ' 上分配0.75 MB 的空间。

CREATE DATABASE 进程正在磁盘'SPJ_log' 上分配0.49 MB 的空间。

1、第三章习题3（P127）：用SQL语句建立第二章习题5 （P74）中四个表，创建的基本表应考虑数据完整性。

1)建立S表，并插入数据

SQL语句：

CREATE TABLE S

(

SNO CHAR(2)NOT NULL,

SNAME CHAR(10)NOT NULL,

STATUS SMALLINT NOT NULL,

CITY CHAR(8)NOT NULL,

PRIMARY KEY(SNO)

);

INSERT INTO S (SNO, SNAME, STATUS, CITY)V ALUES('S1','精益', 20,'天津');

INSERT INTO S (SNO, SNAME, STATUS, CITY)V ALUES('S2','盛锡', 10,'北京');

INSERT INTO S (SNO, SNAME, STATUS, CITY)V ALUES('S3','东方红', 30,'北京');

INSERT INTO S (SNO, SNAME, STATUS, CITY)V ALUES('S4','丰泰盛', 20,'天津');

INSERT INTO S (SNO, SNAME, STATUS, CITY)V ALUES('S5','为民', 20,'上海');

SELECT * FROM S;

实验结果：

2)建立P表，并插入数据

SQL语句：

CREATE TABLE P

(

PNO CHAR(2)NOT NULL,

PNAME CHAR(10)NOT NULL,

COLOR CHAR(2)NOT NULL,

WEIGHT INT NOT NULL,

PRIMARY KEY(PNO)

);

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);

SELECT * FROM P;

实验结果：

3)建立J表，并插入数据

SQL语句：

CREATE TABLE J

(

JNO CHAR(2)NOT NULL,

JNAME CHAR(10)NOT NULL,

CITY CHAR(8)NOT NULL,

PRIMARY KEY(JNO)

);

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','半导体厂','南京'); SELECT * FROM J;

实验结果：

4)建立SPJ表，并插入数据

SQL语句：

CREATE TABLE SPJ

(

SNO CHAR(2)NOT NULL,

PNO CHAR(2)NOT NULL,

JNO CHAR(2)NOT NULL,

QTY INT NOT NULL,

FOREIGN KEY(SNO)REFERENCES S(SNO),

FOREIGN KEY(PNO)REFERENCES P(PNO),

FOREIGN KEY(JNO)REFERENCES J(JNO),

PRIMARY KEY(SNO, PNO, JNO)

);

insert into spj values('S1','P1','J1',200);

insert into spj values('S1','P1','J3',100);

insert into spj values('S1','P1','J4',700);

insert into spj values('S1','P2','J2',100);

insert into spj values('S2','P3','J1',400);

insert into spj values('S2','P3','J2',200);

insert into spj values('S2','P3','J4',500);

insert into spj values('S2','P3','J5',400);