MySQL約束與多表查詢實(shí)例分析

本篇內(nèi)容主要講解“MySQL約束與多表查詢實(shí)例分析”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL約束與多表查詢實(shí)例分析”吧!

1.約束

概述

概念：約束是作用于表中字段上的規(guī)則，用于限制存儲(chǔ)在表中的數(shù)據(jù)。

目的：保證數(shù)據(jù)庫(kù)中數(shù)據(jù)的正確、有效性和完整性。

注意：約束是作用于表中字段上的，可以在創(chuàng)建表/修改表的時(shí)候添加約束。

約束演示

上面我們介紹了數(shù)據(jù)庫(kù)中常見(jiàn)的約束，以及約束涉及到的關(guān)鍵字，那這些約束我們到底如何在創(chuàng)建表、

修改表的時(shí)候來(lái)指定呢，接下來(lái)我們就通過(guò)一個(gè)案例，來(lái)演示一下。

案例需求： 根據(jù)需求，完成表結(jié)構(gòu)的創(chuàng)建。需求如下：

對(duì)應(yīng)的建表語(yǔ)句為：

CREATE TABLE tb_user
(
    id     int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一標(biāo)識(shí)',
    name   varchar(10) NOT NULL UNIQUE COMMENT '姓名',
    age     tinyint unsigned  COMMENT '年齡',
    status char(1) default '1' COMMENT '狀態(tài)',
    gender char(1) COMMENT '性別'
);

如果你的Mysql是8版本之后  age可以這樣創(chuàng)建

age int check (age > 0 && age <= 120) COMMENT '年齡' ,

在為字段添加約束時(shí)，我們只需要在字段之后加上約束的關(guān)鍵字即可，需要關(guān)注其語(yǔ)法。我們執(zhí)行上面的SQL把表結(jié)構(gòu)創(chuàng)建完成，然后接下來(lái)，就可以通過(guò)一組數(shù)據(jù)進(jìn)行測(cè)試，從而驗(yàn)證一下，約束是否可以生效。

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

可見(jiàn) 對(duì)于我們?cè)O(shè)置name的約束： 非空 且唯一  生效。

上面，我們是通過(guò)編寫(xiě)SQL語(yǔ)句的形式來(lái)完成約束的指定，那加入我們是通過(guò)圖形化界面來(lái)創(chuàng)建表結(jié)構(gòu)時(shí)，又該如何來(lái)指定約束呢？ 只需要在創(chuàng)建表的時(shí)候，根據(jù)我們的需要選擇對(duì)應(yīng)的約束即可。

外鍵約束

介紹

外鍵：用來(lái)讓兩張表的數(shù)據(jù)之間建立連接，從而保證數(shù)據(jù)的一致性和完整性。

我們來(lái)看一個(gè)例子：

左側(cè)的emp表是員工表，里面存儲(chǔ)員工的基本信息，包含員工的ID、姓名、年齡、職位、薪資、入職日 期、上級(jí)主管ID、部門ID，在員工的信息中存儲(chǔ)的是部門的ID dept_id，而這個(gè)部門的ID是關(guān)聯(lián)的部門表dept的主鍵id，那emp表的dept_id就是外鍵,關(guān)聯(lián)的是另一張表的主鍵。

注意：目前上述兩張表，只是在邏輯上存在這樣一層關(guān)系；在數(shù)據(jù)庫(kù)層面，并未建立外鍵關(guān)聯(lián)，所以是無(wú)法保證數(shù)據(jù)的一致性和完整性的。

沒(méi)有數(shù)據(jù)庫(kù)外鍵關(guān)聯(lián)的情況下，能夠保證一致性和完整性呢，我們來(lái)測(cè)試一下。

準(zhǔn)備數(shù)據(jù)

