MySQL数据库：数据库的约束以及数据的聚合、联合查询

最新推荐文章于 2023-07-24 13:19:30 发布

tq02

最新推荐文章于 2023-07-24 13:19:30 发布

阅读量4.8k

点赞数 83

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/m0_74097410/article/details/131292864

版权

MySQL 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

🎁个人主页：tq02的博客_CSDN博客-C语言,Java,Java数据结构领域博主
🎥 本文由 tq02 原创，首发于 CSDN🙉
🎄 本章讲解内容：MySQL的数据库的约束、数据的高效率查询

🎁欢迎各位→点赞👍 + 收藏⭐ + 评论📝+关注✨

注：在学习本文前，需要学会数据库的基本操作以及数据表中数据的增删查找

🤞数据库的基本操作学习链接：http://t.csdn.cn/LS3Dp

🤞数据表的数据增删查找学习链接：http://t.csdn.cn/78QIz

一.关系模型的简要概述

我们先来普及最基础的知识点-----关系模型的基本术语：

关系：一个关系对应一张表

属性：表中一列即为一个属性

元组：表中一行即为一个元组

域：某个属性的取值范围

候选码：关系中某个属性组的值能唯一标识一个元组，则该属性组为候选码。

主码：也称'主键'，当一个关系中有多个候选码，则可以选择任意一个为主码。

外码：也称'外键'，如果一个数据表中的一个属性是另一个数据表的主码，则为外码

候选码的详细讲解

例如上图，学号的值不会重复，每一个值可以标识一个元组。而姓名不可以，其中含有重复的值，也就是说候选码的条件是属性中没有重复的值。但从定义中知道这个是属性组，也就是说，一个表的候选码不唯一，而成绩的值也不存在重复，因此成绩也是候选码。

主码的详细讲解

就是从候选码中选择一个，作为主码。当然，选择主码应该要选择更合适的，例如学生成绩的表，如果再添加一组数据，那么成绩可能重复，所以为了安全起见，我们选择学号为主码

外码的讲解

从图中，我们可以看出，学生成绩表的候选码：学号，成绩。图书借阅表的候选码：图书编号

而当我们将学号作为学生成绩表的主码时，图书借阅表中的‘学号’属性便是学生成绩表的外码

二.数据库的约束

2.1约束类型

数据库的约束，其实约束的是 数据表结构，控制每一列的存储数值。

约束类型：

NOT NULL ：限制列取值不为空。
DEFAULT：给定列的默认值。
UNIQUE：限制列的取值不重复。
CHECK：限制列的取值范围。
PRIMARY KEY：指定本列为主码。
FREIGN KEY ：定义本列为引用其他表的外码。

2.2NULL约束

创建表时，可以指定某列不为空。

例如：创建一个学生表，并且指定学号不为空
CREATE TABLE student (
    id INT NOT NULL,
    sn INT,
    name VARCHAR(20),
    qq_mail VARCHAR(20)
);

2.3 UNIQUE：唯一约束

创建表时，指定某一列的值不能重复。

指定 sn 列不可重复。

CREATE TABLE student (
    id INT NOT NULL,
    sn INT UNIQUE,
    name VARCHAR(20),
    qq_mail VARCHAR(20)
);

2.4 默认约束

创建表时，当输入的某字段值为空，可赋 默认值。

例如：指定插入数据时，name列为空，默认值tq02
CREATE TABLE student (
    id INT NOT NULL,
    sn INT UNIQUE,
    name VARCHAR(20) DEFAULT 'tq02',
    qq_mail VARCHAR(20)
);

2.5 PRIMARY KEY：主键约束

创建表时，指定某列为主键。

注：对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1.

例如：指定 id 列为主键

CREATE TABLE student (
    id INT NOT NULL PRIMARY KEY,
    sn INT UNIQUE,
    name VARCHAR(20) DEFAULT 'unkown',
    qq_mail VARCHAR(20)
);

2.6 FOREIGN KEY：外键约束

创建表时，用于关联其他表的主码和唯一键

语法格式：foreign key (字段名) references 主表(列)

例子：先创建一个班级表，id为主码。

//创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
CREATE TABLE classes (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(20),
    `desc` VARCHAR(100)
);

