夏兮后端 2024-04-11

MySQL 增删改查多表查询和联合查询

AI 概述

一、数据库约束二、表的设计1. 一对一2. 一对多3. 多对多三、查询1.聚合查询2. GROUP BY 子句3. HAVING四、多表查询4.1 内连接4.2 外连接4.3 自连接4.4 子查询4.5 合并查询一、数据库约束 1.NOT NULL – 指示某列不能存储 NULL 值。 2.UNIQUE – 保证某列的每行必须有唯一的值。 3.DEFAULT ...

文章目录隐藏

一、数据库约束
二、表的设计

1. 一对一
2. 一对多
3. 多对多

三、查询

1.聚合查询
2. GROUP BY 子句
3. HAVING

四、多表查询

4.1 内连接
4.2 外连接
4.3 自连接
4.4 子查询
4.5 合并查询

一、数据库约束

1.NOT NULL – 指示某列不能存储 NULL 值。

2.UNIQUE – 保证某列的每行必须有唯一的值。

3.DEFAULT – 规定没有给列赋值时的默认值。

4.PRIMARY KEY – NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

5.FOREIGN KEY – 保证一个表中的数据匹配另一个表中的值的参照完整性。

6.CHECK – 保证列中的值符合指定的条件。对于 MySQL 数据库，对 CHECK 子句进行分析，但是忽略 CHECK 子句。

这些约束条件在数据库中起着非常重要的作用，可以确保数据的完整性和一致性。在设计数据库表结构时，合理地运用这些约束条件可以有效地避免数据错误和不一致性。

案例示范：

 create table student(
    -> id int primary key auto_increment,--主键约束,并且自动加 1
    -> name varchar(20) not null,--不能为空
    -> qq int unique,--值唯一
    -> age int default 18,--赋值默认值
    -> gender varchar(2) check (gender = '男生' or gender = '女生'),--保证性别为男生或女生
    -> classid int,
    -> foreign key (classid) references class(id) -- 外键基于外键 classid 和 class 表的主键 id 在两个表之间建立联系
    -> );

结果:

使用 desc 表名查看表结构

desc student

这时候我们插入表的数据就受到数据库的约束了例如我们可以插入一条不符合数据库的约束的数据查看一下：

insert into student values(1,"张三",1234,null,"未知",2);

例如我们插入一条性别为未知的数据可以看到：

不符合 CHECK 条件的数据无法插入。博主在这里就验证这一条约束。别的就不过多的赘述了。

注意事项：

NOT NULL：
- NOT NULL 约束用于确保指定列不能存储 NULL 值。这意味着，在插入新记录或更新现有记录时，该列必须包含有效数据。
- 注意：在设计表结构时应谨慎使用 NOT NULL 约束，特别是在考虑业务逻辑和未来扩展性时，要确保所有必要的数据都能在任何时候提供。
UNIQUE：
- UNIQUE 约束强制某一列（或多个列组合）中的每行都具有唯一的值，不允许重复。
- 注意：对于多列组成的唯一键，只要这些列的组合不重复即可，单个列可以有重复值。另外，每个表只能有一个主键约束，但可以有多个 UNIQUE 约束。
DEFAULT：
- DEFAULT 约束为指定列定义一个默认值，当插入新记录时如果没有明确为该列赋值，则自动填充这个预设值。
- 注意：默认值的选择应该符合业务逻辑，并且对于可能需要特殊处理的值（如时间戳、序列号等），需要考虑是否需要触发器或者程序逻辑来动态生成更复杂的默认值。
PRIMARY KEY：
- PRIMARY KEY 是一种特殊的约束，它结合了 NOT NULL 和 UNIQUE 的特点，即主键列的值不能为空，并且每一行的主键值在整个表中必须是唯一的。
- 注意：每个表只能有一个主键，它可以是一个单列或多列的组合（复合主键）。主键通常被用作索引，有助于快速定位和查询特定记录。
FOREIGN KEY：(特别关注)
- FOREIGN KEY 约束用于维护两个表之间的引用完整性，确保一个表中的列（外键列）的值必须匹配另一个表（参照表）的主键列的值。
- 注意：在设置外键约束时，要考虑删除规则（ON DELETE CASCADE, SET NULL, NO ACTION 等）和更新规则（ON UPDATE CASCADE 等），以避免违反引用完整性和级联操作带来的影响。
CHECK：
- CHECK 约束理论上允许你限制列的值必须满足特定条件，例如年龄必须在 0 到 120 之间这样的范围检查。
- 注意：在 MySQL 中，默认情况下并不支持标准 SQL 的 CHECK 约束进行行级的数据验证。尽管可以在创建表时编写 CHECK 子句，但 MySQL 会忽略执行这些约束，除非在某些特定存储引擎下或通过触发器模拟实现类似功能。在其他数据库系统如 Oracle、PostgreSQL 中，CHECK 约束会被正常执行并用于保证列值符合特定条件。

