MySQL表的增刪改查之多表查詢和聯(lián)合查詢功能

更新時間：2024年04月13日 10:18:58 作者：小謝在努力

這篇文章主要介紹了MySQL表的增刪改查---多表查詢和聯(lián)合查詢功能,這些約束條件在數(shù)據(jù)庫中起著非常重要的作用,可以確保數(shù)據(jù)的完整性和一致性,本文給大家介紹的非常詳細,需要的朋友可以參考下

一. 數(shù)據(jù)庫約束

1.NOT NULL - 指示某列不能存儲 NULL 值。

2.UNIQUE - 保證某列的每行必須有唯一的值。

3.DEFAULT - 規(guī)定沒有給列賦值時的默認值。

4.PRIMARY KEY - NOT NULL 和 UNIQUE 的結(jié)合。確保某列（或兩個列多個列的結(jié)合）有唯一標識，有助于更容易更快速地找到表中的一個特定的記錄。

5.FOREIGN KEY - 保證一個表中的數(shù)據(jù)匹配另一個表中的值的參照完整性。

6.CHECK - 保證列中的值符合指定的條件。對于MySQL數(shù)據(jù)庫，對CHECK子句進行分析，但是忽略 CHECK子句。

這些約束條件在數(shù)據(jù)庫中起著非常重要的作用，可以確保數(shù)據(jù)的完整性和一致性。在設(shè)計數(shù)據(jù)庫表結(jié)構(gòu)時，合理地運用這些約束條件可以有效地避免數(shù)據(jù)錯誤和不一致性。

案例示范:

 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在兩個表之間建立聯(lián)系
    -> );

結(jié)果:

使用 desc 表名查看表結(jié)構(gòu)

desc student

這時候我們插入表的數(shù)據(jù)就受到數(shù)據(jù)庫的約束了例如我們可以插入一條不符合數(shù)據(jù)庫的約束的數(shù)據(jù)查看一下

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

例如我們插入一條性別為未知的數(shù)據(jù)可以看到

不符合CHECK條件的數(shù)據(jù)無法插入.博主在這里就驗證這一條約束,別的就不過多的贅述了.

注意實現(xiàn)

NOT NULL：

NOT NULL 約束用于確保指定列不能存儲NULL值。這意味著，在插入新記錄或更新現(xiàn)有記錄時，該列必須包含有效數(shù)據(jù)。注意：在設(shè)計表結(jié)構(gòu)時應謹慎使用NOT NULL約束，特別是在考慮業(yè)務(wù)邏輯和未來擴展性時，要確保所有必要的數(shù)據(jù)都能在任何時候提供。

UNIQUE：

UNIQUE 約束強制某一列（或多個列組合）中的每行都具有唯一的值，不允許重復。注意：對于多列組成的唯一鍵，只要這些列的組合不重復即可，單個列可以有重復值。另外，每個表只能有一個主鍵約束，但可以有多個UNIQUE約束。

DEFAULT：

DEFAULT 約束為指定列定義一個默認值，當插入新記錄時如果沒有明確為該列賦值，則自動填充這個預設(shè)值。注意：默認值的選擇應該符合業(yè)務(wù)邏輯，并且對于可能需要特殊處理的值（如時間戳、序列號等），需要考慮是否需要觸發(fā)器或者程序邏輯來動態(tài)生成更復雜的默認值。

PRIMARY KEY：

PRIMARY KEY 是一種特殊的約束，它結(jié)合了NOT NULL 和 UNIQUE 的特點，即主鍵列的值不能為空，并且每一行的主鍵值在整個表中必須是唯一的。注意：每個表只能有一個主鍵，它可以是一個單列或多列的組合（復合主鍵）。主鍵通常被用作索引，有助于快速定位和查詢特定記錄。

FOREIGN KEY：(特別關(guān)注)

FOREIGN KEY 約束用于維護兩個表之間的引用完整性，確保一個表中的列（外鍵列）的值必須匹配另一個表（參照表）的主鍵列的值。注意：在設(shè)置外鍵約束時，要考慮刪除規(guī)則（ON DELETE CASCADE, SET NULL, NO ACTION等）和更新規(guī)則（ON UPDATE CASCADE等），以避免違反引用完整性和級聯(lián)操作帶來的影響。

CHECK：

CHECK 約束理論上允許你限制列的值必須滿足特定條件，例如年齡必須在0到120之間這樣的范圍檢查。
注意：在MySQL中，默認情況下并不支持標準SQL的CHECK約束進行行級的數(shù)據(jù)驗證。盡管可以在創(chuàng)建表時編寫CHECK子句，但MySQL會忽略執(zhí)行這些約束，除非在某些特定存儲引擎下或通過觸發(fā)器模擬實現(xiàn)類似功能。在其他數(shù)據(jù)庫系統(tǒng)如Oracle、PostgreSQL中，CHECK約束會被正常執(zhí)行并用于保證列值符合特定條件。

總之，在實際應用中合理使用這些約束能夠有效地維護數(shù)據(jù)庫的一致性和完整性，但在MySQL中尤其要注意其對CHECK約束的支持情況，以確保數(shù)據(jù)驗證機制的有效實施。

二.表的設(shè)計

1.1對1

在一對一關(guān)系中，一個表中的每一行只與另一個表中的一行相關(guān)聯(lián)，反之亦然。

例如：每個員工（Employee）可能有一個唯一的身份證記錄（IDCard）。在這種情況下，可以有兩種設(shè)計方式：

方式一（共享主鍵）：

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), -- 其他與身份證相關(guān)的字段
    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)
);

在這種設(shè)計中，兩個表各自有自己的主鍵，并且通過EmployeeID字段建立外鍵約束，確保IDCard表中每張身份證只對應一個Employee。

2.1對多

在一對多關(guān)系中，一個表的一行可以與另一個表的多行關(guān)聯(lián)，但反過來不行。

例如：一個部門（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表中作為外鍵出現(xiàn)多次，表明了一個部門可以擁有多個員工。

3.多對多

在多對多關(guān)系中，一個表的每一行都可以與另一個表的多行關(guān)聯(lián)，反之亦然。

例如：一個老師（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)
);

在這種情況下，需要創(chuàng)建一個中間表（TeacherStudent），它包含兩個外鍵分別引用Teacher和Student表的主鍵，以此來存儲這種多對多的關(guān)系信息。

三.查詢

1.聚合查詢

函數(shù)	說明
count(數(shù)據(jù))	返回查詢到的數(shù)據(jù)的數(shù)量
sum(數(shù)據(jù))	返回查詢到的數(shù)據(jù)的總和，不是數(shù)字沒有意義
avg(數(shù)據(jù))	返回查詢到的數(shù)據(jù)的平均值，不是數(shù)字沒有意義
max(數(shù)據(jù))	返回查詢到的數(shù)據(jù)的最大值，不是數(shù)字沒有意義
min(數(shù)據(jù))	返回查詢到的數(shù)據(jù)的最小值，不是數(shù)字沒有意義

語法:

select 聚合函數(shù)(數(shù)據(jù)) from + 表名 + where 條件

2.GROUP BY子句

SELECT 中使用 GROUP BY 子句可以對指定列進行分組查詢。需要滿足：使用 GROUP BY 進行分組查詢時，SELECT 指定的字段必須是“分組依據(jù)字段”，其他字段若想出現(xiàn)在SELECT 中則必須包含在聚合函數(shù)中。

語法:

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
('馬云','服務(wù)員', 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;

結(jié)果:

3 HAVING

GROUP BY 子句進行分組以后，需要對分組結(jié)果再進行條件過濾時，不能使用 WHERE 語句，而需要用 HAVING

案例:

顯示平均工資低于1500的角色和它的平均工資

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

四.多表查詢

實際開發(fā)中往往數(shù)據(jù)來自不同的表，所以需要多表聯(lián)合查詢。多表查詢是對多張表的數(shù)據(jù)取笛卡爾積：

準備實驗數(shù)據(jù)

創(chuàng)建班級表:

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

創(chuàng)建學生表:

 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));

