综合练习-02

项目介绍

有一个仓库管理数据库equInfo，包含以下两张数据表：

（1）depot ( dNo, dName )
库房表depot由库房编号(dNo)、库房名称(dName)两个属性组成，dNo为主键。
（2）product( equNo, equName, equModel, equStock, equPrice, dNo )
商品表product由商品编号(equNo)、商品名称(equName)、型号(equModel)、库存量(equStock)、价格（equPrice）、库房编号（dNo）六个属性组成，equNo为主键。

题目

创建数据库equInfo，默认字符集为utf8。并设置其为当前默认的数据库。

create database equInfo
default character utf8;

use equInfo;

创建库房表depot：dNo、dName为字符型，其中dNo长度为3（定长）、不能为空、主键；dName长度为20、不能为空、唯一索引。

create table depot(
  dNo char(3) not null,
  dName varchar(20) not null,
  primary key(dNo),
  unique(dName)
);

创建商品表product：equNo、equName、equModel、dNo为字符型，其中equNo长度为5（定长）、不能为空、主键；equName长度为30、不能为空、普通索引；equModel长度为50；dNo长度为3（定长）；equStock为整型；equPrice为浮点数型。

create table product(
 equNo char(5) not null,
 equName varchar(30) not null,
 equModel varchar(50),
 dNo char(3),
 equStock int,
 equPrice float(8,2),
 primary key(equNo),
 index(equName)
);

向库房表depot中插入如下记录：

insert depot(dNo, dName) values('X01', '成品库');

向商品表product中插入如下记录：

insert product(equNo, equName, equModel, equStock, equPrice, dNo) 
  values('C0001', '笔记本电脑', '联想E450', 12, 3999, 'X01');

查询型号中包含“联想”的商品记录。

select * from product where equModel like '%联想%';

查询库存量小于50的商品记录，并按照价格降序进行排列。

select * from product where equStock < 50 order by equPrice desc;

统计商品表中商品的最高价格和最低价格。

select MAX(equPrice) as '最高价格', MIN(equPrice) as '最低价格' from product;

以“库房编号”分组统计商品信息，包括库房编号、商品总量、平均价格。

select dNo as '库房编号', SUM(equStock) as '商品总量', AVG(equPrice) as '平均价格' from product group by dNo;

查询与“C0001”商品同一个库房的商品记录。

select * from product where dNo = (select dNo from product where equNo='C0001');

查询库存量最高的5条商品记录。

select * from product order by equStock desc limit 5;

把“C0001”商品的库存量更改为15、价格更改为3900。

update product set equStock=15, equPrice=3900 where equNo='C0001';

创建视图v_view1：列出商品编号、商品名称、型号、库存量、价格、库房名称。并通过视图查询价格大于3000的商品信息。

create view v_view1
as
  select equNo, equName, equModel, equStock, equPrice, dName from Product,Depot
    where Product.dNo=Depot.dNo;
 
select * from v_view1 where equPrice>3000;

创建一个带有输入参数和输出参数的存储过程up_1，通过一个给定的仓库编号，统计出该仓库中的库存总量，并通过输出参数返回。然后通过一个给定的仓库编号“X01”，调用并执行该存储过程。

create procedure up_1(in depotNo char(3), out sumStock int)
begin
  select SUM(equStock) into sumStock from product where dNo=depotNo;
end

call up_1('X01', @sumStock);
select @sumStock;

在MySQL中创建一个普通用户loginUser，密码为“12345678”，主机名为“localhost”。然后再授予loginUser用户对equInfo数据库中所有数据表拥有查询和修改的权限。

create user loginUser@'localhost' identified by '12345678';
grant select,update on equInfo.* to loginUser@'localhost';

撤销loginUser用户对equInfo数据库中所有数据表所拥有的修改权限。

revoke update on equInfo.* from loginUser@'localhost';

备份数据库：使用root用户身份，把数据库equInfo备份为一个位于C盘根目录下的脚本文件equInfo.sql。还原数据库：使用root用户身份，使用C盘根目录下的脚本文件equInfo.sql还原数据库equInfo。

mysqldump -u root -p equInfo > c:equInfo.sql
mysql -u root -p equInfo < c:equInfo.sql

删除编号为“C0001”的商品记录。

delete from product where equNo='C0001';

删除“成品库”中的所有商品记录。

delete from product where dNo =(select dNo from depot where dName='成品库');

删除商品表product。

drop table product;