MySQL数据库对视图和存储过程的练习

• 如何创建视图和存储过程都不再说了，下面直接看看练习。
• 参考书籍：
  《数据库系统概论》第五版：王珊

先来看下数据库：

• 下面是问题：
  

下面是代码：

• 经过实际的编写正常执行，运行结果和预期一样

-- 后面跟的 select * 查询语句，是为了测试试图的正确性，不用写。 

-- 1. 男雇员 -- 
create view employee_mal
as 
select * from employee 
where sex='男';

select * from employee_mal;

-- 2.创建客户订购产品的 customer_order -- 
create view customer_order
as
select c.companyname, o.* from customer as c, sell_order as o 
where c.customerid = o.customerid;

select * from customer_order;
-- 下面还要查询 某公司对应的订单信息，接着关联查询,题中给的是 通恒机械，公司全称是通恒机械有限公司，我就用了一个模糊查询like -- 、
select * from (select * from customer_order where companyname like '通恒机械%') as c, product as p
where c.productid = p.productid; 

-- 3. 和上面一样-- 
create view em_order 
as
select e.employeename, po.* from employee as e, purchase_order as po
where e.employeeid = po.employeeid; 

select * from em_order;
-- 接着需要使用聚合函数，分组函数 -- 

select employeename, avg(purchaseordernumber) as avg, max(purchaseordernumber) as max, min(purchaseordernumber) as min 
from em_order GROUP BY employeeid;


-- 4. -- 
create view paper_order 
AS
select s.employeeid, p.* from product as p, sell_order as s
WHERE s.productid = p.productid and productname = '打印纸';

select * from paper_order;


-- 5.
CREATE view proc_select 
as 
select * from employee;


select * from proc_select;

--  6.
delimiter $$
CREATE PROCEDURE proc_empolyee_orderb( name char(10))
BEGIN
		declare id  int;
		declare num1  int;
		declare num2  int;
		-- 三表关联性能太差了，分步执行，先求 id
		select employeeid into id from employee where employeename = name;
		select sum(c.sellordernumber*p.price) into num1  from sell_order as c, product  as p
where c.productid = p.productid and employeeid = id;
		-- select salary into num1 from employee where employeename=name;
		set num2 = num1 * 0.05;
		select num2;
end $$
delimiter ;

call proc_empolyee_orderb('蔡慧敏');

# 若报错执行如下语句： 以内数据库的设置不同吧
select version(),
@@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));


select  *  from purchase_order as c, product  as p
where c.productid = p.productid;


-- 7.
delimiter $$
CREATE PROCEDURE proc_customer_order5( name char(10))
BEGIN
		declare id  int;
		declare error_info  varchar(50);
		select customerid into id from customer where companyname = name;
		-- 判断为Null值，要用 is ，不能使用 = 号！！
		if id is Null then
			set error_info = '该公司没有订购商品信息';
			select error_info;
		else 
			select c.companyname, c.contactname, p.productname, p.price, s.sellordernumber 
			from customer as c, sell_order as s, product as p
			where c.customerid = s.customerid and c.companyname = name and s.productid = p.productid;
		end if;
				
		
end $$
delimiter ;

call proc_customer_order5('通恒机械有限公司');