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

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

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

1.前言

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

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

2.1約束類型

NOT NULL：指示某列不能存儲NULL值。

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

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

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

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

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

2.2 NULL約束

我們在數(shù)據(jù)庫創(chuàng)建表時，可以指定某列不為空，比如我們在創(chuàng)建一個學生表時，可以對表的結(jié)構(gòu)進行設置。

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

2.3 NUIQUE：唯一約束

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

-- 重新設置學生表結(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：默認值約束

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

-- 重新設置學生表結(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 列為主鍵：

-- 重新設置學生表結(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)
);

對于整數(shù)類型的主鍵，常配搭自增長 auto_increment 來使用。插入數(shù)據(jù)對應字段不給值時，使用最大值+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)建一個班級表和學生表為例：

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

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

創(chuàng)建學生表student，一個學生對于一個班級，一個班級對應多個學生。使用id為主鍵，class_id為外鍵，關(guān)聯(lián)班級表id。

-- 創(chuàng)建學生表
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使用時不報錯，但忽略該約束。

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

3.表的設計

3.1一對一

比如：一個學生只能對應一個身份證，一個身份證也只能對應一個學生。

3.2一對多

一個班級有多個學生，但一個學生只能對應一個班級。

3.3多對多

一個學生能夠選擇多門課程，一個課程可以被多個學生選擇。

4.新增

語法：

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

給大家舉個例子：創(chuàng)建一張用戶表，設計有 name 姓名、 email 郵箱、 sex 性別、 mobile 手機號字段。需要把已有的學生數(shù)據(jù)復制進來，可以復制的字段為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 '手機號'
);

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

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

-- 將學生表中的所有數(shù)據(jù)復制到用戶表
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)計總數(shù)、計算平局值等操作，可以使用 聚合函數(shù) 來實現(xiàn)，常見的聚合函數(shù)有：

函數(shù)	說明
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)計班級共有多少同學
SELECT COUNT(*) FROM student;

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

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

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

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

-- 返回數(shù)學最高分
select max(math) from exam;

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

5.1.2 GROUP BY子句

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

語法：

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

舉個例子：

準備測試表及數(shù)據(jù)：員工表，有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
('張三','服務員', 1000.20),
('李四','游戲陪玩', 2000.99),
('孫悟空','游戲角色', 999.11),
('豬無能','游戲角色', 333.5),
('沙和尚','游戲角色', 700.33),
('王五','董事長', 12000.66);

查詢每個角色的最高工資、最低工資和平均工資。

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

5.1.3 HAVING

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

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

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

5.2聯(lián)合查詢

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

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

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

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

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

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

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

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

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

5.2.1內(nèi)連接

語法：

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

例如：（1）查詢“許仙”同學的成績

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）查詢所有同學的總成績，及同學的個人信息：

-- 成績表對學生表是多對一關(guān)系，查詢總成績是根據(jù)成績表的同學id來進行分組的
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）查詢所有同學的成績，及同學的個人信息：

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

-- 學生表、成績表、課程表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è)的表完全顯示我們就說是左外連接；右側(cè)的表完全顯示我們就是是右外連接。

語法：

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

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

例如查詢所有同學的成績，及同學的個人信息，如果該同學沒有成績，也需要顯示出來。

-- “老外學中文”同學沒有考試成績，也顯示出來了
select * from student stu left join score sco on stu.id=sco.student_id;
-- 對應的右外連接為：
select * from  score sco right join student stu on stu.id=sco.student_id;

-- 學生表、成績表、課程表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自連接

自連接是指在同一張表連接自身進行查詢。

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

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

-- 再查詢成績表中，"計算機原理"成績比"Java"成績好的信息
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 語句中的 select 語句，也叫嵌套查詢。

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

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

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

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

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

例如：查詢 “ 語文 ” 或 “ 英文 ” 課程的成績信息。

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

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

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

注意：

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

5.2.5合并查詢

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

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

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

6.總結(jié)

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

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

您可能感興趣的文章: