MySQL common command MySQL handles commands for databases and tables

Learning how to manage and navigate MySQL's databases and tables is one of the first tasks to master. The following is a summary of 1 common commands for MySQL's databases and tables, 1 that we have to master, and 1 that we have come up with.
1. Work with the database

1, view the database
It is often useful to get a list of databases on the server. Perform show databases; The command will do.
mysql > show databases;

Create a database
mysql > create database db_test;
Query OK, 1 row affected (0.00 sec)

Use a database
Once a database is created, it can be specified as the default working database by using the use command.
mysql > use db_test;
Database changed

4. Delete the database
The way the database is deleted is similar to the way it is created. You can use the drop command in the mysql client to delete the database, as follows:
mysql > drop database db_test;
Query OK, 0 rows affected (0.00 sec)

2. The treatment table

Here's how to create, list, view, delete, and modify MySQL database tables.

1. Create tables
The table is created using the create table statement. There are a lot of options and clauses used in the process of creating the table. It is not realistic to summarize the table completely for one time. Here is only the most common summary. 1 General usage of creating a table is as follows:

mysql> create table tb_test(
  -> id int unsigned not null auto_increment,
  -> firstname varchar(25) not null,
  -> lastname varchar(25) not null,
  -> email varchar(45) not null,
  -> phone varchar(10) not null,
  -> primary key(id));
Query OK, 0 rows affected (0.03 sec)

Remember, the table contains at least 1 column. In addition, you can always go back and modify the structure of the table after you create it. You can create a table whether or not you are currently using the target database, simply by prefixing the target database to the table name. Such as:

mysql> create table db_test.tb_test(
  -> id int unsigned not null auto_increment,
  -> firstname varchar(25) not null,
  -> lastname varchar(25) not null,
  -> email varchar(45) not null,
  -> phone varchar(10) not null,
  -> primary key(id));
Query OK, 0 rows affected (0.03 sec)

2. Create tables conditionally
By default, MySQL will produce an error if you try to create a table that already exists. To avoid this error, the create table statement provides a clause that you can use if you want to simply exit table creation if the target table already exists. Such as:

mysql> create table if not exists db_test.tb_test(
  -> id int unsigned not null auto_increment,
  -> firstname varchar(25) not null,
  -> lastname varchar(25) not null,
  -> email varchar(45) not null,
  -> phone varchar(10) not null,
  -> primary key(id));
Query OK, 0 rows affected, 1 warning (0.00 sec)

Whether or not it has been created, the "Query OK" message is displayed when you return to the command prompt window.

3. Copy the table
Creating a new table based on an existing table is an easy task. The following code will give you a copy of the tb_test table named tb_test2:

mysql> create table tb_test2 select * from db_test.tb_test;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

The same table tb_test2 is added to the database. In some cases, you might want to create a table based on only a few columns of an existing table. By specifying the column in the create select statement:

mysql> describe tb_test;
+-----------+------------------+------+-----+---------+----------------+
| Field   | Type       | Null | Key | Default | Extra     |
+-----------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO  | PRI | NULL  | auto_increment |
| firstname | varchar(25)   | NO  |   | NULL  |        |
| lastname | varchar(25)   | NO  |   | NULL  |        |
| email   | varchar(45)   | NO  |   | NULL  |        |
| phone   | varchar(10)   | NO  |   | NULL  |        |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> create table tb_test2 select id, firstname, lastname, email from tb_test;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe tb_test2;
+-----------+------------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO  |   | 0    |    |
| firstname | varchar(25)   | NO  |   | NULL  |    |
| lastname | varchar(25)   | NO  |   | NULL  |    |
| email   | varchar(45)   | NO  |   | NULL  |    |
+-----------+------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

4. Create temporary tables
Sometimes, when working on a very large tables, may occasionally need to run a lot of queries to obtain a small subset of the large amounts of data, not for the whole table to run these queries, but to make MySQL every time to find the required a few records, record keeping to a temporary table 1, may be faster and query operation on the temporary table. This can be achieved by using the temporary keyword and the create table statement.

mysql> create temporary table emp_temp select firstname, lastname from tb_test;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

Temporary tables are created just like other tables 1, except that they are stored in a temporary directory specified by the operating system. The temporary table will exist during your connection to MySQL. When you disconnect, MySQL will automatically delete the table and free up all memory space. Of course, you can manually delete the temporary table using the drop table command.

View the tables available in the database
This can be done using the show tables command. Such as:

mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| tb_test      |
| tb_test2     |
+-------------------+
2 rows in set (0.00 sec)

6, view the table structure
You can view the table structure using the describe statement, for example:

mysql> describe tb_test;
+-----------+------------------+------+-----+---------+----------------+
| Field   | Type       | Null | Key | Default | Extra     |
+-----------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO  | PRI | NULL  | auto_increment |
| firstname | varchar(25)   | NO  |   | NULL  |        |
| lastname | varchar(25)   | NO  |   | NULL  |        |
| email   | varchar(45)   | NO  |   | NULL  |        |
| phone   | varchar(10)   | NO  |   | NULL  |        |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

In addition, using the show command yields the same result, for example:

mysql> show columns in tb_test;
+-----------+------------------+------+-----+---------+----------------+
| Field   | Type       | Null | Key | Default | Extra     |
+-----------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO  | PRI | NULL  | auto_increment |
| firstname | varchar(25)   | NO  |   | NULL  |        |
| lastname | varchar(25)   | NO  |   | NULL  |        |
| email   | varchar(45)   | NO  |   | NULL  |        |
| phone   | varchar(10)   | NO  |   | NULL  |        |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

7. Delete the table
The deletion table is implemented using the drop table statement, and the syntax is as follows:

drop [temporary] table [if exists] tbl_name [, tbl_name, ...]

8. Change the table structure
We find that we often modify and improve the table structure, especially in the early stages of development; However, you don't have to delete and recreate the table each time you make a change. Instead, you can use the alter statement to modify the structure of the table. With this statement, you can delete, modify, and add columns if necessary. Like create table1, alter table provides many clauses, keywords, and options. This is just a simple use of 1, such as inserting a column in table tb_demo to represent email. The code is as follows:

mysql> alter table tb_demo add column email varchar(45);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

The new column is placed at the end of the table. However, you can also use the appropriate keywords, including first, after, and last, to control the location of new columns. If I want to modify the table, for example, email I just added, I want to add an not null control, and the code could look like this:

mysql> create table db_test.tb_test(
  -> id int unsigned not null auto_increment,
  -> firstname varchar(25) not null,
  -> lastname varchar(25) not null,
  -> email varchar(45) not null,
  -> phone varchar(10) not null,
  -> primary key(id));
Query OK, 0 rows affected (0.03 sec)

0

If you feel that the email column is no longer necessary, you can delete it using the following code, for example:

mysql> create table db_test.tb_test(
  -> id int unsigned not null auto_increment,
  -> firstname varchar(25) not null,
  -> lastname varchar(25) not null,
  -> email varchar(45) not null,
  -> phone varchar(10) not null,
  -> primary key(id));
Query OK, 0 rows affected (0.03 sec)

1

This article generally summarizes 1 common commands with MySQL, mainly to learn Mysql common commands to deal with databases and tables, I hope to help you.