[Mysql] 4.Mysql 建立資料庫

4.1 create and drop database

C:\Users\admin> mysql -h localhost -u root -pmysql

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.14 MySQL Community Server (GPL)

 

Copyright (c) 2000, 2016, 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.

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| test               |

+--------------------+

5 rows in set (0.00 sec)

 

mysql> create database example;         #create database

Query OK, 1 row affected (0.04 sec)

 

mysql> show databases;                # 顯示 databases

+--------------------+

| Database           |

+--------------------+

| information_schema |

| example            |

| mysql              |

| performance_schema |

| sys                |

| test               |

+--------------------+

6 rows in set (0.00 sec)

 

mysql> drop database example;              #drop database;

Query OK, 0 rows affected (0.04 sec)

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| test               |

+--------------------+

5 rows in set (0.00 sec)

 

mysql> create database mysql;

ERROR 1007 (HY000): Can't create database 'mysql'; database exists

mysql> create database mydata;

Query OK, 1 row affected (0.01 sec)

 

4.2 儲存引擎

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)

 

mysql>

 

4.2.1 InnoDB engine

InnoDB 是 mysql 資料庫中的儲存引擎 ,innodb 給 mysql 的表提供了事務，回滾，崩潰修復能力，多版本併發控制的事務安全

(1)     Innodb 那個。儲存引擎支援自動增長列 auto_increment.

(2)     Innodb 儲存引擎支援外來鍵 (foreign key)

(3)     Innodb 儲存引擎，建立的表的表結構在 .frm 檔案中，資料和索引儲存在 innodb_data_home_dir 和 innodb_data_file_path 定義的表空間

(4)     Innodb 儲存引擎的 缺點 是讀寫效率稍差，佔用的資料空間相對較大。

 

4.2.2 MyISAM engine

MyISAM 是 mysql 中常見的儲存引擎

MyISAM 儲存引擎的表儲存成三個檔案。檔案的名字與表名相同，副檔名包括 frm,MYD,MYI,

. 其中 frm 為副檔名的檔案儲存表的結構； MYD(MYData) 為副檔名的檔案儲存資料， MYI(MYindex) 為副檔名的檔案儲存引擎

MyISAM 儲存引擎的優勢在於佔用空間小，處理速度快。缺點是不支援事務的完整性和併發性。

 

4.2.3 MEMORY

特殊儲存引擎 , 表結構在 disk 上，資料在記憶體中。

Memory 表的大小受到顯示，表的大小取決於兩個引數 max_rows 和 max_heap_table_size

 

4.2.4 engines 的選擇 .

特性	InnoDB	MyISAM	MEMORY
事務安全	支援	無	無
儲存限制	64TB	有	有
空間使用	高	低	低
記憶體使用	高	低	高
插入資料的速度	低	高	高
對外來鍵的遲滯	支援	無	無