数据库的基本操作(4)

先总结一下上一章的内容。

1.修改

update 表名 set 列名 = 值... where 条件；

2.删除

delete from 表名 where 条件；

3.mysql的约束

约束：数据库对数据本身有一些要求和限制。

NOT NULL 数据不能为空。

UNIQUE 数据唯一（针对列来描述）

DEFAULT 默认值

PRIMARY KEY 数据的唯一身份标识

FOREIGN KEY 外键约束，针对两张表进行关联。

下面开始介绍新的内容。

一.表的设计

所谓的“数据库设计”“表的设计”其实解释根据实际的问题场景，把表给构造出来。

一个经典的通用的办法，先找出这个场景涉及到的“实体”，然后再来分析“实体之间的关系”。

分析实体之间的关系，就是“小学生造句” 

1.1一对一

以教务系统为例

student表（学生的id，学生姓名，学生班级...）

user表（用户的账号，密码...）

造的句子：一个账号对应一个学生，一个学生也只有一个账号。

那怎么在数据库中如何表示这种一对一的关联关系呢？

方法一：可以把这两个实体用一张表来表示

方法二：可以用两张表来表示，之后再两张表中加一个id。使得两个表中的id可以对应。根据这个对应关系，就随时可以找到某个账户对应的账户是谁，也可以找到学生对应的账户是啥。

1.2.一对多

以教务系统为例：

student表（学号，姓名...）

class表（班级编号，班级名称...）

造句：一个学生再一个班级之中，但是一个班级可以包含很多学生。

方法一：班级表不变，在学生表中新增一列班级编号，用来随时可以找到对应的班级是哪个。

方法二：在班级表中新增一个学生编号，里面存班里面的学生编号。

1.3.多对多

同样的，也是以教育系统为例：

学生表和课程表；

造句：一个学生可以选择多门课程，一个课程也包含很多学生。

在表的设计中是介绍一下简单的逻辑，在后面会讲到如何对其进行应用。

二.新增

和查询在一起的新增操作，把从上一个表中的查询结果，作为下一个表要插入的输出的数据。

下面我用实际的操作来演示一下整体的过程。

通过代码我们可以看出，当ab两个表的格式一致时，我们可以通过搜索a里面的数据，将a中的数据插入到b中。

 

通过该代码我们可以看出，当我们将ab里面的表顺序相反，之后发现无法插入，但是当我们按照b里面的列的顺序查询可以发现，可以将a中的表的数据插入到b中。

三.查询

3.1聚合查询

3.1.1.聚合函数

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

 expr写的是列名、表达式，聚合函数就相当于把同一列的若干行都相加到一起。

DISTINCT表示去重，根据对数据的去重和不去重，得到的数据结果不一定一样。

下面我们用代码的形式来展现一下效果。

 首先我们先查看表里面的数据，之后用count（*）来看表中的数据，显示的个数为8。

之后我们用sum（*）的时候发现报错，主要的原因是因为这些操作主要是针对列来进行计算的，之后我们用count来单独针对一列来进行操作的时候发现，个数为7，比之前少了一个，可以发现null是不在计数范围之内的。

同时聚合函数也是可以通过where语句来进行使用的。

 3.1.2.group  by 子句

这是一个分类语句，所谓的分类，其实就是按照某一列的值来进行分组，之后把相同的放到一起。

现在我们输进去一些数据。

给出一个题目： 查询每个角色的最高工资、最低工资和平均工资

根据题目要求我们可以发现这个是按照肉了来进行分组的，代码如下：

 3.1.3.having

having和where其实是类似的，只是用的时间不一样，前面提到过，在MySQL里面不是按照我们写代码的先后顺序来执行操作的。在进行上面了上面讲的分组操作之后，进行指定条件的筛选就需要用到having，而where是在分组条件之前来进行操作的，下面我就用代码来给大家展示一下整体的过程。

还是之前的数据，用where的来做的话，我除了题目就是把数据中‘马云’的书进行删除。

我们会发现以及将马云代表的服务员的选项进行了删除。

having是对分组之后的才做进行条件的约束，给出的题目是平均工资为一万一下。

 

通过having操作之后我们会发现已经将分组之后董事长的工资进行了删除。

从这里可以看出，根据场景的不同，我们需要进行不同的操作方法，在分组之前进行条件用where，在分组之后我们用having，当然having和where可以同时使用。 

3.2.联合查询

联合查询，就是把多个表的记录合并在一起之后进行查询，也可以叫做多表查询。

夺标查询可以说是整个sql里面最复杂的部分，在一些笔试和面试当中考的次数和频率会比较多，但是在很多实际的开发中会禁止使用多表查询。

多表查询的核心操作就是笛卡尔积，简单的说就是排列组合。笛卡尔积是针对任意两张表之间进行的运算。

 如何在SQL中进行笛卡尔积呢？最简单的做法就是直接select * from 后面跟上多个表名，表名之间只用都好分割。下面用简单的例子来演示一下。

 但是，我们会发现通过笛卡尔积会产生很多没有用的数据，下面就会讲一些操作使得数据变得合理化。

我们通过上面的表中可以看出，在两个表中都有classId，向这里的classId就是“连接条件”。

带有连接条件的笛卡尔积其实就是多表查询了。

 通过表名加.的方式来进行=，之后选出有用的数据。

当然如果列名不会混淆（不是同名的），可以用表名.列名也可以省略表名。

我们也可以对数据进行精简，比如上面的数据，我们只想要姓名和班级。

通过不同的方法以及选择可以得出结果。

上面只是简单的例子还有介绍，下面我会慢慢用难一点的例子来进行讲解。

下面先将我创建的表里面的数据展示一下：

 

 

3.2.1内连接 

 先亮题目：查询“许仙”同学的 成绩

其实这里面有两种内连接的方法，一个是和以前一样，直接用where条件来进行查询，还有一个是join...on...但是我个人觉得join...on...没有where好用，虽然join...on...多了一些用处。

下面我先来解决一下这条题目：

做这种题目，我们优先去找相关的表，之后才能进行操作，查学生成绩首先需要学生表以及学生成绩表。之后发现两个表是通过id来进行连接的，所以在where里面来输入id相等的条件以及name为许仙。

 这种写法的格式是：from 表一 join 表二 on 条件；

前面写法的格式是from 表一 ，表二 where 条件；

这两种写法都是等价的，但是相比较而言，我更喜欢where，但是join  on 有的功能是from多个表无法实现的，之后会进行介绍。

下面再来一题试试水：查询所有同学的总成绩，及同学的个人信息：

首先分析一下这个题目，需要学生的成绩那么就肯定是要成绩表的，之后还有个人信息，那么就是学深表和成绩表来进行内连接。

 首先我一开始做的时候发现，按照之前的方法计算会使得一个人有好几个成绩，所以后面我进行了分组之后，分组之后运用聚合查询的sum操作，结果如下：

 这样就可以得到每个人的总成绩。

再来一个比较难的题目：查询所有同学的成绩，及同学的个人信息：

再这个题目里面我们需要用到学生表，成绩表以及课程表：

以上就是多表查询内连接的操作。

既然都推荐使用这个from多个表where..那么join on 还有啥用？

上面说了这个from多个表where写法叫做“内连接”

使用join ... on...的写法既可以表示内连接还可以表示外连接。

select 列 from 表1 inner join 表2 on 条件；

inner join 表示的是内连接，当然inner是可以省略的；

select 列 from 表1 left join 表2 on 条件；左外连接

select 列 from 表1 right join 表2 on 条件；右外连接

讲一下什么是左右外连接；下面我用一个实际的例子来演示一下：

这是我新录入的 数据，从数据中我们可以发现，虽然student里面的class可以和class里面的id进行对应，但是有一部分是没有办法对应的，当我们用内连接来试试效果；

 

 通过内连接我们会发现，里面少了一个人的数据。

当我们试试外连接，看看外连接的效果：

 通过上面的例子我们可以看到left join 和inner join 之间的区别

left join 是以左侧的表为主，会将左侧的所有数据全部列出来，如果右边的表中没有对应的就用null来代替。

inner join 是要求两个表中都同时有的数据才可以进行连接。

right join和left join的效果类似。