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存储引擎

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

巴特西

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

最新文章

热门文章