Mysql（三）-阿里云开发者社区

存储

1.创建语法

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

注意：

①.参数列表包括三部分参数模式参数名参数类型

eg:in stuname varchar(20)

参数模式：

in:该参数可以作为输入,也就是改参数需要调用方法传入值

out: 该参数可以作为输出,也就是改参数可以作为返回值

inout:该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，也可以返回值

②如果存储过程体仅仅只有一句话,begin end可以省略，存储过程中的每条sql语句要求必须要加分号;存储过程的结尾可以使用delimiter

语法：

delimiter 结束标记 -->delimiter $

注意：delimiter的作用

其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

如输入下面的语句mysql> select * from test_table;然后回车，那么MySQL将立即执行该语句。但有时候，不希望MySQL这么做。在为可能输入较多的语句，且语句中包含有分号。

2.调用：call 存储过程名(实参列表)

input

#案列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模式的存储过程

#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$

4.创建带inout模式参数的存储过程

#案例一 传入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$

5.存储过程的删除

语法：drop procedure 存储过程名；

注意：一次只能删除一个

drop procedure cc3;

函数

1.函数与存储的区别

存储过程：可以有0个返回，也可以有多个返回，适合做批量的插入，批量的更新

函数：有且仅有一个返回，适合做处理数据后返回一个结果

2.创建语法[掌握]

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

3.注意：

①参数列表包括两部分：参数名，参数类型

②函数体：肯定会有return语句，如果没有会报错

③如果return语句没有放在函数体的最后也不会报错，不建议

④函数体中仅有一句话，则可以省略begin end

⑤使用delimiter语句设置结束标志

4.调用select 函数名（参数列表）[掌握]

案列：无参有返回的

#案例：返回公司的员工个数
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')$

5.查看函数：show create function myf3;

6.删除函数：drop function myf3;

存储和函数习题

#一、创建存储过程实现传入用户名和密码，插入到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)$

流程控制结构

顺序结构：程序从上往下依次执行

分支结构：程序从两条或多条路径中选择一条去执行

循环结构：程序在满足一定条件的基础上，重复执行一段代码

顺序结构

①if函数：实现简单的双分支

语法：select if(表达式1，表达式2，表达式3)

如果表达式1成立，则IF 函数返回表达式2的值，否则返回表达式3的值

②case结构

情况1：类似于java中农的switch语句，一般用于实现等值的判断

语法：

case 变量|表达式|字段

when 要判断的值 then 返回的值1 或语句

when 要判断的值 then 返回的值2 或语句

…

else 要返回的值n 或语句n

end case

情况2：类似于java中的多重if语句，一般用于实现区间判断

语法

case

when 要判断的条件1 then 返回的值1

when 要判断的条件1 then 返回的值2

…

else 要返回的值n

end

特点：

①可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end中或begin end外面

②可以作为独立的语句去使用，只能放在begin end中

#创建存储过程，根据传入的成绩，来显示等级，比如传入成绩：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 $

数据库的设置

1. 多表之间的关系

1>. 分类

一对一（了解）：人和身份证

一个人只有一个身份证，一个身份证只能对应一个人

一对多、多对一（掌握）部门和员工

一个部门有多个员工

一个员工只能对应一个部门

多对多：学生和课程

一个学生可以选择很多门课程

一个课程也可以被很多学生选择

2>. 一对一

一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键

一对一可以把两张表合成一张表

3>. 一对多（多对一）

实现方式：在多的一方建立外键，指向一的一方的主键 [ 如：部门和员工 ]

4>. 多对多

多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

2. 数据库设置的范式

          -- 创建旅游线路分类表 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)
      );

3. 数据库设置的范式

1>. 范式的概念

设置数据库时，需要遵循一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求

设计关系数据时，遵从不同的范式要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小（重复少）

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式、第四范式（4NF）、第五范式（5NF，又称完美范式）

2>. 分类

这部分内容了解知道即可

第一范式（1NF）：每一列都是不可分割的原子数据项

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）

几个概念：

1. 函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A例如：学号–>姓名。（学号，课程名称） --> 分数

2. 完全函数依赖：A–>B，如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。例如：（学号，课程名称） --> 分数

3. 部分函数依赖：A–>B，如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。例如：（学号，课程名称） – > 姓名

4. 传递函数依赖：A–>B, B – >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A

例如：学号–>系名，系名–>系主任

5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码例如：该表中码为：（学号，课程名称）

* 主属性：码属性组中的所有属性

* 非主属性：除过码属性组的属性

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

Mysql（三）

存储