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)
mysql> drop database firstDB; Query OK, 0 rows affected (0.00 sec)
2. Data table (table) management
##
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
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)
mysql> alter table PEOPLE MODIFY star int; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
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)
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)
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
The following commands all operate on the PEOPLE table.
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.
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)
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)
