MySQL 数据库约束、聚合查询和联合查询使用案例

文文看不惯

数据库约束


约束类型

数据库的约束类型主要包括以下几种：

• 主键约束（Primary Key Constraint）：确保表中的每一行都有唯一的标识，且不能为NULL。
  
• 外键约束（Foreign Key Constraint）：确保表中的数据与另一个表中的数据保持一致性，维护数据之间的关系。
  
• 唯一约束（Unique Constraint）：确保字段中的所有值都是唯一的，不允许重复。
  
• 检查约束（Check Constraint）：限制某一列中的值符合特定条件，如数值范围、字符串格式等。
  
• 非空约束（NOT NULL Constraint）：确保某一列不能包含NULL值，必须有实际数据。
  
• 默认约束（Default Constraint）：为字段设置默认值，在插入记录时若未提供该字段的值，则会使用默认值。
  

这些约束保证了数据库中的数据完整性和一致性，是设计数据库时的重要组成部分。


使用案例

主键约束（Primary Key Constraint）
主键约束用于唯一标识表中的每一行，并确保其值不为NULL。

1. CREATE TABLE Students (
   
2.     StudentID INT PRIMARY KEY,
   
3.     StudentName VARCHAR(20)
   
4. );

复制代码

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

1. StudentID INT PRIMARY KEY auto_increment,

复制代码

外键约束（Foreign Key Constraint）
外键约束用于维护不同表之间的关系，确保引用的数据存在。

1. CREATE TABLE Courses (
   
2.     CourseID INT PRIMARY KEY,
   
3.     CourseName VARCHAR(100)
   
4. );
   
5. CREATE TABLE Enrollments (
   
6.     EnrollmentID INT PRIMARY KEY,
   
7.     StudentID INT,
   
8.     CourseID INT,
   
9.     FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
   
10.     FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
    
11. );

复制代码

唯一约束（Unique Constraint）
唯一约束确保字段值的唯一性，不能重复，但允许NULL值。

1. CREATE TABLE Users (
   
2.     UserID INT PRIMARY KEY,
   
3.     Email VARCHAR(100) UNIQUE
   
4. );

复制代码

检查约束（Check Constraint）
检查约束用于确保字段值满足特定条件。

1. CREATE TABLE Products (
   
2.     ProductID INT PRIMARY KEY,
   
3.     Price DECIMAL(10, 2) CHECK (Price >= 0)
   
4. );

复制代码

非空约束（NOT NULL Constraint）
非空约束确保某一列的值不能为空。

1. CREATE TABLE Employees (
   
2.     EmployeeID INT PRIMARY KEY,
   
3.     EmployeeName VARCHAR(100) NOT NULL
   
4. );

复制代码

默认约束（Default Constraint）
默认值约束在插入新记录时指定列的默认值。

1. CREATE TABLE Orders (
   
2.     OrderID INT PRIMARY KEY,
   
3.     OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP
   
4. );

复制代码

这些约束在创建和管理数据库表时非常重要，有助于维护数据的完整性和准确性。

聚合查询


聚合函数

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

• COUNT()：计算行数或非NULL值的数量。
  
• SUM()：计算数值型列的总和。
  
• AVG()：计算数值型列的平均值。
  
• MAX()：返回指定列中的最大值。
  
• MIN()：返回指定列中的最小值。
  
• GROUP_CONCAT()（某些数据库）：将多行的值连接成一个字符串。
  
• VARIANCE()：计算数值型列的方差。
  
• STDDEV()：计算数值型列的标准差。
  

这些聚合函数用于对一组数据进行汇总和分析，是数据库查询的重要工具。
使用案例

COUNT()

1. COUNT()

复制代码

函数用于计算表中的行数或特定列中非NULL值的数量。
用法：

• 计算总行数：
  1. SELECT COUNT(*) FROM 表名;

  复制代码
• 计算某列非NULL值的数量：
  1. SELECT COUNT(列名) FROM 表名

  复制代码

示例：

1. SELECT COUNT(*) FROM Employees;  -- 计算员工总数
   
2. SELECT COUNT(EmployeeID) FROM Employees;  -- 计算非NULL的员工ID数量

复制代码

SUM()

1. SUM()

复制代码

函数用于计算数值型列的总和。
用法：

1. SELECT SUM(列名) FROM 表名;

复制代码

示例：

1. SELECT SUM(Salary) FROM Employees;  -- 计算所有员工的工资总和

复制代码

AVG()

