简述： 本篇是数据库的学习笔记，仅供自己学习使用。

本文初发于 “曾晨de小站” zengchen233.cn，同步转载于此。

数据库和表的基本操作（四）

内连接查询

  • 仅将两个表中满足连接条件的行组合起来作为结果集，称为内连接；
  • 关键字：[inner] join ... on。

语法：

表1 [inner] join 表2 on 表1.字段=表2.字段

语法解释：

从表1中取出每一条记录，去表2中与所有的记录进行匹配，匹配必须是某个条件在表1中与表2中相同，最终才会保留结果，否则不保留。inner 关键字可省略不写；on 表示连接条件：条件字段就是代表相同的业务含义（如下面两张表中的 employee.dept_id 和 department.id），大多数情况下为两张表中的主外键关系。

外连接查询

  • 以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留。能匹配，正确保留；不能匹配，其它表的字段都置空（null），称为外连接。
  • 外连接查询分为左外连接查询和右外连接查询；
  • 关键字：left/right [outer] join ... on。

语法：

表1 left/right [outer]  join 表2 on 表1.字段=表2.字段

语法解释：

左外连接：在内连接的基础上，还包含表1中所有不符合条件的数据行，并在其中的表2列填写 NULL；
右外连接：在内连接的基础上，还包含表2中所有不符合条件的数据行，并在其中的表1列填写 NULL。

Tips：虽然左外连接和右外连接有主表差异，但左外连接和右外连接可以互转。

复合条件连接查询

  • 复合条件连接查询，就是在连接查询的过程中，通过添加过滤条件来限制查询结果，使查询结果更加精确。

使用having子句进行分组筛选

简单来说，having子句用来对分组后的数据进行筛选，即having针对查询结果中的列发挥筛选数据作用。因此having通常与Group by连用。

基本格式：

select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名] [having 字段名 筛选条件]

select语句中，where、group by、having子句和聚合函数的执行次序如下：

1.where子句从数据源中去除不符合条件的数据；

2.然后group by子句搜集数据行到各个组中；

3.接着统计函数为各个组计算统计值；

4.最后having子句去掉不符合其组搜索条件的各组数据行。

Having与Where的区别

where子句都可以用having代替，区别在于where过滤行，having过滤分组；

  • where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行；
  • having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件过滤出特定的组，也可以使用多个分组标准进行分组。

Group By 和 Order By

基本格式：

select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名] [order by 字段名 排序方向]

Tips：若分组字段和排序字段一样时，可不需要order by关键字，则只需告知排序方向。

COUNT()函数基本使用

COUNT()函数是用来统计记录的总条数。

select count(*/字段名) from 数据表;

SUM()函数基本使用

SUM()函数是对数据表的某列进行求和操作。

select sum(字段名) from 数据表;

AVG()函数基本使用

AVG()函数是对数据表的某列进行求平均值操作。

select avg(字段名) from 数据表;

MAX()函数基本使用

MAX()函数是求某列的最大数值。

select max(字段名) from 数据表;

MIN()函数基本使用

MIN()函数是求某列的最小数值。

select min(字段名) from 数据表

视图的定义

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。通过视图，可以展现基表（用来创建视图的表）的部分数据；视图数据来自定义视图的查询表。

我们知道了视图的定义，那么，为什么要使用它呢？

因为视图有如下优点：

①. 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集；

②. 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行或列，但是通过视图就可以简单的实现；

③. 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列队视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

创建视图

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

参数说明：

  • OR REPLACE：表示替换已有视图；
  • ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)： MySQL 自动选择要使用的算法 ；merge合并；temptable临时表；
  • column_list：可选参数，指定视图中各个属性的名词，默认情况下与select语句中查询的属性相同；
  • select_statement：表示select语句；
  • [WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内；cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件；local表示更新视图的时候，要满足该视图定义的一个条件即可。

在实际开发中，用户可以根据自己的需求，通过视图的方式，获取基本表中自己需要的数据，这样既能满足用户的需求，也不会破坏基本表原来的结构，从而保证了基本表中数据的安全性。

操作视图

视图是逻辑表，也就是说视图不是真实的表，但操作视图和操作普通表的语法是一样的。

用户可以在视图中无条件地使用select语句查询数据。但使用insert、update和delete操作需要在创建视图时满足以下条件（满足以下条件的视图称为可更新视图）：

  • from子句中只能引用有1个表（真实表或可更新视图）；
  • 不能包含 with、distinct、group by、having、limit等子句；
  • 不能使用复合查询，即不能使用union、intersect、except等集合操作；
  • select子句的字段列表不能包含聚合、窗口函数、集合返回函数。

删除视图

若视图不再被需要，我们可以将其删除，且视图的删除并不影响源表中的数据。

删除视图的 SQL 如下：

DROP VIEW view_name;

存储过程的定义

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的 SQL 语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

简单的说存储过程就是具有名字的一段代码，用来完成一个特定的功能。

存储过程的创建和查询

创建存储过程：create procedure 存储过程名(参数)

  • 下面我们来创建第一个存储过程

每个存储的程序都包含一个由 SQL 语句组成的主体。此语句可能是由以分号（;）字符分隔的多个语句组成的复合语句。例如：

CREATE PROCEDURE proc1()
BEGIN
SELECT * FROM user;
END;

在命令行客户端中，如果有一行命令以分号结束，那么回车后，MySQL 将会执行该命令，但在创建存储过程中我们并不希望 MySQL 这么做。

MySQL 本身将分号识别为语句分隔符，因此必须临时重新定义分隔符以使 MySQL 将整个存储的程序定义传递给服务器。

要重新定义 MySQL 分隔符，请使用该 delimiter命令。使用 delimiter 首先将结束符定义为//，完成创建存储过程后，使用//表示结束，然后将分隔符重新设置为分号（;）：

DELIMITER //
CREATE PROCEDURE proc1()
BEGIN
SELECT * FROM user;
END //
DELIMITER ;

注意：/也可以换成其他符号，例如$;

  • 执行存储过程：call 存储过程名

  • 创建带有参数的存储过程 存储过程的参数有三种：

    • IN：输入参数，也是默认模式，表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回；
    • OUT：输出参数，该值可在存储过程内部被改变，并可返回；
    • INOUT：输入输出参数，调用时指定，并且可被改变和返回。
存储过程的查询和删除

我们如何在数据库中查询我们已经创建过的存储过程呢：

SHOW PROCEDURE STATUS WHERE db='数据库名';

查看存储过程的详细定义信息：

SHOW CREATE PROCEDURE 数据库.存储过程名;

当我们不再需要某个存储过程时，我们可以使用：

DROP PROCEDURE [IF EXISTS] 数据库名.存储过程名;