MySQL基础总结（2）-CSDN博客

本文介绍了MySQL中的UNION操作符、JOIN（INNERJOIN、LEFTJOIN、RIGHTJOIN）连接数据，以及事务的ACID特性，涵盖了ALTER命令、索引创建与管理等内容，为开发者提供基础数据库操作指导。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

上一次，我们了解 Mysql 的基本命令与常见的 SQL 语句，本次，我们将一起走进 SQL 的功能句与进阶概念中去。

Mysql 中的功能大师

union 操作符（连接去重）

注意： union 操作符必须由两个或多个 select 语句组成，每个 select 语句的列数和对应位置的数据类型必须相同。

select column1, column2, ...
from table1
where condition1
union
select column1, column2, ...
from table2
where condition2
[order by column1, column2, ...];

column1, column2, … 是你要选择的列的名称，如果使用 * 表示选择所有列。
table1, table2, … 是你要从中查询数据的表的名称。
condition1, condition2, … 是每个 SELECT 语句的过滤条件，是可选的。
ORDER BY 子句是一个可选的子句，用于指定合并后的结果集的排序顺序。

-- 基本操作
select city from sustomers union select city from supplies order by city;
-- 选择客户表和供应商表中所有城市的唯一值，并按城市名称升序排序

-- 使用过滤条件
select product_name from products where category = "electronics"
union
select product_name from products where category = "clothing"
order by product_name;
-- 选择电子产品和服装类别的产品名称，并按产品名称升序排序

-- 不去重
select city from customers 
union all
select city from suppliers
order by first_name;
-- 将客户表和供应商表中的所有城市合并在一起，不去除重复行

连接

在此之前，我们已经学会了如何对一张表进行操作，这是相对简单的，但是在实际应用中我们经常需要从多张表中读取数据。

接下来，我们将介绍如何使用 Mysql 中的 join 在两个及其以上的表去查询数据。

对 join 的划分，我们分为以下三类：

inner join （内连接/等值连接）：获取两个表中字段匹配关系的记录。
left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
right join（右连接）：与 left join 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

inner join

select column1, column2, ...
from table1
inner join table2 
on table1.column_name = table2.column_name;

column1 , column2, … 是你要选择的列的名称，如果使用 * 表示选择所有列。
table1, table2 是要连接的两个表的名称。
table1.column_name = table2.column_name 是连接条件，指定了两个表中用于匹配的列。

-- 普通的 inner join 语句
select orders.order_id, customers.order_name 
from orders 
inner join customers on orders.customer_id = customers.customer_id;
-- 选择 orders 表和 customers 表中满足连接条件的订单 ID 和客户名称

-- 使用表的别名
select o.order_id, c.order_name 
from orders as o
inner join customers as c on o.customer_id = c.customer_id;
-- 使用表别名 o 和 c 作为 orders 和 customers 表的别名

-- 多张表使用 inner join
select o.order_id, c.order_name 
from orders as o
inner join customers as c on o.customer_id = c.customer_id
inner join order_items as oi on o.order_id = oi.order_id
inner join products as p on oi.order_id = p.product_id;
-- 涉及了 orders、customers、order_items 和 products 四个表的连接。它选择了订单 ID、客户名称和产品名称，连接了这些表的关联列

-- 与 where 子句结合
select orders.order_id, customers.order_name 
from orders 
inner join customers on orders.customer_id = customers.customer_id
where orders.order_data >= '2024-01-01';
-- 过滤了订单日期在 '2023-01-01' 及以后的订单

left join

left join 返回左表的所有行，并包含右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值。

select column1, column2, ...
from table1
left join table2 on table1.column_name = table2.column_name;

-- 普通的 left join 语句
select customers.customer_id, customers.customer_name, orders.order_id
from customers 
left join orders on customers.customer_id = orders.order_id;
-- 选择客户表中的客户 ID 和客户名称，并包括左表 customers 中的所有行，以及匹配的订单 ID（如果有的话）

