SQL学习-数据库高级操作

牛奶
329 阅读9分钟

SQL学习-数据库高级操作

本文主要介绍下 MYSQL 数据库方面的操作，包括数据库的创建、修改、删除、备份与恢复、数据库视图、使用存储过程等操作。

供自己以后查漏补缺，也欢迎同道朋友交流学习。

引言

前俩篇文章主要是基于 MYSQL 介绍数据库表的 CRUD 操作，并没有对数据库进行过多介绍。

本篇文章就主要介绍下 MYSQL 数据库方面的操作，包括数据库的创建、修改、删除、备份与恢复、数据库视图、使用存储过程等操作。

创建数据库

使用CREATE DATABASE语句

创建数据库的最基本语法是使用 CREATE DATABASE 语句，语法如下:

-- database_name 是要创建的数据库名称
CREATE DATABASE database_name;

指定字符集和校对规则

在创建数据库时，可以选择指定字符集和校对规则，这对于支持多语言环境非常重要，语法如下:

CREATE DATABASE database_name
CHARACTER SET charset_name
COLLATE collation_name;

charset_name 是字符集名称，collation_name 是校对规则名称。

指定数据库存储引擎

MySQL 支持多种存储引擎，如 InnoDB、MyISAM 等。可以在创建数据库时指定默认的存储引擎：

CREATE DATABASE database_name
DEFAULT CHARACTER SET charset_name
DEFAULT COLLATE collation_name
ENGINE = InnoDB;

创建数据库时的其他选项

  • 指定数据库的注释：可以为数据库添加注释，这在数据库较多时有助于识别和分类数据库。
  • 指定数据库的位置：在某些情况下可能需要指定数据库文件的存储位置。

验证数据库创建

创建数据库后，可以使用 SHOW DATABASES; 语句来查看所有数据库，确认数据库是否已经成功创建：

SHOW DATABASES LIKE 'database_name';

使用（进入）数据库

在创建数据库后，使用USE语句来选择（或称为“进入”）数据库，以便在该数据库中执行后续的 SQL 语句：

USE database_name;

修改数据库

在 MySQL 中，修改数据库通常指的是修改数据库的属性，如字符集、校对规则、默认存储引擎等。

修改数据库的字符集和校对规则

可以使用 ALTER DATABASE 语句来修改数据库的字符集和校对规则。

ALTER DATABASE database_name
CHARACTER SET charset_name
COLLATE collation_name;

修改数据库的默认存储引擎

修改数据库的默认存储引擎，可以使用以下语句：

ALTER DATABASE database_name
DEFAULT CHARACTER SET charset_name
DEFAULT COLLATE collation_name;

修改数据库注释

ALTER DATABASE mydatabase
COMMENT='这是我的数据库的新注释';

修改数据库的其他属性

MySQL 还允许修改数据库的其他属性，例如表的空间大小限制等。这些操作通常较为复杂，需要根据具体需求来定制。

删除数据库

删除数据库是一个需要谨慎处理的操作，因为它会永久移除数据库及其包含的所有数据和结构。

使用DROP DATABASE语句

删除数据库的基本语法是使用 DROP DATABASE 语句。

DROP DATABASE database_name;

删除数据库时的注意事项

  • 权限：确保有足够的权限来删除数据库。需要具有数据库的 DROP 权限。
  • 事务：删除数据库是一个不可逆的操作，一旦执行，所有数据将永久丢失。
  • 影响：考虑删除数据库对业务的影响，确保所有相关人员都已通知并同意。

备份与恢复

数据库备份与恢复是数据库管理中的重要环节，它们确保了数据的安全性和可靠性。

备份数据库

使用mysqldump工具

mysqldump是 MySQL 提供的一个强大的命令行工具，用于创建数据库的逻辑备份。

基本语法：

mysqldump -u username -p database_name > backup_file.sql
  • username 是数据库用户名。
  • database_name 是要备份的数据库名称。
  • backup_file.sql 是备份文件的名称。

备份整个数据库实例：

mysqldump -u username -p --all-databases > all_db_backup.sql

备份多个数据库：

mysqldump -u username -p --databases db1 db2 db3 > multiple_db_backup.sql
备份选项

  • 压缩备份：使用--single-transaction选项来减少锁定时间，并在备份期间压缩输出。

    mysqldump -u username -p --single-transaction --quick --compress database_name | gzip > backup_file.sql.gz

  • 排除某些数据库：使用--exclude-db选项排除特定数据库。

    mysqldump -u username -p --all-databases --exclude-db=db_to_exclude > all_db_backup.sql
增量备份

MySQL 的逻辑备份不支持直接的增量备份，但可以通过以下方法实现：

  • 二进制日志（Binary Log）：开启 MySQL 的二进制日志功能，并使用 mysqlbinlog 工具来处理增量备份。
  • Percona XtraBackup：这是一个开源的热备份工具，支持 InnoDB 存储引擎的增量备份。

恢复数据库

使用mysql命令

恢复数据库通常使用mysql命令将备份文件导入到数据库中。

基本语法：

mysql -u username -p database_name < backup_file.sql

恢复压缩备份：

