MySQL的基础操作(二)

目录

一.数据库约束

1.主键约束 (Primary Key)

2.唯一约束 (Unique)

3.外键约束 (Foreign Key):

4.检查约束(Check)

5.默认约束 (Default)

二.聚合查询

1.简单聚合函数

2.GROUP BY子句

3.HAVING子句

三.联合查询

1.内连接

2.左连接

3.右连接

4.子查询

5.合并查询

---

一.数据库约束

数据库约束是用于保证数据完整性和一致性的重要机制，它可以应用在表的列级别或表级别。以下是常见的数据库约束类型：

1.主键约束 (Primary Key)

• 主键约束用于唯一标识表中的每一行数据。
• 每个表只能有一个主键。
• 主键列不允许包含NULL值。
• 主键可以是单列或多列的组合

示例:

CREATE TABLE STUDENT (
     id INT PRIMARY KEY,
     name VARCHAR(20),
     qq_mail VARCHAR(20)
);
//设定学生表中的id字段为外键

2.唯一约束 (Unique)

• 唯一约束确保列中的数据是唯一的，但允许NULL值。
• 每个表可以有多个唯一约束。
• 可以在单列或多列上定义唯一约束。

示例:

CREATE TABLE student (
   id INT UNIQUE,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
)
//将学生表中的id字段设置为unique,表示为唯一的、不重复的

3.外键约束 (Foreign Key):

• 外键约束用于建立表之间的关联关系。
• 它指定一个或多个列作为外键，这些外键值必须存在于另一个表的主键或唯一约束中。
• 外键可以防止对父表的引用数据进行意外的更改或删除。

语法：

foreign key (字段名) references 主表(列)

create table class(
   id int primary key,
   class_name varchar(20),
);
//先创建一个班级表class，使id为主键
 
create table class(
   id int primary key,
   name varchar(20),
   classes_id int,
   foreign key (classes_id) references class(id)
);
//再创建一个学生表,使用id为主键，classes_id为外键，关联班级表id

4.检查约束(Check)

• 检查约束用于限制列中允许的值的范围。
• 可以基于表达式或条件来定义检查约束，如果条件为假，则不允许插入或更新数据。

示例:

CREATE TABLE Grades (
   StudentID INT, 
   Score INT CHECK (Score >= 0 AND Score <= 100)
);
//在一个学生成绩表中，使用检查约束确保分数在0到100之间

5.默认约束 (Default)

• 默认约束定义了列在没有明确指定值时应该使用的默认值。
• 当插入新行或更新行时，如果未提供值，则数据库将使用默认值。

示例:

CREATE TABLE Employees (
   EmployeeID INT, 
   Name VARCHAR(50), 
   HireDate DATE DEFAULT CURRENT_DATE
);
//在一个员工表中，将入职日期设置为当前日期作为默认值

6.非空约束 (Not Null):

• 非空约束确保列中的值不为空值 (NULL)。
• 当尝试插入或更新行时，如果指定了该列，并且值为NULL，则会产生错误。

示例:

CREATE TABLE Users (
   UserID INT, 
   Username VARCHAR(50) NOT NULL
);
//在一个用户表中，确保用户名不为空

---

二.聚合查询

1.简单聚合函数

• COUNT(): 计算某列的行数，或者满足特定条件的行数。
• SUM(): 计算指定列的总和。
• AVG(): 计算指定列的平均值。
• MIN(): 计算指定列的最小值。
• MAX(): 计算指定列的最大值。

示例:

select avg(math) from student;
//计算学生表数学成绩的平均值
 
select sum(math) from student;
//计算学生表数学成绩的总和
 
select max(math) from student;
//计算学生表数学成绩的最大值

2.GROUP BY子句

将结果集按照指定列进行分组，并对每个分组应用聚合函数。

语法:

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

示例:

create table student (
   id int,
   name varchar(20),
   grade varchar(5),
   math decimal(3,1),
   chinese decimal(3,1),
   english decimal(3,1)
);
//先创建一个学生表
 