-- 使用表的别名
select c.customer_id, c.customer_name, o.order_id
from customers 
left join orders on c.customer_id = o.order_id;
-- 使用表别名 c 和 o 分别代替 customers 和 orders 表的名称

-- 多张表使用 left join
select o.order_id, c.order_name 
from customers as c
left join orders as o on o.customer_id = c.customer_id
left join order_items as oi on o.order_id = oi.order_id
left join products as p on oi.order_id = p.product_id;
-- 连接了 customers、orders、order_items 和 products 四个表，并选择了客户 ID、客户名称、订单 ID 和产品名称。左连接保证了即使在 order_items 或 products 中没有匹配的行，仍然会返回客户和订单的信息

-- 与 where 子句结合
select customers.customer_id, customers.customer_name, orders.order_id
from customers
left join orders on customers.customer_id = orders.customer_id
where orders.order_data >= '2024-01-01' or orders.order_id is null;
-- 过滤了订单日期在 '2023-01-01' 及以后的订单，以及没有匹配订单的客户

对于 right join 同理，返回右表的所有行，并包括左表中匹配的行，如果左表中没有匹配的行，将返回 NULL 值。

Mysql 中的事务

在 MySQL 中，事务是一组 SQL 语句的执行，它们被视为一个单独的工作单元。

事务主要适用于处理操作量巨大且复杂度高的数据。比如，工资的变化，人事部人员信息等。

一个事务具备以下特点（ACID）：

原子性（Atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如同原子一样是不可分割的。若事务在执行过程中发生错误，所有的修改被回滚，数据库回到事务开始前的状态，像什么都没发生一样。如果事务成功完成，则其修改被永久保存在数据库中。
一致性（Consistency）：一致性是指事务必须保证数据库的状态从一个一致状态转变为另一个一致状态。一致状态意味着数据库中的数据必须满足所有的完整性约束。
隔离性（Isolation）：隔离性是指并发的事务之间不会互相影响，如果事务并发地执行，其最终效果应该与那些事务串行执行时相同。每个事务都应该在尚未完成时，对其他事务的执行结果保持隔离。
持久性（Durability）：持久性是指一旦事务完成（即提交状态），其对数据库中数据的修改就会永久保存，即使在发生故障（如系统崩溃、断电等情况）时也能保证数据的安全性。

事务控制语句

BEGIN TRANSACTION 或 START TRANSACTION：这是开始一项新的事务，所有随后的 SQL 语句都将作为这项事务的一部分。
COMMIT：这是提交事务，即使它成为持久化的一部分。从这一点开始，你不可以回滚事务。
ROLLBACK：这是回滚事务，即取消所有未提交事务的修改。
SAVEPOINT savepoint_name：创建一个保存点，以便在事务中创建一个可以回滚的点。savepoint_name 是这个保存点指定的名字。
ROLLBACK TO savepoint_name：回滚到之前定义的保存点。savepoint_name 之前为保存点指定的名字。该语句只会回滚到指定的保存点，并不会结束事务。还可以在这个事务中做其他操作，然后决定是提交 (COMMIT) 还是整个事务都回滚 (ROLLBACK)。
RELEASE SAVEPOINT savepoint_name：删除之前定义的保存点。如果保存点之后的语句执行成功，可能会希望删除保存点。这并不会影响事务的状态，仍然可以选择是提交 (COMMIT) 还是回滚 (ROLLBACK) 这个事务。

事务处理的方法

用 BEGIN, ROLLBACK, COMMIT 来实现

BEGIN 或 START TRANSACTION：开用于开始一个事务。
ROLLBACK 事务回滚，取消之前的更改。
COMMIT：事务确认，提交事务，使更改永久生效。

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

BEGIN TRANSACTION;

-- 执行你的 SQL 查询语句
UPDATE table_name SET column1 = 'new_value1' WHERE condition;

-- 检查更新是否正确
SELECT column1 FROM table_name WHERE condition;

-- 如果一切都看起来不错，那么提交这个事务
COMMIT;

-- 如果有些地方出错了，那么回滚这个事务
ROLLBACK;

Mysql 中的 alter

alter 命令经常用在修改数据库，表和索引等对象的结构。

