MySQL Terminal (Terminal) basic operations for managing databases, data tables, and data

MySQL has many visual management tools, such as ldquo, mysql-workbenchrdquo, and ldquo; sequel-pro-rdquo ;. Now I am writing MySQL terminal commands

MySQL has many visual management tools, such as ldquo, mysql-workbenchrdquo, and ldquo; sequel-pro-rdquo ;. Now I am writing MySQL terminal commands

MySQL has many visual management tools, such as "mysql-workbench" and "sequel-pro -". Now I am writing an article on MySQL terminal command operations to enhance my understanding of MySQL. I always have a better understanding than using graphical interfaces, because I prefer to write code. At the same time, I wrote these articles to serve as a reference for everyone, hoping to help and improve them. This is why I want to write articles on terminal operations for MySQL.

Note: MySQL database commands are case-insensitive. However, on a MAC terminal, if you want to use the tab to automatically complete the command, you must use uppercase letters so that the MAC terminal can complete the Command for you, otherwise, no response is returned if you press the tab N times.

1. database Management

1.1 create a database

1.2 show view all databases

1.3 alter modify Database

1.4 use Database

1.5 view the currently used database

1.6 drop delete database

2. table Management

2.1 create a table

2.2 show table

2.3 desc View table structure

2.4 alter modify the table structure (add, delete, and modify)

2.4.1 insert add columns (fields) to the table)

2.4.2 alter TABLE (column) Field

2.4.3 delete a table (column) Field

2.4.4 rename the table name

2.5 create use existing data to create a new table

3. Data Operations and Management

3.1 increase data (Increase)

3.2 delete data)

3.3 modify data)

3.4 query data (query)

1. database Management

1.1 create a database

Create database firstDB;

1.2 show view all databases

Mysql> show databases; information_schema mysqlrows in set (0.00 sec)

1.3 alter modify Database

Alter command to modify the database encoding:

The database created by default does not support Chinese characters by default. If you need to support Chinese characters, set the encoding to utf8:

MysqltestDB character set UTF8; Query OK, 1 row affected (0.00 sec)

1.4 use Database

Mysql> use firstDB; Database changed

1.5 view the currently used database

Mysql (); () firstdb row in set (0.00 sec)

1.6 drop delete database

Mysql firstDB; Query OK, 0 rows affected (0.00 sec)

2. table Management

First, we create a database to provide future usage:

Mysql testDB; Query OK, 1 row affected (0.00 sec)

Remember to use the use command to enter (use) The database after creation. Otherwise, subsequent operations will fail.

2.1 create a table

Mysql PEOPLE (-> ID int AUTO_INCREMENT primary key,-> NAME varchar (20) not null,-> BIRTHDAY datetime); Query OK, 0 rows affected (0.01 sec)

2.2 show table

Display All data tables of the current database

Mysql> show tables; Tables_in_testdb PEOPLErow in set (0.00 sec)

2.3 desc View table structure

Mysql> desc PEOPLE->; Field ExtraIDauto_increment (agebirthday yes rows in set (0.01 sec)

2.4 alter modify the table structure (add, delete, and modify)

The table created by default does not support Chinese characters, so you need to set the table encoding 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 add columns (fields) to the table)

MysqlPEOPLE add star BOOL; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

Tip: in MySQL, The boolean type is automatically converted to the tinyint (1) type.

Let's use desc to check the structure of the PEOPLE table:

Mysql> desc PEOPLE; Field ExtraIDauto_increment (agebirthday yes starrows in set (0.00 sec)

Now, should you trust me?

2.4.2 alter TABLE (column) Field

MysqlPEOPLE MODIFY star int; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

We use desc to view the structure of the PEOPLE table again:

Mysql> desc PEOPLE; Field ExtraIDauto_increment (agebirthday yes starrows in set (0.00 sec)

2.4.3 delete a table (column) Field