1. AVG()

复制代码

函数用于计算数值型列的平均值。
用法：

1. SELECT AVG(列名) FROM 表名;

复制代码

示例：

1. SELECT AVG(Salary) FROM Employees;  -- 计算所有员工的平均工资

复制代码

MAX()

1. MAX()

复制代码

函数用于返回指定列中的最大值。
用法：

1. SELECT MAX(列名) FROM 表名;

复制代码

示例：

1. SELECT MAX(Salary) FROM Employees;  -- 找到最高的工资

复制代码

MIN()

1. MIN()

复制代码

函数用于返回指定列中的最小值。
用法：

1. SELECT MIN(列名) FROM 表名;

复制代码

示例：

1. SELECT MIN(Salary) FROM Employees;  -- 找到最低的工资

复制代码

这些聚合函数可以单独使用，也可以与

1. GROUP BY

复制代码

子句结合使用，以对结果进行分组和汇总分析。

GROUP BY子句

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

1. select column1, sum(column2), .. from table group by column1,column3;

复制代码

HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING。
使用案例
下面是一个结合聚合函数、

1. GROUP BY

复制代码

和

1. HAVING

复制代码

的使用案例。
假设我们有一个名为

1. Sales

复制代码

的表，其中包含以下字段：

• 1. SalesID

  复制代码
  ：销售记录的唯一标识
  
• 1. SalesAmount

  复制代码
  ：销售金额
  
• 1. SalesPerson

  复制代码
  ：销售人员的名称
  
• 1. SalesDate

  复制代码
  ：销售日期
  

我们想要查询每个销售人员的总销售金额和平均销售金额，并且只返回那些总销售金额超过 10,000 的销售人员。
以下是相应的 SQL 查询示例：

1. SELECT
   
2.     SalesPerson,
   
3.     SUM(SalesAmount) AS TotalSales,
   
4.     AVG(SalesAmount) AS AverageSales
   
5. FROM
   
6.     Sales
   
7. GROUP BY
   
8.     SalesPerson
   
9. HAVING
   
10.     SUM(SalesAmount) > 10000;

复制代码

在这个查询中：

• 1. GROUP BY SalesPerson

  复制代码
  将结果按销售人员进行分组。
  
• 1. SUM(SalesAmount)

  复制代码
  计算每个销售人员的总销售金额。
  
• 1. AVG(SalesAmount)

  复制代码
  计算每个销售人员的平均销售金额。
  
• 1. HAVING SUM(SalesAmount) > 10000

  复制代码
  筛选出总销售金额超过 10,000 的销售人员。
  

这个案例展示了如何结合聚合函数和

1. GROUP BY

复制代码

与

1. HAVING

复制代码

条件来分析数据。

联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

注意：关联查询可以对关联表使用别名。

内连接

1. select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

复制代码

外连接

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

1. -- 左外连接，表1完全显示select 字段名  from 表名1 left join 表名2 on 连接条件;-- 右外连接，表2完全显示select 字段 from 表名1 right join 表名2 on 连接条件;

复制代码

自连接

自连接是指在同一张表连接自身进行查询。

1. select 字段名 from 表1 as 别名 ，表1 as 别名 where 连接条件 and 其他条件；

复制代码

联合查询使用案例

下面是一个关于数据库中联合查询（也称为联接查询）的案例，结合多个表进行数据检索。
假设我们有两个表：
Customers 表

• 1. CustomerID

  复制代码
  ：客户唯一标识
  
• 1. CustomerName

  复制代码
  ：客户姓名
  
• 1. ContactNumber

  复制代码
  ：联系方式
  

Orders 表

• 1. OrderID

  复制代码
  ：订单唯一标识
  
• 1. OrderDate

  复制代码
  ：订单日期
  
• 1. CustomerID

  复制代码
  ：关联的客户ID（外键）
  
• 1. TotalAmount

  复制代码
  ：订单总金额
  

我们希望查询每个客户的订单信息，包括客户姓名和订单总金额。
以下是结合

1. INNER JOIN

复制代码

的 SQL 查询示例：

1. SELECT
   
2.     Customers.CustomerName,
   
3.     Orders.TotalAmount
   
4. FROM
   
5.     Customers
   
6. INNER JOIN
   
7.     Orders ON Customers.CustomerID = Orders.CustomerID;

复制代码

在这个查询中：

• 1. INNER JOIN

  复制代码
  用于将
  1. Customers

  复制代码
  表和
  1. Orders

  复制代码
  表连接起来。
  
