MySQL Terminal Management Database Operation Guide

MySQL has many visual management tools, such as "mysql-workbench" and "sequel-pro-". Now I write articles about MySQL terminal command operations because I want to strengthen my understanding of MySQL. It will always be more thorough than using graphical methods, because I prefer to write code. At the same time, I write these articles to serve as a reference for everyone. I hope they can also help and improve everyone. This is why I want to write articles about terminal operation of MySQL.

Note: MySQL database commands are not case-sensitive. But in the MAC terminal, if you want to use tab to automatically complete the command, then you must use uppercase letters, so that the MAC terminal will complete the command for you, otherwise there will be no response if you press tab N times.

1. Database management

1.1 create Create database

create database firstDB;

1.2 show View all databases

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| firstDB            |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

1.3 alter Modify database

The alter command modifies the database encoding:

The database created by default does not support Chinese characters by default. If we need it to support Chinese characters, set its encoding to utf8 format:

mysql> ALTER DATABASE testDB CHARACTER SET UTF8;
Query OK, 1 row affected (0.00 sec)

##1.4 use Use the database

mysql> use firstDB;
Database changed

1.5 View the currently used database

mysql> select database();
+------------+
| database() |
+------------+
| firstdb    |
+------------+
1 row in set (0.00 sec)

##1.6 drop Delete the database

mysql> drop database firstDB;
Query OK, 0 rows affected (0.00 sec)

2. Data table (table) management

We first create a database , provide us with future use:

##

mysql> create database testDB;
Query OK, 1 row affected (0.00 sec)

After creating, remember to use the use command to enter (use) the database, otherwise All operations will be unsuccessful.

2.1 create Create table

mysql> create table PEOPLE (
    -> ID int AUTO_INCREMENT PRIMARY KEY,
    -> NAME varchar(20) not null,
    -> AGE int not null,
    -> BIRTHDAY datetime);                                                     
Query OK, 0 rows affected (0.01 sec)

2.2 show display table

Display all data tables in the current database

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| PEOPLE           |
+------------------+
1 row in set (0.00 sec)

##2.3 desc View table structure

mysql> desc PEOPLE
    -> ;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| ID       | int(11)     | NO   | PRI | NULL    | auto_increment |
| NAME     | varchar(20) | NO   |     | NULL    |                |
| AGE      | int(11)     | NO   |     | NULL    |                |
| BIRTHDAY | datetime    | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

2.4 alter Modify table structure (add, delete, change)

Default The created table does not support Chinese characters, so the table encoding needs to be set to utf8:

mysql> ALTER TABLE KEYCHAIN CONVERT TO CHARACTER SET UTF8;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

2.4.1 insert in Add columns (fields) to the table

mysql> alter table PEOPLE add star BOOL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Tip: In MySQL, the Boolean type will be automatically converted to tinyint(1 )type.

We might as well use desc to check the PEOPLE table structure:

mysql> desc PEOPLE;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| ID       | int(11)     | NO   | PRI | NULL    | auto_increment |
| NAME     | varchar(20) | NO   |     | NULL    |                |
| AGE      | int(11)     | NO   |     | NULL    |                |
| BIRTHDAY | datetime    | YES  |     | NULL    |                |
| star     | tinyint(1)  | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Now, you should believe me ?

2.4.2 alter Modify table (column) fields

mysql> alter table PEOPLE MODIFY star int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

We use it again desc View the PEOPLE table structure:

mysql> desc PEOPLE;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| ID       | int(11)     | NO   | PRI | NULL    | auto_increment |
| NAME     | varchar(20) | NO   |     | NULL    |                |
| AGE      | int(11)     | NO   |     | NULL    |                |
| BIRTHDAY | datetime    | YES  |     | NULL    |                |
| star     | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

##2.4.3 delete delete table (column) field

mysql> alter table PEOPLE DROP column star;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

After deletion, view the PEOPLE table structure again:

mysql> desc PEOPLE;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| ID       | int(11)     | NO   | PRI | NULL    | auto_increment |
| NAME     | varchar(20) | NO   |     | NULL    |                |
| AGE      | int(11)     | NO   |     | NULL    |                |
| BIRTHDAY | datetime    | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

The field was deleted successfully. Now we can no longer see the star field.

2.4.4 rename Rename the table name

mysql> RENAME TABLE PEOPLE TO NEW_PEOPLE;
Query OK, 0 rows affected (0.00 sec)

2.5 create Create a new table using existing data

mysql> create table newTable select * from PEOPLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

## Let’s check the tables that currently exist in the database:

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| PEOPLE           |
| newTable         |
+------------------+
2 rows in set (0.00 sec)

3. Data operation and management

Basic operations of data tables, including adding, deleting, modifying, and querying data.

