MySQL 数据库【增删查改（二）】-Toy模板网

这篇具有很好参考价值的文章主要介绍了MySQL 数据库【增删查改（二）】。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

一、表的设计

1、一对一

2、一对多

3、多对多

二、新增

三、查询

1、聚合查询

（1）聚合函数：

（2） group by 子句

（3）having

2、联合查询

(1)内连接

(2)外连接

(3)自链接

(4)子查询

(5)合并查询

四、总结

一、表的设计

此处只是讨论一些比较基本的设计表的方法原则

设计数据库的表，就需要先把实体和关系梳理清楚

实体：从需求场景中，提炼出一些关键性的名词（可以理解成Java中的对象）

关系：是实体和实体之间的关联关系

数据库这里的关系只有四种：

1、一对一

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

一个人可以有一个身份证号

一个身份证号只能归一个人所有

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

这三种方法都是可以的

2、一对多

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

一个同学只能存在于一个班级中

一个班级，可以包含多个同学

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

这种设计方式，并不推荐

因为MySQL 这里的类型，并没有提供 '数组' 这样的类型

如果想要这么做，就需要按照一定的格式把多个 student 构造成一个字符串

这样的过程比较繁琐，也比较低效

但是有些数据库（redis）支持数组这样的类型，就可以采取上述的方法来设计了

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

按照 classId 来查询 student 就能够知道这个表里面的每个班级里包含哪些同学

3、多对多

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

一个同学，可以选择多门课程来学习

一门课程，也可以被多个同学来选择

往往表示多对多关系，需要引入一个关联表

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

二、新增

把insert 和 select 两个操作合并在一起了

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

把 select 查询出来的结果数据，插入到另一个表中

我们先重新创建两个表分别为 student 和 student2

并向 student 中插入三条数据：

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

然后再运用上述语法，将 student 中的数据转移到 studen2 中

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

此处 select 查询的记过得和插入的表能对应（列的数目，类型，约束得匹配

三、查询

1、聚合查询

表达式查询，本质上是在用列和列之间进行运算

还有的时候，我们需要进行 " 行和行 "之间的运算(聚合查询)

（1）聚合函数：

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

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

使用示例：

-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;

-- 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;、

-- 返回英语最高分
SELECT MAX(english) FROM exam_result;

-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

注意：在sql 中，要求聚合函数和 () 必须是紧紧挨在一起的

sum 是进行求和，会把这一列的若干行，按照 double 的方式进行累加。（会尝试把这一列的每一行的数据先转成 double ）

（2） group by 子句

有的时候，希望把数据进行分组来进行计算，这个时候就要用到 group by 语句

group by 语句是指定一个列，按照这个列进行分组，这一列中数值相同的行，会被放到一组

每个分组中，都可以使用上述的聚合函数来进行计算

我们重新创建一个员工表，并向其中插入几个数据：

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

现在，我们想统计每一个岗位的平均薪资，此时就可以使用 group by 子句

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

在分组查询中，select 指定的列，必须是当前group by 指定的列，如果select 想用到其它的列，其它的列必须放到聚合函数中，否则直接写，此时查询的结果无意义！！！

（3）having

分组查询，也是可以搭配条件来使用的

1、分组之前的条件： where

示例：求每个岗位的平均薪资，但是除掉张三

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

2、分组之后的条件 : having

示例：求每个岗位的平均薪资，但是除去平均薪资超过10000 的

分组之后的条件也是搭配聚合函数来使用的

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

3、一个SQL 中，可以同时包含分组前的条件和分组后的条件

示例：求每个岗位的平均薪资，但是除掉张三和除去平均薪资超过10000 的

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

2、联合查询

联合查询也叫做多表查询，是查询中最复杂的一种写法

联合查询就是给你多个表，结合多个表的数据，进行一些综合性更强的查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积： MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

针对任意的两张表都可以计算笛卡尔积，但是一般来说，如果两个表没有任何的关系，那么计算的结果也是无意义的

笛卡尔积就是把两个表里的记录按照排列组合的方式构成了一个更大的表

笛卡尔积的列数就是原来两个表的列数之和

笛卡尔积的行数就是原来两个表的行数之积

笛卡尔积是一个非常低效的操作，尤其是表的本身的记录比较多的情况

同样的多表联合查询也是非常低效的

因此在实际开发中，使用联合查询一定要慎重

计算笛卡尔积的时候，会出现一些不太靠谱的数据，如果此时我们加上链接条件，那么筛选出来的数据就都是合理的数据了

(1)内连接

语法：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

我们现在有四张表，包含了三个实体：学生、班级、课程

其中包含的关系有：学生和班级：一对多的关系、学生和课程：多对多的关系

分数表，就相当于学生和课程之间的这个关联表

我们先使用学生表和班级表进行笛卡尔积

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

此时可以看到，现在得到的这些结果就是笛卡尔积的结果

这个时候，如果 class_id 和 id 是一样的，那么我们就称这个数据是合法的数据

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