創(chuàng)建課程表:

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

創(chuàng)建學生課程中間表，考試成績表:

 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)
     );

插入數(shù)據(jù):

insert into class(name)values("計科"),("軟工"),("網(wǎng)絡(luò)");
 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','不想畢業(yè)',null,1),
     ('51234','好好說話','say@qq.com',2),
     ('83223','tellme',null,2),
     ('09527','老外學中文','foreigner@qq.com',2);
insert into course(name) values('Java'),('中國傳統(tǒng)文化'),('計算機原理'),('語文'),('高階數(shù)學'),('英文');
 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),
   -- 不想畢業(yè)
   (81, 5, 1),(37, 5, 5),
    -- 好好說話
   (56, 6, 2),(43, 6, 4),(79, 6, 6),
    -- tellme
   (80, 7, 2),(92, 7, 6);

4.1內(nèi)連接

語法:

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;

總結(jié)來說:內(nèi)連接我們一開始不熟練可以以下幾步來做

1.先確定我們要查詢的信息,來自那幾張表

2.針對這幾張表進行笛卡爾積

3.加上連接條件,去除無效數(shù)據(jù)

4.在根據(jù)題目要求,加上其他條件

5.繼續(xù)根據(jù)題目要求看需要查詢的是那些列,把不需要的去除即可,例如案例一,我們只需要知道許仙的名字和成績, 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）中的分數(shù)高于計算機原理課程（course_id = 3）中的分數(shù)。該查詢在“score”表上使用自連接來比較同一學生在兩門課程中的分數(shù)。

自連接是指在同一個表中進行連接操作。在這種情況下，我們使用表的別名來區(qū)分不同的實例。以下是自連接的步驟：

為表分配別名：在查詢中為同一張表分配不同的別名，以便區(qū)分它們。在這個例子中，我們使用了s1和s2作為score表的別名。
指定連接條件：在JOIN子句中指定連接條件，以便確定兩個表之間的關(guān)聯(lián)。在這個例子中，連接條件是s1.student_id = s2.student_id，表示兩個實例具有相同的學生ID。
添加過濾條件：在ON子句中添加額外的條件來過濾出符合特定條件的記錄。在這個例子中，過濾條件是s1.score < s2.score，s1.course_id = 1和s2.course_id = 3。
選擇需要的列：在SELECT子句中指定要檢索的列。在這個例子中，我們選擇了s1.*，表示選擇s1表中的所有列。
執(zhí)行查詢：執(zhí)行SQL查詢以獲取符合條件的結(jié)果集。

4.4 子查詢

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

1.單行子查詢：返回一行記錄的子查詢查詢與“不想畢業(yè)” 同學的同班同學：

 select * from student where class_id=(select class_id from student where
     name='不想畢業(yè)');

多行子查詢：返回多行記錄的子查詢案例：查詢“語文”或“英文”課程的成績信息:

(not)in關(guān)鍵字

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

(not)exists關(guān)鍵字

-- 使用 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的執(zhí)行結(jié)果，可以使用集合操作符 union，union all。使用UNION 和UNION ALL時，前后查詢的結(jié)果集中，字段需要一致。

1.union

該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時，會自動去掉結(jié)果集中的重復行。案例：查詢id小于3，或者名字為“英文”的課程：

select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or來實現(xiàn)
select * from course where id<3 or name='英文';

2.union all

該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時，不會去掉結(jié)果集中的重復行。案例：查詢id小于3，或者名字為“Java”的課程

-- 可以看到結(jié)果集中出現(xiàn)重復數(shù)據(jù)Java
select * from course where id<3
union all
select * from course where name='Java';

以上就是博主關(guān)于MySQL的多表查詢和聯(lián)合查詢的全部類型了,感謝您的閱讀

到此這篇關(guān)于MySQL表的增刪改查---多表查詢和聯(lián)合查詢的文章就介紹到這了,更多相關(guān)MySQL多表查詢和聯(lián)合查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片