数据库系统基础教程第三版 部分实验命令

本文为机械工业出版社 出版的《数据库系统基本教程（第三版）》一些课后习题的数据库操作命令。

#创建产品数据库
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;