Create, modify, and delete a table in MySQL (1/2)

The basic knowledge of creating, modifying, and deleting mysql Data Tables is also the basic knowledge. The following section describes how to create, modify, and delete tables in MySQL, hope to help you.

In fact, many people have forgotten a lot about SQL statements, or do not understand a lot. Because of the database graphic operation software, it is convenient for everyone, but we cannot forget the most fundamental thing, in particular, you may be familiar with Hibernate and do not need to write SQL statements, but not all projects need to use big frameworks. If not, so you won't be able to operate the database, so we 'd better be familiar with it and it will help us find a job and work in the future.

Before creating, modifying, and deleting a table, let's make a simple description:
1. log on to the Database System

Log on to the MySQL database management system in the command line and enter the following content:

Mysql-h localhost-u root-p

Many people know this, but we still need to know the specific representation of the parameters, including:

-H: indicates the connection host name. It is localhost by default and can be ignored;
-U: indicates the user name. The user name here is root;
-P: indicates the user's password.

Press Enter to display "Enter password:". Enter the password to log in.

The Code is as follows:

Copy code

Welcome to the MySQL monitor. Commands end with; or g. Your MySQL connection id is 2 Server version: 5.6.13 Source distribution Copyright (c) 2000,201 3, 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>

2. Create a database

Before creating a database, you can view the existing database:

Format:

Create database name;

Example: view an existing database

The Code is as follows:	Copy code
Mysql> show databases; ++ | Database | ++ | Information_schema | | Mysql | | Performance_schema | ++ 3 rows in set (0.00 sec)

Example: Create a database named example

The Code is as follows:	Copy code
Mysql> create databases 'example '; Query OK, 1 row affected (0.00 sec) Mysql> show databases; ++ | Database | ++ | Information_schema | | Example | | Mysql | | Performance_schema | ++ 4 rows in set (0.00 sec)

3. delete a database:

Format:

Drop database name;

Example: Delete the example Database

The Code is as follows:	Copy code
Mysql> drop database 'example '; Query OK, 0 rows affected (0.02 sec) Mysql> show databases; ++ | Database | ++ | Information_schema | | Mysql | | Performance_schema | ++ 3 rows in set (0.00 sec)

4. database storage engine

The storage engine refers to the table type. The database storage engine determines the storage mode of the table on the computer.

Command for querying the storage engine type in MySQL: show engines;

The Code is as follows:

Copy code

Mysql> show engines; ++-++ | Engine | Support | Comment | Transactions | XA | Savepoints | ++-++ | FEDERATED | NO | Federated MySQL storage engine | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | | MyISAM | YES | MyISAM storage engine | NO | | BLACKHOLE | YES |/dev/null storage engine (anything you write to it disappears) | NO | | CSV | YES | CSV storage engine | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | | ARCHIVE | YES | Archive storage engine | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | ++-++ 9 rows in set (0.00 sec)

Query results:

Engine parameter refers to the name of the storage Engine;
The Support parameter indicates whether MySQL supports this type of engine;
Comment parameter indicates a Comment on the engine;
The Transaction parameter indicates whether Transaction processing is supported;
The XA parameter indicates whether the XA specification of Distributed Transaction processing is used;
The Savepoints parameter indicates whether the storage point is supported to facilitate transaction rollback;

From the above we can see that the InnoDB Storage engine is default, that is, the default database storage engine. Next we will briefly introduce InnoDB.

InnoDB is a storage engine of MySQL. InnoDB provides MySQL with transaction security, rollback, crash repair capabilities, and multi-version concurrency control. InnoDB is the first MySQL table engine that provides foreign key constraints, and its transaction processing capabilities are unmatched by other storage engines. However, the disadvantage of such an engine is that the Read and Write efficiency is slightly lower, and the Occupied data space is relatively large.

1 2