文档介绍：数据库系统原理
实验报告
实验名称:数据操作语言DML实验
任课教师:霍纬刚
学号:100341324
姓名:汪何媛
完成日期:
实验目的
建立基本表并进行DML操作。
数据查询:单表查询、连接查询、嵌套查询、集合查询和统计等功能。
数据更新:增加、删除、查询和修改功能。
二、实验过程与分析
1、用Create、Insert语句创建基本表S,P,J和SPJ
(1)、基本表S
create table S
(
SNO char(3) primary key,
SNAME char(10),
STATUS char(2),
CITY char(10)
);
(2)、基本表P
create table P
(PNO char(3) primary key,
PNAME char(10),
COLOR char(4),
WEIGHT int
);
(3)、基本表J
create table J
(JNO char(3) primary key,
JNAME char(10),
CITY char(10));
(4)、基本表SPJ
create table SPJ
(SNO char(3),
PNO char(3),
JNO char(3),
QTY int,
primary key (SNO,PNO,JNO),
foreign key (SNO) references S (SNO),
foreign key (PNO) references P (PNO),
foreign key (JNO) references J (JNO));
2、插入数据
(1)、向S表插入数据
insert into S values('S1','精益','20','天津');
insert into S values('S2','盛锡','10','北京');
insert into S values('S3','东方红','30','北京');
insert into S values('S4','丰泰盛','30','天津');
insert into S values('S5','为民','30','上海');
(2)、向P表插入数据
insert into P values('P1','螺母','红','12');
insert into P values('P2','螺楦','绿','17');
insert into P values('P3','螺丝刀','蓝','14');
insert into P values('P4','螺丝刀','红','14');
insert into P values('P5','凸轮','蓝','40');
insert into P values('P6','齿轮','红','30');
(3)、向J表插入数据
insert into J values('J1','三建','北京');
insert into J values('J2','一汽','长春');
insert into J values('J3','弹簧厂','天津');
insert into J values('J4','造船厂','天津');
insert into J values('J5','机车厂','唐山');
insert into J values('J6','无线电厂','常州');
insert into J values('J7','半导体厂','南京');
(4)、向SPJ表插入数据
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','J1','100');
insert into SPJ values('S2','P3','J1','400');
insert into SPJ values('S2','P2','J4','200');
insert into SPJ values('S2','P3','J4','500');
insert into SPJ values('S2','P3','J5','400'