Implement terminal management of MySQL database on Mac

Open the terminal and enter the following command:

/usr/local/MySQL/bin/mysql -u root -p

where root is the user name.

The following command will appear:

Enter password:

If you have not changed your password at this time, just hit Enter. Otherwise, enter your password.
This way you can access your database server.

---

1. Operation and management

Basic of data table

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 |+--------------------+rows in set (0.00 sec)

1.3 alter Modify the 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 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 to provide us with future use:

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

After creation, remember to use the use command to enter (use) the database, otherwise subsequent 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, modify)
The table created by default 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 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

Tips: In MySQL, Boolean types are automatically converted to tinyint(1) types.

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, right?

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

You can also specify the length of int(n), such as int(2) .

We use desc again to 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, check 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, and 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.4.5 null or not null
Modify the table fields that are allowed to be empty or not allowed to be empty:

mysql> ALTER TABLE PEOPLE MODIFY AGE INT(3) NULL;
Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0

Set the AGE field of the PEOPLE table to "Allow to be empty", that is, this field does not need to be entered when inserting records. Otherwise it's the opposite.
Its format is: ALTER TABLE MODIFY

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

This operation, Including adding, deleting, modifying and checking 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 we view the data of 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 to delete 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" can no longer be seen.

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)

named The record for "Garvey" has been changed 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

, * 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 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.

4. Management view

4.1 Create a view

A view is a virtual table that exports one or more tables from the database for convenience. User operations on data.

mysql> CREATE VIEW PEOPLE_VIEW (
    -> NAME, AGE)
    -> AS SELECT NAME, AGE FROM PEOPLE;

View the view after successful creation.

PEOPLE          PEOPLE.AGE      PEOPLE.BIRTHDAY PEOPLE.ID       PEOPLE.NAME    mysql> SELECT * FROM PEOPLE_VIEW
    -> ;+--------+-----+
| NAME   | AGE |
+--------+-----+
| Anny   |  22 |
| Calvin |  23 |
| Nick   |  24 |
| Rick   |  24 |
+--------+-----+4 rows in set (0.00 sec)

We can also use the DESC command to view the structure of the view.

mysql> DESC PEOPLE_VIEW;
+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+| ID    | int(11) | NO   |     | 0       |       |
+-------+---------+------+-----+---------+-------+1 row in set (0.01 sec)

4.2 Replace view

Create or replace the original view.

mysql> CREATE OR REPLACE VIEW PEOPLE_VIEW(PEOPLE_ID,PEOPLE_NAME,PEOPLE_AGE) AS SELECT ID,NAME,AGE FROM PEOPLE;
Query OK, 0 rows affected (0.00 sec)

View view after creation or replacement.

mysql> SELECT * FROM PEOPLE_VIEW;
+-----------+-------------+------------+| PEOPLE_ID | PEOPLE_NAME | PEOPLE_AGE |
+-----------+-------------+------------+|         1 | Anny        |         22 |
|         2 | Calvin      |         23 |
|         4 | Nick        |         24 ||         5 | Rick        |         24 |
+-----------+-------------+------------+4 rows in set (0.00 sec)

4.3 Operating views

When the view data changes (add, delete, modify), the real table data will also change. In other words, the operation on the view is the data of the table, so we can treat the view as a table.

Example: Insert a piece of data into the view.

mysql> INSERT INTO PEOPLE_VIEW VALUES(NULL, 'Kerry', '33');
Query OK, 1 row affected (0.00 sec)

View the view after successfully inserting data.

mysql> SELECT * FROM PEOPLE_VIEW ;
+-----------+-------------+------------+| PEOPLE_ID | PEOPLE_NAME | PEOPLE_AGE |
+-----------+-------------+------------+|         1 | Anny        |         22 |
|         2 | Calvin      |         23 |
|         4 | Nick        |         24 |
|         5 | Rick        |         24 ||         6 | Kerry       |         33 |
+-----------+-------------+------------+5 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 ||  6 | Kerry  |  33 | NULL                |
+----+--------+-----+---------------------+5 rows in set (0.00 sec)

可见，真实的表数据也已经有所改变，刚刚往视图里插入的那一条数据存在于真实表中，真理便是：对视图的操作就是对表的数据。

4.4 删除视图

mysql> DROP VIEW PEOPLE_VIEW;Query OK, 0 rows affected (0.00 sec)

相关推荐：

mac使用终端运行mysql,mysql终端,mysql mac,mysql目录,mysql路径，macmysql

mac使用终端运行mysql,mysql终端,mysql mac,mysql目录,mysql路径

The above is the detailed content of Implement terminal management of MySQL database on Mac. For more information, please follow other related articles on the PHP Chinese website!