ホームページ  >  記事  >  データベース  >  MySQL の制約と複数テーブルのクエリ例の分析

MySQL の制約と複数テーブルのクエリ例の分析

WBOY
WBOY転載
2023-06-03 15:10:56654ブラウズ


MySQL の制約と複数テーブルのクエリ例の分析

#1. 制約

概要

概念: 制約は、テーブル内のフィールドに作用して、テーブルに格納されるデータを制限するルールです。

目的: データベース内のデータの正確性、有効性、整合性を確保すること。

MySQL の制約と複数テーブルのクエリ例の分析

注: 制約はテーブル内のフィールドに適用されます。制約はテーブルの作成/変更時に追加できます。

制約のデモ

データベースの一般的な制約とその制約に関連するキーワードを紹介しました。では、これらの制約を持つテーブルをどのように作成すればよいでしょうか? ,

テーブルを変更する際に指定します。次にケースで説明します。

ケース要件: 要件に従ってテーブル構造の作成を完了します。要件は次のとおりです:

MySQL の制約と複数テーブルのクエリ例の分析

対応するテーブル作成ステートメントは次のとおりです:

CREATE TABLE tb_user
(
    id     int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
    name   varchar(10) NOT NULL UNIQUE COMMENT '姓名',
    age     tinyint unsigned  COMMENT '年龄',
    status char(1) default '1' COMMENT '状态',
    gender char(1) COMMENT '性别'
);

Mysql のバージョンが 8 以降の場合、age は次のように作成できます。

age int check (age > 0 && age <= 120) COMMENT &#39;年龄&#39; ,

フィールドに制約を追加するときは、フィールドの後に制約のキーワードを追加するだけでよく、その構文に注意する必要があります。上記の SQL を実行してテーブル構造を作成し、一連のデータを使用してそれをテストして、制約が有効かどうかを確認します。

