一、多表连接查询

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

使用较多的连接查询包括：内连接、左连接和右连接

-- 创建表模板
mysql> create table yaling(a_id int(10) default null,a_name varchar(30) default null,a_level int(10) default null);

mysql> create table galing(a_id int(10) default null,a_name varchar(30) default null,a_level int(10) default null);

mysql> insert into gangling values(2,'bbb',20),(3,'ccc',30),(5,'eee',50),(6,'fff',60);

mysql> insert into galing values(2,'bbb',20),(3,'ccc',30),(5,'eee',50),(6,'fff',60);
mysql> select * from yaling;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
|    1 | aaa    |      10 |
|    2 | bbb    |      20 |
|    3 | ccc    |      30 |
|    4 | ddd    |      40 |
+------+--------+---------+
4 rows in set (0.00 sec)

mysql> select * from galing;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
|    2 | bbb    |      20 |
|    3 | ccc    |      30 |
|    5 | eee    |      50 |
|    6 | fff    |      60 |
+------+--------+---------+
4 rows in set (0.01 sec)

1.内连接——inner join

左表对右边进行匹配，只返回两个表中联接字段相等的行

SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;

mysql> select a_id,b_name from yaling inner join galing on a_name=b_name; 
+------+--------+
| a_id | b_name |
+------+--------+
|    2 | bbb    |
|    3 | ccc    |
+------+--------+
2 rows in set (0.00 sec)

2.左连接——left join

返回左表中所有的记录+右表中和左表相同的数据，不同的地方均为null补足

SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;

mysql> select * from yaling left join galing on a_name=b_name;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbb    |      20 |    2 | bbb    |      20 |
|    3 | ccc    |      30 |    3 | ccc    |      30 |
|    1 | aaa    |      10 | NULL | NULL   |    NULL |
|    4 | ddd    |      40 | NULL | NULL   |    NULL |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)

3.右连接——right join

返回右表中所有的记录+左表中和右表相同的数据，不同的地方均为null补足

SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

mysql> select * from yaling right join galing on a_name=b_name;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbb    |      20 |    2 | bbb    |      20 |
|    3 | ccc    |      30 |    3 | ccc    |      30 |
| NULL | NULL   |    NULL |    5 | eee    |      50 |
| NULL | NULL   |    NULL |    6 | fff    |      60 |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)

二、数据库函数

MySQL数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更高效的输出

常用的包括：数学函数、聚合函数、字符串函数和日期时间函数

1.数学函数

• 数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数

2.聚合函数

• Mysql 数据库函数中专门有一组函数特意为库内记录求和或者对表中的数据进行几种概括而设计的，这些函数被称作聚合函数

select avg(id) from xjj;
select count(id) from xjj;
select min(id) from xjj;
select max(id) from xjj;
select sum(id) from xjj;

3.字符串函数

select trim(' 123abc');

select concat('abc',123);

select substr('abcdefg',3);
select substr('abcdefg',3,2);

select length('abcdefg');

select replace('abcdefg','a','1');

select upper('abc');

select lower('ABC');

select left('abcdefg',3);

select right('abcdefg',3);

select repeat('abc',2);

select length(space(3)); 

select strcmp(1,2),strcmp(2,2),strcmp(3,2);

select reverse('abcdefg');

4.日期时间函数

select dayofweek(curtime());
select dayofmonth(curtime());
select dayofyear(curtime());
select curdate();
select curtime();
select now();

三、存储过程

1.简介

• MySQL数据库存储过程是一组为了完成特定功能的SQL语句的集合
• 存储过程这个功能是从5.0版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性
• 存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，在这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可
• 操作数据库的传统SQL语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高，存储过程在数据库中创建并保存，它不仅仅时SQL语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式

2.优点

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

3.语法

create procedure <过程名> ([过程参数……]) <过程体>
[过程参数……] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[in|out|inout] <参数名><类型>

①示例

示例（不带参数的创建）
##创建存储过程##
delimiter $$							#将语句的结束符号从分号;临时改为两个$$(可以自定义)
create procedure club()					#创建存储过程，过程名为club，不带参数
-> BEGIN								#过程体以关键字 BEGIN 开始
-> create table mk (id int (10), name char(10),score int (10));
-> insert into mk values (1, 'xjj',22);
-> select * from mk;			        #过程体语句
-> end $$								#过程体以关键字 END 结束
delimiter ;								#将语句的结束符号恢复为分号

show create procedure club\G		#查看culb储存过程的具体信息
show procdure status			    #查看存储过程
mysql> show procedure status like '%proc%'\G
*************************** 1. row ***************************
                  Db: puxin
                Name: Proc
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2021-10-29 18:54:15
             Created: 2021-10-29 18:54:15
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

②参数分类

• 存储过程的主体部分，即过程体
• 以begin开始，end结束，若只有一条SQL语句，可省略begin和end
• 以delimiter开始和结束

③带参数的存储过程

• 输入参数：in表示调用者向过程传入值（传入值可以时字面量或变量）
• 输入参数：out表示过程向调用者传出值
• 输入/输出参数：inout，；表示调用者向过程传入值，又表示过程向调用者传入值（只能是变量）

mysql> delimiter @@
mysql> create procedure qisi (in inname varchar(40))     #行参
    -> begin
    -> select * from yaling where name=inname;
    -> end @@
mysql> delimiter @@			
mysql> call proc2('wangwu');              #实参
+--------+-------+---------+
| name   | score | address |
+--------+-------+---------+
| wangwu | 80.00 | beijing |
+--------+-------+---------+
1 row in set (0.00 sec)

④修改存储过程

alter procedure <过程名> [<特征>……]
alter procedure qisi modifies sql data sql security invoker;

modifies sql data：表名子程序包含写程序的语句

security：安全等级

invoker：当定义为 invoker 时，只要执行者有执行权，就可以成功执行

⑤删除存储过程

• 存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程

drop procedure if exists qisi;