数据库基本操作

1.创建数据库

• show databases; 查看当前所有存在的数据库

  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | sakila             |
  | sys                |
  | world              |
  | zoo                |
  +--------------------+
  7 rows in set (0.00 sec)
  

• create database zoo;   创建数据库zoo，创建时不能与已有的数据库重名

  mysql> create database zoo;
  Query OK, 1 row affected (0.00 sec)
  

  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | sakila             |
  | sys                |
  | world              |
  | zoo                |
  +--------------------+
  7 rows in set (0.00 sec)
  

  
  

• show create database zoo;   查看数据库zoo创建信息。

  mysql> show create database zoo;
  +----------+----------------------------------------------------------------+
  | Database | Create Database                                                |
  +----------+----------------------------------------------------------------+
  | zoo      | CREATE DATABASE `zoo` /*!40100 DEFAULT CHARACTER SET latin1 */ |
  +----------+----------------------------------------------------------------+
  1 row in set (0.00 sec)
  

  
  
  

2.删除数据库

• drop database zoo;   删除数据库zoo,若指定的数据库不存在，则删除出错。

  mysql> drop database zoo;
  Query OK, 0 rows affected (0.00 sec)
  

  使用Drop database命令要谨慎，在执行该命令时，MySQL不会给出任何提醒确认信息，且删除数据库后，数据库中存储的所有数据表和数据也将一同被删除，且不能在恢复。
  
  
  

3.数据库存储引擎

• 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)
  

  YES表示可以使用，NO表示不能使用，DEFAULT表示默认。
  
  

• show variables like '%storage_engine%';   查看默认存储引擎。

  mysql> show variables like '%storage_engine%';
  +----------------------------------+--------+
  | Variable_name                    | Value  |
  +----------------------------------+--------+
  | default_storage_engine           | InnoDB |
  | default_tmp_storage_engine       | InnoDB |
  | disabled_storage_engines         |        |
  | internal_tmp_disk_storage_engine | InnoDB |
  +----------------------------------+--------+
  4 rows in set, 1 warning (0.00 sec)