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

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

#创建产品数据库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 Laptopvalues(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 Printervalues(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 pcwhere speed>=3.00;#查询能生产硬盘容量100GB以上的笔记本电脑的厂商select distinct makerfrom productwheremodel in (select distinct modelfrom Laptopwhere hd>100);#查询厂商B生产的所有产品的型号和价格(select product.model,pc.pricefrom product,pcwhere product.maker="B"and product.model=pc.model)union(select product.model,laptop.pricefrom product,laptopwhere product.maker="B" and product.model=laptop.model )union(select product.model,printer.pricefrom product,printerwhere product.maker="B"and product.model=printer.model);#查询所有彩色激光打印机的型号select  model from Printerwhere color='true'and type='laser';#查询那些只售笔记本不售PC的厂商select distinct product.makerfrom product,laptopwhere product.model=laptop.model and product.maker not in(select product.makerfrom product,pcwhere 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 hdfrom pcgroup by hdhaving count(hd)>=2;#找出所有价格低于1000的个人计算机的型号、速度和硬盘的大小select model ,speed,hdfrom pcwhere price>1000;#同上条，改名select model ,speed as gigahertz,hd as gigabytes from pcwhere price>1000;#查找所有打印机制造商select distinct makerfrom Productwhere type='printer';#价格高于1500的笔记本电脑型号、内存、屏幕尺寸select model,ram,screenfrom laptopwhere price>=1500;#找出所有彩色打印机元祖select *from printerwhere color='true';#找出速度为3.2且价格低于2000的个人计算机的型号和硬盘大小select model,hdfrom pcwhere speed>=3.2 and price <=2000;#6.5.1 a）#通过两条INSERT语句在数据库中添加如下信息：#厂商C生产的型号为1100的pc，其速度为3.2，RAM容量大小为1024，硬盘容量为180，售价为2499insert into productvalues('C',1100,'PC');insert into pcvalues(1100,3.2,1024,180,2499);#删除所有硬盘容量低于100GB的pcdelete from pc where hd<100;///////////////////////////////////////////////////#删除所有不生产打印机厂商生产的笔记本电脑#第一步：删除laptop表delete from laptopwhere laptop.model in(select product.modelfrom product where maker not in(select distinct makerfrom product where type ='printer'));#错误的方式删除productdelete 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;#创建MovieStarcreate table MovieStar(name char primary key,address char,gender char,birthdate date);#创建MovieExeccreate table MovieExec(name char,adress char,cert int,netWorth int,primary key (name,cert));#创建表studiocreate table studio(name char,address char,presc int,primary key(name));#8.1.1#视图RichEXec给出了所有资产在10000000以上的制片人的名字、地址、证书号、资产create view RichExec asselect * from MovieExec where networth>10000000;#视图StudioPress给出了既是电影公司经理（Studio president）#又是制片人（Movie Executive）的那些人的名字，地址和证书号create view StudioPress asselect studio.name as name,studio.address as address ,prescfrom movieExec,Studiowhere Studio.name=movieexec.name;#视图ExecutiveStar给出了既是制片人又是演员的那些人的名字，地址，性别、生日，证书号和资产总值create view ExecutiveStar asselect MovieStar.name as name,MovieStar.address as address,gender,birthdate,cert,networthfrom MovieStar,MovieExecwhere MovieStar.name=MovieExec.name;#显示RichEXec表中元素名称show columns from RichEXec;#显示StudioPress表中元素名称show columns from StudioPress;#显示ExecutiveStar表中元素名称show columns from ExecutiveStar;