Shulou(Shulou.com)05/31 Report--

Editor to share with you how to create a database Mysql, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

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

4.2 Storage engine

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 is the storage engine in mysql database. Innodb provides transaction, rollback, crash repair and multi-version concurrency control transaction security to mysql tables.

(1) Innodb that. The storage engine supports auto-growing column auto_increment.

(2) Innodb storage engine supports foreign keys (foreign key)

(3) Innodb storage engine, the table structure of the created table is in the .frm file, and the data and indexes are stored in the table space defined by innodb_data_home_dir and innodb_data_file_path.

(4) the disadvantage of Innodb storage engine is that the reading and writing efficiency is relatively low, and the data space occupied is relatively large.

4.2.2 MyISAM engine

MyISAM is a common storage engine in mysql

The table of the MyISAM storage engine is stored in three files. The name of the file is the same as the table name, and the extension includes frm,MYD,MYI

. Where frm is the structure of file storage table with extension, MYD (MYData) is file storage data with extension, and MYI (MYindex) is file storage engine with extension.

The advantage of MyISAM storage engine lies in its small footprint and fast processing speed. The disadvantage is that the integrity and concurrency of transactions are not supported.

4.2.3 MEMORY

Special storage engine, table structure on disk, data in memory.

The size of the Memory table is displayed, depending on the two parameters max_rows and max_heap_table_size

4.2.4 selection of engines.

Characteristics

InnoDB

MyISAM

MEMORY

Transaction security

Support

None

None

Storage limit

64TB

Yes

Yes

Space usage

High

Low

Low

Memory usage

High

Low

High

The speed at which data is inserted

Low

High

High

The lag of foreign keys

Support

None

None

These are all the contents of the article "how to create a database in Mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.