再创建一个学生表student，一个学生对应一个班级，一个班级对应多个学生，使用id为主码，class_id为外键，关联班级表id

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
    CREATE TABLE student (
    id INT PRIMARY KEY auto_increment,
    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约束

约束某个列的取值范围

约束性别，只能为男和女。

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

三.高效率查询

3.1高效率查询的分类

高效率查询分为2种，聚合查询和联合查询

聚合查询：对数据表中的一个字段进行部分或者全部进行统计查询。

联合查询：合并多个相似的选择查询的结果，等同于将一个表追加到另一个表，从而实现将2个表的查询组合在一起。

3.2聚合查询

3.2.1聚合函数

聚合函数，用于统计总数、计算平局值等操作。

常见的聚合函数：

函数	意义
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

count使用：

//统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果
SELECT COUNT(qq_mail) FROM student;

sum使用：

-- 不及格 < 60 的总分，没有结果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

avg使用：

-- 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;

max使用：

-- 返回英语最高分
SELECT MAX(english) FROM exam_result;

min使用：

-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

3.2.2 GROUP BY子句

使用group by子句，可以指定列进行分组查询。

使用规定：select指定的字段必须是'分组依据字段'，其他字段若是想出现，则必须包含在聚合函数中。

语法格式: select 字段名1,聚合函数名(字段名2).....for table group by 字段名1，字段名2；

例子：

准备一个测试表，有id（主键）,name(姓名)，role(职位)，salary（薪水）

create table emp(
    id int not null 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
('奥特曼','服务员', 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;

看到这里的时候，我们会发现，原来聚合函数需要和group by组合一起使用才会有高效率啊。

3.2.3HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING

例如：显示平均工资低于1500的员工和他们的平均工资

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

3.3.联合查询

3.3.1联合查询的解析

联合联合，顾名思义，需要多表进行查询，而多表查询：对多张表的数据取笛卡尔积。

联合查询分为了5种：1.内连接 2.外连接 3.自连接 4.子连接 5.合并查询

为了更好的讲述联合查询，我们可以先建立几个数据表，并往其中添加数据。

1.建立班级表，往其中加入数据


create table classes(
    classes_id int not null , 
    descrtion  varchar(20) not null  primary key                  
);

insert into classes(classes_id, descrtion) values
(1, '学习了Java语言'),
(2,'学习了中国传统文学'),
(2,'学习了语文');

2.建立学生表，往其中添加数据

//创建学生表
CREATE TABLE student (
    sid varchar(20) not null  primary key ,
    name VARCHAR(20) comment '姓名',
    age INT comment '年龄',
    sex varchar(1) comment '性别',
    classes_id int comment '班级'
);

insert into student(sid, name, age, sex, classes_id) values
('09982','黑旋风李逵',18,'男',1),
('00835','菩提老祖',19,'女',1),
('00391','白素贞',22,'女',2),
('00031','许仙',25,'男',1),
('09527','孙悟空',300,'男',2);

3.建立课程表，往其中添加数据

create table course(
    name varchar(100) comment '课程名',
    course_id int comment '课程号'
);

insert into course(name,course_id) values
('Java',1),('中国传统文化',2),('语文',4),('高阶数学',5);

4.建立分数表，并往其中添加数据

create table score(
    grade int comment"分数",
    sid varchar(20) comment"学号",
     course_id  int comment "课程号"
);

insert into score(grade,sid,course_id) values(
-- 黑旋风李逵
(70, '09982', 1),
-- 菩提老祖
(60, '00835', 1),
--白素贞
(99, '00391', 2),(88, '00391',4),
-- 许仙
(67, '00031', 1),
-- 孙悟空
(81, '09527', 2),(37,'09527',4)
);

3.3.2内连接

内连接，也被称为自然连接，只有两个表相匹配的行才能在结果集中出现。返回的结果集选取了两个表中所有相匹配的数据，舍弃了不匹配的数据。

语法格式：

select *from 表1 join 表2 [ on过滤条件 ] [ where查询条件 ] ；
select *from 表1，表2 [ where查询条件 ] ；
select *from 表1 inner join 表2 [ on过滤条件 ] [ where查询条件 ] ；

注，经常使用1来进行多表连接

查询白素贞的总分时，使用：

select student.name,sum(score.grade) as sum from student join score on student.sid=score.sid where student.name='白素贞';

当查询白素贞的个人所有信息时，我们使用：

select * from student join score on student.sid=score.sid where student.name='白素贞';

3.3.3外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

1.左外连接，表1完全显示：

语法格式：select 字段名 from 表名1 left join 表名2 on 连接条件 [where条件查询];

注：表1 查询结果是所有数据，表2查询结果是与表1重合部分的数据。

2.右外连接，表2完全显示：

语法格式：select 字段 from 表名1 right join 表名2 on 连接条件 [where条件查询];

注：表2 查询的是所有数据，表 1查询结果是与表2重合的部分