MySQL數(shù)據(jù)庫(kù)約束和多表查詢實(shí)例代碼

更新時(shí)間：2025年07月31日 11:10:25 作者：蠟筆-小欣

這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)約束和多表查詢的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),還總結(jié)了SQL執(zhí)行順序,為后續(xù)索引與事務(wù)內(nèi)容做鋪墊,需要的朋友可以參考下

1.前言

我們上一次介紹了MySQL數(shù)據(jù)庫(kù)關(guān)于表的增刪改查，在平時(shí)是用的過程還需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行約束以及多表查詢，因此今天跟大家分享并介紹數(shù)據(jù)庫(kù)約束和多表查詢的內(nèi)容。

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

2.1約束類型

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

UNIQUE：保證某列的每行必須有唯一的值。

DEFAULT：規(guī)定沒有給列賦值時(shí)的默認(rèn)值。

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

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

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

2.2 NULL約束

我們?cè)跀?shù)據(jù)庫(kù)創(chuàng)建表時(shí)，可以指定某列不為空，比如我們?cè)趧?chuàng)建一個(gè)學(xué)生表時(shí)，可以對(duì)表的結(jié)構(gòu)進(jìn)行設(shè)置。

-- 創(chuàng)建學(xué)生表并設(shè)置學(xué)生表結(jié)構(gòu)
create table student (
id int not null,
sn int,
name varchar(20),
qq_mail varchar(20)
);

2.3 NUIQUE：唯一約束

指定sn列中的值唯一且不重復(fù)的，比如我們重新設(shè)置學(xué)生表的結(jié)構(gòu)：

-- 重新設(shè)置學(xué)生表結(jié)構(gòu)
drop table if exists student;
create table student (
id int not null,
sn int unique,
name varchar(20),
qq_mail varchar(20)
);

2.4 DEFAULT：默認(rèn)值約束

指定插入數(shù)據(jù)時(shí)， name 列為空，默認(rèn)值 unkown ：

-- 重新設(shè)置學(xué)生表結(jié)構(gòu)
drop table if exists student;
create table student (
id int not null,
sn int unique,
name varchar(20)  default 'unkown',
qq_mail varchar(20)
);

2.5 PRIMARY KEY：主鍵約束

指定 id 列為主鍵：

-- 重新設(shè)置學(xué)生表結(jié)構(gòu)
drop table if exists student;
create table student (
id int not null primary key,
sn int unique,
name varchar(20)  default 'unkown',
qq_mail varchar(20)
);

對(duì)于整數(shù)類型的主鍵，常配搭自增長(zhǎng) auto_increment 來(lái)使用。插入數(shù)據(jù)對(duì)應(yīng)字段不給值時(shí)，使用最大值+1 。

-- 主鍵是 NOT NULL 和 UNIQUE 的結(jié)合，可以不用 NOT NULL
id INT PRIMARY KEY auto_increment

2.6 FOREIGN KEY：外鍵約束

外鍵用于關(guān)聯(lián)其他表的主鍵或唯一鍵。

foreign key (字段名) references 主表(列)

下面以創(chuàng)建一個(gè)班級(jí)表和學(xué)生表為例：

創(chuàng)建班級(jí)表classes，id為主鍵

-- 創(chuàng)建班級(jí)表，有使用MySQL關(guān)鍵字作為字段時(shí)，需要使用``來(lái)標(biāo)識(shí)
create table classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
`desc` VARCHAR(100)
);

創(chuàng)建學(xué)生表student，一個(gè)學(xué)生對(duì)于一個(gè)班級(jí)，一個(gè)班級(jí)對(duì)應(yīng)多個(gè)學(xué)生。使用id為主鍵，class_id為外鍵，關(guān)聯(lián)班級(jí)表id。

-- 創(chuàng)建學(xué)生表
drop table if exists student;
create table student (
id int not null primary key,
sn int unique,
name varchar(20)  default 'unkown',
qq_mail varchar(20),
classes_id int,
foreign key (classes_id) references classes(id)
);

2.7 CHECK約束

MySQL使用時(shí)不報(bào)錯(cuò)，但忽略該約束。

create table test_user (
id int,
name varchar(20),
sex varchar(1),
check (sex = '男' or sex = '女')
);

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

3.1一對(duì)一

比如：一個(gè)學(xué)生只能對(duì)應(yīng)一個(gè)身份證，一個(gè)身份證也只能對(duì)應(yīng)一個(gè)學(xué)生。

3.2一對(duì)多

一個(gè)班級(jí)有多個(gè)學(xué)生，但一個(gè)學(xué)生只能對(duì)應(yīng)一個(gè)班級(jí)。

3.3多對(duì)多

一個(gè)學(xué)生能夠選擇多門課程，一個(gè)課程可以被多個(gè)學(xué)生選擇。

4.新增

語(yǔ)法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

給大家舉個(gè)例子：創(chuàng)建一張用戶表，設(shè)計(jì)有 name 姓名、 email 郵箱、 sex 性別、 mobile 手機(jī)號(hào)字段。需要把已有的學(xué)生數(shù)據(jù)復(fù)制進(jìn)來(lái)，可以復(fù)制的字段為name，qq_ mail。

-- 創(chuàng)建用戶表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id INT primary key auto_increment,
name VARCHAR(20) comment '姓名',
age INT comment '年齡',
email VARCHAR(20) comment '郵箱',
sex varchar(1) comment '性別',
mobile varchar(20) comment '手機(jī)號(hào)'
);

-- 新增班級(jí)數(shù)據(jù)
insert into classes values(601,"六年級(jí)一班","1班");

-- 新增學(xué)生數(shù)據(jù)
insert into student values(1,100,"張三","123@qq.com",601);
insert into student values(2,101,"李四","456@qq.com",601);

-- 將學(xué)生表中的所有數(shù)據(jù)復(fù)制到用戶表
insert into test_user(name, email) select name, qq_mail from student;

-- 查詢用戶表
select * from test_user;

5.查詢

5.1聚合查詢

5.1.1聚合函數(shù)

常見的統(tǒng)計(jì)總數(shù)、計(jì)算平局值等操作，可以使用 聚合函數(shù) 來(lái)實(shí)現(xiàn)，常見的聚合函數(shù)有：

