MySQL Terminal Management Database Operation Guide-Mysql Tutorial-php.cn

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;

Copy after login

1.2 show View all databases

mysql> show databases;

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

Copy after login

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)

Copy after login

##1.4 use Use the database

mysql> use firstDB;
Database changed

Copy after login

1.5 View the currently used database

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

Copy after login

##1.6 drop Delete the database

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

Copy after login

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)

Copy after login

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)

Copy after login

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)

Copy after login

##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)

Copy after login

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

Copy after login

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

Copy after login

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)

Copy after login

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

Copy after login

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)

Copy after login

##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

Copy after login

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)

Copy after login

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)

Copy after login

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

Copy after login

## 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)

Copy after login

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, &#39;Anny&#39;, 22, &#39;1992-05-22&#39;);
Query OK, 1 row affected (0.00 sec)

Copy after login

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)

Copy after login

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)

Copy after login

3.2 Delete data (delete)

delete command deletes data:

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

Copy after login

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)

Copy after login

The data named "Lisa" is no longer visible.

3.3 Modify data (change)

update command to modify data:

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

Copy after login

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)

Copy after login

##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)

Copy after login

Format: select * from , * 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)

Copy after login

Format: select from

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)!

Related labels：

mysql Operation guide

source：php.cn

Previous article：Performance comparison of MySQL insertion methods under large data volumes Next article：Detailed explanation of MySQL's InnoDB index principle

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn