Mysql数据库（二）Mysql数据库管理

　　一 、创建数据库

　　1.通过CREATE DATABASE db_library;创建名称为db_library的数据库。

　　2.通过CREATE SCHEMA db_library1;创建名称为db_library1的数据库。

　　3.通过mysql> CREATE DATABASE db_library_gbk -> CHARACTER SET = GBK;指定其字符集为GBK。

　　4.通过CREATE DATABASE IF NOT EXISTS db_library2;判断该数据库名称是否存在，只有在不存在时才创建。

　　二、查看数据库

　　1.使用SHOW DATABASES;语句查看Mysql服务器中所有数据库名称。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_database13      |
| db_library         |
| db_library1        |
| db_library2        |
| db_library_gbk     |
| hotel              |
| mybatis            |
| mysql              |
| mysqldb            |
| performance_schema |
| se                 |
| spring             |
| springapp          |
| sys                |
| test               |
+--------------------+
16 rows in set (0.01 sec)

　　三、选择名称为db_library的数据库，设置其为当前默认的数据库

mysql> USE db_library;
Database changed

　　四、修改数据库db_library1，设置默认字符集为GBK和校对规则为简体中文

mysql> ALTER DATABASES db_library1
    -> DEFAULT CHARACTER SET gbk
    -> DEFAULT CHARACTER SET gbk^C
mysql> ALTER DATABASE db_library1
    -> DEFAULT CHARACTER SET gbk
    -> DEFAULT COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)

 　　五、删除数据库

mysql> DROP DATABASE db_library2;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_database13      |
| db_library         |
| db_library1        |
| db_library_gbk     |
| hotel              |
| mybatis            |
| mysql              |
| mysqldb            |
| performance_schema |
| se                 |
| spring             |
| springapp          |
| sys                |
| test               |
+--------------------+
15 rows in set (0.00 sec)

 　六、数据库存储引擎

　　存储引擎其实就是存储数据，为存储的数据建立索引，以及更新、查询数据等技术的实现方法。因为在关系数据库中数据是以表的形式存储的，所以存储引擎也可以成为表类型。

　　1.查询支持的全部存储引擎

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

 　　2.查询默认的存储引擎，为InnoDB。

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.01 sec)

 　　3.InnoDB存储引擎

　　优势在于提供了良好的事务管理、崩溃修复能力和并发控制。缺点是其读写速率稍差，占用的数据空间比较大。

　　4.MylSAM存储引擎

　　优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

　　5.MEMORY存储引擎

　　大小受限，其存在于内存中的特性使得这类表的处理速度非常快，但是其数据易丢失，声明周期短。