掌握数据库操作:索引、视图、备份和恢复

来自:互联网
时间:2024-09-17
阅读:
免费资源网 - https://freexyz.cn/

介绍

掌握数据库操作:索引、视图、备份和恢复

在本实验中,我们将学习和练习索引、视图、备份和恢复。这些概念对于数据库管理员来说非常重要。

学习目标

  • 创建索引
  • 创建视图
  • 备份与恢复

准备

开始之前,我们需要准备好环境。

启动MySQL服务并以root身份登录。

cd ~/project
sudo service mysql start
mysql -u root

加载文件中的数据。需要在mysql控制台输入命令来构建数据库:

source ~/project/init-database.txt

指数

索引是与表相关的结构。它的作用相当于一本书的目录。您可以根据目录中的页码快速找到内容

当你要查询一张记录数量较多的表,并且该表没有索引时,那么会拉出所有记录一一匹配搜索条件,并返回符合条件的记录。非常耗时,导致大量的磁盘i/o操作。

如果表中存在索引,那么我们可以通过索引值快速找到表中的数据,从而大大加快查询过程。

有两种方法可以为特定列设置索引:

alter table table name add index index name (column name);

create index index name on table name (column name);

让我们使用这两条语句来构建索引。

在employee表的id列建立idx_id索引:

alter table employee add index idx_id (id);

在employee表的name列建立idx_name索引

create index idx_name on employee (name);

我们使用索引来加速查询过程。当没有足够的数据时,我们将无法感受到它的神奇力量。这里我们使用命令show index from table name来查看我们刚刚创建的索引

show index from employee;
mariadb [mysql_labex]> alter table employee add index idx_id (id);
query ok, 0 rows affected (0.005 sec)
records: 0  duplicates: 0  warnings: 0

mariadb [mysql_labex]> show index from employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| table    | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment | ignored |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| employee |          0 | primary  |            1 | id          | a         |           5 |     null | null   |      | btree      |         |               | no      |
| employee |          0 | phone    |            1 | phone       | a         |           5 |     null | null   |      | btree      |         |               | no      |
| employee |          1 | emp_fk   |            1 | in_dpt      | a         |           5 |     null | null   |      | btree      |         |               | no      |
| employee |          1 | idx_id   |            1 | id          | a         |           5 |     null | null   |      | btree      |         |               | no      |
| employee |          1 | idx_name |            1 | name        | a         |           5 |     null | null   | yes  | btree      |         |               | no      |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
5 rows in set (0.000 sec)

当我们使用select语句查询时,where条件会自动判断是否存在索引。

看法

视图是从一个或多个表派生的虚拟表。它就像一个窗口,通过它人们可以查看系统提供的特殊数据,从而不必查看数据库中的全部数据。他们可以专注于他们感兴趣的事情。

如何解释“view是一个虚拟表”?

  • 数据库中只存储view的定义,而其数据存储在原表中;
  • 当我们使用view查询数据时,数据库会相应地从原表中提取数据。
  • 由于view中的数据取决于原始表中存储的内容,因此一旦表中的数据发生变化,我们在view中看到的内容也会发生变化。
  • 将 view 视为表格。

创建view时使用的语句格式:

create view view name (column a, column b, column c) as select column 1, column 2, column 3 from table name;

从语句中我们可以看到后半部分是一条select语句,这意味着view也可以建立在多个表上。我们需要做的就是在 select 语句中使用子查询或 join 。

现在让我们创建一个名为v_emp的简单视图,其中包含三列v_namev_agev_phone:

create view v_emp (v_name,v_age,v_phone) as select name,age,phone from employee;

然后输入

select * from v_emp;
mariadb [mysql_labex]> create view v_emp (v_name,v_age,v_phone) as select name,age,phone from employee;
query ok, 0 rows affected (0.003 sec)

mariadb [mysql_labex]> select * from v_emp;
+--------+-------+---------+
| v_name | v_age | v_phone |
+--------+-------+---------+
| tom    |    26 |  119119 |
| jack   |    24 |  120120 |
| jobs   |  null |   19283 |
| tony   |  null |  102938 |
| rose   |    22 |  114114 |
+--------+-------+---------+
5 rows in set (0.000 sec)

备份

出于安全考虑,备份在数据库管理中极其重要。

导出文件仅保存数据库中的数据,而备份将整个数据库结构(包括数据、约束、索引、视图等)保存到新文件。

mysqldump是mysql中用于备份的实用程序。它生成一个 sql 脚本文件,其中包含从头开始重新创建数据库的所有基本命令,例如 create、insert 等。

使用mysqldump备份的声明:

mysqldump -u root database name > backup file name;   #backup entire database

mysqldump -u root database name table name > backup file name;  #backup the entire table

尝试备份整个数据库mysql_labex。将文件命名为 bak.sql。首先按ctrl+z退出mysql控制台,然后打开终端输入命令:

cd ~/project/
mysqldump -u root mysql_labex > bak.sql;

使用命令“ls”,我们会看到备份文件bak.sql;

cat bak.sql
-- mariadb dump 10.19  distrib 10.6.12-mariadb, for debian-linux-gnu (x86_64)
--
-- host: localhost    database: mysql_labex
-- ------------------------------------------------------
-- server version       10.6.12-mariadb-0ubuntu0.22.04.1

/*!40101 set @old_character_set_client=@@character_set_client */;
/*!40101 set @old_character_set_results=@@character_set_results */;
/*!40101 set @old_collation_connection=@@collation_connection */;
/*!40101 set names utf8 */;
/*!40103 set @old_time_zone=@@time_zone */;
/*!40103 set time_zone='+00:00' */;
/*!40014 set @old_unique_checks=@@unique_checks, unique_checks=0 */;
/*!40014 set @old_foreign_key_checks=@@foreign_key_checks, foreign_key_checks=0 */;
/*!40101 set @old_sql_mode=@@sql_mode, sql_mode='no_auto_value_on_zero' */;
/*!40111 set @old_sql_notes=@@sql_notes, sql_notes=0 */;

……

恢复

在本实验的前面,我们练习了使用备份文件来恢复数据库。我们使用了类似这样的命令:

source ~/project/init-database.txt

此语句从 import-database.txt 文件恢复 mysql_labex 数据库。

还有另一种方法来恢复数据库,但在此之前,我们需要先创建一个名为 test 的空数据库:

mysql -u root
create database test;
mariadb [(none)]> create database test;
query ok, 1 row affected (0.000 sec)

mariadb [(none)]> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_labex        |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.000 sec)

ctrl+z退出mysql。恢复bak.sql测试数据库:

mysql -u root test 



我们可以通过输入命令查看测试数据库中的表来确认恢复是否成功:<p>
<br></p><pre class="brush:php;toolbar:false">mysql -u root
use test
show tables
MariaDB [(none)]&gt; USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]&gt; SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| department     |
| employee       |
| project        |
| table_1        |
+----------------+
4 rows in set (0.000 sec)

我们可以看到4张表已经恢复到测试数据库了

 

概括

 

恭喜!您已经完成了有关 mysql 中其他基本操作的实验。您已经学习了如何创建索引、视图以及如何备份和恢复数据库。

 


? 现在练习:其他基本操作

想了解更多吗?

 

  • ?学习最新的mysql技能树
  •  
  • ? 阅读更多 mysql 教程
  •  
  • ? 加入我们的 discord 或发推文@wearelabex
免费资源网 - https://freexyz.cn/
返回顶部
顶部