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

1.NOT NULL - 指示某列不能存儲(chǔ) NULL 值。

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

3.DEFAULT - 規(guī)定沒(méi)有給列賦值時(shí)的默認(rèn)值。

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

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

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

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

案例示范:

 create table student(
    -> id int primary key auto_increment,--主鍵約束,并且自動(dòng)加1
    -> name varchar(20) not null,--不能為空
    -> qq int unique,--值唯一
    -> age int default 18,--賦值默認(rèn)值
    -> gender varchar(2) check (gender = '男生' or gender = '女生'),--保證性別為男生或女生
    -> classid int,
    -> foreign key (classid) references class(id) -- 外鍵基于外鍵 classid和class表的主鍵 id在兩個(gè)表之間建立聯(lián)系
    -> );

結(jié)果:

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

desc student

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

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

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

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

注意實(shí)現(xiàn)

NOT NULL：

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

UNIQUE：

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

DEFAULT：

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

PRIMARY KEY：

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

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

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

CHECK：

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

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

 二.表的設(shè)計(jì)

1.1對(duì)1

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

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

方式一（共享主鍵）：

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表共享同一個(gè)主鍵，即Employee的ID也是IDCard的主鍵。

方式二（獨(dú)立主鍵，但使用唯一外鍵約束）：

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è)計(jì)中，兩個(gè)表各自有自己的主鍵，并且通過(guò)EmployeeID字段建立外鍵約束，確保IDCard表中每張身份證只對(duì)應(yīng)一個(gè)Employee。

2.1對(duì)多

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

例如：一個(gè)部門(mén)（Department）可以有多個(gè)員工（Employee），但每個(gè)員工只能屬于一個(gè)部門(mén)。

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

在這個(gè)例子中，Department表的每一個(gè)DeptID在Employee表中作為外鍵出現(xiàn)多次，表明了一個(gè)部門(mén)可以擁有多個(gè)員工。

3.多對(duì)多

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

例如：一個(gè)老師（Teacher）可以教多個(gè)學(xué)生（Student），而一個(gè)學(xué)生也可以被多個(gè)老師教授。

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

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

 三.查詢

1.聚合查詢

語(yǔ)法:

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

2.GROUP BY子句

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

語(yǔ)法:

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),
('豬無(wú)能','游戲角色', 333.5),
('沙和尚','游戲角色', 700.33),
('隔壁老王','董事長(zhǎng)', 12000.66);

 查詢每個(gè)角色的最高工資、最低工資和平均工資

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

結(jié)果:

 3 HAVING

GROUP BY 子句進(jìn)行分組以后，需要對(duì)分組結(jié)果再進(jìn)行條件過(guò)濾時(shí)，不能使用 WHERE 語(yǔ)句，而需要用 HAVING

案例:

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

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

 四.多表查詢

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

準(zhǔn)備實(shí)驗(yàn)數(shù)據(jù)

創(chuàng)建班級(jí)表:

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

創(chuàng)建學(xué)生表:

 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)建學(xué)生課程中間表，考試成績(jī)表:

 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("計(jì)科"),("軟工"),("網(wǎng)絡(luò)");
 insert into student(sn, name, qq_mail, class_id) values
     ('09982','黑旋風(fēng)李逵','xuanfeng@qq.com',1),
     ('00835','菩提老祖',null,1),
     ('00391','白素貞',null,1),
     ('00031','許仙','xuxian@qq.com',1),
     ('00054','不想畢業(yè)',null,1),
     ('51234','好好說(shuō)話','say@qq.com',2),
     ('83223','tellme',null,2),
     ('09527','老外學(xué)中文','foreigner@qq.com',2);
insert into course(name) values('Java'),('中國(guó)傳統(tǒng)文化'),('計(jì)算機(jī)原理'),('語(yǔ)文'),('高階數(shù)學(xué)'),('英文');
 insert into score(score, student_id, course_id) values
   -- 黑旋風(fēng)李逵
   (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),
    -- 好好說(shuō)話
   (56, 6, 2),(43, 6, 4),(79, 6, 6),
    -- tellme
   (80, 7, 2),(92, 7, 6);

4.1內(nèi)連接

語(yǔ)法:

select 字段 from 表1 別名1 [inner] join 表2 別名2 on 連接條件 and 其他條件;
select 字段 from 表1 別名1,表2 別名2 where 連接條件 and 其他條件;

案例：1）查詢“許仙”同學(xué)的 成績(jī) 

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）查詢所有同學(xué)的總成績(jī)，及同學(xué)的個(gè)人信息：

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é)來(lái)說(shuō):內(nèi)連接我們一開(kāi)始不熟練可以以下幾步來(lái)做

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

2.針對(duì)這幾張表進(jìn)行笛卡爾積

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

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

5.繼續(xù)根據(jù)題目要求看需要查詢的是那些列,把不需要的去除即可,例如案例一,我們只需要知道許仙的名字和成績(jī), select student.name,score.score 這樣即可

4.2外連接

語(yǔ)法：

-- 左外連接，表1完全顯示
select 字段名  from 表名1 left join 表名2 on 連接條件;
-- 右外連接，表2完全顯示
select 字段 from 表名1 right join 表名2 on 連接條件;

案例：查詢所有同學(xué)的成績(jī)，及同學(xué)的個(gè)人信息，如果該同學(xué)沒(méi)有成績(jī)，也需要顯示

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

 4.3自連接

自連接是指在同一張表連接自身進(jìn)行查詢。也就是自己對(duì)自己進(jìn)行笛卡爾積,屬于是對(duì)待特殊問(wèn)題的特殊技巧

案例:顯示所有“計(jì)算機(jī)原理”成績(jī)比“Java”成績(jī)高的成績(jī)信息

 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的課程號(hào)
     AND s2.course_id = 3;--計(jì)算機(jī)原理的課程號(hào)

這個(gè)SQL查詢從“score”表中選擇所有記錄，其中學(xué)生在Java課程（course_id = 1）中的分?jǐn)?shù)高于計(jì)算機(jī)原理課程（course_id = 3）中的分?jǐn)?shù)。該查詢?cè)?ldquo;score”表上使用自連接來(lái)比較同一學(xué)生在兩門(mén)課程中的分?jǐn)?shù)。

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

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

4.4 子查詢

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

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

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

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

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

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

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

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

4.5 合并查詢 

在實(shí)際應(yīng)用中，為了合并多個(gè)select的執(zhí)行結(jié)果，可以使用集合操作符 union，union all。使用UNION 和UNION ALL時(shí)，前后查詢的結(jié)果集中，字段需要一致。

1.union

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

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

 2.union all

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

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

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

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