MySQL篇

视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询，自身不包含任何数据。

为什么要用视图

• 重用sql语句
• 简化复杂的sql操作
• 使用表的组成部分而不是整个表
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个权限
• 更改数据格式和表示

视图的规则和限制

• 视图必须唯一命名
• 在一个数据库中，可以创建的视图数目没有限制
• 视图可以嵌套，即可以利用从其他视图中查询出来的数据构建新的视图
• Order by 可以用在视图中，但如果从该视图检索数据的select中也包含order by，那么视图中的order by将会被覆盖
• 视图不能索引，也不能有关联的触发器或默认值
• 视图可以和表一起用，；例如编写一条联接表和视图的查询

创建视图

--  创建视图
CREATE VIEW v_product as
SELECT product.product_name,product_type.type_name 
FROM product
LEFT JOIN product_type ON product.type_id = product_type.id

-- 使用视图
SELECT * FROM v_product

-- 删除视图
DROP VIEW v_product 

存储过程

存储过程就是一组为了完成某个特定功能而编写并运行在数据库端的sql程序集

存储过程的优点

• 通过把处理封装在简易的单元中，简化复杂的操作
• 提高性能，比单独的sql语句速度要快
• 安全，调用者只需要调用指定的存储过程即可，而不用关心存储过程的内容

缺点

• 编写复杂
• 如果没有相应的权限，将无法创建存储过程

-- 创建存储过程 
delimiter//
CREATE PROCEDURE productavg()
BEGIN
SELECT AVG(product.market_price) AS avgprice FROM product;
END //
delimiter;

-- 调用存储过程
CALL productavg(); 

-- 带返回值的存储过程
delimiter//
CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8,2), -- OUT 代表返回值参数 pl 是变量名 DECIMAL是数据类型
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
) 
BEGIN
SELECT MIN(price) INTO pl FROM product;-- into 将值付给变量pl
SELECT MAX(price) INTO ph FROM product;
SELECT AVG(price) INTO pa FROM product;
END//
delimiter;
-- 调用带返回值的存储过程
CALL productpricing(@pricelow,@priceheight,@priceavg);
SELECT @pricelow,@priceheight,@priceavg

-- 带入传入参数的存储过程
delimiter//
CREATE PROCEDURE num(
in id INT, -- in代表传入的参数
OUT total int(11)
)
BEGIN
SELECT sum(comment_num) INTO total FROM product WHERE type_id = id;
END//
delimiter;

-- 调用带传入参数的存储过程
CALL num(2,@total);
SELECT @total

-- 删除存储过程
DROP PROCEDURE num;

Java调用存储过程

这里借鉴
http://www.open-open.com/code/view/1456844372750
带入传入参数的存储过程，示例如下

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class Test {


    public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://127.0.0.1:3306/db_7";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "rootroot";

    public static void main(String[] args) throws Exception {
        test1();
    }
    public static void test1() throws Exception{
        Class.forName(DRIVER_CLASS);
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        String sql = "{CALL num(?,?)}";//调用存储过程
        CallableStatement pamer = connection.prepareCall(sql);//实例化对象pamer
        pamer.setInt(1, 1);//存储过程输入参数 第一个1代表第一个参数 第二个1是要传入的值
        pamer.registerOutParameter(2, Types.INTEGER);//设置返回值类型，即返回值
        pamer.execute();//执行存储过程
        System.out.println(pamer.getInt(2));
        pamer.close();
        connection.close();



    }
}