MYSQL学习笔记——连接以及存储过程

连接                                                                                              

      当我们需要从多个表查询数据时，我们就需要使用到连接操作，mysql支持内连接，左连接以及右连接三种连接方式。

数据库准备

      首先我们创建两个表t1, t2:

create table t1(i1 int, c1 varchar(10));
create table t2(i2 int, c2 varchar(10));

　　插入数据：

insert into t1 values (1, ‘a‘), (2, ‘b‘), (3, ‘c‘);
insert into t2 values (2, ‘c‘), (3, ‘b‘), (4, ‘a‘);

　　最后可以查看我们的数据库表如下：

内连接

      如果在SELECT语句的FROM子句中列出多个数据表，并用INNER JOIN将它们的名字隔开，MYSQL将执行内连接操作，这将通过把一个数据表里的数据行与另一个数据表里的数据行进行匹配产生结果。比如，下面的查询将把t1里的每一个数据行与t2里的每一个数据行组合：

      不加任何条件的内连接将产生笛卡尔积，结果行数是两张数据表行数的乘积，例如两张表分别有1000条数据，那么结果行数将有100万条数据，所以我们在用内连接时一般会加上条件，下面的查询只会选出t1.i1=t2.i2的行，大大减少了检出行：

外连接

      内连接只显示在两个数据表里都能找到匹配的数据行，外连接除了显示同样的匹配结果，还可以把其中一个数据表在另一个数据表里没有匹配的数据行也显示出来，mysql支持的外连接分左外连接（LEFT JOIN）和右外连接（RIGHT JOIN）。

      LEFT JOIN的工作情况是这样的：两个数据表，以左表为基准，当来自左表的某个数据行与右表的某个数据行匹配时，那两个数据表的内容就会作为一个输出行；如果右表没有匹配行，那么也会产生输出行，此时右表的内容用NULL填充。

      RIGHT JOIN与LEFT JOIN相似，只是以右表为基准，如果左表不匹配，用NULL填充。

      我们用LEFT JOIN代替上例的INNER JOIN，得到的输出如下：

      有时候，我们在编写左连接时，真正感兴趣的是那些左数据表里没被匹配的行，这时候我们可以用IS NULL判断条件把这些行挑选出来：

      RIGHT JOIN与LEFT JOIN十分相似，而且所有RIGHT JOIN都能改写成LEFT JOIN这里就不举例了，而且就我的工作经验来看，工作中统一写LEFT JOIN更好。

存储过程                                                                                           

      存储过程简单来说，就是为以后的使用而保存的一条或多条MYSQL语句的集合，可将其视为批文件，虽然它们的作用不仅限于批处理。

1、数据库准备

      首先使用以下命令创建一个数据库并批量插入数据：

create table g(num int,value  varchar(10)); 

insert into g values(1, ‘1‘),(10, ‘10‘),(60, ‘60‘),(100, ‘100‘);

      查看该表数据如下：

2、调用存储过程

      在创建存储过程之前，先说一下如何调用存储过程，调用存储过程的命令如下：

call 存储过程名字();

2.1、创建封装sql存储过程

      存储过程的一个作用就是封装sql，我们创建的第一个存储过程就是封装一个select语句：

DELIMITER //
CREATE PROCEDURE p1()
BEGIN
	select * from g;
END//
DELIMITER ;

     "DELIMITER //"的作用是把行结束符替换为//，默认的行结束符是";"。为什么要替换行结束符呢？因为创建存储过程是一条独立语句，但是这条创建语句内部一般都会包含以";"结束的语句，如果我们不更改结束符，那么语句解析到";"就会提前结束。

      以上面的语句为例，如果我们不更改行结束符，那么创建语句到"select * from g;"就会结束（因为遇到了行结束符";"），这显然不是我们想要的，但是我们更改结束符后，创建语句会一直解析到"END //"，成功创建存储过程。

      最后别忘了调用"DELIMITER ;"把行结束符改回来。

      创建完后我们调用一下该存储过程，结果如下：

2.2、创建包含参数的存储过程

      存储过程还可以包含参数，下面我们创建一个包含参数的存储过程　

DELIMITER //
CREATE PROCEDURE p2(IN n int, OUT avg double, INOUT min int)
BEGIN
	select avg(num) from g where num > n INTO avg;
	select min(num) from g where num > n INTO min;
END//
DELIMITER ;

      注意，在参数前面包含IN，OUT，INOUT关键字，IN表示传入值，OUT表示传出值，INOUT表示即可以传入也可以传出。

      最后，我们调用一下我们创建的存储过程如下：

2.3、包含控制结构的存储过程

      存储过程还可以包含if-else结构，例子如下：

DELIMITER //
CREATE PROCEDURE p3(IN n int, IN switch char(1))
BEGIN
	if switch=‘a‘ then
        select * from g where num > n;
    else 
	    select * from g where num > n;
	end if;     
END//
DELIMITER ;

　　调用该存储过程的结果如下： 

2.4、包含循环的存储过程

      除了包含if-else结构，存储过程还可以包含while循环结构，举例如下：

DELIMITER //
CREATE PROCEDURE p4(IN n int)
BEGIN
	declare i int;
	declare s int;
	set i = 1;
	set s = 0;
	while i <= n do
	    set s = s + i;
	    set i = i + 1;
	end while;
	select s;
END//
DELIMITER ;

　　该存储过程将计算从1到n的累加，调用该存储过程的例子如下：

3、查看现有的存储过程

      如果想知道数据库有哪些存储过程，我们可以使用以下命令查看：

show procedure status

4、删除存储过程

      如果想要删除存储过程，我们可以使用如下命令：

drop procedure 存储过程的名字

5、查看存储过程的创建语句

      有的时候我们想要查看存储过程的创建语句那么我们可以使用以下命令：

show create procedure 存储过程的名字

6、存储过程与存储函数　　

　　在mysql中除了存储过程，还有存储函数，存储过程与存储函数的区别有两点：

      1、创建语句不一样，存储过程使用命令"CREATE PROCEDURE"创建，存储函数使用命令"CREATE FUNCTION"创建；

      2、存储过程没有返回值，而函数有返回值。