Mysql约束与聚合查询,联合查询

最新推荐文章于 2024-09-30 17:19:22 发布

老皇甫

最新推荐文章于 2024-09-30 17:19:22 发布

阅读量735

点赞数 25

分类专栏： Mysql 文章标签：数据库 sql

本文链接：https://blog.csdn.net/m0_73233932/article/details/142185210

版权

Mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

写在前面：文中的sql语句是不能在mysql的黑框框里直接复制使用的

(一)数据库约束

数据库约束就是，对加入表中的数据进行限制，一般在创建表时就进行指定，这些限制是强制执行的，如果数据不满足这些约束，将不被允许插入数据库中，以此来保证库中数据的准确性和可靠性

（1）常见约束概览

not null ：表示此字段（列）中不能存在null值

unique:保证此字段（列）中的值每一个都是唯一的

default:为表中的列提供默认值

primary key:主键，notnull + unique结合体，不仅保证此列中数据唯一，还不能为空，一个表中只能有一个主键

foreign key ：使两表建立连接，将一个表中列的值和另一个表中主键或唯一键列的值相匹配

check: 保证列中的值符合指定的条件。

（2）null约束

创建一个students表，指定name列不能为空值

CREATE TABLE students (  
    id INT AUTO_INCREMENT,  
    name VARCHAR(100) NOT NULL,  
    age INT,  
    email VARCHAR(255) 
);

（3）唯一约束（unique）

创建一个students表，并指定email列是唯一的

CREATE TABLE students (  
    id INT AUTO_INCREMENT,  
    name VARCHAR(100),  
    age INT,  
    email VARCHAR(255) UNIQUE,  
   
);

(4)默认值约束（default）

创建一个学生表，指定当name列没有数据时，插入的默认值是未知

CREATE TABLE students (  
    id INT AUTO_INCREMENT,  
    name VARCHAR(100) DEFAULT '未知',  
    age INT,  
    email VARCHAR(255) UNIQUE,  
   
);

（5）主键约束(primary key)

创建一个学生表，设置id为主键，当插入数据没有id是按照表中最大值+1（auto_increment）

CREATE TABLE students (  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    name VARCHAR(100) DEFAULT '未知',  
    age INT,  
    email VARCHAR(255) UNIQUE, 
);

（6）外键约束(foreign key)

创建一个班级表，再创建一个学生表，其中班级表id为主键，学生表class_id为外键

班级表创建

CREATE TABLE classes (  
    id INT AUTO_INCREMENT,  
    class_name VARCHAR(100) NOT NULL,  
    description TEXT,  
    PRIMARY KEY (id)  
);

学生表创建

CREATE TABLE students (  
    id INT AUTO_INCREMENT,  
    name VARCHAR(100) NOT NULL,  
    age INT,  
    class_id INT,  
    PRIMARY KEY (id),  
    FOREIGN KEY (class_id) REFERENCES classes(id)  
);

（7）检查约束（check）

创建一个学生表，并检查年龄是否在0~100之间，和邮件格式是否正确

注意：以下情况check约束可能不会生效

1.数据库版本不支持

比如mysql5.7.8之前的版本是不支持check约束的

2.约束条件过于复杂

如果Check约束的条件过于复杂，涉及多个列或使用了复杂的函数，那么数据库可能会选择忽略该约束（摆烂），从而导致Check约束不起作用。

3.索引或外键约束冲突

如果Check约束的条件与表中的索引或外键约束存在冲突，数据库可能会选择忽略该Check约束。

CREATE TABLE students (  
    student_id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(100) NOT NULL,  
    age INT CHECK (age > 0 AND age <= 100),  
    email VARCHAR(100) CHECK (email LIKE '%@%.%')  
);

(二)新增

双表新增操作，将students表中的name,和age字段，复制到people表中

insert into people(student_id,name,age) select id,name,age from students;

（三）查询

（1）聚合查询

当需要对数据库中的数据进行统计和分析的时候，往往需要用到聚合查询，常见的统计总数，计算平局值等操作可以使用聚合函数来实现

1.1 聚合函数

下表是常见的聚合函数汇总

函数	说明
count()	返回查询到数据的数量
sum()	返回查询到数据的值的总和，必须是数字
avg()	返回查询到数据的平均值，必须是数字
max()	返回查询到数据的最大值，必须是数字
min()	返回查询到数据的最小值，必须是数字

1.1.1 count

计算students表中数据量的总和

select count(*) from students;

1.1.2 sum

计算students表中学生年龄的总和

select sum(age) from students;

1.1.3 avg

计算students表中学生年龄的平均值

select avg(age) from students;

1.1.4 max

计算students表中学生年龄的最大值

select max(age) from students;

1.1.5 min

计算students表中学生年龄的最小值

select min(age) from students;

1.2 group by

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

为方便讲述下面例子，我在这里又创建了一张成绩表，并将student_id作为外键将students表和成绩表连接起来。

 CREATE TABLE scores (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     student_id BIGINT UNSIGNED,
    ->     chinese DECIMAL(5, 2),
    ->     math DECIMAL(5, 2),
    ->     english DECIMAL(5, 2),
    ->
    ->     FOREIGN KEY (student_id) REFERENCES students(id)
    ->
    -> );

查询每个学生三门的平均成绩，并按照从大到小进行排序

select student_id, avg(chinese+math+english)/3 as avg from scores group by student_id order by avg desc;

看这里的结果很不舒服，毕竟谁会盯着学号看呢，一般都是看名字的，这时刚刚做的外键连接就起到了作用，这个可以放在下面联合查询中优化一下

1.3having

having主要和group by一起使用，用于过滤分组后的数据，因为where只能过滤分组之前的数据，而having正好弥补了分组后过滤的缺口(注意这里并没有说where 和group by不能同时存在，只是说where不能过滤分组后数据)

查询每个学生语数英的平均成绩并对平均成绩在80以上的进行保留

select student_id, avg(chinese+math+english)/3 as avg from scores group by student_id having avg>80 order by avg desc;

1.4一个易混淆的地方

如果你想要查找每个学生的语数英平均成绩，你可能会写成如下格式

select avg(chinese+math+english) from scores group by student_id;

我们来看结果

可以看到查询到的不是平均成绩，而是语数英三个成绩的总和，这是怎么回事呢？

这里的关键是理解avg() 函数的操作范围。在这个查询中,avg()是在每个student_id分组上操作（group by student_id），但它操作的是每个分组内所有行的chinese+math+english 结果的总和的平均值。然而，由于每个student_id只对应一行（每个学生在表中只有一条记录），因此这个“平均值”实际上就是每个学生的三门课成绩之和，而不是这三门课的平均成绩。