南京理工大学紫金学院
实验报告书
课程名称： 《数据库系统》
实验题目： 实验1
用SQL语句创建数据库和表
班 级： 计算机科学与技术2班
学 号：
姓 名：
指导教师：
一、实验目的
1. 使用SQL2005练习使用SQL语句创建数据库与表
2．练习SQL查询、修改、删除等语句语句
3.掌握数据库中创建表时主键、约束条件的纂写
二 .实验内容
1．根据下列数据库模式，用SQL语句创建一个适当地数据库，并创建相关的表，注意表中的主键和外键及某些属性域的约束。
Product(marker, model, type)
PC(model, speed, ram, hd, rd, price)
Laptop(model, speed, ram ,hd, screen, price)
Printer(model, color, type, price)
2．根据下列数据库模式，用SQL语句创建一个适当地数据库，并创建相关的表，注意表中的主键和外键及某些属性域的约束。
Classes ( class , type , country , numCuns , bore , displacement )
Ships（ name , class , launched )
Battles（ name , date )
Outcomes ( ship , battle , result )
3．将相关的数据用SQL语句插入到数据中。
4．基于习题5.2.4给出的数据库模式和数据写出后面的查询语句以及查询结果。
Classes ( class , type , country , numCuns , bore , displacement )
Ships（ name , class , launched )
Battles（ name , date )
Outcomes ( ship , battle , result )
a）找出战舰类型的数量。
b）找出不同类型战舰拥有的平均火炮数量。
!c）找出战舰的平均火炮数量。注意c）和b）的不同在于：在计算均值的时候，是使用战舰的数目还是战舰的类型数目。
!d）找出每一类型（ class ）的第一艘船下水的年份。
!e）找出每一类型中被击沉船的数目。
!!f) 找出至少有3艘船的类型中被击沉的船的数目。
!!g）军舰火炮使用的炮弹的重量（以磅为单位）大约是火炮的口径（以英寸为单位）的一半。找出各个国家的军舰炮弹重量的平均值。
5．根据习题5.2.1给出的数据库模式，写出下面的数据库修改。描述对该习题数据库修改够的结果。
Product(marker, model, type)
PC(model, speed, ram, hd, rd, price)
Laptop(model, speed, ram ,hd, screen, price)
Printer(model, color, type, price)
a) 通过两条INSERT语句在数据库中添加如下信息：制造商C生产的型号为1100的PC，速度为1800，RAM为256，硬盘大小80，具有一个20x的DVD，售价为$2499。
！b) 加入如下信息：对于数据库中每台PC，都对应一台与其速度、RAM、硬盘相同，具有15英寸的屏幕，型号大于1100、价格高于$500的相同厂商制造的手提电脑。
c) 删除所有硬盘不超过20G的PC。
d) 删除所有不制造打印机的厂商生产的手提电脑。
e) 厂商A收购了厂商B，将所有B生产的产品改为由A生产。
f) 对于每台PC，把它的内存加倍并且增加20G的硬盘容量。（记住UPDATE语句中可以同时更改多个属性的值）
！g) 把厂商B生产的手提电脑的屏幕尺寸增加一英寸并且价格下调$100。
三、实验步骤
1．创建数据库 表
2. 编写SQL语句
3,在SQL2005上调试语句并运行结果
四、实验结果
1．根据下列数据库模式，用SQL语句创建一个适当地数据库，并创建相关的表，注意表中的主键和外键及某些属性域的约束。
Product(marker, model, type)
PC(model, speed, ram, hd, rd, price)
Laptop(model, speed, ram ,hd, screen, price)
Printer(model, color, type, price)
SQL语句：
create database [zuoye1]
create table product
(
marker varchar(16) primary key,
model varchar(16) not null,
type varchar(16) not null,
)
create table PC
(
model varchar(16) primary key,
speed varchar(16) not null,
ram varchar(16) not null,
hd varchar(16) not null,
rd varchar(16) not null,
price int not null
)
create table laptop
(
model varchar(16) primary key,
speed varchar(16) not null,
ram varchar(16) not null,
hd varchar(16) not null,
screem varchar(16) not null,
price int not null,
)
create table printer
(
model varchar(16) primary key,
color varchar(16) not null,
type varchar(16) not null,
price int not null,
)
2．根据下列数据库模式，用SQL语句创建一个适当地数据库，并创建相关的表，注意表中的主键和外键及某些属性域的约束。
Classes ( class , type , country , numCuns , bore , displacement )
Ships（ name , class , launched )
Battles（ name , date )
Outcomes ( ship , battle , result )
SQL语句：
create database [zuoye2]
create table Classes
(
class varchar(16)primary key not null,
type varchar(16) not null,
country varchar(16) not null,
numGuns int,
bore int,
displacement int,
)
create table Ships
(
name varchar(16) primary key,
class varchar(16) not null,
launched varchar(16) not null,
)
create table Battles
(
name varchar(16) primary key,
date varchar(16) not null,
)
create table Outcomes
(
ship varchar(16) primary key,
battle varchar(16) not null,
result varchar(16) not null,
)
3．将相关的数据用SQL语句插入到数据中。
SQL语句：
数据库1：zuoye1
insert
into Product (marker,model,type)
values('A',1001,'pc');
insert
into Product (marker,model,type)
values('A',1002,'pc');
insert
into Product (marker,model,type)
values('A',1003,'pc');
insert
into Product (marker,model,type)
values('A',2004,'laptop');
insert
into Product (marker,model,type)
values('A',2005,'laptop');
insert
into Product (marker,model,type)
values('A',2006,'laptop');
insert
into Product (marker,model,type)
values('B',1004,'pc');
insert
into Product (marker,model,type)
values('B',1005,'pc');
insert
into Product (marker,model,type)
values('B',1006,'pc');
insert
into Product (marker,model,type)
values('B',2007,'laptop');
insert
into Product (marker,model,type)
values('C',1007,'pc');
insert
into Product (marker,model,type)
values('D',1008,'pc');
insert
into Product (marker,model,type)
values('D',1009,'pc');
insert
into Product (marker,model,type)
values('D',1010,'pc');
insert
into Product (marker,model,type)
values('D',3004,'printer');
insert
into Product (marker,model,type)
values('D',3005,'printer');
insert
into Product (marker,model,type)
values('E',1011,'pc');
insert
into Product (marker,model,type)
values('E',1012,'pc');
insert
into Product (marker,model,type)
values('E',1013,'pc');
insert
into Product (marker,model,type)
values('E',2001,'laptop');
insert
into Product (marker,model,type)
values('E',2002,'laptop');
insert
into Product (marker,model,type)
values('E',2003,'laptop');
insert
into Product (marker,model,type)
values('E',3001,'printer');
insert
into Product (marker,model,type)
values('E',3002,'printer');
insert
into Product (marker,model,type)
values('E',3003,'printer');
insert
into Product (marker,model,type)
values('F',2008,'laptop');
insert
into Product (marker,model,type)
values('F',2009,'laptop');
insert
into Product (marker,model,type)
values('G',2010,'laptop');
insert
into Product (marker,model,type)
values('H',3006,'printer');
insert
into Product (marker,model,type)
values('H',3007,'printer');
insert
into PC (model,speed,ram,hd,price)
values(1001,2.66,1024,250,2114);
insert
into PC (model,speed,ram,hd,price)
values(1002,2.10,512,250,995);
insert
into PC (model,speed,ram,hd,price)
values(1003,1.42,512,80,478);
insert
into PC (model,speed,ram,hd,price)
values(1004,2.80,1024,250,649);
insert
into PC (model,speed,ram,hd,price)
values(1005,3.20,512,250,630);
insert
into PC (model,speed,ram,hd,price)
values(1006,3.20,1024,320,1049);
insert
into PC (model,speed,ram,hd,price)
values(1007,2.20,1024,200,510);
insert
into PC (model,speed,ram,hd,price)
values(1008,2.20,2048,250,770);
insert
into PC (model,speed,ram,hd,price)
values(1009,2.00,1024,250,650);
insert
into PC (model,speed,ram,hd,price)
values(1010,2.80,2048,300,770);
insert
into PC (model,speed,ram,hd,price)
values(1011,1.86,2048,160,959);
insert
into PC (model,speed,ram,hd,price)
values(1012,2.80,1024,160,649);
insert
into PC (model,speed,ram,hd,price)
values(1013,3.06,512,80,529);
insert
into Printer (model,color,type,price)
values(3001,'true','ink-jet',99);
insert
into Printer (model,color,type,price)
values(3002,'false','laser',239);
insert
into Printer (model,color,type,price)
values(3003,'true','laser',899);
insert
into Printer (model,color,type,price)
values(3004,'true','ink-jet',120);
insert
into Printer (model,color,type,price)
values(3005,'false','laser',120);
insert
into Printer (model,color,type,price)
values(3006,'true','ink-jet',100);
insert
into Printer (model,color,type,price)
values(3007,'true','laser',200);
insert
into Laptop (model,speed,ram,hd,screen,price)
values(2001,2.00,2048,240,20.1,3673);
insert
into Laptop (model,speed,ram,hd,screen,price)
values(2002,1.73,1024,80,17.0,3673);
insert
into Laptop (model,speed,ram,hd,screen,price)
values(2003,1.802,512,60,16.4,549);
insert
into Laptop (model,speed,ram,hd,screen,price)
values(2004,2.00,512,60,13.3,1150);
insert
into Laptop (model,speed,ram,hd,screen,price)
values(2005,2.16,1024,120,17.0,2600);
insert
into Laptop (model,speed,ram,hd,screen,price)
values(2006,2.00,2048,80,15.4,1700);
insert
into Laptop (model,speed,ram,hd,screen,price)
values(2007,1.83,1024,120,13.3,1429);
insert
into Laptop (model,speed,ram,hd,screen,price)
values(2008,1.60,1024,100,15.4,900);
insert
into Laptop (model,speed,ram,hd,screen,price)
values(2009,1.60,512,80,14.1,680);
insert
into Laptop (model,speed,ram,hd,screen,price)
values(2010,2.00,2048,160,15.4,2300);
数据库2：zuoye2
into Classes(class,type,country,numGuns,bore,displacement)
values('Bismark','bb','Germany',8,15,42000);
insert
into Classes(class,type,country,numGuns,bore,displacement)
values('Iowa','bb','USA',9,15,46000);
insert
into Classes(class,type,country,numGuns,bore,displacement)
values('Kongo','bc','Janpan',8,14,32000);
insert
into Classes(class,type,country,numGuns,bore,displacement)
values('North Carolina','bb','USA',9,16,37000);
insert
into Classes(class,type,country,numGuns,bore,displacement)
values('Renown','bc','Gt.Brintain',6,15,32000);
insert
into Classes(class,type,country,numGuns,bore,displacement)
values('Revenge','bb','Gt.Brintain',8,15,29000);
insert
into Classes(class,type,country,numGuns,bore,displacement)
values('Tennessee','bb','USA',12,14,32000);
insert
into Classes(class,type,country,numGuns,bore,displacement)
values('Yamato','bb','Janpan',9,18,65000);
insert
into Battles(name,date)
values('Denmark Strait','5/24-27-41');
insert
into Battles(name,date)
values('Guadalcanal','11/15/42');
insert
into Battles(name,date)
values('North Cape','12/26/43');
insert
into Battles(name,date)
values('Surigao Strait','10/25/44');
insert
into Ships(name,class,launched)
values('California','Tennessee',1921);
insert
into Ships(name,class,launched)
values('Haruna','Kongo',1915);
insert
into Ships(name,class,launched)
values('Hiei','Kongo',1914);
insert
into Ships(name,class,launched)
values('Iowa','Iowa',1943);
insert
into Ships(name,class,launched)
values('Kirishima','Kongo',1915);
insert
into Ships(name,class,launched)
values('Kongo','Kongo',1913);
insert
into Ships(name,class,launched)
values('Missouri','Iowa',1944);
insert
into Ships(name,class,launched)
values('Mnsashi','Yamato',1942);
insert
into Ships(name,class,launched)
values('New Jersey','Iowa',1943);
insert
into Ships(name,class,launched)
values('North California','North California',1941);
insert
into Ships(name,class,launched)
values('Ramilliss','Revenge',1917);
insert
into Ships(name,class,launched)
values('Renown','Renown',1915);
insert
into Ships(name,class,launched)
values('Repulse','Renown',1915);
insert
into Ships(name,class,launched)
values('Resolution','Revenge',1916);
insert
into Ships(name,class,launched)
values('Revenge','Revenge',1916);
insert
into Ships(name,class,launched)
values('Royal Dak','Revenge',1916);
insert
into Ships(name,class,launched)
values('Royal Sovereign','Revenge',1916);
insert
into Ships(name,class,launched)
values('Tennessee','Tennessee',1920);
insert
into Ships(name,class,launched)
values('Washington','North Carolina',1941);
insert
into Ships(name,class,launched)
values('Wisconsin','Iowa',1944);
insert
into Ships(name,class,launched)
values('Yamato','Yamato',1941);
insert
into Outcomes(ship,battle,result)
values('Arizona','Pearl Harbor','sunk');
insert
into Outcomes(ship,battle,result)
values('Bismarck','Denmark Strait','sunk');
insert
into Outcomes(ship,battle,result)
values('California','Denmark Strait','ok');
insert
into Outcomes(ship,battle,result)
values('Dunk of York','North Cape','ok');
insert
into Outcomes(ship,battle,result)
values('Fuso','Surgao Strait','sunk');
insert
into Outcomes(ship,battle,result)
values('Hook','Denmark Strait','sunk');
insert
into Outcomes(ship,battle,result)
values('King George V','Denmark Strait','ok');
insert
into Outcomes(ship,battle,result)
values('Kirishina','Guadalcanal','sunk');
insert
into Outcomes(ship,battle,result)
values('Prince of Wales','Denmark Strait','damaged');
insert
into Outcomes(ship,battle,result)
values('Rodney','Denmark Strait','ok');
insert
into Outcomes(ship,battle,result)
values('Scharnhorst','North Cape','sunk');
insert
into Outcomes(ship,battle,result)
values('South Dakota','Guadalcanal','damaged');
insert
into Outcomes(ship,battle,result)
values('Tennessee','Surigao Strait','ok');
insert
into Outcomes(ship,battle,result)
values('Washington','Guadalcanal','ok');
insert
into Outcomes(ship,battle,result)
values('West Virginia','Surigao Strait','ok');
insert
into Outcomes(ship,battle,result)
values('Yamashiro','Surigao Strait','sunk');
4．基于习题5.2.4给出的数据库模式和数据写出后面的查询语句以及查询结果。
Classes ( class , type , country , numGuns , bore , displacement )
Ships（ name , class , launched )
Battles（ name , date )
Outcomes ( ship , battle , result )
a）找出战舰类型的数量。
b）找出不同类型战舰拥有的平均火炮数量。
!c）找出战舰的平均火炮数量。注意c）和b）的不同在于：在计算均值的时候，是使用战舰的数目还是战舰的类型数目。
!d）找出每一类型（ class ）的第一艘船下水的年份。
!e）找出每一类型中被击沉船的数目。
!!f) 找出至少有3艘船的类型中被击沉的船的数目。
!!g）军舰火炮使用的炮弹的重量（以磅为单位）大约是火炮的口径（以英寸为单位）的一半。找出各个国家的军舰炮弹重量的平均值。
SQL语句：
a) select count (distinct class)
from ships
b) select type, avg(numGuns)
from classes
group by type
having type in (select distinct type
from classes)
c)
select type ,sum(numGuns)
from classes
group by type
d) select class,min(launched)
from ships
group by class
e)
select class, count(result)
from outcomes,ships
where result='sunk' and ships.name=outcomes.ship
group by class
f) select country ,avg(bore*1/2)
from classes
group by country
5．根据习题5.2.1给出的数据库模式，写出下面的数据库修改。描述对该习题数据库修改够的结果。
Product(marker, model, type)
PC(model, speed, ram, hd, rd, price)
Laptop(model, speed, ram ,hd, screen, price)
Printer(model, color, type, price)
a) 通过两条INSERT语句在数据库中添加如下信息：制造商C生产的型号为1100的PC，速度为1800，RAM为256，硬盘大小80，具有一个20x的DVD，售价为$2499。
！b) 加入如下信息：对于数据库中每台PC，都对应一台与其速度、RAM、硬盘相同，具有15英寸的屏幕，型号大于1100、价格高于$500的相同厂商制造的手提电脑。
c) 删除所有硬盘不超过20G的PC。
d) 删除所有不制造打印机的厂商生产的手提电脑。
e) 厂商A收购了厂商B，将所有B生产的产品改为由A生产。
f) 对于每台PC，把它的内存加倍并且增加20G的硬盘容量。（记住UPDATE语句中可以同时更改多个属性的值）
！g) 把厂商B生产的手提电脑的屏幕尺寸增加一英寸并且价格下调$100。
SQL语句：
a) insert
into PC (model,speed,ram,hd,price)
values(1100,1800,256,80,2499);
insert
into Product (marker,model,type)
values('C',1100,'pc'); 
b) create table handpc
( speed varchar(28),
ram varchar(28),
hd varchar(28),
screen varchar(28) check(screen='15'),
model varchar(28)check(model＞1001) primary key,
price int check(price＞500),
rd varchar(28),
marker varchar(28),
)
insert into handpc(model,speed,ram,hd,rd,price,marker)
select '1002','2.10','512','250','2212','995','A'union
select '1004','2.80','1024','250','224','649','B'union
select '1005','3.20','512','250','225','630','B'union
select '1006','3.20','1024','250','226','1049','B'union
select '1007','2.20','1024','250','227','510','C'union
select '1008','2.20','2048','250','228','770','D'union
select '1009','2.00','1024','250','229','650','D'union
select '1010','2.80','2048','250','2210','770','E'union
select '1011','1.86','2048','250','2211','549','E'union
select '1012','2.80','1024','250','2212','649','E'union
select '1013','3.06','512','250','2213','2529','E';
c) delete from pc
where hd＜200
d) delete from handpc
where marker not in(select marker from product where type='printer')
e) Update product
set marker='A'
where marker='B'；
f) update pc
set hd=hd+20;
d)
update handpc
set screen=screen+1,price=price-100
where marker='B'
五、总结
经过此次上机以及课后练习，我发现了自己在使用SQL语句是有许多毛病，比如在创建表的时候，键单词创建错误，导致后面插入时无法插入；在定义价格时没有选择int 而选择了varchar导致后面的sum函数无法使用等一系列问题的出现。
在第四题时，对group by语句的使用存在明显的问题，还想通过二层嵌套来完成要求，使问题麻烦化。
以后上及时会多多注意这些细节，从而更好地完成数据库上机实验。