此时，我们的笛卡尔积在加上一些必要的条件后，就构成了多表联合查询

如果是单个表进行查询，条件中直接写列名即可

如果是多个表进行查询，条件中最好写作 ' 表名. 列名' 的形式，这是因为进行联合查询的这两个表里，有些列名可能是一样的，如果列名没有重复的，此时直接写列名也不是不可以，但是最好还是带上表名

示例：查询许仙同学的成绩

" 许仙 " 来自于 student 表，' 成绩 ' 来自于 score 表

（1）我们先把 score 表和 student 表进行笛卡尔积

（2）指定链接条件此处是按照学生id 进行筛选

（3）根据需求，进一步的添加条件此处按照名字为 “ 许仙 ”再来进行筛选

（4）针对查询的结果的列，进行精简

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql 多表查询，本质上就是把所有的情况都类出来再筛选出合适的，因此，多表查询效率是比较低的

示例：查询所有同学的总成绩及个人信息

每个同学，可以有多门课程，这几门课的成绩，是按照行来组织的

(1) 两个表进行笛卡尔积

(2) 指定链接条件

(3) 这里要求的是每个学生的总成绩，就需要按照学生维度进行 group by

(4) 需要搭配聚合函数，针对分数进行进一步计算

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql 示例：列出同学的名字，课程名字，分数

现在，我们需要通过 student , course , score 三张表进行联合查询

这里 student 和 course 是多对多的关系，score 是它们之间的一个关联表，因此三个表进行笛卡尔积筛选数据，就需要两个链接条件

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

此处进行三张表的笛卡尔积，由于此时的表比较小，所以执行笛卡尔积的过程其实是非常快的，但是此处显示了几秒才结束，这个是控制台打印的缘故

此处能够写出链接条件是因为这两个实体之间存在关联关系，如果是没有关系的两个表，这种条件就写不了

前面多个表，我们使用逗号来分割，现在使用 join 来分割，前面链接条件通过 where 指定，现在使用 on 来指定

示例：查询许仙同学的成绩

（1）直接只写 join 没有 on 则是完整的笛卡尔积

（2）使用 on 表示链接条件

（3）结合需求，加上其它的条件

（4）针对列进行精简

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

(2)外连接

那么内连接和外连接有什么区别呢？

我们现在假设有这么两张表：

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

此时，左侧表的每一条记录，都能在右侧找到对应的，右侧表的记录也能在左侧找到

此时针对这两个表，进行内连接和外连接，结果完全相同

当这两个表里面的数据对不上的时候，内连接就和外连接产生差别了

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

例如这种情况，此时王五同学在 score 表中没有数据， id 为 4的同学，在学生表中，也没有数据

内连接的时候，产生的结果一定是两个表中都存在的数据（公共的部分）

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

外连接，在MySQL 里，有两种情况，一种叫左外连接，一种叫右外连接 left join / right join

左外连接就是以左侧表为主，左侧表的每个记录，都会存在于最终结果里，如果遇到了左侧表中存在，但是右侧表中不存在的数据，此时就会把对应的列填成空值(王五的成绩是空)

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

同理，右外连接就是以右侧的表为主，右侧表的每个记录，都会存在于最终结果里，如果遇到了右侧表中存在，左侧表中不存在的数据，也会把对应的列填成 NULL

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql 全外连接

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

这个操作，MySQL 不支持，但是Oracle 能支持

(3)自链接

自链接本质上是自己和自己做笛卡尔积

本质是把行之间的关系，转换成列之间的关系

SQL 中，编写条件，条件都是列和列之间进行比较，但是SQL无法进行行和行之间的比较

示例：查询所有 “计算机原理” 成绩比“Java”成绩高的成绩信息

（1）算出笛卡尔积

（2）加上链接条件，此处可以使用学生id 作为链接条件，也可以使用课程id

（3）再加上进一步的业务中的条件，让左侧的表只保存 course_id = 3 的记录，同时，让右侧的表保存 course_id = 1的记录

（4）再针对列进行精简

score 表是按照行来组织多个课程的成绩的

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

因此，在这里我们要将行转换成列（把未知问题，转换成已知问题）

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

我们会发现，当直接对score 表及其自己进行笛卡尔积的时候，会进行报错

这是因为，select 的时候，多个表的名字不能相同，因此需要起一个别名，也是使用 as 起别名

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

这个时候，score 就可以进行笛卡尔积了

最后的答案如下：

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

(4)子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

本质上就是把多个SQL合并成一个SQL

示例：查询与 "不想毕业" 同学同班的同学

分成两步完成，是比较推荐的做法

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

子查询就是将两个SQL语句合并在一起

MySQL 数据库【增删查改（二）】,Mysql,数据库,mysql,database,大数据,sql

SQL 支持这么写，但是并不建议，因为可读性低

IN 关键字

-- 使用IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');

-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文');

EXISTS 关键字

-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou 
where (name='语文' or name='英文') and cou.id = sco.course_id);

-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou 
where (name!='语文' and name!='英文') and cou.id = sco.course_id);