函數(shù)	說(shuō)明
COUNT([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的數(shù)量
SUM([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的總和，不是數(shù)字沒有意義
AVG([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的平均值，不是數(shù)字沒有意義
MAX([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的最大值，不是數(shù)字沒有意義
MIN([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的最小值，不是數(shù)字沒有意義

COUNT

-- 統(tǒng)計(jì)班級(jí)共有多少同學(xué)
SELECT COUNT(*) FROM student;

-- 統(tǒng)計(jì)班級(jí)收集的qq_mail 有多少個(gè)，qq_mail 為 NULL 的數(shù)據(jù)不會(huì)計(jì)入結(jié)果
SELECT COUNT(qq_mail) FROM student;

-- 統(tǒng)計(jì)數(shù)學(xué)成績(jī)總分
select sum(math) from exam;

-- 統(tǒng)計(jì)所有數(shù)學(xué)成績(jī)不及格(<60)人的數(shù)學(xué)總分，沒有則返回NULL
select sum(math) from exam where math < 60;

-- 統(tǒng)計(jì)平均總分
select avg(Chinese + math + English) from exam;

-- 返回?cái)?shù)學(xué)最高分
select max(math) from exam;

-- 返回 > 70 分以上的數(shù)學(xué)最低分
select min(math) from exam where math > 70;

5.1.2 GROUP BY子句

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

語(yǔ)法：

select column1, sum(column2), .. fromtablegroupby column1,column3;

舉個(gè)例子：

準(zhǔn)備測(cè)試表及數(shù)據(jù)：?jiǎn)T工表，有id（主鍵）、name（姓名）、role（角色）、salary（薪水）

-- 創(chuàng)建員工表
create table emp (
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);

-- 添加員工數(shù)據(jù)
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 role, max(salary), min(salary), avg(salary) from emp group by role;

5.1.3 HAVING

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

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

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

5.2聯(lián)合查詢

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

注意：關(guān)聯(lián)查詢可以對(duì)關(guān)聯(lián)表使用別名。、

下面展示初始化測(cè)試數(shù)據(jù)：

-- 在班級(jí)表中新增數(shù)據(jù)
insert into classes(name, `desc`) values
('計(jì)算機(jī)系2019級(jí)1班', '學(xué)習(xí)了計(jì)算機(jī)原理、C和Java語(yǔ)言、數(shù)據(jù)結(jié)構(gòu)和算法'),
('中文系2019級(jí)3班','學(xué)習(xí)了中國(guó)傳統(tǒng)文學(xué)'),
('自動(dòng)化2019級(jí)5班','學(xué)習(xí)了機(jī)械自動(dòng)化');

-- 在學(xué)生表中新增數(shù)據(jù)
insert into student(sn, name, qq_mail, classes_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);

-- 創(chuàng)建課程表
create table course(id int primary key auto_increment, name varchar(20));

-- 在課程表中新增數(shù)據(jù)
insert into course(name) values
('Java'),
('中國(guó)傳統(tǒng)文化'),
('計(jì)算機(jī)原理'),
('語(yǔ)文'),
('高階數(shù)學(xué)'),
('英文');

-- 創(chuàng)建成績(jī)表
create table score(score decimal(3, 1), student_id int, course_id int);

--在成績(jī)表中新增數(shù)據(jù)
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);

5.2.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 stu.name, sco.score from student as stu join score as sco on stu.id = sco.student_id and stu.name = '許仙';

-- 也可以采用下面這種方法
select stu.name, sco.score from student as stu, score as sco where stu.id = sco.student_id and stu.name = '許仙';

（2）查詢所有同學(xué)的總成績(jī)，及同學(xué)的個(gè)人信息：

-- 成績(jī)表對(duì)學(xué)生表是多對(duì)一關(guān)系，查詢總成績(jī)是根據(jù)成績(jī)表的同學(xué)id來(lái)進(jìn)行分組的
select stu.sn, stu.name, stu.qq_mail, sum(sco.score) 
from 
student as stu join score as sco 
on stu.id = sco.student_id 
group by sco.student_id;

（3）查詢所有同學(xué)的成績(jī)，及同學(xué)的個(gè)人信息：

-- 查詢出來(lái)的都是有成績(jī)的同學(xué)，“老外學(xué)中文”同學(xué)沒有顯示
select * from student stu join score sco on stu.id=sco.student_id;

-- 學(xué)生表、成績(jī)表、課程表3張表關(guān)聯(lián)查詢
select stu.id,stu.sn,stu.NAME,stu.qq_mail,sco.score,sco.course_id,cou.NAME
FROM
student stu
JOIN score sco ON stu.id = sco.student_id
JOIN course cou ON sco.course_id = cou.id 
order by stu.id;

5.2.2外連接

外連接分為左外連接和右外連接。如果聯(lián)合查詢，左側(cè)的表完全顯示我們就說(shuō)是左外連接；右側(cè)的表完全顯示我們就是是右外連接。

語(yǔ)法：

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

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

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

-- “老外學(xué)中文”同學(xué)沒有考試成績(jī)，也顯示出來(lái)了
select * from student stu left join score sco on stu.id=sco.student_id;
-- 對(duì)應(yīng)的右外連接為：
select * from  score sco right join student stu on stu.id=sco.student_id;

-- 學(xué)生表、成績(jī)表、課程表3張表關(guān)聯(lián)查詢
SELECT stu.id,stu.sn,stu.NAME,stu.qq_mail,sco.score,sco.course_id,cou.NAME
FROM
student stu
LEFT JOIN score sco ON stu.id = sco.student_id     
LEFT JOIN course cou ON sco.course_id = cou.id 
order by stu.id;

5.2.3自連接

自連接是指在同一張表連接自身進(jìn)行查詢。

例如：顯示所有"計(jì)算機(jī)原理"成績(jī)比"Java"成績(jī)高的成績(jī)信息。

-- 先查詢"計(jì)算機(jī)原理"和"Java"課程的id
select id, name from course where name = 'Java' or name = '計(jì)算機(jī)原理';

-- 再查詢成績(jī)表中，"計(jì)算機(jī)原理"成績(jī)比"Java"成績(jī)好的信息
select s1.* 
from score s1,score s2 
where
s1.student_id = s2.student_id
and s1.score < s2.score and s1.course_id = 1
and s2.course_id = 3;

5.2.4子查詢

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

注意：編程中的基本思想原則化繁為簡(jiǎn)，但子查詢是化簡(jiǎn)為繁，我們要慎重使用，避免出錯(cuò)。

單行子查詢：返回一行記錄的子查詢

比如：查詢與 “ 不想畢業(yè) ” 同學(xué)的同班同學(xué)：

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

多行子查詢：返回多行記錄的子查詢

例如：查詢 “ 語(yǔ)文 ” 或 “ 英文 ” 課程的成績(jī)信息。

1. [NOT] IN關(guān)鍵字：

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

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

注意：

在 from 子句中使用子查詢：子查詢語(yǔ)句出現(xiàn)在 from 子句中。這里要用到數(shù)據(jù)查詢的技巧，把一個(gè)子查詢當(dāng)做一個(gè)臨時(shí)表使用。

5.2.5合并查詢

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

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='英文';

union all

該操作符用于取得兩個(gè)結(jié)果集的并集。 當(dāng)使用該操作符時(shí)，不會(huì)去掉結(jié)果集中的重復(fù)行。

舉個(gè)例子：查詢 id 小于 3 ，或者名字為 “Java” 的課程

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

6.總結(jié)

SQL 查詢中各個(gè)關(guān)鍵字的執(zhí)行先后順序： from > on> join > where > group by > with > having > select > distinct > order by > limit， 我們?cè)诰帉慡QL語(yǔ)句時(shí)要按照上面的執(zhí)行順序去編寫，才能成功運(yùn)行獲取到想要查詢的數(shù)據(jù)，以上就是本次所介紹的內(nèi)容，我們下次再跟繼續(xù)大家分享MySQL數(shù)據(jù)庫(kù)中的索引與事務(wù)！

到此這篇關(guān)于MySQL數(shù)據(jù)庫(kù)約束和多表查詢的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫(kù)約束和多表查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: