Implement terminal management of MySQL database on Mac-Mysql Tutorial-php.cn

Open the terminal and enter the following command:

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

Copy after login

where root is the user name.

The following command will appear:

Enter password:

Copy after login

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;

Copy after login

1.2 show View all databases

mysql> show databases;+--------------------+| Database           |
+--------------------+| information_schema |
| firstDB            |
| mysql              |
| performance_schema |+--------------------+rows in set (0.00 sec)

Copy after login

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)

Copy after login

1.4 use use 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 to provide us with future use:

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

Copy after login

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)

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

Copy after login

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

Copy after login

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)

Copy after login

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

Copy after login

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)

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

Copy after login

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)

Copy after login

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

Copy after login

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

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

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

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 to delete 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" can no longer be seen.

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

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)

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.

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;

Copy after login

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)

Copy after login

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)

Copy after login

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)

Copy after login

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)

Copy after login

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, &#39;Kerry&#39;, &#39;33&#39;);
Query OK, 1 row affected (0.00 sec)

Copy after login

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)

Copy after login

可以在视图上看到我们刚刚插入的数据，现在我们就来验证一下真实的表是否也会作出变化。

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)

Copy after login

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

4.4 删除视图

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

Copy after login