select grade 年级,max(math+chinese+english) 总分 from student group by grage;
//查询每个年级的最高分并以年级分组
(有多少年级就分成多少组,每一个组当成单独的一张表并查询其中的最高分)  

3.HAVING子句

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

示例:

create table student (
   id int,
   name varchar(20),
   grade varchar(5),
   math decimal(3,1),
   chinese decimal(3,1),
   english decimal(3,1)
);
//先创建一个学生表
 
select grade 年级, count(*) 总人数 
   from student 
   group by grade 
   having count(*)>200;
//查找总人数超过200的年级  

三.联合查询

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

在介绍之前,先创建好要示例的表:

create table student (
    -> id int ,
    -> name varchar(30),
    -> e_mail varchar(30),
    -> class_id int
    -> );
//创建学生表用于储存学生个人信息
 create table grade(
    -> course varchar(20),
    -> student_id int,
    -> score int
    -> );
//创建成绩表用于储存学生的各科成绩

填充数据后为

1.内连接

• 内连接返回两个表中匹配连接条件的行，如果某行在其中一个表中没有匹配的行，则不会包含在结果集中。
• 内连接使用的语法是 INNER JOIN 或简写形式 JOIN。
• 内连接只返回两个表中连接条件匹配的行，不会返回任何一个表中没有匹配的行。

语法:

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
 
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
 
//两种写法都可以

示例:

select 
   student.name, 
   grade.course, 
   grade.score 
from 
   student,
   grade 
where 
   student.id=grade.student_id;
 
//查询学生的姓名与对应的课程名称和成绩
//注意限定条件是学生表的id与成绩表的student_id相等

结果为:

2.左连接

• 左连接返回左表中的所有行，以及右表中匹配连接条件的行。如果右表中没有匹配的行，则返回 NULL 值。
• 左连接使用的语法是 LEFT JOIN。
• 左连接会保留左表中没有匹配的行，并在右表中没有匹配的行处填充 NULL 值。

语法:

select 字段名  from 表名1 left join 表名2 on 连接条件;

示例:

我们先往学生表添加一个新的学生小何,但是成绩表没有添加对应的成绩

insert into
   student (name,id,class_id) 
values 
   ('小何',7,2);

接着查询学生表左连接成绩表

select 
   student.name, 
   grade.course, 
   grade.score 
from 
   student left join grade 
on
   student.id=grade.student_id;

结果为:

可以看到,小何明明没有成绩也出现在结果中了,这就是左连接保留左表多余的数据

3.右连接

• 右连接与左连接相反，返回右表中的所有行，以及左表中匹配连接条件的行。如果左表中没有匹配的行，则返回 NULL 值。
• 右连接使用的语法是 RIGHT JOIN。
• 右连接会保留右表中没有匹配的行，并在左表中没有匹配的行处填充 NULL 值。

语法:

select 字段 from 表名1 right join 表名2 on 连接条件;

示例:

与上方一样添加学生小何,但是查询成绩表右连接学生表

select
   student.name, grade.course, grade.score 
from 
   grade right join student 
on 
   student.id=grade.student_id;

结果为:

可以看到,将两表调换后,小何明明没有成绩也出现在结果中了,这就是右连接保留右表多余的数据

4.子查询

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

示例:

如果现在想寻找与小明同班的同学

select 
   * 
from 
   student 
where 
   class_id=(select class_id from student where name='小明');

结果为:

5.合并查询

MySQL中的合并查询指的是使用UNION或UNION ALL操作符将多个SELECT语句的结果合并成一个结果集。合并查询通常用于将多个相似的查询结果合并在一起，以便在单个结果集中检索数据

UNION操作符：

UNION操作符用于合并多个SELECT语句的结果，并去除重复的行。

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

UNION ALL操作符：

UNION ALL操作符用于合并多个SELECT语句的结果，包括重复的行。

SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

---