MySQL的连接与存储

简介

MySQL的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接，左连接和右连接。

外连接：分为左连接和右连接。

左外连接就是以左表为准，去匹配右表，左表有多少条数据，结果就是多少条数据。左表有的数据正常显示，右表没有的数据就用NULL显示。

mysql> create table chengji(id int(3) not null primary key auto_increment,age int(3) not null,name varchar(64) not null,scrore decimal(4,1));
mysql> insert into chengji(age,name,scrore) values(17,'zhangsan',60),(18,'lisi',70),(18,'wangwu',80),(17,'zhaoliu',95),(19,'tianqi',55);
mysql> select * from chengji;
+----+-----+----------+--------+
| id | age | name     | scrore |
+----+-----+----------+--------+
|  1 |  17 | zhangsan |   60.0 |
|  2 |  18 | lisi     |   70.0 |
|  3 |  18 | wangwu   |   80.0 |
|  4 |  17 | zhaoliu  |   95.0 |
|  5 |  19 | tianqi   |   55.0 |
+----+-----+----------+--------+
5 rows in set (0.00 sec)

mysql> create table sushe(id int(3) not null primary key,num int(3) not null);
mysql> insert into sushe values(1,305),(2,306),(3,307),(8,308),(9,309),(10,310);
mysql> select * from sushe;
+----+-----+
| id | num |
+----+-----+
|  1 | 305 |
|  2 | 306 |
|  3 | 307 |
|  8 | 308 |
|  9 | 309 |
| 10 | 310 |
+----+-----+
6 rows in set (0.00 sec)

内连接
mysql> select * from chengji c inner join sushe s on c.id=s.id;
+----+-----+----------+--------+----+-----+
| id | age | name     | scrore | id | num |
+----+-----+----------+--------+----+-----+
|  1 |  17 | zhangsan |   60.0 |  1 | 305 |
|  2 |  18 | lisi     |   70.0 |  2 | 306 |
|  3 |  18 | wangwu   |   80.0 |  3 | 307 |
+----+-----+----------+--------+----+-----+
3 rows in set (0.00 sec)

左连接
mysql> select * from chengji c left join sushe s on c.id=s.id;
+----+-----+----------+--------+------+------+
| id | age | name     | scrore | id   | num  |
+----+-----+----------+--------+------+------+
|  1 |  17 | zhangsan |   60.0 |    1 |  305 |
|  2 |  18 | lisi     |   70.0 |    2 |  306 |
|  3 |  18 | wangwu   |   80.0 |    3 |  307 |
|  4 |  17 | zhaoliu  |   95.0 | NULL | NULL |
|  5 |  19 | tianqi   |   55.0 | NULL | NULL |
+----+-----+----------+--------+------+------+
5 rows in set (0.00 sec)

右连接
mysql> select * from chengji c right join sushe s on c.id=s.id;
+------+------+----------+--------+----+-----+
| id   | age  | name     | scrore | id | num |
+------+------+----------+--------+----+-----+
|    1 |   17 | zhangsan |   60.0 |  1 | 305 |
|    2 |   18 | lisi     |   70.0 |  2 | 306 |
|    3 |   18 | wangwu   |   80.0 |  3 | 307 |
| NULL | NULL | NULL     |   NULL |  8 | 308 |
| NULL | NULL | NULL     |   NULL |  9 | 309 |
| NULL | NULL | NULL     |   NULL | 10 | 310 |
+------+------+----------+--------+----+-----+
6 rows in set (0.00 sec)

MySQL的存储过程

1.简介：

• 是一组为了完成特定功能的SQL语句集合
• 比传统SQL速度更快，执行效率更高

存储过程的优点

• 执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
• SQL语句加上控制语句的集合，灵活性高
• 在服务器端存储，客户端调用时，降低网络负载
• 可多次重复被调用，可随时修改，不影响客户端调用
• 可完成所有的数据库操作，也可控制数据库的信息访问权限

2.创建存储过程

• 使用CREATE PROCEDURE语句创建存储过程
• 创建存储过程的语法结构
  CREATE PROCEDURE <过程名> （过程参数[…]）<过程体> [过程参数[…]] 格式 [IN|OUT|INOUT] <参数名> <类型>

3.参数分为

• 输入参数：IN
• 输出参数：OUT
• 输入/输出参数：INOUT

4.存储过程的主体部分，被称为过程体
5.以BEGIN开始，以END结束，若只有一条SQL语句
6.以DELIMITER开始和结束
7.存储过程

mysql> delimiter $$
mysql> create procedure a()
    -> begin
    -> select * from chengji limit 3;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call a();
+----+-----+----------+--------+------+
| id | age | name     | scrore | num  |
+----+-----+----------+--------+------+
|  1 |  17 | zhangsan |   60.0 | NULL |
|  2 |  18 | lisi     |   70.0 | NULL |
|  3 |  18 | wangwu   |   80.0 | NULL |
+----+-----+----------+--------+------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$
mysql> create procedure p(in num1 int,out num2 int,inout num3 int)
    -> begin
    -> select num1,num2,num3;
    -> set num1=10,num2=20,num3=30;
    -> select num1,num2,num3;
    -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call p(@num1,@num2,@num3);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
|    1 | NULL |    3 |
+------+------+------+
1 row in set (0.00 sec)

+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
|   10 |   20 |   30 |
+------+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
in和inout参数会将全局变量的值传入存储过程中，而out参数不会将全局变量的值传入存储过程中，在全局过程使用中，参数值in、out、inout都会发生改变


mysql> select @num1,@num2,@num3;
+-------+-------+-------+
| @num1 | @num2 | @num3 |
+-------+-------+-------+
|     1 |    20 |    30 |
+-------+-------+-------+
1 row in set (0.00 sec)
调用完存储过程后，发现in参数不会对全局变量的值引起变化，而out和inout参数调用完存储过程后，会对全局变量的值产生变化，会将存储过程引用后的值赋值给全局变量，in参数赋值类型可以是变量还有定值，而out和inout参数赋值类型必须是变量

8.修改存储过程

• 存储过程的修改分为特征修改和内容修改
• 特征修改的方法
  ALTER PROCEDURE <过程名> [<特征>…]
• 内容修改可先删除原有存储过程，之后再创建方法

9、删除存储过程

• 删除存储过程的语法

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

• 删除的过程

mysql> drop procedure a;
mysql> call a();
ERROR 1305 (42000): PROCEDURE score.a does not exist