create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部門名稱'
) comment '部門表';
INSERT INTO dept (id, name)
VALUES (1, '研發(fā)部'),
       (2, '市場(chǎng)部'),
       (3, '財(cái)務(wù)部'),
       (4, '銷售部'),
       (5, '總經(jīng)辦');
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年齡',
    job       varchar(20) comment '職位',
    salary    int comment '薪資',
    entrydate date comment '入職時(shí)間',
    managerid int comment '直屬領(lǐng)導(dǎo)ID',
    dept_id   int comment '部門ID'
) comment '員工表';
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '總裁', 20000, '2000-01-01', null, 5),
       (2, '張無(wú)忌', 20, '項(xiàng)目經(jīng)理', 12500, '2005-12-05', 1, 1),
       (3, '楊逍', 33, '開(kāi)發(fā)', 8400, '2000-11-03', 2, 1),
       (4, '韋一笑', 48, '開(kāi) 發(fā)', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '開(kāi)發(fā)', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程 序員鼓勵(lì)師', 6600, '2004-10-12', 2, 1);

接下來(lái)，我們可以做一個(gè)測(cè)試，刪除id為1的部門信息。

結(jié)果，我們看到刪除成功，而刪除成功之后，部門表不存在id為1的部門，而在emp表中還有很多的員工，關(guān)聯(lián)的為id為1的部門，此時(shí)就出現(xiàn)了數(shù)據(jù)的不完整性。 而要想解決這個(gè)問(wèn)題就得通過(guò)數(shù)據(jù)庫(kù)的外鍵約束。

語(yǔ)法

1). 添加外鍵

CREATE TABLE 表名
(
    字段名 數(shù)據(jù)類型, ... [
    CONSTRAINT] [
    外鍵名稱]
    FOREIGN
    KEY
(
    外鍵字段名
) REFERENCES 主表
(
    主表列名
) );

ALTER TABLE 表名
    ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名) REFERENCES 主表 (主表列名);

案例:

為emp表的dept_id字段添加外鍵約束,關(guān)聯(lián)dept表的主鍵id。

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

添加了外鍵約束之后，我們?cè)俚絛ept表(父表)刪除id為1的記錄，然后看一下會(huì)發(fā)生什么現(xiàn)象。 此時(shí)

將會(huì)報(bào)錯(cuò)，不能刪除或更新父表記錄，因?yàn)榇嬖谕怄I約束。

2). 刪除外鍵

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;

案例：

刪除emp表的外鍵fk_emp_dept_id

alter table emp drop foreign key fk_emp_dept_id; 1

刪除/更新行為

添加了外鍵之后，再刪除父表數(shù)據(jù)時(shí)產(chǎn)生的約束行為，我們就稱為刪除/更新行為。具體的刪除/更新行為有以下幾種:

具體語(yǔ)法為:

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

演示如下：

由于NO ACTION 是默認(rèn)行為，我們前面語(yǔ)法演示的時(shí)候，已經(jīng)測(cè)試過(guò)了，就不再演示了，這里我們?cè)?/p>

演示其他的兩種行為：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

我們發(fā)現(xiàn)，原來(lái)在子表中dept_id值為1的記錄，現(xiàn)在也變?yōu)?了，這就是cascade級(jí)聯(lián)的效果

在一般的業(yè)務(wù)系統(tǒng)中，不會(huì)修改一張表的主鍵值。

B. 刪除父表id為6的記錄

我們發(fā)現(xiàn)，父表的數(shù)據(jù)刪除成功了，但是子表中關(guān)聯(lián)的記錄也被級(jí)聯(lián)刪除了。

2). SET NULL

在進(jìn)行測(cè)試之前，我們先需要?jiǎng)h除上面建立的外鍵 fk_emp_dept_id。然后再通過(guò)數(shù)據(jù)腳本，將 emp、dept表的數(shù)據(jù)恢復(fù)了。

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

接下來(lái)，我們刪除id為1的數(shù)據(jù)，看看會(huì)發(fā)生什么樣的現(xiàn)象。

我們發(fā)現(xiàn)父表的記錄是可以正常的刪除的，父表的數(shù)據(jù)刪除之后，再打開(kāi)子表 emp，我們發(fā)現(xiàn)子表emp 的dept_id字段，原來(lái)dept_id為1的數(shù)據(jù)，現(xiàn)在都被置為NULL了