总之，在实际应用中合理使用这些约束能够有效地维护数据库的一致性和完整性，但在 MySQL 中尤其要注意其对 CHECK 约束的支持情况，以确保数据验证机制的有效实施。

二、表的设计

1. 一对一

在一对一关系中，一个表中的每一行只与另一个表中的一行相关联，反之亦然。

例如：每个员工（Employee）可能有一个唯一的身份证记录（IDCard）。在这种情况下，可以有两种设计方式：

方式一（共享主键）：

CREATE TABLE Employee (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    -- 其他字段...
);
 
CREATE TABLE IDCard (
    ID INT PRIMARY KEY, -- 使用相同的主键
    CardNumber VARCHAR(20),
    EmployeeDetails VARCHAR(100), -- 其他与身份证相关的字段
    FOREIGN KEY (ID) REFERENCES Employee(ID)
);

这种方式下，Employee 表和 IDCard 表共享同一个主键，即 Employee 的 ID 也是 IDCard 的主键。

方式二（独立主键，但使用唯一外键约束）：

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    -- 其他字段...
);
 
CREATE TABLE IDCard (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    EmployeeID INT UNIQUE,
    CardNumber VARCHAR(20),
    EmployeeDetails VARCHAR(100),
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

在这种设计中，两个表各自有自己的主键，并且通过 EmployeeID 字段建立外键约束，确保 IDCard 表中每张身份证只对应一个 Employee。

2. 一对多

在一对多关系中，一个表的一行可以与另一个表的多行关联，但反过来不行。

例如：一个部门（Department）可以有多个员工（Employee），但每个员工只能属于一个部门。

CREATE TABLE Department (
    DeptID INT PRIMARY KEY AUTO_INCREMENT,
    DeptName VARCHAR(50)
);
 
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

在这个例子中，Department 表的每一个 DeptID 在 Employee 表中作为外键出现多次，表明了一个部门可以拥有多个员工。

3. 多对多

在多对多关系中，一个表的每一行都可以与另一个表的多行关联，反之亦然。

例如：一个老师（Teacher）可以教多个学生（Student），而一个学生也可以被多个老师教授。

CREATE TABLE Teacher (
    TeacherID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50)
);
 
CREATE TABLE Student (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50)
);
 
