Mysql数据库基本操作实战

小咪咪 2021-07-24 22:03 490阅读 0赞

**一 实战**

步骤1：登陆数据库：

mysql -h localhost -u root -p

E:\database\Mysql\MySQL 5.7从零开始学\课本源代码\03>mysql -h localhost -u root -p
    Enter password: ******
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.7.22 MySQL Community Server (GPL)
    
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

步骤2：创建数据库zookeep：

CREATE DATABASE zookeep;

SHOW DATABASES;

步骤3：选择当前数据库为zookeep：

USE zookeep;

mysql> USE zookeep;
    Database changed

查看数据库zoo的信息：

SHOW CREATE DATABASE zookeep;

mysql> SHOW CREATE DATABASE zookeep;
    +----------+------------------------------------------------------------------+
    | Database | Create Database                                                  |
    +----------+------------------------------------------------------------------+
    | zookeep  | CREATE DATABASE `zookeep` /*!40100 DEFAULT CHARACTER SET utf8 */ |
    +----------+------------------------------------------------------------------+
    1 row in set (0.00 sec)

步骤4：删除数据库zoo：

DROP DATABASE zoo;

mysql> DROP DATABASE zoo;
    Query OK, 0 rows affected (0.00 sec)

步骤5：查看存储引擎

SHOW ENGINES;

mysql> SHOW ENGINES;
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
    | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    9 rows in set (0.00 sec)

**二 存储引擎选择**

<table> 
 <tbody> 
 <tr> 
 <td> 功能 </td> 
 <td> MyISAM </td> 
 <td> Memory </td> 
 <td> InnoDB </td> 
 <td> Archive </td> 
 </tr> 
 <tr> 
 <td> 存储限制 </td> 
 <td> 256TB </td> 
 <td> RAM </td> 
 <td> 64TB </td> 
 <td> None </td> 
 </tr> 
 <tr> 
 <td> 支持事务 </td> 
 <td> No </td> 
 <td> No </td> 
 <td> Yes </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> 支持全文索引 </td> 
 <td> Yes </td> 
 <td> No </td> 
 <td> No </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> 支持数索引 </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> 支持哈希索引 </td> 
 <td> No </td> 
 <td> Yes </td> 
 <td> Yes </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> 支持数据缓存 </td> 
 <td> No </td> 
 <td> NA </td> 
 <td> Yes </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> 支持外键 </td> 
 <td> No </td> 
 <td> No </td> 
 <td> Yes </td> 
 <td> No </td> 
 </tr> 
 </tbody> 
</table>