全面解析MySQL(3)——CRUD进阶&数据库约束

1.插入查询结果

MySQL中insert into ... select ...语句用于将select的查询结果插入到目标表中。一般用于数据的迁移或备份

注意：

• 1.插入语句不使用value关键字
• 2.确保目标表和查询结果的列数、数据类型匹配
  

2.常用聚合函数

MySQL聚合函数用于对一组值执行计算并返回单个值，常用于数据统计和分析。以下是常见的聚合函数及其用法

2.1 count

• 描述：计算指定列或所有列的行数。常用于统计记录数量
• 语法示例：统计exam表中有多少条记录

注意：

• 统计某一列的行数时，不统计null所在的行；统计所有列的行数时，null所在的行也会被统计

2.2 sum

描述：计算数值列的总和。如果列中包含null值，则忽略它们
语法示例：统计英语成绩的总分

注意：如果尝试对非数值列使用sum函数，MySQL会根据列的数据类型采取不同的处理方式

• 字符串类型(如varchar,char, text)：MySQL会尝试将字符串转换为数值。如果字符串以数字开头，MySQL会将其转换为对应的数值；否则，转换为0
  • 123abc转换为123
  • abc123转换为0
• 日期和时间类型(如date,datetime,timestamp)：MySQL会将日期和时间转换为自1970-01-01以来的秒数
• 布尔类型：实际上是tinyint(1)的别名，true为1，false为0
• 混合类型：如果列中包含多种数据类型，MySQL会尝试将所有值转换为数值，遵循上述规则

2.3 avg

描述：计算数值列的平均值(平均值 = 总和 / 非null值的个数
计算公式：avg(colunm) = sum(colunm) / count(colunm))
语法示例：统计英语成绩的平均分

2.4 min

描述：返回指定列的最小值。适用于数值、日期或字符串类型
语法示例1(数值)：查看英语成绩的最低分
语法示例2(字符串)
语法示例3(日期)注意：MySQL对字符的排序是基于字符集的校对规则(collation)，所以min()函数对于汉字的计算结果与数据库的校对规则有关

2.5 max

描述：返回指定列的最大值。适用于数值、日期或字符串类型(计算规则和min()函数同理)
语法示例：查看英语成绩的最高分

3.分组查询(group by)

3.1 概念

分组查询(group by)：用于将查询结果按照一个或多个列进行分组，通常与聚合函数(如count、sum、avg等）结合使用，对每个分组进行统计计算
基础语法：select 列名 from 表名 where 条件 group by 列名

3.2 语法示例

语法示例：统计每种角色的人数

注意1：使用分组查询时，select子句中只能包含聚合列或聚合函数

• 聚合列：出现在group by子句中的列
• 非聚合列：没有出现在group by子句中的列

在上述从查询中，我们按照role进行1分组，但是select *意味着我们选择了所有列。然而，除了role列，其他列并没有出现在group by子句中，也没有被聚合函数包裹。因此，MySQL不知道该如何显示这些非聚合列的值。例如：按照role分组后，在老师这个组里，name那一列是显示(张三，李四，王五，赵六)中的哪一个？

注意2：别名问题

• 使用group by进行分组查询时，可以使用select子句中的别名，但是别名不能为字符串常量(比如汉字)
  
• 使用order by进行排序时，可以使用select子句中的别名，但是别名不能为字符串常量(比如汉字)
  
• 使用where进行条件查询时，不能使用select子句中的别名

问题：为什么order by和group by子句中可以使用select子句中的别名，而where不行？

question：这和sql语句的执行顺序有关 sql语句的执行顺序不是按书写顺序(如 select → from → where)，而是按以下逻辑顺序：

• 1. from 和 join(确定数据源，join后面联合查询再讲)
• 2. where(过滤行)
• 3. group by(分组)
• 4. having(过滤分组，等会就讲)
• 5. select(选择列并计算表达式/别名)
• 6. order by(排序)
• 7. limit(限制行数)

where在select之前执行，所以where中不认识select中定义的别名；order by在select之后执行，所以order by可以使用select中定义的别名；group by在select之前执行，但仍能使用select中的别名，这是因为MySQL做的优化(也就是一些特殊手段)

3.3 having

having：用于对分组后的结果进行筛选 where：用于对分组之前的结果进行筛选
示例：显示平均工资低于1500的角色和它的平均工资

4.数据库约束

4.1 概述

数据库约束：是数据库管理系统中用于确保数据完整性、一致性和有效性的规则集合。它们定义了数据在存储和操作过程中必须满足的条件，从而防止无效或不一致的数据进入数据库。常见的约束包括：非空约束、默认值约束、唯一约束、主键约束、外键约束、检查约束等

4.2 非空约束

非空约束(Not Null Constraint)：防止列中出现空值(null)，强制该列必须有有效数据(这常用于关键属性列)

#创建表
create table demo_not_null (id int not null,name varchar(20),age int);

4.3 默认值约束

默认值约束(Default Constraint)：为列提供默认值，当插入数据时未指定该列值时使用

#创建表
create table demo_default (id int not null,name varchar(20),age int default 15);

4.4 唯一约束

唯一约束(Unique Constraint)：保证列或列组合的值在表中唯一，但允许空值(除非与非空约束结合)。与主键不同，一张数据表中唯一约束可以有多个
注意：

#创建表
create table demo_unique (id int unique,name varchar(20),age int default 16);

4.5 主键约束

主键约束(Primary Key Constraint)：确保表中每行记录的唯一标识，防止重复或空值(相当于非空约束+唯一约束)。每个表只能有一个主键

#创建表
create table demo_primary_key (id int primary key,name varchar(20),age int default 16);

自增主键(Auto-increment Primary Key)：是数据库表中一种特殊的主键，其值由数据库系统自动递增生成，通常用于确保每条记录的唯一性

#创建表
create table demo_primary_key_auto (id int primary key auto_increment,name varchar(20),age int default 16);

4.6 外键约束

外键约束(Foreign Key Constraint)：维护表之间的引用完整性，确保一个表(子表)的列值引用另一个表(父表)的主键值。换言之，确保子表中的值必须在父表中存在，这防止了无效的引用或孤立记录

#创建父表
create table class (class_id int primary key auto_increment,name varchar(20));
#创建子表
create table student (student_id int primary key auto_increment,name varchar(20),
class_id int,foreign key (class_id) references class(class_id));

注意：如果上述class表和student表没有建立外键约束，虽然(4,'赵六',4)能插入成功，但在class表中没有对应的班级，所以这是一条无效的数据。外键约束防止了这种无效的记录插入

4.7 检查约束

检查约束 (Check Constraint)：定义列值必须满足的条件表达式，通常涉及逻辑或数学条件，如范围检查或格式验证

#创建表
#check约束：age大于17
create table demo_check (id int,name varchar(20),age int check (age > 17));