MySQL学习10_通配符和存储过程

select prod_id, prod_name
from Products
where prod_name like 'Fish%'  -- 查询Fish开头
where prod_name like '%Fish'  -- 查询Fish结尾
where prod_name like '%Fish%' -- 查询包含Fish，两边是任意字符

select prod_id, prod_name
from Products
where prod_name like '_ inch teddy bear';   -- 找出个位数的teddy bear
where prod_name like '__ inch teddy bear';  -- 两个下划线，找出两位数的teddy bear

select cust_contact
from Customers
where cust_contact likt '[JM]%'     -- 表示匹配以J或者M开头的内容，后面是任意字符
where cust_contact likt '[^JM]%'    -- ^表示取反
where not cust_contact likt '[JM]%' -- 效果等同于^取反操作
order by cust_contact;

execute addNewProduct(   -- 执行该名字的存储过程
    'JS01',   --供应商
    'Shufujia',   -- 商品名字
    7.08,   -- 价格
    'a product of skin' -- 描述
);

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type   -- 3个主要的参数
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

DELIMITER $$   -- 两者皆可
delimiter //

CREATE PROCEDURE demo_in_parameter(IN p_in int)

BEGIN .... END

SET @p_in=1

DECLARE l_int int unsigned default 4000000;

create procedure 存储过程名(参数)

create function 存储函数名(参数)

mysql> create database db1;   -- 创建库
mysql> use db1;      -- 使用库
mysql> create table PLAYERS as select * from TENNIS.PLAYERS;
mysql> create table MATCHES  as select * from TENNIS.MATCHES;

mysql>delimiter $$    -- 将SQL语句的结束符临时改为$$(这个符号可以自定义)
mysql>create procedure delete_matches(in p_playerno integer)
->begin
->    delete from matches
->    where playerno = p_playerno;
->end$$

mysql>delimiter ;   -- 结束符号改回;