這就是SET NULL這種刪除/更新行為的效果。

2.多表查詢

我們之前在講解SQL語(yǔ)句的時(shí)候，講解了DQL語(yǔ)句，也就是數(shù)據(jù)查詢語(yǔ)句，但是之前講解的查詢都是單表查詢，而本章節(jié)我們要學(xué)習(xí)的則是多表查詢操作，主要從以下幾個(gè)方面進(jìn)行講解。

多表關(guān)系

項(xiàng)目開(kāi)發(fā)中，在進(jìn)行數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)時(shí)，會(huì)根據(jù)業(yè)務(wù)需求及業(yè)務(wù)模塊之間的關(guān)系，分析并設(shè)計(jì)表結(jié) 構(gòu)，由于業(yè)務(wù)之間相互關(guān)聯(lián)，所以各個(gè)表結(jié)構(gòu)之間也存在著各種聯(lián)系，基本上分為三種：

一對(duì)多(多對(duì)一)     多對(duì)多      一對(duì)一

1.一對(duì)多

案例: 部門 與 員工的關(guān)系

關(guān)系: 一個(gè)部門對(duì)應(yīng)多個(gè)員工，一個(gè)員工對(duì)應(yīng)一個(gè)部門

實(shí)現(xiàn): 在多的一方建立外鍵，指向一的一方的主鍵

2.多對(duì)多

案例: 學(xué)生 與 課程的關(guān)系

關(guān)系: 一個(gè)學(xué)生可以選修多門課程，一門課程也可以供多個(gè)學(xué)生選擇

實(shí)現(xiàn): 建立第三張中間表，中間表至少包含兩個(gè)外鍵，分別關(guān)聯(lián)兩方主鍵

對(duì)應(yīng)的SQL腳本:

create table student
(
    id   int auto_increment primary key comment '主鍵ID',
    name varchar(10) comment '姓名',
    no   varchar(10) comment '學(xué)號(hào)'
) comment '學(xué)生表';
insert into student
values (null, '黛綺絲', '2000100101'),
       (null, '謝遜', '2000100102'),
       (null, '殷天正', '2000100103'),
       (null, '韋一笑', '2000100104');
create table course
(
    id   int auto_increment primary key comment '主鍵ID',
    name varchar(10) comment '課程名稱'
) comment '課程表';
insert into course
values (null, 'Java'),
       (null, 'PHP'),
       (null, 'MySQL'),
       (null, 'Hadoop');
create table student_course
(
    id        int auto_increment comment '主鍵' primary key,
    studentid int not null comment '學(xué)生ID',
    courseid  int not null comment '課程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '學(xué)生課程中間表';
insert into student_course
values (null, 1, 1),
       (null, 1, 2),
       (null, 1, 3),
       (null, 2, 2),
       (null, 2, 3),
       (null, 3, 4);

3.一對(duì)一

案例: 用戶與 用戶詳情的關(guān)系

關(guān)系: 一對(duì)一關(guān)系，多用于單表拆分，將一張表的基礎(chǔ)字段放在一張表中，其他詳情字段放在另一張表中，以提升操作效率

實(shí)現(xiàn): 在任意一方加入外鍵，關(guān)聯(lián)另外一方的主鍵，并且設(shè)置外鍵為唯一的(UNIQUE)

對(duì)應(yīng)的SQL腳本:

create table tb_user
(
    id     int auto_increment primary key comment '主鍵ID',
    name   varchar(10) comment '姓名',
    age    int comment '年齡',
    gender char(1) comment '1: 男 , 2: 女',
    phone  char(11) comment '手機(jī)號(hào)'
) comment '用戶基本信息表';
create table tb_user_edu
(
    id            int auto_increment primary key comment '主鍵ID',
    degree        varchar(20) comment '學(xué)歷',
    major         varchar(50) comment '專業(yè)',
    primaryschool varchar(50) comment '小學(xué)',
    middleschool  varchar(50) comment '中學(xué)',
    university    varchar(50) comment '大學(xué)',
    userid        int unique comment '用戶ID',
    constraint fk_userid foreign key (userid) references tb_user (id)
) comment '用戶教育信息表';
insert into tb_user(id, name, age, gender, phone)
values (null, '黃渤', 45, '1', '18800001111'),
       (null, '冰冰', 35, '2', '18800002222'),
       (null, '碼云', 55, '1', '18800008888'),
       (null, '李彥宏', 50, '1', '18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid)
values (null, '本科', '舞蹈', '靜安區(qū)第一小學(xué)', '靜安區(qū)第一中學(xué)', '北京舞蹈學(xué)院', 1),
       (null, '碩士', '表演', '朝陽(yáng)區(qū)第一小學(xué)', '朝陽(yáng)區(qū)第一中學(xué)', '北京電影學(xué)院', 2),
       (null, '本科', '英語(yǔ)', '杭州市第一小學(xué)', '杭州市第一中學(xué)', '杭州師范大學(xué)', 3),
       (null, '本科', '應(yīng)用數(shù)學(xué)', '陽(yáng)泉第一小學(xué)', '陽(yáng)泉區(qū)第一中學(xué)', '清華大學(xué)', 4);

3.多表查詢概述

1.數(shù)據(jù)準(zhǔn)備

1). 刪除之前 emp, dept表的測(cè)試數(shù)據(jù)

2). 執(zhí)行如下腳本，創(chuàng)建emp表與dept表并插入測(cè)試數(shù)據(jù)