alter 允许添加，修改或者删除数据库对象，并且可以用于更改表的定义，添加约束，创建和删除索引等操作。

alter的常见操作：

添加列

alter table table_name
add column new_column datatype;

alter table students
add column birth_date date;
-- 在 students 表中添加了一个名为 birth_date 的日期列

修改列中的数据类型

alter table table_name
modify column column_name new_datatype;

alter table students
modify column is_active int;
-- 将 students 表中的 is_active 列的数据类型修改为 int

修改列名

alter table table_name
change column old_column_name new_column_name datatype;

alter table students
change column is_active is_actived int;
-- 将 students 表中的 is_active 列名修改为 is_actived，并且将其类型修改为 int

删除列

alter table table_name
drop column column_name;

alter table students
drop column birth_date;
-- 将 students 表中的 birth_date 列删除

添加 primary key

alter table table_name
add primary key (cloumn_name);

alter table students
add primary key (student_name);
-- 在 students 表中添加了一个主键

修改表名

alter table old_table_name
rename to new_table_name;

alter table employees
rename to staff;
-- 将表名由 employees 修改为 staff

Mysql 中的索引

MySQL 中的索引用于优化数据库检索性能。索引就像书籍的目录，通过索引可以快速定位到信息。

MySQL支持以下几种索引：

主键索引（PRIMARY KEY）：数据表中的主键。每个表有且仅有一个主键。主键值唯一，不能为空。
唯一索引（UNIQUE KEY）：类似于主键的索引，但一个表可以拥有多个唯一索引。唯一索引的值也必须是唯一的，但可以包含NULL值。
普通索引（INDEX or KEY）：最基本的索引，没有特殊限制。
全文索引（FULLTEXT）：针对大文本字段的索引。该索引能够在文本中查找关键字，而不仅仅是与索引完全匹配。

建表时创建

在建表时创建索引时，我们直接指定索引就可以了。

create table table_name (
  column1 data_type,
  column2 data_type,
  ...,
  index index_name (column1 [asc|desc], column2 [asc|desc], ...)
);

create table: 用于创建新表的关键字。
table_name: 指定要创建的表的名称。
(column1, column2, ...): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
index: 用于创建普通索引的关键字。
index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
(column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
asc 和 desc（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

create table users (
  id int not null,
  email varchar(255) not null,
  password varchar(255) not null,
  primary key (id),
  unique (email)
);

建表后创建

使用 CREATE INDEX 创建索引：

create index index_name
on table_name (column1 [asc|desc], column2 [asc|desc ...);

create index: 用于创建普通索引的关键字。
index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
table_name: 指定要在哪个表上创建索引。
(column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
asc 和 desc（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

create index idx_name on students (name);
-- 在 students 表的 name 列上创建一个名为 idx_name 的普通索引

修改/添加

数据库允许我们对索引进行修改/添加/删除。

我们可以使用 alter table 命令对表进行操作。

alter table 创建索引

alter table table_name
add index index_name (column1 [asc|desc], column2 [asc|desc], ...);

alter table: 用于修改表结构的关键字。
table_name: 指定要修改的表的名称。
add index: 添加索引的子句。add index用于创建普通索引。
index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
(column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

alter table employees
add index idx_age (age);
-- 在 employees 表的 age 列上创建一个名为 idx_age 的普通索引

删除索引

我们可以删除我们之前建立的索引。使用 drop index 删除索引。

drop index index_name on table_name;

drop index: 用于删除索引的关键字。
index_name: 指定要删除的索引的名称。
on table_name: 指定要在哪个表上删除索引。

alter table 删除索引

alter table table_name
drop index index_name;

alter table: 用于修改表结构的关键字。
table_name: 指定要修改的表的名称。
drop index: 用于删除索引的子句。
index_name: 指定要删除的索引的名称。

-- 有一个名为 employees 的表
-- 在 age 列上有一个名为 idx_age 的索引，现在要删除这个索引
drop index idx_age on employees;

-- 使用 alter table
alter table employees
drop index index_age;