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

一.关系模型的简要概述

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

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

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

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

域：某个属性的取值范围

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

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

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

候选码的详细讲解

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

主码的详细讲解

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

外码的讲解

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

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

二.数据库的约束

2.1约束类型

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

  约束类型：

1. NOT  NULL ：限制列取值不为空。

1. DEFAULT： 给定列的默认值。
2. UNIQUE：限制列的取值不重复。
3. CHECK：限制列的取值范围。
4. PRIMARY KEY：指定本列为主码。
5. 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使用：

//统计班级收集的 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重合的部分

联表查询中on和where的区别：

1. on在内连接中可省略，外连接不可省略；
2. on在内连接中执行效果和外连接执行效果不同；
3. left join...on查询不能过滤掉左表中的数据，而内连接on查询可以过滤掉全局数据。
4. 外连接中on和where不同，on筛选笛卡尔积过滤条件，where筛选具体业务

 3.3.4自连接

自连接，顾名思义，在同一张表连接自身。

语法格式：select *from 表名 as t1,表名 as t2 where t1.id=t2.id [, ...]；

例如，查询分数表中，某个人一门成绩大于另一门的成绩

使用：select * from score as t1,score as t2 where t1.sid=t2.sid and t1.grade<t2.grade;

3.3.5子查询

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

分两种，一种单行子查询，另一种多行子查询

• 单行子查询

  返回一行记录的子查询

例如：查询与许仙同班的同学

• 多行子查询

返回多行记录的子查询

例如：查询Java和中国传统文学的分数

3.3.6合并查询

 在实际操作中，我们需要合并多了select的执行结果，因此我们需要使用集合操作符union，union all。

• union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

例如：查询学生表中大于22岁的男性信息

• union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

结：这个很鸡肋，几乎没有用处，了解就好。