-- 創(chuàng)建dept表，并插入數(shù)據(jù)
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部門名稱'
) comment '部門表';
INSERT INTO dept (id, name)
VALUES (1, '研發(fā)部'),
       (2, '市場(chǎng)部'),
       (3, '財(cái)務(wù)部'),
       (4, '銷售部'),
       (5, '總經(jīng)辦'),
       (6, '人事部');
-- 創(chuàng)建emp表，并插入數(shù)據(jù)
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年齡',
    job       varchar(20) comment '職位',
    salary    int comment '薪資',
    entrydate date comment '入職時(shí)間',
    managerid int comment '直屬領(lǐng)導(dǎo)ID',
    dept_id   int comment '部門ID'
) comment '員工表';
-- 添加外鍵
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, '金庸', 66, '總裁', 20000, '2000-01-01', null, 5),
       (2, '張無(wú)忌', 20, '項(xiàng)目經(jīng)理', 12500, '2005-12-05', 1, 1),
       (3, '楊逍', 33, '開(kāi)發(fā)', 8400, '2000-11-03', 2, 1),
       (4, '韋一笑', 48, '開(kāi)發(fā)', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '開(kāi)發(fā)', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序員鼓勵(lì)師', 6600, '2004-10-12', 2, 1),
       (7, '滅絕', 60, '財(cái)務(wù)總監(jiān)', 8500, '2002-09-12', 1, 3),
       (8, '周芷若', 19, '會(huì)計(jì)', 48000, '2006-06-02', 7, 3),
       (9, '丁敏君', 23, '出納', 5250, '2009-05-13', 7, 3),
       (10, '趙敏', 20, '市場(chǎng)部總監(jiān)', 12500, '2004-10-12', 1, 2),
       (11, '鹿杖客', 56, '職員', 3750, '2006-10-03', 10, 2),
       (12, '鶴筆翁', 19, '職員', 3750, '2007-05-09', 10, 2),
       (13, '方東白', 19, '職員', 5500, '2009-02-12', 10, 2),
       (14, '張三豐', 88, '銷售總監(jiān)', 14000, '2004-10-12', 1, 4),
       (15, '俞蓮舟', 38, '銷售', 4600, '2004-10-12', 14, 4),
       (16, '宋遠(yuǎn)橋', 40, '銷售', 4600, '2004-10-12', 14, 4),
       (17, '陳友諒', 42, null, 2000, '2011-10-12', 1, null)

dept表共6條記錄，emp表共17條記錄。

2.概述

多表查詢就是指從多張表中查詢數(shù)據(jù)。

原來(lái)查詢單表數(shù)據(jù)，執(zhí)行的SQL形式為：select * from emp;

那么我們要執(zhí)行多表查詢，就只需要使用逗號(hào)分隔多張表即可，如： select * from emp , dept ; 具體的執(zhí)行結(jié)果如下:

此時(shí),我們看到查詢結(jié)果中包含了大量的結(jié)果集，總共102條記錄，而這其實(shí)就是員工表emp所有的記錄 (17) 與 部門表dept所有記錄(6) 的所有組合情況，這種現(xiàn)象稱之為笛卡爾積。接下來(lái)，就來(lái)簡(jiǎn)單 介紹下笛卡爾積。

笛卡爾積: 笛卡爾乘積是指在數(shù)學(xué)中，兩個(gè)集合A集合 和 B集合的所有組合情況。

而在多表查詢中，我們是需要消除無(wú)效的笛卡爾積的，只保留兩張表關(guān)聯(lián)部分的數(shù)據(jù)。

在SQL語(yǔ)句中，如何來(lái)去除無(wú)效的笛卡爾積呢？ 我們可以給多表查詢加上連接查詢的條件即可。

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

而由于id為17的員工，沒(méi)有dept_id字段值，所以在多表查詢時(shí)，根據(jù)連接查詢的條件并沒(méi)有查詢到。

4.分類

連接查詢

內(nèi)連接：相當(dāng)于查詢A、B交集部分?jǐn)?shù)據(jù)

外連接：

左外連接：查詢左表所有數(shù)據(jù)，以及兩張表交集部分?jǐn)?shù)據(jù)

右外連接：查詢右表所有數(shù)據(jù)，以及兩張表交集部分?jǐn)?shù)據(jù)

自連接：當(dāng)前表與自身的連接查詢，自連接必須使用表別名

子查詢

1.內(nèi)連接

內(nèi)連接查詢的是兩張表交集部分的數(shù)據(jù)。(也就是綠色部分的數(shù)據(jù))

內(nèi)連接的語(yǔ)法分為兩種: 隱式內(nèi)連接、顯式內(nèi)連接。先來(lái)學(xué)習(xí)一下具體的語(yǔ)法結(jié)構(gòu)。

1). 隱式內(nèi)連接

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

2). 顯式內(nèi)連接

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

案例:

A. 查詢每一個(gè)員工的姓名 , 及關(guān)聯(lián)的部門的名稱 (隱式內(nèi)連接實(shí)現(xiàn))

表結(jié)構(gòu): emp , dept

連接條件: emp.dept_id = dept.id

select emp.name, dept.name
from emp,
     dept
where emp.dept_id = dept.id; 
-- 為每一張表起別名,簡(jiǎn)化SQL編寫(xiě)
 select e.name,d.name from emp e , dept d where e.dept_id = d.id;

B. 查詢每一個(gè)員工的姓名 , 及關(guān)聯(lián)的部門的名稱 (顯式內(nèi)連接實(shí)現(xiàn)) --- INNER JOIN ...

ON ...

表結(jié)構(gòu): 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;
-- 為每一張表起別名,簡(jiǎn)化SQL編寫(xiě)
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 ;

注意事項(xiàng):一旦為表起了別名，就不能再使用表名來(lái)指定對(duì)應(yīng)的字段了，此時(shí)只能夠使用別名來(lái)指定字段。

2.外連接

外連接分為兩種，分別是：左外連接 和 右外連接。具體的語(yǔ)法結(jié)構(gòu)為：

1). 左外連接  

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

左外連接相當(dāng)于查詢表1(左表)的所有數(shù)據(jù)，當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)。

2). 右外連接

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

右外連接相當(dāng)于查詢表2(右表)的所有數(shù)據(jù)，當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)。

案例:

A. 查詢emp表的所有數(shù)據(jù), 和對(duì)應(yīng)的部門信息

由于需求中提到，要查詢emp的所有數(shù)據(jù)，所以是不能內(nèi)連接查詢的，需要考慮使用外連接查詢。

表結(jié)構(gòu): 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;

B. 查詢dept表的所有數(shù)據(jù), 和對(duì)應(yīng)的員工信息(右外連接)

由于需求中提到，要查詢dept表的所有數(shù)據(jù)，所以是不能內(nèi)連接查詢的，需要考慮使用外連接查詢。

表結(jié)構(gòu): 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;

注意事項(xiàng)：

左外連接和右外連接是可以相互替換的，只需要調(diào)整在連接查詢時(shí)SQL中，表結(jié)構(gòu)的先后順

序就可以了。而我們?cè)谌粘ｉ_(kāi)發(fā)使用時(shí)，更偏向于左外連接。

3.自連接

1.自連接查詢

自連接查詢，顧名思義，就是自己連接自己，也就是把一張表連接查詢多次。我們先來(lái)學(xué)習(xí)一下自連接的查詢語(yǔ)法：

SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ... ;

而對(duì)于自連接查詢，可以是內(nèi)連接查詢，也可以是外連接查詢

案例：

A. 查詢員工 及其 所屬領(lǐng)導(dǎo)的名字

表結(jié)構(gòu): emp

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

B. 查詢所有員工 emp 及其領(lǐng)導(dǎo)的名字 emp , 如果員工沒(méi)有領(lǐng)導(dǎo), 也需要查詢出來(lái)

表結(jié)構(gòu): emp a , emp b

select a.name '員工', b.name '領(lǐng)導(dǎo)'
from emp a
         left join emp b on a.managerid = b.id;

注意事項(xiàng):

在自連接查詢中，必須要為表起別名，要不然我們不清楚所指定的條件、返回的字段，到底

是哪一張表的字段。

2.聯(lián)合查詢

對(duì)于union查詢，就是把多次查詢的結(jié)果合并起來(lái)，形成一個(gè)新的查詢結(jié)果集。

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

對(duì)于聯(lián)合查詢的多張表的列數(shù)必須保持一致，字段類型也需要保持一致。

union all 會(huì)將全部的數(shù)據(jù)直接合并在一起，union 會(huì)對(duì)合并之后的數(shù)據(jù)去重。

案例:

A. 將薪資低于 5000 的員工 , 和 年齡大于 50 歲的員工全部查詢出來(lái).

當(dāng)前對(duì)于這個(gè)需求，我們可以直接使用多條件查詢，使用邏輯運(yùn)算符 or 連接即可。 那這里呢，我們 也可以通過(guò)union/union all來(lái)聯(lián)合查詢.

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

union all查詢出來(lái)的結(jié)果，僅僅進(jìn)行簡(jiǎn)單的合并，并未去重。

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

union 聯(lián)合查詢，會(huì)對(duì)查詢出來(lái)的結(jié)果進(jìn)行去重處理。

注意：

如果多條查詢語(yǔ)句查詢出來(lái)的結(jié)果，字段數(shù)量不一致，在進(jìn)行union/union all聯(lián)合查詢時(shí)，將會(huì)報(bào)

錯(cuò)。如：

4.子查詢

1.概述

1). 概念

SQL語(yǔ)句中嵌套SELECT語(yǔ)句，稱為嵌套查詢，又稱子查詢。

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

子查詢外部的語(yǔ)句可以是INSERT / UPDATE / DELETE / SELECT 的任何一個(gè)。

2). 分類

根據(jù)子查詢結(jié)果不同，分為：

A. 標(biāo)量子查詢（子查詢結(jié)果為單個(gè)值）

B. 列子查詢(子查詢結(jié)果為一列)

C. 行子查詢(子查詢結(jié)果為一行)

D. 表子查詢(子查詢結(jié)果為多行多列)

根據(jù)子查詢位置，分為：

A. WHERE之后

B. FROM之后

C. SELECT之后

2.標(biāo)量子查詢

子查詢返回的結(jié)果是單個(gè)值（數(shù)字、字符串、日期等），最簡(jiǎn)單的形式，這種子查詢稱為標(biāo)量子查詢。

常用的操作符：= <> > >= < <=

案例:

A. 查詢 "銷售部" 的所有員工信息

完成這個(gè)需求時(shí)，我們可以將需求分解為兩步：

查詢 "銷售部" 部門ID

select id from dept where name = '銷售部';

根據(jù) "銷售部" 部門ID, 查詢員工信息

select * from emp where dept_id = (select id from dept where name = '銷售部');

B. 查詢?cè)?"方東白" 入職之后的員工信息

完成這個(gè)需求時(shí)，我們可以將需求分解為兩步：

查詢 方東白 的入職日期

select entrydate from emp where name = '方東白';

查詢指定入職日期之后入職的員工信息

select * from emp where entrydate > (select entrydate from emp where name = '方東白');

3.列子查詢

子查詢返回的結(jié)果是一列（可以是多行），這種子查詢稱為列子查詢。

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

案例:

A. 查詢 "銷售部" 和 "市場(chǎng)部" 的所有員工信息

分解為以下兩步:

查詢 "銷售部" 和 "市場(chǎng)部" 的部門ID

select id from dept where name = '銷售部' or name = '市場(chǎng)部';

根據(jù)部門ID, 查詢員工信息

select *
from emp
where dept_id in (select id from dept where name = '銷售部' or name = '市場(chǎng)部');

B. 查詢比 財(cái)務(wù)部 所有人工資都高的員工信息

分解為以下兩步:

查詢所有 財(cái)務(wù)部 人員工資

select salary from emp where dept_id = (select id from dept where name = '財(cái)務(wù)部');

比 財(cái)務(wù)部 所有人工資都高的員工信息

select *
from emp
where salary > all (select salary from emp where dept_id = (select id from dept where name = '財(cái)務(wù)部'));

C. 查詢比研發(fā)部其中任意一人工資高的員工信息

分解為以下兩步:

查詢研發(fā)部所有人工資

select salary from emp where dept_id = (select id from dept where name = '研發(fā)部');

比研部其中任意一人工資高的員工信息

select *
from emp
where salary > any (select salary from emp where dept_id = (select id from dept where name = '研發(fā)部'));

4.行子查詢

子查詢返回的結(jié)果是一行（可以是多列），這種子查詢稱為行子查詢。

常用的操作符：= 、<> 、IN 、NOT IN

案例:

A. 查詢與 "張無(wú)忌" 的薪資及直屬領(lǐng)導(dǎo)相同的員工信息 ;

這個(gè)需求同樣可以拆解為兩步進(jìn)行:

查詢 "張無(wú)忌" 的薪資及直屬領(lǐng)導(dǎo)

select salary, managerid from emp where name = '張無(wú)忌';

查詢與 "張無(wú)忌" 的薪資及直屬領(lǐng)導(dǎo)相同的員工信息 ;

select *
from emp
where (salary, managerid) = (select salary, managerid from emp where name = '張無(wú)忌');

5.表子查詢

子查詢返回的結(jié)果是多行多列，這種子查詢稱為表子查詢。

常用的操作符：IN

案例:

A. 查詢與 "鹿杖客" , "宋遠(yuǎn)橋" 的職位和薪資相同的員工信息

分解為兩步執(zhí)行:

查詢 "鹿杖客" , "宋遠(yuǎn)橋" 的職位和薪資

select job, salary from emp where name = '鹿杖客' or name = '宋遠(yuǎn)橋';

查詢與 "鹿杖客" , "宋遠(yuǎn)橋" 的職位和薪資相同的員工信息

select *
from emp
where (job, salary) in (select job, salary from emp where name = '鹿杖客' or name = '宋遠(yuǎn)橋');

B. 查詢?nèi)肼毴掌谑?"2006-01-01" 之后的員工信息 , 及其部門信息

分解為兩步執(zhí)行:

入職日期是 "2006-01-01" 之后的員工信息

select * from emp where entrydate > '2006-01-01';

.查詢這部分員工, 對(duì)應(yīng)的部門信息;

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

到此，相信大家對(duì)“MySQL約束與多表查詢實(shí)例分析”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！