Mysql（三）

create procedure 存储过程名(参数列表)
begin 
     存储过程体（一组合法的sql语句）
end

#案列1：创建存储过程实现 根据女神名，查询对应的男神信息
create procedure my2(in beautyName VARCHAR(20))
begin 
     select bo.* 
     from boys bo
     right join 
     beauty b 
on bo.id=b.boyfriend_id  
where b.name=beautyName;
end $
#调用
call my4('柳岩')$

create table if not exists user(
    id int primary key auto_increment,
    username varchar(20),
    password varchar(20)
);
insert into user values(1,'xiaozhi','123'),(2,'xiaoxing','123');
#比较是否登录成功
delimiter $
create procedure my7(in username varchar(20),in password varchar(20))
begin 
    declare result int DEFAULT 0;#声明局部变量
   select count(*) into result
   from user 
   where USER.username=username
   and USER.password=password; 
   select if(result>0,'成功','失败');#调用局部变量
end $
#调用
call my6('xiaozhi','123')$

#3.创建带out模式的存储过程
#根据女神名，返回对应的男神名
delimiter $
create procedure cc1(in beautyName varchar(20),out boysName varchar(20))
BEGIN
     select bo.boyName into boysName
     from boys bo
     inner join beauty b 
     on bo.id=b.boyfriend_id
     where b.`name`=beautyName;
end $
#调用
set @bname$
call cc1('小昭',@bname)$
select @bname$

#案列2：根据女神名，返回对应的男神名和男神魅力值'
delimiter $
create procedure cc2(in beautyName varchar(20),out boysName varchar(20),out userCP int)
BEGIN
 select bo.boyName,bo.userCP into boysName,userCP
     from boys bo
     inner join beauty b 
     on bo.id=b.boyfriend_id
     where b.`name`=beautyName;
end $
#调用
call cc2('小昭',@bName,@usercp)$
select @bName,@usercp$

#案例一 传入a和b两个值，最终a和b都翻倍返回
create procedure cc3(inout a int,inout b int)
begin 
     set a =a*2;
     set b=b*2;
end $
set @m=10$
set @n=20$
call cc3(@m,@n)$
select @m,@n$

drop procedure cc3;

create function 函数名(参数列表) returns 返回类型
begin
        函数体
end

#案例：返回公司的员工个数
delimiter $
create function myf1() returns int
begin
     declare c int default 0;#定义变量
     select count(*) into c
     from employees;
     return c;
end $
select myf1()$

#案列1： 根据员工名返回工资
desc employees;
select * from employees;
create function myf4(empName varchar(20)) returns double
begin
    set @sal=0;#定义用户变量
    select salary into @sal
    from employees
    where last_name=empName;
    return @sal;
end $
select myf4('Hunold')$

#根据部门名，返回该部门的平均工资
desc departments;
select * from departments;
create function myf5(employeesName varchar(20)) returns double
begin
     declare sal double;
     select avg(salary) into sal
     from employees e
     inner join departments d
     on e.department_id=d.department_id
     where d.department_name=employeesName;
     return sal;
end $
select myf5('Adm')$

#一、创建存储过程实现传入用户名和密码，插入到admin表中
desc admin;
delimiter $
create procedure ccp1(in usernamec varchar(10),in password varchar(10))
begin
     insert into admin(username,password) values(usernamec,password);
end $
call ccp1('aaa','aaa')$
select * from admin$
show create PROCEDURE ccp1;
#二、创建存储过程实现传入女神编号，返回女神名称和女神电话
desc beauty;
select * from beauty;
create procedure ccp2(in id int,out name varchar(20),out  phone varchar(20))
begin 
     select b.name,b.phone into name,phone
     from beauty b
     where b.id=id;
end $
call ccp2(1,@gname,@gphone)$
drop PROCEDURE ccp2$
#三、创建存储存储过程或函数实现传入两个女神生日，返回大小
#DATEDIFF(expr1,expr2):返回两个日期之间的时间，expr2-expr1
desc beauty $
select * from beauty;
CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)
BEGIN
  SELECT DATEDIFF(birth1,birth2) INTO result;
END $
call test_pro3('1999-02-03',NOW(),@result)$

#创建存储过程，根据传入的成绩，来显示等级，比如传入成绩：90-100,显示A 
#80-90 显示B 60-80，显示C  否则 显示D
delimiter $
create procedure cc_case1(in score int)
begin
     case  
     when score >90 and socre<100 then select 'A';
     when score >80 then select 'B';
     when score >60 then select 'C';
     else select 'D';
     end case;
end $

          -- 创建旅游线路分类表 tab_category
      -- cid 旅游线路分类主键，自动增长
      -- cname 旅游线路分类名称非空，唯一，字符串 100
      CREATE TABLE tab_category (
        cid INT PRIMARY KEY AUTO_INCREMENT,
        cname VARCHAR(100) NOT NULL UNIQUE
      );
      -- 创建旅游线路表 tab_route
      /*
      rid 旅游线路主键，自动增长
      rname 旅游线路名称非空，唯一，字符串 100
      price 价格
      rdate 上架时间，日期类型
      cid 外键，所属分类
      */
      CREATE TABLE tab_route(
        rid INT PRIMARY KEY AUTO_INCREMENT,
        rname VARCHAR(100) NOT NULL UNIQUE,
        price DOUBLE,
        rdate DATE,
        cid INT,
        FOREIGN KEY (cid) REFERENCES tab_category(cid)
      );
      /*创建用户表 tab_user
      uid 用户主键，自增长
      username 用户名长度 100，唯一，非空
      password 密码长度 30，非空
      name 真实姓名长度 100
      birthday 生日
      sex 性别，定长字符串 1
      telephone 手机号，字符串 11
      email 邮箱，字符串长度 100
      */
      CREATE TABLE tab_user (
        uid INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(100) UNIQUE NOT NULL,
        PASSWORD VARCHAR(30) NOT NULL,
        NAME VARCHAR(100),
        birthday DATE,
        sex CHAR(1) DEFAULT '男',
        telephone VARCHAR(11),
        email VARCHAR(100)
      );
      /*
      创建收藏表 tab_favorite
      rid 旅游线路 id，外键
      date 收藏时间
      uid 用户 id，外键
      rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
      */
      CREATE TABLE tab_favorite (
        rid INT, -- 线路id
        DATE DATETIME,
        uid INT, -- 用户id
        -- 创建复合主键
        PRIMARY KEY(rid,uid), -- 联合主键
        FOREIGN KEY (rid) REFERENCES tab_route(rid),
        FOREIGN KEY(uid) REFERENCES tab_user(uid)
      );