資料庫系統基礎教程第三版 部分實驗命令

本文為機械工業出版社 出版的《資料庫系統基本教程（第三版）》一些課後習題的資料庫操作命令。

#建立產品資料庫
create database products;

#使用產品資料庫
use products;

#建立產品表
create table Product(
maker char(5),
model int(10),
type char(10)
);



#建立PC表
create table PC(
model int(10),
speed float,
ram int(7),
hd int(5),
price int(7)
);

#建立Laptop表
create table Laptop(
model int(10),
speed float,
ram int(7),
hd int(5),
screen float,
price int(7)
);


#建立printer表
create table Printer(
model int(10),
color char(10),
type char(15),
price int(7)
);


#插入product資料
insert into Product (maker,model,type)
values 
('A',1001,'pc'),
('A',1002,'pc'),
('A',1003,'pc'),
('A',2004,'laptop'),
('A',2005,'laptop'),
('A',2006,'laptop'),
('B',1004,'pc'),
('B',1005,'pc'),
('B',1006,'pc'),
('B',2007,'laptop'),
('C',1007,'pc'),
('D',1008,'pc'),
('D',1009,'pc'),
('D',1010,'pc'),
('D',3004,'printer'),
('D',3005,'printer'),
('E',1011,'pc'),
('E',1012,'pc'),
('E',1013,'pc'),
('E',2001,'laptop'),
('E',2002,'laptop'),
('E',2003,'laptop'),
('E',3001,'printer'),
('E',3002,'printer'),
('E',3003,'printer'),
('F',2008,'laptop'),
('F',2009,'laptop'),
('G',2010,'laptop'),
('H',3006,'printer'),
('H',3007,'printer');


#插入PC表
insert into PC(model,speed,ram,hd,price)
values(1001,2.66,1024,250,2114),
(1002,2.10,512,250,955),
(1003,1.42,512,80,478),
(1004,2.80,1024,250,649),
(1005,3.20,512,250,630),
(1006,3.20,1024,320,1049),
(1007,2.20,1024,200,510),
(1008,2.20,2048,250,770),
(1009,2.00,1024,250,650),
(1010,2.80,2048,300,770),
(1011,1.86,2048,160,959),
(1012,2.80,1024,160,649),
(1013,3.06,512,80,529);




#插入Laptop表
insert into Laptop
values
(2001,2.00,2048,240,20.1,3673),
(2002,1.73,1024,80,17.0,949),
(2003,1.80,512,60,15.4,549),
(2004,2.00,512,60,13.3,1150),
(2005,2.16,1024,120,17.0,2500),
(2006,2.00,2048,80,15.4,1700),
(2007,1.83,1024,120,13.3,1429),
(2008,1.60,1024,100,15.4,900),
(2009,1.60,512,80,14.1,680),
(2010,2.00,2048,160,15.4,2300);



#插入Printer表
insert into Printer
values
(3001,'true','ink-jet',99),
(3002,'false','laser',239),
(3003,'true','laser',899),
(3004,'true','ink-jet',120),
(3005,'false','laser',120),
(3006,'true','ink-jet',100),
(3007,'true','laser',200);


#查詢速度大於3.00的pc型號
select model 
from pc
where speed>=3.00;

#查詢能生產硬碟容量100GB以上的膝上型電腦的廠商
select distinct maker
from product
where
model in (
select distinct model
from Laptop
where hd>100);

#查詢廠商B生產的所有產品的型號和價格

(select product.model,pc.price
from product,pc
where product.maker="B"and product.model=pc.model)union
(select product.model,laptop.price
from product,laptop
where product.maker="B" and product.model=laptop.model )union
(select product.model,printer.price
from product,printer
where product.maker="B"and product.model=printer.model);




#查詢所有彩色鐳射印表機的型號
select  model 
from Printer
where color='true'and type='laser';


#查詢那些只售筆記本不售PC的廠商
select distinct product.maker
from product,laptop
where product.model=laptop.model and product.maker not in(
select product.maker
from product,pc
where product.model=pc.model);

select distinct maker from product  where type="laptop"
 and maker not in
(select maker from product where type="pc");


#查詢在兩種以上PC機中出現過的硬碟容量


select hd
from pc
group by hd
having count(hd)>=2;







#找出所有價格低於1000的個人計算機的型號、速度和硬碟的大小
select model ,speed,hd
from pc
where price>1000;

#同上條，改名
select model ,speed as gigahertz,hd as gigabytes 
from pc
where price>1000;

#查詢所有印表機制造商
select distinct maker
from Product
where type='printer';

#價格高於1500的膝上型電腦型號、記憶體、螢幕尺寸
select model,ram,screen
from laptop
where price>=1500;

#找出所有彩色印表機元祖
select *
from printer
where color='true';

#找出速度為3.2且價格低於2000的個人計算機的型號和硬碟大小
select model,hd
from pc
where speed>=3.2 and price <=2000;






#6.5.1 a）
#通過兩條INSERT語句在資料庫中新增如下資訊：
#廠商C生產的型號為1100的pc，其速度為3.2，RAM容量大小為1024，硬碟容量為180，售價為2499
insert into product
values('C',1100,'PC');

insert into pc
values(1100,3.2,1024,180,2499);


#刪除所有硬碟容量低於100GB的pc
delete from pc 
where hd<100;










///////////////////////////////////////////////////
#刪除所有不生產印表機廠商生產的膝上型電腦
#第一步：刪除laptop表
delete from laptop
where laptop.model in(
select product.model
from product 
where maker not in(
select distinct maker
from product where type ='printer'
)
);

#錯誤的方式刪除product
delete from product
 where maker not in(
   select distinct maker
   from product where type ='printer'
);

#第二步：通過中間表刪除product中資料
delete from product 
where maker not in(
   select a.maker from
   ( select distinct a.maker from product a where a.type ='printer')a
);


#廠商A收購了廠商B，將所有B生產的產品改為由A生產
update product 
set maker='A'
where maker='B';

#對於每臺pc，將其RAM容量加倍，並將其硬碟容量增加60GB。
update pc 
set ram=ram*2,hd=hd+60;






#movie

#建資料庫
create database movie;

#選擇資料庫
use  movie;

#建立MovieStar
create table MovieStar(
name char primary key,
address char,
gender char,
birthdate date
);


#建立MovieExec
create table MovieExec(
name char,
adress char,
cert int,
netWorth int,
primary key (name,cert)
);


#建立表studio
create table studio(
name char,
address char,
presc int,
primary key(name)
);


#8.1.1
#檢視RichEXec給出了所有資產在10000000以上的製片人的名字、地址、證照號、資產

create view RichExec as
select * from MovieExec 
where networth>10000000;

#檢視StudioPress給出了既是電影公司經理（Studio president）
#又是製片人（Movie Executive）的那些人的名字，地址和證照號

create view StudioPress as
select studio.name as name,studio.address as address ,presc
from movieExec,Studio
where Studio.name=movieexec.name;

#檢視ExecutiveStar給出了既是製片人又是演員的那些人的名字，地址，性別、生日，證照號和資產總值

create view ExecutiveStar as
select MovieStar.name as name,MovieStar.address as address,gender,birthdate,cert,networth
from MovieStar,MovieExec
where MovieStar.name=MovieExec.name;

#顯示RichEXec表中元素名稱
show columns from RichEXec;

#顯示StudioPress表中元素名稱
show columns from StudioPress;

#顯示ExecutiveStar表中元素名稱
show columns from ExecutiveStar;