MySQL约束与多表查询实例分析

本篇内容主要讲解“MySQL约束与多表查询实例分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL约束与多表查询实例分析”吧!概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。目的：保证数据库中数据的正确、有效性和完整性。注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。上面我们介绍了数据库中常见的约束，以及约束涉及到的关键字，那这些约束我们到底如何在创建表、修改表的时候来指定呢，接下来我们就通过一个案例，来演示一下。案例需求： 根据需求，完成表结构的创建。需求如下：对应的建表语句为：如果你的Mysql是8版本之后 age可以这样创建在为字段添加约束时，我们只需要在字段之后加上约束的关键字即可，需要关注其语法。我们执行上面的SQL把表结构创建完成，然后接下来，就可以通过一组数据进行测试，从而验证一下，约束是否可以生效。可见 对于我们设置name的约束： 非空 且唯一 生效。上面，我们是通过编写SQL语句的形式来完成约束的指定，那加入我们是通过图形化界面来创建表结构时，又该如何来指定约束呢？ 只需要在创建表的时候，根据我们的需要选择对应的约束即可。外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。我们来看一个例子：左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID、姓名、年龄、职位、薪资、入职日 期、上级主管ID云主机域名免费试用、部门ID，在员工的信息中存储的是部门的ID dept_id，而这个部门的ID是关联的部门表dept的主键id，那emp表的dept_id就是外键,关联的是另一张表的主键。注意：目前上述两张表，只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。没有数据库外键关联的情况下，能够保证一致性和完整性呢，我们来测试一下。准备数据接下来，我们可以做一个测试，删除id为1的部门信息。结果，我们看到删除成功，而删除成功之后，部门表不存在id为1的部门，而在emp表中还有很多的员工，关联的为id为1的部门，此时就出现了数据的不完整性。 而要想解决这个问题就得通过数据库的外键约束。1). 添加外键案例:为emp表的dept_id字段添加外键约束,关联dept表的主键id。添加了外键约束之后，我们再到dept表(父表)删除id为1的记录，然后看一下会发生什么现象。 此时将会报错，不能删除或更新父表记录，因为存在外键约束。2). 删除外键案例：删除emp表的外键fk_emp_dept_id添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种:具体语法为:演示如下：由于NO ACTION 是默认行为，我们前面语法演示的时候，已经测试过了，就不再演示了，这里我们再演示其他的两种行为：CASCADE、SET NULL。1). CASCADEA. 修改父表id为1的记录，将id修改为6我们发现，原来在子表中dept_id值为1的记录，现在也变为6了，这就是cascade级联的效果在一般的业务系统中，不会修改一张表的主键值。B. 删除父表id为6的记录我们发现，父表的数据删除成功了，但是子表中关联的记录也被级联删除了。2). SET NULL 在进行测试之前，我们先需要删除上面建立的外键 fk_emp_dept_id。然后再通过数据脚本，将 emp、dept表的数据恢复了。接下来，我们删除id为1的数据，看看会发生什么样的现象。我们发现父表的记录是可以正常的删除的，父表的数据删除之后，再打开子表 emp，我们发现子表emp 的dept_id字段，原来dept_id为1的数据，现在都被置为NULL了这就是SET NULL这种删除/更新行为的效果。我们之前在讲解SQL语句的时候，讲解了DQL语句，也就是数据查询语句，但是之前讲解的查询都是单表查询，而本章节我们要学习的则是多表查询操作，主要从以下几个方面进行讲解。项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结 构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：一对多(多对一) 多对多 一对一 案例: 部门 与 员工的关系关系: 一个部门对应多个员工，一个员工对应一个部门实现: 在多的一方建立外键，指向一的一方的主键案例: 学生 与 课程的关系关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键对应的SQL脚本:案例: 用户与 用户详情的关系关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)对应的SQL脚本:1). 删除之前 emp, dept表的测试数据2). 执行如下脚本，创建emp表与dept表并插入测试数据dept表共6条记录，emp表共17条记录。多表查询就是指从多张表中查询数据。原来查询单表数据，执行的SQL形式为：select * from emp;那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept ; 具体的执行结果如下:此时,我们看到查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp所有的记录 (17) 与 部门表dept所有记录(6) 的所有组合情况，这种现象称之为笛卡尔积。接下来，就来简单 介绍下笛卡尔积。笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可。而由于id为17的员工，没有dept_id字段值，所以在多表查询时，根据连接查询的条件并没有查询到。连接查询 内连接：相当于查询A、B交集部分数据外连接：左外连接：查询左表所有数据，以及两张表交集部分数据右外连接：查询右表所有数据，以及两张表交集部分数据自连接：当前表与自身的连接查询，自连接必须使用表别名子查询 内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据)内连接的语法分为两种: 隐式内连接、显式内连接。先来学习一下具体的语法结构。 1). 隐式内连接2). 显式内连接案例:A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)表结构: emp , dept连接条件: emp.dept_id = dept.idB. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) — INNER JOIN …ON …表结构: emp , dept连接条件: emp.dept_id = dept.id表的别名:①. tablea as 别名1 , tableb as 别名2 ;②. tablea 别名1 , tableb 别名2 ;注意事项:一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：1). 左外连接 左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。2). 右外连接右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。案例:A. 查询emp表的所有数据, 和对应的部门信息由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。表结构: emp, dept连接条件: emp.dept_id = dept.idB. 查询dept表的所有数据, 和对应的员工信息(右外连接)由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。表结构: emp, dept连接条件: emp.dept_id = dept.id注意事项：左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法：而对于自连接查询，可以是内连接查询，也可以是外连接查询案例：A. 查询员工 及其 所属领导的名字表结构: empB. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来表结构: emp a , emp b注意事项: 在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底 是哪一张表的字段。 对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。案例:A. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。 那这里呢，我们 也可以通过union/union all来联合查询.union all查询出来的结果，仅仅进行简单的合并，并未去重。union 联合查询，会对查询出来的结果进行去重处理。注意： 如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报错。如：1). 概念SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。2). 分类根据子查询结果不同，分为：A. 标量子查询（子查询结果为单个值）B. 列子查询(子查询结果为一列)C. 行子查询(子查询结果为一行)D. 表子查询(子查询结果为多行多列)根据子查询位置，分为：A. WHERE之后B. FROM之后C. SELECT之后子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。常用的操作符：= > >=
案例:A. 查询 “销售部” 的所有员工信息完成这个需求时，我们可以将需求分解为两步：查询 “销售部” 部门ID根据 “销售部” 部门ID, 查询员工信息B. 查询在 “方东白” 入职之后的员工信息完成这个需求时，我们可以将需求分解为两步：查询 方东白 的入职日期查询指定入职日期之后入职的员工信息子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL案例:A. 查询 “销售部” 和 “市场部” 的所有员工信息分解为以下两步:查询 “销售部” 和 “市场部” 的部门ID根据部门ID, 查询员工信息B. 查询比 财务部 所有人工资都高的员工信息分解为以下两步:查询所有 财务部 人员工资比 财务部 所有人工资都高的员工信息
C. 查询比研发部其中任意一人工资高的员工信息分解为以下两步:查询研发部所有人工资比研部其中任意一人工资高的员工信息子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。常用的操作符：= 、 、IN 、NOT IN案例:A. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;这个需求同样可以拆解为两步进行:查询 “张无忌” 的薪资及直属领导查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;子查询返回的结果是多行多列，这种子查询称为表子查询。常用的操作符：IN案例:A. 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息分解为两步执行:查询 “鹿杖客” , “宋远桥” 的职位和薪资查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息B. 查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息分解为两步执行:入职日期是 “2006-01-01” 之后的员工信息.查询这部分员工, 对应的部门信息;到此，相信大家对“MySQL约束与多表查询实例分析”有了更深的了解，不妨来实际操作一番吧！这里是开发云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

相关推荐: mysql触发器的概念是什么

本篇内容介绍了“mysql触发器的概念是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 在mysql中，触发器是存储在数据库目录中的一组SQL语句，每当与表…