gunzip < backup_file.sql.gz | mysql -u username -p database_name
恢复选项

在恢复过程中，可能需要禁用外键检查以避免错误。

mysql -u username -p database_name < backup_file.sql

在恢复完成后，重新启用外键检查并修复任何问题。

验证备份

在恢复备份之前，验证备份文件的完整性是非常重要的。可以使用以下命令检查备份文件：

mysqldump -u username -p --host=localhost --databases db1 db2 test < backup_file.sql

这将检查备份文件是否完整且无错误。

备份策略

  • 定期备份：根据数据的重要性和变化频率，制定定期备份计划。
  • 测试恢复：定期测试备份文件的恢复过程，确保在需要时能够成功恢复数据。
  • 多地点存储：将备份文件存储在多个地点，以防数据丢失。

数据库视图

数据库视图（View）是一种虚拟表，其内容由 SQL 查询定义。视图可以包含多个表和复杂的 SQL 语句，它们对于简化复杂的查询、保护数据和提供额外的数据安全非常有用。

创建视图

基本语法：

创建视图的语法如下：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

我们使用上一篇文章的产品表来做示例：

-- 创建视图
CREATE VIEW product_category_view AS
SELECT p.product_name, c.category_name
FROM products p, categories c
WHERE p.category_id = c.id;

查询视图

视图可以像普通表一样被查询：

-- 查询视图
SELECT * FROM product_category_view;

执行结果如下：

sql-operate-1

修改视图

替换视图

可以使用 CREATE OR REPLACE VIEW 语句来替换现有的视图定义：

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
改变视图定义

如果需要修改视图的定义，可以使用 ALTER VIEW 语句：

ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

删除视图

删除视图的语法如下：

DROP VIEW view_name;

视图的优点

  • 简化复杂的查询：视图可以将复杂的查询简化为一个单一的实体。
  • 提供逻辑上的独立性：视图可以隐藏表的复杂性和变化，对用户透明。
  • 增强安全性：通过限制对基表的直接访问，视图可以提供额外的安全层。

视图的限制

  • 更新限制：视图可能不支持某些更新操作，特别是包含聚合函数、DISTINCT、GROUP BY等的视图。
  • 性能：视图可能会影响查询性能，尤其是在视图定义复杂或包含多个表的情况下。
  • 权限：视图继承基表的权限，但有时可能需要额外的权限设置。

使用存储过程

存储过程是一组为了执行特定任务而预编译的 SQL 语句。它们存储在数据库中，可以通过调用存储过程的名称来执行。

创建存储过程

存储过程是一组为了执行特定任务而预编译的 SQL 语句。创建存储过程的语法如下：

基本语法：

创建存储过程的基本语法如下：

DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
  -- SQL statements
END //

DELIMITER ;

procedure_name 是存储过程的名称，BEGIN 和 END 之间的部分是存储过程的主体。

我们以上一篇文章的产品表来做示例：

-- 创建存储过程
DELIMITER //

CREATE PROCEDURE GetProductDetails(IN productID INT)
BEGIN
  SELECT * FROM products WHERE id = productID;
END //

DELIMITER ;

具体显示如下：

sql-operate-2

调用存储过程

调用存储过程的基本语法如下：

CALL procedure_name(param1, param2, ...);

例如：

-- 通过ID查询详情
CALL GetProductDetails(1);

返回：

sql-operate-3

参数化存储过程

输入参数

输入参数允许在调用存储过程时传递值给存储过程。

输出参数

输出参数允许存储过程返回值给调用者。

DELIMITER //

CREATE PROCEDURE GetProductsCount(OUT count INT)
BEGIN
  SELECT COUNT(*) INTO count FROM products;
END //

DELIMITER ;

-- 查询产品数量
CALL GetProductsCount(@productCount);
SELECT @productCount;

具体显示如下：

sql-operate-4

本地变量

本地变量用于存储过程内部的计算。

DELIMITER //

CREATE PROCEDURE CalculateProductsStocks()
BEGIN
  DECLARE total INT(0);
  SELECT SUM(stock) INTO total FROM products;
  SELECT total;
END //

DELIMITER ;

-- 计算商品库存
CALL CalculateProductsStocks();

具体显示如下：

sql-operate-5

控制结构

  • 条件语句：存储过程中可以使用 IF 语句来处理条件逻辑。
  • 循环语句：存储过程中可以使用 WHILE 或 LOOP 语句来执行循环操作。

错误处理

可以在存储过程中声明错误处理器来处理特定的错误。

DELIMITER //

CREATE PROCEDURE ProcessData()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Error handling code
    ROLLBACK;
  END;

  -- SQL statements
END //

DELIMITER ;

删除存储过程

DROP PROCEDURE procedure_name;

存储过程的优点

  • 性能：存储过程是预编译的，因此执行速度比单个 SQL 语句快。
  • 重用性：存储过程可以被多次调用，提高了代码的重用性。
  • 减少网络流量：逻辑封装在服务器端，减少了网络传输的数据量。

SQL学习专栏系列

练习代码库地址

SQL学习 sql-study

avatar
文章
阅读
粉丝