The following commands all operate on the PEOPLE table.

3.1 Add data (increase)

The PEOPLE table currently has no data. It is an empty data table. Let’s add some data now.

insert into command to add data:

mysql> insert into PEOPLE VALUES (null, 'Anny', 22, '1992-05-22');
Query OK, 1 row affected (0.00 sec)

Use the select command to view the table (will be introduced later), now Let’s check the data in the PEOPLE data table:

mysql> select * from PEOPLE;
+----+------+-----+---------------------+
| ID | NAME | AGE | BIRTHDAY            |
+----+------+-----+---------------------+
|  1 | Anny |  22 | 1992-05-22 00:00:00 |
+----+------+-----+---------------------+
1 row in set (0.00 sec)

The data table now has a piece of data.

Let’s add a few more pieces of data, such as:

mysql> select * from PEOPLE;
+----+--------+-----+---------------------+
| ID | NAME   | AGE | BIRTHDAY            |
+----+--------+-----+---------------------+
|  1 | Anny   |  22 | 1992-05-22 00:00:00 |
|  2 | Garvey |  23 | 1991-05-22 00:00:00 |
|  3 | Lisa   |  25 | 1989-05-22 00:00:00 |
|  4 | Nick   |  24 | 1990-05-22 00:00:00 |
|  5 | Rick   |  24 | 1991-05-22 00:00:00 |
+----+--------+-----+---------------------+
5 rows in set (0.00 sec)

3.2 Delete data (delete)

delete command deletes data:

mysql> delete from PEOPLE where name = 'Lisa';
Query OK, 1 row affected (0.01 sec)

Query the PEOPLE table again:

mysql> select * from PEOPLE;
+----+--------+-----+---------------------+
| ID | NAME   | AGE | BIRTHDAY            |
+----+--------+-----+---------------------+
|  1 | Anny   |  22 | 1992-05-22 00:00:00 |
|  2 | Garvey |  23 | 1991-05-22 00:00:00 |
|  4 | Nick   |  24 | 1990-05-22 00:00:00 |
|  5 | Rick   |  24 | 1991-05-22 00:00:00 |
+----+--------+-----+---------------------+
4 rows in set (0.00 sec)

The data named "Lisa" is no longer visible.

3.3 Modify data (change)

update command to modify data:

mysql> update PEOPLE set name='Calvin' where name = 'Garvey';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query the contents of the PEOPLE table:

mysql> select * from PEOPLE;
+----+--------+-----+---------------------+
| ID | NAME   | AGE | BIRTHDAY            |
+----+--------+-----+---------------------+
|  1 | Anny   |  22 | 1992-05-22 00:00:00 |
|  2 | Calvin |  23 | 1991-05-22 00:00:00 |
|  4 | Nick   |  24 | 1990-05-22 00:00:00 |
|  5 | Rick   |  24 | 1991-05-22 00:00:00 |
+----+--------+-----+---------------------+
4 rows in set (0.00 sec)

##The record named "Garvey" has been modified to "Calvin".

3.4 Query data (check)

select command to query data. The simplest is to query all the data in the table, which is the command we used initially:

mysql> select * from PEOPLE;
+----+--------+-----+---------------------+
| ID | NAME   | AGE | BIRTHDAY            |
+----+--------+-----+---------------------+
|  1 | Anny   |  22 | 1992-05-22 00:00:00 |
|  2 | Calvin |  23 | 1991-05-22 00:00:00 |
|  4 | Nick   |  24 | 1990-05-22 00:00:00 |
|  5 | Rick   |  24 | 1991-05-22 00:00:00 |
+----+--------+-----+---------------------+
4 rows in set (0.00 sec)

Format: select * from ccc43248daffbac9770dee47fdaff697, * represents all fields.

You can also specify the displayed (column) fields when querying data:

mysql> select NAME, AGE, BIRTHDAY from PEOPLE;
+--------+-----+---------------------+
| NAME   | AGE | BIRTHDAY            |
+--------+-----+---------------------+
| Anny   |  22 | 1992-05-22 00:00:00 |
| Calvin |  23 | 1991-05-22 00:00:00 |
| Nick   |  24 | 1990-05-22 00:00:00 |
| Rick   |  24 | 1991-05-22 00:00:00 |
+--------+-----+---------------------+
4 rows in set (0.00 sec)

Format: select b1cf741c0f4de46280509a03275f60cf from ccc43248daffbac9770dee47fdaff697.

The select query command also has many advanced uses, such as finding unique data (distinct), sorting data according to conditions (order by), displaying data according to query conditions (where), etc. These will be highlighted in the next article. Please continue to pay attention to my blog. Thank you.

The above is the content of the MySQL terminal management database operation guide. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!