CREATE TABLE TeacherStudent (
    TeacherID INT,
    StudentID INT,
    PRIMARY KEY (TeacherID, StudentID),
    FOREIGN KEY (TeacherID) REFERENCES Teacher(TeacherID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

在这种情况下，需要创建一个中间表（TeacherStudent），它包含两个外键分别引用 Teacher 和 Student 表的主键，以此来存储这种多对多的关系信息。

三、查询

1.聚合查询

函数	说明
count(数据)	返回查询到的数据的数量
sum(数据)	返回查询到的数据的总和，不是数字没有意义
avg(数据)	返回查询到的数据的平均值，不是数字没有意义
max(数据)	返回查询到的数据的最大值，不是数字没有意义
min(数据)	返回查询到的数据的最小值，不是数字没有意义

语法：

select 聚合函数(数据) from + 表名 + where 条件

2. GROUP BY 子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在 SELECT 中则必须包含在聚合函数中。

语法：

select 列名 1,列名 2....from 表名 group by 列名 1....

案例：

create table emp(
 id int primary key auto_increment,
 name varchar(20) not null,
 role varchar(20) not null,
 salary numeric(11,2)
);
insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);

查询每个角色的最高工资、最低工资和平均工资：

select name,role,max(salary),min(salary),avg(salary) from emp group by role;

结果：

3. HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING。

案例:

显示平均工资低于 1500 的角色和它的平均工资：

select name,role,avg(salary) from emp group by role having avg(salary) < 1500;

四、多表查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积。

准备实验数据

创建班级表：

create table class(id int primary key auto_increment,name varchar(20));

创建学生表：

 create table student(
     id int primary key auto_increment,
     sn int unique,
     name varchar(20) default "unkown",
     qq_mail varchar(20),
     class_id int,
     foreign key(class_id) references class(id));

创建课程表：

create table course(id int primary key auto_increment,name varchar(20));

创建学生课程中间表，考试成绩表：

 create table score(
     id int primary key auto_increment,
     score decimal(3,1),
     student_id int,
     course_id int,
     foreign key(student_id) references student(id),
     foreign key(course_id) references course(id)
     );

插入数据：

insert into class(name)values("计科"),("软工"),("网络");
 insert into student(sn, name, qq_mail, class_id) values
     ('09982','黑旋风李逵','xuanfeng@qq.com',1),
     ('00835','菩提老祖',null,1),
     ('00391','白素贞',null,1),
     ('00031','许仙','xuxian@qq.com',1),
     ('00054','不想毕业',null,1),
     ('51234','好好说话','say@qq.com',2),
     ('83223','tellme',null,2),
     ('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
 insert into score(score, student_id, course_id) values
   -- 黑旋风李逵
   (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
   -- 菩提老祖
   (60, 2, 1),(59.5, 2, 5),
   -- 白素贞
   (33, 3, 1),(68, 3, 3),(99, 3, 5),
   -- 许仙
   (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
   -- 不想毕业
   (81, 5, 1),(37, 5, 5),
    -- 好好说话
   (56, 6, 2),(43, 6, 4),(79, 6, 6),
    -- tellme
   (80, 7, 2),(92, 7, 6);

4.1 内连接

语法:

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

案例：

（1）查询“许仙”同学的成绩

select student.name,score.score from student,score where student.id = score.student_id and student.name = "许仙";

select student.name,score.score from student join score on student.id = score.student_id and student.name = "许仙";

（2）查询所有同学的总成绩，及同学的个人信息：

select student.sn,student.name,student.qq_mail,sum(score) as total
    from student join score
    on student.id = score.student_id
    group by score.student_id;

总结来说:内连接我们一开始不熟练可以以下几步来做：

1.先确定我们要查询的信息,来自那几张表；

2.针对这几张表进行笛卡尔积；

3.加上连接条件,去除无效数据；

4.在根据题目要求,加上其他条件；

5.继续根据题目要求看需要查询的是那些列,把不需要的去除即可,例如案例一,我们只需要知道许仙的名字和成绩, select student.name,score.score 这样即可。

4.2 外连接

语法：

-- 左外连接，表 1 完全显示
select 字段名  from 表名 1 left join 表名 2 on 连接条件;
-- 右外连接，表 2 完全显示
select 字段 from 表名 1 right join 表名 2 on 连接条件;

案例：

查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示。

select * from student left join score on student.id = score.student_id;

4.3 自连接

自连接是指在同一张表连接自身进行查询。也就是自己对自己进行笛卡尔积，属于是对待特殊问题的特殊技巧。

案例：

显示所有“计算机原理”成绩比“Java”成绩高的成绩信息。

 SELECT
     s1.*
     FROM
      score s1
     JOIN score s2 ON s1.student_id = s2.student_id
     AND s1.score < s2.score
     AND s1.course_id = 1 --java 的课程号
     AND s2.course_id = 3;--计算机原理的课程号

这个 SQL 查询从“score”表中选择所有记录，其中学生在 Java 课程（course_id = 1）中的分数高于计算机原理课程（course_id = 3）中的分数。该查询在“score”表上使用自连接来比较同一学生在两门课程中的分数。

自连接是指在同一个表中进行连接操作。在这种情况下，我们使用表的别名来区分不同的实例。以下是自连接的步骤：

为表分配别名：在查询中为同一张表分配不同的别名，以便区分它们。在这个例子中，我们使用了 s1 和 s2 作为 score 表的别名。
指定连接条件：在 JOIN 子句中指定连接条件，以便确定两个表之间的关联。在这个例子中，连接条件是 s1.student_id = s2.student_id，表示两个实例具有相同的学生 ID。
添加过滤条件：在 ON 子句中添加额外的条件来过滤出符合特定条件的记录。在这个例子中，过滤条件是 s1.score < s2.score，s1.course_id = 1 和 s2.course_id = 3。
选择需要的列：在 SELECT 子句中指定要检索的列。在这个例子中，我们选择了 s1.*，表示选择 s1 表中的所有列。
执行查询：执行 SQL 查询以获取符合条件的结果集。

4.4 子查询

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

1. 单行子查询：返回一行记录的子查询

案例：查询与“不想毕业” 同学的同班同学

 select * from student where class_id=(select class_id from student where
     name='不想毕业');

2. 多行子查询：返回多行记录的子查询

案例：查询“语文”或“英文”课程的成绩信息

(not)in关键字

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

(not)exists关键字

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

4.5 合并查询

在实际应用中，为了合并多个 select 的执行结果，可以使用集合操作符 union，union all。使用 UNION 和 UNION ALL 时，前后查询的结果集中，字段需要一致。

1. union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

案例：查询 id 小于 3，或者名字为“英文”的课程：

select * from course where id<3
union
select * from course where name='英文';
-- 或者使用 or 来实现
select * from course where id<3 or name='英文';

2.union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

案例：查询 id 小于 3，或者名字为“Java”的课程

-- 可以看到结果集中出现重复数据 Java
select * from course where id<3
union all
select * from course where name='Java';

以上就是关于 MySQL 的多表查询和联合查询的全部类型了,希望对大家有帮助。

以上关于MySQL 增删改查多表查询和联合查询的文章就介绍到这了，更多相关内容请搜索码云笔记以前的文章或继续浏览下面的相关文章，希望大家以后多多支持码云笔记。

「点点赞赏，手留余香」

赞 0 赏

给作者打赏，鼓励TA抓紧创作！

微信

支付宝

还没有人赞赏，快来当第一个赞赏的人吧！

MySQL 后端数据库

声明：本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权/违法违规/事实不符，请将相关资料发送至 admin@mybj123.com 进行投诉反馈，一经查实，立即处理！
重要：如软件存在付费、会员、充值等，均属软件开发者或所属公司行为，与本站无关，网友需自行判断
码云笔记 » MySQL 增删改查多表查询和联合查询