Tutorial _ MySQL

This article describes how to use MySQL to operate databases and tables. the commands summarized in this article are all common commands that must be mastered by mysql. For more information, see I am a newbie

Learning how to manage and navigate to MySQL databases and tables is one of the first tasks to be mastered. the following content summarizes some common commands of MySQL databases and tables, some commands that we have to master, some handy commands.

Processing database

1. view the database

It is usually useful to obtain the database list on the server. Run the show databases command.

The code is as follows:

Mysql> show databases;

2. create a database

The code is as follows:

Mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)

3. use the database

Once a database is created, you can use the use command to specify it as the default working database.

The code is as follows:

Mysql> use db_test;
Database changed

4. delete a database

The method for deleting a database is similar to that for creating a database. You can use the drop command in the mysql client to delete a database, as shown below:

The code is as follows:

Mysql> drop database db_test;
Query OK, 0 rows affected (0.00 sec)

Processing Table

This section describes how to create, list, view, delete, and modify a MySQL database table.

1. create a table

You can use the create table statement to create a table. Many options and clauses are used during table creation. it is unrealistic to simply summarize them here. here, we will summarize them most commonly. in the future, we will summarize them separately. The common usage of creating a table is as follows:

The code 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, a table must contain at least one column. In addition, after creating a table, you can always go back and modify the table structure. You can create a table no matter whether or not you are using the target database. you only need to add the target database before the table name. For example:

The code is as follows:

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. Conditional table creation

By default, if you try to create an existing table, MySQL will generate an error. To avoid this error, the create table statement provides a sub-statement. if you want to simply exit table creation when the target table already exists, you can use this sub-statement. For example:

The code is as follows:

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)

The "Query OK" message is displayed when you return to the command prompt window whether or not you have created the command.

3. copy a table

Creating a new table based on an existing table is an easy task. The following code returns a copy of the tb_test table named tb_test2:

The code is as follows:

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

An identical table tb_test2 will be added to the database. In some cases, you may want to create a table only based on several columns of the existing table. You can use the create select statement to specify columns:

The code is as follows:

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 a temporary table

Sometimes, when you work on a very large table, you may occasionally need to run many queries to obtain a small subset of a large amount of data, instead of running these queries on the entire table, instead, it is faster for MySQL to locate a few required records and save the records to a temporary table. then, it queries these temporary tables. You can use the temporary keyword and the create table statement.

The code is as follows:

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 in the same way as other tables, but they are stored in the temporary directory specified by the operating system. Temporary tables will exist during your connection to MySQL. when you disconnect, MySQL will automatically delete the table and release all the memory space. of course, you can also manually use the drop table command to delete a temporary table.

5. View available tables in the database

You can run the show tables command. For example:

The code is as follows:

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 use the describe statement to view the table structure, for example:

The code is as follows:

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, use the show command to get the same results, for example:

The code is as follows:

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 a table

The drop table statement is used to delete a table. Its syntax is as follows:

The code is as follows:

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

8. change the table structure

We will find that we often modify and improve the table structure, especially in the early stages of development. However, you do not have to delete the table before creating a new table each time you modify it. Instead, you can use the alter Statement to modify the table structure. With this statement, you can delete, modify, and add columns if necessary. Like create table, alter table provides many clauses, keywords, and options. Here we will only talk about some simple usage. for example, insert a column in The tb_demo table to indicate email. the code is as follows:

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 appropriate keywords (including first, after, and last) to control the positions of new columns. If you want to modify the table, such as the newly added email address, I want to add a not null control. The code can be like this:

The code is as follows:

Mysql> alter table tb_demo change email varchar (45) not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

If you think this email column is unnecessary, you can use the following code to delete it, for example:

The code is as follows:

Mysql> alter table tb_demo drop email;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

I am not a newbie

This article roughly summarizes some frequently used commands for dealing with MySQL, hoping to help you. After reading this article, you should think that you are no longer a newbie. if you have practiced the above commands, you should be familiar with MySQL databases more than 60% of people. That's it. the simpler things, the more people don't.