• 连接条件是
  1. ON Customers.CustomerID = Orders.CustomerID

  复制代码
  ，即通过客户ID来匹配订单和客户信息。
  
• 选择了
  1. Customers.CustomerName

  复制代码
  和
  1. Orders.TotalAmount

  复制代码
  来显示客户姓名和相应的订单总金额。
  

这个案例展示了如何在数据库中使用联合查询来获取来自多个表的相关数据。

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
1、单行子查询：返回一行记录的子查询
2、多行子查询：返回多行记录的子查询

• [NOT] IN关键字
  
• [NOT] EXISTS关键字
  

子查询案例

下面是一个关于数据库中子查询的案例，展示如何使用子查询来获取相关数据。
假设我们有两个表：
Employees 表

• 1. EmployeeID

  复制代码
  ：员工唯一标识
  
• 1. EmployeeName

  复制代码
  ：员工姓名
  
• 1. DepartmentID

  复制代码
  ：员工所在部门的ID
  
• 1. Salary

  复制代码
  ：员工工资
  

Departments 表

• 1. DepartmentID

  复制代码
  ：部门唯一标识
  
• 1. DepartmentName

  复制代码
  ：部门名称
  

现在，我们想要查询那些工资高于该部门平均工资的员工姓名和工资。我们可以通过子查询来实现这个目标。
以下是相应的 SQL 查询示例：

1. SELECT
   
2.     EmployeeName,
   
3.     Salary
   
4. FROM
   
5.     Employees
   
6. WHERE
   
7.     Salary > (
   
8.         SELECT
   
9.             AVG(Salary)
   
10.         FROM
    
11.             Employees
    
12.         WHERE
    
13.             DepartmentID = Employees.DepartmentID
    
14.     );

复制代码

在这个查询中：

• 外层查询从
  1. Employees

  复制代码
  表中选择
  1. EmployeeName

  复制代码
  和
  1. Salary

  复制代码
  。
  
• 子查询
  1. (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = Employees.DepartmentID)

  复制代码
  计算每个部门的平均工资。
  
• 外层查询的
  1. WHERE

  复制代码
  子句中，条件
  1. Salary > (子查询)

  复制代码
  用于过滤出工资高于该部门平均工资的员工。
  

这个案例展示了如何在数据库中使用子查询来进一步筛选和获取数据。

合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。
union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

合并查询案例

合并查询通常是指使用

1. UNION

复制代码

运算符将多个 SELECT 查询的结果合并在一起。下面是一个关于数据库中合并查询的案例。
假设我们有两个表：
Customers 表

• 1. CustomerID

  复制代码
  ：客户唯一标识
  
• 1. CustomerName

  复制代码
  ：客户姓名
  
• 1. ContactNumber

  复制代码
  ：联系方式
  

Suppliers 表

• 1. SupplierID

  复制代码
  ：供应商唯一标识
  
• 1. SupplierName

  复制代码
  ：供应商姓名
  
• 1. ContactNumber

  复制代码
  ：联系方式
  

我们希望从这两个表中获取所有联系人姓名，无论是客户还是供应商。可以使用

1. UNION

复制代码

查询来合并两个表中的联系人的姓名。
以下是相应的 SQL 查询示例：

1. SELECT
   
2.     CustomerName AS ContactName
   
3. FROM
   
4.     Customers
   
5. UNION
   
6. SELECT
   
7.     SupplierName AS ContactName
   
8. FROM
   
9.     Suppliers;

复制代码

在这个查询中：

• 第一个
  1. SELECT

  复制代码
  查询从
  1. Customers

  复制代码
  表中选取
  1. CustomerName

  复制代码
  ，并将其重命名为
  1. ContactName

  复制代码
  。
  
• 第二个
  1. SELECT

  复制代码
  查询从
  1. Suppliers

  复制代码
  表中选取
  1. SupplierName

  复制代码
  ，同样将其重命名为
  1. ContactName

  复制代码
  。
  
• 1. UNION

  复制代码
  将两个查询的结果合并在一起，自动去除重复的值。
  

请注意，使用

1. UNION

复制代码

时，两个查询的列数和数据类型必须相匹配。
这个案例展示了如何在数据库中使用合并查询来获取来自多个表的相关数据。
到此这篇关于MySQL 数据库约束、聚合查询和联合查询的文章就介绍到这了,更多相关mysql聚合查询和联合查询内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/database/326564alt.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！