insert into tb_user(name, age, status, gender)
values (&#39;Tom1&#39;, 19, &#39;1&#39;, &#39;男&#39;),
       (&#39;Tom2&#39;, 25, &#39;0&#39;, &#39;男&#39;);
insert into tb_user(name, age, status, gender)
values (&#39;Tom3&#39;, 19, &#39;1&#39;, &#39;男&#39;);
insert into tb_user(name, age, status, gender)
values (null, 19, &#39;1&#39;, &#39;男&#39;);
insert into tb_user(name, age, status, gender)
values (&#39;Tom3&#39;, 19, &#39;1&#39;, &#39;男&#39;);
insert into tb_user(name, age, status, gender)
values (&#39;Tom4&#39;, 80, &#39;1&#39;, &#39;男&#39;);
insert into tb_user(name, age, status, gender)
values (&#39;Tom5&#39;, -1, &#39;1&#39;, &#39;男&#39;);
insert into tb_user(name, age, status, gender)
values (&#39;Tom5&#39;, 121, &#39;1&#39;, &#39;男&#39;);
insert into tb_user(name, age, gender)
values (&#39;Tom5&#39;, 120, &#39;男&#39;);

MySQL の制約と複数テーブルのクエリ例の分析

MySQL の制約と複数テーブルのクエリ例の分析

Visible 設定した名前の制約 (空でない、一意である) が有効になります。

上記で、SQL ステートメントを記述する形式で制約の指定が完了しましたが、グラフィカル インターフェイスを使用してテーブル構造を作成する場合、どのように制約を指定すればよいでしょうか。テーブルを作成するときに、必要に応じて対応する制約を選択するだけです。

MySQL の制約と複数テーブルのクエリ例の分析

外部キー制約

はじめに

外部キー: 2 つのキーを作成するために使用されます。テーブル内のデータ間の接続を確立して、データの一貫性と整合性を確保します。

例を見てみましょう:

MySQL の制約と複数テーブルのクエリ例の分析

左側の emp テーブルは従業員テーブルで、従業員 ID などの従業員に関する基本情報が保存されます。 、名前、年齢、役職、給与、入社日、上司上司ID、部門ID、社員情報に格納されるのは部門ID dept_id であり、この部門のIDが関連付けられている部門テーブルdeptの主キーidとなります。 emp テーブルの dept_id は次のとおりです。 外部キーは別のテーブルの主キーに関連付けられています。

注: 現時点では、上記の 2 つのテーブルにはそのような論理関係があるだけであり、データベース レベルでは外部キーの関連付けが確立されていないため、データの一貫性と完全性は保証できません。 。

データベース外部キーの関連付けがない場合は、一貫性と完全性が保証されるので、テストしてみましょう。

データの準備

create table dept
(
    id   int auto_increment comment &#39;ID&#39; primary key,
    name varchar(50) not null comment &#39;部门名称&#39;
) comment &#39;部门表&#39;;
INSERT INTO dept (id, name)
VALUES (1, &#39;研发部&#39;),
       (2, &#39;市场部&#39;),
       (3, &#39;财务部&#39;),
       (4, &#39;销售部&#39;),
       (5, &#39;总经办&#39;);
create table emp
(
    id        int auto_increment comment &#39;ID&#39; primary key,
    name      varchar(50) not null comment &#39;姓名&#39;,
    age       int comment &#39;年龄&#39;,
    job       varchar(20) comment &#39;职位&#39;,
    salary    int comment &#39;薪资&#39;,
    entrydate date comment &#39;入职时间&#39;,
    managerid int comment &#39;直属领导ID&#39;,
    dept_id   int comment &#39;部门ID&#39;
) comment &#39;员工表&#39;;
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, &#39;金庸&#39;, 66, &#39;总裁&#39;, 20000, &#39;2000-01-01&#39;, null, 5),
       (2, &#39;张无忌&#39;, 20, &#39;项目经理&#39;, 12500, &#39;2005-12-05&#39;, 1, 1),
       (3, &#39;杨逍&#39;, 33, &#39;开发&#39;, 8400, &#39;2000-11-03&#39;, 2, 1),
       (4, &#39;韦一笑&#39;, 48, &#39;开 发&#39;, 11000, &#39;2002-02-05&#39;, 2, 1),
       (5, &#39;常遇春&#39;, 43, &#39;开发&#39;, 10500, &#39;2004-09-07&#39;, 3, 1),
       (6, &#39;小昭&#39;, 19, &#39;程 序员鼓励师&#39;, 6600, &#39;2004-10-12&#39;, 2, 1);

MySQL の制約と複数テーブルのクエリ例の分析

MySQL の制約と複数テーブルのクエリ例の分析

次に、ID 1 の部門情報を削除するテストを実行できます。

MySQL の制約と複数テーブルのクエリ例の分析

結果、削除が成功したことがわかります。削除が成功すると、部門テーブルには ID 1 の部門は存在せず、従業員が多数存在します。 emp テーブルの . は ID 1 の部門であり、現時点ではデータが不完全です。この問題を解決するには、データベースの外部キー制約を使用する必要があります。

構文

##1). 外部キーを追加します

CREATE TABLE 表名
(
    字段名 数据类型, ... [
    CONSTRAINT] [
    外键名称]
    FOREIGN
    KEY
(
    外键字段名
) REFERENCES 主表
(
    主表列名
) );
ALTER TABLE 表名
    ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);
Case:

は emp テーブルです 追加dept_id フィールドに外部キー制約を追加し、それを dept テーブルの主キー ID に関連付けます。

alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);

MySQL の制約と複数テーブルのクエリ例の分析外部キー制約を追加した後、dept テーブル (親テーブル) に移動して ID 1 のレコードを削除し、何が起こるかを確認します。現時点では、

はエラーを報告し、外部キー制約があるため、親テーブルのレコードを削除または更新できません。

MySQL の制約と複数テーブルのクエリ例の分析

#2). 外部キーを削除します

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
ケース:

外部キー f​​k_emp_dept_id# を削除しますemp テーブルの ##

alter table emp drop foreign key fk_emp_dept_id; 1

删除/更新行为

当添加了外键之后,在删除父表数据时引发的约束操作,我们称之为删除或更新操作。具体的删除/更新行为有以下几种:

MySQL の制約と複数テーブルのクエリ例の分析

具体语法为:

ALTER TABLE 表名
    ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

演示如下:

由于NO ACTION 是默认行为,我们前面语法演示的时候,已经测试过了,就不再演示了,这里我们再

演示其他的两种行为:CASCADE、SET NULL。

1). CASCADE

alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references dept (id) on update cascade on delete cascade;

A. 修改父表id为1的记录,将id修改为6

MySQL の制約と複数テーブルのクエリ例の分析

我们发现,原来在子表中dept_id值为1的记录,现在也变为6了,这就是cascade级联的效果

在一般的业务系统中,不会修改一张表的主键值。

B. 删除父表id为6的记录

MySQL の制約と複数テーブルのクエリ例の分析

我们发现,父表的数据删除成功了,但是子表中关联的记录也被级联删除了。

2). SET NULL

在进行测试之前,我们先需要删除上面建立的外键 fk_emp_dept_id。然后再通过数据脚本,将 emp、dept表的数据恢复了。

alter table emp
    add constraint fk_emp_dept_id
        foreign key (dept_id) references dept (id) on update set null on delete set null;

接下来,我们删除id为1的数据,看看会发生什么样的现象。

MySQL の制約と複数テーブルのクエリ例の分析

我们发现父表的记录是可以正常的删除的,父表的数据删除之后,再打开子表 emp,我们发现子表emp 的dept_id字段,原来dept_id为1的数据,现在都被置为NULL了

MySQL の制約と複数テーブルのクエリ例の分析

这就是SET NULL这种删除/更新行为的效果。

2.多表查询

我们之前在讲解SQL语句的时候,讲解了DQL语句,也就是数据查询语句,但是之前讲解的查询都是单表查询,而本章节我们要学习的则是多表查询操作,主要从以下几个方面进行讲解。

多表关系

项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结 构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:

一对多(多对一)     多对多      一对一

1.一对多

案例: 部门 与 员工的关系

关系: 一个部门对应多个员工,一个员工对应一个部门

实现: 在多的一方建立外键,指向一的一方的主键

MySQL の制約と複数テーブルのクエリ例の分析

2.多对多

案例: 学生 与 课程的关系

关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择

实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

MySQL の制約と複数テーブルのクエリ例の分析

对应的SQL脚本:

create table student
(
    id   int auto_increment primary key comment &#39;主键ID&#39;,
    name varchar(10) comment &#39;姓名&#39;,
    no   varchar(10) comment &#39;学号&#39;
) comment &#39;学生表&#39;;
insert into student
values (null, &#39;黛绮丝&#39;, &#39;2000100101&#39;),
       (null, &#39;谢逊&#39;, &#39;2000100102&#39;),
       (null, &#39;殷天正&#39;, &#39;2000100103&#39;),
       (null, &#39;韦一笑&#39;, &#39;2000100104&#39;);
create table course
(
    id   int auto_increment primary key comment &#39;主键ID&#39;,
    name varchar(10) comment &#39;课程名称&#39;
) comment &#39;课程表&#39;;
insert into course
values (null, &#39;Java&#39;),
       (null, &#39;PHP&#39;),
       (null, &#39;MySQL&#39;),
       (null, &#39;Hadoop&#39;);
create table student_course
(
    id        int auto_increment comment &#39;主键&#39; primary key,
    studentid int not null comment &#39;学生ID&#39;,
    courseid  int not null comment &#39;课程ID&#39;,
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment &#39;学生课程中间表&#39;;
insert into student_course
values (null, 1, 1),
       (null, 1, 2),
       (null, 1, 3),
       (null, 2, 2),
       (null, 2, 3),
       (null, 3, 4);

3.一对一

案例: 用户与 用户详情的关系

关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率

实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)

MySQL の制約と複数テーブルのクエリ例の分析

对应的SQL脚本:

create table tb_user
(
    id     int auto_increment primary key comment &#39;主键ID&#39;,
    name   varchar(10) comment &#39;姓名&#39;,
    age    int comment &#39;年龄&#39;,
    gender char(1) comment &#39;1: 男 , 2: 女&#39;,
    phone  char(11) comment &#39;手机号&#39;
) comment &#39;用户基本信息表&#39;;
create table tb_user_edu
(
    id            int auto_increment primary key comment &#39;主键ID&#39;,
    degree        varchar(20) comment &#39;学历&#39;,
    major         varchar(50) comment &#39;专业&#39;,
    primaryschool varchar(50) comment &#39;小学&#39;,
    middleschool  varchar(50) comment &#39;中学&#39;,
    university    varchar(50) comment &#39;大学&#39;,
    userid        int unique comment &#39;用户ID&#39;,
    constraint fk_userid foreign key (userid) references tb_user (id)
) comment &#39;用户教育信息表&#39;;
insert into tb_user(id, name, age, gender, phone)
values (null, &#39;黄渤&#39;, 45, &#39;1&#39;, &#39;18800001111&#39;),
       (null, &#39;冰冰&#39;, 35, &#39;2&#39;, &#39;18800002222&#39;),
       (null, &#39;码云&#39;, 55, &#39;1&#39;, &#39;18800008888&#39;),
       (null, &#39;李彦宏&#39;, 50, &#39;1&#39;, &#39;18800009999&#39;);
insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid)
values (null, &#39;本科&#39;, &#39;舞蹈&#39;, &#39;静安区第一小学&#39;, &#39;静安区第一中学&#39;, &#39;北京舞蹈学院&#39;, 1),
       (null, &#39;硕士&#39;, &#39;表演&#39;, &#39;朝阳区第一小学&#39;, &#39;朝阳区第一中学&#39;, &#39;北京电影学院&#39;, 2),
       (null, &#39;本科&#39;, &#39;英语&#39;, &#39;杭州市第一小学&#39;, &#39;杭州市第一中学&#39;, &#39;杭州师范大学&#39;, 3),
       (null, &#39;本科&#39;, &#39;应用数学&#39;, &#39;阳泉第一小学&#39;, &#39;阳泉区第一中学&#39;, &#39;清华大学&#39;, 4);

3.多表查询概述

1.数据准备

1). 删除之前 emp, dept表的测试数据

2). 执行如下脚本,创建emp表与dept表并插入测试数据

-- 创建dept表,并插入数据
create table dept
(
    id   int auto_increment comment &#39;ID&#39; primary key,
    name varchar(50) not null comment &#39;部门名称&#39;
) comment &#39;部门表&#39;;
INSERT INTO dept (id, name)
VALUES (1, &#39;研发部&#39;),
       (2, &#39;市场部&#39;),
       (3, &#39;财务部&#39;),
       (4, &#39;销售部&#39;),
       (5, &#39;总经办&#39;),
       (6, &#39;人事部&#39;);
-- 创建emp表,并插入数据
create table emp
(
    id        int auto_increment comment &#39;ID&#39; primary key,
    name      varchar(50) not null comment &#39;姓名&#39;,
    age       int comment &#39;年龄&#39;,
    job       varchar(20) comment &#39;职位&#39;,
    salary    int comment &#39;薪资&#39;,
    entrydate date comment &#39;入职时间&#39;,
    managerid int comment &#39;直属领导ID&#39;,
    dept_id   int comment &#39;部门ID&#39;
) comment &#39;员工表&#39;;
-- 添加外键
alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, &#39;金庸&#39;, 66, &#39;总裁&#39;, 20000, &#39;2000-01-01&#39;, null, 5),
       (2, &#39;张无忌&#39;, 20, &#39;项目经理&#39;, 12500, &#39;2005-12-05&#39;, 1, 1),
       (3, &#39;杨逍&#39;, 33, &#39;开发&#39;, 8400, &#39;2000-11-03&#39;, 2, 1),
       (4, &#39;韦一笑&#39;, 48, &#39;开发&#39;, 11000, &#39;2002-02-05&#39;, 2, 1),
       (5, &#39;常遇春&#39;, 43, &#39;开发&#39;, 10500, &#39;2004-09-07&#39;, 3, 1),
       (6, &#39;小昭&#39;, 19, &#39;程序员鼓励师&#39;, 6600, &#39;2004-10-12&#39;, 2, 1),
       (7, &#39;灭绝&#39;, 60, &#39;财务总监&#39;, 8500, &#39;2002-09-12&#39;, 1, 3),
       (8, &#39;周芷若&#39;, 19, &#39;会计&#39;, 48000, &#39;2006-06-02&#39;, 7, 3),
       (9, &#39;丁敏君&#39;, 23, &#39;出纳&#39;, 5250, &#39;2009-05-13&#39;, 7, 3),
       (10, &#39;赵敏&#39;, 20, &#39;市场部总监&#39;, 12500, &#39;2004-10-12&#39;, 1, 2),
       (11, &#39;鹿杖客&#39;, 56, &#39;职员&#39;, 3750, &#39;2006-10-03&#39;, 10, 2),
       (12, &#39;鹤笔翁&#39;, 19, &#39;职员&#39;, 3750, &#39;2007-05-09&#39;, 10, 2),
       (13, &#39;方东白&#39;, 19, &#39;职员&#39;, 5500, &#39;2009-02-12&#39;, 10, 2),
       (14, &#39;张三丰&#39;, 88, &#39;销售总监&#39;, 14000, &#39;2004-10-12&#39;, 1, 4),
       (15, &#39;俞莲舟&#39;, 38, &#39;销售&#39;, 4600, &#39;2004-10-12&#39;, 14, 4),
       (16, &#39;宋远桥&#39;, 40, &#39;销售&#39;, 4600, &#39;2004-10-12&#39;, 14, 4),
       (17, &#39;陈友谅&#39;, 42, null, 2000, &#39;2011-10-12&#39;, 1, null)

dept表共6条记录,emp表共17条记录。

2.概述

多表查询就是指从多张表中查询数据。

原来查询单表数据,执行的SQL形式为:select * from emp;

那么我们要执行多表查询,就只需要使用逗号分隔多张表即可,如: select * from emp , dept ; 具体的执行结果如下:

MySQL の制約と複数テーブルのクエリ例の分析

此时,我们看到查询结果中包含了大量的结果集,总共102条记录,而这其实就是员工表emp所有的记录 (17) 与 部门表dept所有记录(6) 的所有组合情况,这种现象称之为笛卡尔积。接下来,就来简单 介绍下笛卡尔积。

笛卡尔积: 笛卡尔乘积是指在数学中,两个集合A集合 和 B集合的所有组合情况。

MySQL の制約と複数テーブルのクエリ例の分析

而在多表查询中,我们是需要消除无效的笛卡尔积的,只保留两张表关联部分的数据。

MySQL の制約と複数テーブルのクエリ例の分析

MySQL の制約と複数テーブルのクエリ例の分析

在SQL语句中,如何来去除无效的笛卡尔积呢? 我们可以给多表查询加上连接查询的条件即可。

 select * from emp , dept where emp.dept_id = dept.id;

MySQL の制約と複数テーブルのクエリ例の分析

在多表查询中,由于id为17的员工没有dept_id字段的值,因此根据连接查询条件无法找到。

4.分类

连接查询

内连接:相当于查询A、B交集部分数据

外连接

左外连接:查询左表所有数据,以及两张表交集部分数据

右外连接:查询右表所有数据,以及两张表交集部分数据

自连接:当前表与自身的连接查询,自连接必须使用表别名

子查询

1.内连接

内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据)

MySQL の制約と複数テーブルのクエリ例の分析

内连接的语法分为两种: 隐式内连接、显式内连接。先来学习一下具体的语法结构。

1). 隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

2). 显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

案例:

A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)

表结构: emp , dept

连接条件: emp.dept_id = dept.id

select emp.name, dept.name
from emp,
     dept
where emp.dept_id = dept.id; 
-- 为每一张表起别名,简化SQL编写
 select e.name,d.name from emp e , dept d where e.dept_id = d.id;

B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ...

ON ...

表结构: emp , dept

连接条件: emp.dept_id = dept.id

select e.name, d.name
from emp e
         inner join dept d on e.dept_id = d.id;
-- 为每一张表起别名,简化SQL编写
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

表的别名:

①. tablea as 别名1 , tableb as 别名2 ;

②. tablea 别名1 , tableb 别名2 ;

注意事项:一旦为表起了别名,就不能再使用表名来指定对应的字段了,此时只能够使用别名来指定字段。

2.外连接

MySQL の制約と複数テーブルのクエリ例の分析

外连接分为两种,分别是:左外连接 和 右外连接。具体的语法结构为:

1). 左外连接  

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。

2). 右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。

案例:

A. 查询emp表的所有数据, 和对应的部门信息

由于需求中提到,要查询emp的所有数据,所以是不能内连接查询的,需要考虑使用外连接查询。

表结构: emp, dept

连接条件: emp.dept_id = dept.id

select e.*, d.name
from emp e
         left outer join dept d on e.dept_id = d.id;
select e.*, d.name
from emp e
         left join dept d on e.dept_id = d.id;

MySQL の制約と複数テーブルのクエリ例の分析

B. 查询dept表的所有数据, 和对应的员工信息(右外连接)

由于需求中提到,要查询dept表的所有数据,所以是不能内连接查询的,需要考虑使用外连接查询。

表结构: emp, dept

连接条件: emp.dept_id = dept.id

select d.*, e.*
from emp e
         right outer join dept d on e.dept_id = d.id;
select d.*, e.*
from dept d
         left outer join emp e on e.dept_id = d.id;

MySQL の制約と複数テーブルのクエリ例の分析

注意事项:

左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺

序就可以了。而我们在日常开发使用时,更偏向于左外连接。

3.自连接

1.自连接查询

自我关联查询是指在查询中将一个表自己连接,即多次连接同一张表。我们先来学习一下自连接的查询语法:

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

而对于自连接查询,可以是内连接查询,也可以是外连接查询

案例:

A. 查询员工 及其 所属领导的名字

表结构: emp

select a.name , b.name from emp a , emp b where a.managerid = b.id;

MySQL の制約と複数テーブルのクエリ例の分析

B. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

表结构: emp a , emp b

select a.name &#39;员工&#39;, b.name &#39;领导&#39;
from emp a
         left join emp b on a.managerid = b.id;

注意事项:

在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底

是哪一张表的字段。

2.联合查询

对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。

SELECT 字段列表
FROM 表A... UNION [ ALL ]
SELECT 字段列表
FROM 表B....;

对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。

union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。

案例:

A. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.

当前对于这个需求,我们可以直接使用多条件查询,使用逻辑运算符 or 连接即可。在这里,我们也可以使用 UNION/UNION ALL 进行联合查询

select *
from emp
where salary < 5000
union all
select *
from emp
where age > 50;

union all查询出来的结果,仅仅进行简单的合并,并未去重。

MySQL の制約と複数テーブルのクエリ例の分析

select *
from emp
where salary < 5000
union
select *
from emp
where age > 50;

MySQL の制約と複数テーブルのクエリ例の分析

union 联合查询,会对查询出来的结果进行去重处理。

注意:

如果多条查询语句查询出来的结果,字段数量不一致,在进行union/union all联合查询时,将会报

错。如:

MySQL の制約と複数テーブルのクエリ例の分析

4.子查询

1.概述

1). 概念

SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

2). 分类

根据子查询结果不同,分为:

A. 标量子查询(子查询结果为单个值)

B. 列子查询(子查询结果为一列)

C. 行子查询(子查询结果为一行)

D. 表子查询(子查询结果为多行多列)

根据子查询位置,分为:

A. WHERE之后

B. FROM之后

C. SELECT之后

2.标量子查询

子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。

常用的操作符:= > >=

案例:

A. 查询 "销售部" 的所有员工信息

完成这个需求时,我们可以将需求分解为两步:

查询 "销售部" 部门ID

select id from dept where name = &#39;销售部&#39;;

根据 "销售部" 部门ID, 查询员工信息

select * from emp where dept_id = (select id from dept where name = &#39;销售部&#39;);

MySQL の制約と複数テーブルのクエリ例の分析

B. 查询在 "方东白" 入职之后的员工信息

完成这个需求时,我们可以将需求分解为两步:

查询 方东白 的入职日期

select entrydate from emp where name = &#39;方东白&#39;;

MySQL の制約と複数テーブルのクエリ例の分析

查询指定入职日期之后入职的员工信息

select * from emp where entrydate > (select entrydate from emp where name = &#39;方东白&#39;);

MySQL の制約と複数テーブルのクエリ例の分析

3.列子查询

子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。

常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL

MySQL の制約と複数テーブルのクエリ例の分析

案例:

A. 查询 "销售部" 和 "市场部" 的所有员工信息

分解为以下两步:

查询 "销售部" 和 "市场部" 的部门ID

select id from dept where name = &#39;销售部&#39; or name = &#39;市场部&#39;;

MySQL の制約と複数テーブルのクエリ例の分析

根据部门ID, 查询员工信息

select *
from emp
where dept_id in (select id from dept where name = &#39;销售部&#39; or name = &#39;市场部&#39;);

MySQL の制約と複数テーブルのクエリ例の分析

B. 查询比 财务部 所有人工资都高的员工信息

分解为以下两步:

查询所有 财务部 人员工资

select salary from emp where dept_id = (select id from dept where name = &#39;财务部&#39;);

MySQL の制約と複数テーブルのクエリ例の分析

比 财务部 所有人工资都高的员工信息

select *
from emp
where salary > all (select salary from emp where dept_id = (select id from dept where name = &#39;财务部&#39;));

C. 查询比研发部其中任意一人工资高的员工信息

分解为以下两步:

查询研发部所有人工资

select salary from emp where dept_id = (select id from dept where name = &#39;研发部&#39;);

比研部其中任意一人工资高的员工信息

select *
from emp
where salary > any (select salary from emp where dept_id = (select id from dept where name = &#39;研发部&#39;));

4.行子查询

子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。

常用的操作符:= 、 、IN 、NOT IN

案例:

A. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

这个需求同样可以拆解为两步进行:

查询 "张无忌" 的薪资及直属领导

select salary, managerid from emp where name = &#39;张无忌&#39;;

查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

select *
from emp
where (salary, managerid) = (select salary, managerid from emp where name = &#39;张无忌&#39;);

MySQL の制約と複数テーブルのクエリ例の分析

5.表子查询

子查询返回的结果是多行多列,这种子查询称为表子查询。

常用的操作符:IN

案例:

A. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

分解为两步执行:

查询 "鹿杖客" , "宋远桥" 的职位和薪资

select job, salary from emp where name = &#39;鹿杖客&#39; or name = &#39;宋远桥&#39;;

查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

select *
from emp
where (job, salary) in (select job, salary from emp where name = &#39;鹿杖客&#39; or name = &#39;宋远桥&#39;);

重新表述为:检索入职日期晚于"2006-01-01"的员工信息和他们所在的部门信息

分解为两步执行:

入职日期是 "2006-01-01" 之后的员工信息

select * from emp where entrydate > &#39;2006-01-01&#39;;

.查询这部分员工, 对应的部门信息;

select e.*, d.*
from (select * from emp where entrydate > &#39;2006-01-01&#39;) e
         left join dept d on e.dept_id = d.id;

以上がMySQL の制約と複数テーブルのクエリ例の分析の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

声明:
この記事はyisu.comで複製されています。侵害がある場合は、admin@php.cn までご連絡ください。