MySQL 基础教程[1]

• 本系列MySQL 基础教程通过“问题-代码”的方式介绍各类方法，每篇设置2个MySQL综合问题，并给出解决方案。

问题1

按照题目要求完成下面的基本操作题, 具体要求如下:

存有一商场信息管理系统的数据库 $\mathrm{db}\_$mall，其包含一个记录商品有关信息的商品表tb_commodity, 该 表包含的字段有商品号 (cno) 、商品名 (cname)、商品类型（ctype)、产地 (origin)、生产日期 (birth) 、价格 (price) 和产品说明 (desc1)

1. 使用 SQL语向, 计算商品表中北京产的电视机的价格总和(字段名为: total)。
2. 使用SQL语句，将商品表中的产品说明 (desc1) 字段删除, 以简化该表。
3. 使用SQL语句, 在商品表中添加如下一行信息, 商品名：钢笔; 商品类型: 文具; 产地: 上海; 生产日期: 201212-25; 价格: 25 。
4. 使用SQL语句, 在数据库db_mall中创建一个视图v_bjcommodity, 要求该视图包含商品表中产地为北京的全部商 品信息。
5. 使用SQL语句, 在当前系统中新建一个用户, 用户名为client, 主机名为localhost, 并为其授予对商品表中商品号 (cno) 字段和商品名 (cname) 字段的select权限。

问题1代码

#【1】
select sum(price) as "total" from db_mall.tb_commodity where origin="北京" and cname="电视机"；
#【2】
alter table tb_commodity drop column desc1;
#【3】
insert into tb_commodity(cname, ctype, origin,birth,peice) values("钢笔","文具","上海","2012-12-25"，25)；
#【4】
create view v_bjcommodity as select * from tb_commodity where origin="北京" ；
#【5】
create user 'client'@'localhost'；
grant select(cno, cname) on tb_commodity  to  'client'@'localhost' with grant option;

问题2

按照题目要求完成下面的简单应用题, 具体要求如下:

存有一商场信息管理系统的数据库db_mall, 其包含一个记录商品有关信息的商品表tb_commodity, 该表包含的字段有商品号 (cno)、商品名 (cname)、商品类型 (ctype)、产地（origin)、生产日期 (birth) 、价 格 (price) 。

1. 请创建一个名为tri price的触发器, 在揷入新的商品记录时, 能够根据商品的品名和产地自动设置商品的价格, 其具体规则如下: 若商品为上海产的电视机, 则价格设置为 2800 , 其它商品价格的设置可为缺省。

2. 请创建二个名为sp_counter的存储过程, 用于计算商品表tb_commodity 的商品记录数。

问题2代码

#【1】
DELIMITER $$
CREATE TRIGGER tri_price BEFORE INSERT ON tb_commodity FOR EACH ROW
  BEGIN
    DECLARE tmp1 CHAR(20);
    DECLARE tmp2 CHAR(20);
    SET tmp1 = NEW.cname;
    SET tmp2 = NEW.origin;
    IF (tmp1= '电视机') && (tmp2= '上海') THEN
      SET NEW.price = 2800;
    END IF;
  END $$
DELIMITER ;

#【2】
DELIMITER $$
CREATE PROCEDURE sp_counter(out ROWS INT)
BEGIN
  DECLARE cid INT;
  DECLARE FOUND BOOLEAN DEFAULT TRUE;
  DECLARE cur_cid CURSOR FOR 
    SELECT cno FROM tb_commodity;
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET FOUND=FALSE;
  SET ROWS=0;
  OPEN cur_cid;
  FETCH cur_cid INTO cid;
  WHILE FOUND DO
    SET ROWS=ROWS+1;
    fetch cur_cid INTO cid;
  END WHILE;
  close cur_cid;
  END $$
DELIMITER ;